N个SQL高级查询练习

 

最近在网上看到一个SQL练习的好例子，跟着练习了下，重新温习了遍SQL查询，收获颇丰；

好记性不如烂笔头，整理如下，便于日后参考，有误或有更好的SQL请不吝赐教。

1、SQL 类型：MySQL5.7.24

2、执行环境：Navicat for MySQL

一、建表与插入样例数据

 

1、表结构概况

-- 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 分数

 

2、建表

 

-- --创建测试数据

create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(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' , '女');

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');

create table Teacher(TID varchar(10),Tname nvarchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

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);

二、N条select查询

 

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

 

select a.* , b.score 课程01的分数,c.score 课程02的分数 from student a , sc b , sc c

where a.sid = b.sid and a.sid = c.sid and b.cid = '01' and c.cid = '02' and b.score > c.score;

 

 

-- 1.1、查询同时存在"01"课程和"02"课程的情况

 

select a.*,b.score as 课程01的分数,c.score as 课程02的分数 from student a

inner join sc b on a.sid = b.sid and b.cid = '01' and b.score > 0

inner join sc c on a.sid = c.sid and c.cid = '02' and c.score > 0;

 

 

-- 1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)

 

select a.*, b.score 课程01的分数, c.score 课程02的分数 from student a

left join sc b on a.sid = b.sid and b.cid = '01'

left join sc c on a.sid = c.sid and c.cid = '02'

where b.score > 0;

 

 

 

-- 2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况

 

select a.*, b.score 课程01的分数, c.score 课程02的分数 from student a

left join sc b on a.sid = b.sid and b.cid = '01'

left join sc c on a.sid = c.sid and c.cid = '02'

where c.score > 0;

 

 

 

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

 

-- 不好的方法，当课程多时将需要大量一个个算

-- SELECT a.SID, a.Sname, (b.score + c.score)/2 AS avgscore FROM student a,

-- sc b,sc c where a.SID = b.SID and a.SID = c.SID and b.CID = '01' and c.CID = '02'

-- AND (b.score + c.score)/2 >= 60;

 

-- 方法2，分组后，使用聚合函数计算平均值

select a.sid, a.sname, cast(avg(b.score) as decimal(18,2)) as avgscore from student a,sc b

where a.sid = b.sid

group by a.sid,a.sname having avg(b.score) >= 60 order by a.sid;

 

 

-- 4.1、查询在sc表存在成绩的学生信息的SQL语句