MySql数据库50题
准备工作
参考答案
use PraticeSql;
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
course表:
sc表:
student表:
teacher表:
关系:
题目
1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
SELECT * FROM
(SELECT `SId` AS sno1, `CId`AS cno1, score FROM sc WHERE `CId`=01) a
LEFT JOIN
(SELECT `SId` AS sno2, `CId`AS cno2, score FROM sc WHERE `CId`=02) b
ON a.sno1 = b.sno2
WHERE a.score > b.score
left join:代表选择的是前面一个表的全部。
左连接是以左表为标准,只查询在左边表中存在的数据,当然需要两个表中的键值一致。语法如下:
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
1.1 查询同时存在" 01 “课程和” 02 "课程的情况
SELECT * FROM
(SELECT `SId` AS sno1, `CId`AS cno1, score FROM sc WHERE `CId`=01) a
LEFT JOIN
(SELECT `SId` AS sno2, `CId`AS cno2, score FROM sc WHERE `CId`=02) b
ON a.sno1 = b.sno2
WHERE sno2 IS NOT NULL;
使用IS NULL运算符
查询包含空值的记录
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT * FROM
(SELECT `SId` AS sno1, `CId`AS cno1, score FROM sc WHERE `CId`=01) a
LEFT JOIN
(SELECT `SId` AS sno2, `CId`AS cno2, score FROM sc WHERE `CId`=02) b
ON a.sno1 = b.sno2;
1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT *
FROM sc
WHERE `CId`= '02' AND `SId` NOT IN(
SELECT `SId`
FROM sc
WHERE 'CId' = '01'
)
使用NOT运算符
NOT是取反的意思
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN (‘CLERK’,‘MANAGER’,‘ANALYST’);
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT a.SId,a.avg_score,b.Sname
FROM(
SELECT `SId`,AVG(score) AS avg_score
FROM sc
GROUP BY `SId`
)AS a
LEFT JOIN
student AS b
ON
a.SId = b.SId
WHERE a.avg_score >= 60;
数据分组–GROUP BY
GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
分组的含义是:把该列具有相同值的多条记录当成一组记录处理,最后只输出一条记录。
分组函数忽略空值,。
结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
#每个部门的平均工资
SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno
#查每个部门的整体工资情况
#如果select语句中的列未使用组函数,那么它必须出现在GROUP BY子句中
#而出现在GROUP BY子句中的列,不一定要出现在select语句中
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno #根据部门编号分组
#每个部门每个职位的平均工资
SELECT deptno,job,AVG(sal) FROM TB_EMP GROUP BY deptno,job
分组函数重要规则
如果使用了分组函数,或者使用GROUP BY 的查询:出现在SELECT列表中的字段,要么出现在组合函数里,
要么出现在GROUP BY 子句中。
GROUP BY 子句的字段可以不出现在SELECT列表当中。
使用集合函数可以不使用GROUP BY子句,此时所有的查询结果作为一组。
AVG(数值类型列名) 平均值
3.查询在 SC 表存在成绩的学生信息
SELECT *
FROM student
WHERE `SId` IN(
SELECT DISTINCT `SId`
FROM sc
)
使用DISTINCT关键字可从查询结果中清除重复行
SELECT DISTINCT department_id
FROM employees;
DISTINCT的作用范围是后面所有字段的组合
SELECT DISTINCT department_id , job_id
FROM employees;
子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 NULL )
SELECT `SId`,Sname,course_total,score_sum
FROM (
SELECT `SId`,Sname FROM student
)AS a
LEFT JOIN
(SELECT `SId` AS sno, COUNT(`CId`) AS course_total, SUM(score) AS score_sum
FROM sc
GROUP BY sno
)AS b
ON a.SId = b.sno;
4.1 查有成绩的学生信息
SELECT `SId`,Sname,course_total,score_sum
FROM (
SELECT `SId`,Sname
FROM student
)AS a
LEFT JOIN
(SELECT `SId` AS sno,COUNT(`CId`) AS course_total,SUM(score) AS score_sum
FROM sc
GROUP BY sno
)AS b
ON a.SId = b.sno
WHERE course_total IS NOT NULL;
5.查询「李」姓老师的数量
SELECT COUNT(*)
FROM teacher
WHERE Tname LIKE '李%';
COUNT(*|列名) 统计行数
6. 查询学过「张三」老师授课的同学的信息
FROM student
WHERE `SId` IN(
SELECT `SId` FROM sc WHERE `CId`=(
SELECT `CId` FROM course WHERE `TId`=(
SELECT `TId` FROM teacher WHERE Tname='张三'
)
)
)
四张表的操作
7. 查询没有学全所有课程的同学的信息
SELECT `SId`,COUNT(`CId`) AS course_num
FROM sc
GROUP BY `SId`
HAVING course_num < (
SELECT COUNT(*)
FROM course
);
数据分组–限定组的结果:HAVING子句
HAVING子句用来对分组后的结果再进行条件过滤。
SELECT column, group_functionFROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BYcolumn];
HAVING子句用来对分组后的结果再进行条件过滤。
#查询部门平均工资大于2000的
#分组后加条件 使用having
#where和having都是用来做条件限定的,但是having只能用在group by之后
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno
HAVING AVG(sal) > 2000
HAVING与WHERE的区别
WHERE是在分组前进行条件过滤, HAVING子句是在分组后进行条件过滤,WHERE子句中不能使用聚合函数,
HAVING子句可以使用聚合函数。
8. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT *
FROM student
WHERE `SId` IN(
SELECT DISTINCT `SId`
FROM sc
WHERE `CId` IN(
SELECT `CId`
FROM sc
WHERE `SId`= '01'
)
)
AND 'SId'!='01';
9. (有点问题)查询和"01"号的同学学习的课程完全相同的其他同学的信息*
“01”号的同学学习的课程号 a
完全相同 having
SELECT * FROM student AS c
RIGHT JOIN
(
SELECT `SId` FROM (
SELECT * FROM sc
LEFT JOIN
(SELECT `CId` AS cno FROM sc WHERE `SId`='01')AS a
ON sc.`CId` = a.cno
) AS b
GROUP BY `SId`
HAVING COUNT(b.SId) = (SELECT COUNT(`CId`) AS cno FROM sc WHERE `SId`='01')
)AS d
ON c.`SId` = d.`SId`;
select * from student
where student.sid in (
select sc.sid from sc
where sc.cid in (
select sc.cid from sc
where sc.sid = '01'
)
);
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT `SId` FROM sc WHERE `CId` IN (
SELECT `CId` FROM course WHERE `TId` IN (
SELECT `TId` FROM teacher WHERE `Tname`='张三'
)
)
11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
(不及格课程的同学的 学号 a
两门及其以上a同学的 学号,姓名 b
)
左连接
(平均成绩 c)
SELECT `SId`,Sname,avg_score FROM
(SELECT `SId`, sname FROM student WHERE `SId` IN(
SELECT a.`SId` FROM
(SELECT `SId`,COUNT(`CId`) AS num FROM sc WHERE score <60 GROUP BY SC.`SId`) a
WHERE num >=2)
)AS b
LEFT JOIN
(SELECT `SId` AS sno ,AVG(score) AS avg_score FROM sc GROUP BY `SId`) AS c
ON b.`SId` = c.sno;
12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
" 01 "课程分数小于 60 SId b
SELECT `SId`,`Sname`,score
FROM student AS a
LEFT JOIN
(SELECT `SId` AS sno,`CId`,score FROM sc WHERE score<60 AND `CId`='01') AS b
ON a.SId = b.sno
WHERE score IS NOT NULL
ORDER BY score DESC ;
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT `SId`,AVG(score) AS avg_score FROM sc
GROUP BY `SId`
ORDER BY avg_score DESC;
14. 查询各科成绩最高分、最低分和平均分:
# 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
# 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
# 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT DISTINCT `SId`,a.`CId`,Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 FROM sc AS a
LEFT JOIN course on a.`CId` = Course.`CId`
LEFT JOIN (SELECT `CId`,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分 FROM sc GROUP BY `CId`)AS b ON a.`CId`=b.`CId`
LEFT JOIN (
SELECT `CId`,ROUND( r1 / cnt * 100, 2)及格率 FROM
(SELECT `CId`,(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)*1.00) r1, COUNT(*)cnt FROM sc GROUP BY `CId`) c1
) AS c
ON a.`CId`=c.`CId`
LEFT JOIN (
SELECT `CId`,ROUND( r2 / cnt *100, 2)中等率 FROM
(SELECT `CId`,(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END)*1.00) r2, COUNT(*)cnt FROM sc GROUP BY `CId`) d1
) AS d
ON a.`CId`=d.`CId`
LEFT JOIN (
SELECT `CId`,ROUND( r3 / cnt *100, 2)优良率 FROM
(SELECT `CId`,(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)*1.00) r3, COUNT(*)cnt FROM sc GROUP BY `CId`) e1
) AS e
ON a.`CId`=e.`CId`
LEFT JOIN (
SELECT `CId`,ROUND( r3 / cnt *100, 2)优秀率 FROM
(SELECT `CId`,(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)*1.00) r3, COUNT(*)cnt FROM sc GROUP BY `CId`) f1
) AS f
ON a.`CId`=f.`CId`;
1.使用CASE WHEN语句执行分支操作
mysql> SELECT CASE WHEN 1<0 THEN ‘true’ ELSE ‘false’ END;
±-------------------------------------------+
| CASE WHEN 1<0 THEN ‘true’ ELSE ‘false’ END |
±-------------------------------------------+
| false |
±-------------------------------------------+
1 row in set (0.00 sec)
2.ROUND :对所传参数进行四舍五入
#(不会)15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
# mysql中没有rank()函数
# 这种是重复时候保留名次,所以最后名次和人数是一样的
SELECT `s#`, `c#`, score, rank FROM
(SELECT `s#`, `c#`, score,
@currank := IF(@prevrank = score, @currank, @incrank) AS rank,
@incrank := @incrank + 1,
@prevrank := score
FROM sc , (
SELECT @currank :=0, @prevrank := NULL, @incrank := 1
) r
ORDER BY score DESC) s
阿里云教你用SQL实现统计排名
在MySQL中实现Rank高级排名函数
#(不会)15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
# 这种是当有重复名次的时候变成只有一个名次,所以排名的数量会变少
SELECT `s#`, `c#`, score,
CASE
WHEN @prevrank = score THEN @currank
WHEN @prevrank := score THEN @currank := @currank + 1
END AS rank
FROM sc,
(SELECT @currank :=0, @prevrank := NULL) r
ORDER BY score DESC
# (不会)16. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
# from后面不需要加表的别名
SELECT `s#`, sum_score, rank FROM
(SELECT `s#`, sum_score,
@currank := IF(@prevrank = sum_score, @currank, @incrank) AS rank,
@incrank := @incrank + 1,
@prevrank := sum_score
FROM
(SELECT `s#`, SUM(score) AS sum_score FROM sc GROUP BY `s#`) c ,
(SELECT @currank :=0, @prevrank := NULL, @incrank := 1) r
ORDER BY sum_score DESC) s
# (不会)16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT c.*,
CASE
WHEN @prevrank = c.sum_score THEN @currank
WHEN @prevrank := c.sum_score THEN @currank := @currank + 1
END AS rank
FROM
(SELECT a.`s#`,a.sname,SUM(score) AS sum_score
FROM (student AS a RIGHT JOIN sc AS b ON a.`s#` = b.`s#`)
GROUP BY a.`s#` ) c ,
(SELECT @currank := 0 , @prevrank :=NULL ) d
ORDER BY sum_score DESC
17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT a.`SId`,b.Cname,
#百分比
#[100-85]
SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END ) '[100-85]',
SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END )*1.00 / COUNT(*) '[100-85]percent',
#(85-70]
SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END ) '(85-70]',
SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END )*1.00 / COUNT(*) AS '(85-70]percent',
#(70-60]
SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END ) '(70-60]',
SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END )*1.00 / COUNT(*) AS '(70-60]percent',
#(60-0]
SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END ) '(60-0]',
SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END )*1.00 / COUNT(*) AS '(60-0]percent',
#人数
COUNT(*) AS counts
FROM sc AS a
LEFT JOIN
course AS b
ON a.`CId` = b.`CId`
GROUP BY a.`CId`;
18. 查询各科成绩前三名的记录
SELECT * FROM sc AS a
WHERE (
SELECT COUNT(*) FROM sc WHERE a.`CId`=a.`CId` AND score >a.score
)<3
ORDER BY a.`CId`,a.score DESC ;
19. 查询每门课程被选修的学生数
方式一:
SELECT `CId`,COUNT(`SId`)
FROM (SELECT `SId`,`CId` FROM sc ORDER BY `CId`)AS a
GROUP BY `CId`;
方式二:
SELECT a.`CId`,b.Cname,COUNT(*) AS num
FROM sc AS a
LEFT JOIN course AS b ON a.`CId` = b.`CId`
GROUP BY a.`CId`;
20. 查询出只选修两门课程的学生学号和姓名
SELECT a.`SId`,a.`Sname`,cnt
FROM student AS a
LEFT JOIN (SELECT `SId`,COUNT(`CId`) AS cnt FROM sc GROUP BY `SId`) AS b
ON a.`SId` = B.`SId`
WHERE cnt=2;
21. 查询男生、女生人数
SELECT Ssex,COUNT(Ssex)
FROM student
GROUP BY Ssex;
22. 查询名字中含有「风」字的学生信息
SELECT *
FROM student
WHERE Sname LIKE '%风';
23. 查询同名同性学生名单,并统计同名人数
SELECT a.*,b.同名人数
FROM student AS a
LEFT JOIN (SELECT Sname,Ssex,COUNT(*)同名人数 FROM student GROUP BY Sname, Ssex) AS b
ON a.Sname = b.Sname AND a.Ssex = b.Ssex
WHERE b.同名人数>1;
24. 查询 1990 年出生的学生名单
SELECT *
FROM student
WHERE YEAR(Sage)=1990;
25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT ROUND(AVG(score),2) AS avg_score,`CId`
FROM sc
GROUP BY `CId`
ORDER BY `CId` ASC;
26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT c.`SId`,Sname,avg_score
FROM(
student AS c
LEFT JOIN
(SELECT `SId`,avg_score FROM
(SELECT `SId`,ROUND(AVG(score),2) AS avg_score FROM sc GROUP BY `SId` ORDER BY avg_score DESC)AS a
WHERE avg_score >= 85
)AS b
ON c.`SId` = b.`SId`
)
WHERE avg_score IS NOT NULL ;
27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT a.`SId`,a.Sname,b.math,b.score FROM
student AS a
LEFT JOIN (
SELECT `SId`,`CId` AS math,score
FROM sc
WHERE `CId` IN(SELECT `CId` FROM course WHERE Cname='数学') AND sc.score < 60
)AS b
ON a.`SId` = b.`SId`
WHERE b.score IS NOT NULL ;
28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT a.`SId`,a.`Sname`,a.`Sage`,a.`Ssex`,b.score,b.`CId`
FROM (student AS a LEFT JOIN sc AS b ON a.`SId` = b.`SId`)
LEFT JOIN course AS c ON c.`CId` = b.`CId`;
29查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT a.`SId`,a.`Sname`,a.`Ssex`,a.`Sage`,b.score,b.`CId`,c.`Cname`
FROM student AS a
LEFT JOIN (SELECT `SId`,`CId`,score FROM sc WHERE score > 70)AS b ON a.`SId` = b.`SId`
LEFT JOIN course AS c ON c.`CId`= b.`CId`
WHERE score IS NOT NULL ;
30. 查询不及格的课程
SELECT * FROM sc WHERE score < 60;
31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT a.`SId`,a.`Sname`,b.score
FROM student AS a
LEFT JOIN (SELECT * FROM sc WHERE `CId` = '01' AND score >= 80) AS b ON a.`SId` = b.`SId`
WHERE score IS NOT NULL ;
32. 求每门课程的学生人数
SELECT `CId`,COUNT(`CId`) FROM sc GROUP BY `CId`;
33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT a.`SId`,a.`Sname`,b.`CId`,b.max_score
FROM student AS a
LEFT JOIN
(SELECT `SId` AS sid, `CId`, MAX(score) AS max_score FROM sc WHERE `CId` IN (
SELECT `CId` FROM course WHERE `TId` IN (
SELECT `TId`FROM teacher WHERE Tname = '张三'
)))AS b
ON a.`SId`=b.`sId`
WHERE max_score IS NOT NULL ;
(不会)34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT * FROM
(SELECT dd.*,
CASE
WHEN @prevrank = dd.score THEN @currank
WHEN @prevrank := dd.score THEN @currank := @currank + 1
END AS rank
FROM (SELECT a.*,b.score FROM
student a
LEFT JOIN sc b ON a.`s#` = b.`s#`
LEFT JOIN course c ON b.`c#` = c.`c#`
LEFT JOIN teacher d ON c.`t#` = d.`t#` WHERE d.tname = '张三' ) dd,(SELECT @currank := 0 , @prevrank :=NULL ) ff
ORDER BY score DESC) AS dddddddd
WHERE rank = 1;
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT a.`SId`,a.`CId`,a.score
FROM sc AS a JOIN SC AS b
WHERE a.`CId` != b.`CId` AND a.score = b.score AND a.`SId` != b.`SId`
ORDER BY a.`SId`,a.`CId`,a.score;
自连接JOIN
36. 查询每门功课成绩最好的前两名
SELECT * FROM sc AS a
WHERE (SELECT COUNT(*) FROM sc WHERE `CId`= a.`CId` AND score > a.score)<2
ORDER BY a.`CId`,a.score DESC ;
37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)
select a.`CId`,COUNT(*) AS num
FROM course AS a
LEFT JOIN sc AS b ON a.`CId` = b.`CId`
GROUP BY a.`CId` HAVING num > 5
ORDER BY num,a.`CId`;
38. 检索至少选修两门课程的学生学号
SELECT `SId`,COUNT(`CId`) AS num
FROM sc
GROUP BY `SId` HAVING num >= 2;
# 39. 查询选修了全部课程的学生信息
SELECT * FROM
(SELECT `SId`,COUNT(*) AS num FROM sc GROUP BY `SId`)AS b
WHERE num = (SELECT COUNT(*) FROM course);
# 40. 查询各学生的年龄,只按年份来算
SELECT * ,YEAR(NOW()) - YEAR(Sage) AS age FROM student;
(不会)# 41. 查询本周过生日的学生
SELECT * FROM
(SELECT * , WEEK(sage), MONTH(sage),DAY(sage),
WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a
WHERE w = WEEK(NOW())
(不会)42. 查询下周过生日的学生
SELECT * FROM
(SELECT * , WEEK(sage), MONTH(sage),DAY(sage),WEEK(NOW()),
WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a
WHERE w + 2 = WEEK(NOW())
43. 查询本月过生日的学生
SELECT * ,MONTH(Sage),MONTH(NOW()) FROM student
WHERE MONTH(Sage) = MONTH(NOW());
44. 查询下月过生日的学生
SELECT * ,MONTH(Sage),MONTH(NOW()) FROM student
WHERE MONTH(Sage) = MONTH(NOW())+1;