MySQL习题

搜集的一些MySQL的基础习题附自己的答案，不涉及排名。答案不是最好的写法，是比较基本的思路。

建表

一共有四个表，主键对应关系为学生表对应成绩表，再到课程表，最后到教师表。

## 学生表
create table Student(id varchar(10),name varchar(10),age datetime,sex 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(C varchar(10),Cname varchar(10),T varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
##教师表
create table Teacher(T varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
##成绩表
create table SC(S varchar(10),C 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);

–查询男生、女生人数

select count(sex) from student
GROUP BY sex; 

–查询名字中含有”风”字的学生信息

select a.* from student a
where a.name like '%风%';

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

select a.* from student a
where year(a.age)=1990;

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

select a.name,b.score from student a
INNER JOIN sc b on a.id=b.S
INNER JOIN course c on b.C=c.C
where c.Cname='数学' and b.score<60;

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

select a.*,e.score from student a
RIGHT JOIN (
select b.*,max(b.score),d.* from sc b
INNER JOIN course c on b.C=c.C
INNER JOIN teacher d on c.T=d.T
GROUP BY b.C
HAVING Tname='张三')e on a.id=e.S;

–统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select a.C,count(*)as num from sc a 
GROUP BY a.C
HAVING num>5
ORDER BY C,num DESC;

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

select id,name,age,sex,b.score as '01成绩',c.score as '02成绩' from student
inner JOIN sc b on b.S = student.id and b.C='01'
INNER JOIN sc c on c.S = student.id and c.C='02'
where b.score>c.score;

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

select id,name,avg(sc.score) as avg_score from student
INNER JOIN sc on sc.S=student.id
GROUP BY sc.S
HAVING avg_score>=60;

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

select id,name,count(sc.C) as courese_num,sum(sc.score) as sum_course_score from student
INNER JOIN sc on sc.S=student.id
GROUP BY sc.S;

–查询”李”姓老师的数量

select count(*) as num from teacher t
where t.Tname LIKE '李%';

–查询学过”张三”老师授课的同学的信息

select * from student
INNER JOIN sc b on b.S=student.id
inner JOIN course c on b.C=c.C
inner JOIN teacher d on c.T=d.T
HAVING d.Tname='张三';

–查询没学过”张三”老师授课的同学的信息

select a.* from student a
LEFT JOIN sc on sc.S=a.id
where not EXISTS(
select * from sc b
inner JOIN course c on b.C=c.C
inner JOIN teacher d on c.T=d.T
HAVING d.Tname='张三' and b.S=a.id)
GROUP BY 1,2,3,4;

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

SELECT a.* from student a
INNER JOIN sc b on b.S=a.id and b.C='01'
INNER JOIN sc c on c.S=a.id and c.C='02';

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

SELECT a.* from student a
INNER JOIN sc b on b.S=a.id and b.C='01'
INNER JOIN sc c on b.S=a.id and c.C<>'02'
GROUP BY 1,2,3,4;

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

select a.*,count(b.C) as num from student a
LEFT JOIN sc b on a.id=b.S
GROUP BY a.id
HAVING num<(select count(C) from course);

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

select a.* from student a
INNER JOIN sc b on a.id=b.S
where C in (
select C from sc
where S='01') and S<>'01'
GROUP BY id;

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

select a.* from student a
INNER JOIN sc b on a.id=b.S
INNER JOIN(
select C from sc
where S='01') and S<>'01'
GROUP BY id;

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

select a.*,avg(score) from student a 
INNER JOIN sc b on a.id=b.S 
GROUP BY id
HAVING sum(case when score<60 then 1 else 0 end)>2;

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

select a.*,score from student a
INNER JOIN sc b on a.id=b.S
where (b.C='01' and b.score<60)
ORDER BY score DESC;

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

select id,name,sum(score) as sum_score,avg(score) as avg_score from student a
INNER JOIN sc b on a.id=b.S
GROUP BY id
ORDER BY sum_score DESC;

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

select a.C,a.Cname,
max(b.score) as '最高分',min(b.score) as '最低分',avg(b.score) as '平均分',
sum(case when b.score >=60 then 1 else 0 end)/count(1) as '及格率',
sum(case when b.score BETWEEN 70 and 80 then 1 else 0 end)/count(1) as '中等率',
sum(case when b.score BETWEEN 80 and 90 then 1 else 0 end)/count(1) as '优良率',
sum(case when b.score >=90 then 1 else 0 end)/count(1) as '优秀率'
from course a
INNER JOIN sc b on a.C=b.C
GROUP BY b.C;

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

select a.*,avg(c.score)as avg_score from teacher a
INNER JOIN course b on a.T=b.T
INNER JOIN sc c on b.C=c.C
GROUP BY a.T
ORDER BY avg_score DESC;

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

select a.*,count(b.C)as num from course a
INNER JOIN sc b on a.C=b.C
GROUP BY a.C;

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

select id,name from student a
INNER JOIN sc b on a.id=b.S
GROUP BY a.id 
HAVING count(b.C)=2;