基础知识

MySQL是一个开源的关系型数据库管理系统（RDBMS），它广泛用于Web应用程序的后台数据存储。

MySQL实操

MySQL官网（国区）

查看初始密码

通过管理员命令进入.\MySQL\MySQL Server 8.0\bin文件夹下，使用命令查看密码：

1

mysqld --initialize --console

运行服务器：

1

mysqld --console

重置密码：

1

ALTER USER 'your_user'@'localhost' IDENTIFIED BY 'your_new_password';

创建用户：

1

CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'yourpassword';

给予该用户全部权限：

1

GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'localhost' WITH GRANT OPTION;

使用VSCode中的插件进行连接登陆服务器

SQL常用语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 创建数据库
CREATE DATABASE test;
# 创建和管理表
USE test;
CREATE TABLE T1 (
 ID INT1, 
 NAME VARCHAR(100),
 LEVEL INT,
 EXP INT,
 GOLD DECIMAL(10,2)
)
DESC t1;
ALTER TABLE t1 MODIFY COLUMN name VARBINARY(200);
DESC t1;
ALTER TABLE t1 RENAME COLUMN name TO nick_name;
DESC t1;
ALTER TABLE t1 ADD COLUMN last_login DATETIME;
ALTER TABLE t1 DROP COLUMN last_login;
DROP TABLE t1;
# 数据的增删改查
INSERT into t1 (id,name) VALUES (1,'zhangsan');
SELECT * from t1;
INSERT into t1 (id,name) VALUES (2,'lisi'),(3,'wangwu');
ALTER TABLE t1 MODIFY LEVEL int DEFAULT 1;
INSERT INTO t1 (id,name) VALUES (4,'zhaoliu');
SELECT * FROM t1;
UPDATE t1 SET level = 1 WHERE name = 'wangwu';
UPDATE t1 SET level = 1 ;
SELECT * FROM t1;
UPDATE t1 SET exp=0,gold=0;
SELECT * FROM t1;
DELETE FROM t1 WHERE gold = 0;

数据的导入方法

安装好MySQL之后，进入MySQL的命令行界面并按照下面的步骤来创建一个数据库：

1. 打开终端，输入如下命令来登录MySQL：

1

mysql -u your_username -p

将your_username替换为你的MySQL用户名，然后会提示你输入MySQL密码。

登录成功后，你应该会看到MySQL的提示符：

1

mysql>

1. 输入如下命令来创建一个名为game的数据库：

1

CREATE DATABASE game;

1. 你可以通过如下命令来确认数据库是否创建成功：

1

SHOW DATABASES;

1. 退出MySQL的命令行界面：

1

exit;

1. 在终端中执行如下命令来导入数据：

1

mysql -u root -p game < game.sql

常用语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

USE test;
SELECT * FROM player WHERE level > 1; 
SELECT * FROM player WHERE level > 1 AND level < 5; 
SELECT * FROM player WHERE exp > 1 AND exp < 5; 
SELECT * FROM player WHERE level > 1 AND level < 5 OR exp > 1 AND exp < 5; 

SELECT * FROM player WHERE level > 1 AND (level < 5 OR exp > 1) AND exp < 5;
SELECT * FROM player WHERE level IN (1,3,5);
SELECT * FROM player WHERE level BETWEEN 1 AND 10;
SELECT * FROM player WHERE level>=1 AND level<=10;
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;
SELECT * FROM player WHERE name LIKE '王%';
SELECT * FROM player WHERE name LIKE '%王%';
SELECT * FROM player WHERE name LIKE '王_';
SELECT * FROM player WHERE name LIKE '王__';
SELECT * FROM player WHERE name REGEXP '王.$';#正则表达式
SELECT * FROM player WHERE name REGEXP '[王张]';
SELECT * FROM player WHERE name REGEXP '王|张';
# 空值
SELECT * FROM player WHERE email is NULL;
SELECT * FROM player WHERE email is NULL or email = '';
SELECT * FROM player ORDER BY level DESC;
SELECT * FROM player ORDER BY level DESC, exp ASC;
SELECT * FROM player ORDER BY 5 DESC;
# 聚合函数 AVG COUNT MAX MIN SUM
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;
SELECT sex,count(*) FROM player GROUP BY sex;
SELECT level,count(level) FROM player GROUP BY level;
SELECT level,count(level) FROM player GROUP BY level HAVING COUNT(level)>4;
SELECT level,count(level) FROM player GROUP BY level HAVING COUNT(level)>4 ORDER BY COUNT(level) DESC;
SELECT SUBSTR(name,1,1),count(substr(name,1,1)) FROM player 
GROUP BY SUBSTR(name,1,1) 
HAVING COUNT(SUBSTR(name,1,1))>=5
ORDER BY COUNT(SUBSTR(name,1,1)) DESC
LIMIT 3,3;
SELECT SUBSTR(name,1,1),count(substr(name,1,1)) FROM player 
GROUP BY SUBSTR(name,1,1) 
ORDER BY COUNT(SUBSTR(name,1,1)) DESC
LIMIT 3,3;
SELECT DISTINCT sex FROM player;
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION # ALL
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

子查询

1
2
3
4
5
6
7
8
9
10
11
12

SELECT AVG(level) FROM player;
SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);
SELECT level,round((SELECT AVG(level) FROM player)) as average,
level - ROUND((SELECT AVG(level) FROM player)) as diff
FROM player;
CREATE TABLE new_player AS
SELECT * FROM player WHERE level < 5;
select * from new_player;
select * from player where level between 6 and 10;
CREATE TABLE new_player SELECT * FROM player WHERE level < 5;
select * from new_player;
SELECT EXISTS(select * FROM player WHERE level > 10)

表关联

1
2
3
4
5
6
7
8
9
10

DESC equip
SELECT * FROM player
INNER JOIN equip
ON player.id = equip.player_id;
SELECT * FROM player
LEFT JOIN equip
ON player.id = equip.player_id;
SELECT * FROM player
RIGHT JOIN equip
ON player.id = equip.player_id;

索引

1. 创建索引：

   • 创建表时添加索引：

     1 2 3 4 5

     sqlCopy codeCREATE TABLE table_name ( column1 datatype, column2 datatype, INDEX index_name (column1, column2) );

   • 或者在已存在的表上添加索引：

     1 2	sqlCopy code CREATE INDEX index_name ON table_name (column1, column2);

2. 查看表的索引：

   1 2	sqlCopy code SHOW INDEX FROM table_name;

3. 删除索引：

   1 2	sqlCopy code DROP INDEX index_name ON table_name;

4. 唯一索引：

   • 创建唯一索引：

     1 2	sqlCopy code CREATE UNIQUE INDEX index_name ON table_name (column1, column2);

   • 在创建表时指定唯一索引：

     1 2 3 4 5

     sqlCopy codeCREATE TABLE table_name ( column1 datatype, column2 datatype, UNIQUE INDEX index_name (column1, column2) );

5. 全文索引（仅适用于MyISAM引擎）：

   • 创建全文索引：

     1 2	sqlCopy code CREATE FULLTEXT INDEX index_name ON table_name (column1, column2);

6. 删除表的所有索引：

   1 2	sqlCopy code ALTER TABLE table_name DROP INDEX ALL;

7. 优化表（重新建立索引）：

   1 2	sqlCopy code OPTIMIZE TABLE table_name;

8. 查看索引的统计信息：

   1 2	sqlCopy code SHOW INDEX STATISTICS FROM table_name;

视图

1. 创建视图：

   1 2 3 4

   sqlCopy codeCREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

2. 查看所有视图：

   1 2	sqlCopy code SHOW FULL TABLES WHERE Table_type = 'VIEW';

3. 查看视图的定义：

   1 2	sqlCopy code SHOW CREATE VIEW view_name;

4. 更新视图：

   1 2 3 4

   sqlCopy codeCREATE OR REPLACE VIEW view_name AS SELECT new_column1, new_column2, ... FROM new_table_name WHERE new_condition;

5. 删除视图：

   1 2	sqlCopy code DROP VIEW view_name;

6. 使用视图进行查询：

   1 2	sqlCopy code SELECT * FROM view_name;

7. 更新视图基表： 视图可以被用来更新其基表，前提是视图的选择列表中仅包含一个基表的列，而且这些列没有使用聚合函数、DISTINCT、GROUP BY、HAVING子句或 UNION。

   1 2 3 4 5

   sqlCopy codeCREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition WITH CHECK OPTION;

   上述的 WITH CHECK OPTION 语句确保只有满足视图定义的条件的行才能被插入或更新。

8. 修改视图的权限：

   1 2	sqlCopy code GRANT SELECT ON database_name.view_name TO 'user'@'host';

SQL学习

Learn SQL for Free (sqlzap.com)

第一课

什么是 SQL？

SQL 代表结构化查询语言，是一种标准化的编程语言，用于管理 关系数据库并对该数据执行各种操作。

SQL 简史

SQL 的历史可以追溯到 1970 年代，当时它由 Donald D. Chamberlin 和 Raymond F. Boyce 在 IBM 开发。 SQL 最初称为 SEQUEL（结构化英语查询语言），旨在操作和检索存储在 IBM 的原始关系数据库系统 System R。在 1970 年代后期，SEQUEL 更名为 SQL，以避免 违反国际商标法。

1986 年，SQL 成为 ANSI 标准，后来在 1987 年成为 ISO 标准。这有助于 SQL 巩固其作为 数据库查询和管理的主要语言。多年来，SQL 不断发展，推出了新版本和 正在添加的功能。尽管有新版本，但核心语言一直保持稳定，这极大地促进了 它被广泛采用。

如今，所有主要的关系数据库管理系统都支持 SQL，包括 Oracle、Microsoft SQL Server、 MySQL 和 PostgreSQL。数据库管理员和开发人员在编写数据集成时广泛使用它 脚本。SQL 语法的简单性和表现力一直是其成功不可或缺的一部分。

SQL 的核心允许您对数据库数据执行四种广泛的操作：

1. 查询 - 使用 SELECT 语句选择和筛选数据以提取特定见解。
2. Insert - 使用 INSERT 语句将新数据记录添加到数据库。
3. Update - 使用 UPDATE 语句修改数据库中的现有记录。
4. Delete - 使用 DELETE 语句从数据库中删除记录。

除了这些基本功能之外，SQL 还允许您管理数据库和数据库对象（如表）、执行 transaction control 和实施访问控制。内置函数和复杂运算符扩展了 语言。

关系数据库在应用程序中无处不在，这意味着 SQL 技能在各行各业都受到高度重视。 无论您是开发人员、数据分析师、数据科学家还是管理员，了解 SQL 都可以帮助您有效地访问 以及使用数据。鉴于 SQL 在整个数据管理领域的主导地位，它可能仍然是必不可少的 在可预见的未来处理数据的技能。

关系数据库

关系数据库已成为跨许多应用程序和系统组织和查询数据的主要方式。 在关系数据库中，数据存储在称为表的关系中。每个表由行和列组成，其中 表示记录的行和表示该记录属性的列。例如，Customers 表可能 具有 CustomerID、Name、Address 和 Phone Number 等列。每行都是具有唯一 ID 的客户记录，并且 name、address 和 phone number 属性的值。

关系数据库中的表之间有关系。通常，关系是使用外键形成的 和主键。一个表中的外键是指另一个表中的主键。例如，Orders 表 可能具有引用 Customers 表中主键的 CustomerID 外键。这允许检索 customer’s orders 的订单。这些关系允许对 数据库表的互连网络。

关系数据库提供了几个关键优势：

有序的结构：表格架构和表之间定义的关系可实现高效存储和 查询。 开发人员可以直观地理解结构。

灵活性：可以单独添加或更改表以更改数据模型。可以定义新的关系 而不会破坏现有的 VPN。

数据完整性：主键、外键和约束等功能可确保数据的有效性和一致性。 通过标准化避免重复。

可扩展性：数据库可以扩展以支持更大的数据集，同时通过分区和 复制。

可访问性：标准化的 SQL 语言提供了与关系数据交互的简单而强大的方法。 这 还支持底层数据库系统的抽象。

事务支持：并发控制、多语句事务和回滚机制有助于维护 数据 一致性。

安全性：可以按表或列应用精细的用户权限以控制访问。网络加密 协议 还要防止窃听。

备份：成熟的备份和恢复工具允许定期快照以防止数据丢失。

这些功能使关系模型适用于大型企业数据库系统，其中 复杂性、性能和完整性至关重要。表格结构的简单性也使其可用于 个人数据库和较小规模的项目。替代 NoSQL 数据库模型可能更适合某些特定的 数据类型，但关系数据库仍然是一般数据管理的多功能、值得信赖的选择域。

第二课 SQL 数据库架构

数据库架构是表示整个数据库的逻辑视图的框架结构。它定义了数据的组织方式以及它们之间的关系如何关联。它制定了要应用于数据的所有约束。

架构定义表、字段、关系、视图、索引、包、过程、函数、队列、触发器、类型、序列、具体化视图、同义词、数据库链接、目录、XML 架构和其他元素。

拥有精心规划的数据库架构的一些优点是：

• 它有助于防止数据冗余和不一致 - 架构通过实体完整性、引用完整性、域约束等强制实施数据完整性约束，从而确保数据的准确性和一致性。这将删除数据中的异常。
• 更好的性能 - 规范化的数据库可以最大限度地减少数据重复和高效存储。索引还有助于更快地检索和访问数据。这可以提高整体性能。
• 可扩展性和灵活性 - 良好的架构有助于数据库的扩展和未来增长，以容纳更多数据、用户或应用程序，而不会影响性能或可用性。
• 维护变得容易 - 解决问题、修改、更新或删除元素（如添加新列、更改数据类型、建立关系等）很容易。这使得维护更简单。
• 多用户访问控制 - 可以在数据库对象上定义复杂的访问权限、用户角色、读/写权限，以允许共享访问。
• 备份和还原 - 架构允许在崩溃或故障情况下顺利备份和还原，而不会丢失数据完整性。

因此，数据库架构构成了构建整个数据库系统的基础。它变得非常关键，特别是对于涉及大量数据和复杂事务的大型企业应用程序;其中，强大的结构化架构是长期成功的关键。因此，在开发 schema 时，应正确应用原子性、规范化等 schema 设计原则。

数据库 SQL 转储

SQL 转储 是一个文件，其中包含表结构和/或数据库中的数据的记录。它通常由数据库管理系统 （DBMS） 生成，允许您在数据库或服务器之间传输数据并进行备份。

需要了解的有关 SQL 转储 的一些关键事项：

• 仅结构转储 - 仅包含表和视图定义、索引、触发器等。不包含任何数据。用于在数据库之间复制架构。
• 仅数据转储 - 仅包括表数据行。不包括表结构。适合在兼容数据库之间迁移数据。
• 完整数据库转储 - 包括数据库结构和数据。允许您从头开始完全重新创建数据库。最完整的备份。
• 自定义转储 - DBMS 工具允许您根据需要自定义和导出特定表、结果集等。

转储中使用的 SQL 格式与大多数流行的 DBMS（如 MySQL、PostgreSQL、SQLite、Oracle、SQL Server 等）兼容。这允许在它们之间导入和迁移数据。

转储可以通过 DBMS 导入实用程序导入以重新创建数据库，或用于使用各种工具分析数据库外部的数据。自动计划转储可帮助 DBA 定期备份以进行灾难恢复。

总之，SQL 转储将数据库内容的可移植副本创建为文件，对于备份、传输和分析非常有用。

https://sqlzap.com/assets/data/dump.sql

第三课

SQL Basic 语法

SQL 语句和对象中的区分大小写

SQL 与其他编程语言不同的关键因素之一是它通常不区分大小写。对于 SELECT、FROM、WHERE 等关键字，大小写无关紧要：

1

SeLeCt * FrOm Books WhErE Publish_Date > '2020-01-01';

此查询搜索书籍的运行方式与以下各项相同：

1

select * from books where publish_date > '2020-01-01';

因此，SQL 允许灵活地格式化语句关键字。CamelCase 或 lowercase 等大小写约定通常只是为了提高可读性。

字符串比较

使用 WHERE 子句或 JOIN 条件比较 SQL 中的字符串值时，默认行为通常不区分大小写。例如，’john’ 和 ‘John’ 将被视为相等。但是，此行为可能因数据库系统和排序规则设置而异。

以分号终止的语句

在 SQL 中，每个语句都必须以分号终止;象征。

如果没有分号，数据库将无法解析完整的语句：

1
2

SELECT * FROM books WHERE price > 10 ORDER BY publish_date DESC
SELECT * FROM authors

这将出错。分号表示结束：

1
2

SELECT * FROM books WHERE price > 10 ORDER BY publish_date DESC;
SELECT * FROM authors;

现在，这两个语句都将正确执行。

缩进和换行符

SQL 语句可能会随着多个子句和嵌套而增长相当长。正确的缩进和换行符是可读性的关键。

例如：

1
2
3
4
5
6
7
8

SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer_name, b.title, r.rating
FROM customers c
LEFT JOIN reviews r
    ON r.customer_id = c.customer_id
LEFT JOIN books b
   ON b.book_id = r.book_id
WHERE r.rating IS NOT NULL
ORDER BY c.first_name, c.last_name;

缩进的 JOIN 子句比塞满一行要清晰得多。一致的缩进在修改复杂查询时也有帮助。

注释代码

应始终包含记录 SQL 代码部分或解释逻辑的注释：

1
2
3
4
5
6

SELECT c.first_name, o.order_date, o.total_amount
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-04-30'
ORDER BY c.first_name;

上述注释可帮助未来的开发人员理解代码段的用途，而不必自己破译复杂的逻辑。

第四课

SQL 数据类型

正确构建数据对于构建高效、准确的数据库驱动系统至关重要。SQL 提供了各种专用数据类型，而不仅仅是纯文本和数字，这些数据类型可实现强大的数据处理。了解可用的选项有助于构建最佳数据库架构。

字符串数据类型

基于文本的信息使用 CHAR 和 VARCHAR 类型存储在 SQL 中。

CHAR（n） 以 n 个字符的精确长度存储字符串。任何未使用的字符槽都用空格填充：

1
2
3
4
5

CREATE TABLE Users (
    Username CHAR(50)
);

INSERT INTO Users VALUES ('user123'); -- Stores as 'user123' plus 32 spaces

VARCHAR（n） 存储最多 n 个字符的可变长度文本。没有空白填充，因此它只使用必要的空间：

1
2
3
4
5

CREATE TABLE Posts (
    Title VARCHAR(100)
);

INSERT INTO Posts VALUES ('Hello World'); -- Stores just the 11 characters

在灵活性方面，VARCHAR 通常优于 CHAR。像 NVARCHAR（n） 和 NCHAR（n） 这样的 Unicode 等效项也可用于国际数据。

数值数据类型

整数使用 INT 类型存储。DECIMAL 存储精确的小数值，而 FLOAT/DOUBLE 存储科学精度数：

1
2
3
4
5
6
7

CREATE TABLE Products (
    Price DECIMAL(8,2), -- 999999.99
    Stock INT, -- Up to 2 billion
    Weight FLOAT -- Fractional scientific notation
);

INSERT INTO Products VALUES (5.99, 105, 1.05986);

我们为确切需要的范围指定精度和小数位数。这优化了存储和计算。

日期和时间数据类型

SQL 具有日期、时间、时间戳和间隔的专用类型：

1
2
3
4
5
6
7
8
9

CREATE TABLE Events (
   `Date` DATE, -- 2023-12-25
   StartTime TIME, -- 12:30:00
   Finish DATETIME -- 2023-12-25 12:30:00
);

INSERT INTO Events
VALUES
   ('2023-12-25', '12:30', '2023-12-25 12:30');

标准 YYYY-MM-DD 格式可避免不同区域日期顺序的歧义。

布尔类型和二进制类型

BOOLEAN 类型存储简单的 true/false 值。BLOB 和 BINARY 类型包含不透明的二进制数据，如图像、文件等：

1
2
3
4
5
6

CREATE TABLE Users (
    AccountVerified BOOLEAN,
    ProfilePicture BLOB
);

INSERT INTO Users VALUES (true, LOAD_FILE('profile.png'));

也可以使用 Bitwise 标志，但 BOOLEAN 对 logic values 的读取更清晰。

自动增量列

自动递增列自动生成序列号。这提供了通常用于主键的唯一 ID：

1
2
3
4
5
6
7
8

CREATE TABLE Users (
    ID INT AUTO_INCREMENT,
    Name VARCHAR(100),
    PRIMARY KEY (ID)
);

INSERT INTO Users (Name) VALUES ('John'); -- ID populates as 1
INSERT INTO Users (Name) VALUES ('Mary'); -- ID populates as 2

特殊和自定义类型

许多数据库支持 XML、JSON、ARRAY 等特殊类型以及特定数据结构的自定义类型。这些处理针对数据语义定制的验证和处理。

选择正确的数据类型最初可能看起来很乏味。但是，优化存储、提高数据质量和简化逻辑的长期好处证明了所需的规划是合理的。

MySQL 中可用的常见数据类型：

第五课

了解 SQL SELECT 语句

SELECT 语句是从 SQL 数据库中检索数据的核心。它指定要 返回数据来源。

SELECT 语法

基本语法是：

1
2

SELECT column1, column2, ...
FROM table_name;

这将查询单个表中的特定列。

您可以根据需要选择任意数量的列，也可以使用 返回所有列。SELECT *

查询单个表

要查询单个表，请在 FROM 后面引用它：

1
2

SELECT id, name
FROM customers;

这将返回 customers 表中所有行的 id 和 name。

返回所有列

使用通配符返回所有列：*

1
2

SELECT *
FROM products;

这对于在开发过程中快速检查 table 的完整内容非常有用。

查询多个表

您可以通过用逗号分隔多个表来查询它们：

1
2

SELECT *
FROM customers, orders;

这将生成交叉联接并返回两个表之间的所有行组合。

总结

总之，SELECT 用于指定：

• 要返回的列
• 要查询的表
• 是返回所有列还是特定列

掌握编写 SELECT 语句的基础知识是在 SQL 中检索和分析数据的基础。

列别名

您可以在 SELECT 子句中为列名称设置别名，以使用更具可读性或描述性的名称返回它们：

1
2
3

SELECT id AS customer_id,
name AS customer_name
FROM customers;

现在，id 列以 customer_id 形式返回，name 以 customer_name 形式返回。

别名有助于：

• 为列提供更具描述性的名称
• 缩短长列名称
• 重命名具有相同名称的不同表中的列

要为列设置别名，请在名称后添加别名，以空格或 AS 关键字分隔。AS 是可选的。

要点：

• 别名重命名结果集中的列
• 提高可读性并消除列的歧义
• 添加到以空格或 AS 分隔的列名后

利用列别名等 SQL 功能，您可以调整和自定义从查询返回的数据。掌握 SELECT 的更精细细节可以使您成为更好的 SQL 开发人员。

第六课

使用条件约束查询

查询数据库表时，指定约束和条件允许您筛选结果集以仅包含满足定义条件的相关行。这可以提高查询性能，减少网络流量，并支持对数据进行更有用的分析。

在 SQL 中，WHERE 子句用于应用过滤器，将结果限制为满足条件逻辑的行。

基本语法和比较运算符

受约束的 SELECT 查询的基本语法是：

1
2
3

SELECT column1, column2,...
FROM mytable
WHERE condition1 AND/OR condition2

数值条件利用比较运算符，例如：

• =：等于
• ！= 或 <> ： 不等于
• <：小于
• >：大于
• <=：小于或等于
• >=：大于或等于

例如：

1
2
3

SELECT name, price
FROM products
WHERE price <= 100

检索价格为 100 或以下的产品的行。可以链接多个 AND/OR 条件以进一步筛选：

1
2
3

SELECT name, stock
FROM products
WHERE price < 50 AND stock > 10

现在，只有 50 美元以下但库存超过 10 件的商品才会被退回。

高级条件

SQL 为更高级的条件逻辑提供了额外的运算符：

BETWEEN 提供数值范围筛选：

1
2
3

SELECT name, qty
FROM sales
WHERE qty BETWEEN 10 AND 50

NOT BETWEEN 筛选条件（不包括范围）。

IN 和 NOT IN 检查值是否与集合中的任何元素匹配：

1
2
3

SELECT name
FROM products
WHERE category IN ('Appliances', 'Electronics')

对于文本，LIKE 和 NOT LIKE 使用通配符启用部分字符串匹配：

1
2
3

SELECT title
FROM books
WHERE title LIKE '%Cook%'

% 与任何 # 字符匹配。_ 匹配单个字符。

联接多个条件

您可以链接许多 AND/OR 运算符来生成复杂的条件逻辑：

1
2
3
4
5
6
7

SELECT *
FROM data
WHERE
  name LIKE 'A%' AND
  fruit IN ('Apple', 'Apricot') OR
  qty > 10 AND
  purchase_date BETWEEN '2020-01-01' AND '2020-12-31'

这将返回以 A 开头的名称的行，这些名称的水果是 Apple/Apricot，或者数量超过 10 个，并且是去年购买的。

通过利用比较运算符和条件链接来仔细限制结果集有助于优化 SQL 查询性能和分析价值。

Table of constraints

第七课

筛选和排序 SQL 结果

使用 DISTINCT 消除重复的行

查询 books 表时，使用不带 DISTINCT 的 SELECT 将为写了多本书的作者返回重复的书籍：

1

SELECT author_id FROM books;

输出：

1
2
3
4
5
6

1
1
2
3
3
...

添加 DISTINCT 关键字可消除以下重复项：

1

SELECT DISTINCT author_id FROM books;

输出：

1
2
3
4

1
2
3
...

现在，我们只需获取唯一的作者 ID。

使用 ORDER BY 对数据进行排序

ORDER BY 子句允许根据列值按升序或降序对查询结果进行排序。

• ASC 按从最低到最高的顺序对值进行排序
• DESC 按从高到低的顺序对值进行排序

例如，要检索按注册日期排序的客户记录：

1
2

SELECT * FROM customers
ORDER BY registration_date DESC;

输出：

默认情况下，ORDER BY 按升序排序，因此省略 DESC 关键字将首先打印最早的注册。

我们还可以链接多个列来确定排序顺序的优先级：

1
2

SELECT * FROM books
ORDER BY author_id, publish_date DESC;

现在，它主要按升序author_id排序。但对于同一作者的任何书籍，它将按降序publish_date排序。

使用 ASC 和 DESC 精细控制排序的能力有助于以最合乎逻辑的方式安排输出以进行分析。

使用 LIMIT 和 OFFSET 约束行

要仅选择 5 本最近出版的书籍：

1
2
3

SELECT * FROM books
ORDER BY publish_date DESC
LIMIT 5;

输出：

我们可以使用 OFFSET 实现结果的分页。就像获取下一个 5 个一样：

1

LIMIT 5 OFFSET 5;

执行顺序

排序后应用 LIMIT/OFFSET 等重要行为可确保在查询 orders 和 order_details 等大型表时进行适当的行约束。

第八课

使用联接查询多个表

掌握 JOINS 是利用关系数据库真正强大功能的关键。JOIN 子句使用逻辑条件组合多个表中的数据，从而支持更复杂的查询以获取业务见解。

内部联接

INNER JOIN 从两个表中选择匹配的行。例如，要匹配客户和订单数据：

1
2
3

SELECT c.Name, o.OrderTotal
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId

这将检索每个有订单的客户的客户名称和订单总额。ON 条件根据表的关系匹配表。

INNER 关键字是可选的 - 单独表示内部联接。JOIN

左/右外部联接

OUTER JOIN 返回一个表中的所有行以及另一个表中的匹配行。当一个表包含另一侧缺少的其他记录时，这非常有用。

LEFT JOIN 返回左表中的所有行：

1
2
3

SELECT c.Name, o.OrderTotal
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId

现在，每个客户都会被返回，即使是那些还没有订单的客户。RIGHT JOIN 根据正确的表反转逻辑。

完全外部联接

FULL OUTER JOIN 结合了来自左连接和右连接的逻辑。无论匹配项如何，都会返回两个表中的每一行。

如果一个表中有数据，但另一个表中没有数据，则不匹配的列为 NULL。

1
2
3
4

SELECT Employees.Name, Employees.Department, Sales.SalesAmount
FROM Employees
FULL OUTER JOIN Sales
  ON Employees.EmployeeID = Sales.EmployeeID

SELF JOINS

表可以 JOIN 到自身来查询层次结构，例如同一表中的员工及其经理：

1
2
3

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id

别名表名称

Table 别名缩短了查询并避免了 self join 冲突：

1
2
3

SELECT o.Product, c.Name AS Customer
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id

这里分别为 Order 和 Customer 表设置别名。o``c

筛选联接

ON 子句过滤哪些行在表之间匹配，而 WHERE 在联接后过滤：

1
2
3
4

SELECT *
FROM OrderDetails o
JOIN Products p ON o.ProductId = p.Id
WHERE p.Discontinued = 0

这将仅维护所有订单和当前产品之间的关系。JOIN + WHERE 组合功能强大地过滤多表数据。

学习通过 SQL JOINS 遍历关系可以将规范化表中的信息合并到单个结果集中！如果您有任何其他 JOIN 问题，请告诉我。

第九课

对数据进行分组和聚合函数

SQL 提供了强大的功能，用于对查询结果进行分组和计算聚合以分析和汇总数据。掌握这些技术可以解锁从数据库表中回答更有洞察力的问题的能力。

GROUP BY 子句

GROUP BY 子句按一列或多列对结果集进行分组。对于这些列中的每个非重复值，它将行聚合到单个摘要行中。

例如，要计算每个城市的客户数：

1
2
3

SELECT city, COUNT(*) AS num_customers
FROM customers
GROUP BY city;

这将按 “city” 列对所有行进行分组。对于每个唯一城市，它会输出一行，其中包含该城市的城市名称和行数。

我们可以按多个列进行分组以创建子分组 - 例如每个国家/地区每个城市的客户：

1
2
3

SELECT country, city, COUNT(*) AS num_customers
FROM customers
GROUP BY country, city;

现在，对于每个国家/地区和城市组合，我们都会得到一个计数。

聚合函数

与 GROUP BY 一起使用的常见聚合函数是：

COUNT（）：对组中的行进行计数。可以传递 或 列名。*

SUM（）：对数值列求和。

AVG（）：对数值列求平均值。

MAX（）/MIN（）：列中的最大值和最小值。

让我们看一些使用聚合来获取业务洞察的示例：

1
2

SELECT MAX(salary) AS highest_salary
FROM employees;

查找已支付的最高薪水。

1
2
3

SELECT department, SUM(sales) AS total_sales
FROM sales_data
GROUP BY department;

按部门获取总销售额。用于部门绩效比较。

HAVING 子句

GROUP BY 按列对行进行分组，而 HAVING 则根据聚合条件筛选行组。这是在聚合之后完成的，这与之前过滤的 WHERE 不同。

例如，要获得销售额超过 100,000 USD 的高绩效部门：

1
2
3
4

SELECT department, SUM(sales) AS total_sales
FROM sales_data
GROUP BY department
HAVING SUM(sales) > 100000;

HAVING 子句在计算总数后筛选组。这只留下销售额大于给定数量的部门。

您可以将 HAVING 与 WHERE 结合使用。WHERE 在聚合之前筛选单个行。然后，HAVING 在计算摘要后过滤整个组。

掌握 GROUP BY、COUNT/SUM/AVG/MIN/MAX 等聚合以及 HAVING 子句，可以从数据库表中打开广泛的报告和业务分析功能。凭借一点 SQL 知识，您可以获得非常强大的见解。

第十课

子查询和公共表表达式

SQL 提供了跨数据库表编写复杂分析查询的强大功能。掌握子查询和公共表表达式等技术可以回答更深层次的问题并更好地组织逻辑。

子查询

子查询是嵌套在另一个查询中的 SQL 查询。它首先运行并输出中间结果，外部查询在其逻辑中使用这些结果。

基本语法：

1
2
3
4
5
6
7

SELECT column
FROM table
WHERE column_name OPERATOR (
   SELECT column
   FROM table
   WHERE condition
);

这将首先单独运行子查询，并为其标量结果提供外部查询以进行比较。

子查询的常见用途：

存在性检查：查看是否存在满足条件的行：

1
2
3
4
5
6
7

SELECT *
FROM products
WHERE EXISTS (
   SELECT *
   FROM reviews
   WHERE product_id = products.id
);

行值比较：将值与子查询输出进行比较：

1
2
3
4
5
6

SELECT *
FROM products
WHERE price < (
   SELECT AVG(price)
   FROM products
);

查找比平均价格更便宜的产品。

行生成：将子查询输出为派生表：

1
2
3
4
5
6

SELECT *
FROM (
   SELECT id, name
   FROM brands
   WHERE country = 'US'
) AS us_brands;

进一步筛选和处理子查询输出。

公用表表达式 （CTE）

公共表表达式 （CTE） 定义可在以后的查询子句中引用的临时结果集。与子查询不同，CTE 可以是自引用的，同时只执行一次。

语法：

1
2
3
4
5
6

WITH cte_name AS (
   SELECT column
   FROM table
)
SELECT *
FROM cte_name;

在上面定义 CTE 后，这会像物理表一样查询 CTE cte_name。

CTE 有助于简化复杂的逻辑并在子句之间共享派生表。递归 CTE 也可以使用自引用对分层数据进行建模。

例如，要递归地将 salaries 向上汇总部门层次结构：

1
2
3
4
5
6
7
8
9
10

WITH emp_tree AS (
   SELECT *, 1 AS level
        FROM employees
   UNION ALL
   SELECT e.*, level + 1
     FROM employees e
        JOIN emp_tree et ON e.manager_id = et.id
)
SELECT SUM(salary)
FROM emp_tree;

掌握高级子查询和 CTE 技术可以构建雄辩的 SQL 以满足分析和报告需求。通过练习，构建复杂的数据转换变得直观。

第十一课

SQL 函数和表达式

SQL 提供了各种内置函数来转换和操作查询中的数据，而无需客户端后处理。表达式允许以类似的方式计算派生值。利用这些可以显著简化数据提取逻辑。

SQL 函数

常见功能类别：

文本函数

操作字符串数据：

1
2

SELECT UPPER(name), LENGTH(comments)
FROM reviews;

UPPER 大小写更改为大写，LENGTH 获取 length。

日期函数

日期处理：

1
2

SELECT DAY(created_date), DATEDIFF(NOW(), created_date)
FROM accounts;

DAY 获取日期中的日期，DATEDIFF 获取日期之间的差值。

集 料

我们之前看到了用于汇总的聚合，如 COUNT（）、AVG（）、SUM（）。

转换

在数据类型之间转换：

1
2

SELECT CAST(price AS VARCHAR)
FROM products;

将 price 转换为文本。

案例表达式

比较值并根据条件返回：

1
2
3
4
5

SELECT name,
  CASE WHEN price < 100 THEN 'Affordable'
       WHEN price < 500 THEN 'Expensive'
       ELSE 'Ultra-Premium' END AS category
FROM wines;

根据定价对葡萄酒进行分类。

表达 式

SQL 允许使用常见的数学和编程运算符（如 +、-、*、/、% 等）编写表达式。括号控制评估的顺序。

例如，要计算订单总额：

1
2

SELECT *, quantity * unit_price AS total_cost
FROM order_lines;

这样就无需在应用程序代码中计算线路成本。

布尔表达式可以利用 AND/OR/NOT 运算符和比较运算符（如 =、！=、> 等）：

1
2
3

SELECT *
FROM contests
WHERE end_date > NOW() AND status = 'Open';

查找当前打开的竞赛。

利用 LENGTH、DATE_DIFF 和表达式等函数进行计算、布尔逻辑等，可以直接在 SQL 中进行更多的数据操作，而后端编程更少。

练习集成函数和表达式，将数据处理推送到数据库引擎中，以便更快、更轻松地进行分析。

第十二课

插入、更新和删除

虽然 SQL 的查询语言 （DQL） 允许检索数据，但数据操作语言 （DML） 有助于插入、修改和删除数据库表中的记录。掌握这些陈述有助于发现数据捕获和演变能力。

INSERT 语句

INSERT 将新记录追加到表中。

基本语法：

1
2

INSERT INTO table (column1, column2)
VALUES (value1, value2);

这将插入一行具有给定的列值。

假设您有一个名为 books 的表，其中包含列 book_id 和 title。

在此表中插入新行的 SQL 命令为：

1
2

INSERT INTO books (book_id, title)
VALUES (1, 'To Kill a Mockingbird');

替代语法支持插入多行：

1
2
3

INSERT INTO table_name (column_list)
VALUES (row1_values),
       (row2_values);

假设您有一个名为 employees 的表，其中包含 employee_id、name 和 department 列。

在此表中插入两个新行的 SQL 命令将如下所示：

1
2
3

INSERT INTO employees (employee_id, name, department)
VALUES (123, 'John Doe', 'Marketing'),
       (456, 'Jane Smith', 'Finance');

用于从应用程序处理中加载批量数据。

主要功能包括：

• 显式指定插入的列
• 在一个语句中插入多行
• 从另一个表查询到 COPY 数据

例如，使用当前数据填充历史记录表：

1
2

INSERT INTO customers_archive
SELECT * FROM customers;

UPDATE 语句

UPDATE 修改现有记录。

1
2
3

UPDATE table
SET column = value, other_column = other_value
WHERE condition;

通过将列设置为给定值来更新满足条件的行。

假设您有一个名为 employees 的表，其中包含列 employee_id、name、department 和 salary。

用于更新特定员工的部门和薪水的 SQL 命令可能如下所示：

1
2
3

UPDATE employees
SET department = 'IT', salary = 75000
WHERE employee_id = 123;

最佳实践：

• 首先检查受影响的行而不更新
• 使用 WHERE 条件限制更新的行
• 在进行更改之前验证限制

DELETE 语句

顾名思义，DELETE 会删除记录：

1
2

DELETE FROM table
WHERE condition;

如果没有限制，则删除与条件匹配的行。

假设您有一个名为 customers 的表，其中包含各种列，包括 customer_id。

删除具有特定customer_id**客户的** SQL 命令为：

1
2

DELETE FROM customers
WHERE customer_id = 101;

应采取类似的谨慎措施，例如 UPDATE：

• 首先检查受影响的行
• 使用 WHERE 限制删除
• 基于关系规则进行验证
• 考虑在删除之前存档数据

学习受控的 DML 执行可将您的 SQL 技能提升到一个新的水平，以随着时间的推移保持数据库完整性。通过个性化练习练习安全的数据操作。

第十三课

创建表

关系数据库由多个相互关联的表组成，其中包含特定数据。适当定义表对于稳健的数据分析至关重要。SQL 为此提供了灵活的 CREATE TABLE 语句。本课介绍其语法、列和约束等组件，以及用于有效建模表架构的常见数据类型。

Create Table 语句

表是使用以下方法创建的：

1
2
3
4
5

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

可以在创建简明架构期间定义约束和默认值。

IF NOT EXISTS 子句告诉 SQL 仅在表不存在时才创建表。如果该表确实存在，则不会执行任何操作，也不会发生错误。

以下是在 SQL 中创建表时使用 IF NOT EXISTS 子句的示例：

1
2
3
4
5
6

CREATE TABLE IF NOT EXISTS customers (
   id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100)
);

有关使用 IF NOT EXISTS 的一些关键点：

• 避免在尝试创建已存在的表时出错
• 在表可能存在也可能不存在的可重用架构脚本中很有用
• 确保 CREATE TABLE 语句是幂等的
• 适用于 MySQL、Postgres、SQL Server、SQLite
• 检查表名 - 允许创建具有相同列的新表

例如，您可能有一个数据库还原脚本，该脚本使用测试数据截断并重新创建表。使用 IF NOT EXISTS 可以处理表可能已经存在或可能不存在的情况，而不会失败。

它还处理多个开发人员运行同一表创建脚本的情况，从而避免重复错误。

仅对表名进行检查。相同的 CREATE TABLE IF NOT EXISTS 语句可用于创建名称相同但列定义不同的表，而不会出现问题。

表 Schema 定义

定义架构时的关键组件：

列名称：描述性标识符，如 user_id、amount_due

数据类型：约束存储的值，如 INTEGER、VARCHAR、DATE

约束：NOT NULL、UNIQUE 和 FOREIGN KEYS 等限制

默认值：如果未指定，则预填充列值

适当的架构定义可确保数据完整性。

SQL 数据类型

常见类型包括：

数值的

• INTEGER， BIGINT：整数
• FLOAT， DOUBLE： 小数
• BOOLEAN：True/false 值

字符串

• CHAR（N）：固定长度字符串
• VARCHAR（N）：可变长度字符串
• TEXT：自由格式文本

日期/时间

• 日期：日历日期
• 时间： 一天中的时间
• DATETIME：日期和时间

大型对象

• BLOB：图像、文件

每个数据库都定义了其他类型，如货币、地理空间等。有关这些类型的更多信息，请访问数据类型。

表约束

约束确保数据有效性：

• PRIMARY KEY：唯一行标识符
• FOREIGN KEY：从子表到父表的引用
• NOT NULL：强制值
• UNIQUE：不同的值
• CHECK：值的条件

主键约束

主键是一种特殊的关系数据库约束，用于唯一标识表中的每条记录。

主键的主要特征是：

• 唯一标识每一行 - 没有两条记录可以具有相同的主键值
• 不能包含 NULL 值 - 主键列必须具有定义的值
• 防止重复条目 - 确保表中不存在重复的行

一个表只能有一个主键。主键可以由单个列或多个列（复合键）组成。

一些例子：

单列主键

1
2
3
4

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

此处 id 列是主键。

多列主键

1
2
3
4
5

CREATE TABLE employee (
  emp_id INT,
  department_id INT,
  PRIMARY KEY (emp_id, department_id)
);

在这里，emp_id 和 department_id 的组合构成了主键。

主键是关系模型中的一个重要概念。它们允许唯一标识表中的元组/行，并构成表之间关系的基础。

外键约束

数据库表中的外键是链接到另一个表的主键的字段。它有助于在表之间建立关系并强制实施引用完整性。

外键的主要特点：

• 它必须与它引用的主键的数据类型和结构匹配
• 包含外键的表称为子表
• 具有主键的被引用表称为被引用表或父表
• 帮助根据逻辑关系连接表
• 强制引用完整性 - 子表不能包含孤立记录

例如：

1
2
3
4
5

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

此处的 orders 表中是一个外键，指的是表中的主键。customer_id``id``customers

这可确保：

• 匹配项的数据类型customer_id``id``customers
• 删除客户记录也会删除关联的订单
• 插入订单需要 存在于customer_id``customers

外键在两个表之间建立定义的关系，从而促进记录之间的规则和完整性。实施外键约束对于准确的互连数据至关重要。

仔细定义约束可以防止无效的数据插入。

第十四课

更改表

SQL 数据库表的结构 - 其列、数据类型、约束等 - 在创建时通过 CREATE TABLE 定义。但是，需求会不断发展，更改需要反映在数据库架构中。

SQL 提供了专门用于此目的的 ALTER TABLE 命令。它允许通过许多操作来修改现有表，例如添加、删除或重命名列和约束。

添加新列

添加列允许在需求更改时捕获其他数据属性：

1
2

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(12);

在这里，我们添加了一个 phone_number 字段来存储客户电话号码，这最初并不是必需的。添加新列允许在需求变化时记录其他数据。

Drop Column

删除不再需要的列或存储过时的数据：

1
2

ALTER TABLE products
DROP COLUMN list_price;

list_price 列不再使用，因此我们将其删除以保持表精简。注意 - 删除会导致该列中的永久数据丢失。

重命名列

随着时间的推移，重命名命名不当的列会使架构更具可读性：

1
2

ALTER TABLE order_reviews
RENAME COLUMN commant TO comment;

在这里，我们修复了列名中从 “commant” 到 “comment” 的拼写错误。在需求扩展时确保准确性。

添加外键

外键强制跨表引用完整性：

1
2
3

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)

我们意识到 orders 表应该链接到 customers。添加外键可以保护数据一致性。

Modify Column

随着约束的更改，更改列数据类型、宽度、可为 null 性：

1
2

ALTER TABLE customers
MODIFY COLUMN last_name VARCHAR(100) NOT NULL;

我们遇到了一个允许 NULL 姓氏的错误。更改列以禁止 NULL 可解决此问题。

通过 ALTER TABLE 精心改进 SQL 表架构是管理不断变化的数据需求的关键。

掌握 ALTER TABLE 可以放心地根据不断变化的需求发展 SQL 架构，同时保持现有数据完整性。在设计数据库时主动参阅表更改要求。

第十五课

删除表

在数据库生命周期中，由于分析需求的变化，一些早期创建的表会过时。结转未使用的表会降低可维护性。SQL 支持通过 DROP TABLE 命令轻松删除现有表。使用 IF EXISTS 使此操作更安全。

DROP TABLE 语句永久删除表和关联的数据/索引。基本语法：

1

DROP TABLE table_name;

但是，如果表尚不存在，则会出现此错误。IF EXISTS 子句修复了这个问题：

1

DROP TABLE IF EXISTS table_name;

现在，如果缺少表，该语句将悄无声息地执行任何操作。

例如，要删除 obsolete products 表：

1

DROP TABLE IF EXISTS products;

有关删除表的一些关键点：

数据丢失

DROP TABLE 永久删除所有表数据。如果需要，请先检索数据。

删除依赖项

如果其他 table 通过外键或 constraints 引用此 table，请先删除这些引用。

恢复需要恢复

没有自动恢复。还原备份是唯一的选项。

谨慎的权限授予

允许 DROP TABLE 谨慎 - 启用删除有价值的表。

例如，删除已弃用的定价表：

1
2
3
4

ALTER TABLE orders
DROP FOREIGN KEY fk_order_pricing;

DROP TABLE IF EXISTS pricing;

在这里，我们首先通过在删除 foreign key constraint 之前删除 pricing table 与 orders 的关系。

谨慎使用 DROP TABLE IF EXISTS 有助于通过清除已弃用的表来保持数据库精简。练习有意识地创建和删除表以了解影响。

第十六课

索引和性能优化

随着数据量的增长，SQL 数据库性能变得至关重要。索引允许通过创建数据结构来优化常见查询，从而避免全表扫描。了解索引和性能优化概念可以提高速度。

SQL 索引

索引的工作方式类似于书后面的索引 - 它添加了一个数据结构，以便更快地查找指定的列。

例如，last_name 上的 customers 表索引将重新排列按姓氏排序的记录。现在搜索如下：

1
2
3

SELECT *
FROM customers
WHERE last_name = 'Smith';

可以直接查找 Smith 条目，而不是扫描每一行。

索引以写入速度换取读取效率。当 INSERTS/UPDATES 在索引中记录新数据时速度变慢，而 SELECT 的速度会显著加快。

SQL 原生支持核心索引类型，例如：

B 树 - 用于对数据进行排序的平衡树

Bitmap - 对不同值进行编码的矢量化位数组

哈希 - 哈希表将值映射到表行

数据库自动使用索引来加快查询速度，以查找定义了有用索引的列。

在 MySQL 中添加索引

要在 MySQL 中的列上创建索引，您可以使用该语句。CREATE INDEX

基本语法是：

1
2

CREATE INDEX index_name
ON table_name (column_name);

例如，要在表中的列上创建索引：last_name``customers

1
2

CREATE INDEX idx_customers_lastname
ON customers (last_name);

有关添加 MySQL 索引的一些要点：

• 为索引指定描述性名称，例如idx_table_column
• 添加索引不需要锁定表或停机
• WHERE 子句中经常使用的列是很好的索引候选者
• 限制低基数列的索引以保持表精简
• 在索引中同时使用多个列以实现高效查询

例如，多列索引：

1
2

CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);

这优化了按客户 ID 和日期筛选的查询。

请谨慎添加索引以平衡性能和开销。最佳索引取决于典型的查询工作负载。使用 EXPLAIN 计划分析慢速查询日志并迭代调整索引。

外键

SQL 中的外键是在两个表中的数据之间建立链接的列。它通过确保表关系有效来强制执行引用完整性。

以下是在 MySQL 中定义外键约束的示例：

1
2
3
4
5
6
7

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  order_total DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

此 orders 表有一个 customer_id 列，该列引用 customers 表中的主键。

MySQL 中关于外键的一些要点：

• 外键和引用的列必须具有匹配的数据类型
• 添加外键需要对引用的表具有 REFERENCES 特权
• ON DELETE 和 ON UPDATE 子句允许配置级联操作
• 外键可防止无效数据进入链接表
• 联接可以跨外键关系检索数据

外键有助于保持分布式数据同步。例如，如果从 customers 表中删除了客户记录，则数据库引擎会自动删除属于该客户的关联订单。

性能优化查询

未充分利用的索引会浪费空间，而缺失索引会导致查询速度变慢。合理调整索引大小的一些原则：

1. 识别慢速查询

检查查询执行计划以查找性能不佳的计划以进行优化。

2. 分析索引使用情况

查看索引是否被充分利用或导致额外的工作。

3. 档案数据检索

衡量索引对查询运行时减少的贡献。

4. 添加明智的索引

仔细为通常筛选的列编制索引。

与书籍索引一样，数据库索引需要规划以加速常见查找，而不会浪费空间或维护成本。测量两次，切割一次。