MYSQL多表查询

1-什么是多表查询

指的是在关系型数据库当中同时查询多个表的数据。

2-多表查询的分类

 3-笛卡尔积现象

笛卡尔积指的是在多个集合之间，生成一个包含所有可能的元素组合的集合。

在数据库当中，笛卡尔积通常指的是两个表的组合，结果集包含每个表中所有行的组合

示例

示例：
如果有两个表：

A（包含2行）：{A1, A2}
B（包含3行）：{B1, B2, B3}
笛卡尔积的结果将是：

(A1, B1)
(A1, B2)
(A1, B3)
(A2, B1)
(A2, B2)
(A2, B3)

 如何消除笛卡尔积

1-使用 INNER JOIN：仅返回在两个表中都存在的匹配行。

SELECT *
FROM table1
INNER JOIN table2 ON table1.id = table2.foreign_id;

2-使用 LEFT JOIN：保留左侧表中的所有行，同时返回右侧表中匹配的行。

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.foreign_id;

3-添加 WHERE 子句：进一步过滤结果，确保只返回满足特定条件的行。

通过这样的方法，可以避免产生大量不必要的行，消除笛卡尔积的影响。

 举一个具体的例子

建表

-- 价格 1
create table price( 
   id int primary key auto_increment,
   price double
);
-- 水果 n
create table fruit(
  id int primary key auto_increment,
  name varchar(20) not null,
  price_id int,
  foreign key(price_id) references price(id)
);
insert into price values(1,2.30);
insert into price values(2,3.50);
insert into price values(4,null);

insert into fruit values(1,'苹果',1);
insert into fruit values(2,'橘子',2);
insert into fruit values(3,'香蕉',null);
-- 一种水果有一个价格 一个价格对应多种水果
-- 价格 1 水果 n 水果将价格主键作为外键

然后查询一下两张表中的水果信息，要求显示水果的名称和价格
操作

select * from fruit,price;

查询结果如下图所示

之所以出现这样的结果是因为将水果的每行记录分别和价格的每行记录进行组合。

所以我们添加一下过滤条件 

select * from fruit,price where fruit.price_id=price.id;

得到的结果如下

4-内连接 

1-什么是内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。内连接查询的结果：两表的公共部分。 

假设有两个表：

• Customers（客户表）：

  CustomerID	Name
  1	Alice
  2	Bob
  3	Charlie

• Orders（订单表）：

  OrderID	CustomerID	OrderDate
  101	1	2024-01-01
  102	2	2024-01-02
  103	4	2024-01-03

内连接查询示例：

我们想要查询每个客户及其订单信息，SQL语句可以写成：

SELECT Customers.Name, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

查询结果：

这个查询会返回如下结果：

Name	OrderID	OrderDate
Alice	101	2024-01-01
Bob	102	2024-01-02

内连接特点：

• 只有在两个表中都存在的匹配记录才会出现在结果中。
• 如果某一表中的某个值在另一表中没有对应的匹配，则该记录不会出现在结果集中。

内连接是关系数据库中最常用的连接类型之一，常用于数据分析和报告生成。

 2-隐式内连接

SELECT Customers.Name, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID;

• 在FROM子句中列出多个表（用逗号分隔）。
• 在WHERE子句中指定连接条件，确保只选择匹配的记录。

3-显示内连接

SELECT Customers.Name, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• INNER JOIN 指定进行内连接。
• ON 子句后面定义了连接条件，即 Customers.CustomerID 和 Orders.CustomerID 必须相等。
• 只有在两个表中都有匹配的记录，结果中才会包含这些记录。
• 查询结果

  Name	OrderID	OrderDate
  Alice	101	2024-01-01
  Bob	102	2024-01-02

 显是内连接也可以使用where语句

SELECT Customers.Name, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate >= '2024-01-01';

5- 左外连接

用于从两个表中获取来自左表（从表）中的记录，以及右表（从表）中匹配的记录。如果右表中没有匹配的记录，则结果中仍然会保留左表中的记录，但右表的相应字段会显示NULL。

 格式如下

左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略

select * from 表1 left outer join 表2 on 条件;
把left 关键字之前的表，是定义为左侧。 left关键字之后的表，定义右侧。
查询的内容，以左侧的表为主，如果左侧有数据，右侧没有对应的数据，仍然会把左侧数据进行显示。

举例如下图

左外连接的基本语法如下：

SELECT columns
FROM left_table
LEFT OUTER JOIN right_table ON left_table.common_field = right_table.common_field;

示例

假设我们有两个表，Employees（员工表）和 Departments（部门表）：

• Employees（员工表）：

  EmployeeID	Name	DepartmentID
  1	Alice	101
  2	Bob	102
  3	Charlie	NULL
  4	David	101

• Departments（部门表）：

  DepartmentID	DepartmentName
  101	HR
  102	IT
  103	Finance

使用左外连接的 SQL 查询示例

以下 SQL 查询使用左外连接来获取所有员工及其部门信息：

SELECT Employees.Name, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

解释

• 左表：Employees，所有的员工记录都会被返回。
• 右表：Departments，只有与左表中记录匹配的部门信息会被返回。
• 连接条件：ON 子句指定了连接条件，即 Employees.DepartmentID 必须与 Departments.DepartmentID 相等。

查询结果

运行上述查询后，结果将如下：

Name	DepartmentID	DepartmentName
Alice	101	HR
Bob	102	IT
Charlie	NULL	NULL
David	101	HR

结果分析

• Alice 和 David 的部门是 HR，Bob 的部门是 IT，且都找到了对应的部门名称。
• Charlie 的 DepartmentID 为 NULL，因此在结果中他仍然被列出，但 DepartmentName 列为 NULL，因为没有对应的部门记录。

使用场景

左外连接常用于以下场景：

1. 保留所有主表记录：即使从表中没有匹配的记录，也需要显示主表中的所有记录。
2. 数据汇总：在分析和汇总数据时，可能需要从主表中保留所有信息，并且只从从表中获取相关信息。
3. 缺失数据分析：用于找出主表中的记录与从表中的记录不匹配的情况。

总结

左外连接是 SQL 查询中非常重要的一种类型，它确保你可以从主表中获取所有记录，同时还可以选择性地从相关表中提取数据。这种连接方式在数据分析和报告生成中极为常用。

6-右外连接

就是左外连接反过来

右外连接就是从右表（主表）中获取所有记录，并且包括左表（从表）中匹配的记录。如果左表中没有匹配的记录，则结果中仍然会保留右表的记录，但左表的相应字段会变为NULL。

基本语法

右外连接的基本语法如下：

SELECT columns
FROM left_table
RIGHT OUTER JOIN right_table ON left_table.common_field = right_table.common_field;

示例

假设我们有两个表，Employees（员工表）和 Departments（部门表）：

• Employees（员工表）：

  EmployeeID	Name	DepartmentID
  1	Alice	101
  2	Bob	102
  3	Charlie	NULL
  4	David	101

• Departments（部门表）：

  DepartmentID	DepartmentName
  101	HR
  102	IT
  103	Finance

使用右外连接的 SQL 查询示例

以下 SQL 查询使用右外连接来获取所有部门及其对应的员工信息：

SELECT Employees.Name, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
RIGHT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

解释

• 右表：Departments，所有的部门记录都会被返回。
• 左表：Employees，只有与右表中记录匹配的员工信息会被返回。
• 连接条件：ON 子句指定了连接条件，即 Employees.DepartmentID 必须与 Departments.DepartmentID 相等。

查询结果

运行上述查询后，结果将如下：

Name	DepartmentID	DepartmentName
Alice	101	HR
Bob	102	IT
NULL	NULL	Finance

结果分析

• Alice 和 Bob 有对应的部门，结果中显示了他们的名字和部门。
• Finance 部门没有对应的员工，因此在结果中显示 NULL，表示在左表中没有匹配的记录。

使用场景

右外连接通常用于以下情况：

1. 保留所有主表记录：即使从表中没有匹配的记录，也需要显示主表中的所有记录。
2. 数据完整性分析：用于确保从表的所有记录都被考虑，尤其是在数据汇总时。
3. 缺失数据的查找：帮助识别哪些右表中的记录没有对应的左表记录。

总结

右外连接是 SQL 查询中一个重要的工具，它确保从右表中获取所有记录，同时可以选择性地从左表中提取相关信息。这种连接方式在分析和报告生成中具有重要价值，尤其是在需要确保某个特定表中的所有数据都被保留时。

7-子查询

子查询就是一条查询语句结果作为另一条查询语句的一部分

首先引入数据

-- 创建部门表 1
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表 n
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT,
  foreign key(dept_id) references dept(id)
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

举例

SELECT 查询字段 FROM 表 WHERE 条件;
举例：
SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

子查询（Subquery）是 SQL 中的一种查询形式，它在其他 SQL 查询的内部执行。子查询可以用于 SELECT、INSERT、UPDATE 或 DELETE 语句中，通常用来提供数据或条件，以支持主查询的执行。

子查询的类型

1. 标量子查询： 返回单个值（单行单列）。可以用于比较操作。

   SELECT name
   FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees);
   

2. 行子查询： 返回单行多列。通常用于与多个列进行比较。

   SELECT name
   FROM employees
   WHERE (department_id, job_id) = (SELECT department_id, job_id FROM jobs WHERE job_title = 'Manager');
   

3. 表子查询： 返回多行多列。可以用于在 FROM 子句中。

   SELECT *
   FROM (SELECT name, salary FROM employees WHERE salary > 50000) AS high_earners;
   

例子

假设有两个表：employees（员工表）和 departments（部门表）。

• employees：

  employee_id	name	salary	department_id
  1	Alice	70000	10
  2	Bob	60000	20
  3	Charlie	50000	10
  4	David	80000	30

• departments：

  department_id	department_name
  10	HR
  20	IT
  30	Sales

例子 1：获取薪资高于某部门平均薪资的员工

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 10);

例子 2：获取所有在 IT 部门工作的员工

SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');