欧美一区二区三区,国内熟女精品熟女A片视频小说,日本av网,小鲜肉男男GAY做受XXX网站

mysql五個表如何建立連接

錢琪琛2年前7瀏覽0評論

在Mysql數據庫中,多張表之間的數據通常是相互關聯的。這些關聯有助于實現復雜的數據查詢和數據處理,因此需要建立這些表之間的連接。下面將介紹如何在Mysql中建立五個表的連接。

CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`department_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `department` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `task` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`employee_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `attendance` (
`id` int NOT NULL AUTO_INCREMENT,
`employee_id` int NOT NULL,
`punch_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `salary` (
`id` int NOT NULL AUTO_INCREMENT,
`employee_id` int NOT NULL,
`salary` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以上代碼創建了五個表:employee(員工表)、department(部門表)、task(任務表)、attendance(考勤表)、salary(薪資表)。

接下來,我們將通過使用外鍵將這些表連接起來。每個表都有一個主鍵,通過主鍵來建立連接。

ALTER TABLE `employee` ADD CONSTRAINT `employee_department_fk`
FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE `task` ADD CONSTRAINT `task_employee_fk`
FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`)
ON DELETE RESTRICT
ON UPDATE CASCADE;
ALTER TABLE `attendance` ADD CONSTRAINT `attendance_employee_fk`
FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE `salary` ADD CONSTRAINT `salary_employee_fk`
FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

以上代碼將建立employee表與department表的連接,task表與employee表的連接,attendance表與employee表的連接,以及salary表與employee表的連接。這些連接將會在刪除或更新數據時起到作用。

通過以上操作,五個表之間的連接成功建立,便可以對這些表進行復雜的數據處理和查詢。