MySQL的多表关联查询_联表查询

一、多表关联查询

多表关联查询是使用一条SQL语句，将关联的多张表的数据查询出来。

1.1 交叉查询

交叉查询就是将多张表的数据没有条件地连接在一起进行展示。

1.1.1 语法

使用交叉查询类别和商品

-- 目标：查询所有分类，以及每个分类下的所有商品信息
SELECT tc.*,tp.* FROM t_category tc,t_product tp;

SELECT * FROM t_category,t_product;
-- 这个语句查询出来是一个笛卡尔积，里面有很多错误的数据，所以不用，这种查询叫做交叉查询
1
2
3
4
5

通过查询结果可以看到，交叉查询其实是一种错误的做法，在查询到的结果集中有大量的错误数据，称交叉查询到的结果集是笛卡尔积。

1.2 内连接查询

通常要查询的多个表之间都存在关联关系，通过关联关系（主外键关系）去除笛卡尔积。这种通过条件过滤去除笛卡尔积的查询，称之为连接查询。

连接查询又分为内连接查询和外连接查询。

1.2.1 隐式内连接

-- 1. 内连接查询：满足连接条件的数据才能查询出来，不满足连接条件的数据无法查询出来
-- 1.1 隐式内连接查询：
-- select 要查询的字段 from 主表，从表 where 从表的外键 = 主表的主键
SELECT tc.*,tp.* FROM t_category tc,t_product tp WHERE tp.cno = tc.cid;

1
2
3
4
5

1.2.2 显式内连接

-- 1.2 显式内连接查询：
-- select 要查询的字段 from 主表 [inner] join 从表 on 从表的外键 = 主表的主键
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid;
1
2
3

-- 查询手机数码分类下的所有商品的信息以及分类信息
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname='手机数码';

1
2
3

1.2.3 内连接查询的特点

主表和从表的数据都是满足连接条件则能够查询出来，不满足连接条件则不会查询出来。

1.3 外连接查询

如果要保证查询出某张表的全部数据情况下进行连接查询，急需要使用外连接查询。

外连接查询分为左外连接和右外连接。

1.3.1 左外连接查询

以join左边的表为主表，展示主表的所有数据，根据条件查询连接右边表的数据，若满足条件则展示，若不满足则以null显示。

可以理解为：在内连接的基础上保证左边表的数据全部显示。

-- 2. 外连接查询:会查询出主表的所有数据，从表的数据满足条件则能查询出来，不满足则查询不出来
-- 2.1 左外连接查询：以join左边的表作为主表，能查询出左边的表的所有数据
SELECT * FROM t_product tp LEFT OUTER JOIN t_category tc ON tp.cno = tc.cid;
1
2
3

1.3.2 右外连接查询

以join右边的表为主表，展示右边表的所有数据，根据条件查询join左边表的数据，若满足则展示，若不满足则以null显示。

可以理解为：在内连接的基础上保证右边表的数据全部显示。

-- 2.2 右外连接查询：以join右边的表作为主表，能查询出右边的表的所有数据
SELECT * FROM t_product tp RIGHT OUTER JOIN t_category tc ON tp.cno = tc.cid;
1
2

1.4 union联合查询

联合查询并不是多表连接查询的一种方式。

联合查询是将多条查询语句的查询结果合并成一个结果并去掉重复数据。

全外连接查询的意思就是将左表和右表的数据都查询出来，然后按照连接条件连接。

-- 全外连接，使用union联合查询做全外连接
-- union是将多个select语句查询到的结果进行合并，合并成一个结果，要求多个select语句查询到的结果的格式是一样的
SELECT * FROM t_product tp LEFT OUTER JOIN t_category tc ON tp.cno = tc.cid
UNION
SELECT * FROM t_product tp RIGHT OUTER JOIN t_category tc ON tp.cno = tc.cid
1
2
3
4
5

1.5 自连接查询

自连接查询是一种特殊的多表连接查询，因为两个关联查询的表是同一张表，通过取别名的方式来虚拟成两张表，然后进行两张表的连接查询。

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2) -- 工资
);
-- 添加员工
INSERT INTO emp(id,ename,mgr,joindate,salary) VALUES 
(1001,'孙悟空',1004,'2000-12-17','8000.00'),
(1002,'卢俊义',1006,'2001-02-20','16000.00'),
(1003,'林冲',1006,'2001-02-22','12500.00'),
(1004,'唐僧',1009,'2001-04-02','29750.00'),
(1005,'李逵',1006,'2001-09-28','12500.00'),
(1006,'宋江',1009,'2001-05-01','28500.00'),
(1007,'刘备',1009,'2001-09-01','24500.00'),
(1008,'猪八戒',1004,'2007-04-19','30000.00'),
(1009,'罗贯中',NULL,'2001-11-17','50000.00'),
(1010,'吴用',1006,'2001-09-08','15000.00'),
(1011,'沙僧',1004,'2007-05-23','11000.00'),
(1012,'李逵',1006,'2001-12-03','9500.00'),
(1013,'小白龙',1004,'2001-12-03','30000.00'),
(1014,'关羽',1007,'2002-01-23','13000.00');


-- 自连接查询：表中的一个字段作为外键指向本表的主键
SELECT employee.*,manager.ename FROM emp employee,emp manager WHERE employee.mgr=manager.id AND employee.ename='孙悟空'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

二、子查询

如果一个查询语句嵌套在另一个查询语句里面，那么这个查询语句就称之为子查询。

根据位置不同，可以分为where型、from型、exists型。

2.1 where型

• 子查询是单值结果，那么可以对其使用（=，>等比较运算符）
• 子查询是多值结果，那么可对其使用（[not] in （子查询结果），或>all（子查询结果），或>=all（子查询结果），<all（子查询结果），<=all（子查询结果），或>any（子查询结果），或>=any（子查询结果），<any（子查询结果），<=any（子查询结果））

-- where型子查询，是将子查询语句放在where后面
-- 需求：查询emp表中的薪资最高的那个员工的信息
-- where后面的条件中不能使用聚合函数
-- 第一步：查询出最高的薪资
SELECT MAX(salary) FROM emp   -- 50000

-- 第二步：根据上一步查询出来的最高薪资，查询具体的员工信息
SELECT * FROM emp WHERE salary = 50000

-- 两步合成一步
SELECT * FROM emp WHERE salary = (SELECT MIN(salary) FROM emp)

-- 需求：查询出手机数码和食物分类下的所有商品信息
SELECT * FROM t_product WHERE cno IN(SELECT cid FROM t_category WHERE cname IN('手机数码','食物'))

-- 需求：查询出价格最高的商品信息
-- 思路一：匹配最高的价格
SELECT * FROM t_product WHERE price = (SELECT MAX(price) FROM t_product)

-- 思路二：按照价格的降序排列，取第一个
SELECT * FROM t_product ORDER BY price DESC LIMIT 0,1

-- 思路三：查询价格大于所有商品的商品
SELECT * FROM t_product WHERE price>=ALL(SELECT price FROM t_product)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.2 from型

子查询的结果是多行多列的结果，类似于一张表格。

必须给子查询取别名，即临时表名，表的别名不要加""和空格。

-- from型的子查询，这个针对的是子查询的结果是多行、多列数据
-- 需求：查询每个分类下的分类名、商品总数
-- 思路一：使用连接查询
-- 使用外连接，查询出分类表的所有数据
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT OUTER JOIN t_product tp ON tp.cno=tc.cid GROUP BY tc.cname

-- 思路二：使用子查询
-- 第一步：对t_product根据cno进行分组查询，统计每个分类的商品数量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步：用t_category表去连接第一步查询出来的结果，进行连接查询，此时要求查询出所有分类
SELECT tc.cname '商品名称',IFNULL(tn.total,0) '总数量' FROM t_category tc LEFT OUTER JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid
1
2
3
4
5
6
7
8
9
10
11

2.3 exists型

-- exists型子查询
-- 需求：查询那些有商品的分类
-- 连接分类表和商品表进行查询
-- exists是如果子查询语句能够至少查询到一条数据，就返回TRUE，否则就返回FALSE
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid)

1
2
3
4
5
6

学海无涯苦作舟