MySQL多表查询

目录

等值连接

单表等值连接

表别名

多表等值连接

自然连接

USING

ON

USING和ON的区别

自连接

外连接

1.左外连接 LEFT OUTER JOIN

2.右外连接 RIGHT OUTER JOIN

子查询

单行子查询

多行子查询

相关子查询 EXISTS

---

等值连接

单表等值连接

SELECT 表名1.列名1,表名2.列名2 
FROM 表名1,表名2 
WHERE 表名1.列名3 = 表名2.列名4；

从表1和表2中 查询 表1中列名3与表2中列名4中 内容相同的数据

表别名

表别名直接写在表名后即可

SELECT t1.列1,t2.列2
FROM 表1 t1,表2 t2
WHERE t1.列3 = t2.列4

使用表别名时，表别名的范围只在该条SQL语句中生效

多表等值连接

如果连接的表超过两张，则需要使用 AND 来组合多个等值条件 

SELECT t1.列1,t2.列2,t3.列3
FROM 表1 t1,表2 t2,表三 t3
WHERE t1.列4 = t2.列5 AND t1.列6 = t3.列7

等值连接的关键在于找到表与表之间的等值条件，通过构建一个信息更全面的表，以达到多表查询的目的。

自然连接

自然连接是特殊的等值连接：

• 等值连接用于连接的字段值相同即可
• 自然连接用于连接的字段必须同名且同类型

自然连接使用关键字 NATURAL JOIN

SELECT t1.列1,t2.列2
FROM 表1 t1
NATURAL JOIN 表2 t2

解析引擎会自动探测两个表中相同的字段并设置等值条件。这样的字段可以不止一个，有多少个这样的字段就有多少个等值条件。

USING

当你想要基于两个表中具有相同名称的列进行连接时, USING 关键字可以提供一个更简洁的语法。使用USING时，你不需要在ON子句中明确指定两个列名，因为 SQL 会自动查找并匹配两个表中名称相同的列。

SELECT t1.*,t2.*
FROM 表1 t1
JOIN 表2 t2
USING(字段)

注意：

• using里面的字段不能加表名作为前缀，该字段是一个连接字段，不再属于某张单独的表。
• 连接的表中必须有相同字段才能使用using

ON

ON关键字主要用于JOIN操作中指定两个表之间的连接条件,JOIN操作是SQL中用于根据两个或多个表之间的共同字段来合并这些表中的数据的一种方式。

SELECT t1.*,t2.*
FROM 表1 t1
JOIN 表2 t2
ON(t1.字段 = t2.字段)

注意：

• 不会消除重复列，因为ON中的等值条件无需列相同
• 连接的字段名可以不同

USING和ON的区别

1. USING进行连接时，结果中用于链接的列不会重复出现，ON的结果在不进行干预的情况下会出现两次
2. USING进行连接时，两张表中必须有相同的字段，ON时可以不出现

自连接

一张表与自己进行连接。将一张表视为两张或多张表进行连接，通过别名区分

员工表employee

id	name	manager_id
1	领导	null
2	员工	1

SELECT e1.name AS employee_name, e2.name AS manager_name  
FROM employee e1,employee e2 
ON e1.manager_id = e2.id;

外连接

员工表 employees

id	name	department_id
1	a	1
2	b	2

部门表 departments

id	name
1	x部门
2	y部门

1.左外连接 LEFT OUTER JOIN

左外连接返回左表（FROM子句中指定的第一个表）的所有行，以及右表中与左表匹配的行。如果左表中的某行在右表中没有匹配行，则结果集中右表的部分将包含NULL值。

SELECT employees.name, departments.department_name  
FROM employees  
LEFT OUTER JOIN departments
ON employees.department_id = departments.id;

LEFT OUTER JOIN 可缩写为 LEFT JOIN

在以上代码中，所有的员工表表中的员工姓名会被查询出来，未分配的部门时，员工名字会显示出来，部门id会显示null

2.右外连接 RIGHT OUTER JOIN

右外连接与左外连接相反，它返回右表（JOIN子句中指定的第二个表）的所有行，以及左表中与右表匹配的行。如果右表中的某行在左表中没有匹配行，则结果集中左表的部分将包含NULL值。

SELECT employees.name, departments.name d_name
FROM employees  
RIGHT OUTER JOIN departments
ON employees.department_id = departments.id;

RIGHT OUTER JOIN 可简写为 RIGHT JOIN

在以上代码中，所有的部门表中的部门名字会被查询出来，没有员工时，会显示部门名字，员工名字会显示为null

子查询

SELECT语句中还有SELECT语句，称为子查询或内查询，外面的称为主查询或外查询

• 根据查询结果记录数量分为
  • 单行子查询
  • 多行子查询
• 根据内外查询的相关性分为
  • 不相关子查询
  • 相关子查询

使用子查询的注意条件

1. 子查询要放在小括号中
2. 子查询一般放在条件判断的右侧
3. 对于单行子查询，常用搭配操作符有>、<、<>、<=、>=等
4. 对于多行子查询，常用搭配操作符有IN、ANY、ALL等
5. 不相关子查询，子查询先于主查询，用子查询的结构构造外查询的条件
6. 相关子查询，以EXISTE为代表，是一个内外一一匹配的过程

单行子查询

只返回一行的子查询称为单行子查询

例如：查询工资高于公司平均工资的员工信息

SELECT employee_id, last_name, salary  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);

多行子查询

返回多行结果的子查询称为多行子查询

• IN ：等于多行子查询返回的结果中的任意一个即可
• ANY ： 和多行子查询返回的所有值进行比较即可
• ALL ： 和多行子查询查询返回的所有制进行比较

订单表 orders

order_id	customer_id	order_date
1	101	2024-09-02
2	102	2024-09-02

顾客表 customers

customer_id	customer_name
101	a
102	b

例如：获取所有已经下了订单的顾客信息

SELECT customer_name  
FROM customers  
WHERE customer_id IN (SELECT customer_id FROM orders);

相关子查询 EXISTS

• 不相关子查询：子查询的查询条件不依赖于父查询
• 相关子查询：子查询的相关条件依赖于外层父查询的某个属性值，带EXISTS的子查询就是相关子查询

EXISTS表示存在量词，带有EXISTS的子查询不返回任何记录的数据，只返回：True 或 False

SELECT column_names  
FROM table_name  
WHERE EXISTS  
(SELECT * FROM table_name WHERE condition);