MySQL 04 高级查询（二）

最新推荐文章于 2024-04-12 14:21:03 发布

众生云海，一念初见

最新推荐文章于 2024-04-12 14:21:03 发布

阅读量4.7k

点赞数 2

分类专栏： MySQL 文章标签：数据库开发数据仓库数据库架构 dba

本文链接：https://blog.csdn.net/qq_24431059/article/details/124393198

版权

MySQL 专栏收录该内容

9 篇文章 8 订阅

订阅专栏

MySQL 04 高级查询（二）

文章目录

MySQL 04 高级查询（二）

一、学习目标

掌握IN子查询的用法
掌握EXISTS子查询
掌握子查询的使用原则和注意事项
能够使用SQL进行综合查询

二、子查询替换表连接

问题：

查询在“2020-01-02”这天做过“尿常规”这项检查的病人名单

使用多种方法实现查询

方法一：采用表连接

执行prescription、patient和checkitem三表JOIN操作，再根据条件筛选病人名单

使用JOIN关键字实现
SELECT patientName FROM patient pa 
INNER JOIN prescription pre ON pa.patientID = pre.patientID 
INNER JOIN checkitem c ON pre.checkItemID = c.checkItemID 
WHERE c.checkItemName = '尿常规' AND pre.examDate = '2020-01-02';
使用WHERE子句实现
SELECT patientName FROM patient pa,prescription pre, checkitem c
WHERE pa.patientID =  pre.patientID AND pre.checkItemID = c.checkItemID 
AND c.checkItemName = '尿常规' AND pre.examDate = '2020-01-02';
方式二：使用子查询实现复核条件的病人信息筛选

实现思路:

查询checkitem表，获得“尿常规”的检查项目编号
根据检查项目编号，在prescription表中查询在“2020-01-02”这一天做过此检查的病人编号
根据病人编号在patient表中查找病人姓名
SELECT patientName FROM patient
WHERE patientID =
 (SELECT patientID FROM prescription
        WHERE checkItemID = 
            (SELECT checkItemID FROM checkitem
                 WHERE checkItemName = '尿常规')  
AND examDate = '2020-01-02');
总结分析：

子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据
表连接更适合于查看多表的数据

三、IN子查询

问题：

如果处理子查询返回多条记录？

分析：

使用IN子查询

语法：

IN子查询的语法
SELECT …… FROM 表名 
WHERE 字段名  IN (子查询);
3.1.子查询返回多个结果示例：
查询在“2020-03-02”做过“血脂、血糖检查”这项检查的病人名单
SELECT patientName FROM patient WHERE patientID =
    (SELECT patientID FROM prescription WHERE checkItemID = 
        (SELECT checkItemID FROM checkitem 
                WHERE checkItemName = '血脂、血糖检查')
AND examDate = '2020-03-02');
运行结果

解决方法：使用IN子查询

3.2.IN子查询示例1

常用IN替换等号运算符（＝）的子查询

IN后面的子查询可以返回多条记录

#in:限制了病人编号的筛选范围，且其后的子查询可返回多条记录
SELECT patientName FROM patient WHERE patientID IN
    (SELECT patientID FROM prescription WHERE checkItemID = 
        (SELECT checkItemID FROM checkitem 
         WHERE checkItemName = '血脂、血糖检查')
AND examDate = '2020-03-02');

3.3.in子查询示例2

问题：

查询在最近一次开出过“血脂、血糖检查”检查的科室看过病的所有病人姓名、性别、出生日期和住址

实现思路：

获得“血脂、血糖检查”这项检查的检查项目编号
根据获得的检查项目编号查询最近一次得到最近一次检查日期
根据获得的项目编号和最近一次检查日期，查询科室编号
根据科室编号查询在这些科室中看过病的所有病人编号
- 注意：获得的科室编号可能为多条记录
根据病人编号查询病人的姓名、性别、出生日期和住址

#1.获得“血脂、血糖检查”这项检查的检查项目编号
SELECT checkItemID FROM checkitem WHERE checkItemName = '血脂、血糖检查';

#2.根据获得的检查项目编号查询最近一次得到最近一次检查日期
SELECT MAX(examDate) FROM prescription WHERE checkItemID=(
SELECT checkItemID FROM checkitem WHERE checkItemName = '血脂、血糖检查');

#3.根据获得的项目编号和最近一次检查日期，查询科室编号
SELECT depID FROM prescription 
WHERE examDate=(
SELECT MAX(examDate) FROM prescription WHERE checkItemID=(
 SELECT checkItemID FROM checkitem WHERE checkItemName = '血脂、血糖检查'))
  AND checkItemID = (
   SELECT checkItemID FROM checkitem WHERE checkItemName = '血脂、血糖检查');

  #4.根据科室编号查询在这些科室中看过病的所有病人编号
SELECT patientID FROM prescription 
WHERE depID IN (    
 SELECT depID FROM prescription 
   WHERE examDate = (
      SELECT MAX(examDate) FROM prescription 
      WHERE checkItemID = (
        SELECT checkItemID FROM checkitem 
        WHERE checkItemName = '血脂、血糖检查'  
       )  
    )
    AND checkItemID = (
      SELECT checkItemID FROM checkitem 
      WHERE checkItemName = '血脂、血糖检查'
    )
   )
   
#5.根据病人编号查询病人的姓名、性别、出生日期和住址
SELECT patientName, gender, birthDate, address FROM patient 
WHERE patientID IN (
SELECT patientID FROM prescription 
WHERE depID IN (    
   SELECT depID FROM prescription 
   WHERE examDate = (
      SELECT MAX(examDate) FROM prescription 
      WHERE checkItemID = (
        SELECT checkItemID FROM checkitem 
        WHERE checkItemName = '血脂、血糖检查'  
       )  
    )
    AND checkItemID = (
      SELECT checkItemID FROM checkitem 
      WHERE checkItemName = '血脂、血糖检查'
    )
   )
   );

四、NOT IN 子查询

问题：

查询在最近一次开出过“血脂、血糖检查”检查的科室以外其他科室看过病的所有病人姓名、性别、出生日期和住址

分析：

实现思路：

获得“血脂、血糖检查”这项检查的检查项目编号
根据获得的检查项目编号查询最近一次得到最近一次检查日期
根据获得的项目编号和最近一次检查日期，查询科室编号
根据科室编号查询在除这些科室外的其他科室看过病的所有病人编号
根据病人编号查询病人的姓名、性别、出生日期和住址
SELECT patientName, gender, birthDate, address FROM patient 
WHERE patientID IN (
#4.查询在其他科室看过病的病人编号
SELECT patientID FROM prescription WHERE depID NOT IN (
   #3.查询科室编号
   SELECT depID FROM prescription WHERE examDate=(
      #2.根据检查项目编号查询得到最近一次检查日期
      SELECT MAX(examDate) FROM prescription 
      WHERE checkItemID=(
        #1.获得“血脂、血糖检查”检查项目编号
        SELECT checkItemID FROM checkitem 
        WHERE checkItemName = '血脂、血糖检查' 
      )
    )
    AND checkItemID = (
      #1.获得“血脂、血糖检查”检查项目编号
      SELECT checkItemID FROM checkitem 
      WHERE checkItemName = '血脂、血糖检查' 
    )
   )
   );
  

五、EXISTS子查询

问题：
如何用SQL语句检测log表是否已经创建？
DROP TABLE IF EXISTS log;
CREATE TABLE IF NOT EXISTS log (
    … … #省略建表语句
);
EXISTS关键字是否还有其他用法？

EXISTS子查询
语法：
SELECT …… FROM 表名 WHERE  EXISTS (子查询);
子查询有返回结果： EXISTS子查询结果为TRUE
子查询无返回结果： EXISTS子查询结果为FALSE, 外层查询不执行

示例：

需求：

查找是否有病人做过“血脂、血糖检查”
如果有，请显示做过此项检查的病人的姓名、性别、年龄、检查日期和检查结果

分析：

使用EXISTS子查询查找是否有病人做过“血脂、血糖检查”
如果有病人做过“血脂、血糖检查”，从patient表和prescription表中查询做过此项检查的病人的姓名、性别、年龄、检查日期和检查结果
使用EXISTS子查询可以检测是否存在符合查询条件的记录如果存在，再根据业务逻辑查询符合要求的相应记录
#查询病人信息和检查信息，病人年龄根据出生日期计算
SELECT 
	patientName,
	gender,
	FLOOR(DATEDIFF(CURDATE(),birthDate)/365) AS age 
FROM patient
#使用EXISTS子句判断是否有做过“血脂、血糖检查”的病人
WHERE EXISTS(
 SELECT patientID FROM prescription 
 WHERE checkItemID = (
		SELECT checkItemID FROM checkitem 
		WHERE checkItemName='血脂、血糖检查') 
 AND patient.patientID = prescription.patientID);
和IN子查询一样，EXISTS子查询也允许添加NOT关键字实现相反的操作
NOT EXISTS表示结果集中不存在记录是返回true，否则返回false！

六、NOT EXISTS子查询

问题：

查询病人“李思雨”是否做过“凝血五项”检查
如果没做过，则插入一条记录
以“内科”的名义在今天给她开一个“凝血五项”的检查

分析：

可使用NOT EXISTS子查询检测“李思雨”是否做过“凝血五项”检查
如果NOT EXIST子查询的执行结果返回true，则执行数据插入操作
INSERT INTO prescription(patientID,depID,examDate,checkItemID)
SELECT patientID, 3 as depID, CURDATE() as examDate, 4 as checkItemID 
FROM patient 
WHERE NOT EXISTS (SELECT patientID FROM prescription 
                   WHERE checkItemID = (
	            SELECT checkItemID FROM checkitem 
		     WHERE checkItemName='凝血五项') 
	    AND patient.patientID = prescription.patientID) 
AND patientName='李思雨';

七、子查询小结

什么是子查询？
当一个查询是另一个查询的条件时，称之为子查询

子查询可以出现在SQL语句的哪些位置？
任何允许使用表达式的地方都可以使用子查询
嵌套在父查询SELECT语句的子查询，可包括
SELECT子句
FROM子句
WHERE子句
GROUP BY子句
HAVING子句

八、子查询注意事项

通常，将子查询放在比较条件的右边以增加可读性
子查询可以返回单行或多行数据，此时要选择合适的关键字
子查询的返回是单行数据时，比较条件中可以使用比较运算符
子查询的返回是多行数据时，比较条件中需要使用IN或NOT IN关键字
如果判断子查询是否有数据返回时，需要使用EXISTS或NOT EXISTS关键字

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