建表
学生表Student
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
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');
教师表Teacher
CREATE TABLE Teacher(TId VARCHAR(10),Tname VARCHAR(10));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
成绩表SC
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);
#1 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
#先查 " 01 “课程比” 02 "课程成绩高的课程分数信息
SELECT t1.SId,class1,class2 FROM
(SELECT SId,score class1 FROM sc WHERE CId='01') t1,
(SELECT SId,score class2 FROM sc WHERE CId='02') t2
WHERE t1.SId=t2.SId AND t1.class1>t2.class2;
####①
SELECT *FROM student ,(
SELECT t1.SId,class1,class2 FROM
(SELECT SId,score class1 FROM sc WHERE CId='01') t1,
(SELECT SId,score class2 FROM sc WHERE CId='02') t2
WHERE t1.SId=t2.SId AND t1.class1>t2.class2
)r
WHERE student.`SId`=r.SId
#####②
SELECT * FROM Student INNER JOIN (
SELECT t1.SId, class1, class2 FROM
(SELECT SId, score AS class1 FROM sc WHERE sc.CId = '01')AS t1,
(SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
WHERE t1.SId = t2.SId AND t1.class1 > t2.class2
)r
ON Student.SId = r.SId;
#####③
SELECT * FROM Student RIGHT JOIN (
SELECT t1.SId, class1, class2 FROM
(SELECT SId, score AS class1 FROM sc WHERE sc.CId = '01')AS t1,
(SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02')AS t2
WHERE t1.SId = t2.SId AND t1.class1 > t2.class2
)r
ON Student.SId = r.SId;
#1.1 查询同时存在" 01 “课程和” 02 "课程的情况
SELECT * FROM
(SELECT SId, score AS class1 FROM sc WHERE sc.CId = '01') t1,
(SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02') t2
WHERE t1.SId = t2.SId
#1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT * FROM
(SELECT SId,score AS class1 FROM sc WHERE sc.`CId`='01')t1 LEFT JOIN
(SELECT SId,score AS class2 FROM sc WHERE sc.`CId`='02')t2
ON t1.SId = t2.SId
#1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT *FROM sc
WHERE SId NOT IN(
SELECT SId FROM sc
WHERE CId=01
)
AND CId=02
#2 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.SId '学生编号',Sname'学生姓名',ss'平均成绩'
FROM student,(
SELECT SId,AVG(score)ss FROM sc
GROUP BY SId
HAVING ss>60
)r
WHERE student.SId=r.SId
#3 查询在 SC 表存在成绩的学生信息
SELECT *FROM student
WHERE SId IN(SELECT sid FROM sc)
####
SELECT DISTINCT student.*
FROM student,sc
WHERE student.SId=sc.SId
#4 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT student.`SId` '学生编号',sname'学生姓名',cc'选课总数',ss'总成绩'
FROM student LEFT JOIN (
SELECT sid,COUNT(*)cc,SUM(score)ss FROM sc GROUP BY sid
)r
ON student.`SId`=r.sid
SELECT s.sid, s.sname,r.coursenumber,r.scoresum
FROM (
(SELECT student.sid,student.sname
FROM student
)s
LEFT JOIN
(SELECT
sc.sid, SUM(sc.score) AS scoresum, COUNT(sc.cid) AS coursenumber
FROM sc
GROUP BY sc.sid
)r
ON s.sid = r.sid
);
#4.1 查有成绩的学生信息
SELECT *FROM student
WHERE SId IN(SELECT sid FROM sc)
####
SELECT * FROM student
WHERE EXISTS (SELECT sc.sid FROM sc WHERE student.sid = sc.sid);
#5 查询「李」姓老师的数量
SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%'
#6 查询学过「张三」老师授课的同学的信息
SELECT *FROM student WHERE sid IN(
SELECT sid FROM sc WHERE cid=(
SELECT cid FROM course WHERE tid=(
SELECT tid FROM teacher WHERE tname='张三'
)
)
)
SELECT student.* FROM student,teacher,course,sc
WHERE
student.sid = sc.sid
AND course.cid=sc.cid
AND course.tid = teacher.tid
AND tname = '张三';
#7 查询没有学全所有课程的同学的信息
SELECT * FROM student
WHERE sid NOT IN (
SELECT sid FROM sc
GROUP BY sid
HAVING COUNT(sid)=(SELECT COUNT(*) FROM course)
)
#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
);
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'
)
);
#9查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT student.*FROM student,sc WHERE student.sid=sc.`SId`
GROUP BY student.`SId`
HAVING SUM(cid)=(SELECT SUM(cid)FROM sc WHERE sid=01)
AND Student.sid!=01;
SELECT student.* FROM student,sc WHERE student.`SId`=sc.`SId`
GROUP BY student.`SId`
HAVING student.SId!=01
AND SUM(Cid)=(
SELECT SUM(cid) FROM sc WHERE sid=01
);
#10 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT sname FROM student WHERE sid NOT IN(
SELECT sid FROM sc,course,teacher
WHERE sc.`CId`=course.`CId`
AND Course.`TId`=teacher.`TId`
AND teacher.`Tname`='张三'
);
SELECT * FROM student
WHERE student.sid NOT IN(
SELECT sc.sid FROM sc WHERE sc.cid IN(
SELECT course.cid FROM course WHERE course.tid IN(
SELECT teacher.tid FROM teacher WHERE tname = "张三"
)
)
);
#11 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT student.`SId` '学号',sname'姓名',AVG(score)'平均成绩'
FROM student,sc
WHERE student.sid=sc.`SId`
AND student.sid IN(
SELECT sid FROM sc
WHERE score<60
GROUP BY sid
HAVING COUNT(score)>1
)
GROUP BY sc.`SId`
SELECT student.SId, student.Sname,b.avg
FROM student RIGHT JOIN(
SELECT sid, AVG(score) AS `avg` FROM sc
WHERE sid IN (
SELECT sid FROM sc
WHERE score<60
GROUP BY sid
HAVING COUNT(score)>1
)
GROUP BY sid
) b
ON student.sid=b.sid;
#12 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT student.*,score FROM student,sc
WHERE student.sid=sc.sid
AND score<60
AND cid=01
ORDER BY score DESC
#13按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc.*,r.avg'平均成绩' FROM sc LEFT JOIN
(SELECT sid,AVG(score)`avg` FROM sc GROUP BY sid )r
ON sc.sid=r.sid
ORDER BY `avg` DESC
####
SELECT * FROM sc
LEFT JOIN (
SELECT sid,AVG(score) AS avscore FROM sc
GROUP BY sid
)r
ON sc.sid = r.sid
ORDER BY avscore DESC;
#14 查询各科成绩最高分、最低分和平均分:
#以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
#及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
#要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT sc.cid'课程ID',
cname'课程name',
MAX(score)'最高分',
MIN(score)'最低分',
AVG(score)'平均分',
COUNT(*)'选修人数',
SUM(CASE WHEN sc.score>=60 THEN 1 ELSE 0 END )/COUNT(*)AS 及格率,
SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END )/COUNT(*)AS 中等率,
SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END )/COUNT(*)AS 优良率,
SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END )/COUNT(*)AS 优秀率
FROM sc,course
WHERE sc.cid=course.cid
GROUP BY sc.cid
ORDER BY COUNT(*)DESC, sc.CId ASC
####
SELECT
sc.CId ,
MAX(sc.score)AS 最高分,
MIN(sc.score)AS 最低分,
AVG(sc.score)AS 平均分,
COUNT(*)AS 选修人数,
SUM(CASE WHEN sc.score>=60 THEN 1 ELSE 0 END )/COUNT(*)AS 及格率,
SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END )/COUNT(*)AS 中等率,
SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END )/COUNT(*)AS 优良率,
SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END )/COUNT(*)AS 优秀率
FROM sc
GROUP BY sc.CId
ORDER BY COUNT(*)DESC, sc.CId ASC
#15 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
#用sc中的score和自己进行对比,来计算“比当前分数高的分数有几个”。
####
SELECT a.*,COUNT(b.score)+1 rank
FROM sc a
LEFT JOIN sc b
ON a.score<b.score AND a.cid=b.cid
GROUP BY a.cid,a.sid
ORDER BY a.cid,rank
####
SELECT a.cid, a.sid, a.score, COUNT(b.score)+1 AS rank
FROM sc AS a
LEFT JOIN sc AS b
ON a.score<b.score AND a.cid = b.cid
GROUP BY a.cid, a.sid,a.score
ORDER BY a.cid, rank ASC;
#15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
SELECT
a.*,
COUNT(a.score) AS 排名
FROM
sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid
AND a.score < b.score
GROUP BY a.cid,
a.sid
ORDER BY a.cid,排名,a.score DESC
#16 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
###select sid,sum(score)ss from sc group by sid order by ss desc
####
SELECT a.*,COUNT(b.ss)+1 rank
FROM (SELECT sid,SUM(score)ss FROM sc GROUP BY sid ORDER BY ss DESC )a
LEFT JOIN (SELECT sid,SUM(score)ss FROM sc GROUP BY sid ORDER BY ss DESC ) b
ON a.ss<b.ss
GROUP BY a.sid
ORDER BY rank
####
SET @crank=0;
SELECT q.sid, total, @crank := @crank +1 AS rank FROM(
SELECT sc.sid, SUM(sc.score) AS total FROM sc
GROUP BY sc.sid
ORDER BY total DESC)q;
#16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT a.*,COUNT(a.ss) rank
FROM (SELECT sid,SUM(score)ss FROM sc GROUP BY sid ORDER BY ss DESC )a
LEFT JOIN (SELECT sid,SUM(score)ss FROM sc GROUP BY sid ORDER BY ss DESC ) b
ON a.ss<b.ss
GROUP BY a.sid
ORDER BY rank,a.ss DESC
#17 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT course.cname, course.cid,
SUM(CASE WHEN sc.score<=100 AND sc.score>85 THEN 1 ELSE 0 END) AS "[100-85]",
SUM(CASE WHEN sc.score<=85 AND sc.score>70 THEN 1 ELSE 0 END) AS "[85-70]",
SUM(CASE WHEN sc.score<=70 AND sc.score>60 THEN 1 ELSE 0 END) AS "[70-60]",
SUM(CASE WHEN sc.score<=60 AND sc.score>0 THEN 1 ELSE 0 END) AS "[60-0]"
FROM sc LEFT JOIN course
ON sc.cid = course.cid
GROUP BY sc.cid;
#18 查询各科成绩前三名的记录
SELECT * FROM sc
WHERE (
SELECT COUNT(*) FROM sc a
WHERE sc.cid = a.cid AND sc.score<a.score
)< 3
ORDER BY cid , sc.score DESC;
###
SELECT * FROM sc a
LEFT JOIN sc b ON a.cid = b.cid AND a.score<b.score
ORDER BY a.cid,a.score;
####
SELECT a.sid,a.cid,a.score FROM sc a
LEFT JOIN sc b ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid)<3
ORDER BY a.cid,a.score DESC
#19 查询每门课程被选修的学生数
SELECT cid,COUNT(cid) FROM sc GROUP BY cid
####
SELECT cid, COUNT(sid) FROM sc
GROUP BY cid;
#20查询出只选修两门课程的学生学号和姓名
SELECT sc.sid,sname FROM sc,student WHERE sc.sid=student.sid GROUP BY sc.sid HAVING COUNT(*)=2
####
SELECT student.sid, student.sname FROM student
WHERE student.sid IN
(SELECT sc.sid FROM sc
GROUP BY sc.sid
HAVING COUNT(sc.cid)=2
);
#21 查询男生、女生人数
SELECT SUM(ssex='男'),SUM(ssex='女') FROM student
####
SELECT ssex, COUNT(*) FROM student
GROUP BY ssex;
#22 查询名字中含有「风」字的学生信息
SELECT *FROM student WHERE sname LIKE '%风%'
#23 查询同名同性学生名单,并统计同名人数
###只查询同名
SELECT * FROM student
WHERE sname IN (
SELECT sname FROM student
GROUP BY sname
HAVING COUNT(*)>1
);
####查询同名同性
SELECT a.sname,a.ssex,COUNT(*) FROM student a JOIN student b ON a.sid !=b.sid AND a.sname = b.sname AND a.ssex = b.ssex
####同名同性详细信息
SELECT *,COUNT(*) FROM student a JOIN student b ON a.sid !=b.sid AND a.sname = b.sname AND a.ssex = b.ssex
#24 查询 1990 年出生的学生名单
SELECT *FROM student WHERE sage LIKE '1990%'
####
SELECT *
FROM student
WHERE YEAR(student.Sage)=1990;
#25 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT course.`CId`,course.`Cname`,AVG(SC.`score`)aa FROM course,sc
WHERE sc.`CId`=course.`CId`
GROUP BY course.`CId`
ORDER BY aa DESC,course.`CId`
#26 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT student.`SId`,sname,AVG(score)aa FROM student,sc
WHERE student.`SId`=sc.`SId`
GROUP BY sid
HAVING aa>=85
#27查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT sname,score FROM student,sc,course
WHERE cname='数学'
AND course.`CId`=sc.`CId`
AND student.`SId`=sc.`SId`
AND score<60
#28 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT student.`SId`,sname,cname,score
FROM student
LEFT JOIN sc
LEFT JOIN course
ON sc.`CId`=course.`CId`
ON student.`SId`=sc.`SId`
#29查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT Sname,Cname,score FROM student,course,sc
WHERE score>70
AND sc.`CId`=course.`CId`
AND student.`SId`=sc.`SId`
#30 查询不及格的课程
#查询课程不及格的学生姓名,课程名称和分数
SELECT Sname,Cname,score FROM student,course,sc
WHERE score<60
AND sc.`CId`=course.`CId`
AND student.`SId`=sc.`SId`
#只查询含有成绩不及格的学生的课程
SELECT cid FROM sc
WHERE score< 60
GROUP BY cid;
#31 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
SELECT student.`SId`,sname FROM student,sc
WHERE cid=01
AND score>=80
AND student.`SId`=sc.`SId`
#32 求每门课程的学生人数
#较详细输出
SELECT course.`CId`,cname,COUNT(*) FROM course,sc
WHERE course.`CId`=sc.`CId`
GROUP BY cid
#简单输出
SELECT sc.CId,COUNT(*) AS 学生人数
FROM sc
GROUP BY sc.CId;
#33 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
#方法一
SELECT student.*,cname,MAX(score) FROM student,sc,course,teacher
WHERE tname='张三'
AND teacher.`TId`=course.`TId`
AND course.`CId`=sc.`CId`
AND student.`SId`=sc.`SId`
#方法二
SELECT student.*, sc.score, sc.cid FROM student, teacher, course,sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
HAVING MAX(sc.score);
#方法三
SELECT student.*, sc.score, sc.cid FROM student, teacher, course,sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
ORDER BY score DESC
LIMIT 1;
#34 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
#先修改数据使成绩重复
UPDATE sc SET score=90
WHERE sid = "02"
AND cid ="02";
#先查出最高成绩,再条件过滤
SELECT student.*,cname,score FROM student,sc,course,teacher
WHERE tname='张三'
AND teacher.`TId`=course.`TId`
AND course.`CId`=sc.`CId`
AND student.`SId`=sc.`SId`
AND score=(
SELECT MAX(score)
FROM sc,student, teacher, course
WHERE tname='张三'
AND teacher.`TId`=course.`TId`
AND course.`CId`=sc.`CId`
AND student.`SId`=sc.`SId`
)
#35 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT a.cid, a.sid, a.score FROM sc AS a
INNER JOIN
sc AS b
ON a.sid = b.sid
AND a.cid != b.cid
AND a.score = b.score
GROUP BY cid, sid;
#36 查询每门功成绩最好的前两名
#同18题
#方法一
SELECT * FROM sc b
WHERE (
SELECT COUNT(*) FROM sc a
WHERE b.cid = a.cid AND b.score<a.score
)< 2
ORDER BY cid , b.score DESC;
#方法二
###
SELECT * FROM sc a
LEFT JOIN sc b ON a.cid = b.cid AND a.score<b.score
ORDER BY a.cid,a.score;
####
SELECT a.sid,a.cid,a.score FROM sc a
LEFT JOIN sc b ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid)<2
ORDER BY a.cid,a.score DESC
#37 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
SELECT cid,COUNT(cid) cc FROM sc
GROUP BY cid
HAVING cc>5
#38 检索至少选修两门课程的学生学号
SELECT sid, COUNT(cid) cc FROM sc
GROUP BY sid
HAVING cc>=2;
#39 查询选修了全部课程的学生信息
#方法一
SELECT s.*,COUNT(cid) cc FROM student s,sc
WHERE s.`SId`=sc.`SId`
GROUP BY sid
HAVING cc=3;
#方法二
SELECT student.* FROM sc ,student
WHERE sc.SId=student.SId
GROUP BY sc.SId
HAVING COUNT(*) = (SELECT DISTINCT COUNT(*) FROM course )
#40 查询各学生的年龄,只按年份来算
#使用NOW()获取当前时间,使用YEAR()将时间转换为年份
SELECT *, YEAR(NOW())-YEAR(sage) AS 年龄
FROM Student
#41 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
#TIMESTAMPDIFF()返回日期或日期时间表达式之间的整数差。
SELECT student.SId AS 学生编号,student.Sname AS 学生姓名,
TIMESTAMPDIFF(YEAR,sage,NOW()) AS 年龄
FROM Student
#获取当前时间Now()或者CURDATE()
SELECT student.SId AS 学生编号,student.Sname AS 学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) AS 年龄
FROM student
#42 查询本周过生日的学生
#WEEKOFYEAR()计算出当前日期所在周数
SELECT * FROM student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());
#43 查询下周过生日的学生
#WEEKOFYEAR()计算出当前日期所在周数
SELECT * FROM student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;
#44 查询本月过生日的学生
#MONTH()计算出当前日期所在月份
SELECT *FROM student
WHERE MONTH(student.Sage)=MONTH(CURDATE());
#45 查询下月过生日的学生
SELECT *FROM student
WHERE MONTH(student.Sage)=MONTH(CURDATE())+1;