sql修炼手册（50道经典sql查询）

最新推荐文章于 2023-05-11 10:54:10 发布

先熬半个月

最新推荐文章于 2023-05-11 10:54:10 发布

阅读量864

点赞数

本文链接：https://blog.csdn.net/qq_35599414/article/details/109412416

版权

根据经典的sql场景题熟练sql编写能力

https://blog.csdn.net/fashion2014/article/details/78826299

创建数据库 sqltest（使用utf8mb4，而不是utf-8，在数据库中前者才是我们编码时的utf-8）

创建表：（java的double/long等精确度比较高的使用mysql中的decimal类型）

--学生表
CREATE TABLE `Student`(
	`s_id` VARCHAR(20),
	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
	`c_id`  VARCHAR(20),
	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
	`t_id` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
	`t_id` VARCHAR(20),
	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);

注意，在实际开发中不要设置外键（性能差还麻烦）。

向表里插入数据

--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

这样我们练习的数据库就弄好了

数据库中的基础知识

1、增：若不指定字段名，则添加的值的顺序应和字段在表中的顺序完全一致。

语法：INSERT INTO 表名（字段名1，字段名2，…） VALUES（值1，值2，…）；

举例：INSERT INTO student(id,name,grade)  VALUES(1,'zhangshan',98);

INSERT语句的其他写法

语法：INSERT INTO 表名 SET 字段名1=值1[,字段名2=值2，…]

举例：INSERT INTO student  SET id=4，name='zhaoliu',grade=72;

同时添加多条数据

语法：INSERT INTO 表名[(字段名1，字段名2，…)]  VALUES （值1，值2，…），（值1，值2，…），

　　　…（值1，值2，…）

举例：INSERT INTO student VALUES (5，‘lilei’,99),(6,'hanmeimei',87),(8,'poly',76);

2、删：

删除指定数据

举例：删除student表中的id值为7的记录

命令：DELETE  FROM student  WHERE id=7;

若 DELETE 语句中没有使用WHERE语句，则会将表中所有记录都删除。

语法：DELETE FROM 表名

举例：删除student表中的所有记录

命令：DELETE FROM student；

3、改：

举例：将student表中id值为1=记录，将其name字段的值改为‘caocao’，grade字段的值改为50。

命令：UPDATE student SET name=‘caocao’,grade=50  WHERE id=1;

在UPDATE 语句中若不使用WHERE 子句，则会将表中所有记录的指定字段都进行更新。

 举例：更新student表中全部记录，将grade字段都更新为80

命令：UPDATE student  SET grade=80；

4、查：对于查询，这是真实项目中比较常用且难度较高的一个操作

查询的操作比较多，主要结合后面的题目说明

mysql不区分大小写，不区分单引号和双引号

练习题目

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

该题需要分两步分析

1、先查出所有有01课程的学生（student和score进行join）

2、在1的情况下leftjoin成绩表找出科目成绩中01>02（对于1结果和score进行leftjoin）

我们要的结果：学生信息+分数：student.*+score.s_score

此时先执行第一步
select a.*,b.s_score as 01_score from 
student a join score b on a.s_id=b.s_id and b.c_id='01'
结果：

然后执行第二步（根据第一步的结果再进行联表）
select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a join score b on a.s_id=b.s_id and b.c_id='01' 
left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score
这里c.c_id = NULL考虑到如果该学生没有选到02科目，拿我们默认其科目1大于科目2（当然你也可以设定为没02科目则不比较）

最后结果：

----------------------------------------------------------------------------------该题也可以这么写
	select a.*,b.s_score as 01_score,c.s_score as 02_score from student a,score b,score c 
			where a.s_id=b.s_id 
			and a.s_id=c.s_id 
			and b.c_id='01' 
			and c.c_id='02' 
			and b.s_score>c.s_score
直接对三表进行条件筛选

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

这题和题1一样的思路就不写了

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
	student b 
	join score a on b.s_id = a.s_id
	GROUP BY b.s_id,b.s_name HAVING avg_score >=60;
其中round函数是将结果四舍五入一下，保留两位小数。

//这里涉及到一个知识点，在分组GROUP BY 后要加条件筛选时不能用where，要有having代替
//我们需要的结果：学生编号+学生名+学生的平均成绩
//student和score表进行联表/条件查询，计算平均成绩，大于60则显示

//并且注意：查出的结果值是可以拿到后面的条件筛选处进行使用的

也可以这样写：将联表改为条件筛选
select b.s_id,b.s_name,round(AVG(s.s_score),2) as avg_score from 
	student b ,score s
	where b.s_id=s.s_id 
	group by b.s_id,b.s_name 
	having avg_score>=60
最终结果：

经过几道练习题，现在是否有感觉联表和条件查询可以达到相同的效果

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的)

这里涉及到学生表和成绩表（可以通过联表/条件查询），这里和3不同的是多了个无成绩的
//其中涉及一个函数ifnull函数，如果为null则置为0，其他和第3题类似
select
    S.s_id,
    S.s_name,
    round(avg(ifnull(C.s_score,0)),2) as avg_score
from
    Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 60;
（也可以用条件查询等其他方法）

结果：

到这里，是否要注意真实语境中要记得边境值/null值的考虑

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

//这里要的到的信息，需要从表student、score中获取（联表/条件查询），涉及到的函数sum总和、count计数
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from 
	student a 
	left join score b on a.s_id=b.s_id
	GROUP BY a.s_id,a.s_name; 
结果：

6、查询"李"姓老师的数量

这题比较简单，涉及到模糊查询like，%代表一个/多个，_代表仅仅一个字符的通配符

MySQL LIKE 匹配字符的时候，默认情况下是不区分大小写的，如果在需要区分大小写的时候，可以加入BINARY操作符：

SELECT * FROM username WHERE LIKE BINARY '%azz%'

SELECT * FROM username WHERE LIKE BINARY '%Azz%'
select count(t_id) from teacher where t_name like '李%';
结果：

关于like的其他用法：

https://www.cnblogs.com/leeyongbard/p/9379255.html

https://blog.csdn.net/zhenwei1994/article/details/81913531

7、查询学过"张三"老师授课的同学的信息

//这里涉及较多的联表，根据张三找出教的课程t_id,根据t_id去课程表找相应的c_id，然后根据c_id去score表找到相应的s_id（学生id）,再根据学生id去学生表找到学生信息

teacher（t_id)——》course(c_id)——》score(s_id)——》student（*）（可以进行四表联查，也可子查询等）
select a.* from 
	student a 
	join score b on a.s_id=b.s_id where b.c_id in(
		select c_id from course where t_id =(
			select t_id from teacher where t_name = '张三'));
结果：

8、查询没学过"张三"老师授课的同学的信息

和第七道类似，改一下即可

SELECT * 
FROM student 
WHERE s_id not in(
	SELECT s.s_id from score s JOIN course c on s.c_id = c.c_id
		WHERE c.t_id in(
			SELECT t_id FROM teacher WHERE t_name = "张三"
		)
)

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

//分别查出两表中学过01课程+02课程,这里注意当需要对某个表连续联表两次查两次记录时，此时可以将其表复制为两份分别查。
//下面student表既要对score表进行联查找出课程表1，还有再次联查找出课程02，此时则可将score表复制成两份进行查询。
//又因为要同时满足两者都选择了，此时就要进行and连接。
select a.* from 
	student a,score b,score c 
	where a.s_id = b.s_id and b.c_id='01' and a.s_id = c.s_id  and c.c_id='02';
或者使用子查询
select a.* from 
	student a 
	where a.s_id in (select s_id from score where c_id='01' ) and a.s_id  in(select s_id from score where c_id='02')
结果：

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

这个和题目9类似，

select a.* from 
	student a 
	where a.s_id in (select s_id from score where c_id='01' ) and a.s_id not	in(select s_id from score where c_id='02')

11、查询没有学全所有课程的同学的信息

//先统计总课程数。再统计每个学生学的课程数（score表），如果小于则查询出学生信息（student）
//select后+group by+查询条件用到合计函数，此时要用having
//此时根据group by 联表中的s_id，根据学生号进行分组并且计算出其学习的课程数
select s.*, count(s1.c_id) from student s
left join Score s1 on s1.s_id=s.s_id
group by s.s_id
//再根据其课程数的count值和总课程数进行比较（将count(s1.c_id)拿来条件筛选）

最终sql：
select s.*, count(s1.c_id) from student s 
left join Score s1 on s1.s_id=s.s_id
group by s.s_id having count(s1.c_id)<(select count(*) from course)   
结果：

这里，对于分组，其目的是为了统计count，在分组时需要考虑的是你分组后count哪个字段进行计数

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

//01同学学习了哪些课程，然后其他同学谁有学习了上面的至少一门课。
//01同学学了哪些课程
select a.c_id from score a where a.s_id='01'
//其他同学
select b.*
from student b left join score s on
b.s_id=s.s_id where s.c_id in (01同学的所有课程)

此时将01同学的课程填入；
select b.*
from student b left join score s on
b.s_id=s.s_id where s.c_id in (select a.c_id from score a where a.s_id='01')
结果

此时发现多个重复的，则进行去重：
select distinct b.*
from student b left join score s on
b.s_id=s.s_id where s.c_id in (select a.c_id from score a where a.s_id='01')
也可以用两层子查询：

select * from student where s_id in(
select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id='01')
);

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

//这里涉及道一个函数GROUP_CONCAT(c_id)，意思根据c_id进行拼接，此时如果两者课程拼接后的字符串值相同，则为相同课程，但这里要注意，要对c_id进行排序，不然可能因为乱序而导致相同的课程拼接出来结果不同

//主要思路，将各个学生学习的课程排序后拼接起来，拼接的结果和01同学一样的就可以，这里要记得排除01本人，排序是一般要加上一个分隔符，以防不太数字结合会造成相同结果：例1、21、365 和12、13、65这样和起来就都是121365了。
SELECT
 Student.*
FROM Student
WHERE s_id IN (select s_id from score 
             GROUP BY s_id 
               HAVING s_id<>01 AND GROUP_CONCAT(c_id order by c_id desc) = (   #将01学生的课程拼接起来（根据c_id排序规则来）                               
                               SELECT GROUP_CONCAT(c_id order by c_id desc) FROM Score WHERE s_id = '01')
 );
结果：

具体GROUP_CONCAT用法：https://blog.csdn.net/u012620150/article/details/81945004

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select a.s_name from student a where a.s_id not in (
 select s_id from score where c_id = 
    (select c_id from course where t_id =(
     select t_id from teacher where t_name = '张三')));
这里要考虑一个老师可能教多门课程（用not in）。

//先找出张三教的所有课程，然后通过成绩表找到相应的学生，not in的’学生则为没学过

结果：

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（对于group by的使用）

//到这里，我们应该掌握一种解决复杂sql的方法，拆开再合并
//该题我们拆为1、查找一个学生的学号、姓名、平均成绩 2、查询出两门以上不合格的同学
//注意当需要聚合函数是都要用到group by ，因为聚合函数需要根据某些分组进行聚合计数
（你可以认为用到聚合函数count、avg、sum等时要用到group by）

注意：对于group by的理解看：https://blog.csdn.net/wqwqwqwq403/article/details/91048160（秒懂）

而这里还涉及到一个inner join内连接：https://blog.csdn.net/qq_38712932/article/details/82558409

count（1）和count（*）等等的区别：https://www.cnblogs.com/hider/p/11726690.html

1、查找一个学生的学号、姓名、平均成绩
select
a.s_id ,a.s_name,avg(s_score)
from student a left join score b
on a.s_id = b.s_id
group by a.s_id
2、查询出两门以上不合格的同学
//group by 与having配合使用时，功能为分组后过滤.这里根据s_id进行分组，分组后计数不及格超过\
//
select s_id
from score
where s_score<60
group by s_id
having count(1)>1
3合并起来

这里可以对查出来的结果c进行联表操作
select 
a.s_id ,a.s_name,avg(s_score)
from student a left join score b
on a.s_id = b.s_id 
inner join(

              select s_id
    from score 
    where s_score<60 
    group by s_id
    having count(1)>1
            ) c
            on c.s_id=a.s_id
group by a.s_id
结果：

也可以这样写：
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
	student a 
	left join score b on a.s_id = b.s_id
	where a.s_id in(
			select s_id from score where s_score<60 GROUP BY  s_id having count(1)>=2)
	GROUP BY a.s_id,a.s_name
可以用round四舍五入下。该题对于group by主要的使用场景：聚合函数、结合having、非常规使用。

可以看：https://www.cnblogs.com/studynode/p/9861522.html

16、检索"01"课程分数小于60，按分数降序排列的学生信息

select a.*,b.c_id,b.s_score from 
	student a,score b 
	where a.s_id = b.s_id and b.c_id='01' and b.s_score<60 ORDER BY b.s_score DESC;

这题比较简单就不说了

结果：

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

//先求出学生的平均成绩

 select a.s_id , round(SUM(s_score)/(select COUNT(1) from course ),2) as 平均分
 from score a group by a.s_id

//根据平均成绩这一列进行排序

 select a.s_id , round(SUM(s_score)/(select COUNT(1) from course ),2) as 平均分
 from score a group by a.s_id order by 平均分 desc;

//再去查找各个学生的各科成绩

 select a.s_id,
        (select s_score  from score where s_id=a.s_id and c_id='01') as 语文, 
         (select s_score from score where s_id=a.s_id and c_id='02') as 数学,
         (select s_score from score where s_id=a.s_id and c_id='03') as 英语,
         round(SUM(s_score)/(select COUNT(1) from course ),2) as 平均分 
            from score a  
            GROUP BY a.s_id ORDER BY 平均分 DESC;

结果：

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 --及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

//这里涉及score表和course表，基本都是对函数的运用
//其中涉及case when then else end 表达式，这里可以类比java中的switch函数。整个表达式只返回一个值
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
    ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
    ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
    ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
    ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
    from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name
对于case when then else end 表达式表达式使用：https://www.cnblogs.com/clphp/p/6256207.html

结果：

19、按各科成绩进行排序，并显示排名 -- mysql没有rank函数

19

20、查询学生的总成绩并进行排名

20

21、查询不同老师所教不同课程平均分从高到低显示

21

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

22

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

23

24、查询学生平均成绩及其名次

24

25、查询各科成绩前三名的记录 -- 1.选出b表比a表成绩大的所有组 -- 2.选出比当前id成绩大的小于三个的

25

26、查询每门课程被选修的学生数

select c_id,count(s_id) from score a GROUP BY c_id

这里比较简单不解析

27、查询出只有两门课程的全部学生的学号和姓名

//先找出count有两门课的学生id，然后根据学生id查找相应的学号姓名
        select s_id,s_name from student where s_id in(
                select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);
结果：

28、查询男生、女生人数

//根据性别进行分组，分组后count统计下
select s_sex,COUNT(s_sex) as 人数  from student GROUP BY s_sex
结果：

29、查询名字中含有"风"字的学生信息

//就是对like、%的运用
select * from student where s_name like '%风%';
结果：

30、查询同名同性学生名单，并统计同名人数

30

31、查询1990年出生的学生名单

select s_name from student where s_birth like '1990%'

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

//计算出平均成绩

//以平均成绩进行降序、课程名进行升序
    select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
结果：

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

//这里涉及到score和student表，先据学号进行分组计算出平均成绩，然后筛选平均成绩大于等于85
    select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
        left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85
结果：

34、

//涉及表student和score、course。先查出课程为数学的课程id，

//根据id查找学生的分数、姓名，并且以分数低于60为筛选信息
   select a.s_name,b.s_score from score b join student a on a.s_id=b.s_id 
    and b.c_id=( select c_id from course where c_name ='数学') 
    and b.s_score<60
或

select a.s_name,b.s_score from score b join student a on a.s_id=b.s_id where b.c_id=(
select c_id from course where c_name ='数学') and b.s_score<60

结果：

35、查询所有学生的课程及分数情况；

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

//只是简单的查询，其中涉及的表有course、score、student，进行联表查询即可
 select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
                left join student a on a.s_id=c.s_id where c.s_score>=70
结果：

37、查询不及格的课程

	select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id
			where a.s_score<60 
//进行成绩表和课程表的联查即可

结果：

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

//涉及成绩表、学生表，两表进行联查即可

        select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
            where a.c_id = '01'    and a.s_score>80

39、求每门课程的学生人数

//简单的统计分组

select count(*) from score GROUP BY c_id;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 查询老师id select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三'

-- 查询最高分（可能有相同分数） select MAX(s_score) from score where c_id='02'

最终查询所有信息
select a.*,b.s_score,b.c_id,c.c_name from student a
			LEFT JOIN score b on a.s_id = b.s_id
			LEFT JOIN course c on b.c_id=c.c_id
			where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')
			and b.s_score in (select MAX(s_score) from score where c_id='02')
结果：

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

    //筛选条件为学生id不同，成绩相同。
    //使用同一表进行对比/同表联查
    select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score

结果：

42、查询每门功成绩最好的前两名

-- 牛逼的写法

//同一课程，两个同表中两两成绩进行对比最后得出成绩高的人数，当计算到只剩下2个人时则就是签两名了
select a.s_id,a.c_id,a.s_score from score a 

        where (  select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 

ORDER BY a.c_id
结果：

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

//注意下排序的先后即可，先按人数进行排，再按课程号进行排

select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC

结果：

44、检索至少选修两门课程的学生学号

select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2

结果：

45、查询选修了全部课程的学生信息

查看总共有几门课程，然后看每个学生有成绩的有几门课程。进行比较即可

select * from student where s_id in( select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))

结果：

-46、查询各学生的年龄 -- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

//主要是对函数的使用

select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - (case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age from student;

结果：

47、查询本周过生日的学生

select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)

//分析同48题。

48、查询下周过生日的学生

select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth)

//WEEK函数，类似month函数，根据传进去的时间值找出是第几周

49、查询本月过生日的学生

select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)
这里就不多讲了，看50题

其中

//DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。语法：DATE_FORMAT(date,format)

实例

下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
结果类似：
Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

50、查询下月过生日的学生

具体month函数教程：https://www.yiibai.com/mysql/month.html

//涉及MONTH函数，month(data)此时会根据该data值获取到相应的月信息。（1-12的整数）
//要查询下月的生日，此时要先获取当前是几月，然后+1表示下个月
    select * from student where 
(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)
结果：（我当前是在11月）

先熬半个月

关注

0
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
sql修炼手册（50道经典sql查询）

根据经典的sql场景题熟练sql编写能力创建数据库 sqltest（使用utf8mb4，而不是utf-8，在数据库中前者才是我们编码时的utf-8）创建表：（java的double/long等精确度比较高的使用mysql中的decimal类型）--学生表CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NUL.
复制链接

扫一扫