MySQL官网示例数据库emploees分析使用

1.下载与安装

下载地址：https://github.com/datacharmer/test_db

安装博客：https://blog.csdn.net/appleyuchi/article/details/79439387

2.ER图

注意：这里使用单下划线表示主键，使用双下划线表示即是主键也是外键。但是标准情况是使用虚线表示外键的，这里要注意一下

3.建表语句

CREATE TABLE employees (

    emp_no      INT             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)

);

 

CREATE TABLE departments (

    dept_no     CHAR(4)         NOT NULL,

    dept_name   VARCHAR(40)     NOT NULL,

    PRIMARY KEY (dept_no),

    UNIQUE  KEY (dept_name)

);

 

CREATE TABLE dept_manager (

   dept_no      CHAR(4)         NOT NULL,

   emp_no       INT             NOT NULL,

   from_date    DATE            NOT NULL,

   to_date      DATE            NOT NULL,

   KEY         (emp_no),

   KEY         (dept_no),

   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,

   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,

   PRIMARY KEY (emp_no,dept_no)

); 

 

CREATE TABLE dept_emp (

    emp_no      INT             NOT NULL,

    dept_no     CHAR(4)         NOT NULL,

    from_date   DATE            NOT NULL,

    to_date     DATE            NOT NULL,

    KEY         (emp_no),

    KEY         (dept_no),

    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,

    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,

    PRIMARY KEY (emp_no,dept_no)

);

 

CREATE TABLE titles (

    emp_no      INT             NOT NULL,

    title       VARCHAR(50)     NOT NULL,

    from_date   DATE            NOT NULL,

    to_date     DATE,

    KEY         (emp_no),

    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

    PRIMARY KEY (emp_no,title, from_date)

); 

 

CREATE TABLE salaries (

    emp_no      INT             NOT NULL,

    salary      INT             NOT NULL,

    from_date   DATE            NOT NULL,

    to_date     DATE            NOT NULL,

    KEY         (emp_no),

    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

    PRIMARY KEY (emp_no, from_date)

);

2.ER图

3.建表语句

更多相关文章

随机推荐