3.高级查询

1. 预习检查
   1. EXISTS关键字的应用场景有哪些？
   2. EXISTS子查询的语法是怎样的？子查询无返回行时外层查询的结果是什么？
   3. SQL语句中SELECT子句、FROM子句中是否可以有子查询？
   4. 只出现在子查询而没出现在父查询中的字段是否可以出现在输出列中？

 

1. 实例-1
   1. 下面的SQL语句有错吗？实现了什么功能？

 SELECT * FROM `student`

 WHERE `studentNo` IN(SELECT * FROM `result`);

有错！ * 应该改为studentNo

1. 1. 上述语句可以用下面的连接替换吗？

     SELECT * FROM `student`  INNER JOIN `result`  --关联方法

 ON `student`.`studentNo`=`result`.`studentNo`;  --条件等值

可以！

1. 实例-2
   1. 下列语句实现了什么功能？   ----查看没有参加考试的学生

SELECT * FROM `student` WHERE `studentNo` NOT IN

 ( SELECT `studentNo` FROM `result `);

 

1. 1. 上述语句可以用下面的连接替换吗？  ---不可以

 SELECT * FROM `student `INNER JOIN `result`

 ON `student`.`studentNo`<>`result`.`studentNo`;

 有的子查询不能用连接替换。这就是典型的例子

 

1. 本章任务
   1. 查询S2学员考试成绩信息
   2. 制作学生成绩单
   3. SQL语句的综合应用

 

1. 本章目标
   1. 掌握EXISTS子查询的用法
   2. 掌握分组查询
   3. 掌握多表连接查询
   4. 应用SQL进行综合查询

 

1. EXISTS子查询3-1

如何用SQL语句检测temp表是否已经创建？

DROP TABLE IF EXISTS temp;

CREATE TABLE temp (

    … … #省略建表语句

) ;

 

1. EXISTS子查询2-1

语法：

EXISTS子查询的语法

SELECT ...FROM 表名 WHERE  EXISTS (子查询);

 

子查询有返回结果： EXISTS子查询结果为TRUE

子查询无返回结果： EXISTS子查询结果为FALSE,

                   外层查询不执行

1. 例题-1：

检查“Logic Java”课程最近一次考试成绩

如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

分析：

采用EXISTS检测是否有人考试成绩达到80分以上

如果有，使用SELECT语句按成绩从高到低排序，显示前5名学员学号和成绩

 

1. 例题-1：

检查“Logic Java”课程最近一次考试成绩

如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分

分析：

可以采用NOT EXISTS检测是否全部未通过考试，即不存在“成绩>=60分”的记录

 

1. 参考语句：

SELECT AVG(studentresult)+5 AS 平均分 FROM result

WHERE NOT EXISTS (

SELECT * FROM `result`  WHERE `subjectNo` = (

   SELECT `subjectNo` FROM `subject` WHERE `subjectName` = 'Logic Java'

  )  AND `examDate` = (

     SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (     

       SELECT `subjectNo` FROM `subject`

       WHERE `subjectName` = 'Logic Java')

  ) AND `studentResult` > 60)

AND `subjectNo` = ( SELECT `subjectNo` FROM `subject`

WHERE `subjectName` = 'Logic Java')

AND `examDate` = (

  SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (     

  SELECT `subjectNo` FROM `subject`

  WHERE `subjectName` = 'Logic Java') );

 

1. 子查询注意事项
   1. 任何允许使用表达式的地方都可以使用子查询
   2. 嵌套在父查询SELECT语句的子查询可包括
      • SELECT子句
      • FROM子句
      • WHERE子句
      • GROUP BY子句
      • HAVING子句

WHERE子句  GROUP BY子句  HAVING子句，这三个是可选子句，根据业务需求决定。

1. 1. 只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

 

1. 学员操作——制作学生成绩单2-1

训练要点

任何允许使用表达式的地方都可以使用子查询

需求说明

为每个学生制作在校期间每门课程的成绩单，要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩

成绩单的数据项

学生姓名

课程所属的年级名称

课程名称

考试日期

考试成绩

 

1. 指导答案

SELECT  `studentName` 姓名,  

   ( SELECT `gradeName` FROM `grade` WHERE `gradeId`=`subject`.`gradeId`  ) AS 课程所属年级 ,

   `subjectName` 课程名称, `examDate` 考试日期, `studentResult` 成绩

FROM  `result` r1

  INNER JOIN `student` ON r1.`studentNo`=`student`.`studentNo`

  INNER JOIN `subject` ON `subject`.`subjectNo`=r1.`subjectNo`

WHERE r1.`examDate` IN (

      SELECT MAX(`examDate`) FROM `result` r2

      WHERE r1.`subjectNo` = r2.`subjectNo`    #主查询和子查询间参数值绑定

      GROUP BY r2.`subjectNo`

)

ORDER BY subject.gradeId;

1. 分组查询用法

掌握GROUP BY子句实现分组查询

SELECT …… FROM  <表名>  

WHERE  ……

GROUP BY ……

1. 分组查询解析

查询每门课程的平均分，并且按照分数由高到低的顺序排列显示

 

SELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩

FROM `result`

GROUP BY `subjectNo`

ORDER BY AVG(`studentResult`);

 

1. 多列分组

分别统计每个年级男、女生人数

SELECT `gradeId` AS 年级编号,`sex` AS 性别,COUNT(*) AS 人数

 FROM `student`

GROUP BY `gradeId`,`sex`

ORDER BY `gradeId`;

 

1. 分组筛选

分组筛选语句:

语法：

SELESELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩

FROM `result`

GROUP BY `subjectNo`

HAVING AVG(`studentResult`) >=60;

CT …… FROM  <表名>

WHERE ……

GROUP BY ……

HAVING……

示例：

SELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩

FROM `result`

GROUP BY `subjectNo`

HAVING AVG(`studentResult`) >=60;

 

1. WHERE与HAVING对比
   1. WHERE子句

用来筛选 FROM 子句中指定的操作所产生的行

1. 1. GROUP BY子句

用来分组 WHERE 子句的输出

1. 1. HAVING子句

用来从分组的结果中筛选行

1. 常用的多表连接查询

内连接(INNER JOIN)

外连接

左外连接   (LEFT JOIN)

右外连接   (RIGHT JOIN)

 

1. 内连接-1

内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行

1. 内连接-2

内连接语句：  语法：

示例：

SELECT `student`.`studentName`,`result`.`subjectNo`,`result`.`studentResult`

FROM `student`,`result`

WHERE  `student`.`studentNo` = `result`.`studentNo`;

等价于：

SELECT S.`studentName`,R.`subjectNo`,R.`studentResult`

FROM `student`  AS S

INNER JOIN `result` AS R ON  (S.`studentNo` = R.`studentNo`);

 

1. 三表内连接

示例：

SELECT S.studentName AS 姓名,SU.subjectName AS 课程,R.studentResult AS 成绩

FROM student AS S

INNER JOIN `result` AS R ON  (S.`studentNo` = R.`studentNo`)

INNER JOIN `subject` AS SU ON (SU.subjectNo=R.subjectNo);

1. 左外连接-1

主表（左表）student中数据逐条匹配表result中的数据

1.匹配，返回到结果集

2.无匹配，NULL值返回到结果集

 

1. 左外连接-2

示例：

SELECT S.studentName,R.subjectNo,R.studentResult

FROM student AS S

LEFT  JOIN result AS R

ON S.studentNo = R.studentNo;

猜一猜：这样写，返回的查询结果是一样的吗？

SELECT S.studentName,R.subjectNo,R.studentResult

FROM result AS R

LEFT JOIN student AS S

ON S.studentNo = R.studentNo;

不一样，主表和从表位置已互换

 

1. 右外连接
   1. 右外连接的原理与左外连接相同
   2. 右表逐条去匹配记录；否则NULL填充

示例：

SELECT 图书编号,图书名称,出版社名称

FROM 图书表

RIGHT JOIN 出版社表

ON 图书表.出版社编号 = 出版社表.出版社编号;

1. SQL 语句综合应用—“我的租房网”数据库设计2-1

1. SQL 语句综合应用—“我的租房网”数据库设计2-2

1. 学员操作——分页显示查询出租房屋信息2-1

指导：

训练要点

使用LIMIT关键字实现查询数据分页显示

使用临时表保存临时的查询结果

需求说明

查询输出第6条～第10条出租房屋信息

1. 学员操作——分页显示查询出租房屋信息2-2

实现思路

使用LIMIT子句实现分页查询

起始位置从0开始，第6条记录位置为5

使用临时表保存查询结果

语法：

 CREATE  TEMPORARY  TABLE 表名（查询语句）;

提示：

临时表只在当前连接可见，连接关闭自动删除，修改临时表数据不影响原表数据

 

1. 学员操作——查询指定客户发布的出租房屋信息

需求说明

查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县

提示：

结果数据来源于出租房屋信息表、客户信息表、区县信息表、街道信息表

使用连接查询和子查询两种方式关联多表数据实现

 

1. 总结