sql常见50道查询练习题

1. 表创建

在这里插入图片描述

1.1 表创建

#–1.学生表 #Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别CREATETABLE`Student`(`s_id`VARCHAR(20),
    s_name VARCHAR(20)NOTNULLDEFAULT'',
    s_brith VARCHAR(20)NOTNULLDEFAULT'',
    s_sex VARCHAR(10)NOTNULLDEFAULT'',PRIMARYKEY(s_id));#–2.课程表 #Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 createtable Course(
    c_id varchar(20),
    c_name VARCHAR(20)notnullDEFAULT'',
    t_id VARCHAR(20)NOTNULL,PRIMARYKEY(c_id));/*
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
*/CREATETABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20)NOTNULLDEFAULT'',PRIMARYKEY(t_id));/*
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
*/Createtable Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20)notnulldefault'',
    s_score INT(3),primarykey(`s_id`,`c_id`));

1.2 数据插入

#--插入学生表测试数据#('01' , '赵雷' , '1990-01-01' , '男')insertinto Student values('01','赵雷','1990-01-01','男');insertinto Student values('02','钱电','1990-12-21','男');insertinto Student values('03','孙风','1990-05-20','男');insertinto Student values('04','李云','1990-08-06','男');insertinto Student values('05','周梅','1991-12-01','女');insertinto Student values('06','吴兰','1992-03-01','女');insertinto Student values('07','郑竹','1989-07-01','女');insertinto Student values('08','王菊','1990-01-20','女');#--课程表测试数据insertinto Course values('01','语文','02');insertinto Course values('02','数学','01');insertinto Course values('03','英语','03');#--教师表测试数据insertinto Teacher values('01','张三');insertinto Teacher values('02','李四');insertinto Teacher values('03','王五');#--成绩表测试数据insertinto Score values('01','01',80);insertinto Score values('01','02',90);insertinto Score values('01','03',99);insertinto Score values('02','01',70);insertinto Score values('02','02',60);insertinto Score values('02','03',80);insertinto Score values('03','01',80);insertinto Score values('03','02',80);insertinto Score values('03','03',80);insertinto Score values('04','01',50);insertinto Score values('04','02',30);insertinto Score values('04','03',20);insertinto Score values('05','01',76);insertinto Score values('05','02',87);insertinto Score values('06','01',31);insertinto Score values('06','03',34);insertinto Score values('07','02',89);insertinto Score values('07','03',98);

2. 简单查询例题(3题）

2.1 查询"李"姓老师的数量

SELECTcount(1)as cnt
FROM
    teacher
WHERE
    t_name like"李%"

2.2 查询男生、女生人数

SELECT
    s.s_sex,count(1)as 人数
FROM
    student s
groupby
    s.s_sex

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

SELECT*FROM
    student
WHERE
    s_name like"%风%"

3. 日期相关例题(6题）

3.1 查询各学生的年龄

(按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一)-- if函数select a.*,year(NOW())-year(a.s_brith)-if(DATE_FORMAT(now(),"%m%d")>DATE_FORMAT(a.s_brith,"%m%d"),0,1)as ageFROM student a-- case函数select s_brith,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_brith,'%Y')-(casewhen DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_brith,'%m%d')then0else1end))as agefrom student;

3.2 查询本周过生日的学生

SELECT*FROM
    student
WHERE
    WEEKOFYEAR(STR_TO_DATE(concat(year(NOW()),DATE_FORMAT(s_brith,'%m%d')),"%Y%m%d"))=WEEKOFYEAR(NOW())--     WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)

3.3 查询下周过生日的学生

SELECT*FROM
    student
WHERE
    WEEKOFYEAR(STR_TO_DATE(concat(year(NOW()),DATE_FORMAT(s_brith,'%m%d')),"%Y%m%d"))=WEEKOFYEAR(NOW()+interval"7"day)--     WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1=WEEK(s_birth)

3.4 查询本月过生日的学生

SELECT*FROM
    student
WHEREMONTH(now())=month(s_brith)

3.5 查询下月过生日的学生

SELECT*FROM
    student
WHEREMONTH(now()+interval"1"month)=month(s_brith)

3.6 查询1990年出生的学生名单

SELECT*FROM
    student
WHERE
    s_brith like"1990%"--     left(s_brith,4)="1990"--     year(s_brith)="1990"

4. 开窗函数查询（7题）

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

方法一:开窗函数select a.*,avg(a.s_score)over(PARTITIONby a.s_id)as avg_scoreFROM score a
方法二：临时表连接SELECT a.*, t.avg_scoreFROM score a,(SELECT a.s_id,round(avg(a.s_score),2)as avg_score FROM score a groupby a.s_id) tWHERE a.s_id=t.s_idorderby t.avg_score desc
方法三:长型数据转为宽型数据SELECT a.s_id, ifnull((select s_score from score where s_id=a.s_id and c_id="01"),0)as"语文", ifnull((select s_score from score where s_id=a.s_id and c_id="02"),0)as"数学", ifnull((select s_score from score where s_id=a.s_id and c_id="03"),0)as"英语", ifnull(round(avg(a.s_score),2),0)as avg_scoreFROM score agroupby a.s_idorderby ifnull(round(avg(a.s_score),2),0)desc

4.2 按各科成绩进行排序，并显示排名(实现不完全)

方法一：开窗函数SELECT a.*, rank()over(PARTITIONby c_id orderby s_score desc) rank排名, row_number()over(PARTITIONby c_id orderby s_score desc) row_number排名, dense_rank()over(PARTITIONby c_id orderby s_score desc) dense_rank排名FROM score a
方法二：子查询SELECT a.*,(selectcount(s_score)from score b where a.c_id=b.c_id and a.s_score<b.s_score)+1 rk,(selectcount(distinct s_score)from score b where a.c_id=b.c_id and a.s_score<=b.s_score) den_rkFROM score aorderby c_id,s_score desc

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

方法一：开窗函数SELECT t.*, rank()over(orderby sum_score desc) rank排名FROM(SELECT s_id,sum(s_score)as sum_score FROM score groupby s_id) t

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

方法一：子查询+开窗函数SELECT a.*, t.c_id, t.rk, t.s_scoreFROM student a,(SELECT a.s_id, a.c_id, a.s_score, dense_rank()over(PARTITIONby a.c_id orderby a.s_score desc)as rk FROM score a) tWHERE t.rk in(2,3)AND a.s_id=t.s_id

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

方法一：开窗函数SELECT t.*, rank()over(orderby t.avg_score desc) 排名FROM(SELECT a.s_id,round(avg(a.s_score),2)as avg_score FROM score a groupby a.s_id) t

4.6 查询各科成绩前三名的记录

方法一：开窗函数SELECT t.*from(SELECT a.c_id, a.c_name, b.s_score, rank()over(PARTITIONby a.c_id orderby b.s_score desc) rk FROM course a LEFTJOIN score b ON a.c_id=b.c_id) tWHERE t.rk<=3;
方法二：子查询SELECT*from(SELECT a.c_id, a.c_name, b.s_score,(selectcount(c.s_score)from score c where a.c_id=c.c_id and b.s_score<c.s_score)+1as rk FROM course a LEFTJOIN score b ON a.c_id=b.c_id) tWHERE t.rk<=3orderby t.c_name,t.rk asc;

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

方法一：开窗函数SELECT t.s_id, t.c_id, t.s_scoreFROM(SELECT*, rank()over(PARTITIONby b.c_id orderby b.s_score desc) rk FROM score b) tWHERE t.rk<=2;
方法二：自连接SELECT t.s_id, t.c_id, t.s_scoreFROM(SELECT a.*,(selectcount(1)from score b where b.c_id=a.c_id and a.s_score<b.s_score)+1as rk FROM score a orderby a.c_id,rk) tWHERE t.rk<=2
方法三：条件查询+子查询SELECT a.*FROM score aWHERE(selectcount(1)from score b where b.c_id=a.c_id and a.s_score<b.s_score)+1<=2orderby a.c_id

5. 表连接+子查询+聚合函数查询(34题）

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

方法一：自连接，同列比较，使用自查询 - 思路：先找出查询条件的学生信息及分数，根据子查询得到最终结果SELECTst.*,t1.sc1,t1.sc2FROM student st,(SELECT s1.s_id,s1.s_score as sc1,s2.s_score as sc2 FROM score s1,score s2 WHERE s1.c_id="01"AND s2.c_id="02"AND s1.s_id=s2.s_id AND s1.s_score>s2.s_score) t1WHERE st.s_id=t1.s_id;
方法二：表连接SELECTst.*,s1.s_score as sc1,s2.s_score as sc2FROM student stleftJOIN score s1ON s1.s_id=st.s_idleftJOIN score s2ON s2.s_id=st.s_idWHERE s1.c_id="01"AND s2.c_id="02"AND s1.s_id=s2.s_idAND s1.s_score>s2.s_score
数据长型数据变为宽型数据-- IF函数或case函数SELECT a.*, t.s01, t.s02from student a,(SELECT a.s_id,max(casewhen a.c_id="01"then a.s_score end)as s01,max(casewhen a.c_id="02"then a.s_score end)as s02-- max(if(a.c_id="01",a.s_score,null)) as s01,-- max(if(a.c_id="02",a.s_score,null)) as s02from score a groupby a.s_id) tWHERE a.s_id=t.s_idAND t.s01>t.s02

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

与上一题思路一致，条件大于变小于
方法一：自连接SELECTst.*,t1.sc1,t1.sc2FROM student st,(SELECT s1.s_id,s1.s_score as sc1,s2.s_score as sc2 FROM score s1,score s2 WHERE s1.c_id="01"AND s2.c_id="02"AND s1.s_id=s2.s_id AND s1.s_score<s2.s_score) t1WHERE st.s_id=t1.s_id;
方法二：表连接SELECT st.*,s1.s_score as sc1,s2.s_score as sc2FROM student stleftJOIN score s1ON s1.s_id=st.s_idleftJOIN score s2ON s2.s_id=st.s_idWHERE s1.c_id="01"AND s2.c_id="02"AND s1.s_id=s2.s_idAND s1.s_score<s2.s_score -- 方法二SELECT st.*,s1.s_score as sc1,s2.s_score as sc2FROM student stleftJOIN score s1ON s1.s_id=st.s_idAND s1.c_id="01"leftJOIN score s2ON s2.s_id=st.s_idAND s2.c_id="02"AND s1.s_id=s2.s_idWHERE s1.s_score<s2.s_score
方法三:数据长型数据变为宽型数据-- IF函数或case函数SELECT a.*, t.s01, t.s02from student a,(SELECT a.s_id,max(casewhen a.c_id="01"then a.s_score end)as s01,max(casewhen a.c_id="02"then a.s_score end)as s02-- max(if(a.c_id="01",a.s_score,null)) as s01,-- max(if(a.c_id="02",a.s_score,null)) as s02from score a groupby a.s_id) tWHERE t.s01<t.s02AND a.s_id=t.s_id

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

方法一：子查询-- 子查询一SELECTst.s_id,st.s_name,t.avg_sFROM student ST,(SELECT s.s_id,round(avg(s.s_score),2)as avg_s FROM score s GROUPBY s.s_id HAVINGround(avg(s.s_score),2)>=60) tWHERE st.s_id=t.s_id-- 方法二：子查询二SELECT s.s_id,(select s_name from student where s_id=s.s_id)as s_name,round(avg(s.s_score),2)as avg_sFROM score sGROUPBY s.s_idHAVING avg_s>=60
方法二：表连接SELECT a.s_id,a.s_name,round(avg(b.s_score),2)as avg_scoreFROM student aLEFTJOIN score bON a.s_id=b.s_idGROUPBY a.s_idHAVINGround(avg(b.s_score),2)>=60;

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

方法一：子查询-- 有成绩的SELECT a.s_id,a.s_name,t.avg_acoreFROM student a,(SELECT a.s_id,round(avg(a.s_score),2)as avg_acore FROM score a GROUPBY a.s_id HAVINGround(avg(a.s_score),2)<60) tWHERE a.s_id=t.s_id UNION-- 没有成绩的：没有成绩的s_id不存在SELECT a.s_id,a.s_name,0as avg_acoreFROM student aWHERE a.s_id notin(SELECTDISTINCT s_id FROM score);
方法二：表连接SELECT a.s_id,a.s_name,ifnull(round(avg(b.s_score),2),0)as avg_scoreFROM student aLEFTJOIN score bon a.s_id=b.s_idGROUPBY a.s_idHAVING avg_score<60

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

SELECT
    a.s_id,
    a.s_name,count(b.c_id)as cnt_course,
    ifnull(sum(b.s_score),0)as sum_score
FROM
    student a
LEFTJOIN
    score b
ON
    a.s_id=b.s_id
groupby
    a.s_id

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

方法一：表连接+子查询单层嵌套SELECT a.*FROM student aLEFTJOIN score bon a.s_id=b.s_idLEFTJOIN course cON b.c_id=c.c_idwhere c.t_id in(SELECT t_id FROM teacher WHERE t_name ="张三")
方法二：表连接+子查询多层嵌套SELECT a.*FROM student aLEFTJOIN score bON a.s_id=b.s_idWHERE b.c_id in(SELECT c_idFROM course where t_id in(SELECT t_id from teacher where t_name="张三"));
方法三：多表连接select a.*from student a,score b,course c,teacher dWHERE a.s_id=b.s_idAND b.c_id=c.c_idAND c.t_id=d.t_idAND d.t_name="张三"

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

注意：一个学生有几门课程包含张三课程，不是张三课程的，根据没学过的查询不出来，因为一个人有多个老师的课程
方法一：多层嵌套子查询SELECT s.*FROM student sWHERE s.s_id NOTIN(-- 查找学的学生SELECTDISTINCT a.s_id FROM student a LEFTJOIN score b ON a.s_id = b.s_id WHERE b.c_id IN(-- 查找学过的课程SELECT c_id FROM course WHERE t_id IN(SELECT t_id FROM teacher WHERE t_name ="张三")))
方法二:条件查询+子表连接SELECT*FROM student sWHERE s.s_id notin(select a.s_id from score a, course b, teacher c WHERE a.c_id=b.c_id AND b.t_id=c.t_id AND c.t_name="张三")

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

方法一：子查询+自连接,同列对比可以用自连接SELECT*FROM student sWHERE s.s_id in(SELECT a.s_id FROM score a,score b WHERE a.c_id="01"AND b.c_id="02"AND a.s_id=b.s_id)
方法二：连表+自连接,同列对比可以用自连接SELECT s.*FROM student sLEFTJOIN score aON s.s_id=a.s_idLEFTJOIN score bON a.s_id=b.s_idWHERE a.c_id="01"AND b.c_id="02"
方法三：条件查询+子查询SELECT*FROM studentWHERE s_id in(SELECT s_id FROM score where c_id="01"or c_id="02"GROUPBY s_id HAVINGcount(1)=2)
方法四：自连接，条件连接SELECT s.*FROM student s,score a,score bWHERE s.s_id=a.s_idAND a.s_id=b.s_idAND a.c_id="01"AND b.c_id="02"
方法五：子查询+数据长型数据变为宽型数据SELECT a.*FROM student a,(select a.s_id,max(if(a.c_id="01",a.s_score,0))as s01,max(if(a.c_id="02",a.s_score,0))as s02 from score a groupby a.s_id) tWHERE a.s_id=t.s_idAND t.s01>0AND t.s02>0

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

方法一：条件查询+子查询select a.*from student aWHERE a.s_id in(select s_id from score where c_id="01")AND a.s_id notin(select s_id from score where c_id="02")
方法二：子查询+分组聚合SELECT s.*FROM student s,(SELECT a.s_id,max(casewhen a.c_id="01"then a.s_score end) s01,max(casewhen a.c_id="02"then a.s_score end) s02 FROM score a groupby a.s_id) tWHERE s.s_id=t.s_idAND t.s01 isnotNULLAND t.s02 isnull
方法三：数据长型数据变为宽型数据SELECT a.*FROM student a,(select a.s_id,max(if(a.c_id="01",a.s_score,null))as s01,max(if(a.c_id="02",a.s_score,null))as s02 from score a groupby a.s_id) tWHERE a.s_id=t.s_idAND t.s01 isnotnullAND t.s02 isnull

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

方法一:条件查询+子查询SELECT s.*FROM student sWHERE s.s_id in(SELECT a.s_id FROM score a groupby a.s_id havingcount(1)<(selectcount(1)from course))
方法二:表连接SELECT s.*,count(a.c_id) cntFROM student sLEFTJOIN score aON a.s_id=s.s_idgroupby s.s_idHAVINGcount(a.c_id)<(selectcount(1)from course)

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

方法一:子查询SELECT s.*FROM student sWHERE s.s_id in(SELECTdistinct a.s_id FROM score a WHERE a.c_id in(SELECT b.c_id FROM score b WHERE b.s_id="01"))AND s.s_id!='01'
方法二:表连接+子查询SELECT a.*FROM student aLEFTJOIN score bon a.s_id=b.s_idWHERE b.c_id in(SELECT b.c_id FROM score b WHERE b.s_id="01")groupby1,2,3,4

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

筛选课程与01号一样的数据，计算课程数与01一致的

SELECT
    s.*FROM
    student s
WHERE
    s.s_id in(SELECTdistinct 
                        a.s_id
                    FROM
                        score a
                    WHERE
                        a.c_id in(SELECT
                                    a.c_id
                                FROM
                                    score a
                                WHERE
                                    a.s_id="01")AND
                        a.s_id!="01"groupby 
                        a.s_id
                    HAVINGcount(distinct a.c_id)=(selectcount(1)from score a where a.s_id="01"))

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

查询学过张三老师的学生，在学生表中反向查询

SELECT
    s.s_name
FROM
    student s
WHERE
    s.s_id notin(SELECT
                    a.s_id
                FROM
                    score a
                WHERE
                    a.c_id in(SELECT
                                a.c_id
                            FROM
                                course a
                            WHERE
                                a.t_id in(SELECT t.t_id FROM teacher t WHERE t.t_name="张三")))

5.14 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

方法一：表连接+分组+having条件SELECT a.s_id, a.s_name,round(avg(b.s_score),2)as avg_scoreFROM student aLEFTJOIN score bON a.s_id=b.s_idgroupby a.s_idhavingsum(if(b.s_score>=60,0,1))>=2
方法二：自连接+子查询select a.s_id,a.s_name,round(avg(b.s_score),2)as avg_scoreFROM student a,score bWHERE a.s_id=b.s_idAND a.s_id in(SELECT a.s_id FROM score a WHERE a.s_score<60groupby a.s_id HAVINGcount(1)>=2)groupby a.s_id
方法三：表连接+子查询select a.s_id,a.s_name,round(avg(b.s_score),2)as avg_scoreFROM student aLEFTJOIN score bon a.s_id=b.s_idwhere a.s_id in(SELECT a.s_id FROM score a WHERE a.s_score<60groupby a.s_id HAVINGcount(1)>=2)groupby a.s_id

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

方法一：表连接SELECT a.*,b.c_id,b.s_scoreFROM student aLEFTJOIN score bON a.s_id=b.s_idWHERE b.c_id="01"and b.s_score<60orderby b.s_score desc

5.16 查询各科成绩最高分、最低分和平均分

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
方法一：if语句SELECT a.c_id, a.c_name,max(b.s_score)as max_score,min(b.s_score)as min_score,round(avg(b.s_score),2)as avg_score,round(100*sum(if(b.s_score>=60,1,0))/count(1),2)as"及格率",round(100*sum(if(b.s_score>=70and b.s_score<80,1,0))/count(1),2)as"中等率",round(100*sum(if(b.s_score>=80and b.s_score<90,1,0))/count(1),2)as"优良率",round(100*sum(if(b.s_score>=90,1,0))/count(1),2)as"优秀率"FROM course a, score bWHERE a.c_id=b.c_idgroupby a.c_id
方法二：case whenSELECT a.c_id, a.c_name,max(b.s_score)as max_score,min(b.s_score)as min_score,round(avg(b.s_score),2)as avg_score,round(100*sum(casewhen b.s_score>=60then1else0end)/sum(casewhen b.s_score then1else0end),2)as"及格率",round(100*sum(casewhen b.s_score>=70and b.s_score<80then1else0end)/sum(casewhen b.s_score then1else0end),2)as"中等率",round(100*sum(casewhen b.s_score>=80and b.s_score<90then1else0end)/sum(casewhen b.s_score then1else0end),2)as"优良率",round(100*sum(casewhen b.s_score>=90then1else0end)/sum(casewhen b.s_score then1else0end),2)as"优秀率"FROM course a, score bWHERE a.c_id=b.c_idgroupby a.c_id

5.17 统计各科成绩各分数段人数

课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]个数及所占百分比
方法一：if函数SELECT b.c_id, a.c_name,round(100*sum(if(b.s_score>85and b.s_score<=100,1,0))/count(1),2)as"[100-85]百分比",sum(if(b.s_score>85and b.s_score<=100,1,0))as"[100-85]",round(100*sum(if(b.s_score>70and b.s_score<=85,1,0))/count(1),2)as"[85-70]百分比",sum(if(b.s_score>70and b.s_score<=85,1,0))as"[85-70]",round(100*sum(if(b.s_score>60and b.s_score<=70,1,0))/count(1),2)as"[70-60]百分比",sum(if(b.s_score>60and b.s_score<=70,1,0))as"[70-60]",round(100*sum(if(b.s_score>0and b.s_score<=60,1,0))/count(1),2)as"[0-60]百分比",sum(if(b.s_score>=0and b.s_score<=60,1,0))as"[0-60]"FROM course a, score bWHERE a.c_id=b.c_idgroupby b.c_id

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

方法一：表连接SELECT c.t_name, a.c_name,round(avg(b.s_score),2)as avg_scoreFROM course aleftJOIN score bON a.c_id=b.c_idLEFTJOIN teacher cON a.t_id=c.t_idgroupby c.t_name,a.c_nameorderby avg_score DESC

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

SELECT
    a.c_id,
    a.c_name,count(1)as cnt
FROM
    course a
LEFTJOIN
    score b
ON    
    a.c_id=b.c_id
groupby
    a.c_id

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

方法一:连表SELECTdistinct a.s_id,a.s_nameFROM student a, score bWHERE a.s_id=b.s_idgroupby a.s_idHAVINGcount(b.c_id)=2
方法二：条件查询select s_id, s_name from student where s_id in(select s_id from score GROUPBY s_id HAVINGCOUNT(c_id)=2);

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

方法一:分组条件查询SELECT s_name,count(1)as"人数"FROM studentgroupby s_name,s_sexHAVINGcount(1)>1
方法二:自连接(同列比较可以用自连接）select a.s_name, a.s_sex,count(*)from student a JOIN student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sexGROUPBY a.s_name,a.s_sex

5.22 查询每门课程的平均成绩

结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列SELECT a.c_id,round(avg(a.s_score),2)as avg_scoreFROM score agroupby a.c_idorderby avg_score desc,a.c_id asc

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

方法一：子查询SELECT a.s_id, a.s_name, t.avg_scoreFROM student a,(SELECT a.s_id,round(avg(a.s_score),2)as avg_score FROM score a groupby a.s_id HAVING avg_score>=85) tWHERE a.s_id=t.s_idAND t.avg_score isnotnull
方法二：表连接select a.s_id, b.s_name, ifnull(round(avg(a.s_score),2),0)as avg_scoreFROM score aLEFTJOIN student bON a.s_id=b.s_idGROUPBY a.s_idHAVING avg_score>=85

5.24 查询课程名称为"数学"，且分数低于60的学生姓名和分数

方法一：条件查询+子查询SELECT b.s_name, a.s_scoreFROM score aLEFTJOIN student bON a.s_id=b.s_idWHERE c_id in(SELECT c_id FROM course where c_name="数学")AND a.s_score<60
方法二：多表连接SELECT b.s_name, a.s_scoreFROM score aLEFTJOIN student bON a.s_id=b.s_idLEFTJOIN course cON a.c_id=c.c_idWHERE c.c_name="数学"AND a.s_score<60

5.25 查询所有学生的课程及分数情况

方法一：表连接SELECT a.s_name,c.c_name,b.s_scoreFROM student aLEFTJOIN score bON a.s_id=b.s_idLEFTJOIN course cON c.c_id=b.c_id
方法二：if函数SELECT a.s_id, a.s_name,sum(if(c.c_name="语文",b.s_score,0))as"语文",sum(if(c.c_name="数学",b.s_score,0))as"数学",sum(if(c.c_name="英语",b.s_score,0))as"英语",sum(b.s_score)as"总分"FROM student aLEFTJOIN score bON a.s_id=b.s_idLEFTJOIN course cON c.c_id=b.c_idgroupby a.s_id,a.s_name
方法三：case函数select a.s_id, a.s_name,sum(casewhen c.c_name="语文"then b.s_score else0end)as"语文",sum(casewhen c.c_name="数学"then b.s_score else0end)as"数学",sum(casewhen c.c_name="英语"then b.s_score else0end)as"英语",sum(b.s_score)as"总分"FROM student aLEFTJOIN score bON a.s_id=b.s_idLEFTJOIN course cON c.c_id=b.c_idgroupby a.s_id,a.s_name

5.26 查询任何一门课程成绩在70分以上的姓名、课程名称和分数(学生的每门课都大于70)

方法一：表连接+子查询SELECT a.s_name, c.c_name, b.s_scoreFROM student aLEFTJOIN score bON a.s_id=b.s_idLEFTJOIN course cON c.c_id=b.c_idWHERE a.s_id in(select s_id from score groupby s_id havingmin(s_score)>70);

5.27 查询不及格的课程

方法一：表连接

SELECTdistinct
    b.s_id,
    b.c_id,
    a.c_name,
    b.s_score
from
    course a
LEFTJOIN
    score b
ON
    a.c_id=b.c_id
WHERE
    b.s_score<60

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

方法一：子查询SELECT t.s_id, t.s_nameFROM student tWHERE t.s_id in(SELECT a.s_id FROM score a WHERE a.c_id="01"AND a.s_score>80)
方法二：表连接select a.s_id, a.s_namefrom student aLEFTJOIN score bON a.s_id=b.s_idWHERE b.c_id="01"AND b.s_score>80

5.29 求每门课程的学生人数

SELECT
    a.c_name,count(1)as"人数"FROM
    course a
LEFTJOIN
    score b
ON
    a.c_id=b.c_id
groupby
    a.c_id

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

方法一：表连接+子查询SELECT a.*, b.s_score as max_score, b.c_id, c.c_nameFROM student aLEFTJOIN score bON a.s_id=b.s_idLEFTJOIN course cON c.c_id=b.c_idWHERE-- 查询id b.c_id in(SELECT c_id FROM course WHERE t_id in(select t_id from teacher where t_name="张三"))AND-- 查询最大分数 b.s_score=(selectdistinctmax(s_score)from score where c_id="02")
方法二：表连接SELECT a.*, b.s_score as max_score, b.c_id, c.c_nameFROM student aLEFTJOIN score bON a.s_id=b.s_idLEFTJOIN course cON c.c_id=b.c_idLEFTJOIN teacher dON d.t_id=c.t_idWHERE d.t_name="张三"orderby max_score desclimit1;

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

SELECTdistinct
    a.*FROM
    score a,
    score b
WHERE
    a.c_id!=b.c_id
AND
    a.s_score=b.s_score

5.32 统计每门课程的学生选修人数（超过5人的课程才统计）

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
方法一：分组聚合SELECT c_id,count(1)as"选修人数"FROM scoregroupby c_idHAVINGcount(1)>5orderby"选修人数"desc,c_id asc
方法二：连表+分组聚合SELECT a.c_id,count(b.s_id) cntFROM course aLEFTJOIN score bON a.c_id=b.c_idgroupby a.c_idHAVINGcount(b.s_id)>5orderby cnt desc,a.c_id asc

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

SELECT
    s_id
FROM
    score
groupby
    s_id
HAVINGcount(c_id)>=2;

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

方法一：连表查询SELECT a.*FROM student a, score bWHERE a.s_id=b.s_idgroupby s_idHAVINGcount(1)=(selectcount(1)from course)
方法二：子查询SELECT*FROM student aWHERE a.s_id in(select s_id FROM score groupby s_id HAVINGcount(1)=(selectcount(1)from course))

标签： sql 数据库 select

本文转载自: https://blog.csdn.net/m0_46926492/article/details/140520386
版权归原作者 荼靡~ 所有，如有侵权，请联系我们删除。

sql常见50道查询练习题

sql常见50道查询练习题

1. 表创建

1.1 表创建

1.2 数据插入

2. 简单查询例题(3题）

2.1 查询"李"姓老师的数量

2.2 查询男生、女生人数

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

3. 日期相关例题(6题）

3.1 查询各学生的年龄

3.2 查询本周过生日的学生

3.3 查询下周过生日的学生

3.4 查询本月过生日的学生

3.5 查询下月过生日的学生

3.6 查询1990年出生的学生名单

4. 开窗函数查询（7题）

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

4.2 按各科成绩进行排序，并显示排名(实现不完全)

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

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

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

4.6 查询各科成绩前三名的记录

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

5. 表连接+子查询+聚合函数查询(34题）

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

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

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

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

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

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

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

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

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

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

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

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

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

5.14 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

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

5.16 查询各科成绩最高分、最低分和平均分

5.17 统计各科成绩各分数段人数

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

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

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

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

5.22 查询每门课程的平均成绩

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

5.24 查询课程名称为"数学"，且分数低于60的学生姓名和分数

5.25 查询所有学生的课程及分数情况

5.26 查询任何一门课程成绩在70分以上的姓名、课程名称和分数(学生的每门课都大于70)

5.27 查询不及格的课程

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

5.29 求每门课程的学生人数

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

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

5.32 统计每门课程的学生选修人数（超过5人的课程才统计）

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

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

发表评论

“sql常见50道查询练习题”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航