• 学习目标
• 基础练习
• 项目练习
• 参考答案

数据库实战练习

学习目标

• 掌握一般的同学：先完成[基础练习]，再进入[项目练习]
• 掌握较好的同学：直接前往[项目练习]

1. 基础练习

根据以下表结构和数据编写SQL：

1. 查询emp表中全部员工信息
2. 查询emp表中的[员工姓名]、[工资]、[奖金]
3. 查询emp表中的[员工编号]、[员工姓名]、[入职时间]，并给每一列起中文别名显示
4. 查询emp表中第3条开始，查询2条数据
5. 查询emp表中的全部数据，根据[入职时间]降序排序
6. 查询emp表中工资最低的10个员工数据
7. 查询emp表中，工资大于6000的[员工编号]、[姓名]、[工资]、[入职时间]
8. 查询emp表中，职位为'后端开发'的员工信息
9. 查询emp表中，直系上级领导编号不是“2”的员工信息
10. 查询emp表中，工资范围在7000~10000之间的员工编号、姓名、工资
11. 查询emp表中，职位包含'测试'关键词，并且工资小于等于8000的员工信息，按照员工编号降序排列
12. 查询emp表中，工资大于6000，并且奖金不为空的员工信息
13. 查询emp表中，职位是'测试经理'或者职位是'产品经理'的员工信息
14. 查询emp表中，员工编号是2、4、5的员工信息
15. 查询emp表中，职位不是'前端开发'的员工信息
16. 查询emp表中奖金为null的员工信息
17. 查询emp表中有奖金的员工信息，且奖金不为0
18. 查询emp表中，员工姓名是‘黑’结尾的员工信息
19. 查询emp表中，员工姓名包含‘红’字的员工信息
20. 查询emp表中，员工姓名是两个字的员工信息
21. 在emp表中，统计员工总人数
22. 在emp表中，统计员工平均工资
23. 在emp表中，统计入职最早的[员工姓名]
24. 在emp表中，统计所有员工奖金总和
25. 在emp表中，统计每个部门对应的员工人数
26. 在emp表中，统计每个部门的员工人数，以及该部门的平均工资
27. 在emp表中，统计工资大于8000的员工，分布在哪些部门，且该部门的最高工资是多少
28. 在emp表中，统计人数大于2人的部门编号，并根据人数倒序排列
29. 查询emp表中，工资大于平均工资的员工信息
30. 查询emp表中，工资大于1号部门最高工资的员工信息
31. 查询emp表中，与姓名叫'小黑'是同一个部门的其他员工信息
32. 查询emp表中，与姓名叫'小黑'不是同一个部门的员工信息
33. 查询部门所在地区是'上海'的员工信息
34. 查询emp表中，员工姓名以及姓名长度
35. 查询emp表中，员工的姓名以及姓名的最后一个字符
36. 查询dept表中，查询[部门名称]和[地址]的字符拼接内容
37. 查询emp表中，入职时间是去年的员工信息
38. 查询emp表中，[员工编号]、[员工姓名]、[职位]，以及dept表中对应的[部门名称]、[部门地址]
39. 查询所在地区在“上海”的员工有哪些，并根据工资升序排列
40. 查询emp表中，[员工姓名]、[工资]、以及salgrade表中对应的[工资等级]，且取别名
41. 查询emp表中，[员工编号]、[员工姓名]、[职位]、[上级领导编号]、[上级领导姓名]

2. 项目练习

根据原型需求，以及表结构，编写校验数据准确性的SQL：

1. 统计昨日收益
2. 统计累计收益
3. 统计近7日折线图节点
4. 统计单篇作品收益信息

参考答案：

① 基础练习：

                    
-- 1查询emp表中全部员工信息
select * from emp;

-- 2查询emp表中的[员工姓名]、[工资]、[奖金]
select ename, sal, comm from emp;

-- 3查询emp表中的[员工编号]、[员工姓名]、[入职时间]，并给每一列起中文别名显示
SELECT empno '员工编号',ename '员工姓名',hiredate '入职时间' from emp;

-- 4查询emp表中第3条开始，查询2条数据
select * from limit 2,2;

-- 5查询emp表中的全部数据，根据[入职时间]降序排序
select * from emp order by hiredate desc;

-- 6查询emp表中工资最低的10个员工数据
select * from emp order by sal asc limit 10;

-- 7查询emp表中，工资大于6000的[员工编号]、[姓名]、[工资]、[入职时间]
select empno,ename,sal,hiredate from emp where sal>6000;

-- 8查询emp表中，职位为'后端开发'的员工信息
SELECT * from emp where job = '后端开发';

-- 9查询emp表中，直系上级领导编号不是“2”的员工信息
SELECT * from emp where mgr != 2 or mgr is null;
SELECT * from emp where mgr <> 2 or mgr is null;
SELECT * from emp where not mgr = 2 or mgr is null;

-- 10查询emp表中，工资范围在7000~10000之间的员工编号、姓名、工资
SELECT * from emp where sal between 7000 and 10000;
SELECT * from emp where sal >=7000 and sal <=10000;

-- 11查询emp表中，职位包含'测试'关键词，并且工资小于等于8000的员工信息，按照员工编号降序排列
SELECT * from emp where job like '%测试%' and sal <= 8000 ORDER BY empno desc;

-- 12查询emp表中，工资大于6000，并且奖金不为空的员工信息
SELECT * from emp where sal >6000 and comm is not null;
SELECT * from emp where sal >6000 and not comm is null;
SELECT * from emp where sal >6000 and comm > 0;

-- 13查询emp表中，职位是'测试经理'或者职位是'产品经理'的员工信息
SELECT * from emp where job = '测试经理' or job = '产品经理';

-- 14查询emp表中，员工编号是2、4、5的员工信息
SELECT * from emp where empno in (2,4,5);

-- 15查询emp表中，职位不是'前端开发'的员工信息
select * FROM emp where job !='前端开发';

-- 16查询emp表中奖金为null的员工信息
SELECT * from emp where comm is null;

-- 17查询emp表中有奖金的员工信息，且奖金不为0
SELECT * from emp where comm > 0;
SELECT * from emp where comm is not null and comm != 0;

-- 18查询emp表中，员工姓名是‘黑’结尾的员工信息
SELECT * from emp where ename like '%黑';

-- 19查询emp表中，员工姓名包含‘红’字的员工信息
select * FROM emp where ename like '%红%';

-- 20查询emp表中，员工姓名是两个字的员工信息
SELECT * from emp where ename like '__';

-- 21在emp表中，统计员工总人数
SELECT COUNT(*) from emp;

-- 22在emp表中，统计员工平均工资
SELECT avg(sal) FROM emp;

-- 23在emp表中，统计入职最早的[员工姓名]
SELECT ename from emp ORDER BY hiredate LIMIT 1;

-- 24在emp表中，统计所有员工奖金总和
SELECT sum(comm) from emp;

-- 25在emp表中，统计每个部门对应的员工人数
SELECT deptno,count(*) from emp GROUP BY deptno;

-- 26在emp表中，统计每个部门的员工人数，以及该部门的平均工资
SELECT deptno,count(*),avg(sal) from emp GROUP BY deptno;

-- 27在emp表中，统计工资大于8000的员工，分布在哪些部门，且该部门的最高工资是多少
select deptno, max(sal) from emp WHERE sal > 8000 GROUP BY daptno;

-- 28在emp表中，统计人数大于2人的部门编号，并根据人数倒序排列
SELECT deptno from emp GROUP BY deptno HAVING count(*)>2 ORDER BY COUNT(*) desc;

-- 29查询emp表中，工资大于平均工资的员工信息
SELECT * from emp where sal > (SELECT avg(sal) from emp);

-- 30查询emp表中，工资大于1号部门最高工资的员工信息
select * from emp where sal > (select max(sal) from emp where deptno = 1);

-- 31查询emp表中，与姓名叫'小黑'是同一个部门的其他员工信息
select * from emp where deptno = (select deptno FROM emp where ename = '小黑') and ename != '小黑';

-- 32查询emp表中，与姓名叫'小黑'不是同一个部门的员工信息
select * from emp where deptno != (select deptno FROM emp where ename = '小黑');

-- 33查询部门所在地区是'上海'的员工信息
SELECT e.* FROM emp e
INNER JOIN dept d
on e.deptno = d.deptno
where d.loc = '上海';

-- 34查询emp表中，员工姓名以及姓名长度
SELECT ename, CHAR_LENGTH(ename) from emp;

-- 35查询emp表中，员工的姓名以及姓名的最后一个字符
SELECT ename,SUBSTR(ename,-1,1) from emp;

-- 36查询dept表中，查询[部门名称]和[地址]的字符拼接内容
select CONCAT(dname,loc) from dept;

-- 37查询emp表中，入职时间是去年的员工信息
SELECT * from emp where year(hiredate) = year(now())-1;

-- 38查询emp表中，[员工编号]、[员工姓名]、[职位]，以及dept表中对应的[部门名称]、[部门地址]
SELECT e.empno, e.ename, e.job, d.dname, d.loc from emp e
LEFT JOIN dept d
on e.deptno = d.deptno;

-- 39查询所在地区在“上海”的员工有哪些，并根据工资升序排列
SELECT e.* from emp e
LEFT JOIN dept d
on e.deptno = d.deptno
where d.loc = '上海'
ORDER BY e.sal asc;

-- 40查询emp表中，[员工姓名]、[工资]、以及salgrade表中对应的[工资等级]，且取别名
SELECT e.ename '员工姓名', e.sal '工资', s.grade '工资等级'
from emp e
left JOIN salgrade s
on e.sal between s.losal and s.upsal;

-- 41查询emp表中，[员工编号]、[员工姓名]、[职位]、[上级领导编号]、[上级领导姓名]
select e1.empno, e1.ename, e1.job e2.empno, e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;
                    
                

② 项目练习：

                    
-- 昨日收益
SELECT SUM(ps.day_money_amount)
FROM profit_statistics ps
WHERE ps.user_id = 1
and is_del = 0
and date(ps.statistics_time) = CURRENT_DATE();

-- 累计收益
SELECT SUM(ps.day_money_amount)
FROM profit_statistics ps
WHERE ps.user_id = 1
and is_del = 0;

-- 7天折线图节点
SELECT SUM(ps.day_money_amount), date(subdate(ps.statistics_time, interval 1 day)
FROM profit_statistics ps
WHERE ps.is_del = 0
and user_id = 1
and ps.task_type = 1
and date(ps.statistics_time) >= subdate(CURDATE(), interval 6 day)
GROUP BY date(ps.statistics_time);

-- 作品收益
SELECT td.money_amount '金额', td.finish_date '完成时间',
CASE t.task_type
	WHEN 1 THEN '直播'
	WHEN 2 THEN '视频'
	WHEN 3 THEN '图文'
	ELSE
		'其他'
END '类型',
CASE t.task_type
	WHEN 1 THEN ld.live_title
	WHEN 2 THEN vd.video_title
	WHEN 3 THEN ad.article_title
	ELSE
		'其他'
END '标题'
FROM task_details td
LEFT JOIN task t
on td.task_id = t.task_id
LEFT JOIN live_details ld
on ld.live_details_id = td.work_id and t.task_type = 1
LEFT JOIN video_details vd
on vd.video_id = td.work_id and t.task_type = 2
LEFT JOIN article_details ad
on ad.author_id = td.work_id and t.task_type = 3
WHERE td.is_del = 0
and td.create_user = 1;