MySQL数据库冰人集团

冰人集团系统数据库设计

最后修改：2022-03-31 版本：1.0.2

1. 使用MySQL 8.x版本数据库，使用Navicat作为数据库客户端管理图形界面工具。
2. 创建名为“iceman2022”的数据库。字符集使用utf8 -- UTF-8 Unicode，排序规则使用utf8_general_ci。
3. 创建“部门”表，表名为t_department。字段为：
   1. 部门编号：dep_id int(10)主键，自增长，无符号。
   2. 部门名称：name varchar(20)，非空。

dep_id	name
1	财务部
2	人事部
3	市场部

1. 创建“员工”表，表名为t_employee。字段为：
   1. 员工编号：emp_id int(10)主键，自增长，无符号。
   2. 姓名：name varchar(10)非空
   3. 性别：sex char(1)默认值为男，非空
   4. 出生日期：birthday date（创建触发器，验证不能大于系统当前日期）
   5. 是否党员：political_status bit(1)默认值为0，值为1表示党员
   6. 入职时间：hiredate date（创建触发器，验证不能大于系统当前日期）
   7. 籍贯：native_place varchar(20)
   8. 所在部门编号：dep_id int(10) 无符号，外键，对应于部门表的主键
2. 创建“工资”表，表名为：t_salary，字段为：
   1. 员工编号employee_id int(10)无符号 外键：员工表 –> 员工编号
   2. 月份pay_month int(2) 为1到12之间的正整数
   3. 基本工资base_salary decimal(10,2)必须大于等于零
   4. 津贴pension decimal(10,2)必须大于等于零
   5. 补助allowance decimal(10,2)必须大于等于零
   6. 奖金bonus decimal(10,2)必须大于等于零
   7. 扣除deduct decimal(10,2)必须大于等于零
   8. 税收tax decimal(10,2)必须大于等于零

emp_id	name	sex	birthday	political_status	hiredate	native_place	dep_id
1	孙华	男	1952-1-3	1	1970-10-10	天津市	1
2	陈明	男	1945-5-8	0	1965-01-01	北京市	2
3	程西	女	1980-6-10	0	2002-07-10	北京市	1
4	苏天琪	女	1965-3-10	1	1987-07-10	天津市	2
5	刘少文	男	1942-1-11	0	1960-08-10		2
6	刘新	男	1952-10-8	0	1970-07-01	上海市	1
7	余辉	女	1980-12-4	0	2001-07-10	武汉市	3
8	王晓艳	女	1980-11-10	1	2001-07-10	河北省	2
9	李玉	女	1964-2-12	1	1989-07-15	天津市	1
10	周涛	男	1963-2-10	0	1983-07-20	河北省	3
11	欧阳少兵	男	1965-4-19	0	1984-07-12	北京市	2
12	张旗	男	1980-11-10	0	1999-09-18	天津市	3
13	李涵	女	1982-5-10	0	2002-02-14	天津市	3
14	魏君	女	1977-3-23	1	1993-07-10		3
15	李艺	男	1979-9-18	0	2001-07-20	上海市	3

emp_id	pay_month	base_salary	pension	allowance	bonus	deduct	tax
1	8	2800	1200	180	200	0	220
2	8	2600	1000	200	300	0	180
3	8	1880	880	200	260	50	120
4	8	2200	980	180	230	80	110
5	8	2300	880	210	0	0	125
6	8	2100	600	220	180	50	110
7	8	2200	800	180	200	120	120
8	8	2000	780	120	100	0	100
9	8	2500	870	130	150	0	120
10	8	2350	700	250	180	50	120
11	8	1900	800	260	130	0	100
12	8	1800	500	200	100	50	100
13	8	1580	500	200	100	0	100
14	8	1300	450	200	100	0	100
15	8	1380	450	200	100	0	10

1. 创建表完成后，做如下查询：
   1. 查询所有员工的基本信息
   2. 查询所有市场部员工的基本信息
   3. 查询出所有人事部员工的姓名和入职时间
   4. 查询出所有男员工的基本信息
   5. 查询出所有女员工的姓名、出生年月和所在部门
   6. 查询出所有入职时间在2000年以后的女员工的员工编号、姓名和入职时间
   7. 查询出所有员工的员工编号、姓名、入职时间和工龄（注：只考虑整年数，取得当前系统日期的是NOW()函数，取得日期的年份可使用YEAR函数
   8. 查询出所有姓名是两个字的员工基本信息
   9. 查询出所有姓李的员工的基本信息
   10. 查询出财务处的所有的党员
   11. 查询所有的女党员
   12. 查询出所有出生年月在1960以前的员工
   13. 查询出所有员工的姓名和年龄（注意：是年龄不是出生日期）
   14. 查询出所有籍贯是北京市或天津市的员工信息（使用or和in分别查询）
   15. 查询出所有籍贯不是天津市的员工信息（使用not和<>分别查询）
   16. 查询出所有籍贯不是天津市也不是北京市的员工信息（使用and和not in分别查询）
   17. 查询出员工编号在5 ~ 10之间的员工信息（使用and和between分别查询）
   18. 查询出所有员工的实际工资和员工编号
   19. 查询出所有实际工资在3000 ~ 3500之间的员工编号和税收
   20. 查询出所有籍贯为空的员工的基本信息
   21. 查询出姓李并且第二个字是“玉”或者“艺”字的员工
   22. 查询出姓李并且第二个字不是“玉”或者“艺”字的员工
   23. 查询出所有不姓“李”的员工的信息
   24. 查询出名字中没有“少”字的员工
   25. 现在每个员工都要拿出自己实际工资的百分之8作为住房公积金使用，查询出所有员工的员工编号的应拿出的公积金。
2. 针对上表，做如下操作：
   1. 新添加16号员工的信息，其它字段值自定
   2. 删除1950年以前的员工信息
   3. 工资的调整：
      1. 1 ~ 5号员工，基本工资增加200
      2. 6 ~ 10号员工，基本工资增加百分之十，津贴增加50
      3. 11号以上员工，基本工资增加150，津贴增加30，补助增加20
      4. 所有员工奖金增加80
      5. 有“扣除”项的员工，没有奖金
3. 高级查询：
   1. 按基本工资由大到小显示所有员工的员工编号和实际工资
   2. 按实际工资由大到小显示所有员工的员工编号和实际工资
   3. 按部门升序显示员工的员工编号和实际工资
   4. 求出所有员工的基本工资的和
   5. 求出所有员工的实际工资的和
   6. 求出5 ~ 10号没有被扣除工资的员工的基本工资和
   7. 求出所有员工基本工资的平均工资
   8. 求出所有员工实际工资的平均工资
   9. 统计出女工的数目
   10. 统计出天津的女工个数
   11. 统计出本月被扣除工资的员工数和扣除的总金额
   12. 统计出实际工资大于3000的员工数
   13. 统计出员工的最大工龄
   14. 统计出女员工的最小年龄
   15. 统计出天津女党员的最大年龄
   16. 统计出各部门最高的工龄
   17. 统计出最大的男工和女工的年龄
   18. 统计出各部门的员工数
   19. 列出各部门的女工数
   20. 列出各部门的男工数和女工数
   21. 列出员工数超过3人的部门号和员工数
   22. 列出女工数超过1人的部门号和女工数
4. 多表连接查询：
   1. 显示出所有员工的姓名和部门名
   2. 按部门升序显示出所有员工的姓名和部门名
   3. 显示出所有员工的姓名、基本工资和入职时间
   4. 显示出所有员工的姓名、年龄、部门和实际工资
   5. 显示出所有被扣工资的员工姓名和被扣金额
   6. 按部门升序显示员工的姓名、所在部门和实际工资
   7. 按部门名升序、实际工资降序显示部门名称、员工编号和实际工资
   8. 统计出天津籍员工的最高实际工资
5. 子查询：
   1. 显示出工龄最大的员工姓名和工龄
   2. 统计出所有北京籍员工的工资总和
   3. 统计出所有女员工的平均工资
   4. 统计出所有天津女党员的最低工资
   5. 查询所有市场部员工的基本信息
   6. 显示出最小的女员工的姓名和年龄
   7. 统计出实际工资最高的北京籍员工的姓名和实际工资
   8. 显示出年龄最大的天津女党员
   9. 显示出基本工资比天津市最高工资高的所有员工
   10. 显示出实际工资比总体平均工资低的女员工
   11. 显示出人数最多的部门的所有员工信息
   12. 显示出工资最高的员工的姓名，性别，籍贯和所在部门名称
   13. 显示出比天津市入职最晚的员工入职还晚的员工
   14. 显示出比‘刘新’工资高的所有员工姓名和实际工资
   15. 显示出奖金和“李艺”一样多的员工姓名和所在部门名称
   16. 给所有市场部的员工增加200元的基本工资
   17. 所有非天津籍员工的补助增加100元作为思乡补助
   18. 删除所有上海籍员工的基本信息（注意删除的次序）
   19. 辞退扣除最多的员工

答案：

-- 1) 查询所有员工的基本信息
SELECT * FROM t_employee

-- 2) 查询所有市场部员工的基本信息
SELECT * FROM t_employee WHERE dep_id=3

-- 3) 查询出所有人事部员工的姓名和入职时间
SELECT `name`,hiredate FROM t_employee WHERE dep_id=2

-- 4) 查询出所有男员工的基本信息
SELECT * FROM t_employee WHERE sex='男'

-- 5) 查询出所有女员工的姓名、出生年月和所在部门
SELECT `name`,birthday,dep_id FROM t_employee WHERE sex='女'

-- 6) 查询出所有入职时间在2000年以后的女员工的员工编号、姓名和入职时间
SELECT emp_id,`name`,hiredate FROM t_employee WHERE sex='女' AND hiredate>='2000-1-1'

-- 7) 查询出所有员工的员工编号、姓名、入职时间和工龄（注：只考虑整年数，取得当前系统日期的是NOW()函数，取得日期的年份可使用YEAR函数
SELECT emp_id,`name`,hiredate,YEAR(NOW())-YEAR(hiredate) AS 工龄 FROM t_employee

-- 8) 查询出所有姓名是两个字的员工基本信息
SELECT * FROM t_employee WHERE `name` like '__'

-- 9) 查询出所有姓李的员工的基本信息
SELECT * FROM t_employee WHERE `name` like '李%'

-- 10) 查询出财务处的所有的党员
SELECT * FROM t_employee WHERE dep_id=1 AND political_status=1

-- 11) 查询所有的女党员
SELECT * FROM t_employee WHERE sex='女' AND political_status=1

-- 12) 查询出所有出生年月在1960以前的员工
SELECT * FROM t_employee WHERE birthday<='1960-1-1'

-- 13) 查询出所有员工的姓名和年龄（注意：是年龄不是出生日期）
SELECT `name`,YEAR(NOW())-YEAR(birthday) AS 年龄 FROM t_employee

-- 14) 查询出所有籍贯是北京市或天津市的员工信息（使用or和in分别查询）
SELECT * FROM t_employee WHERE native_place='北京市' OR native_place='天津市'

SELECT * FROM t_employee WHERE native_place IN ('北京市','天津市')

-- 15) 查询出所有籍贯不是天津市的员工信息（使用not和<>分别查询）
SELECT * FROM t_employee WHERE NOT native_place='天津市'

SELECT * FROM t_employee WHERE native_place<>'天津市'

-- 16) 查询出所有籍贯不是天津市也不是北京市的员工信息（使用and和not in分别查询）
SELECT * FROM t_employee WHERE native_place<>'北京市' AND native_place<>'天津市'

SELECT * FROM t_employee WHERE native_place NOT IN('北京市','天津市')

-- 17) 查询出员工编号在5 ~ 10之间的员工信息（使用and和between分别查询）
SELECT * FROM t_employee WHERE emp_id>=5 AND emp_id<=10

SELECT * FROM t_employee WHERE emp_id BETWEEN 5 AND 10

-- 18) 查询出所有员工的实际工资和员工编号
SELECT emp_id,base_salary+pension+allowance+bonus-deduct-tax AS 实际工资 FROM t_salary

-- 19) 查询出所有实际工资在3000 ~ 3500之间的员工编号和税收
SELECT emp_id,tax FROM t_salary WHERE base_salary+pension+allowance+bonus-deduct-tax BETWEEN 3000 AND 3500

-- 20) 查询出所有籍贯为空的员工的基本信息
SELECT * FROM t_employee WHERE native_place IS NULL

-- 21) 查询出姓李并且第二个字是“玉”或者“艺”字的员工
SELECT * FROM t_employee WHERE `name` LIKE '李玉%' OR `name` LIKE '李艺%'

-- 22) 查询出姓李并且第二个字不是“玉”或者“艺”字的员工
SELECT * FROM t_employee WHERE `name` LIKE '李%' AND `name` NOT LIKE '李玉%' AND `name` NOT LIKE '李艺%'

-- 23) 查询出所有不姓“李”的员工的信息
SELECT * FROM t_employee WHERE `name` NOT LIKE '李%'

-- 24) 查询出名字中没有“少”字的员工
SELECT * FROM t_employee WHERE `name` NOT LIKE '%少%'

-- 25) 现在每个员工都要拿出自己实际工资的百分之8作为住房公积金使用，查询出所有员工的员工编号的应拿出的公积金。
SELECT emp_id,(base_salary+pension+allowance+bonus-deduct-tax)*0.08 AS 公积金 FROM t_salary

-- 7. 针对上表，做如下操作：
-- 1) 新添加16号员工的信息，其它字段值自定
INSERT INTO t_employee (emp_id,`name`,sex)
VALUES (16,'王大锤','男')

-- 2) 删除1950年以前的员工信息
-- 设置外键约束检查关闭首先执行
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM t_employee
WHERE YEAR(birthday) < 1950
-- 开启外键约束检查最后再开启外键约束
SET FOREIGN_KEY_CHECKS=1;
-- 3) 工资的调整：
-- a) 1 ~ 5号员工，基本工资增加200
UPDATE t_salary
SET base_salary = base_salary + 200
WHERE emp_id BETWEEN 1 AND 5

-- b) 6 ~ 10号员工，基本工资增加百分之十，津贴增加50
UPDATE t_salary
SET base_salary = base_salary + base_salary*0.1,
pension = pension + 50
WHERE emp_id BETWEEN 6 AND 10

-- c) 11号以上员工，基本工资增加150，津贴增加30，补助增加20
UPDATE t_salary
SET base_salary = base_salary + 150,
pension = pension + 30,
allowance = allowance +20
WHERE emp_id > 11

-- d) 所有员工奖金增加80
UPDATE t_salary
SET bonus = bonus + 80

-- e) 有“扣除”项的员工，没有奖金
UPDATE t_salary
SET bonus = 0
WHERE deduct > 0

-- 8. 高级查询：
-- 1) 按基本工资由大到小显示所有员工的员工编号和实际工资
SELECT emp_id,base_salary+pension+allowance+bonus-deduct-tax AS 实际工资 FROM t_salary ORDER BY base_salary DESC

-- 2) 按实际工资由大到小显示所有员工的员工编号和实际工资
SELECT emp_id,base_salary+pension+allowance+bonus-deduct-tax AS 实际工资 FROM t_salary ORDER BY base_salary+pension+allowance+bonus-deduct-tax DESC

-- 3) 按部门升序显示员工的员工编号和实际工资
SELECT t1.emp_id,(base_salary+pension+allowance+bonus-deduct-tax) 实际工资 FROM t_employee t1
INNER JOIN t_salary t2 on t2.emp_id=t1.emp_id
ORDER BY dep_id 

-- 4) 求出所有员工的基本工资的和
SELECT SUM(base_salary) AS 基本工资的和 FROM t_salary

-- 5) 求出所有员工的实际工资的和
SELECT SUM(base_salary+pension+allowance+bonus-deduct-tax) AS 实际工资的和 FROM t_salary

-- 6) 求出5 ~ 10号没有被扣除工资的员工的基本工资和
SELECT SUM(base_salary) AS 基本工资和 FROM (SELECT * FROM t_salary WHERE emp_id>=5 AND  emp_id<=10) t WHERE deduct=0

-- 7) 求出所有员工基本工资的平均工资
SELECT AVG(base_salary) AS 平均工资 FROM t_salary

-- 8) 求出所有员工实际工资的平均工资
SELECT AVG(base_salary+pension+allowance+bonus-deduct-tax) AS 平均工资 FROM t_salary

-- 9) 统计出女工的数目
SELECT COUNT(emp_id) FROM t_employee WHERE sex='女'

-- 10) 统计出天津的女工个数
SELECT COUNT(emp_id) FROM t_employee WHERE sex='女' AND native_place='天津市'

-- 11) 统计出本月被扣除工资的员工数和扣除的总金额
SELECT COUNT(emp_id) AS 员工数,SUM(deduct) AS 扣除的总金额 FROM t_salary WHERE deduct<>'0'

-- 12) 统计出实际工资大于3000的员工数
SELECT COUNT(DISTINCT emp_id) FROM t_salary WHERE (base_salary+pension+allowance+bonus-deduct-tax)>3000

-- 13) 统计出员工的最大工龄
SELECT MAX(YEAR(NOW())-YEAR(hiredate)) AS 最大工龄 FROM t_employee

-- 14) 统计出女员工的最小年龄
SELECT MIN(YEAR(NOW())-YEAR(birthday)) AS 最小年龄 FROM t_employee WHERE sex='女'

-- 15) 统计出天津女党员的最大年龄
SELECT MAX(YEAR(NOW())-YEAR(birthday)) AS 最大年龄 FROM t_employee WHERE political_status=1 AND sex='女' AND native_place='天津市'

-- 16) 统计出各部门最高的工龄
SELECT dep_id,MAX(YEAR(NOW())-YEAR(hiredate)) AS 最大工龄 FROM t_employee 
WHERE dep_id IS NOT NULL
GROUP BY dep_id

-- 17) 统计出最大的男工和女工的年龄
SELECT sex,MAX(YEAR(NOW())-YEAR(birthday)) AS 最大年龄 FROM t_employee GROUP BY sex

-- 18) 统计出各部门的员工数
SELECT dep_id,count(0) AS 员工数 FROM t_employee GROUP BY dep_id;

-- 19) 列出各部门的女工数
SELECT dep_id,count(0) AS 女员工数 FROM t_employee WHERE sex='女' GROUP BY dep_id;

-- 20) 列出各部门的男工数和女工数
SELECT dep_id,sex,count(emp_id) AS 员工数 FROM t_employee GROUP BY dep_id,sex;

-- 21) 列出员工数超过3人的部门号和员工数
SELECT dep_id,count(emp_id) AS 员工数 FROM t_employee GROUP BY dep_id HAVING count(emp_id)>3;

-- 22) 列出女工数超过1人的部门号和女工数
SELECT dep_id,count(emp_id) AS 员工数 FROM t_employee 
WHERE sex='女' 
GROUP BY dep_id 
HAVING count(emp_id)>1;

-- 9. 多表连接查询：
-- 1) 显示出所有员工的姓名和部门名
SELECT t1.`name`,t2.`name`  FROM t_employee t1 
INNER JOIN t_department t2 ON t1.dep_id=t2.dep_id

-- 2) 按部门升序显示出所有员工的姓名和部门名
SELECT t1.`name`,t2.`name`  FROM t_employee t1 
INNER JOIN t_department t2 ON t1.dep_id=t2.dep_id
ORDER BY t2.dep_id ASC

-- 3) 显示出所有员工的姓名、基本工资和入职时间
SELECT t1.`name`,t2.base_salary, t1.hiredate  FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id

-- 4) 显示出所有员工的姓名、年龄、部门和实际工资
SELECT t1.`name`,(YEAR(NOW())-YEAR(birthday)) AS 年龄, t1.dep_id,(base_salary+pension+allowance+bonus-deduct-tax) AS 实际工资 FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id

-- 5) 显示出所有被扣工资的员工姓名和被扣金额
SELECT t1.`name`,t2.deduct  FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id
WHERE deduct>0;

-- 6) 按部门升序显示员工的姓名、所在部门和实际工资
SELECT t1.`name`,(YEAR(NOW())-YEAR(birthday)) AS 年龄, t1.dep_id,(base_salary+pension+allowance+bonus-deduct-tax) AS 实际工资 FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id
ORDER BY t1.dep_id 

-- 7) 按部门名升序、实际工资降序显示部门名称、员工编号和实际工资
SELECT t3.`name`,t4.emp_id,(base_salary+pension+allowance+bonus-deduct-tax) AS 实际工资 FROM t_department t3
INNER JOIN(SELECT * FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id) t4 ON t3.dep_id=t4.dep_id
ORDER BY dep_id

-- 8) 统计出天津籍员工的最高实际工资
SELECT t1.`name`,MAX((base_salary+pension+allowance+bonus-deduct-tax)) AS 实际工资 FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id
WHERE t1.native_place='天津市'

-- 10. 子查询：
-- 1) 显示出工龄最大的员工姓名和工龄
SELECT `name`,(YEAR(NOW())-YEAR(hiredate)) 工龄 FROM t_employee
WHERE (YEAR(NOW())-YEAR(birthday))=(
SELECT max(YEAR(NOW())-YEAR(birthday)) FROM t_employee
)

-- 2) 统计出所有北京籍员工的工资总和
SELECT SUM(base_salary+pension+allowance+bonus-deduct-tax) 工资总和 FROM t_salary
WHERE emp_id in (
SELECT emp_id FROM t_employee
WHERE native_place='北京市'
)

-- 3) 统计出所有女员工的平均工资
SELECT avg(base_salary+pension+allowance+bonus-deduct-tax) 平均工资 FROM t_salary
WHERE emp_id IN(
SELECT emp_id FROM t_employee
WHERE sex='女'
)

-- 4) 统计出所有天津女党员的最低工资
SELECT min(base_salary+pension+allowance+bonus-deduct-tax) 平均工资 FROM t_salary
WHERE emp_id IN(
SELECT emp_id FROM t_employee
WHERE sex='女' AND native_place='天津市' AND political_status=1
)

-- 5) 查询所有市场部员工的基本信息
SELECT * FROM t_employee 
WHERE dep_id=(
SELECT dep_id FROM t_department 
WHERE name='市场部'
);

-- 6) 显示出最小的女员工的姓名和年龄
SELECT `name`,(YEAR(NOW())-YEAR(birthday)) 年龄 FROM t_employee
WHERE (YEAR(NOW())-YEAR(birthday)) =(
SELECT MIN(YEAR(NOW())-YEAR(birthday)) 年龄 FROM t_employee
WHERE sex='女'
) AND sex = '女'

-- 7) 统计出实际工资最高的北京籍员工的姓名和实际工资
SELECT `name`,(base_salary+pension+allowance+bonus-deduct-tax) 实际工资 FROM t_employee t3
INNER JOIN t_salary t4 on t3.emp_id=t4.emp_id
WHERE  (base_salary+pension+allowance+bonus-deduct-tax)=(
SELECT MAX(base_salary+pension+allowance+bonus-deduct-tax) 实际工资 FROM t_employee t1
INNER JOIN t_salary t2 on t1.emp_id=t2.emp_id 
WHERE t1.native_place='北京市'
) AND t3.native_place='北京市'

-- 8) 显示出年龄最大的天津女党员
SELECT * FROM t_employee 
WHERE (YEAR(NOW())-YEAR(birthday))=(
SELECT MAX(YEAR(NOW())-YEAR(birthday)) 年龄 FROM t_employee 
WHERE native_place='天津市' AND sex='女' AND political_status=1
) AND sex='女' AND native_place='天津市' AND political_status=1;

-- 9) 显示出基本工资比天津市最高工资高的所有员工
SELECT t3.* FROM t_employee t3 
INNER JOIN t_salary t4 ON t3.emp_id=t4.emp_id 
WHERE t4.base_salary>(
SELECT MAX(t2.base_salary) FROM t_employee t1 
INNER JOIN t_salary t2 ON t1.emp_id=t2.emp_id 
WHERE t1.native_place='天津市'
) AND t3.native_place='天津市'

-- 10) 显示出实际工资比总体平均工资低的女员工
SELECT t3.* FROM t_employee t3 INNER JOIN t_salary t4 on t3.emp_id=t4.emp_id WHERE (t4.base_salary+t4.pension+t4.allowance+t4.bonus-t4.deduct-t4.tax)<(
SELECT avg(base_salary+pension+allowance+bonus-deduct-tax)  FROM t_salary
) AND t3.sex='女'

-- 11) 显示出人数最多的部门的所有员工信息
SELECT * FROM t_employee WHERE
dep_id IN (
SELECT dep_id FROM(
   SELECT dep_id,COUNT(0) cnt FROM t_employee
GROUP BY dep_id
HAVING cnt = (
SELECT COUNT(0) cnt FROM t_employee
GROUP BY dep_id
ORDER BY cnt DESC LIMIT 1)
) t
)

-- 12) 显示出工资最高的员工的姓名，性别，籍贯和所在部门名称
SELECT t3.`name`,t3.sex,t3.native_place,t4.`name`FROM t_employee t3 
INNER JOIN t_department t4 on t3.dep_id=t4.dep_id 
WHERE t3.emp_id=(
SELECT emp_id FROM t_salary t1
WHERE (t1.base_salary+t1.pension+t1.allowance+t1.bonus-t1.deduct-t1.tax)=(
SELECT max(t2.base_salary+t2.pension+t2.allowance+t2.bonus-t2.deduct-t2.tax) FROM  t_salary t2))

-- 13) 显示出比天津市入职最晚的员工入职还晚的员工
SELECT * FROM t_employee
WHERE hiredate>(SELECT MAX(hiredate) FROM t_employee WHERE native_place='天津市')

-- 14) 显示出比‘刘新’工资高的所有员工姓名和实际工资
SELECT t4.`name`,(base_salary+pension+allowance+bonus-deduct-tax) 工资 FROM t_employee t4 
INNER JOIN t_salary t2 on t4.emp_id=t2.emp_id 
WHERE (base_salary+pension+allowance+bonus-deduct-tax)>
(SELECT (base_salary+pension+allowance+bonus-deduct-tax) 工资 FROM t_employee t3 
INNER JOIN t_salary t1 on t3.emp_id=t1.emp_id 
WHERE t3.`name`='刘新')

-- 15) 显示出奖金和“李艺”一样多的员工姓名和所在部门名称
SELECT t1.`name`,t2.`name` FROM t_employee t1
INNER JOIN t_department t2 on t1.dep_id = t2.dep_id
INNER JOIN t_salary t3 on t1.emp_id = t3.emp_id
WHERE bonus = (
    SELECT bonus FROM t_salary WHERE emp_id = (
        SELECT emp_id FROM t_employee WHERE `name` = '李艺'
    )
)

-- 16) 给所有市场部的员工增加200元的基本工资
UPDATE t_salary SET base_salary = base_salary + 200
WHERE emp_id in (
    SELECT emp_id FROM t_employee WHERE dep_id = (
        SELECT dep_id FROM t_department WHERE `name` = '市场部'
    )
)

-- 17) 所有非天津籍员工的补助增加100元作为思乡补助
UPDATE t_salary t3 SET t3.allowance=t3.allowance+100 
WHERE t3.emp_id in(SELECT t1.emp_id FROM t_employee t1 WHERE not t1.native_place='天津市')

-- 18) 删除所有上海籍员工的基本信息（注意删除的次序）
-- 删除工资表
DELETE FROM t_salary WHERE emp_id in (SELECT emp_id FROM t_employee WHERE native_place='上海市')

-- 删除员工表
DELETE FROM t_employee WHERE native_place='上海市'

-- 19) 辞退扣除最多的员工
-- 删除员工表
DELETE FROM t_employee WHERE emp_id=
(SELECT emp_id FROM t_salary 
WHERE deduct=(SELECT max(deduct) FROM t_salary ))

-- 删除工资表
DELETE  FROM t_salary WHERE deduct=(SELECT m FROM
(SELECT MAX(deduct) m FROM t_salary )t)