精华内容
下载资源
问答
  • mysql练习题
    2021-01-19 14:29:38

    mysql练习题及答案

    mysql练习题及答案

    mysql查询语句练习题

    Sutdent表的定义

    字段名 字段描述 数据类型 主键 外键 非空 唯一 自增

    Id 学号 INT 10 是 否 是 是 是

    Name 姓名 VARCHAR 20 否 否 是 否 否

    Sex 性别 VARCHAR 4 否 否 否 否 否

    Birth 出生年份 YEAR 否 否 否 否 否

    Department 院系 VARCHAR 20 否 否 是 否 否

    Address 家庭住址 VARCHAR 50 否 否 否 否 否

    Score表的定义

    字段名 字段描述 数据类型 主键 外键 非空 唯一 自增

    Id 编号 INT 10 是 否 是 是 是

    Stu_id 学号 INT 10 否 否 是 否 否

    C_name 课程名 VARCHAR 20 否 否 否 否 否

    Grade 分数 INT 10 否 否 否 否 否

    1.创建student和score表

    CREATE TABLE student

    id INT 10 NOT NULL UNIQUE PRIMARY KEY ,

    name VARCHAR 20 NOT NULL ,

    sex VARCHAR 4 ,

    birth YEAR,

    department VARCHAR 20 ,

    address VARCHAR 50 ;

    创建score表。SQL代码如下:

    CREATE TABLE score

    id INT 10 NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,

    stu_id INT 10 NOT NULL ,

    c_name VARCHAR 20 ,

    grade INT 10

    ;

    2.为student表和score表增加记录

    向student表插入记录的INSERT语句如下:

    INSERT INTO student VALUES 901,'张老大', '男',1985,'计算机系', '北京市海淀区' ;

    INSERT INTO student VALUES 902,'张老二', '男',1986,'中文系', '北京市昌平区' ;

    INSERT INTO student VALUES 903,'张三', '女',1990,'中文系', '湖南省永州市' ;

    INSERT INTO student VALUES 904,'李四', '男',1990,'英语系', '辽宁省阜新市' ;

    INSERT INTO student VALUES 905,'王五', '女',1991,'英语系', '福建省厦门市' ;

    INSERT INTO student VALUES 906,'王六', '男',1988,'计算机系', '湖南省衡阳市' ;

    向score表插入记录的INSERT语句如下:

    INSERT INTO score VALUES NULL,901, '计算机',98 ;

    INSERT INTO score VALUES NULL,901, '英语', 80 ;

    INSERT INTO score VALUES NULL,902, '计算机',65 ;

    INSERT INTO score VALUES NULL,902, '中文',88 ;

    INSERT INTO score VALUES NULL,903, '中文',95 ;

    INSERT INTO score VALUES NULL,904, '计算机',70 ;

    INSERT INTO score VALUES NULL,904, '英语',92 ;

    INSERT INTO score VALUES NULL,905, '英语',94 ;

    INSERT INTO score VALUES NULL,906, '计算机',90 ;

    INSERT INTO score VALUES NULL,906, '英语',85 ;

    3.查询student表的所有记录

    mysql SELECT * FROM student;

    +-----+

    | id | name | sex | birth | department | address |

    +-----+

    | 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |

    | 902 | 张老二 | 男 | 1986 | 中

    更多相关内容
  • mysql练习题

    2018-08-02 21:50:42
    MYSQL子查询、相关子查询、inner join、left|right join、cross join 、行列转换等高级查询练习题
  • mysql建表习题,对应着博主的另两篇pdf,此文件可以改成sql后缀,这样在navicat中可以直接导入,此文件中所用的是mysql语句
  • Mysql练习题.txt

    2020-07-15 09:21:17
    众多MySql以及SQL数据库语句的练习题,内涵题目数据库的创库建表语句,不会因为数据库不对而无法做题,每道题都有详细的结果数据库样式参照。主要分为单表查询和多表查询等的增删改查题目,着重掌握链接查询方式。...
  • PHP+mysql练习题一.pdf

    2021-01-08 15:59:42
    PHP+mysql练习题
  • MySql练习题30.txt

    2021-03-09 17:53:45
    适合MySQL入门查询练习
  • 50道经典MySQL练习题(含解答)

    千次阅读 多人点赞 2022-05-30 14:54:38
    本文摘要:本篇主要分享50道经典MySQL练习题(含解答)。

    在这里插入图片描述

    ✅✅作者主页:🔗孙不坚1208的博客

    📚📚精选专栏:🔗MySQL从入门到入土(持续更新中)

    📋📋 本文摘要:本篇主要分享50道经典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 教师姓名
    --4.成绩表
    SC(SId,CId,score)
    --SId 学⽣编号,CId 课程编号,score 分数
    

    表中数据如下

    -- 学⽣表 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);
    

    50道练习题目

    1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
    SELECT stu.*,s.score FROM
        student AS stu
        JOIN (
                SELECT
                    s1.sid,
                    s1.score 
                FROM
                    ( SELECT sid, score FROM sc WHERE Cid = 01 ) AS s1
                    JOIN 
                    ( SELECT sid, score FROM sc WHERE Cid = 02 ) AS s2 ON s1.sid = s2.sid 
                WHERE
                    s1.score > s2.score 
        ) AS s ON stu.sid = s.sid;
    
    +------+--------+---------------------+------+-------+
    | SId  | Sname  | Sage                | Ssex | score |
    +------+--------+---------------------+------+-------+
    | 02   | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 |
    | 04   | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 |
    +------+--------+---------------------+------+-------+
    
    2.查询同时存在" 01 “课程和” 02 "课程的情况
    SELECT s1.sid,s1.score as 01_score,s2.score as 02_score FROM
    (SELECT sid,score from sc WHERE cid=01) as s1
    JOIN
    (SELECT sid,score from sc WHERE cid=02) as s2
    on s1.sid = s2.sid;
    
    +------+----------+----------+
    | sid  | 01_score | 02_score |
    +------+----------+----------+
    | 01   |     80.0 |     90.0 |
    | 02   |     70.0 |     60.0 |
    | 03   |     80.0 |     80.0 |
    | 04   |     50.0 |     30.0 |
    | 05   |     76.0 |     87.0 |
    +------+----------+----------+
    5 rows in set (0.00 sec)
    
    3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
    `mysql SELECT s1.sid,s1.score as 01_score,s2.score as 02_score FROM (SELECT sid,score from sc WHERE cid=01) as s1 LEFT JOIN (SELECT sid,score from sc WHERE cid=02) as s2 on s1.sid = s2.sid;`
    
    4.查询不存在" 01 “课程但存在” 02 "课程的情况
    SELECT s2.sid,s1.score as 01_score,s2.score as 02_score FROM
    (SELECT sid,score from sc WHERE cid=01) as s1
    RIGHT JOIN
    (SELECT sid,score from sc WHERE cid=02) as s2
    on s1.sid = s2.sid;
    
    5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    SELECT stu.sid,stu.`sname`,s1.avg_score
    from student as stu JOIN 
    (SELECT sid,avg(score) as avg_score from sc GROUP BY sid) as s1
    on stu.sid = s1.sid
    WHERE s1.avg_score >60;
    
    6.查询在 SC 表存在成绩的学生信息
    SELECT distinct stu.* from student as stu,sc WHERE stu.SId=sc.SId
    
    7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
    SELECT stu.SId,stu.Sname,count(sc.SId) as '选课总数',sum(sc.score) as '所有课程的总成绩' 
    from student as stu LEFT join sc on stu.SId = sc.SId GROUP BY stu.SId,stu.sname;
    
    8.查询「李」姓老师的数量
    SELECT count(*) FROM teacher WHERE tname like '李%'
    
    9.查询学过「张三」老师授课的同学的信息
    SELECT s1.* FROM 
    (SELECT stu.*,sc.CId from student as stu join sc on stu.SId = sc.SId) as s1 
    JOIN 
    (SELECT teacher.Tname,course.cid FROM course join teacher on course.tid = teacher.Tid) as c1 
    on s1.cid = c1.cid WHERE c1.tname = '张三'
    
    10.查询没有学全所有课程的同学的信息
    SELECT stu.* FROM student as stu 
    where sid not in 
    (SELECT s1.sid FROM (SELECT sid,count(sid) as count_sid FROM sc GROUP BY sid) as s1 WHERE s1.count_sid=3)
    
    11.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    SELECT DISTINCT stu.* from 
    student as stu JOIN sc on stu.sid = sc.SId 
    WHERE sc.CId 
    in
    (SELECT cid FROM sc where sid=01)
    
    12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
    SELECT stu.* FROM student as stu JOIN
    (SELECT s2.sid FROM sc as s1 JOIN sc as s2 on s1.cid = s2.cid and s1.sid=01 and s2.sid!=01
    GROUP BY s2.sid HAVING count(s2.cid)=(SELECT count(*) from sc where sid=01)) as s
    on stu.SId = s.sid
    
    13.查询没学过"张三"老师讲授的任一门课程的学生姓名
    SELECT * from student  
    WHERE SId not in 
    (SELECT s1.sid FROM 
    (SELECT stu.*,sc.CId from student as stu join sc on stu.SId = sc.SId) as s1 
    JOIN 
    (SELECT teacher.Tname,course.cid FROM course join teacher on course.tid = teacher.Tid) as c1 
    on s1.cid = c1.cid WHERE c1.tname = '张三')
    
    14.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    SELECT stu.sname,stu.sid,s1.avg_score from 
    student as stu 
    JOIN 
    (SELECT sid,AVG(score) as avg_score from sc where score<60 GROUP BY sid HAVING count(*)>=2) as s1 
    on stu.sid = s1.sid
    
    15.检索" 01 "课程分数小于 60,按分数降序排列的学生信息16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    SELECT * FROM student WHERE sid in (SELECT sid from sc WHERE cid=01 and score<60 ORDER BY score DESC)
    
    16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    SELECT sc.*,s2.avg_score 
    FROM sc  
    join (SELECT sid,AVG(score) as avg_score from sc GROUP BY sid) as s2
    on sc.sid = s2.sid
    ORDER BY s2.avg_score DESC
    
    17.查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 要求输出课程号和选修⼈数,查询结果按⼈数降序排列,若人数相同,按课程号升序排列
    SELECT sc.cid,course.Cname,max(sc.score) as '最高分',min(sc.score) as '最低分',
    AVG(sc.score) as '平均分',count(sc.CId) as '选修人数',
    SUM(case when sc.score>=60 then 1 else 0 end)/count(sc.CId) as '及格率',
    SUM(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(sc.CId) as '中等率',
    SUM(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(sc.CId) as '优良率',
    SUM(case when sc.score>=90 then 1 else 0 end)/count(sc.CId) as '优秀率'
    from sc,course WHERE sc.CId=course.CId 
    GROUP BY cid,course.Cname
    ORDER BY '选修人数' DESC,sc.cid;
    
    18.按各科平均成绩进行排序,并显示排名, Score 重复时保留名次空缺
    select s2.cid,s2.avg_sc,count(s1.avg_sc) as rank
    from 
    (SELECT cid,ROUND(AVG(score),2) as avg_sc from sc GROUP BY cid ) as s1
    join
    (SELECT cid,ROUND(AVG(score),2) as avg_sc from sc GROUP BY cid ) as s2
    on s1.avg_sc>=s2.avg_sc and s1.cid = s1.cid
    group by s2.cid, s2.avg_sc
    order by rank;
    
    19.按各科平均成绩进行排序,并显示排名, Score 重复时不保留名次空缺
    SELECT b.cid,b.avg_sc,@i:=@i+1 as rank 
    from (SELECT @i :=0) as a,
    (SELECT cid,round(avg(score),2) as avg_sc from sc GROUP BY cid ORDER BY avg_sc desc) as b
    
    20.查询学生的总成绩,并进行排名,总分重复时保留名次空缺
    SELECT s2.sid,s2.sum_sc,COUNT(s2.sum_sc) as rank from
    (SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sid) as s1
    JOIN 
    (SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sid) as s2
    on s1.sum_sc>=s2.sum_sc
    group by s2.sid,s2.sum_sc
    order by rank;
    
    21.查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
    SELECT b.sid,b.sum_sc,@i:=@i+1 as rank 
    from (SELECT @i :=0) as a,
    (SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sum_sc desc) as b
    
    22.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0]及所占百分比
    SELECT sc.CId,c.cname,
    SUM(case when sc.score>85 and sc.score<=100 then 1 else 0 end) as '[100-85]',
    SUM(case when sc.score>85 and sc.score<=100 then 1 else 0 end)/count(sc.CId) as '百分比',
    SUM(case when sc.score>70 and sc.score<=85 then 1 else 0 end) as '[85-70]',
    SUM(case when sc.score>70 and sc.score<=85 then 1 else 0 end)/count(sc.CId) as '百分比',
    SUM(case when sc.score>60 and sc.score<=70 then 1 else 0 end) as '[70-60]',
    SUM(case when sc.score>60 and sc.score<=70 then 1 else 0 end)/count(sc.CId) as '百分比',
    SUM(case when sc.score>0 and sc.score<=60 then 1 else 0 end) as '[60-0]',
    SUM(case when sc.score>0 and sc.score<=60 then 1 else 0 end)/count(sc.CId) as '百分比'
    FROM sc join course as c on sc.CId=c.cid
    GROUP BY sc.CId,c.cname
    
    23.查询各科成绩前三名的记录
    (select CId,score from SC where CId = '01' order by score desc limit 3)
    union all
    (select CId,score from SC where CId = '02' order by score desc limit 3)
    union all
    (select CId,score from SC where CId = '03' order by score desc limit 3)
    
    24.查询每门课程被选修的学生数
    SELECT cid,count(cid) as 选课人数 FROM sc GROUP BY CId;
    
    25.查询出只选修两门课程的学生学号和姓名
    SELECT s2.sid,s2.sname FROM
    (SELECT sid,count(sid) as '选修课程数'  FROM sc GROUP BY SId) as s1
    JOIN student as s2 on s1.SId = s2.SId
    WHERE s1.选修课程数=2
    
    26.查询男生、女生人数
    SELECT ssex,count(SId) FROM student GROUP BY ssex
    
    27.查询名字中含有「风」字的学生信息
    SELECT * FROM student WHERE Sname like '%风%'
    
    28.查询同名同姓学生名单,并统计同名⼈数
    select sname,count(*) as 人数
    from student
    group by sname
    having count(*)>=2;
    
    29.查询 1990 年出生的学生名单
    SELECT * FROM student WHERE YEAR(sage)=1990
    
    30.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    SELECT cid,AVG(score) FROM sc GROUP BY CId ORDER BY AVG(score),CId
    
    31.查询平均成绩⼤于等于 85 的所有学生的学号、姓名和平均成绩
    SELECT s1.sid,s1.sname,s2.平均分
    FROM student as s1 
    join
    (SELECT sid,AVG(score) as '平均分' FROM sc GROUP BY sid) as s2
    on s1.SId = s2.SId
    WHERE s2.平均分>=85
    
    32.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    SELECT s2.sname,s1.score
    FROM
    course as c1 JOIN sc as s1 on c1.cid=s1.CId
    JOIN student as s2 on s1.sid =s2.SId
    WHERE c1.cname='数学' and s1.score<60
    
    33.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    SELECT s2.sname,s2.sid,c1.cname,s1.score
    FROM
     sc as s1
    right JOIN student as s2 on s1.sid =s2.SId
    left JOIN course as c1 on s1.cid=c1.cid
    GROUP BY s2.sid,s2.sname,c1.cname,s1.score
    
    34.查询任何⼀门课程成绩在 70 分以上的姓名、课程名称和分数
    SELECT s2.sname,c1.cname,s1.score
    FROM
     sc as s1
    JOIN student as s2 on s1.sid =s2.SId
    JOIN course as c1 on s1.cid=c1.cid
    WHERE s1.score>70
    
    35.查询不及格的课程
    SELECT s2.sname,c1.cname,s1.score
    FROM
     sc as s1
    JOIN student as s2 on s1.sid =s2.SId
    JOIN course as c1 on s1.cid=c1.cid
    WHERE s1.score<60
    
    36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
    SELECT s1.sid,s2.Sname
    FROM
     (SELECT sid,score FROM sc WHERE cid='01') as s1
    JOIN student as s2 on s1.sid =s2.SId
    WHERE score>=80
    
    
    select SC.SId,Student.Sname
    from SC join Student on SC.SId = Student.SId
    where SC.Score > 80 and SC.CId = '01';
    
    37.求每门课程的学生人数
    SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid
    
    38.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    SELECT s2.sname,s2.SId,s1.score
    FROM
    course as c1 JOIN sc as s1 on c1.cid=s1.CId
    JOIN student as s2 on s1.sid =s2.SId
    JOIN teacher as t1 on c1.tid=t1.tid
    WHERE t1.tname='张三'
    ORDER BY s1.score DESC  LIMIT 1
    
    39.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    SELECT s2.sname,s2.sid,s1.score FROM student as s2 JOIN sc as s1 on s1.SId =s2.SId
    WHERE score=(
    SELECT max(score) FROM sc as s1 WHERE cid=(SELECT c1.cid FROM course as c1 JOIN teacher as t1 on c1.tid = t1.tid WHERE t1.tname = '张三'))
    
    -- 其它⽅案
    select a.sname,b.score
    from student a join sc b on a.sid=b.sid
    and b.cid in (select cid from course where tid in (select tid from teacher
    where tname='张三'))
    join (select cid,max(score) m from sc group by cid) c on b.cid=c.cid and
    b.score=c.m;
    
    40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    SELECT sid,cid,score FROM sc WHERE sid=(select sid from (select sid,score from sc group by sid,score) as s1
    group by sid having count(sid)=1)
    
    -- 其他方案
    select distinct s1.SId,s1.CId,s1.Score
    from SC s1 join SC s2
    on s1.CId != s2.CId and s1.score = s2.score
    group by s1.SId,s1.CId,s1.Score
    
    41.查询每门课程成绩最好的前两名
    SELECT s1.* FROM sc s1 WHERE
    (
    SELECT COUNT(1) FROM sc s2 WHERE
    s1.cid=s2.cid AND s2.score>=s1.score
    )<=2
    ORDER BY s1.cid,s1.score DESC;
    
    -- 其他方案
    (select CId,score from SC where CId = '01' order by score desc limit 2)
    union all
    (select CId,score from SC where CId = '02' order by score desc limit 2)
    union all
    (select CId,score from SC where CId = '03' order by score desc limit 2)
    
    42.统计每门课程的学生选修人数(超过 5 ⼈的课程才统计)。
    SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid HAVING count(cid)>5
    
    43.检索至少选修两门课程的学生学号
    SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid HAVING count(cid)>=5
    
    44.查询选修了全部课程的学生信息
    SELECT sid FROM sc GROUP BY SId HAVING count(sid)=3
    
    45.查询各学生的年龄,只按年份来算
    SELECT sname,year(now())-YEAR(sage) as '年龄' FROM student
    
    46.按照出生日期来算,当前月日 < 出生年月的月日则,年龄减⼀

    TIMESTAMPDIFF() 从日期时间表达式中减去间隔 https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

    select student.sid, student.sname,student.ssex, sage,
    timestampdiff(year,sage,now()) as '按月日计算',  
    year(now())-year(sage) as '按年份计算'  
    from student;
    
    47.查询本周过生日的学生

    返回日期从范围内的数字日历星期1到53

    select sid, sname,ssex, sage
    from student
    WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())
    
    48.查询下周过生日的学生
    select sid, sname,ssex, sage
    from student
    where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1
    
    49.查询本月过生日的学生
    select *
    from student
    where month(student.sage)=month(now());
    
    50.查询下月过生日的学生
    select *
    from student
    where month(student.sage)=month(now())+1;
    

    在这里插入图片描述

    展开全文
  • MySQL 查询练习(50MySQL 查询练习(50) I 1 -- 建表 1 1.1 -- 学生表 1 1.2 -- 课程表 1 1.3 -- 教师表 1 1.4 -- 成绩表 2 1.5 -- 插入学生表测试数据 2 1.6 -- 课程表测试数据 2 1.7 -- 教师表测试数据 2 ...
  • MySQL练习题(经典50题)

    千次阅读 2021-11-05 15:42:47
    MySQL练习题(经典50题) – 建表 –学生表 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...

    MySQL练习题(经典50题)

    – 建表
    –学生表
    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、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
    select st.*,sc.s_score as ‘语文’ ,sc2.s_score ‘数学’
    from student st
    left join score sc on sc.s_id=st.s_id and sc.c_id=‘01’
    left join score sc2 on sc2.s_id=st.s_id and sc2.c_id=‘02’
    where sc.s_score>sc2.s_score

    – 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
    select st.*,sc.s_score ‘语文’,sc2.s_score ‘数学’ from student st
    left join score sc on sc.s_id=st.s_id and sc.c_id=‘01’
    left join score sc2 on sc2.s_id=st.s_id and sc2.c_id=‘02’
    where sc.s_score<sc2.s_score

    – 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
    select st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) cjScore from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id having AVG(sc.s_score)>=60

    – 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
    – (包括有成绩的和无成绩的)
    select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score)) end ) cjScore from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL

    – 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
    select st.s_id,st.s_name,count(c.c_id),( case when SUM(sc.s_score) is null or sum(sc.s_score)="" then 0 else SUM(sc.s_score) end) from student st
    left join score sc on sc.s_id =st.s_id
    left join course c on c.c_id=sc.c_id
    group by st.s_id

    – 6、查询"李"姓老师的数量
    select t.t_name,count(t.t_id) from teacher t
    group by t.t_id having t.t_name like “李%”;

    – 7、查询学过"张三"老师授课的同学的信息
    select st.* from student st
    left join score sc on sc.s_id=st.s_id
    left join course c on c.c_id=sc.c_id
    left join teacher t on t.t_id=c.t_id
    where t.t_name=“张三”

    – 8、查询没学过"张三"老师授课的同学的信息
    – 张三老师教的课
    select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name=“张三”
    – 有张三老师课成绩的st.s_id
    select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name=“张三”)
    – 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
    select st.* from student st where st.s_id not in(
    select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name=“张三”)
    )

    – 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
    select st.* from student st
    inner join score sc on sc.s_id = st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=“01”
    where st.s_id in (
    select st2.s_id from student st2
    inner join score sc2 on sc2.s_id = st2.s_id
    inner join course c2 on c2.c_id=sc2.c_id and c2.c_id=“02”
    )

    网友提供的思路(厉害呦~):
    SELECT st.*
    FROM student st
    INNER JOIN score sc ON sc.s_id=st.s_id
    GROUP BY st.s_id
    HAVING SUM(IF(sc.c_id=“01” OR sc.c_id=“02” ,1,0))>1

    – 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
    select st.* from student st
    inner join score sc on sc.s_id = st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=“01”
    where st.s_id not in (
    select st2.s_id from student st2
    inner join score sc2 on sc2.s_id = st2.s_id
    inner join course c2 on c2.c_id=sc2.c_id and c2.c_id=“02”
    )

    – 11、查询没有学全所有课程的同学的信息
    – 太复杂,下次换一种思路,看有没有简单点方法
    – 此处思路为查学全所有课程的学生id,再内联取反面
    select * from student where s_id not in (
    select st.s_id from student st
    inner join score sc on sc.s_id = st.s_id and sc.c_id=“01”
    where st.s_id in (
    select st2.s_id from student st2
    inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id=“02”
    ) and st.s_id in (
    select st2.s_id from student st2
    inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id=“03”
    ))
    – 来自一楼网友的思路,左连接,根据学生id分组过滤掉 数量小于 课程表中总课程数量的结果(show me his code),简洁不少。
    select st.* from Student st
    left join Score S
    on st.s_id = S.s_id
    group by st.s_id
    having count(c_id)<(select count(c_id) from Course)

    – 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
    select distinct st.* from student st
    left join score sc on sc.s_id=st.s_id
    where sc.c_id in (
    select sc2.c_id from student st2
    left join score sc2 on sc2.s_id=st2.s_id
    where st2.s_id =‘01’
    )

    – 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
    select st.* from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id
    having group_concat(sc.c_id) =
    (
    select group_concat(sc2.c_id) from student st2
    left join score sc2 on sc2.s_id=st2.s_id
    where st2.s_id =‘01’
    )

    – 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
    select st.s_name from student st
    where st.s_id not in (
    select sc.s_id from score sc
    inner join course c on c.c_id=sc.c_id
    inner join teacher t on t.t_id=c.t_id and t.t_name=“张三”
    )

    – 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    select st.s_id,st.s_name,avg(sc.s_score) from student st
    left join score sc on sc.s_id=st.s_id
    where sc.s_id in (
    select sc.s_id from score sc
    where sc.s_score<60 or sc.s_score is NULL
    group by sc.s_id having COUNT(sc.s_id)>=2
    )
    group by st.s_id

    – 16、检索"01"课程分数小于60,按分数降序排列的学生信息
    select st.*,sc.s_score from student st
    inner join score sc on sc.s_id=st.s_id and sc.c_id=“01” and sc.s_score<60
    order by sc.s_score desc

    – 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    – 可加round,case when then else end 使显示更完美
    select st.s_id,st.s_name,avg(sc4.s_score) “平均分”,sc.s_score “语文”,sc2.s_score “数学”,sc3.s_score “英语” from student st
    left join score sc on sc.s_id=st.s_id and sc.c_id=“01”
    left join score sc2 on sc2.s_id=st.s_id and sc2.c_id=“02”
    left join score sc3 on sc3.s_id=st.s_id and sc3.c_id=“03”
    left join score sc4 on sc4.s_id=st.s_id
    group by st.s_id
    order by SUM(sc4.s_score) desc

    – 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
    – 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
    select c.c_id,c.c_name,max(sc.s_score) “最高分”,MIN(sc2.s_score) “最低分”,avg(sc3.s_score) “平均分”
    ,((select count(s_id) from score where s_score>=60 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) “及格率”
    ,((select count(s_id) from score where s_score>=70 and s_score<80 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) “中等率”
    ,((select count(s_id) from score where s_score>=80 and s_score<90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) “优良率”
    ,((select count(s_id) from score where s_score>=90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) “优秀率”
    from course c
    left join score sc on sc.c_id=c.c_id
    left join score sc2 on sc2.c_id=c.c_id
    left join score sc3 on sc3.c_id=c.c_id
    group by c.c_id

    – 19、按各科成绩进行排序,并显示排名(实现不完全)
    – mysql没有rank函数
    – 加@score是为了防止用union all 后打乱了顺序
    select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 from (select c.c_name,sc.* from course c
    left join score sc on sc.c_id=c.c_id
    where c.c_id=“01” order by sc.s_score desc) c1 ,
    (select @i:=0) a
    union all
    select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 from (select c.c_name,sc.* from course c
    left join score sc on sc.c_id=c.c_id
    where c.c_id=“02” order by sc.s_score desc) c2 ,
    (select @ii:=0) aa
    union all
    select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 from (select c.c_name,sc.* from course c
    left join score sc on sc.c_id=c.c_id
    where c.c_id=“03” order by sc.s_score desc) c3;
    set @iii=0;

    – 20、查询学生的总成绩并进行排名
    select st.s_id,st.s_name
    ,(case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end)
    from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id order by sum(sc.s_score) desc

    – 21、查询不同老师所教不同课程平均分从高到低显示
    select t.t_id,t.t_name,c.c_name,avg(sc.s_score) from teacher t
    left join course c on c.t_id=t.t_id
    left join score sc on sc.c_id =c.c_id
    group by t.t_id
    order by avg(sc.s_score) desc

    – 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
    select a.* from (
    select st.,c.c_id,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id =sc.c_id and c.c_id=“01”
    order by sc.s_score desc LIMIT 1,2 ) a
    union all
    select b.
    from (
    select st.,c.c_id,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id =sc.c_id and c.c_id=“02”
    order by sc.s_score desc LIMIT 1,2) b
    union all
    select c.
    from (
    select st.*,c.c_id,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id =sc.c_id and c.c_id=“03”
    order by sc.s_score desc LIMIT 1,2) c

    – 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
    select c.c_id,c.c_name
    ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80)/(select count(1) from score sc where sc.c_id=c.c_id )) “100-85”
    ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70)/(select count(1) from score sc where sc.c_id=c.c_id )) “85-70”
    ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60)/(select count(1) from score sc where sc.c_id=c.c_id )) “70-60”
    ,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>=0)/(select count(1) from score sc where sc.c_id=c.c_id )) “60-0”
    from course c order by c.c_id

    – 24、查询学生平均成绩及其名次
    set @i=0;
    select a.*,@i:=@i+1 from (
    select st.s_id,st.s_name,round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end),2) “平均分” from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id order by sc.s_score desc) a

    – 25、查询各科成绩前三名的记录
    select a.* from (
    select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=‘01’
    order by sc.s_score desc LIMIT 0,3) a
    union all
    select b.* from (
    select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=‘02’
    order by sc.s_score desc LIMIT 0,3) b
    union all
    select c.* from (
    select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=‘03’
    order by sc.s_score desc LIMIT 0,3) c

    – 26、查询每门课程被选修的学生数
    select c.c_id,c.c_name,count(1) from course c
    left join score sc on sc.c_id=c.c_id
    inner join student st on st.s_id=c.c_id
    group by st.s_id

    – 27、查询出只有两门课程的全部学生的学号和姓名
    select st.s_id,st.s_name from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id
    group by st.s_id having count(1)=2

    – 28、查询男生、女生人数
    select st.s_sex,count(1) from student st group by st.s_sex

    – 29、查询名字中含有"风"字的学生信息
    select st.* from student st where st.s_name like “%风%”;

    – 30、查询同名同性学生名单,并统计同名人数
    select st.*,count(1) from student st group by st.s_name,st.s_sex having count(1)>1

    – 31、查询1990年出生的学生名单
    select st.* from student st where st.s_birth like “1990%”;

    – 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    select c.c_id,c.c_name,avg(sc.s_score) from course c
    inner join score sc on sc.c_id=c.c_id
    group by c.c_id order by avg(sc.s_score) desc,c.c_id asc

    – 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
    select st.s_id,st.s_name,avg(sc.s_score) from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id having avg(sc.s_score)>=85

    – 34、查询课程名称为"数学",且分数低于60的学生姓名和分数
    select st.s_id,st.s_name,sc.s_score from student st
    inner join score sc on sc.s_id=st.s_id and sc.s_score<60
    inner join course c on c.c_id=sc.c_id and c.c_name =“数学”

    – 35、查询所有学生的课程及分数情况;
    select st.s_id,st.s_name,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    left join course c on c.c_id =sc.c_id
    order by st.s_id,c.c_name

    – 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
    select st2.s_id,st2.s_name,c2.c_name,sc2.s_score from student st2
    left join score sc2 on sc2.s_id=st2.s_id
    left join course c2 on c2.c_id=sc2.c_id
    where st2.s_id in(
    select st.s_id from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id having min(sc.s_score)>=70)
    order by s_id

    – 37、查询不及格的课程
    select st.s_id,c.c_name,st.s_name,sc.s_score from student st
    inner join score sc on sc.s_id=st.s_id and sc.s_score<60
    inner join course c on c.c_id=sc.c_id

    – 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
    select st.s_id,st.s_name,sc.s_score from student st
    inner join score sc on sc.s_id=st.s_id and sc.c_id=“01” and sc.s_score>=80

    – 39、求每门课程的学生人数
    select c.c_id,c.c_name,count(1) from course c
    inner join score sc on sc.c_id=c.c_id
    group by c.c_id

    – 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
    select st.*,c.c_name,sc.s_score,t.t_name from student st
    inner join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id
    inner join teacher t on t.t_id=c.t_id and t.t_name=“张三”
    order by sc.s_score desc
    limit 0,1

    – 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    select st.s_id,st.s_name,sc.c_id,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    left join course c on c.c_id=sc.c_id
    where (
    select count(1) from student st2
    left join score sc2 on sc2.s_id=st2.s_id
    left join course c2 on c2.c_id=sc2.c_id
    where sc.s_score=sc2.s_score and c.c_id!=c2.c_id
    )>1

    – 42、查询每门功成绩最好的前两名
    select a.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=“01”
    order by sc.s_score desc limit 0,2) a
    union all
    select b.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=“02”
    order by sc.s_score desc limit 0,2) b
    union all
    select c.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
    left join score sc on sc.s_id=st.s_id
    inner join course c on c.c_id=sc.c_id and c.c_id=“03”
    order by sc.s_score desc limit 0,2) c

    – 借鉴(更准确,漂亮):
    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 sc.c_id,count(1) from score sc
    left join course c on c.c_id=sc.c_id
    group by c.c_id having count(1)>5
    order by count(1) desc,sc.c_id asc

    – 44、检索至少选修两门课程的学生学号
    select st.s_id from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id having count(1)>=2

    – 45、查询选修了全部课程的学生信息
    select st.* from student st
    left join score sc on sc.s_id=st.s_id
    group by st.s_id having count(1)=(select count(1) from course)

    – 46、查询各学生的年龄
    select st.*,timestampdiff(year,st.s_birth,now()) from student st

    – 47、查询本周过生日的学生
    – 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
    – 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写
    select st.* from student st
    where week(now())=week(date_format(st.s_birth,’%Y%m%d’))

    – 48、查询下周过生日的学生
    select st.* from student st
    where week(now())+1=week(date_format(st.s_birth,’%Y%m%d’))

    – 49、查询本月过生日的学生
    select st.* from student st
    where month(now())=month(date_format(st.s_birth,’%Y%m%d’))

    – 50、查询下月过生日的学生
    – 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模
    select st.* from student st
    where month(timestampadd(month,1,now()))=month(date_format(st.s_birth,’%Y%m%d’))
    – 或
    select st.* from student st where (month(now()) + 1) mod 12 = month(date_format(st.s_birth,’%Y%m%d’))

    展开全文
  • mysql用本博主前面的文件建表完成后,此文档为题目,博主本来想把这些文档一起上传,奈何csdn一次只能上传一个文档(╥╯^╰╥)
  • mysql练习题,mysql的mysql
  • MySQL练习题(附带数据)

    2020-12-02 21:24:45
    MySQL练习题(附带数据), 包含:DDL:数据定义语言,定义库,表结构等,包括create,drop,alter等 DML:数据操作语言,增删改查数据,包括insert,delete,update,select等 DCL:数据控制语言,权限,事务等管理。 DQL:...
  • MySQL练习题.pdf

    2022-06-27 05:11:36
    MySQL练习题.pdfMySQL练习题.pdfMySQL练习题.pdfMySQL练习题.pdfMySQL练习题.pdfMySQL练习题.pdfMySQL练习题.pdfMySQL练习题.pdf
  • MySQL练习题.docx

    2022-06-27 03:00:01
    MySQL练习题.docxMySQL练习题.docxMySQL练习题.docxMySQL练习题.docxMySQL练习题.docxMySQL练习题.docxMySQL练习题.docxMySQL练习题.docx
  • MySQL练习题

    千次阅读 2018-04-09 22:32:34
    MySQL练习题 查找最晚入职员工的所有信息 相关建表语句以及测试数据 CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_...

    MySQL练习题

    查找最晚入职员工的所有信息

    相关建表语句以及测试数据

    CREATE TABLE `employees` (
    	`emp_no` int(11) NOT NULL,
    	`birth_date` date NOT NULL,
    	`first_name` varchar(14) NOT NULL,
    	`last_name` varchar(16) NOT NULL,
    	`gender` char(1) NOT NULL,
    	`hire_date` date NOT NULL,
    	PRIMARY KEY (`emp_no`)
    );
    INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
    INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
    INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
    INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
    INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
    INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
    INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
    INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
    INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
    INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
    INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
    

    答案1

    select * from employees
    order by hire_date desc
    limit 0, 1;
    

    答案2

    select * from employees
    where hire_date = (
    	select max(hire_date) from employees
    );
    

    查找入职员工时间排名倒数第三的员工所有信息

    答案

    select * from employees
    order by hire_date desc
    limit 2, 1;
    
    展开全文
  • MySQL练习题及答案.pdf

    2022-06-27 03:36:04
    MySQL练习题及答案.pdfMySQL练习题及答案.pdfMySQL练习题及答案.pdfMySQL练习题及答案.pdfMySQL练习题及答案.pdfMySQL练习题及答案.pdfMySQL练习题及答案.pdfMySQL练习题及答案.pdf
  • MySQL练习题及答案.docx

    2022-06-27 03:01:15
    MySQL练习题及答案.docxMySQL练习题及答案.docxMySQL练习题及答案.docxMySQL练习题及答案.docxMySQL练习题及答案.docxMySQL练习题及答案.docxMySQL练习题及答案.docxMySQL练习题及答案.docx
  • MYSQL练习题34道.zip

    2020-06-16 23:29:43
    MySQL数据库基础练习题34道,有题有答案,包括练习用.sql文件,适合新手练习。 基础的增删改查。
  • MySQL练习题 (2).pdf

    2022-06-27 03:15:17
    MySQL练习题 (2).pdfMySQL练习题 (2).pdfMySQL练习题 (2).pdfMySQL练习题 (2).pdfMySQL练习题 (2).pdfMySQL练习题 (2).pdfMySQL练习题 (2).pdfMySQL练习题 (2).pdf
  • MySQL练习题 (2).docx

    2022-06-27 02:59:24
    MySQL练习题 (2).docxMySQL练习题 (2).docxMySQL练习题 (2).docxMySQL练习题 (2).docxMySQL练习题 (2).docxMySQL练习题 (2).docxMySQL练习题 (2).docxMySQL练习题 (2).docx
  • MySQL练习题.md

    2021-04-03 23:46:21
    做完这100SQL练习题,SQL面试轻松搞定
  • MySQL练习题及答案-精品.pdfMySQL练习题及答案-精品.pdfMySQL练习题及答案-精品.pdfMySQL练习题及答案-精品.pdfMySQL练习题及答案-精品.pdfMySQL练习题及答案-精品.pdfMySQL练习题及答案-精品.pdfMySQL练习题及答案-...
  • MySQL练习题及答案-精品.docxMySQL练习题及答案-精品.docxMySQL练习题及答案-精品.docxMySQL练习题及答案-精品.docxMySQL练习题及答案-精品.docxMySQL练习题及答案-精品.docxMySQL练习题及答案-精品.docxMySQL练习题...
  • MySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdfMySQL练习题及答案 (2).pdf
  • MySQL练习题及答案 (2).docxMySQL练习题及答案 (2).docxMySQL练习题及答案 (2).docxMySQL练习题及答案 (2).docxMySQL练习题及答案 (2).docxMySQL练习题及答案 (2).docxMySQL练习题及答案 (2).docxMySQL练习题及答案 ...
  • 2018 年 3 月计算机二级 MySQL练习题一 一选择题 1在 MySQL中通常使用 _语句来指定一个已有数据库作为当前工 作数据库 2下列 _类型不是 MySQL中常用的的数据类型 3可用于从表或视图中检索数据的 SQL语句是 _ 语句 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,669
精华内容 13,867
关键字:

mysql练习题

mysql 订阅
友情链接: 充电桩插件.zip