背景:
数据库一些基本操作忘记了,需要回顾
实现:
总结写在前面,花了较多时间,比较麻烦
1-视频demo不一定全对,但老师意思无问题,部分错误需要运行才能看的出来
2-部分运行结果和题目不是严格一致,但是意思同
3-不要钻牛角尖,索引及其运用场景是我目前所需要的,部分运行不出来不是主要问题
4-使用了navicat的查询界面进行操作,还使用了它的sql美化功能
sql优先级
join [肯定要先处理join再处理where]
where
group by
having [对分组做二次筛选]
order by (asc,desc)
limit
count(1)是计算一共有多少符合条件的行
示例1:
select count(1) from course
示例2:
select student.sid,student.sname,count(score.course_id),count(1) from student
left join score on student.sid = score.student_id
group by score.student_id
1:表及数据
所用到的表和数据
create table class(
cid int not null auto_increment primary key,
caption varchar(32) not null
)default charset=utf8;
create table teacher(
tid int not null auto_increment primary key,
tname varchar(32) not null
)default charset=utf8;
create table student(
sid int not null auto_increment primary key,
sname varchar(32) not null,
gender char(1) not null,
class_id int not null,
constraint fk_student_class_student foreign key student(class_id) references class(cid)
)default charset=utf8;
create table course(
cid int not null auto_increment primary key,
cname varchar(32) not null,
teacher_id int not null,
constraint fk_course_teacher_course foreign key course(teacher_id) references teacher(tid)
)default charset=utf8;
create table score(
sid int not null auto_increment primary key,
student_id int not null,
course_id int not null,
number int not null,
constraint fk_score_student_score foreign key score(student_id) references student(sid),
constraint fk_score_course_score foreign key score(course_id) references course(cid)
)default charset=utf8;
insert into class(caption) values('三年二班'),('一年三班'),('三年一班');
insert into teacher(tname) values('波多'),('苍空'),('饭岛');
insert into student(sname,gender,class_id) values('钢蛋','女',1),('铁锤','女',1),('山炮','男',2);
insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2)
insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100)
2:1到10题实现
-- 2忽略
-- 3查询姓"李"老师的个数 模糊匹配[通配符]+聚合函数
select count(tid) from teacher where tname like '李%'
-- 4查询姓"张"的学生名单
select sname from student where sname like '张%'
-- 5查询男生、女生的人数 group_by分组
select gender,count(sid) as human_count from student group by gender
-- 6查询同名同姓学生名单,并统计学生人数 分组+聚合函数
-- 方法1
select sname,count(sid) from student group by sname
-- 在同名同姓基础上再次筛选
select sname,count(sid) from student group by sname having count(sid) > 1
-- 7查询“三年二班”的所有学生 连表查询,左右外连接和内连接均可,选内连接
select * from student inner join class on student.class_id = class.cid where caption="三年二班"
-- 8查询每个班级的班级名称、班级人数 内连接+分组
select class.caption,count(class.cid) as class_count from student inner join class on student.class_id = class.cid group by student.class_id
-- 9查询成绩小于60分的同学的学号、姓名、成绩、课程名称
-- 内连接+where
SELECT
student.sid,
student.sname,
score.number,
course.cname
FROM student
INNER JOIN score ON score.student_id = student.sid
INNER JOIN course ON score.course_id = course.cid
where score.number < 60
-- *10查询选修了"生物课"的所有学生id,学生姓名,成绩
-- 这个虽说是老师的答案,但是未达到预期,看起来像是表问题
SELECT
student.sid,
student.sname,
score.number
FROM
score
left JOIN course ON score.course_id = course.cid
left JOIN student ON score.student_id = student.sid
WHERE course.cname = "生物课";
3:11到20题目实现
-- *11查询选修了"生物课"且分数低于60的所有学生id,姓名,成绩
-- 和10同理,但是我执行未成功
SELECT
student.sid,
student.sname,
score.number
FROM
score
left JOIN course ON score.course_id = course.cid
left JOIN student ON score.student_id = student.sid
WHERE cname = "生物课" and score.number < 60
-- *12查询所有同学的学号,姓名,选课数,总成绩
select score.student_id,student.sname,count(score.sid),sum(score.number) from score
left JOIN student ON score.student_id = student.sid
group by score.student_id
-- 13查询各科被选修的学生数
select count(sid),course.cname from score left join course on score.course_id = course.cid group by score.course_id
-- 14查询各科成绩的总分、最高分、最低分,显示:课程id,总分,最高分,最低分
select course.cid,sum(score.number),max(score.number),min(score.number) from score left join course on score.course_id = course.cid group by score.course_id
-- 15查询各科成绩的平均分,显示:课程id,课程名称,平均分
select course.cid,course.cname,avg(score.number) from score left join course on score.course_id = course.cid group by score.course_id
-- 16查询各科成绩的平均分,显示:课程id,课程名称,平均分(从大到小)
select course.cid,course.cname,avg(score.number) from score left join course on score.course_id = course.cid group by score.course_id order by avg(score.number) desc
-- **17查询各科的平均分和及格率,显示:课程id,课程名称,平均分,及格率
-- 及格率?这个难一点
SELECT
course.cid,
course.cname,
avg(score.number ),
sum(case when score.number >= 60 then 1 else 0 end) / count(score.sid) * 100
FROM
score
LEFT JOIN course ON score.course_id = course.cid
GROUP BY
score.course_id
-- 18查询平均成绩大于60的所有学生的学号,平均成绩
SELECT
student.sid,
avg(score.number)
FROM
score
left JOIN student ON score.student_id = student.sid
having avg(score.number) > 60
-- *19查询平均成绩大于85的所有学生的学号,平均成绩,姓名
SELECT
student.sid,
avg(score.number ),
student.sname
FROM
score
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
score.course_id having avg(score.number ) > 85
-- 20查询"三年二班"每个学生的学号,姓名,总成绩,平均成绩
SELECT
student.sid,
score.number,
sum(score.number),
avg(score.number)
FROM
score
left JOIN course ON score.course_id = course.cid
left JOIN student ON score.student_id = student.sid
left join class on student.class_id = class.cid
where class.caption = "三年二班" order by score.student_id
4:21到30题实现
-- **21查询各个班级的班级名称,总成绩,平均成绩,及格率(从小到大排序)
-- 及格率难一点,参考20
SELECT
class.caption,
sum(score.number),
avg(score.number),
sum(case when score.number >= 60 then 1 else 0 end ) / count(1) * 100
FROM
score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN class ON student.class_id = class.cid
order by class.cid
-- *22查询学过"波多"老师课的同学的学号,姓名
-- 这里面有一个去重的问题,使用group by来解决
select student.sid,student.sname
from score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join teacher on course.teacher_id = teacher.tid
where teacher.tname = "波多" group by student.sid
-- **23查询没学过"波多"老师课的同学的学号,姓名
-- 错误思想,除了波多老师,选过其它课程的同学
select student.sid,student.sname
from student
left join score on student.sid = score.student_id
left join course on score.course_id = course.cid
left join teacher on course.teacher_id = teacher.tid
where teacher.tname != "波多"
-- 正确思想,先查选过波多老师的学生id,在排除
SELECT
student.sid,
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
student.sid
FROM
score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = "波多"
GROUP BY
student.sid
)
-- 24查询选修"苍空"老师所授课程的学生中,成绩最高的学生姓名及其成绩(不考虑并列)
SELECT
student.sid,
student.sname,
score.number
FROM
score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = "苍空"
ORDER BY
score.number DESC
LIMIT 1 OFFSET 0
-- *25查询选修"苍空"老师所授课程的学生中,成绩最高的学生姓名及其成绩(考虑并列)
-- 考虑并列=考虑最高分的成绩一样
-- 思路:找考的最高的那个人的成绩,在筛选等于这个成绩的!这个有意思,我想复杂了,还想多分组呢。。
SELECT
student.sname,
score.number
FROM
score score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = "苍空"
AND score.number = (
SELECT
score.number
FROM
score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = "苍空"
ORDER BY
score.number DESC
LIMIT 1 OFFSET 0)
-- 26查询只选修了一门课程的全部学生的学号,姓名
select student.sid,student.sname from student
left join score on student.sid = score.student_id
group by score.student_id having count(1) = 1
-- 27查询至少只选修了一门课程的全部学生的学号,姓名
-- 选修课程>=2,我之前还以为是>=1呢
select student.sid,student.sname from student
left join score on student.sid = score.student_id
group by score.student_id having count(1) >= 2
-- *28查询两门及以上不及格的同学的学号,学生姓名,选修课程数量
-- 先查到不及格的所有数据,在进行分组
select student.sid,student.sname,count(1) from student
left join score on student.sid = score.student_id
where score.number < 60
group by score.student_id
-- *29查询选修了所有课程的学生的学号,姓名
-- 计算学生选课的个数,看是否等于课程表里面的课程数
select student.sid,student.sname,count(score.course_id) from student
left join score on student.sid = score.student_id
group by score.student_id having count(1) = (select count(1) from course)
-- 30查询未选修所有课程的学生的学号,姓名
select student.sid,student.sname,count(score.course_id) from student
left join score on student.sid = score.student_id
group by score.student_id having count(1) != (select count(1) from course)
5:31到40题实现
-- 31查询所有学生都选修了的课程的课程号,课程名
select course.cid,course.cname from course
left join score on score.course_id = course.cid
group by score.course_id having count(1) = (
select count(1) from student
)
-- *32查询选修"生物"和"物理"课程的所有学生学号,姓名
-- 这个having有点东西
select student.sid,student.sname from student
left join score on student.sid = score.student_id
left join course on course.cid = score.course_id
where course.cname in ("生物","物理")
group by student.sid having count(1) = 2
-- **33查询至少有一门课程与学号为"1"的学生所选的课程相同的其他学生学号和姓名
-- 先知道学生id为1的人选了那些课?
select student.sid,student.sname from student
left join score on student.sid = score.student_id
left join course on course.cid = score.course_id
where score.course_id in (
select course_id from score where student_id = 1
) and score.student_id != 1
group by student_id having count(1) > 1
-- ***34查询与学号为"2"的同学选修的课程完全相同的其他学生学号和姓名
-- 和33类似
-- id=2的学生他的课程数量和其他人的课程数量是一样
SELECT
student.sid,
student.sname
FROM
student
LEFT JOIN score ON student.sid = score.student_id
LEFT JOIN course ON course.cid = score.course_id
WHERE
score.course_id IN ( SELECT course_id FROM score WHERE student_id = 2 )
AND score.student_id IN (
SELECT
student_id
FROM
score
WHERE
student_id != 2
GROUP BY
student_id
HAVING
count( 1 ) = ( SELECT count( 1 ) FROM score WHERE student_id = 2 )
)
GROUP BY
score.student_id
HAVING
count( 1 ) = (
SELECT
count( 1 )
FROM
score
WHERE
score.student_id = 1
GROUP BY
score.student_id
)
-- ***35查询"生物"课程比"物理"课程成绩高的所有学生的学号
select *,
max(case course.cname when "生物" then number else -1 end) as sw,
max(case course.cname when "物理" then number else -1 end) as wl
from score
left join course on score.course_id = course.cid
where course.cname in ("生物","物理")
group by score.student_id
having sw > wl
-- **36查询每门课程成绩最好的前3名(不考虑)并列
-- 1先查到课程名
-- 这样不严谨,并列第一名这种情况不行
select cid,cname,
(select student.sname from score
left join student on student.sid = score.student_id
where course_id = course.cid order by number desc limit 1 offset 0
) as "第1名",
(select student.sname from score
left join student on student.sid = score.student_id
where course_id = course.cid order by number desc limit 1 offset 1
) as "第2名",
(select student.sname from score
left join student on student.sid = score.student_id
where course_id = course.cid order by number desc limit 1 offset 2
) as "第3名"
from course;
-- ***37查询每门课程成绩最好的前3名(考虑成绩并列情况)
SELECT
*
FROM
score
LEFT JOIN (
SELECT
cid,
cname,
( SELECT score.number FROM score, course WHERE course_id = course.cid ORDER BY number DESC LIMIT 1 OFFSET 0 ) AS "最高分",
( SELECT score.number FROM score, course WHERE course_id = course.cid ORDER BY number DESC LIMIT 1 OFFSET 1 ) AS "第二高分",
( SELECT score.number FROM score, course WHERE course_id = course.cid ORDER BY number DESC LIMIT 1 OFFSET 2 ) AS third
FROM
course
) AS C ON score.course_id = C.cid
WHERE
score.number >= C.third
-- *38创建一个表sc,然后将score表中的所有数据插入到sc表中
-- sc表结构和score一样
CREATE TABLE sc (
sid INT NOT NULL auto_increment PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
number INT NOT NULL,
CONSTRAINT fk_sc_student FOREIGN KEY sc ( student_id ) REFERENCES student ( sid ),
CONSTRAINT fk_sc_course FOREIGN KEY sc ( course_id ) REFERENCES course ( cid )
) DEFAULT charset = utf8;
insert into sc select * from score;
-- *39向score表中插入一些数据,这些数据符合以下条件
-- 1:学生id为:没上过课程id为"2"课程的学生的学号
-- 2:课程id为2
-- 3:成绩为80
-- 构造数据
INSERT INTO sc ( student_id, course_id, number ) SELECT
sid,
2,
80
FROM
student
WHERE
sid NOT IN (
SELECT
sid
FROM
student
WHERE
sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
)
-- 40向sc表中插入一些记录,这些记录要求符合以下条件:
-- 1:学生id为:没上过课程id为"2"课程的学生的学号
-- 2:课程id为2
-- 3:成绩为:课程id为3的最高分
INSERT INTO sc ( student_id, course_id, number ) SELECT
sid,
2,
(select max(number) from score where course_id) as num
FROM
student
WHERE
sid NOT IN (
select student_id from score where course_id = 2
)