首先创建数据库,以便练习:
/*
Navicat Premium Data Transfer
Source Server : mysql-3309
Source Server Type : MySQL
Source Server Version : 50640
Source Host : 127.0.0.1:3309
Source Schema : sqltest-45
Target Server Type : MySQL
Target Server Version : 50640
File Encoding : 65001
Date: 21/04/2021 08:20:36
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`CId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`TId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`CId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 60.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`Sage` datetime(0) NULL DEFAULT NULL,
`Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2017-12-30 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2017-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2018-01-01 00:00:00', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`TId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');
SET FOREIGN_KEY_CHECKS = 1;
节目开始:
# 1. 查询同时存在课程01 ,02 的学生
# 提示: 用两个子查询进行匹配,左边结果集是01课程的学生,右边结果集是02 课程学生,
# inner join用on 连接 学生ID,则可查询到 两侧同时有此学生的数据
select
*
FROM
(SELECT sc.*,student.Sname FROM sc AS sc inner join student AS student ON student.SId=sc.SId where CId=01 ) as a
INNER JOIN (SELECT * FROM sc where CId=02 ) as b on a.SId=b.SId
# 自关联方式,条件放在最后,匹配一个合并后的表条件
-- (select * from sc as sc INNER JOIN student as student on student.SId=sc.SId) as a
select * from sc as a
INNER JOIN
(select * from sc ) AS b on a.SId=b.SId
-- INNER JOIN student as student on student.SId=a.SId
where
a.CId=01 and b.CId=02
#2.查询存在01课程,但可能不存在02课程,不存在的用NULL 补齐;
select
*
from (select * from sc where CId=01) a
LEFT JOIN (SELECT * FROM sc where Cid=02) b
on a.SId=b.SId
select
*
from sc a
LEFT JOIN (SELECT * FROM sc where Cid=02) b
on a.SId=b.SId
where a.CId=01
#3.查询不存在01, 但存在02课程的同学
select
*
from (select * from sc where SId NOT IN (select SId FROM sc where CId='01')) a
INNER JOIN sc b ON a.SId=b.SId AND b.CId=02
#####################################################
SELECT
*
FROM sc a
WHERE
SId not in (select SId FROM sc WHERE CId ='01' )
and CId='02'
#2-1 查询平均成绩大于60 分的同学 名称、编号、平均成绩
select
a.SId,
a.Sname,
b.avg_score
from
student as a
INNER JOIN
(select
SId,
AVG(score) as avg_score
from sc
GROUP BY SId HAVING AVG(score) > 60) b
ON a.SId=b.SId
#——————————————————————————————————————————分割线————————————————————————————————————————
#3.1 查询存在成绩表中的学生信息
#先来看第一种方式,这种方式虽说也能查出来相关数据,但是由于MySQL有自动优化查询语句的功能没有报错,
#不证明其他数据库不报错误。 group by 作为分组条件,按照要求只要查询分组字段,其他(非分组)字段必须要以聚合
#函数的形式存在
SELECT
b.*
FROM
sc AS a
LEFT JOIN student as b on a.SId=b.SId
GROUP BY a.SId
#第二种方式,子查询方式
select
*
from student WHERE SId IN
(select SId FROM sc GROUP BY SId)
#第三种方式,将分组后的学生成绩当主表,左连接匹配学生信息
select
b.*
from
(select SId FROM sc GROUP BY SId) AS a
left JOIN student as b on a.SId= b.SId
#——————————————————————————————————————————分割线————————————————————————————————————————
#4.1 查询所有同学的学生编号、姓名、选课数、总成绩
#第一种,先查询所有选课数、总成绩
select
a.SId,
a.Sname,
b.count_cid,
b.sum_score
from
student a
LEFT JOIN
(select
sid,
count(CId) as count_cid,
SUM(score) as sum_score
from
sc
GROUP BY SId) AS b
ON
a.SId=b.SId
# 第二种 ,先查询出所有需要的信息然后再进行分组
select
a.SId,
a.Sname,
count(b.CId) as cid_count,
SUM(b.score) as score_sum
from student as a
left join sc as b
on a.SId = b.Sid
GROUP BY a.SId,a.Sname
#——————————————————————————————————————————分割线————————————————————————————————————————
#5.查询李姓老师数量 (这个过于简单~~~)
select count(1) as counts_t from teacher where Tname like '李%'
#——————————————————————————————————————————分割线————————————————————————————————————————
#6.查询学过张三老师课的学生信息
# 首先用子查询(select c.CId from teacher as t INNER JOIN course as c ON t.TId=c.TId WHERE t.Tname='张三')
# 查出 此教师都教了什么课,然后把结果当做成绩表的条件,用inner join 来连接查询学生信息,GROUP BY 去重即可
select
s.Sname,
s.SId
FROM
sc as sc
INNER JOIN student s ON sc.SId=s.SId where sc.CId in
(select c.CId from teacher as t INNER JOIN course as c ON t.TId=c.TId WHERE t.Tname='张三')
GROUP BY s.Sname,s.SId
#——————————————————————————————————————————分割线————————————————————————————————————————
# 7.查询没有学完所有课程的学生信息
select
s.SId,
s.Sname,
COUNT(sc.CId) as count_cid
from
sc as sc
INNER JOIN student as s on sc.SId=s.SId
GROUP BY
s.SId,
s.Sname
HAVING count_cid < (SELECT COUNT(1) FROM course)
#——————————————————————————————————————————分割线————————————————————————————————————————
# 8.查询至少一门课与 赵雷 同学 相同的课程 的同学名单
# 先查询出赵雷学了什么课, 其他同学 再用赵雷学的课程的CID 当做条件,用 IN 去匹配
select
*
from
sc as sc
INNER JOIN student as s ON sc.SId = s.SId
WHERE
sc.CId
IN
(select sc.CId FROM sc as sc INNER JOIN student AS s ON sc.SId = s.SId WHERE s.Sname='赵雷')
AND Sname!='赵雷'
GROUP BY sc.SId
#**********************
select
DISTINCT *
from
sc as sc
INNER JOIN student as s ON sc.SId = s.SId
WHERE
sc.CId
IN
(select sc.CId FROM sc as sc INNER JOIN student AS s ON sc.SId = s.SId WHERE s.Sname='赵雷')
AND Sname!='赵雷'
GROUP BY sc.SId
#——————————————————————————————————————————分割线————————————————————————————————————————
# 9.查询出和“赵雷”学习的课程完全一样的同学都是谁
# 分析:需要满足两个条件: 首先课程在赵雷的学习课程之内,然后学习的课程数量跟赵雷一致
select
SId
from sc AS sc
WHERE
sc.CId
IN
(select sc.CId from sc as sc INNER JOIN student as s ON sc.SId=s.SId where s.Sname='赵雷')
AND sc.SId!='01'
GROUP BY sc.SId
HAVING COUNT(1) = (select COUNT(1) from sc as sc INNER JOIN student as s ON sc.SId=s.SId where s.Sname='赵雷')
#——————————————————————————————————————————分割线————————————————————————————————————————
# 10. 查询没学过张三老师任何一门课的学生
# 先查出学过张三老师课的学生,然后用 NOT IN 排除一下即可
select
sc.SId,
s.Sname
from
sc as sc
INNER JOIN student as s on s.SId=sc.SId
where sc.SId NOT IN
(select
sc.SId
from
sc as sc
where sc.CId in
(select c.CId from teacher as t INNER JOIN course as c ON t.TId=c.TId where t.Tname='张三'))
GROUP BY sc.SId
#——————————————————————————————————————————分割线————————————————————————————————————————
# 11.查询两门及其以上不及格的学生的学号,和平均成绩
# 先查询出两门课以上不及格的都有谁
#-- select SId FROM sc WHERE score<60 GROUP BY SId HAVING count(1)>1
select
SId,
AVG(score) as score_avg
from
sc
where SId in (select SId FROM sc WHERE score<60 GROUP BY SId HAVING count(1)>1 )
GROUP BY SId
#——————————————————————————————————————————分割线————————————————————————————————————————
# 12.检索01课程小于60分的学生信息 并按照降序排列
select
sc.SId,s.Sname,sc.score
from
sc as sc
INNER JOIN student as s ON sc.SId=s.SId
where
sc.score<60 and sc.CId=01
ORDER BY sc.score desc
#——————————————————————————————————————————分割线————————————————————————————————————————
# 13.学生按照成绩从高到低排序,并显示其平均成绩
#先查询平均成绩,在跟学生成绩做关联即可
select
sc.SId,
t.Sname,
sc.score,
b.score_avg
from
sc as sc
INNER JOIN student AS t on sc.SId=t.SId
LEFT JOIN
(select
SId,
AVG(score) AS score_avg
from
sc
GROUP BY SId) as b
on sc.SId=b.SId
#——————————————————————————————————————————分割线————————————————————————————————————————
# 14. 统计各门课程的最高分,最低分,平均分,选修人数,及格率,中等率,优良率,优秀率:、
# 其中 及格率>=60 , 中等率 [70-80) , 中等率 [80-90), 优良率 [80-90), 优秀率 [90-100]
SELECT
s.CId,
t.Cname,
MAX(s.score) as 最高分,
MIN(s.score) as 最低分,
AVG(s.score) as 平均分,
(SUM(CASE WHEN s.score>60 THEN 1 ELSE 0 END)/COUNT(1)) AS 及格率,
(SUM(CASE WHEN s.score>=70 AND s.score<80 THEN 1 ELSE 0 END)/COUNT(1)) AS 中等率,
(SUM(CASE WHEN s.score>=80 AND s.score<90 THEN 1 ELSE 0 END)/COUNT(1)) AS 优良率,
(SUM(CASE WHEN s.score>=90 AND s.score<=100 THEN 1 ELSE 0 END)/COUNT(1)) AS 优秀率
FROM
sc AS s
INNER JOIN course as t on t.CId=s.CId
GROUP BY s.CId,t.Cname
—————————————————————————————————————分割线————————————————————————————————————————
# 15.查询学生各科成绩及其排名,遇到重复成绩不考虑
-- select @rank:=1 as rank;
-- select rank;
select
Sid as 学生ID,
Cid as 课程,
score as 成绩,
#让排名依次累加
@rank:=@rank+1 as 排名
from sc,
#将变量的初始值设为0 ,必须设置别名 查询的字段里面不知道是谁了
(SELECT @rank:=0) as t
ORDER BY score desc
#15-1 : 查询学生各科成绩及其排名,遇到重复成绩并列排名
select
Sid as 学生ID,
Cid as 课程,
-- score as 成绩,
CASE WHEN (@sco=score) THEN
@rank:=@rank
ELSE
@rank:=@rank+1
END as 排名,
@sco:=score as 成绩
from sc,
#将变量的初始值设为0 ,必须设置别名 查询的字段里面不知道是谁了
(SELECT @rank:=0, @sco:=null) as t
ORDER BY score desc
# 16.查询学生总成绩并排名,总分一样的合并排名
SET @sco:=0,@rank:=0 ;
select
a.SId,
a.sc_all,
@rank:=if(@sco=a.sc_all,@rank,@rank+1) as rank,
@sco:=a.sc_all
from
(SELECT SId,sum(score) as sc_all FROM sc GROUP BY SId ORDER BY sc_all DESC) AS a;
#17.分组统计 学生所学课程的各分数段占比
#[100-85] [85-70] [70-60] [60-0] 的各个课程占比数
select
s.CId,
c.Cname,
(SUM(CASE WHEN s.score>=85 THEN 1 ELSE 0 END)/COUNT(1)) AS 【85到100占比】,
(SUM(CASE WHEN s.score<85 AND s.score>=70 THEN 1 ELSE 0 END)/COUNT(1)) AS 【70到85占比】,
(SUM(CASE WHEN s.score<70 AND s.score>=60 THEN 1 ELSE 0 END)/COUNT(1)) AS 【60到70占比】,
(SUM(CASE WHEN s.score<60 THEN 1 ELSE 0 END)/COUNT(1)) AS 【60以下占比】
from
sc as s
INNER JOIN course as c ON s.CId = c.Cid
GROUP BY s.Cid,c.Cname
#18.查询各科成绩前三名的记录
# 思路: 前三名转化为 若大于此成绩的数量少于3即为前三名
EXPLAIN
-- show variables like '%log_queries_not_using_indexes%';
select
a.*
from
sc as a
where
(select count(1) from sc b where a.CId=b.CId and b.score>a.score)<3
ORDER BY a.CId DESC
# 19.查询每个课程的学生数量
select
a.CId,
c.Cname,
COUNT(1) as 课程学生数
from
sc a
INNER JOIN course as c ON a.CId=c.CId
GROUP BY a.CId,c.Cname
# 20.查询出只选修两门课程的学生姓名和学号
select
a.SId,
s.Sname
from
sc a
INNER JOIN student as s ON a.SId=s.SId
GROUP BY a.SId
HAVING COUNT(1)=2
# 21.查询男生女生人数
SELECT
Ssex,
COUNT(1) as 本班男女数
from
student
GROUP BY Ssex
# 22.查询名字中带有“风”的学生信息
select
*
from
student
where Sname LIKE "%风%"
# 23. 统计同名同性别的学生信息,和数量
select
a.Sname,
a.Ssex,
COUNT(a.Sname) as 同名同性别学生数量
from
student as a
inner join student as b on a.Sname=b.Sname and a.Ssex=b.Ssex and a.SId!=b.SId
GROUP BY a.Sname,a.Ssex
# 24.查询1990年出生的学生
-- select YEAR("2020-09-09")
select
*
from
student
where YEAR(Sage)=1990
# 25.查询每门课程的平均成绩并且按照成绩的降序排序,如果成绩相同按照课程编号的升序排序
select
CId,
AVG(score) as avg_score
from
sc
GROUP BY CId
ORDER BY avg_score desc,CId asc
# 26.查询平均成绩大于85分的学生信息和平均成绩
select
a.SId,
b.Sname,
AVG(a.score) as 平均成绩
from
sc as a
INNER JOIN student as b on a.SId=b.SId
GROUP BY a.SId
HAVING 平均成绩>85
# 27.查询课程为“数学”。且成绩低于60分的学生信息与分数
select
*
from
sc a
INNER JOIN course b ON a.CId=b.CId
where b.Cname="数学" and a.score < 60
# 28. 查询所有学生的学的课程情况及其分数
select
*
from
student as a
LEFT JOIN sc b ON a.SId=b.SId
LEFT JOIN course c on b.CId=c.CId
# 29. 查询任何一门成绩在70分以上的学生信息和分数
select
*
from
sc as a
INNER JOIN student b ON a.SId=b.SId
WHERE a.score>70
# 30.查询不及格课程
select
*
from
sc as a
INNER JOIN course b ON a.CId=b.CId
where a.score<60
# 31.查询课程编号为01并且成绩在80分以上的学生信息
select
*
from
sc a
INNER JOIN student b on a.SId=b.SId
where a.score>=80 and a.CId='01'
----------------------------------------------------------------------------------------
# 32.求每门课程的学生人数
select
b.Cname,
COUNT(1) as 学生数
from
sc as a
INNER JOIN course as b on a.CId=b.CId
GROUP BY a.CId
------------------------------------------------------------------------------------
# 33. 成绩不重复情况下,查询选修“张三”老师课程成绩最高的学生
# 第一种:
-- EXPLAIN
select
*,
MAX(a.score)
from
sc a
left join student b on a.SId=b.SId
left join course c on a.CId=c.CId
LEFT JOIN teacher d on c.TId=d.TId
where d.Tname="张三";
# 第二种:
-- EXPLAIN
select
*
from
sc a
left join student b on a.SId=b.SId
left join course c on a.CId=c.CId
LEFT JOIN teacher d on c.TId=d.TId
where d.Tname="张三"
ORDER BY a.score desc
LIMIT 1;
------------------------------------------------------------------------------------
# 34. 成绩有重复情况下,查询选修“张三”老师课程成绩最高的学生
select
*
FROM
(select
m.*,
CASE WHEN @score=score THEN @rank
WHEN @score:=score THEN
@rank:=@rank+1
END as rn
from
(select
a.SId,
a.score,
b.Sname,
c.CId,
d.Tname
from
sc a
left join student b on a.SId=b.SId
left join course c on a.CId=c.CId
LEFT JOIN teacher d on c.TId=d.TId
where d.Tname="张三") AS m, (select @score:=null,@rank:=0) as t) a
where a.rn=1
---------------------------------------------------------------------------------
# 35. 查询课程不同但成绩相同的学生信息和成绩
select
*
from
sc a
INNER JOIN sc b on a.SId=b.SId
where a.CId!=b.CId and a.score=b.score
GROUP BY a.SId
# 36.查询每门课程成绩最好的前两名学生信息
select
SId,
rank,
score
from
(select
sc.*,
@rank:=if(@c_id=CId,if(@sco=score,@rank,@rank+1),1) as rank,
@sco:=score as sco,
@c_id:=CId as c_id
from
sc,(select @rank:=0,@sco:=0,@c_id:=0) b
ORDER BY CId,score DESC) as c
where c.rank<3
#################################################################
第一种:使用case when判断
select X
case when 条件 then 返回值
else 返回值 end as Y, #也可以是两个或多个when语句省略else
#变量值更新
from 表名,(select 给变量赋初值)表名
order by ... desc/asc;
第二种:使用if( , , )语句判断
select X,
@变量名:=if(条件,条件成立返回值,条件不成立返回值) as Y,
#变量值更新
from 表名,(select 给变量赋初值)表名
order by ... desc/asc;
例如:按各科成绩进行排序,并显示排名, Score 重复时合并名次
第一种写法:
select Sid,Cid,Score, #分别表示学生id,课程id,课程分数
case when @sco=Score then@rank
else @rank:=@rank+1 end as rn,
@sco:=Score
from SC,(select @rank:=0,@sco:=null)t
order by Score desc;
第二种写法:
select Sid,Cid,Score,
@rank:= if(@sco=Score,@rank,@rank+1) as rn,
@sco:=Score
from SC,(select @rank:=0,@sco:=null)t
order by Score desc;
""'
给变量赋值:@变量名:=
判断变量是否相等:@变量名=
""'
# 37.统计每门课程的选修人数(超过5人的课程才统计)
select
*,
count(1) as c_count
from
sc
GROUP BY CId
HAVING COUNT(CId) >5
# 38.检索至少选修两门课程的学生信息
SELECT
SId,
count( Cid ) AS 课程数
FROM
sc
GROUP BY
Sid
HAVING
count( CId ) >= 2
# 39.查询选修了全部课程的学生信息
select
*
from
sc
GROUP BY SId
HAVING COUNT(CId)=(SELECT count(1) from course)
# 40.查询个学生年龄,只按年份做划分
-- select year("2021-02-03")
-- SELECT now();
-- select curdate();
select
SId,
Sage,
Sname,
Ssex,
YEAR(now())-YEAR(Sage) 实际年龄
from
student
ORDER BY 实际年龄 desc
------------------------------------分割线-----------------------------------
# 41.按照出生日期来算,当前月日<出生年月的月日规则,年龄减一
-- 日期相减函数
-- select TIMESTAMPDIFF(year,"2020-02-03","2021-03-03")
--
-- select TIMESTAMPDIFF(year,"2020-02-03","2022-03-03")
-- 日期函数
-- select now()
--
-- select curdate()
select
Sname,
TIMESTAMPDIFF(year,Sage,now()) as 当前年龄
from
student
------------------------------------分割线-----------------------------------
# 42.查询本周过生日的学生
-- 当前日期是第几周?
-- select week("2021-03-07");
select
*,
WEEK(Sage),
WEEK(NOW())
from
student
where WEEK(Sage)=WEEK(now())
------------------------------------分割线-----------------------------------
# 43.查询下周过生日的学生
-- 当前日期是第几周?
-- select week("2021-03-07");
select
*,
WEEK(Sage),
WEEK(NOW())
from
student
where WEEK(Sage)=WEEK(now())+1
------------------------------------分割线-----------------------------------
# 44.查询本月过生日的学生
-- 当前日期是第几周?
-- select month("2021-03-07");
select
*,
month(Sage),
month(NOW())
from
student
where month(Sage)=month(now())
------------------------------------分割线-----------------------------------
# 45.查询下月过生日的学生
-- select month("2021-03-07");
select
*,
month(Sage),
month(NOW())
from
student
where month(Sage)=month(now())+1