MySQL 教程

术语

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列（数据元素）包含了相同类型的数据，例如邮政编码的数据。
行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

数据库

MySQL 是一个关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL 是开源的，所以你不需要支付额外的费用。
MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL 使用标准的 SQL 数据语言形式。
MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 对 PHP 有很好的支持，PHP 是目前最流行的 Web 开发语言。
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为 8TB。
MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

连接

您可以使用 MySQL 二进制方式进入到 mysql 命令提示符下来连接 MySQL 数据库。

以下是在命令提示窗口中连接 MySQL 服务器的简单实例：

1 2	[root@host]# mysql -u root -p Enter password:******

在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。

以上命令执行后，登录成功输出结果如下:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

在以上实例中，我们使用了 root 用户登录到 MySQL 服务器，当然你也可以使用其他 MySQL 用户登录。

如果用户权限足够，任何用户都可以在 mysql 的命令提示窗口中进行 SQL 操作。

退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：

1 2	mysql> exit Bye

创建数据库

我们可以在登陆 MySQL 服务后，使用 CREATE DATABASE 命令创建数据库，语法如下：

1	CREATE DATABASE 数据库名;

使用 CREATE DATABASE 命令

以下命令简单的演示了创建数据库的过程，数据名为 TestDB:

1 2	[root@host]# mysql -u root -p Enter password:****** # 登录后进入终端

1	mysql> CREATE DATABASE TestDB;

使用 mysqladmin 创建数据库

使用普通用户，你可能需要特定的权限来创建或者删除 MySQL 数据库。

所以我们这边使用 root 用户登录，root 用户拥有最高权限，可以使用 mysql mysqladmin 命令来创建数据库。

以下命令简单的演示了创建数据库的过程，数据名为 TestDB:

1 2	[root@host]# mysqladmin -u root -p CREATE TestDB Enter password:******

以上命令执行成功后会创建 MySQL 数据库 TestDB。

删除数据库

使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

使用 DROP DATABASE 命令

1	DROP DATABASE <数据库名>;

例如删除名为 TestDB 的数据库：

1	mysql> DROP DATABASE TestDB;

使用 mysqladmin 删除数据库

你也可以使用 mysqladmin 命令在终端来执行删除命令。

以下实例删除数据库 TestDB：

1 2	[root@host]# mysqladmin -u root -p DROP TestDB Enter password:******

执行以上删除数据库命令后，会出现一个提示框，来确认是否真的删除数据库：

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'TestDB' database [y/N] y
Database "TestDB" dropped

选择数据库

在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。

从命令提示窗口中选择 MySQL 数据库

在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用 SQL 命令来选择指定的数据库。

以下实例选取了数据库 TestDB:

[root@host]# mysql -u root -p
Enter password:******
mysql> use TestDB;
Database changed
mysql>

执行以上命令后，你就已经成功选择了 TestDB 数据库，在后续的操作中都会在 TestDB 数据库中执行。

注意:所有的数据库名，表名，表字段都是区分大小写的。所以你在使用 SQL 命令时需要输入正确的名称。

数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）。

关键字 INT 是 INTEGER 的同义词，关键字 DEC 是 DECIMAL 的同义词。

BIT 数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB 表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT 或 INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9 223 372 036 854 775 808， 9 223 372 036 854 775 807)	(0， 18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38， -1.175 494 351 E-38)， 0， (1.175 494 351 E-38， 3.402 823 466 351 E+38)	0， (1.175 494 351 E-38， 3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308， -2.225 073 858 507 201 4 E-308)， 0， (2.225 073 858 507 201 4 E-308， 1.797 693 134 862 315 7 E+308)	0， (2.225 073 858 507 201 4 E-308， 1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	DECIMAL(M, D)，如果 M>D，为 M+2 ，否则为 D+2	依赖于 M 和 D 的值	依赖于 M 和 D 的值	小数值

日期和时间类型

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

每个时间类型有一个有效值范围和一个“零”值，当指定不合法的 MySQL 不能表示的值时使用“零”值。

TIMESTAMP 类型有专有的自动更新特性，将在后面描述。

类型	大小	范围	格式	用途
DATE	3 bytes	1000-01-01 / 9999-12-31	YYYY-MM-DD	日期值
TIME	3 bytes	'-838:59:59' / '838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1 bytes	1901 / 2155	YYYY	年份值
DATETIME	8 bytes	1000-01-01 00:00:00 / 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 bytes	1970-01-01 00:00:00 / 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038-1-19 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

创建数据表

创建 MySQL 数据表需要以下信息：

表名
表字段名
定义每个表字段

CREATE TABLE 命令

以下为创建 MySQL 数据表的 SQL 通用语法：

1	CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 TestDB 数据库中创建数据表 tbl：

CREATE TABLE IF NOT EXISTS `tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为 NULL，就会报错。
AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。
PRIMARY KEY 关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

在命令提示窗口中创建表

通过 mysql> 命令窗口可以很简单的创建 MySQL 数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。

以下为创建数据表 tbl 实例:

mysql> CREATE TABLE tbl(
    -> id INT NOT NULL AUTO_INCREMENT,
    -> title VARCHAR(100) NOT NULL,
    -> author VARCHAR(40) NOT NULL,
    -> submission_date DATE,
    -> PRIMARY KEY ( id )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

注意：MySQL 命令终止符为分号 ; 。

删除数据表

MySQL 中删除数据表是非常容易操作的，但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

DROP TABLE 命令

以下为删除 MySQL 数据表的通用语法：

1	DROP TABLE table_name ;

在命令提示窗口中删除数据表

在 mysql> 命令提示窗口中删除数据表 SQL 语句为 DROP TABLE：

以下实例删除了数据表 tbl:

1 2	mysql> DROP TABLE tbl Query OK, 0 rows affected (0.8 sec)

插入数据

MySQL 表中使用 INSERT INTO 语句来插入数据。

INSERT INTO 命令

以下为向 MySQL 数据表插入数据通用的 INSERT INTO 的 SQL 语法：

1
2
3

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如："value"。

通过命令提示窗口插入数据

以下我们将使用 INSERT INTO 语句向 MySQL 数据表 tbl 插入数据

以下实例中我们将向 tbl 表插入三条数据:

mysql> INSERT INTO tbl 
    -> (title, author, submission_date)
    -> VALUES
    -> ("学习 PHP", "PHP", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO tbl
    -> (title, author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "MySQL", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO tbl
    -> (title, author, submission_date)
    -> VALUES
    -> ("学习 JAVA", "JAVA", '2021-01-01');
Query OK, 1 rows affected (0.00 sec)
mysql> INSERT INTO tbl
    -> (title, author, submission_date)
    -> VALUES
    -> ("学习 Python", "Python", '2021-01-01');
Query OK, 1 rows affected, 1 warnings (0.01 sec)

注意：使用箭头标记 -> 不是 SQL 语句的一部分，它仅仅表示一个新行，如果一条 SQL 语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;。

在以上实例中，我们并没有提供 id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

查询数据

SELECT 命令

MySQL 数据库使用 SELECT 语句来查询数据。

以下为在 MySQL 数据库中查询数据通用的 SELECT 语法：

SELECT column_name, column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号 , 分割，并使用 WHERE 语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号 * 来代替其他字段，SELECT 语句会返回表的所有字段数据。
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过 OFFSET 指定 SELECT 语句开始查询的数据偏移量。默认情况下偏移量为 0。

在命令提示窗口中获取数据

以下实例我们将通过 SELECT 命令来获取数据表 tbl 的数据：

读取数据表：

1	SELECT * FROM tbl;

输出结果：

mysql> SELECT * FROM tbl;
+----+-------------+--------+-----------------+
| id | title       | author | submission_date |
+----+-------------+--------+-----------------+
|  1 | 学习 PHP    | PHP    | 2021-01-12      |
|  2 | 学习 MySQL  | MySQL  | 2021-01-12      |
|  3 | 学习 JAVA   | JAVA   | 2021-01-01      |
|  4 | 学习 Python | Python | 2021-01-01      |
+----+-------------+--------+-----------------+
4 rows in set (0.00 sec)

WHERE 子句

WHERE 子句语法

我们知道从 MySQL 表中使用 SELECT 语句来读取数据。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法

以下是 SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

1 2	SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用 WHERE 语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回 true	(A = B) 返回 false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回 true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回 true	(A > B) 返回 false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回 true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回 true	(A >= B) 返回 false。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回 true	(A <= B) 返回 true。

如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

BINARY 关键字

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

mysql> SELECT * FROM tbl WHERE BINARY author='mysql';
Empty set (0.00 sec)

mysql> SELECT * FROM tbl WHERE BINARY author='MySQL';
+----+------------+--------+-----------------+
| id | title      | author | submission_date |
+----+------------+--------+-----------------+
|  2 | 学习 MySQL | MySQL  | 2021-01-12      |
+----+------------+--------+-----------------+

实例中使用了 BINARY 关键字，是区分大小写的，所以 author='mysql' 的查询条件是没有数据的。

UPDATE 更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作。

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

1 2	UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

在命令提示窗口中更新数据

以下我们将在 UPDATE 命令使用 WHERE 子句来更新 tbl 表中指定的数据：

以下实例将更新数据表中 id 为 3 的 title 字段值：

mysql> UPDATE tbl SET title='学习 C++' WHERE id = 3;
Query OK, 1 rows affected (0.01 sec)
 
mysql> SELECT * FROM tbl WHERE id = 3;
+----+----------+--------+-----------------+
| id | title    | author | submission_date |
+----+----------+--------+-----------------+
|  3 | 学习 C++ | JAVA   | 2021-01-01      |
+----+----------+--------+-----------------+

从结果上看，id 为 3 的 title 已被修改。

DELETE 语句

DELETE FROM 命令

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

1	DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

在命令提示窗口中删除数据

这里我们将在 DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 tbl 所选的数据。

以下实例将删除 tbl 表中 id 为 3 的记录：

1 2	mysql> DELETE FROM tbl WHERE id=3; Query OK, 1 row affected (0.23 sec)

多表连接删除

在 MySQL 中可以同时删除两个表以上的数据：

1
2
3

DELETE orders, items FROM orders, items 
WHERE orders.userid = items.userid 
AND orders.orderid = items.orderid

LIKE 子句

我们知道在 MySQL 中使用 SELECT 命令来读取数据，同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 author = 'MySQL'。

但是有时候我们需要获取 author 字段含有 P 字符的所有记录，这时我们就需要在 WHERE 子句中使用 LIKE 子句。

LIKE 子句中使用百分号 % 字符来表示任意字符，类似于 UNIX 或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

LIKE 子句语法

以下是 SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

1
2
3

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用 LIKE 子句。
你可以使用 LIKE 子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

在命令提示符中使用 LIKE 子句

以下我们将在 SELECT 命令中使用 WHERE...LIKE 子句来从 MySQL 数据表 tbl 中读取数据。

以下是我们将 tbl 表中获取 author 字段中以 P 为开头的所有记录：

mysql> SELECT * FROM tbl  WHERE author LIKE 'P%';
+----+-------------+--------+-----------------+
| id | title       | author | submission_date |
+----+-------------+--------+-----------------+
|  1 | 学习 PHP    | PHP    | 2021-01-12      |
|  4 | 学习 Python | Python | 2021-01-01      |
+----+-------------+--------+-----------------+
2 rows in set (0.00 sec)

UNION 操作符

下面为大家介绍 MySQL UNION 操作符的语法和实例。

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNION 操作符语法

MySQL UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

参数

expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

演示数据库

下面是选自 Websites 表的数据：

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 微博          | https://weibo.com/         | 20    | CN      |
| 4  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 5  | stackoverflow | https://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 apps 表的数据：

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | https://im.qq.com/       | CN      |
|  2 | 微博 APP | https://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

在命令提示符中使用 UNION 操作符

下面的 SQL 语句从 Websites 和 apps 表中选取所有不同的 country（只有不同的值），执行 SQL 输出结果如下：

mysql> SELECT country FROM Websites
    -> UNION
    -> SELECT country FROM apps
    -> ORDER BY country;
+---------+
| country |
+---------+
|  CN     |
|  IND    |
|  USA    |
+---------+
3 rows in set (0.00 sec)

注释：UNION 不能用于列出两个表中所有的 country。如果一些网站和 APP 来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值。

在命令提示符中使用 UNION ALL 操作符

下面的 SQL 语句使用 UNION ALL 从 Websites 和 apps 表中选取所有的 country（有重复的值）：

mysql> SELECT country FROM Websites
    -> UNION ALL
    -> SELECT country FROM apps
    -> ORDER BY country;
+---------+
| country |
+---------+
|  CN     |
|  CN     |
|  CN     |
|  CN     |
|  IND    |
|  USA    |
|  USA    |
|  USA    |
+---------+
8 rows in set (0.01 sec)

带有 WHERE 的 UNION ALL

下面的 SQL 语句使用 UNION ALL 从 Websites 和 apps 表中选取所有的中国(CN)的数据（有重复的值）：

mysql> SELECT country, name FROM Websites
    -> WHERE country='CN'
    -> UNION ALL
    -> SELECT country, app_name FROM apps
    -> WHERE country='CN'
    -> ORDER BY country;
+---------+------------+
| country | name       |
+---------+------------+
| CN      | 淘宝       |
| CN      | QQ APP     |
| CN      | 微博 APP   |
| CN      | 微博       |
| CN      | 淘宝 APP   |
+---------+------------+
5 rows in set (0.03 sec)

排序语句

我们知道从 MySQL 表中使用 SELECT 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

ORDER BY 子句语法

以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

1 2	SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。

在命令提示符中使用 ORDER BY 子句

以下将在 SELECT 语句中使用 ORDER BY 子句来读取 MySQL 数据表 tbl 中的数据：

尝试以下实例，结果将按升序及降序排列。

mysql> SELECT * FROM tbl ORDER BY submission_date ASC;
+----+-------------+--------+-----------------+
| id | title       | author | submission_date |
+----+-------------+--------+-----------------+
|  3 | 学习 C++    | JAVA   | 2021-01-01      |
|  4 | 学习 Python | Python | 2021-01-01      |
|  1 | 学习 PHP    | PHP    | 2021-01-12      |
|  2 | 学习 MySQL  | MySQL  | 2021-01-12      |
+----+-------------+--------+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tbl ORDER BY submission_date DESC;
+----+-------------+--------+-----------------+
| id | title       | author | submission_date |
+----+-------------+--------+-----------------+
|  1 | 学习 PHP    | PHP    | 2021-01-12      |
|  2 | 学习 MySQL  | MySQL  | 2021-01-12      |
|  3 | 学习 C++    | JAVA   | 2021-01-01      |
|  4 | 学习 Python | Python | 2021-01-01      |
+----+-------------+--------+-----------------+
4 rows in set (0.00 sec)

分组语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

GROUP BY 子句语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

本章节实例使用到了以下表结构及数据，使用前我们可以先将以下数据导入数据库中。

DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee_tbl` VALUES 
('1', '小明', '2016-04-22 15:25:33', '1'), 
('2', '小王', '2016-04-20 15:25:47', '3'), 
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'), 
('6', '小明', '2016-04-04 15:26:54', '2');

导入成功后，执行以下 SQL 语句：

mysql> SELECT * FROM employee_tbl;
+----+------+---------------------+--------+
| id | name | date                | singin |
+----+------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+------+---------------------+--------+
6 rows in set (0.00 sec)

接下来我们使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+------+----------+
3 rows in set (0.01 sec)

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM, AVG, COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) AS singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL |           16 |
+------+--------------+
4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。

我们可以使用 COALESCE 来设置一个可以取代 NUll 的名称

COALESCE 语法：

1	SELECT COALESCE(a, b, c) FROM ...;

参数说明：如果 a == NULL，则选择 b；如果b == NULL，则选择 c；如果 a != NULL，则选择a；如果 a b c 都为 NULL ，则返回为 NULL（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT COALESCE(name, '总数'), SUM(singin) AS singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+------------------------+--------------+
| COALESCE(name, '总数') | singin_count |
+------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+------------------------+--------------+
4 rows in set (0.00 sec)

JOIN

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在 SELECT、UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN

我们在 testDB 数据库中有两张表 tcount_tbl 和 tbl。两张数据表数据如下：

mysql> SELECT * FROM tcount_tbl;
+--------+-------+
| author | count |
+--------+-------+
| JAVA   |    10 |
| MySQL  |    20 |
| C++    |    30 |
+--------+-------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM tbl;
+----+-------------+--------+-----------------+
| id | title       | author | submission_date |
+----+-------------+--------+-----------------+
|  1 | 学习 PHP    | PHP    | 2021-01-12      |
|  2 | 学习 MySQL  | MySQL  | 2021-01-12      |
|  3 | 学习 C++    | JAVA   | 2021-01-01      |
|  4 | 学习 Python | Python | 2021-01-01      |
|  5 | MySQL 教程  | MySQL  | 2021-01-12      |
|  6 | JAVA 教程   | JAVA   | 2021-01-12      |
+----+-------------+--------+-----------------+
6 rows in set (0.00 sec)

接下来我们就使用 MySQL 的INNER JOIN (也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取 tbl 表中所有 author 字段在 tcount_tbl 表对应的 count 字段值：

mysql>  SELECT a.id, a.author, b.count FROM tbl a INNER JOIN tcount_tbl b ON a.author = b.author;
+----+--------+-------+
| id | author | count |
+----+--------+-------+
|  2 | MySQL  |    20 |
|  3 | JAVA   |    10 |
|  5 | MySQL  |    20 |
|  6 | JAVA   |    10 |
+----+--------+-------+
4 rows in set (0.00 sec)

以上 SQL 语句等价于以下 WHERE 子句：

mysql> SELECT a.id, a.author, b.count FROM tbl a, tcount_tbl b WHERE a.author = b.author;
+----+--------+-------+
| id | author | count |
+----+--------+-------+
|  2 | MySQL  |    20 |
|  3 | JAVA   |    10 |
|  5 | MySQL  |    20 |
|  6 | JAVA   |    10 |
+----+--------+-------+
4 rows in set (0.00 sec)

LEFT JOIN

MySQL LEFT JOIN 与 JOIN 有所不同。 LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

尝试以下实例，以 tbl 为左表，tcount_tbl 为右表，理解 LEFT JOIN 的应用：

mysql> SELECT a.id, a.author, b.count FROM tbl a LEFT JOIN tcount_tbl b ON a.author = b.author;
+----+--------+-------+
| id | author | count |
+----+--------+-------+
|  3 | JAVA   |    10 |
|  6 | JAVA   |    10 |
|  2 | MySQL  |    20 |
|  5 | MySQL  |    20 |
|  1 | PHP    |  NULL |
|  4 | Python |  NULL |
+----+--------+-------+
6 rows in set (0.00 sec)

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl 中没有对应的 author 字段值。

RIGHT JOIN

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

尝试以下实例，以 tbl 为左表，tcount_tbl 为右表，理解 RIGHT JOIN 的应用：

mysql> SELECT a.id, a.author, b.count FROM tbl a RIGHT JOIN tcount_tbl b ON a.author = b.author;
+------+--------+-------+
| id   | author | count |
+------+--------+-------+
|    2 | MySQL  |    20 |
|    3 | JAVA   |    10 |
|    5 | MySQL  |    20 |
|    6 | JAVA   |    10 |
| NULL | NULL   |    30 |
+------+--------+-------+
5 rows in set (0.00 sec)

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 tbl 中没有对应的 author 字段值。

NULL 值处理

我们已经知道 MySQL 使用 SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

NULL 值运算符

为了处理这种情况，MySQL 提供了三大运算符:

IS NULL: 当列的值是 NULL，此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

注意：

1	SELECT *, columnName1 + IFNULL(columnName2, 0) FROM tableName;

columnName1，columnName2 为 int 型，当 columnName2 中，有值为 NULL 时，columnName1 + columnName2 = NULL，IFNULL(columnName2, 0) 把 columnName2 中 NULL 值转为 0。

在命令提示符中使用 NULL 值

以下实例中假设数据库 TestDB 中的表 tbl 含有两列 author 和 count, count 中设置插入 NULL 值。

mysql> SELECT * FROM tbl;
+---------------+--------------+
| author | count |
+---------------+--------------+
| TestDB        | NULL         |
| Google        | NULL         |
| FK            | 20           |
| Baidu         | 20           |
+---------------+--------------+
4 rows in set (0.01 sec)

以下实例中你可以看到 = 和 != 运算符是不起作用的：

mysql> SELECT * FROM tbl WHERE count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tbl WHERE count != NULL;
Empty set (0.01 sec)

查找数据表中 tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例：

mysql> SELECT * FROM tbl WHERE count IS NULL;
+--------+-------+
| author | count |
+--------+-------+
| TestDB | NULL  |
| Google | NULL  |
+---- ---+-------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM tbl WHERE count IS NOT NULL;
+--------+-------+
| author | count |
+--------+-------+
| FK     | 20    |
| Baidu  | 20    |
+--------+-------+
2 rows in set (0.01 sec)

正则表达式

在前面的章节我们已经了解到 MySQL 可以通过 LIKE 子句来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL 中使用 REGEXP 操作符来进行正则表达式匹配。

下表中的正则模式可应用于 REGEXP 操作符中。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，`^` 也匹配 `\n` 或 `\r` 之后的位置。
$	匹配输入字符串的结束位置。如果设置了 RegExp 对象的 Multiline 属性，`$` 也匹配 `\n` 或 `\r` 之前的位置。
.	匹配除 `\n` 之外的任何单个字符。要匹配包括 `\n` 在内的任何字符，请使用像 `[.\n]` 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如，`[abc]` 可以匹配 `plain` 中的 `a`。
[^...]	负值字符集合。匹配未包含的任意字符。例如，`[\^abc]` 可以匹配 `plain` 中的`p`。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，`z`\|`food` 能匹配 `z` 或 `food`。`(z`\|`f)ood` 则匹配 `zood` 或 `food`。
*	匹配前面的子表达式零次或多次。例如，`zo` 能匹配 `z` 以及 `zoo`。`` 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，`zo+` 能匹配 `zo` 以及 `zoo`，但不能匹配 `z`。`+` 等价于 `{1,}`。
{n}	`n` 是一个非负整数。匹配确定的 `n` 次。例如，`o{2}` 不能匹配 `Bob` 中的 `o`，但是能匹配 `food` 中的两个 `o`。
{n, m}	`m` 和 `n` 均为非负整数，其中 `n <= m`。最少匹配 `n` 次且最多匹配 `m` 次。

了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的 SQL 语句。以下我们将列出几个小实例（表名：person_tbl）来加深我们的理解：

查找 name 字段中以 st 为开头的所有数据：

1	mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找 name 字段中以 ok 为结尾的所有数据：

1	mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找 name 字段中包含 mar 字符串的所有数据：

1	mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找 name 字段中以元音字符开头或以 ok 字符串结尾的所有数据：

1	mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]\|ok$';

ALTER 命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到 ALTER 命令。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除，添加或修改表字段

如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

1	mysql> ALTER TABLE testalter_tbl DROP i;

如果数据表中只剩余一个字段则无法使用 DROP 来删除字段。

MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

1	mysql> ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果你需要指定新增字段的位置，可以使用 MySQL 提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在 ALTER 命令中使用 MODIFY 或 CHANGE 子句。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

1	mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

1 2	mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 NULL 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。

以下实例，指定字段 j 为 NOT NULL 且默认值为 100 。

1 2	mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL 会自动设置该字段默认为 NULL。

修改字段默认值

你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

你也可以使用 ALTER 命令及 DROP 子句来删除字段的默认值，如下实例：

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Changing a Table Type:

修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MyISAM：

注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句，并使用 \G 格式化输出。

mysql> ALTER TABLE testalter_tbl ENGINE = MyISAM;
mysql> SHOW TABLE STATUS LIKE 'testalter_tbl' \G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

1	mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

ALTER 命令还可以用来创建及删除 MySQL 数据表的索引，该功能我们会在接下来的章节中介绍。

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。

在 MySQL 中只有使用了 InnoDB 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 INSERT，UPDATE, DELETE 语句。

一般来说，事务是必须满足 4 个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

用 BEGIN，ROLLBACK，COMMIT 来实现
- BEGIN：开始一个事务
- ROLLBACK：事务回滚
- COMMIT：事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0：禁止自动提交
- SET AUTOCOMMIT=1：开启自动提交

mysql> CREATE TABLE transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM transaction_test;
Empty set (0.01 sec)

mysql> BEGIN;  # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO transaction_test VALUE(5);
Query OK, 1 rows affected (0.01 sec)

mysql> INSERT INTO transaction_test VALUE(6);
Query OK, 1 rows affected (0.00 sec)

mysql> COMMIT;  # 提交事务
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql> BEGIN;  # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO transaction_test VALUE(7);
Query OK, 1 rows affected (0.00 sec)

mysql> rollback;  # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM transaction_test;  # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件（一般作为 WHERE 子句的条件）。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

1	CREATE INDEX indexName ON table_name (column_name);

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

修改表结构(添加索引)

1	ALTER table tableName ADD INDEX indexName(columnName);

创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引的语法

1	DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

1	CREATE UNIQUE INDEX indexName ON mytable(username(length));

修改表结构

1	ALTER table mytable ADD UNIQUE [indexName] (username(length));

创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

使用 ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)：这条语句创建索引的值必须是唯一的（除了 NULL 外，NULL 可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)：添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)：该语句指定了索引为 FULLTEXT，用于全文索引。

以下实例为在表中添加索引。

1	mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

1	mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

1 2	mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

1	mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定 PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

1	mysql> SHOW INDEX FROM table_name; \G

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。

临时表在 MySQL 3.23 版本中添加，如果你的 MySQL 版本低于 3.23 版本就无法使用 MySQL 的临时表。不过现在一般很少有再使用这么低版本的 MySQL 数据库服务了。

MySQL 临时表只在当前连接可见，如果你使用 PHP 脚本来创建 MySQL 临时表，那每当 PHP 脚本执行完成后，该临时表也会自动销毁。

如果你使用了其他 MySQL 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

创建 MySQL 临时表

以下展示了使用 TEMPORARY 关键字创建 MySQL 临时表的简单实例：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL,
    -> total_sales DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    -> avg_unit_price DECIMAL(7, 2) NOT NULL DEFAULT 0.00,
    -> total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

用查询直接创建临时表：

CREATE TEMPORARY TABLE 临时表名 AS
(
    SELECT *  FROM 旧的表名
);

当你使用 SHOW TABLES 命令显示数据表列表时，你将无法看到 SalesSummary 表。

如果你退出当前 MySQL 会话，再使用 SELECT 命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

删除 MySQL 临时表

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前 MySQL 会话使用 DROP TABLE 命令来手动删除临时表。

以下是手动删除临时表的实例：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL,
    -> total_sales DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    -> avg_unit_price DECIMAL(7, 2) NOT NULL DEFAULT 0.00,
    -> total_units_sold INT UNSIGNED NOT NULL DEFAULT 0,
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TestDB.SalesSummary' doesn't exist

复制表

如果我们需要完全的复制 MySQL 的数据表，包括表的结构，索引，默认值等。如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。

完整的复制 MySQL 数据表的步骤如下：

使用 SHOW CREATE TABLE 命令获取创建数据表（CREATE TABLE）语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的 SQL 语句，修改数据表名，并执行 SQL 语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

尝试以下实例来复制表 tbl。

获取数据表的完整结构。

mysql> SHOW CREATE TABLE tbl \G;
*************************** 1. row ***************************
      Table: tbl
Create Table: CREATE TABLE `tbl` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `AUTHOR_INDEX` (`author`)
) ENGINE=InnoDB 
1 row in set (0.00 sec)

ERROR:
No query specified

修改 SQL 语句的数据表名，并执行 SQL 语句。

mysql> CREATE TABLE `clone_tbl` (
  -> `id` int(11) NOT NULL auto_increment,
  -> `title` varchar(100) NOT NULL default '',
  -> `author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`author`)
  -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)

执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

mysql> INSERT INTO clone_tbl (id,
    ->                        title,
    ->                        author,
    ->                        submission_date)
    -> SELECT id,title, author, submission_date
    -> FROM tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

执行以上步骤后，你将完整的复制表，包括表结构及表数据。

另一种完整复制表的方法:

1 2	CREATE TABLE targetTable LIKE sourceTable; INSERT INTO targetTable SELECT * FROM sourceTable;

可以拷贝一个表中其中的一些字段:

1	CREATE TABLE newadmin AS (SELECT username, password FROM admin);

可以将新建的表的字段改名:

1	CREATE TABLE newadmin AS (SELECT id, username AS uname, password AS pass FROM admin);

可以拷贝一部分数据:

1	CREATE TABLE newadmin AS (SELECT * FROM admin WHERE LEFT(username, 1) = 's');

可以在创建表的同时定义表中的字段信息:

1	CREATE TABLE newadmin (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) AS (SELECT * FROM admin);

元数据

查询结果信息：SELECT，UPDATE 或 DELETE 语句影响的记录数。
数据库和数据表的信息：包含了数据库及数据表的结构信息。
MySQL 服务器信息：包含了数据库服务器的当前状态，版本号等。

在 MySQL 的命令提示符中，我们可以很容易的获取以上服务器信息。

获取服务器元数据

以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中使用。

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名（或者返回空）
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

序列使用

MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用 MySQL 序列来实现。

使用 AUTO_INCREMENT

MySQL 中最简单使用序列的方法就是使用 AUTO_INCREMENT 来定义列。

以下实例中创建了数据表 insect，insect 表中 id 无需指定值可实现自动增长。

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO insect (id, name, date, origin) VALUES
    -> (NULL, 'housefly', '2001-09-10', 'kitchen'),
    -> (NULL, 'millipede', '2001-09-10', 'driveway'),
    -> (NULL, 'grasshopper', '2001-09-10', 'front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

获取 AUTO_INCREMENT 值

在 MySQL 的客户端中你可以使用 SQL 中的 LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的 AUTO_INCREMENT 列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

设置序列的开始值

一般情况下序列的开始值为 1，但如果你需要指定一个开始值 100，那我们可以通过以下语句来实现：

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL,
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后，通过以下语句来实现：

1	mysql> ALTER TABLE t AUTO_INCREMENT = 100;

处理重复数据

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。

防止表中出现重复数据

你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键）或者 UNIQUE（唯一）索引来保证数据的唯一性。

让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

如果你想设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功，并抛出错误。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而 REPLACE INTO 如果存在 PRIMARY 或 UNIQUE 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

统计重复数据

以下我们将统计表中 first_name 和 last_name 的重复记录数：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

以上查询语句将返回 person_tbl 表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：

确定哪一列包含的值可能会重复。
在列选择列表使用 COUNT(*) 列出的那些列。
在 GROUP BY 子句中列出的列。
HAVING 子句设置重复数大于 1。

过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

1 2	mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

1
2
3

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的 SQL 语句：

mysql> CREATE TABLE tmp
    -> SELECT last_name, first_name, sex
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX（索引）和 PRIMARY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

1 2	mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);

SQL 注入

如果您通过网页获取用户输入的数据并将其插入一个 MySQL 数据库，那么就有可能发生 SQL 注入安全的问题。

本章节将为大家介绍如何防止 SQL 注入，并通过脚本来过滤 SQL 中注入的字符。

所谓 SQL 注入，就是通过把 SQL 命令插入到 Web 表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的 SQL 命令。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

以下实例中，输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间：

if (preg_match("/^\w{8, 20}$/", $_GET['username'], $matches))
{
   $result = mysqli_query($conn, "SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username 输入异常";
}

让我们看下在没有过滤特殊字符时，出现的 SQL 情况：

1
2
3

// 设定 $name 中插入了我们不需要的 SQL 语句
$name = "Qadir'; DELETE FROM users;";
mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

以上的注入语句中，我们没有对 $name 的变量进行过滤，$name 中插入了我们不需要的 SQL 语句，将删除 users 表中的所有数据。

在 PHP 中的 mysqli_query() 是不允许执行多个 SQL 语句的，但是在 SQLite 和 PostgreSQL 是可以同时执行多条 SQL 语句的，所以我们对这些用户的数据需要进行严格的验证。

防止 SQL 注入，我们需要注意以下几个要点：

永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。
永远不要使用动态拼装 sql，可以使用参数化的 sql 或者直接使用存储过程进行数据查询存取。
永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
不要把机密信息直接存放，加密或者 hash 掉密码和敏感的信息。
应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
SQL 注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用 SQL 注入检测工具 jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN 等。采用 MDCSOFT-IPS 可以有效的防御 SQL 注入，XSS 攻击等。

防止 SQL 注入

在脚本语言，如 Perl 和 PHP 你可以对用户输入的数据进行转义从而来防止 SQL 注入。

PHP 的 MySQL 扩展提供了 mysqli_real_escape_string() 函数来转义特殊的输入字符。

if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

LIKE 语句中的注入

LIKE 查询时，如果用户输入的值有 _ 和 %，则会出现这种情况：用户本来只是想查询 abcd_，查询结果中却有 abcd_、abcde、abcdf 等等；用户要查询 30%（注：百分之三十）时也会出现问题。

在 PHP 脚本中我们可以使用 addcslashes() 函数来处理以上情况，如下实例：

1
2
3

$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");
// $sub == \%something\_
 mysqli_query($conn, "SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

addcslashes() 函数在指定的字符前添加反斜杠。

语法格式:

1	addcslashes(string, characters)

导出数据

MySQL 中你可以使用 SELECT...INTO OUTFILE 语句来简单的导出数据到文本文件上。

使用 SELECT ... INTO OUTFILE 语句导出数据

以下实例中我们将数据表 tbl 数据导出到 /tmp/TestDB.txt 文件中:

1 2	mysql> SELECT * FROM tbl -> INTO OUTFILE '/tmp/TestDB.txt';

你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

1
2
3

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/TestDB.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a, b, a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table;

SELECT ... INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的逆操作，SELECT 句法。为了将一个数据库的数据写入一个文件，使用 SELECT ... INTO OUTFILE；为了将文件读回数据库，使用 LOAD DATA INFILE。
SELECT...INTO OUTFILE 'file_name' 形式的 SELECT 可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有 FILE 权限，才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
在 UNIX 中，该文件被创建后是可读的，权限由 MySQL 服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

导出表作为原始数据

mysqldump 是 MySQL 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

以下实例将数据表 tbl 导出到 /tmp 目录中：

1 2	$ mysqldump -u root -p --no-create-info --tab=/tmp TestDB tbl password ******

导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件，如下所示：

1 2	$ mysqldump -u root -p TestDB tbl > dump.txt password ******

以上命令创建的文件内容如下：

-- MySQL dump 8.23
--
-- Host: localhost    Database: TestDB
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `tbl`
--

CREATE TABLE tbl (
  id int(11) NOT NULL auto_increment,
  title varchar(100) NOT NULL default '',
  author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY AUTHOR_INDEX (author)
) TYPE=MyISAM;

--
-- Dumping data for table `tbl`
--

INSERT INTO tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

如果你需要导出整个数据库的数据，可以使用以下命令：

1 2	$ mysqldump -u root -p TestDB > database_dump.txt password ******

如果需要备份所有数据库，可以使用以下命令：

1 2	$ mysqldump -u root -p --all-databases > database_dump.txt password ******

--all-databases 选项在 MySQL 3.23.12 及以后版本加入。

该方法可用于实现数据库的备份策略。

将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

1 2	$ mysqldump -u root -p database_name table_name > dump.txt password *****

如果完整备份数据库，则无需使用特定的表名称。

如果你需要将备份的数据库导入到 MySQL 服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

1 2	$ mysql -u root -p database_name < dump.txt password *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

1 2	$ mysqldump -u root -p database_name \ \| mysql -h other-host.com database_name

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

导入数据

mysql 命令导入

使用 mysql 命令导入语法格式为：

mysql -u 用户名 -p 密码 < 要导入的数据库数据(TestDB.sql)

1	$ mysql -uroot -p123456 < TestDB.sql

以上命令将将备份的整个数据库 TestDB.sql 导入。

source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> CREATE DATABASE abc;      # 创建数据库
mysql> USE abc;                  # 使用已创建的数据库 
mysql> SET names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE 语句来插入数据。以下实例中将从当前目录中读取文件 dump.txt，将该文件中的数据插入到当前数据库的 mytbl 表中。

1	mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定 LOCAL 关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在 LOAD DATA 语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY）也是可选的，不过，用户必须至少指定它们中的一个。

1
2
3

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
    -> FIELDS TERMINATED BY ':'
    -> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a, b, c，但在插入表的列顺序为 b, c, a，则数据导入语法如下：

1 2	mysql> LOAD DATA LOCAL INFILE 'dump.txt' -> INTO TABLE mytbl (b, c, a);

使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

1 2	$ mysqlimport -u root -p --local mytbl dump.txt password *****

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

1
2
3

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

1
2
3

$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****

mysqlimport 的常用选项介绍

选项	功能
-d or --delete	新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force	不管是否遇到错误，mysqlimport 将强制继续插入数据
-i or --ignore	mysqlimport 跳过或者忽略那些有相同唯一关键字的行，导入文件中的数据将被忽略。
-l or -lock-tables	数据被插入之前锁住表，这样就防止了在更新数据库时，用户的查询和更新受到影响。
-r or -replace	这个选项与 -i 选项的作用相反；此选项将替代表中有相同唯一关键字的记录。
--fields -enclosed -by=char	指定文本文件中数据的记录时以什么括起的，很多情况下数据以双引号括起。默认的情况下数据是没有被字符括起的。
--fields -terminated -by=char	指定各个数据的值之间的分隔符，在句号分隔的文件中，分隔符是句号。可以用此选项指定数据之间的分隔符。默认的分隔符是跳格符（Tab）
--lines -terminated -by=str	此选项指定文本文件中行与行之间数据的分隔字符串或者字符。默认的情况下 mysqlimport 以 newline 为行分隔符。您可以选择用一个字符串来替代一个单个的字符：一个新行或者一个回车。

mysqlimport 命令常用的选项还有 -v 显示版本（version），-p 提示输入密码（password）等。

函数

字符串函数

函数	描述	实例
ASCII(s)	返回字符串 s 的第一个字符的 ASCII 码。	返回 CustomerName 字段第一个字母的 ASCII 码： `SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;`
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 GOOGLE 的字符数： `SELECT CHAR_LENGTH("GOOGLE") AS LengthOfString;`
CHARACTER_LENGTH(s)	返回字符串 s 的字符数	返回字符串 GOOGLE 的字符数： `SELECT CHARACTER_LENGTH("GOOGLE") AS LengthOfString;`
CONCAT(s1, s2 ... sn)	字符串 s1, s2 等多个字符串合并为一个字符串	合并多个字符串： `SELECT CONCAT("SQL ", "Baidu ", "Google ", "Facebook") AS ConcatenatedString;`
CONCAT_WS(x, s1, s2 ... sn)	同 CONCAT(s1, s2, ...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符	合并多个字符串，并添加分隔符 s： `SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;`
FIELD(s, s1, s2 ...)	返回第一个字符串 s 在字符串列表(s1, s2 ...)中的位置	返回字符串 c 在列表值中的位置： `SELECT FIELD("c", "a", "b", "c", "d", "e"); -- 3`
FIND_IN_SET(s1, s2)	返回在字符串 s2 中与 s1 匹配的字符串的位置	返回字符串 c 在指定字符串中的位置： `SELECT FIND_IN_SET("c", "a,b,c,d,e"); -- 5`
FORMAT(x, n)	函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。	格式化数字 "#,###.##" 形式： `SELECT FORMAT(250500.5634, 2); -- 250,500.56`
INSERT(s1, x, len, s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串	从字符串第一个位置开始的 6 个字符替换为 baidu： `SELECT INSERT("google.com", 1, 6, "baidu"); -- baidu.com`
LOCATE(s1, s)	从字符串 s 中获取 s1 的开始位置	获取 b 在字符串 abc 中的位置： `SELECT LOCATE('st', 'myteststring'); -- 5` 返回字符串 abc 中 b 的位置： `SELECT LOCATE('b', 'abc'); -- 2`
LCASE(s)	将字符串 s 的所有字母变成小写字母	字符串 GOOGLE 转换为小写： `SELECT LCASE('GOOGLE'); -- google`
LEFT(s, n)	返回字符串 s 的前 n 个字符	返回字符串 GOOGLE 中的前两个字符： `SELECT LEFT('GOOGLE', 2); -- GO`
LOWER(s)	将字符串 s 的所有字母变成小写字母：	字符串 GOOGLE 转换为小写： `SELECT LOWER('GOOGLE'); -- google`
LPAD(s1, len, s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 xx 填充到 abc 字符串的开始处： `SELECT LPAD('abc', 5,'xx'); -- xxabc`
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 GOOGLE 开始处的空格： `SELECT LTRIM(" GOOGLE") AS LeftTrimmedString;-- GOOGLE`
MID(s, n, len)	从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s, n, len)	从字符串 GOOGLE 中的第 2 个位置截取 3 个字符： `SELECT MID("GOOGLE", 2, 3) AS ExtractString; -- OOG`
POSITION(s1 IN s)	从字符串 s 中获取 s1 的开始位置	返回字符串 abc 中 b 的位置： `SELECT POSITION('b' in 'abc'); -- 2`
REPEAT(s, n)	将字符串 s 重复 n 次	将字符串 GOOGLE 重复三次： `SELECT REPEAT('GOOGLE', 3); -- GOOGLEGOOGLEGOOGLE`
REPLACE(s, s1, s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 abc 中的字符 a 替换为字符 x： `SELECT REPLACE('abc','a','x'); -- xbc`
REVERSE(s)	将字符串 s 的顺序反过来	将字符串 abc 的顺序反过来： `SELECT REVERSE('abc'); -- cba`
RIGHT(s, n)	返回字符串 s 的后 n 个字符	返回字符串 GOOGLE 的后两个字符： `SELECT RIGHT('GOOGLE', 2); -- LE`
RPAD(s1, len, s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx 填充到 abc 字符串的结尾处： `SELECT RPAD('abc', 5,'xx'); -- abcxx`
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 GOOGLE 的末尾空格： `SELECT RTRIM("GOOGLE ") AS RightTrimmedString; -- GOOGLE`
SPACE(n)	返回 n 个空格	返回 10 个空格： `SELECT SPACE(10);`
STRCMP(s1, s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串： `SELECT STRCMP("GOOGLE", "GOOGLE"); -- 0`
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 GOOGLE 中的第 2 个位置截取 3 个字符： `SELECT SUBSTR("GOOGLE", 2, 3) AS ExtractString; -- OOG`
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 GOOGLE 中的第 2 个位置截取 3 个字符： `SELECT SUBSTRING("GOOGLE", 2, 3) AS ExtractString; -- OOG`
SUBSTRING_INDEX(s, delimiter, number)	返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。	`SELECT SUBSTRING_INDEX('ab', '', 1); -- a` `SELECT SUBSTRING_INDEX('ab', '', -1); -- b` `SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('abcde', '', 3), '', -1); -- c`
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 GOOGLE 的首尾空格： `SELECT TRIM(' GOOGLE ') AS TrimmedString;`
UCASE(s)	将字符串转换为大写	将字符串 google 转换为大写： `SELECT UCASE("google"); -- GOOGLE`
UPPER(s)	将字符串转换为大写	将字符串 google 转换为大写： `SELECT UPPER("google"); -- GOOGLE`

数字函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	`SELECT ABS(-1); -- 返回 1`
ACOS(x)	求 x 的反余弦值（参数是弧度）	`SELECT ACOS(0.25);`
ASIN(x)	求反正弦值（参数是弧度）	`SELECT ASIN(0.25);`
ATAN(x)	求反正切值（参数是弧度）	`SELECT ATAN(2.5);`
ATAN2(n, m)	求反正切值（参数是弧度）	`SELECT ATAN2(-0.8, 2);`
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中 Price 字段的平均值： `SELECT AVG(Price) AS AveragePrice FROM Products;`
CEIL(x)	返回大于或等于 x 的最小整数	`SELECT CEIL(1.5); -- 返回 2`
CEILING(x)	返回大于或等于 x 的最小整数	`SELECT CEILING(1.5); -- 返回 2`
COS(x)	求余弦值（参数是弧度）	`SELECT COS(2);`
COT(x)	求余切值（参数是弧度）	`SELECT COT(6);`
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号	返回 Products 表中 products 字段总共有多少条记录： `SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;`
DEGREES(x)	将弧度转换为角度	`SELECT DEGREES(3.1415926535898); -- 180`
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5： `SELECT 10 DIV 5; -- 2`
EXP(x)	返回 e 的 x 次方	计算 e 的三次方： `SELECT EXP(3); -- 20.085536923188`
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数： `SELECT FLOOR(1.5); -- 返回 1`
GREATEST(expr1, expr2, expr3, ...)	返回列表中的最大值	返回以下数字列表中的最大值： `SELECT GREATEST(3, 12, 34, 8, 25); -- 34` 返回以下字符串列表中的最大值： `SELECT GREATEST("Google", "Baidu", "Apple"); -- Google`
LEAST(expr1, expr2, expr3, ...)	返回列表中的最小值	返回以下数字列表中的最小值： `SELECT LEAST(3, 12, 34, 8, 25); -- 3` 返回以下字符串列表中的最小值： `SELECT LEAST("Google", "Baidu", "Apple"); -- Apple`
LN(x)	返回数字 x 的自然对数，以 e 为底。	返回 2 的自然对数： `SELECT LN(2); -- 0.6931471805599453`
LOG(x) 或 LOG(base, x)	返回自然对数（以 e 为底的对数），如果带有 base 参数，则 base 为指定带底数。	`SELECT LOG(20.085536923188); -- 3` `SELECT LOG(2, 4); -- 2`
LOG10(x)	返回以 10 为底的对数	`SELECT LOG10(100); -- 2`
LOG2(x)	返回以 2 为底的对数	返回以 2 为底 6 的对数： `SELECT LOG2(6); -- 2.584962500721156`
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值： `SELECT MAX(Price) AS LargestPrice FROM Products;`
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值： `SELECT MIN(Price) AS MinPrice FROM Products;`
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数： `SELECT MOD(5, 2); -- 1`
PI()	返回圆周率(3.141593）	`SELECT PI(); --3.141593`
POW(x,y)	返回 x 的 y 次方	2 的 3 次方： `SELECT POW(2, 3); -- 8`
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方： `SELECT POWER(2, 3); -- 8`
RADIANS(x)	将角度转换为弧度	180 度转换为弧度： `SELECT RADIANS(180); -- 3.1415926535898`
RAND()	返回 0 到 1 的随机数	`SELECT RAND(); --0.93099315644334`
ROUND(x)	返回离 x 最近的整数,四舍五入	`SELECT ROUND(1.23456); --1`
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	`SELECT SIGN(-10); -- (-1)`
SIN(x)	求正弦值（参数是弧度）	`SELECT SIN(RADIANS(30)); -- 0.5`
SQRT(x)	返回 x 的平方根	25 的平方根： `SELECT SQRT(25); -- 5`
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和： `SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;`
TAN(x)	求正切值（参数是弧度）	`SELECT TAN(1.75); -- (-5.52037992250933)`
TRUNCATE(x, y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	`SELECT TRUNCATE(1.23456, 3); -- 1.234`

日期函数

函数名	描述	实例
ADDDATE(d, n)	计算起始日期 d 加上 n 天的日期	`SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); --2017-06-25`
ADDTIME(t, n)	n 是一个时间表达式，时间 t 加上时间表达式 n	加 5 秒： `SELECT ADDTIME('2011-11-11 11:11:11', 5); --2011-11-11 11:11:16` 添加 2 小时, 10 分钟, 5 秒： `SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -- 2020-06-15 11:44:26`
CURDATE()	返回当前日期	`SELECT CURDATE(); -- 2018-09-19`
CURRENT_DATE()	返回当前日期	`SELECT CURRENT_DATE(); -- 2018-09-19`
CURRENT_TIME	返回当前时间	`SELECT CURRENT_TIME(); -- 19:59:02`
CURRENT_TIMESTAMP()	返回当前日期和时间	`SELECT CURRENT_TIMESTAMP(); -- 2018-09-19 20:57:43`
CURTIME()	返回当前时间	`SELECT CURTIME(); -- 19:59:02`
DATE()	从日期或日期时间表达式中提取日期值	`SELECT DATE("2017-06-15"); -- 2017-06-15`
DATEDIFF(d1, d2)	计算日期 d1 和 d2 之间相隔的天数	`SELECT DATEDIFF('2001-01-01', '2001-02-02'); -- (-32)`
DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期	`SELECT ADDDATE('2011-11-11 11:11:11', 1); -- 2011-11-12 11:11:11（默认是天）` `SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE); -- 2011-11-11 11:16:11 （TYPE 的取值与上面那个列出来的函数类似）`
DATE_FORMAT(d, f)	按表达式 f 的要求显示日期 d	`SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r'); -- 2011-11-11 11:11:11 AM`
DATE_SUB(date, INTERVAL expr type)	函数从日期减去指定的时间间隔。	Orders 表中 OrderDate 字段减去 2 天： `SELECT OrderId, DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders;`
DAY(d)	返回日期值 d 的日期部分	`SELECT DAY("2017-06-15"); -- 15`
DAYNAME(d)	返回日期 d 是星期几，如 Monday, Tuesday	`SELECT DAYNAME('2011-11-11 11:11:11'); --Friday`
DAYOFMONTH(d)	计算日期 d 是本月的第几天	`SELECT DAYOFMONTH('2011-11-11 11:11:11'); --11`
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期一，以此类推	`SELECT DAYOFWEEK('2011-11-11 11:11:11'); --6`
DAYOFYEAR(d)	计算日期 d 是本年的第几天	`SELECT DAYOFYEAR('2011-11-11 11:11:11'); --315`
EXTRACT(type FROM d)	从日期 d 中获取指定的值，type 指定返回的值。 type 可取值为： MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH	`SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11'); -- 11`
FROM_DAYS(n)	计算从 0000 年 1 月 1 日开始 n 天后的日期	`SELECT FROM_DAYS(1111); -- 0003-01-16`
HOUR(t)	返回 t 中的小时值	`SELECT HOUR('1:2:3'); -- 1`
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	`SELECT LAST_DAY("2017-06-20"); -- 2017-06-30`
LOCALTIME()	返回当前日期和时间	`SELECT LOCALTIME(); -- 2018-09-19 20:57:43`
LOCALTIMESTAMP()	返回当前日期和时间	`SELECT LOCALTIMESTAMP(); -- 2018-09-19 20:57:43`
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期	`SELECT MAKEDATE(2017, 3); -- 2017-01-03`
MAKETIME(hour, minute, second)	组合时间，参数分别为小时、分钟、秒	`SELECT MAKETIME(11, 35, 4); -- 11:35:04`
MICROSECOND(date)	返回日期参数所对应的微秒数	`SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -- 23`
MINUTE(t)	返回 t 中的分钟值	`SELECT MINUTE('1:2:3'); -- 2`
MONTHNAME(d)	返回日期当中的月份名称，如 November	`SELECT MONTHNAME('2011-11-11 11:11:11'); -- November`
MONTH(d)	返回日期 d 中的月份值，1 到 12	`SELECT MONTH('2011-11-11 11:11:11'); --11`
NOW()	返回当前日期和时间	`SELECT NOW(); -- 2018-09-19 20:57:43`
PERIOD_ADD(period, number)	为年-月组合日期添加一个时段	`SELECT PERIOD_ADD(201703, 5); -- 201708`
PERIOD_DIFF(period1, period2)	返回两个时段之间的月份差值	`SELECT PERIOD_DIFF(201710, 201703); -- 7`
QUARTER(d)	返回日期 d 是第几季节，返回 1 到 4	`SELECT QUARTER('2011-11-11 11:11:11'); -- 4`
SECOND(t)	返回 t 中的秒钟值	`SELECT SECOND('1:2:3'); -- 3`
SEC_TO_TIME(s)	将以秒为单位的时间 s 转换为时分秒的格式	`SELECT SEC_TO_TIME(4320); -- 01:12:00`
STR_TO_DATE(string, format_mask)	将字符串转变为日期	`SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -- 2017-08-10`
SUBDATE(d, n)	日期 d 减去 n 天后的日期	`SELECT SUBDATE('2011-11-11 11:11:11', 1); --2011-11-10 11:11:11 (默认是天)`
SUBTIME(t, n)	时间 t 减去 n 秒的时间	`SELECT SUBTIME('2011-11-11 11:11:11', 5); --2011-11-11 11:11:06 (秒)`
SYSDATE()	返回当前日期和时间	`SELECT SYSDATE(); -- 2018-09-19 20:57:43`
TIME(expression)	提取传入表达式的时间部分	`SELECT TIME("19:30:10"); -- 19:30:10`
TIME_FORMAT(t, f)	按表达式 f 的要求显示时间 t	`SELECT TIME_FORMAT('11:11:11', '%r'); -- 11:11:11 AM`
TIME_TO_SEC(t)	将时间 t 转换为秒	`SELECT TIME_TO_SEC('1:12:00'); -- 4320`
TIMEDIFF(time1, time2)	计算时间差值	`SELECT TIMEDIFF("13:10:11", "13:10:10"); -- 00:00:01`
TIMESTAMP(expression, interval)	单个参数时，函数返回日期或日期时间表达式；有 2 个参数时，将参数加和	`SELECT TIMESTAMP("2017-07-23", "13:10:11"); -- 2017-07-23 13:10:11`
TO_DAYS(d)	计算日期 d 距离 0000 年 1 月 1 日的天数	`SELECT TO_DAYS('0001-01-01 01:01:01'); -- 366`
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	`SELECT WEEK('2011-11-11 11:11:11'); -- 45`
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星期二	`SELECT WEEKDAY("2017-06-15"); -- 3`
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	`SELECT WEEKOFYEAR('2011-11-11 11:11:11'); -- 45`
YEAR(d)	返回年份	`SELECT YEAR("2017-06-15"); -- 2017`
YEARWEEK(date, mode)	返回年份及第几周（0 到 53），mode 中 0 表示周天，1 表示周一，以此类推	`SELECT YEARWEEK("2017-06-15"); -- 201724`

高级函数

函数名	描述	实例
BIN(x)	返回 x 的二进制编码	15 的 2 进制编码： `SELECT BIN(15); -- 1111`
BINARY(s)	将字符串 s 转换为二进制字符串	`SELECT BINARY "GOOGLE"; -- GOOGLE`
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result	END \| CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。	`SELECT CASE` `WHEN 1 > 0` `THEN '1 > 0'` `WHEN 2 > 0` `THEN '2 > 0'` `ELSE '3 > 0'` `END` `-- 1 > 0`
CAST(x AS type)	转换数据类型	字符串日期转换为日期： `SELECT CAST("2017-08-29" AS DATE); -- 2017-08-29`
COALESCE(expr1, expr2, ...., expr_n)	返回参数中的第一个非空表达式（从左向右）	`SELECT COALESCE(NULL, NULL, NULL, 'baidu.com', NULL, 'google.com'); -- baidu.com`
CONNECTION_ID()	返回唯一的连接 ID	`SELECT CONNECTION_ID(); -- 4292835`
CONV(x, f1, f2)	返回 f1 进制数变成 f2 进制数	`SELECT CONV(15, 10, 2); -- 1111`
CONVERT(s USING cs)	函数将字符串 s 的字符集变成 cs	`SELECT CHARSET('ABC') --utf-8` `SELECT CHARSET(CONVERT('ABC' USING gbk)) --gbk`
CURRENT_USER()	返回当前用户	`SELECT CURRENT_USER(); -- root@localhost`
DATABASE()	返回当前数据库名	`SELECT DATABASE(); -- testdb`
IF(expr, v1, v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	`SELECT IF(1 > 0,'正确','错误') --正确`
IFNULL(v1, v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	`SELECT IFNULL(NULL, 'Hello Word') --Hello Word`
ISNULL(expression)	判断表达式是否为 NULL	`SELECT ISNULL(NULL); --1`
LAST_INSERT_ID()	返回最近生成的 AUTO_INCREMENT 值	`SELECT LAST_INSERT_ID(); --6`
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等返回 NULL，否则返回 expr1	`SELECT NULLIF(25, 25); --`
SESSION_USER()	返回当前用户	`SELECT SESSION_USER(); -- root@localhost`
SYSTEM_USER()	返回当前用户	`SELECT SYSTEM_USER(); -- root@localhost`
USER()	返回当前用户	`SELECT USER(); -- root@localhost`
VERSION()	返回数据库的版本号	`SELECT VERSION() -- 5.6.34`

运算符

本章节我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：

算术运算符
比较运算符
逻辑运算符
位运算符

算术运算符

MySQL 支持的算术运算符包括:

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

在除法运算和模运算中，如果除数为 0，将是非法除数，返回结果为 NULL。

加

mysql> SELECT 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

减

mysql> SELECT 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+

乘

mysql> SELECT 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+

除

mysql> SELECT 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+

商

mysql> SELECT 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+

取余

mysql> SELECT 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min && <=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个 NULL 值是否相等	两个操作码均为 NULL 时，其所得值为 1；而当一个操作码为 NULL 时，其所得值为 0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

等于

mysql> SELECT 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+
mysql> SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

不等于

mysql> SELECT 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+

安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0 而不为 NULL。

mysql> SELECT 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+
mysql> SELECT NULL=NULL;
+-----------+
| NULL=NULL |
+-----------+
|      NULL |
+-----------+
mysql> SELECT NULL<=>NULL;
+-------------+
| NULL<=>NULL |
+-------------+
|           1 |
+-------------+

小于

mysql> SELECT 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+

小于等于

mysql> SELECT 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+

大于

mysql> SELECT 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+

大于等于

mysql> SELECT 2>=3;
+------+
| 2>=3 |
+------+
|    0 |
+------+

BETWEEN

mysql> SELECT 5 BETWEEN 1 AND 10;
+--------------------+
| 5 BETWEEN 1 AND 10 |
+--------------------+
|                  1 |
+--------------------+

mysql> SELECT 5 IN (1, 2, 3, 4, 5);
+------------------+
| 5 IN (1, 2, 3, 4, 5) |
+----------------------+
|                    1 |
+----------------------+

NOT IN

mysql> SELECT 5 NOT IN (1, 2, 3, 4, 5);
+--------------------------+
| 5 NOT IN (1, 2, 3, 4, 5) |
+--------------------------+
|                        0 |
+--------------------------+

IS NULL

mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+
mysql> SELECT 'a' IS NULL;
+-------------+
| 'a' IS NULL |
+-------------+
|           0 |
+-------------+

IS NOT NULL

mysql> SELECT NULL IS NOT NULL;
+------------------+
| NULL IS NOT NULL |
+------------------+
|                0 |
+------------------+
mysql> SELECT 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+

mysql> SELECT '12345' LIKE '12%';
+--------------------+
| '12345' LIKE '12%' |
+--------------------+
|                  1 |
+--------------------+
mysql> SELECT '12345' LIKE '12_';
+--------------------+
| '12345' LIKE '12_' |
+--------------------+
|                  0 |
+--------------------+

REGEXP

mysql> SELECT 'beijing' REGEXP 'jing';
+-------------------------+
| 'beijing' REGEXP 'jing' |
+-------------------------+
|                       1 |
+-------------------------+
mysql> SELECT 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

与

mysql> SELECT 2 AND 0;
+---------+
| 2 AND 0 |
+---------+
|       0 |
+---------+
+---------+
mysql> SELECT 2 AND 1;
+---------+
+---------+
| 2 AND 1 |
+---------+
|       1 |
+---------+

或

mysql> SELECT 2 OR 0;
+--------+
| 2 OR 0 |
+--------+
|      1 |
+--------+
mysql> SELECT 2 OR 1;
+--------+
| 2 OR 1 |
+--------+
|      1 |
+--------+
mysql> SELECT 0 OR 0;
+--------+
| 0 OR 0 |
+--------+
|      0 |
+--------+
mysql> SELECT 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+
mysql> SELECT NULL OR 1;
+-----------+
| NULL OR 1 |
+-----------+
|         1 |
+-----------+

非

mysql> SELECT NOT 1;
+-------+
| NOT 1 |
+-------+
|     0 |
+-------+
mysql> SELECT !0;
+----+
| !0 |
+----+
|  1 |
+----+

异或

mysql> SELECT 1 XOR 1;
+---------+
| 1 XOR 1 |
+---------+
|       0 |
+---------+
mysql> SELECT 0 XOR 0;
+---------+
| 0 XOR 0 |
+---------+
|       0 |
+---------+
mysql> SELECT 1 XOR 0;
+---------+
| 1 XOR 0 |
+---------+
|       1 |
+---------+
mysql> SELECT NULL XOR 1;
+------------+
| NULL XOR 1 |
+------------+
|       NULL |
+------------+
mysql> SELECT 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符号	作用
&	按位与
\|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

按位与

mysql> SELECT 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+

按位或

mysql> SELECT 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+

按位异或

mysql> SELECT 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

按位取反

mysql> SELECT ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+

按位右移

mysql> SELECT 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+

按位左移

mysql> SELECT 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

最低优先级为： :=。

优先级顺序	运算符
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	!

关键字

关键字汇总

关键字	关键字	关键字
ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONNECTION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GOTO	GRANT	GROUP
HAVING	HIGH_PRIORITY	HOUR_MICROSECOND
HOUR_MINUTE	HOUR_SECOND	IF
IGNORE	IN	INDEX
INFILE	INNER	INOUT
INSENSITIVE	INSERT	INT
INT1	INT2	INT3
INT4	INT8	INTEGER
INTERVAL	INTO	IS
ITERATE	JOIN	KEY
KEYS	KILL	LABEL
LEADING	LEAVE	LEFT
LIKE	LIMIT	LINEAR
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_MICROSECOND	MINUTE_SECOND	MOD
MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR ORDER
OUT	OUTER	OUTFILE
PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	RANGE
READ	READS	REAL
REFERENCES	REGEXP	RELEASE
RENAME	REPEAT	REPLACE
REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE
SCHEMA	SCHEMAS	SECOND_MICROSECOND
SELECT	SENSITIVE	SEPARATOR
SET	SHOW	SMALLINT
SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
TABLE	TERMINATED	THEN
TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER
TRUE	UNDO	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY
VARCHAR	VARCHARACTER	VARYING
WHEN	WHERE	WHILE
WITH	WRITE	X509
XOR	YEAR_MONTH	ZEROFILL

执行顺序

在 SQL 语句中每个关键字都会按照顺序往下执行，而每一步操作，会生成一个虚拟表，最后的虚拟表就是最终结果。

基本 SQL 语句如下：

(8)SELECT (9)DISTINCT <select_list>
(1)FROM <left_table>
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH{CUBE|ROLLUP}
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
(11)LIMIT <limit_number>

执行顺序：

FROM：对 FROM 左边的表和右边的表计算笛卡尔积，产生虚表 VT1；
ON：对虚拟表 VT1 进行 ON 筛选，只有那些符合条件的行才会被记录在虚拟表 VT2 中；
JOIN：如果是 OUT JOIN，那么将保留表中（如左表或者右表）未匹配的行作为外部行添加到虚拟表 VT2 中，从而产生虚拟表 VT3；
WHERE：对虚拟表 VT3 进行 WHERE 条件过滤，只有符合的记录才会被放入到虚拟表 VT4；
GROUP BY：根据 GROUP BY 子句中的列，对虚拟表 VT4 进行分组操作，产生虚拟表 VT5；
CUBE | ROLLUP：对虚拟表 VT5 进行 CUBE 或者 ROLLUP 操作，产生虚拟表 VT6；
HAVING：对虚拟表 VT6 进行 HAVING 条件过滤，只有符合的记录才会被插入到虚拟表 VT7 中；
SELECT：执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中；
DISTINCT：对虚拟表 VT8 中的记录进行去重，产生虚拟表 VT9；
ORDER BY：将虚拟表 VT9 中的记录按照进行排序操作，产生虚拟表 VT10；
LIMIT：取出指定行的记录，产生虚拟表 VT11，并将结果返回。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，`^` 也匹配 `\n` 或 `\r` 之后的位置。
$	匹配输入字符串的结束位置。如果设置了 RegExp 对象的 Multiline 属性，`$` 也匹配 `\n` 或 `\r` 之前的位置。
.	匹配除 `\n` 之外的任何单个字符。要匹配包括 `\n` 在内的任何字符，请使用像 `[.\n]` 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如，`[abc]` 可以匹配 `plain` 中的 `a`。
[^...]	负值字符集合。匹配未包含的任意字符。例如，`[\^abc]` 可以匹配 `plain` 中的`p`。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，`z`\|`food` 能匹配 `z` 或 `food`。`(z`\|`f)ood` 则匹配 `zood` 或 `food`。
*	匹配前面的子表达式零次或多次。例如，`zo` 能匹配 `z` 以及 `zoo`。`` 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，`zo+` 能匹配 `zo` 以及 `zoo`，但不能匹配 `z`。`+` 等价于 `{1,}`。
{n}	`n` 是一个非负整数。匹配确定的 `n` 次。例如，`o{2}` 不能匹配 `Bob` 中的 `o`，但是能匹配 `food` 中的两个 `o`。
{n, m}	`m` 和 `n` 均为非负整数，其中 `n <= m`。最少匹配 `n` 次且最多匹配 `m` 次。