创表语句
create database school;
use school;
create table student(
s_id varchar(10),
s_name varchar(20),
s_age date,
s_sex varchar(10)
);
s_id varchar(10),
s_name varchar(20),
s_age date,
s_sex varchar(10)
);
create table course(
c_id varchar(10),
c_name varchar(20),
t_id varchar(10)
);
c_id varchar(10),
c_name varchar(20),
t_id varchar(10)
);
create table teacher (
t_id varchar(10),
t_name varchar(20)
);
create table score (
s_id varchar(10),
c_id varchar(10),
score varchar(10)
);
s_id varchar(10),
c_id varchar(10),
score varchar(10)
);
insert into student (s_id, s_name, s_age, s_sex)
values ('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
insert into course (c_id, c_name, t_id)
values ('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
values ('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
insert into teacher (t_id, t_name)
values ('01' , '张三'),
('02' , '李四'),
('03' , '王五');
values ('01' , '张三'),
('02' , '李四'),
('03' , '王五');
insert into score (s_id, c_id, score)
values ('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);
values ('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);
# 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.s_id,a.score from (select * from score where c_id='01') as a,
(select * from score where c_id='02') as b
where a.s_id=b.s_id and a.score>b.score;
# 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.s_id,a.score from (select * from score where c_id='01') as a,
(select * from score where c_id='02') as b
where a.s_id=b.s_id and a.score<b.score;
# 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select score.*,AVG(score.score) from score GROUP BY score.s_id HAVING AVG(score.score)>=60
# 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select score.*,AVG(score.score) from score GROUP BY score.s_id HAVING AVG(score.score)<60
# 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s_id,s.s_name,s2.countcou '选课数',s2.sumscore '总成绩' from student s,
(select s1.s_id,SUM(s1.score) as sumscore,COUNT(s1.c_id) as countcou from score s1 GROUP BY s1.s_id) as s2
where s.s_id=s2.s_id
# 6、查询"李"姓老师的数量
select COUNT(*) from teacher t where t.t_name like('李%')
# 7、查询学过"张三"老师授课的同学的信息
select s.* from student s ,score s1,(select t.t_id,c.c_id from teacher t,course c where t.t_name='张三' and t.t_id=c.t_id) s2
where s.s_id = s1.s_id and s1.c_id=s2.c_id
# 8、查询没学过"张三"老师授课的同学的信息
select * from student where student.s_id not in
(select s.s_id from score s where s.c_id
in (select c.c_id from teacher t ,course c where t.t_id=c.t_id and t.t_name='张三'))
#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student
where s_id in(
select sc1.s_id
from score sc1,
score sc2
where
sc1.s_id = sc2.s_id
and sc1.c_id = '01'
and sc2.c_id = '02'
);
select a.s_id,a.score from (select * from score where c_id='01') as a,
(select * from score where c_id='02') as b
where a.s_id=b.s_id and a.score>b.score;
# 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.s_id,a.score from (select * from score where c_id='01') as a,
(select * from score where c_id='02') as b
where a.s_id=b.s_id and a.score<b.score;
# 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select score.*,AVG(score.score) from score GROUP BY score.s_id HAVING AVG(score.score)>=60
# 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select score.*,AVG(score.score) from score GROUP BY score.s_id HAVING AVG(score.score)<60
# 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s_id,s.s_name,s2.countcou '选课数',s2.sumscore '总成绩' from student s,
(select s1.s_id,SUM(s1.score) as sumscore,COUNT(s1.c_id) as countcou from score s1 GROUP BY s1.s_id) as s2
where s.s_id=s2.s_id
# 6、查询"李"姓老师的数量
select COUNT(*) from teacher t where t.t_name like('李%')
# 7、查询学过"张三"老师授课的同学的信息
select s.* from student s ,score s1,(select t.t_id,c.c_id from teacher t,course c where t.t_name='张三' and t.t_id=c.t_id) s2
where s.s_id = s1.s_id and s1.c_id=s2.c_id
# 8、查询没学过"张三"老师授课的同学的信息
select * from student where student.s_id not in
(select s.s_id from score s where s.c_id
in (select c.c_id from teacher t ,course c where t.t_id=c.t_id and t.t_name='张三'))
#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student
where s_id in(
select sc1.s_id
from score sc1,
score sc2
where
sc1.s_id = sc2.s_id
and sc1.c_id = '01'
and sc2.c_id = '02'
);
#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select s.*,sc1.score,sc2.score
from student s
left join (select * from score where c_id = '01') sc1 on s.s_id = sc1.s_id
left join (select * from score where c_id = '02') sc2 on s.s_id = sc2.s_id
where sc1.c_id = '01' and sc2.c_id is null;
select s.*,sc1.score,sc2.score
from student s
left join (select * from score where c_id = '01') sc1 on s.s_id = sc1.s_id
left join (select * from score where c_id = '02') sc2 on s.s_id = sc2.s_id
where sc1.c_id = '01' and sc2.c_id is null;
#11、查询没有学全所有课程的同学的信息
select s.* from student s
left join Score s1 on s1.s_id=s.s_id
group by s.s_id
having count(s1.c_id)<(select count(*) from course)
select s.* from student s
left join Score s1 on s1.s_id=s.s_id
group by s.s_id
having count(s1.c_id)<(select count(*) from course)
#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in(
select distinct a.s_id from score a where a.c_id
in(select a.c_id from score a where a.s_id='01')
);
select * from student where s_id in(
select distinct a.s_id from score a where a.c_id
in(select a.c_id from score a where a.s_id='01')
);
#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student
where s_id in (select s_id from score t1 group by s_id
having group_concat(c_id) =
( select group_concat(c_id) as str2 from score
where s_id = '01')
and s_id != '01');
select * from student
where s_id in (select s_id from score t1 group by s_id
having group_concat(c_id) =
( select group_concat(c_id) as str2 from score
where s_id = '01')
and s_id != '01');
#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student s
where s.s_id not in(
select sc.s_id from score sc
where sc.c_id in(
select c.c_id from course c
where c.t_id = (
select t.t_id from teacher t
where t.t_name = '张三'
)
)
)
select * from student s
where s.s_id not in(
select sc.s_id from score sc
where sc.c_id in(
select c.c_id from course c
where c.t_id = (
select t.t_id from teacher t
where t.t_name = '张三'
)
)
)
#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
#个体的某一个信息进行统计操作 使用having 和 GROUP BY
select student.s_id, student.s_name, AVG(score.score)
from student,score
where student.id = score.s_id and score.score<60
group by score.s_id
having count(*) > 1;
# 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select s2.* from score s1,student s2
where s1.c_id=1
and s1.s_id=s2.s_id
and s1.score <60
ORDER BY s1.score
# 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s1.s_id,s1.c_id,s1.score,t1.avgscores
from score s1 left join (select s2.s_id,AVG(s2.score) AS avgscores from score s2 GROUP BY s2.s_id) as t1
on s1.s_id =t1.s_id
ORDER BY t1.avgscores desc
# 18、查询每门课程被选修的学生数
select s1.c_id,count(*) from score s1 GROUP BY s1.c_id
# 19、查询出只有两门课程的全部学生的学号和姓名
select s1.s_id,s1.s_name
from student s1,score s2
where s1.s_id=s2.s_id
GROUP BY s2.s_id
HAVING COUNT(*) = 2
# 20、查询男生、女生人数
select s.s_sex,count(*) AS '人数' from student s
group by s.s_sex
-- 21、查询名字中含有"风"字的学生信息
select * from student s where s.s_name like('%风%')
-- 22、查询同名同姓学生名单，并统计同名人数
select t1.* from student s2 left join (select s1.s_name,s1.s_sex,count(*) as '同名人数' from student s1 group by s1.s_sname)as t1 on s2.s_name = t1.s_name and s2.s_sex= t1.s_sex where t1.同名人数>1
-- 23、查询1990年出生的学生名单
select * from student s where YEAR(s.s_age) = 1990
# 24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
select s1.c_id, AVG(s1.score) '平均成绩' from score s1
GROUP BY s1.c_id
ORDER BY AVG(s1.score) desc,s1.c_id asc
#个体的某一个信息进行统计操作 使用having 和 GROUP BY
select student.s_id, student.s_name, AVG(score.score)
from student,score
where student.id = score.s_id and score.score<60
group by score.s_id
having count(*) > 1;
# 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select s2.* from score s1,student s2
where s1.c_id=1
and s1.s_id=s2.s_id
and s1.score <60
ORDER BY s1.score
# 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s1.s_id,s1.c_id,s1.score,t1.avgscores
from score s1 left join (select s2.s_id,AVG(s2.score) AS avgscores from score s2 GROUP BY s2.s_id) as t1
on s1.s_id =t1.s_id
ORDER BY t1.avgscores desc
# 18、查询每门课程被选修的学生数
select s1.c_id,count(*) from score s1 GROUP BY s1.c_id
# 19、查询出只有两门课程的全部学生的学号和姓名
select s1.s_id,s1.s_name
from student s1,score s2
where s1.s_id=s2.s_id
GROUP BY s2.s_id
HAVING COUNT(*) = 2
# 20、查询男生、女生人数
select s.s_sex,count(*) AS '人数' from student s
group by s.s_sex
-- 21、查询名字中含有"风"字的学生信息
select * from student s where s.s_name like('%风%')
-- 22、查询同名同姓学生名单，并统计同名人数
select t1.* from student s2 left join (select s1.s_name,s1.s_sex,count(*) as '同名人数' from student s1 group by s1.s_sname)as t1 on s2.s_name = t1.s_name and s2.s_sex= t1.s_sex where t1.同名人数>1
-- 23、查询1990年出生的学生名单
select * from student s where YEAR(s.s_age) = 1990
# 24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
select s1.c_id, AVG(s1.score) '平均成绩' from score s1
GROUP BY s1.c_id
ORDER BY AVG(s1.score) desc,s1.c_id asc
# 25、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select t1.*,s1.s_name from(select s2.s_id,AVG(s2.score)'平均成绩' from score s2 group by s2.s_id) as t1 left join student s1 on t1.s_id = s1.s_id where t1.平均成绩>=85
# 26、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select * from course c
inner join score sc on c.c_id = sc.c_id
inner join student stu on sc.s_id = stu.s_id
where c.c_name = '数学'
# 27、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s1.s_name,c1.c_name,s2.score from student s1,course c1,score s2 where s1.s_id = s2.s_id and c1.c_id = s2.c_id and s2.score >70
# 28、查询不及格的课程
select distinct s1.c_id from score s1 where s1.score < 60
# 29、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select st.s_id,st.s_name,sc.score from student st
inner join score sc using(s_id)
where sc.c_id='01' and sc.score>=80;
# 30、求每门课程的学生人数
select s1.c_id,count(*)'学生人数' from score s1 GROUP BY s1.s_id
# 31、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select * from score s2 where EXISTS
(select * from score s1 where s1.s_id=s2.s_id and s1.c_id!=s2.c_id and s1.score=s2.score)
#32、查询每门功成绩最好的前两名
select * from score s1 where
(select COUNT(*) from score s2 where s1.c_id=s2.c_id and s2.score>s1.score) <2
ORDER BY s1.c_id
# 33、检索至少选修两门课程的学生学号
select s1.s_id from score s1 GROUP BY s1.s_id HAVING COUNT(*)>=2
# 34、查询选修了全部课程的学生信息
select s2.* from score s1 ,student s2 where s1.s_id=s2.s_id
GROUP BY s1.s_id HAVING COUNT(*)=(select COUNT(*) from course)
select t1.*,s1.s_name from(select s2.s_id,AVG(s2.score)'平均成绩' from score s2 group by s2.s_id) as t1 left join student s1 on t1.s_id = s1.s_id where t1.平均成绩>=85
# 26、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select * from course c
inner join score sc on c.c_id = sc.c_id
inner join student stu on sc.s_id = stu.s_id
where c.c_name = '数学'
# 27、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s1.s_name,c1.c_name,s2.score from student s1,course c1,score s2 where s1.s_id = s2.s_id and c1.c_id = s2.c_id and s2.score >70
# 28、查询不及格的课程
select distinct s1.c_id from score s1 where s1.score < 60
# 29、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select st.s_id,st.s_name,sc.score from student st
inner join score sc using(s_id)
where sc.c_id='01' and sc.score>=80;
# 30、求每门课程的学生人数
select s1.c_id,count(*)'学生人数' from score s1 GROUP BY s1.s_id
# 31、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select * from score s2 where EXISTS
(select * from score s1 where s1.s_id=s2.s_id and s1.c_id!=s2.c_id and s1.score=s2.score)
#32、查询每门功成绩最好的前两名
select * from score s1 where
(select COUNT(*) from score s2 where s1.c_id=s2.c_id and s2.score>s1.score) <2
ORDER BY s1.c_id
# 33、检索至少选修两门课程的学生学号
select s1.s_id from score s1 GROUP BY s1.s_id HAVING COUNT(*)>=2
# 34、查询选修了全部课程的学生信息
select s2.* from score s1 ,student s2 where s1.s_id=s2.s_id
GROUP BY s1.s_id HAVING COUNT(*)=(select COUNT(*) from course)