强化练习
（一）、创建数据库
create database strengthen_db default charset utf8 collate utf8_general_ci; -- 创建数据库
drop database strengthen_db; -- 删除数据库，若没有该数据库会报错
-- 改进，添加判断条件，如果存在（if exists）
drop database if exists strengthen_db;
- 利用命令导入数据库（数据库的备份和传输都是以这种方式操作）
-
导入
mysql -u 用户名 -p 目标数据库 < xxx.sql文件所在的路径 -- strengthen_table.sql先将命令导入.txt并用ANSI格式保存，并修改后缀为.sql，再执行导入命令 mysql -u root -p strengthen_db < C:\Users\Liu\Desktop\strengthen_table.sql -
导出
-- 导出 结构+数据 mysqldump -u root -p strengthen_db > C:\Users\Liu\Desktop\strengthen_table1.sql -- 导出 结构 mysqldump -u root -p -d strengthen_db > C:\Users\Liu\Desktop\strengthen_table2.sql
create table class(
cid int not null primary key auto_increment,
caption varchar(16) not null
)default charset=utf8;
insert into class values('1','一班'),('2','二班'),('3','三班'),('4','四班');
create table student(
sid int not null primary key auto_increment,
gender char(1) not null,
class_id int not null,
sname varchar(16) not null,
constraint fk_student_class foreign key student(class_id) references class(cid)
)default charset=utf8;
insert into student values('101','男','1','小明'),("102","男","1","小张"),('201','女','2','小红'),('301','男','3','小张'),('401','女','4','小陈');
create table teacher(
tid int not null primary key auto_increment,
tname varchar(16) not null
)default charset=utf8;
insert into teacher values('001','张老师'),('002','刘老师'),('003','徐老师'),('004','陈老师');
create table course(
cid int not null primary key auto_increment,
cname varchar(16) not null,
teacher_id int not null,
constraint fk_course_teacher foreign key course(teacher_id) references teacher(tid)
)default charset=utf8;
insert into course values('1','语文','001'),('2','数学','002'),('3','英语','003'),('4','体育','004');
create table score(
sid int not null primary key auto_increment,
student_id int not null,
course_id int not null,
score int not null,
constraint fk_score_student foreign key score(student_id) references student(sid),
constraint fk_score_course foreign key score(course_id) references course(cid)
)default charset=utf8;
insert into score values('1','101','1','80'),('2','101','2','81'),('3','101','3','89'),('4','101','4','90'),
('5','201','1','90'),('6','201','2','91'),('7','201','3','99'),('8','201','4','90'),
('9','301','1','70'),('10','301','2','77'),('11','301','3','89'),('12','301','4','100'),
('13','401','1','33'),('14','401','2','56'),('15','401','3','12'),('16','401','4','66');
（二）、设置权限
-
创建用户
joywon并赋予数据库所有权限create user 'joywon'@'%' identified by 'joywon'; grant all privileges on strengthen_db.* to 'joywon'@'%'; flush privileges;
（三）、查询
-
查询姓刘的老师
select * from teacher where tname like '刘%'; -
查询男生、女生的人数
select gender,count(1) from student group by gender; -
查询同名学生名单，并计数
mysql> select sname,count(1) from student group by sname; +-------+----------+ | sname | count(1) | +-------+----------+ | 小张 | 2 | | 小明 | 1 | | 小红 | 1 | | 小陈 | 1 | +-------+----------+ 4 rows in set (0.00 sec) mysql> select sname,count(1) from student group by sname having count(1)>1; +-------+----------+ | sname | count(1) | +-------+----------+ | 小张 | 2 | +-------+----------+ 1 row in set (0.00 sec) -
查询 一班 所有学生
mysql> select sid,gender,class.caption,sname from student left join class on student.class_id=class.cid; +-----+--------+---------+-------+ | sid | gender | caption | sname | +-----+--------+---------+-------+ | 101 | 男 | 一班 | 小明 | | 102 | 男 | 一班 | 小张 | | 201 | 女 | 二班 | 小红 | | 301 | 男 | 三班 | 小张 | | 401 | 女 | 四班 | 小陈 | +-----+--------+---------+-------+ 5 rows in set (0.00 sec) mysql> select sid,gender,class.caption,sname from student left join class on student.class_id=class.cid where caption='一班'; +-----+--------+---------+-------+ | sid | gender | caption | sname | +-----+--------+---------+-------+ | 101 | 男 | 一班 | 小明 | | 102 | 男 | 一班 | 小张 | +-----+--------+---------+-------+ 2 rows in set (0.00 sec) -
查询所有班级名称，和班级下的人数
mysql> select class.caption,count(1) from student left join class on student.class_id=class.cid group by class_id; +---------+----------+ | caption | count(1) | +---------+----------+ | 一班 | 2 | | 二班 | 1 | | 三班 | 1 | | 四班 | 1 | +---------+----------+ 4 rows in set (0.00 sec) -
查询成绩小于80分的学生的学号，姓名，成绩，课程名
mysql> select score.student_id,student.sname,score.score,course.cname from score left join student on score.student_id=student.sid left join course on score.course_id=course.cid; +------------+-------+-------+-------+ | student_id | sname | score | cname | +------------+-------+-------+-------+ | 101 | 小明 | 80 | 语文 | | 101 | 小明 | 81 | 数学 | | 101 | 小明 | 89 | 英语 | | 101 | 小明 | 90 | 体育 | | 201 | 小红 | 90 | 语文 | | 201 | 小红 | 91 | 数学 | | 201 | 小红 | 99 | 英语 | | 201 | 小红 | 90 | 体育 | | 301 | 小张 | 70 | 语文 | | 301 | 小张 | 77 | 数学 | | 301 | 小张 | 89 | 英语 | | 301 | 小张 | 100 | 体育 | | 401 | 小陈 | 33 | 语文 | | 401 | 小陈 | 56 | 数学 | | 401 | 小陈 | 12 | 英语 | | 401 | 小陈 | 66 | 体育 | +------------+-------+-------+-------+ 16 rows in set (0.00 sec) mysql> select score.student_id,student.sname,score.score,course.cname from score left join student on score.student_id=student.sid left join course on score.course_id=course.cid where score<80; +------------+-------+-------+-------+ | student_id | sname | score | cname | +------------+-------+-------+-------+ | 301 | 小张 | 70 | 语文 | | 301 | 小张 | 77 | 数学 | | 401 | 小陈 | 33 | 语文 | | 401 | 小陈 | 56 | 数学 | | 401 | 小陈 | 12 | 英语 | | 401 | 小陈 | 66 | 体育 | +------------+-------+-------+-------+ 6 rows in set (0.00 sec) -
查看所有学生的学号、姓名、课程数、总成绩
mysql> select score.student_id,student.sname,count(1),sum(score) from score left join student on score.student_id=student.sid group by score.student_id; +------------+-------+----------+------------+ | student_id | sname | count(1) | sum(score) | +------------+-------+----------+------------+ | 101 | 小明 | 4 | 340 | | 201 | 小红 | 4 | 370 | | 301 | 小张 | 4 | 336 | | 401 | 小陈 | 4 | 167 | +------------+-------+----------+------------+ 4 rows in set (0.00 sec) -
查询各科成绩的总分、最高分、最低分。 显示：课程号、课程名称、总分、最高分、最低分
mysql> select score.course_id,course.cname,sum(score),max(score),min(score) from score left join course on score.course_id=course.cid group by score.course_id; +-----------+-------+------------+------------+------------+ | course_id | cname | sum(score) | max(score) | min(score) | +-----------+-------+------------+------------+------------+ | 1 | 语文 | 273 | 90 | 33 | | 2 | 数学 | 305 | 91 | 56 | | 3 | 英语 | 289 | 99 | 12 | | 4 | 体育 | 346 | 100 | 66 | +-----------+-------+------------+------------+------------+ 4 rows in set (0.00 sec) -
查询各科成绩的平均分。 显示：课程号、课程名称、平均分
mysql> select course.cid,course.cname,avg(score) from score left join course on score.course_id=course.cid group by score.course_id; +------+-------+------------+ | cid | cname | avg(score) | +------+-------+------------+ | 1 | 语文 | 68.2500 | | 2 | 数学 | 76.2500 | | 3 | 英语 | 72.2500 | | 4 | 体育 | 86.5000 | +------+-------+------------+ 4 rows in set (0.00 sec) -
查询各科成绩的平均分。 显示：课程号、课程名称、平均分（从大到小排列）
mysql> select course.cid,course.cname,avg(score) from score left join course on score.course_id=course.cid group by score.course_id order by avg(score) desc; +------+-------+------------+ | cid | cname | avg(score) | +------+-------+------------+ | 4 | 体育 | 86.5000 | | 2 | 数学 | 76.2500 | | 3 | 英语 | 72.2500 | | 1 | 语文 | 68.2500 | +------+-------+------------+ 4 rows in set (0.00 sec) mysql> select course.cid,course.cname,avg(score) as AVG from score left join course on score.course_id=course.cid group by score.course_id order by AVG desc; +------+-------+---------+ | cid | cname | AVG | +------+-------+---------+ | 4 | 体育 | 86.5000 | | 2 | 数学 | 76.2500 | | 3 | 英语 | 72.2500 | | 1 | 语文 | 68.2500 | +------+-------+---------+ 4 rows in set (0.00 sec) -
查询各科成绩的平均分和及格率。 显示：课程号、课程名称、平均分、及格率
mysql> select score.course_id,score.score,case when score.score>60 then 1 else 0 end "是否及格" from score; +-----------+-------+----------+ | course_id | score | 是否及格 | +-----------+-------+----------+ | 1 | 80 | 1 | | 2 | 81 | 1 | | 3 | 89 | 1 | | 4 | 90 | 1 | | 1 | 90 | 1 | | 2 | 91 | 1 | | 3 | 99 | 1 | | 4 | 90 | 1 | | 1 | 70 | 1 | | 2 | 77 | 1 | | 3 | 89 | 1 | | 4 | 100 | 1 | | 1 | 33 | 0 | | 2 | 56 | 0 | | 3 | 12 | 0 | | 4 | 66 | 1 | +-----------+-------+----------+ 16 rows in set (0.00 sec) mysql> select course_id,course.cname,avg(score),count(1) as total,sum(case when score.score>60 then 1 else 0 end) as "及格数" from score left join course on score.course_id = course.cid group by course_id; +-----------+-------+------------+-------+--------+ | course_id | cname | avg(score) | total | 及格数 | +-----------+-------+------------+-------+--------+ | 1 | 语文 | 68.2500 | 4 | 3 | | 2 | 数学 | 76.2500 | 4 | 3 | | 3 | 英语 | 72.2500 | 4 | 3 | | 4 | 体育 | 86.5000 | 4 | 4 | +-----------+-------+------------+-------+--------+ 4 rows in set (0.00 sec) mysql> select course.cid,course.cname,avg(score.score) as "平均分", -> sum(case when score.score>60 then 1 else 0 end)/count(1) * 100 as "及格率%" from score left join course on score.course_id=course.cid group by score.course_id; +------+-------+---------+----------+ | cid | cname | 平均分 | 及格率% | +------+-------+---------+----------+ | 1 | 语文 | 68.2500 | 75.0000 | | 2 | 数学 | 76.2500 | 75.0000 | | 3 | 英语 | 72.2500 | 75.0000 | | 4 | 体育 | 86.5000 | 100.0000 | +------+-------+---------+----------+ 4 rows in set (0.01 sec) -
查询平均成绩大于60的所有学生。 显示：学号、姓名、平均成绩
mysql> select student.sid,student.sname,avg(score.score) as AVG from score left join student on score.student_id=student.sid group by score.student_id having AVG>60; +------+-------+---------+ | sid | sname | AVG | +------+-------+---------+ | 101 | 小明 | 85.0000 | | 201 | 小红 | 92.5000 | | 301 | 小张 | 84.0000 | +------+-------+---------+ 3 rows in set (0.00 sec) -
查询一班每个学生的学号、姓名、总成绩、平均成绩
mysql> select student.sid,student.sname,sum(score.score) as "sum score",avg(score.score) as "avg score" from score left join student on score.student_id=student.sid group by score.student_id; +------+-------+-----------+-----------+ | sid | sname | sum score | avg score | +------+-------+-----------+-----------+ | 101 | 小明 | 340 | 85.0000 | | 201 | 小红 | 370 | 92.5000 | | 301 | 小张 | 336 | 84.0000 | | 401 | 小陈 | 167 | 41.7500 | +------+-------+-----------+-----------+ 4 rows in set (0.03 sec) -
查询各个班级的班级名称、总成绩、平均成绩、及格率（从小到大排序）
mysql> select class.caption,sum(score.score),avg(score.score),sum(case when score.score>60 then 1 else 0 end)/count(1) * 100 as "jgl" from score left join student on score.student_id=student.sid left join class on student.class_id=class.cid group by class.cid order by jgl asc; +---------+------------------+------------------+----------+ | caption | sum(score.score) | avg(score.score) | jgl | +---------+------------------+------------------+----------+ | 四班 | 167 | 41.7500 | 25.0000 | | 二班 | 370 | 92.5000 | 100.0000 | | 三班 | 336 | 84.0000 | 100.0000 | | 一班 | 340 | 85.0000 | 100.0000 | +---------+------------------+------------------+----------+ 4 rows in set (0.00 sec) -
查询学过 刘老师 课的学生学号、姓名
mysql> 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 left join teacher on course.teacher_id=teacher.tid where teacher.tname="刘老师"; +-----+-------+ | sid | sname | +-----+-------+ | 101 | 小明 | | 201 | 小红 | | 301 | 小张 | | 401 | 小陈 | +-----+-------+ 4 rows in set (0.00 sec) -
查询没有上过刘老师的学生
mysql> select * from student where sid not in( select student.sid from student left join score on student.sid=score.student_id left join course on course.cid=score.course_id left join teacher on course.teacher_id=teacher.tid where teacher.tname="刘老师"); +-----+--------+----------+-------+ | sid | gender | class_id | sname | +-----+--------+----------+-------+ | 102 | 男 | 1 | 小张 | +-----+--------+----------+-------+ 1 row in set (0.00 sec) -
查询刘老师交的课中成绩最高的学生学号、姓名、分数（没有考虑并列）
mysql> select student.sid,student.sname,score.score 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.score desc -> limit 1; +------+-------+-------+ | sid | sname | score | +------+-------+-------+ | 201 | 小红 | 91 | +------+-------+-------+ 1 row in set (0.01 sec) -
查询刘老师交的课中成绩最高的学生学号、姓名、分数（考虑并列）
mysql> select student.sid,student.sname,score.score 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="刘老师" -> and score.score=(select max(score.score) from score left join course on score.course_id = course.cid -> left join teacher on course.teacher_id=teacher.tid -> where teacher.tname="刘老师"); +------+-------+-------+ | sid | sname | score | +------+-------+-------+ | 201 | 小红 | 91 | +------+-------+-------+ 1 row in set (0.01 sec) -
查询没有选修的学生
mysql> select student.sid,student.sname from student left join score on student.sid=score.student_id where student.sid not in (select score.student_id from score); +-----+-------+ | sid | sname | +-----+-------+ | 102 | 小张 | +-----+-------+ 1 row in set (0.00 sec) -
查询不及格的学生学号、姓名、课程名、分数
mysql> select student.sid,student.sname,course.cname,score.score from score left join student on score.student_id=student.sid left join course on score.course_id=course.cid where score.score<60; +------+-------+-------+-------+ | sid | sname | cname | score | +------+-------+-------+-------+ | 401 | 小陈 | 语文 | 33 | | 401 | 小陈 | 数学 | 56 | | 401 | 小陈 | 英语 | 12 | +------+-------+-------+-------+ 3 rows in set (0.00 sec) -
查询选修了所有课程的学生学号、姓名
mysql> select student.sid,student.sname from score left join student on score.student_id=student.sid group by score.student_id having count(1)=(select count(1) from course); +------+-------+ | sid | sname | +------+-------+ | 101 | 小明 | | 201 | 小红 | | 301 | 小张 | | 401 | 小陈 | +------+-------+ 4 rows in set (0.01 sec) -
查询与学号为102的同学选课一致的同学学号、姓名（查询结果应该为空才是正确答案）
-- 先给102同学赋值一门课的成绩 mysql> insert into score values("17","102","1","100"); Query OK, 1 row affected (0.02 sec) mysql> select * from score; +-----+------------+-----------+-------+ | sid | student_id | course_id | score | +-----+------------+-----------+-------+ | 1 | 101 | 1 | 80 | | 2 | 101 | 2 | 81 | | 3 | 101 | 3 | 89 | | 4 | 101 | 4 | 90 | | 5 | 201 | 1 | 90 | | 6 | 201 | 2 | 91 | | 7 | 201 | 3 | 99 | | 8 | 201 | 4 | 90 | | 9 | 301 | 1 | 70 | | 10 | 301 | 2 | 77 | | 11 | 301 | 3 | 89 | | 12 | 301 | 4 | 100 | | 13 | 401 | 1 | 33 | | 14 | 401 | 2 | 56 | | 15 | 401 | 3 | 12 | | 16 | 401 | 4 | 66 | | 17 | 102 | 1 | 100 | +-----+------------+-----------+-------+ 17 rows in set (0.00 sec)-- 这里查询不够严谨，若某同学比101同学多选了课，也会出现在查询列表中 mysql> select student.sid,student.sname from score left join course on score.course_id=course.cid left join student on score.student_id=student.sid where course_id in (select score.course_id from score where score.student_id="102") and score.student_id!="102" group by score.student_id having count(1)=(select count(1) from score where student_id="102"); +------+-------+ | sid | sname | +------+-------+ | 101 | 小明 | | 201 | 小红 | | 301 | 小张 | | 401 | 小陈 | +------+-------+ 4 rows in set (0.01 sec) -
改进
思路：
1、将3个表链接；
2、判断课程号是否在102选课的课程号内；
3、同时输出的学生id号不能是102本身，同时选课的数量要等于102同学的选课数量
mysql> select student.sid,student.sname from score left join course on score.course_id=course.cid left join student on score.student_id=student.sid where course_id in (select score.course_id from score where score.student_id="102") and score.student_id in ( select student_id from score where student_id!="102" group by student_id having count(1)=(select count(1) from score where student_id="102") -- 选课的个数要和101同学一致 ) group by score.student_id having count(1)=(select count(1) from score where student_id="102"); Empty set (0.01 sec) -
查询语文比数学成绩高的所有学生学号，姓名
-- 思路： -- 1.先查询分数表，找到所有 语文，数学成绩 select * from score left join course on score.course_id=course.cid where cname in ("语文","数学"); +-----+------------+-----------+-------+------+-------+------------+ | sid | student_id | course_id | score | cid | cname | teacher_id | +-----+------------+-----------+-------+------+-------+------------+ | 1 | 101 | 1 | 80 | 1 | 语文 | 1 | | 5 | 201 | 1 | 90 | 1 | 语文 | 1 | | 9 | 301 | 1 | 70 | 1 | 语文 | 1 | | 13 | 401 | 1 | 33 | 1 | 语文 | 1 | | 17 | 102 | 1 | 100 | 1 | 语文 | 1 | | 2 | 101 | 2 | 81 | 2 | 数学 | 2 | | 6 | 201 | 2 | 91 | 2 | 数学 | 2 | | 10 | 301 | 2 | 77 | 2 | 数学 | 2 | | 14 | 401 | 2 | 56 | 2 | 数学 | 2 | +-----+------------+-----------+-------+------+-------+------------+ 9 rows in set (0.01 sec) -- 2. 现在分数都是以行的形式显示，我们添加两列，将 语文/数学 成绩显示出来 select *, case course.cname when "语文" then score.score else -1 end yw, case course.cname when "数学" then score.score else -1 end sx from score left join course on score.course_id=course.cid where cname in ("语文","数学"); +-----+------------+-----------+-------+------+-------+------------+-----+----+ | sid | student_id | course_id | score | cid | cname | teacher_id | yw | sx | +-----+------------+-----------+-------+------+-------+------------+-----+----+ | 1 | 101 | 1 | 80 | 1 | 语文 | 1 | 80 | -1 | | 5 | 201 | 1 | 90 | 1 | 语文 | 1 | 90 | -1 | | 9 | 301 | 1 | 70 | 1 | 语文 | 1 | 70 | -1 | | 13 | 401 | 1 | 33 | 1 | 语文 | 1 | 33 | -1 | | 17 | 102 | 1 | 100 | 1 | 语文 | 1 | 100 | -1 | | 2 | 101 | 2 | 81 | 2 | 数学 | 2 | -1 | 81 | | 6 | 201 | 2 | 91 | 2 | 数学 | 2 | -1 | 91 | | 10 | 301 | 2 | 77 | 2 | 数学 | 2 | -1 | 77 | | 14 | 401 | 2 | 56 | 2 | 数学 | 2 | -1 | 56 | +-----+------------+-----------+-------+------+-------+------------+-----+----+ 9 rows in set (0.01 sec) -- 3.将两门成绩分别用两列展示出来（去除-1）（相当于取出yw sx中的最大值），用student_id分组 select score.student_id, max(case course.cname when "语文" then score.score else -1 end) as yw, max(case course.cname when "数学" then score.score else -1 end) as sx from score left join course on score.course_id=course.cid where cname in ("语文","数学") group by score.student_id; +------------+------+------+ | student_id | yw | sx | +------------+------+------+ | 101 | 80 | 81 | | 102 | 100 | -1 | | 201 | 90 | 91 | | 301 | 70 | 77 | | 401 | 33 | 56 | +------------+------+------+ 5 rows in set (0.01 sec) -- 4.添加判断条件 having输出结果 select score.student_id, student.sname, max(case course.cname when "语文" then score.score else -1 end) as yw, max(case course.cname when "数学" then score.score else -1 end) as sx from score left join course on score.course_id=course.cid left join student on score.student_id=student.sid where cname in ("语文","数学") group by score.student_id having yw>sx; +------------+-------+------+------+ | student_id | sname | yw | sx | +------------+-------+------+------+ | 102 | 小张 | 100 | -1 | +------------+-------+------+------+ 1 row in set (0.00 sec) -
查询每门课前3名学生的学号、姓名（不考虑并列）
-- 思路 select course.cid, course.cname, (姓名) as "no1", (姓名) as "no2", (姓名) as "no3" from course; -- 补充 select course.cid, course.cname, (select student.sname from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 0) as "no1", (select student.sname from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 1) as "no2", (select student.sname from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 2) as "no3" from course; +-----+-------+------+------+------+ | cid | cname | no1 | no2 | no3 | +-----+-------+------+------+------+ | 1 | 语文 | 小张 | 小红 | 小明 | | 2 | 数学 | 小红 | 小明 | 小张 | | 3 | 英语 | 小红 | 小张 | 小张 | | 4 | 体育 | 小张 | 小红 | 小红 | +-----+-------+------+------+------+ 4 rows in set (0.01 sec) select course.cid, course.cname, (select student.sid from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 0) as "no1_id", (select student.sname from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 0) as "no1", (select student.sid from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 1) as "no2_id", (select student.sname from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 1) as "no2", (select student.sid from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 2) as "no3_id", (select student.sname from score left join student on score.student_id=student.sid where score.course_id=course.cid order by score.score desc limit 1 offset 2) as "no3" from course; +-----+-------+--------+------+--------+------+--------+------+ | cid | cname | no1_id | no1 | no2_id | no2 | no3_id | no3 | +-----+-------+--------+------+--------+------+--------+------+ | 1 | 语文 | 102 | 小张 | 201 | 小红 | 101 | 小明 | | 2 | 数学 | 201 | 小红 | 101 | 小明 | 301 | 小张 | | 3 | 英语 | 201 | 小红 | 101 | 小张 | 301 | 小明 | | 4 | 体育 | 301 | 小张 | 101 | 小明 | 201 | 小明 | +-----+-------+--------+------+--------+------+--------+------+ 4 rows in set (0.00 sec) -
考虑并列
-- 思路： -- 1. 考虑并列输出 最高分，第二高分，第三高分（查询course，在后面链接1，2，3高分） mysql> select cid,cname from course; +-----+-------+ | cid | cname | +-----+-------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | | 4 | 体育 | +-----+-------+ 4 rows in set (0.00 sec) mysql> select cid,cname,1,2,3 from course; +-----+-------+---+---+---+ | cid | cname | 1 | 2 | 3 | +-----+-------+---+---+---+ | 1 | 语文 | 1 | 2 | 3 | | 2 | 数学 | 1 | 2 | 3 | | 3 | 英语 | 1 | 2 | 3 | | 4 | 体育 | 1 | 2 | 3 | +-----+-------+---+---+---+ 4 rows in set (0.00 sec) -- 实现，通过对score.score分组实现考虑并列 select cid,cname, (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 0) as "最高分", (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 1) as "第二高分", (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 2) as "第三高分" from course; +-----+-------+--------+----------+----------+ | cid | cname | 最高分 | 第二高分 | 第三高分 | +-----+-------+--------+----------+----------+ | 1 | 语文 | 100 | 90 | 80 | | 2 | 数学 | 91 | 81 | 77 | | 3 | 英语 | 99 | 89 | 12 | | 4 | 体育 | 100 | 90 | 66 | +-----+-------+--------+----------+----------+ 4 rows in set (0.00 sec) -- 2.现在我们找到各科第三高分的分数，所以只要大于等于第三高分，就能计入前三名（含并列） select * from score left join( select cid,cname, (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 0) as "最高分", (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 1) as "第二高分", (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 2) as "third" from course) as c on score.course_id=c.cid where score.score>=c.third order by cid asc,score desc; +-----+------------+-----------+-------+------+-------+--------+----------+-------+ | sid | student_id | course_id | score | cid | cname | 最高分 | 第二高分 | third | +-----+------------+-----------+-------+------+-------+--------+----------+-------+ | 17 | 102 | 1 | 100 | 1 | 语文 | 100 | 90 | 80 | | 5 | 201 | 1 | 90 | 1 | 语文 | 100 | 90 | 80 | | 1 | 101 | 1 | 80 | 1 | 语文 | 100 | 90 | 80 | | 6 | 201 | 2 | 91 | 2 | 数学 | 91 | 81 | 77 | | 2 | 101 | 2 | 81 | 2 | 数学 | 91 | 81 | 77 | | 10 | 301 | 2 | 77 | 2 | 数学 | 91 | 81 | 77 | | 7 | 201 | 3 | 99 | 3 | 英语 | 99 | 89 | 12 | | 11 | 301 | 3 | 89 | 3 | 英语 | 99 | 89 | 12 | | 3 | 101 | 3 | 89 | 3 | 英语 | 99 | 89 | 12 | | 15 | 401 | 3 | 12 | 3 | 英语 | 99 | 89 | 12 | | 12 | 301 | 4 | 100 | 4 | 体育 | 100 | 90 | 66 | | 4 | 101 | 4 | 90 | 4 | 体育 | 100 | 90 | 66 | | 8 | 201 | 4 | 90 | 4 | 体育 | 100 | 90 | 66 | | 16 | 401 | 4 | 66 | 4 | 体育 | 100 | 90 | 66 | +-----+------------+-----------+-------+------+-------+--------+----------+-------+ 14 rows in set (0.00 sec) -- 3.优化显示 select course.cid,course.cname,student.sid,student.sname,score.score from score left join student on score.student_id=student.sid left join course on score.course_id=course.cid left join( select cid,cname, (select score.score from score where score.course_id=course.cid group by score.score order by score.score desc limit 1 offset 2) as "third" from course) as c on score.course_id=c.cid where score.score>=c.third order by cid asc,score desc; +------+-------+------+-------+-------+ | cid | cname | sid | sname | score | +------+-------+------+-------+-------+ | 1 | 语文 | 102 | 小张 | 100 | | 1 | 语文 | 201 | 小红 | 90 | | 1 | 语文 | 101 | 小明 | 80 | | 2 | 数学 | 201 | 小红 | 91 | | 2 | 数学 | 101 | 小明 | 81 | | 2 | 数学 | 301 | 小张 | 77 | | 3 | 英语 | 201 | 小红 | 99 | | 3 | 英语 | 301 | 小张 | 89 | | 3 | 英语 | 101 | 小明 | 89 | | 3 | 英语 | 401 | 小陈 | 12 | | 4 | 体育 | 301 | 小张 | 100 | | 4 | 体育 | 101 | 小明 | 90 | | 4 | 体育 | 201 | 小红 | 90 | | 4 | 体育 | 401 | 小陈 | 66 | +------+-------+------+-------+-------+ 14 rows in set (0.00 sec)
（四）、查询后的数据插入新表内
-
创建一个要查询并存储的数据表（先格式化），例如需要插入score中的所有数据
-- 创建与score一样的数据表 create table sc( sid int not null primary key auto_increment, student_id int not null, course_id int not null, score 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 语句插入查询数据
-- insert into 数据表 查询语句 insert into sc select * from score; 或 insert into sc (student_id,course_id,score) select...... -
向sc表中插入数据，要符合以下要求：
- 学生学号：没上过课程号为2的学号
- 课程号：2
- 分数：60
-- 1.上过课程号为2的学号查询如下 mysql > select student_id from score where course_id=2; +------------+ | student_id | +------------+ | 101 | | 201 | | 301 | | 401 | +------------+ 4 rows in set (0.00 sec) -- 2.没上过课程2的学号 mysql > select sid from student where sid not in (select student_id from score where course_id=2); +-----+ | sid | +-----+ | 102 | +-----+ 1 row in set (0.00 sec) -- 3.构造数据 select sid,2,60 from student where sid not in (select student_id from score where course_id=2); -- 4.插入sc表中 insert into sc (student_id,course_id,score) select sid,2,60 from student where sid not in (select student_id from score where course_id=2); -
向sc表中插入记录，需符合以下条件
- 学生学号为：没上过课程号为2的学生学号
- 课程号为：2
- 分数为：课程号为3的最高分
-- 1.上过课程号为2的学号查询如下 mysql > select student_id from score where course_id=2; +------------+ | student_id | +------------+ | 101 | | 201 | | 301 | | 401 | +------------+ 4 rows in set (0.00 sec) -- 2.没上过课程2的学号 mysql > select sid from student where sid not in (select student_id from score where course_id=2); +-----+ | sid | +-----+ | 102 | +-----+ 1 row in set (0.00 sec) -- 3.课程号为3的最高分 mysql> select score from score where course_id=3 order by score desc limit 1; +-------+ | score | +-------+ | 99 | +-------+ 1 row in set (0.01 sec) mysql> select max(score) from score where course_id=3; +------------+ | max(score) | +------------+ | 99 | +------------+ 1 row in set (0.01 sec) -- 4.构造数据 select sid, 2, (select score from score where course_id=3 order by score desc limit 1) as score from student where sid not in (select student_id from score where course_id=2); -- 5.插入 insert into sc(student_id,course_id,score) select sid, 2, (select score from score where course_id=3 order by score desc limit 1) from student where sid not in (select student_id from score where course_id=2);
扫一扫
926
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
