MySQL基础练习题：习题12-15

这部分主要是为了帮助大家回忆回忆MySQL的基本语法，数据库来自于MySQL的官方简化版，题目也是网上非常流行的35题。这些基础习题基本可以涵盖面试中需要现场写SQL的问题。

取得每个薪水等级有多少员工

思路

• 使用JOIN语句将emp表（别名为t）和salgrade表（别名为s）连接起来。而连接条件是emp表中的员工薪水（t.sal）位于salgrade表中定义的薪水范围之间。这个范围由每个工资等级的最低薪水（s.losal）和最高薪水（s.hisal）定义。
• 使用GROUP BY语句将结果按照工资等级（s.grade）进行分组。并使用COUNT(t.ename)函数计算每个工资等级下员工的数量，并将结果别名为count。

Solution

mysql> select s.grade,count(t.ename) as count from emp t join salgrade s on t.sal between s.losal and s.hisal group by s.grade;
+-------+-------+
| grade | count |
+-------+-------+
|     1 |     3 |
|     3 |     2 |
|     2 |     3 |
|     4 |     5 |
|     5 |     1 |
+-------+-------+

面试题

这是之前面试字节跳动被问到现场手写SQL题

三张表：

1. 学生表S：学号SNO，姓名SNAME
2. 课程表C：课号CNO，课程名CNAME，课程老师CTEACHER
3. 选课表SC：学号SNO，课号CNO，分数SCGRADE

第一问，显示没选”黎明“老师课的学生

获取”黎明“老师的课号

select cno from c where cteacher='黎明';

获取没选其课学生的学号

select distinct sno from sc where sno not in (select cno from c where cteacher='黎明');

没选课学生的姓名

select sname from s where sno in (select distinct sno from sc where sno not in (select cno from c where cteacher='黎明'));

---

第二问，列出2门以上（含2门）不及格学生姓名及平均成绩

获取2门及以上不及格学生的学号

select sno from sc where scgrade<60 group by sno having count(*)>=2;

获取不及格学生姓名

select sname,sno from s where sno in (select sno from sc where scgrade<60 group by sno having count(*)>=2);

计算所有学生的平均成绩

select sno,avg(scgrade) avggrade from sc group by sno;

联合姓名和平均成绩

select a.sno,a.sname,b.avggrade from (select sname,sno from s where sno in (select sno from sc where scgrade<60 group by sno having count(*)>=2)) a join (select sno,avg(scgrade) avggrade from sc group by sno) b on a.sno=b.sno;

---

第三问，学过1号课程和2号课程的所有学生的姓名

选过1号课程和2号课程的学生学号

select distinct sno from sc where cno=1 or cno=2;

从s表中取出其姓名

select sno,sname from s where sno in (select distinct sno from sc where cno=1 or cno=2);

最后补充一下可供练习的sql文件

链接：https://pan.baidu.com/s/1otmZlfrkKh6dlf4Hc0Wa9w?pwd=aigo
提取码：aigo

列出所有员工及领导的姓名

这也是一道普普通通的自己和自己join联查，不过要注意的是，因为最高领导的领导是NULL， 因此需要使用left join

mysql> select a.ename as empname,b.ename as leadname from emp a left join emp b on a.mgr=b.empno;
+---------+----------+
| empname | leadname |
+---------+----------+
| SIMITH  | FORD     |
| ALLEN   | BLAKE    |
| WARD    | BLAKE    |
| JONES   | KING     |
| MARTIN  | BLAKE    |
| BLAKE   | KING     |
| CLARK   | KING     |
| SCOTT   | JONES    |
| KING    | NULL     |
| TURNER  | BLAKE    |
| ADAMS   | SCOTT    |
| JAMES   | BLAKE    |
| FORD    | JONES    |
| MILLER  | CLARK    |
+---------+----------+

JOIN 和 LEFT JOIN 是 SQL 中用于连接多个表的关键字，它们之间的主要区别在于如何处理连接条件不满足的情况。

1. JOIN（内连接）：

• JOIN 或者 INNER JOIN 返回两个表中在连接条件下匹配的行。如果连接条件不满足，则不会返回该行。
• 这意味着只有当连接条件在两个表中都能找到匹配的行时，才会返回结果。

2. LEFT JOIN（左连接）：

• LEFT JOIN 返回左边表（在 LEFT JOIN 关键字之前的表）中的所有行，无论连接条件是否满足。
• 如果右边表中找不到匹配的行，则会在结果中用 NULL 填充右边表的所>有列。
• 即使左边表中的某些行在右边表中没有匹配的行，它们也会包含在结果中。

总的来说，JOIN 返回的结果集包含了两个表中连接条件匹配的行，而 LE>FT JOIN 则返回左边表中的所有行，即使在右边表中找不到匹配的行也会返回。

列出受雇日期早于其上级的所有员工的编号、姓名、部门名称

mysql> select e.empno,e.ename,e.hiredate,l.ename,l.hiredate from emp e join emp l on e.mgr=l.empno and e.hiredate<l.hiredate;
+-------+--------+------------+-------+------------+
| empno | ename  | hiredate   | ename | hiredate   |
+-------+--------+------------+-------+------------+
|  7369 | SIMITH | 1980-12-17 | FORD  | 1981-12-03 |
|  7499 | ALLEN  | 1981-02-20 | BLAKE | 1981-05-01 |
|  7521 | WARD   | 1981-02-22 | BLAKE | 1981-05-01 |
|  7566 | JONES  | 1981-04-02 | KING  | 1981-11-17 |
|  7698 | BLAKE  | 1981-05-01 | KING  | 1981-11-17 |
|  7782 | CLARK  | 1981-06-09 | KING  | 1981-11-17 |
+-------+--------+------------+-------+------------+