MySQL3--查询案例解析

1、素材

-- 创建数据库
create database test;

-- 锁定数据库
use test;

-- 学生表
create table student(
    Sid int primary key,
    Sname varchar(15),
    Sage int,
    Ssex char(1)
);
insert into student values 
(1001,'张锋',18,'男'),
(1002,'李涛',20,'女'),
(1003,'王璐',19,'男'),
(1004,'赵鹏',21,'女'),
(1005,'李丁',17,'男'),
(1006,'吴淞',18,'男'),
(1007,'胡松',19,'女'),
(1008,'刘丽',18,'女'),
(1009,'钱坤',20,'男'),
(1010,'周舟',21,'男');

-- 教师表
create table teacher(
    Tid int primary key auto_increment,
    Tname varchar(15)
);
insert into teacher values 
(1,'叶平'),
(2,'马克'),
(3,'李魁'),
(4,'李鬼');

-- 课程表
create table course(
    Cid varchar(5) primary key,
    Cname varchar(15),
    Tid int
);
insert into course values 
('001','企业管理',1),
('002','马克思',1),
('003','UML',2),
('004','数据库',2),
('005','Java',3);

-- 成绩表
create table SC(
    Sid int,Cid varchar(5),score int,
    primary key(Sid,Cid)
);
insert into sc values 
(1001,'001',81),
(1001,'002',81),
(1001,'003',99),
(1001,'004',58),
(1001,'005',90),
(1002,'001',78),
(1002,'002',88),
(1002,'003',91),
(1002,'004',92),
(1003,'003',93),
(1003,'004',77),
(1004,'001',82),
(1004,'002',83),
(1005,'001',98),
(1005,'002',79),
(1006,'005',80);

2、查询题目及解析

/*删除1002同学的001课程的成绩*/
delete from SC where Sid=1002 and Cid='001';

/*查询李姓同学的名单*/
select Sname from Student where Sname like '李%'; 

/*查询004课程小于60分的同学学号，按分数倒序显示*/
select Sid from SC  WHERE Cid='004' and score<60 order by score desc;

/*查询每门课程选修人数*/
select Cid,count(*) from SC group by Cid;

/*查询至少学过两门课程的学生学号*/
select Sid from SC group by Sid having count(*)>=2;

/*查询全部学生都选修了的课程编号与名称*/
select Cid,Cname from Course where Cid in(select Cid from sc group by Cid);

/*查询不同课程成绩相同的学号、课程号、成绩*/
select DISTINCT a.sid,a.cid,b.score from sc a,sc b where a.score=b.score and a.cid<>b.cid;

/*查询所有同学的选修课情况（姓名，课程，分数）*/
select a.sname,b.cname,c.score from student a,course b,sc c WHERE a.sid=c.sid and b.cid=c.cid;

/*查询同名同姓同学名单，并统计人数*/
SELECT sname,count(*) from student group by sname having COUNT(*)>1;

/*查询姓李的老师人数*/
select count(DISTINCT(tname)) from teacher where tname like'李%';

/*查询平均成绩大于60分的同学的学号和平均成绩*/
select sid,avg(score) from sc  GROUP by sid having AVG(score)>60;

/*查询所有同学的学号、姓名、选课数、总成绩*/
select a.Sid,a.Sname,count(b.Cid),sum(b.score)
from student a left join sc b on a.Sid=b.Sid 
group by a.Sid,Sname;

/*查询001课程比002课程成绩高的所有学生学号*/
select a.Sid from
(select Sid,score from sc where Cid='001') a,
(select Sid,score from sc where Cid='002') b
where a.score>b.score and a.Sid=b.Sid;

/*查询没学过叶平老师课程的同学学号与姓名*/
select a.Sid,a.Sname from student a 
where a.Sid not in(select distinct(b.Sid) 
from sc b,course c,teacher d 
where b.Cid=c.Cid and d.Tid=c.Tid and d.Tname='叶平');

/*查询不及格的课程*/
select Cname from Course a,SC b where a.Cid=b.Cid 
and b.score<60;

/*查询两门以上不及格课程的同学的学号及平均成绩*/
select Sid,avg(score) from SC where Sid 
in(select Sid from SC where score<60 group by Sid 
having count(*)>2) group by Sid;

/*统计各科成绩达到A、B、C、D的各有多少人*/
select cname,sum(case when score>=90 and score<=100 
then 1 else 0 end) as 'A',sum(case when score>=80 
and score<=90 then 1 else 0 end) as 'B', sum(case when score<80 
then 1 else 0 end) as 'C' from sc,course 
where sc.cid=course.cid group by cname;

/*查询没有学全所有课程的同学学号与姓名*/
select a.Sid,a.Sname from Student a,SC b
where a.Sid=b.Sid group by a.Sid,a.Sname 
having count(*)<(select count(*) from Course);

/*查询只选修了一门课程的全部同学的学号和姓名*/
select a.Sid,a.Sname,count(*) from Student a,SC b
where b.Sid=a.Sid group by b.Sid,a.Sname having count(*)=1;

/*查询各科成绩前三名的记录（不考虑并列）*/
select Sid,Cid,Score from SC a  where
(select count(*) from SC b where b.Cid=a.Cid and b.Score>=a.Score)<=3; 

/*查询学过叶平老师所有课程的同学学号与姓名*/
select Sid,Sname from Student where Sid in(select Sid from SC a,Course b,Teacher c 
where a.Cid=b.Cid and b.Tid=c.tid and c.Tname='叶平' group by Sid
having count(a.Cid)=(select count(Cid) from Course d,Teacher e 
where e.Tid=d.Tid 
and e.Tname='叶平'));