MySQL 建库建表作练习题目
create database if not exists school_train;
use school_train;
# 学生表
create table Student(Sno varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');
insert into Student values('04' , N'李云' , '1990-08-06' , N'男');
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');
# 科目表
create table Course(Cno varchar(10),Cname nvarchar(10),Tno varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
# 教师表
create table Teacher(Tno varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
# 成绩表
create table SC(Sno varchar(10),Cno 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);
以下是MySQL题目
1. 查询“ 01 ”课程比" 02 "课程成绩高的学生的信息及课程分数
select A.*,B.Cno,B.score from (select * from SC where Cno='01')A
left join(select * from SC where Cno='02')B
on A.Sno=B.Sno
where A.score>B.score
结果如下
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select A.*,B.Cno,B.score from (select * from SC where Cno='01')A
left join(select * from SC where Cno='02')B
on A.Sno=B.Sno
where A.score is not null and B.score is not null
# 注释掉的是第二种方法
-- select * from (select * from SC where Cno='01')A
-- left join (select * from SC where Cno='02')B on A.Sno=B.Sno
-- where B.Sno is not null
结果如下
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为null)
select A.*,B.Cno,B.score from (select * from SC where Cno='01')A
left join(select * from SC where Cno='02')B
on A.Sno=B.Sno
where A.score is not null
# 第二种方法
-- select * from (select * from SC where Cno='01')A
-- left join (select * from SC where Cno='02')B on A.Sno=B.Sno
结果如下
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select A.*,B.Cno,B.score from (select * from SC where Cno='02')A
left join(select * from SC where Cno='01')B
on A.Sno=B.Sno
where A.score is not null and B.score is null
# 第二种方法
-- select * from SC where Cno='02'and Sno not in(select Sno from SC where Cno='01')
结果如下
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select A.Sno,B.Sname,A.dc from(select Sno,AVG(score)dc from SC group by Sno)A
left join Student B on A.Sno=B.Sno where A.dc>=60
结果如下
3. 查询在 SC 表存在成绩的学生信息
select student.* from SC,student
where SC.Sno=student.Sno
GROUP BY student.Sno
# 第二种方法
-- select * from Student where Sno in (select distinct Sno from SC)
结果如下
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
select B.Sno,B.Sname,A.选课总数,A.总成绩 from
(select Sno,COUNT(Cno)选课总数,sum(score)总成绩 from SC group by Sno)A
right join Student B on A.Sno=B.Sno
4.1 查有成绩的学生信息
select student.Sno,student.Sname,COUNT(Cno)选课总数,sum(score)总成绩 from SC,student
where SC.Sno=student.Sno
GROUP BY SC.Snojieg
# 第二种方法
-- select A.Sno,B.Sname,A.选课总数,A.总成绩 from
-- (select Sno,COUNT(Cno)选课总数,sum(score)总成绩 from SC group by Sno)A
-- left join Student B on A.Sno=B.Sno
结果如下
这是查询学生详细信息的就不展示了，自行查询。
select B.* from SC A left join (select * from student)B
on A.Sno=B.Sno
GROUP BY A.Sno
5.查询「李」姓老师的数量
select COUNT(*)李姓老师数量 from Teacher where Tname like '李%'
结果如下
6.查询学过「张三」老师授课的同学的信息
select sc.Sno,Sname,Sage,Ssex from sc,student
where sc.Sno=student.Sno
and Cno=(select course.Cno from teacher,course
where course.Tno=teacher.Tno and Tname="张三")
# 第二种方法
-- select * from Student
-- where Sno in(select distinct Sno from SC
-- where Cno=(select Cno from Course
-- where Tno=(select Tno from Teacher where Tname='张三')))
7.查询没有学全所有课程的同学的信息
select * from Student where Sno in(select Sno from SC group by Sno
having COUNT(Cno)<(select count(*) from course))
结果如下
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student
where sno in (select sno from sc
where cno in(select cno from sc where sno = '01') and sno != '01'
GROUP BY sno)
# 第二种方法
select * from Student
where Sno in(select distinct Sno from SC where Cno in(select Cno from SC where Sno='01'))
9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from student
where sno in(select sno from sc where cno in(select cno from sc where sno='01') and sno <> '01'
GROUP BY sno
having count(cno)>=(select count(cno) from sc where sno='01'))
10. 查询没学过「张三」老师讲授的任一门课程的学生姓名
select sname from student where sno not in(select sno from sc where cno in (
select cno from course where tno in (select tno from teacher where tname='张三')))
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select sc.sno,sname 姓名,avg(score) 平均成绩 from sc,student
where score<60 and sc.sno=student.sno
GROUP BY sno
having count(cno)>=2
# 第二种方法
select A.Sno,A.Sname,B.平均成绩 from Student A right join
(select Sno,AVG(score)平均成绩 from SC where score<60 group by Sno having COUNT(score)>=2)B
on A.Sno=B.Sno
12.检索" 01 "课程分数小于 60 ，按分数降序排列的学生信息
select student.*,score from sc,student
where cno='01' and score<60 and sc.Sno=student.Sno
order by score desc
13. （静态写法）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select Sno,max(case Cno when '01' then score else 0 end)'01',
max(case Cno when '02' then score else 0 end)'02',
MAX(case Cno when '03' then score else 0 end)'03',AVG(score)平均分 from SC
group by Sno order by 平均分 desc
浙公网安备 33010602011771号