mysql 百度网盘_SQL语言【基于MySQL】【面试刷题必备】

最新推荐文章于 2024-09-07 16:27:19 发布
最新推荐文章于 2024-09-07 16:27:19 发布
阅读量301 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39581972/article/details/111691925
版权

855f1801e40493ab85019ef8ae40be9f.png

——这个比较适合入过门、略懂基本操作的小伙伴——

各种连接-内连接、左（外）连接、右（外）连接、全（外）连接

事前准备：普通表两份-X表、Y表

X表中字段：x_id 、x_name、x_game

Y表中字段：y_id 、y_name、y_game2

9c6b2440bcf1e804d951e1219b4c1e98.png
X表

76ae58a34a50648d37545d9e9a84631d.png
Y表

内连接：

语法：inner join……on……

例子：select * from X表 (as) a inner join Y表 (as) b on a.x_id =b.y_id

*()里表示可以省略

结果：

d86b26f9bd9833be793e865984f345d1.png

不想要全部列的话可以这个亚子：

select a.*, b.y_game2 from X表 (as) a

inner join Y表 (as) b

on a.x_id =b.y_id

5f46a42e539b60bf47278cbfab0a56d7.png

说明：内连接表示的是取两表之间的交集，即两表都有的数据

左（外）连接

语法：left (outer) join……on……

例子：

select a.* , b.y_game2 from X表 a

left join Y表 b

on a.x_id =b.y_id;

结果：

62ccc09a9f65e482b6c374ae4384c672.png

说明：左连接以左表为准，取左表（指 join 左边）所有值，右表中有符合左表on之后条件的值一起取上，没有则显示Null。

右（外）连接

语法：right (outer) join……on……

例子：

select a.* , b.y_game2 from X表 a

right join Y表 b

on a.x_id =b.y_id;

结果：

433106ee24a21de877d29fc154dfcd84.png

说明：右连接以右表为准，取右表（指 join 右边）所有值，左表中有符合右表on之后条件的值一起取上，没有则显示Null。

上面图表中没有显示出第十条数据的id和name，可以select数据较多的表中的基本字段即可：

select b.* , a.x_game from X表 a

right join Y表 b

on a.x_id =b.y_id;

396dcf96c334b972b7af70bb9abf30fd.png

这亚子就舒服很多了。

利用上述各种连接作为辅助解题

先准备四张表：这四张表我是用EXCEL直接写好导进MySQL的，这亚子比较快，萌新可以去练练怎么在数据库里增加表和在表中增加数据。我这里使用的是Navicat for MySQL，这个比MySQL workbench 好用很多，导入导出数据都比较简单，因为都是中文嘛，哈哈。

小提示：增加数据语法：insert into [表名] values (值,值,值, …… ); 有多少列就增加多少列的值，没有的话就用null，这亚子比较快。

遇到字段有很多的表格，一次增加不了多少数据的话，可以用介个：

insert into [表名] ([列名],[列名],........) values (值,值,值, …… ); 灵活运用，效率至上。

哦对了，四张表（学生表、课程表、教师表、成绩表）：

四表.xlsx
12K
·
百度网盘

bb2d724f1147f2d4a5c63b6e5a77280e.png

60db8c9a6a2619e01986ea07702a837b.png
课程表

f8815a0d33b8772f94150f91b88edc6b.png
教师表

ea8f337c94a87902302831bbcfbd9aa0.png
成绩表

玩表格呢最关键的两点就是单个表格整洁清晰，多个表格相互关联数据不冗余，来，先瞅瞅这四张表，仔细点就可以看出：

学生表中的学号与成绩表中的学号可以相互关联，

成绩表中的课程号与课程表中的课程号可以相互关联，

课程表中的教师号与教师表中的教师号可以相互关联，

于是乎，

学生表.学号=成绩表.学号，成绩表.课程号=课程表.课程号，课程表.教师号=教师表.教师号，四表联立成功。

第一题：查找课程0001成绩大于课程0002的学生学号以及成绩

思路：

898c00007915f5eb7088e1add1b0490f.png

是不是感觉这个样子的表会好取数一点？

那我们就构建这个表，代码来：

SELECT a.`学号`,a.`成绩` "0001",b.`成绩` "0002" FROM 
(SELECT * from `成绩表` WHERE `成绩表`.`课程号`=0001) a
INNER JOIN
(SELECT * from `成绩表` WHERE `成绩表`.`课程号`=0002) b
ON  a.`学号`=b.`学号`
where  a.`成绩` > b.`成绩`;

结果：

3f6e6497e6db4060b80b36d3e62ec5e5.png

来加个名字吧：

SELECT a.`学号`,a.`成绩` "0001",b.`成绩` "0002", c.`姓名`  FROM 
(SELECT * from `成绩表` WHERE `成绩表`.`课程号`=0001) a
INNER JOIN
(SELECT * from `成绩表` WHERE `成绩表`.`课程号`=0002) b
ON a.`学号`=b.`学号`
INNER  JOIN  `学生表`  c  ON  c.`学号`=a.`学号`
where a.`成绩`>b.`成绩`;

结果：

b1e0bea302b2e92327a7a972f2e544ec.png

第二题：查询平均成绩大于60分的学生和学号以及成绩

SELECT `成绩表`.`学号`,AVG(`成绩表`.`成绩`) FROM `成绩表`
GROUP BY `成绩表`.`学号` 
HAVING AVG(`成绩表`.`成绩`)>60;

注意：GROUP BY后面的条件要用 HAVING 来连接哦

结果：

ab036a87aef47729a9212a320c992fdb.png

第三题：查询所有学生的学号、姓名、选课数、总成绩

SELECT a.`学号`, COUNT(b.`学号`), SUM(b.`成绩`)
FROM `学生表` a
LEFT JOIN `成绩表` b 
ON a.`学号`=b.`学号`
GROUP BY `学号`;

说明：这里用左连接的原因是以学生表为准，选课和未选课的学生一定在学生表内，未选某些课的学生在成绩表内没有该课程成绩，因此无法使用inner join(因为这个取的是交集呀，会漏掉没有选课的学生

结果：

805615c5bc79bf276f1385c199844432.png

再来加个姓名吧：

SELECT  a.`学号`, a.`姓名` , COUNT(b.`学号`), SUM(b.`成绩`)
FROM `学生表` a
LEFT JOIN `成绩表` b ON a.`学号`=b.`学号`
GROUP BY a.`学号`, a.`姓名`;

结果：

c0bfcdef98505837cfc1def0ed110458.png

这里表格没有出现有学生没有选课的情况，如果有这种情况咋整呢？这亚子整：

SELECT a.`学号`,a.`姓名`,COUNT(b.`学号`),SUM(CASE WHEN b.`成绩` IS NULL THEN 0 ELSE b.`成绩` END )

FROM `学生表` a

LEFT JOIN `成绩表` b ON a.`学号`=b.`学号`

GROUP BY a.`学号`,a.`姓名`

结果就不展示了，因为没有null值，记住，CASE WHEN 超好用。

第四题：查询姓张老师的个数

SELECT COUNT( `教师号`) FROM `教师表` WHERE `教师名称` LIKE '张%'

结果：

136a3c6b4f3b2ad6ab1168434d0a29d8.png 
SELECT * FROM `教师表` WHERE `教师名称` LIKE '张%';

结果：

2c6516da73c48531cb38035411976a4a.png

第五题：查询没学过张三老师课的学生学号和姓名

思路：先找学过的，再排除掉，找到没学过的。从一开始讲过的四表关系来看，我们要找的学生信息和教师信息两表并没有直接联系，而是通过成绩表和课程表达成潜在联系。所以先找出张三老师教的课程号才行：

SELECT * FROM `课程表` 
WHERE `课程表` .`教师号`=(
SELECT `教师表`.`教师号` FROM `教师表`
WHERE `教师表`.`教师名称`='张三');

结果：

14b0e44e6466023f6f4ff97d663dee5b.png

老师是教0002课程的，然后找没有0002课程成绩的学生就知道是谁没学过张三老师的课啦！

完整代码：

SELECT * FROM `学生表`
WHERE `学号` NOT in (
SELECT `学号` FROM `成绩表`
WHERE `课程号`=(
SELECT `课程号` FROM `课程表` 
WHERE `教师号`=(
SELECT `教师号` FROM `教师表`
WHERE `教师名称`='张三')
));

结果：

8a01953f07c8414d5b73024b46e2edcd.png

嵌套代码容易出错，换个法子：万能的JOIN啊来吧：

SELECT * FROM `学生表`
WHERE `学号` not in (
SELECT `学号` FROM `成绩表` a
INNER JOIN `课程表` b on a.`课程号`=b.`课程号`
INNER JOIN `教师表` c ON b.`教师号`=c.`教师号`
WHERE c.`教师名称`='张三');

结果：

deb3a9fb6503c20d6e5c969aab5fc3ee.png

相同，看起来也很简洁明了，就很奈斯。

第六题：查询学过张三老师教的所有课的学生学号和姓名

完整代码：

SELECT st.`学号`,st.`姓名`,s.`课程号`,c.`课程名称`,t.`教师号`,t.`教师名称`
FROM  `成绩表` as s
INNER JOIN `课程表` as c ON s.`课程号`=c.`课程号`
INNER JOIN `教师表` as t ON c.`教师号`=t.`教师号`
INNER JOIN `学生表` as st ON st.`学号`=s.`学号`
WHERE t.`教师名称`='张三'
GROUP BY st.`学号`;

结果：

b755b1e35e3c9e8bb66628607a8798e5.png

看视频的时候有小伙伴疑问如果出现一个老师教多门课程的情况是否还能使用这串代码，亲测是可以的。

先增加几行数据试试：

INSERT INTO `课程表` VALUES ('0004','德语','0001');

INSERT INTO `成绩表` VALUES ('0500','0004',90),('0510','0004',75);

INSERT INTO `学生表` VALUES ('0500','机器人甲',32510,'男'), ('0510','机器人以',32511,'女');

增加之后再运行上述代码所得结果如下：

42f07b3822a45a95c7a0fe6f2ce4ceae.png

这里用了INNER JOIN 把所有表都联立了，而题目中学过张三老师教的所有课的学生学号和姓名已经表明了潜在意思就是四张表里均有这些人的信息，用INNER JOIN是可以把数据全部提取出来的。

第七题：查询学过编号为01的课程并且也学过编号为02的课程的学生的学号和姓名

完整代码：

SELECT `学号`,`姓名` FROM `学生表`
WHERE `学号` in 
(SELECT a.`学号` FROM
(SELECT `学号` FROM `成绩表` WHERE `课程号`='0001') a
INNER JOIN
(SELECT `学号` FROM `成绩表` WHERE `课程号`='0002') b
ON a.`学号`=b.`学号`);

结果：

db95eed9809fe4a37bc5cff2b06cb7f5.png

逐步分解：

(SELECT `学号` FROM `成绩表` WHERE `课程号`='0001') a

(SELECT `学号` FROM `成绩表` WHERE `课程号`='0002') b

创建包含了0001和0002课程成绩信息的两个临时表，两表INNER JOIN取出交集，即同时学了0001和0002课程的学生学号，学生姓名在学生表中，所以，

SELECT `学号`,`姓名` FROM `学生表`

WHERE `学号` in ……

利用取得的学号从学生表中取出对应学生姓名数据。

第八题：查询课程编号为0002的总成绩

基本代码：

SELECT SUM(`成绩`) ,AVG(`成绩`) ,COUNT(`成绩`),COUNT(DISTINCT `学号`) 
FROM `成绩表`
WHERE `课程号`='0002';

进阶代码：

SELECT `课程号`,SUM(`成绩`) ,AVG(`成绩`) ,COUNT(`成绩`),COUNT(DISTINCT `学号`) 
FROM `成绩表`
GROUP BY `课程号`
HAVING `课程号`='0002';

结果：

9e247fafd54fb05f7dc32928485ab678.png

用GROUP BY 是因为大部分场景是为了得到这个结果：

35ea2859b3710ef4b45d5860c801b816.png

第九题：查询所有课程成绩小于60的学生的学号和姓名

SELECT `学号`,`姓名` FROM `学生表`
WHERE `学号` in (
SELECT `学号` FROM `成绩表`
GROUP BY `学号`
HAVING MAX(`成绩`)<60);

思路：所有课程成绩小于60的，用聚合函数MAX()来表示就是所有成绩里最大得分小于60的即可。这里要注意聚合函数只能跟在HAVING后面。

第十题：查询没有学全所有课程的学生的学号和姓名

增加一个没有选课的人的信息做干扰项。

INSERT INTO `学生表` VALUES ('0600','机器人并','1988/12/4','男');

完整代码：

SELECT st.* ,sc.*
FROM `学生表` as st 
LEFT JOIN `成绩表` as sc 
ON st.`学号`=sc.`学号`
GROUP BY st.`学号`
HAVING COUNT(DISTINCT sc.`学号`)<(SELECT COUNT(DISTINCT `课程号`) FROM `课程表`);

结果：

25541fbe6ad604be37b1787abec71926.png

第十一题：查询至少有一门课与学号为01的学生所学的课程相同的学生的学号和姓名

完整代码：

SELECT `学号`,`姓名` FROM `学生表`
WHERE `学号` in (
	SELECT DISTINCT `学号` FROM `成绩表`
	WHERE `课程号` in (
		SELECT `课程号` FROM `成绩表`
		WHERE `学号`=0001)  AND `学号`!=0001 );

结果：

07df4debfeb4f7674ba51140bb838587.png

第十二题：查询和01号同学所学课程完全相同的其他同学的学号和姓名

SELECT * FROM `学生表`
WHERE `学号` in 
(SELECT `学号`  FROM `成绩表`
WHERE `学号` !=0001
GROUP BY `学号` 
HAVING COUNT(DISTINCT `课程号`)=(SELECT COUNT(DISTINCT `课程号`) 
FROM `成绩表` WHERE `学号` =0001))
and `学号` not in(
SELECT DISTINCT `学号`  FROM `成绩表`
WHERE `课程号` NOT in (
	SELECT `课程号` FROM `成绩表`
	WHERE `学号`=0001));

步骤分解：

and之前：找出非0001号的其他同学与0001号课程数相同的人

and之后：找出课程号不在0001号同学所学的课程里的人，将其排除在外

符合两条件的非0001号就是题目所要求的的人。

第十三题：查询有两门及以上不及格课程的同学的学号、姓名以及平均成绩

完整代码：

SELECT a.`学号`,a.`姓名` ,AVG(`成绩`) FROM `学生表` a
INNER JOIN `成绩表` b
ON a.`学号`=b.`学号`
WHERE a.`学号` in (
SELECT `学号` FROM `成绩表`
WHERE `成绩`<60
GROUP BY `学号`
HAVING COUNT(DISTINCT `课程号`)>=2)
GROUP BY `学号`,`姓名`；

结果：

3e5065aa3bfd2e1ab4d8ce6ccf4d5431.png

思路：

SELECT `学号` FROM `成绩表`

WHERE `成绩`<60

GROUP BY `学号`

HAVING COUNT(DISTINCT `课程号`)>=2

先找出成绩有两门及以上小于60的同学，再INNER JOIN学生表和成绩表提取出学生信息。

第十四题：检索0001课程分数小于60并按降序排序罗列出学生信息

完整代码：

SELECT a.*,b.`课程号`,b.`成绩` FROM `学生表` a 
INNER JOIN `成绩表` b on a.`学号`=b.`学号`
WHERE b.`课程号`=0001 AND b.`成绩`<60
ORDER BY b.`成绩` DESC;

结果：

c75a5b2c4797b1a2ea22c40e9fb112ff.png

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

完整代码：

SELECT `学号`,
MAX(case when `课程号`=0001 THEN `成绩` ELSE NULL END) "语文",
MAX(case when `课程号`=0002 THEN `成绩` ELSE NULL END) "数学",
MAX(case when `课程号`=0003 THEN `成绩` ELSE NULL END) "英语",
MAX(case when `课程号`=0004 THEN `成绩` ELSE NULL END) "德语",
AVG(`成绩`)"平均成绩"
FROM `成绩表`
GROUP BY `学号`
ORDER BY AVG(`成绩`) DESC;

结果：

7b517b7618704f2e3103651941cb8707.png

*利用MAX()聚合函数进行取数。

第十六题：查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率

及格：>=60，中等：70-80，优良：80-90，优秀：>=90

完整代码：

SELECT s.`课程号`,b.`课程名称`,
MAX(s.`成绩`) '最高分',
MIN(s.`成绩`) '最低分',
AVG(s.`成绩`) '平均分',
SUM(case when s.`成绩`>=60 then 1 else 0 end)/COUNT(`学号`) '及格率',
SUM(case when s.`成绩`>=70 AND s.`成绩`<80 then 1 else 0 end)/COUNT(`学号`) '中等率',
SUM(case when s.`成绩`>=80 AND s.`成绩`<90 then 1 else 0 end)/COUNT(`学号`) '优良率',
SUM(case when s.`成绩`>=90 then 1 else 0 end)/COUNT(`学号`) '优秀率'
from `成绩表` s
INNER JOIN `课程表` b ON s.`课程号`=b.`课程号`
GROUP BY `课程号`;

结果：

83317010005b03a2a43df90e1e31afd0.png

第十七题：按各科成绩进行排序，并显示排名（row_number, rank, dense_rank）

e859a5eb0837ebcaa2a639c62dc52afc.png
B站UP主的一个视频截图

rownumber() 可替换成rank()、dense_rank()

SELECT *,row_number() over(PARTITION BY `课程号` ORDER BY `成绩` DESC) AS 'rank' FROM `成绩表`;

14679b504f1fdd884319ed5dc778bcb0.png

第十八题：查询学生的总成绩并进行排名

代码：

SELECT a.`学号`,
b.`姓名`,
SUM(`成绩`) '总成绩',
rank() over(ORDER BY SUM(`成绩`) DESC)'排名' 
FROM  `成绩表`   a
INNER JOIN   `学生表` b       
ON a.`学号`=b.`学号` 
GROUP BY a.`学号`,b.`姓名`;

结果：

11fc9401636e7bbe1423678e5b466754.png

第十九题：查询不同老师所教不同课程平均分从高到低显示

代码：

SELECT z.`教师号`,z.`教师名称`,y.`课程号`,y.`课程名称`,avg(`成绩`)
FROM `成绩表` x
INNER JOIN `课程表` y ON x.`课程号`=y.`课程号`
INNER JOIN `教师表` z ON y.`教师号`=z.`教师号`
GROUP BY z.`教师号`,z.`教师名称`,y.`课程号`
ORDER BY avg(`成绩`) DESC;

结果：

9a10490ca8ff2dbad6aaed2e4043ddce.png

第二十题：查询所有课程的成绩第二名到第三名的学生信息及该课程成绩

代码：

SELECT * FROM
(SELECT s.`学号`,d.`姓名`,s.`成绩`,s.`课程号`,row_number()over(PARTITION BY s.`课程号` ORDER BY s.`成绩` DESC) n
FROM `成绩表` s 
INNER JOIN `学生表` d ON s.`学号`=d.`学号`)a
WHERE n in (2,3);

结果：

ba2c8b11d68f7ba321b59b6b55bb86d3.png

分析：这里有小伙伴会问为什么加一个临时表a，因为要取生成的窗口函数里的值，直接加where条件是取不出来的，聚合函数也是如此，必须先生成一个临时表才可以取出对应范围的值。

第二十一题：使用分段来统计各科成绩，分别统计各分段人数

代码：

SELECT h.`课程号`,h.`课程名称`
,SUM(CASE WHEN g.`成绩`>85 and g.`成绩`<=100 THEN 1 ELSE 0 END) "(85,100]"
,SUM(CASE WHEN g.`成绩`>70 and g.`成绩`<=85 THEN 1 ELSE 0 END) "(70,85]"
,SUM(CASE WHEN g.`成绩`>60 and g.`成绩`<=70 THEN 1 ELSE 0 END) "(60,70]"
,SUM(CASE WHEN g.`成绩`<=60 THEN 1 ELSE 0 END) "[0,60]"
FROM `成绩表` g 
INNER JOIN `课程表` h ON g.`课程号`=h.`课程号`
GROUP BY h.`课程号`,h.`课程名称`;

结果：

18a7699a8b12f5203cace28db7d3a697.png

分析：这里的

SUM(CASE WHEN g.`成绩`>85 and g.`成绩`<=100 THEN 1 ELSE 0 END) "(85,100]"

sum()也可以用count()如下：

COUNT(CASE WHEN g.`成绩`>85 and g.`成绩`<=100 THEN 1 ELSE NULL END) "(85,100]"

注意，ELSE后面需要使用NULL值，计数count()和求和sum()是不一样的，使用NULL值防止函数将不适合的项计算进去。

第二十二题：查询学生平均成绩及其名次

代码：

SELECT k.`学号`,l.`姓名`,AVG(`成绩`),row_number()over( ORDER BY AVG(`成绩`) DESC)
FROM `成绩表` k 
INNER JOIN `学生表` l ON k.`学号`=l.`学号`
GROUP BY k.`学号`;

结果：

57d0051d87c3283e4d6da1bd6884f0e1.png

第二十三题：查询每门课程被选修的学生人数

代码：

SELECT t.`课程号`,y.`课程名称`,COUNT(t.`学号`)
FROM `成绩表` t
INNER JOIN `课程表` y on t.`课程号`=y.`课程号`
GROUP BY t.`课程号`,y.`课程名称`
ORDER BY `课程号`;

结果：

668969c46f54d002cd1be9fc9f3a87d0.png

第二十四题：查询只学了两门课程的学生的学号和姓名

代码：

SELECT u.`学号`,i.`姓名`
FROM `成绩表` u
INNER JOIN `学生表` i ON u.`学号`=i.`学号`
GROUP BY u.`学号`,i.`姓名`
HAVING COUNT(DISTINCT u.`课程号`)=2

结果：

967c02bac207eca8226b1ef19b6297fe.png

第二十五题：查询男生、女生的人数

代码：

SELECT COUNT(CASE WHEN `性别`='男' THEN 1 ELSE NULL END) '男生人数',
COUNT(CASE WHEN `性别`='女' THEN 1 ELSE NULL END) '女生人数'
FROM `学生表`;

结果：

552f4b34769019369fdd0d107b07a518.png

另一种：

SELECT `性别`, COUNT(`学号`)
FROM `学生表`
GROUP BY `性别`;

结果：

60ce797afd93c60f26c583889c8cda59.png

第二十六题：查询名字中含有‘四’字的学生信息

代码：

SELECT * FROM `学生表`
WHERE `姓名` LIKE "%四%"

结果：

6cfed4b9278e84498a4ea6562ff95f4f.png

注意：

"%四%"--含有“四”字

"四%"--“四”字开头

"%四"--“四”字结尾

第二十七题：查询1990年出生的学生名单

代码：

SELECT * FROM `学生表`
WHERE YEAR(`出生日期`)=1990;

结果：

258aa83c4994745df4594dcca403dad7.png

第二十八题：查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

代码：

SELECT b.`学号`,b.`姓名`,avg(`成绩`)
FROM `成绩表` a
INNER JOIN `学生表` b ON a.`学号`=b.`学号`
GROUP BY a.`学号`
HAVING avg(`成绩`)>=85

结果：

ae1daa54adc6da1d5e835466582f0245.png

第二十九题：查询每门课的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序

代码：

SELECT c.`课程号`,d.`课程名称`,avg(`成绩`)
FROM `成绩表` c
INNER JOIN `课程表` d ON c.`课程号`=d.`课程号`
GROUP BY c.`课程号`
ORDER BY avg(`成绩`) ASC,c.`课程号` DESC

ASC表示升序，DESC表示降序

结果：

7a912bc840fe2f1916df0e6501cd5594.png

第三十题：查询课程名称为数学，且分数低于60分的学生姓名和分数

代码：

SELECT e.`课程号`,f.`课程名称`,g.`姓名`,e.`成绩`
FROM `成绩表` e 
INNER JOIN `课程表` f ON e.`课程号`=f.`课程号`
INNER JOIN `学生表` g ON e.`学号`=g.`学号`
WHERE e.`成绩`<60 AND f.`课程名称`='数学';

结果：

96dc0cadf1cf2ea6f285a8b7ea5f86af.png

第三十一题：查询所有学生的课程及分数情况

代码：

SELECT h.`学号`,j.`姓名`,i.`课程号`,i.`课程名称`,h.`成绩` FROM `成绩表` h 
INNER JOIN `课程表`  i ON h.`课程号`=i.`课程号`
INNER JOIN  `学生表` j ON h.`学号`=j.`学号`;

结果：

f2b0ce93497436c0150b201aecac80fb.png

优化代码：

SELECT h.`学号`,j.`姓名`,
max(CASE WHEN i.`课程名称`='语文' THEN h.`成绩` else NULL END) '语文',
max(CASE WHEN i.`课程名称`='数学' THEN h.`成绩` else NULL END) '数学',
max(CASE WHEN i.`课程名称`='英语' THEN h.`成绩` else NULL END) '英语',
max(CASE WHEN i.`课程名称`='德语' THEN h.`成绩` else NULL END) '德语'
FROM `成绩表` h 
INNER JOIN `课程表`  i ON h.`课程号`=i.`课程号`
INNER JOIN  `学生表` j ON h.`学号`=j.`学号`
GROUP BY  h.`学号`,j.`姓名`;

结果：

4246b2e9740c0038c6e6ca2d999a39c8.png

解释下为什么用max()+case when

首先想要获得此格式：

cf84f1f2e04e0eebfd00046c64356247.png

利用上述代码中的一段可以得到学号和姓名：

SELECT h.`学号`,j.`姓名`
FROM `成绩表` h 
INNER JOIN `课程表`  i ON h.`课程号`=i.`课程号`
INNER JOIN  `学生表` j ON h.`学号`=j.`学号`
GROUP BY  h.`学号`,j.`姓名`;

f6f33390950438647ecbfe6ddd41b75f.png

用图表格式解释group by就是下面这个样子：

73beed20fce0ca83b81434bf08e6726d.png

当这样子直接用case when 时会出现某些数值明明存在却显示为NULL的情况，这是因为已经通过group by进行了分组，甲包含了四个成绩，但case when只是一个判断语句，只能返回一个值，默认返回第一行数值，所以就会出现，当想取出甲的数学成绩时，会返回第一条NULL值，因此需要加一个聚合函数，取出对应数据值，这里MAX()也可用SUM()进行替代。

c23e7ff14a7500a010e2faad64078930.png

第三十二题：查询课程成绩在70分以上课程名称、分数和学生姓名

代码：

SELECT k.`课程名称`,j.`成绩`,l.`姓名`
FROM `成绩表` j
INNER JOIN `课程表` k ON j.`课程号`=k.`课程号`
INNER JOIN `学生表` l ON j.`学号`=l.`学号`
WHERE j.`成绩`>70;

结果：

952f35fc37f15e6540f10f4957047074.png

第三十三题：查询不及格的课程并按课程号从大到小排列

代码：

SELECT m.`课程号`,n.`课程名称`,o.`姓名`,m.`成绩`
FROM `成绩表` m
INNER JOIN `课程表` n ON m.`课程号`=n.`课程号`
INNER JOIN `学生表` o ON m.`学号`=o.`学号`
WHERE m.`成绩`<60
ORDER BY m.`课程号` DESC;

结果：

9c07710df0e363db60a4d6e15e1932f1.png

第三十四题：查询课程编号为0003且课程成绩在80分以上的学生姓名和学号

代码：

SELECT m.`课程号`,n.`课程名称`,o.`姓名`,m.`成绩`
FROM `成绩表` m
INNER JOIN `课程表` n ON m.`课程号`=n.`课程号`
INNER JOIN `学生表` o ON m.`学号`=o.`学号`
WHERE m.`成绩`>80 and m.`课程号`=0003;

结果：

d010b932b376868bf7f2bacdab5e7684.png

第三十五题：求每门课的学生人数

代码：

SELECT m.`课程号`,n.`课程名称`,COUNT(m.`学号`)
FROM `成绩表` m
INNER JOIN `课程表` n ON m.`课程号`=n.`课程号`
GROUP BY m.`课程号`,n.`课程名称`;

结果：

16049f7cb524fb026241c7e89365aebb.png

第三十六题：查询选修张三老师所授课程的学生中成绩最高的学生姓名以及成绩

代码：

SELECT m.`课程号`,n.`课程名称`,p.`教师名称`,o.`姓名`,max(m.`成绩`)
FROM `成绩表` m
INNER JOIN `课程表` n ON m.`课程号`=n.`课程号`
INNER JOIN `学生表` o ON m.`学号`=o.`学号`
INNER JOIN `教师表` p ON p.`教师号`=n.`教师号`
WHERE p.`教师名称`='张三';

结果：

aed6de6aa173307567df6bcba866c360.png

第三十七题：统计每门课程的学生选修人数（超过3人）

代码：

SELECT n.`课程号`,n.`课程名称`,COUNT(m.`学号`)
FROM `成绩表` m
INNER JOIN `课程表` n ON m.`课程号`=n.`课程号`
GROUP BY n.`课程号`
HAVING COUNT(m.`学号`)>3;

结果：

4604b5ed43bed75d68f25a94cf41c585.png

第三十八题：检索至少选修两门课程的学生学号

代码：

SELECT p.`学号`,o.`姓名`,COUNT(p.`学号`) '选修的课数'
FROM `学生表` o 
INNER JOIN `成绩表` p ON o.`学号`=p.`学号`
GROUP BY p.`学号`
HAVING COUNT(p.`学号`)>=2
ORDER BY COUNT(p.`学号`);

结果：

a0af4850f5b5ba18334fb7ff270bde37.png

第三十九题：查询选修了全部课程的学生信息

代码：

SELECT p.`学号`,o.`姓名`,COUNT(p.`学号`) '选修的课数'
FROM `学生表` o 
INNER JOIN `成绩表` p ON o.`学号`=p.`学号`
GROUP BY p.`学号`
HAVING COUNT(p.`学号`)=(SELECT COUNT(`课程号`) FROM `课程表`);

结果：

f9e4fffb8e5060b3431dc0ca8b7c0a06.png

第四十题：查询各学生的年龄

代码：

SELECT `姓名`,year(now())-YEAR(`出生日期`) "年龄"
FROM `学生表`

结果：

4af57dc0ac860cbf0591d609702b3d08.png

第四十一题：查询没学过张三老师讲授的任一门课程的学生姓名

代码：

SELECT * FROM  `学生表` 
WHERE `学号` NOT in (
SELECT w.`学号` 
FROM `成绩表` w
INNER JOIN `课程表` e ON w.`课程号`=e.`课程号`
WHERE e.`教师号`=(SELECT `教师号` FROM `教师表` WHERE `教师名称`='张三'));

结果：

44d10e0243edc045a781cbbeb03ad49e.png

在写这题的时候犯过一个错误，看下错误代码：

SELECT *
FROM `学生表` q
INNER JOIN `成绩表` w ON q.`学号`=w.`学号`
WHERE w.`学号` NOT in (
SELECT w.`学号` FROM `成绩表` w
INNER JOIN `课程表` e ON w.`课程号`=e.`课程号`
WHERE e.`教师号`=(SELECT `教师号` FROM `教师表` WHERE `教师名称`='张三'));

结果：

2500eab66e6edd5f47ec5320550c5c45.png

可以看出我这里把学生表和成绩表先INNER JOIN 了，这会导致，学生表里没有选课的同学0600号被排除在外，所以在最终取数时一定要从有完整信息的表格取，否则会有遗漏。

第四十二题：查询下周过生日的同学

代码：

这里使用了 @相遇天使 的思路以及解题方法：

以下是查询本周过生日的代码（参考时间 2020-5-29）：

考虑的两点

1.同周跨年（即上一年的12月31号与下一年的1月1号在同一个星期）

2.特殊的2月29号生日

delimiter //
create function birth(`出生日期` date)
returns boolean NOT DETERMINISTIC NO SQL
begin
   declare date_now date;
   declare bool boolean;
   set date_now = curdate();
   if month(`出生日期`)=12 and month(date_now)=1 then
      if week(replace(`出生日期`, year(`出生日期`), year(date_now)-1), 7) = week(date_now,7) then
         set bool = 1;
      else
         set bool = 0;
      end if;
   elseif month(date_now)=12 and month(`出生日期`)=1 then
      if week(replace(`出生日期`, year(`出生日期`), year(date_now)+1), 7) = week(date_now,7) then
         set bool = 1;
      else
         set bool = 0;
      end if;
   elseif month(`出生日期`)=2 and day(`出生日期`)=29 then
      if year(date_now)%4=0 then
         if week(replace(`出生日期`, year(`出生日期`), year(date_now)),7) = week(date_now,7) then
	    set bool = 1;
         else
	    set bool = 0;
         end if;
      else
         if week(concat_ws('-',year(curdate()),'03','01'),7) = week(date_now,7) then
	    set bool = 1;
         else
	    set bool = 0;
         end if;
      end if;	
   else
      if week(replace(`出生日期`, year(`出生日期`), year(date_now)),7) = week(date_now,7) then
         set bool = 1;
      else
         set bool = 0;
      end if;
   end if;
   return bool;
end//
delimiter ;

select `学号`, `姓名`, `出生日期`, `性别`
from (select *, birth(`出生日期`) as bool from `学生表`) as a
where a.bool = 1;

结果：

9cedd98139c46f1c600c706dc42c137e.png

*这里对源表日期做了修改，为了提取到数据，否则数据为NULL，以为没有5月过生日的人的数据

查询下周过生日的只需要把第七行的set date_now = curdate()改为set date_now = date_add(curdate(), interval 1 week)即可。

第四十三题：查询本月过生日的人

代码：

SELECT * FROM `学生表`
WHERE MONTH(`出生日期`)=MONTH(NOW());

结果：

87e7ba393b9cff81d9afbe08b4d25c7c.png

第四十四题：查询下一个月过生日的人

代码：

SELECT * FROM `学生表`
WHERE MONTH(`出生日期`)=MONTH(NOW())+1;

结果：

062fc66275001e029b63c3e6cbe82468.png

这里有个问题如果本月为12月，MONTH(NOW())+1就会得到13，这就出错了。

优化代码：

SELECT * FROM `学生表` WHERE
CASE WHEN MONTH('2020-12-11')=12 THEN MONTH(`出生日期`)=1 else MONTH(`出生日期`)=MONTH('2020-12-11')+1 END;

*这里的 MONTH('2020-12-11') 替代了 MONTH(NOW()) 用来假设本月是12月

结果：

140e8135f380bc97a93aff371d5f6ced.png
weixin_39581972
关注
百度mysql性能优化ppt
03-20
NULL 博文链接：https://soartju.iteye.com/blog/762913
mysql性能优化
ChengWeiUser的博客
10-09 200
百度云盘: https://pan.baidu.com/s/12MU5sbN1KSOuKI0UI0iORA 提取码: 8rtj
mysql5.7安装
最新发布
qq_52648720的博客
09-07 595
这边提供百度网盘的资料：链接：https://pan.baidu.com/s/1Ivc5y8DTRWTXxAvqRkIa6w?如果出现没有MSVCP120.dll,请下载x64的文件。先以管理员身份进入cmd。
网络云盘项目——Redis部署、MySQL部署、MySQL表设计
董哥的黑板报
08-09 3442
本项目分为7篇博客文章完成： 1.项目总体介绍：https://blog.csdn.net/qq_41453285/article/details/107871393 2.Redis部署、MySQL部署、MySQL表设计：本文介绍 一、本文目的 本文配置整个项目中的Redis与MySQL，并且对MySQL中的数据表进行设计 二、Redis部署 Redis的安装 Redis的安装比较容易，可以参阅：https://blog.csdn.net/qq_41453285/article/det
百度云盘分享：MySQL零基础入门视频教程！
x910378112的博客
08-04 2716
百度云盘分享：MySQL零基础入门视频教程！ 首先给大家介绍一下数据库工程师，数据库工程师(Database Engineer)，是从事管理和维护数据库管理系统(DBMS) 的相关工作人员的统称，他属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。数据库工程师的核心目标是保证数据库管理系统的稳定性、安全性、完整性和高性能。 今天在这里给大家分享一个干货教程，
mysql性能优化微盘_MySQL性能优化最佳实践.pdf
weixin_29015801的博客
01-31 223
MySQL 性能优化最佳实践About me简朝阳(sky000)Oracle ACE(Expertise: MySQL)技术保障部 @麦包包Blog：Twitter ：@sky000Weibo ：@简朝阳DTCC2012DTCC20122012/4/6 1MySQL 性能优化最佳实践优化过程找出瓶颈确认结果 优化 ...
MYSQL.rar_MYSQL_mysql 优化_mysql优化_mysql备份_sql优化
09-14
MySQL是世界上最受欢迎的关系型数据库管理系统之一，用于存储和管理各种类型的数据。为了确保数据库高效运行，需要进行一系列的优化操作，包括结构优化、查询优化、配置优化和硬件优化。本资料包“MYSQL.rar”提供了...
安装最新的MySQL8.0.19 附百度网盘链接
12-14
由于提供的链接是百度网盘，可以直接访问给出的链接（https://pan.baidu.com/s/1cn0EuH8JRQ9KMsryf64iFg），输入提取码（ntw7）下载MySQL Community Server。 2. 在下载页面，选择"MySQL Community (GPL) Downloads...
SQL文件（mysql练习必备）
03-03
本文件为博客内Mysql知识文章的练习脚本
MySQL开发者SQL权威指南_MYSQL_
10-04
MySQL开发者SQL权威指南是一本专为MySQL开发人员设计的详细参考书籍，旨在帮助读者深入理解和掌握SQL语言在MySQL环境中的应用。MySQL是一种广泛使用的开源关系型数据库管理系统，它以其高效、稳定和易用性赢得了全球...
MySQL.rar_MYSQL_mysql 安装包
09-14
MySQL是一款广泛使用的开源关系型数据库管理系统（RDBMS），它基于结构化查询语言（SQL）进行数据操作。MySQL因其高效、稳定和易于管理的特点，在Web应用程序开发和服务器端数据存储中占据重要地位。本安装包是针对...
sql视频教程(网盘资源永久有效)
08-15
sql数据库基础视频视频精讲。（因视频文件较大，所以不能上传，所以将其放在了网盘里。网盘链接永久有效。下载该文档就可以看到链接及密码） 分享一点学习资料，希望和大家一起学习。
数据库SQL教学视频百度云链接
07-22
数据库SQL基础教学视频，百度云链接，欢迎大家下载使用
sql server视频教程.txt（老师分享的学习视频）
06-20
sql server视频教程.txt（老师分享的学习视频）
兄弟连MySQL数据库视频教程（18集）
07-23
教程名称：兄弟连MySQL数据库视频教程（18集）课程目录：【】1.MySQL数据库管理系统概述【】10.PHP设计完美分页类【】11.PHP的mysqli扩展库操作MySQL数据库【】12.使用PHP中mysqli扩展处理结果集【】13.使用PHP的mysql扩展完成事务处理和一次执行多条SQL语句【】14.使用PHP的mysqli扩展 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。
Mysql性能优化详解
weixin_72049402的博客
01-29 541
从硬件角度讲，服务器的CPU资源、内存资源、磁盘I/O共同决定了MySQL运行的效率和稳定性，任何部分出现资源不足的情况，MySQL服务本身就会出现性能问题，严重时Mysql服务可能不可用。4. 针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB 等，他们可以很好的缓解 Mysql 的访问压力，同时还能提升数据检索性能。3. 引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表的方式可以降低单表数据量，从而提升 sql 查询的效率。
史上最全！通往BAT必备法宝，内容太过真实
m0_56067448的博客
03-17 467
接口概述: 接口是Java语言中的一种引用类型，是方法的"集合"，所以接口的内部主要就是定义方法，包含常量,抽象方法（JDK 7及以前），额外增加默认方法和静态方法（JDK 8）,额外增加私有方法(jdk9)。 接口的定义，它与定义类方式相似，但是使用 interface 关键字。它也会被编译成.class文件，但一定要明确它并不是类，而是另外一种引用数据类型。 public class 类名.java–>.class public interface 接口名.java–>.class 接口的使
MySQL性能优化和架构设计
weixin_42248522的博客
08-05 337
MySQL
关于MySQL性能优化方案，掌握这一篇就够了！
mashangtt的博客
06-26 297
MySQL是目前应用非常广泛的关系型数据库管理系统，它在实际应用中的性能表现往往是决定系统效率和用户使用体验的重要因素。因此，MySQL性能优化方案非常重要。MySQL性能优化方案需要从多个方面入手，包括硬件性能、数据库设计、SQL语句优化等。需要系统性的分析MySQL性能瓶颈及优化方案后，才能使其在高并发场景或大数据量下有更好的性能表现。在实际应用中，常见的MySQL性能优化方案包括使用索引、优化查询语句、优化数据库结构、调整参数配置、分库分表、使用缓存等措施。
详述MySQL8.0.19安装步骤：从下载到配置
"本文提供了安装MySQL 8.0.19的详细步骤，包括从官网或百度网盘下载安装包，选择正确的版本，以及在Windows操作系统上执行安装过程。此外，还简要介绍了MySQL Workbench的基本使用方法，如创建数据库。" 在安装...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值