【MYSQL】查询例题练习笔记

inganxu

已于 2022-02-26 09:12:22 修改

阅读量286

点赞数

分类专栏：数据库体系文章标签：经验分享 mysql 数据库

于 2022-02-22 16:58:03 首次发布

本文链接：https://blog.csdn.net/weixin_46159679/article/details/123054832

版权

数据库体系专栏收录该内容

2 篇文章 0 订阅

订阅专栏

第一部分：MYSQL50道经典例题

摘选50道经典例题中的一部分例题

数据源

create table student(
sid varchar(10) comment '学生编号',
sname nvarchar(10) comment '学生姓名',
sage datetime comment '出日生日',
ssex nvarchar(10) comment '学生性别',
primary key(sid)
) comment '学生表';

insert into student values ('01' , ' 赵雷 ' , '1990-01-01' , ' 男 ');
insert into student values ('02' , ' 钱电 ' , '1990-12-21' , ' 男 ');
insert into student values ('03' , ' 孙风 ' , '1990-05-20' , ' 男 ');
insert into student values ('04' , ' 李云 ' , '1990-08-06' , ' 男 ');
insert into student values ('05' , ' 周梅 ' , '1991-12-01' , ' 女 ');
insert into student values ('06' , ' 吴兰 ' , '1992-03-01' , ' 女 ');
insert into student values ('07' , ' 郑竹 ' , '1989-07-01' , ' 女 ');
insert into student values ('08' , ' 王菊 ' , '1990-01-20' , ' 女 ');


create table courese(
cid varchar(10) comment '课程编号',
cname nvarchar(10) comment '课程名称',
tid varchar(10) comment '教师编号',
primary key(cid)
) comment '课程表';

insert into courese values ('01' , ' 语文 ' , '02');
insert into courese values ('02' , ' 数学 ' , '01');
insert into courese values ('03' , ' 英语 ' , '03');


create table score(
sid varchar(10) comment '学生编号',
cid varchar(10) comment '课程编号',
scoreore decimal(18,1) comment '分数',
primary key(sid,cid)
) comment '成绩表';

insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);


create table teacher(
tid varchar(10) comment '教师编号',
tname nvarchar(10) comment '教师姓名',
primary key(tid)
) comment '成绩表';

insert into teacher values ('01' , ' 张三 ');
insert into teacher values ('02' , ' 李四 ');
insert into teacher values ('03' , ' 王五 ');

例题

查询 "01" 课程比 "02" 课程成绩高的学生的信息及课程分数

SELECT s.sid,s.sname,a_score 
FROM
	(
	SELECT a.sid,a.score a_score 
	FROM score a
	INNER JOIN score b ON a.sid = b.sid AND a.cid = '01' AND b.cid = '02' 
	WHERE a.score > b.score 
	) t
LEFT JOIN student s ON t.sid = s.sid

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select s1.sid,sname,avg(score) as avg_score
from student s1
left join score s2 on s1.sid = s2.sid
group by s1.sid,sname
having avg_score > 60

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s1.sid,sname,count(cid) as cnt_cid,sum(score) as sum_score
from student s1
left join score s2 on s1.sid = s2.sid
group by s1.sid,sname

查询 "李" 姓老师的数量

select count(tname) as cnt_tname from teacher where tname like '李%'

查询没学过 "张三" 老师授课的同学的信息

SELECT sid,sname 
FROM student 
WHERE
	sid NOT IN (
			SELECT sid 
			FROM score 
			WHERE cid IN ( 
							SELECT cid 
							FROM courese 
							WHERE tid = ( 
											SELECT tid 
											FROM teacher 
											WHERE tname = '张三' 
											) 
											) 
	)

查询学过编号为 "01" 并且也学过编号为 "02" 的课程的同学的信息

select t.sid,sname
from (select a.sid,a.cid from score a where cid = '01'
			union all
			select a.sid,a.cid from score a where cid = '02'
			)t
left join student s on s.sid = t.sid
group by t.sid,sname
having count(cid) > 1

查询学过编号为 "01" 但是没有学过编号为 "02" 的课程的同学的信息

select distinct s1.sid,sname
from score s1
left join student s2 on s1.sid = s2.sid
where s1.sid not in (select distinct a.sid from score a where cid = '02')

查询没有学全所有课程的同学的信息

select sid,sname from student where sid in (select sid from score group by sid having count(cid) <3)

查询至少有一门课与学号为 "01" 的同学所学相同的同学的信息

select sid,sname
from student
where sid in (select distinct sid from score where cid in (select cid from score where sid = '01'))

查询和 "01" 号的同学学习的课程完全相同的其他同学的信息

select sid,sname
from student
where sid in (select sid from score group by sid having count(cid) = (select count(cid) from score where sid = '01'))

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s1.sid,sname,avg(score) as avg_score
from score s1
left join student s2 on s1.sid = s2.sid
where s1.sid in (select sid from score where score < 60 group by sid having count(sid) >= 2)
group by sid,sname

检索 "01" 课程分数小于 60，按分数降序排列的学生信息

select sid,score
from score s1
left join courese c on s1.cid = c.cid
where c.tid = '01'
group by sid,score
having score < 60
order by score desc

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select sid,cid,score,avg(score) over (partition by sid order by score desc)
from score

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；及格为 >=60，中等为：70-80，优良为：80-90，优秀为：>=90

select
cid,
max(score) as max_score,
min(score) as min_score,
round(avg(score),2) as avg_score,
concat(round(sum(score >= 90)/count(score)*100,2),'%') as '优秀',
concat(round(sum(score >= 80 and score < 90)/count(score)*100,2),'%') as '优良',
concat(round(sum(score >= 70 and score < 80)/count(score)*100,2),'%') as '中等',
concat(round(sum(score < 70)/count(score)*100,2),'%') as '及格'
from score
group by cid

按各科成绩进行排序，并显示排名

SELECT
	cid,
	sid,
	score,
	row_number ( ) over ( PARTITION BY cid ORDER BY score DESC ) 
FROM
	score

查询学生的总成绩并进行排名

select sid,sum_score,rank() over (order by sum_score desc)
from(select sid,sum(score) as sum_score from score group by sid)t

查询不同老师所教不同课程平均分从高到低显示

select distinct tname,t.cid,avg(score) over (partition by t.tname,t.cid) as avg_score_over
from score s
left join (select cid,tname from courese c left join teacher t on c.tid = t.tid) t on t.cid = s.cid
order by avg_score_over desc

查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩

select tname,s.cid,avg(score) as avg_score
from score s
left join (select cid,tname from courese c left join teacher t on c.tid = t.tid) t on t.cid = s.cid
group by tname,s.cid
order by avg_score desc


select distinct tname,t.cid,avg(score) over (partition by t.tname,t.cid) as avg_score_over
from score s
left join (select cid,tname from courese c left join teacher t on c.tid = t.tid) t on t.cid = s.cid
order by avg_score_over desc

统计各科成绩各分数段人数：课程编号，课程名称，[100-85],[84-70],[69-60],[0-59] 及所占百分比

查询学生平均成绩及其名次

select 
sid,
avg_over_score,
rank() over (order by avg_over_score desc) as rank_over_score
from (select distinct sid,avg(score) over (partition by sid) as avg_over_score from score)t

查询各科成绩前三名的记录

select cid,score,r_over_score
from (select cid,score,row_number() over (partition by cid order by score desc) as r_over_score from score)t
where r_over_score <= 3

查询每门课程被选修的学生数

select count(sid) as cng_sid,cid from score group by cid

查询出只有两门课程的全部学生的学号和姓名

select sid,sname from student where sid in (select sid from score group by sid having count(cid) = 2)

查询男生、女生人数

select ssex,count(ssex) as cnt_ssex from student group by ssex

查询名字中含有 "风" 字的学生信息

select sid,sname from student where sname like '%风%'

查询同名同性学生名单，并统计同名人数

select a.sname,count(*) cnt
from student a
left join student b on a.sname = b.sname and a.ssex = b.ssex
group by a.sname,a.ssex
having cnt > 1

查询 1990 年出生的学生名单 (注：Student 表中 Sage 列的类型是 datetime)

select sid,sname,sage from student group by sid,sname,sage having year(sage) = 1990

查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

select distinct cid,avg(score) over (partition by cid) avg_over from score order by avg_over desc,cid

查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select s1.sid,sname,avg_score
from student s1
inner join (select sid,avg(score) avg_score from score group by sid having avg_score >85) s2 on s1.sid = s2.sid

查询课程名称为 "数学"，且分数低于 60 的学生姓名和分数

select sname,score
from score s1
left join student s2 on s1.sid = s2.sid
left join courese c on s1.cid = c.cid
where cname = '数学' and score < 60

查询所有学生的课程及分数情况

select score.sid,cid,score from score left join student on student.sid = score.sid

查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select sname,cid,score from score s1 left join student s2 on s1.sid = s2.sid where score > 70

查询不及格的课程

select * from score where score < 60

查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

select s1.sid,sname from score s1 left join student s2 on s1.sid = s2.sid where cid = '01' and score >80

求每门课程的学生人数

select count(*) from score group by cid

查询选修 "张三" 老师所授课程的学生中，成绩最高的学生信息及其成绩

select s2.sid,s2.sname,score
from score s1
left join student s2 on s1.sid = s2.sid
where cid in (select cid from courese c where tid in (select tid from teacher where tname = '张三'))
order by score desc
limit 1

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select sid,cid,score from score where sid in (select sid from score group by sid,score having count(*) > 1)

查询每门课程成绩最好的前两名同学

select * from (select cid,score,row_number() over (partition by cid order by score desc) r_over from score)t where r_over < 3

统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cid,count(*) cnt from score group by cid order by cnt desc,cid

检索至少选修两门课程的学生学号

select sid from score group by sid having count(*) > 1

查询选修了全部课程的学生信息

select s1.sid,sname
from score s1
left join student s2 on s1.sid = s2.sid
group by s1.sid,sname
having count(s1.sid) = (select count(*) from courese)

查询各学生的年龄

select sid,sname,timestampdiff(year,sage,now()) age from student

查询本周过生日的学生

select sid,sname from student where yearweek(sage) = yearweek(now())

查询下月过生日的学生

select sid,sname from student where month(now()) - month(now()) = 1

第二部分：B站老杜34道查询例题

链接：https://pan.baidu.com/s/1a0z519Zz0NaJR7kgbnRpRw
提取码：Java

数据源

use test;
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

例题

1、取得每个部门最高薪水的人员名称
select ename,deptno,max(sal) as max_sal
from emp
group by deptno


2、哪些人的薪水在部门的平均薪水之上
select e.ename,sal,e.deptno
from emp as e
left join (select avg(sal) as avg_sal,ename,deptno
from emp
group by deptno)t1
on e.deptno = t1.deptno
where sal > avg_sal


3、取得部门中（所有人的）平均的薪水等级
select deptno,avg(grade) as avg_grade
from (select deptno,grade
from emp
left join salgrade
on sal between losal and hisal)t1
group by deptno


4、不准用组函数（Max ），取得最高薪水
select sal
from emp
order by sal desc
limit 1


5、取得平均薪水最高的部门的部门编号
第一种方案：降序取第一个。
select deptno,avg(sal) as avg_sal
from emp
group by deptno
order by deptno
limit 1


第二种方案：max
select deptno,avg(sal) as avg_sal
from emp
group by deptno
having avg_sal = (select max(t1.avg_sal) from (select avg(sal) as avg_sal from emp group by deptno) as t1)
	

6、取得平均薪水最高的部门的部门名称
select dname,avg(sal) as avg_sal
from emp as e
left join dept as d
on e.deptno = d.deptno
group by e.deptno
order by avg_sal desc
limit 1


7、求平均薪水的等级最低的部门的部门名称
select dname,avg(sal) as avg_sal
from emp as e
left join dept as d
on e.deptno = d.deptno
group by e.deptno
order by avg_sal
limit 1


8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名
select ename
from emp
where mgr is null


9、取得薪水最高的前五名员工
select ename,sal
from emp
order by sal desc
limit 5


10、取得薪水最高的第六到第十名员工
select ename,sal
from emp
order by sal desc
limit 5,5


11、取得最后入职的 5 名员工
select ename,hiredate
from emp
order by hiredate desc
limit 5


12、取得每个薪水等级有多少员工
select grade,count(grade)
from emp e
left join salgrade s
on e.sal between losal and hisal
group by grade
order by grade


13、面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。


14、列出所有员工及领导的姓名
select e.ename,t1.ename as leader
from emp e
left join (select ename,empno from emp)t1
on e.mgr = t1.empno


15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select e.ename,e.hiredate,t1.leader,t1.l_hiredate,d.dname
from emp e
left join dept d
on e.deptno = d.deptno
left join (
	select e1.ename as leader,e2.empno,e1.hiredate as l_hiredate
	from emp e2
	left join (select empno,ename,hiredate
	from emp)e1
	on e2.mgr = e1.empno where e1.ename is not null
) t1
on e.empno = t1.empno
where e.hiredate < t1.l_hiredate


16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门
select *,d.dname
from emp e
right join dept d
on e.deptno = d.deptno


17、列出至少有 5 个员工的所有部门
select cnt_empno,d1.deptno
from dept d1
left join (select count(empno) as cnt_empno,dname
from emp e
inner join dept d
on e.deptno = d.deptno
group by dname
having cnt_empno > 4)t1
on d1.dname = t1.dname
where cnt_empno is not null


18、列出薪金比"SMITH" 多的所有员工信息
select ename,sal
from emp e
where sal > (select sal from emp where ename = 'SMITH')


19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数
select ename,job,dname,count(dname) over (partition by dname) as c_o_dname
from emp
left join dept on emp.deptno = dept.deptno
where job = 'CLERK'



20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值。
select count(job),job
from(select ename,job,s.*
from emp e
left join salgrade s
on sal between losal and hisal
where losal > 1500)t1
group by job


21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号
select ename,dname
from emp e
left join dept d on e.deptno = d.deptno
where dname = 'SALES'


22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.
select e.ename,dname,t1.ename as leader,grade
from emp e
left join dept d on e.deptno = d.deptno
left join (
	SELECT
		e1.empno,e2.ename 
	FROM
		emp e1
		LEFT JOIN ( SELECT ename, empno FROM emp ) e2 ON e1.mgr = e2.empno
) t1 on e.empno = t1.empno
left join salgrade s on e.sal between s.losal and hisal
where sal > (select avg(sal)_gross_avg_sal from emp)


23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称
select ename,dname
from emp e
left join dept d on e.deptno = d.deptno
where job = (select job from emp where ename = 'SCOTT') and ename <> 'SCOTT'


24、列出薪金等于（部门编号为30 的员工的薪金）的其他员工的姓名和薪金.
select ename,sal,deptno
from emp
where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30


26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
select 
count(empno),
dname,
ifnull(avg(sal),0),
ifnull(avg(timestampdiff(year,hiredate,now())),0)
from emp e
right join dept d on e.deptno = d.deptno
group by dname


27、 列出所有员工的姓名、部门名称和工资。
select ename,dname,sal
from emp e
left join dept d on e.deptno = d.deptno


28、列出所有部门的详细信息和人数
select d.deptno,dname,loc,count(ename)
from emp e
right join dept d on e.deptno = d.deptno
group by d.deptno


29、列出各种工作的最低工资及从事此工作的雇员姓名
select ename,e.sal,e.job
from emp e
inner join (select job,min(sal) sal
from emp
group by job)t1
on e.job = t1.job and e.sal = t1.sal


30、列出各个部门的 MANAGER( 领导) 的最低薪金
select deptno,ename,sal
from emp e
where job = 'MANAGER'


31、列出所有员工的 年工资, 按 年薪从低到高排序
select ename,sal*12 as year_sal
from emp
order by year_sal


32、求出员工领导的薪水超过3000的员工名称与领导
select e1.ename,e2.ename as leader
from emp e1
left join emp e2 on e1.mgr = e2.empno
where e2.sal > 3000


33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
select d.deptno,dname,loc,ifnull(sum(sal),0),ifnull(count(empno),0)
from emp e
right join dept d on e.deptno = d.deptno
where dname like '%S%'
group by deptno


34、给任职日期超过 30 年的员工加薪 10%.
select ename,empno,sal*1.1 new_sal
from emp
where timestampdiff(year,hiredate,now()) > 30

后续遇到系统性的例题也会放上去