SQL经典45题（完结）

最新推荐文章于 2021-06-18 16:16:53 发布

会飞的小蜗

最新推荐文章于 2021-06-18 16:16:53 发布

阅读量447

点赞数 1

分类专栏： SQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/YL3126/article/details/115932226

版权

SQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

首先创建数据库，以便练习：

/*
 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

会飞的小蜗

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL经典45题（完结）

首先创建数据库，以便练习：/* 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
复制链接

扫一扫