在开章有下载、导入官方测试数据教程。

查看库中所有表

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |部门
| dept_emp            |部门员工
| dept_manager        |部门管理人员
| employees           |总员工表
| salaries            |员工工薪
| titles              |员工职称(员工、高级工程师之类)
+---------------------+

设计图

employees总人员为主表,员工属性分为四个部分部门员工、管理员、工薪、职称,与总表都是多对一的关系

建表语句

  • departments
    dept_no为主键,dept_name设置唯一约束。
    CREATE TABLE `departments` (
    `dept_no` char(4) NOT NULL,
    `dept_name` varchar(40) NOT NULL,
    PRIMARY KEY (`dept_no`),
    UNIQUE KEY `dept_name` (`dept_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • dept_emp
    on delete cascade :从父表中删除数据时自动删除子表中的数据
    CREATE TABLE `dept_emp` (
    `emp_no` int(11) NOT NULL,
    `dept_no` char(4) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`dept_no`),
    KEY `emp_no` (`emp_no`),
    KEY `dept_no` (`dept_no`),
    CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
    CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • dept_manager
    CREATE TABLE `dept_manager` (
    `dept_no` char(4) NOT NULL,
    `emp_no` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`dept_no`),
    KEY `emp_no` (`emp_no`),
    KEY `dept_no` (`dept_no`),
    CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
    CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • employees
    CREATE TABLE `employees` (
    `emp_no` int(11) NOT NULL,
    `birth_date` date NOT NULL,
    `first_name` varchar(14) NOT NULL,
    `last_name` varchar(16) NOT NULL,
    `gender` enum('M','F') NOT NULL,
    `hire_date` date NOT NULL,
    PRIMARY KEY (`emp_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • salaries
    CREATE TABLE `salaries` (
    `emp_no` int(11) NOT NULL,
    `salary` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`from_date`),
    KEY `emp_no` (`emp_no`),
    CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  • titles
    CREATE TABLE `titles` (
    `emp_no` int(11) NOT NULL,
    `title` varchar(50) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date DEFAULT NULL,
    PRIMARY KEY (`emp_no`,`title`,`from_date`),
    KEY `emp_no` (`emp_no`),
    CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1