MySQL DQL 查的方法02

MySQL 这边的东西全部来找自 https://itbaima.net/document

聚集函数

聚集函数一般用作统计，包括：

• count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
• count([distinct]列名)统计某列的值总和
• sum([distinct]列名)求一列的和（注意必须是数字类型的）
• avg([distinct]列名)求一列的平均值（注意必须是数字类型）
• max([distinct]列名)求一列的最大值
• min([distinct]列名)求一列的最小值

一般聚集函数是这样使用的：

SELECT count(distinct 列名) FROM 表名 WHERE 条件 

分组和分页查询

通过使用group by来对查询结果进行分组，它需要结合聚合函数一起使用：

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名

我们还可以添加having来限制分组条件：

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

我们可以通过limit来限制查询的数量，只取前n个结果：

SELECT * FROM 表名 LIMIT 数量

我们也可以进行分页：

SELECT * FROM 表名 LIMIT 起始位置,数量

举个例子

让我们通过一个具体的示例来理解GROUP BY, HAVING, LIMIT等SQL语句的使用。

假设我们有一个名为Sales的表，它记录了各个员工的销售数据：

1. Sales 表格

EmployeeID	SaleAmount
1	100
2	150
1	200
3	120
2	180
3	160

2. 使用 GROUP BY 和 聚合函数

我们想计算每个员工的总销售额：

SELECT EmployeeID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY EmployeeID;

这个查询将按EmployeeID分组，并计算每组的销售总额。

3. 使用 GROUP BY 和 HAVING

如果我们只想查看总销售额超过300的员工：

SELECT EmployeeID, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY EmployeeID
HAVING SUM(SaleAmount) > 300;

这个查询在GROUP BY的基础上，通过HAVING子句进一步限制只显示总销售额超过300的结果。

4. 使用 LIMIT 进行限制查询

如果我们只想查询表中的前3条记录：

SELECT * FROM Sales
LIMIT 3;

这将返回Sales表的前3条记录。

5. 使用 LIMIT 进行分页

如果我们想查看第二页的数据，假设每页显示2条记录，那么第二页的查询将如下：

SELECT * FROM Sales
LIMIT 2 OFFSET 2;

或者等价的：

SELECT * FROM Sales
LIMIT 2, 2;

这将跳过前2条记录，然后返回接下来的2条记录，即第3条和第4条记录。

这些查询示例展示了如何在SQL中使用分组、分页和聚合函数来管理和分析数据。

多表查询

多表查询是同时查询的两个或两个以上的表，多表查询会提通过连接转换为单表查询。

SELECT * FROM 表1, 表2

直接这样查询会得到两张表的笛卡尔积，也就是每一项数据和另一张表的每一项数据都结合一次，会产生庞大的数据。

SELECT * FROM 表1, 表2 WHERE 条件

这样，只会从笛卡尔积的结果中得到满足条件的数据。

**注意：**如果两个表中都带有此属性吗，需要添加表名前缀来指明是哪一个表的数据。

举个例子

假设我们有两个表格：

1. Employees 表格

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

2. Departments 表格

DepartmentID	DepartmentName
101	HR
102	IT
103	Marketing
104	Finance

现在，如果我们想要获取每个员工及其对应部门的信息，我们可以进行多表查询。

3. 使用笛卡尔积（不推荐）

使用笛卡尔积进行查询的SQL语句：

SELECT * FROM Employees, Departments

这将产生所有可能的员工与部门的组合，这通常不是我们想要的，因为它会产生大量不相关的数据组合。

4. 使用条件进行过滤

更合理的查询方式是使用条件来限制结果集，仅显示员工所属的部门：

SELECT Employees.Name, Departments.DepartmentName
FROM Employees, Departments
WHERE Employees.DepartmentID = Departments.DepartmentID;

这个查询会得到以下结果：

5. 查询结果

Name	DepartmentName
Alice	HR
Bob	IT
Charlie	Marketing
David	Finance

在这个例子中，我们通过在WHERE子句中指定条件（Employees.DepartmentID = Departments.DepartmentID），只获取了那些员工和部门ID相匹配的行。这种方式避免了产生无关的数据组合。

自身连接查询

自身连接，就是将表本身和表进行笛卡尔积计算，得到结果，但是由于表名相同，因此要先起一个别名：

SELECT * FROM 表名 别名1, 表名 别名2

其实自身连接查询和前面的是一样的，只是连接对象变成自己和自己了。

举个例子

假设我们有一个名为Employees的表，其中包含员工的ID、姓名以及他们经理的ID。

1. Employees 表格

EmployeeID	Name	ManagerID
1	Alice	3
2	Bob	3
3	Carol	NULL
4	David	2
5	Eve	2

在这个表中，每个员工（除了Carol，因为她是经理级别且没有上级经理）都有一个上级经理。例如，Alice和Bob的经理是Carol，David和Eve的经理是Bob。

使用自身连接查询来获取员工及其对应经理的姓名的SQL语句如下：

SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

执行这个查询后，我们将得到以下结果：

2. 查询结果

EmployeeName	ManagerName
Alice	Carol
Bob	Carol
David	Bob
Eve	Bob

在这个结果中，我们可以看到每个员工及其直接上级经理的姓名。注意，Carol在这个结果集中没有出现，因为她没有上级经理（ManagerID是NULL），而在自身连接查询中，我们只连接了有经理的员工记录。

外连接查询

外连接就是专门用于联合查询情景的，比如现在有一个存储所有用户的表，还有一张用户详细信息的表，我希望将这两张表结合到一起来查看完整的数据，我们就可以通过使用外连接来进行查询，外连接有三种方式：

MySQL不支持full join

• 通过使用inner join进行内连接，只会返回两个表满足条件的交集部分：

• 通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理，只是反过来而已，这里就不再介绍了）：

举个例子

假设这两个表格的数据如下：

1. Employees 表格

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

2. Departments 表格

DepartmentID	DepartmentName
101	Sales
102	HR
104	IT

现在，我们将使用INNER JOIN和LEFT JOIN来查询这些数据。

3. INNER JOIN 示例

SQL查询：

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

这将返回两个表中DepartmentID匹配的行：

Name	DepartmentName
Alice	Sales
Bob	HR

注意，Charlie和David没有出现在结果中，因为他们的DepartmentID在Departments表中没有对应的条目（Charlie的部门不存在，David的DepartmentID是NULL）。

4. LEFT JOIN 示例

SQL查询：

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

这将返回Employees表中的所有行，并尝试与Departments表中的行匹配：

Name	DepartmentName
Alice	Sales
Bob	HR
Charlie	NULL
David	NULL

这里，即使Charlie和David在Departments表中没有匹配项，他们仍然出现在结果集中，但与部门名称相关的字段为NULL。

嵌套查询

我们可以将查询的结果作为另一个查询的条件，比如：

SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)

举个例子

假设我们有两个表格：

1. Employees 表格，包含员工的信息：

   • EmployeeID（员工ID）
   • Name（姓名）
   • ManagerID（经理ID）

2. Projects 表格，包含项目的信息：

   • ProjectID（项目ID）
   • ProjectName（项目名称）
   • LeadEmployeeID（项目负责人ID）

现在，我们想找出所有由Alice负责的项目中的员工。首先，我们需要找出Alice的EmployeeID，然后使用这个ID来找出所有她负责的项目的ProjectID，最后找出所有参与这些项目的员工。

这里是如何用子查询实现这个目标的：

SELECT *
FROM Employees
WHERE EmployeeID IN (
    SELECT LeadEmployeeID
    FROM Projects
    WHERE ProjectID IN (
        SELECT ProjectID
        FROM Projects
        WHERE LeadEmployeeID = (
            SELECT EmployeeID
            FROM Employees
            WHERE Name = 'Alice'
        )
    )
);

这个查询的解释如下：

1. 内层子查询：SELECT EmployeeID FROM Employees WHERE Name = 'Alice' - 这个查询找出Alice的EmployeeID。

2. 中层子查询：SELECT ProjectID FROM Projects WHERE LeadEmployeeID = ([Alice的ID]) - 使用Alice的EmployeeID来找出所有她负责的项目。

3. 外层子查询：SELECT LeadEmployeeID FROM Projects WHERE ProjectID IN ([Alice负责的项目ID]) - 使用项目ID来找出所有这些项目的负责人ID。

4. 最终查询：SELECT * FROM Employees WHERE EmployeeID IN ([项目负责人ID]) - 最后，使用这些ID来找出所有相关的员工信息。

这是一个比较复杂的嵌套子查询例子，展示了如何层层递进地使用子查询来解决复杂的数据关联问题。