MySQL-----多表查询（二）

目录

一.子查询概述：

二：标量子查询：

 三：列子查询：

四：行子查询：

五：表子查询：

六：练习部分：

写在之前：本文承接上文MySQL-----多表查询（一）-CSDN博客

一.子查询概述：

首先引出子查询的概念：子查询指一个查询语句嵌套在另一个查询语句内部的查询，即SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

如下面这种形式： 

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

 子查询分类：

• A. 标量子查询（子查询结果为单个值）
• B. 列子查询(子查询结果为一列)
• C. 行子查询(子查询结果为一行)
• D. 表子查询(子查询结果为多行多列)

  查询位置可分为：

  • A. WHERE之后
  • B. FROM之后
  • C. SELECT之后

    接着，基于上面的概述，相信你对子查询有了一定了解了，首先我们导入要查询的数据，下面在根据子查询的分类一个一个说明：（在查询之前，我们先导入数据（建立表以及表之间的关系））

    -- 创建部门表create table dept(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部门名称')comment '部门表'; INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,'销售部'), (5, '总经办'), (6, '人事部');-- 创建员工表create table emp(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID')comment '员工表';-- 设置外键约束alter table emp add constraint fk_emp_dept_id foreign key (dept_id) referencesdept(id);-- 插入数据INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)VALUES(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),(8, '周芷若', 19, '会计',4800, '2006-06-02', 7,3),(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

    创建好后的表中数据及其对应关系：

     

    二：标量子查询：

    子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。 常用的操作符：= > >=

    查询结果（根据上述例A与例B展示）对比上述员工表与部门表可知，查询无误： 

     三：列子查询：

    子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL：

    案例演示：

    # 列子查询：-- A: 查询“销售部”和“市场部”的的所有员工信息：-- 第一步：查询“销售部”和“市场部”的部门idselect id from dept where name = '销售部' or name = '市场部';-- 第二步：根据部门id，查询员工信息：select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');-- B: 查询比 财务部 所有人工资都高的员工信息:-- 第一步：查询所有 财务部 人员工资：select id from dept where name = '财务部';select salary from emp where dept_id = (select id from dept where name = '财务部');-- 第二步：比 财务部 所有人工资都高的员工信息:select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));  -- C:查询比研发部其中任意一人工资高的员工信息:  -- 第一步：查询研发部所有人工资： select id from dept where name = '研发部';  select salary from emp where dept_id = ( select id from dept where name = '研发部');  -- 第二步：根据部门id查询比研发部任意一人工资都高的员工信息： select * from emp where salary > any( select salary from emp where  dept_id = ( select id from dept where name = '研发部'));

    查询结果（根据上述例A与例B例C展示）：

    四：行子查询：

    子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。 常用的操作符：= 、 、IN 、NOT IN

    案例演示：

     # 行子查询:  -- A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息:  -- 第一步：查询 "张无忌" 的薪资及直属领导： select salary , managerid from emp where name = '张无忌';  -- 第二步：查询与 "张无忌" 的薪资及直属领导相同的员工信息: select * from emp where (salary,managerid) = (select salary , manageridfrom emp where name = '张无忌');

    查询结果：

    五：表子查询：

    子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符：IN

    案例演示：

     #  表子查询:  -- A:查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息-- 第一步：查询 "鹿杖客" , "宋远桥" 的职位和薪资：select job , salary from emp where name = '鹿杖客' or name = '宋远桥';-- 第二步：查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息:select * from emp where (job,salary) in (select job , salary from emp where name = '鹿杖客' or name = '宋远桥');-- B:查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息:-- 第一步： 入职日期是 "2006-01-01" 之后的员工信息：select * from emp where entrydate > '2006-01-01';-- 第二步： 查询这部分员工信息及其对应的部门信息:select e.*,d.* from (select * from emp where entrydate > '2006-01-01')e left join dept d on e.dept_id = d.id;

    查询结果：

    通过上面的学习，我们对多表查询有了一定认识，接下来练习一下加深印象：

    六：练习部分：

    此部分就不给出运行结果了，有需要可以自己尝试运行:

    # 综合练习：-- 1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）：select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;-- 2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）：select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id =d.id where e.age 40 ;-- 5).查询 "研发部" 员工的平均工资:select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';-- 6). 查询低于本部门平均工资的员工信息:select * from emp e2 where e2.salary  
     结语： 写博客不仅仅是为了分享学习经历，同时这也有利于我巩固知识点，总结该知识点，由于作者水平有限，对文章有任何问题的还请指出，接受大家的批评，让我改进。同时也希望读者们不吝啬你们的点赞+收藏+关注，你们的鼓励是我创作的最大动力！