MySQL

约 47662 字大约 159 分钟

安装MySQL

MySQL官网 https://www.mysql.com。

点击第二行的DOWNLOADS。

向下滑，点击MySQL Community (GPL) Downloads »。

点击第一列第四个MySQL Community Server。

Operating system选windows 。

点击下面other downlaods部分的 Windows (x86, 64-bit), ZIP Archive右边的download。

点击最下面的No thanks, just start my download.。

解压后放到任一存储目录。

然后在系统变量中Path的路径下新建一个路径路径写数据库根目录下的bin文件夹。

然后以管理员身份运行cmd。

先输入mysqld --initialize 目的是初始化MySQL。

然后输入mysqld --install安装MySQL，即可安装成功。

数据库基础概念

数据库是按照数据结构组织、存储和管理数据的仓库，是存储在一起的相关数据的集合。其优点主要体现在以下一些方面。

减少数据的冗余度，节省数据的存储空间。
具有较高的数据独立性和易扩充性。
实现数据资源的充分共享。

数据库的基础概念

数据库系统

数据库系统是采用数据库技术的计算机系统，是由数据库（数据）、数据库管理系统（软件）、数据库管理员（人员）、硬件平台（硬件）和软件平台（软件）5个部分组成的运行实体。其中，数据库管理员是对数据库进行规划、设计、维护和监视等的专业管理人员，在数据库系统中起着非常重要的作用。

数据库管理系统

数据库管理系统是数据库系统的一个重要组成部分，是位于用户和操作之间的一层数据管理软件，负责数据库的数据组织、数据操纵、数据维护和数据服务等，主要有以下功能。

数据存储的物理构建：为数据模式的物理存取与构建提供有效的存取方法及手段。
数据操纵功能：为用户使用数据库的数据提供方便，如查询、插入、修改、删除等以及简单的算术运算和统计。
数据定义功能：用户可以通过数据库管理系统提供的数据定义语言方便地对数据库中对象进行定义。
数据库的运行管理：数据库的管理系统统一管理数据库的运行和维护，以保障数据的安全性、完整性、并发性和故障的系统恢复性。
数据库的建立和维护功能：数据库管理系统能完成初始数据的输入和转换、数据库的转储和恢复、数据库的性能监视和分析等任务。

关系数据库

关系数据库是支持关系模型的数据库。关系模型由关系数据结构、关系操作集合和完整性约束3个部分组成。

关系数据结构：在关系模型中数据结构单一，现实世界的实体以及实体间的联系均用关系来表示，实际上关系模型中数据结构就是一个二维表。
关系操作集合：关系操作分为关系代数、关系演算、具有关系代数和关系演算双重特点的语言（SQL）。
完整性约束：包括实体完整性、参照完整性和用户定义完整性。

数据库常用对象

在MySQL数据库中，表、字段、索引、视图和存储过程等具体存储数据或对数据进行操作的实体都叫做数据库对象，下面介绍几种常用的数据库对象。

表表是包含数据库中所有数据的数据库对象，由行和列组成，用于组织和存储数据。
字段表中每列成为一个字段，字段具有自己的属性，如字段类型、大小。其中字段类型是最重要的属性，它决定了该字段能存储什么类型的数据。
索引索引是一个单独的、物理的数据库结构。它是依赖于表建立的，有了它，数据库无须对整个表进行扫描就能找到想要的数据。
视图视图是从一张表或多张表中导出的表（也叫虚拟表），是用户查看数据表中数据的一种方式。表中包括几个被定义的数据列与数据行，其结构和数据建立在对表的查询基础之上。
存储过程 存储过程是一组为了完成特定功能的SQL语句集合（包含查询、插入、删除、更新等操作），经编译后以名称的形式存储在MySQL服务器端的数据库中，由用户通过指定存储过程的名字来执行。当这个存储过程被调用执行时，这些操作也会同时执行。

本节介绍MySQL中的数据类型。

数字类型

MySQL支持所有的ANSI/ISO 92 数字类型，包括准确数字的数字类型以及近似数字的数字类型。

数据类型主要可以分为整型和浮点型两种数据类型，如下：

整型

数据类型	取值范围	说明	单位
TINYINT	符号值：-128~127，无符号值：0~255	最小的整数	1字节
BIT	符号值：-128~127，无符号值：0~255	最小的整数	1字节
BOOL	符号值：-128~127，无符号值：0~255	最小的整数	1字节
SMALLINT	符号值：-32768~32767，无符号值：0~65535	小型整数	2字节
MEDIUMINT	符号值：-8388608~8388607，无符号值：0~16777215	中型整数	3字节
INT	符号值：-2147683648~2147683647，无符号值：0~4294967295	标准整数	4字节
BIGINT	符号值：-9223372036854775808~9223372036854775807，无符号值：0~18446744073709551615	大型整数	8字节

浮点型

数据类型	取值范围	说明	单位
FLOAT	+(-)3.402823466E+38	单精度浮点数	8或4字节
DOUBLE	+(-)1.7976931348623157E+308、 +(-)2.2250738585072014E-308	双精度浮点数	8字节
DECIMAL	可变	一般整数	自定义长度

其中DECIMAL是精度确定的小数，比如想要指定总位数为8、小数位数为2的数就可以使用DECICAL(8,2)，其中8就表示总共有8位数，2表示其中有2位小数。

FLOAT和DOUBLE存在误差问题，尽量避免浮点数比较，若要使用货币等高精度的数字则应该使用DECIMAL数据类型。

字符串类型

字符串类型可以分为3类：普通的文本类型（CHAR和VARCHAR）、可变类型（BLOB和TEXT）、和特殊类型（SET和ENUM）。

普通文本类型：即CHAR和VARCHAR类型，CHAR列的长度被固定为创建表声明的长度，取值在1-255；VARCHAR列的值是变长的字符串，取值和CHAR一样。

类型	取值范围	说明
[national] CHAR(M) [binary\|ASCII\|unicode]	0-255个字符	固定长度为M的字符串，其中M的取值为0-255，national关键字指定了应该使用的默认字符集。binary关键字指定了数据是否区分大小写（默认是区分大小写的）。ASCII关键字指定了在该列中使用latin1字符集。unicode关键字指定了是否使用UCS字符集。
CHAR	0-255个字符	与CHAR(M)类似
[national] VARCHAR(M) [binary]	0-255个字符	长度可变，其他和CHAR(M)类似

存储字符串长度相同的全部使用CHAR类型；字符长度不相同的使用VARCHAR类型，不预先分配存储空间，长度不要超过255。

可变类型：大小可以改变，TEXT类型适合存储长文本，BLOB适合存储二进制数据，支持任何数据，如声音、文本和图像等。

类型	最大长度（字节数）	说明
TINYBLOB	2⁸-1（255）	小BLOB字段
TINYTEXT	2⁸-1（255）	小TEXT字段
BLOB	2¹⁶-1（255）	常规BLOB字段
TEXT	2¹⁶-1（255）	常规TEXT字段
MEDIUMBLOB	2²⁴-1（255）	中型BLOB字段
MEDIUMTEXT	2²⁴-1（255）	中型TEXT字段
LONGBLOB	2³²-1（255）	长BLOB字段
LONGTEXT	2³²-1（255）	长TEXT字段

特殊类型：ENUM和SET类型。

类型	最大值	说明
ENUM(“value1”,”value2”,...)	65535	该类型的列只可以容纳值之一或者NULL
SET(“value1”,”value2”,...)	64	该类型的列可以容纳一组值或为NULL

误区警示：BLOB、TEXT、ENUM和SET字段类型在MySQL中的检索速度不高，很难使用索引进行优化。如果必须使用这些类型，一般采用特殊的结构设计，或者与程序相结合使用其他的字段类型替代。例如，SET类型可以使用整型（0、1、2、3、…）注释功能和程序的检查功能集合替代。

若速度优先、要选择固定的列，则选择CHAR类型；若节省空间、使用动态的列，则使用VARCHAR类型；要将列的内容限制在一种选择则选择ENUM类型；允许一个列有多于一个的条目则选择SET，若字符串不区分大小写则选择TEXT类型，若区分大小写则选择BLOB类型。

类型	取值范围	说明
DATE	1000-01-01~9999-12-30	日期，格式为YYYY-MM-DD
TIME	-898:58:59~835:59:59	时间，格式为HH:MM:SS
DATETIME	1000-01-01 00:00:00~9999-12-31 23:59:59	日期和时间，格式为YYYY-MM-DD HH:MM:SS
TIMESTAMP	1970-01-01 00:00:00~2037年的某个时间	时间标签，在处理报告时使用，显示格式取决于M的值
YEAR	1901~2155	年份可指定两位数字和四位数字的格式

查询MySQL中支持的中文引擎

查询支持的全部中文引擎

MySQL中的数据使用不同的技术存储在文件中（或者内存中），每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供应用广泛的、不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。

在MySQL中，可以使用SHOW ENGINE语句查询MySQL中支持的存储引擎，查询语句如下：

SHOW ENGINES\g

注：使用\g与使用;效果是一样的，使用\G可以使结果更美观。

SHOW ENGINES\G

同时，使用如下的命令可以查看默认的存储引擎：

SHOW VARIABLES LIKE 'storage_engine%';

常见的存储引擎

InnoDB存储引擎

优点特别适合处理多重处理的更新请求；是唯一支持事务的标准MySQL存储引擎，这是管理敏感数据（如金融信息和用户注册信息）所必需的；与其他存储引擎不同，InnoDB表能自动从灾难中恢复，虽然MyISAM表也能在灾难后修复，但是过程要长得多。

MyISAM存储引擎

文件类型
.frm 存储表的结构
.MYD 存储数据，是MYData的缩写
.MYI 存储索引，是MYIndex的缩写
存储格式
- MyISAM静态
- MyISAM动态
- MyISAM压缩

优点占用空间小，处理速度快

缺点不支持事务的完整性和并发性

MEMORY存储引擎

文件存储形式
每个基于MEMORY存储引擎的表是一个文件。
索引类型
默认使用哈希（HASH）索引，其速度要比B树（BTREE）索引快。
存储周期
存储在内存上，一旦重启机器或者关机，表中的所有数据都会消失。
优缺点
缺点表的大小有限制；不支持VARCHAR、BLOB、TEXT数据类型。
什么时候选择此存储引擎
暂时目标数据只是临时需要，在其生命周期中必须立即可用
相对无关 存储在MEMORY表中的数据如果突然丢失，不会对应用服务产生负面影响，不会对数据完整性有长期影响。

选择存储引擎

InnoDB 用于事务处理应用程序，具有众多特性，包括支持ACID事务、外键、崩溃修复能力和并发控制。如果对事务的完整性要求比较高，要求实现并发控制，那么选择此引擎有很大优势。如果需要进行频繁更新，删除数据库的操作，也可以选择此引擎，因为此引擎可以实现事务的实现和回滚。

MyISAM 管理非事务表，它提供高速存储和检索，以及全文搜索能力。此引擎插入数据快，空间和内存使用比较低。如果表主要是插入新记录和读出记录，那么选择此引擎可以实现处理的高效率，如果应用的完整性、并发性很低，也可以选择此引擎。

MEMORY引擎 此引擎提供“内存中”的表，其所有数据都在内存中，数据的处理速度快，但安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求，不能建太大的表。所以，这类数据库只使用较小的数据库表。

系统数据库

系统数据库是指在安装完MySQL服务器后系统自动创建的一些服务器后，会附带一些数据库，例如，在默认安装MySQL数据库之后，会默认创建如下数据库，它们就被称为系统数据库，这些数据库存储了必须的系统信息，用户不能直接修改。

information_schema数据库：主要存储MySQL服务器所有数据库的信息，如数据库的名，数据库的表，访问权限，数据库表的存储类型，数据库索引信息等。

mysql数据库：是MySQL的核心数据库，主要负责存储数据库的用户，权限设置，关键字等MySQL自己需要使用的控制和管理信息。

performance_schema数据库：主要收集数据库主要性能参数，可用于监控数据库服务器在一个较低级别的运行过程中的资源消耗，资源等待等状况。

sys数据库：它所有的数据源都来自performance_schema，目标是把performance_schema的复杂度降低，让DBA能更好地阅读这个库里的内容，让DBA更快地了解DB的运行情况。

查看数据库

成功创建数据库之后，使用show命令查看MySQL服务器中的所有数据库信息，语法如下：

SHOW {DATABASES|SCHEMAS} [LIKE '模式' WHERE '条件'];

其中，LIKE是可选项，用于指定匹配模式。

WHERE也为可选项，用于指定数据库名称查询范围的条件。

查看上方创建的数据库

show databases;

筛选以db开头的数据库

show databases like 'db_%';

选择数据库

在MySQL中，创建数据库后，该数据库并不会自动成为当前数据库，需要使用MySQL提供的USE语句，语法如下：

USE 数据库名;

选择一个数据库

use db_admin;

修改数据库

在MySQL中，创建一个数据库后，还可以对齐修改，如修改编码格式，修改校对规则等，语法·规则如下：

ALTER {DATABASE|SCHEMA} [数据库名]
[DEFAULT] CHARACTER [=] 字符集
[DEFAULT] COLLATE [=] 校对规则民称;

更改db_admin中的编码和校对规则

alter database db_admin default character set gbk default collate gbk_chinese_ci;

删除数据库

在MySQL中，可以使用DROP DATABASES或者DROP SCHEMA删除已经存在的数据库，在删除数据库之后，该数据库之中的所有数据表以及所有数据都会删除且无法撤销，因此在删除之前要多加思考。删除数据库的语法如下：

DROP {DATABASES|SCHEMA} [IF EXISTS] 数据库名;

删除数据库db_admin

drop database db_admin;

拿Windows系统的文件系统进行类比的话，如果说数据库是文件系统中的文件夹，那么数据表就是文件夹中的文件。在MySQL中，数据库里可以存放多张数据表。在对数据表进行操作之前，必须使用USE语句选择数据库，才可以在指定的数据库中对数据表进行操作，如创建数据表，修改表结构，以及重命名、复制或者删除数据表等，否则将无法对数据表进行操作。

创建数据表

创建数据表使用CREATE TABLE语句，语法如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名 [{create_difinition,...}][table_options][select_statement];

其中，TEMPORARY代表若使用此关键字，那么将创建一个临时表。

create_definition是表的列属性部分，MySQL要求在创建表时至少包含一列。

table_options是表的一些特性参数，其中大部分选项涉及的是表数据如何存储以及存储在何处，如ENGINE选项用于定义表的存储引擎。多数情况下，用户不必指定表选项。

select_statement是SELECT语句描述部分，用于快速创建表。

下面介绍列属性create_statement部分，每一列定义的具体格式如下：

col_name type [NOT NULL|NULL] [DEFUALT default_value] [AUTO_INCREAMENT] [PRIMARY_KEY] [reference_definition]

其中，col_name代表字段名。

type代表字段类型。

NOT NULL|NULL代表该列是否允许是空值，系统一般允许是空值，所以若不想允许为空值必须使用NOT NULL。

DEFAULT default_value代表默认值。

AUTO_INCREMENT代表是否自动编号，每个表只能有一个该列，且必须被索引。

PRIMARY_KEY表示是否为主键，一个表只能有一个主键。若表中没有主键，而某些应用程序需要主键，MySQL将返回第一个没有任何NULL列的UNIQUE键作为主键。

reference_definition代表为字段添加注释。

创建数据表的简洁形式

CREATE TABLE table_name (列名1 属性, 列名2 属性);

创建第一个数据表

create table tb_first (id int primary key not null auto_increment,name varchar(4));

第一行表示使用db_admin数据库，第二行表示创建数据表，名字叫做tb_first（tb是table）的缩写，其中id是第一个字段名，int是该字段的数据类型，即整型，primary key表示该字段为主键，not null表示该字段不能为NULL值，auto_increment代表该字段可以自动增长，后面的name为第二个字段名，类型为变长字符串，最长为4个字符。

查看表结构

对于一个创建成功的数据表，可以使用SHOW COLUMNS或者DESCRIBES语句查看指定数据表的表结构。

SHOW COLUMNS

在MySQL中，使用SHOW COLUMNS语句可以查看表结构，基本语法如下：

SHOW [FULL]COLUMNS FROM 数据表名 [FROM 数据库名];

在上面我们创建了数据表tb_first数据表，里面有有两个字段：id和name，下面我们尝试读取这几个字段：

show columns from tb_first;

DESCRIBES

在MySQL中，还可以使用DESCRIBE语句查看数据表结构，基本语法如下：

DESCRIBE 数据表名;

其中，DESCRIBE可以简写成DESC，在查看表结构时，也可以只列出某一列的信息，基本语法如下：

DESCRIBE 数据表名 列名;

使用DESC查看数据表结构

DESC tb_first;

修改表结构

修改表结构指增加或者删除字段、修改字段名或字段类型以及修改表名等，可以使用ALTER TABLE语句实现，语法如下：

ALTER [IGNORE] TABLE 数据表名 alter_spec[,alter_spec]...| table_options;

其中，[IGNORE]是可选项，表示如果出现重复的行，则只执行一行，其他的行将被删除。

alter_spec子句表示定义要修改的内容，基本语法如下：

ADD [COLUMN] create_difinition [FIRST|AFTER column_name]  -- 添加新字段
| ADD INDEX [index_name](index_col_name,...)  -- 添加索引名称
| ADD PRIMARY KEY (index_col_name)  -- 添加主键名称
| ADD UNIQUE [index_name](index_col_name)  -- 添加唯一索引
| ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}  -- 修改字段默认值
| CHANGE [COLUMN] old_col_name create_definition  -- 修改字段名/类型
| MODIFY [COLUMN] create_definition  -- 修改子句定义字段
| DROP [COLUMN] col_name  -- 删除某一字段
| DROP PRIMARY KEY  -- 删除主键
| DROP INDEX index_name  -- 删除某一索引
| RENAME [AS] new_tbl_name  -- 更改表名
| COMMENT 'comment'  -- 给表添加注释

其中，create_definition用于定义列的数据类型和属性，与CREATE TABLE语句中的语法相同。

[FIRST|AFTER column_name] 用于指定位于哪个字段的前面还是后面，使用FIRST关键字时表示位于指定字段的前面；使用AFTER关键字时表示位于哪个字段的后面；column_name表示字段名。

[index_name] 是可选项，用于指定索引名。

{index_col_name}用于指定索引列名。

{SET DEFAULT literal|DROP DEFAULT}子句表示为字段设置或者删除默认值，其中literal参数要设置的默认值。

old_col_name表示指定要修改的字段名。

new_tbl_name表示指定新的表名。

table_options表示指定表的一些特性参数，其中大多数选项涉及的是表数据如何存储以及存储在何处，如ENGINE选项表示定义表的存储引擎。多数情况下，用户不必指定表选项。

添加新字段和修改字段定义

在MySQL的ALTER TABLE中，可使用ADD [COLUMN] create_definition [FIRST|AFTERT column_name]子句来添加新字段；使用MODIFY [COLUMN] create_definition子句修改已定义的字段的定义，下面通过实例演示如何为一个已有表添加新字段，并修改已有字段的定义。

添加新字段并修改已有字段的定义

首先查看已有的字段以及它们的描述。

DESC tb_first;

下面我们新增加一个字段叫做create_time，意为创建时间，并且将name的类型改为varchar(10)。

alter table tb_first add column (create_time datetime default now() not null),modify column name varchar(10);

最后再查看表的描述。

DESC tb_first;

可以看到name的字段类型已经改为长度为10的变长字符串，且新增添了一个字段create_time。

修改表的名称

使用ALTER语句将tb_first名字修改为table_first。

alter table tb_first rename as table_first;

重命名表

在MySQL中，可以使用RENAME TABLE语句将表重命名，语法如下。

RENAME TABLE 数据表名1 To 数据表名2;

如将table_first命名为tb_first。

rename table table_first to tb_first;

复制表

创建表的CREATE TABLE语句还有另外一种语法结构：在一个已经存在的数据表基础上创建该表的备份，也就是复制表，语法如下。

CREATE TABLE [IF NOT EXISTS] 数据表名 {LIKE 源数据表名|{LIKE 源数据表名}}；

现在创建第二个表，名字叫做tb_second，源数据表就是tb_first。

create table if not exists tb_second like tb_first;

一些操作的语法，可直接套用

为字段添加约束

如为表添加外键约束（外键约束请见《数据完整性约束》一章）。

alter table tb_name add 
(contraint `fri_key_name`  --外键名
    foreign key (col_name) --参照表的字段名
reference tb_name  --被参照表名
    (col_name)  --被参照表的字段名
    [on delete reference_option]
    [on update reference_option]
);

算术运算符

算术运算符是MySQL中最常用的一种运算符。MySQL中支持的运算符如下表。

符号	作用	符号	作用
+	加法运算	%	求余运算
-	减法运算	DIV	除法运算，返回商，同“/”
*	乘法运算	MOD	求余运算，返回余数，同”%“
/	除法运算

加减乘运算符可以同时运算多个操作数。除号和求余运算符也可以同时运算多个操作数，但不建议使用。DIV和MOD这两个运算符只有两个参数，进行除法和求余的运算时，若x2为空值则会返回NULL。

使用加减乘除和求余计算两个操作数的结果

我们首先创建一个表，然后创建两列，第一个操作数为7，第二个操作数为2，然后计算，查看结果。

（下面代码中insert语句和select语句后面会讲到）

create table demo (num1 int,num2 int);
insert into demo (num1,num2) values (7,2);
select num1,num2,num1+num2,num1-num2,num1*num2,num1/num2,num1%num2 from demo;

比较运算符

比较运算符在查询数据时常用的一种运算符，SELECT语句中经常用到比较运算符。MySQL中常用的比较运算符如下表。

符号	名称	示例	符号	名称	示例
=	等于	id=5	is not null	n/a	id is not null
>	大于	id>5	between and	n/a	id between 1 and 5
<	小于	id<5	in	n/a	id in (3,4,5)
>=	大于等于	id>=5	not in	n/a	id not in (3,4,5)
<=	小于等于	id<=5	like	模式匹配	name like (‘shi%’)
!= <>	不等于	id!=5	not like	模式匹配	name not like (‘shi%’)
is null	n/a	id is null	regexp	常规表达式	name 正则表达式

下面对常用的比较运算符进行详解。

在进行演示前，我们先创建如下的数据表，并且输入如图的数据。

运算符“=”

它用来判断数字、字符串和表达式是否相等。如果相等返回1；否则返回0。如：

select * from tb_demo where id = 1;

运算符“<>”和“!=”

这两个不等于符号可以用来判断数字、字符串和表达式等是否不相等，如果不相等则返回1；否则返回0。如：

select * from tb_demo where id != 1;

运算符“>”

大于号可以用来判断左边的操作数是否大于右边的操作数。如：

select * from tb_demo where id > 2;

“<”、“>=”、“<=”等的用法与“>”基本相同，这里不再赘述。

运算符 IS NULL

IS NULL用于判断操作是否是空值，操作数为空值时结果返回1，否则返回0。如：

select * from tb_demo where username is NULL;

运算符 BETWEEN AND

BETWEEN AND运算符用于判断数据是否在某个取值范围内，表达式如下：

x BETWEEN x1 AND x2

若x大于x1小于x2则返回1，否则返回0。如：

select * from tb_demo where id between 1 and 3;

运算符IN

IN用于判断数据是否存在于某个集合中，表达式如下：

x IN (x1,x2,...,xn)

若x的值等于序列中的任意一个则返回1，否则返回0。如

select * from tb_demo where username in ('ab','abc','abcd');

运算符 LIKE

LIKE可用于匹配字符串，表达式如下：

x LIKE s

若x与s匹配则返回1，否则返回0。如：

select * from tb_demo where password like '%bcde';

运算符REGEXP

REGEXP同样用于匹配字符串，但其使用的是正则表达式，表达式如下：

x REGEXP s

若x满足s表示的匹配方式则返回1，否则返回0。如：

select * from tb_demo where password regexp '^a';

逻辑运算符

逻辑运算符用来判断表达式的真假，若表达式为真则返回1，否则返回0。MySQL中支持四种逻辑运算符：与（&&或AND）、非（!或NOT）、或（||或OR）、异或（XOR）。下面分别介绍这四种运算符。

与运算

“&&”和AND是与运算的两种表达方式。如果所有数据不为0且不为空值NULL，结果返回1；如果存在任何一个数据为0，结果返回0；如果有一个数据为NULL且没有数据为0时返回NULL。可以有多个操作数同时进行与运算。

或运算

“||”或者OR表示或运算。所有数据中存在任何一个数据为非0的数字时返回1；如果数据中不包含非0的数字但包含NULL，结果返回NULL；如果数据中有结果返回0。可以有多个操作数同时进行或运算。

非运算

“!”或者NOT表示非运算。通过非运算，表达式会返回一个与操作数据相反的结果。若数据为非0的数字则结果返回0；若数据为0则结果返回1；若操作数是NULL则返回NULL。

异或运算

XOR表示异或运算，只要其中任何一个操作数为NULL则结果返回NULL；若两个操作数都为非0值则结果返回0；若两个操作数有一个不为0则结果返回1。

如：

符号	作用
&	按位与。先将十进制数转换成二进制数，然后对每个位的数进行按位与运算，1和1相与得1，与0相与得0，最后再将二进制数转换为十进制数
\|	按位或。先将十进制数转换成二进制数，然后对每个位的数进行按位或运算，1和任何数相或都是1，0与0相或为0，最后再将二进制数转换为十进制数
~	按位取反。先将十进制数转换成二进制数，然后对每一位上的数进行取反操作，0取反为1，1取反为0，然后再将二进制数转换为十进制数
^	按位异或。先将十进制数转换为二进制数，然后对每位数都进行按位异或运算，相同的数异或之后是0，不同的数异或之后是1，然后再将二进制数转换成十进制数
<<	按位左移。将左操作数的二进制数向左移动右操作数位，右边补0，最后转换为十进制数
>>	按位右移。将左操作数的二进制数向右移动右操作数位，左边补0，最后转换为十进制数

运算符优先级

运算符优先级如下表。

优先级	符号
1	!
2	~
3	^
4	*,/,DIV,%,MOD
5	+,-
6	>>,<<
7	&
8	\|
9	=,<=>,>,>=,<,<=,!=,<>,IN,IS,NULL.LIKE,REGEXP
10	BETWEEN,AND,CASE,WHEN,THEN,ELSE
11	NOT
12	&&,AND
13	\|\|,OR,XOR
14	:=

添加数据

使用INSERT...VALUES语句插入新记录

语法：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名 [(字段名,...)]
    VALUES ({值 | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE 字段名=表达式, ... ]

插入一条记录

首先来看下表的结构，如下图。

代码如下。

insert into demo_insert values(1,'张三','zhangsan','2000-01-01 12:00:00');

插入部分语句

为没有自动增长或默认值等字段添加记录，代码如下。

insert into demo_insert (user,password,createtime) values ('李四','lisi','2014-01-01 12:00:00');

插入多条记录

代码如下（输入此代码之前删除了之前的记录）。

insert into demo_insert
(user,password,createtime)
values
('张三','zhangsan','2015-01-01 12:00:00'),
('李四','lisi','2014-01-01 12:00:00'),
('王五','wangwu','2014-01-02 00:00:00');

使用INSERT ... SET插入数据

语法：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名
    SET 字段名={值 | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE 字段名=表达式,...]

该语法相比于INSERT ... INTO，在于它直接设置字段的值，出错率更小。

如添加上面实例1的数据。

INSERT INTO demo_insert set id=1,user='张三',password='zhangsan',createtime='2000-01-01 12:00:00';

使用INSERT ... SELECT添加数据

语法：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名 [(字段名,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE 字段名=表达式, ... ]

其中，SELECT子句表示快速从一个或多个表中快速取出数据，并将这些数据作为行数据插入目标数据表。SELECT子句返回的结果集中的字段数和字段类型，必须和目标数据表完全一致。

例：先查询源表中数据。

select * from demo_table;

添加。

insert into demo_insert select * from demo_table;

修改数据

使用UPDATE语句可以修改数据，语法如下。

UPDATE [LOW_PRIORITY][IGNORE] 数据表名
SET 字段1=值1[,字段2=值2...]
[WHERE 条件表达式]
[ORDER BY...]
[LIMIT 行数];

需要特别注意，WHERE千万不能出错，否则会破坏所有已经更改的数据。

如将下图表中id为1的用户名改为王六。

 update demo_update set user='王六' where id=1;

基本查询语句

SELECT语句是最常用的查询语句，它的使用方式有点复杂，但是功能非常强大。语法如下。

select selection_list
from 数据表名
where primary_columns
group by grouping_columns
order by sorting_columns
having secondary_constraint
limit count;

其中的子句将在后面介绍。

使用SELECT语句查询一个数据表

使用具体的字段名查询该字段，或者用*号查询全字段。如：

select * from tb1;

查询表中部分数据

select c1,c3 from tb1;

查询多个表中的数据

select c1,c2,c3,c4,c5 from tb1,tb2;

单表查询

查询所有字段

语法：

SELECT * FROM 表名;

如：

select * from tb1;

查询指定字段

语法：

SELECT 字段名 FROM 表名;

如：

select c1,c3 from tb1;

查询指定数据

可以通过使用WHERE子句来设定查询条件。如：

select * from tb1 where c1>1;

带关键字IN的查询

关键字IN可以判断某个字段的值是否在某一集合中，语法：

SELECT * FROM 表名 WHERE 条件 [NOT] IN (元素1,元素2,...);

如：

select * from tb1 where c1 in (0,1,2);

带关键字BETWEEN AND的范围查询

关键字BETWEEN AND可以判断某个字段的值是否在指定的范围之内，语法：

SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2;

如：

select * from tb1 where c1 between 1 and 3;

带LIKE的字符匹配查询

LIKE是比较常用的比较运算符，可以实现模糊查询，它有“%”、“_”两种通配符。

“%”表示匹配一个或者多个字段，可以代表任意长度的字符串，长度可以为0。

“_”表示只匹配一个字符。

如下面的语句代表查找字段以bc为结尾的数据行：

select * from tb3 where name like '%bc';

用IS NULL查找空值

IS NULL关键字可以判断字段的值是否为空值，语法如下：

IS [NOT] NULL

如查找表中不是NULL的字段：

select * from tb3 where name is not null;

带关键字AND的多条件查询

语法：

SELECT * FROM 数据表名 WHERE 条件1 AND 条件2 [...AND 条件表达式n];

如：

select * from tb1 where id>0 and id<2;

带OR的多条件查询

语法：

SELECT * FROM 数据表名 WHERE 条件1 OR 条件2[...OR 条件表达式n];

如：

select * from tb1 where c1>2 or c2<'b';

使用ORDER BY关键字对查询结果排序

语法：

ORDER BY [ASC|DESC]

其中，ASC表示按照升序排序，DESC表示按照降序排序。

如：

select * from d order by a asc;

select * from d order by a desc;

用GROUP BY关键字分组查询

单独使用GROUP BY关键字分组

如：

select a,count(*) from d group by a;

GROUP BY关键字和GROUP_CONCAT()函数一起使用

通常情况下，GROUP BY关键字和聚合函数一起使用。

如：

select a,group_concat(a) from d group by a;

按多个字段分组

若第一个字段有相同值时再按照第二个字段分组。

select * from d group by b,a;

用LIMIT限制输出结果的数量

如：

select * from d limit 3;

聚合函数查询

COUNT()函数

COUNT()的参数若为非“*”的参数，返回所选集合中非NULL值的行的数目。对于参数“*”，返回集合中所有行的数目，包含NULL值的行。没有WHERE子句的count(*)是经过内部优化的，能够快速返回表中所有数据的总数。

如：

select count(*) from d;

SUM()函数

SUM()函数可以求出表中某个数值类型字段取值的总和。

如：

select *,sum(a) from d;

AVG函数

AVG()函数可以返回某个字段的平均值。

如：

select AVG(a) from d;

MAX()函数

MAX()函数可以返回某个字段的最大值。

如：

select max(a) from d;

MIN()函数

MIN()函数可以返回某个字段的最小值。

select min(a) from d;

连接查询

连接是把不同的表的记录连接到一起，MySQL从一开始就很好地支持连接操作。

内连接查询

内连接是普遍的连接类型，而且是最匀称的，它们要求构成连接的每个表的共有列匹配，不匹配的行将被删除。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,book_borrow.borrower from book_list,book_borrow where id=bookid;

上面代码中，设置了若book_list中的书的“bookid”和book_borrow中借书的“id”相等才连接。

外连接查询

外连接就是使用OUT JOIN关键字连接两个表。选取一个主表，然后在选取一个副表，将主表和副表连接起来。语法如下：

SELECT 字段名称 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.字段名1=表名2.属性名2;

使用左外连接：

左外连接是将左表中所有数据分别都与右表中每条数据相连接组合，返回的结果除了左连接的数据以外，还包括左表中不符合条件的数据，并在右表中不符合条件的位置添加NULL。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,book_borrow.borrower from book_borrow left join book_list on bookid=id;

针对这里的图书信息表和借阅表，内连接和左外连接得到的结果一样。这是因为左表（借阅表）中的数据在右表你（图书信息表）中一定有之相对应的数据。如果将图书信息表作为左表，借阅表作为右表，就会得到如下图的数据。

使用右外连接

右外连接是指将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据以外，还包括右表中不符合条件的数据，并在左表的相应列中添加NULL。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,
book_borrow.borrower from book_borrow right join book_list on bookid=id;

外连接的列顺序还是根据select后面的字段顺序保持一致，也就是说左外连接和右外连接不会改变字段的顺序。
若为左连接，那么左表是LEFT JOIN左边的表，也是主表，若为右连接，那么右表是RIGHT JOIN右边的表，也是主表。

复合条件连接查询

在连接查询时，也可以添加其他的查询条件，使查找结果更加准确。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,book_borrow.borrower from book_list,book_borrow where id=bookid and isback=0;

子查询

子查询就是一个SELECT语句查询另一个SELECT语句的附属。MySQL中支持嵌套多个查询，在里面查询的结果可以作为外面查询的条件。

带关键字IN的子查询

只有在子查询返回至少一个结果时才可以使用带IN关键字的子查询。IN关键字用于检测结果集中是否存在某个特定的值，若检测成功则执行外部查询。

在下面的例子中，我们每次可以执行查询book_borrow表中id字段的所有值，然后再执行查询book_list表中bookid字段含有id字段的可能取值来查询被借阅的书的信息，但是book_borrow中字段的值随时会发生改变，因此我们可以一次使用子查询简化操作。

select * from book_list where bookid in (select id from book_borrow);

带比较运算符的子查询

如下方代码代表查询张三借阅的书的信息：

select * from book_list where book_list.bookid=(select id from book_borrow where borrower='张三');

带EXISTS关键字的子查询

此情况子查询不返回查询的记录而是返回一个真假值。当内层查询语句查询到满足条件的记录就返回真值（true），否则返回假值（false）；当值为真值时外层查询语句开始查询，所以查询的条件一般在内查询实现。

如下面的语句查询李四是否借阅了且没有归还的书，若有就输出：

select * from book_list where exists (select * from book_borrow where borrower='李四' and isback='0' and book_list.bookid=book_borrow.id);

NOT EXISTS关键字与EXISTS关键字相反，当返回值：为true时外查询不查询。

带ANY关键字的子查询

通常与比较运算符一起使用，它代表若内查询中的条件有一个满足就执行外查询，语法如下：

列名 比较运算符 ANY(内查询语句)

如果比较运算符是“<”，则表示小于子查询结果集中的某一个值，如果是“>”，则表示至少大于子查询结果集中的任何一个值。

如下面查找比一年三班最低分高的全部学生信息：

select * from tb_student where score>any(select score from tb_student where class='1.3');

带ALL关键字的子查询

表示满足所有条件。语法如下

列名 比较运算符 ALL(子查询)

如下面查找是否有比一年三班最高分高的全部学生信息：

select * from tb_student where score>all(select score from tb_student where class='1.3');

为表和字段取别名

若表或者字段的名称特别长，可以给表或者字段取别名，采用这种方式可以使代码更加简洁易读，能使查询更加方便。

为表取别名

下面的代码就将book_list表取别名为book，将book_borrow表取别名为borrow。

select * from book_list as book left join book_borrow as borrow on book.bookid=borrow.id;

为字段取别名

为字段取别名就是在输出时让该列的名称用别名替代。

select id,count(id) as borrow_number from book_borrow;

使用正则表达式查询

使用正则表达式比使用通配符更加强大，使用关键字REGEXP关键字匹配查询正则表达式，语法如下：

字段名 REGEXP '匹配方式'

正则表达式请在其他地方学习，这里不过多阐述。

如下面的正则表达式表达式表示匹配以三个数字加一个小写字母开头的字符串。

select d from demo where d regexp '^(\\d{3}[a-z])';

MySQL函数就是MySQL数据库提供的内置函数，可以帮助用户更好地的处理数据表中的数据。下表列举了MySQL内置函数的类别和这些函数的使用范围以及作用。

函数	作用
数学函数	用于处理数字。其中包括绝对值函数、正弦函数、随机数函数等。
字符串函数	用于处理字符串，其中包括字符串连接函数、字符串比较函数、大小写字母转换函数等。
日期和时间函数	用于处理时间和日期。其中包括获取获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数。
条件判断函数	用于在SQL中控制条件选择。其中包括IF、CASE和WHEN语句等。
系统信息函数	用于获取SQL数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。
加密函数	用于对字符串进行加密和解密。其中包含字符串加密函数和字符串解密函数。
其他函数	包括格式化函数和锁函数等。

数学函数

数学函数是MySQL中常用的一类函数，主要用于处理数字，包括整型和浮点型数等。常用的数学函数如下表：

函数	作用
ABS(x)	返回x的绝对值
CEIL(x)，CEILIN(x)	返回不小于x的最小整数值
FLOOR(x)	返回不大于x的最大整数值
RAND()	返回0~1的随机数
RAND(x)	返回0~1的随机数，x相同时返回的随机数相同
SIGN(x)	返回参数的符号，x的值分别为负数、0和正数时返回结果分别是-1、0、1
PI()	返回圆周率的值
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值
ROUND(x)	返回离x最近的整数
ROUND(x,y)	保留x小数点后y位的值，但截断会使用四舍五入
POW(x,y)，POWER(x,y)	返回x的y乘方的结果值
SQRT(x)	返回非负数x的二次方根
EXP(x)	返回e的x乘方后的值
MOD(x,y)	返回x除以y的余数
LOG(x)	返回x的基数为2的对数
LOG10(x)	返回x的基数为10的对数
RADIANS(x)	将x转换为弧度
DEGREES(x)	将x转换为角度
SIN(x)	返回x的正弦，其中x为弧度值
ASIN(x)	返回x的反正弦，若x不在-1~1的范围之内则返回NULL
COS(x)	返回x的余弦，其中x为弧度值
ACOS(x)	返回x的反余弦，若x不在-1~1的范围之内则返回NULL
TAN(x)	返回x的正切值，其中x为弧度值
ATAN(x)，ATAN2(x,y)	返回两个变量x及y的反正切。它类似于x或y的反正切计算，除非两个参数的符号均用于结果所在象限
COT(x)	返回x的余切，其中x为弧度值

字符串函数

常用的字符串函数如下表：

函数	作用
CHAR_LENGTH(s)	返回s的字符数
LENGTH(x)	返回值为字符串x的长度，单位为字节。
CONCAT(s1,s2,...)	返回结果为连接参数产生的字符串。若有一个参数为NULL则返回值为NULL，若所有参数均为非二进制字符串则结果为非二进制字符串；若参数中有一个是二进制字符串则结果为二进制字符串；一个数字参数会被转换成与之相等的二进制字符串格式，要避免此情况可以使用显示类型cast。
CONCAT_WS(x,s1,s2,...)	同CONCAT(x)函数，但是每个字符串要加上x
INSERT(x1,x,len,s2)	将字符串s1的x位置开始、长度为len的字符串替换成s2
UPPER(s),UCASE(s)	将字符串s的所有字母都变成大写字母
LOWER(s),LCASE(s)	将字符串s的所有字母都变成小写字母
LEFT(s,n)	返回字符串s的开始的最左n个字符
RIGHT(s,n)	返回从字符串s的最右n个字符
LPAD(s1,len,s2)	返回字符串s1，其左边由字符s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符
RPAD(s1,len,s2)	返回字符串s1，其右边被字符串s2填补至len字符长度
LTRIM(s)	将字符串s的前导空格删除后返回s
RTRIM(s)	将字符串s的结尾空格删除后返回s
TRIM(s)	去掉字符串s开始处和结尾处的空格
TRIM(s1 FROM s)	去掉字符串s中开始处和结尾处的字符串s1
REPEAT(s,n)	将字符串s重复n次
SPACE(n)	返回n个空格
REPLACE(s,s1,s2)	用字符串s2替代字符串s中的字符串s1
STRCMP(s1,s2)	比较字符串s1和s2
SUNSTRING(s,n,len)	获取从字符串s中的第n个位置开始、长度为len的字符串
MID(s,n,len)	同SUBSTRING(s,n,len)
LOCATE(s1,s),POSITION(s1 in s)	从字符串s中的获取s1的开始位置
INSTR(s,s1)	查找字符串s1在s中的位置，返回首次出现位置的索引值
REVERSE(s)	将字符串s的顺序反过来
ELT(n,s1,s2,...)	返回第n个字符串
EXPORT_SET(bits,on,off,[,separator[,number_of_bits]])	返回一个字符串，生成规则如下：针对bits的二进制模式，如果其位为1，则返回一个on值；如果其位为0，则返回一个off值。每个字符串采用separator进行分隔，默认值为“,”。number_of_bits参数指定bits可用的位数，默认为64位。例如，生成数字位182的二进制（10110110）替换格式，以“@”作为分隔符，设置有效位为6位。其语句如下：select EXPORT_SET(182,‘Y’,‘N’,‘@’，6);其运行结果为：N@Y@Y@N@Y@Y
FIELD(s,s1,s2,...)	返回一个与字符串s匹配的字符串的位置
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置
MAKE_SET(x,s1,s2,...)	按x的二进制数从s1，s2，...中选取字符串

日期时间函数

常用的日期时间函数如下：

函数	作用
CURDATE(),CURRENT_DATE()	返回当前日期
CURTIME(),CURRENT_TIME()	返回当前时间
CURRENT_TIMESTAMP(),LOCALTIME()	返回当前日期和时间
NOW(),SYSDATE(),LOCALTIMESTAMP()	返回当前日期和时间
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(d)	将时间d以UNIX时间戳的形式返回
FROM_UNIXTIME(d)	以UNIX时间戳的时间转换为普通格式的时间戳
UTC_DATE()	返回UTC日期
UTC_TIME()	返回UTC时间
MONTH(d)	返回日期d中的月份值，范围是1~12
MONTHNAME(d)	返回日期d中的月份名称
DAYNAME(d)	返回日期d是星期几，如Monday
DAYOFWEEK(d)	返回日期d是星期几，0是星期一，1是星期二等
WEEK(d)	计算日期d是本年的第几个星期，范围是0~53
WEEKOFYEAR(d)	计算日期d是本年的第几个星期，范围是1~53
DAYOFYEAR(d)	计算日期d是本年的第几天
DAYOFMONTH(d)	计算日期d是本月的第几天
YEAR(d)	返回日期d中的年份值
QUARTER(d)	返回日期d是第几季度，范围是1~4
HOUR(t)	返回时间t中的小时值
MINUTE(t)	返回日期t中的分钟值
SECOND(t)	返回日期t中的秒钟值
EXTRACT(type FROM d)	从日期d中获取指定的值，type指定返回的值，如YEAR、HOUR等
TIME_TO_SEC(t)	将时间t转换为秒
SEC_TO_TIME(s)	将以秒为单位的时间s转换为时分秒的格式
TO_DAYS(d)	计算日期d从0000年1月1日的天数
FROM_DAYS(n)	计算从0000年1月1日开始天后的日期
DATEDIFF(d1,d2)	计算日期d1和d2相隔的天数
ADDDATE(d,n)	计算起始日期d加上n天后的日期
ADDDATE(d,INTERVAL expr type)	计算起始日期加上一个时间段后的日期
DATE_ADD(d,INTERVAL expr type)	同ADDDATE(d,INTERVAL n type)
SUBDATE(d,n)	计算起始日期d减去n天后的日期
SUBDATE(d,INTERVAL expr type)	计算起始日期d减去一个时间段的日期
ADDTIME(t,n)	计算起始时间t加上n秒的时间
SUBTIME(t,n)	计算起始时间t减去n秒的时间
DATE_FORMAT(d,f)	按照表达式f的要求显示日期d
TIME_FORMAT(t,f)	按照表达式f的要求显示时间d
GET_FORMAT(type,s)	根据字符串s获取type型数据的显示格式

条件判断函数

条件判断函数如下：

函数	作用
IF(expr,v1,v2)	若表达式expr成立，则执行v1，否则执行v2
IFNULL(v1,v2)	若v1不为空则返回v1的值，否则返回v2的值
CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2 ...][ELSE vn] END	case表示函数开始，end表示语句结束。若表达式expr1成立则返回v1的值；若表达式expr2成立则返回v2的值，以此类推，最后遇到ELSE时返回vn的值。

系统信息函数

常用的系统信息函数如下：

函数	作用
VERSION()	获取数据库的版本号
CONNECTION_ID()	获取数据库的连接数
DATABASE(),SCHEMA()	获取当前数据库名
USER(),SYSTEM_USER(),SESSION_USER()	获取当前用户
CURRENT_USER(),CURRENT_USER	获取当前用户
CHAR_SET(str)	获取字符串str的字符集
COLLATION(str)	获取字符串str的字符排列方式
LAST_INSERT_ID()	获取最近生成的AUTO_INCREMENT值

其他函数

MySQL中的其他函数如下：

函数	作用
FORMAT(x,n)	将数字x进行格式化，将x保留到小数点后6位。这个过程需要四舍五入
ASCII(s)	返回字符串s的第一个字符的ASCII码
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	将x从f1进制数变成f2进制数
INET_ATON(IP)	将IP地址转换成数字显示
INET_NTOA(N)	将数字N转换成IP的形式
GET_LOCK(name,time)	定义一个名称为name、持续时间为time秒的锁。锁定成功返回1，锁定失败返回0，若遇到错误则返回NULL
RELEASE_LOCK(name)	解除名称为name的锁。若解锁成功则返回1，若尝试超时返回0，若遇到错误则返回NULL
IS_FREE_LOCK(name)	判断是否使用名为name的锁。若使用则返回0，否则返回1
BENCHMARK(count,expr)	将表达式expr重复执行count次
CONVERT(s using cs)	将字符串s的字符集变为cs
CAST(x AS type), CONVERT(x,type)	将x变为type类型，这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER类型起作用。这两种方法只是改变了输出值的数据类型，并没有改变表中字段的类型

在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中，其中就包含IF语句、CASE语句、WHILE语句、LOOP语句、REPEAT语句、LEAVE语句和ITERATE语句，它们可以进行流程控制。

定义一个存储过程体的模板是：

delimiter 结束符号
create procedure 存储过程体名(in/out 变量名 变量类型)
begin
存储过程体
end
结束符号

其中，结束符号一般使用“//”，它作为调用此存储过程体的结束标志。

存储过程体名要是MySQL中有效的标识符，in/out表示调用此存储过程体时输入还是输出。

IF语句

IF语句可以用来进行条件判断，根据条件的真假执行不同的操作。

IF语句的表示形式如下：

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

其中，condition表示判断条件。如：

delimiter //
create procedure example_if(in x int)
begin
if x=1 then select 1;
elseif x=2 then select 2;
else select 'other';
end if;
end
//

CASE 语句

CASE语句为多分支语句，该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值，如果查找到则执行该分支的语句，否则执行ELSE后面的内容。

CASE语句的语法如下：

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

其中，value表示条件判断的变量，WHEN后面的value表示变量的取值。如：

delimiter //
create procedure example_case(in x int)
begin
case x
when 1 then select 1;
when 2 then select 2;
else select 'other';
end case;
end
//

WHILE语句

WHILE语句执行时首先判断condition条件是否为真，如果为真则执行循环，否则退出循环。

WHILE语句的语法如下：

WHILE condition DO
...
END WHILE;

其中condition表示判断的条件。如

delimiter //
create procedure example_while(in x int)
begin
while x<=5 do
select x;
set x = x+1;
end while;
end
//

LOOP 语句

LOOP语句没有循环条件，但是可以通过LEAVE语句退出循环。

LOOP语句的语法如下：

label:loop
...
end loop;

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，中间省略的部分是要重复执行的语句，退出循环用LEAVE语句，语法为LEAVE label。

如：

delimiter //
create procedure example_loop(in x int)
begin
label:loop
if x>5 then leave label;
end if;
select x; 
set x = x+1;
end loop;
end
//

REPEAT语句

REPEAT语句是先执行一个循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环。

REPEAT语句的语法如下：

REPEAT
...
UNTIL condition
END REPEAT

如：

delimiter //
create procedure example_repeat(in x int)
begin
repeat
select x;
set x = x+1;
until x=5
end repeat;
end
//

在一个循环中继续循环

使用关键字ITERATE跳过本次循环，在LOOP，REPEAT和WHILE语句中很常用。使用ITERATE label跳过本次循环。

如：

delimiter //
create procedure example_iterate(in x int)
begin
label:while x<5 do
set x = x+1;
select x;
if x=3 then iterate label;
end if;
end while;
end //

终止循环：使用关键字LEAVE，语法是leave label，其中label表示为循环指定的标签。

索引是一种特殊的数据库结构，是提高数据库性能的重要方式，可以用来快速查询数据库表中的特定记录，MySQL中所有的数据类型都可以被索引。

我们来看一个数据表有索引和无索引时检索相同数据的时间。

首先看一下表的结构：

我们为num1添加了索引，num2没有。

然后我们查看里面的数据。

在此表中添加了两个字段在这两个字段中num1添加了索引，num2没有添加索引，还添加了2432358条数据行，0~2432357，我们分别使用where语句设置查询条件为字段1和字段2的某个值，分别看一下查询速度。

可以看到使用索引后查询几乎可以秒出，但是不使用索引就会遍历整个表，非常耗费时间。

索引概述

索引分类

普通索引

普通索引不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。在某数据表的某一字段中创建该类型索引后，用户便可通过索引进行查询。

唯一索引

使用UNIQUE参数可以设置索引为唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊的唯一索引。

全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能在CHAR、VARCHAR、TEXT类型的字段上创建。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。如，查询带有回复内容的字段，可以应用全文索引方式。需要注意的是，在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序，可以使用对大小写敏感的全文索引。

单列索引

顾名思义，单列索引即只对一个字段生效的索引，其可以包含上述3种索引方式。应用该索引时必须保证该索引的值对应一个字段。在添加单列索引时，若被索引的列为varchar、text等类型，则还可以为索引添加长度。

多列索引

多列索引实在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段的第一个字段。多列索引在使用联合查询时会起到优化作用，但是它不能优化单独使用被索引的列满足特定条件的查询。多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

空间索引

使用SPATIAL参数可以设置该索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间型数据的速度。MySQL中只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

创建索引

在创建数据表时创建索引

语法如下：

create table table_name(
属性名 数据类型[约束条件],
...,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名] （属性1[(长度)][ASC|DESC]
);

INDEX和KEY参数用于指定字段索引，选择其中一个即可。

创建普通索引

create table score(
    id int(11) not null auto_increment primary key,
    name varchar(50) not null ,
    math int(5) not null ,
    chinese int(5) not null ,
    english int(5) not null ,
    index(id)
);

在最后一行可以看到，id已经被索引。

创建唯一索引

create table address(
id int(11) auto_increment primary key not null,
    name varchar(50),
    detail_address varchar(200),
    unique index address(id)
);

在数据完整性约束里，若为字段设置主键约束，则MySQL会自动为该字段添加主键索引；若为字段设置候选键约束，则MySQL会自动为该字段设置唯一索引。
设置主键约束的部分语法为constraint index_col_name primary key (col_name)，其中constaint关键字表示下面的语句代表添加完整性约束，index_col_name代表主键的名称，col_name代表字段，如下面为表a中的字段a添加主键约束，由于MySQL会自动为该字段添加主键索引，因而运行结果如下图。
下面是为字段b添加候选键约束的演示。
运行结果

创建全文索引

create table cards(
id int(11) auto_increment primary key not null,
    name varchar(50),
    number bigint(11),
    info varchar(50),
    fulltext key card_info(info)
)

创建单列索引

create table telephone(
id int(11) primary key auto_increment not null,
    name varchar(50) not null,
    tel varchar(50) not null,
    index tel_num(tel(20))
);

创建多列索引

create table information(
id int(11) auto_increment primary key not null,
    name varchar(50) not null,
    sex varchar(5) not null,
    birthday varchar(50) not null,
    index info(name,sex)
);

创建空间索引

create table list(
id int(11) primary key auto_increment not null,
    goods geometry not null,
    spatial index listinfo(goods)
);

在已建立的数据表上创建索引

语法结构如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(属性 [(length)])[ASC|DESC]);

创建普通索引

在创建普通索引之前，先来看一下student的表的结构。

create index id_index on student(id);

创建唯一索引

语法：

CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);

我们在删除前面创建的索引之后为id添加一个唯一索引（删除索引的语句没有包含在下面的截图中）：

create unique index id_uindex on student(id);

创建全文索引

语法如下：

CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);

我们在演示之前先创建一个如下的数据表：

create fulltext index info_index on information(info);

创建单列索引

语法如下：

CREATE INDEX 索引名 ON 数据表名称(字段名称(长度));

设置字段名称长度，可以优化查询，提高查询效率。

我们为info中的information添加单列索引（先删除）：

create index info_index on information(info(10));

创建多列索引

语法如下：

CREATE INDEX 索引名 ON 数据表名称(字段1,...);

如我们为数据表student添加一个多列索引：

create index multi_index on student(name,age);

创建空间索引

语法如下：

CREATE SPATIAL INDEX 索引名 ON 数据表名称(字段名称);

删除索引

若索引不使用也不删除，会既占用系统资源也降低表更新的速度。删除索引的语法如下：

DROP INDEX 索引名 ON 数据表名;

我们可以看到demo表中的字段num1存在我们之前创建过的索引，我们删除此索引。

drop index num on demo;

可以看到成功删除了索引。

视图是从一个表或多个表中导出的表，是一种虚拟的表。视图就像一个窗口，通过这个窗口就可以看到系统专门提供的数据。这样，用户就不需要查看整个数据表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更加方便，而且可以保障数据库系统的安全性。

视图概述

视图是一个虚拟表，是从数据库中的一个或多个表中导出来的表，其内容由查询语句定义。同真实的表一样，视图包含一系列的行列数据。但是，数据库中只存放了视图的定义，而没有存放视图中的数据。这些数据还存在原来的表中。使用视图查询数据时，数据库系统会在原来的表中取出对应的数据。因此，视图的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图是保存在数据库中的查询SQL语句，它的存在主要出于两个原因：首先是安全原因，视图可以隐藏一些数据，例如，它可以用视图显示员工信息表中的姓名、工龄、地址而不显示社会保险号和工资等；另一个原因是它可使复杂的查询易于理解和使用。

对所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过视图修改数据时的限制也很少，视图的作用可以总结为以下几点：

1. 简单性

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。可以将经常使用的查询定义为视图，这样用户就不必为以后的操作每次都指定全部的条件。

2. 安全性

视图的安全性可以防止未授权用户查看特定的行或列，使有权限的用户只能看到表中特定行的方法如下：

(1) 在表中增加一个标志用户名的列。

(2) 建立视图，使用户只能看到标有自己用户名的行。

(3) 把视图授权给其他用户。

3. 逻辑数据安全性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在试图之上，从而使程序与数据库表被视图分割开来。视图可以在以下几个方面与数据独立。

(1) 如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序不动。

(2) 如果应用建立在数据库表上，当应用发生变化时，可以在表上创建视图，通过视图屏蔽应用的变化，从而使数据库表不动。

(3) 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序不动。

(4) 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序不动。

创建视图

查看创建视图的权限

查询是否具有操作视图的权限由Create View语句和select语句实现，代码如下:

SELECT Selete_priv,Create_view_priv FROM mysql.user WHERE user = '用户名';

其中Selete_priv代表用户是否具有SELECT权限，Y表示具有SELECT权限，N表示没有。

Create_view_priv代表是否具有CREATE VIEW的权限，mysql.user表示查看mysql下的user表。

如我们查看管理员root是否具有这两个权限：

select select_priv,create_view_priv from mysql.user where user = 'root';

结果中select_priv和create_view_priv权限都为Y，表示root具有这两个权限。

创建视图

创建视图的语法如下：

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION];

ALGORITHM：表示视图选择的算法。

(属性清单)：可选参数，指定视图中各个属性的名词，默认情况下与SELECT语句中查询的属性相同。

SELECT语句：一个完整的查询语句，表示从某个表中查出满足条件的记录，将这些记录导入视图中。

WITH...：可选参数，表示更新视图时要保证在该视图的权限范围之内。

创建视图的注意事项：
运行创建视图的语句需要用户具有CREATE_VIEW的权限
SELECT语句不能包含FROM字句中的子查询
在定义中引用的表或者视图必须存在
在定义中不能使用多个temporary表，不能创建temporary视图
在视图中允许定义ORDER BY，但是若从特定视图中选择，而该视图中使用了具有自己定义的ORDER BY的语句，它将被忽略

如我们为一个员工信息表创建视图。

在创建视图之前先创建数据表。

第一个数据表为员工信息表，此表中的id代表编号，name代表员工姓名，sex代表员工性别，salary代表员工工资，section代表员工部门，telephone代表员工电话号码，number代表员工编号。

在此数据表中，我们设定工资、部门、员工编号为隐私信息，那么我们就可以创建一个只显示字段id、name、sex、telephone的视图。

create view v_employees (id,name,sex,tele) as select id,name,sex,telephone from employees;

到这里视图创建成功。

视图操作

查看视图

查看视图指查看数据库中存在的视图，需要具有SHOW VIEW权限。查看视图的方法如下。

DESCRIBE 视图名;
DESC 视图名;
SHOW TABLE STATUS LIKE '视图名';
SHOW CREATE VIEW 视图名;

以上四种方法都可以查看已创建的视图，下面都进行演示。

desc v_employees;

show table status like 'v_employees';

show create view v_employees;

修改视图

修改视图指的是修改数据库中已存在的表的定义。当基本的表字段发生改变时，可以通过修改视图来保持视图和表保持一致。修改视图的语法如下：

CREATE OR REPLACE VIEW [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图[(属性清单)] AS SELECT语句 [WITH [CASCADE|LOCAL] CHECK OPTION];

ALTER [algorithm={undefined|merge|template}] VIEW view_name [(column_list)] AS select_statement [with [cascaded|local] CHECK OPTION];

若在创建视图时使用了WITH CHECK OPTION、WITH ENCRYPTION、WITH SCHEMABING或VIEW_METADATA选项，修改视图时项保存这些选项提供的功能，必须在ALTER VIEW语句中将它们包括进去。

下面分别使用这两个语句修改上文创建的视图，这里我们先使用CREATE OR REPLACE VIEW语句删除字段tele。

create or replace view v_employees (id,name,sex) as select id,name,sex from employees;

再使用ALTER VIEW重新添加字段tele。

alter view v_employees (id,name,sex,tele) as select id,name,sex,telephone from employees;

更新视图

更新视图指对表的更新。但是视图有很多限制条件所以我们一般不更新视图。更新视图的语法如下：

UPDATE view_name SET column_name=value ... [WHERE子句];

下面演示如何通过视图更改表的数据，首先看一下视图和表的数据。

然后我们输入下面的语句，然后再查看视图和表的数据。

update v_employees set tele='30003' where id='3';

可见数据被成功修改了。

但是若有以下几种情况是不能更新视图的：

视图中包含COUNT()、SUM()、MAX()和MIN()等函数；
视图中包含UNION、UNION ALL、DINTINCT、GROUP BY、HAVIG等关键字；
常量视图，如create view view_book as select 'ansheng' as ansheng;；
视图中SELECT包含子查询；
由不可更新的视图导出的视图，如create view view_book as select * from book_view1;；
创建视图时ALGORITHM为TMPLTABLE类型；
视图对应的表存在没有默认值的列，而且该列没有包含在视图里。

删除视图

语法如下：

DROP VIEW IF EXISTS <视图名> [RESTRICT|CASCADE];

该语句从数据字典中删除指定的视图定义；若该视图导出了其他视图，则使用CASCADE级联删除，或者先显式删除导出的视图，再删除该视图；删除基表时，由该基表导出的所有视图必须显式删除。

如我们删除上面创建的v_employees视图。

drop view id exists v_employees;

由于视图是虚拟表，因此查询视图的语法和查询普通表相同，如下图。
运行结果

数据完整性是指数据的统一性和相容性，是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。一旦定义了完整性约束，MySQL服务器会实时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性和正确性。这样既能防止对数据库的意外破坏又能减轻数据库管理人员的维护负担。

定义完整性约束

实体完整性

实体是一个数据对象，是指客观存在并且可以相互区分的事物。如一个教师，一个学生，一个桌子，一个椅子。一个实体在数据中表示为一条记录，通常它必须遵循实体完整性约束。

在MySQL中实体完整性是由主键约束和候选键约束实现的。

主键约束

主键可以是一个字段，也可以是多个字段的组合，叫做复合主键。主键约束必须满足以下规则：

每个表只能定义一个主键。
唯一性原则：主键的值，也称键值，必须能够唯一标识表中的某一条数据，且不能为NULL。
最小化原则：复合主键不能包含不必要的多余字段。
一个字段名在复合主键中只能出现一次。

在MySQL中，可以在CREATE TABLE和ALTER TABLE语句中使用PRIMARY KEY关键字创建主键约束，实现方式有以下两种：

第一种，在字段的定义后面声明该字段为主键。如下面将id字段设为主键。

create table tb_user(
id int primary key auto_increment,
user varchar(50) not null,
password varchar(50) not null,
createtime date not null
);

可以看到，此数据表中字段id为主键。

第二种，在最后声明，如将主键设为id和class字段。

create table tb_stu(
    id int not null auto_increment,
    name varchar(50) not null,
    sex enum('男','女') not null,
    class int not null,
    primary key (id,class)
);

可以看到此数据表中字段id和class都为主键，即为复合主键。

候选键约束

若一个属性能完整标识一个元组，那么这个属性就可以设置成候选键，如学号可以唯一地区分不同的学生，那么学号就可以设为候选键。候选键可以是某一列，也可以是多个列组成的一个组合。候选键必须唯一且不能为NULL。候选键使用CREATE TABLE或ALTER TABLE语句创建，其实现方法与主键约束类似，也是作为列的完整性约束和表的完整性约束两种格式。

在MySQL中，候选键与主键有以下两种区别：

一个表只能创建一个主键，但是可以创建多个候选键；
定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。

如我们创建一个员工表，将id和姓名作为候选键。

create table tb_employees (
id int auto_increment unique,
    name varchar(50) not null unique,
    sex enum('男','女'),
    tele varchar(50)
);

参照完整性

参照完整性规则就是定义外码（外键）和主码（主键）之间的引用规则，它是对关系间引用数据的一种限制。

InnoDB引擎定义外键约束的语法如下：

[CONSTRAINT[SYMBOL]] FOREIGN KEY (index_col_name) reference_definition;

reference_definition主要用于定义外键所参照的表、列、参照动作的声明和实施策略四部分内容。它的基本语法如下：

REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL|MATCH PARTIAL| MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]

index_col_name的语法格式如下：

col_name [(length)] [ASC|DESC]

reference_option的语法格式如下：

RESTRICT|CASCADE|SET FULL|NO ACTION

参数说明如下：

index_col_name：用于指定被设置为外键的字段。
tbl_name：用于指定外键所参照的表名。这个表称为被参照表（或父表），而外键所在的表为参照表（或子表）。
col_name：用于指定被参照的列名，外键可以引用被参照表中的主键或候选键，也可以引用被参照表中某一些字段的组合，但这个组合不能是被参照表中随机的一组列，必须保证该组合的取值在被参照表中是唯一的。外键中所有列值在被参照表中必须全部存在，也就是通过外键来对参照表中某些列的取值进行限定和约束。
ON DELETE|ON UPDATE指定参照动作相关的SQL语句。可为每个外键指定对应于DELETE语句和UPDATE语句的参照动作.
reference_option：指定参照完整性约束的实现策略。其中当没有明确指定参照完整性约束的实现策略时，两个参照动作都会使用RESTRICT，具体策略见下表：

可选值	说明
RESTRICT	限制策略：当要删除或更新被参照表中被参照表列上、在外键中出现的值时，系统拒绝对被参照表的删除或更新操作
CASCADE	级联策略：从被参照表中删除或更新记录行时，自动删除或更新参照表中的记录行
SET FULL	置空策略：当从被参照表中删除或更新记录行时，设置参照表中与之对应的外键的值为NULL，这个记录需要被参照表中的外键列没有声明为NOT NULL
NO ACTION	不采取实施策略：当一个相关的外键值在被参照表中时，删除或更新被参照表中键值的动作不被允许。该策略的动作语言与RESTRICT相同

如我们定义一个存储学生信息的数据库，在这里面存在两个数据表：tb_stu和tb_class。tb_stu中我们定义五个字段：id、name、sex、classid（班级id）、remark（备注），将id设为主键；tb_class中定义两个字段：id（班级id）、classname，在此数据表中，我们约定班级id一一对应班级名称，那么我们就可以通过将tb_stu中的classid定义为外键，与tb_class中的主键id相连接，这就是参照完整性。在向学生数据表中添加信息时，若我们添加的班级id在班级数据表中不存在则添加失败。

实现上述功能的步骤如下：

创建学生信息表和班级id转换表。

班级id转换表
运行结果
学生信息表（创建的同时将classid的外键连接到tb_class的主键id上）

create table tb_stu(
id int auto_increment primary key,
name varchar(50) not null,
classid int not null,
remark varchar(50),
foreign key (classid)
references tb_class (id)
on delete restrict
on update restrict
);

注意在创建学生信息表的时候我们指定了on delete restrict on update restrict，这个代表在尝试删除参照表也就是学生信息表时若该数据行的classid字段的值等于被参照表班级id参照表中id的某一个值就会弹出删除失败的信息，若尝试添加一个不存在的班级id的学生时也会报出添加失败的信息。如我们先录入几个班级id和班级名称对应的关系：

insert into tb_class (classname) values ('一年一班'),('一年二班'),('一年三班'),('一年四班');

我们尝试添加一个不存在的班级id的学生：

insert into tb_stu (name,classid) values ('张三',0);

可见0不存在于班级id中，下面我们添加一个班级id为1的学生：

insert into tb_stu (name,classid) values ('张三',1);

可见添加成功。

我们再尝试班级id参照表中classid为1的数据行。

delete from tb_class where id=1;

可见由于外键约束，学生信息表中还存在着classid为1的数据行，因此删除失败，解决方式就是先删除学生信息表中classod为1的数据行再删除此数据。

设置外键时，必须遵守如下规则：

被参照表必须是存在的，或者是当前正在创建的表。若为当前正在创建的表，也就是说，被参照表与参照表是一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为被参照表定义主键。
必须在被参照表名后指定列名或列名的组合。这个列或者列组合必须是这个被参照表的主键或候选键。
外键中列的数据类型必须与被参照表中主键（或候选键）的数据类型相同。
外键中列的数目必须与被参照表中列的数据相同。
尽管主键不能包含空值，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

用户定义完整性

用户定义完整性规则是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求。

非空约束

若要设置某一字段不为空，可以再CREATE TABLE或者ALTER TABLE语句中添加关键字NOT NULL修饰。如：

可以看到我们将字段c1设为非空，在插入数据行时若c1为空则会报错，而无论c2是否为空都不会报错。

CHECK约束

对列实施CHECK约束

若要对列实施CHECK约束，只需要在该列定义后加一个CHECK(para)即可，其中para表示判断的表达式，如我们设置c列必须大于10且小于20。

如下面我们定义一个字段，在该字段后面加上CHECK约束，使该字段的值大于10且小于20。

create table demo (c int check(c>10 and c<20));

可以看到我们设置CHECK约束之后当c等于1时插入失败，而当c等于15时可以成功插入。

对表实施CHECK约束

若要对表实施完整性约束，可以在CREATE TABLE语句或ALTER TABLE语句中定义一个CHECK语句即可，语法如下：

CREATE TABLE table_name(
col_definitions ...
    CHECK(para)
)

其中para代表检查条件，如CHECK(classid in(2,3))代表classid必须在2和3里面，CHECK(classid in(select id from tb_class))代表classid必须为tb_class表中的某一个值。如：

create table demo(
c int not null,
    check(c>0 and c<100)
)

可以看到我们定义c必须大于0且小于10，在插入的值为-1时插入失败，在插入的值为1时插入成功。

命名完整性约束

在MySQL中也可以对完整性约束进行添加、修改和删除等操作，在进行这些操作之前就需要先将完整性约束命名。命名完整性约束的方式是在定义完整性约束的语句前加CONSTRAINT字句，具体语法如下：

CONSTRAINT <symbol>
[PRIMARY KEY 短语|UNIQUE 短语|FOREIGN KEY 短语|CHECK 短语]

其中symbol就为完整性约束的名字。

如我们在创建员工表的同时为id添加一个主键约束，并且将其命名为pri_key。

create table employees (
id int auto_increment unique,
name varchar(50) not null,
sex enum('男','女') not null ,
salary int not null,
constraint pri_key primary key (id)
);

在定义完整性约束时，最好在定义的同时就为其添加名字，这样方便后期对其进行修改、删除等操作。

更新完整性约束

删除完整性约束

在ALTER TABLE语句中使用DROP字句可以单独地删除一个完整性约束，语句如下：

DROP [FOREIGN KEY|INDEX|<SYMBOL>]|[PRIMARY KEY]

其中，FOREIGN KEY表示删除外键约束。PRIMARY KEY表示删除主键约束，但是需要注意在删除主键约束时必须再次创建一个主键，否则会删除失败。INDEX表示删除候选键约束，symbol表示要删除的约束名称。

如我们删除employees表中字段id的唯一索引，就可以使用下面的语法：

alter table employees drop index id;

在删除这个索引后我们可以再次查看此表的索引，可以看到唯一索引已被删除。

修改完整性约束

MySQL中不存在直接修改完整性约束的方法，需要先删除对应的完整性约束然后再创建一个同名的完整性约束完成修改。

在下面的实例中我们将候选键约束name改为id，具体是先删除候选键约束name，然后在添加值为id的候选键约束。

其中删除候选键约束和重新添加候选键约束的语句如下：

alter table employees drop index name;
alter table employees add unique index id(id);

运行结果如下图，可见执行这两条语句之后候选键约束从name改为了id。

再若我们要给表tb1添加一个如下的外键约束（无运行结果）。

alter table tb1 add (constraint 'fri_key'  -- 该外键名为fri_key
foreign key (col1,col2)  -- 参照表为tb1 添加外键约束的字段为col1和col2
    references tb2 (col3,col4) -- 被参照表为tb2 被参照字段为col3和col4
   on delete set null  -- 若删除被参照表中的字段值，则在参照表中对应的位置设成NULL
    on update restrict  -- 若更新被参照表中的字段值，若参照表有对应的值则不允许此操作
);

在设置此外键约束前，被参照表tb2必须具有对应字段为col3和col4的多列索引，创建该索引的部分语法如下：add index (col3,col4)。
设置此外键约束后，参照表中若要添加数据，则参照表中同一数据行的字段col1和字段col2必须同时对应被参照表中某一同一数据行的字段col3和col4，如被参照表中有一数据行，该数据行中col3的值为1，col4的值为2，则向参照表中添加数据时，该行数据的字段col1必须为1，col4必须为2，若col1为1col2为3那么就是不同时对应该数据行的每个字段了，若被参照表中其他字段也不满足此规则，则该数据行不能添加。

存储过程和存储函数是指在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和存储函数来执行已经定义好的SQL语句，以避免开发人员重复编写相同的SQL语句，增加开发的效率，减少客户端和服务端的数据传输。

创建存储过程和存储函数

创建存储过程

创建存储过程的语法如下：

CREATE PROCEDURE proc_name([proc_parameter[...]])
[characteristic] routine_body

其中，proc_name代表存储过程的名称；proc_charameter代表存储过程的参数列表；characteristic代表存储过程的特性；routine_body代表SQL代码的内容。proc_parameter参数由三部分组成，分别为输入或输出类型、参数名称和参数类型。其形式为[IN|OUT|INOUT]param_name type。其中IN表示输入参数；OUT表示输出参数；INOUT表示既可作为输入函数又可作为输出函数；param_type表示存储过程参数名称；type参数指存储过程的参数类型，该类型可以为MySQL中的任意数据类型。一个存储过程包含名称、参数列表、还可以包含很多SQL语句集。

注：characteristic部分的语法如下。

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'

参数	说明
sp_name	存储过程或存储函数的名称
characteristic	指定存储函数的特性
CONTRAINS SQL	表示子程序包含SQL语句，但不包含读写数据的语句
NO SQL	表示子程序不包含SQL语句
READS SQL DATA	表示子程序中包含读数据的语句
MODIFIES SQL DATA	表示子程序中包含写数据的语句
SQL SECURITY	指明权限执行。DEFINER表示只有定义者才能执行；INVOKER表示调用者才能执行
COMMENT ‘string’	注释信息

输入参数、输出参数、输入输出参数：
in 输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变，并向外输出，一般在调用时使用全局变量，即在变量名前加一个“@”符号，此时这个变量就存储起来了
inout 输入输出参数，既能输入一个值又能传出来一个值

在MySQL中存储过程体以CREATE PROCECURE开始，后面紧跟存储过程的名称和参数。MySQL中的存储过程名称不区分大小写，且存储过程不能与MySQL中的内置函数同名。存储过程体的语句块以begin开始，以end结束，语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程体内部要以分号结束，所以在定义存储过程体之前都要新定义一个结束符号，并且应降低该字符在语句块中的出现概率。

下面我们先创建一个数据表，该数据表中只包含一个字段用于存储编程语言名称的字段，创建存储过程的语句如下。

delimiter //
create procedure proc_name(in parameter int)
begin
declare variable varchar(50);
if parameter = 1 then
set variable = 'MySQL';
elseif parameter = 2 then
set variable = 'Java';
else set variable = 'Python';
end if;
insert into tb values (variable);
end
//

在此例中，我们将parameter作为输入量，然后通过判断parameter的值来改变变量的值，最后使用INSERT INTO语句实现快速插入数据的操作。

MySQL中默认的语句结束符为分号，存储过程中的SQL语句需要使用分号结束.为了避免冲突，首先用“delimiter //”将MySQL的结束符设置为”//“，再用“delimiter ;”来将结束符设置成分号。

创建存储函数

创建存储函数的语法和创建存储过程的语法类似，语法如下：

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]  routine_body

其中sp_name为存储函数的名称，func_parameter为存储函数的参数列表，RETURNS type指定返回值的类型，characteristic指定存储函数的特性，routine_body代表SQL代码内容。

下面仅演示如何创建存储函数。

delimiter //
create function func(i int)
returns varchar(50)
begin
if i=1 then return 'Java';
elseif i=2 then return 'Python';
else return 'MySQL';
end if;
end;
//

若MySQL提示：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 需要在MySQL的配置文件中找到[mysqld]位置，在该行下面设置log-bin-trust-function-creators=1。

变量的应用

局部变量

局部变量以关键字DECLARE声明，语法如下：

DECLARE variable_name type [DEFAULT value]

其中variable_name表示局部变量名，type表示类型，DEFAULT value可选，表示为局部变量设置默认值。

如：

delimiter //
create procedure p1()
begin
declare i varchar(10) default 'outer';
begin
declare i varchar(10) default 'inner';
select i;
end;
select i;
end;
//

全局变量

全局变量比较于局部变量，只需要在前面加上一个“@”符号。

delimiter //
create procedure p2()
begin
set @a=1;
begin
set @a=2;
select @a;
end;
select @a;
end;
//

为变量赋值

MySQL中可以使用关键字DECLARE来声明变量，可以使用关键字SET为变量赋值，可以同时为多个变量赋值，各个变量的赋值语句用“,”隔开。

另外，MySQL还支持使用下面的语句结构为变量赋值：

SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition;

其中col_name代表查询的字段名称；var_name代表变量的名称；table_name代表表的名称，如从stu表中查询name为‘张三’的记录，并将记录中的tel赋值为局部变量i，那么代码如下：

select tel into i from stu where name = '张三';

光标的应用

在MySQL中查询记录，其结果可能位多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。

光标的使用包括以下步骤：

声明光标

光标只能在存储过程中定义。声明光标使用关键字DECLARE，语法如下。

DECLARE cursor_name CURSOR FOR select_statement;

其中，cursor_name为光标的名称，光标使用和表是同样的规则；select_statement是一个SELECT语句，返回一行或多行数据，该语句也可以在存储过程中定义多个光标，但是必须保证光标的名称不能相同。

打开光标

在声明光标之后，若要使用光标需要先打开光标。打开光标的语法如下。

OPEN cursor_name;

使用光标

顺利打开光标后，可以使用FETCH...INTO语句读取数据，语法如下。

FETCH cursor_name INTO var_name[,var_name]...

关闭光标

使用CLOSE关键字关闭光标，语法如下。

CLOSE cursor_name;

现在举一个实例——更新表中信息：在员工表中将name字段由‘姓名’改成‘id-姓名’：

首先查看表中数据。

然后我们创建如下的存储过程。

delimiter //
create procedure proc()
begin
    declare e_id int;  # 存储字段id
    declare e_name varchar(50);  # 存储字段name
    declare isDone int default 0;  # 存储是否结束的标志
    declare cur_proc cursor for select id as e_id,name as e_id from employees;  # 声明光标
    declare continue handler for not found set isDone = 1;
    # 若光标没有成功查找到数据就将结束表示设为1
    open cur_proc;  # 开启光标
    while not isDone do  # 循环，若结束标志为0则继续循环
        fetch cur_proc into e_id,e_name;  # 使用光标，赋值
        update employees set name = concat(e_id,'-',e_name) where id = e_id; # 连接
        end while;
    close cur_proc; # 关闭光标
end //

然后调用存储过程，最后再查看表的数据。

可以看到表的数据被改动了，说明达到目的了。

调用存储过程和存储函数

调用存储过程

存储过程的调用在前面的示例中已多次使用。使用CALL关键字调用存储过程，然后存储过程会被执行，然后将结果输出给返回值。语法如下。

CALL sp_name([parameter[,...]]);

其中sp_name代表存储过程的名称，parameter是参数，且参数不能省略。

MySQL中，若存储过程的参数是OUT或者INOUT作为输出参数使用时，我们一般在传递参数的时候将全局变量作为参数，如我们定义一个如下的存储过程： “create procedure proc(out i int) begin ... end //” 那么我们在调用的时候这样调用：“call proc(@a) //” 这样调用后就会在MySQL中生成这个全局变量@a，之后我们使用“select @a;” 或者其他的语句就可以使用这个值了。

如我们先定义一个如下的存储过程，然后调用此存储过程。调用存储过程的语句如下：

call count_of_student(@total) //
select @total //

可以看到@total的值为4，说明employees表中存在4条数据行。

调用存储函数

存储函数的调用和MySQL的内置函数基本相同。语法如下：

SELECT function_name([parameter[,...]]);

如我们定义如下的存储函数，然后调用此存储函数。调用存储函数的语法如下：

select search_name(1) //

查看存储过程和存储函数

SHOW STATUS语句

使用SHOW STATUS语句查看存储过程和存储函数的语法如下：

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

如查看db数据库中所有的存储过程：

show procedure status;

查看所有以字母p开头的存储过程名：

show procedure status like 'p%';

SHOW CREATE语句

通过SHOW CREATE语句查看存储过程和存储状态的语法如下：

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

sp_name代表存储过程或者存储函数的名称。

如查询count_of_student的定义：

show create procedure count_of_student //

查询结果显示存储过程的定义、字符集等信息。

SHOW STATUS语句只能查看存储过程或存储函数所操作的数据库对象，如存储过程或存储函数的名称、类型、定义者、修改时间等信息，并不能查询存储过程或存储函数的具体定义。若要查看详细定义，可以使用SHOW CREATE语句。

修改存储过程和存储函数

使用ALTER关键字修改存储过程或存储函数的语法如下：

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'

参数	说明
sp_name	存储过程或存储函数的名称
characteristic	指定存储函数的特性
CONTRAINS SQL	表示子程序包含SQL语句，但不包含读写数据的语句
NO SQL	表示子程序不包含SQL语句
READS SQL DATA	表示子程序中包含读数据的语句
MODIFIES SQL DATA	表示子程序中包含写数据的语句
SQL SECURITY	指明权限执行。DEFINER表示只有定义者才能执行；INVOKER表示调用者才能执行
COMMENT ‘string’	注释信息

删除存储过程和存储函数

使用DROP PROCEDURE来删除存储过程，使用DROP FUNCTION删除存储函数，语法如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;

事务机制

事务修改的概念

事务是一组相互依赖的操作单元的集合，用来保存对数据库的正确修改，保证数据的完整性，如果事务的某个单元操作失败，将取消本次事务的全部操作。如银行交易，A给B转账，需要执行两次操作：A的银行资金减少，B的银行资金增加，若B没有转账成功，则取消A的本次扣除，即事务回滚到扣除之前，即A中没少，B中没多，事务的特征如下：

原子性：每个事务是一个不可分割的整体，只有所有的操作都执行成功，事务才能够提交，否则此次事务失败，所有成功执行的操作单元都需撤销，数据库回到此次事务之前的状态。
一致性：在执行一次事务后，关系数据的完整性和业务逻辑的一致性不能被破坏。假如A和B转账结束后，资金总额是不能改变的。
隔离性：在并发环境中，一个事务所做的修改必须与其他事务所做的修改相隔离。例如一个事务查看的数据必须是其他并发事务不能查修改之前或修改完毕的数据，不能是修改中的数据。
持久性：事务结束后，对数据的修改是永久保存的，即使数据故障导致数据库系统重启，数据依然是修改后的状态。

事务机制的重要性

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库中只有一个账户表tb_account，保存着两张借记卡账户A和B，并且要求这两张借记卡都不能透支。下面通过具体的代码演示一下没有事务机制的话会有什么后果。

创建银行数据库bank，在下面创建数据表tb_account，代码如下。

create database bank;
use bank;
create table tb_account (
id int unsigned not null auto_increment primary key,
    name varchar(50) not null,
    balance float(8,2) unsigned default 0
);

向银行数据表中插入两条记录，具体代码如下。

insert into tb_account (name,balance) values
('A',1000),('B',0);
select * from tb_account;

创建模拟转账的存储过程，代码如下。

delimiter //
create procedure proc_transfer(in name_from varchar(50),in name_to varchar(50),in money int) reads sql data
begin
update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
end
//

调用刚刚创建的存储过程，实现A向B转账七百元，代码如下。
```
call proc_transfer('A','B',700);
select * from tb_account;
```
运行结果
再次尝试转账700元，代码如下。
```
call proc_transfer('A','B',700);
select * from tb_account;
```
运行结果
可以看到，由于A的账户不足700元，所以扣款失败，但是B账户还是收到了700元，这是因为在扣除A账户的700元时发生了异常，我们应该撤销B账户得到的700元，并没有，这并不符合我们的预期，所以事务就可以解决这一问题。在有了事务之后，我们就可以将扣款和转账的语句看成一个整体，若扣款失败，则目标账户的资金不能增加，或者扣除已经增加不该增加的金额。

关闭MySQL自动提交

MySQL默认开启自动提交，也就是说，MySQL会自动地把每一个语句都看成一个事务，在执行完该语句后执行提交操作。

查看MySQL的自动提交是否开启

查看MySQL的自动提交是否关闭有以下几种方法。

使用SHOW VARIABLES语句，语法如下。

SHOW VARIABLES LIKE 'autocommit';

在Value字段值为ON或1时表示自动提交开启，值为OFF或0时表示自动提交结束。

关闭自动提交有以下两种情况：

在当前连接中，可以通过将AUTOCOMMIT变量设置为0，来禁用自动提交功能，代码如下。
```
set autocommit = 0;
show variables like 'autocommit';
```
运行结果
系统变量autocommit是会话变量，即在一个命令行窗口将autocommit设置为0，另一个命令行窗口的autocommit的值还是1。
当autocommit设置为0时，所有的SQL语句都是在一个事务中，直到显式地执行提交或者回滚时该事务才结束，并且同时开启另一个新事务。
当使用START TRANSACTION命令时可以隐式地关闭自动提交功能，该方法不会改变autocommit变量的值。

事务回滚

在上面的转账过程中，我们添加当转账用户的余额不足时撤销目标用户增加金额的操作，即进行事务回滚，步骤如下。

关闭MySQL的自动提交功能。

set autocommit = 0;

接着《事务机制的重要性》的例子，之前的。
```
select * from tb_account;
```
运行结果

接着进行一次转账并查看存款。

call proc_transfer('A','B',700);
select * from tb_account;

可以看到在转账之后，A的账户扣款失败，但是B账户仍然多了700元，现在我们重新打开一个命令行窗口，并查看这两个账户的存款。

可见在新命令行窗口中并没有实际改变这两个变量的值，这是因为关闭了MySQL的自动提交后，若不手动提交，那么UPDATE操作将只会影响内存中的临时记录，并没有真正写入数据库文件。所以在当前命令行窗口执行SELECT语句时，获得的是临时记录，并不是实际数据表中的记录。此时的结果取决于接下来执行的操作，若执行ROLLBACK操作，那么就放弃当前的修改，若执行COMMIT，那么做出的修改会永久存到数据表文件中。

之后我们执行事务回滚操作，事务回滚操作的代码如下。

rollback;
select * from tb_account;

事务提交

当关闭自动提交之后，数据库开发人员可以根据需要提交更新操作，否则更新的结果不会提交到数据库文件中，成为数据库永久的组成部分。

显式提交

关闭自动提交功能之后，可以使用COMMIT命令显式提交更新语句，如上一节最后若把回滚语句换成提交语句COMMIT，就会变成以下结果。

此时该条记录就会永久地保存到数据库文件中，此时若再次打开一个新的命令行窗口并执行查看语句，会得到和上图相同的结果。

隐式提交

关闭自动提交后，若没有手动提交更新操作或者执行回滚操作，则当执行下表的命令时也将执行提交操作。

BEGIN	SET AUTOCOMMIT = 1	LOCK TABLES
START TRANSACTION	CREATE DATABASE/TABLE/INDEX/PROCEDURE	UNLOCK TABLES
TRUNCATE TABLE	ALTER DATABASE/TABLE/INDEX/PROCEDURE
RENAME TABLE	DROP DATABASE/TABLE/INDEX/PROCEDURE

MySQL中的事务

在MySQL中，应用START TRANSACTION命令来标记一个事务的开始，语法如下。

start transaction;

通常START TRANSACTION后面跟着的是组成事务的SQL语句，并且要在所有语句都成功执行后执行提交事务COMMIT语句，下面通过一个具体实例演示事务的使用。

首先看一下A账户和B账户的账户余额，如下图，实现转账过程并调用的代码步骤如下。

创建存储过程，在存储过程中添加一个事务，实现从一个账户向另一个账户转账的过程，代码如下。

delimiter //
create procedure prog_tran_account(in name_from varchar(50),in name_to varchar(50),in money int)
modifies sql data -- 修改数据库内数据
begin
    declare exit handler for sqlexception rollback;  -- 若发生数据库异常则回滚
    start transaction;
    update tb_account set balance = balance + money where name = name_to;
    update tb_account set balance = balance - money where name = name_from;
    commit;
end
//

尝试从A账户向B账户转账1200元，代码如下。可以看到转账失败了，但是A和B账户的资金总额没变，因为进行了事务回滚。
```
call prog_tran_account('A','B',1200);
select * from tb_account;
```
运行结果
这次我们尝试从A账户向B账户转账800元，代码如下。可以看到这次转账成功了。
```
call prog_tran_account('A','B',800);
select * from tb_account;
```
运行结果

回退点

默认情况下若回滚事务，所有执行成功的SQL操作都会撤销.若不想全部撤销，而只需要撤回一部分，就可以通过设置回退点，然后退回到回退点实现，创建回退点、退回该点的语法如下。

SAVEPOINT 回退点名;
ROLLBACK TO SAVEPOINT 回退点名;

如我们在转账之前先创建一个C账户，然后从A向B转账1200元（假设A的余额有1000元），代码如下。从结果可以看到，虽然转账失败了，但是C账户成功创建了。

delimiter //
create procedure prog_savepoint_account(in name_from varchar(50),in name_to varchar(50),in money int) modifies sql data
begin
    declare continue handler for sqlexception
        begin
            rollback to savepoint A;
            commit;
        end;
    start transaction;
    start transaction;
    insert into tb_account (name,balance) values ('C',0);
    savepoint A;
    update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
    commit;
end //
delimiter ;
call prog_savepoint_account('A','B',1200);
select * from tb_account;

数据库管理系统采用锁的机制来管理事务。当多个事务同时修改同一数据时，只允许持有锁的事务修改该数据，其他事务只能“排队等待”，直到前一个事务释放其拥有的锁。下面对MySQL中提供的锁机制进行详细介绍。

锁机制

MySQL锁机制的基本知识

在同一时刻，可能会有多个客户端对中同一行操作，例如，有的客户端在读取该行数据，有的则尝试删除它。为了保证数据的一致性，数据库就要对这种并发操作进行控制，因此产生了锁的概念。

锁的类型

读锁

读锁也称为共享锁。它是共享的，或者说相互不阻塞的。多个客户端在同一时间可以同时读取同一资源，互不干扰。

写锁

写锁也成为排他锁。它是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。这是为了确保在给定的时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源，保证安全。

在实际数据库中，随时都在发生锁定。例如，当某个用户修改某一部分数据时，MySQL就会通过锁定防止其他用户读取同一数据。在大多数时候，MySQL锁的内部管理是透明的。

读锁和写锁的区别如下表。

请求模式	读锁	写锁
读锁	兼容	不兼容
写锁	不兼容	不兼容

锁粒度

一种提高共享资源并发性的方式就是让指定对象更有选择性，也就是尽量只锁定部分数据，而不是所有的资源，这就是锁粒度的概念。它是指锁的作用范围，是为了对数据库中高并发的响应和系统性能两方面进行平衡提出的。

锁粒度越小，并发访问性能越高，越适合做并发更新操作（即采用InnoDB存储引擎的表适合做并发查询操作）；锁粒度越大，并发访问的效率越低，越适合做并发查询操作（即采用MyISAM存储引擎的表适合做并发查询操作）。

由于加锁也要消耗资源，所以需要注意，若系统花费大量的时间来管理锁，而不是存储数据，会得不偿失。

锁策略

锁策略是指在锁的开销和数据的安全性之间寻求平衡，但是这种平衡会影响性能，所以大多数数据库安全系统都没有提供更多的选择，一般都是在表上添加行级锁，并以各种复杂的方式来实现，以便在比较多的情况下提供更好的性能。

在MySQL中，每种存储引擎都可以实现自己的锁策略和颗粒度。因此，它提供了多种锁策略。在存储引擎的设计中，锁管理是非常重要的决定，它将颗粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时会失去对另外一个应用场景的良好支持。MySQL支持多个存储引擎，所以不用单一的通用解决方法。下面介绍两种不同的锁策略。

表级锁：表级锁是MySQL中最基本的锁策略，而且是开销最小的锁策略。它会固定整个表，一个用户在对表进行操作（如插入、删除、更新）前，需要先获得写锁，这会阻塞其他用户对表的所有读写操作。只有没有写锁时，其他读取的用户才能获取该锁，并且读锁之间是不会相互阻塞的。

另外，由于写锁比读锁的优先级高，所以一个写锁请求可能会被插入读锁队列的前面，但是读锁不能插入写锁的前面。

行级锁：行级锁可以最大限度地支持并发处理，同时也带来了最大的锁开销。在InnoDB或者一些其他存储引擎中实现了行级锁。行级锁只在存储引擎层中实现，而在服务器层中没有实现。服务器层中完全不了解存储引擎中的锁实现。

锁的生命周期

锁的生命周期就是在一个MySQL会话内，对数据进行加锁和解锁之间的时间间隔。锁的生命周期越长，并发性能就越低，反之并发性能就越高。另外，锁是数据库管理系统中的重要资源，需要占据一定的服务器内存，锁的周期越长，占用服务器内存的时间就越长；相反，占用的内存时间也就越短，因此应尽可能地缩短锁的生命周期。

MyISAM的表级锁

MySQL的MyISAM存储引擎不支持事务提交和回滚操作，所以当用户对数据库执行插入、删除、更新等操作时，这些变化的操作会立即保存在磁盘中。这在多用户环境会导致诸多问题。为了避免同一时间有多个用户对数据库中指定表进行操作，可以应用表锁定来避免用户在操作数据表时受到干扰。当且仅当用户释放表的操作锁定后，其他用户才可以访问修改后的数据表。

设置表级锁定事务的基本步骤如下：

为指定数据表添加锁定，语法如下。
```
LOCK TABLE table_name lock_type,...;
```
其中，lock_type表示锁定类型，该类型包括以读方式（READ）锁定表和以写方式（WRITE）锁定表。
用户执行数据表，可以添加、删除或者更改部分数据。
用户完成对锁定数据表的操作后，需要对该表进行解锁操作，释放该表的锁定状态，语法如下。
```
UNLOCK TABLES;
```

下面将分别介绍如何以读方式和以写方式锁定数据表。

以只读方式锁定事务表

以读方式锁定数据表，是指设置锁定用户的其他操作方式，如删除、插入、更新等不被允许，直至用户进行解锁操作。下面我们使用只读方式锁定表tb_user，步骤如下。

创建数据表tb_user，存储引擎选择MyISAM，语法如下。

create table tb_user(
    id int(10) not null auto_increment primary key,
    username varchar(30) not null,
    pwd varchar(30) not null
) ENGINE = 'MyISAM';

尝试向表中添加三组数据，语法如下。

insert into tb_user (username, pwd) values 
('first','123456'),('second','222222'),('third','123123');

查看表中数据，语法如下。
```
select * from tb_user;
```
运行结果
以读方式锁定tb_user表，语法如下。
```
lock tables tb_user read;
```
运行结果

尝试查看数据和插入数据，代码如下。

select * from tb_user;
insert into tb_user (username, pwd) values ('fourth','1234321');

可以看到加入读锁后表仍然可以读取，但是不能写入。

解锁tb_user表，代码如下。
```
unlock tables;
```
运行结果

再次尝试查看和插入数据表，代码如下。

select * from tb_user;
insert into tb_user (username, pwd) values ('fourth','1234321');

可以看到在解锁后可以插入数据表。

最后查看数据表的结果，代码如下。
```
select * from tb_user;
```
运行结果

在LOCK TABLES的参数中，用户指定数据表以读方式（READ）锁定数据表的变体为READ LOCAL锁定，其与READ锁定的不同点是：该参数所指定的用户会话可以执行INSERT语句，它是为了方便MySQL dump工具而创建的变体形式。

InnoDB的行级锁

为InnoDB表设置锁比为MyISAM设置锁更为复杂，这是因为InnoDB既支持表级锁又支持行级锁。为InnoDB表设置行级锁也是使用LOCK TABLES命令，其使用方法同MyISAM表基本相同，这里不再赘述。

为InnoDB表设置行级锁有以下三种方式。

在查询语句中设置读锁，语法如下。
```
SELECT 语句 LOCK IN SHARE MODE;
```
如为采用InnoDB存储引擎的数据表tb_account在查询语句中设置读锁，可以使用如下语句：
```
select * from tb_account lock in share mode;
```
在查询语句中设置写锁，语法如下。
```
SELECT 语句 FOR UPDATE;
```
如为采用InnoDB存储引擎的数据表tb_account在查询语句中设置写锁，语法如下：
```
select * from tb_account for update;
```
在更新（包括INSERT、UPDATE和DALETE）语句中，InnoDB存储引擎会自动为更新语句添加隐式写锁。

通过以上三种方式为表设置行级锁的生命周期非常短暂，通常使用事务来延长行级锁的生命周期。

下面以写锁的方式演示如何使用行级锁。

在命令行窗口1输入下面的语句开启事务并开启写锁。
```
start transaction;
select * from tb_account for update;
```
运行结果
在命令行窗口2输入下面的语句开启事务并开启写锁。
```
start transaction;
select * from tb_account for update;
```
运行结果
提交命令行窗口1的事务，释放写锁。
```
commit;
```
运行结果

可以看到提交命令行窗口1的事务后，命令行窗口2的select语句执行了，中间间隔16.39秒。

死锁的概念及避免

死锁就是两个或多个处于不同序列的用户打算同时更新某相同的表时，因互相等待对方释放锁而导致几方一直处于等待状态。实际应用中，若两个处于不同序列的用户想要同时更新某个相同的表时最有可能产生死锁。更具体地讲，当两个事务同时等待对方释放所持有的资源，而导致两个事务都无法操作对方持有的资源，这样无限期的等待被称为死锁。

InnoDB表处理程序会自动检测死锁，若该处理程序发现用户在操作过程中产生死锁，会立即通过撤销方式来撤销其中一个事务，以便使死锁消失。

事务的隔离级别

锁机制有效地解决了事务的并发问题，但也影响了事务的并发性能（并发指数据库系统同时为多个用户提供服务的能力）。当一个事务将其操纵的数据资源锁定时，其他欲操作该资源的的事务必须等待锁定解除，才能继续进行，这就降低了数据库同时相应多个用户的速度，因此合理地选择隔离级别关系到一个软件的性能。下面将对MySQL事务的隔离级别进行详细介绍。

事务的隔离级别与并发问题

数据库系统提供了4种可选的事务隔离级别，它们与并发性能之间的关系如下图。

各种隔离级别的作用如下。

Serializable（串行化）。采用此隔离级别，一个事务在执行过程中首先要将其欲操纵的数据锁定，待事务结束后再释放。若此时另一个事务也要操纵该数据，必须等待前一个事务释放锁定后才能继续进行，两个事务实际上是以串行化方式运行的。
Repeatable Read（可重复读）。采用此隔离级别，一个事务在执行过程中能够看到其他事务已经提交的新插入记录，看不到其他事务对已有记录的修改。
Read Committed（读已提交数据）。采用此隔离级别，一个事务在执行过程中能够看到其他事务未提交的新插入记录，也能看到其他事务已经提交的对已有事务的修改。
Read Uncommitted（读未提交数据）。采用此隔离级别，一个事务在执行过程中能够看到其他事务未提交的新插入数据，也能看到其他事务提交的已插入数据。

综上所述，并非隔离级别越高越好，对于多数应用程序，只需要把隔离级别设置车工Read Committed即可（尽管会出现一些问题）。

设置事务的隔离级别

MySQL中使用SET TRANSACTION ISOLATION LEVEL命令设置事务的隔离级别。新的隔离级别将在下一个事务开始时生效。

设置事务隔离级别的语法格式如下。

SET {GLOBAL|SESSION} TRANSACTION ISOLATION LEVEL 具体级别;

触发器就是由事件来触发某个操作，这些事件包括INSERT、UPDATE和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行对应的操作。满足触发器的触发条件时，数据库系统就会自动执行触发器定义的程序语句，可以令某些操作之间的一致性得到协调。

创建触发器

创建只有一条语句的触发器

创建只有一条语句的触发器的语句如下。

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句

其中，BEFORE表示在触发器时间之前执行触发语句，AFTER表示在触发时间之后执行触发语句；触发事件表示数据库操作触发事件，包括INSERT、UPDATE和DELETE语句；表名表示指定事件触发操作表的名称；FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；执行语句代表触发器被触发后执行的语句。

如创建一条以插入命令INSERT语句触发的触发器auto_save_time，在这之前先创建一个time_log表格。创建触发器的语法如下。

create trigger auto_save_time before insert on employees for each row insert into time_log (savetime) values (now());

可见触发器auto_save_time创建成功。该触发器表示在employees表中执行insert语句后自动在auto_time表中保存插入时间。现在我们插入一个信息。

可见，插入数据后显示了保存时间为23:27:12。

创建具有多条语句的触发器

创建具有多条语句的触发器语法如下。

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW
BEGIN 执行语句列表 END

我们创建另一个自动保存时间的触发器auto_save_time2，在执行INSERT语句后向time_log表中插入数据并显示，并在time_log2表中存放此操作的具体操作（先创建此数据表）（先删除前面创建的触发器，否则会创建两条记录。删除触发器的语法在后面讲）。创建此触发器的语法如下。

delimiter //
create trigger auto_save_time2 before insert on employees for each row 
begin
insert into time_log (savetime) values (now());
insert into time_log2 (savetime,act) values (now(),'delete');
end //

这里不放出结果了。

查看触发器

SHOW TRIGGERS语句

使用SHOW TRIGGERS查看触发器的基本信息。语法如下。

SHOW TRIGGERS;

或者

SHOW TRIGGERS\G

查看TRIGGERS表中触发器信息

MySQL中所有的触发器的信息都存在TRIGGER表中，从此表查看所有触发器信息的语句如下。

SELECT * FROM information_schema.triggers;

其中，information_schema是MySQL中默认存在的库，而information_schema是数据库中用于记录触发器信息的数据表。同通过SELECT语句查看触发器信息，结果与上图相同。但是如果想查看特定的触发器的名称，可以通过WHERE子句应用TRIGGER_NAME字段作为查询条件，代码如下。

select * from information_schema.triggers where trigger_name = 'auto_save_time'\G

使用触发器

触发器的执行顺序

下面通过一个实例演示触发器的执行顺序。

create trigger before_in before insert on employees
    for each row insert into timeinfo (info) values ('before');

create trigger after_in after insert on employees
    for each row insert into timeinfo (info) values ('after');

在执行这两部分代码之后，向employees表中插入一条记录后查看数据表timeninfo的插入情况。

可以看到我们插入数据时先是before触发器先被激活，after触发器后被激活。

触发器中不能包含START TRANSCATION、COMMIT或ROLLBACK等关键字，也不能包含CALL语句。触发器执行非常严密，每一环都息息相关，任何错误都可能导致程序无法向下执行。已经更新的数据表是不能回滚的，故在设计过程中一定要注意触发器的逻辑严密性。

使用触发器维护冗余数据

在数据库中，冗余数据的一致性非常重要。为了避免数据不一致的问题的发生，尽量不要人工维护数据，建议是用编程自动维护。例如通过触发器实现。下面我们使用触发器实现当添加一条商品销售信息时自动修改库存信息表中的库存数量。步骤如下

创建库存表tb_stock，包含字段id（编号）、good_name（商品名称）、number（库存）。

create table tb_stock
(
id        int auto_increment,
good_name varchar(50) not null,
number    int         not null,
constraint tb_stock_pk
    primary key (id)
);

创建商品销售表tb_sell，包括字段id（编号）、good_name（商品名称）、good_type（商品类型）、number（购买数量）、price（价格）、amount（订单总价）。

create table tb_sell
(
 id        int auto_increment,
 good_name varchar(50)    not null,
 good_type int            not null,
 number    int            not null,
 price     decimal(10, 2) not null,
 amount    int            not null,
 constraint tb_sell_pk
     primary key (id)
);

向库存信息表tb_stock中添加一条商品库存信息，代码如下。
```
insert into tb_stock (good_name,number) values ('马克杯 350ML',100);
```
运行结果

为商品销售信息tb_sell创建一个触发器，名称为auto_number，实现向商品销售信息表tb_sell中添加数据时自动更新库存信息表tb_stock的商品销售数量，代码如下。

delimiter //
create trigger auto_number after insert
    on tb_sell for each row
    begin
        declare sellnum int;
        select number from tb_sell where id = new.id into @sellnum;
        update tb_stock set number = number - @sellnum where good_name = '马克杯 350ML';
    end //

向商品销售记录表tb_sell中插入一条商品销售信息，代码如下。

insert into tb_sell (good_name,good_type,number,price,amount) values ('马克杯 350ML',1,1,29.80,29.80);

查看库存信息表tb_stock中商品“马克杯 350ML”的库存数量，代码如下。
```
select * from tb_stock where good_name = '马克杯 350ML';
```
运行结果

从上图可以看到，再插入一条销售记录后，库存变量变成了99，原来是100。

删除触发器

删除触发器的语法如下。

DROP TRIGGER 触发器名;

在使用完触发器后记得删除触发器，否则在执行某些操作时会造成数据的变化。

事件概述

事件调度器，简称事件，可以做为定时任务调度器，取代部分只能使用操作系统才能执行的工作。MySQL支持每秒执行一次事件，在一些对实时性能比较高的环境下还是非常实用的。

事件调度器是定时触发执行的，从这个角度上看也可以称其为“临时触发器”，但是它与触发器有所区别，触发器只针对某一个表产生的事件执行一些语句，而事件调度器则是在某一段（间隔）时间执行一些语句。

查看事件是否开启

事件由一个特殊的线程来管理。启用事件调度器后，拥有SUPER权限的用户就可以通过SHOW PROCESSLIST命令就可以看到这个线程。

我们可以通过下面的代码查看事件是否开启。

show variables like 'event_scheduler';
select @@event_scheduler;
show processlist;

从运行结果中可以看到，事件已经开启。

开启事件

设定全局变量event_scheduler的值就可以动态地控制时间是否开启。开启MySQL事件调度器，可以通过下面两种方式实现。

设置全局参数
在MySQL的命令行中，使用SET GLOBAL命令可以开启或关闭事件。如开启事件，就可以使用下面的代码。
```
set global event_scheduler = OFF;
show variables like 'event_scheduler';
```
运行结果
更改配置文件
在MySQL的配置文件my.ini中，找到[mysqld]，然后在下面添加如下的字符串即可始终开启事件。
event_scheduler = ON

创建事件

在MySQL中，可以通过CREATE EVENT语句来创建事件，语法如下。

CREATE 
[DEFINER = {user|CURRENT_USER}]
EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;

各子句的详细说明如下表。

子句	说明
DEFINER	可选，用于定义事件执行时检查权限的用户
ON SCHEDULE schedule	必选，用于定义执行的时间和时间间隔
ON COMPLETION [NOT] PRESERVE	可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认是依次执行，即NOT PRESERVE
ENABLE\|DISABLE\| DISABLE ON SLAVE	可选，用于指定事件的一种属性。关键字ENABLE表示该事件是活动的，也就是调度器检查事件是否必须调用；关键字DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；关键字DISABLE ON SLAVE表示事件在从机中是被关闭的。如不指定这三个选项中的任何一个，则在一个事件被创建后，它立即变为活动的。
COMMENT 'comment'	可选，用于定义事件的注释
DO event_body	必选，用于指定事件启动后要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的程序。若要包含多个语句，可以用begin ... end复合结构

在ON SCHEDULE子句中，参数schedule的值为一个AS语句，用于指定事件在某个时刻发生，其语法格式如下。

AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[START timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

参数说明如下。

timestamp：表示一个具体的时间点，后面再加上一个时间间隔，表示在这个时间间隔后事件发生。
EVERY子句：用于表示事件在指定区间内每个多长时间发生一次，其中STARTS子句用于指定开始时间，ENDS用于指定结束时间。
interval：表示一个从现在开始的时间，其值由一个数值和一个单位组成。例如，使用4 WEEK表示4周，使用'1:10' HOUR_MINUTE表示1小时10分钟。间隔的距离用DATE_ADD()支配。
interval参数值的参数如下：
```
quantity {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH
|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}
```

如下面我们创建一个每五秒向表中插入一个数据的事件。

create event auto_add on schedule every 5 second on completion preserve 
do insert into tb_time (createtime, name) values (now(), 'root');

下面我们再来创建一个自动在月初保存已注册人数的事件调度器。

use db;
-- 创建表
create table tb_info (
    id int primary key not null auto_increment,
    num_user int not null,
    savetime datetime not null default now()
);  
-- 创建存储过程
delimiter //
create procedure auto_save_num()
begin 
declare num int default 0;
select count(*) into num from tb_user;
insert into tb_info (num_user) values (num);
end
//
delimiter ;
-- 调用存储过程
call auto_save_num();
select * from tb_info;
call auto_save_num();
select * from tb_info;
-- 创建事件
create event auto_save_number on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day ),interval 1 month ),interval 1 hour) on completion preserve do call auto_save_num();

修改事件

修改事件的语法如下。

ALTER
[DEFINER = {user | CURRENT_USER}]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE|DISABLE|DISABLE ON SLAVE]
    [COMMENT 'comment']
[DO event_body];

删除事件

删除事件的语法如下。

DROP EVENT [IF EXISTS] event_name;

安全保护策略概述

要确保MySQL的安全，可以做如下工作。

为操作系统和所安装的软件打安全补丁

如今打开计算机时。都会弹出软件的安全警告。虽然这些警告有时会给我们带来困扰，但是打上安全补丁还是非常有必要的。即使某些恶意用户在攻击方面没有太多经验，他们也能毫无阻碍地攻击未打补丁的服务器。即使用户在使用托管服务器，也不要过分依赖服务提供商来完成必要的升级；相反，要坚持间隔手动式更新，以确保和补丁相关的事情都安排妥当。

禁用所有不使用的系统服务

始终要注意再将服务器放在网络之前，已经消除所有不必要的潜在服务器攻击途径。这些攻击往往是由不安全的系统服务带来的，通常运行在不为系统管理员所知的系统中，简言之，如果不打算使用一个服务，就禁用该服务。

关闭端口

虽然系统不使用的系统服务是减少攻击可能性的好方法，不过还可以通过关闭未使用的端口来添加第二层防护。对于专用的数据库服务器，可以考虑关闭除22（SSH协议专用）、3306（MySQL数据库使用）和一些“工具”专用的端口号在1024以下的端口。简言之，若不希望在指定端口有数据通信，就关闭这个端口。除了在专用防火墙或路由器上做这些调整，还可以使用操作系统的防火墙。

审计服务器的用户账户

当已有的服务器再作为公司的数据库主机时，要确保禁用所有非特权用户，或者最好是全部删除。虽然MySQL用户和操作系统完全无关，但他们都需要访问服务器环境，就有可能有意地破坏数据库服务器及其内容。为确保在审计中不会有遗漏，可以考虑重新格式化所有相关的驱动器，并重新安装操作系统。

设置MySQL的用户密码

对所有MySQL用户设置密码，客户端程序不需要知道运行它的人员的身份。对于服务器应用程序，用户可以指定客户端程序的用户名。这样可以避免其他用户冒充他人进行数据库连接。

用户权限管理

MySQL中的表和其他任何关系表没有区别，都可以通过典型的SQL语句修改其结构和数据。使用GRANT和REVOKE命令可以创建和删除用户，也可以在线授予和撤回用户访问权限。由于语法严谨，消除了由不好的SQL语句（如忘记在UPDATE语句中添加WHERE子句）带来潜在的错误。

## 使用CREATE USER命令添加用户

CREATE USER用于创建新用户。要使用CREATE USER语句必须具有MySQL数据库全局的CREATE USER权限，或拥有INSERT权限。对于每个账户，CREATE USER会在没有权限的mysql.user表中创建一个新纪录。若账户已经存在则会发生错误。使用自选的IDENTIFIED BY子句，可以为账户设置一个密码。user值的密码和GRANT语句一样，其命令的语法如下。

CREATE USER user_name [IDENTIFIED BY [[PASSWORD] 'password']]
[,user_name [IDENTIFIED BY [[PASSWORD] 'password']]] ...

如我们创建一个新用户，用户名叫user1，密码为123456。

create user user1 identified by '123456';

常用的几种创建特定要求的用户的语句：
若要创建一个新用户admin，只允许该用户从localhost主机并使用密码123456连接到MySQL数据库，则CREATE USER语句如下。
create user admin@localhost identified by '123456';
若用户admin还可以从IP为192.168.1.100的主机连接到MySQL服务器，则CREATE USER语句如下。
create user admin@192.168.1.100 identified by '123456';
若允许用户从任何主机连接，则使用百分号通配符。
create user admin@'%' identified by '123456';

使用DROP USER命令删除用户

若存在一个或多个闲置用户，应当考虑将其删除，确保其不会被用于可能的违法活动。使用DROP USER就可以删除用户。该命令会从权限表中删除用户的所有信息。DROP USER命令语法如下。

DROP USER user1[,user2];

DROP USER不能自动关闭任何已经打开的用户对话框。而且若用户有被打开的对话，此时删除用户，则命令不会立即生效，直至用户对话被关闭后删除用户才生效。一旦用户对话被关闭，用户被删除，此用户再次登录时就会失败，因为此用户已经不存在了。

如我们使用DROP USER命令删除上面创建的user1用户，语法如下。

drop user user1;

使用RENAME USER命令重命名用户

语法如下。

RENAME USER old_user TO new_user [,old_user TO new_user]...;

使用GRANT和REVOKE命令管理访问权限

GEANT和REVOKE命令用来管理权限，也可以用来创建和删除用户。但是使用CREATE USER和DROP USER更容易实现。

查看用户权限

使用SHOW GRANTS语句可以查看用户的权限，语法如下。

SHOW GRANTS FOR 用户名@主机名;

如我们创建一个新用户叫demo，然后查看此用户的权限。

show grants for demo@localhost;

上面结果中的*\*表示用户只能登录到数据库服务器，没有其他权限。

设置用户权限

只有拥有GRANT权限的用户才可以执行GRANT语句，其语法如下。

GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option]...];

参数说明如下表。

参数	含义
`priv_type`	权限类型
`column_list`	表示权限作用于哪些列上，省略该参数时，表示作用于整个表
`database.table`	用于指定权限的级别
`user`	代表用户账户，由用户名和主机名构成，如`user@localhost`
`IDENTIFIED BY`	为用户设置密码
`password`	是用户的新密码
`WITH`	该参数后面跟着一个或多个with_option参数，该参数有五个选项，详细介绍如下表

可选项	含义
`GRANT_OPTION`	被授权的用户可以将这些权限授予其他用户
`MAX_QUERIES_PER_HOUR count`	设置每小时可以执行count次查询
`MAX_UPDATES_PER_HOUR count`	设置每小时可以执行count次更新
`MAX_CONNECTIONS_PER_HOUR count`	设置每小时可以建立count个连接
`MAX_USER_CONNECTIONS count`	设置单个用户可以同时具有count个连接

MySQL中可以授予的权限有以下几组。

列权限：和表的一个具体列相关。如可以使用UPDATE语句更新students表的name字段的值的权限。
表权限：和一个具体表的所有字段数据相关。如可以使用SELECT语句查询students表的所有数据的权限。
数据库权限：和一个具体的数据库的所有数据表相关。如可以在已有的数据库mytest中创建新表的权限。
用户权限：和MySQL中所有数据相关。如可以删除一个数据库的权限。

对应地，在GRANT语句中可用于指定权限级别的值有以下几类格式。

*：表示当前数据库的所有表。
*.*：表示所有数据库的所有表。
db.*：表示db数据库中的所有表。
db.tb表示db数据库中的tb表。
db.routine：表示db数据库中的某个存储过程或存储函数。

TO子句表示将权限授予一个用户，若此用户不存在则会创建此用户，但是必须为此用户设置密码。

权限列表如下。

权限	意义
`ALL [PRIVILEGES]`	设置除GRANT OPTION外的所有简单权限
`ALTER`	允许使用ALTER TABLE
`ALTER ROUTINE`	更改或取消已有的子程序
`CREATE`	允许使用CREATE TABLE
`CREATE ROUTINE`	创建已存储的子程序
`CREATE TEMPORARY TABLES`	允许使用CREATE TEMPORARY TABLE
`CREATE USER`	允许使用CREATE USER、DROP USER、RENAME USER 和REVOKE ALL PRIVILEGES
`CREATE VIEW`	允许使用CREATE VIEW
`DELETE`	允许使用DELETE
`DROP`	允许使用DROP TABLE
`EXECUTE`	允许用户运行已存储的子程序
`FILE`	允许使用LOAD DATA INFILE和SELECT INTO OUTFILE
`INDEX`	允许使用CREATE INDEX和DROP INDEX
`INSERT`	允许使用INSERT
`LOCK TABLES`	允许对拥有SELECT权限的表使用LOCK TABLES
`PROCESS`	允许使用SHOW FULL PROCESSLIST
`REFERENCES`	未被实施
`RELOAD`	允许使用FLUSH
`REPLICATION CLIENT`	允许用户询问从属服务器或主服务器的地址
`REPLICATION SLAVE`	用于复制型从属服务器（从主服务器中读取二进制日志事件）
`SELECT`	允许使用SELECT
`SHOW DATABASES`	显示所有数据库
`SHOW VIEWS`	允许使用SHOW CREATE VIEW
`SHUTDOWN`	允许使用mysqladmin shutdouwn
`SUPER`	允许使用CHANGE MASTER、KILL、PURGE MASTER LOGS和SET GLOBAL语句，myadmin debug命令；允许连接（一次），即使已达到max_connections
`UPDATE`	允许使用UPDATE
`USAGE`	“无权限”的同义词
`GRANT OPTION`	允许授予权限

下面会创建一个管理员，来演示如何使用GRANT命令。

以root身份登录，使用CREATE USER命令创建一个管理员user_demo，设置密码为1234，语法如下。

create user demo_user identified by '1234';

使用root用户为用户demo赋予在db数据库下执行INSERT、SELECT、UPDATE和DELETE权限，然后查看权限是否赋予成功，代码如下。

grant insert,select,update,delete on db.* to demo_user;
show grants for demo_user;

新建一个cmd窗口，使用demo用户登录MySQL，执行CREATE TABLE命令创建user数据表，代码如下。

create table user(
id int primary key auto_increment,
    name varchar(50)
);

在上图中，数据库提示“CREATE command denied to user 'demo_user'@'locallhost' for table 'user'”，这是因为demo_user不具备创建数据表的权限。

使用root用户赋予demo_user用户在db数据库下执行CREATE的权限，并查看demo_user的权限，代码如下。

grant create on db.* to demo_user;
show grants for demo_user;

新建一个cmd窗口，用demo_user登录，并再次尝试创建表user。

create table user(
id int primary key auto_increment,
    name varchar(50)
);

可以看到创建表成功了，因为root管理员已授予demo_user创建表的权利。

MySQL数据库安全常见问题

权限更改何时生效

MySQL服务器启动及使用GRANT和REVOKE语句时，服务器会自动读取GRANT表。但是，既然知道这些权限保存的位置以及它们是如何保存的，就可以手动修改它们。当手动更新权限时，MySQL服务器不会注意到权限已修改，必须向服务器指出已经对权限进行了修改，有3种方法可以实现这个任务。

可以在MySQL命令提示符下（必须已管理员身份运行）输入以下命令。

flush privileges;

这是更新权限最常用的方法。或者也可以在操作系统中运行。

mysqladmin flush-privileges

或者是

mysqladmin reload

设置账户密码

可以用mysqladmin命令在DOS命令窗口中指定密码。

mysqladmin -u user_name -p "oldpwd" -h host_name password "newpwd"

mysqladmin命令重设服务器为host_name、用户名为user_name的用户的密码、oldpwd为旧密码，newpwd为新密码。

通过SET PASSWORD命令设置用户的密码。

SET PASSWORD FOR 'user_name'@'host_name' = 'newpwd';

只有以root用户（可以更新MySQL数据库的用户）的身份登录，才可以修改其他用户的密码。若没有以匿名用户连接，省略for子句可以修改自己的密码。

SET PASSWORD = '123456';

在全局级别下使用1GRANT USAGE语句（在*.*）指定某个账户的密码，而不影响账户当前的权限。

GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY 'newpwd;'

使密码更安全

在管理级别，切记不能将mysql.user表的访问权限授予任何非管理账户。
采用下面的命令登录MySQL，以隐藏密码。

mysql -u user -p
Enter password:******

若要在非交互方式下运行一个管理脚本调用一个客户端，就没有从终端输入密码的机会。其最安全的方法是让客户端程序提示输入密码或在适当的选项文件中指定密码。

日志文件

MySQL提供了日志文件来帮助开发人员更好地定位MySQL的错误信息和检查运行性能。

错误日志

错误日志对MySQL的启动、运行、关闭过程都有记录。开发人员在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录了一些警告信息或正确的信息。可以通过以下的命令查看错误日志路径。

SHOW VARIABLES LIKE '%log_error%';

重启MySQL，就会在错误日志中找到MySQL重启的信息。

慢查询日志

MySQL提供了记录慢查询的文件，该文件会记录查询时长超过long_query_time的SQL语句，默认是关闭的。若SQL查询的时间操作了设置的阈值，就会被记录到慢查询日志中。

可以通过以下语句查看慢查询日志是否开启，以及日志文件位置。

SHOW VARIABLES LIKE '%slow_query_log%';

慢查询日志默认是关闭的，可通过下面的语句开启。

SET GLOBAL slow_query_log = ON;

若在设置时提示ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation，则需要以管理员身份运行cmd，然后在此cmd中登录MySQL，再尝试设置即可。

MySQL中慢查询的时长阈值是靠变量long_query_time控制的，单位是秒，默认是10，可以通过下面的语句查看。

SHOW VARIABLES LIKE '%long_query_time%';

可通过下面的语句修改为1秒。

SET GLOBAL long_query_time = 1;

查询日志

查询日志会记录发送给MySQL服务器的所有SQL，因为SQL量大，默认是不开启的。若一个问题反复出现（如事务不结束），就需要把查询日志打开，即使事务没有提交，一样会写到查询日志，这样就可以定位出现问题的SQL语句。

MySQL有三个参数可以设置general log。

general_log：用于开启general log。ON表示开启，OFF表示关闭。
log_output：日志输出的模式。FILE表示输出的文件，TABLE表示输出到MySQL库的general_log表，NONE表示不记录。
general_log_file：日记输出文件的路径，设置log_output为FILE时才会输出到此文件。

可以使用如下命令查看general_log是否开启。

show variables like '%general%';

若general_log是关闭的，可以通过以下的命令开启。

set global general_log = ON;

大多数情况都是临时开启general log，需要记得关闭，并把日志文件输出模式恢复为FILE，命令如下。

set global general_log = OFF;
set global log_output = 'FILE';

二进制日志

二进制日志包含描述数据库更改的事件，如表创建操作或表数据更改。除非使用基于行的日志记录，否则它还可能包含已进行更改的语句的事件（如，不匹配任何行的DELETE）。另外，二进制日志还包含关于每个语句花费多长时间更新数据的信息。二进制日志有以下几个用途。

恢复：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
复制：其原理和恢复类似，通过复制和执行二进制日志，使一台远程的MySQL数据库（一般称为slave或standby）与一台数据库（一般称为master或primary）进行实时同步。
审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

使用以下命令查看二进制日志是否开启。

show variables like 'log_bin';

默认情况下二进制日志是开启的。

使用以下命令查看二进制日志文件名。

show binary logs;

运行结果如上图。以后每次操作相关的表时，File_size都会增大。

此外，还可以使用如下命令对日志进行回放。

SHOW BINLOG EVENTS [IN 'log_name'];

为了保证数据的安全，需要定时对数据进行备份。备份的方式有很多种，效果也不一样。若数据库中的数据发生了错误，需要使用备份好的数据进行数据还原，以将损失降到最低，过程中还可能涉及到数据库信息的导入和导出。本章将介绍数据备份和还原的方法。

数据备份

备份数据是数据库管理最常用的操作。为了保证数据库中数据的安全，数据管理员还需定期进行数据备份，一旦数据库遭到损坏，即通过备份的文件来还原数据库。可能遭到数据破坏的原因有以下几个：存储介质故障、服务器彻底瘫痪、用户的误操作以及黑客的入侵。因此数据备份是很重要的操作。

使用mysqldump命令备份

使用mysqldump命令可以将数据库的数据备份成一个文本文件。表的结构和表的数据将存储在文本文件中。mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一条CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。在恢复时，CREATE语句和INSERT语句就可以用来重新生成表。

在使用mysqldump命令进行数据备份时，经常分为以下三种格式：备份一个数据库、备份多个数据库、备份所有数据库。下面将分别介绍这三种备份格式。

备份一个数据库

使用mysqldump备份一个数据库的语法如下。

mysqldump -u username -p dbname table1,table2,... > BackupName.sql

其中，username指用户名；dbname指数据库的名称；tableX表示表的名称，没有该参数时将备份整个数据库；BackupName.sql代表目标备份文件的名称，文件名前还可加上一个绝对路径。

目标备份文件的后缀并非都是.sql，只是.sql给人的第一感觉就是它和数据库有关，但如将它命名为.txt的文件也可以。

如我们创建一个如下的数据库和数据表并将它备份至A盘中。

create database db_stu;
use db_stu;
create table tb_stu(
	id int(11) auto_increment,
    name varchar(50) not null comment '姓名',
  	enroll_year year not null comment '入学年份',
    stu_id varchar(4) not null comment '学号',
    birthday date not null comment '生日',
    primary key id (id),
    index id (id),
    unique u_stu_id (stu_id),
    check (stu_id regexp '[0123456789]{4}')
);
insert into tb_stu (name, enroll_year, stu_id, birthday) values
    ('张三', 2020, '1001', '2002-01-01'),
    ('李四', 2020, '1002', '2002-02-01'),
    ('王五', 2022, '2001', '2002-10-01'),
    ('王六', 2022, '2002', '2003-01-01');
select * from tb_stu;

执行完以上语句后，使用mysqldump将此表备份到A盘，语法如下。

mysqldump -u root -p db_stu tb_stu > A:\stu.sql

在退出MySQL命令行之后，回到系统命令行，然后输入上面的命令，再输入密码，就可以在A盘根目录看到此文件了。我们使用Datagrip打开此文件，便可查看此文件的内容。此文件的主要内容及意义如下图。

文件开头记录的就是MySQL的版本号、备份的主机名、数据库名；以/*!40101开头的内容是只有MySQL版本大于4.1.1才执行的语句，下面的/*!40103、/*!40104也是这样。

该文件中没有创建数据库的语句，因此以上语句必须还原到已经存在的数据库中。

备份多个数据库

使用mysqldump命令备份多个数据库的语法如下。

mysqldump -u root -p --databases database1,database2,... > BackupName.sql

这里要加上子参数--databases，代表选择多个数据库。

备份所有数据库

使用mysqldump命令备份所有数据库的语法如下。

mysqldump -u root -p254456 --all-databases > A:/BackupName.sql

直接复制整个数据库目录

MySQL有一种最简单的备份方法，就是将MySQL的数据库文件直接复制出来，使用此方法前最好先将服务器停止，因为在复制的过程中若服务器改变了数据，再次恢复时会造成数据不一致的情况。即使这样，一般情况下也不建议这样做，因为可能涉及到版本等诸多问题。

采用此方法复制目录前可通过以下语句查看数据库文件的保存位置。

SHOW VARIABLES LIKE '%datadir%';

数据恢复

使用mysql命令还原

此方法是将上一节中使用mysqldump命令保存的文本文件作为数据源导入到数据库中，语法如下。

mysql -u root -p [dbname] < backup.sql

其中``dbname`参数表示数据库名称。该参数是可选参数，选择此参数时，表示还原此数据库下的表，不选此参数则代表还原所有数据库。

如我们还原上面保存的tb_stu表。

先看一下还原之前的数据库。

我们需要先创建数据库db_stu，然后退出MySQL命令行，然后我们通过下面的命令还原数据表。

mysql -u root -p < A:\stu.sql

没有报错后我们重新登录数据库，并且查看数据。

可以看到之前的表结构以及表数据都已经还原回来了。

直接复制到数据库目录

在上面介绍了一种直接复制数据库目录的方法，通过这种方式备份的数据，在还原时可以直接复制到MySQL的数据库目录下，但必须保证两个数据库的版本号是相同的，而且此方法对MyISAM类型的表比较有效，对InnoDB类型的表则不可用，因为InnoDB表的表空间不可直接复制。

数据库迁移

实际应用中可能遇到数据库升级、开发了新的管理系统、换用其他的数据库等情况。数据库迁移指将数据库中的数据从一个数据库转移到另一个数据库。数据库迁移可分为相同版本的数据库迁移和不同数据库的数据库迁移。因为只有MyISAM的表支持直接复制整个数据库目录，而InnoDB表不支持，因此最安全最有效的方法就是使用上文介绍过的mysqldump命令复制整个数据库。语法如下。

mysql -h name1 -u root -password=password1 --all-databases |
mysql -h host2 -u root -password=password2

其中|表示管道，其作用是将mysqldump备份的文件送给mysql命令；-password=password1是name1主机上root用户的密码，后面的也是同理。通过这种方式可以实现迁移。

但要是想在不同的数据库之间进行迁移，就需要根据双方数据库的类型指定方法。如从MySQL迁移到Oracle数据库，需要使用mysqldump命令先导出SQL文件，然后修改文件中的CREATE语句；若要从MySQL迁移到SQL Server，则可以使用MySQL提供的ODBC驱动。

表的导出与导入

MySQL数据库的表可以导出成文本文件、XML文件或HTML文件，相应的文本文件也可以导入到MySQL中。在表的维护中，需要经常执行数据库文件的导入和导出。下面将分别进行介绍。

用SELECT ... INTO OUTFILE语句导出文本文件

语法如下。

SELECT [col_name[,col_name,...]] FROM table [where clause] INTO OUTFILE 'target_file' [OPTION];

该语句分为两个部分，前半部分是一个简单的SELECT语句，通过该语句查询所需要的数据，后半段的作用是导出数据，其中，'target_file'参数指出将查询的数据导入到哪个文件；OPTION参数可选的几个选项如下：

选项	含义
`FIELDS TERMINATED BY 'str'`	设置字符串的分隔符，默认值是`'\t'`，其中`str`表示字符串
`FIELDS ENCLOSED BY 'str'`	设置字符来括上字段的值，默认情况下不使用任何符号
`FIELDS OPTIONALLY ENCLOSED BY 'str'`	设置字符来括上CHAR、VARCAHR、TEXT等字符型字段，默认情况下不使用任何符号
`FIELDS ESCAPED BY 'str'`	设置转义字符，默认情况下是`\`
`LINES STARTING BY 'str'`	设置每行开头的字符，默认情况下无任何字符
`LINES TERMINATED BY 'str'`	设置每行的结束符，默认是`\n`

使用此语句时，指定的目标路径只能是MySQL的secure_file_priv参数所指定的路径，获取此路径的语法如下。

SELECT @@secure_file_priv;

secure_file_priv参数用于限制LOAD DATA，SELECT ... OUTFILE，LOAD_FILE()传到哪个指定目录。

secure_file_priv为NULL时，表示限制mysqld不允许导入或导出。
secure_file_priv参数为/tmp时，表示限制mysqld只能在/tmp目录下执行导入或导出，其他目录不能执行。
secure_file_priv没有值时，表示不限制mysqld在任意目录的导入或导出。

从上图可看到，该值为NULL，表示限制mysqld导入或导出。此时可以在my.ini文件中添加secure_file_priv=''一行即可。设置完成后重启MySQL并重新登录，再次查看该值，如下图，即可导出数据。

例：

select * from db_stu.tb_stu into outfile 'A:\stu1.txt'
fields terminated by ',' optionally enclosed by '\"'
lines starting by '\>' terminated by '\r\n';

其中\"、\>都为转义字符，\r\n是Windows系统的分隔符。

执行成功后，打开A盘下的stu1.txt文件即可查看保存的文本文件。

使用mysqldump命令导出文本文件

语法如下。

mysqldump -u root -p -T 目标目录 dbname table [option];

其中“目标目录”参数指出文本文件的路径；dbname参数表示数据库的名称；table参数表示表的名称；option表示附件选项，具体如下：

--fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是``\t`。
--fields-enclosed-by=字符串：设置字符来括上字段的值。
--fields-optionally-enclosed-by=字符：设置字符括上CHAR、VARCHAR和TEXT等字符型字段。
--fields-escaped-by=字符：设置转义字符。
--lines-terminated-by=字符：设置每行结束符

有些时候这些字符必须用双引号括起来，否则MySQL数据库系统将不能识别这几个参数。

mysqldump命令还可以导出为XML文件，语法如下。

mysqldump -u root -p --xml | -X dbname table > E:\name.xml

使用mysql命令导出文本文件

mysql命令可以用来登录MySQL服务器和还原备份文件，除此之外还可以用来导出为文本文件。语法如下。

mysql -u root -p -e "select 语句" db_name > name.txt;

其中，-e选项代表执行SQL语句，"select 语句"代表查询语句。

如我们将tb_stu表存到A盘中，语法如下。

mysql -u root -p -e "select * from tb_stu" db_stu > A:/stu3.txt

可以看到，我们成功保存了。

mysql命令还可以导出XML文件或HTML文件，语法如下。

mysql -u root -p --xml | -X -e "select 语句" dbname > name.xml
mysql -u root -p --html | -H -e "select 语句" dbname > name.html

如我们将tb_stu表导出为html文件，语法如下。

mysql -u root -p --html -e "select * from tb_stu" db_stu > A:\stu.html

从运行结果可以看到，导出的数据变成了一张数据表。

用LOAD DATA INFILE命令将文本文件导入数据表

语法如下。

LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE file_name INTO TABLE table_name [OPTION];

参数说明如下表。

参数	含义
LOW_PRIORITY	若指定此参数，则LOAD DATA语句会被延迟，直到没有其他的客户端正在读取表
CONCURRENT	若指定此参数，则当LOAD DATA正在执行时，其他线程可以同时使用此表的数据
LOCAL	若指定此参数，则文件会被主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定其确切的位置。若给定的是一个相对路径名称，则此名称会被理解成相对于启动客户端时所在的目录。若没有指定此参数，则文件必须位于服务器主机上，并且会被服务器直接读取。使用此参数时速度会略慢。
file_name	用来指定导入的文本文件得路径和名称。这个文件可以手动创建，也可以使用其他的程序创建。可以使用绝对路径，也可以不指定路径，直接指定文件名，这时服务器会在默认数据库路径中查找并读取
table_name	用来指定需要导入数据的表名，该表在数据库中必须存在，表结构必须与导入文件的数据一致
OPTION	用于设置相应的选项，其值可以是下面9个值中的任意一个

值	含义
`FIELDS TERMINATED BY '字符串'`	用于指定字段分隔符为此字符串，默认是`\t`
`FILED ENCLOSED BY '字符串'`	用于设置字符串括上每个字段的值，默认不设置任何字符
`FIELDS OPTIONALLY ENCLOSED` `BY '字符串'`	用于设置括上CHAR、VARCHAR和TEXT类型的字段值的符号，默认不使用任何符号
`FILEDS ESCAPED BY '字符串'`	用来设置转义字符的字符符号，默认是`\`
`LINES STARTING BY 字符串`	用来设置每行开头的字符串，默认不设置任何字符
`LINES TERMINATED BY '字符串'`	设置每行结束的字符串，默认使用`\n`
`IGNORE n LINES`	忽略文件的前n条记录
`(字段列表)`	用于实现根据字段列表中的字符和顺序加载记录
`SET column=expr`	用于设置列的转换方式，即所指定的列要经过转换才会被加载

在使用该命令时，必须根据要导入文本文件中字段值的分隔符来指定使用的分隔符；并且如果文本文件中的字段顺序和表的字段顺序不一致时，就需要使用(字段列表)参数来指定加载字段的顺序。

如我们使用LOAD DATA ... INFILE命令导入如下的学生表。

load data infile 'A:/tb_stu.txt' into table new_tb_stu
	character set 'utf8' -- 若出现乱码，可将读取方式改为utf8
	fields terminated by ',' enclosed by '\"'
	lines starting by '\>' terminated by '\r\n';

用mysqlimport命令导入文本文件

在MySQL中，若只是恢复数据，则可以命令行窗口中使用mysqlimport实现。语法如下。

mysqlimport -u root -p database filename [option];

OPTION参数的几个可用值如下表。

值	含义
`--fields-terminated-by='str'`	以字符串str作为字段的分隔符，默认为`\t`
`--fields-enclosed-by='str'`	用字符串str括上每个字段的值，默认不使用任何字符
`--fields-optionally-enclosed-by='str'`	用字符串str括上CHAR、VARCHAR和TEXT类型字段的值，默认不使用任何字符
`--fields-escaped-by='str'`	设置字符串str为转义字符
`--lines-terminated-by='str'`	设置每行的结束符为str
`--ingore-lines=n`	忽略前n行的数据

在MySQL中，表名和字符串可以使用反引号括起来，但是这不是必须的，但若表名或字符串是MySQL中的某个关键字，那么就必须使用反引号将其括起来。

性能优化是指通过某些有效的方法提升MySQL数据库的性能。性能优化的目的是使MySQL数据运行速度更快、占用的磁盘空间更小。性能优化有很多方面，如优化查询速度、优化更新速度和优化MySQL服务器等。当表中的记录很少时，MySQL的性能差别很小，只有在大量用户使用、记录很大、运行时间很长时，MySQL的性能差别才开始体现出来。若用户在查询或者添加数据时因为MySQL的性能很差而导致响应速度变慢，用户很难喜欢这个网站。因此为了提高MySQL的性能，就要对MySQL进行性能优化。如若大量用户想要进行查询操作，就要对查询语句进行优化；若大量用户想要进行更新操作，就要对更新语句进行优化。下面介绍各种情况如何进行优化。

优化概述

分析MySQL数据库的性能

数据库管理员可以使用SHOW STATUS语句查询MySQL数据库的性能，语法如下。

SHOW STATUS LIKE 'value 参数';

其中，value参数是常用的几个统计参数，具体介绍如下。

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Show_queries：慢查询次数。
Com_select：查询操作的次数。
Com_insert：插入操作的次数。
Com_delete：删除操作的次数。

MySQL中存在查询InnoDB类型的表的一些参数。例如，Innodb_rows_read参数表示SELECT语句查询的记录数；Innodb_rows_inserted参数表示INSERT语句的插入数；Innodb_rows_updated参数表示UPDATE语句更新的记录数；Innodb_rows_deleted参数表示DELETE语句删除的记录数。

如我们查询MySQL服务器的连接数。

show status like 'Connections';

运行结果如上图，通过这些结果，我们可以知晓什么语句使用得多，如使用SELECT语句的次数大于INSERT语句的次数，我们就可以通过多使用索引来提升查询的速度，而因为插入表的次数少，也不会有太大影响。

使用profile工具分析语句消耗性能

在MySQL命令窗口中，使用SELECT语句查询到记录后，最后一行会显示查询的时间，精度为秒，小数点后两位，若要知晓精度更高的查询速度，可以通过开启profile工具实现。MySQL8.0安装后profile工具是默认不开启的。MySQL是通过profilig参数标记profile工具是否开启的，查看方法如下。

SHOW VARIABLES LIKE '%pro%';

运行结果如下图，可以看到，现在的profile工具处于关闭状态。

开启profile工具的语法如下。

SET profiling=1;

运行结果如下。

开启profile工具后，我们就可以通过先执行SELECT语句，再使用profile工具查看执行此语句消耗的时间。语法如下。

select * from db_stu.tb_stu;
show profiles;

红色框内就是此语句执行的时间，字段Duration表示本次查询耗费的时间，单位为秒。

优化查询

分析查询语句

可使用关键字EXPLAIN分析查询语句，语法如下。

EXPLAIN SELECT 语句;

其中SELECT 语句就是我们平时使用的查询语句，例如下面的例子。

explain select * from db_stu.tb_stu;

其中主要的字段代表意义如下。

id：指出在整个查询中SELECT的位置。
table：查找的表名。
type：连接类型，该列中存储许多值，范围从const到ALL。
possible_keys：指出为了提高查找速度，可以使用的索引。
keys：指出实际使用的键。
rows：指出MySQL需要在相应表中返回查询结果所检验的行数，为了得到总行数，MySQL必须扫描处理整个查询，再乘以每个表的行值。
Extra：包含一些其他信息，如MySQL如何设计查询。

MySQL也支持使用关键字DESCRIBE分析查询，语法就是把EXPLAIN换成DECRIBE，且DESCRIBE可简写成DESC。

索引对查询速度的影响

下面我们通过使用索引和不使用索引，分别分析两次查询然后对比来观察查询的优化情况。

首先我们先查看一下数据表的定义语句以及数据。

可以看到表中字段price加了索引，其他列无索引。下面开始对比。

首先分析不使用索引的查询情况，我们查询bookname='MySQL从入门到精通'的记录，语法如下。

explain select * from tb_bookinfo where bookname = 'MySQL从入门到精通';

可以看到表中总共查询了6行记录，接下来我们为字段bookname添加索引，并且再查询一次，语法如下。

create index index_bookname on tb_bookinfo;
explain select * from tb_bookinfo where bookname = 'MySQL从入门到精通';

可以看到此时只查询了两行，比不使用索引少查询了4 行，所以在查询操作时使用索引不仅可以提高查询效率，还可以降低服务器开销。

使用索引查询

虽然索引可以提高查询的速度，但并不能充分发挥其作用，因此可以通过关键字或者其他方式优化查询处理。

应用关键字like优化索引查询

应用EXPLAIN语句执行以下查询。

explain select * from tb_bookinfo where bookname like '%Java Web';

可以看到查询的行数仍为6条，这是因为若匹配字符串第一个字符就是百分号“%”，则索引不会被使用，若“%”不在匹配字符串的第一位，则索引会被正常使用。

如我们再次使用查询，语法如下。

explain select * from tb_bookinfo where bookname like 'Java Web%';

查询语句中使用多列索引

多列索引是在表的多个字段上创建一个索引。只有在查询条件中使用了这些字段中的一个字段时，索引才会被正常使用。

应用多列索引在表的多个字段中创建一个索引，其命令如下。

CREATE INDEX index_name ON table_name (col_name1,[col_name2...]);

如拿此语句：create index index_name on table_name (c1,c2);来说，在应用字段c2时，索引不能正常使用。这就意味着索引并未在MySQL优化中起到任何作用，故必须使用第一字段c1，索引才可以被正常使用。具体可看下图。
运行结果

查询语句中使用关键字OR

在MySQL中，查询语句包含OR关键字时，要求查询的两个字段必须同为索引，若所搜索的字段有一个不为索引，则在查询中不会应用索引进行查询。下面我们通过一个实例来对比使用索引和不使用索引的性能差异。

首先查看tb_bookinfo表的结构。我们发现字段price有索引，我们把此索引删除。
运行结果
然后我们使用EXPLAIN语句分析使用OR关键字查询，语法如下。
```
explain select * from tb_bookinfo 
where bookname = 'MySQL从入门到精通' or price = 89;
```
运行结果
可以看到它扫描了整个表（6行），并且一个索引也没有使用。

我们重新添加索引，然后再次查找，语法如下。

create index index_price on tb_bookinfo (price);
explain select * from tb_bookinfo 
where bookname = 'MySQL从入门到精通' or price = 89\G

可以看到它成功使用了两个索引，且共扫描了4行。

优化数据库结构

数据库结构是否合理，需要考虑是否存在冗余，对表的查询和更新速度如何、表中字段的数据类型是否合理等多方面的内容。本节将介绍优化数据库结构的方法。

将字段很多的表分解成多个表

有些表在设计时设置了就包含很多字段，其中有的字段使用频率很低，那么当这个表的数据量很大时，查询数据的速度就会变慢，对于这种字段特别多的表，就可以将使用频率低的字段分解成多个新的表。

增加中间表

有的时候经常查询两个表中的好几个字段，若经常进行连表查询，会降低MySQL的查询速度。这种情况下，可以建立中间表来提高查询速度。我们需要先分析最可能同时查询表中的哪几个字段，然后将这些字段建立为一个中间表，并将原来的几个数据插入中间表中，之后就可以使用中间表进行查询和统计。

如我们查看以下学生表tb_student和班级表tb_classes的结构，结果如图。

实际应用中，我们常常查询学号、姓名和班级。根据这个情况，可以创建一个temp_student表，temp_student表中存储三个字段，分别是id、name和classname。创建以及插入记录的语句如下。

create table temp_student(
id int not null,
    name varchar(50) not null,
    classname varchar(45)
);
insert into temp_student select s.id,s.name,c.name from tb_student as s,tb_classes as c where s.class_id = c.id;
select * from temp_student;

优化插入记录的速度

索引、唯一性检验都会影响插入数据的速度，是因为每次都要进行排列以及检验，而且一次插入和多次插入的速度是不一样的。

禁用索引

若索引开启，则插入数据的速度会变慢，若插入大量数据时应先关闭索引，待插入完成后再次开启索引，关闭索引的语法如下。

ALTER TABLE table_name DISABLE KEYS;

重新开启索引的方法如下。

ALTER TABLE table_name ENABLE KEYS;

对于新创建的表，可以先不创建索引，等到记录都导入以后再创建索引，这样可以提高插入数据的速度。

禁用唯一性检查

唯一性检查开启时，MySQL会对插入的数据进行检验，这也会降低插入记录的速度。可以在插入记录前禁用唯一性检查，待记录完成后再开启，禁用唯一性检查的语法如下。

SET UNIQUE_CHECKS = 0;

重新开启唯一性检查的语法如下。

SET UNIQUE_CHECKS = 1;

优化INSERT语句

在插入多条语句时，若要插入多条记录，则将这些数据放在一条INSERT语句中的方式要比多条INSERT语句，每条INSERT语句只包含一个记录的方式要快。如下面。

insert into tb values (v1),(v2),...  -- 方式一

insert into tb values v1;  -- 方式二
insert into tb values v2;
...

在上面的例子中，方式一就比方式二快，因为方式一不需和数据库进行多次连接。

当插入大量数据时，建议使用一个INSERT语句插入多条记录的方式。而且若能够使用LOAD DATA INFILE语句就尽量使用该语句，因为该语句比INSERT语句更快。

分析表、检查表和优化表

分析表的主要作用是分析关键字的分布。检查表的主要作用是检查表是否存在错误。优化表的主要作用是消除删除或更新操作带来的空间浪费。

分析表

MySQL中使用ANALYZE TABLE语句来分析表，该语句的语法如下。

ANALYZE TABLE table_name1[,table_name2,...];

使用ANALYZE TABLE语句分析表的过程中，数据库系统会给表添加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ALALYZE TABLE语句能分析InnoDB和MyISAM类型的表。如我们分析tb_bookinfo表，语法如下。

analyze table tb_bookinfo;

其中Table字段表示表的名称；op是operation的缩写，代表执行的操作，analyze代表分析操作，check代表检查查找，optimize代表进行优化操作；Msg_type代表信息类型，其显示的值通常是状态、警告、错误、信息中的一个；Msg_text显示信息。

检查表和优化表之后也会出现这4列信息。

检查表

MySQL使用CHECK TABLE语句检查表。CHECK TABLE语句可以检查InnoDB、MyISAM类型的表是否存在错误，而且还可以检查视图。该语句的语法如下。

CHECK TABLE table_name1[,table_name2,...][option];

其中，参数option有五个可选项：QUICK、FAST、CHANGED、MEDIUM和EXTENTED。这五个选项的执行效率依次降低。option选项只对MyISAM表有效，对InnoDB表无效。CHECK TABLE语句在执行时也会给表添加只读表。

优化表

MySQL中使用OPTIMIZE TABLE语句来优化表。OPTIMIZE TABLE语句对InnoDB和MyISAM表都有效。但是该语句只能优化表中的VARCHAR、BLOB和TEXT字段。OPTIMIZE TABLE语句的语法如下。

OPTIMIZE TABLE table_name1[,table_name2,...];

通过此语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。OPTIMIZE TABLE语句执行时也会给表添加只读表。

若一个表使用了TEXT或BLOB这样的数据类型，那么更新、删除等操作就会造成磁盘空间的浪费，因为更新和删除后，以前分配的磁盘空间不会自动收回。使用OPTIMIZE TABLE语句就可以将这些磁盘碎片整理出来，以便以后再次利用。

优化多表查询

在MySQL中，用户可以通过连接来实现多表查询，在查询过程中，用户将一个表中的一个或多个共同字段进行连接，定义查询条件，返回统一的查询结果。这通常用来建立RDBMS常规表的关系。在多表查询中，可以应用子查询来进行优化，即在SELECT语句中嵌套SELECT语句。采用子查询优化多表查询的好处有很多，其中可以将分步查询的结果整合为一个查询，这样就不需要进行多次查询，提高查询的效率。

下面通过一个实例演示如何优化多表查询，如我们查询属于“一年三班”的全部学生姓名，其中学生信息表在tb_student表中，班级名称在tb_classes表中。第一个查询语句是使用连接查询，第二个查询语句是使用子查询。

set profiling = 1;
select s.name from tb_student as s,tb_classes as c
where s.class_id = c.id and c.name = '一年三班';
select name from tb_student as s 
where s.class_id = (select id from tb_classes where name = '一年三班');
show profiles\G

可以看到，使用子查询的速度要比使用连接查询的速度快。

优化表设计

在MySQL数据库中，提高查询速度还需要思考其他方法。首先，在设计数据表时应优先考虑使用特定长度，后考虑使用变长字段，如在创建数据表时，考虑将某个字段类型设为VARCHAR且长度为255，但是在实际应用时所存储的数据并不能达到这个长度，就造成了过多列宽，不仅浪费资源，也会降低数据表的查询效率。适当调整列宽可以减少磁盘占用空间，还可以在数据处理时使数据的I/O过程减少。将字段尽可能地设计成其可能长度的最大值以充分优化查询效率。改善性能的另一个方法就是使用OPTIMIZE TABLE命令处理用户经常操作的表，频繁操作数据库中的特定表会产生许多磁盘碎片，降低MySQL的速度，故应经常处理经常操作的数据表。最后可以考虑某些表是否可以整合为一个表，若没有使用整合，则再查询过程中可以使用连接，这样若连接的列采用相同的数据类型和长度，同样可以达到优化表的目的。

在事务中，我们举了一个若在一个完整的事务中发生异常则回滚的例子，里面使用了处理器，下面我们简单叙述一下如何使用处理器处理存储过程中的异常。

定义处理器

DECLARE …… HANDLER的语法如下。

DECLARE handler_action HANDLER FOR condition_value [,condition_value] ...
	statement;

参数意义如下：

handler_action：代表SQL脚本是否继续运行，其可选两个值：CONTINUE，表示继续执行当前SQL脚本；和EXIT，即使condition_value由statement语句块触发，也会终止执行。
statement：当处理器满足conditon_value时，要执行的SQL语句。它可以是一个简单的SQL语句，若为多个语句，需使用begin ... end。
condition_value：满足条件。其取值可为以下几种：
- mysql_error_code：MySQL的错误码，为整数类型。
- SQLSTATE：用五个字符表示的SQLSTATE值。
- condition_name：使用declare ... condition定义的条件名称。
- SQLWARNING：SQL警告，相当于值从01开始的SQLSTATE。
- NOT FOUND：未找到，相当于值从02开始的SQLSTATE。
- SQLEXCEPTION：SQL异常，相当于所有值不为00、01、02的SQLSTATE。

版权所有

版权归属：棋

许可证：署名 4.0 国际 (CC-BY-4.0)