在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表的連接。這些連接將會在刪除或更新數據時起到作用。
通過以上操作,五個表之間的連接成功建立,便可以對這些表進行復雜的數據處理和查詢。