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)

); 

 

熟悉数据库

1.部门员工表

 

我们查看部门员工表的数据量

select count(*) from dept_emp;

 

 

 

使用去重操作之后：

select count(distinct emp_no) from dept_emp;

 

 

 

我们发现有很多数据重复了。那是什么原因造成的呢？

 

首先我们筛选出这些重复的数据信息：

select * 

from dept_emp

where emp_no in (select emp_no

from dept_emp 

    group by emp_no

    having count(emp_no)>1

);

 

 

我们可以发现同一个员工有在不同的部门工作的经历，说明了有331603-300024=31579名员工有调职的经历

 

2.薪资表

  

我们可以看出这里工资记录是按照“年”为单位进行记录的。

 

通过这个表的数据来查询资历最高的员工：

 

select max(a.pnum) max_old_year

from (

select emp_no  id,count(emp_no)  pnum

from salaries

group by emp_no

) a;

 

可知该公司干的最久的员工干了18年

 

 

 

 

 

问题设计：

查找该公司资历最老的员工的个人全部数据（salaries表和 employees 表） 

 

参考博客：

http://www.cnblogs.com/stream886/p/6254630.html