单表查询小考
# -*- coding: utf-8 -*- __author__ = 'caiqinxiong_cai' # 2019/9/23 11:15 # 作业链接：https://www.cnblogs.com/Eva-J/articles/11074845.html ''' # 0.建表book，并向表中插入数据 ''' # 创建book表 # create table book(书名 char(20) not null unique, 作者 char(20) not null, 出版社 char(30) not null, 价格 int not null, 出版日期 date not null) charset = utf8; # 查看表结构 # desc book; # +--------------+----------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +--------------+----------+------+-----+---------+-------+ # | 书名 | char(20) | NO | PRI | NULL | | # | 作者 | char(20) | NO | | NULL | | # | 出版社 | char(30) | NO | | NULL | | # | 价格 | int(11) | NO | | NULL | | # | 出版日期 | date | NO | | NULL | | # +--------------+----------+------+-----+---------+-------+ # 5 rows in set (0.01 sec) # 向book表插入7条数据 ''' insert into book values ('倚天屠龙记','egon','北京工业地雷出版社', 70,20190701), ('九阳神功','alex','人民音乐不好听出版社',5,20180704), ('九阴真经', 'yuan','北京工业地雷出版社',62,20170712), ('九阴白骨爪','jinxin','人民音乐不好听出版社',40,20190807), ('独孤九剑','alex','北京工业地雷出版社', 12,20170901), ('降龙十巴掌','egon','知识产权没有用出版社',20,20190705), ('葵花宝典','yuan','知识产权没有用出版社',33,20190802); ''' # 查询book表所有数据 # mysql> select * from book; # +-----------------+--------+--------------------------------+------ # | 书名 | 作者 | 出版社 | 价格 | 出版日期 | # +-----------------+--------+--------------------------------+------ # | 九阳神功 | alex | 人民音乐不好听出版社 | 5 | 2018-07-04 | # | 九阴白骨爪 | jinxin | 人民音乐不好听出版社 | 40 | 2019-08-07 | # | 九阴真经 | yuan | 北京工业地雷出版社 | 62 | 2017-07-12 | # | 倚天屠龙记 | egon | 北京工业地雷出版社 | 70 | 2019-07-01 | # | 独孤九剑 | alex | 北京工业地雷出版社 | 12 | 2017-09-01 | # | 葵花宝典 | yuan | 知识产权没有用出版社 | 33 | 2019-08-02 | # | 降龙十巴掌 | egon | 知识产权没有用出版社 | 20 | 2019-07-05 | # +-----------------+--------+--------------------------------+---- # 7 rows in set (0.00 sec) ''' 1.查询egon写的所有书和价格 ''' #mysql> select 书名,价格 from book where 作者='egon'; # +-----------------+--------+ # | 书名 | 价格 | # +-----------------+--------+ # | 倚天屠龙记 | 70 | # | 降龙十巴掌 | 20 | # +-----------------+--------+ # 2 rows in set (0.00 sec) ''' 2.找出最贵的图书的价格 ''' # mysql> select max(价格) from book; # +-------------+ # | max(价格) | # +-------------+ # | 70 | # +-------------+ # 1 row in set (0.00 sec) ''' 3.求所有图书的均价 ''' # mysql> select avg(价格) from book; # +-------------+ # | avg(价格) | # +-------------+ # | 34.5714 | # +-------------+ # 1 row in set (0.00 sec) ''' 4.将所有图书按照出版日期排序 ''' # mysql> select * from book order by 出版日期 asc; # +-----------------+--------+--------------------------------+--------+--------------+ # | 书名 | 作者 | 出版社 | 价格 | 出版日期 | # +-----------------+--------+--------------------------------+--------+--------------+ # | 九阴真经 | yuan | 北京工业地雷出版社 | 62 | 2017-07-12 | # | 独孤九剑 | alex | 北京工业地雷出版社 | 12 | 2017-09-01 | # | 九阳神功 | alex | 人民音乐不好听出版社 | 5 | 2018-07-04 | # | 倚天屠龙记 | egon | 北京工业地雷出版社 | 70 | 2019-07-01 | # | 降龙十巴掌 | egon | 知识产权没有用出版社 | 20 | 2019-07-05 | # | 葵花宝典 | yuan | 知识产权没有用出版社 | 33 | 2019-08-02 | # | 九阴白骨爪 | jinxin | 人民音乐不好听出版社 | 40 | 2019-08-07 | # +-----------------+--------+--------------------------------+--------+--------------+ # 7 rows in set (0.00 sec) ''' 5.查询alex写的所有书的平均价格 ''' # mysql> select avg(价格) from book where 作者='alex' group by 作者 ; # +-------------+ # | avg(价格) | # +-------------+ # | 8.5000 | # +-------------+ # 1 row in set (0.00 sec) ''' 6.查询人民音乐不好听出版社出版的所有图书 ''' # mysql> select * from book where 出版社='人民音乐不好听出版社' group by 出版社; # +--------------+--------+--------------------------------+--------+--------------+ # | 书名 | 作者 | 出版社 | 价格 | 出版日期 | # +--------------+--------+--------------------------------+--------+--------------+ # | 九阳神功 | alex | 人民音乐不好听出版社 | 5 | 2018-07-04 | # +--------------+--------+--------------------------------+--------+--------------+ # 1 row in set (0.00 sec) ''' 7.查询人民音乐出版社出版的alex写的所有图书和价格 ''' #按人民音乐出不好听版社搜索到0条结果 # mysql> select 书名,价格 from book where 出版社='人民音乐出不好听版社' and 作者='alex'; # Empty set (0.00 sec) # # 按人民音乐不好听出版社搜索到1条结果 # mysql> select 书名,价格 from book where 出版社='人民音乐不好听出版社' and 作者='alex'; # +--------------+--------+ # | 书名 | 价格 | # +--------------+--------+ # | 九阳神功 | 5 | # +--------------+--------+ # 1 row in set (0.00 sec) ''' 8.找出出版图书均价最高的作者 ''' # mysql> select 作者,avg(价格) from book group by 作者 order by avg(价格) desc limit 1; # +--------+-------------+ # | 作者 | avg(价格) | # +--------+-------------+ # | yuan | 47.5000 | # +--------+-------------+ # 1 row in set (0.00 sec) ''' 9.找出最新出版的图书的作者和出版社 ''' # mysql> select 作者,出版社 from book order by 出版日期 desc limit 1; # +--------+--------------------------------+ # | 作者 | 出版社 | # +--------+--------------------------------+ # | jinxin | 人民音乐不好听出版社 | # +--------+--------------------------------+ # 1 row in set (0.00 sec) ''' 10.显示各出版社出版的所有图书 ''' # mysql> select 出版社,group_concat(书名) from book group by 出版社; # +--------------------------------+-------------------------------------------+ # | 出版社 | group_concat(书名) | # +--------------------------------+-------------------------------------------+ # | 人民音乐不好听出版社 | 九阳神功,九阴白骨爪 | # | 北京工业地雷出版社 | 九阴真经,倚天屠龙记,独孤九剑 | # | 知识产权没有用出版社 | 葵花宝典,降龙十巴掌 | # +--------------------------------+-------------------------------------------+ # 3 rows in set (0.00 sec) ''' 11.查找价格最高的图书，并将它的价格修改为50元 ''' # mysql> select max(价格) from book; # mysql> update book set 价格=50 where 价格=70; # 将两条语句合并成一条执行,不能直接将查询结果进行修改，因为都用了同一个表，如果查询就行，就是同时进行读操作可以，不能同时进行读写操作。所以要将表重新命名转换一下。 # mysql> update book set 价格=50 where 价格=(select * from (select max(价格) from book) as t1); # Query OK, 1 row affected (0.07 sec) # Rows matched: 1 Changed: 1 Warnings: 0 ''' 12.删除价格最低的那本书对应的数据 ''' # mysql> select min(价格) from book; # mysql> delete from book where 价格=5; # 将两条语句合并成一条执行 # mysql> delete from book where 价格=(select * from (select min(价格) from book) as t1); # Query OK, 1 row affected (0.08 sec) ''' 13.将所有alex写的书作者修改成alexsb ''' # mysql> update book set 作者='alexsb' where 作者='alex'; ''' 14.select year(publish_date) from book 自己研究上面sql语句中的year函数的功能，完成需求： 将所有2017年出版的图书从数据库中删除 ''' # mysql> delete from book where year(出版日期)=2017; ''' 15.有文件如下，请根据链接自学pymysql模块，使用python写代码将文件中的数据写入数据库 学python从开始到放弃|alex|人民大学出版社|50|2018-7-1 学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3 学html从开始到放弃|alex|机械工业出版社|20|2018-4-1 学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2 学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30 ''' import pymysql conn = pymysql.connect(host='127.0.0.1', user='caiqinxiong', password="cai",database='school') cur = conn.cursor() sql = "insert into book values (%s,%s,%s,%s,%s)" with open('book',mode='r',encoding='utf-8') as f: for line in f: book_name, author, press, price, date = line.strip().split('|') try: cur.execute(sql,(book_name, author, press, price, date)) # 执行插入数据sql语句，以元组的方式传入参数，字符拼接必须在execute中，防止sql注入。 conn.commit() # 修改、插入、删除操作的sql语句在内存中，需要提交才能真正写入数据库 except: conn.rollback()# 如果发生错误则回滚 cur.close() conn.close()
多表查询的作业-必做15题
# -*- coding: utf-8 -*- __author__ = 'caiqinxiong_cai' # 2019/9/24 14:26 # 多表查询的作业 : https://www.cnblogs.com/Eva-J/articles/9688383.html
# 答案：https://www.cnblogs.com/Eva-J/articles/9760077.html
''' 1、查询男生、女生的人数； ''' # mysql> select gender,count(gender) from student group by gender; # +--------+---------------+ # | gender | count(gender) | # +--------+---------------+ # | 女 | 6 | # | 男 | 10 | # +--------+---------------+ # 2 rows in set (0.00 sec) ''' 2、查询姓“张”的学生名单； ''' # mysql> select * from student where sname like '张%'; # +-----+--------+----------+--------+ # | sid | gender | class_id | sname | # +-----+--------+----------+--------+ # | 3 | 男 | 1 | 张三 | # | 4 | 男 | 1 | 张一 | # | 5 | 女 | 1 | 张二 | # | 6 | 男 | 1 | 张四 | # +-----+--------+----------+--------+ # 4 rows in set (0.00 sec) ''' 3、课程平均分从高到低显示 ''' # mysql> select course_id,avg(num) from score group by course_id order by avg(num) desc; # +-----------+----------+ # | course_id | avg(num) | # +-----------+----------+ # | 4 | 85.2500 | # | 2 | 65.0909 | # | 3 | 64.4167 | # | 1 | 53.4167 | # +-----------+----------+ # 4 rows in set (0.00 sec) # mysql> select cname,avg(num) from course,score where course_id = cid group by course_id order by avg(num) desc; # +--------+----------+ # | cname | avg(num) | # +--------+----------+ # | 美术 | 85.2500 | # | 物理 | 65.0909 | # | 体育 | 64.4167 | # | 生物 | 53.4167 | # +--------+----------+ # 4 rows in set (0.00 sec) ''' 4、查询有课程成绩小于60分的同学的学号、姓名； ''' # mysql> select student_id,sname from student inner join score on student_id = student.sid where num <60; # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 1 | 理解 | # | 1 | 理解 | # | 2 | 钢蛋 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +------------+--------+ # 12 rows in set (0.00 sec) # # mysql> select student_id,sname,num from student inner join score on student_id = student.sid where num <60; # +------------+--------+-----+ # | student_id | sname | num | # +------------+--------+-----+ # | 1 | 理解 | 10 | # | 1 | 理解 | 9 | # | 2 | 钢蛋 | 8 | # | 4 | 张一 | 11 | # | 5 | 张二 | 11 | # | 6 | 张四 | 9 | # | 7 | 铁锤 | 9 | # | 8 | 李三 | 9 | # | 9 | 李一 | 22 | # | 10 | 李二 | 43 | # | 11 | 李四 | 43 | # | 12 | 如花 | 43 | # +------------+--------+-----+ # 12 rows in set (0.01 sec) ''' 5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名； ''' # 学号为1的同学所学课程 # mysql> select course_id from score where student_id = 1; # +-----------+ # | course_id | # +-----------+ # | 1 | # | 2 | # | 4 | # +-----------+ # 3 rows in set (0.00 sec) # mysql> select distinct student_id,sname from student inner join score on student.sid = student_id where course_id in (select course_id from score where student_id = 1); # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 1 | 理解 | # | 2 | 钢蛋 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +------------+--------+ # 12 rows in set (0.00 sec) ''' 6、查询出只选修了一门课程的全部学生的学号和姓名； ''' # mysql> select student_id,sname from student inner join score on student_id = student.sid group by student_id having count(course_id)=1 ; # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 13 | 刘三 | # +------------+--------+ # 1 row in set (0.00 sec) ''' 7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； ''' # mysql> select course_id as 课程ID, max(num) as 最高分, min(num) as 最低分 from score group by course_id; # +----------+-----------+-----------+ # | 课程ID | 最高分 | 最低分 | # +----------+-----------+-----------+ # | 1 | 91 | 8 | # | 2 | 100 | 9 | # | 3 | 87 | 43 | # | 4 | 100 | 22 | # +----------+-----------+-----------+ # 4 rows in set (0.00 sec) ''' 8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名； ''' # mysql> select student.sid,sname from student where ( (select num from score where student_id = student.sid and course_id=2) < (select num from score where student_id = student.sid and course_id=1) ); # +-----+--------+ # | sid | sname | # +-----+--------+ # | 1 | 理解 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +-----+--------+ # 8 rows in set (0.00 sec) ''' 9、查询“生物”课程比“物理”课程成绩高的所有学生的学号； ''' # 1、先将课程表的名称和成绩表的课程id对应起来 # select * from score inner join course on course_id = course.cid; #2、再分别找出“生物”课程和“物理”课程的信息 # mysql> select * from score inner join course on course_id = course.cid where cname='生物'; # +-----+------------+-----------+-----+-----+--------+------------+ # | sid | student_id | course_id | num | cid | cname | teacher_id | # +-----+------------+-----------+-----+-----+--------+------------+ # | 1 | 1 | 1 | 10 | 1 | 生物 | 1 | # | 6 | 2 | 1 | 8 | 1 | 生物 | 1 | # | 10 | 3 | 1 | 77 | 1 | 生物 | 1 | # | 14 | 4 | 1 | 79 | 1 | 生物 | 1 | # | 18 | 5 | 1 | 79 | 1 | 生物 | 1 | # | 22 | 6 | 1 | 9 | 1 | 生物 | 1 | # | 26 | 7 | 1 | 9 | 1 | 生物 | 1 | # | 30 | 8 | 1 | 9 | 1 | 生物 | 1 | # | 34 | 9 | 1 | 91 | 1 | 生物 | 1 | # | 38 | 10 | 1 | 90 | 1 | 生物 | 1 | # | 42 | 11 | 1 | 90 | 1 | 生物 | 1 | # | 46 | 12 | 1 | 90 | 1 | 生物 | 1 | # +-----+------------+-----------+-----+-----+--------+------------+ # mysql> select * from score inner join course on course_id = course.cid where cname='物理'; # +-----+------------+-----------+-----+-----+--------+------------+ # | sid | student_id | course_id | num | cid | cname | teacher_id | # +-----+------------+-----------+-----+-----+--------+------------+ # | 2 | 1 | 2 | 9 | 2 | 物理 | 2 | # | 11 | 3 | 2 | 66 | 2 | 物理 | 2 | # | 15 | 4 | 2 | 11 | 2 | 物理 | 2 | # | 19 | 5 | 2 | 11 | 2 | 物理 | 2 | # | 23 | 6 | 2 | 100 | 2 | 物理 | 2 | # | 27 | 7 | 2 | 100 | 2 | 物理 | 2 | # | 31 | 8 | 2 | 100 | 2 | 物理 | 2 | # | 35 | 9 | 2 | 88 | 2 | 物理 | 2 | # | 39 | 10 | 2 | 77 | 2 | 物理 | 2 | # | 43 | 11 | 2 | 77 | 2 | 物理 | 2 | # | 47 | 12 | 2 | 77 | 2 | 物理 | 2 | # +-----+------------+-----------+-----+-----+--------+------------+ # 11 rows in set (0.00 sec) # 3、把这两个查询出来的信息当做两张表组合成一张表 # mysql> select * from (select * from score inner join course on course_id = course.cid where cname='生物') as t1 inner join (select * from score inner join course on course_id = course.cid where cname='物理') as t2 on t1.student_id = t2.student_id; # +-----+------------+-----------+-----+-----+--------+------------+-----+------------+-----------+-----+-----+--------+------------+ # | sid | student_id | course_id | num | cid | cname | teacher_id | sid | student_id | course_id | num | cid | cname | teacher_id | # +-----+------------+-----------+-----+-----+--------+------------+-----+------------+-----------+-----+-----+--------+------------+ # | 1 | 1 | 1 | 10 | 1 | 生物 | 1 | 2 | 1 | 2 | 9 | 2 | 物理 | 2 | # | 10 | 3 | 1 | 77 | 1 | 生物 | 1 | 11 | 3 | 2 | 66 | 2 | 物理 | 2 | # | 14 | 4 | 1 | 79 | 1 | 生物 | 1 | 15 | 4 | 2 | 11 | 2 | 物理 | 2 | # | 18 | 5 | 1 | 79 | 1 | 生物 | 1 | 19 | 5 | 2 | 11 | 2 | 物理 | 2 | # | 22 | 6 | 1 | 9 | 1 | 生物 | 1 | 23 | 6 | 2 | 100 | 2 | 物理 | 2 | # | 26 | 7 | 1 | 9 | 1 | 生物 | 1 | 27 | 7 | 2 | 100 | 2 | 物理 | 2 | # | 30 | 8 | 1 | 9 | 1 | 生物 | 1 | 31 | 8 | 2 | 100 | 2 | 物理 | 2 | # | 34 | 9 | 1 | 91 | 1 | 生物 | 1 | 35 | 9 | 2 | 88 | 2 | 物理 | 2 | # | 38 | 10 | 1 | 90 | 1 | 生物 | 1 | 39 | 10 | 2 | 77 | 2 | 物理 | 2 | # | 42 | 11 | 1 | 90 | 1 | 生物 | 1 | 43 | 11 | 2 | 77 | 2 | 物理 | 2 | # | 46 | 12 | 1 | 90 | 1 | 生物 | 1 | 47 | 12 | 2 | 77 | 2 | 物理 | 2 | # +-----+------------+-----------+-----+-----+--------+------------+-----+------------+-----------+-----+-----+--------+------------+ # 11 rows in set (0.00 sec) # 4、再比较查询“生物”课程比“物理”课程成绩高的所有学生的学号 # mysql> select t1.student_id,t1.num as 生物,t2.num as 物理 from (select * from score inner join course on course_id = course.cid where cname='生物') as t1 inner join (select * from score inner join course on course_id = course.cid where cname='物理') as t2 on t1.student_id = t2.student_id where t1.num > t2.num; # +------------+--------+--------+ # | student_id | 生物 | 物理 | # +------------+--------+--------+ # | 1 | 10 | 9 | # | 3 | 77 | 66 | # | 4 | 79 | 11 | # | 5 | 79 | 11 | # | 9 | 91 | 88 | # | 10 | 90 | 77 | # | 11 | 90 | 77 | # | 12 | 90 | 77 | # +------------+--------+--------+ # 8 rows in set (0.00 sec) ''' 10、查询平均成绩大于60分的同学的学号和平均成绩; ''' # mysql> select student_id,avg(num) from score group by student_id having avg(num)>60; # +------------+----------+ # | student_id | avg(num) | # +------------+----------+ # | 3 | 82.2500 | # | 4 | 64.2500 | # | 5 | 64.2500 | # | 6 | 69.0000 | # | 7 | 66.0000 | # | 8 | 66.0000 | # | 9 | 67.0000 | # | 10 | 74.2500 | # | 11 | 74.2500 | # | 12 | 74.2500 | # | 13 | 87.0000 | # +------------+----------+ # 11 rows in set (0.01 sec) ''' 11、查询所有同学的学号、姓名、选课数、总成绩； ''' # mysql> select student_id,sname,count(course_id),sum(num) from student inner join score on student.sid = student_id group by student_id; # +------------+--------+------------------+----------+ # | student_id | sname | count(course_id) | sum(num) | # +------------+--------+------------------+----------+ # | 1 | 理解 | 3 | 85 | # | 2 | 钢蛋 | 3 | 175 | # | 3 | 张三 | 4 | 329 | # | 4 | 张一 | 4 | 257 | # | 5 | 张二 | 4 | 257 | # | 6 | 张四 | 4 | 276 | # | 7 | 铁锤 | 4 | 264 | # | 8 | 李三 | 4 | 264 | # | 9 | 李一 | 4 | 268 | # | 10 | 李二 | 4 | 297 | # | 11 | 李四 | 4 | 297 | # | 12 | 如花 | 4 | 297 | # | 13 | 刘三 | 1 | 87 | # +------------+--------+------------------+----------+ # 13 rows in set (0.00 sec) ''' 12、查询姓“李”的老师的个数； ''' # mysql> select count(tname) from teacher where tname like '李%'; # +--------------+ # | count(tname) | # +--------------+ # | 2 | # +--------------+ # 1 row in set (0.00 sec) ''' 13、查询没学过“张磊老师”课的同学的学号、姓名； ''' # 方法一 # 1、先找到张磊老师所教课程 # mysql> select * from course inner join teacher on teacher_id=tid where tname='张磊老师'; # +-----+--------+------------+-----+--------------+ # | cid | cname | teacher_id | tid | tname | # +-----+--------+------------+-----+--------------+ # | 1 | 生物 | 1 | 1 | 张磊老师 | # +-----+--------+------------+-----+--------------+ # 1 row in set (0.00 sec) # 2、找出选了张磊老师课程的学生 # mysql> select * from score where course_id = (select cid from course inner join teacher on teacher_id=tid where tname='张磊老师'); # +-----+------------+-----------+-----+ # | sid | student_id | course_id | num | # +-----+------------+-----------+-----+ # | 1 | 1 | 1 | 10 | # | 6 | 2 | 1 | 8 | # | 10 | 3 | 1 | 77 | # | 14 | 4 | 1 | 79 | # | 18 | 5 | 1 | 79 | # | 22 | 6 | 1 | 9 | # | 26 | 7 | 1 | 9 | # | 30 | 8 | 1 | 9 | # | 34 | 9 | 1 | 91 | # | 38 | 10 | 1 | 90 | # | 42 | 11 | 1 | 90 | # | 46 | 12 | 1 | 90 | # +-----+------------+-----------+-----+ # 12 rows in set (0.00 sec) # 3、最后反选一下所有学生中不包含选了张磊老师的课的学生就行 # mysql> select sid,sname from student where sid not in (select student_id from score where course_id = (select cid from course inner join teacher on teacher_id=tid where tname='张磊老师')); # +-----+--------+ # | sid | sname | # +-----+--------+ # | 13 | 刘三 | # | 14 | 刘一 | # | 15 | 刘二 | # | 16 | 刘四 | # +-----+--------+ # 4 rows in set (0.00 sec) # 方法二，直接用多个子查询套用 # mysql> select sid,sname from student where sid not in (select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'))); # +-----+--------+ # | sid | sname | # +-----+--------+ # | 13 | 刘三 | # | 14 | 刘一 | # | 15 | 刘二 | # | 16 | 刘四 | # +-----+--------+ # 4 rows in set (0.00 sec) ''' 14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名； ''' # 方法一 # 1、先找出学过课程1的同学 # mysql> select * from student inner join score on student_id = student.sid where course_id=1 ; # +-----+--------+----------+--------+-----+------------+-----------+-----+ # | sid | gender | class_id | sname | sid | student_id | course_id | num | # +-----+--------+----------+--------+-----+------------+-----------+-----+ # | 1 | 男 | 1 | 理解 | 1 | 1 | 1 | 10 | # | 2 | 女 | 1 | 钢蛋 | 6 | 2 | 1 | 8 | # | 3 | 男 | 1 | 张三 | 10 | 3 | 1 | 77 | # | 4 | 男 | 1 | 张一 | 14 | 4 | 1 | 79 | # | 5 | 女 | 1 | 张二 | 18 | 5 | 1 | 79 | # | 6 | 男 | 1 | 张四 | 22 | 6 | 1 | 9 | # | 7 | 女 | 2 | 铁锤 | 26 | 7 | 1 | 9 | # | 8 | 男 | 2 | 李三 | 30 | 8 | 1 | 9 | # | 9 | 男 | 2 | 李一 | 34 | 9 | 1 | 91 | # | 10 | 女 | 2 | 李二 | 38 | 10 | 1 | 90 | # | 11 | 男 | 2 | 李四 | 42 | 11 | 1 | 90 | # | 12 | 女 | 3 | 如花 | 46 | 12 | 1 | 90 | # +-----+--------+----------+--------+-----+------------+-----------+-----+ # 2、再找出学过课程2的同学 # mysql> select * from student inner join score on student_id = student.sid where course_id=2; # +-----+--------+----------+--------+-----+------------+-----------+-----+ # | sid | gender | class_id | sname | sid | student_id | course_id | num | # +-----+--------+----------+--------+-----+------------+-----------+-----+ # | 1 | 男 | 1 | 理解 | 2 | 1 | 2 | 9 | # | 3 | 男 | 1 | 张三 | 11 | 3 | 2 | 66 | # | 4 | 男 | 1 | 张一 | 15 | 4 | 2 | 11 | # | 5 | 女 | 1 | 张二 | 19 | 5 | 2 | 11 | # | 6 | 男 | 1 | 张四 | 23 | 6 | 2 | 100 | # | 7 | 女 | 2 | 铁锤 | 27 | 7 | 2 | 100 | # | 8 | 男 | 2 | 李三 | 31 | 8 | 2 | 100 | # | 9 | 男 | 2 | 李一 | 35 | 9 | 2 | 88 | # | 10 | 女 | 2 | 李二 | 39 | 10 | 2 | 77 | # | 11 | 男 | 2 | 李四 | 43 | 11 | 2 | 77 | # | 12 | 女 | 3 | 如花 | 47 | 12 | 2 | 77 | # +-----+--------+----------+--------+-----+------------+-----------+-----+ # 11 rows in set (0.00 sec) # # 3、把前面找出来的信息再组合成新的一张表，就可以找出既选择1课程又选择2课程学生了 # mysql> select t1.student_id,t1.sname from (select student_id,sname,course_id from student inner join score on student_id = student.sid where course_id=1) as t1 inner join ( select student_id,sname,course_id from student inner join score on student_id = student.sid where course_id=2) as t2 on t1.student_id = t2.student_id; # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 1 | 理解 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +------------+--------+ # 11 rows in set (0.00 sec) # 方法二 # mysql> select sid,sname from student where sid in (select t1.student_id from (select student_id from score where course_id = 1) t1 inner join (select student_id from score where course_id = 2) t2 on t1.student_id = t2.student_id); # +-----+--------+ # | sid | sname | # +-----+--------+ # | 1 | 理解 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +-----+--------+ # 11 rows in set (0.00 sec) ''' 15、查询学过“李平老师”所教的所有课的同学的学号、姓名； ''' # 方法一 # 1、先找到'李平老师'对应的tid号 # mysql> select * from teacher where tname='李平老师'; # +-----+--------------+ # | tid | tname | # +-----+--------------+ # | 2 | 李平老师 | # +-----+--------------+ # 1 row in set (0.00 sec) # # 2、通过tid号找到'李平老师'所教的课程对应的cid号 # mysql> select * from course where teacher_id = (select tid from teacher where tname='李平老师'); # +-----+--------+------------+ # | cid | cname | teacher_id | # +-----+--------+------------+ # | 2 | 物理 | 2 | # | 4 | 美术 | 2 | # +-----+--------+------------+ # 2 rows in set (0.00 sec) # # 3、统计'李平老师'一共教了几门课程 # mysql> select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'); # +------------+ # | count(cid) | # +------------+ # | 2 | # +------------+ # 1 row in set (0.00 sec) # 3、将选择了'李平老师'所教的两门课程的学生查询出来。 # mysql> select student_id,sname from student inner join score on student_id = student.sid where course_id in (select cid from course where teacher_id = (select tid from teacher where tname='李平老师')) group by sname having count(sname)=(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师')); # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 12 | 如花 | # | 4 | 张一 | # | 3 | 张三 | # | 5 | 张二 | # | 6 | 张四 | # | 9 | 李一 | # | 8 | 李三 | # | 10 | 李二 | # | 11 | 李四 | # | 1 | 理解 | # | 7 | 铁锤 | # +------------+--------+ # 11 rows in set (0.01 sec) # 方法二 # mysql> select sid,sname from student where sid in ( select student_id from ( select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1 where t1.course_count = (select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师')) ); # +-----+--------+ # | sid | sname | # +-----+--------+ # | 1 | 理解 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +-----+--------+ # 11 rows in set (0.00 sec)
多表查询的作业-选做25题
# -*- coding: utf-8 -*- __author__ = 'caiqinxiong_cai' # 2019/9/25 15:22 # 多表查询的作业 : https://www.cnblogs.com/Eva-J/articles/9688383.html
# 答案：https://www.cnblogs.com/Eva-J/articles/9765370.html
''' 1、查询没有学全所有课的同学的学号、姓名； ''' # 1、先查询一共有几门课程 # mysql> select count(cid) from course; # +------------+ # | count(cid) | # +------------+ # | 4 | # +------------+ # 1 row in set (0.00 sec) # 2、在分数表中根据学生进行分组，获取每一个学生选课数量，如果数量等于总课程数量，说明已经选择了所有课程 # mysql> select student_id,sname from score inner join student on score.student_id = student.sid group by student_id having count(course_id)=(select count(cid) from course); # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +------------+--------+ # 10 rows in set (0.00 sec) ''' 2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名； ''' # 1、先查找2号同学学习了哪些课程 # mysql> select * from score where student_id = 2; # +-----+------------+-----------+-----+ # | sid | student_id | course_id | num | # +-----+------------+-----------+-----+ # | 6 | 2 | 1 | 8 | # | 8 | 2 | 3 | 68 | # | 9 | 2 | 4 | 99 | # +-----+------------+-----------+-----+ # 3 rows in set (0.01 sec) # # 2、统计2号同学一共学了几门课程 # mysql> select count(course_id) from score where student_id = 2; # +------------------+ # | count(course_id) | # +------------------+ # | 3 | # +------------------+ # 1 row in set (0.00 sec) # # 3、找出和2号同学学习课程数量一样的同学id # mysql> select student_id from score where student_id != 2 group by student_id having count(course_id)=(select count(course_id) from score where student_id = 2); # +------------+ # | student_id | # +------------+ # | 1 | # +------------+ # 1 row in set (0.00 sec) # 4、最后找出既课程数量和2号同学一样的并且选课也一样的 # mysql> select student_id,sname from score inner join student on score.student_id = student.sid where student_id in (select student_id from score where student_id != 2 group by student_id having count(course_id) = (select count(course_id) from score where student_id = 2)) and course_id in (select course_id from score where student_id = 2) group by student_id having count(course_id) =(select count(course_id) from score where student_id = 2); # Empty set (0.00 sec) # 没有和2号同学选课一样的，改成3号同学看看效果 # mysql> select student_id,sname from score inner join student on score.student_id = student.sid where student_id in (select student_id from score where student_id != 3 group by student_id having count(course_id) = (select count(course_id) from score where student_id = 3)) and course_id in (select course_id from score where student_id = 3) group by student_id having count(course_id) =(select count(course_id) from score where student_id = 3); # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # +------------+--------+ # 9 rows in set (0.00 sec) ''' 3、删除学习“李平”老师课的SC表记录； ''' # 1、查出李平老师的课程id号 # mysql> select * from course inner join teacher on course.teacher_id = teacher.tid where tname = '李平老师'; # +-----+--------+------------+-----+--------------+ # | cid | cname | teacher_id | tid | tname | # +-----+--------+------------+-----+--------------+ # | 2 | 物理 | 2 | 2 | 李平老师 | # | 4 | 美术 | 2 | 2 | 李平老师 | # +-----+--------+------------+-----+--------------+ # 2 rows in set (0.00 sec) # # 2、在成绩表中删除掉李平老师相关的课程 # mysql> delete from score where course_id in (select cid from course inner join teacher on course.teacher_id = teacher.tid where tname = '李平老师'); # Query OK, 23 rows affected (0.14 sec) ''' 4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； ''' # 表插入数据方法一 # insert into 表名(字段名) values (值); # 方法二 # inset into t1(a,b) select c,d from t2; # t2中选择两个字段的值插入到t1，数据类型必须一致。 # 1、查看score表的字段约束，发现sid是自动增长的，所以，只需要插入student_id, course_id, num三个字段的值就行了,而且course_id=2 # mysql> desc score; # +------------+---------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +------------+---------+------+-----+---------+----------------+ # | sid | int(11) | NO | PRI | NULL | auto_increment | # | student_id | int(11) | NO | MUL | NULL | | # | course_id | int(11) | NO | MUL | NULL | | # | num | int(11) | NO | | NULL | | # +------------+---------+------+-----+---------+----------------+ # 4 rows in set (0.00 sec) # # 2、获取002的平均成绩 # mysql> select avg(num) from score where course_id = 2; # +----------+ # | avg(num) | # +----------+ # | 0.0000 | # +----------+ # 1 row in set (0.01 sec) # 3、获取所有没上过002课的所有同学 # mysql> select student_id from score where course_id = 2; # +------------+ # | student_id | # +------------+ # | 1 | # | 2 | # | 3 | # | 4 | # | 5 | # | 6 | # | 7 | # | 8 | # | 9 | # | 10 | # | 11 | # | 12 | # | 13 | # | 14 | # | 15 | # | 16 | # +------------+ # 16 rows in set (0.00 sec) # 4、将这些满足条件的sql语句进行整合，插入数据到score表。 # mysql> insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) from student where sid not in ( select student_id from score where course_id = 2); # Query OK, 16 rows affected, 16 warnings (0.59 sec) # Records: 16 Duplicates: 0 Warnings: 16 ''' 5、按平均成绩从低到高显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分； ''' # 按平均成绩从低到高显示有效课程数,有效平均分 # mysql> select student_id,count(course_id),avg(num) from score group by student_id order by avg(num) asc; # +------------+------------------+----------+ # | student_id | count(course_id) | avg(num) | # +------------+------------------+----------+ # | 14 | 1 | 0.0000 | # | 15 | 1 | 0.0000 | # | 16 | 1 | 0.0000 | # | 1 | 2 | 5.0000 | # | 6 | 3 | 25.3333 | # | 7 | 3 | 25.3333 | # | 8 | 3 | 25.3333 | # | 2 | 3 | 25.3333 | # | 13 | 2 | 43.5000 | # | 10 | 3 | 44.3333 | # | 11 | 3 | 44.3333 | # | 12 | 3 | 44.3333 | # | 4 | 3 | 48.6667 | # | 5 | 3 | 48.6667 | # | 9 | 3 | 52.6667 | # | 3 | 3 | 54.6667 | # +------------+------------------+----------+ # 16 rows in set (0.00 sec) # 求出生物的成绩 # mysql> select student_id,num from score left join course on course_id = cid where cname = "生物"; # +------------+-----+ # | student_id | num | # +------------+-----+ # | 1 | 10 | # | 2 | 8 | # | 3 | 77 | # | 4 | 79 | # | 5 | 79 | # | 6 | 9 | # | 7 | 9 | # | 8 | 9 | # | 9 | 91 | # | 10 | 90 | # | 11 | 90 | # | 12 | 90 | # +------------+-----+ # 12 rows in set (0.00 sec) # 将前面两张表整合 # mysql> select sc.student_id,(select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id ) as 生物,count(course_id),avg(sc.num) from score as sc group by student_id order by avg(sc.num) asc; # +------------+--------+------------------+-------------+ # | student_id | 生物 | count(course_id) | avg(sc.num) | # +------------+--------+------------------+-------------+ # | 15 | NULL | 1 | 0.0000 | # | 16 | NULL | 1 | 0.0000 | # | 14 | NULL | 1 | 0.0000 | # | 1 | 10 | 2 | 5.0000 | # | 6 | 9 | 3 | 25.3333 | # | 7 | 9 | 3 | 25.3333 | # | 2 | 8 | 3 | 25.3333 | # | 8 | 9 | 3 | 25.3333 | # | 13 | NULL | 2 | 43.5000 | # | 10 | 90 | 3 | 44.3333 | # | 11 | 90 | 3 | 44.3333 | # | 12 | 90 | 3 | 44.3333 | # | 4 | 79 | 3 | 48.6667 | # | 5 | 79 | 3 | 48.6667 | # | 9 | 91 | 3 | 52.6667 | # | 3 | 77 | 3 | 54.6667 | # +------------+--------+------------------+-------------+ # 16 rows in set (0.00 sec) # # 以此类推，加上物理和体育的成绩进行整合 # mysql> select sc.student_id as 学生ID,(select num from score inner join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as 生物,(select num from score inner join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as 物理,(select num from score inner join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as 体育,count(sc.course_id) as 有效课程数,avg(sc.num) as 有效平均分 from score as sc group by student_id order by avg(sc.num) asc; # +----------+--------+--------+--------+-----------------+-----------------+ # | 学生ID | 生物 | 物理 | 体育 | 有效课程数 | 有效平均分 | # +----------+--------+--------+--------+-----------------+-----------------+ # | 14 | NULL | 0 | NULL | 1 | 0.0000 | # | 15 | NULL | 0 | NULL | 1 | 0.0000 | # | 16 | NULL | 0 | NULL | 1 | 0.0000 | # | 1 | 10 | 0 | NULL | 2 | 5.0000 | # | 7 | 9 | 0 | 67 | 3 | 25.3333 | # | 8 | 9 | 0 | 67 | 3 | 25.3333 | # | 2 | 8 | 0 | 68 | 3 | 25.3333 | # | 6 | 9 | 0 | 67 | 3 | 25.3333 | # | 13 | NULL | 0 | 87 | 2 | 43.5000 | # | 11 | 90 | 0 | 43 | 3 | 44.3333 | # | 12 | 90 | 0 | 43 | 3 | 44.3333 | # | 10 | 90 | 0 | 43 | 3 | 44.3333 | # | 4 | 79 | 0 | 67 | 3 | 48.6667 | # | 5 | 79 | 0 | 67 | 3 | 48.6667 | # | 9 | 91 | 0 | 67 | 3 | 52.6667 | # | 3 | 77 | 0 | 87 | 3 | 54.6667 | # +----------+--------+--------+--------+-----------------+-----------------+ # 16 rows in set (0.00 sec) ''' 6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； ''' # mysql> select course_id as 课程ID, max(num) as 最高分, min(num) as 最低分 from score group by course_id; # +----------+-----------+-----------+ # | 课程ID | 最高分 | 最低分 | # +----------+-----------+-----------+ # | 1 | 91 | 8 | # | 2 | 0 | 0 | # | 3 | 87 | 43 | # +----------+-----------+-----------+ # 3 rows in set (0.00 sec) ''' 7、按各科平均成绩从低到高和及格率的百分数从高到低顺序； ''' # mysql> select course_id, avg(num) as 平均分,sum(case when score.num > 60 then 1 else 0 end)/count(1)*100 as 及格率 from score group by course_id order by 平均分 asc,及格率 desc; # +-----------+-----------+-----------+ # | course_id | 平均分 | 及格率 | # +-----------+-----------+-----------+ # | 2 | 0.0000 | 0.0000 | # | 1 | 53.4167 | 58.3333 | # | 3 | 64.4167 | 75.0000 | # +-----------+-----------+-----------+ # 3 rows in set (0.00 sec) ''' 8、查询各科成绩前三名的记录:(不考虑成绩并列情况) ''' # mysql> SELECT s1.cid AS 课程ID,s1.cname AS 课程, (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一, (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 2,1) AS 第三 FROM course AS s1; # +----------+--------+--------+--------+--------+ # | 课程ID | 课程 | 第一 | 第二 | 第三 | # +----------+--------+--------+--------+--------+ # | 1 | 生物 | 91 | 90 | 79 | # | 2 | 物理 | 0 | NULL | NULL | # | 3 | 体育 | 87 | 68 | 67 | # | 4 | 美术 | NULL | NULL | NULL | # +----------+--------+--------+--------+--------+ # 4 rows in set (0.00 sec) ''' 9、查询每门课程被选修的学生数； ''' # mysql> select cid,cname,count(student_id) from course left join score on course_id = cid group by course_id; # +-----+--------+-------------------+ # | cid | cname | count(student_id) | # +-----+--------+-------------------+ # | 4 | 美术 | 0 | # | 1 | 生物 | 12 | # | 2 | 物理 | 16 | # | 3 | 体育 | 12 | # +-----+--------+-------------------+ # 4 rows in set (0.00 sec) ''' 10、查询同名同姓学生名单，并统计同名人数； ''' # mysql> select sname,count(sname) from student group by sname; # +--------+--------------+ # | sname | count(sname) | # +--------+--------------+ # | 刘一 | 1 | # | 刘三 | 1 | # | 刘二 | 1 | # | 刘四 | 1 | # | 如花 | 1 | # | 张一 | 1 | # | 张三 | 1 | # | 张二 | 1 | # | 张四 | 1 | # | 李一 | 1 | # | 李三 | 1 | # | 李二 | 1 | # | 李四 | 1 | # | 理解 | 1 | # | 钢蛋 | 1 | # | 铁锤 | 1 | # +--------+--------------+ # 16 rows in set (0.00 sec) ''' 11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； ''' # mysql> select cid,cname,avg(if(isnull(num), 0 ,num)) as 平均分 from course left join score on course_id = cid group by course_id order by avg(num) asc,cid desc; # +-----+--------+-----------+ # | cid | cname | 平均分 | # +-----+--------+-----------+ # | 4 | 美术 | 0.0000 | # | 2 | 物理 | 0.0000 | # | 1 | 生物 | 53.4167 | # | 3 | 体育 | 64.4167 | # +-----+--------+-----------+ # 4 rows in set (0.00 sec) ''' 12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩； ''' # 1、先查询所有学生的平均成绩 # mysql> select student_id,sname,avg(num) from student left join score on student_id = student.sid group by student_id; # +------------+--------+----------+ # | student_id | sname | avg(num) | # +------------+--------+----------+ # | 1 | 理解 | 5.0000 | # | 2 | 钢蛋 | 25.3333 | # | 3 | 张三 | 54.6667 | # | 4 | 张一 | 48.6667 | # | 5 | 张二 | 48.6667 | # | 6 | 张四 | 25.3333 | # | 7 | 铁锤 | 25.3333 | # | 8 | 李三 | 25.3333 | # | 9 | 李一 | 52.6667 | # | 10 | 李二 | 44.3333 | # | 11 | 李四 | 44.3333 | # | 12 | 如花 | 44.3333 | # | 13 | 刘三 | 43.5000 | # | 14 | 刘一 | 0.0000 | # | 15 | 刘二 | 0.0000 | # | 16 | 刘四 | 0.0000 | # +------------+--------+----------+ # 16 rows in set (0.00 sec) # 2、过滤出平均成绩大于85分的，没有大于85分的同学 # mysql> select student_id,sname,avg(num) from student left join score on student_id = student.sid group by student_id having avg(num)>85; # Empty set (0.00 sec) ''' 13、查询课程名称为“生物”，且分数低于60的学生姓名和分数 ''' # 方法一 # mysql> select sname,num from student left join score on student_id = student.sid where course_id = ( select cid from course where cname='生物') and num<60; # +--------+------+ # | sname | num | # +--------+------+ # | 理解 | 10 | # | 钢蛋 | 8 | # | 张四 | 9 | # | 铁锤 | 9 | # | 李三 | 9 | # +--------+------+ # 5 rows in set (0.00 sec) # 方法二（3个左连接并用） # mysql> SELECT student.sid AS 学号,student.sname AS 姓名,score.num AS 成绩 FROM score LEFT JOIN course ON score.course_id=course.cid LEFT JOIN student ON score.student_id= student.sid WHERE course.cname="生物" AND score.num<60; # +--------+--------+--------+ # | 学号 | 姓名 | 成绩 | # +--------+--------+--------+ # | 1 | 理解 | 10 | # | 2 | 钢蛋 | 8 | # | 6 | 张四 | 9 | # | 7 | 铁锤 | 9 | # | 8 | 李三 | 9 | # +--------+--------+--------+ # 5 rows in set (0.00 sec) ''' 14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； ''' # mysql> select student_id,sname from student left join score on student_id = student.sid where course_id=3 and num >80; # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 3 | 张三 | # | 13 | 刘三 | # +------------+--------+ # 2 rows in set (0.00 sec) ''' 15、求选了课程的学生人数 ''' # 方法一 # mysql> select count(t1.student_id) as 选课总人数 from (select student_id from score group by student_id) as t1; # +-----------------+ # | 选课总人数 | # +-----------------+ # | 16 | # +-----------------+ # 1 row in set (0.00 sec) # # 方法二 # mysql> select count(distinct student_id) as 选课总人数 from score; # +-----------------+ # | 选课总人数 | # +-----------------+ # | 16 | # +-----------------+ # 1 row in set (0.01 sec) ''' 16、查询选修“张磊老师”所授课程的学生中，成绩最高的学生姓名及其成绩； ''' # 方法一 # mysql> select sname,num from score left join student on score.student_id = student.sid where score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊老师') order by num desc limit 1; # +--------+-----+ # | sname | num | # +--------+-----+ # | 李一 | 91 | # +--------+-----+ # 1 row in set (0.00 sec) # 方法二（3个左连接并用） # mysql> SELECT student.sid AS 学号,student.sname AS 姓名,num AS 成绩 FROM score LEFT JOIN course ON score.course_id=course.cid LEFT JOIN student ON score.student_id=student.sid LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname = "张磊老师" ORDER BY num DESC LIMIT 1; # +--------+--------+--------+ # | 学号 | 姓名 | 成绩 | # +--------+--------+--------+ # | 9 | 李一 | 91 | # +--------+--------+--------+ # 1 row in set (0.00 sec) # 方法三（多个子查询套用） # mysql> select sname,num from student left join score on student_id = student.sid where course_id in (select cid from course where teacher_id = ( select tid from teacher where tname='张磊老师')) order by num desc limit 1; # +--------+------+ # | sname | num | # +--------+------+ # | 李一 | 91 | # +--------+------+ # 1 row in set (0.00 sec) ''' 17、查询各个课程及相应的选修人数； ''' # mysql> select cid,cname,count(student_id) from course left join score on course_id = cid group by course_id; # +-----+--------+-------------------+ # | cid | cname | count(student_id) | # +-----+--------+-------------------+ # | 4 | 美术 | 0 | # | 1 | 生物 | 12 | # | 2 | 物理 | 16 | # | 3 | 体育 | 12 | # +-----+--------+-------------------+ # 4 rows in set (0.00 sec) # ''' 18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； ''' # 利用同一张表重命名成两张表，再比较。 # mysql> select distinct s1.course_id,s2.course_id,s1.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; # Empty set (0.00 sec) # 没有不同课程一样成绩的同学，手动修改某个同学的成绩查看效果 # mysql> update score set num = 8 where sid=8; # Query OK, 1 row affected (0.07 sec) # Rows matched: 1 Changed: 1 Warnings: 0 # # mysql> select distinct s1.course_id,s2.course_id,s1.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; # +-----------+-----------+-----+ # | course_id | course_id | num | # +-----------+-----------+-----+ # | 3 | 1 | 8 | # | 1 | 3 | 8 | # +-----------+-----------+-----+ # 2 rows in set (0.00 sec) ''' 19、查询每门课程成绩最好的前两名； ''' # mysql> SELECT cid AS 课程ID,cname AS 课程,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二 FROM course AS s1; # +----------+--------+--------+--------+ # | 课程ID | 课程 | 第一 | 第二 | # +----------+--------+--------+--------+ # | 1 | 生物 | 91 | 90 | # | 2 | 物理 | 0 | NULL | # | 3 | 体育 | 87 | 68 | # | 4 | 美术 | NULL | NULL | # +----------+--------+--------+--------+ # 4 rows in set (0.00 sec) # ''' 20、检索至少选修两门课程的学生学号； ''' # mysql> select student_id from score group by student_id having count(course_id)>=2; # +------------+ # | student_id | # +------------+ # | 1 | # | 2 | # | 3 | # | 4 | # | 5 | # | 6 | # | 7 | # | 8 | # | 9 | # | 10 | # | 11 | # | 12 | # | 13 | # +------------+ # 13 rows in set (0.00 sec) ''' 21、查询全部学生都选修的课程的课程号和课程名； ''' # mysql> select course_id,cname from course inner join score on course_id = cid group by course_id having count(course_id)=(select count(sid) from student); # +-----------+--------+ # | course_id | cname | # +-----------+--------+ # | 2 | 物理 | # +-----------+--------+ # 1 row in set (0.00 sec) ''' 22、查询没学过“李平”老师讲授的任一门课程的学生姓名； ''' # 方法一 （多个子查询套用） # mysql> select student_id,sname from student left join score on student.sid=student_id where course_id not in (select cid from course where teacher_id = (select tid from teacher where tname='李平老师')) group by student_id; # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 1 | 理解 | # | 2 | 钢蛋 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # | 13 | 刘三 | # +------------+--------+ # 13 rows in set (0.00 sec) # 方法二（利用左连接） # mysql> select student_id,student.sname from score left join student on score.student_id = student.sid where score.course_id not in (select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师') group by student_id; # +------------+--------+ # | student_id | sname | # +------------+--------+ # | 1 | 理解 | # | 2 | 钢蛋 | # | 3 | 张三 | # | 4 | 张一 | # | 5 | 张二 | # | 6 | 张四 | # | 7 | 铁锤 | # | 8 | 李三 | # | 9 | 李一 | # | 10 | 李二 | # | 11 | 李四 | # | 12 | 如花 | # | 13 | 刘三 | # +------------+--------+ # 13 rows in set (0.00 sec) ''' 23、查询两门以上不及格课程的同学的学号及其平均成绩； ''' # mysql> select student_id,avg(num) from score where student_id in ( select student_id from score where num<60 group by student_id having count(course_id)>=2) group by student_id; # +------------+----------+ # | student_id | avg(num) | # +------------+----------+ # | 1 | 5.0000 | # | 2 | 25.3333 | # | 6 | 25.3333 | # | 7 | 25.3333 | # | 8 | 25.3333 | # | 10 | 44.3333 | # | 11 | 44.3333 | # | 12 | 44.3333 | # +------------+----------+ # 8 rows in set (0.00 sec) ''' 24、检索“004”课程分数小于60，按分数降序排列的同学学号； ''' # mysql> select student_id from score where course_id=4 and num<60 order by num desc; # Empty set (0.00 sec) # “004”课程没有同学，改成查询1号课程看一下效果 # mysql> select student_id from score where course_id=1 and num<60 order by num desc; # +------------+ # | student_id | # +------------+ # | 1 | # | 6 | # | 7 | # | 8 | # | 2 | # +------------+ # 5 rows in set (0.00 sec) ''' 25、删除“002”同学的“001”课程的成绩； ''' # mysql> delete from score where course_id = 1 and student_id = 2; # Query OK, 1 row affected (0.08 sec)
浙公网安备 33010602011771号