数据库练习 数据库查询练习 ***
冰人集团系统数据库设计
1. 使用MySQL 8.x版本数据库，使用Navicat作为数据库客户端管理图形界面工具。
2. 创建名为“iceman2018”的数据库。字符集使用utf8 -- UTF-8 Unicode，排序规则使用utf8_general_ci。
3. 创建“部门”表，表名为t_department。字段为：
1) 部门编号：department_id int(10) 主键，自增长，无符号。
2) 部门名称：name varchar(20)，非空。
department_id | name |
1 | 财务部 |
2 | 人事部 |
3 | 市场部 |
4. 创建“员工”表，表名为 t_empoyee。字段为：
1) 员工编号：employee_id int(10) 主键，自增长，无符号。
2) 姓名：name varchar(10) 非空
3) 性别：sex char(1) 默认值男，非空
4) 出生日期：birthday date （创建触发器，验证不能大于系统当前日期）
5) 是否党员：sfdy bit(1) 默认值 0
6) 入职时间：rzsj date （创建触发器，验证不能大于系统当前日期）
7) 所在部门编号：department_id int(10) 无符号，外键，对应于部门表的主键
8) 籍贯：jiguan varchar(20)
employee_id | name | sex | birthday | sfdy | rzsj | jiguan | department_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 |
5. 创建“工资”表，表名为：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) 必须大于等于零
employee_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 |
6. 创建表完成后，做如下查询：
1) 查询所有员工的基本信息
2) 查询所有市场部员工的基本信息
3) 查询出所有人事部员工的姓名和入职时间
4) 查询出所有男员工的基本信息
5) 查询出所有女员工的姓名、出生年月和所在部门
6) 查询出所有入职时间在 2000 年以后的女员工的员工编号、姓名和入职时间
7) 查询出所有员工的员工编号、姓名、入职时间和工龄（注：只考虑整年数，取得当前系统日期的是 now() 函数，取得日期的年份是 date_format() 函数
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 作为住房公积金使用，查询出所有员工的员工编号的应拿出的公积金
7. 针对上表，做如下操作：
1) 新添加 16 号员工的信息，其它字段值自定
2) 删除 1950 年以前的员工信息
3) 工资的调整：
a) 1-5 号员工，基本工资增加200
b) 6-10 号员工，基本工资增加百分之十，津贴增加 50
c) 11 号以上员工，基本工资增加 150，津贴增加 30，补助增加 20
d) 所有员工奖金增加 80
e) 有“扣除”项的员工，没有奖金
8. 高级查询：
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人的部门号和女工数
9. 多表连接查询：
1) 显示出所有员工的姓名和部门名
2) 按部门升序显示出所有员工的姓名和部门名
3) 显示出所有员工的姓名、基本工资和入职时间
4) 显示出所有员工的姓名、年龄、部门和实际工资
5) 显示出所有被扣工资的员工姓名和被扣金额
6) 按部门升序显示员工的姓名、所在部门和实际工资
7) 按部门名升序、实际工资降序显示部门名称、员工编号和实际工资
8) 统计出天津籍员工的最高实际工资
10. 子查询：
1) 显示出工龄最大的员工姓名和工龄
2) 统计出所有北京籍员工的工资总和
3) 统计出所有女员工的平均工资
4) 统计出所有天津女党员的最低工资
5) 查询所有市场部员工的基本信息
6) 显示出最小的女员工的姓名和年龄
7) 统计出实际工资最高的北京籍员工的姓名和实际工资
8) 显示出年龄最大的天津女党员
9) 显示出基本工资比天津市最高工资高的所有员工
10) 显示出实际工资比总体平均工资低的女员工
11) 显示出人数最多的部门的所有员工信息
12) 显示出工资最高的员工的姓名，性别，籍贯和所在部门名称
13) 显示出比天津市入职最晚的员工入职还晚的员工
14) 显示出比‘刘新’工资高的所有员工姓名和实际工资
15) 显示出奖金和“李艺”一样多的员工姓名和所在部门名称
16) 给所有市场部的员工增加200元的基本工资
17) 所有非天津籍员工的补助增加100元作为思乡补助
18) 删除所有上海籍员工的基本信息（注意删除的次序）
19) 辞退扣除最多的员工
-- 6. 创建表完成后，做如下查询：
-- 1) 查询所有员工的基本信息
SELECT * FROM t_employee
-- 2) 查询所有市场部员工的基本信息
SELECT * FROM t_employee WHERE department_id = (
SELECT department_id FROM t_department WHERE name = '市场部'
)
-- 3) 查询出所有人事部员工的姓名和入职时间
SELECT name,rzsj FROM t_employee WHERE department_id = (
SELECT department_id FROM t_department WHERE name = '人事部'
)
-- 4) 查询出所有男员工的基本信息
SELECT * FROM t_employee WHERE sex = '男'
-- 5) 查询出所有女员工的姓名、出生年月和所在部门
SELECT t1.`name`,t1.birthday,t2.`name` FROM t_employee t1
INNER JOIN t_department t2
WHERE sex = '女'
-- 6) 查询出所有入职时间在 2000 年以后的女员工的员工编号、姓名和入职时间
SELECT employee_id,`name`,rzsj FROM t_employee WHERE sex = '女' AND rzsj > '2000-01-01'
-- 7) 查询出所有员工的员工编号、姓名、入职时间和工龄（注：只考虑整年数，取得当前系统日期的是 now() 函数，取得日期的年份是 date_format() 函数
SELECT employee_id,`name`,rzsj,YEAR(NOW()) - YEAR(rzsj) 工龄 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 sfdy = 1 AND department_id = (
SELECT department_id FROM t_department WHERE `name` = '财务部'
)
-- 11) 查询所有的女党员
SELECT * FROM t_employee WHERE sex = '女' AND sfdy = 1
-- 12) 查询出所有出生年月在 1960 以前的员工
SELECT * FROM t_employee WHERE YEAR (birthday) < 1960
-- 13) 查询出所有员工的姓名和年龄（注意：是年龄不是出生日期）
SELECT `name`,YEAR(NOW()) - YEAR(birthday) 年龄 FROM t_employee
-- 14) 查询出所有籍贯是北京市或天津市的员工信息（使用 or 和 in 分别查询）
SELECT * FROM t_employee WHERE jiguan = '天津市' OR jiguan = '北京市'
SELECT * FROM t_employee WHERE jiguan IN ('天津市','北京市')
-- 15) 查询出所有籍贯不是天津市的员工信息（使用 not 和 <> 分别查询）
SELECT * FROM t_employee WHERE jiguan <> '天津市'
SELECT * FROM t_employee WHERE jiguan != '天津市'
-- 16) 查询出所有籍贯不是天津市也不是北京市的员工信息（使用 and 和 not in 分别查询）
SELECT * FROM t_employee WHERE jiguan != '天津市' AND jiguan != '北京市'
SELECT * FROM t_employee WHERE jiguan NOT IN ('天津市','北京市')
-- 17) 查询出员工编号在 5-10 之间的员工信息（使用 and 和 between 分别查询）
SELECT * FROM t_employee WHERE employee_id BETWEEN 5 AND 10
SELECT * FROM t_employee WHERE employee_id >= 5 AND employee_id <= 10
-- 18) 查询出所有员工的实际工资和员工编号
SELECT (base_salary + pension + allowance + bonus - deduct - tax ),employee_id FROM t_salary
-- 19) 查询出所有实际工资在 3000-3500 之间的员工编号和税收
SELECT employee_id,tax FROM t_salary WHERE (base_salary + pension + allowance + bonus - deduct - tax) BETWEEN 3000 AND 3500
-- 20) 查询出所有籍贯为空的员工的基本信息
SELECT * FROM t_employee WHERE jiguan IS NULL
-- 21) 查询出姓李并且第二个字是“玉”或者“艺”字的员工
SELECT * FROM t_employee WHERE `name` = '李玉' OR `name` = '李艺'
-- 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 employee_id,(base_salary + pension + allowance + bonus - deduct - tax)*0.08 住房公积金 FROM t_salary
-- 8. 高级查询：
-- 1) 按基本工资由大到小显示所有员工的员工编号和实际工资
SELECT employee_id,(base_salary + pension + allowance + bonus - deduct - tax) FROM t_salary ORDER BY base_salary DESC
-- 2) 按实际工资由大到小显示所有员工的员工编号和实际工资
SELECT employee_id,(base_salary + pension + allowance + bonus - deduct - tax) FROM t_salary ORDER BY (base_salary + pension + allowance + bonus - deduct - tax) DESC
-- 3) 按部门升序显示员工的员工编号和实际工资
SELECT t2.employee_id,(base_salary + pension + allowance + bonus - deduct - tax) FROM t_salary t1
INNER JOIN t_employee t2 ON t1.employee_id = t2.employee_id
INNER JOIN t_department t3 ON t2.department_id = t3.department_id
ORDER BY t3.department_id DESC
-- 4) 求出所有员工的基本工资的和
SELECT SUM(base_salary) FROM t_salary
-- 5) 求出所有员工的实际工资的和
SELECT SUM(base_salary + pension + allowance + bonus - deduct - tax) FROM t_salary
-- 6) 求出 5-10 号没有被扣除工资的员工的基本工资和
SELECT SUM(base_salary) FROM t_employee t1
INNER JOIN t_salary t2 ON t1.employee_id = t2.employee_id
WHERE t2.deduct != 0
-- 7) 求出所有员工基本工资的平均工资
SELECT SUM(base_salary) FROM t_salary
-- 8) 求出所有员工实际工资的平均工资
SELECT AVG(base_salary + pension + allowance + bonus - deduct - tax) FROM t_salary
-- 9) 统计出女工的数目
SELECT count(*) FROM t_employee WHERE sex = '女'
-- 10) 统计出天津的女工个数
SELECT count(*) FROM t_employee WHERE sex = '女' AND jiguan = '天津市'
-- 11) 统计出本月被扣除工资的员工数和扣除的总金额
SELECT COUNT(*),sum(deduct) FROM t_salary WHERE deduct != 0
-- 12) 统计出实际工资大于 3000 的员工数
SELECT count(*) FROM t_salary WHERE (base_salary + pension + allowance + bonus - deduct - tax) > 3000
-- 13) 统计出员工的最大工龄
SELECT MAX(YEAR(NOW()) - YEAR(rzsj)) FROM t_employee
-- 14) 统计出女员工的最小年龄
SELECT MIN(YEAR(NOW()) - YEAR(birthday)) FROM t_employee WHERE sex = '女'
-- 15) 统计出天津女党员的最大年龄
SELECT MAX(YEAR(NOW()) - YEAR(birthday)) FROM t_employee WHERE sex = '女' AND jiguan = '天津市'
-- 16) 统计出各部门最高的工龄
SELECT MAX(YEAR(NOW()) - YEAR(rzsj)),department_id FROM t_employee GROUP BY department_id
-- 17) 统计出最大的男工和女工的年龄
SELECT MAX(YEAR(NOW()) - YEAR(birthday)),sex FROM t_employee GROUP BY sex
-- 18) 统计出各部门的员工数
SELECT COUNT(*),department_id FROM t_employee GROUP BY department_id
-- 19) 列出各部门的女工数
SELECT COUNT(*),department_id FROM t_employee where sex != '男' GROUP BY department_id
-- 20) 列出各部门的男工数和女工数
SELECT COUNT(*),department_id,sex FROM t_employee GROUP BY department_id,sex
-- 21) 列出员工数超过3人的部门号和员工数
SELECT department_id,count(*) FROM t_employee GROUP BY department_id HAVING department_id IS NOT NULL AND count(*) > 3
-- 22) 列出女工数超过1人的部门号和女工数
SELECT department_id,count(*) FROM t_employee WHERE sex = '女' GROUP BY department_id HAVING count(0) > 1
-- 9. 多表连接查询：
-- 1) 显示出所有员工的姓名和部门名
SELECT t1.name,t2.`name` FROM t_employee t1 INNER JOIN t_department t2 ON t1.department_id = t2.department_id
-- 2) 按部门升序显示出所有员工的姓名和部门名
SELECT t1.name,t2.`name` FROM t_employee t1
INNER JOIN t_department t2 ON t1.department_id = t2.department_id
ORDER BY t2.department_id
-- 3) 显示出所有员工的姓名、基本工资和入职时间
SELECT t1.name,t2.base_salary,t1.rzsj FROM t_employee t1
INNER JOIN t_salary t2 ON t1.employee_id = t2.employee_id
-- 4) 显示出所有员工的姓名、年龄、部门和实际工资
SELECT t1.name,YEAR(NOW()) - YEAR(birthday),t3.`name`,(base_salary + pension + allowance + bonus - deduct - tax) FROM t_employee t1
INNER JOIN t_salary t2 ON t1.employee_id = t2.employee_id
INNER JOIN t_department t3 ON t1.department_id = t3.department_id
-- 5) 显示出所有被扣工资的员工姓名和被扣金额
SELECT t1.`name`,t2.deduct FROM t_employee t1
INNER JOIN t_salary t2 ON t1.employee_id = t2.employee_id
WHERE t2.deduct != 0
-- 6) 按部门升序显示员工的姓名、所在部门和实际工资
SELECT t1.`name`,t2.`name`,(base_salary + pension + allowance + bonus - deduct - tax) FROM t_employee t1
INNER JOIN t_department t2 ON t1.department_id = t2.department_id
INNER JOIN t_salary t3 ON t1.employee_id =t3.employee_id
-- 7) 按部门名升序、实际工资降序显示部门名称、员工编号和实际工资
SELECT t3.`name`,t1.employee_id,(base_salary + pension + allowance + bonus - deduct - tax) FROM t_employee t1
INNER JOIN t_salary t2 ON t1.employee_id = t2.employee_id
INNER JOIN t_department t3 ON t3.department_id = t1.department_id
ORDER BY t3.`name` ASC,(base_salary + pension + allowance + bonus - deduct - tax) DESC
-- 8) 统计出天津籍员工的最高实际工资
SELECT MAX(base_salary + pension + allowance + bonus - deduct - tax) FROM t_salary t1
INNER JOIN t_employee t2 ON t1.employee_id = t2.employee_id
WHERE jiguan = '天津市'
-- 10. 子查询：
-- 1) 显示出工龄最大的员工姓名和工龄
SELECT `name`,YEAR(NOW()) - YEAR(rzsj) FROM t_employee
WHERE YEAR(NOW()) - YEAR(rzsj) = (
SELECT MAX(YEAR(NOW()) - YEAR(rzsj)) FROM t_employee
)
-- 2) 统计出所有北京籍员工的jiben工资总和
SELECT * FROM t_salary
WHERE employee_id =
-- 3) 统计出所有女员工的平均工资
-- 4) 统计出所有天津女党员的最低工资
-- 5) 查询所有市场部员工的基本信息
-- 6) 显示出最小的女员工的姓名和年龄
-- 7) 统计出实际工资最高的北京籍员工的姓名和实际工资
-- 8) 显示出年龄最大的天津女党员
-- 9) 显示出基本工资比天津市最高工资高的所有员工
-- 10) 显示出实际工资比总体平均工资低的女员工
-- 11) 显示出人数最多的部门的所有员工信息
-- 12) 显示出工资最高的员工的姓名，性别，籍贯和所在部门名称
-- 13) 显示出比天津市入职最晚的员工入职还晚的员工
-- 14) 显示出比‘刘新’工资高的所有员工姓名和实际工资
-- 15) 显示出奖金和“李艺”一样多的员工姓名和所在部门名称
-- 16) 给所有市场部的员工增加200元的基本工资
-- 17) 所有非天津籍员工的补助增加100元作为思乡补助
-- 18) 删除所有上海籍员工的基本信息（注意删除的次序）
-- 19) 辞退扣除最多的员工
--本文作者：刘广法，转载注明出处。
