mysql基础小项目———雇员雇主参照完整性实战

1.创建一个job数据库，并在此数据库里创建departments（部门表）、employees（雇员表）、salaries（工资表）、dept_emp（雇员与部门关系表）
要求：
1）departments表中含有dept_no（部门编码）、dept_name（部门名称）两个字段，其中，dept_no 为定长字符型，长度为4，dept_name 为不定长字符型，长度为40，不能为空，主键为dept_no，唯一键为dept_name。

CREATE TABLE departments (
dept_no CHAR(4) PRIMARY KEY,
dept_name VARCHAR(40) NOT NULL  UNIQUE
)

2）employees表中含有emp_no（雇员ID）、birth_date（雇员生日）、name（雇员名字）、hire_date（入住时间），其中，emp_no 为整型，birth_date和hire_date均为日期类型，不能为空，name为不定长字符型，长度为14，不能为空。emp_no含有主键。

CREATE TABLE employees (
emp_no INT PRIMARY KEY,
birth_date date,
name VARCHAR(14) NOT NULL,
hire_date date
)

3）salaries表中含有emp_no（雇员ID），salary（工资）、month（月份）、level（工资等级），字段均为整型且不能为空，其中当前表中的emp_no存在外键关联到employees表中的emp_no字段。

CREATE TABLE salaries (
emp_no INT ,
CONSTRAINT employees_fk FOREIGN KEY(emp_no) REFERENCES employees(emp_no),
salary INT,
month INT,
level INT
)

4）dept_emp表中含有dept_no（部门编码）、emp_no（雇员ID），其中，dept_no 为定长字符型，长度为4，emp_no 为整型。且均不能为空。当前表中的emp_no存在外键关联到employees表中的emp_no字段。当前表中的dept_no存在外键关联到departments表中的dept_no字段。主键为dept_no和emp_no。

CREATE TABLE dept_emp (
dept_no CHAR(4) NOT NULL,
emp_no INT NOT NULL ,
PRIMARY KEY (dept_no,emp_no),
CONSTRAINT employees1_fk FOREIGN KEY(emp_no) REFERENCES employees(emp_no),
CONSTRAINT departments1_fk FOREIGN KEY(dept_no) REFERENCES departments(dept_no)
)

2.向departments表中插入以下几条数据：插入字段顺序为：dept_no，dept_name
(‘1001’,‘A’), (‘1002’,‘B’), (‘1003’,‘C’), (‘1004’,‘D’)
3.向employees表中插入以下几条数据：插入字段顺序为：emp_no，birth_date，name，hire_date
(‘100’,‘1990-08-19’,‘JACK’,‘20160811’),(‘101’,‘1970-08-12’,‘TOM’,‘20100606’),(‘102’,‘1996-03-19’,‘JAMES’,‘20140101’), (‘103’,‘1987-04-28’,‘KETTY’,‘20130910’), (‘104’,‘1983-05-19’,‘JIM’,‘20160418’);
4.向salaries表中插入以下几条数据：插入字段顺序为：emp_no，salary，month，level
(‘100’,‘12000’,‘201601’,2),(‘101’,‘9000’,‘201601’,1),(‘102’,‘90000’,‘201601’,10),(‘103’,‘2300’,‘201601’,1),(‘104’,‘4000’,‘201601’,1),(‘100’,‘12000’,‘201602’,2),(‘101’,‘9000’,‘201602’,1),(‘102’,‘90000’,‘201602’,10),(‘103’,‘2300’,‘201602’,1),(‘104’,‘4000’,‘201602’,1),(‘100’,‘12000’,‘201603’,2),(‘101’,‘9000’,‘201603’,1),(‘102’,‘90000’,‘201603’,10),(‘103’,‘2300’,‘201603’,1),(‘104’,‘4000’,‘201603’,1),(‘100’,‘12000’,‘201604’,2),(‘101’,‘9000’,‘201604’,1),(‘102’,‘90000’,‘201604’,10),(‘103’,‘2300’,‘201604’,1),(‘104’,‘4000’,‘201604’,1);
5.向dept_emp表中插入以下几条数据：插入字段顺序为：emp_no，dept_no
(‘100’,‘1001’), (‘101’,‘1001’), (‘102’,‘1002’), (‘103’,‘1003’), (‘104’,‘1004’);
2-5题：

INSERT INTO departments VALUES('1001','A'), ('1002','B'), ('1003','C'), ('1004','D')
INSERT INTO employees VALUES('100','1990-08-19','JACK','20160811'),('101','1970-08-12','TOM','20100606'),('102','1996-03-19','JAMES','20140101'), ('103','1987-04-28','KETTY','20130910'), ('104','1983-05-19','JIM','20160418')

INSERT INTO salaries VALUES ('100','12000','201601',2),('101','9000','201601',1),('102','90000','201601',10),('103','2300','201601',1),('104','4000','201601',1),('100','12000','201602',2),('101','9000','201602',1),('102','90000','201602',10),('103','2300','201602',1),('104','4000','201602',1),('100','12000','201603',2),('101','9000','201603',1),('102','90000','201603',10),('103','2300','201603',1),('104','4000','201603',1),('100','12000','201604',2),('101','9000','201604',1),('102','90000','201604',10),('103','2300','201604',1),('104','4000','201604',1);

INSERT INTO dept_emp(emp_no,dept_no)VALUES ('100','1001'), ('101','1001'), ('102','1002'), ('103','1003'), ('104','1004');

6.分别查看部门表和员工表中的所有记录

SELECT * FROM departments;
SELECT * FROM employees;

7.将雇员表按照出生日期进行降序排列，并找到前三条数据

SELECT * FROM EMPLOYEES ORDER BY birth_date DESC LIMIT 3;

8.查询2016年1月份的工资平均值

SELECT AVG(salary) FROM salaries WHERE MONTH=201601;

9.查询雇员ID小于103且名字以J开头的员工信息

SELECT * FROM employees WHERE emp_no<103 AND name LIKE 'J%';

10.查询雇员ID在101-103之间的员工信息（两种方式）

SELECT * FROM employees WHERE emp_no BETWEEN 101 AND 103;
SELECT * FROM employees WHERE emp_no >=101 AND emp_no<=103;

11.查询所有名字以M结尾的雇员信息，并按照入职时间进行倒叙排序

SELECT * FROM employees WHERE NAME LIKE '%M' ORDER BY hire_date DESC;

12.查询每个员工所属的部门

SELECT * FROM employees as a LEFT JOIN dept_emp AS b ON a.emp_no=b.emp_no LEFT JOIN departments as c ON b.dept_no=c.dept_no;

13.查询属于A部门的员工

SELECT * FROM employees as a LEFT JOIN dept_emp AS b ON a.emp_no=b.emp_no LEFT JOIN departments as c ON b.dept_no=c.dept_no WHERE c.dept_name='A';

14.查询属于A部门员工的平均工资

SELECT AVG(salary) FROM employees as a LEFT JOIN dept_emp AS b ON a.emp_no=b.emp_no LEFT JOIN departments as c ON b.dept_no=c.dept_no LEFT JOIN salaries AS d on a.emp_no=d.emp_no WHERE c.dept_name='A';

15 . 查询所有员工的薪水总和

 SELECT SUM(salary) FROM salaries;

16.查询各个部门的员工平均工资

SELECT dept_name,AVG(salary) FROM employees as a LEFT JOIN dept_emp AS b ON a.emp_no=b.emp_no LEFT JOIN departments as c ON b.dept_no=c.dept_no LEFT JOIN salaries AS d on a.emp_no=d.emp_no GROUP BY c.dept_name;

17.外键约束的几种方式及区别

CASCADE：表示父表在进行更新和删除时，更新和删除子表相对应的记录 
RESTRICT和NO ACTION：限制在子表有关联记录的情况下，父表不能单独进行删除和更新操作 
SET NULL：表示父表进行更新和删除的时候，子表的对应字段被设为NULL