MySQL基础知识学习指南

标签：学习指南 语句 存储 数据库 tableName 基础知识 columnName MySQL

一、MySQL-DDL（Data Definition Language）

在数据库管理领域，MySQL 是广泛应用的关系型数据库管理系统。其中，数据定义语言（Data Definition Language，简称 DDL）起着至关重要的作用，它主要用于对数据库内部对象进行创建、删除、修改等操作。本文将深入且系统地介绍 MySQL 中的 DDL 相关知识。

1. 简介

1.1 基本概念

MySQL DDL 的主要功能是对数据库中的各类对象进行定义和管理。其操作涵盖了数据库、表、视图、索引等多种数据库组件。在操作过程中，主要使用的关键字包括create（创建）、drop（删除）、alter（修改） 。这些操作通常由数据库管理员（DBA）执行，因为他们负责数据库的整体架构设计、维护以及确保数据的完整性和安全性。

1.2 与数据库管理的关系

DDL 是数据库管理的基础工具之一。通过 DDL，DBA 能够创建满足业务需求的数据库结构，根据业务变化灵活修改数据库对象，以及在必要时删除不再使用的对象，从而优化数据库的性能和资源利用。

2. 数据库操作

2.1创建数据库

使用CREATE DATABASE语句来创建一个新的数据库。语法如下：

CREATE DATABASE dbName;

这里的dbName是你想要创建的数据库名称，需遵循 MySQL 的命名规则。

2.2 查看数据库创建语句

若想查看创建某个数据库时的详细语句，可以使用SHOW CREATE DATABASE语句：

SHOW CREATE DATABASE dbName;

这条语句会返回创建指定数据库的完整 SQL 语句，包括数据库的字符集、排序规则等详细信息。

2.3 显示所有数据库

通过SHOW DATABASES语句可以查看当前 MySQL 服务器上所有的数据库：

SHOW DATABASES;

在 MySQL 中，有几个特殊的数据库，它们各自承担着重要的系统功能：

• information_schema：存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等。它就像是数据库的 “信息宝库”，为数据库管理员提供了关于数据库结构和元数据的详细信息。
• Performance_schema：记录了 MySQL 实时性能消耗情况。通过分析这个数据库中的数据，管理员可以了解到数据库在运行过程中的性能瓶颈，从而进行针对性的优化。
• mysql：存储了系统的用户权限信息。所有用户在数据库中的访问权限、角色等信息都存储在这个数据库中，确保了数据库的安全性。

2.4 显示数据库相关信息

• 服务器状态信息：使用SHOW STATUS语句可以显示服务器的各种状态信息，例如当前连接数、查询执行次数等，帮助管理员了解数据库的运行状况。

SHOW STATUS;

• 当前用户信息：SHOW GRANTS语句用于显示当前用户的权限信息，这对于管理用户权限和确保数据库安全非常重要。

SHOW GRANTS;

• 服务器错误信息：SHOW ERRORS语句可以查看服务器产生的错误信息，在排查数据库问题时十分有用。

SHOW ERRORS;

• 警告信息：SHOW WARNINGS语句用于显示服务器产生的警告信息，帮助管理员及时发现潜在的问题。

SHOW WARNINGS;

• 存储引擎信息：SHOW ENGINES语句可以显示服务器支持的存储引擎，管理员可以根据业务需求选择合适的存储引擎。

SHOW ENGINES;

2.5 选择数据库

在进行数据库操作之前，需要先选择要操作的数据库，使用USE语句实现：

USE dbName;

一旦执行了这条语句，后续的操作（如创建表、查询数据等）都会在指定的数据库中进行。

2.6 删除数据库

使用DROP DATABASE语句可以删除指定的数据库，同时该数据库中的所有表和数据也会被全部删除。请谨慎使用此操作，以免造成数据丢失。

DROP DATABASE dbName;

3. 表操作

3.1 创建表

使用CREATE TABLE语句来创建表，其基本语法如下：

CREATE TABLE tableName(
columnName dataType constraints,
…
) constraints;

在这个语句中，tableName是表的名称，columnName是列名，dataType是数据类型，constraints是约束条件。约束条件用于定义列的特性，如是否为主键、是否允许为空等

3.2 查看表结构

• 查看所有表：使用SHOW TABLES语句可以查看当前数据库中的所有表。

SHOW TABLES;

• 查看表结构详情：
• DESC tableName语句是一种简洁的查看表结构的方式，它会列出表中各列的名称、数据类型、是否允许为空、默认值等基本信息。

DESC tableName;

​

• SHOW COLUMNS FROM tableName语句也可以查看表的列信息，与DESC语句功能类似，但在显示格式和部分细节上可能略有不同。

SHOW COLUMNS FROM tableName;

• DESCRIBE tableName与DESC tableName功能相同，也是用于查看表的结构。

DESCRIBE tableName;

• SHOW CREATE TABLE tableName语句则会返回创建表的完整 SQL 语句，包括表的所有定义信息，如列定义、约束条件、存储引擎等。

SHOW CREATE TABLE tableName;

3.3 约束条件

在创建表时，约束条件用于确保数据的完整性和一致性。常见的约束条件包括：

PRIMARY KEY	主键约束，用于唯一标识表中的每一行记录，并且主键列的值不能为空。一个表只能有一个主键。
NOT NULL	非空约束，指定该列不允许存储NULL值。
UNIQUE	唯一约束，确保该列的值在表中是唯一的，但可以为NULL（除非同时指定了NOT NULL约束）。
BINARY	用于字符类型的字段，使字段在比较和排序时按二进制字节进行操作，区分大小写。
UNSIGNED	通常与数值类型配合使用，表示该字段只能存储非负的数值。
ZEROFILL	用于数值类型的字段，当字段存储的值位数小于其定义的显示宽度时，会在数值前面用0填充至指定宽度。
AUTO_INCREMENT	自动递增约束，常用于主键字段。每当插入一条新记录时，如果该字段没有显式指定值，数据库会自动为其分配一个比上一条记录该字段值更大的整数值。
DEFAULT value	默认值约束，为字段指定一个默认值。当插入数据时若该字段未显式赋值，就会采用这个默认值。

3.4 删除表

使用DROP TABLE语句可以删除指定的表及其所有数据。请谨慎操作，以免误删重要数据。

DROP TABLE tableName;

​

3.5 创建包含主键的表

• 方式一：在列定义时直接指定主键。

CREATE TABLE tableName(
columnName columnType constraints PRIMARY KEY,
…
);

• 方式二：在表定义的末尾统一指定主键。

CREATE TABLE tableName(
    columnName columnType constraints,
    …,
    PRIMARY KEY（columnName,…）
);

3.6 添加主键

如果在创建表时没有指定主键，后续可以使用ALTER TABLE语句添加主键。

ALTER TABLE tableName ADD PRIMARY KEY(columnName,…);

3.7 删除主键

同样使用ALTER TABLE语句来删除主键。

ALTER TABLE tableName DROP PRIMARY KEY;

3.8 两表建立关系条件

当两个表之间需要建立关系（如外键关系）时，需要满足以下两个条件：

• 存储引擎相同：两个存在关系的表存储引擎必须相同，否则可能会导致数据一致性问题和性能问题。
• 数据类型相同：用于建立关系的列（外键和主键）的数据类型必须相同，以确保数据的正确匹配和关联。

3.9 创建包含外键的表

使用CREATE TABLE语句创建包含外键的表，语法如下：

CREATE TABLE tableName(
columnName dataType constraints,
…
[CONSTRAINT foreignKeyConstraintName] 
FOREIGN KEY(columnName, …) REFERENCES 
mainTableName (primaryKeyColumnName,…) 
foreignKeyOption
);

在这个语句中，FOREIGN KEY指定了外键列，REFERENCES指定了外键所引用的主表和主键列，foreignKeyOption是外键的选项，如ON DELETE和ON UPDATE的相关设置。

3.10 添加外键

如果表已经创建，可以使用ALTER TABLE语句添加外键。

ALTER TABLE tableName ADD 
[CONSTRAINT foreignKeyConstraintName] 
FOREIGN KEY(foreignKeyName) REFERENCES 
mainTableName(primaryKeyName) foreignKeyOption;

3.11 删除外键

使用ALTER TABLE语句删除外键。

ALTER TABLE tableName 
DROP FOREIGN KEY 
foreignKeyConstraintName;

3.12 外键约束

外键约束中的ON DELETE和ON UPDATE属性用于定义当主表中的数据发生删除或更新操作时，从表中相关数据的处理方式：

• SET NULL：当主表数据删除或更新时，将从表对应外键值设为null。例如，ON DELETE SET NULL表示主表数据删除时，从表对应外键的数据设为null；ON UPDATE SET NULL表示主表数据更新时，从表对应外键的数据设为null。
• CASCADE（级联）：主表数据删除或更新时，从表对应数据也会随之删除或更新。例如，ON DELETE CASCADE表示主表数据删除时，从表对应数据也删除；ON UPDATE CASCADE表示主表主键数据更新时，从表对应数据也更新。
• RESTRICT（约束）：如果从表中有相关数据，主表不允许进行删除和更新操作。在 InnoDB 存储引擎中，NO ACTION与RESTRICT效果相同，都表示拒绝删除或更新。

3.13 外键检查

• 查看外键检查状态：使用SELECT @@FOREIGN_KEY_CHECKS语句可以查看当前数据库中外键检查的状态。如果返回结果为1，说明外键检查是开启的；如果返回0，则表示外键检查处于关闭状态。

SELECT @@FOREIGN_KEY_CHECKS;

• 关闭外键检查功能：在某些情况下，如数据迁移或批量插入数据时，可能需要暂时关闭外键检查，可以使用以下语句：

SET FOREIGN_KEY_CHECKS=0;

• 开启外键检查功能：在完成相关操作后，应及时开启外键检查，以确保数据的完整性，使用以下语句：

SET FOREIGN_KEY_CHECKS=1;

4. 修改表

4.1 改表名

使用ALTER TABLE语句修改表名，语法如下：

ALTER TABLE tableName RENAME [TO] newTableName;

这里的newTableName是修改后的表名称。

​

4.2 增加列

使用ALTER TABLE语句增加列，语法如下：

ALTER TABLE tableName ADD [COLUMN] 
columnDefinition[FIRST|AFTER columnName];

columnDefinition是新增列的定义，包括列名、数据类型和约束条件等。FIRST表示将新增列放在表的第一列，AFTER columnName表示将新增列放在指定列columnName之后。

​

4.3 删除列

使用ALTER TABLE语句删除列，语法如下：

ALTER TABLE tableName DROP [COLUMN] columnName;

这里的columnName是要删除的列名称。

​

4.4 修改列类型

使用ALTER TABLE语句修改列的数据类型，语法如下：

ALTER TABLE tableName MODIFY [COLUMN] 
columnDefinition[FIRST|AFTER columnName];

columnDefinition是修改后的列定义，包括新的数据类型和约束条件等。

​

4.5 改列名

使用ALTER TABLE语句修改列名，语法如下：

ALTER TABLE tableName CHANGE [COLUMN] 
oldColumnName columnDefinition
[FIRST|AFTER columnName];

oldColumnName是原来的列名，columnDefinition是修改后的列定义，包括新的列名、数据类型和约束条件等。CHANGE语句也可以只修改数据类型，不修改列名。

​

4.6 修改列的顺序

使用ALTER TABLE语句修改列的顺序，语法如下：

ALTER TABLE tableName [ADD|CHANGE|MODIFY] 
[COLUMN] columnDefinition[FIRST|AFTER columnName];

通过FIRST或AFTER columnName来指定列的新位置。需要注意的是，CHANGE、FIRST、AFTER COLUMN这些关键字都属于 MySQL 在标准 SQL 上的扩展，在其他数据库中不一定适用。

​

​

4.7 更改存储引擎

使用ALTER TABLE语句更改表的存储引擎，语法如下：

ALTER TABLE tableName ENGINE=engineName;

这里的engineName是要更改的存储引擎名称，如InnoDB、MyISAM等。

MySQL DDL 是数据库管理和开发中不可或缺的一部分。通过熟练掌握 DDL 的各种操作，数据库管理员和开发人员能够高效地创建、管理和维护数据库结构，确保数据库的性能、完整性和安全性。希望本文能为读者提供全面而深入的 MySQL DDL 知识，助力大家在数据库领域的学习和实践。

二、MySQL-DML（Data Manipulation Language）

在数据库操作领域，MySQL 的 DML（Data Manipulation Language）语句是软件开发人员频繁运用的关键工具，其主要负责对数据库内的数据执行插入、删除、更新和查询等操作，涉及的关键字包括 insert、delete、update 和 select。

1. 插入记录

1.1 基础插入语法

INSERT INTO 语句是实现数据插入的核心，其基本格式为：

INSERT INTO tableName[([columnName,]...)] 
VALUES(value,...);

在实际插入操作中，空字段、有默认值的字段和自增字段可省略，values 后仅需填写对应非省略字段的值。特别要注意的是，字符串类型和日期类型数据需用单引号括起，而数值型、逻辑型数据则无需添加。

1.2 多条记录插入

为提高插入效率，可一次性插入多条记录，语法如下：

INSERT INTO tableName (columnName, …) 
values (value, …), …;

2. 删除记录

2.1 单表数据删除

DELETE FROM 语句用于删除表中的数据，其语法为：

DELETE FROM tableName [WHERE condition];

.

2.2 多表数据删除

如需同时删除多个表中的记录，可使用以下语法：

DELETE tableName, … FROM tableReferences
[WHERE condition];

3. 更新记录

3.1 单表数据更新

UPDATE 语句用于更新表中的数据，语法如下：

UPDATE tableName SET 
columnName=value|expression|DEFAULT, ...
[WHERE condition];

3.2 多表数据更新

同时更新多个表的数据时，语法为：

UPDATE tableName,… SET 
tableName.columNmae=value|expression|DEF
AULT,… [WHERE condition];

多表更新常用于依据一个表的字段动态更新另一个表的字段，如：

UPDATE orders, order_details SET 
orders.total_amount = orders.total_amount + 
order_details.quantity * order_details.unit_price 
WHERE orders.order_id = order_details.order_id;

4. 查询记录

4.1 基本查询操作

SELECT 语句是查询数据的基础，语法为：

SELECT *|columnName,... FROM tableName
[WHERE condition];

4.2 去重查询

使用 DISTINCT 关键字可实现查询结果去重，语法为：

SELECT DISTINCT columnName,... FROM tableName;

4.3 条件查询

 WHERE 子句用于实现条件查询，其条件可运用比较运算符、逻辑运算符等，例如：、

4.4 排序查询

ORDER BY 子句用于对查询结果进行排序，默认升序，语法为：

SELECT * FROM tableName [WHERE condition] 
[ORDER BY columnName|expresions|position 
[ASC|DESC],...];

4.5 限制查询结果

LIMIT 子句用于限制查询结果的数量，语法为：

SELECT … LIMIT offset, rowCount; //offset 默认 0

常与 ORDER BY 配合用于分页显示，例如：

4.6 聚合查询

聚合操作可对数据进行汇总统计，语法为：

SELECT [columnName, …] funName FROM tableName
[WHERE condition] [GROUP BY columnName, … [WITH 
ROLLUP]] [HAVING condition];

其中，funName 代表聚合函数，如 sum (columnName)、count (*|columnName|number)、max (columnName)、min (columnName)、avg (columnName) 等。GROUP BY 用于分类聚合，WITH ROLLUP 对分类聚合结果再汇总，HAVING 对分类后的结果进行条件过滤，且 HAVING 用于聚合后过滤，WHERE 用于聚合前过滤，应优先使用 WHERE。例如：

• 统计公司员工总数：

• 统计各部门员工人数：

• 统计各部门人数及总人数：

• 统计人数大于1人的部门的人数

• 统计公司所有员工的薪水总额、最高和最低薪水

5. 连接查询

 5.1 连接的必要性

在实际应用中，当查询信息涉及多张表时，就需要使用连接操作。例如，查询学生的课程成绩，需从学生表、课程表和成绩表中获取学生姓名、课程名称和成绩等信息，此时就需确定表间关系并进行连接。

5.2 内连接

内连接仅选取两张表中相互匹配的记录，语法为：

SELECT *|columnName,… from tableName,… 
[WHERE tableName1.columnName=
tableName2.columnName];

 5.3 外连接

外连接会选取不匹配的记录，包括左连接和右连接。

• 左连接：包含左边表的所有记录及右边表与左表匹配的记录，语法为：

SELECT columnName,… FROM tableName1 LEFT JOIN 
tableName2 ON 
tableName1.columnName=tableName2.columnName;

• 右连接：包含右边表的所有记录及左边表与右表匹配的记录，语法为：

SELECT columnName,… FROM tableName1 RIGHT JOIN 
tableName2 ON 
tableName1.columnName=tableName2.columnName;

6. 子查询

 6.1 子查询的概念

子查询是指查询条件为另一个 SELECT 语句的结果，常用关键字有 IN、NOT IN、=、!=、EXISTS、NOT EXISTS 等。适用于查询需求复杂、一次查询难以获取结果的场景，可将复杂问题拆解为简单问题。

6.2 子查询示例

需注意，当子查询记录数唯一时，可用 = 代替 in，但要确保子查询结果唯一，否则会报错。

6.3 子查询与表连接的转换

在某些情况下，子查询可转换为表连接。但 MySQL 4.1 之前版本不支持子查询，需用表连接替代，且在很多场景中表连接可优化子查询。

7. 记录联合

通过 UNION 和 UNION ALL 可将多个表的查询结果合并显示。UNION ALL 直接合并结果集，UNION 则会去除重复记录，语法为：

SELECT * FROM t1 UNION|UNION ALL SELECT 
* FROM t2 … UNION|UNION ALL SELECT * 
FROM tn;

将emp和dept表中的部门编号的集合显示出来

MySQL DML 为数据库数据操作提供了强大而灵活的功能，熟练掌握这些操作对于高效管理和开发数据库应用至关重要。通过合理运用这些语句，能够满足各种复杂的数据处理需求，确保数据库系统的稳定运行和数据的有效利用。

三、MySQL-DCL（Data Control Language）

在 MySQL 数据库体系中，数据控制语言（Data Control Language，DCL）扮演着极为关键的角色，主要由数据库管理员（DBA）用于管理系统中的对象权限，确保数据库的安全性与合规性。

1. 用户与权限查询

1.1 系统用户及权限查询

• 若要查看系统中的所有用户及其对应的主机信息，可执行以下语句

SELECT user,host FROM mysql.user;

• 显示当前用户的所有权限信息，使用：

SHOW GRANTS;

• 若只想查看特定用户（如 username）的权限，则执行：
   

SHOW GRANTS FOR username;

1.2 当前用户与数据库查询

• 确定当前登录的用户，通过：

SELECT USER();

• 了解当前正在操作的数据库，可执行：

SELECT DATABASE();

2. 用户管理操作

2.1 用户创建

创建新用户的语法如下：

CREATE USER username [IDENTIFIED BY 'password'];

其中，username 可以是 username[@localhost|IP|%] 的形式，password 则是该用户登录数据库所需的密码。

2.2 用户登录

在命令行下进入 mysql 安装目录的 bin 目录后，使用以下命令登录：
 

MYSQL [–H ip] –U userName –P

也可通过设置环境变量（如 D:\Program Files\mysql5.7.18-win32\bin）来简化登录操作。

2.3 用户名修改

若需更改用户名，可使用：

RENAME USER oldName to newName;

2.4 密码修改

• 修改当前用户密码

SET PASSWORD='password';

• 修改指定用户（userName）密码：

SET PASSWORD FOR userName=PASSWORD('password');

2.5 用户删除

删除指定用户的语句为：

DROP USER userName;

3. 权限管理操作

3.1 授权

授予用户权限的语法为：

GRANT privileges ON scopes TO 
userName@[localhost| hostname |%];

其中，privileges 可以是 creat、alter、drop、insert、delete、update、select、all、usage 等；scopes 可以是所有数据库（*.*）、一个特定数据库（dbName.*）或一个具体表（dbName.tableName）。

3.2 权限收回

收回用户权限的语句为：

REVOKE privileges ON scopes from userName;

执行权限收回操作后，需执行以下语句使更改生效：

FLUSH PRIVILEGES;

3.3 授权与收回示例

• 创建新用户 testuser：

create user testuser; 

• 查看 testuser 的初始权限：

show grants for testuser;

• 授予 testuser 对所有数据库的查询权限：

grant select on *.* to testuser;

• 打开新窗口用 testuser 登录以测试权限。
• 收回 testuser 的所有权限：

revoke all on *.* from testuser;

• 关闭 testuser 登录窗口，再次打开新窗口登录测试权限是否已成功收回。

4. 帮助信息查阅

4.1 帮助类型

在使用 MySQL 过程中，可能会遇到忘记语法、不清楚字段类型取值范围、不了解支持函数或不确定是否支持某个功能等问题，此时可查阅帮助信息。

4.2 按层次查阅帮助

• 最基础的帮助命令为：

help

• 若想查看可供查询的分类，可执行：

help CONTENTS

• 针对感兴趣的具体类别进一步查看详细信息，使用：

help 类别名称

4.3 快速查阅语法帮助

如需快速查阅特定语法，可利用关键字进行查询，例如：

help show;

MySQL DCL 为数据库权限管理提供了一套完善且高效的机制。通过上述丰富多样的操作指令，数据库管理员能够精准且灵活地把控用户权限，确保每个用户在数据库系统中拥有恰当的访问级别和操作权限。熟练掌握 DCL 的各类操作，是保障数据库安全稳定运行、维护数据完整性与保密性的关键所在，对于构建可靠且安全的数据库环境起着不可或缺的重要作用，助力企业和开发者在数据库管理工作中实现高效、有序的权限管控。

四、MySQL 支持的数据类型详解

在 MySQL 数据库中，数据类型的合理选择对于数据的存储、检索和操作效率至关重要。以下将详细介绍 MySQL 支持的各类数据类型。

1. 数值类型

1.1 严格数字类型

MySQL 支持标准 SQL 的数值类型中的严格数字类型，如 INT、SMALLINT、DECIMAL、NUMERIC 等。这些类型在数据存储和计算中遵循严格的数值规则，能确保数据的准确性和一致性。

1.2 近似数值类型

包括 FLOAT、REAL、DOUBLE PRECISION 等。其中，FLOAT 小数精度可达到 24，DOUBLE 可达到 53。在进行科学计算或对精度要求不是极高的数值处理场景中较为常用，但需要注意其近似性可能带来的细微误差。

1.3 扩展类型

TINYINT、MEDIUMINT、BIGINT 等属于扩展类型。不同整数类型在字节占用和取值范围上有所差异，例如 TINYINT (M) 占用 1 字节，取值范围是有符号 -128~127，无符号 0~255；而 BIGINT (M) 占用 8 字节，取值范围有符号 -9223372036854775808~9223372036854775807，无符号 0~18446744073709551615。在实际应用中，应根据数据的可能取值范围合理选择整数类型，避免出现 out of range 错误提示 。

1.4整数类型

类型名	字节	取值范围	字符长度(最大显示宽度， 含符号位)
TINYINT(M)	1	有符号-128~127 无符号0~255	4
SMALLINT(M)	2	有符号-32768~32767 无符号0~65535	5
MEDIUMINT(M)	3	有符号-8388608~8388607 无符号0~1677215	9
INT(M)	4	有符号-2147483648~2147483647 无符号0~4294967295	11
BIGINT(M)	8	有符号-9223372036854775808~ 9223372036854775807 无符号0~ 18 446 744 073 709 551 615	20

在使用 MySQL 数据类型时，若进行超出类型范围的操作，系统会给出 out of range 错误提示。因此，需要依据实际应用场景来确定数据的取值范围，进而根据该取值范围准确选择合适的数据类型。特别要注意的是，当指定 ZEROFILL 时，数据类型会自动添加 UNSIGNED 属性。

• 填充

1.5 浮点数与定点数

• 浮点数：如前所述，FLOAT 和 DOUBLE 有各自的精度和取值范围，其存储格式和计算方式适合处理一般的数值数据，但在高精度要求场景下可能存在精度损失。FLOAT小数精度可达到24，DOUBLE可达到 53

类型名	字节	取值范围
FLOAT(M,D)	4	-3.402823466E+38~-1.175494351E-38 0 1.175494351E-38~3.402823466E+38	M是总宽度，D是小数 位数。默认(10,2),
DOUBLE(M,D)	8	-1.7976931348623157E+308 ~2.2250738585072014E-308 0 2.2250738585072014E-308 ~1.7976931348623157E+308	默认 (16 ,4)

• 定点数：DECIMAL (M,D) 等定点数是非压缩的无符号浮点数，在 MySQL 中以字符串形式存放，比浮点数更精确，适合表示货币、科学数据等精度高的数据。不指定精度时，默认 (10,0)，最高可达 (65,30) 。

类型名	字节	描述
DEC(M,D), DECIMAL(M,D）	M+2	最大取值范围与DOUBLE相同，给定decimal的有效 取值范围由M和D决定

2. 位类型

BIT (M) 用来存放多位二进制数，M 取值范围为 1~64，默认 1。位字段用 SELECT 命令不会直接看到结果，可使用 bin ()（显示为二进制格式）或 hex ()（显示为十六进制格式）函数进行读取。插入 bit 类型字段时，数据首先转换为二进制，如果位数允许则插入成功，否则插入失败 。

类型名	字节	描述及存储需求
BIT(M)	M	M1~64,默认1，位字段类型
BINARY(M)	M	允许长度0～M字节的定长字节字符串，M指的是字节长度
VARBINARY(M)	M	允许长度0～M字节的变长字节字符串，值的长度+1个字节， M指的是字节长度
TINYBLOB(M)	L+1	允许长度0～255字节，值的长度+1个字节
BLOB(M)	L+2	允许长度0～65535字节，值的长度+2个字节binary large  object
MEDIUMBLOB(M)	L+3	允许长度0～167772150字节，值的长度+3个字节
LONGBLOB(M)	L+4	允许长度0～4294967295字节，值的长度+4个字节

• 比较

• 插入失败与成功

3. 图片的存取

在处理图片的存取操作时，需注意以下方面：

• 存储条件：首先要查看图片大小，以便为其选择合适的数据类型。同时，需要查看两个重要的环境变量，即通过执行

show variables like ‘max_allowed_packet’; 

show variables like ‘secure_file_priv’; 

       两个语句来了解相关设置情况。

• 插入数据：可使用

insert into img values(load_file(‘C:\ProgramData\MySQL\MySQL 
Server 5.7\Uploads\boluomi.jpg’)); 

       语句将图片数据插入到相应表中，但需确保环境变量及数据类型等条件均已正确设置。

4. 日期和时间类型

4.1 基本类型及取值范围

类型名	字节	值范围
DATE	4	1000-01-01~9999-12-31
TIME(fsp)	3	-838:59:59~838:59:59
DATETIME(fsp)	8	1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP(fsp)	4	19700101080001~2038年的某一个时刻
YEAR	1	1901~2155

4.2 timestamp 和 datetime 区别

timestamp 存储时转为 UTC（Coordinated Universal Time），检索时转为服务器的时区；而 datetime 存储和检索不进行时区转换 。

4.3 日期和时间类型的零值表示

类型名	零值表示
DATETIME	0000-00-00 00:00:00
DATE	0000-0000-00
TIMESTAMP	00000000000000
TIME	00:00:00
YEAR	0000

4.4插入当前时间

• 创建表

• 插入数据

5. 字符串类型

5.1 基本类型及存储需求

类型名	描述及存储需求
CHAR(M)	定长，M为0～255之间的整数，默认1，M指的是字符长度，存储 长度跟字符集有关
VARCHAR(M)	变长，值的长度+1个字节，M为0～65535之间的整数，M指的是 字符长度
TINYTEXT	允许长度0～255字节，值的长度+2个字节
TEXT	允许长度0～65535字节，值的长度+2个字节
MEDIUMTEXT	允许长度0～167772150字节，值的长度+3个字节
LONGTEXT	允许长度0～4294967295字节，值的长度+4个字节

5.2 CHAR 和 VARCHAR 类型对比

• 存储方式：CHAR 列固定为创建表时声明的长度，VARCHAR 列为可变长字符串。
• 检索方式：CHAR 列删除了尾部的空格，VARCHAR 列保留了尾部的空格。

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
‘’	‘’	4 bytes	‘’	1 byte
'ab'	'ab'	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

5.3 BINARY 和 VARBINARY 类型

类似于 CHAR 和 VARCHAR，不同的是存储的二进制字符串，而非字符型字符串。排序和比较都是按照二进制值进行对比。

5.4CHAR VARCHAR和BINARY VARBINARY

• 后面的M代表的值不同，
• CHAR VARCHAR进行字符比较时只比较字符本身存储的字符，忽略字符后的填充字符。BINARY VARBINARY按二进制值进行比较
• BINARY填充字符是0x00，CHAR填充字符是0x20

5.5 ENUM 类型

ENUM 是枚举类型，如 enum (value1,…,valuen)，1~255 个成员的枚举需要 1 个字节存储，255~65535 个成员需要 2 个字节存储，最多允许 65535 个成员。一次只能插入定义好的 n 个值中的一个或 null，插入值时也可用这个值的序号 (1…n) 代替插入，且 ENUM 忽略大小写，都转成大写 。

• ENUM定义

• ENUM插入数据

5.6 SET 类型

和 ENUM 类似，也是一个字符串对象，可包含 0~64 个成员。SET 类型一次可选取多个成员，与 ENUM 除存储外的主要区别在此。可从允许值集合中选取任意 1 个或多个元素进行组合，超出允许值的值不允许注入到设置的类型列中，包含重复成员的集合，重复成员只取一次 。

成员	字节
1～8	1
9～16	2
17～24	3
25～32	4
33～64	8

通过深入了解 MySQL 支持的这些数据类型，在数据库设计和开发过程中，能够根据实际业务需求和数据特点，选择最合适的数据类型，从而优化数据库性能、节省存储空间并确保数据的完整性和准确性。在后续的数据库操作中，合理运用这些数据类型将为数据管理工作带来极大的便利和效率提升。

五、MySQL中的运算符

1. 算术运算符

运算符	作用
+	加分
-	减法
*	乘法
/，a DIV b	除法，返回商
%，MOD(a,b)	取模，返回余数

2. 比较运算符

• 真返回1
• 假返回0
• 不确定返回NULL

运算符	作用	用法
=	等于
<>或!=	不等于，NULL不能比较
<=>	NULL安全的等于（NULL-safe）	空值比较
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN AND	存在于指定范围	（a BETWEEN b AND c）
IN	存在于指定集合	expression IN (value,...)
NOT IN	不存在于指定集合	expression NOT IN (value,...)
IS NULL	为NULL	空值比较
IS NOT NULL	不为NULL	空值比较
LIKE	通配符匹配	WHERE field LIKE  ‘%|_field的部分内容’

• REGEXP运算符

模式	模式匹配对象
^	字符串开始位置
$	字符串的结尾
.	单个字符
[…]	一对方括号之间的字符
[^…]	未在一对方括号之间的字符
P1|p2|p3	交替匹配模式1、模式2或模式3
*	匹配前面元素的零个或多个实例
+	匹配前面元素的一个或多个实例
{n}	匹配前面元素的n个实例
{m,n}	匹配前面元素的m~n个实例，m<=n

3. 逻辑运算符

• 逻辑运算又称布尔运算，用来确认表达式的真和假

运算符	作用	用法
AND或&&	逻辑与	a AND b或a && b 操作数都为非零且不为NULL时，返回1 操作数有一个为0，返回0 有一个操作数为NULL，返回NULL
OR或||	逻辑或
NOT或!	逻辑非	NOT a或！a 操作数为假，返回1 操作数为真，返回0 NOT NULL返回值为NULL
XOR	逻辑异或	不等返回1

4. 位运算符

• 位运算是将给定的操作数转化为二进制后，对各个操作数每一位都进行指定的逻辑运算

运算符	作用
&	位与（位AND）
|	位或（位OR）
^	位异或（位XOR）
~	位取反
>>	位后移
<<	位左移

• 运算符的优先级

优先级顺序	运算符
1	:=
2	||、OR、XOR
3	&&、AND
4	NOT
5	BETWEEN、CASE、WHEN、THEN和ELSE
6	=、<=>、>=、>、<=、<、<>、!=、IS、LIKE、REGEXP和IN
7	|
8	&
9	<<和>>
10	-和+
11	*、/、DIV、%和MOD
12	^
13	-（一元减号）和～（一元比特反转）
14	！

六、MYSQL中的常用函数

1. 函数简介

函数是是数据库的一个对象，是独立的程序单元，它可以更方便的处理表中数据。select、insert、update、delete语句及条件表达式都可以使用函数

• 函数使用语法

SELECT funName(参数,…);

2. 字符串函数

函数	功能
length(str)
CONCAT(s1,s2,…sn)	连接s1…sn为一个字符串
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str)	将字符串str中所有字符变为小写
UPPER(str)	将字符串str中所有字符变为大写
LEFT(str,x)	返回字符串str最左边的x个字符
RIGHT(str,x)	返回字符串str最右边的x个字符
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串str行尾的空壳
REPEAT(str,x)	返回str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉字符串行尾和行头的空格

3. 数值函数

函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND()	返回0～1内的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

4. 日期和时间函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回时间的最小值
MINUTE(time)	返回time的分钟值
MOUTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数

5. 日期和时间格式

格式符	格式说明
%D	英文后缀表示月中的天数（1，2，…，31）
%w	以数字形式表示周中的天数（0=Sunday，1=Monday，…，6=Saturday）
%j	以3为数字表示年中的天数（001，002，…，366）
%U	周（0，1，52），其中Sunday为周中的第一天
%u	周（0，1，52），其中Monday为周中的第一天
%M	月名（January,February,…,December）
%b	编写的月名（January,February,…,December）
%m	两位数字表示的月份（01，01，…，12）
%c	数字表示的月份（1，2，…，12）
%Y	4位数字表示的年份
%y	两位数字表示的年份
%%	直接值“%”
%S和%s	两位数字形式的秒（00，01，…，59）
%i	两位数字形式的分（00，01，…，59）
%H	两位数字形式的小时，24小时（00，01，…，23）
%h和%I	两位数字形式的小时，12小时（00，01，…，12）
%k	数字形式的小时，24小时（0，1，…，12）
%1	数字形式的小时，12小时（0，1，…，12）
%T	24小时的时间形式（hh:mm:ss）
%r	12小时的时间形式（hh:mm:ssAM或hh:mm:ssPM）
%p	AM和PM
%W	一周中每一天的名称（Sunday，Monday，…，Saturday）
%a	一周中每一天名称的缩写（Sun，Mon，…，Sat）
%d	两位数字表示月中的天数（00，01，…，31）
%e	数字形式表示月中的天数（1，2，…，31）

6. 日期间隔类型

表达式类型	描述	格式
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAT_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

7. 其他常用函数

函数	功能
DATABASE()	返回当前数据库名
VARSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA(num)	返回谁代表的IP地址
PASSWORD(str)	返回字符串str的加密版本
MD5()	返回字符串str的MD5值

8. 流程函数

函数	功能
IF(value,t,f)	如果value是真，返回t；否则返回f
IFNULL(value1,value2)	如果value1为NULL，返回value2，否则返回value1

七、MySQL 表类型、数据类型、字符集、索引的设计和使用、触发器详解

在 MySQL 数据库的应用中，除了前面介绍的 DDL、DML、DCL 以及数据类型等知识外，表类型、字符集、索引和触发器等方面的内容也极为关键，它们对于数据库的性能、数据管理和业务逻辑实现起着重要作用。

1. 表类型（存储引擎）

1.1 概述与设置

存储引擎是存储和管理数据的方式，用于创建、查询、更新和删除数据等操作，不同存储引擎提供不同的存储机制、索引技巧、锁定水平等。MySQL 具有插件式存储引擎的特性，可通过 

SHOW ENGINES;

查看支持的存储引擎。在创建新表时，使用

CREATE TABLE tableName（）
ENGINE=engineName DEFAULT 
CHARSET=charname;

指定存储引擎，也可使用

ALTER TABLE tableName ENGINE=engineName
DEFAULT CHARSET=charName;

对已有表的存储引擎进行修改。

1.2 事务

事务是一组操作，其具有特定的性质，要么都成功执行，要么都不执行。它具有以下特性：

• 原子性，事务是一个不可分割的逻辑单元，一组 sql 语句，要么都执行，要么都不执行。
• 隔离性，事务中的执行过程是不可见的。
• 持久性，事务一旦提交，就不可撤销。
• 一致性，事务在发生之前和发生之后，数据是一致的。

1.3 常用存储引擎对比

特点	MyISAM	InnoDB	MEMORY
存储限制	有	64TB	有
事务安全		支持
锁机制	表锁	行锁	表锁
B树索引	支持	支持	支持
哈希索引			支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入的速度	高	低	高
支持外键		支持

• MyISAM：不支持事务和外键，但速度快，适用于对事务完整性没有要求的场景，采用表锁机制。在磁盘上存储为 3 个文件，分别以 .frm、.myd、.myi 为后缀，数据文件和索引文件可放在不同目录以平均 I/O。其存储方式包括静态（固定长度）表、动态表和压缩表。静态表是默认存储格式，所有字段定长，记录定长，优点是存储块、易缓存、故障易恢复，但占用空间比动态表多，且数据存储时会按照列宽补空格，但应用访问时不会得到这些空格；动态表包含变长字段，记录长度不固定，占用空间少，但频繁更新和删除会产生碎片；压缩表由myisampack 工具创建，占据空间小，每个记录单独压缩，访问开支小。
• InnoDB：提供具有提交、回滚和崩溃恢复能力的事务安全，采用行锁机制。比 MyISAM 写处理效率稍差，占用更多磁盘空间以保留数据和索引。其自动增长列可手动插入，但插入值为空或 0 时实际插入的将是自动增长后的值，

       且自动增长列必须是索引，若为组合索引则必须是第一列，但对于MyISAM表，自动增长列可         以是组合索引的其他列。

       强制设置自动增长列的初始值

ALTER TABLE *** AUTO_INCREMENT=n;

1.4 如何选择存储引擎

1.4.1 MyISAM

• 插件式存储引擎
• 以读和插入为主
• 只有很少更新和删除操作，并对事务的完整性、并发性要求不高
• 是web、数据仓库和其他应用环境常用存储引擎

1.4.2 InnoDB

• 用于事务处理应用程序，支持外键
• 对事务的完整性有比较高的要求，在并发条件 下要求数据一致性，数据操作除了插入和查询 以外，还包括很多的更新、删除操作
• 除了有效地降低由于删除和更新导致的锁定， 还可以确保事务的完整提交（commit）和回滚 （rollback），对于类似计费系统或者财务系统 等对数据准确性要求比较高的系统

1.4.3 MEMORY

• 将数据存储在RAM中
• 需要快速定位记录和其他类似数据的环境下，可提供极快的访问
• 缺点是对表的大小有限制，太大的表无法缓存在内存中
• 常用于更新不太频繁的小表，用以快速得到访问结果

2. 数据类型的选择

2.1 不同存储引擎CHAR和VARCHAR使用原则

CHAR为固定长度的字符类型，VARCHAR为可变长的字符类型

• MyISAM：建议使用固定列长的数据列代替可变长的
• MEMORY：都是用定长，无论使用谁，都作为char处理
• InnoDB：使用varchar

2.2 TEXT与BLOB

• 少量文本使用char和varchar
• 较大文本使用TEXT和BLOB
• BLOB能存储二进制，如照片
• TEXT只能保存字符数据

2.3 浮点数与定点数

• 浮点数存在误差
• 对货币等精确度要求高的数据应用定点数
• 在编程中如用到浮点数，应避免浮点数比较
• 要注意浮点数中一些特殊值的处理

2.4 日期类型的选择

• 选择满足需求的最小存储日期类型
• 如果记录年月时分秒，并且记录年份比较久远，用DATETIME不要用TIMESTAMP，因为TIMESTAMP表示的日期范围短
• 如果保存的日期需要让不同时区的用户使用，那么使用TIMESTAMP，能和实际时区相对应

3. 字符集

3.1 常用字符集

字符集	是否定长	编码方式	其他说明
ACSII	是	单字节7位编码	最早的奠基性字符集
ISO-8859-1/latin1	是	单字节8位编码	西欧字符集，经常被一些程序员用来转码
GB2312-80	是	双字节编码	早期标准，不推荐再使用
GBK	是	双字节编码	虽然不是国标，但支持的系统不少
GB 18030	否	2字节或4字节编码	开始有一些支持，但数据库支持的还少见
UTF-32	是	4字节编码	UCS-4原始编码，目前很少采用
UCS-2	是	2字节编码	Windouws2000内部用UCS-2
UTF-16	否	2字节或4字节编码	Java和Windows XP/NT等内部使 用UTF-16
UTF-8	否	1-4字节编码	互联网和UNIX/Linux存放你支持的Unicode字符集；MySQLServer也使用UTF-8

3.2 MySQL支持的字符集

• 显示MySQL支持的字符集

SHOW CHARACTER SET;

• 显示校对规则

SHOW COLLATION;

SHOW COLLATION LIKE ‘gbk%’;

• MySQL支持30多种字符集的70多种校对规则，Ci大小写不敏感，cs大小写敏感，bin二元

3.3 怎样选择合适的字符集

• 满足应用支持语言的需求,多文字、多语言应选择Unicode字符集，如UTF-8
• 应用中涉及已有数据的导入，考虑数据库字符集对已有数据的兼容性
• 如果数据库只需支持一般中文，数据量很大，性能 要求也很高，应选择双字节定长编码的中文字符集， 如GBK
• 如果处理的主要是英文字符，那么选UTF-8
• 如果数据库需要做大量的字符运算，如比较、排序 等，选定长字符更好
• 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集

3.4 MySQL字符集的设置(服务器级、数据库级、表级、列级)

3.4.1 服务器级

• SHOW VARIABLES LIKE ‘character_set_server’;

• SHOW VARIABLES LIKE ‘collation_server’;

• 在my.ini中设置CHARACTER-SETSERVER=charName;

3.4.2 数据库级

• SHOW VARIABLES LIKE ‘character_set_database’;

• SHOW VARIABLES LIKE ‘collation_database’;

• CREATE DATABASE `databaseName` DEFAULT 
  CHARACTER SET characterName COLLATE collateName ;

3.4.3 表级

• SHOW CREATE TABLE tableName;

• CREATE TABLE tableName(…) DEFAULT 
  CHARSET=charName
  COLLATE=collateName;

3.4.4 列级

• CREATE TABLE tableName(…CHARACTER 
  SET ‘characterName’ COLLATION ‘collationName’);

3.5 连接字符集和校对规则

• 三个参数

character_set_client=‘charName’;

character_set_connection=‘charName’;

character_set_results=‘charName’;

• SET NAMES ‘charName’;
• 在my.ini中设置DEFAULT-CHARACTER-SET=charName;

3.6 字符集的修改

• 导出表结构
• 修改导出的脚本，将字符集修改
• 导出记录
• 修改导出的记录脚本中的字符集
• 使用新字符集创建新的数据库
• 创建表，执行导出的表结构脚本
• 导入数据

4. 索引的设计和使用

4.1 概述

索引是一列或多列排序后单独存储的数据结构，存储类型有 BTREE 和 HASH、RTREE 等，默认是 BTREE，且与存储引擎有关。常用引擎支持每张表至少 16 个索引，总索引长度至少 256 字节，MySQL 支持前缀索引，前缀索引长度和引擎有关，MyISAM 可达 1000 字节，InnoDB 最长 767 字节。

4.2 索引优缺点与分类

• 优点：能加快查询速度、强制数据唯一、加速排序、优化链接、支持分组、提高查询覆盖率。
• 缺点：创建和维护索引需要占用时间和空间。
• 分类：包括普通索引和唯一索引、单列索引和组合索引，组合索引遵循最左前缀集合；还有全文索引，可对 char、varchar、text 建立；以及空间索引，用于空间数据类型如 geometry、point、linestring、polygon 等，且必须声明为 NOT NULL。

4.3 索引操作

4.3.1 创建索引

CREATE [type] INDEX indexName
[storageType]
ON tableName(columnName[(length)] [ ASC|DESC],…);

• type: INDEX|UNIQUE|FULLTEXT|SPATIAL|PRIMARY
• storageType: USING (BTREE|RTREE)
• length: 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

4.3.2 创建表时创建索引

CREATE TABLE tableName(
columnName columnType constraints,
INDEX indexName (colName(length))
);

4.3.3 修改表创建索引

ALTER TABLE tableName
ADD INDEX indexName (columnName(length));

4.3.4 显示索引信息

SHOW INDEX FROM tableName;

4.3.4查看索引是否正在使用

EXPLAIN SELECT * FROM tableName WHERE condition;

4.3.4 删除索引

DROP INDEX indexName ON tableName;

ALTER TABLE tableName DROP INDEX indexName;

4.4 设计索引原则

• 不要过度索引
• 使用唯一索引
• 使用短索引
• 利用最左前缀
• 将频繁进行排序和分组的列建立索引
• 出现在WHERE子句或连接子句中的列
• InnoDB表尽量自己指定主键，主键选择较短数据类型
• 不同值少的列不要建立索引
• 经常更新的表尽量少建立索引，索引中的列也尽可能少

4.5 引擎对索引的支持

索引	MyISAM引擎	InnoDB引擎	Memory引擎
B-Tree	支持	支持	支持
HASH	不支持	不支持	支持
R-Tree	支持	不支持	不支持
Full-text	支持	支持	不支持

• Memory只有在‘=’条件下才会使用索引

4.6 BETREE索引和HASH索引

HASH

• 只用于使用=或<=>操作符的等式比较
• Hash索引不适用范围查询
• Memory/Heap引擎只有在“=”的条件下才会使用索引
• 只能使用整个关键字搜索一行

BETREE

• 当使用>,<,>=,<=,BETWEEN,!=,<>,LIKE ‘pattern’操作符时

5. 触发器

5.1 概述

触发器建立在实体表上，运行在整个数据库。它是一种特殊的存储过程，在数据库中发生特定事件（如 INSERT、UPDATE、DELETE 等）时自动执行预定义的操作。

5.2 创建触发器

DELIMITER delimiterName
CREATE TRIGGER
[DEFINER=user|CURRENT_USER]
triggerName triggerTime triggerEvent
ON tableName FOR EACH ROW 
[triggerOrder]
triggerBody
delimiterName
DELIMITER;

• triggerTime:BEFORE, AFTER
• triggerEvent:INSERT, UPDATE, DELETE
• triggerOrder:FOLLOWS|PRECEDES
• triggerBody：BEGIN statementList END
• DELIMITER delimiterName是一条命令，它之后的语句只有遇到了delimiterName才认为语句结束，delimiterName可设为1个或多个长度的符号， 默认为‘ ；’

5.3 事件详解

共 6 种类型，除 INSERT、UPDATA、DELETE 外，还包括 LOAD DATA 和 REPLACE。

• INSERT 型触发器可由 INSERT、LOAD DATA、REPLACE 触发；
• UPDATA 型触发器由 UPDATA 触发；
• DELETE 型触发器由 DELETE 、REPLACE 触发。

5.4 变量

可使用

DECLARE variableName,... type [DEFAULT value];

声明变量；

使用

 SET variableName=expression,...;

设置变量值；

也可通过

SELECT * from tableName WHERE condition INTO variableName;

语句赋值；

5.5 触发器执行顺序

MySQL 的触发器按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行。在事务性表上，若 SQL 语句或触发器执行失败，MySQL 会回滚事务。BEFORE 触发器执行失败，SQL 无法正确执行；SQL 执行失败，AFTER 触发器不会触发；AFTER 触发器执行失败，SQL 会回滚。

5.6 NEW和OLD

NEW 和 OLD 用于表示触发哪一行数据。在 INSERT 操作中，NEW 表示将要（BEFORE）或已经（AFTER）插入的数据；在 UPDATA 操作中，OLD 表示将要或已经被修改的原数据，NEW 表示将要或已经修改的新数据；在 DELETE 操作中，OLD 表示将要或已经被删除的原数据。其使用方法为，可通过 NEW.columnName 来访问 NEW 中的数据，而 OLD 是只读的，NEW 可使用 SET 赋值。

5.7 查看和删除触发器

• 查看触发器

SHOW TRIGGERS;

• 删除触发器

DROP TRIGGER [databaseName.]triggerName;

在 MySQL 数据库的实际应用中，深入理解和合理运用表类型、字符集、索引和触发器等知识，能够显著提升数据库的性能、保证数据的完整性和一致性，并实现复杂的业务逻辑。开发人员和数据库管理员应根据具体的业务需求和数据特点，精心设计和优化这些方面，以构建高效、稳定和可靠的数据库系统。

八、MySQL 视图、编程基础、存储过程和函数详解

在 MySQL 数据库的应用中，视图、SQL 编程基础、存储过程和函数是几个重要的方面，它们对于数据管理、业务逻辑处理和应用开发效率有着重要影响。

1. 视图

1.1 概述

视图是由查询结果形成的虚拟表，当某些查询结果频繁出现时可创建视图。它相对于表具有简单、安全、数据独立等优势。

1.2 创建视图

CREATE
[OR REPLACE] 
[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW viewName[(columnName,...)] AS selectStatement
[WITH[CASCADED|LOCAL] CHECK OPTION];

其中 OR REPLACE 表示若视图已存在则替换，ALGORITHM 用于指定视图的算法，selectStatement 是查询语句，WITH[CASCADED|LOCAL] CHECK OPTION 用于控制视图更新时的检查规则 。

1.3 修改视图

ALTER 
[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] 
VIEW viewName[(columnName)] 
AS selectStatement
[WITH[CASCADED|LOCAL]CHECK OPTION]];

1.4 查看视图

SHOW TABLE STATUS [FROM dbName] [LIKE ‘viewName’];

DESC viewName;

1.5 查看视图定义

SHOW CREATE VIEW viewName;

1.6 更新视图

UPDATE viewName SET 
columnName=value,... WHERE statement;

视图的更新与视图中查询的定义相关。包含聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL 等关键字的 SQL 语句创建的视图不可更新，常量视图、SELECT 中包含子查询、JION、 FROM 一个不能更新的视图、WHERE 子句的子查询引用了 FROM 子句中的表等情况也不可更新 。

1.7 删除视图

DROP VIEW viewName,… 
[RESTRICT|CASCADE];

2. SQL编程基础

2.1 系统变量

系统变量存储系统参数，分为 global 和 session 两种类型。global 变量影响 MySQL 实例的整体操作，如 auto_increment_increment；session 变量影响当前到 MySQL 实例的连接。

2.2 查看与设置系统变量

• 查看系统变量：查看 global 配置变量可使用 show variables;、show global variables;、show variables like ‘log%’; 等语句；监控变量使用 show status;、show status like ‘sort%’;；查看 session 变量使用 show session variables;。
• 常见的系统变量查看如查看版本信息用 show variables like ‘version%’;，查看最大连接数用 show variables like ‘max_connections’;，查看当前连接数用 show status like ‘Threads_connected’;，查看编码配置信息用 show variables like ‘%set%’; 。
• 设置和修改系统变量：使用 set [global/session] var_name = value 或 set@@[global/session.]var_name=value 语句设置和修改系统变量。

2.3 用户变量

用户变量由用户自己定义，作用域为当前连接范围，以 @ 开始，如 @var_name。定义方式有

• set @var_name = expr [, @var_name = expr] …
• set @var_name := expr [, @var_name = expr] …
• select @var_name := expr [, @var_name=expr]… 等。

2.4 外部变量与局部变量

• 外部变量：在函数内部定义并执行后，外部可访问。

• 局部变量：必须在 BEGIN…END 中第一行定义，格式为 DECALARE varname[,varname]…datatype [DEFAULT value]，赋值方式有 SET varname = value;、SELECT expr INTO varname FROM…;、SELECT expr FROM…INTO varname; 等，作用范围为BEGIN … END。

2.5 流程控制结构

包括 IF、CASE、LOOP、LEAVE、WHILE、repeat 等语句。

• IF

IF condition THEN statements ;
[ELSEIF condition THEN statements;]
...
[ELSE statements;] 
END IF;

• CASE

CASE [value]
WHEN value THEN statement;
...
[ELSE statement;]
END CASE;

• LOOP

[label:] LOOP
statement
LEAVE label
END LOOP[label];

• while

[label]WHILE condition DO 
statement;
END WHILE[labe];

• repeat

[label:] REPEAT 
statement
UNTIL endCondition
END REPEAT [label:];

3. 存储过程和函数

3.1 概述

存储过程和函数是事先经过编译并存储在数据库中的一组 SQL 语句集。通过存储过程名调用和传参，在多语言应用程序中可简化开发工作，能使用流程控制语句、自定义变量等完成复杂业务逻辑，提高数据安全性，减少客户端应用程序负担和传输，提高数据处理效率。

3.2 存储过程和函数区别

• 返回值：函数有返回值，存储过程没有。
• 参数类型：存储过程参数可使用 IN、OUT 、INOUT 类型，函数只能是 IN。
• 返回内容：存储过程可返回参数如记录集，函数只能返回值。
• 语句限制：MySQL 的存储过程和函数允许包含 DDL，允许在存储过程中执行提交（commit）或者回滚（rollback），但不允许执行 LOAD DATA IN FILE 语句。

3.3 存储过程和函数操作

3.3.1 创建存储过程

 CREATE PROCEDURE SPName([parameter,...]) [characteristic…] routineBody;

其中 parameter 格式为 [IN|OUT|INOUT] parameterName dataType，characteristic 可设置如 COMMENT ‘string’、LANGUAGE SQL、[NOT] DETERMINISTIC、{CONTAIN SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}、SQL SECURITY {DEFINER|INVOKER} 等属性。

3.3.2 创建函数

DELIMITER $$ 
[use databaseName$$] 
CREATE 
[DEFINER = {user|CURRENT_USER}] 
FUNCTION functionName([parameter,…]) 
RETURNS dataType 
[characteristic…] 
routineBody 
DELIMITER ; 

函数要声明 DETERMINISTIC, NO SQL, or READS SQL DATA 等特性，routineBody 一般在 BEGINE…END $$ 中定义。

3.3.3 查看存储过程和函数

• 查看定义

SHOW CREATE {PROCEDURE|FUNCTION} 
spName|functionName;

• 查看状态

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’];

• 也可从 information_schema 下的 routine 表中查看

select * from information_schema.routines where routine_name=‘SPName’;

3.3.4 调用存储过程和函数

• 调用存储过程

CALL SPName(parameter,…);

• 调用函数

SELECT functionName(parameter,…);

3.3.5 修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} 
SPName[characteristic…];

3.3.6 删除存储过程和函数

DROP{PROCEDURE|FUNCTION} SPName;

MySQL 是广泛应用的关系型数据库管理系统，涵盖了 DDL（用于创建、删除、修改数据库对象）、DML（负责数据的插入、删除、更新和查询等操作）、DCL（管理系统对象权限）等重要语言，还包括多种数据类型、运算符、函数，以及表类型（存储引擎）、字符集、索引、触发器、视图、存储过程和函数等方面的知识，熟练掌握这些内容有助于高效地进行数据库设计、开发和管理，确保数据库的性能、完整性、安全性以及数据处理的准确性和灵活性。

标签：学习指南,语句,存储,数据库,tableName,基础知识,columnName,MySQL
From： https://blog.csdn.net/2303_81897063/article/details/145065565