MySQL数据库详解_default charset与character set

一、数据库相关概念

1. 数据（Data）： 数据是事实或信息的描述，是数据库中存储的基本元素。
2. **数据库（Database）**是一个组织和存储数据的系统，它是按照数据结构来组织、存储和管理数据的集合。
3. 数据库管理系统（DBMS）： 是一种管理数据库的软件，负责数据库的创建、维护、更新和查询等操作。常见的DBMS有MySQL、Oracle、SQL Server等。
4. 数据库系统（Database System）： 由数据库、数据库管理系统和应用软件组成的一个整体，用于存储和管理大量结构化数据。
5. 表（Table）： 数据库中的基本组织单元，用于存储特定类型的数据。表由行和列组成，行表示记录，列表示字段。
6. 字段（Field）： 表中的一个数据项，具有特定的数据类型，用于存储表中的数据。
7. 主键（Primary Key）： 一种唯一标识表中记录的字段，确保每条记录都有唯一的标识符。
8. 外键（Foreign Key）： 用于建立表与表之间关系的字段，指向其他表的主键。
9. 索引（Index）： 用于加快数据检索速度的数据结构，类似书的目录，可以提高查询效率。
10. SQL（Structured Query Language）： 结构化查询语言，用于与数据库交互的标准语言。通过SQL可以执行查询、更新、插入和删除等操作。
11. 事务（Transaction）： 是由一个或多个操作组成的一个工作单元，要么全部执行成功，要么全部失败，具有原子性、一致性、隔离性和持久性（ACID）的特性。
12. 范式（Normalization）： 数据库设计中的一种优化方法，通过规范化可以减少数据冗余，提高数据的一致性和完整性。
13. 触发器（Trigger）： 是一种特殊的存储过程，当满足某些条件时会自动执行，常用于实现数据完整性约束和业务规则。
14. 函数（Function）： 数据库函数是一段封装好的可重复使用的代码，接受输入参数，执行一系列操作，并返回一个值。函数通常用于计算或处理数据，并且可以在SQL查询中直接调用。数据库系统通常提供了内置的函数，同时也支持用户自定义函数。
15. 存储过程（Stored Procedure）： 存储过程是一组预先编译好的SQL语句集合，类似于脚本或程序，存储在数据库中以便重复使用。存储过程可以接受输入参数，执行一系列的SQL语句，并返回一个或多个结果。存储过程通常用于封装业务逻辑，提高数据库操作的效率和安全性。

二、安装MySQL数据库

MySQL 默认使用的端口是3306

1.修改端口

如果你需要在 MySQL 服务器上修改端口号，可以按照以下步骤进行：

1. 打开 MySQL 配置文件。在 Linux 上，通常是 /etc/mysql/my.cnf 或 /etc/my.cnf。在 Windows 上，可能是 C:\ProgramData\MySQL\MySQL Server X.X\my.ini。

2. 找到 port 配置项，将其值修改为你想要的端口号。例如：

   port = 3307
   1

3. 保存文件并重新启动 MySQL 服务器，使更改生效。

请注意，如果你修改了 MySQL 服务器的端口号，确保在连接时使用相应的端口号。

2.登录、退出MySQL

1.连接数据库

mysql -u username -p
1

2.退出

exit;
1

3.更改用户密码

1.更改用户密码

ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
1

三、数据库操作相关命令

1.显示数据库列表

1.查询全部

SHOW DATABASES;
1

2.模糊查询

SHOW DATABASES [LIKE '数据库名']
1

2.创建数据库

创建数据库语法：

CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];
1
2
3

• IF NOT EXISTS: 这是一个可选的子句，用于在创建数据库之前检查是否已经存在同名的数据库。如果存在，并且使用了 IF NOT EXISTS，则不会引发错误，而是忽略创建操作。
• database_name: 这是要创建的数据库的名称。
• CHARACTER SET charset_name: 这是可选的子句，用于指定数据库的字符集。字符集定义了数据库中可以存储的字符集合。例如，utf8mb4 是一种常用的字符集。
• COLLATE collation_name: 这是可选的子句，用于指定数据库的校对规则。校对规则定义了字符的比较和排序规则。例如，utf8mb4_unicode_ci 是一种常用的校对规则。
• 常用字符集：常用支持中文的字符集utf8mb3，utf8mb4对应校验规则
• 校验规则名称： utf8mb4_general_ci、utf8mb4_unicode_ci 等
• 描述： ci 表示 Case-Insensitive，即大小写不敏感。utf8mb4_general_ci 是一种比较宽松的规则，而 utf8mb4_unicode_ci 支持更广泛的 Unicode 字符，适用于中文。

示例：

1.基本，使用默认字符集和校验规则

CREATE DATABASE database_name;
1

2.设置字符集和校验规则

CREATE DATABASE IF NOT EXISTS company_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
1
2
3

3.删除数据库

删除数据库语法：

DROP DATABASE [IF EXISTS] database_name;
1

4.修改数据库

修改数据库语法：

ALTER DATABASE database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];
1
2
3

• ALTER DATABASE: 这是 MySQL 中用于修改数据库属性的命令。
• database_name: 要修改的数据库的名称。
• CHARACTER SET charset_name: 可选，用于指定新的字符集。这会影响数据库中所有表的默认字符集。
• COLLATE collation_name: 可选，用于指定新的校对规则。同样，这会影响数据库中所有表的默认校对规则。

示例：

ALTER DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
1
2
3

5.选择数据库

USE database_name;
1

6.查看当前选择的数据库

SELECT DATABASE();
1

7.显示数据库的创建语句

SHOW CREATE DATABASE database_name;
1

8.显示数据库的大小信息

SELECT table_schema "Database Name", 
       SUM(data_length + index_length) / 1024 / 1024 "Database Size (MB)" 
FROM information_schema.tables 
GROUP BY table_schema;
1
2
3
4

四、MySQL数据类型

1.MySQL全部数据类型

1. bigint: 64位整数，通常用于存储大整数值。可以表示范围广泛的整数，从-9,223,372,036,854,775,808到9,223,372,036,854,775,807。适用于需要大范围整数值的情况，如数据库中的主键。
2. binary: 固定长度的二进制字符串。存储的数据长度是固定的，不受存储的实际数据长度影响。适用于存储二进制数据，如哈希值或固定长度的标识符。
3. bit: 固定长度的比特串，可以存储0和1。适用于存储位标志或表示开关状态的二进制信息。
4. blob (Binary Large Object): 用于存储大量的二进制数据，例如图像、音频或视频文件。适用于需要处理大型二进制数据的场景。
5. char: 固定长度的字符串。存储的数据长度是固定的，不受存储的实际数据长度影响。适用于存储长度固定的字符数据，如国家代码或固定长度的标识符。
6. date: 日期类型，存储格式为’YYYY-MM-DD’的日期。适用于存储日期信息，如生日或事件日期。
7. datetime: 日期和时间类型，存储格式为’YYYY-MM-DD HH:MM:SS’的日期和时间。适用于需要同时存储日期和时间的情况，如记录数据变更的时间戳。
8. decimal: 定点数，用于存储精确的小数。通过指定总位数和小数位数，提供了精确的数值存储。适用于需要高精度计算的场景，如财务数据。
9. double: 双精度浮点数，用于存储浮点数值。适用于一般的浮点数值存储需求，但在需要高精度计算时应优先考虑使用decimal。
10. enum: 枚举类型，用于存储从预定义值集合中选择的一个值。适用于列具有有限离散选项的情况，如性别或状态。
11. float: 单精度浮点数，用于存储浮点数值。与double相似，但占用的存储空间较小，适用于存储较大数据集的情况。
12. geometry: 用于存储几何数据，如点、线、面等。适用于需要处理地理空间数据的场景。
13. geometrycollection: 存储几何对象集合，包含多个几何对象。适用于需要处理包含多种几何类型的数据集的场景。
14. int (integer): 32位整数，通常用于存储整数值。适用于一般的整数值存储需求，如计数器或索引。
15. json: 存储JSON格式的数据。适用于存储非结构化或半结构化的数据，如配置信息或日志。
16. linestring: 存储线的几何数据。适用于处理地理空间数据中的线要素。
17. longblob: 用于存储大量二进制数据的大对象。适用于存储大型文件或图像数据。
18. longtext: 用于存储大量文本数据的大对象。适用于存储大型文档或文章。
19. mediumblob: 用于存储中等大小的二进制数据的大对象。介于blob和longblob之间，适用于中等大小的二进制数据。
20. mediumint: 中等大小的整数类型。介于int和bigint之间，适用于中等大小的整数值。
21. mediumtext: 用于存储中等大小文本数据的大对象。介于text和longtext之间，适用于中等大小的文本数据。
22. multilinestring: 存储多条线的几何数据。适用于处理地理空间数据中的多条线要素。
23. multipoint: 存储多个点的几何数据。适用于处理地理空间数据中的多个点要素。
24. multipolygon: 存储多个多边形的几何数据。适用于处理地理空间数据中的多个多边形要素。
25. numeric: 用于精确存储小数的数值类型。类似于decimal，提供了精确的数值存储。
26. point: 存储点的几何数据。适用于处理地理空间数据中的点要素。
27. polygon: 存储多边形的几何数据。适用于处理地理空间数据中的多边形要素。
28. real: 单精度浮点数，用于存储浮点数值。类似于float，但占用的存储空间较小。
29. set: 存储字符串的集合，允许多个值。适用于列具有多个可能值的情况，但相较于enum更为灵活。
30. smallint: 小整数类型。适用于存储较小范围的整数值。
31. text: 用于存储文本数据的大对象。适用于存储较短的文本数据，如评论或描述。
32. time: 时间类型，存储格式为’HH:MM:SS’的时间。适用于存储时间信息，如活动开始时间。
33. timestamp: 时间戳类型，存储日期和时间。通常用于记录数据的创建或修改时间。
34. tinyblob: 用于存储小量二进制数据的大对象。适用于存储较小的二进制数据。
35. tinyint: 小整数类型。适用于存储较小范围的整数值，但比smallint范围更小。
36. tinytext: 用于存储小量文本数据的大对象。适用于存储较短的文本数据。
37. varbinary: 可变长度的二进制字符串。适用于存储可变长度的二进制数据。
38. varchar: 可变长度的字符串。适用于存储可变长度的字符数据，如变长的描述信息。
39. year: 年份类型，存储四位数的年份。适用于存储年份信息，如生产年份或有效期截止年份。

选择适当的数据类型有助于提高数据库性能、减小存储空间需求，并确保数据的准确性和一致性。在设计数据库时，需要根据实际场景和数据特性仔细选择合适的数据类型。

2.MySQL常用数据类型

1. varchar:可变长度的字符串。
2. char:固定长度的字符串。
3. int:32位整数。
4. bigint:64位整数，范围更大的整数类型。
5. float:单精度浮点数。
6. double:双精度浮点数。
7. date:日期类型，格式为’YYYY-MM-DD’。
8. datetime: 日期和时间类型，格式为’YYYY-MM-DD HH:MM:SS’。
9. blob:Binary Large Object,存储二进制数据，适用于存储大量的二进制数据。

五、MySQL存储引擎

1.主要存储引擎

1. InnoDB:

   • 事务支持： 是 MySQL 中唯一的支持事务和外键约束的存储引擎，提供了 ACID 特性。
   • 行级锁定： 使用行级锁定，适合高并发写入场景。
   • 崩溃恢复： 支持崩溃恢复。
   • 适用场景： 适用于需要事务支持和高并发写入的应用，如事务型应用、在线事务处理（OLTP）系统。

2. MyISAM:

   • 全文索引： 支持全文索引。
   • 表级锁定： 使用表级锁定，适合读密集型场景。
   • 不支持事务： 不支持事务，不具备 ACID 特性。
   • 不支持外键： 不支持外键关系。
   • 适用场景： 适用于读密集型、对全文搜索需求较多的应用，如博客、论坛等。

3. ARCHIVE:

   • 压缩存储： 数据进行压缩存储，适合用于存储大量历史数据。
   • 只支持插入和查询： 不支持更新和删除。
   • 表级锁定： 提供表级锁定。
   • 适用场景： 适用于存储归档数据，主要用于数据仓库或日志存储。

4. BLACKHOLE:

   • 不存储数据： 接收写入操作，但实际上不存储数据。主要用于复制和分发数据的情况。
   • 适用场景： 主要用于数据复制和数据分发，将写入的数据传递给其他 MySQL 服务器。

5. CSV:

   • 数据存储在 CSV 文件： 将数据存储在 CSV 文件中。
   • 不支持索引： 不支持索引，适用于小型数据集。
   • 表级锁定： 提供表级锁定。
   • 适用场景： 适用于导入导出数据，但不适用于高并发或大规模数据。

6. MEMORY:

   • 数据存储在内存： 将表数据存储在内存中，适合用于临时表和快速读写场景。
   • 不持久化： 数据不会持久化到磁盘，重启后数据丢失。
   • 表级锁定： 提供表级锁定。
   • 适用场景： 适用于对性能要求较高、数据量较小、可以随时重新生成的临时数据。

7. MRG_MYISAM:

   • 合并 MyISAM 表： 用于将多个 MyISAM 表合并成一个逻辑表。
   • 适用场景： 适用于 MyISAM 表的合并操作。

8. PERFORMANCE_SCHEMA:

   • 性能监控： 提供性能监控和诊断的功能。
   • 适用场景： 适用于进行性能分析和调优，监控系统资源使用情况。

每个存储引擎都有其优势和适用场景，选择合适的存储引擎取决于你的应用需求。在进行选择时，需要考虑事务支持、并发控制、崩溃恢复、全文搜索等因素。

2.修改表的存储引擎

mysql默认存储引擎是InnoDB

ALTER TABLE table_name
    ENGINE = new_storage_engine;
1
2

• table_name: 要修改的表的名称。
• new_storage_engine: 新的存储引擎的名称。

实例：

ALTER TABLE mytable
    ENGINE = MyISAM;
1
2

六、表操作相关命令

1.查看表列表

查看当前数据的表

SHOW TABLES;
1

查看指定数据库的表

SHOW TABLES FROM your_database;
1

查看当前表状态信息

SHOW TABLE STATUS;
1

查看指定数据库的表状态信息

SHOW TABLE STATUS FROM your_database;
1

2.创建表

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype1 [DEFAULT default_value1] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY],
    column2 datatype2 [DEFAULT default_value2],
    ...
    [PRIMARY KEY (one_or_more_columns)],
    [UNIQUE [KEY] index_name (column1, column2, ...)],
    [FOREIGN KEY (column) REFERENCES parent_table(parent_column)],
    ...
) ENGINE=storage_engine [DEFAULT CHARSET=character_set];

-- 示例
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

语法说明：

• CREATE TABLE: 创建表的关键字。

• [IF NOT EXISTS]: 可选，表示如果表不存在则创建。如果存在，则会忽略。

• table_name: 表的名称。

• column1, column2, ...: 列的定义，包括列名、数据类型等。

• datatype1, datatype2, ...: 列的数据类型。

• [DEFAULT default_value1]: 可选，指定列的默认值。

• [AUTO_INCREMENT]: 可选，表示列为自增长。

• [UNIQUE [KEY] | [PRIMARY] KEY]: 可选，定义唯一约束或主键约束。

• [PRIMARY KEY (one_or_more_columns)]: 可选，定义表的主键。

• [UNIQUE [KEY] index_name (column1, column2, ...)]: 可选，定义唯一索引。

• [FOREIGN KEY (column) REFERENCES parent_table(parent_column)]: 可选，定义外键关系。

• [ENGINE=storage_engine]: 指定存储引擎，默认为 InnoDB。

• [DEFAULT CHARSET=character_set]: 指定字符集，默认为数据库的默认字符集。

3.删除表

DROP TABLE [IF EXISTS] table_name;
1

其中：

• table_name: 要删除的表的名称。
• [IF EXISTS]: 可选，表示如果表存在则删除。如果省略了此选项并且尝试删除不存在的表，将会导致错误。

示例：

-- 删除名为 "mytable" 的表
DROP TABLE mytable;

-- 如果存在，则删除名为 "mytable" 的表
DROP TABLE IF EXISTS mytable;
1
2
3
4
5

请注意，删除表将会删除表中的所有数据，因此在执行删除操作之前，请确保你已经备份了重要的数据，或者你确实想要删除表及其数据。

4.修改表结构

1. 添加列

ALTER TABLE your_table
ADD COLUMN new_column_name datatype [DEFAULT default_value] [AFTER existing_column];
1
2

• your_table: 要修改的表的名称。
• new_column_name: 要添加的新列的名称。
• datatype: 新列的数据类型。
• default_value: （可选）新列的默认值。
• existing_column: （可选）新列在某一已存在列之后。

2. 修改列名和类型

ALTER TABLE your_table
CHANGE COLUMN old_column_name new_column_name new_datatype [DEFAULT default_value] [AFTER existing_column];
1
2

• your_table: 要修改的表的名称。
• old_column_name: 要修改的列的旧名称。
• new_column_name: 列的新名称。
• new_datatype: 列的新数据类型。
• default_value: （可选）新列的默认值。
• existing_column: （可选）新列在某一已存在列之后。

3. 修改列的默认值

ALTER TABLE your_table
ALTER COLUMN column_name SET DEFAULT default_value;
-- 或者
ALTER TABLE your_table
ALTER COLUMN column_name DROP DEFAULT;
1
2
3
4
5

• your_table: 要修改的表的名称。
• column_name: 要修改的列的名称。
• default_value: 新列的默认值。

4. 修改列的数据类型

ALTER TABLE your_table
MODIFY COLUMN column_name new_datatype [DEFAULT default_value] [AFTER existing_column];
1
2

• your_table: 要修改的表的名称。
• column_name: 要修改的列的名称。
• new_datatype: 列的新数据类型。
• default_value: （可选）新列的默认值。
• existing_column: （可选）新列在某一已存在列之后。

5. 删除列

ALTER TABLE your_table
DROP COLUMN column_name;
1
2

• your_table: 要修改的表的名称。
• column_name: 要删除的列的名称。

6. 修改表名

ALTER TABLE old_table_name
RENAME TO new_table_name;
1
2

• old_table_name: 要修改的表的旧名称。
• new_table_name: 表的新名称。

7. 修改字符集和校对规则

ALTER TABLE your_table
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
1
2

• your_table: 要修改的表的名称。

8. 添加主键

ALTER TABLE your_table
ADD PRIMARY KEY (column1, column2, ...);
1
2

• your_table: 要修改的表的名称。
• (column1, column2, ...): 主键列的列表。

9. 添加外键

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (child_column)
REFERENCES parent_table(parent_column);
1
2
3
4

• child_table: 子表的名称。
• fk_constraint_name: 外键约束的名称。
• child_column: 子表中的外键列。
• parent_table: 父表的名称。
• parent_column: 父表中的关联列。

10. 添加唯一索引

ALTER TABLE your_table
ADD UNIQUE INDEX index_name (column1, column2, ...);
1
2

• your_table: 要修改的表的名称。
• index_name: 索引的名称。
• (column1, column2, ...): 要创建索引的列的列表。

11. 添加普通索引

ALTER TABLE your_table
ADD INDEX index_name (column1, column2, ...);
1
2

• your_table: 要修改的表的名称。
• index_name: 索引的名称。
• (column1, column2, ...): 要创建索引的列的列表。

12. 修改存储引擎

ALTER TABLE your_table
ENGINE = new_storage_engine;
1
2

• your_table: 要修改的表的名称。
• new_storage_engine: 新的存储引擎的名称。

5.查看表结构

查看表结构

DESCRIBE your_table;
--简写
DESC your_table;
--或者
SHOW COLUMNS FROM your_table;
1
2
3
4
5

查看建表语句，添加\g或者\G参数可以改变展示形式

SHOW CREATE TABLE your_table \g;
1

七、约束

1.主键约束

主键约束是数据库中一种用于标识表中每一行数据的机制，确保每个行都有一个唯一的标识符。

1. 主键的定义

在 MySQL 中，主键是通过在表的列上应用 PRIMARY KEY 关键字来定义的。主键可以是一个或多个列，取决于表的设计。通常，主键列的值是唯一的，而且不能为空。

CREATE TABLE students (
    student_id INT PRIMARY KEY, //列级约束
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);
1
2
3
4
5

在上述示例中，student_id 被定义为表 students 的主键。

2. 复合主键

主键可以由多个列组成，这被称为复合主键。在列定义后使用 PRIMARY KEY 关键字，并将多个列名放在括号中。

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id) //表级约束
);
1
2
3
4
5

在上述示例中，order_id 和 product_id 组成了复合主键。

3. 主键的自动递增

通常，主键列还可以设置为自动递增，以便数据库系统自动生成唯一的标识符。这在大多数情况下用于整数类型的主键。

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    password VARCHAR(255)
);
1
2
3
4
5

在上述示例中，user_id 被设置为自动递增的主键。

4. 主键的作用

主键约束在数据库中有以下作用：

• 唯一标识行： 每个行都有一个唯一的标识符，方便在表中查找和更新特定的记录。
• 提高检索效率： 主键通常会被自动索引，这有助于提高查询效率。
• 关联表： 主键经常用于与其他表建立关系，例如在外键中引用。
• 数据完整性： 通过主键约束，确保每个行都有一个唯一标识，并且该标识不为空。

2.外键约束

外键约束是 MySQL 中用于确保两个表之间关系完整性的机制。外键用于定义一个表中的列，该列的值必须是另一表中的主键或唯一键的值。外键约束有助于维护表之间的关联性，确保数据的一致性。

1. 外键的定义

在 MySQL 中，外键是通过在表的列上应用 FOREIGN KEY 关键字来定义的。外键列的值必须与另一表中的主键或唯一键列的值匹配。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
1
2
3
4
5

在上述示例中，product_id 被定义为 orders 表的外键，它参考了 products 表的 product_id 列。

2. 外键约束的目的

外键约束的主要目的有以下几点：

• 确保引用完整性： 外键约束确保在引用表中的值在被引用表中存在。

• 维护关联关系： 外键约束用于建立表之间的关系，例如父子关系、关联关系等。

• 自动索引： 外键通常会自动创建索引，提高关联表之间查询的性能。

3. 外键的复合约束

外键可以由多个列组成，这被称为复合外键。在列定义后使用 FOREIGN KEY 关键字，并将多个列名放在括号中。

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
1
2
3
4
5
6
7

在上述示例中，order_id 和 product_id 组成了复合外键。

4. 外键约束的操作

• CASCADE（级联）： 如果引用表中的行发生更改（更新或删除），则在引用表中的外键列上的相应操作也将发生。

• SET NULL： 如果引用表中的行发生更改，外键列上的相应操作将设置为 NULL。

• SET DEFAULT： 如果引用表中的行发生更改，外键列上的相应操作将设置为默认值。

• NO ACTION： 如果引用表中的行发生更改，外键列上的相应操作将无效。

5. 外键的限制

• 引用表必须存在： 外键引用的表必须存在，否则无法创建外键。

• 数据类型必须匹配： 外键列和被引用列的数据类型必须匹配，或者它们必须是可以隐式转换的类型。

• 唯一键或主键： 外键通常引用另一表的唯一键或主键。

3.唯一约束

唯一约束是 MySQL 中用于确保表中列或列组中的所有值都是唯一的机制。它与主键约束类似，但允许存在一个 NULL 值。唯一约束可用于一个或多个列。

1. 唯一约束的定义

在 MySQL 中，唯一约束是通过在列定义后使用 UNIQUE 关键字来定义的。唯一约束确保指定列或列组中的所有值都是唯一的。

CREATE TABLE employees (
    employee_id INT UNIQUE,
    email VARCHAR(255) UNIQUE,
    phone_number VARCHAR(20) UNIQUE
);
1
2
3
4
5

在上述示例中，employee_id、email 和 phone_number 列都分别应用了唯一约束。

2. 复合唯一约束

唯一约束可以由多个列组成，这称为复合唯一约束。在列定义后使用 UNIQUE 关键字，并将多个列名放在括号中。

CREATE TABLE products (
    category_id INT,
    product_name VARCHAR(255),
    UNIQUE (category_id, product_name)
);
1
2
3
4
5

在上述示例中，category_id 和 product_name 组成了复合唯一约束。

3. 唯一约束与主键的区别

• 主键要求列的值不能为空，而唯一约束允许存在一个 NULL 值。

• 表中可以有多个唯一约束，但只能有一个主键。

4. 唯一约束的作用

• 确保唯一性： 唯一约束确保指定列或列组中的所有值都是唯一的。

• 提高检索效率： 唯一约束通常会自动创建索引，从而提高查询效率。

5. 唯一约束与索引

唯一约束和索引紧密相关，因为在许多数据库系统中，唯一约束通常会自动创建唯一索引。这有助于加速唯一性检查和加速查询操作。

6. 使用唯一约束的注意事项

• 使用唯一约束时，要确保列的数据类型是适合唯一性比较的。

• 在插入或更新数据时，数据库会检查唯一约束，确保不会违反唯一性规则。

• 考虑使用唯一约束来确保表中某些列的数据完整性。

4.检查约束

在 MySQL 中，检查约束是一种用于确保列中的值满足指定条件的机制。检查约束允许在插入或更新行时对列中的值进行验证，确保其符合规定的条件。

1. 检查约束的定义

在 MySQL 中，可以在列定义后使用 CHECK 关键字为列添加检查约束。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price >= 0),
    quantity INT CHECK (quantity >= 0)
);
1
2
3
4
5

在上述示例中，price 和 quantity 列都有检查约束，确保它们的值大于或等于 0。

2. 检查约束的作用

• 确保数据完整性： 检查约束确保列中的值满足特定条件，从而保护数据的完整性。

• 限制列的取值范围： 检查约束可以限制列的取值范围，防止插入不符合条件的数据。

3. 复合检查约束

检查约束可以应用于单个列，也可以应用于多个列，形成复合检查约束。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    shipped_date DATE,
    CHECK (order_date <= shipped_date)
);
1
2
3
4
5
6

在上述示例中，复合检查约束确保 order_date 的值不晚于 shipped_date 的值。

4. 使用表达式作为检查条件

检查约束的条件可以是任何合法的 SQL 表达式。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    hire_date DATE,
    birth_date DATE,
    CHECK (hire_date >= '2000-01-01' AND birth_date <= CURRENT_DATE)
);
1
2
3
4
5
6

在上述示例中，检查约束确保 hire_date 在 2000 年以后，birth_date 在当前日期之前。

5. 检查约束的注意事项

• 检查约束在 MySQL 中并不是强制的，因为某些存储引擎（如 InnoDB）并不直接支持 CHECK。

• 使用触发器（Triggers）是一种在 MySQL 中实现类似检查约束的方法。

5.默认值约束

默认值约束是 MySQL 中用于为列指定默认值的机制。当在插入新行时没有提供该列的值时，将使用默认值。

1. 默认值约束的定义

在 MySQL 中，可以在列定义后使用 DEFAULT 关键字为列指定默认值。

CREATE TABLE messages (
    message_id INT PRIMARY KEY,
    content TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1
2
3
4
5

在上述示例中，timestamp 列被定义为默认值为当前时间戳的列。

2. 默认值约束的位置

默认值约束通常出现在列定义的最后，但在 MySQL 中，你也可以在列定义中的任何位置使用 DEFAULT。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) DEFAULT 'Unknown',
    price DECIMAL(10, 2) DEFAULT 0.00
);
1
2
3
4
5

在上述示例中，product_name 和 price 列都有默认值。

3. 使用表达式作为默认值

除了直接指定常量值，还可以使用表达式作为默认值。

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(255),
    priority INT DEFAULT 1,
    due_date DATE DEFAULT (CURRENT_DATE + INTERVAL 7 DAY)
);
1
2
3
4
5
6

在上述示例中，priority 的默认值是 1，而 due_date 的默认值是当前日期加上 7 天。

4. 默认值约束的作用

• 确保数据完整性： 默认值约束确保在插入新行时，如果没有提供值，则会使用默认值，从而避免了 NULL 值的情况。

• 简化插入语句： 默认值允许你在插入语句中省略包含默认值的列。

5. 使用 CURRENT_TIMESTAMP 获取当前时间戳

CURRENT_TIMESTAMP 是一个常用的表达式，用于获取当前的时间戳。它通常用于为 TIMESTAMP 类型的列设置默认值。

CREATE TABLE log (
    log_id INT PRIMARY KEY,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1
2
3
4
5

在上述示例中，log_time 的默认值是当前时间戳。

6.非空约束

在 MySQL 中，非空约束是一种用于确保列中的值不为空的机制。非空约束用于防止插入或更新行时将 NULL 值插入到指定列中。

1. 非空约束的定义

在 MySQL 中，可以在列定义后使用 NOT NULL 关键字为列添加非空约束。

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);
1
2
3
4
5

在上述示例中，first_name 和 last_name 列都有非空约束，确保它们的值不为空。

2. 非空约束的作用

• 确保数据完整性： 非空约束确保列中的值不为空，防止插入或更新时将 NULL 值插入到指定列中。

• 简化查询： 避免了在查询中对 NULL 值的处理，使查询更加简洁。

3. 多列非空约束

非空约束可以应用于多个列，形成复合非空约束。

CREATE TABLE addresses (
    address_id INT PRIMARY KEY,
    street VARCHAR(255) NOT NULL,
    city VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    zip_code VARCHAR(10) NOT NULL
);
1
2
3
4
5
6
7

在上述示例中，street、city、state 和 zip_code 列都有非空约束。

4. 非空约束与默认值

如果给定列定义了默认值，并且该列有非空约束，那么即使没有显式提供值，也会使用默认值。

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) DEFAULT 'default_password'
);
1
2
3
4
5

在上述示例中，password 列定义了默认值，但由于有非空约束，插入行时即使不提供密码，也会使用默认值。

5. 非空约束的注意事项

• 在设计数据库时，对于不允许为空的列，应该使用非空约束。
• 使用非空约束可以避免在查询中处理 NULL 值，使代码更加清晰和健壮。

7. 自动递增约束

自动递增约束是 MySQL 中一种用于为列生成唯一标识符的机制，通常用于为主键列创建唯一标识符。这允许数据库自动为插入的每一行生成一个唯一的值，而无需手动指定。以下是自动递增约束的详细介绍：

1. 自动递增约束的定义

在 MySQL 中，可以通过在列定义后使用 AUTO_INCREMENT 关键字为列添加自动递增约束。

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    password VARCHAR(255)
);
1
2
3
4
5

在上述示例中，user_id 列被定义为主键，并且使用了自动递增约束。

2. 自动递增的作用

• 生成唯一标识符： 自动递增约束用于为列生成唯一的、递增的整数值。

• 简化插入操作： 无需手动为主键列提供唯一值，数据库会自动处理。

3. 自动递增的起始值和步长

在默认情况下，自动递增列从 1 开始，步长为 1。但也可以通过 AUTO_INCREMENT 的可选参数来指定起始值和步长。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT,
    total_price DECIMAL(10, 2),
    INDEX (product_id)
) AUTO_INCREMENT = 1001;
1
2
3
4
5
6
7

在上述示例中，order_id 列的起始值为 1001。

4. 自动递增约束的注意事项

• 自动递增列通常用于主键列，确保每行都有一个唯一标识符。

• 如果插入操作未提供自动递增列的值，数据库将自动为其分配下一个可用的唯一值。

• 删除表中的行并不会重置自动递增列的计数，除非显式地使用 ALTER TABLE 语句进行重置。

八、运算符

1. 算术运算符

MySQL 中的算术运算符用于执行基本的数学运算。以下是常见的算术运算符：

• 加法 (+): 对两个数值相加。

  SELECT 5 + 3; -- 结果为 8
  1

• 减法 (-): 从左边的操作数中减去右边的操作数。

  SELECT 8 - 3; -- 结果为 5
  1

• 乘法 (*): 将两个数相乘。

  SELECT 2 * 4; -- 结果为 8
  1

• 除法 (/): 将左边的操作数除以右边的操作数。

  SELECT 10 / 2; -- 结果为 5
  1

• 取余 (%): 返回左边操作数除以右边操作数的余数。

  SELECT 10 % 3; -- 结果为 1
  1

2. 逻辑运算符

逻辑运算符用于在条件语句中组合条件。以下是常见的逻辑运算符：

• AND (AND): 逻辑与，两个条件都必须为真。

  SELECT * FROM users WHERE age > 18 AND gender = 'Male';
  1

• OR (OR): 逻辑或，其中一个条件为真即可。

  SELECT * FROM products WHERE category = 'Electronics' OR price < 100;
  1

• NOT (NOT): 逻辑非，取反一个条件。

  SELECT * FROM orders WHERE NOT status = 'Canceled';
  1

3. 比较运算符

比较运算符用于比较两个值。以下是常见的比较运算符：

• 等于 (=): 检查两个值是否相等。

  SELECT * FROM employees WHERE department = 'HR';
  1

• 不等于 (<> or !=): 检查两个值是否不相等。

  SELECT * FROM products WHERE price <> 0;
  1

• 大于 (>): 检查左边的值是否大于右边的值。

  SELECT * FROM students WHERE age > 20;
  1

• 小于 (<): 检查左边的值是否小于右边的值。

  SELECT * FROM orders WHERE total_amount < 1000;
  1

• 大于等于 (>=): 检查左边的值是否大于或等于右边的值。

  SELECT * FROM employees WHERE salary >= 50000;
  1

• 小于等于 (<=): 检查左边的值是否小于或等于右边的值。

  SELECT * FROM products WHERE stock <= 10;
  1

4. 位运算符

位运算符用于对二进制数进行操作。以下是常见的位运算符：

• 按位与 (&): 对两个数的每一位执行与操作。

  SELECT 5 & 3; -- 结果为 1
  1

• 按位或 (|): 对两个数的每一位执行或操作。

  SELECT 5 | 3; -- 结果为 7
  1

• 按位异或 (^): 对两个数的每一位执行异或操作。

  SELECT 5 ^ 3; -- 结果为 6
  1

• 按位非 (~): 对数的每一位执行取反操作。

  SELECT ~5; -- 结果为 -6
  1

• 左移 (<<): 将数的所有位向左移动指定的位数。

  SELECT 4 << 2; -- 结果为 16
  1

• 右移 (>>): 将数的所有位向右移动指定的位数。

  SELECT 16 >> 2; -- 结果为 4
  1

5. 运算符优先级

运算符优先级指定了在一个表达式中多个运算符的计算顺序。以下是一些常见运算符的优先级：

1. 括号 (): 最高优先级
2. 乘法 *, 除法 /, 取余 %
3. 加法 +, 减法 -
4. 比较运算符 (=, !=, <, >, <=, >=)
5. 逻辑非 NOT
6. 逻辑与 AND
7. 逻辑或 OR

在使用多个运算符时，可以使用括号来明确运算的顺序。

九、MySQL函数

1.数值型函数

2.字符串函数

3.日期时间函数

4. 聚合函数

5. 流程控制函数

十、操作表中的数据

1.插入数据

INSERT INTO…VALUES语句

--基本语句
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

--如果要插入表中的所有列，可以省略列名
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

--插入多条
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1_row1, value2_row1, value3_row1, ...),
    (value1_row2, value2_row2, value3_row2, ...),
    ...;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

示例

INSERT INTO users (id, username, email)
VALUES (1, 'john_doe', 'john.doe@example.com');
1
2

INSERT INTO users
VALUES (1, 'john_doe', 'john.doe@example.com');
1
2

INSERT INTO users (id, username, email)
VALUES
    (1, 'john_doe', 'john.doe@example.com'),
    (2, 'jane_doe', 'jane.doe@example.com'),
    (3, 'bob_smith', 'bob.smith@example.com');
1
2
3
4
5

INSERT INTO…SET语句

INSERT...SET 语句用于向表中插入一行或多行数据，并可以通过指定列和对应的值来设置新行的值。

INSERT INTO table_name
SET column1 = value1, column2 = value2, ...;
1
2

示例：

INSERT INTO users
SET id = 1, username = 'john_doe', email = 'john.doe@example.com';
1
2

INSERT INTO … SELECT语句

使用 INSERT INTO ... SELECT 语句来实现从一个表中选择数据并插入到另一个表中。

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;
1
2
3
4

其中，target_table 是要插入数据的目标表名，column1, column2, ... 是目标表的列名，source_column1, source_column2, ... 是源表的列名，source_table 是源表名，condition 是选择数据的条件。

需要注意的是，源表和目标表的列数和数据类型应该匹配，否则可能会导致插入失败。

示例：

INSERT INTO target_table (id, username, email)
SELECT id, username, email
FROM source_table
WHERE condition;
1
2
3
4

2.删除数据

DELETE语句

DELETE FROM table_name WHERE condition;
1

其中，table_name 是要删除数据的表名，condition 是删除数据的条件。只有符合条件的数据才会被删除。

需要注意的是，上述语句会删除表中的所有行，但表的结构仍然保留。如果要删除整个表，包括表结构，可以使用 DROP TABLE 语句。在使用 DELETE 语句时，请谨慎使用 WHERE 子句以确保只删除需要删除的数据，以免造成数据丢失。

示例：

DELETE FROM users WHERE username = 'john_doe';

--删除整表
DELETE FROM users;
1
2
3
4

TRUNCATE语句

TRUNCATE 语句用于删除表中的所有数据，但保留表结构。

TRUNCATE TABLE table_name;
1

需要注意的是，使用 TRUNCATE 命令时，请确保你不需要保留表中的任何数据，因为这个操作是不可逆的。如果需要删除表中的部分数据，可以使用 DELETE 语句。

DELETE语句和TRUNCATE语句比较

• DELETE：

  • 适用场景： 需要精确控制删除条件、需要触发器执行等情况，例如，需要删除满足特定条件的行，或者需要触发DELETE触发器。
  • 示例： DELETE FROM users WHERE age > 30;

• TRUNCATE：

  • 适用场景： 需要快速清空表数据、不需要记录日志的情况，例如，清空整个表的数据，或者在不需要事务回滚的情况下进行清空操作。
  • 示例： TRUNCATE TABLE users;

3.更新数据

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

其中，table_name 是要更新数据的表名，column1 = value1, column2 = value2, ... 是要设置的新值，WHERE condition 是要更新数据的条件

示例：

UPDATE users
SET email = 'new_email@example.com', age = 30
WHERE username = 'john_doe';

--更新整个表
UPDATE users
SET email = 'new_email@example.com', age = 30;
1
2
3
4
5
6
7

4.查询数据

查询数据的语法结构主要使用 SELECT 语句。

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
FROM
    table_references
[WHERE
    where_condition]
[GROUP BY
    {col_name | expr | position}
    [ASC | DESC], ... [WITH ROLLUP]]
[HAVING
    where_condition]
[WINDOW
    window_name AS (window_spec)
    [, window_name AS (window_spec)] ...]
[ORDER BY
    {col_name | expr | position}
    [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE];
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

其中，各部分的含义如下：

• SELECT: 表示要查询数据的关键字。
• ALL | DISTINCT | DISTINCTROW: 用于指定选择所有行 (ALL)，或仅选择不同的行 (DISTINCT 或 DISTINCTROW)。
• HIGH_PRIORITY: 将查询设置为高优先级。
• STRAIGHT_JOIN: 强制使用连接表的顺序，而不进行优化。
• SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT: 控制结果集的大小和缓存。
• SQL_CACHE | SQL_NO_CACHE: 控制查询结果是否被缓存。
• SQL_CALC_FOUND_ROWS: 记录找到的行数，用于在分页时获取总行数。
• select_expr: 要选择的列或表达式。
• FROM: 表示从哪个表中选择数据。
• WHERE: 指定筛选条件。
• GROUP BY: 对结果进行分组。
• HAVING: 对分组后的结果进行筛选。
• WINDOW: 定义窗口函数。
• ORDER BY: 对结果进行排序。
• LIMIT: 限制返回的行数。
• INTO OUTFILE, INTO DUMPFILE, INTO var_name: 将查询结果导出到文件或变量。
• FOR UPDATE, LOCK IN SHARE MODE: 锁定选定的行。

5.快速复制表

使用 CREATE TABLE ... AS SELECT 语句来将查询的结果用于创建新表。

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;
1
2
3
4

这将创建一个名为 new_table_name 的新表，其列和数据由 SELECT 语句的结果确定。existing_table 是已存在的表，condition 是选择数据的条件。

需要注意的是，新表的列名和数据类型将由 SELECT 语句的结果确定。确保查询结果中的列名和数据类型符合创建表的要求。

示例：

CREATE TABLE new_users AS
SELECT id, username, email
FROM users
WHERE age > 30;
1
2
3
4

十一、查询

1.简单查询

-- 选择所有列的所有行
SELECT * FROM 表名;

-- 选择特定列的所有行
SELECT 字段名, 字段名 FROM 表名;

-- 带有条件的查询
SELECT 字段名, 字段名 FROM 表名
WHERE 条件;
1
2
3
4
5
6
7
8
9

2.去重查询

使用 DISTINCT 关键字进行去重查询，以获取唯一的值。

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition;
1
2
3

使用 DISTINCT 可以确保查询结果中的值是唯一的，不会包含重复的行。需要注意，DISTINCT 是作用于查询结果的整行，而不是单独的某个列。

3.设置别名

使用 AS 关键字为表和列设置别名

SELECT
    column1 AS alias1,
    column2 AS alias2,
    ...
FROM
    table_name AS alias_table
WHERE
    condition;
    
--省略AS

SELECT
    column1  alias1,
    column2  alias2,
    ...
FROM
    table_name  alias_table
WHERE
    condition;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.条件查询

条件查询是通过 WHERE 子句实现的，它允许你根据指定的条件来筛选出符合条件的行。

1. 等值条件查询

SELECT * FROM table_name
WHERE column_name = value;
1
2

这会选择表中指定列等于给定值的所有行。

2. 不等值条件查询

SELECT * FROM table_name
WHERE column_name <> value;
1
2

这会选择表中指定列不等于给定值的所有行。

3. 范围条件查询

SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;
1
2

这会选择表中指定列在给定范围内的所有行。

4. 空值条件查询

SELECT * FROM table_name
WHERE column_name IS NULL;
1
2

这会选择表中指定列值为 NULL 的所有行。

5. 非空值条件查询

SELECT * FROM table_name
WHERE column_name IS NOT NULL;
1
2

这会选择表中指定列值非 NULL 的所有行。

6. 模糊查询（LIKE）

SELECT * FROM table_name
WHERE column_name LIKE pattern;
1
2

这会选择表中指定列匹配指定模式的所有行，可以使用 % 表示任意字符。

7. IN 条件查询

SELECT * FROM table_name
WHERE column_name IN (value1, value2, ...);
1
2

这会选择表中指定列值在给定值列表中的所有行。

8. NOT IN 条件查询

SELECT * FROM table_name
WHERE column_name NOT IN (value1, value2, ...);
1
2

这会选择表中指定列值不在给定值列表中的所有行。

9. 复合条件查询

SELECT * FROM table_name
WHERE condition1 AND condition2;
1
2

这会选择表中满足所有条件的行，可以使用 AND、OR 和括号来组合多个条件。

5.查询排序

使用 ORDER BY 子句对查询结果进行排序。

1. 单列排序

SELECT * FROM table_name
ORDER BY column_name [ASC | DESC];
1
2

这会选择表中所有列，并按照指定列进行默认的升序排序。

-- 按照 age 列升序排序
SELECT * FROM users
ORDER BY age ASC;
1
2
3

-- 按照 salary 列降序排序
SELECT * FROM employees
ORDER BY salary DESC;
1
2
3

2. 多列排序

SELECT * FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
1
2

这会选择表中所有列，并按照多个列进行排序，按照列的顺序依次进行排序。

-- 先按照 department_id 列升序排序，再按照 salary 列降序排序
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;
1
2
3

3. 使用列索引排序

SELECT * FROM table_name
ORDER BY column_name [ASC | DESC]
  USING column_index;
1
2
3

这会选择表中所有列，并按照指定列使用索引进行排序。USING 子句通常在使用覆盖索引（Covering Index）时使用。

-- 按照 age 列升序排序，使用 age_index 索引
SELECT * FROM users
ORDER BY age ASC
  USING age_index;
1
2
3
4

以上是一些常见的查询排序示例，你可以根据具体需求选择合适的排序方式。需要注意，ASC 是升序（默认值），DESC 是降序。

6.分组查询

分组查询是通过 GROUP BY 子句来实现的，它允许你根据指定的列对查询结果进行分组，并对每个组应用聚合函数。

1. 基本的分组查询

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
1
2
3

这会选择表中的列，并按照指定的列进行分组，对每个组应用聚合函数。

-- 按照 department_id 列分组，计算每个部门的平均工资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
1
2
3
4

2. 多列分组查询

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;
1
2
3

这会选择表中的列，并按照多个列进行分组，对每个组应用聚合函数。

-- 按照 department_id 和 job_title 列分组，计算每个部门和职位的平均工资
SELECT department_id, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_title;
1
2
3
4

3. 使用 HAVING 子句进行条件过滤

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
1
2
3
4

HAVING 子句用于对分组后的结果进行条件过滤。

-- 按照 department_id 列分组，计算每个部门的平均工资，筛选平均工资大于 50000 的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary > 50000;
1
2
3
4
5

4. 使用聚合函数

常用的聚合函数有 COUNT、SUM、AVG、MIN、MAX 等。

-- 按照 department_id 列分组，计算每个部门的员工数量
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
1
2
3
4

这只是一些分组查询的基本示例，实际应用中可以根据具体需求使用不同的聚合函数和分组条件。需要注意，GROUP BY 子句中的列必须在 SELECT 子句中出现，除非它是聚合函数中的参数。

在一条查询语句中，如果有group by语句的话，select 后面只能跟：参加分组的字段，以及分组函数，其他一律不能跟。

优化：先考虑使用where，实在不行才使用having。

6.限制查询数

使用 LIMIT 子句来限制查询结果的行数，LIMIT 子句接受两个参数，第一个参数是返回的行数，第二个参数是从哪一行开始返回。

1. 基本的LIMIT使用

SELECT * FROM table_name
LIMIT number_of_rows;
1
2

这会选择表中的所有列，限制返回的行数为 number_of_rows。

-- 选择 employees 表的前 10 行数据
SELECT * FROM employees
LIMIT 10;
1
2
3

2. 结合OFFSET实现通用分页

SELECT * FROM table_name
LIMIT number_of_rows OFFSET offset_value;
1
2

这会选择表中的所有列，从 offset_value 行开始，限制返回的行数为 number_of_rows，通常用于实现分页。

-- 选择 employees 表的第 11 到 20 行数据，实现分页
SELECT * FROM employees
LIMIT 10 OFFSET 10;
1
2
3

3. 使用LIMIT和ORDER BY进行分页

结合 ORDER BY 和 LIMIT 可以实现基于某个列的分页查询。

-- 按照 salary 降序排序，选择前 5 个最高工资的员工
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;
1
2
3
4

以上是一些基本的使用示例，根据具体的业务需求，你可以调整 LIMIT 和 OFFSET 的值来实现不同的分页效果。需要注意，LIMIT 和 OFFSET 通常用于处理小型结果集，对于大型数据集，可能需要更高效的分页策略。

4.LIMIT实现分页

通用分页：第pageNo页：limit (pageNo - 1) * pageSize，pageSize

7.连接查询

1. 笛卡尔积

笛卡尔积是指两个表之间的所有可能的组合，它是没有连接条件的默认连接方式。返回的结果集行数等于两个表行数的乘积。

SELECT *
FROM table1
CROSS JOIN table2;
1
2
3

两张表连接，表一的字段和表二的每个字段匹配，加了筛选条件匹配次数不减少。

通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数。

2. 内连接

等值连接

等值连接是最常见的连接类型，它基于两个表中的列具有相等值的条件进行连接。

SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
1
2
3
4

非等值连接

非等值连接是指连接条件不仅限于相等，可以是其他比较条件，例如大于、小于等。

SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name > table2.column_name;
1
2
3
4

自连接

自连接是指表与自身进行连接，通常用于在同一表中比较不同行之间的数据。

SELECT *
FROM table1 t1
INNER JOIN table1 t2
ON t1.column_name = t2.column_name;
1
2
3
4

INNER可以省略。

3. 外连接

外连接查询结果数>=内连接查询结果数

左外连接

左外连接返回左表的所有行以及右表中满足连接条件的行，没有匹配的右表行用 NULL 填充。

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
1
2
3
4

右外连接

右外连接返回右表的所有行以及左表中满足连接条件的行，没有匹配的左表行用 NULL 填充。

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
1
2
3
4

任何一个右连接都有左连接的写法

任何一个左连接都有右连接的写法

全连接

全连接返回左表和右表中的所有行，无论是否满足连接条件。MySQL 不直接支持全连接，但可以通过左外连接和右外连接的组合来模拟。

SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name

UNION

SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

--省略OUTER
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name

UNION

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

可以省略OUTER关键字。

4. 多表连接

连接多个表时，可以使用多个 JOIN 子句连接它们。

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name
JOIN table3
ON table2.column_name = table3.column_name;
1
2
3
4
5
6

这种方式适用于多个表之间存在连接关系的情况，可以根据需要连接更多的表。

8.子查询

在 MySQL 中，子查询（也称为内部查询或嵌套查询）可以出现在 SQL 语句的多个位置，具体取决于使用子查询的上下文。select语句中嵌套select语句，被嵌套的select语句称为子查询

1. 在 SELECT 子句

SELECT
  column1,
  column2,
  (SELECT subquery_column FROM another_table WHERE condition) AS subquery_result
FROM
  table_name;
1
2
3
4
5
6

2. 在 FROM 子句

SELECT
  t1.column_name,
  t2.subquery_column
FROM
  table1 t1
JOIN
  (SELECT subquery_column FROM another_table WHERE condition) t2
ON
  t1.id = t2.id;
1
2
3
4
5
6
7
8
9

3. 在 WHERE 子句

SELECT
  column_name
FROM
  table_name
WHERE
  column_name IN (SELECT subquery_column FROM another_table WHERE condition);
1
2
3
4
5
6

4. 在 GROUP BY 子句

SELECT
  column_name,
  COUNT(*)
FROM
  table_name
GROUP BY
  column_name,
  (SELECT subquery_column FROM another_table WHERE condition);
1
2
3
4
5
6
7
8

5. 在 HAVING 子句

SELECT
  column_name,
  COUNT(*)
FROM
  table_name
GROUP BY
  column_name
HAVING
  COUNT(*) > (SELECT subquery_count FROM another_table WHERE condition);
1
2
3
4
5
6
7
8
9

6. EXISTS 和 NOT EXISTS 子句

EXISTS 和 NOT EXISTS 子句用于检查子查询是否返回结果，常用于条件判断。

SELECT column_name
FROM table_name
WHERE EXISTS (SELECT subquery FROM another_table WHERE condition);
1
2
3

7. INSERT、UPDATE 和 DELETE 语句

子查询可以嵌套在 INSERT、UPDATE 和 DELETE 语句中，用于提供被操作的数据。

INSERT 语句

INSERT INTO target_table (column1, column2)
VALUES ((SELECT subquery FROM source_table WHERE condition), value2);
1
2

UPDATE 语句

UPDATE target_table
SET column_name = (SELECT subquery FROM source_table WHERE condition)
WHERE another_condition;
1
2
3

DELETE 语句

DELETE FROM target_table
WHERE column_name = (SELECT subquery FROM source_table WHERE condition);
1
2

以上是子查询在 MySQL 中可能出现的几个位置的详细介绍。在实际使用时，需要根据具体的业务需求和查询场景选择合适的子查询位置。

9.正则表达式

在 MySQL 中，可以使用 REGEXP 或 RLIKE 操作符进行正则表达式查询。这两个操作符是等价的，都用于匹配正则表达式。

SELECT column_name
FROM table_name
WHERE column_name REGEXP 'your_regex_pattern';
1
2
3

或者

SELECT column_name
FROM table_name
WHERE column_name RLIKE 'your_regex_pattern';
1
2
3

请注意，REGEXP 和 RLIKE 都是大小写敏感的。如果你希望进行大小写不敏感的正则匹配，可以使用 REGEXP BINARY 或 RLIKE BINARY。

以下是一个例子，假设我们有一个名为 users 的表，其中有一个 email 列，我们想要查找以 “gmail.com” 结尾的电子邮件地址：

SELECT email
FROM users
WHERE email REGEXP 'gmail\\.com$';
1
2
3

在这个例子中，REGEXP 后面的正则表达式 'gmail\\.com$' 匹配以 “gmail.com” 结尾的字符串，\\ 是为了转义点号，使其匹配实际的点号。

请根据你的实际需求调整正则表达式。MySQL 中的正则表达式语法基本上遵循 POSIX 正则表达式标准。

十二、视图相关的操作

1.视图概念

在MySQL数据库中，视图（View）是虚拟表，是基于查询结果集的可视化的数据库对象。视图并不实际存储数据，而是根据定义的查询动态生成结果。

2.创建视图

在MySQL中，创建视图的语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
1
2
3
4

其中：

• view_name 是视图的名称。
• column1, column2, ... 是视图中包含的列。
• table_name 是从中选择数据的表。
• condition 是可选的筛选条件。

现在，我将为你提供一个单表视图和一个多表视图的创建示例。

单表视图示例：

假设有一个名为 employees 的表，包含以下列：employee_id, first_name, last_name, salary。我们可以创建一个名为 salary_view 的视图，只包含员工的姓名和薪水信息：

CREATE VIEW salary_view AS
SELECT first_name, last_name, salary
FROM employees;
1
2
3

多表视图示例：

假设有两个表，employees 和 departments，其中 employees 包含员工信息，departments 包含部门信息。我们可以创建一个名为 employee_department_view 的视图，将这两个表关联起来：

CREATE VIEW employee_department_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
1
2
3
4

在上述示例中，employee_department_view 视图包含了员工的姓名、薪水以及所属部门的信息，通过连接 employees 和 departments 表实现。

请注意，视图的实际应用取决于具体的业务需求和数据模型，你可以根据自己的情况调整视图的定义。

3.查看视图

使用SHOW CREATE VIEW语句查看视图的详细信息。

SHOW CREATE VIEW view_name;
1

执行上述命令将返回视图的创建语句，包括视图的名称和定义。

查看视图的字段信息与查看数据表的字段信息一样，都是使用 DESCRIBE 关键字来查看的。

DESCRIBE view_name;
--简写
DESC view_name;
1
2
3

4.修改视图

在MySQL中，ALTER VIEW语句用于修改已存在的视图的定义，而不是删除和重新创建。以下是使用ALTER VIEW修改视图的一般语法：

ALTER VIEW view_name AS
SELECT new_column1, new_column2, ...
FROM new_table
WHERE new_condition;
1
2
3
4

其中：

• view_name 是要修改的视图的名称。
• new_column1, new_column2, ... 是新视图中包含的列。
• new_table 是新的数据来源表。
• new_condition 是新的筛选条件。

使用ALTER VIEW时，你只需提供新的视图定义，而不需要删除和重新创建视图。这有助于保留视图的名称和权限设置。

例如，如果你有一个名为 my_view 的视图，想要修改它的定义，可以执行以下命令：

ALTER VIEW my_view AS
SELECT new_column1, new_column2
FROM new_table
WHERE new_condition;
1
2
3
4

这将修改现有的 my_view 视图的定义。请确保新的视图定义与旧的定义兼容，以防止出现问题。

请注意，ALTER VIEW的使用可能受到MySQL版本的限制，确保你的MySQL版本支持这一功能。

5.删除视图

在MySQL中，要删除视图，可以使用DROP VIEW语句。以下是删除视图的语法：

DROP VIEW [IF EXISTS] view_name [, view_name, ...];
1

其中：

• view_name 是要删除的视图的名称。
• IF EXISTS 是可选的，表示如果视图不存在也不会引发错误。

示例：

DROP VIEW IF EXISTS my_view;
1

上述示例中，如果 my_view 视图存在，则它将被删除。如果你省略 IF EXISTS，并且试图删除一个不存在的视图，MySQL 将引发错误。

请注意，删除视图将永久性地移除视图的定义，且与 DROP TABLE 不同，DROP VIEW 不会删除视图所引用的基础表。确保在执行DROP VIEW之前，你了解其影响，并且谨慎操作，以防止数据丢失。

6.嵌套视图

在MySQL中，你可以创建嵌套视图（Nested Views），也就是在一个视图的定义中引用另一个或多个视图。这可以帮助你构建更复杂的查询结构，将多个视图组合在一起以满足特定的查询需求。

以下是一个简单的嵌套视图的示例：

假设有两个基本表：employees 和 departments。

1. 创建第一个视图 employee_view：

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;
1
2
3

2. 创建第二个视图 department_view：

CREATE VIEW department_view AS
SELECT department_id, department_name
FROM departments;
1
2
3

3. 创建嵌套视图 nested_view，引用了前两个视图：

CREATE VIEW nested_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee_view e
JOIN department_view d ON e.department_id = d.department_id;
1
2
3
4

在上述示例中，nested_view 嵌套了 employee_view 和 department_view，通过连接它们的列来创建一个新的查询结构。当你查询 nested_view 时，它会联合使用两个基本视图的数据，从而形成一个包含更多信息的新视图。

请注意，嵌套视图的创建可以根据具体的业务需求和查询复杂性而变化。在设计时，确保你理解每个视图的作用，以及它们之间的关系。

7.视图优势

视图在数据库中有许多优势，这些优势使得它们成为数据库设计和查询中强大的工具。以下是一些视图的优势：

1. 简化复杂查询：
   视图允许将复杂的查询逻辑封装在一个虚拟的表中。这有助于简化对数据库的访问，特别是在需要执行复杂的联接、过滤和计算的情况下。

2. 安全性：
   视图可以用于限制用户对表的访问权限。通过只向用户提供访问特定列或行的视图，可以增加数据的安全性，防止用户访问不应该看到的信息。

3. 数据独立性：
   视图隐藏了底层表的结构。这意味着在不影响应用程序的情况下，可以更改基础表的结构，而不需要对应用程序代码进行修改。

4. 重用性：
   定义好的视图可以被多个查询或应用程序重复使用。这提高了代码的重用性，减少了代码冗余，并简化了应用程序维护。

5. 性能优化：
   在某些情况下，数据库引擎可以优化对视图的查询，提高查询性能。例如，数据库引擎可以使用索引来加速对视图的查询。

6. 抽象层：
   视图提供了一个抽象层，允许数据库设计者隐藏底层表的复杂性。这有助于简化数据库结构，使其更易于理解和维护。

7. 灵活性：
   通过创建不同的视图，可以为不同的用户或应用程序提供不同的数据视图，以满足其特定需求。这提高了数据库的灵活性和可定制性。

8. 简化权限管理：
   视图可以用于简化用户权限管理。通过为用户分配对特定视图的访问权限，可以更细粒度地控制用户对数据的访问。

总体而言，视图是数据库设计中非常有用的工具，它们提供了一种灵活、安全、高效的方式来处理和查询数据库中的数据。

8.面向视图增删改查

在 MySQL 中，对视图的增删改查操作与对普通表的操作类似，但在某些方面可能有所限制。以下是针对视图的常见操作：

1. 查询视图数据：

SELECT * FROM your_view;
1

这条语句将从视图中检索数据，就像从普通表中检索数据一样。

2. 更新视图数据：

UPDATE your_view SET column1 = value1, column2 = value2 WHERE condition;
1

你可以使用 UPDATE 语句来更新视图中的数据，前提是视图是可更新的。可更新的视图是指满足一定条件的视图，例如只包含单个表、不包含聚合函数、不包含 DISTINCT、GROUP BY、HAVING 子句等。

3. 删除视图数据：

DELETE FROM your_view WHERE condition;
1

使用 DELETE 语句从视图中删除数据，同样前提是视图是可更新的。

4. 插入数据到视图：

INSERT INTO your_view (column1, column2, ...) VALUES (value1, value2, ...);
1

在某些情况下，你也可以向视图中插入数据，前提是视图是可更新的，并且满足一定的条件。例如，视图必须仅包含一个表，并且不能包含聚合函数、DISTINCT、GROUP BY、HAVING 子句等。

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原被操作。

通过对视图的操作会影响到原表数据

9.视图的作用

视图在数据库中扮演着多种角色，它们的作用包括但不限于以下几个方面：

1. 简化复杂查询：

视图可以将复杂的 SQL 查询逻辑封装在一个虚拟的表中，使得用户可以通过简单的查询语句访问复杂的数据结构。这样可以提高查询的可读性和易用性。

2. 数据安全性：

通过视图，数据库管理员可以控制用户对数据库中数据的访问权限。管理员可以只向用户提供特定的视图，而不是直接暴露整个表。这样可以提高数据的安全性，并且可以根据用户的角色和权限控制不同用户对数据的访问级别。

3. 数据抽象：

视图可以隐藏底层数据表的物理结构，使得用户只关心逻辑上的数据结构，而不需要了解数据表的具体实现细节。这样可以降低系统的耦合度，并提高数据的抽象性和灵活性。

4. 重用 SQL 逻辑：

通过视图，可以将常用的 SQL 查询逻辑封装为视图，以便在多个查询中重复使用。这样可以提高代码的重用性和维护性，并且可以确保查询逻辑的一致性。

5. 性能优化：

在某些情况下，视图可以提高查询性能。例如，当视图定义了常用的过滤条件或连接条件时，数据库系统可以使用预先计算的结果来加速查询。

总的来说，视图是一种强大的数据库对象，它可以提高数据库的可维护性、安全性和性能，并且可以简化复杂的数据访问操作。

十三、索引

1.索引概念

在数据库中，索引是一种用于提高数据检索速度的数据结构。它类似于书的目录，通过建立一种映射关系，使得数据库系统可以更快速、有效地定位和访问特定数据行。索引通常是基于表中一个或多个列的值构建的，以提高相关查询的性能。

2.索引作用

1. 加速数据检索：

   • 索引允许数据库系统更快速地定位和访问特定行的数据。通过建立索引，数据库可以跳过不需要检索的数据，从而提高查询速度。

2. 优化排序和聚合操作：

   • 对于需要排序或进行聚合操作的查询，索引同样可以提高性能。有序的索引使得排序操作更加高效，而聚合操作也可以更快地执行。

3. 唯一性约束：

   • 主键索引和唯一索引可以用于确保表中的每一行都有唯一标识。这有助于维持数据的一致性和完整性。

4. 加速连接操作：

   • 在连接多个表时，索引可以加速连接操作的执行速度。通过在连接条件的列上创建索引，数据库可以更有效地执行连接操作。

5. 提高多表关联查询性能：

   • 对于多表关联查询，通过在连接条件上创建索引，可以减少关联操作的成本，提高查询性能。

6. 加速搜索操作：

   • 对于包含搜索操作的查询，索引可以显著加速数据的搜索速度。这对于包含WHERE子句的查询尤其重要。

7. 提高应用程序性能：

   • 通过减少数据库查询的响应时间，索引可以显著提高应用程序的性能。这对于需要快速响应用户请求的在线应用程序尤为重要。

8. 降低系统负载：

   • 通过加速查询和减少数据检索所需的资源，索引可以降低数据库系统的整体负载，提高系统的容量和吞吐量。

3.索引分类

1. 主键索引（Primary Key Index）：

   • 主键索引用于唯一标识表中的每一行，确保每个记录都有唯一的标识。
   • 每个表只能有一个主键索引。
   • 主键索引通常是聚集索引，即数据按照主键的顺序存储。

2. 唯一索引（Unique Index）：

   • 唯一索引保证索引列中的所有值都是唯一的，但允许存在空值。
   • 表可以有多个唯一索引。

3. 普通索引（Normal Index）：

   • 普通索引是最基本的索引类型，没有唯一性或主键的要求。
   • 可以用于加速查询，但允许重复值和空值。

4. 全文索引（Full-Text Index）：

   • 全文索引用于进行全文搜索，适用于文本字段的检索。
   • 允许在文本数据中进行关键字搜索，并提供更高级的搜索功能。

5. 组合索引（Composite Index）：

   • 组合索引是建立在多个列上的索引，用于支持查询涉及多个列的条件。
   • 组合索引的创建顺序对性能有影响，遵循最左前缀原则。

6. 空间索引（Spatial Index）：

   • 空间索引用于支持对空间数据类型（如几何数据类型）的检索。
   • 适用于地理信息系统（GIS）等应用场景。

7. 前缀索引（Prefix Index）：

   • 前缀索引是对索引列的前缀进行索引，而不是整个列的值。
   • 用于节省索引空间和提高查询性能。

8. 覆盖索引（Covering Index）：

   • 覆盖索引是一种特殊的索引，包含了查询中所需的所有列，而无需回表到实际的数据行。
   • 可以提高查询性能，减少了不必要的I/O操作。

4.创建索引

当使用 CREATE INDEX 、 CREATE TABLE 和 ALTER TABLE 语句来创建索引时，可以根据具体需求添加一些细节。

1. 使用 CREATE INDEX 语句：

普通索引：

CREATE INDEX idx_name
ON existing_table (column1 ASC, column2 DESC);
1
2

唯一索引：

CREATE UNIQUE INDEX idx_name
ON existing_table (column1);
1
2

2. 使用 CREATE TABLE 语句：

普通索引：

CREATE TABLE new_table (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  INDEX idx_name (name)
);
1
2
3
4
5

唯一索引：

CREATE TABLE new_table (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  UNIQUE INDEX idx_name (name)
);
1
2
3
4
5

3. 使用 ALTER TABLE 语句：

普通索引：

ALTER TABLE existing_table
ADD INDEX idx_name (column1, column2);
1
2

唯一索引：

ALTER TABLE existing_table
ADD UNIQUE INDEX idx_name (column1);
1
2

上述示例中，需要注意以下细节：

• 在 CREATE INDEX 和 CREATE TABLE 语句中，可以使用 ASC（升序）和 DESC（降序）关键字指定索引列的排序方式。
• 在 CREATE TABLE 语句中，使用 INDEX 或 UNIQUE INDEX 关键字定义索引。
• 在 ALTER TABLE 语句中，可以使用 ADD INDEX 或 ADD UNIQUE INDEX。

这些细节可以根据具体的需求进行调整，确保索引的创建符合实际情况和查询需求。

5.查看索引

在MySQL中，你可以使用SHOW INDEX语句来查看表的索引信息。以下是查看索引的基本语法：

SHOW INDEX FROM table_name;
1

其中，table_name是你要查看索引的表的名称。

示例：

SHOW INDEX FROM example_table;
1

这将显示 example_table 表的索引信息，包括索引名称、索引类型、索引中的列、索引的唯一性等。

6.修改索引

不能直接修改索引，先删除，再创建

7.删除索引

在MySQL中，有两种常见的语法用于删除索引：DROP INDEX 和 ALTER TABLE ... DROP INDEX。

1. 使用 DROP INDEX 语法：

DROP INDEX index_name ON table_name;
1

其中，index_name 是要删除的索引的名称，table_name 是包含该索引的表的名称。

示例：

DROP INDEX idx_name ON example_table;
1

2. 使用 ALTER TABLE ... DROP INDEX 语法：

ALTER TABLE table_name
DROP INDEX index_name;
1
2

这种语法允许你在 ALTER TABLE 语句中直接指定要删除的索引。

示例：

ALTER TABLE example_table
DROP INDEX idx_name;
1
2

无论选择哪种语法，都需要谨慎执行删除索引的操作，因为它可能会影响数据库的性能。在生产环境中，建议在维护期间进行此类操作，以减少对数据库性能的影响。

8.索引失效

MySQL中的索引在某些情况下可能不被使用，导致查询性能下降。以下是一些常见的情况：

1. 使用函数或表达式：

   • 当在查询的条件中使用函数、表达式或对列进行运算时，索引可能不会被使用。
   • 例如，WHERE YEAR(date_column) = 2022 可能无法使用 date_column 上的索引。

2. 模糊查询的前置通配符：

   • 在模糊查询中使用前置通配符（LIKE '%value'）会导致索引失效。
   • 索引通常可以用于查询前缀匹配（LIKE 'value%'），但前置通配符可能无法利用索引。

3. 使用 OR 条件：

   • 在查询中使用多个 OR 条件时，可能无法充分利用索引。
   • 尽量将条件组织成能够使用索引的形式，或者考虑使用合适的联合索引。

4. 数据类型不匹配：

   • 当查询条件中的数据类型与索引列的数据类型不匹配时，索引可能不会被使用。
   • 确保查询条件的数据类型与索引列的数据类型一致。

5. 小表的索引失效：

   • 对于非常小的表，使用索引可能不会带来性能提升，因为数据库可能选择全表扫描更为高效。

6. 统计信息不准确：

   • 如果数据库统计信息不准确，优化器可能无法正确选择使用索引的路径。

7. 索引选择度低：

   • 当索引的选择度（唯一值的比例）很低时，数据库可能选择不使用索引，因为全表扫描可能更为高效。

8. 索引过度使用：

   • 如果有太多的索引，优化器可能在选择最优执行计划时变得困惑，导致不使用索引。

9. 表被锁定：

   • 当表被锁定时，查询可能无法使用索引。例如，长时间运行的事务可能会阻塞其他查询的索引使用。

要解决这些问题，可以考虑以下方法：

• 优化查询语句，避免使用导致索引失效的操作符和表达式。
• 确保查询条件中的数据类型与索引列的数据类型匹配。
• 使用合适的索引类型。
• 更新数据库统计信息，以帮助优化器做出更好的执行计划。

优化索引的使用需要具体根据查询和表结构进行分析和调整。

9.索引实现原理

B-Tree（平衡树）索引是 MySQL 中最常见的索引类型之一，它是一种多叉树数据结构，被广泛用于数据库系统中。B-Tree 索引的实现原理如下：

1. 树结构：

B-Tree 是一种多层次的树状结构，其中每个节点都可以拥有多个子节点。树的根节点位于最顶层，叶子节点位于最底层。在 MySQL 中，B-Tree 索引通常被实现为平衡的二叉树（B+Tree），这意味着每个内部节点至少包含两个子节点。

2. 节点结构：

• 根节点： 根节点是整棵树的起始节点，包含指向子节点的指针和键值。

• 内部节点： 内部节点包含指向子节点的指针和键值，用于在树中导航至叶子节点。

• 叶子节点： 叶子节点包含键值和指向实际数据行的指针，叶子节点按照键值的顺序存储。

3. 平衡性：

B-Tree 索引是一种自平衡的树结构，保持了树的平衡性。这意味着每个节点的子树高度差不超过一个固定的阈值，通常为 1。当插入或删除操作导致节点的不平衡时，B-Tree 索引会自动进行节点的分裂或合并，以保持树的平衡性。

4. 有序性：

B-Tree 索引中的节点按照键值的顺序存储，这使得 B-Tree 索引非常适合用于范围查询。在搜索过程中，可以通过二分查找等算法快速定位到目标位置。

5. 插入与删除操作：

• 插入操作： 当执行插入操作时，系统会根据插入的键值找到相应的叶子节点，然后将键值插入到叶子节点中。如果叶子节点已满，可能会触发节点的分裂操作，将节点分裂成两个，并将中间键值提升到父节点。

• 删除操作： 当执行删除操作时，系统会根据删除的键值找到相应的叶子节点，并将该键值从叶子节点中删除。如果删除导致节点的键值数量低于阈值，可能会触发节点的合并操作，将节点合并成一个。

6. 查询操作：

查询操作从根节点开始，根据查询条件逐级向下搜索，直到找到目标叶子节点。然后，系统通过叶子节点中的指针找到相应的数据行，完成查询操作。

综上所述，B-Tree 索引通过自平衡的树状结构、有序的节点存储和高效的插入、删除、查询操作，实现了对数据库中数据的快速定位和访问。

10.什么时候添加索引

在考虑添加索引时，需要考虑以下几个方面：

1. 频繁的查询操作：

如果某个字段经常被用于查询操作，特别是在 WHERE 子句中作为查询条件，那么考虑为该字段添加索引。索引可以显著提高查询的性能，特别是对于大型数据集。

2. 数据列的基数性：

基数性指的是数据列中不同值的数量。如果数据列的基数性很高，即数据列中的唯一值很多，那么添加索引的效果可能会更好。相反，如果基数性很低，即数据列中的唯一值很少，那么添加索引的效果可能会降低。

3. 经常使用的连接操作：

如果某些字段经常被用于连接操作，特别是在 JOIN 子句中作为连接条件，那么考虑为这些字段添加索引。索引可以加速连接操作，提高查询效率。

4. 经常进行排序和分组操作：

如果某个字段经常被用于排序和分组操作，特别是在 ORDER BY 和 GROUP BY 子句中，那么考虑为该字段添加索引。索引可以加速排序和分组操作，提高查询性能。

5. 避免过度索引：

虽然索引可以提高查询性能，但过度索引会增加数据写入的开销，并占用额外的存储空间。因此，应避免为每个字段都添加索引，而是根据实际需求和查询模式选择合适的索引。

6. 考虑索引的大小和维护成本：

索引的大小会影响数据库的内存消耗和磁盘占用，因此需要权衡索引的大小和查询性能。此外，索引的维护成本也需要考虑，包括插入、更新和删除操作的开销。

综上所述，添加索引应该根据具体的查询模式、数据特点和性能需求来决定，需要权衡查询性能和索引维护成本，并避免过度索引。

11.SQL语句索引检索

在 MySQL 中，你可以通过 EXPLAIN 关键字来查看 SQL 语句的执行计划，以了解是否使用了索引。EXPLAIN 语句会返回有关查询执行的信息，包括使用的索引、表扫描方式等。

使用方法如下：

EXPLAIN your_sql_query;
1

例如，如果你有一个查询语句如下：

SELECT * FROM your_table WHERE your_column = 'some_value';
1

你可以通过下面的方式查看执行计划：

EXPLAIN SELECT * FROM your_table WHERE your_column = 'some_value';
1

执行结果中的 key 列会显示使用的索引。如果 key 列为 NULL，则表示该查询未使用索引。如果 key 列包含索引的名称，表示查询使用了该索引。

注意，EXPLAIN 结果中的其他列也提供了关于查询执行计划的其他重要信息，例如表的读取顺序、使用的索引类型、扫描的行数等。

例如，查询结果中的 type 列表示表的访问方式，常见的值有：

• ALL：表示全表扫描。
• index：表示通过索引扫描整个表。
• range：表示使用索引范围扫描。
• ref：表示使用非唯一索引进行等值查询。
• 等等。

通过分析 EXPLAIN 的输出，你可以更好地理解 MySQL 如何执行你的查询，以及是否有效地利用了索引。

十四、存储函数

1.存储函数概念

在MySQL中，存储函数（Stored Functions）是一种用户自定义的、可在数据库服务器上存储的、可重复使用的逻辑单元。这些函数通常用于执行特定的任务，返回一个值，可以在SQL查询中被调用。

以下是存储函数的一些关键概念：

1. 创建和存储： 存储函数是在数据库中创建和存储的。一旦创建，它们可以在整个数据库中被访问和重复使用。

2. 输入参数和返回值： 存储函数可以接受输入参数，并且它们通常返回一个值。参数用于传递信息给函数，而返回值是函数的输出。

3. 逻辑单元： 存储函数包含一个逻辑单元，其中包含了特定任务的实现。这个逻辑单元使用SQL语句和一些编程元素（如条件、循环、变量等）来定义函数的行为。

4. 事务处理： 存储函数可以包含事务处理逻辑，这对于需要保证一系列SQL语句的原子性和一致性的操作很有用。

5. 声明变量： 存储函数中可以使用DECLARE语句声明局部变量，这些变量在函数的整个生命周期内都是可见的。

6. 循环和条件： 存储函数支持使用循环和条件语句，允许进行更复杂的逻辑控制。

7. 使用 BEGIN…END： 存储函数的主体通常使用 BEGIN...END 来定义，类似于其他编程语言中的代码块。

8. 错误处理： 存储函数可以包含错误处理逻辑，例如使用 DECLARE CONTINUE HANDLER 声明处理程序来捕获异常。

以下是一个简单的存储函数的示例，计算两个整数的和：

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = a + b;
    RETURN result;
END;
1
2
3
4
5
6
7

这个函数接受两个整数作为参数，返回它们的和。这是一个简单的例子，实际的存储函数可以包含更复杂的业务逻辑。

2.创建存储函数

在MySQL中，创建存储函数的语法如下

DELIMITER //

CREATE FUNCTION function_name (parameter1 data_type, parameter2 data_type, ...)
RETURNS return_type
BEGIN
    -- 函数体，包含实际逻辑
    DECLARE variable_name data_type;
    -- 初始化变量等
    -- 具体逻辑代码
    RETURN result_value;
END //

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

具体解释：

• DELIMITER //: 改变语句分隔符为 //，这是为了允许在函数体中使用分号（;），可以不要。
• CREATE FUNCTION: 创建存储函数的关键字。
• function_name: 存储函数的名称。
• (parameter1 data_type, parameter2 data_type, ...): 函数的输入参数列表，每个参数都包含参数名和数据类型。
• RETURNS return_type: 定义函数的返回值类型。
• BEGIN: 开始函数体的声明。
• DECLARE variable_name data_type;: 可选，用于声明局部变量。
• -- 具体逻辑代码: 函数体，包含实际的逻辑实现，可以包括各种SQL语句和控制结构。
• RETURN result_value;: 返回语句，用于指定函数的返回值。
• END: 结束函数体的声明。
• //: 结束存储函数的定义，由于改变了语句分隔符，这里使用 // 作为结束标记，可以不要。
• DELIMITER ;: 恢复语句分隔符为默认值 ;，可以不要。

下面是一个实际的例子，创建一个简单的存储函数，计算两个整数的和：

DELIMITER //

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = a + b;
    RETURN result;
END //

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

此函数接受两个整数作为输入参数，计算它们的和，并返回结果。在实际应用中，你可以根据业务需求编写更复杂的存储函数。

3.调用存储函数

调用MySQL中的存储函数的语法如下：

SELECT function_name(parameter1, parameter2, ...);
1

其中：

• function_name 是存储函数的名称。
• parameter1, parameter2, ... 是存储函数的输入参数。

示例，假设有一个名为 add_numbers 的存储函数，接受两个整数参数并返回它们的和：

-- 调用存储函数
SELECT add_numbers(5, 3) AS sum_result;
1
2

这将返回 8，即存储函数计算的结果。你可以将存储函数的调用嵌套在查询语句中，也可以将其作为单独的语句进行调用。

确保调用存储函数的语法中，参数的数量和类型与存储函数定义中的一致。如果存储函数返回一个值，可以通过 AS 关键字为结果指定别名。

4.查看存储函数

查看所有数据库中的

SHOW FUNCTION STATUS;
1

查看某个数据库中的

SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
1

查看指定存储函数的详细信息

SHOW CREATE FUNCTION your_database_name.function_name;
1

5.修改存储函数

没有直接的修改语句，先删除，在创建

6.删除存储函数

要删除MySQL中的存储函数，可以使用DROP FUNCTION语句。以下是删除存储函数的语法：

DROP FUNCTION [IF EXISTS]your_database_name.function_name;
1

替换 'your_database_name' 和 'function_name' 为实际的数据库和存储函数名称。

• IF EXISTS: 这是一个可选的部分，用于在删除之前检查函数是否存在。如果存在，则删除；如果不存在，则不执行任何操作。

示例：

-- 删除存储函数
DROP FUNCTION IF EXISTS your_database_name.function_name;
1
2

请注意，删除存储函数会使引用该函数的触发器、事件或其他依赖项失效。在删除之前，请确保了解对其他对象的潜在影响。在生产环境中，最好在维护期间执行这类操作，以减少对系统的影响。

7.存储函数使用场景

存储函数在MySQL中的使用场景非常广泛，主要用于封装一系列SQL操作并返回一个值。以下是一些常见的存储函数使用场景：

1. 数据计算：

   • 总和、平均值、最大值、最小值等： 创建存储函数用于计算某列的总和、平均值、最大值、最小值等，以简化查询。

     CREATE FUNCTION CalculateSum() RETURNS INT
     BEGIN
         DECLARE result INT;
         SELECT SUM(column_name) INTO result FROM your_table;
         RETURN result;
     END;
     1
     2
     3
     4
     5
     6

2. 字符串操作：

   • 拼接字符串： 创建存储函数用于拼接多个字符串，可以根据业务需求进行定制。

     CREATE FUNCTION ConcatenateStrings(str1 VARCHAR(255), str2 VARCHAR(255)) RETURNS VARCHAR(510)
     BEGIN
         DECLARE result VARCHAR(510);
         SET result = CONCAT(str1, str2);
         RETURN result;
     END;
     1
     2
     3
     4
     5
     6

3. 日期时间操作：

   • 计算日期差： 创建存储函数用于计算两个日期之间的差值。

     CREATE FUNCTION CalculateDateDifference(date1 DATE, date2 DATE) RETURNS INT
     BEGIN
         DECLARE result INT;
         SET result = DATEDIFF(date2, date1);
         RETURN result;
     END;
     1
     2
     3
     4
     5
     6

4. 条件判断：

   • 根据条件返回值： 创建存储函数用于根据条件返回不同的值。

     CREATE FUNCTION GetDiscountPrice(original_price DECIMAL(10, 2), discount_type VARCHAR(10)) RETURNS DECIMAL(10, 2)
     BEGIN
         DECLARE discounted_price DECIMAL(10, 2);
         IF discount_type = 'Percentage' THEN
             SET discounted_price = original_price * 0.9; -- 10% discount
         ELSE
             SET discounted_price = original_price - 5; -- $5 discount
         END IF;
         RETURN discounted_price;
     END;
     1
     2
     3
     4
     5
     6
     7
     8
     9
     10

5. 数据验证：

   • 验证输入参数： 创建存储函数用于验证输入参数，确保符合特定规则。

     CREATE FUNCTION ValidateInputParameter(input_param VARCHAR(255)) RETURNS BOOLEAN
     BEGIN
         DECLARE is_valid BOOLEAN;
         -- 根据规则进行验证
         IF LENGTH(input_param) > 0 AND input_param NOT LIKE '%@%' THEN
             SET is_valid = TRUE;
         ELSE
             SET is_valid = FALSE;
         END IF;
         RETURN is_valid;
     END;
     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11

6. 逻辑复杂的查询：

   • 封装复杂的查询逻辑： 当需要进行一系列复杂的查询操作时，可以将这些逻辑封装在一个存储函数中，提高代码的可维护性。

     CREATE FUNCTION ComplexQueryLogic() RETURNS INT
     BEGIN
         DECLARE result INT;
         -- 复杂的查询逻辑
         SELECT COUNT(*) INTO result FROM your_complex_table WHERE some_condition;
         RETURN result;
     END;
     1
     2
     3
     4
     5
     6
     7

这些只是存储函数可能应用的一些典型场景。实际应用中，存储函数可以根据业务需求进行更加复杂和特定的定制。

十五、存储过程

1.存储过程概念

存储过程（Stored Procedure）是在数据库中预先编译并存储的一系列SQL语句和逻辑操作，它可以像单个操作一样被调用。存储过程通常由数据库管理员或开发人员创建，用于执行一系列常见的或复杂的数据库操作。

以下是存储过程的一些关键概念：

1. 预编译： 存储过程是在数据库中预先编译的，这有助于提高执行速度。相比于单个SQL语句，存储过程的执行更为高效。

2. 封装： 存储过程可以封装一系列SQL语句，使其成为一个单元。这有助于提高代码的可维护性，降低对数据库的访问复杂性。

3. 参数： 存储过程可以接受输入参数，这使得它们更加灵活。参数允许存储过程根据不同的需求执行不同的操作。

4. 返回值： 存储过程可以返回一个或多个值。这些返回值可以用于向调用者提供执行结果、错误信息等信息。

5. 事务控制： 存储过程可以包含事务控制逻辑，例如COMMIT和ROLLBACK语句，以确保一系列操作的原子性和一致性。

6. 条件和循环： 存储过程支持条件语句（如IF和CASE）以及循环结构，使得可以根据不同的情况执行不同的操作。

7. 权限控制： 存储过程的执行权限可以由数据库管理员分配给不同的用户或角色，以保障数据的安全性。

8. 代码复用： 存储过程提供了一种将常用的逻辑封装并复用的方式，可以被多个应用程序或查询调用。

以下是一个简单的存储过程的创建示例：

CREATE PROCEDURE GetEmployeeById(IN employeeId INT)
BEGIN
    SELECT * FROM employees WHERE id = employeeId;
END;
1
2
3
4

这个存储过程接受一个输入参数 employeeId，并返回具有相应ID的员工的信息。

2.创建存储过程

在MySQL中，创建存储过程的语法如下。

DELIMITER //

CREATE PROCEDURE procedure_name(IN parameter1 data_type, IN parameter2 data_type, ...)
BEGIN
    -- 存储过程体，包含实际逻辑
    -- 使用参数和其他SQL语句进行操作
END //

DELIMITER ;
1
2
3
4
5
6
7
8
9

具体解释：

• DELIMITER //: 改变语句分隔符为 //，这是为了允许在存储过程体中使用分号（;）。
• CREATE PROCEDURE: 创建存储过程的关键字。
• procedure_name: 存储过程的名称。
• (IN parameter1 data_type, IN parameter2 data_type, ...): 存储过程的输入参数列表，每个参数都包含参数名、IN 关键字和数据类型。
• BEGIN: 开始存储过程体的声明。
• -- 存储过程体，包含实际逻辑: 存储过程体，包含实际的逻辑实现，可以包括各种SQL语句和控制结构。
• END: 结束存储过程体的声明。
• //: 结束存储过程的定义，由于改变了语句分隔符，这里使用 // 作为结束标记。
• DELIMITER ;: 恢复语句分隔符为默认值 ;。

以下是一个实际的例子，创建一个简单的存储过程，根据员工的ID返回其信息：

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN employeeId INT)
BEGIN
    SELECT * FROM employees WHERE id = employeeId;
END //

DELIMITER ;
1
2
3
4
5
6
7
8

这个存储过程接受一个整数参数 employeeId，并在 employees 表中查找相应ID的员工信息。在实际应用中，你可以根据业务需求编写更复杂的存储过程。

3.调用存储过程

在MySQL中，要调用存储过程，可以使用 CALL 语句。

CALL procedure_name(parameter1, parameter2, ...);
1

其中：

• procedure_name 是存储过程的名称。
• parameter1, parameter2, ... 是存储过程的输入参数。

示例，假设有一个名为 GetEmployeeById 的存储过程，接受一个整数参数 employeeId 并返回相应ID的员工信息：

-- 调用存储过程
CALL GetEmployeeById(1);
1
2

这将执行存储过程并输出符合条件的员工信息。

请确保调用存储过程的语法中，参数的数量和类型与存储过程定义中的一致。如果存储过程返回一个结果集，你可以使用 SELECT 语句来检索结果。例如：

-- 调用存储过程并获取结果集
CALL GetEmployeeById(1);
1
2

在这个例子中，结果集将在存储过程的执行窗口中显示。

请注意，有些MySQL客户端可能会使用不同的语法来调用存储过程，具体取决于客户端的实现。

4.查看存储过程

查看所有数据库中的

SHOW FUNCTION STATUS;
1

查看某个数据库中的

SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
1

查看指定存储过程的详细信息

SHOW CREATE FUNCTION your_database_name.function_name;
1

5.修改存储过程

没有直接的语法

6.删除存储过程

在MySQL中，要删除存储过程，可以使用 DROP PROCEDURE 语句。以下是删除存储过程的语法：

DROP PROCEDURE [IF EXISTS] your_database_name.procedure_name;
1

替换 'your_database_name' 和 'procedure_name' 为实际的数据库和存储过程名称。

• IF EXISTS: 这是一个可选的部分，用于在删除之前检查存储过程是否存在。如果存在，则删除；如果不存在，则不执行任何操作。

示例：

-- 删除存储过程
DROP PROCEDURE IF EXISTS your_database_name.procedure_name;
1
2

请注意，删除存储过程可能会导致引用该过程的触发器、事件或其他依赖项失效。在删除之前，请确保了解对其他对象的潜在影响。在生产环境中，最好在维护期间执行这类操作，以减少对系统的影响。

7.存储过程使用场景

MySQL存储过程在数据库开发中有许多常见的使用场景，以下是一些典型的场景：

1. 复杂的业务逻辑：

   • 存储过程可用于封装复杂的业务逻辑，将一系列SQL语句和控制结构组织成可重用的单元。

     CREATE PROCEDURE ComplexBusinessLogic()
     BEGIN
         -- 复杂的业务逻辑
         -- 包含多个SQL语句和控制结构
     END;
     1
     2
     3
     4
     5

2. 数据验证和处理：

   • 存储过程可以用于验证和处理输入数据，确保符合特定的规范和业务需求。

     CREATE PROCEDURE ProcessUserData(IN username VARCHAR(255), IN email VARCHAR(255))
     BEGIN
         -- 数据验证逻辑
         IF LENGTH(username) > 0 AND LENGTH(email) > 0 THEN
             -- 数据处理逻辑
             INSERT INTO users (username, email) VALUES (username, email);
         END IF;
     END;
     1
     2
     3
     4
     5
     6
     7
     8

3. 事务控制：

   • 存储过程可用于封装事务逻辑，确保多个SQL语句的原子性操作。

     CREATE PROCEDURE TransactionExample(IN amount DECIMAL(10, 2))
     BEGIN
         -- 启动事务
         START TRANSACTION;
     
         -- 执行一系列SQL语句
         UPDATE account SET balance = balance - amount WHERE user_id = 1;
         INSERT INTO transaction_history (user_id, amount) VALUES (1, amount);
     
         -- 提交或回滚事务
         COMMIT;
         -- ROLLBACK;
     END;
     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13

4. 动态SQL：

   • 存储过程允许使用动态SQL，可以根据不同的条件构建不同的SQL语句。

     CREATE PROCEDURE DynamicQueryExample(IN condition INT)
     BEGIN
         DECLARE query VARCHAR(255);
     
         IF condition = 1 THEN
             SET query = 'SELECT * FROM table1;';
         ELSE
             SET query = 'SELECT * FROM table2;';
         END IF;
     
         -- 执行动态SQL语句
         PREPARE dynamic_query FROM query;
         EXECUTE dynamic_query;
         DEALLOCATE PREPARE dynamic_query;
     END;
     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14
     15

5. 生成报表或查询结果：

   • 存储过程可以用于生成复杂的报表或查询结果，将查询逻辑封装在存储过程中。

     CREATE PROCEDURE GenerateReport(IN start_date DATE, IN end_date DATE)
     BEGIN
         -- 复杂的报表生成逻辑
         SELECT * FROM orders WHERE order_date BETWEEN start_date AND end_date;
     END;
     1
     2
     3
     4
     5

6. 权限控制：

   • 存储过程可以用于实现更精细的权限控制，确保只有授权用户能够执行特定的操作。

     CREATE PROCEDURE RestrictedOperation(IN user_id INT)
     BEGIN
         -- 检查用户权限
         IF CheckUserPermission(user_id) THEN
             -- 执行受限操作
         ELSE
             -- 拒绝操作或执行其他逻辑
         END IF;
     END;
     1
     2
     3
     4
     5
     6
     7
     8
     9

这些场景仅仅是存储过程可能应用的一些例子。实际应用中，存储过程可以根据具体业务需求进行更复杂和特定的定制。

十六、变量，游标，流程控制

1. 变量的使用

声明变量：

DECLARE variable_name data_type;
1

这里，variable_name 是变量名，data_type 是数据类型。

赋值：

SET variable_name = some_value;
1

2. 游标的使用

声明游标：

DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM your_table;
1

打开游标：

OPEN cursor_name;
1

获取数据：

FETCH cursor_name INTO variable1, variable2;
1

可以在循环中使用 FETCH 语句获取下一行数据。

关闭游标：

CLOSE cursor_name;
1

3. 流程控制

IF语句：

IF some_condition THEN
    -- 条件为真时执行的逻辑
ELSE
    -- 条件为假时执行的逻辑
END IF;
1
2
3
4
5

CASE语句：

CASE variable_name
    WHEN value1 THEN
        -- 逻辑1
    WHEN value2 THEN
        -- 逻辑2
    ELSE
        -- 默认逻辑
END CASE;
1
2
3
4
5
6
7
8

WHILE循环：

DECLARE counter INT DEFAULT 0;

WHILE counter < 10 DO
    -- 逻辑
    SET counter = counter + 1;
END WHILE;
1
2
3
4
5
6

LOOP循环：

DECLARE counter INT DEFAULT 0;

loop_label: LOOP
    -- 逻辑
    SET counter = counter + 1;
    IF counter = 10 THEN
        LEAVE loop_label;
    END IF;
END LOOP;
1
2
3
4
5
6
7
8
9

LEAVE语句：

LEAVE 用于退出循环。

ITERATE语句：

ITERATE 用于跳到循环开始。

REPEAT循环：

REPEAT
    -- 逻辑
    SET counter = counter + 1;
UNTIL counter = 10 END REPEAT;
1
2
3
4

这些语句的组合可以创建复杂的存储过程和脚本，适应不同的业务逻辑和需求。请注意，MySQL支持的流程控制语句有限，不同版本可能会有差异，建议查阅相关版本的官方文档。

十七、触发器

1.触发器概念

在MySQL中，触发器（Trigger）是一种与表相关联的特殊类型的存储过程。触发器是由事件触发的一组SQL语句，这些事件可以是INSERT、UPDATE、DELETE等。触发器允许在表上执行特定的操作，而无需在应用程序中显式调用。

2.创建触发器

在MySQL中，创建触发器的语法如下：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
1
2
3
4
5
6

具体解释：

• trigger_name: 触发器的名称，应该是唯一的。
• BEFORE/AFTER: 触发器可以在被触发的事件之前或之后执行。
• INSERT/UPDATE/DELETE: 触发器可以与插入、更新或删除事件相关联。
• ON table_name: 触发器所在的表名。
• FOR EACH ROW: 每行触发，表示逻辑会对每一行生效。
• BEGIN...END: 触发器的逻辑部分，包含执行的SQL语句。

下面是一个具体的例子，创建一个BEFORE INSERT触发器，用于在插入数据之前修改将要插入的数据：

CREATE TRIGGER before_insert_example
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    SET NEW.column_name = CONCAT('Modified: ', NEW.column_name);
END;
1
2
3
4
5
6

在这个例子中，触发器修改了将要插入的数据，将列 column_name 的值修改为其原始值前面加上 "Modified: "。这是一个简单的例子，实际上触发器的逻辑可以更加复杂，根据业务需求进行定制。

3.查看触发器

要查看MySQL数据库中的触发器，可以使用以下查询：

SHOW TRIGGERS;
1

此查询将返回数据库中所有表的触发器的列表，包括触发器的名称、事件（BEFORE或AFTER INSERT/UPDATE/DELETE）、表名等信息。

如果你只想查看特定表的触发器，可以使用以下查询：

SHOW TRIGGERS LIKE 'your_table';
1

替换 'your_table' 为实际的表名。

这些查询提供了有关数据库中触发器的基本信息，如果需要更详细的触发器定义，可以查询 information_schema 数据库的 TRIGGERS 表，例如：

SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database';
1

替换 'your_database' 为实际的数据库名。这将返回有关数据库中所有触发器的详细信息，包括触发器的定义和其他属性。

4.修改触发器

1. 删除旧的触发器：

   DROP TRIGGER IF EXISTS your_trigger_name;
   1

   替换 'your_trigger_name' 为实际的触发器名称。

2. 创建新的触发器：
   根据你的需求，使用 CREATE TRIGGER 语句创建新的触发器。

   CREATE TRIGGER your_trigger_name
   BEFORE/AFTER INSERT/UPDATE/DELETE ON your_table
   FOR EACH ROW
   BEGIN
       -- 新的触发器逻辑
   END;
   1
   2
   3
   4
   5
   6

   替换 'your_trigger_name'、BEFORE/AFTER、INSERT/UPDATE/DELETE、your_table 和 BEGIN...END 中的内容为实际的触发器信息和逻辑。

在生产环境中修改触发器时，请确保在维护期间执行这类操作，以避免对正在运行的系统产生不必要的影响。删除触发器可能会导致与触发器相关的业务逻辑失效，因此请谨慎执行。

5.删除触发器

在MySQL中，要删除触发器，可以使用 DROP TRIGGER 语句。

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
1

• IF EXISTS: 这是一个可选的部分，用于在删除之前检查触发器是否存在。如果存在，则删除；如果不存在，则不执行任何操作。

• schema_name: 可选，表示触发器所在的数据库。

• trigger_name: 触发器的名称。

示例：

-- 删除触发器
DROP TRIGGER IF EXISTS your_trigger_name;
1
2

替换 'your_trigger_name' 为实际的触发器名称。

请注意，删除触发器可能会导致与触发器相关的业务逻辑失效，因此在执行之前，请确保了解对其他对象的潜在影响。在生产环境中，最好在维护期间执行这类操作，以减少对系统的影响。

6.触发器使用场景

触发器在数据库中的使用场景主要涉及到自动化处理、数据一致性、审计跟踪等方面。以下是触发器常见的使用场景：

1. 数据一致性和完整性：

   • 外键约束： 使用触发器确保外键关系的一致性，以防止删除或修改主表中的关键数据而未正确处理相关的外键关联。
   • 字段约束： 使用触发器进行字段级别的约束，确保特定字段的取值范围、格式等符合预期。

2. 审计和日志记录：

   • 审计跟踪： 在表上创建触发器，记录用户对数据的操作，包括谁在什么时候进行了插入、更新、删除等操作，用于审计和跟踪。
   • 版本控制： 在表上创建触发器，用于记录数据变更的历史版本，以便回溯和恢复先前的数据状态。

3. 自动化处理：

   • 派生数据： 使用触发器生成派生数据，例如计算总和、平均值等，并将结果存储在另一张表中，以避免手动计算和维护。
   • 自动化约束： 在表上创建触发器，用于自动应用某些业务规则和约束，确保数据符合预期的业务逻辑。

4. 防范性约束：

   • 业务规则： 使用触发器实施业务规则，确保在插入、更新或删除数据时符合业务逻辑，例如检查订单状态、库存数量等。

5. 复杂计算和逻辑：

   • 动态计算： 在表上创建触发器，用于在数据变更时执行复杂的计算，以确保数据的及时性和准确性。
   • 数据转换： 使用触发器处理数据的转换，例如将某个字段值进行格式化或翻译。

6. 实现数据库级别的业务逻辑：

   • 规范化数据： 使用触发器规范化数据，例如将数据转换为小写、大写形式，以保持数据的一致性。
   • 处理复杂逻辑： 在数据库层面实现一些复杂的业务逻辑，减轻应用程序的复杂性。

在使用触发器时，需要注意避免过度使用，以防止引起性能问题。此外，对于复杂的业务逻辑，有时候更好的做法是在应用层面处理，而不是完全依赖数据库触发器。

7.触发器的生命周期

触发器的生命周期指的是触发器在数据库中的执行阶段和时机。触发器可以在触发的事件之前（BEFORE）或之后（AFTER）执行，具体的生命周期如下：

1. BEFORE触发器（BEFORE INSERT/UPDATE/DELETE）：

   • 触发时机： 在触发事件（INSERT、UPDATE、DELETE）之前执行。
   • 执行阶段： 在执行实际的数据库操作之前，允许修改将要插入、更新或删除的数据。

2. AFTER触发器（AFTER INSERT/UPDATE/DELETE）：

   • 触发时机： 在触发事件（INSERT、UPDATE、DELETE）之后执行。
   • 执行阶段： 在执行实际的数据库操作之后，允许在数据已经被修改后执行其他逻辑。

触发器的生命周期可用于执行在数据库操作之前或之后的逻辑，这些逻辑可能包括验证、审计、自动化处理等。选择使用BEFORE还是AFTER触发器取决于具体的业务需求和逻辑。

示例：

1. BEFORE INSERT触发器：

CREATE TRIGGER before_insert_example
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    -- 在插入数据之前执行的逻辑
    -- 可以修改NEW中的字段值
    SET NEW.column_name = CONCAT('Modified: ', NEW.column_name);
END;
1
2
3
4
5
6
7
8

2. AFTER UPDATE触发器：

CREATE TRIGGER after_update_example
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    -- 在更新数据之后执行的逻辑
    -- 可以使用NEW和OLD来比较新旧数据
    IF OLD.column_name != NEW.column_name THEN
        INSERT INTO log_table (message) VALUES ('Column changed from ' + OLD.column_name + ' to ' + NEW.column_name);
    END IF;
END;
1
2
3
4
5
6
7
8
9
10

这些示例演示了BEFORE和AFTER触发器的生命周期，以及在不同时机执行的逻辑。在实际使用时，根据需求选择合适的触发器生命周期，以确保数据库操作和业务逻辑的一致性。

8.触发器的访问NEW和OLD

在MySQL中，触发器可以通过使用 NEW 和 OLD 关键字来访问新旧数据。这两个关键字在触发器的上下文中提供了对插入、更新或删除数据的访问权限。

• NEW: 用于访问新插入或更新的数据。
• OLD: 用于访问旧数据，在UPDATE触发器中可用。

以下是对这两个关键字的使用示例：

1. BEFORE INSERT触发器访问NEW：

CREATE TRIGGER before_insert_example
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    -- 访问并修改将要插入的数据
    SET NEW.column_name = CONCAT('Modified: ', NEW.column_name);
END;
1
2
3
4
5
6
7

2. BEFORE UPDATE触发器访问NEW和OLD：

CREATE TRIGGER before_update_example
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    -- 访问并比较新旧数据
    IF NEW.column_name != OLD.column_name THEN
        -- 执行逻辑
    END IF;
END;
1
2
3
4
5
6
7
8
9

3. AFTER DELETE触发器访问OLD：

CREATE TRIGGER after_delete_example
AFTER DELETE ON your_table
FOR EACH ROW
BEGIN
    -- 访问已删除的旧数据
    INSERT INTO deleted_data_log (column_name) VALUES (OLD.column_name);
END;
1
2
3
4
5
6
7

在上述示例中，NEW 和 OLD 关键字用于访问相应的数据。BEFORE INSERT 触发器中使用 NEW 访问将要插入的数据，BEFORE UPDATE 触发器中同时使用 NEW 和 OLD 访问新旧数据，AFTER DELETE 触发器中使用 OLD 访问已删除的旧数据。这些关键字提供了触发器中访问数据的一种方式，可以根据需要在触发器的逻辑中使用它们。

十八、事务

1.事务概念

在数据库管理系统中，事务（Transaction）是一系列数据库操作的执行单元，被视为一个不可分割的工作单位。事务确保数据库的完整性和一致性，并提供了一种机制来处理并发操作时的数据访问冲突。

事务具有四个关键属性，通常被称为ACID属性：

1. 原子性（Atomicity）： 事务是原子的，要么全部执行，要么全部回滚。如果事务中的任何操作失败，整个事务将回滚到事务开始前的状态，不会留下部分执行的结果。

2. 一致性（Consistency）： 事务的执行将数据库从一种一致状态转移到另一种一致状态。如果事务执行失败，数据库将回滚到一致状态。

3. 隔离性（Isolation）： 多个事务可以并发执行，但各个事务的操作对其他事务是隔离的，一个事务的执行不会影响其他事务的执行。隔离性可以通过锁机制实现。

4. 持久性（Durability）： 一旦事务提交，其结果就是永久性的，即使在系统发生故障的情况下，数据库也能够恢复到事务提交后的状态。

事务的基本语法：

在MySQL中，使用以下语法开始和提交事务：

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作

-- 提交事务
COMMIT;

-- 或者回滚事务
ROLLBACK;
1
2
3
4
5
6
7
8
9
10

示例：

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
UPDATE orders SET status = 'Shipped' WHERE customer_id = 1;

-- 提交事务
COMMIT;
1
2
3
4
5
6
7
8
9

如果在执行一系列数据库操作时发生了错误，可以选择回滚事务：

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
UPDATE orders SET status = 'Shipped' WHERE customer_id = 1;

-- 发生错误，回滚事务
ROLLBACK;
1
2
3
4
5
6
7
8
9

通过使用事务，可以确保在一系列相关操作中，要么全部成功提交，要么全部回滚，从而维护数据库的一致性和完整性。

2.执行事务

在MySQL中，执行事务涉及到开始事务、提交事务和回滚事务等步骤。以下是MySQL中执行事务的基本语法：

1. 开始事务：

START TRANSACTION;
1

此语句用于标识事务的开始。一旦执行此语句，后续的SQL语句将被视为在同一个事务中执行。

2. 提交事务：

COMMIT;
1

此语句用于将之前开始的事务中的所有操作提交到数据库，使其永久生效。

3. 回滚事务：

ROLLBACK;
1

此语句用于取消之前开始的事务中的所有操作，将数据库恢复到事务开始前的状态。

示例：

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
UPDATE orders SET status = 'Shipped' WHERE customer_id = 1;

-- 提交事务
COMMIT;
1
2
3
4
5
6
7
8
9

如果在执行一系列数据库操作时发生了错误，可以选择回滚事务：

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
UPDATE orders SET status = 'Shipped' WHERE customer_id = 1;

-- 发生错误，回滚事务
ROLLBACK;
1
2
3
4
5
6
7
8
9

请注意，事务的使用需要谨慎，确保事务中的操作满足原子性、一致性、隔离性和持久性的要求。在事务中，如果所有操作成功，可以通过COMMIT提交事务；如果发生错误或者需要取消之前的操作，可以通过ROLLBACK回滚事务。

3.设置事务

在MySQL中，事务的自动提交设置是由系统变量autocommit控制的。当autocommit为开启状态时，每个SQL语句都将被视为一个单独的事务并立即提交，这意味着不需要显式地使用COMMIT语句。如果autocommit被禁用，需要使用COMMIT或ROLLBACK语句显式地处理事务。

查看当前autocommit状态：

SHOW VARIABLES LIKE 'autocommit';
1

修改autocommit状态：

• 开启autocommit：

SET autocommit = 1;
1

或者

SET autocommit = ON;
1

• 禁用autocommit：

SET autocommit = 0;
1

或者

SET autocommit = OFF;
1

示例：

-- 查看当前autocommit状态
SHOW VARIABLES LIKE 'autocommit';

-- 禁用autocommit
SET autocommit = 0;

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
UPDATE orders SET status = 'Shipped' WHERE customer_id = 1;

-- 提交事务
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在上述示例中，通过禁用autocommit，事务将从START TRANSACTION开始，一直到COMMIT语句才会提交。如果autocommit处于开启状态，每个SQL语句将被视为一个独立的事务并立即提交。

4.事务的隔离级别

在MySQL中，事务的隔离级别（Isolation Level）是指多个事务之间相互隔离的程度，以防止并发事务执行时出现一致性问题。MySQL支持多个隔离级别，常见的隔离级别包括：

1. READ UNCOMMITTED（未提交读）： 允许一个事务读取另一个事务未提交的数据。最低的隔离级别，存在脏读、不可重复读和幻读问题。

2. READ COMMITTED（提交读）： 一个事务只能读取另一个事务已经提交的数据。避免了脏读问题，但仍可能出现不可重复读和幻读。

3. REPEATABLE READ（可重复读）： 保证在同一个事务中多次执行相同的查询，结果都是一致的。避免了脏读和不可重复读，但仍可能出现幻读。

4. SERIALIZABLE（可串行化）： 最高的隔离级别，确保事务的串行执行。避免了脏读、不可重复读和幻读，但性能较低。

查看当前事务隔离级别：

SHOW VARIABLES LIKE 'transaction_isolation';
1

修改当前会话的事务隔离级别：

SET TRANSACTION ISOLATION LEVEL isolation_level;
1

其中，isolation_level可以是以下值之一：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

示例：

-- 查看当前事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置当前会话的事务隔离级别为REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 开始事务
START TRANSACTION;

-- 执行一系列数据库操作

-- 提交事务
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13

在上述示例中，通过SET TRANSACTION ISOLATION LEVEL语句可以修改当前会话的事务隔离级别。随后的事务将在指定的隔离级别下执行。请注意，修改隔离级别可能会影响数据库性能，需要根据具体场景权衡隔离级别和性能需求。

5.验证事务隔离级别

在MySQL中，可以通过模拟并观察事务的并发操作来验证事务的四种隔离级别。以下是一个简单的例子，假设表名为book：

首先，创建一个名为book的表：

CREATE TABLE book (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  author VARCHAR(255)
);

INSERT INTO book VALUES (1, 'Book A', 'Author A');
INSERT INTO book VALUES (2, 'Book B', 'Author B');
1
2
3
4
5
6
7
8

接下来，使用两个不同的客户端窗口（例如，两个终端窗口或两个数据库连接），模拟四种隔离级别的情况：

1. 读未提交（Read Uncommitted）：

窗口1：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
1
2

窗口2：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE book SET author = 'New Author' WHERE id = 1;
1
2
3

窗口1：

SELECT * FROM book WHERE id = 1;
1

在读未提交的隔离级别下，窗口1可以读取到窗口2未提交的修改。

2. 读提交（Read Committed）：

窗口1：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
1
2

窗口2：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE book SET author = 'New Author' WHERE id = 1;
1
2
3

窗口1：

SELECT * FROM book WHERE id = 1;
1

在读提交的隔离级别下，窗口1只能读取到窗口2已经提交的修改。

3. 可重复读（Repeatable Read）：

窗口1：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
1
2

窗口2：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE book SET author = 'New Author' WHERE id = 1;
1
2
3

窗口1：

SELECT * FROM book WHERE id = 1;
1

在可重复读的隔离级别下，窗口1仍然可以读取到窗口2未提交的修改，但只能读到事务开始时的快照。

4. 串行化（Serializable）：

窗口1：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
1
2

窗口2：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
UPDATE book SET author = 'New Author' WHERE id = 1;
1
2
3

窗口1：

SELECT * FROM book WHERE id = 1;
1

在串行化的隔离级别下，窗口1不能读取到窗口2未提交的修改。读取和写入的操作都被串行执行，确保了最高的隔离性。

十九、用户管理

在MySQL中，用户管理涉及到创建、删除、修改用户，以及为用户分配权限等操作。以下是一些基本的MySQL用户管理命令：

1. 创建用户

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
1

这将创建一个用户名为’username’，只能从本地连接（‘localhost’）登录的用户，并设置密码为’password’。

2. 修改用户密码

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');
1

这将修改指定用户的密码为’new_password’。

3. 授予权限

GRANT permission ON database.table TO 'username'@'localhost';
1

这将为指定用户授予数据库或表的特定权限。例如：

GRANT SELECT, INSERT ON your_database.* TO 'username'@'localhost';
1

4. 撤销权限

REVOKE permission ON database.table FROM 'username'@'localhost';
1

这将从指定用户撤销数据库或表的特定权限。例如：

REVOKE INSERT ON your_database.* FROM 'username'@'localhost';
1

5. 删除用户

DROP USER 'username'@'localhost';
1

这将删除指定用户。

6. 查看用户权限

SHOW GRANTS FOR 'username'@'localhost';
1

这将显示指定用户的授权信息。

7. 查看所有用户

SELECT user, host FROM mysql.user;
1

这将显示所有MySQL用户及其主机信息。

这些是一些基本的MySQL用户管理命令，你可以根据具体需求和安全性考虑使用这些命令进行用户管理。确保设置强密码、限制用户的访问范围，并仔细授予和撤销用户的权限以确保数据库的安全性。

二十、备份与恢复

1.备份类型

当涉及到MySQL数据库备份时，我们可以按照不同的方面进行分类：

1. 备份方法

a. 物理备份：

• 文件系统级备份：直接复制数据库文件，包括数据文件、索引文件和日志文件。
• 数据库快照：使用存储引擎提供的快照功能，如ZFS或LVM快照。

b. 逻辑备份：

• 使用 mysqldump 工具：导出数据库中的数据和结构，生成SQL语句文件。
• 使用 MySQL Workbench：提供图形化的备份和还原工具，支持逻辑备份。

2. 备份对象

a. 数据库级备份：

• 全库备份：备份整个数据库，包括数据和结构。

b. 表级备份：

• 指定表备份：只备份指定的表，包括数据和结构。

c. 数据级备份：

• 表数据备份：只备份表中的数据，不包括表结构。

3. 备份级别

a. 全量备份：

• 备份整个数据库，包括所有数据和结构。

b. 增量备份：

• 只备份自上一次备份以来发生变化的数据，减少备份文件大小。

c. 差异备份：

• 备份自上一次全量备份以来发生变化的数据，相对于增量备份，差异备份是相对于上一次全量备份而言。

4. 备份周期

a. 定期备份：

• 按照一定的时间间隔执行备份，如每天、每周等。

b. 实时备份：

• 连续地对数据库进行备份，保持实时或近实时的备份状态。

2.备份数据库

在MySQL中，可以使用mysqldump命令进行数据库备份。以下是备份一个数据库、备份多个数据库以及备份所有数据库的命令：

1. 备份一个数据库

mysqldump -u username -p your_database > backup.sql
1

此命令将备份名为your_database的数据库，并将备份数据保存到名为backup.sql的文件中。系统将提示输入密码。

2. 备份多个数据库

mysqldump -u username -p --databases db1 db2 db3 > backup_multi.sql
1

此命令将备份名为db1、db2、db3的多个数据库，并将备份数据保存到名为backup_multi.sql的文件中。系统将提示输入密码。

3. 备份所有数据库

mysqldump -u username -p --all-databases > backup_all.sql
1

此命令将备份所有数据库，并将备份数据保存到名为backup_all.sql的文件中。系统将提示输入密码。

请确保替换命令中的username为您的MySQL用户名，并根据需要修改文件名和数据库名称。输入命令后，系统会要求输入密码以完成备份过程。备份文件可以是纯文本SQL文件，也可以是经过压缩的文件（例如.gz）以节省存储空间。

3.恢复数据库

在MySQL中，可以使用mysql命令或者source命令来恢复数据库。以下是两种常见的方式：

1. 使用 mysql 命令

mysql -u username -p your_database < backup.sql
1

此命令将从名为 backup.sql 的备份文件中恢复数据库数据到名为 your_database 的数据库。系统将提示输入密码。

2. 使用 source 命令

首先登录到 MySQL 控制台：

mysql -u username -p
1

然后在 MySQL 控制台中执行以下命令：

source /path/to/backup.sql;
1

这将从名为 backup.sql 的备份文件中恢复数据库数据。请确保替换命令中的 username、your_database 和 /path/to/backup.sql 为相应的值，并根据系统提示输入密码。

无论使用哪种方式，都需要确保备份文件的路径和文件名正确，并根据实际情况提供正确的用户名、数据库名和密码。在执行恢复命令之前，最好先确保数据库是空的或者与备份文件相匹配，以避免数据冲突。

二十一、日志

MySQL数据库的日志系统包含多个类型的日志，用于记录数据库的运行和操作，以便进行故障排查、恢复和性能优化。以下是MySQL数据库日志的主要类型：

1. 错误日志

错误日志用于记录MySQL服务器在运行时遇到的错误和警告信息。这些信息包括数据库启动和关闭的事件，以及运行过程中的错误信息。错误日志的位置通常在MySQL的数据目录下，文件名为hostname.err，其中hostname是服务器的主机名。

2. 查询日志

查询日志用于记录所有到达MySQL服务器的查询语句，包括成功和失败的查询。启用查询日志可能会影响性能，因为每个查询都将被记录。查询日志可以通过设置log_queries_not_using_indexes等参数进行配置。

3. 慢查询日志

慢查询日志记录执行时间超过阈值的查询语句，帮助识别性能问题。可以通过设置slow_query_log和long_query_time等参数启用慢查询日志。慢查询日志的位置通常也在MySQL的数据目录下，文件名为hostname-slow.log。

4. 二进制日志

二进制日志记录了对数据库进行更改的所有数据更改操作，包括插入、更新和删除。它是MySQL数据库复制和恢复的关键组成部分。二进制日志的位置通常在MySQL的数据目录下，文件名为hostname-bin.xxxxxx，其中xxxxxx是一个数字。

5. 事务日志

事务日志（也称为redo log）用于记录正在进行的事务操作，以确保数据库的事务的原子性。事务日志的位置通常在MySQL的数据目录下，文件名为ib_logfileN，其中N表示日志文件的序号。

6. InnoDB错误日志

InnoDB错误日志包含了InnoDB存储引擎的特定错误和警告信息。它通常位于MySQL的数据目录下，文件名为ib_logfileN。

查看日志

要查看MySQL错误日志，可以使用以下方法：

cat /var/log/mysql/error.log
1

请注意，实际的日志文件路径可能因系统和MySQL配置而异。

这些日志对于监控和维护MySQL数据库非常重要，可以帮助管理员及时发现和解决问题。