从0开始学mysql 第七课：MySQL 高级查询技巧

第七课：MySQL 高级查询技巧

学习目标

在本课中，我们将探索更深层次的 MySQL 高级查询技术，这将加强您处理复杂数据检索任务的能力。您将学习如何运用子查询、高级的 JOIN 操作、集合操作，以及如何创建和使用视图和索引以优化性能。

1. 子查询

子查询是嵌套在其他 SQL 查询中的查询，通常用在 WHERE 子句中，但也可以在 SELECT 和 FROM 子句中使用。

详细示例

-- 找出销售额超过某个部门平均销售额的所有员工
SELECT e.Name, e.Sales
FROM Employees e
WHERE e.Sales > (
    SELECT AVG(Sales)
    FROM Employees
    WHERE Department = 'Sales'
);

在这个例子中，子查询计算销售部门的平均销售额，然后外层查询找出所有超过这个平均数的员工。

2. 连接表

在数据库中，JOIN 操作用于根据两个表之间的关系来组合行。JOIN 类型非常关键，它决定了查询的结果。

详细示例

-- 使用 LEFT JOIN 获取所有员工及其部门名称，即使某些员工没有分配部门
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

在这个例子中，LEFT JOIN 确保所有员工都会显示，即使他们没有对应的部门。

3. 集合操作

集合操作符，如 UNION, INTERSECT, 和 EXCEPT，用于合并两个或多个 SELECT 语句的结果。

详细示例

-- 获取在两个不同表中都有记录的员工名单
(SELECT Name FROM FullTimeEmployees)
UNION
(SELECT Name FROM PartTimeEmployees);

UNION 操作符合并两个查询的结果，并且消除重复的记录。如果要保留重复记录，可以使用 UNION ALL。

4. 视图

视图是一个虚拟表，其内容由查询定义。它们是存储查询的一种方式，可以像表一样使用。

详细示例

-- 创建一个包含特定信息的视图
CREATE VIEW ActiveCustomers AS
SELECT CustomerID, Name, LastOrderDate
FROM Customers
WHERE IsActive = 1;

-- 从视图中查询数据
SELECT * FROM ActiveCustomers;

创建视图之后，可以重复使用它，而无需编写完整的查询。

5. 索引

索引是帮助数据库快速检索数据的数据结构。正确使用索引可以极大地提高查询的速度。

详细示例

-- 为 Employees 表的 Name 列创建一个索引
CREATE INDEX idx_name ON Employees (Name);

-- 解释
-- 索引 idx_name 将加快基于 Name 列的查询速度。
-- 但是，如果频繁更新 Name 列，索引可能会减慢这些操作的速度，因为索引本身也需要更新。

创建索引时，应权衡查询速度的提升和更新操作的开销。

课后练习

1. 编写使用子查询的 SQL 语句来选择所有的订单，这些订单的金额超过客户的平均订单金额。
2. 创建一个 SQL 查询来展示员工和他们直接上司的姓名。
3. 使用 UNION 的 SQL 语句来合并两个有相似数据结构的表的数据。
4. 创建一个视图来简化复杂查询，并从该视图中选择数据。
5. 选择一个表和列，根据查询频率创建一个索引，并解释为什么选择了这个表和列。

让我们一一解答这些练习题：

练习 1: 使用子查询选择订单

编写使用子查询的 SQL 语句来选择所有的订单，这些订单的金额超过客户的平均订单金额。

SELECT * 
FROM Orders AS o 
WHERE o.Amount > (
    SELECT AVG(Amount) 
    FROM Orders 
    WHERE CustomerID = o.CustomerID
);

解释：
这个查询使用了一个子查询来计算每个客户的平均订单金额。外层查询然后找出了金额大于其客户平均订单金额的订单。

练习 2: 展示员工和他们上司的姓名

创建一个 SQL 查询来展示员工和他们直接上司的姓名。

假设我们有两个表：Employees（包含员工信息）和Managers（包含经理信息），并且Employees表中有一个ManagerID字段关联到Managers表。

SELECT e.EmployeeName, m.ManagerName 
FROM Employees AS e
JOIN Managers AS m 
ON e.ManagerID = m.ManagerID;

解释：
这个查询通过JOIN操作符将员工与他们的上司连接起来，假设每位员工的上司在Managers表中有记录。

练习 3: 使用 UNION 合并表

使用 UNION 的 SQL 语句来合并两个有相似数据结构的表的数据。

假设我们有两个表：Sales2019和Sales2020，它们结构相同。

SELECT * FROM Sales2019
UNION ALL
SELECT * FROM Sales2020;

解释：
这里使用UNION ALL来合并两个表中的数据。如果你不希望合并重复的记录，可以使用UNION代替UNION ALL。

练习 4: 创建视图简化查询

创建一个视图来简化复杂查询，并从该视图中选择数据。

CREATE VIEW View_ComplexData AS
SELECT e.EmployeeName, d.DepartmentName, p.ProjectName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
JOIN Projects p ON e.ProjectID = p.ProjectID;

SELECT * FROM View_ComplexData;

解释：
这个视图View_ComplexData将三个表：员工、部门和项目，通过JOIN操作合并，以便简化后续的查询操作。

练习 5: 创建索引优化查询

选择一个表和列，根据查询频率创建一个索引，并解释为什么选择了这个表和列。

假设Orders表经常根据CustomerID进行查询：

CREATE INDEX idx_customer_id ON Orders (CustomerID);

解释：
由于Orders表中的查询经常根据CustomerID进行筛选，创建一个索引在CustomerID上可以加快这些查询的速度。索引可以提高查询效率，因为它允许数据库更快地定位到特定的CustomerID关联的行。