SQL语句 - 多表查询使用详细介绍
作者:mmseoamin日期:2023-12-25

文章目录

  • 多表查询
    • 多表查询简介
    • 多表查询内连接
    • 多表查询外连接
    • 多表查询子查询

      多表查询

      多表查询简介

      例如我们有一张员工表和部门表, 员工表有6条数据, 部门表表有4条数据:

      # 创建部门表
      CREATE TABLE dept (
      	did INT PRIMARY KEY AUTO_INCREMENT,
      	dname VARCHAR (20)
      );
      -- 添加部门数据
      INSERT INTO dept (dNAME)
      VALUES ('研发部'), ('市场部'), ('财务部'), ('销售部');
      # 创建员工表
      CREATE TABLE emp (
      	id INT PRIMARY KEY AUTO_INCREMENT,
      	NAME VARCHAR (10),
      	gender CHAR (1),
      	-- 性别
      	salary DOUBLE,
      	-- 工资
      	join_date DATE,
      	-- 入职日期
      	dep_id INT,
      	FOREIGN KEY (dep_id) REFERENCES dept (did) -- 外键,关联部门表(部门表的主键)
      );
      -- 添加员工数据
      INSERT INTO emp (
      	name,
      	gender,
      	salary,
      	join_date,
      	dep_id
      )
      VALUES(
      	'孙悟空',
      	'男',
      	7200,
      	'2013-02-24',
      	1
      ), (
      	'猪八戒',
      	'男',
      	3600,
      	'2010-12-02',
      	2
      ), (
      	'唐僧',
      	'男',
      	9000,
      	'2008-08-08',
      	2
      ), (
      	'白骨精',
      	'女',
      	5000,
      	'2015-10-07',
      	3), (
      	'蜘蛛精',
      	'女',
      	4500,
      	'2011-03-14',
      	1
      ), (
      	'小白龙',
      	'男',
      	2500,
      	'2011-02-14',
      	NULL
      );
      

      使用多表查询, 查询员工表和部门表两张表:

      SELECT * FROM emp, dept;
      

      此时查询到的结果会有24条数据, 为什么会这样呢?

      笛卡尔积:有A, B两个集合, 取A, B集合所有组合情况(4*6=24);

      我们多表查询, 更多的是消除这些无效的数据

      例如上面表中, 我们可以加上emp表中的dep_id和dept表中的did相等的条件, 筛选出有效的数据

      SELECT * FROM emp, dept WHERE emp.dep_id = dept.did;
      

      多表查询:从多张表查询数据, 有下面两种方式:

      连接查询

      内连接:相当于查询A B交集数据

      外连接:

      • 左外连接:相当于查询A表所有数据和交集部分数据
      • 右外连接:相当于查询B表所有数据和交集部分数据

      子查询

      多表查询内连接

      内连接相当于查询 A B 交集数据:

      SQL语句 - 多表查询使用详细介绍,在这里插入图片描述,第1张

      内连接查询语法:

      -- 隐式内连接
      SELECT 字段列表 FROM 表1,表2… WHERE 条件;
      -- 显式内连接
      SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
      

      隐式演示如下:

      查询emp表中的name和gender, dept表中的did

      SELECT emp.name, emp.gender, dept.did FROM emp, dept WHERE emp.dep_id = dept.did;
      

      在多表查询的时候, 我们也是可以给表取别名的

      SELECT t1.name, t1.gender, t2.did FROM emp t1, dept t2 WHERE t1.dep_id = t2.did;
      

      显式内连接:

      外连查询emp.dep_id = dept.did的所有员工信息

      SELECT * FROM emp INNER JOIN dept ON emp.dep_id = dept.did;
      

      INNER是可以省略的

      SELECT * FROM emp JOIN dept ON emp.dep_id = dept.did;
      

      查询emp表中的name和gender, dept表中的did, 同样显式内连接同样是可以取别名的

      SELECT t1.name, t1.gender, t2.did FROM emp t1 JOIN dept t2 ON t1.dep_id = t2.did;
      

      多表查询外连接

      外连接查询语法:

      左外连接:相当于查询A表所有数据和交集部分数据

      右外连接:相当于查询B表所有数据和交集部分数据

      -- 左外连接
      SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
      -- 右外连接
      SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
      

      演示如下:

      -- 左外连接
      SELECT * FROM emp LEFT JOIN dept ON emp.dep_id = dept.did;
      -- 右外连接
      SELECT * FROM emp RIGHT JOIN dept ON emp.dep_id = dept.did;
      

      多表查询子查询

      子查询概念:

      查询中嵌套查询,称嵌套查询为子查询

      子查询根据查询结果不同,作用不同:

      单行单列: 作为条件值,使用 = != > <等进行条件判断;

      指的是子查询语句的查询结果是单行单列的, 例如查询猪八戒工资结果如下:

      SQL语句 - 多表查询使用详细介绍,在这里插入图片描述,第2张

      单行单列子查询语法如下:

      SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
      

      演示: 查询emp表中猪八戒的工资, 然后再查出工资高于猪八戒工资的员工信息;

      SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name = '猪八戒');
      

      多行单列: 作为条件值,使用 in 等关键字进行条件判断;

      指的是子查询的结果是多行单列的, 例如查询出dept表中财务部和市场部的did结果如下:

      SQL语句 - 多表查询使用详细介绍,在这里插入图片描述,第3张

      多行单列查询语法如下:

      SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
      

      演示: 查询财务部和市场部的所有员工信息

      SELECT * FROM emp WHERE dep_id 
      IN (SELECT did FROM dept WHERE dname = '财务部' OR dname = '市场部');
      

      多行多列: 作为虚拟表;

      子查询出来的结果是多行多列: 例如查询入职日期是’2011-11-11’之后的员工信息, 结果如下

      SQL语句 - 多表查询使用详细介绍,在这里插入图片描述,第4张

      多行多列语法如下:

      SELECT 字段列表 FROM (子查询) WHERE 条件;
      

      演示: 查询入职日期是’2011-11-11’之后的员工信息和部门信息

      SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-11-11') t1, dept WHERE t1.dep_id = dept.did;