本文共 9040 字，大约阅读时间需要 30 分钟。

– 练习题目来源https://blog.csdn.net/paul0127/article/details/82529216

– 菜鸟的查询思路练习及语句经验累积 包含各种可直接用的语句 函数和语句只是思路的实现过程 –这里是自学经验总结，基础语法在隔壁 2020/9/28编辑 2020/10-27更新

–sql书写顺序：select from where group by order by having

sql执行顺序： from where group by select having order by – 复杂查询思路 1：看sql中是否有统计函数，如果有要想到group by。 2：根据条件看需要几张表，还是自身关联。 3：看是否需要左外连接等。 4：where条件，消除笛卡尔积。 5：having过滤。 6：order by。 7：一般可在from和where后加子查询（子查询中也适用以上条件） 8：有时外键的关联需要3到4张表，消除笛卡尔积，再加条件。 9：一个表要关联另一个表，如果没有直接的外键关联，中间需要好几个表过度，通过各个外键将表关联成一个，然后进行子查询。

– 子查询 左右连接 多表联查 区分 熟练掌握行转列

– 先写明查询条件(先明确要查出的字段与条件和需要的表) 再根据不同条件选择不同查询方式 – 子查询：针对不需要查出null值的场景 当前表不含符合要求字段时 创造符合条件的表 简洁明了是其主要优点 – 左右连接 需要查询出null值时使用 区分：子查询是另一张表 左右连接是根据条件合并为了同一张表 不能分组 – where 本质就是inner jion 会去除null值 – in适用于一个或多个返回结果的情况 适应性比等号更广 例如6题中张三老师可能存在授多门课的情况此时等号不适用 与 SORT_NO BETWEEN ‘0010’ AND ‘0050’ 方式区分 – 区分子查询和多表联查的用法 – 熟练掌握行转列 sum(case when 条件 then 展示的数据 else 展示的数据 end) 列名 – rank() select RANK() OVER( partition by 排序列 order by 排序参照列 排序方式) 别名

– 左右连接练习

CREATE TABLE _left(id VARCHAR(10),name VARCHAR(10)); CREATE TABLE _right(id VARCHAR(10),A_id VARCHAR(10),job VARCHAR(10)); alter table _left add primary key (id); – ALTER TABLE _right ADD FOREIGN KEY (A_id) REFERENCES _left(id); insert into _left values(‘1’ , ‘小王’ ); – 报错先看是不是库、表、字段数据集设置不一致 insert into _left values(‘2’ , ‘小李’ ); insert into _left values(‘3’ , ‘小刚’ ); insert into _right values(‘1’ , ‘2’,‘老师’ ); insert into _right values(‘2’ , ‘4’,‘程序员’ );

– 内连接

SELECT a.name,b.job FROM _left a INNER JOIN _right b on a.id=b.A_id; – 左连接 SELECT a.name,b.job FROM _left a LEFT JOIN _right b on a.id=b.A_id; – 右连接 SELECT a.name,b.job FROM _left a RIGHT JOIN _right b on a.id=b.A_id; – 全外连接 MySql不支持fulljoin SELECT a.name,b.job FROM _left a full JOIN _right b on a.id=b.A_id; – 全连接代替写法 SELECT A.name,B.job FROM _left A LEFT JOIN _right B ON A.id=B.A_id UNION SELECT A.name,B.job FROM _left A RIGHT JOIN _right B ON A.id=B.A_id

create table Student(Sid varchar(6), 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-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’ , ‘女’); show VARIABLES like ‘character%’; alter database day14 character set utf8mb4;

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)

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

insert into Course values(‘01’ , ‘语文’ , ‘02’); insert into Course values(‘02’ , ‘数学’ , ‘01’); insert into Course values(‘03’ , ‘英语’ , ‘03’)

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

insert into Teacher values(‘01’ , ‘张三’); insert into Teacher values(‘02’ , ‘李四’); insert into Teacher values(‘03’ , ‘王五’)

ALTER TABLE SC ADD FOREIGN KEY (Sid) REFERENCES Student(Sid);

ALTER TABLE SC ADD FOREIGN KEY (Cid) REFERENCES Course(Cid); ALTER TABLE Course ADD FOREIGN KEY (Tid) REFERENCES Teacher(Tid);

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

SELECT s.*,a.Score 课程01,b.Score 课程02 FROM student s, (SELECT Sid,Score FROM SC where cid=‘01’) a, (SELECT Sid,Score FROM SC where cid=‘02’) b WHERE a.Score > b.Score and s.Sid=a.Sid and a.Sid=b.Sid

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

SELECT s.sid 学生编号,s.Sname 学生姓名,a.avg(score) 平均成绩 from student s, (SELECT sid,avg(score) from sc GROUP BY sid HAVING avg(score)>60) a where s.Sid= a.Sid

– 3 查询在 SC 表存在成绩的学生信息

SELECT

– 4 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT s.sid 学生编号,s.Sname 学生姓名,a.选课总数,a.总成绩 FROM student s, (SELECT Sid,count(cid) 选课总数,sum(Score) 总成绩 FROM sc GROUP BY sid) a where a.Sid=s.Sid – 写法错误 需要展示null值 要用左连接 SELECT s.sid 学生编号,s.Sname 学生姓名,count(cid) 选课总数,sum(score) 总成绩 FROM student s LEFT JOIN sc ON s.Sid=sc.sid GROUP BY s.Sid

– 4.1 查有成绩的学生信息

SELECT * from student s where s.sid in (SELECT sid from sc GROUP BY sid) – 要查的是学生信息该行转列附带成绩么？

– 5 查询「李」姓老师的数量 %替代0个或多个字符 _替代一个字符

select count(*) 姓李老师数量 from teacher t where t.Tname like ‘李%’

– 6 查询学过「张三」老师授课的同学的信息

SELECT * from student s LEFT JOIN sc ON sc.Sid=s.Sid where cid IN (SELECT cid from teacher t left JOIN course c on t.Tid=c.Tid where Tname=‘张三’) – 思路相同但是把条件写在一起更简洁明了 也不需要左连接

– 7 查询没有学全所有课程的同学的信息 --没有劝学所有课程作为建立条件子查询

SELECT * from student s where s.Sid in (select sid from sc GROUP BY Sid HAVING count(cid)❤️)

– 🔺9 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 01同学学习的课程用子查询作为条件查出所需信息

– 学习的课程完全相同 有的都有没有的不能有 条件查出后用in？same？ select cid from sc where sid=‘01’ GROUP BY cid – 不太懂答案 如何确定01的选课>=3的？提前查出来当作条件？ 那如果01选课变化sql不就不能用了么 select * from Student where Sid in( select Sid from SC where Cid in (select Cid from SC where Sid = ‘01’) and Sid <>‘01’ group by Sid having COUNT(Cid)>=3)

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

– 很好理解 子表查出01选的课 用in筛选就好 SELECT s.* from student s, sc c where c.cid in (select cid from sc c where c.sid=01 GROUP BY cid) and s.Sid=c.sid GROUP BY s.sid

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

– 还是以 "张三"老师讲授的任一门课程 为条件用子表查出来 使用not in筛选 select * from sc where cid not in (select cid FROM course where tid=(select tid from teacher where tname=‘张三’)) GROUP BY sid – 可以不用子查询 直接多表联查就好 逻辑是没学过张三老师课的sname select sname from student where sname not in ( select s.sname from student as s, course as c, teacher as t, sc where s.sid = sc.sid and sc.cid = c.cid and c.tid = t.tid and t.tname = ‘张三’ )

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

select s.sid, s.sname, avg(score) 平均成绩 from student as s, sc where s.sid = sc.sid and score<60 group by s.sid having count(score)>=2 – 用分组和having

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

select * from sc,student s WHERE sc.sid=s.Sid and cid=‘01’ and score<‘60’ ORDER BY score desc

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

– 用行转列 select sid, sum(case when cid=01 then score else null end) as score_01, sum(case when cid=02 then score else null end) as score_02, sum(case when cid=03 then score else null end) as score_03, avg(score) from sc group by sid order by avg(score) desc

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

select s.cid,c.cname, max(s.score) 最高分,min(s.score) 最低分,avg(s.score) 平均分, sum(case WHEN s.score>=‘60’ then 1 else 0 end)/count() 及格率,

sum(case WHEN s.score>=‘70’ and s.score<=‘80’ then 1 else 0 end)/count() 中等率, sum(case WHEN s.score>=‘80’ and s.score<=‘90’ then 1 else 0 end)/count() 优良率,

sum(case WHEN s.score>=‘90’ then 1 else 0 end)/count() 优秀率

from sc s,course c

where s.cid=c.cid GROUP BY cid

– 15 按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺

– rank排序函数使用 select RANK() OVER( partition by 排序列 order by 排序参照列 排序方式) 别名 select s.*, rank_01, rank_02, rank_03, rank_total from student s left join (select sid, rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid left join (select sid, rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid left join (select sid, rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid left join (select sid, rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid order by rank_total asc

– 16 按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次

select s.*, rank_01, rank_02, rank_03, rank_total from student s left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid left join (select sid, dense_rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid order by rank_total asc

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

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

– 19 查询出只选修两门课程的学生学号和姓名

– 20 查询名字中含有「风」字的学生信息

– 21 查询 1990 年出生的学生名单

– 22 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

– 23 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

– 24 查询各学生的年龄，只按年份来算

– 25 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

– 26 查询本周过生日的学生

– 27 查询下周过生日的学生

– 28 查询本月过生日的学生

– 29 查询下月过生日的学生

转载地址：https://blog.csdn.net/Beatingworldline/article/details/108844043 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！