MySQL — 多表查询

一、语法

SELECT 
	字段1,字段2...
FROM 
	表1,表2
WHERE
	条件列表

二、笛卡尔积

概念：

有两个集合，A和B，取这两个集合的所有排列组合情况

要完成多表查询，需要消除无用的笛卡尔积。

三、分类

1. 内连接查询

1. 隐式内连接

   使用where条件消除无用的笛卡尔积

   -- 查询用户的姓名和职业名称
   SELECT 
   	u.username, p.name 
   FROM 
   	`user` AS u, `profession` AS p -- AS 可省略
   WHERE 
   	u.pro_id = p.id;
   
   

   2. 显式内连接

   INNER JOIN 或 JOIN

   • 语法

     SELECT 
     	字段列表
     FROM 
     	表1
     -- INNER JOIN -- INNER可以省略
     JOIN
     	表2
     ...
     JOIN
     	表n
     ON 
     	条件列表;
     

   • 示例

     SELECT 
     	u.username,p.name 
     FROM 
     	`user` AS u -- AS可以省略
     -- INNER JOIN -- INNER可以省略
     JOIN
     	profession AS p 
     ON 
     	u.pro_id = p.id;
     

   ​

   使用内连接时的思维逻辑

   1. 从哪些表中查询数据
   2. 条件是什么
   3. 查询哪些字段

   ​

2. 外连接查询

常用左外连接

1. 左外连接（常用）

   LEFT OUTER JOIN或LEFT JOIN

   查询的是左表的所有数据及其与所有表的交集。以左表为准

   • 语法

   SELECT 
   	字段列表
   FROM 
   	表1
   -- LEFT OUTER JOIN  -- OUTER可省略
   LEFT JOIN 
   	表2
   LEFT JOIN 
   	表n
   ON 
   	条件列表;
   

   • 示例

     SELECT 
     	u.username,p.name 
     FROM 
     	`user` AS u 
     LEFT 
     	JOIN profession AS p 
     ON 
     	u.pro_id = p.id;
     

2. 右外连接

   RIGHT OUTER JOIN或RIGHT JOIN

   与左外连接相反，查询的是右表的所有数据及其与所有表的交集。以右表为准

   语法与左外连接相同

3. 子查询

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

SELECT 
	表1字段列表
FROM 
	表1
WHERE 
	表1字段 运算符 
	(SELECT 
     	表2字段列表 
     FROM 
     	表2
     WHERE
     	表2字段 运算符 
     	(SELECT 
     		表3字段列表 
         FROM 
            表3
        );
    );

1. 结果为单行单列

该结果可以作为查询条件，使用运算符判断

例：

-- 查询表中年龄小于平均值的用户的用户名
SELECT 
	username 
FROM 
	`user` 
WHERE 
	age < (
        SELECT 
        	AVG(IFNULL(age,0)) 
        FROM 
        	`user`);

2. 结果为多行单列

该结果可以作为查询条件，使用运算符IN判断

例：

-- 查询用户表中职业为医生或老师的用户的用户名
SELECT 
	username 
FROM
	`user`
WHERE 
	pro_id IN 
	(SELECT 
     	id 
     FROM 
     	`profession` 
     WHERE 
     	name = '医生' 
     OR 
     	name = '老师');

3. 结果为多行多列

该结果可以作为一张虚拟表，然而是多此一举

一般不用

例：

-- 查询年龄大于20岁的用户的基本信息和职业
SELECT 
	* 
FROM
	profession t1, 
	(SELECT * FROM `user` WHERE age > 20) t2 
WHERE 
	t1.id = t2.pro_id;
	
-- 然而这是脱了裤子放屁，多此一举
-- 我们可以用内连接来查询
SELECT 
	* 
FROM
	`user` t1, 
	profession t2 
WHERE  
	t1.pro_id = t2.id 
AND 
	t1.age > 20;