MySQL||数据库查询操作（四）相关子查询

墨蓝 2021-11-22 14:01 320阅读 0赞

## 建立一张范例表 ##

为了方便执行操作，我们需要建立一个范例表进行操作，如我们建立一张员工表，表中包含员工的编号，姓名，职务，入职时间，当月收入（美金），当月奖金，系属部门等。

-- 删除数据表  
    DROP TABLE IF EXISTS emp ;  
    DROP TABLE IF EXISTS dept;    
    DROP TABLE IF EXISTS salgrade;  
      
    -- 创建数据表
    -- 员工表
    CREATE TABLE dept (  
        deptno  INT PRIMARY KEY,  -- 部门编号
        dname   VARCHAR(14) ,  -- 部门名称
        loc VARCHAR(13)   -- 部门地址
    ) ;  
    -- 员工表
    CREATE TABLE emp (  
        empno   INT PRIMARY KEY,  -- 员工编号
        ename   VARCHAR(10),  -- 员工姓名
        job VARCHAR(9), -- 员工职务
        mgr INT, -- 经理的员工编号
        hiredate DATE,  -- 入职日期
        sal DOUBLE,  -- 员工收入
        comm DOUBLE,  -- 奖金
        deptno  INT -- 部门表外键，代表当前员工属于哪个部门   
    );  
    alter table emp add foreign key (deptno) references dept (deptno); 
    -- 工资等级
    CREATE TABLE salgrade (   
        grade INT,   -- 工资等级
        losal DOUBLE,  -- 当前等级最低收入
        hisal DOUBLE   -- 当前等级最高收入
    );  
      
    -- 插入测试数据 —— dept  
    INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');  
    INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
    INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
    INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');  
      
    -- 插入测试数据 —— emp  
    INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,str_to_date('17-12-1980','%d-%m-%Y'),800,NULL,20);  
    INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30);  
    INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30);  
    INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20);  
    INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);  
    INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,str_to_date('1-5-1981','%d-%m-%Y'),2850,NULL,30);  
    INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,str_to_date('9-6-1981','%d-%m-%Y'),2450,NULL,10);  
    INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,str_to_date('19-04-1987','%d-%m-%Y'),3000,NULL,20);  
    INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10);  
    INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30);  
    INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,str_to_date('23-05-1987','%d-%m-%Y'),1100,NULL,20);  
    INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,str_to_date('3-12-1981','%d-%m-%Y'),950,NULL,30);  
    INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20);  
    INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,str_to_date('23-1-1982','%d-%m-%Y'),1300,NULL,10);  
      
    -- 插入测试数据 —— salgrade  
    INSERT INTO salgrade VALUES (1,700,1200);  
    INSERT INTO salgrade VALUES (2,1201,1400);  
    INSERT INTO salgrade VALUES (3,1401,2000);  
    INSERT INTO salgrade VALUES (4,2001,3000);  
    INSERT INTO salgrade VALUES (5,3001,9999);  
    
    -- 事务提交  
    COMMIT;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70][]

## 子查询： ##

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 [MySQL][] 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

*  子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。
 *  子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。
 *  子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

### 常用的运算符 ###

**IN子查询**

结合关键字 IN 所使用的子查询主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为：

<表达式> [NOT] IN <子查询>

语法说明：

*  `<表达式>`：用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
 *  `<子查询>`：用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求，可以使用 SELECT 语句实现子查询的多层嵌套。

**比较运算符子查询**

比较运算符所使用的子查询主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为：

<表达式> {= | < | > | >= | <= | <=> | < > | != }
    { ALL | SOME | ANY} <子查询>

语法说明

*  `<子查询>`：用于指定子查询。
 *  `<表达式>`：用于指定要进行比较的表达式。
 *  `ALL`、`SOME` 和 `ANY`：可选项。用于指定对比较运算的限制。其中，关键字 ALL 用于指定表达式需要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，会返回 TRUE，否则返回 FALSE；关键字 SOME 和 ANY 是同义词，表示表达式只要与子查询结果集中的某个值满足比较关系，就返回 TRUE，否则返回 FALSE。

**EXIST子查询**

关键字 EXIST 所使用的子查询主要用于判断子查询的结果集是否为空。其语法格式为：

EXIST <子查询>

若子查询的结果集不为空，则返回 TRUE；否则返回 FALSE。

-- 查找至少有一个员工的经理 相关子查询
    select * from emp e where exists(select * from emp e1 where e1.mgr = e.empno);
    -- 查找至少有三个员工的经理 相关子查询
    select * from emp e2 where (select * from emp e where 3 <= (select count(*) from emp e1 where e1.mgr = e.empno);
    -- 查找至少有三个员工的经理的员工 相关子查询
    select t.empno 员工编号,t.ename 员工姓名,e3.ename 经理姓名 from emp e3 join (select * from emp e2 where e2.mgr in (select e.empno from emp e where 3 <= (select count(*) rom emp e1 where e1.mgr = e.empno)))t on t.mgr = e3.empno;

**操作示例：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70]: /images/20211122/656267439f43405ab74747fda0125557.png
[MySQL]: http://c.biancheng.net/mysql/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 1]: /images/20211122/ff50e17fb19f41f48461d3c4fe9d9693.png