MySQL——多表查询

2021/5/4 19:25:30

本文主要是介绍MySQL——多表查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

文章目录

        • 1、内连接
          • 1.隐式内连接
          • 2.显示内连接
          • 3.总结
        • 2、外连接、
          • 1.左外链接
          • 2.右外链接
        • 3、子查询
          • 1.概念
          • 2.子查询的三种情况
          • 3.查询结果是单行单列
          • 4.查询结果是多行单列
          • 5.查询结果是多行多列

在这里插入图片描述

准备数据库

# 创建部门表
        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 ; 
    

在这里插入图片描述

  1. 理解:

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

       SELECT * FROM dept d,emp e WHERE d.`id`=e.`dept_id` AND e.`join_date`>'2021-1-1'; 
    
  3. 也可以直接使用显示内连接

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


这篇关于MySQL——多表查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程