MySQL——多表查询

准备数据库

# 创建部门表
        CREATE TABLE dept(
            id INT PRIMARY KEY AUTO_INCREMENT,
            NAME VARCHAR(20)
        );
        INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
        # 创建员工表
        CREATE TABLE emp (
            id INT PRIMARY KEY AUTO_INCREMENT,
            NAME VARCHAR(10),
            gender CHAR(1), -- 性别
            salary DOUBLE, -- 工资
            join_date DATE, -- 入职日期
            dept_id INT,
            FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
        );
        INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('啊一','男',7200,'2021-02-01',1);
        INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('啊二','男',3600,'2021-01-02',2);
        INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('张三','男',9000,'2019-03-01',2);
        INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('李四','女',5000,'2016-01-01',3);
        INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('王五','女',4500,'2011-03-14',1);

dept（部门）表

emp（员工）表

1、内连接

• 用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

1.隐式内连接

1. 隐式内联查询：条件是左边和右表的交集部分

2. 语法

   SELECT 字段名 FROM 左表, 右表 WHERE 条件

3. 示例：

   -- 查询所有员工信息和对应的部门信息
   SELECT * FROM emp e,dept d WHERE  e.`dept_id`=d.`id`;
   

 -- 查询员工表的名称，性别。部门表的名称
 SELECT e.`NAME`,e.`gender`,d.`NAME` FROM  emp e,dept d  WHERE e.`dept_id`=d.`id`;

     -- 查询张三的信息，显示员工 id，姓名，性别，工资和所在的部门名
    SELECT e.`dept_id`,e.`NAME`,e.`gender`, e.`salary`,d.`NAME`FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND e.`NAME`='张三';

2.显示内连接

1. 语法

   SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

2. 演示

     -- 查询员工表的名称，性别。部门表的名称
           SELECT e.`NAME`,e.`gender`,d.`NAME` FROM emp e JOIN dept d ON e.`dept_id`=d.`id`;
   

    -- 查询张三的信息，显示员工 id，姓名，性别，工资和所在的部门名
 SELECT e.`dept_id`,e.`NAME`,e.`gender`, e.`salary`,d.`NAME` FROM emp e JOIN dept d ON e.`NAME`='张三' WHERE e.`dept_id`=d.`id`;   

3.总结

1. 隐式链接采用的条件是WHERE，多个Where时用AND隔开，相容易编写和容易理解
2. 显示链接采用的时JOIN ON方式，ON后面附加条件，当有多个条件时，可以增加WHERE
3. 隐式链接对于少数表的链接查询速度快，对于表的数量过多时，采用显示连接速度快。
4. 内链接的查询步骤
   1. 确定查询哪些表
   2. 确定表连接的条件
   3. 确定查询的条件
   4. 确定查询的字段

2、外连接、

注意：左右外链接查看的是左表的全部信息或右表的全部信息，与两表之间出现再左边或者右边的顺序无关。例如

要emp表再左则，dept表在右侧。 Select emp，dept

要dept表在左侧，emp表再右则，Select dept，emp

1.左外链接

1. 左外连接：用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL ，可以理解在内连接的基础上保证左表的数据全部显示

2. 语法(OUTER可以省略)

   SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

3. 演示

     -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
                SELECT e.*,d.`NAME`FROM emp e LEFT JOIN dept d ON e.`dept_id`=d.`id`;
   

2.右外链接

1. 右外链接： 用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL ，可以理解在内连接的基础上保证左表的数据全部显示

2. 语法(OUTER可以省略)

   SELECT 字段名 FROM 左表 RIGHT [OUTER ] JOIN 右表 ON 条件

3. 演示

             -- 查看所有部门信息和部门中员工的名字
             SELECT d.*,e.`NAME` FROM emp e RIGHT JOIN dept d ON e.`dept_id`=d.`id`;
   

3、子查询

1.概念

• 查询中嵌套查询，称嵌套查询为子查询

  1. 一个查询的结果做为另一个查询的条件
  2. 有查询的嵌套，内部的查询称为子查询
  3. 子查询要使用括号

• 示例

 -- 查询工资最高的员工信息
         SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

2.子查询的三种情况

1. 查询结果是单行单列
2. 查询结果是多行单列
3. 查询结果是多行多列

3.查询结果是单行单列

1. 也就是查询结果是一个值得时候，在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、 = 等

2. 语法：

   SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

3. 示例：

          -- 查询员工得最低工资
          SELECT MAX(salary) FROM emp
          # 查询工资最高的员工信息
            SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
   

          -- 查询平均工资
          SELECT AVG(salary) FROM emp;
          #查询工资小于平均工资的员工有哪些？ 
          SELECT * FROM emp WHERE emp.`salary`<( SELECT AVG(salary) FROM emp);

4.查询结果是多行单列

1. 子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

2. 语法

   SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

   注意：不能使用=。使用=会报错，因为=一般只是一个值

3. 示例

       -- 先查询大于6500的员工所在的部门id 
             SELECT dept_id FROM emp WHERE salary > 6500; 
             -- 再查询在这些部门id中部门的名字  Subquery returns more than 1 row 
             SELECT dept.`NAME` FROM dept WHERE id = (SELECT dept_id FROM emp WHERE salary > 6500); 
             
             SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 6500);
   

   注意：可见，使用=后市报错的，因为使用=只能是一个值，查询员工工资大于6500结果可能有多种，不能使用=

5.查询结果是多行多列

1. 子查询结果只要是多列，肯定在 FROM 后面作为子句

2. 语法

   SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件

3. 演示

             -- 查询出2021年以后入职的员工信息，包括部门名称 
            SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date >='2021-1-1') e WHERE d.`id`= e.dept_id ; 
   

4. 理解：

   • 要求的是查询2021年以后入职的，那么查询所有2021入职的员工组成一张表。
   • 现在就是有两张表。2021入职员工表和部门表。使用隐式内连接查询即可

5. 还可以直接使用隐式内连接

      SELECT * FROM dept d,emp e WHERE d.`id`=e.`dept_id` AND e.`join_date`>'2021-1-1'; 
   

6. 也可以直接使用显示内连接

   select * from emp  join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';