练习21-25：模糊查询like、多字段自连接查询、year()、left()、substr()等函数

四张表信息

21. 查询男生、女生人数

简单分组统计即可

SELECT 
Ssex,
COUNT(*) AS '数量'
FROM student
GROUP BY Ssex;

22. 查询名字中含有【风】字的学生信息

简单模糊查询

SELECT * FROM student WHERE Sname LIKE '%风%';

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

第一步：自连接查询，筛选出名字、性别相同但学生编号不同的信息，学生编号不同即排除自身

SELECT
*
FROM student a
JOIN student b
ON a.`Sname` = b.`Sname` AND a.`Ssex` = b.`Ssex` AND a.`SId` != b.`SId`;

第二步：统计人数，按照姓名、性别分组，统计记录条数即可

SELECT
a.`Sname`,
a.`Ssex`,
COUNT(*) AS ct
FROM student a
JOIN student b
ON a.`Sname` = b.`Sname` AND a.`Ssex` = b.`Ssex` AND a.`SId` != b.`SId`
GROUP BY a.`Sname`,a.`Ssex`;

24. 查询1990年出生的学生名单

1. 使用year()函数可以获取年份

SELECT * FROM student WHERE YEAR(sage) = '1990';

2. 也可以使用left函数或者substr函数获取年份

SELECT * FROM student WHERE LEFT(sage,4) = '1990';

3. 使用substr函数时注意下标是从1开始的

SELECT * FROM student WHERE SUBSTR(sage,0,4) = '1990';

SELECT * FROM student WHERE SUBSTR(sage,1,4) = '1990';

比较下面两句，可见substr函数下标若写成从0开始不会保错，但是不会获取到任何东西

SELECT SUBSTR(sage,0,4) FROM student WHERE sid = '01';

SELECT SUBSTR(sage,1,4) FROM student WHERE sid = '01';

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

简单分组排序

 SELECT 
 cid,
 AVG(score) AS avg_score
 FROM sc
 GROUP BY cid
 ORDER BY avg_score DESC,cid ASC;