MySQL 多表查询

多表查询顾名思义就是从多张表中一次性查询出我们需要的数据。

先建表

# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建员工表
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 dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
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;后，我们看到这个

 你肯定会想，这什么玩意，这里就是两个表字段之间的笛卡尔积了，就不详细说了，反正这样是不可以的。

所以我们要 通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据。

select * from emp,dept where emp.dept_id=dept.did;

执行结果如下

 1、内连接查询

-- 隐式内连接 SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

上面的方法就是隐式内连接。

两种方法作用（应该）相同。

2、外连接查询

但我们会发现上面的方法查询不到小白龙这个数据。于是我们可以用外连接查询

语法

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

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

select * from emp left join dept on emp.dept_id=dept.did;

这条sql语句执行结果

 这样就不会有数据遗漏了

3、子查询（查询中嵌套查询）

 比如：我们现在需要查询工资高于猪八戒的员工，现在的查询方式是这样的

select salary from emp where name='猪八戒';

SELECT * from emp where salary>3600;

嵌套后

SELECT * from emp where salary>(select salary from emp where name='猪八戒');

子查询根据查询结果不同，作用不同 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断

子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断

子查询语句结果是多行多列，子查询语句作为虚拟表

-- 查询 '财务部' 或者 '市场部' 所有的员工的部门

SELECT * from emp where dep_id in(SELECT did from dept where dname="财务部" or dname="市场部");

//in的作用是将两条select语句的结果取并集

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

SELECT * from (SELECT * from emp where join_date>"2011-11-11") t1,dept where t1.dep_id=dept.did;

 总结：内连接查询相当于查询两个表的交集部分；

外连接查询相当于查询一个表的全部和两个表的交集部分；

子查询用于查询筛选后的表；

4、自连接查询

 需求：我们需要查询员工和直属领导的姓名

先来看自连接查询的语法

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

方法一：

select a.name,b.name from emp a,emp b where a.managerid=b.id;

方法二：

select a.name ,b.name from emp a left join emp b on a.managerid=b.id;

5、联合查询

语法

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

将薪资大于5000和年龄大于50的员工全部查询出来

 

select * from emp where salary < 5000
union all
select * from emp where age > 50;

如果不想要重复，就去掉all

如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报 错。