MySql经典练习 50道题目+ 答案讲解

于 2022-09-06 14:47:56 发布
阅读量159 收藏 2
点赞数 1
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_54342458/article/details/126725115
版权

MySQL练习

50道经典SQL练习题

数据表介绍

  • 1.学⽣表 Student(SId,Sname,Sage,Ssex)

    • SId 学⽣编号

    • Sname 学⽣姓名

    • Sage 出⽣年⽉

    • Ssex 学⽣性别

  • 2.课程表 Course(CId,Cname,TId)

    • CId 课程编号

    • Cname 课程名称

    • TId 教师编号

  • 3.教师表 Teacher(TId,Tname)

    • TId 教师编号

    • Tname 教师姓名

  • 4.成绩表 SC(SId,CId,score)

  • SId 学⽣编号

  • CId 课程编号

  • score 分数

建表语句

  • 学⽣表 Student

    create table Student(
    SId varchar(10),
    Sname varchar(10),
    Sage datetime,
    Ssex varchar(10)
);

  • 课程表 Course

    create table Course(
    CId varchar(10),
    Cname nvarchar(10),
    TId varchar(10)
);

  • 教师表 Teacher

    create table Teacher(
    TId varchar(10),
    Tname varchar(10)
);

  • 成绩表 SC

    create table SC(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
);
插入数据

  • 学⽣表 Student

    -- 学生表 Student
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

    -- 科⽬表 Course 
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03');

  • 教师表 Teacher

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

  • 成绩表 SC

    -- 成绩表 SC 
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 "课程成绩⾼的学⽣的信息及课程分数
SELECT  tt1.SId
        ,tt2.Sname
        ,tt3.CId
        ,tt3.score
from (
    select	t1.SId
    from(
        select	SId
                ,CId
                ,score 
        from SC 
        where CId = '01'
    ) t1
    left join(
        select	SId
                ,CId
                ,score 
        from SC 
        where CId = '02'
    ) t2
    on t1.Sid = t2.Sid
    where t1.Score > t2.Score
) tt1 join Student tt2 on tt1.Sid = tt2.Sid
join SC tt3 on tt1.Sid = tt3.Sid;
2.查询同时存在" 01 “课程和” 02 "课程的情况
SELECT	t1.SId
from(
	select	SId
	from SC 
	where CId = '01'
) t1 join (
	select SId
	from SC 
	where CId = '02'
) t2 on t1.SId = t2.SId;
3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT	t1.SId
        ,t1.CId
        ,t1.score
        ,t2.CId as t2CId
        ,t2.score as t2Score
from(
    select	SId
            ,CId
            ,score
    from SC 
    where CId = '01'
) t1 left join (
    select 	SId
            ,CId
            ,score
    from SC 
    where CId = '02'
) t2 on t1.SId = t2.SId;
4.查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT	t1.SId
        ,t1.CId
        ,t1.score
        ,t2.CId as t2CId
        ,t2.score as t2Score
from(
    select	SId
            ,CId
            ,score
    from SC 
    where CId = '02'
) t1 left join (
    select 	SId
            ,CId
            ,score
    from SC 
    where CId = '01'
) t2 on t1.SId = t2.SId;
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
SELECT  t1.SId
        ,t2.SName
        ,t1.avg_score
from(
    SELECT  SId
            ,round(AVG(score),2) as avg_score
    FROM SC
    GROUP BY SId
    HAVING avg_score>=60
) t1 join Student t2
on t1.SId = t2.SId

PS：round(m,n)函数将字段m的查询结果保留小数点n位

6.查询在 SC 表存在成绩的学⽣信息
SELECT  t1.SId
        ,t2.SName
from (
    SELECT distinct SId
    from SC
) t1 join Student t2
on t1.SId = t2.SId

PS：DISTINCT 将后面的字段的查询结果进行去重。

7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select  t2.SId
        ,t2.SName
        ,t1.cnt
        ,t1.sum_score
from(
    select  SId
            ,count(CId) as cnt
            ,sum(score) as sum_score
    from SC 
    group by SId
) t1 right join
Student t2 on t1.SId = t2.SId
8.查询「李」姓⽼师的数量
SELECT COUNT(Tname)
FROM Teacher
WHERE Tname LIKE '李%';
9.查询学过「张三」⽼师授课的同学的信息
select  t2.SId
        ,t2.SName
from (
    select  SId
    from SC
    where CId =(
        select  CId
        from Course
        where TId = (
            select  TId
            from Teacher
            where Tname = '张三'
        )
    )
) t1 join Student t2 on t1.SId = t2.SId;
10.查询没有学全所有课程的同学的信息
select  tt1.SId
        ,tt1.SName
        ,count(tt1.CId) as cnt
from (
    select  t1.SId
            ,t1.CId
            ,t2.SName
    from SC t1 join Student t2
    on t1.SId = t2.SID
) tt1 group by tt1.SId,tt1.SName
Having cnt < (select  count(*) from Course)
11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
SELECT	DISTINCT t1.SId
				,t2.Sname
from SC t1 join Student t2 on t1.SId = t2.SId
where t1.SId!='01' and t1.CId in (SELECT cid FROM SC WHERE sid = '01')
12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT	ttt1.SId
        ,ttt2.SName
from(
    SELECT	tt1.SId
            ,count(tt1.t1CId) as t1Cnt
            ,count(tt1.t2CId) as t2Cnt
    from(
        SELECT	t1.CId as t1CId
                ,t2.SId
                ,t2.CId as t2CId
        FROM (
            SELECT 	SId
                    ,CId 
            FROM SC WHERE SId = '01'
        ) t1 right join(
            SELECT 	SId
                    ,CId 
            FROM SC WHERE SId != '01'
        ) t2 on t1.CId = t2.CId
        order by t1.SId,t2.SId
    ) tt1 group by tt1.SId
    HAVING t1Cnt = t2Cnt and t2Cnt = (SELECT  count(*) from SC where Sid = '01')
) ttt1 join Student ttt2 on ttt1.SId = ttt2.SId;
13.查询没学过"张三"⽼师讲授的任⼀⻔课程的学⽣姓名
select  SId
        ,SName
from Student
where SId not in (
    select  SId
    from SC
    where CId in (
        select  CId
        from Course
        where TId = (
            select  TId
            from Teacher
            where Tname = '张三'
        )
    )
);
14.查询两⻔及其以上不及格课程的同学的学号，姓名及其平均成绩
select  tt2.SId
        ,tt2.SName
        ,tt1.avg_score
from (
    select  t1.SId
            ,count(t1.CId) as cnt
            ,avg(t1.score) as avg_score
    from(
        select  SId
                ,CId
                ,score
        from SC
        where score < 60
    ) t1 group by SId having cnt >=2
) tt1 join Student tt2 on tt1.SId = tt2.SId;
15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
select  t2.SId
        ,t2.Sname
        ,t1.score
from SC t1
join Student t2
on t1.SId = t2.SId
and t1.CId = '01' and t1.score < 60
order by t1.score desc;
16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩
select  tt1.SId
        ,tt1.avg_score
        ,tt2.score as 'Chinese'
        ,tt3.score as 'Math'
        ,tt4.score as 'English'
from (
    select  t1.SId
            ,avg(t1.score) as avg_score
    from SC t1
    group by t1.SId
) tt1 
left join (select SId,score from SC where CId = '01') tt2 on tt1.SId = tt2.SId
left join (select SId,score from SC where CId = '02') tt3 on tt1.SId = tt3.SId
left join (select SId,score from SC where CId = '03') tt4 on tt1.SId = tt4.SId
order by tt1.avg_score desc;
17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列
select  t1.CId
        ,t2.Cname
        ,max(score) as max_score
        ,min(score) as min_score
        ,avg(score) as avg_score
        ,concat(round(sum(if(score>=60,1,0))*100/count(score),2),"%") as 及格率
        ,concat(round(sum(if(score>=70 and score<80,1,0))*100/count(score),2),"%") as 中等率
        ,concat(round(sum(if(score>=80 and score<90,1,0))*100/count(score),2),"%") as 优良率
        ,concat(round(sum(if(score>=90,1,0))*100/count(score),2),"%") as 优秀率
        ,count(score) as num
from SC t1 join Course t2 on t1.CId = t2.CId
group by t1.CId,t2.Cname
order by num desc,CId asc

concat()用作字符串拼接

concat(‘1’ , ’+’ , ’1’ , ‘=’ , ‘2’) ==> 1+1=2

case when 条件判断

CASE WHEN sc.score>=60 THEN 1 ELSE 0 END

意思是sc.score>=60这个条件成立时，返回1，不成立时返回0

18.按各科平均成绩进⾏排序，并显示排名，Score 重复时保留名次空缺
SET @i := 0; -- 定义一个变量
SELECT  t1.CId
        ,t1.avg_score
        ,@i := @i + 1 as rank
from (
    select  CId
            ,avg(score) as avg_score
    from SC 
    group by CId
    order by avg_score desc
) t1;
19.按各科平均成绩进⾏排序，并显示排名， Score 重复时不保留名次空缺
SET @i := 0;
SELECT  s.cid
        ,s.avg_sc AS '平均分'
        ,@i := @i + 1 AS '排名' 
FROM (
    SELECT  SC.`CId`
            ,AVG(SC.`score`) AS avg_sc 
    FROM SC 
    GROUP BY SC.`CId` 
    ORDER BY avg_sc DESC
)s;
20.查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺

1 2 2 4 5 6

SET @i := 0;
SET @j := 0;
SET @p := 0;
SET @q := 0;
select  t1.SId
        ,t1.sum_score
        ,@j := @j + 1
        ,@p := t1.sum_score
        ,if(@p=@q,@i,@i := @j) as rank
        ,@q :=@p
from (
    select  SId
            ,sum(score) as sum_score
    from SC
    group by SId
    order by sum_score desc
) t1;
21.查询学⽣的总成绩，并进⾏排名，总分重复时不保留名次空缺

1 2 2 2 3 4 5

SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT  t1.SId
        ,t1.sum_score
        ,@p := t1.sum_score
        ,if(@p=@q,@i,@i := @i+1) as dense_rank
        ,@q :=@p

from (
    select  SId
            ,sum(score) as sum_score
    from SC
    group by SId
    order by sum_score desc
) t1;
22.统计各科成绩各分数段⼈数：课程编号，课程名称，[100-85)，[85-70)，[70-60)，[60-0)及所占百分⽐
select  t1.CId
        ,t2.Cname
        ,concat(round(sum(if(score<=100 and score>85,1,0))*100/count(score),2),"%") as '[100-85)'
        ,concat(round(sum(if(score<=85 and score>70,1,0))*100/count(score),2),"%") as '[85-70)'
        ,concat(round(sum(if(score<=70 and score>60,1,0))*100/count(score),2),"%") as '[70-60)'
        ,concat(round(sum(if(score<=60 and score>0,1,0))*100/count(score),2),"%") as '[60-0)'
from SC t1 join Course t2 on t1.CId = t2.CId
group by t1.CId,t2.Cname;
23.查询各科成绩前三名的记录
SELECT  a.* 
FROM (
    SELECT  stu.`SId`
            ,stu.`Sname`
            ,sc.`CId`
            ,c.`Cname`
            ,sc.`score`
    FROM student stu 
    JOIN sc ON sc.`SId` = stu.`SId`
    JOIN course c ON c.`CId` = sc.`CId` AND sc.`CId` = '01'
    ORDER BY sc.`score` DESC
    LIMIT 0,3
) a 
UNION ALL
SELECT  b.* 
FROM (
    SELECT  stu.`SId`
            ,stu.`Sname`
            ,sc.`CId`
            ,c.`Cname`
            ,sc.`score`
    FROM student stu 
    JOIN sc ON sc.`SId` = stu.`SId`
    JOIN course c ON c.`CId` = sc.`CId` AND sc.`CId` = '02'
    ORDER BY sc.`score` DESC
    LIMIT 0,3
) b
UNION ALL
SELECT  c.* 
FROM (
    SELECT  stu.`SId`
            ,stu.`Sname`
            ,sc.`CId`
            ,c.`Cname`
            ,sc.`score`
    FROM student stu 
    JOIN sc ON sc.`SId` = stu.`SId`
    JOIN course c ON c.`CId` = sc.`CId` AND sc.`CId` = '03'
    ORDER BY sc.`score` DESC
    LIMIT 0,3
) c;

PS:UNION用的比较多union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复

1、UNION 的语法如下：

[SQL 语句 1]

UNION

[SQL 语句 2]

2、UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

24.查询每⻔课程被选修的学⽣数
SELECT  SC.`CId`
        ,COUNT(SC.`CId`) as cnt
FROM SC 
GROUP BY SC.`CId`;
25.查询出只选修两⻔课程的学⽣学号和姓名
select  t1.SId
        ,t2.SName
from (
    SELECT  SId
            ,COUNT(CId) as cnt
    FROM SC
    GROUP BY SId
    having cnt = 2
) t1 join Student t2 on t1.SId = t2.SId;
26.查询男⽣、⼥⽣⼈数
SELECT  st.ssex
        ,COUNT(1) 
FROM Student st 
GROUP BY st.ssex;
27.查询名字中含有「⻛」字的学⽣信息
SELECT * FROM Student WHERE Student.`Sname` LIKE '%风%';
28.查询同名同性学⽣名单，并统计同名⼈数
SELECT  SName
        ,SSex
        ,count(1) as cnt
from Student
group by SName,SSex
having cnt > 1;
29.查询 1990 年出⽣的学⽣名单
SELECT  s.* 
FROM Student s 
WHERE s.`Sage` BETWEEN '1990-1-1' AND '1990-12-31';
30.查询每⻔课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT  SC.CId
        ,c.Cname
        ,AVG(SC.score) AS avg_score
FROM SC 
JOIN Course c ON SC.CId = c.CId 
GROUP BY SC.CId,c.Cname
ORDER BY avg_score DESC,SC.CId;
31.查询平均成绩⼤于等于 85 的所有学⽣的学号、姓名和平均成绩
SELECT  s.`SId`
        ,s.`Sname`
        ,AVG(sc.`score`) AS '平均成绩'
FROM student s
JOIN sc ON sc.`SId` = s.`SId`
GROUP BY s.`SId`,s.`Sname`
HAVING 平均成绩>=85;
32.查询课程名称为「数学」，且分数低于 60 的学⽣姓名和分数
select  s.`SId`
        ,s.`Sname`
        ,sc.`score`
from student s
join sc on s.`SId` = sc.`SId`
join course c on c.`CId` = sc.`CId` 
and c.`Cname` = '数学' 
and sc.`score`<60;
33.查询所有学⽣的课程及分数情况（存在学⽣没成绩，没选课的情况）
SELECT  s.`SId`
        ,s.`Sname`
        ,s1.score AS '语文'
        ,s2.score AS '数学'
        ,s3.score AS '英语' 
FROM student s 
LEFT JOIN (
    SELECT * 
    FROM sc 
    WHERE sc.`CId` = '01'
) s1 ON s1.sid = s.`SId`
LEFT JOIN (
    SELECT * 
    FROM sc 
    WHERE sc.`CId` = '02'
) AS s2 ON s2.sid = s.`SId`
LEFT JOIN (
    SELECT * 
    FROM sc 
    WHERE sc.`CId` = '03'
) AS s3 ON s3.sid = s.`SId`;
34.查询任何⼀⻔课程成绩在 70 分以上的姓名、课程名称和分数
SELECT  stu.`SId`
        ,stu.`Sname`
        ,sc.`CId`
        ,c.cname
        ,sc.`score`
FROM student stu 
JOIN sc ON stu.`SId` = sc.`SId` AND sc.`score` > 70
JOIN course c ON c.`CId` = sc.`CId`;
35.查询不及格的课程
SELECT  stu.`SId`
        ,stu.`Sname`
        ,sc.`CId`
        ,c.cname
        ,sc.`score`
FROM student stu 
JOIN sc ON stu.`SId` = sc.`SId` AND sc.`score` < 60
JOIN course c ON c.`CId` = sc.`CId`;
36.查询课程编号为 01 且课程成绩在 80 分以上的学⽣的学号和姓名
SELECT  stu.`SId`
        ,stu.`Sname`
        ,c.`Cname`
        ,sc.`score`
FROM student stu 
JOIN sc
ON sc.`SId` = stu.`SId` AND sc.`CId` = '01' AND sc.`score` >= 80
JOIN course c
ON c.`CId` = sc.`CId`;
37.求每⻔课程的学⽣⼈数
SELECT  sc.`CId`
        ,c.cname
        ,COUNT(sc.`SId`)
FROM sc
JOIN course c
ON sc.`CId` = c.`CId`
GROUP BY sc.`CId`,c.`Cname`;
38.成绩不重复，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
SELECT  stu.`SId`
        ,stu.`Sname`
        ,MAX(sc.`score`)
FROM student stu 
JOIN sc ON sc.`SId` = stu.`SId`
JOIN (
    SELECT cid 
    FROM teacher t
    JOIN course c
    ON c.`TId` = t.`TId`
    WHERE t.tname = '张三'
) t ON t.cid = sc.`CId`;
39.成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
SELECT  s.`SId`
        ,s.`Sname`
        ,sc.`CId`
        ,sc.`score`
FROM student s
JOIN sc
ON sc.`SId` = s.`SId` AND sc.`CId` = (
    SELECT cid 
    FROM teacher t
    JOIN course c
    ON c.`TId` = t.`TId`
    WHERE t.tname = '张三'
) AND sc.`score` = (
    SELECT MAX(sc.`score`)
    FROM sc
    JOIN (
        SELECT cid 
        FROM teacher t
        JOIN course c
        ON c.`TId` = t.`TId`
        WHERE t.tname = '张三'
    ) t ON t.cid = sc.`CId`
);
40.查询不同课程成绩相同的学⽣的学⽣编号、课程编号、学⽣成绩
SELECT  st.sid
        ,st.sname
        ,sc.cid
        ,sc.score
FROM student st 
LEFT JOIN sc ON sc.sid=st.sid
LEFT JOIN course c ON c.cid=sc.cid
WHERE (
    SELECT COUNT(1)
    FROM student st2 
    LEFT JOIN sc sc2 ON sc2.sid=st2.sid
    LEFT JOIN course c2 ON c2.cid=sc2.cid
    WHERE sc.score=sc2.score AND c.cid!=c2.cid 
)>1;
41.查询每⻔课程成绩最好的前两名
SELECT  * 
FROM (
    SELECT  stu.`SId`
            ,stu.`Sname`
            ,sc.`CId`
            ,sc.`score`
    FROM student stu
    JOIN sc
    ON sc.`SId` = stu.`SId`
    AND sc.`CId` = '01'
    ORDER BY sc.`score` DESC
    LIMIT 0,2
) a
UNION ALL
SELECT * 
FROM (
    SELECT  stu.`SId`
            ,stu.`Sname`
            ,sc.`CId`
            ,sc.`score`
    FROM student stu
    JOIN sc
    ON sc.`SId` = stu.`SId`
    AND sc.`CId` = '02'
    ORDER BY sc.`score` DESC
    LIMIT 0,2
) b
UNION ALL
SELECT * 
FROM (
    SELECT  stu.`SId`
            ,stu.`Sname`
            ,sc.`CId`
            ,sc.`score`
    FROM student stu
    JOIN sc
    ON sc.`SId` = stu.`SId`
    AND sc.`CId` = '03'
    ORDER BY sc.`score` DESC
    LIMIT 0,2
) c;
42.统计每⻔课程的学⽣选修⼈数（超过 5 ⼈的课程才统计）。
SELECT  sc.`CId`
        ,COUNT(sc.`SId`) AS '选课人数'
FROM sc
GROUP BY sc.`CId`
HAVING 选课人数 > 5;
43.检索⾄少选修两⻔课程的学⽣学号
SELECT  sc.`SId`
        ,COUNT(sc.`CId`) AS '选课数量'
FROM sc
GROUP BY sc.`SId`
HAVING COUNT(sc.`CId`)>=2;
44.查询选修了全部课程的学⽣信息
SELECT  s.`SId`
        ,s.`Sname`
        ,COUNT(sc.`CId`) AS 选课数量
FROM student s
JOIN sc ON sc.`SId` = s.`SId`
GROUP BY s.`SId`,s.`Sname`
HAVING COUNT(sc.`CId`) = (SELECT COUNT(c.`CId`) FROM course c);
45.查询各学⽣的年龄，只按年份来算
SELECT  s.*
        ,TIMESTAMPDIFF(YEAR,s.`Sage`,NOW()) 
FROM student s;
46.按照出⽣⽇期来算，当前⽉⽇ < 出⽣年⽉的⽉⽇则，年龄减⼀
47.查询本周过⽣⽇的学⽣
select  st.* 
from student st 
where week(now()) = week(date_format(st.sage,'%Y%m%d'));

ps:

week()函数参见下列网址

https://blog.csdn.net/moakun/article/details/82528773

date_format()函数参见下列网址

https://www.w3school.com.cn/sql/func_date_format.asp

48.查询下周过⽣⽇的学⽣
select  st.* 
from student st 
where week(now())+1=week(date_format(st.sage,'%Y%m%d'));
49.查询本⽉过⽣⽇的学⽣
select  st.* 
from student st 
where month(now())=month(date_format(st.sage,'%Y%m%d'));
50.查询下⽉过⽣⽇的学⽣
select  st.* 
from student st 
where month(timestampadd(month,1,now()))=month(date_format(st.sage,'%Y%m%d'));
-- 或
select  st.* 
from student st 
where (month(now()) + 1) mod 12 = month(date_format(st.sage,'%Y%m%d'));
派大唾沫星子！
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
最全MySQL面试50题和答案.pdf
07-11
最全MySQL面试50题和答案
# MySQL练习 50道，题目+ 答案
Kayleigh520的博客
01-13 600
mysql的练习题
MySQL练习题50
kylin_learn的博客
07-14 914
create database execise_new; use execise_new; -- 建表 -- 学生表 create table Student( s_id varchar(20), s_name varchar(20) not null default '', s_birth varchar(20) not null default '', s_sex ...
MySQL的五十道练习题
码基的博客
06-15 1732
MySQL的五十道练习题 数据表介绍 –1.学生表 Student(SId,Sname,Sage,Ssex) –SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(CId,Cname,TId) –CId 课程编号,Cname 课程名称,TId 教师编号 –3.教师表 Teacher(TId,Tname) –TId 教师编号,Tname 教师姓...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做出来才能达到最好的训练效果。（感觉做这种题是会上瘾的……） 我使用的Mysql版本是5.7.19。SQL语句可能会因数据库系统的不同会有少许差异。 这里放上MySQL语法执行的先后顺序： 7:SELECT 查询列表 1:FROM 表 2:连接类型 JOIN 表2 3:ON 连接条件 4:WHERE 筛选条件 5:GROUP BY 分组 6:HAV
MySQL练习题（34道）
09-12
此练习题可以配套本人MySQL讲解专栏使用，在本人文件分享中，有nanxi.sql文件，该文件为此练习题配套文件，文档中所有题目均来源于该文件. 适合人群： 初学mysql的任何人群，已经配套专栏系统学习过mysql的任何人群...
mysql最强练习代码.sql
04-27
该资源提供了详细的题目描述和答案解析，用户可以通过在线 IDE 或者本地 MySQL 客户端来练习和验证答案。同时，用户也可以根据自己的需求来自定义练习内容和难度等级，以便更好地适应自己的学习和应用需要。
Mysql 基础练习大全(建表到基本查询)​
07-27
答案和解析：MySQL查询练习可以提供每个练习题目的答案和解析。答案可以包括正确的查询语句，以及查询结果的示例。解析可以解释查询语句的思路和步骤，帮助学员理解查询的原理和实现方法。通过提供答案和解析，学员...
MySQL 练习目（含建表语句，解析）
12-24
MySQL 练习目（含建表语句，解析） 题目出自于老杜MySQL，该资源中有题解，若有疑问请留言
MySQL面试题：基础知识与实战技巧
03-30
通过该资源，读者可以学习MySQL的基本概念、语法和操作技巧，了解MySQL在实际应用中的使用方法和技巧，同时还可以通过练习和答案解析来提高自己的MySQL面试水平。 适用人群：本资源适用于准备MySQL面试或者想要深入...
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
mysql练习题
05-16
mysql练习题，一些简单的mysal练习题，适合初学者哦
Mysql经典练习题50题
weixin_42072280的博客
10-10 586
https://www.cnblogs.com/zsty/p/10109125.html http://www.manongjc.com/article/108264.html DROP DATABASE IF EXISTS anan50; CREATE DATABASE anan50; USE anan50; -- 建表 -- 学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name`...
mysql面试题50
bellus
04-18 2455
般面试时考SQL，主要就是考你“统计分析”这一块，下面我们来看面试官经常采用的手段。 由4张简单的不能再简单的表，演变出50道SQL 哈哈哈哈，够这个面试官面个15，20个人，不带重复的了，而且每个SQL你真的不动动脑子还写不出呢，你别不服气，下面开始。 表结构： 表Student (S#,Sname,Sage,Ssex) 学生表
MySQL练习题 （练习表+题目+答案）
qq_42192701的博客
03-11 1万+
mysql练习题 （表+题目+答案） 一、创建所需要练习的表 CREATE TABLE J_TEACHER ( tno int NOT NULL PRIMARY KEY, tname varchar(20) NOT NULL ); INSERT INTO J_TEACHER(tno,tname)VALUES(1,‘张老师’); INSERT INTO J_TEACHER(tno,tname)VAL...
MySQL习题
热门推荐
大熊的博客
07-13 3万+
搜集的一些MySQL的基础习题附自己的答案，不涉及排名。答案不是最好的写法，是比较基本的思路。 建表 一共有四个表，主键对应关系为学生表对应成绩表，再到课程表，最后到教师表。 ## 学生表 create table Student(id varchar(10),name varchar(10),age datetime,sex nvarchar(10)); insert into Stude...
mysql 练习题及答案 50道
u014510892的博客
02-13 4583
此50题参考出处，题目一样，代码有出入,因为题目比较有意思，都自己做了一次。 https://zhuanlan.zhihu.com/p/50662216   -- 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。 -- 方法1 select * from score a inner join score b on (a.s_id = b.s_id and a.c_id...
mysql经典50道练习题pdf
最新发布
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值