MYSQL-在图书表和学生表基础上进行子查询

USE dbstudent2;-- 先使用学生表
-- 1、基础训练
-- （1）计算“高等数学”课程的平均分。
SELECT AVG(score) FROM score WHERE courseid=(SELECT courseid FROM course WHERE coursename='高等数学'); 

-- （2）列出系别号为“2”所有男生学号、姓名、班号。（使用IN关键字实现）
SELECT student.studentid,studentname,classid FROM student WHERE classid IN (SELECT classid FROM class WHERE departid='2')AND sex='男'; 

-- （3）统计成绩大于任何一个“11701”班级学生成绩的学号、姓名。
SELECT studentid AS '学号',studentname AS '姓名' FROM student WHERE studentid IN (SELECT studentid FROM score WHERE score > 
ANY(SELECT score FROM student INNER JOIN score ON student.studentid=score.studentid WHERE classid='11701'));-- >=any 表示大于任何一个值

-- （4）统计成绩大于所有“11701”班级学生成绩的学号、姓名。
SELECT studentid AS '学号',studentname AS '姓名' FROM student WHERE studentid IN (SELECT studentid FROM score WHERE score >
ALL (SELECT score FROM student INNER JOIN score ON student.studentid=score.studentid WHERE classid='11701')); -- all 表示所有 

-- 查询没有课程成绩的学生姓名、班号
SELECT studentname AS '学生姓名',classid AS '班号' FROM student WHERE NOT EXISTS(SELECT * FROM score 
WHERE student.studentid=score.studentid);

-- （6）列出所有课程的课程名称、平均分数。
SELECT coursename,AVG(score) FROM course LEFT JOIN score ON course.courseid=score.courseid GROUP BY course.courseid;

-- 2、提升训练
USE dblibrary; -- 先使用图书表

-- （1）查询借阅了图书的读者姓名。
SELECT readername FROM reader WHERE readerid IN(SELECT DISTINCT readerid FROM record);

-- （2）查询没有借阅过图书的读者姓名。
SELECT readername FROM reader WHERE readerid NOT IN(SELECT DISTINCT readerid FROM record);

-- （3）查询借阅过《不抱怨的世界》的读者姓名。
SELECT readername FROM reader WHERE readerid IN(SELECT readerid FROM record WHERE bookid IN
 (SELECT bookid FROM book WHERE title='不抱怨的世界')); 

-- （4）列出其他出版社中图书价格高于任何出版社为“陕西师范大学出版社”的图书价格的图书编号、名称和价格。
SELECT bookid AS '图书编号',title AS '图书名称',price AS '图书价格' FROM book WHERE price >ALL 
(SELECT price FROM book WHERE press='陕西师范大学出版社');

-- （5）列出其他出版社中图书价格高于出版社为“陕西师范大学出版社”所有图书价格的图书编号、名称和价格。
SELECT bookid AS '图书编号',title AS '图书名称',price AS '图书价格' FROM book WHERE price >ANY 
(SELECT price FROM book WHERE press='陕西师范大学出版社');

-- （6）UNION关键字，完成查询：查询所有出版社是“陕西师范大学出版社”或者价格超过25的图书编号、图书名称、价格和出版社。 
SELECT bookid AS '图书编号',title AS '图书名称',price AS '图书价格',press AS '出版社' FROM book WHERE press='陕西师范大学出版社' OR price >ANY 
(SELECT price FROM book WHERE price>'25') ORDER BY price ASC;

-- （6.1）完成查询：查询所有出版社是“陕西师范大学出版社”或者价格超过25的图书编号、图书名称、价格和出版社。 
SELECT bookid AS '图书编号',title AS '图书名称',price AS '图书价格',press AS '出版社' FROM book WHERE press='陕西师范大学出版社' UNION
(SELECT bookid AS '图书编号',title AS '图书名称',price AS '图书价格',press AS '出版社' FROM book WHERE price >ANY 
(SELECT price FROM book WHERE price>'25'));

-- （7）使用UNION关键字，完成查询：列出读者编号为3872-3423-001和3872-3423-006的借阅记录（读者姓名，图书名称），读者编号为3872-3423-001的记录在前，读者编号为3872-3423-006的记录在后。
SELECT reader.readername AS '读者姓名',book.title AS '图书名称' FROM reader JOIN record ON reader.readerid=record.readerid
INNER JOIN book ON record.bookid=book.bookid WHERE reader.readerid='3872-3423-001'; 
 UNION ALL(SELECT reader.readername AS '读者姓名',book.title AS '图书名称' FROM reader JOIN record ON reader.readerid=record.readerid
 INNER JOIN book ON record.bookid=book.bookid WHERE reader.readerid='3872-3423-006');