SQL语句 - 多表查询使用详细介绍

文章目录

多表查询

多表查询简介

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

# 创建部门表CREATETABLE dept (
    did INTPRIMARYKEYAUTO_INCREMENT,
    dname VARCHAR(20));-- 添加部门数据INSERTINTO dept (dNAME)VALUES('研发部'),('市场部'),('财务部'),('销售部');# 创建员工表CREATETABLE emp (
    id INTPRIMARYKEYAUTO_INCREMENT,
    NAME VARCHAR(10),
    gender CHAR(1),-- 性别
    salary DOUBLE,-- 工资
    join_date DATE,-- 入职日期
    dep_id INT,FOREIGNKEY(dep_id)REFERENCES dept (did)-- 外键，关联部门表(部门表的主键));-- 添加员工数据INSERTINTO 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 交集数据:

内连接查询语法:

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

隐式演示如下:

查询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 INNERJOIN 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 表1LEFT[OUTER]JOIN 表2ON 条件;-- 右外连接SELECT 字段列表 FROM 表1RIGHT[OUTER]JOIN 表2ON 条件;

演示如下:

-- 左外连接SELECT*FROM emp LEFTJOIN dept ON emp.dep_id = dept.did;-- 右外连接SELECT*FROM emp RIGHTJOIN dept ON emp.dep_id = dept.did;

多表查询子查询

子查询概念：

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

子查询根据查询结果不同，作用不同：

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

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

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

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

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

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

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

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

多行单列查询语法如下:

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

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

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

多行多列: 作为虚拟表;

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

多行多列语法如下:

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;