2025-02-25发表2025-02-25更新sql1 小时读完 (大约10574个字)

Learning SQL

SQL（Structured Query Language）是一种用于管理关系型数据库的编程语言。通过 SQL，你可以查询、插入、更新和删除数据库中的数据。关系型数据库（RDBMS）是使用表格来组织数据的数据库管理系统。常见的关系型数据库包括 MySQL、PostgreSQL、SQLite 和 Oracle 等。

SQL 基础语法

SQL 的基础语法包括数据类型、运算符、关键字等内容。让我们一步步来详细了解。

数据类型（Data Types）

在 SQL 中，数据类型用于定义每个列可以存储的数据的类型。常见的数据类型如下：

整数类型：
- INT：用于存储整数（没有小数）。
- TINYINT：非常小的整数。
- BIGINT：非常大的整数。
浮动类型：
- FLOAT：单精度浮动点数。
- DOUBLE：双精度浮动点数。
字符类型：
- VARCHAR(size)：变长字符串类型，size指定最大字符长度。
- CHAR(size)：定长字符串类型。
- TEXT：用于存储大量文本。
日期和时间类型：
- DATE：存储日期（年月日）。
- TIME：存储时间（小时、分钟、秒）。
- DATETIME：存储日期和时间。
布尔类型：
- BOOLEAN：存储TRUE或FALSE。

运算符（Operators）

SQL 中的运算符用于执行各种操作，常见的运算符包括：

算术运算符：
- +：加法。
- -：减法。
- *：乘法。
- /：除法。
- %：取余。
比较运算符：
- =：等于。
- != 或 <>：不等于。
- >：大于。
- <：小于。
- >=：大于等于。
- <=：小于等于。
逻辑运算符：
- AND：与，表示两个条件同时满足。
- OR：或，表示其中一个条件满足。
- NOT：非，表示条件不满足。
其他运算符：
- BETWEEN：判断值是否在某个范围内。
- IN：判断值是否在一个给定的集合中。
- LIKE：模糊匹配，常用于字符串匹配。
- IS NULL：判断值是否为空。
- IS NOT NULL：判断值是否非空。

SQL 关键字（Keywords）

SQL 的关键字用于指定 SQL 语句的各种操作，常见的关键字包括：

**SELECT**：用于从表格中检索数据。
**INSERT INTO**：用于插入新数据。
**UPDATE**：用于更新现有数据。
**DELETE**：用于删除数据。
**FROM**：指定查询的表格。
**WHERE**：用于添加条件过滤数据。
**ORDER BY**：用于排序数据。
**GROUP BY**：用于将数据按某一列分组。
**HAVING**：用于对分组后的数据进行条件过滤。
**JOIN**：用于连接多个表格的数据。
**CREATE**：用于创建数据库对象，如表、索引、视图等。
**ALTER**：用于修改数据库对象，如修改表格结构。
**DROP**：用于删除数据库对象。

常用的 SQL 语句

以下是常见的 SQL 语句及其功能：

`SELECT` 查询语句

用于从一个或多个表格中检索数据。

1
2
3

SELECT column1, column2
FROM table_name
WHERE condition;

`INSERT INTO` 插入数据

用于向表格中插入一条或多条记录。

1 2	INSERT INTO table_name (column1, column2) VALUES (value1, value2);

`UPDATE` 更新数据

用于修改表格中现有的记录。

1
2
3

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

`DELETE` 删除数据

用于删除表格中的数据。

1 2	DELETE FROM table_name WHERE condition;

数据定义语言（DDL）

数据定义语言（DDL）用于定义和修改数据库结构（如表格、视图、索引等）。常见的 DDL 语句有：

`CREATE` 创建数据库或表格

创建数据库：
1
CREATE DATABASE database_name;

创建表格：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

`ALTER` 修改表格

添加新列：

1 2	ALTER TABLE table_name ADD column_name datatype;

修改列的数据类型：

1 2	ALTER TABLE table_name MODIFY column_name new_datatype;

`DROP` 删除表格或数据库

删除表格：
1
DROP TABLE table_name;
删除数据库：
1
DROP DATABASE database_name;

`TRUNCATE` 清空表格

删除表格中的所有记录，但保留表格结构：
1
TRUNCATE TABLE table_name;

小结

数据类型：用于定义列的数据格式，如INT、VARCHAR等。
运算符：用于进行各种运算和比较，如算术运算符、逻辑运算符等。
关键字：用于执行 SQL 操作的单词，如SELECT、INSERT、WHERE等。
常用 SQL 语句：包括数据查询、插入、更新和删除。
数据定义语言（DDL）：用于创建、修改和删除数据库对象（表格、数据库等）。

数据操作：聚合查询与数据约束

这部分内容会涉及到聚合查询和数据约束，帮助你更好地处理数据分析和数据库完整性。

聚合查询（Aggregate Queries）

聚合函数用于对一组数据进行计算，并返回单个值。常见的聚合函数有：AVG（平均值）、MIN（最小值）、MAX（最大值）、SUM（总和）、COUNT（计数）等。

聚合函数的使用

**AVG()**：计算一组数据的平均值。
1
SELECT AVG(age) FROM users;
**MIN()**：返回一组数据中的最小值。
1
SELECT MIN(age) FROM users;
**MAX()**：返回一组数据中的最大值。
1
SELECT MAX(age) FROM users;
**SUM()**：计算一组数据的总和。
1
SELECT SUM(age) FROM users;

**COUNT()**：计算某列或所有行的数量。

1 2	SELECT COUNT(*) FROM users; -- 计算所有记录的数量 SELECT COUNT(age) FROM users; -- 计算非空年龄的记录数量

`GROUP BY` 语句

当你需要对数据进行分组时，可以使用GROUP BY语句。它可以将查询结果分成若干组，然后对每组数据进行聚合计算。

1 2	SELECT age, COUNT(*) FROM users GROUP BY age;

上面的查询会根据age对用户进行分组，并返回每个年龄的用户数量。

`HAVING` 语句

HAVING语句用于对分组后的数据进行过滤。不同于WHERE只能对原始数据进行过滤，HAVING是对聚合结果进行过滤。

1
2
3

SELECT age, COUNT(*) FROM users
GROUP BY age
HAVING COUNT(*) > 1;

此查询返回有多个用户的年龄组。

数据约束（Data Constraints）

数据约束用于限制数据库中数据的类型、范围等，确保数据的准确性和完整性。常见的数据约束有：

主键（Primary Key）

主键是表中唯一标识每一行的字段或字段组合。主键字段不能有重复值，且不能为空。每个表只能有一个主键。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

这里，id是主键，保证了每个id的唯一性。

外键（Foreign Key）

外键是指向另一个表中的主键，用于建立和维护两个表之间的关系。外键可以帮助确保数据一致性。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

在这个例子中，orders表中的user_id是外键，它引用了users表中的id列。

唯一约束（Unique Constraint）

唯一约束确保列中的所有值都是唯一的。它允许空值，但每个非空值都必须是唯一的。

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

在这个例子中，email列有唯一约束，确保每个用户的电子邮件地址是唯一的。

非空约束（NOT NULL）

非空约束用于确保列中的值不能为空。如果一个列有NOT NULL约束，那么在插入或更新数据时，必须提供该列的值。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

在这个例子中，name列不能为NULL，每个用户都必须有名字。

检查约束（CHECK Constraint）

CHECK约束用于限制列的值范围。例如，确保用户年龄在 18 岁到 100 岁之间：

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age BETWEEN 18 AND 100)
);

小结

聚合查询：包括常用的聚合函数（AVG、MIN、MAX、SUM、COUNT）及其在GROUP BY和HAVING中的使用，用于对数据进行分组和过滤。
数据约束：用于保证数据库中的数据完整性，包括主键、外键、唯一约束、非空约束等。

联接查询（JOIN Queries）

在 SQL 中，联接查询用于将多个表的数据按某种条件结合起来，形成一个包含相关数据的结果集。常见的连接方式有INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN等。此外，还有自连接和交叉连接等特殊用法。

各种连接方式

`INNER JOIN`（内连接）

INNER JOIN是最常用的连接方式，它返回两个表中匹配的行。如果没有匹配的行，结果中不包含该行。

示例：

假设有两个表：

users表：存储用户信息
orders表：存储订单信息

1
2
3

SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

这个查询会返回所有用户和他们的订单，其中users.id和orders.user_id是连接的条件。

`LEFT JOIN`（左外连接）

LEFT JOIN返回左表的所有行，即使右表没有匹配的行。如果右表没有匹配的行，结果中右表的列会显示NULL。

示例：

1
2
3

SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

这个查询会返回所有用户及其订单，如果某个用户没有订单，则orders.order_id列显示NULL。

`RIGHT JOIN`（右外连接）

RIGHT JOIN与LEFT JOIN相似，但它返回右表的所有行，即使左表没有匹配的行。如果左表没有匹配的行，结果中左表的列会显示NULL。

示例：

1
2
3

SELECT users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

这个查询会返回所有订单以及与之关联的用户。如果某个订单没有关联的用户，则users.name列显示NULL。

`FULL OUTER JOIN`（全外连接）

FULL OUTER JOIN返回左表和右表中的所有行。对于没有匹配的行，左表或右表的列会显示NULL。

示例：

1
2
3

SELECT users.name, orders.order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

这个查询会返回所有用户和所有订单。如果某个用户没有订单，orders.order_id列显示NULL；如果某个订单没有用户，users.name列显示NULL。

自连接（Self Join）

自连接是指同一个表与其自身进行连接。通常用于表中存储层级或父子关系的数据，比如组织结构、分类结构等。

示例

假设有一个employees表，其中manager_id字段指向另一个员工的id，表示员工的经理。我们想查询员工及其经理的信息。

1
2
3

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

在这个查询中，employees表与自身进行连接，e表示员工，m表示经理。

交叉连接（CROSS JOIN）

CROSS JOIN返回左表和右表的笛卡尔积，即左表的每一行与右表的每一行都进行组合。结果集的行数是左表行数与右表行数的乘积。

示例

假设有两个表：products（产品）和colors（颜色）。

1
2
3

SELECT products.product_name, colors.color
FROM products
CROSS JOIN colors;

这个查询会返回所有产品和所有颜色的组合。假设products表有 3 个产品，colors表有 4 个颜色，则结果集将有 12 行。

小结

**INNER JOIN**：返回两个表中匹配的行，不匹配的行会被忽略。
**LEFT JOIN**：返回左表的所有行，即使右表没有匹配的行，右表没有匹配的地方会显示NULL。
**RIGHT JOIN**：返回右表的所有行，即使左表没有匹配的行，左表没有匹配的地方会显示NULL。
**FULL OUTER JOIN**：返回两个表中所有的行，如果某一表没有匹配的行，相关列显示NULL。
自连接（Self Join）：表与自身进行连接，通常用于表示层级关系。
交叉连接（CROSS JOIN）：返回左表与右表的笛卡尔积，通常用于生成所有可能的组合。

子查询（Subqueries）

子查询是嵌套在另一个查询中的查询，用于从另一个查询的结果中提取数据。子查询可以放在SELECT、WHERE、FROM等不同位置，起到过滤、计算、联接等作用。常见的子查询类型包括标量列子查询、行子查询、表子查询等。

子查询类型

标量列子查询（Scalar Subquery）

标量列子查询返回一个单一的值，即一个列中的单个值。它通常用于SELECT、WHERE等子句中。

示例

假设有两个表：employees和departments，我们希望查询每个部门的员工信息，其中部门的经理 ID 来自departments表。

SELECT name, department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE manager_id = 1001
);

在这个查询中，子查询返回manager_id = 1001的部门 ID，然后主查询使用这个部门 ID 来查找相应的员工。

行子查询（Row Subquery）

行子查询返回一个行数据（多列的值）。通常，行子查询会用于WHERE子句中，并且通过比较操作符（如=、>等）与外部查询进行匹配。

示例

假设有一个employees表和一个salaries表，我们希望找到工资大于某个特定员工的所有员工。我们使用行子查询来获取该员工的salary和department_id。

SELECT name
FROM employees
WHERE (salary, department_id) > (
    SELECT salary, department_id
    FROM employees
    WHERE id = 1001
);

在这里，子查询返回员工1001的salary和department_id，外部查询则返回所有工资高于该员工且属于同一部门的员工。

表子查询（Table Subquery）

表子查询返回多个行和列，通常会在FROM子句中使用。表子查询返回的是一个临时表或结果集，可以像普通的表格一样进行查询。

示例

假设我们想查询每个部门的平均工资。可以使用表子查询来先计算出每个部门的平均工资，然后在外部查询中计算总工资。

SELECT department_id, SUM(salary) AS total_salary
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
)
GROUP BY department_id;

这个查询使用表子查询来获取New York的所有部门 ID，然后外部查询根据这些部门 ID 计算总工资。

嵌套子查询与相关子查询

嵌套子查询（Nested Subquery）

嵌套子查询是指子查询本身还可以包含其他的子查询。嵌套子查询可以帮助你进行复杂的查询，通常用于多层数据过滤或计算。

示例

假设有一个employees表和departments表，我们想要找出在所有部门中，工资高于所在部门平均工资的员工。

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

这里，外部查询返回工资高于其部门平均工资的员工，内部查询计算每个部门的平均工资。

小结

标量列子查询：返回单一值，通常用于WHERE或SELECT中。
行子查询：返回一行数据，通常用于WHERE中与外部查询进行比较。
表子查询：返回一个完整的结果集，可以在FROM子句中使用。
嵌套子查询：子查询中包含另一个子查询，用于复杂的多层查询。
相关子查询：子查询依赖外部查询的列，每行数据都会执行子查询。

SQL 高级函数

SQL 提供了许多高级函数，用于对数据进行更复杂的操作和处理。常见的高级函数包括字符串函数、日期和时间函数以及数值函数。下面，我们逐一讲解这些函数的用法。

字符串函数（String Functions）

字符串函数用于对字符串类型的数据进行操作。以下是一些常用的字符串函数：

`CONCAT()`：连接字符串

CONCAT()函数用于将两个或多个字符串连接成一个字符串。

示例

1 2	SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

这将返回员工的全名，将first_name和last_name用空格连接起来。

`LENGTH()`：字符串长度

LENGTH()函数返回字符串中的字符数。

示例

1 2	SELECT LENGTH(name) AS name_length FROM employees;

此查询返回员工名字的长度。

`UPPER()`：将字符串转换为大写

UPPER()函数将字符串中的所有字符转换为大写字母。

示例

1 2	SELECT UPPER(name) AS uppercase_name FROM employees;

此查询将返回员工姓名的全大写形式。

`LOWER()`：将字符串转换为小写

LOWER()函数将字符串中的所有字符转换为小写字母。

示例

1 2	SELECT LOWER(name) AS lowercase_name FROM employees;

此查询将返回员工姓名的全小写形式。

`SUBSTRING()`：提取字符串的一部分

SUBSTRING()函数从字符串中提取指定位置的子字符串。

示例

1 2	SELECT SUBSTRING(name, 1, 3) AS short_name FROM employees;

此查询将返回员工姓名的前 3 个字符。

`REPLACE()`：替换字符串中的内容

REPLACE()函数用于替换字符串中的子串。

示例

1 2	SELECT REPLACE(email, 'example', 'newdomain') AS updated_email FROM employees;

此查询将替换email中的example为newdomain。

日期和时间函数（Date and Time Functions）

日期和时间函数用于处理日期和时间类型的数据，帮助我们进行时间计算、格式化等操作。

`DATE()`：提取日期部分

DATE()函数从日期时间值中提取出日期部分（年、月、日）。

示例

1 2	SELECT DATE(hire_date) AS hire_date_only FROM employees;

此查询将返回员工的入职日期，只保留日期部分（去掉时间部分）。

`TIME()`：提取时间部分

TIME()函数从日期时间值中提取出时间部分（小时、分钟、秒）。

示例

1 2	SELECT TIME(hire_date) AS hire_time FROM employees;

此查询将返回员工入职时间部分。

`DATEPART()`：提取日期的特定部分

DATEPART()函数用于提取日期中的特定部分（如年、月、日、小时等）。

示例

1 2	SELECT DATEPART(YEAR, hire_date) AS hire_year FROM employees;

此查询将返回员工入职年份。

`DATEADD()`：添加时间间隔

DATEADD()函数用于在日期上添加指定的时间间隔（如天、月、年）。

示例

1 2	SELECT DATEADD(MONTH, 1, hire_date) AS next_month_hire FROM employees;

此查询将返回员工入职日期的一个月后的日期。

`DATEDIFF()`：计算两个日期之间的差异

DATEDIFF()函数计算两个日期之间的差异（通常是天数）。

示例

1 2	SELECT DATEDIFF(CURRENT_DATE, hire_date) AS days_since_hired FROM employees;

此查询将返回员工入职日期到今天的天数差。

数值函数（Numeric Functions）

数值函数用于对数值数据进行操作，常见的有四舍五入、取整等函数。

`FLOOR()`：向下取整

FLOOR()函数将数字向下取整（即舍去小数部分，返回最接近的整数）。

示例

1 2	SELECT FLOOR(salary) AS rounded_salary FROM employees;

此查询将返回员工工资的小数部分被舍弃后的整数部分。

`CEILING()`：向上取整

CEILING()函数将数字向上取整（即将小数部分向上调整到下一个整数）。

示例

1 2	SELECT CEILING(salary) AS rounded_up_salary FROM employees;

此查询将返回员工工资的小数部分被向上取整后的值。

`ROUND()`：四舍五入

ROUND()函数将数字四舍五入到指定的小数位数。

示例

1 2	SELECT ROUND(salary, 2) AS rounded_salary FROM employees;

此查询将返回员工工资四舍五入到小数点后两位的结果。

`ABS()`：绝对值

ABS()函数返回数值的绝对值，即无论原值是正数还是负数，返回其正数值。

示例

1 2	SELECT ABS(balance) AS absolute_balance FROM accounts;

此查询将返回账户余额的绝对值。

小结

字符串函数：
- CONCAT()：连接多个字符串。
- LENGTH()：返回字符串的长度。
- UPPER()：将字符串转换为大写。
- LOWER()：将字符串转换为小写。
- SUBSTRING()：提取字符串的一部分。
- REPLACE()：替换字符串中的子串。
日期和时间函数：
- DATE()：提取日期部分。
- TIME()：提取时间部分。
- DATEPART()：提取日期的特定部分（如年、月、日）。
- DATEADD()：在日期上添加时间间隔。
- DATEDIFF()：计算两个日期之间的差异。
数值函数：
- FLOOR()：向下取整。
- CEILING()：向上取整。
- ROUND()：四舍五入。
- ABS()：绝对值。

视图和索引（Views and Indexes）

在 SQL 中，视图和索引是用来管理和优化数据库的两个重要概念。视图用于简化复杂查询，而索引用于提高查询效率。下面我们详细介绍这两个方面。

视图（Views）

视图是一个虚拟表，它通过SELECT查询结果定义，并不是物理存储的数据。视图使得复杂的查询操作变得简洁，并且可以重复使用查询逻辑。视图可以简化多表联合查询、聚合操作等。

创建视图（CREATE VIEW）

创建视图时，我们通常定义一个SELECT查询，视图的内容由这个查询的结果决定。

示例

CREATE VIEW employee_view AS
SELECT name, department_id, salary
FROM employees
WHERE salary > 50000;

这个视图employee_view将包含所有薪水大于 50,000 的员工的姓名、部门和薪水信息。你可以像查询表格一样查询视图。

1	SELECT * FROM employee_view;

修改视图（ALTER VIEW）

如果需要修改视图，可以直接重新定义视图的查询逻辑。不同的数据库管理系统可能有不同的方式，但大多数情况下，你需要先删除旧视图，然后创建新的视图。

示例

DROP VIEW IF EXISTS employee_view;

CREATE VIEW employee_view AS
SELECT name, department_id, salary, hire_date
FROM employees
WHERE salary > 50000;

这里我们删除了旧的视图，并且增加了hire_date字段。

删除视图（DROP VIEW）

如果不再需要某个视图，可以使用DROP VIEW语句删除它。

示例

1	DROP VIEW employee_view;

这将删除employee_view视图。

索引（Indexes）

索引是用于提高查询效率的数据结构。它通常用于加速SELECT查询，尤其是在大量数据的情况下。索引通过创建一个指向数据表中相关数据行的指针来加速数据检索。需要注意的是，虽然索引提高了查询效率，但它们也可能减慢数据插入、更新和删除操作。

创建索引（CREATE INDEX）

索引通常用于频繁查询的列。常见的做法是为WHERE、JOIN、ORDER BY、GROUP BY等操作中经常使用的列创建索引。

示例

1	CREATE INDEX idx_department_id ON employees(department_id);

这将为employees表中的department_id列创建一个索引。当你在查询中使用department_id时，查询会更高效。

唯一索引（Unique Index）

如果你希望索引列的值唯一，可以使用唯一索引。它不仅加速查询，还确保列中的值唯一。

示例

1	CREATE UNIQUE INDEX idx_unique_email ON employees(email);

这个索引确保email列中的每个值都是唯一的，并且提高查询效率。

删除索引（DROP INDEX）

如果索引不再需要，或者为了优化性能，你可以删除它。

示例

1	DROP INDEX idx_department_id ON employees;

这将删除employees表中的idx_department_id索引。

索引的优化技巧

选择合适的列：创建索引时，要选择查询中常用的列作为索引。通常，索引适用于主键、外键、WHERE子句中的条件列以及JOIN操作的列。
避免过度索引：过多的索引会导致插入、更新和删除操作变慢，因为每次修改数据时，相关的索引也需要更新。
复合索引：当查询涉及多个列时，可以使用复合索引（多个列的索引）。复合索引可以显著提高多列查询的效率。

示例
1
CREATE INDEX idx_name_department ON employees(name, department_id);
这个索引将同时加速根据name和department_id的查询。
覆盖索引：如果索引包含查询所需的所有列，那么数据库可以直接从索引中获取数据，而无需访问实际的表数据。这样的索引称为覆盖索引。

例如，如果你只查询name和department_id，并且这些列已经在索引中，查询可以直接使用索引，而不必访问表数据。

小结

视图（Views）：
- 创建视图：通过CREATE VIEW语句创建虚拟表，简化复杂的查询。
- 修改视图：一般通过删除旧视图并创建新视图来修改。
- 删除视图：使用DROP VIEW删除不再需要的视图。
索引（Indexes）：
- 创建索引：通过CREATE INDEX为表格中的列创建索引，常用于加速查询。
- 唯一索引：确保列中的值唯一，并加速查询。
- 删除索引：使用DROP INDEX删除不再需要的索引。
- 优化索引使用：选择合适的列、避免过度索引、使用复合索引和覆盖索引来优化查询性能。

事务管理（Transaction Management）

事务（Transaction）是数据库操作的基本单位，通常指一系列数据库操作，它们要么全部执行，要么全部不执行，以确保数据的一致性和完整性。在 SQL 中，事务的管理通常遵循ACID特性，并通过一系列操作（如BEGIN、COMMIT、ROLLBACK等）进行控制。

事务的 ACID 特性

ACID 是四个用于描述事务特性的首字母缩写：

A - 原子性（Atomicity）

原子性意味着事务中的所有操作要么全部成功执行，要么全部失败回滚。即使在事务执行过程中发生了错误，数据库也会回到事务开始之前的状态。

示例

假设我们有一个转账操作，涉及从账户 A 转账到账户 B。如果其中一个步骤失败，整个转账过程就会失败，数据库会回到初始状态。

C - 一致性（Consistency）

一致性确保事务执行前后，数据库的状态始终符合定义好的规则或约束。例如，银行账户的余额不能为负数。

在事务开始之前和结束之后，数据库的状态应该是合法的。

I - 隔离性（Isolation）

隔离性确保事务在执行过程中不会受到其他事务的干扰。每个事务都有自己的工作空间，其他事务在它未提交之前看不到它的中间结果。

SQL 提供了多种隔离级别来控制事务之间的隔离性，后面会详细介绍。

D - 持久性（Durability）

持久性确保事务一旦提交，数据库中的更改会永久保存，不会因为系统崩溃或其他问题而丢失。

即使系统发生故障，一旦事务提交成功，所做的更改会持久保存在数据库中。

事务的隔离级别和操作

事务的隔离级别

SQL 提供了四种事务隔离级别，用来控制事务之间的交互行为。不同的隔离级别在性能和数据一致性方面做出不同的权衡。

读未提交（Read Uncommitted）：
- 允许事务读取其他事务未提交的数据（脏读）。这种隔离级别性能最好，但数据一致性最差。
示例
事务 A 修改了某数据，但未提交，事务 B 可以读取该数据，导致脏读。
读已提交（Read Committed）：
- 事务只能读取其他事务已经提交的数据（没有脏读）。这是大多数数据库的默认隔离级别。
示例
事务 A 修改了某数据并提交后，事务 B 才能读取数据。如果事务 A 未提交，事务 B 无法读取该数据。
可重复读（Repeatable Read）：
- 保证在事务执行期间，读取的数据不会被其他事务修改。虽然避免了脏读和不可重复读问题，但可能会发生幻读（即事务读取的行数发生变化）。
示例
事务 A 读取了某行数据，事务 B 不能修改该行直到事务 A 提交。如果事务 A 重复读取相同数据，会得到相同的结果。
序列化（Serializable）：
- 这是最严格的隔离级别，事务执行时会像顺序执行一样，完全避免脏读、不可重复读和幻读，但会导致性能下降。
示例
事务 A 和事务 B 都想修改相同的数据，系统会按顺序执行，避免数据冲突。

事务操作（`BEGIN`、`COMMIT`、`ROLLBACK`）

在 SQL 中，事务的控制通过以下操作来管理：

**BEGIN**：开始一个新的事务。
- 在事务开始时，所有的操作将被视为一个整体，直到事务提交或回滚。
示例
1
BEGIN;
**COMMIT**：提交事务。
- 一旦COMMIT被执行，事务中的所有更改将永久保存到数据库中，所有其他事务都能看到这些更改。
示例
1
COMMIT;
**ROLLBACK**：回滚事务。
- 如果事务执行过程中发生错误，或者需要撤销事务中的所有操作，可以使用ROLLBACK来回滚事务。所有未提交的更改将被撤销，数据库恢复到事务开始之前的状态。
示例
1
ROLLBACK;

事务的示例

假设有一个银行系统，涉及从账户 A 向账户 B 转账的操作。我们希望保证这个操作的原子性，要么转账成功（提交），要么失败（回滚）。

示例：转账操作

BEGIN;

-- 从账户A扣除100元
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

-- 向账户B存入100元
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

-- 提交事务，保存更改
COMMIT;

如果在过程中发生错误（如余额不足等），可以回滚事务：

BEGIN;

-- 从账户A扣除100元
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';

-- 如果账户A余额不足，回滚事务
IF (SELECT balance FROM accounts WHERE account_id = 'A') < 0 THEN
  ROLLBACK;
ELSE
  -- 向账户B存入100元
  UPDATE accounts
  SET balance = balance + 100
  WHERE account_id = 'B';

  -- 提交事务，保存更改
  COMMIT;
END IF;

小结

ACID 特性：
- 原子性：事务内的所有操作要么全部成功，要么全部失败。
- 一致性：事务执行前后，数据库始终处于一致状态。
- 隔离性：事务在执行过程中不受其他事务干扰。
- 持久性：事务一旦提交，数据更改会永久保存。
事务隔离级别：
- 读未提交：允许脏读，性能最优。
- 读已提交：防止脏读，常见的默认级别。
- 可重复读：防止脏读和不可重复读，但可能发生幻读。
- 序列化：最严格的隔离级别，性能较低。
事务操作：
- **BEGIN**：开始一个事务。
- **COMMIT**：提交事务，保存所有操作。
- **ROLLBACK**：回滚事务，撤销所有操作。

存储过程和性能优化（Stored Procedures and Performance Optimization）

在 SQL 中，存储过程（Stored Procedures）和性能优化（Performance Optimization）是提高数据库操作效率和管理复杂查询的重要工具。下面我们详细介绍这两个主题。

查询优化技巧

SQL 查询优化是提高数据库性能的一个关键部分，涉及多个方面，如减少子查询、优化联接操作等。以下是一些常见的查询优化技巧：

减少子查询（Subquery Optimization）

子查询可以带来性能问题，尤其是在大量数据的情况下。通过适当的替换子查询为联接（JOIN）或使用其他优化技术，可以提高查询性能。

避免重复的子查询：如果子查询被多次引用，可以考虑将其提取为临时表或视图。

示例

使用子查询：

1
2
3

SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

优化：
将子查询转换为JOIN操作：

SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location = 'New York';

这样可以避免多次执行子查询，从而提高查询效率。

优化联接操作（JOIN Optimization）

使用合适的索引：对于JOIN操作，确保联接的列（通常是外键列）有索引，以加速查找过程。
**避免不必要的JOIN**：不要在查询中加入不需要的数据表。仅选择查询中实际需要的表格，减少计算负担。

示例

如果在查询中不需要employees表的所有列，可以只选择需要的列：

1
2
3

SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

使用`EXISTS`替代`IN`（优化子查询）

在某些情况下，EXISTS操作符的性能优于IN，尤其是当子查询返回大量数据时。

示例

原查询使用IN：

1
2
3

SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

优化：使用EXISTS：

1
2
3

SELECT name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location = 'New York');

EXISTS会在找到第一个匹配的行时就停止查询，因此通常比IN更高效。

使用合适的聚合函数

如果你需要执行聚合操作（如SUM()、COUNT()、AVG()等），确保你使用的列上有索引，尤其是在GROUP BY或HAVING条件中使用的列。

动态 SQL 和递归查询

动态 SQL（Dynamic SQL）

动态 SQL是指在运行时构造和执行 SQL 查询的能力。它允许你在程序中动态地生成和执行 SQL 语句，通常用于那些在编译时无法预知的查询。

用途：动态 SQL 通常用于处理不同条件、表名或列名的查询，或者当需要动态修改查询语句时非常有用。
实现：在 SQL 中，动态 SQL 通常通过EXEC或sp_executesql等机制执行。

示例：

1
2
3

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'SELECT name FROM employees WHERE department_id = @dept_id'
EXEC sp_executesql @sql, N'@dept_id INT', @dept_id = 3;

在这个例子中，SQL 查询被动态生成并通过sp_executesql执行。这样，你可以动态地根据输入的条件执行查询。

递归查询（Recursive Queries）

递归查询是指查询中自己调用自己，通常用于处理层级数据或树形结构，如组织架构、文件目录等。SQL 提供了公共表达式（CTE）来实现递归查询。

WITH子句：递归查询通常使用WITH子句定义递归公共表达式（CTE），然后在查询中递归地引用它。

示例

假设我们有一个employees表，其中包含员工和他们的经理，表示层级关系。我们希望查询某个经理下的所有员工，包括直接和间接下属。

WITH RecursiveCTE AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL  -- 根节点（最高层级经理）

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN RecursiveCTE r ON e.manager_id = r.id
)
SELECT * FROM RecursiveCTE;

在这个例子中，递归公共表达式RecursiveCTE从顶级经理（manager_id IS NULL）开始，然后递归地查找所有直接或间接下属。UNION ALL用于将结果合并到一起。

递归查询的优化

递归查询可能会导致性能问题，尤其是在大规模数据的情况下。以下是一些优化技巧：

限制递归深度：通过MAXRECURSION限制递归的最大层数。
1
OPTION (MAXRECURSION 10);
这会限制查询最多递归 10 次，以防止无限递归。
使用索引：确保递归查询中涉及的列（如manager_id）有适当的索引，以加速递归过程。

存储过程（Stored Procedures）

存储过程是预编译的 SQL 语句集合，可以在数据库中定义并多次执行。它通常用于封装复杂的操作，避免重复代码，增加查询的可维护性和性能。

创建存储过程（CREATE PROCEDURE）

存储过程可以包含输入参数、输出参数，并且可以执行多条 SQL 语句。它通过CREATE PROCEDURE语句创建。

示例

CREATE PROCEDURE GetEmployeeInfo (@emp_id INT)
AS
BEGIN
    SELECT name, department_id, salary
    FROM employees
    WHERE id = @emp_id;
END;

执行存储过程（EXECUTE）

存储过程创建后，可以通过EXEC或EXECUTE来调用执行。

示例

1	EXEC GetEmployeeInfo @emp_id = 3;

存储过程的性能优化

避免使用游标：尽量避免使用游标（CURSOR），因为它们通常会导致性能问题。尽可能用JOIN或SET操作代替游标。
使用参数化查询：通过传递参数来执行存储过程，避免在 SQL 中拼接字符串，这有助于提高性能和安全性。
避免重复查询：如果存储过程内有重复的查询，可以考虑优化逻辑或使用临时表、变量等手段来减少查询次数。

小结

查询优化技巧：
- 减少子查询，使用联接（JOIN）优化查询。
- 优化联接操作，通过合理的索引和选择性减少不必要的联接。
- 使用EXISTS替代IN，避免子查询中的性能瓶颈。
- 使用合适的聚合函数和索引来加速查询。
动态 SQL 和递归查询：
- 动态 SQL 用于在运行时构建和执行查询，适用于灵活的查询需求。
- 递归查询通过 CTE（WITH子句）处理层级数据，如员工和经理关系。
存储过程：
- 存储过程是一组预编译的 SQL 语句，能够提高代码的可维护性和执行性能。
- 优化存储过程时，应避免使用游标，减少重复查询，并利用参数化查询提高性能。

Learning SQL

https://1uciuszzz.github.io/2025/02/25/learning-sql/

作者

1uciuszzz

发布于

2025-02-25

更新于

2025-02-25

许可协议

#sql

Learning SQL

SQL 基础语法

数据类型（Data Types）

运算符（Operators）

SQL 关键字（Keywords）

常用的 SQL 语句

SELECT 查询语句

INSERT INTO 插入数据

UPDATE 更新数据

DELETE 删除数据

数据定义语言（DDL）

CREATE 创建数据库或表格

ALTER 修改表格

DROP 删除表格或数据库

TRUNCATE 清空表格

小结

数据操作：聚合查询与数据约束

聚合查询（Aggregate Queries）

聚合函数的使用

GROUP BY 语句

HAVING 语句

数据约束（Data Constraints）

主键（Primary Key）

外键（Foreign Key）

唯一约束（Unique Constraint）

非空约束（NOT NULL）

检查约束（CHECK Constraint）

小结

联接查询（JOIN Queries）

各种连接方式

INNER JOIN（内连接）

示例：

LEFT JOIN（左外连接）

示例：

RIGHT JOIN（右外连接）

示例：

FULL OUTER JOIN（全外连接）

示例：

自连接（Self Join）

示例

交叉连接（CROSS JOIN）

示例

小结

子查询（Subqueries）

子查询类型

标量列子查询（Scalar Subquery）

示例

行子查询（Row Subquery）

示例

表子查询（Table Subquery）

示例

嵌套子查询与相关子查询

嵌套子查询（Nested Subquery）

示例

相关子查询（Correlated Subquery）

示例

小结

SQL 高级函数

字符串函数（String Functions）

CONCAT()：连接字符串

示例

LENGTH()：字符串长度

示例

UPPER()：将字符串转换为大写

示例

LOWER()：将字符串转换为小写

示例

SUBSTRING()：提取字符串的一部分

示例

REPLACE()：替换字符串中的内容

示例

日期和时间函数（Date and Time Functions）

DATE()：提取日期部分

示例

TIME()：提取时间部分

示例

DATEPART()：提取日期的特定部分

示例

DATEADD()：添加时间间隔

示例

`SELECT` 查询语句

`INSERT INTO` 插入数据

`UPDATE` 更新数据

`DELETE` 删除数据

`CREATE` 创建数据库或表格

`ALTER` 修改表格

`DROP` 删除表格或数据库

`TRUNCATE` 清空表格

`GROUP BY` 语句

`HAVING` 语句

`INNER JOIN`（内连接）

`LEFT JOIN`（左外连接）

`RIGHT JOIN`（右外连接）

`FULL OUTER JOIN`（全外连接）

`CONCAT()`：连接字符串

`LENGTH()`：字符串长度

`UPPER()`：将字符串转换为大写

`LOWER()`：将字符串转换为小写

`SUBSTRING()`：提取字符串的一部分

`REPLACE()`：替换字符串中的内容

`DATE()`：提取日期部分

`TIME()`：提取时间部分

`DATEPART()`：提取日期的特定部分

`DATEADD()`：添加时间间隔

`DATEDIFF()`：计算两个日期之间的差异

`FLOOR()`：向下取整

`CEILING()`：向上取整

`ROUND()`：四舍五入

`ABS()`：绝对值

事务操作（`BEGIN`、`COMMIT`、`ROLLBACK`）

使用`EXISTS`替代`IN`（优化子查询）