SQL语句基础：查询、插入、更新和删除数据的语法指南，掌握SQL操作的精髓

# 1. SQL语句概述**

SQL（结构化查询语言）是一种用于与关系型数据库管理系统（RDBMS）交互的特殊语言。它允许用户创建、查询、修改和删除数据库中的数据。SQL语句由一系列命令组成，每个命令都执行特定操作。

SQL语句通常分为以下几类：

* **数据定义语言 (DDL)**：用于创建和修改数据库结构，例如表、视图和索引。
* **数据操作语言 (DML)**：用于查询、插入、更新和删除数据库中的数据。
* **数据控制语言 (DCL)**：用于授予和撤销对数据库对象的访问权限。

# 2. 查询数据

### 2.1 SELECT 语句

#### 2.1.1 基本语法和使用

SELECT 语句用于从数据库表中检索数据。其基本语法如下：

SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition;

其中：

* `column_name1`, `column_name2`, ...：要检索的列名。
* `table_name`：要查询的表名。
* `condition`：可选的过滤条件，用于限制返回的行。

**示例：**

SELECT name, age
FROM students
WHERE age > 18;

此查询将检索所有年龄大于 18 岁的学生的姓名和年龄。

#### 2.1.2 过滤条件和排序

`WHERE` 子句用于指定过滤条件，以限制返回的行。常见的过滤操作符包括：

* `=`：等于
* `!=`：不等于
* `>`：大于
* `<`：小于
* `>=`：大于或等于
* `<=`：小于或等于

**示例：**

SELECT name, age
FROM students
WHERE age > 18 AND gender = 'male';

此查询将检索所有年龄大于 18 岁且性别为男性的学生的姓名和年龄。

`ORDER BY` 子句用于对结果集进行排序。排序操作符包括：

* `ASC`：升序
* `DESC`：降序

**示例：**

SELECT name, age
FROM students
ORDER BY age DESC;

此查询将检索所有学生的姓名和年龄，并按年龄降序排列。

#### 2.1.3 聚合函数

聚合函数用于对结果集中的数据进行汇总。常见的聚合函数包括：

* `COUNT()`：计算行数
* `SUM()`：计算值的总和
* `AVG()`：计算值的平均值
* `MAX()`：计算最大值
* `MIN()`：计算最小值

**示例：**

SELECT gender, COUNT(*) AS num_students
FROM students
GROUP BY gender;

此查询将按性别对学生进行分组，并计算每组学生的数量。

### 2.2 JOIN 语句

#### 2.2.1 不同类型的 JOIN

JOIN 语句用于将来自多个表的行组合在一起。常见的 JOIN 类型包括：

* **INNER JOIN**：仅返回在所有连接表中都匹配的行。
* **LEFT JOIN**：返回所有左表中的行，即使在右表中没有匹配的行。
* **RIGHT JOIN**：返回所有右表中的行，即使在左表中没有匹配的行。
* **FULL JOIN**：返回来自两个表的全部行，无论是否存在匹配。

**示例：**

SELECT s.name, c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.course_id;

此查询将连接 `students` 表和 `courses` 表，并返回所有学生及其课程名称。

#### 2.2.2 JOIN 条件和优化

JOIN 条件指定了用于连接表的列。可以使用 `ON` 或 `USING` 子句来指定条件。

**示例：**

SELECT s.name, c.course_name
FROM students AS s
JOIN courses AS c
ON s.course_id = c.course_id
WHERE s.age > 18;

此查询将连接 `students` 表和 `courses` 表，并仅返回年龄大于 18 岁的学生的姓名和课程名称。

为了优化 JOIN 查询，可以使用索引和适当的连接类型。

# 3. 插入数据

### 3.1 INSERT语句

#### 3.1.1 基本语法和使用

INSERT语句用于向表中插入新数据。其基本语法如下：

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

其中：

* `table_name`：要插入数据的表名。
* `column1`, `column2`, ..., `columnN`：要插入数据的列名。
* `value1`, `value2`, ..., `valueN`：要插入数据的列值。

例如，要向`users`表中插入一条新记录，可以使用以下语句：

INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john.doe@example.com', 30);

#### 3.1.2 约束和默认值

在插入数据时，需要遵守表的约束和默认值。约束可以防止插入无效或不一致的数据，而默认值可以在插入时自动填充缺失的值。

* **约束**：约束可以是主键、唯一索引、外键或检查约束。主键约束确保表中每一行的唯一性，唯一索引约束防止重复值，外键约束确保表之间的关系完整性，检查约束限制列值范围或格式。
* **默认值**：默认值是在插入时自动填充缺失值的列值。默认值可以在表定义中指定，也可以在INSERT语句中显式指定。

例如，如果`users`表有一个`created_at`列，并且该列有默认值`NOW()`，则在插入新记录时，该列将自动填充为当前时间戳。

### 3.2 批量插入

当需要向表中插入大量数据时，可以使用批量插入技术。批量插入可以显著提高插入效率，因为它减少了数据库与应用程序之间的往返次数。

#### 3.2.1 使用INSERT INTO ... SELECT

`INSERT INTO ... SELECT`语句允许从另一个表或查询中选择数据并将其插入到目标表中。其语法如下：

INSERT INTO table_name (column1, column2, ..., columnN)
SELECT column1, column2, ..., columnN
FROM source_table
[WHERE condition];

其中：

* `table_name`：要插入数据的表名。
* `column1`, `column2`, ..., `columnN`：要插入数据的列名。
* `source_table`：要从中选择数据的表名或查询。
* `WHERE condition`（可选）：用于过滤要插入数据的条件。

例如，要从`temp_users`表中将数据批量插入到`users`表中，可以使用以下语句：

INSERT INTO users (name, email, age)
SELECT name, email, age
FROM temp_users;

#### 3.2.2 使用LOAD DATA INFILE

`LOAD DATA INFILE`语句允许从外部文件中加载数据并将其插入到表中。其语法如下：

LOAD DATA INFILE 'filename.csv'
INTO TABLE table_name
[FIELDS TERMINATED BY ',' | '\t' | '|' | ... ]
[LINES TERMINATED BY '\n' | '\r' | '\r\n' | ... ]
[IGNORE 1 LINES]
[COLUMNS (column1, column2, ..., columnN)];

其中：

* `filename.csv`：要加载数据的外部文件路径。
* `table_name`：要插入数据的表名。
* `FIELDS TERMINATED BY`：指定字段分隔符。
* `LINES TERMINATED BY`：指定行分隔符。
* `IGNORE 1 LINES`（可选）：忽略文件中的第一行（通常是标题行）。
* `COLUMNS (column1, column2, ..., columnN)`（可选）：指定要加载到表中的列名。

例如，要从名为`users.csv`的CSV文件中加载数据到`users`表中，可以使用以下语句：

LOAD DATA INFILE 'users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
COLUMNS (name, email, age);

# 4. 更新数据

### 4.1 UPDATE语句

#### 4.1.1 基本语法和使用

UPDATE语句用于修改表中现有记录的数据。其基本语法如下：

UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;

其中：

* `table_name`：要更新的表名
* `column1`, `column2`, ...：要更新的列名
* `value1`, `value2`, ...：要更新的值
* `condition`：更新条件，用于指定要更新哪些记录

**示例：**

更新`employees`表中`salary`列的值，将所有`department_id`为1的员工的工资增加10%：

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

#### 4.1.2 更新条件和限制

`WHERE`子句用于指定要更新哪些记录。它可以包含任何有效的SQL条件表达式，例如：

* `=`：相等
* `!=`：不等于
* `<`：小于
* `>`：大于
* `<=`：小于或等于
* `>=`：大于或等于
* `BETWEEN`：介于两个值之间
* `IN`：在指定值列表中
* `LIKE`：模式匹配

**示例：**

更新`orders`表中`status`列的值，将所有`order_date`在2023-01-01之后的订单状态更新为`shipped`：

UPDATE orders
SET status = 'shipped'
WHERE order_date > '2023-01-01';

### 4.2 MERGE语句

#### 4.2.1 INSERT、UPDATE和DELETE操作

MERGE语句是一种强大的SQL语句，可以同时执行插入、更新和删除操作。其基本语法如下：

MERGE INTO table_name AS target
USING source_table AS source
ON target.primary_key = source.primary_key
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

其中：

* `target`：要更新或插入的表
* `source`：提供要更新或插入数据的表
* `primary_key`：用于匹配`target`和`source`表记录的主键列
* `MATCHED`：如果`target`和`source`表中存在匹配记录，则执行更新操作
* `NOT MATCHED`：如果`target`和`source`表中不存在匹配记录，则执行插入操作
* `NOT MATCHED BY SOURCE`：如果`target`表中存在但不匹配`source`表中的记录，则执行删除操作

#### 4.2.2 冲突解决和优化

当使用MERGE语句时，可能会出现冲突，即`target`和`source`表中存在多个匹配记录。为了解决冲突，可以使用`WHEN MATCHED`子句中的`AND`和`OR`条件来指定优先级。

此外，为了优化MERGE语句的性能，可以考虑以下技巧：

* 使用索引：在`target`和`source`表上创建索引以加快匹配过程。
* 批量更新：使用`IN`或`BETWEEN`条件一次更新多个记录。
* 减少子查询：避免在MERGE语句中使用子查询，因为它们会降低性能。

# 5. 删除数据**

**5.1 DELETE语句**

**5.1.1 基本语法和使用**

`DELETE` 语句用于从表中删除行。其基本语法如下：

DELETE FROM table_name
WHERE condition;

其中：

* `table_name` 是要删除行的表名。
* `condition` 是一个可选的条件，用于指定要删除哪些行。如果没有指定条件，则将删除表中的所有行。

**示例：**

DELETE FROM employees
WHERE salary < 10000;

此语句将删除所有薪水低于 10000 的员工记录。

**5.1.2 删除条件和限制**

`DELETE` 语句可以使用各种条件来指定要删除的行。最常见的条件是：

* `WHERE` 子句：用于指定要删除行的条件。
* `LIMIT` 子句：用于限制要删除的行数。
* `ORDER BY` 子句：用于指定删除行的顺序。

**示例：**

DELETE FROM employees
WHERE department_id = 10
ORDER BY salary DESC
LIMIT 5;

此语句将删除部门 ID 为 10 的员工记录，按降序排列薪水，并限制删除的行数为 5。

**5.2 TRUNCATE语句**

**5.2.1 快速删除数据**

`TRUNCATE` 语句用于快速删除表中的所有行。其语法如下：

TRUNCATE TABLE table_name;

与 `DELETE` 语句不同，`TRUNCATE` 语句不会触发任何触发器或外键约束。它直接从表中删除所有行，而无需逐行扫描。

**5.2.2 与DELETE语句的比较**

`TRUNCATE` 语句和 `DELETE` 语句都用于删除表中的行。但是，它们之间存在一些关键差异：

| 特征 | TRUNCATE | DELETE |
|---|---|---|
| 速度 | 更快 | 更慢 |
| 触发器 | 不触发 | 触发 |
| 外键约束 | 不检查 | 检查 |
| 日志记录 | 不记录 | 记录 |
| 回滚 | 不可回滚 | 可回滚 |

一般来说，如果需要快速删除表中的所有行，并且不需要触发触发器或检查外键约束，则可以使用 `TRUNCATE` 语句。否则，应该使用 `DELETE` 语句。

# 6. 高级SQL技巧**

**6.1 子查询**

子查询是嵌套在主查询中的一个单独的查询。它允许我们在主查询中使用子查询的结果。子查询可以用于：

- 过滤主查询中的数据
- 提供主查询中使用的值
- 汇总主查询中的数据

**6.1.1 嵌套子查询**

嵌套子查询是放置在另一个子查询中的子查询。嵌套子查询可以用于创建更复杂的数据检索。

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = 'Sales'
);

**6.1.2 相关子查询**

相关子查询是引用主查询中的列的子查询。相关子查询用于从主查询中检索与当前行相关的数据。

SELECT employee_id,
       salary,
       (
           SELECT MAX(salary)
           FROM employees
           WHERE department = e.department
       ) AS max_salary_in_department
FROM employees AS e;

**6.2 窗口函数**

窗口函数是在一组行（称为窗口）上执行计算的函数。窗口函数用于：

- 对数据进行排序和聚合
- 计算偏移量和滚动平均值

**6.2.1 排序和聚合**

窗口函数可以用于对数据进行排序和聚合。

SELECT employee_id,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees;

**6.2.2 偏移和滚动**

窗口函数可以用于计算偏移量和滚动平均值。

SELECT employee_id,
       salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;