#1.自行创建测试数据
create database homework;
use homework;
View Code# 年级表->老师表->课程表->班级表->学生表->成绩表->班级任职表
#------------------------------------------------------#
# 年级表
create table class_grade (
gid int PRIMARY KEY auto_increment,
gname varchar(10) not null
);
View Code
insert into class_grade values
(1,'一年级'),
(2,'二年级'),
(3,'三年级');
# 插入数据
#------------------------------------------------------#
create table teacher (
tid int PRIMARY KEY auto_increment,
tname varchar(10) not null
);
# 老师表
insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');
# 插入数据
#------------------------------------------------------#
create table course(
cid int primary key auto_increment,
cname varchar(10) not null unique ,
teacher_id int not null ,
foreign key (teacher_id) references teacher(tid)
on delete cascade
on update cascade
);
# 课程表
insert into course values
(1,'生物',1),
(2,'体育',1),
(3,'物理',2);
# 插入数据
#------------------------------------------------------#
create table class (
cid int primary key auto_increment,
caption varchar(10) not null unique ,
grade_id int not null,
FOREIGN KEY (grade_id) REFERENCES class_grade(gid)
on delete cascade
on update cascade
);
# 班级表
insert into class values
(1,'一年一班',1),
(2,'二年一班',2),
(3,'三年二班',3);
# 插入数据
#------------------------------------------------------#
create table student(
sid int primary key auto_increment,
sname varchar (10) not null ,
gender enum('男', '女') not null default '男',
class_id int not null ,
FOREIGN KEY (class_id) REFERENCES class(cid)
on delete cascade
on update cascade
);
# 学生表
insert into student values
(1,'乔丹','女',1),
(2,'艾弗森','女',1),
(3,'科比','男',2);
# 插入数据
#------------------------------------------------------#
create table score(
sid int primary key auto_increment,
student_id int not null,
course_id int not null,
score int not null ,
FOREIGN KEY (student_id) REFERENCES student(sid)
on delete cascade
on update cascade ,
FOREIGN KEY (course_id) REFERENCES course(cid)
on delete cascade
on update cascade
);
# 成绩表
insert into score values
(1,1,1,60),
(2,1,2,59),
(3,2,2,99);
# 插入数据
#------------------------------------------------------#
create table teacher2cls(
tcid int primary key auto_increment,
tid int not null ,
cid int not null,
FOREIGN key (tid) REFERENCES teacher(tid)
on delete cascade
on update cascade ,
FOREIGN key (cid) REFERENCES class(cid)
on delete cascade
on update cascade
);
# 班级任职表
insert into teacher2cls values
(1,1,1),
(2,1,2),
(3,2,1),
(4,3,2);
# 插入数据
#########################################################
select count(sid) as '学生总人数' from student;
#2.查询学生总人数
#########################################################
select sname,sid from student where sid in
(select student_id from score inner join
course on course_id = cid where course.cname in ('生物','物理') and score.score >= 60
group by score.student_id having count(course_id) = 2
);
#3.查询"生物"课程和"物理"课程成绩都及格的学生id和姓名
#########################################################
select gname from class_grade inner join
(select grade_id,count(cid) as '班级数' from class group by grade_id order by '班级数' limit 3) as t1
on class_grade.gid = t1.grade_id;
#4.查询每个年级的班级数,取出班级数最多的前三个年级;
#########################################################
create view tmp as (
select * from student inner join
(select avg(score) as avg_score ,student_id from score group by student_id) as t1
on student.sid = t1.student_id
);
select sid,sname,avg_score from tmp where
tmp.avg_score =(select avg_score from tmp order by avg_score desc limit 1)
or
tmp.avg_score =(select avg_score from tmp order by avg_score limit 1);
# 5.查询平均成绩最高和最低的学生的id和姓名以及平均成绩
#########################################################
select gname,count(sid) as student_num from class inner join class_grade
on class.grade_id = class_grade.gid inner join
(select sid,cid,student.sname from class inner join student on class.cid = student.class_id) as t1
on t1.cid = class.cid
group by sid;
# 6.查询每个年级的学生人数
#########################################################
select sid,student.sname,choose_num,avg_score from student inner join
(select student.sname,count(student_id) as choose_num,avg(score) as avg_score from score inner join student
on student.sid = score.student_id group by student_id) as t1
on t1.sname = student.sname;
# 7.查询每位学生的学号,姓名,选课数,平均成绩;
#########################################################
select sname,cname,score from
(select sid,sname,cname,score from student inner join
(select student_id,cname,score from score inner join course on course_id = cid) as t1
on sid = t1.student_id
where sid = 2 and score in
((select max(score) from score),(select min(score)from(score))))as t2;
# 8.查询学生编号为'2'的学生的姓名,该学生成绩最高的课程名,成绩最低的课程名及分数
#########################################################
create view tmp1 as
(select tname,caption from class inner join
(select tname,cid from teacher2cls inner join
(select tid,tname,count(tname)as count_num from teacher where tname like '李%' group by tid) as t1
on t1.tid = teacher2cls.tid)as t2
on t2.cid = class.cid);
select count(tname)as '姓李个数',count(caption)as '带班级个数' from tmp1 ;
# 9.查询姓'李'的老师的个数和所带班级数
#########################################################
select gid,gname from class_grade
inner join class
on class.grade_id = class_grade.gid
group by grade_id
having count(caption)<5;
# 10.查询班级数小于5的年级id和年级名
#########################################################
select
class.cid as '班级id',
class.caption as '班级名称',
class_grade.gname as '年级',
case
when class_grade.gid in (1,2) then '低'
when class_grade.gid in (3,4) then '中'
when class_grade.gid in (5,6) then '高'else 0
end as '年级级别'
from class
left join class_grade on class.grade_id = class_grade.gid;
# 11.查询班级信息,包括班级id,班级名称,年级,年级级别(12为低年级,34为中年级,56为高年级)
#########################################################
select sid,sname from student
where sid in
(
select score.student_id from score
left join course
on score.course_id = course.cid
where course.teacher_id in
(
select tid from teacher
where tname = '张三'
)
group by student_id
having count(course.cid) >2
);
# 12.查询学过'张三'老师2门课以上的同学的学号,姓名
#########################################################
select tid,tname from teacher
inner join course
on teacher.tid = course.teacher_id
group by teacher_id
having count(cid)>2;
# 13.查询教授课程超过2门的老师的id和姓名
#########################################################
select sid,sname from student where sid in
(select student_id from score where course_id in (1,2) group by student_id having count(student_id) >=2);
# 14.查询学过编号'1'课程和编号'2'课程的同学的学号,姓名
#########################################################
select tid,tname from teacher
where tid not in
(select tid from teacher2cls
left join class
on teacher2cls.cid = class.cid
where grade_id in (5,6)
);
# 15.查询没有带过高年级的老师id和姓名
#########################################################
SELECT DISTINCT sid,sname from student
inner JOIN
(select student_id from score
LEFT JOIN
(select tid,cid from course
LEFT JOIN teacher
on tid = teacher_id
where tname = '张三') as t
on cid = course_id) as t1
on sid = t1.student_id;
# 16.查询学过'张三'老师所教的所有课的同学的学号,姓名
#########################################################
select t.tid,tname from teacher t
INNER JOIN teacher2cls t_c
on t_c.tid = t.tid
GROUP BY t_c.tid
HAVING count(cid)>2;
# 17.查询带过超过2个班级的老师的id和姓名
#########################################################
SELECT sid,sname from student
where sid in
(select t1.student_id from
(SELECT student_id,score from score
where course_id = 2) as t1,
(SELECT student_id,score from score
where course_id = 1) as t2
where t1.student_id = t2.student_id and t1.score<t2.score);
# 18.查询课程编号'2'的成绩比课程编号'1'课程低的所有同学的学号,姓名
#########################################################
select t.tid,tname from teacher t
INNER JOIN
(select tid,max(count_class) as max_class from
(select tid,count(cid) as count_class from teacher2cls
GROUP BY tid) as t1) as t2
on t.tid = t2.tid;
# 19.查询所带班级数最多的老师id和姓名
#########################################################
SELECT st.sid,sname from student st
INNER JOIN score sc
on st.sid = sc.student_id
where score <60;
# 20.查询有课程成绩小于60分的同学的学号,姓名
#########################################################
select sid,sname from student
where sid not IN
(SELECT student_id from score
GROUP BY student_id
HAVING count(course_id) = (select count(cid) from course));
# 21.查询没有学全所有课的同学的学号,姓名
#########################################################
SELECT st.sid,sname from student st
where st.sid in
(select t2.sid from
(select s.sid,sname,course_id from student s
INNER JOIN score
on s.sid = student_id
where s.sid = 1) as t1,
(select s.sid,sname,course_id from student s
INNER JOIN score
on s.sid = student_id
where s.sid <> 1) as t2
where t2.course_id = t1.course_id);
# 22.查询至少有一门课与学号为'1'的同学所学相同的同学的学号和姓名
#########################################################
SELECT st.sid,sname from student st
INNER JOIN score sc
on st.sid = sc.student_id
where st.sid <> 1
and course_id in
(SELECT course_id from student st
INNER JOIN score sc
on st.sid = sc.student_id
where st.sid = 1);
# 23.查询至少学过学号为'1'同学所选课程中任意一门课的其他同学学号和姓名
#########################################################
select sid,sname from student
where sid in
(select t2.student_id from
-- 查询2号所学课程
(SELECT course_id,count(course_id) as course_num from score
where student_id = 2
GROUP BY course_id) as t1,
-- 查询非2号所学课程
(SELECT student_id,course_id,count(course_id) as course_num from score
where student_id <> 2
GROUP BY course_id) as t2
where t1.course_id = t2.course_id and t1.course_num = t2.course_num);
# 24.查询和'2'号同学学习的课程完全相同的其他同学的学号和姓名
#########################################################
delete from score
where score.course_id in
(select cid from course
LEFT JOIN teacher
on course.teacher_id = teacher.tid
where teacher.tname = '张三');
# 25.删除学习'张三'老师课的score表记录
#########################################################
insert into score(student_id,course_id,score)
-- ①没有上过编号“2”课程的同学学号；
SELECT t1.sid,t1.course_id,t2.score from
(SELECT student.sid,course_id from student
LEFT JOIN score
on student.sid = score.sid
where course_id <>2) as t1,
-- ②插入“2”号课程的平均成绩；
(SELECT AVG(score) as score from score
where course_id = 2) as t2;
# 26.向score表中插入一些记录,这些记录要求符合以下条件 # 1.没有上过编号'2'课程的同学学号; # 2.插入'2'号课程的平均成绩;
#########################################################
select cname,avg_score from course
LEFT JOIN
(select course_id,avg(score) as avg_score from score
GROUP BY course_id) as t1
on course.cid = t1.course_id
WHERE cname in ('语文','数学','英语')
ORDER BY avg_score;
# 27.按平均成绩从低到高显示所有学生的'语文','数学','英语'三门的课程成绩, #按如下形式显示:学生ID,语文,数学,英语,有效课程数,有效平均分;
#########################################################
select cid as '课程ID',max(score) as '最高分',min(score) as '最低分'
from score
RIGHT JOIN course
on course.cid = score.course_id
GROUP BY course_id
ORDER BY '课程ID';
# 28.查询各科成绩最高和最低的分; # 以如下形式显示:课程ID,最高分,最低分
#########################################################
SELECT course_id,AVG(score) as avg_score,
concat(round(sum(case when score.score >= 60 then 1 else 0 end) / count(1) * 100,2),'%') as percent
from score
group by course_id
ORDER BY avg_score,percent desc;
# 29.按各科平均成绩从低到高和及格率的百分数从高到低顺序
#########################################################
select tname,avg_score from teacher
INNER JOIN
(select avg(score) as avg_score,teacher_id from score
LEFT JOIN course
on course.cid = score.course_id
GROUP BY course_id
ORDER BY avg_score desc)as t1
ON teacher.tid = t1.teacher_id;
# 30.课程平均分从高到低显示(显示任课老师)
#########################################################
# 31.查询各科成绩前三名的记录(不考虑成绩并列情况)
#########################################################
SELECT cname,count(score.student_id) as count_s from course
left JOIN score
on course.cid = score.course_id
GROUP BY course.cid;
# 32.查询每门课程被选修的学生数
#########################################################
SELECT student.sid,sname from student
INNER JOIN score
ON score.student_id = student.sid
GROUP BY score.student_id
HAVING count(course_id) >2;
# 33.查询选修了2门以上课程的全部学生的学号和姓名
#########################################################
SELECT gender,count(gender) as count_sex from student
GROUP BY gender;
# 34.查询男生,女生的人数,按倒序排列
#########################################################
select sid,sname,gender from student
where sname like '张%';
# 35.查询姓'张'的学生名单
#########################################################
SELECT sname,count(sname) as count_name from student
GROUP BY sname
HAVING count(sname) >=2;
36.查询同名同姓学生名单,并统计同名人数
#########################################################
SELECT course_id,avg(score) as avg_score from score
GROUP BY course_id
ORDER BY avg_score,course_id desc;
37.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
#########################################################
select sname,score from student st
inner JOIN
(select s.student_id,cname,score from score s
LEFT JOIN course c
ON c.cid = s.course_id
where c.cname = '数学' and s.score <60) as t1
on st.sid = t1.student_id;
38.查询课程名称为'数学',且分数低于60的学生姓名和分数
#########################################################
SELECT sid,sname from student
WHERE sid IN
(SELECT student_id from score
where course_id = 3 and score>80
);
39.查询课程编号为'3'且课程成绩在80分以上的学生的学号和姓名
#########################################################
SELECT course_id,count(student_id)as count_s from score
GROUP BY course_id;
40.求选修了课程的学生人数
#########################################################
-- 最高成绩
SELECT sname,score from student st
INNER JOIN
(SELECT s.student_id,score from score s
where s.course_id in
(SELECT cid from course c
inner JOIN teacher t
on c.teacher_id = t.tid
where tname = '王五'
ORDER BY score desc
))as t1
ON st.sid = t1.student_id
LIMIT 1;
-- 最低成绩
SELECT sname,score from student st
INNER JOIN
(SELECT s.student_id,score from score s
where s.course_id in
(SELECT cid from course c
inner JOIN teacher t
on c.teacher_id = t.tid
where tname = '王五'
ORDER BY score
))as t1
ON st.sid = t1.student_id
LIMIT 1;
41.查询选修'王五'老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩
#########################################################
SELECT cname,count(student_id) as count_student from course
inner JOIN score
on course.cid = score.course_id
GROUP BY course_id;
42.查询各个课程及相应的选修人数
#########################################################
SELECT
s1.student_id,s2.student_id,
s1.course_id,s2.course_id,
s1.score,s2.score
from score as s1,score as s2
where s1.course_id <> s2.course_id
and s1.score = s2.score
and s1.student_id =s2.student_id;
43.查询不同课程但成绩相同的学生的学号,课程号,学生成绩
#########################################################
1.查所有的课程及成绩;2.取前2个?;3.和学生表连表
44.查询每门课程成绩最好的前两名学生id和姓名(未实现)
#########################################################
SELECT student_id from score
GROUP BY student_id
HAVING count(course_id) >=2;
45.检索至少选修两门课程的学生学号
#########################################################
SELECT cid,cname from course
where cid not in
(SELECT course_id from score
group by course_id);
46.查询没有学生选修的课程的课程号和课程名
#########################################################
SELECT tid,tname from teacher
where tid not in
(select tid from teacher2cls
GROUP BY tid);
47.查询没带过任何班级的老师id和姓名
#########################################################
select student_id,avg(score) as avg_score from score
where score >80
group by student_id
having count(course_id) >2;
48.查询有两门以上课程超过80分的学生id及其平均成绩
#########################################################
SELECT student_id from score
where course_id = 3 and score <60
order by score desc;
49.检索'3'课程分数小于60,按分数降序排列的同学学号
#########################################################
DELETE from score where sid =
(select sid from score where student_id = 2 and course_id=1);
50.删除编号为'2'的同学的'1'课程的成绩
#########################################################
SELECT sid,sname from student
where sid in
(SELECT student_id from score
LEFT JOIN course
on course_id = cid
where cname = '物理' and cname ='生物');
51.查询同时选修了物理课和生物课的学生id和姓名