MySQL很久没碰，复习一下

MySQL学习记录和Leetcode刷题记录

第一题：

表：Customers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| customer_id  | int  |
| year         | int  |
| revenue      | int  |
+--------------+------+
(customer_id, year) 是该表的主键（具有唯一值的列的组合）。
这个表包含客户 ID 和不同年份的客户收入。
注意，这个收入可能是负数。

编写一个解决方案来报告 2021 年具有 正收入 的客户。

可以以 任意顺序 返回结果表。

结果格式如下示例所示。

示例 1:

Input:
Customers
+-------------+------+---------+
| customer_id | year | revenue |
+-------------+------+---------+
| 1           | 2018 | 50      |
| 1           | 2021 | 30      |
| 1           | 2020 | 70      |
| 2           | 2021 | -50     |
| 3           | 2018 | 10      |
| 3           | 2016 | 50      |
| 4           | 2021 | 20      |
+-------------+------+---------+

Output:
+-------------+
| customer_id |
+-------------+
| 1           |
| 4           |
+-------------+
客户 1 在 2021 年的收入等于 30 。
客户 2 在 2021 年的收入等于 -50 。
客户 3 在 2021 年没有收入。
客户 4 在 2021 年的收入等于 20 。
因此，只有客户 1 和 4 在 2021 年有正收入。

答案如下：

# Write your MySQL query statement below
SELECT customer_id
FROM Customers
WHERE year = 2021 AND revenue > 0
ORDER BY customer_id;

第二题

Customers 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
该表的每一行都表示客户的 ID 和名称。

Orders 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
在 SQL 中，id 是该表的主键。
customerId 是 Customers 表中 ID 的外键( Pandas 中的连接键)。
该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。

找出所有从不点任何东西的顾客。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1：

输入：
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
输出：
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

答案如下：

`\# Write your MySQL query statement below`

`SELECT c.name AS Customers`

`FROM Customers c`

`LEFT JOIN Orders o ON c.id = o.customerId`

`WHERE o.customerId IS NULL;`

没问题！我根据 SQL 的核心知识点，结合一个完整的案例，逐点梳理，确保每一个知识点都有具体的案例来帮助你理解。我们将以一个 在线商店 的数据库为例，涵盖所有知识点。

---

案例背景

我们有一个在线商店，数据库中包含以下表：

1. Customers 表：存储客户信息。
2. Orders 表：存储订单信息。
3. Products 表：存储产品信息。
4. OrderDetails 表：存储订单的详细信息。

---

表结构及数据

1. Customers 表

id	name	email
1	Alice	alice@example.com
2	Bob	bob@example.com
3	Charlie	charlie@example.com

---

2. Orders 表

id	customerId	orderDate
101	1	2023-01-01
102	1	2023-02-01
103	2	2023-03-01

---

3. Products 表

id	name	price
201	Laptop	1200
202	Smartphone	800

---

4. OrderDetails 表

id	orderId	productId	quantity
301	101	201	1
302	101	202	2
303	103	202	1

---

SQL 知识点及案例

1. SELECT 查询

查询所有客户的信息。

SELECT id, name, email FROM Customers;

---

2. WHERE 条件筛选

查询名为 “Alice” 的客户信息。

SELECT id, name, email 
FROM Customers 
WHERE name = 'Alice';

---

3. JOIN 连接

查询每个订单的客户名称和订单日期。

SELECT c.name, o.orderDate
FROM Customers c
INNER JOIN Orders o ON c.id = o.customerId;

---

4. LEFT JOIN 与 NULL 处理

查询从未下过订单的客户。

SELECT c.name
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.customerId IS NULL;

---

5. GROUP BY 与聚合函数

查询每个客户的总订单数。

SELECT c.name, COUNT(o.id) AS orderCount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
GROUP BY c.name;

---

6. ORDER BY 排序

查询所有产品，按价格从高到低排序。

SELECT name, price
FROM Products
ORDER BY price DESC;

---

7. INSERT 插入数据

向 Customers 表中插入一条新客户记录。

INSERT INTO Customers (name, email)
VALUES ('David', 'david@example.com');

---

8. UPDATE 更新数据

将 “Alice” 的邮箱更新为 alice_new@example.com。

UPDATE Customers
SET email = 'alice_new@example.com'
WHERE name = 'Alice';

---

9. DELETE 删除数据

删除名为 “Charlie” 的客户。

DELETE FROM Customers
WHERE name = 'Charlie';

---

10. 子查询

查询订单数量超过 1 的客户信息。

SELECT id, name, email
FROM Customers
WHERE id IN (
    SELECT customerId
    FROM Orders
    GROUP BY customerId
    HAVING COUNT(id) > 1
);

---

11. 视图（View）

创建一个视图，显示每个订单的客户名称、订单日期和总金额。

CREATE VIEW OrderSummary AS
SELECT c.name, o.orderDate, SUM(p.price * od.quantity) AS totalAmount
FROM Customers c
INNER JOIN Orders o ON c.id = o.customerId
INNER JOIN OrderDetails od ON o.id = od.orderId
INNER JOIN Products p ON od.productId = p.id
GROUP BY c.name, o.orderDate;

查询视图：

SELECT * FROM OrderSummary;

---

12. 索引（Index）

为 Customers 表的 email 列创建索引，加快查询速度。

CREATE INDEX idx_email ON Customers(email);

---

13. 事务（Transaction）

模拟一个事务：插入一条新订单并更新库存。

BEGIN TRANSACTION;

-- 插入订单
INSERT INTO Orders (customerId, orderDate)
VALUES (1, '2023-04-01');

-- 更新库存（假设有库存表）
UPDATE Inventory
SET quantity = quantity - 1
WHERE productId = 201;

-- 提交事务
COMMIT;

如果发生错误，可以回滚事务：

ROLLBACK;

---

总结

通过这个在线商店的案例，逐一覆盖了 SQL 的核心知识点，包括：

1. 基本查询 (SELECT, FROM)。
2. 条件筛选 (WHERE)。
3. 连接 (JOIN)。
4. 分组及聚合 (GROUP BY, COUNT, SUM)。
5. 排序 (ORDER BY)。
6. 数据操作 (INSERT, UPDATE, DELETE)。
7. 高级功能 (子查询, 视图, 索引, 事务)。