MySQL技术内幕——使用SQL管理数据
使用SQL管理数据
服务器的SQL模式
MySQL服务器的SQL模式对SQL语句的执行会产生如下几个方面的影响：
- 在服务器上有一个名为 sql_mode 的系统变量，可以用它来配置这个模式。
- 可以全局性地设置这个变量，让它对所有客户端产生影响；
- 也可以让每个客户端自己更改这个模式，从而只对其自己与（它所连接的那个）服务器的会话产生影响。
几种可能的SQL模式设置值：
- STRICT_ALL_TABLES 和 STRICT_TRANS_TABLES 将启用“严格”模式。
在严格模式下，MySQL服务器将会更加严格地对待接受到的坏（bad）数据。 - TRADITIONAL 是一个组合模式。
可以让MySQL服务器在处理坏数据时，其行为更接近于那些传统的SQL服务器。 - ANSI_QUOTES 会告诉MySQL服务器，把双引号识别为一个标识符引用字符。
- PIPES_AS_CONCAT 会让服务器把 “ | | ” 当成一个标准的SQL字符串连接运算符，而不会把它当成是OR运算符的同义词。
- ANST 也是一个组合模式。它会同时启用 ANST_QUOTES、PIPES_AS_CONCAT 和其他的几个模式值，其结果是让MySQL服务器的行为比它的默认运行状态更接近于标准SQL。
在设置SQL模式时，需要指定一个由单个模式值或多个模式值（用逗号分隔）构成的值，或者指定一个空串，用以清除该值。
模式值不区分大小写。
想在启动服务器时设置SQL模式，那么可以在 mysql 命令行，或者在某个选项文件里设置系统变量 sql_mode 。
sql-mode = "TRADITIONAL"
sql-mode = "ANSI_QUOTES, PIPES_AS_CONCAT"
要在运行时更改SQL模式，可以使用一条SET语句来设置 sql_mode 系统变量。
SET sql_mode = 'TRADITIONAL'；
要设置全局性的SQL模式，则需要加上 GLOBAL 关键字：
SET GLOBAL sql_mode = 'TRADITIONAL'；
设置全局变量需要具备 SUPER 管理权限。
想知道当前会话或全局的SQL模式值，则可以使用如下语句：
SELECT @@ SESSION.sql_mode;
SELECT @@ GLOBAL.sql_mode;
其返回值由当前设置为启用的所有模式构成，在两个模式之间以逗号隔开。
如果当前没有启用任何模式，则返回一个空值。
MySQL的标识符语法和命名规则
在引用数据库的各个元素时，相应的标识符必须遵从以下几条规则。
-
（1）标识符里的字符要合法。
不加引号的标识符可以由大小写形式的字母 a-z、数字 0-9、美元符号、下划线，以及范围在 U+0080 到 U+FFFF 之间的 Unicode 扩展字符构成。
标识符 的第一个字符可以是标识符所允许的任何一种字符，其中包括数字。
不加引号的标识符不允许完全由数字字符构成。
MySQL支持标识符以数字字符开头的做法。
标识符可以用反映“ ` ”引起来，它支持使用除 NUL 字节和 Unicode 补充字符（范围在 U+10000 及以上）以外的任何字符：CREATE TABLE `my table` (`my-int-column` INT); -
（2）服务器的SQL模式。
若启用了SQL的 ANSI_QUOTES 模式，就可以使用双引号将标识符引起来（也可以使用反引号）。CREATE TABLE "my table" ("my-int-column" INT);启用 ANSI_QUOTES 会有副作用，即字符串文字量必须使用单引号引起来。
如果使用了双引号，那么服务器会将这个值解释为标识符，而不会把它当作一个字符串。
内建函数的名字通常都不是保留字，因此可以不加引号之间将其用作标识符。
若启用了 IGNORE_SPACE 模式，那么函数名就会变成保留字。（想要把它们当作标识符，则必须使用引号将它们引起来）。 -
（3）标识符的长度。
大部分标识符的最大长度是 64 个字符。
别名的最大长度是 256 个字符。 -
（4）标识符限定符。
根据具体的上下文，标识符可能需要加以限定，以明确它所引用的是什么内容。
想要引用数据库，只需直接指定其名字即可：USE db_name; SHOW TABLES FROM db_namae;有两种选择，引用表：
- 使用一个完全限定的表名，即用数据库标识符和表标识符共同指定：
SHOW COLUMNS FROM db_name.tbl_name; SELECT * FROM db_name.tbl_name; - 表标识符子句可以引用默认（当前）数据库里的表。如果 sampdb 是默认数据库，那么下面两条语句是等效的：
SELECT * FROM member; SELECT * FROM sampdb.member;
- 使用一个完全限定的表名，即用数据库标识符和表标识符共同指定：
对于表列的引用，有如下3中选择。
- 使用完全限定的列名：db_name.tbl_name.col_name。
- 对于默认数据库中命名表里的某一个列，可以使用部分限定名：tbl_name.col_name。
- 只简单地写一个无限定名来引用具体环境上下文所确定的那个表： col_name。
如果在引用一个完全限定名时使用了引号，那么需要给该名字里的每一个标识符分别加上引号。
SELECT * FROM `sampdb.member` WHERE `sampdb.member.member_id` > 100;
如果需要把某个保留字用作标识符，那么必须给它加上引号。但当这个保留字紧跟在一个句号限定符后面时，则不能这样做。
SQL语句的大小写规则
SQL语句中的大小写规则因语句各个组成元素的不同而有所差异，同时还取决于你正引用的内容和MySQL服务器主机所允许的操作系统。
-
（1）SQL关键字和函数名。
不区分大小写，可以为任意的大小写组合。 -
（2）数据库名、表名和视图名。
MySQL使用了服务器主机上的底层文件系统所包含的目录和文件来表示数据库和表。
库名和表名的默认大小写取决于服务器主机上的操作系统在文件名方面的规定。
Windows系统的文件名不区分大小写，运行在上面的MySQL服务器也不区分库名和表名的大小写。
运行于Unix主机上的MySQL服务器，通常需要区分库名和表名的大小写，因为Unix系统区分大小写。
对于Mac OS X 平台，其扩展文件系统中的名字是个例外，它们不区分大小写。
MySQL会使用一个文件来表示一个视图，所以刚才与表有关的讨论也同样适用于视图。 -
（3）存储程序的名字。
存储函数、存储过程和时间的名字都不区分大小写。
触发器的名字要区分大小写，这一点与标准SQL的行为有所不同。 -
（4）列名和索引名。
在MySQL里，列名和索引名都不区分大小写。 -
（5）别名的名字。
默认情况下，表的别名要区分大小写。
可以使用任意的大小写组合（大写、小写或大小写混用）来指定一个别名，但如果需要在同一条语句里多次用到同一个别名，则必须让它们保持同样的大小写组合。 -
（6）字符串值。
字符串值是否区分大小写，具体取决于它是二进制还是非二进制串，并且对于非二进制串，还取决于字符集的排序规则。
对于文字串和字符串类型列的内容，这一点也同样成立。
避免大小写问题，可行的办法是：先选定一种大小写方案，然后一直按照该方案去创建数据库和表。
不管系统是否会区分数据库名和表名的大小写，在给定的查询语句里都必须使用与之相同的大小写来引用它们。
字符集支持
MySQL支持多种字符集，而且允许在服务器、数据库、表、列和字符串常量等不同层次单独指定字符集。
此外，还可以显式地指定排序规则。
可以找出MySQL具有都支持哪些字符集和排序规则，也可以将数据从一种字符集转换为另一种。
MySQL提供了以下这些字符集特性。
-
MySQL服务器允许同时使用多种字符集。
-
一个给定的字符集可以有一种或多种排序规则。
-
支持Unicode的字符集有：utf8 和 ucs2 字符集，它们包括基本多文种平面（BMP）字符；以及 utf16 、utf32 和 utf8mb4 字符集，它们包括BMP字符和补充字符。
MySQL5.6.1 增加了 utf16e。该字符集与 utf16很像，主要差异在于其使用的编码是低字节优先，而非高字节优先。 -
可以分别在服务器、数据库、表、列和字符串常量等这些层次上指定字符集。
- MySQL服务器有一个默认字符集。
- 可以使用 CREATE DATABASE 语句来设置数据库的字符集，使用 ALTER DATABASE 语句来更改它。
- CREATE TABLE 和 ALTER TABLE 有专门的子句，用于设定表和列的字符集。
- 字符串常量的字符集既可以通过上下文来确定，也可以显示指定。
-
还有几个函数和运算符可用来将一些单独的值从一种字符集转换成另一种。
CHARSET() 函数可返回某个给定值的字符集。 -
SHOW 语句和 INFORMATION_SCHEMA 库里的数据表能提供与可用字符集和可用排序规则相关的信息。
-
当更改某个索引过的字符列时，MySQL服务器会自动对索引进行重新排序。
指定字符集
字符集和排序规则可以在多个层次进行设定。
服务器的默认字符集和排序规则是在编译时构建好的。
可以在服务器启动或运行时，通过设系统变量 character-srt-server 和 collation-server 来改写它们。
在用于创建数据库和表的SQL语句里，有两个子句可用于指定数据库、表和列的字符集和排序规则，即：
CHARACTER SET charset
COLLATE collation
CHARACTER SET 可用 CHARSET来代替。
charset 是服务器支持的某个字符集的名字，而 collation 是该字符集的某种排序规则的名字。
这两个子句可同时使用，也可分开使用。
在同时使用它们两时，必须保证排序规则的名字与字符集相兼容。
如果只给出了 CHARACTER SET 子句，则表示使用默认排序规则。
如果只给出了COLLATE子句，则使用由给定排序规则的名字的开头部分所确定的那个字符集。
也可通过使用 COLLATE 运算符，按照特定的排序规则对字符串值进行排序。
确定可用字符集和当前设置
想知道有哪些字符集和排序规则可供选用，可使用下面这些语句：
SHOW CHARACTER SET;
SHOW COLLATION;
这两条语句都支持 LIKE 子句，它可以把查询结果的范围缩小到那些字符集或排序规则的名字可以与某个给定的模式相匹配。
排序规则的名字总是以字符集的名字作为开头。
每种字符集都最少拥有一种排序规则，并且其中有一种即为它的默认排序规则。
与可用字符集和排序规则有关的信息，还可以从 INFORMATION_SCHEMA 库的 CHARACTER_SETS 表和 COLLATIONS 表里获得。
如果想显示服务器当前的字符集和排序规则设置，则可以使用SHOW VARIABLES。
Unicode 支持
为不同的语言制定了不同的字符编码方案。
Unicode的目标是提供一种统一的字符编码系统，让所有语言的字符集都能以一种统一的方式进行表示。
utf8 与 ucs2 这两种类型的Unicode字符集，都只包括了BMP里定义的字符，即最多只有65536个字符。
它们都不支持BMP之外的那些补充字符。
-
usc2 字符集与 Unicode 的UCS-2 编码方案相对应。它使用2个字节来表示1个字符，并且最高有效字节优先。
USC是通用字符集的缩写。 -
utf8 字符集采用了一种长度可变的格式，使用1到3个字节来表示1个字符。
-
字符集 utf16 和 utf32 类似于 usc2，只是它们增加了对补充字符的支持。
-
utf8mb4 字符集包含了所有的 utf8 字符（其中，每个字符占1到3个字节），另外也包含了补充字符，其中，每个字条占4个字节。
数据库的选择、创建、删除和更改
USE 用于选定一个默认数据库。
CREATE DATABASE 用于创建数据库。
DROP DATABASE 用于删除数据库。
ALTER DATABASE 用于更改数据库的全局属性。
选择数据库
使用 USE 语句可以选择数据库，并把它指定为MySQL服务器连接的默认（当前）数据库：
USE db_name;
要想选择数据库，必须要具备相应的访问权限；否则，会出现错误提示。
在没有选择数据库的情况下，只要用数据库名来限定表名，就可以引用该库里的表。
当断开与服务器的连接时，针对该会话的那个服务器默认数据库概念也将不复存在。
创建数据库
创建数据库的方法是，使用 CREATE DATABASE 语句：
CREATE DATABASE db_name;
先决条件：这个数据库不能是已存在的，并且必须要拥有CREATE权限。
CREATE DATABASE 语句有几个可选子句。完整语法如下：
CREATE DATABASE [IF NOT EXISTS] db_name
[CHARACTER SET charset] [COLLATE collation];
在默认情况下，试图创建的一个已存在的数据库时，系统会报错。
如果想要避免这类错误，并且只想在给定数据库尚不存在时才创建它，那么可以加上 IF NOT EXISTS 子句：
CREATE DATABASE IF NOT EXISTS db_name;
在默认情况下，服务器的字符集和排序规则将成为新建数据库的默认字符集和排序规则。
显示设置数据库属性的方法是，使用 CHARACTER SET 子句和 COLLATE 子句。
只给出了 CHARACTER SET ，则意味着使用这个给定字符集的默认排序规则。
只给出了 COLLATE，则意味着使用排序规则的名字的开头部分来确定的字符集。
在创建表时，MySQL服务器会在其数据目录下创建一个目录，其名与数据库名相同。
这个新目录被称为数据库目录。
服务器还会在这个目录下创建一个名为 db.opt 的文件，用于存储一些属性，如数据库的字符集和排序规则。
查看现有数据库定义的方法是，使用 SHOW CREATE DATABASE 语句。
删除数据库
如果有DROP权限，那么删除数据库会跟创建数据库一样简单：
DROP DATABASE db_name;
但，它会删掉数据库和其中的所有内容（表、存储例程等）。此时，这个数据库也会永远消失，除非定期对它做过备份，才有可能把它恢复过来。
一个数据库就是MySQL数据目录里的一个目录，这个目录主要用于存放对象，如表、视图和触发器。
更改数据库
如果拥有ALTER/权限，则可以使用 ALTER DATABASE 语句来更改数据库的全局属性。
目前，数据库的全局属性只包含默认字符集和排序规则：
ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];
如果省略了数据库名，那么ALTER DATABASE 会应用到默认数据库。
表的创建、删除、索引和更改
MySQL允许使用 CREATE TABLE 语句和 DROP TABLE 语句来创建、删除表，使用 ALTER TABLE 语句更改其结构。
CREATE INDEX 语句和 DROP INDEX语句则可以用来为已有表增加或删除索引。
存储引擎的特性
有几种存储引擎的名字还有同义词。
例如，MRG_MyISAM 和 NDBCLUSTER 分别是 MERGE 和 NDB 的同义词。
查看有哪些存储引擎可用
查出服务器都知道哪些存储引擎的方法是，使用 SHOW ENGINES 语句。
Support 栏的值为 YES 或 NO，分别代表了该存储引擎是否可用；
DISABLED 的意思是该存储引擎存在，但它已被关闭；
DEFAULT 表示它是服务器默认使用的存储引擎。
Transaction 栏表示的是存储引擎是否支持事务处理。
XA 和 Savepoints 栏表示的是存储引擎是否支持分布式事务和部分事务回滚。
表的磁盘存储方式
每次在创建表时，MySQL都会创建一个磁盘文件，用于保存该表的格式（即它的定义）。
这个格式文件的基本名与表名相同，扩展名为 .frm。
服务器会在包含这个表的那个数据库所对应的数据库目录里，创建这个文件。
各个存储引擎还可能会为表再创建几个特定的文件，用以存储表的内容。
对于某个给定的表，与之相关的所有文件都集中存放在包含这个表的那个数据库所对应的目录里。
对某些存储引擎而言，格式文件是与某特定表相关联的唯一文件。
其他存储引擎可能会把表的内容保存到磁盘上的其他地方，或者可能使用一个或多个表空间（由多个表共享的存储区域）。
- MEMORY 存储引擎会把表存放在内存里，而非磁盘上。
- 默认情况下，InnoDB 引擎会把表的数据和索引存储在它的系统表空间里。
InnoDB 存储引擎
具有以下几项功能：
- 其表在执行提交和回滚操作时是事务安全的。可以通过创建保存点来实现部分回滚。
- 在系统崩溃后可以自动恢复。
- 外键和引用完整性支持，包括级联删除和更新。
- 基于行级别的锁定和多版本化，使得 在执行同时包含有检索和更新操作的组合条件查询时，可以表现出很好的并发性能。
- 从MySQL 5.6 开始，InnoDB 支持全文搜索和 FULLTEXT 索引。
MyISAM 存储引擎
MyISAM 存储引擎提供了如下这些功能。
- 当保存连续相似的字符串索引值时，它会对键进行压缩。
- 与其他存储引擎相比，它为 AUTO_INCREATMENT 列提供了更多的功能。
- 每个 MyISAM 表都有一个标志，它会在执行表检查操作时被设置。 MyISAM 表还有一个标志，用于表明该表在上次使用后是否被正常关闭。
- 支持全文检索和 FULLTEXT索引。
- 支持空间数据类型和 SPATIAL 索引。
MEMORY 存储引擎
MEMORY 存储引擎会把表存储在内存里，并且这些表的行长度固定不变。
这两个特性使得它的相关操作非常快。
在某种意义上，MEMORY 表是临时性的，当服务器掉电时，其内容也会消失。
也就是说，MEMORY 表在服务器重启之后依然存在，只是它们的内容为空。
MEMORY 表的以下几个特点使它们比其他类型的表更易处理，因此速度也更快。
- 默认情况下，MEMORY 表使用的是散列索引——这种索引对于“相等比较”非常快，但对于“范围比较”非常慢。
因此，散列索引只适合用于相等运算符“=”和“<=>”的比较操作，不太适合像“<”或“>”这样的比较运算符。
同样地，散列索引也不适合用在 ORDER BY 子句里。 - 为更便于处理，存储在 MEMORY 表里的行使用长度固定不变的格式。（如 VARCHAR 在MySQL内部被当作是一种长度固定不变的 CHAR 类型，可以在 MEMORY 表里使用。
NDB 存储引擎
NDB是MySQL的集群存储引擎。
对于这个存储引擎，MySQL服务器实际上变成了一个其他进程（它们提供了对NDB表的访问）的集群客户端。
集群节点会处理彼此间的通信，从而在内存中实现对表的管理。
为了实现冗余，这些表会在集群进程之间被复制。
内存存储提供了高性能，而集群机制则提供了高可用性，因为即使某个节点发生了故障，整个系统也不会崩溃。
其他存储引擎
- ARCHIVE 引擎提供了数据归档存储功能。
- BLACKOLE 引擎所创建的表，其写操作会被忽略，读操作是返回空内容。
- CSV 引擎在存储数据时，会用逗号分隔值。
- FEDREATED 引擎提供了访问由其他MySQL服务器进行管理的表的能力。
- MERGE 引擎提供了一种把多个MyISAM表合并为一个逻辑单元的手段。
创建表
创建表的方法，使用CREATE TABLE 语句。
必须要拥有表的 CREATE 权限。
在最简单的情况下， CREATE TABLE 语句只须指定表名和其中的各个列即可。
在创建表时，除了各列的定义，还可以指定它要如何进行索引。
另一种做法是，先创建一个不带任何索引的表，然后再为它加上索引。
CREATE TABLE 语句的几种重要扩展，它们可以帮助你灵活地构造表。
- 修改存储特性的表选项。
- 只在表不存在时才创建它。
- 临时表——在客户端会话结束时服务器会自动删除它们。
- 根据另一个表或是根据某次 SELECT 查询的结果来创建表。
- 使用分区表。
表选项
如果想要更改某个表的存储特性，可以在 CREATE TABLE语句中的右括号之后加上一个或多个表选项。
一个表选项是 ENGINE = engine_name，它可以为表指定要用的存储引擎。
存储引擎的名字不区分大小写。
要保证CREATE TABLE 语句没有产生任何警告消息——它们经常是提示所指定的存储引擎不可用，并且提示使用了默认的存储引擎来替代。
如果想让 MySQL在指定的存储引擎不可用时产生一个错误（而不是使用默认的存储引擎来代替），则需要启用 SQL 的 NO_ENGINE_SUBSTITUTION 模式。
如果想要确定某个表使用的是哪一种存储引擎，那么可以执行SHOW CREATE TABLE 语句，并查看其输出内容里的ENGINE选项。
存储引擎还可以在 SHOW TABLE STATUS 语句或 INFORMATION_SCHEMA.TABLES 表的输出内容里看到。
选项 MAX_ROWS 和 AVG_ROW_LENGTH 可用于调整 MyISAM 表的大小。
如果想要修改某个已有表的存储特性，可以在 ALTER TABLE 语句里使用表选项。
暂时性表的创建
如果指向创建原本不存在的表，可以使用CREATE TABLE IF NOT EXIST 语句。
TEMPORARY 表
在表创建语句里加上 TEMPORARY 关键字，服务器将创建出一个临时表，它会在你与服务器的会话终止时自动消失。（注意，最好在用完它时立即显式地删除它，这可以让服务器尽快释放与之有关的资源）
若要创建使用某种引擎的临时表，则可以为 CREATE TEMPORARY TABLE 语句加上 ENGINE 表选项。
TEMPORARY 表只对创建该表的客户端可见。不同客户端创建具有相同名字的TEMPORARY 表时，不会发生任何冲突。
TEMPORARY 表的名字可以与某个已有的永久表的名字相同。
在考虑是否需要使用TEMPORARY 表时，注意以下几个因素：
- 如果客户端程序在于服务器的连接意外断开时自动重建连接，那么在你重新连接之后，上次创建的那个TEMPORARY 表将不复存在。
- 因为TEMPORARY 表只在创建它们的会话里是可见的，所以它们与连接池机制一起使用时没什么作用。
- 如果使用连接池或永久性连接，那么当你的应用程序终止时，你与MySQL服务器之间的连接不一定会被关闭。
根据其他表或查询结果来创建表
MySQL提供了两条语句，可用于根据其他表或根据查询结果创建新表。
-
CREATE TABLE … LIKE 会根据原有表创建一个新表，该表是原有表的一个空副本。
它会把原有表的结构丝毫不差地赋值过来，而且会保留各列的所有属性。
索引结构也照样会被复制。
注意，CREATE TABLE … LIKE 不能根据原有表的列子集创建出新表，它也不能使用除原有表以外的任何其他表里的列。 -
CREATE TABLE … SELECT 可以根据任意一条 SELECT 语句的查询结果创建一个新表。
默认情况下，这条语句不会复制所有的列属性。
根据查询数据来自创建表，并把数据填入其中，这种做法也不会把原有表里任何索引复制过去，因为结果集不会自己索引。
另一方面，CREATE TABLE … SELECT只用一条语句即可完成创建表和填充新表的操作。它也可以用原有表的某个子集来创建新表，并且可以包括来自其他表的列，或者以表达式结果形式创建出来的列。
使用分区表
MySQL支持表分区，从而可以让表的内容分散存储在不同的物理存储位置。
在对表存储进行分区后，得到分区表，使用分区表有很多好处，其中两个好处如下。
- 表存储可以分区在多个设备上，这样可以通过 I/O并行机制来缩短访问时间。
- 优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区。
分区函数可以根据范围、值列表或散列值来分配各行。
- 根据范围来分区。 适用这种方式的情形是，行包括的是一些可划分为多个离散区间的值域，如日期、收入水平或重量。
- 根据列表来分区。 适用这种方式的情形是，每个分区都分别对应于某些明确的列表值，如邮政编码表、电话号码区号、按地理区域划分出的各实体的编号。
- 根据散列值来分区。 适用这种方式的情形是，根据从行键计算出的散列值，把行分布到各个分区。可以自行提供一个散列函数；也可以告知MySQL要使用到的有哪些列，然后它会基于那些列使用内建的散列函数计算出散列值。
分区函数必须具有确定性，这样，在分配行时，才会把相同的输入值分配到同一个分区。
有些函数不适合这个规则，如 RAND() 和 NOW() 。
默认情况下，MySQL会将分区存储在专属于分区表的数据库目录里。
若想将存储分散到其他地方（如放置到不同的物理设备上），则需要用分区选项 DATA_DIRECTORY 和 INDEX_DIRECTORY 。
删除表
DROP
DROP TABLE tbl_name;
MySQL中，DROP TABLE 提供了几种有用的扩展。
-
删除多个表，可把它们依次列在同一条语句里：
DROP TABLE tbl_name1, tbl_name2, ... ; -
删除一个不存在的表时，使其产生一条警告信息，而不是系统报错。可在语句里加上 IF EXISTS 子句。
DROP TABLE IF EXISTS tbl_name;如果语句产生了警告信息，那么可以使用 SHOW WARNINGS 来查看它们。
-
如果只想删除临时表，则可以加上 TEMPORARY 关键字：
DROP TEMPORARY TABLE tbl_name;
索引表
索引是加快访问表内容的基本手段，尤其是在涉及多个表的关联查询里。
存储引擎的索引特性
MySQL提供了多种灵活的索引创建办法。
- 可以对单个列或多个列建立索引。多列索引也被称作复合索引。
- 索引可以只包含唯一值，也可以包含重复值。
- 可以为同一个表创建多个索引，帮助优化对表的不同类型的查询。
- 对于除 ENUM 和 SET 以外的字符串类型，可以利用列的前缀创建索引。
如果想将某个已有表转换成使用另一种存储引擎的，这种存储引擎具有更为合适的索引特性，那么可以使用 ALTER TABLE 语句来实现。
创建索引
MySQL可以创建多种类型的索引。
- 唯一索引。 对单列索引，不允许有重复值出现；对于多列（复合）索引，不允许出现重复的组合值。
- 常规（非唯一性）索引。 它可以让你获得索引的好处，但会出现重复值的情况。
- FULLTEXT 索引。 它可用于完成全文检索。 这种类型的索引只适用于 MyISAM表。
- SPATIAL 索引。 这种索引只适用于包含空间值的 MyISAM 表。
- HASH 索引。 这是 MEMORY 表的默认索引类型，不过可以通过创建 BTREE 索引来改写它。
删除索引
使用 DROP INDEX 或 ALTER TABLE 语句。
若使用 DROP INDEX 语句，则必须给出那个要被删除的索引的名字：
DROP INDEX index_name ON tbl_name;
若使用 DROP INDEX 语句删除 PRIMARY KEY，则必须以带引号标识符的形式给指定名字 PRIMARY：
DROP INDEX 'PRIMARY' ON tbl_name;
等价于：
ALTER TABLE tbl_name DROP INDEX index_name;
ALTER TABLE tbl_name DROP PRIMARY KEY;
更改表结构
ALTER TABLE 语句的语法：
ALTER TABLE tbl_name action [, action] ... ;
其中的每个动作 （action）指的是对表所做的修改。
ALTER TABLE 语句的某些功能：
- （1）更改列的数据类型。
可以使用 CHANGE 子句或 MODIFY 子句。 - （2）让表改用另一种存储引擎。
可使用 ENGINE 子句来指定一个新的存储名字。 - （3）重新命名表。
用 RENAME 子句可以为表指定一个新的名字。
重新命名表时不能使用已有的名字。
获取数据库元数据
使用 SHOW 语句获取元数据
SHOW 语句的一些常见用法：
- 列出可以访问的数据库：
SHOW DATABASES;
- 显示数据库的 CREATE DATABASE 语句：
SHOW CREATE DATABASE db_name;
- 列出默认数据库或给定数据库里的表：
SHOW TABLES;
SHOW TABLES FROM db_name;
SHOW TABLES语句无法显示出 TEMPORARY 表。
- 显示表的 CREATE TABLE语句：
SHOW CREATE TABLE tbl_name;
- 显示表里的列或索引信息：
SHOW COLUMNS FROM tbl_name;
SHOW INDEX FROM tbl_name;
- 显示默认数据库或给定数据库里的表描述信息：
SHOW TABLES STATUS;
SHOW TABLE STATUS FROM db_name;
有几种SHOW语句形式还可以带上一条 LIKE ‘pattern’ 子句——它可以通过一个模式来限制 SHOW 语句的输出范围。
借助 INFORMATION_SCHEMA 获取元数据
通过 SELECT 语句可以访问 INFORMATION_SCHEMA 库。
在SHOW语句的输出里，列的个数是固定的，而且无法把输出内容捕获到表里去。
利用 INFORMATION_SCHEMA 数据库，SELECT 语句可以选取特定的输出列，而且 WHERE 子句可以让你通过各种表达挑选你真正需要的信息。
查询 INFORMATION_SCHEMA 库包含了哪些表，可以使用如下语句：
SHOW TABLES IN INFORMATION_SCHEMA;
查询某个特定的 INFORMATION_SCHEMA 里表包含哪些列，可使用语句 SHOW COLUMNS 或 DESCRIBE：
DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS;
用于查看 INFORMATION_SCHEMA 里的某个给定表的所有列的通用查询语句，如下所示：
SELECT * FROM INFORMATION_SCHEMA.tbl_name;
若要有选择地查看，则可以加上一条 WHERE 子句。
从命令行获取元数据
从命令行提示符获取数据库和表的信息。
- 列出服务器所管理的数据库：
mysqlshow
- 列出数据库里的表：
mysqlshow db_name
- 显示表里的列信息：
mysqlshow db_name tbl_name
- 显示表里的索引信息：
mysqlshow --keys db_name tbl_name
- 显示数据库里所有表的描述性信息：
mysqlshow --status db_name
客户端程序 mysqldump 能够让你看到 CREATE TABLE 语句所定义的表结构。
mysqldump --no-data db_name [tbl_name] ...
在使用 mysqlshow 和 mysqldump 时，要指定必要的连接参数选项，如 --host、–user 或 --password 。
使用连接实现多表检索
SELECT 语句的基本语法，如下所示：
SELECT select_list # 所选择的列
FROM table_list # 要查询的那些表
WHERE row_constraint # 行必须满足的条件
GROUP BY grouping_columns # 结果如何分组
ORDER BY sorting_columns # 结果如何排序
HAVING group_constraint # 分组必须满足的条件
LIMIT count; # 限制结果里的行数
内连接
如果在SELECT 语句的FROM子句里列出了多个表的名字，并且这些名字是由 INNER JOIN 隔开的，那么 MySQL就会执行一个内连接（inner join）操作——它会把一个表里的行与另一个表里的行进行匹配，然后产生结果。
把 t1 和 t2 连接起来，那么t1里的每一行将与 t2里的每一行进行组合：
SELECT * FROM t1 INNER JOIN t2;
也可以把它写成 SELECT t1., t2. ：
SELECT t1.*, t2.* FROM t1 INNER JOIN t2;
如果增加 WHERE 子句，实现表之间基于某些列值的匹配，那么整个连接操作将只会选取那些列值彼此相等的行：
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 WHERE t1.i1 = t2.i2;
连接类型 CROSE JOIN 和 JOIN 都等同于 INNER JOIN ，因此下面的语句是等价的：
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 WHERE t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1 = t2.i2;
连接运算符“,”（逗号）也与之类似：
SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2;
在指定如何对表里的列进行匹配方面，INNER JOIN、CROSS JOIN 和 JOIN 还支持另外几种语法格式。
- 有一种语法是用 ON 子句代替 WHERE 子句。
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2;
- 另一种语法是使用 USING() 子句。它在概念上类似于 ON 子句，但要求被连接的列必须同名。
SELECT mytbl1.*, mytbl2.* FROM mytbl1 INNER JOIN mytbl2 USING(b);
对被连接表里的列引用进行限定
假设，表 mytbl1里包含列 a 和 b，表 mytbl2 里包含列 b 和 c 。
此时，引用 a 和 c 不会产生歧义，但在引用 b 时则必须将它限定为 mytbl1.b 或 mytbl2.b ：
SELECT a, mytbl1.b, mytbl2.b, c FROM mytbl1 INNER JOIN mytbl2 ... ;
下面这条查询语句将把某个表与其自身连接起来，不过它为该表的一个实例分配了一个别名，从而消除了在列引用时可能产生的歧义问题：
SELECT mytbl1.col1, m.col2 FROM mytbl INNER JOIN mytbl AS m
WHERE mytbl.col1 > m.col1;
使用子查询实现多表检索
子查询指的是用括号括起来，并嵌入另一条语句里的那条 SELECT 语句。
子查询可以返回各种不同类型的信息。
- 标量子查询返回一个值。
- 列子查询返回一个由一个值或多个值构成的列。
- 行子查询返回一个由一个值或多个值构成的行。
- 表子查询返回一个由一个行或多个行构成的表，而行则由一个或多个列构成。
子查询结果的测试方法各不相同。
- 可以用诸如 “=” 或 “<” 之类的关系比较运算符来评估标量子查询的结果。
- 可以用运算符 IN 和 NOT IN 来测试某给定值是否包含在子查询的结果集里。
- 可以用运算符 ALL、ANY 和 SOME 把某给定值域子查询的结果集进行比较。
- 可以用运算符 EXISTS 和 NOT EXISTS 来测试子查询的结果是否为空。
标量子查询是最严格的，因为它只会产生一个值。
除了 SELECT 语句，子查询还可以用在其他语句里。
有些子查询可以改写为连接操作。
带关系比较运算符的子查询
运算符 =、<>、>、>=、< 和 <= 可用来对值之间的关系进行比较。
当与标量子查询配合使用时，它们会将外层查询里所有与子查询的返回值有着特定关系的行找出来。
IN 和 NOT IN 子查询
当子查询要返回多个行来与外层查询进行比较运算时，可以使用运算符 IN 和 NOT IN 。
它们会测试某个给定的比较值是否存在于某一组值里。
如果外层查询里的行与子查询返回的某一个行相匹配，那么 IN 的结果即为真。
如果外层查询里的行与子查询返回的所有行都不匹配，那么 NOT IN 的结果即为真。
IN 和 NOT IN 还可以用于会返回多个列的子查询。即，可以把它们与表的子查询一起使用。
ALL、ANY 和 SOME 子查询
运算符 ALL 和 ANY 常与某个关系比较运算符结合在一起使用，以便测试列子查询的结果。
它们会测试比较值域子查询返回的全部或部分值是否匹配。
SOME 是 ANY 的同义词。
当 ALL、ANY 或 SOME 与比较运算符 “=” 配合使用时，子查询可以是表子查询。此时，需要使用一个行构造器来提供比较值。
IN 和 NOT IN 是 = ANY 和 <> ALL 的简写。
IN 的含义是“等于子查询所返回的某个行”，而 NOT IN 的含义是“不等于子查询所返回的任何行”。
EXISTS 和 NOT EXISTS 子查询
运算符 EXISTS 和 NOT EXISTS 只会测试某个子查询是否返回了行。如果有返回，则 EXISTS 的结果为真，而 NOT EXISTS 的结果为假。
下面两条语句展示了这两个运算符的具体用法。
如果 absebce 表为空，那么第一条语句将返回 0，第二条语句将返回 1 ：
SELECT EXISTS (SELECT * FROM absebce);
SELECT NOT EXISTS (SELECT * FROM absence);
在使用 EXISTS 和 NOT EXISTS 时，子查询通常将 “ * ” 用作输出列的列表。
相关子查询
子查询要不要相关都可以。
- 不相关的子查询不会引用外层查询里的值，因此它自己可以作为一条的单独查询命令去执行。
- 相关子查询则引用了外层查询里的值，所以它也就依赖于外层查询。
FROM 子句里的子查询
子查询可以用在 FROM 子句里，以生成某些值。此时，子查询的结果就像一个表。
FROM 子句里的子查询可以参与关联操作，其值可以在 WHERE 子句里进行测试，等等。
在使用这种类型的子查询时，必须提供一个表别名，用作子查询结果的名字：
SELECT * FROM (SELECT 1, 2) AS t1 INNER JOIN (SELECT 3, 4) AS t2 ;
使用 UNION 实现多表检索
如想要编写一条 UNION 语句把多个检索合并在一起，首先需要写成多条 SELECT 语句，然后把关键字 UNION 放置到它们的中间。
每条 SELECT 语句都必须检索出相同数量的列。
下面这条语句将选取各个表的整数列，并把它们合并在一起：
SELECT i FROM t1 UNION SELECT i FROM t2 UNION SELECT i FROM t3 ;
UNION 有以下几种特性。
- （1）列名和数据类型。
- （2）重复处理
- （3）ORDER BY 和 LIMIT 处理。
多表删除和更新
要完成单个表的 DELETE 和 UPDATE 操作，只需要引用一个表的列即可，并不需要使用表名来限定列名。
在编写涉及多表的 DELETE 语句时，需要把所有涉及的表全部列在 FROM 子句里，并把用来匹配表中各行的检索条件写在 WHERE 子句里。
如果某个列名出现在多个表里，则可能导致歧义问题，这时必须用表的名字对它加以限定。
DELETE 语句还支持一种语法，可以一次性删除多个表里的行。
MySQL还支持另一种多表 DELETE语法。这种语法会使用一个 FROM 子句，列出那些需要删除行的表；并使用一个 USING 子句来连接这些表，从而确定出哪些行是需要删除的。
事务处理
事务 指的是一组SQL语句，它们是一个执行单位，且在必要时还可以取消。
事务处理是通过使用 提交 和 回滚 功能来实现的。
提交和回滚所提供的功能，能够确保尚未全部完成的操作不会影响到数据库，而且不会让数据库处于部分更新的（不一致）状态。
如果在执行这两条语句时正好发生了故障，那么这个操作就不能算完成。
事务的另一个用途是，确保某个操作所涉及的行不会在你正在使用它们时被其他客户端修改。
MySQL 在执行每一条SQL语句时，会自动对该语句所涉及的资源进行锁定，以避免各个客户端之间相互干扰。
事务把多条语句定义为一个执行单位，便可以防止在多客户端环境里可能会发生的并发问题。
事务系统通常具有“ACID”的四种特性，即 Atomic （原子性）、Consistent（一致性）、Isolated（独立性）和 Durable（持久性）。
对各个特性的具体描述如下。
- 原子性。
构成事务的所有语句应该是一个独立的逻辑单元。不能只执行它们当中的一部分。 - 一致性。
数据库在事务的执行前后都必须是一致的。 - 独立性。
事务之间不应该相互影响，这样事务在并发执行时才会得到与挨个依次执行一样的效果。 - 持久性。
当事务执行成功完成时，其影响将被永久性地记录到数据库里。
利用事务保证语句安全执行
默认下，MYSQL运行模式为自动提交，即每条语句的更改会立刻提交到数据库，永久保留。
一种常见的执行事务的办法是：先调用START TRANSACTION（或BEGIN） 语句，挂起自动提交模式；接着，再执行构成本次事务的各条语句；最后，用 COMMIT 语句结束事务，从而让所有修改持久化。
如果在事务处理过程中发生错误，那么可以调用 ROLLBACK 语句来撤销所有的更改。
START TRANSACTION语句会挂起当前的自动提交模式，因此在事务被提交或回滚之后，该模式又会恢复到指向 START TRANSACTION 之前的那个状态。
另一个执行事务的办法是，利用SET语句直接操作自动提交模式的状态：
SET autocommit = 0;
SET autocommit = 1;
将 autocommit 变量设置为0，可以禁用自动提交模式。
被禁用时，到后面开启自动提交或显式COMMIT/ROLLBACK中间的语句序列构成一个事务序列。
用来创建，更改，删除数据库或其中的对象的数据定义语言语句，及与锁定有关的语句不能成为事务的一部分。
例如，在事务处理过程中调了以下语句的任何一条，则服务器在执行这条语句前将先提交当前事务：
ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
LOCK TABLES
RENAME TABLE
SET autocommit = 1 (if not already set to 1)
TRUNCATE TABLE
UNLOCK TABLE (if tables currently are locked)
如果事务被提交前，意外中断，会导致事务结束。
服务器会自动回滚该客户尚未完成的事务。
使用事务保存点
MySQL可以让你对事务进行部分回滚。
具体做法是，在事务里调用 SAVEPOINT 语句来设置一些命名标记。
在事务的后续部分，如果想要回滚到那个标记点位置，那么需要使用 ROLLBACK 语句来指定那个保存点。
事务隔离
- 脏读
事务A修改了数据X尚未提交。
事务B此时访问X，访问的是A已经修改，尚未提交的版本。 - 不可重复读
事务A执行表T上的SELECT。
事务B修改表T且提交。
事务A对表T再次执行同样的SELECT，但此次结果和上次不同。 - 幻影行
事务A执行表T上的SELECT。
事务B修改表T且提交。
事务A对表T再次执行同样的SELECT，但此次结果和上次不同．且此次结果中出现了上次未出现的新项。
MYSQL的多种隔离级别：
-
READ UNCOMMITTED
它只允许某个事务看到其他事务已经提交的行修改。事务A修改了X但尚未提交； 事务B访问X，事务B访问的是A已经修改尚未提交的版本 -
READ COMMITTED
它允许某个事务看到其他事务尚未提交的行修改。事务A修改了X， 事务B访问X， 若A已经提交事务B看到A已经提交的版本。 若A未提交，事务B看到A修改前版本。 -
REPEATABLE READ
如果某个事务两次执行同一条 SELECT 语句，其结果是可重复的。事务A执行SELECT 其他事务执行 事务A再次执行同样的SELECT 事务A两次SELECT得到同样结果 -
SERIALIZABLE
与 REPEATABLE READ 类似，对事务的隔离更彻底。
主要表现：对于某个事务正在查看的行，只有等到该事务完成才能被其他事务所修改。若事务A在进行， 则事务A访问或更新的所有数据项， 其他事务都无权访问。 只能等待A结束后，再访问。
InnoDB存储引擎默认的隔离级别为REPEATABLE READ。
显式设置：
SET GLOBAL TRANSACTION ISOLATION LEVEL level;
SET SESSION TRANSACTION ISOLATION LEVEL level;
SET TRANSACTION ISOLATION LEVEL level; // 只对下一事务有效
外键和引用完整性
在MySQL里，InnoDB 存储引擎提供了对外键的支持。
首先，需要定义几条术语。
- 父表。它指的是包含原始键值的表。
- 子表。它指的是引用了父表中键值的相关表。
提供参照完整性。
用于实现级联删除，级联更新。
[CONSTRAINT constraint_name]
FOREIGN KEY [fk_name] (index_columns)
REFERENCES tbl_name (index_columns)
[ON DELETE action]
[ON UPDATE action]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
- ON DELETE子句
１．未定义时，默认行为为 拒绝从父表中删除仍被子表行所引用的行。
２．ON DELETE CASCADE，删除父表中行时，自动删除参照此行的子表中的行。
３．ON DELETE SET NULL，删除父表中行时，参照此行子表中对应行的对应列设为NULL。
子表该列也需要允许设为NULL - ON UPDATE子句
对子表，若对子表中某行某个外部引用列修改或插入新的行，外部引用列的新值也要满足参照完整性．对父表中某个被引用的值的更新不被允许／允许但同步更新引用此值的表的相应行相应列的值为新值。／允许但同步更新引用此值的表的相应行相应列的值为NULL。
对被引用列应该定义此列的索引；对外部参考的列，应该定义此列或包含此列的索引；外键索引不应为前缀索引。
- UNICODE索引
对UNICODE索引的列，出现多个NULL值的列是允许的。
使用 FULLTEXT 搜索
MySQL具备全文搜索的能力，它可以让你在不使用模板匹配操作的情况下进行单词或短语的查找。
全文搜索有3种类型。
- 自然语言搜索（默认类型）
把搜索字符串解析成一系列单词，再搜索。 - 布尔模式搜索
搜索字符串可包含修饰符。 - 查询扩展搜索
这种搜索分两阶段进行。第一阶段是自然语言搜索。第二阶段则先把原来的搜索字符串，与在第一阶段的搜索里高度匹配的那些行，连接在一起，然后再进行一次搜索。
要想对某个给定表进行全文搜索，则必须事先为它创建一个特殊类的索引，这种索引具有以下几个方面的特点。
-
全文搜索基于FULLTEXT索引。
FULLTEXT索引只针对CHAR, VARCHAR, TEXT几种类型的列。 -
全文搜索将会忽略掉那些常见词。
至少一半行都出现的词被忽略。 -
有些内建的常用单词。
如the, after,…被称为停用词，在进行全文搜索时会被忽略掉。 -
太短的单词也会被忽略。
默认情况下，“太短”指少于4个字符。 -
全文搜索对“单词”的定义是，它们是由字母，数字，撇号，下划线构成的字符序列。
通常只要在某个行里找到了搜索字符串里的某个单词，则FULLTEXT引擎便会认为这个行与搜索字符串匹配。 -
可为单个列或多个列建立FULLTEXT索引。
全文搜索时给出的列列表必须和某个FULLTEXT索引所匹配的那些列精确匹配。
举例：
CREATE TABLE apothegm (attribution VARCHAR(40),phrase TEXT) ENGINE = MyISAM;
LOAD DATA LOCAL INFILE 'apothegm.txt' INTO TABLE apothegm;
ALTER TABLE apothegm
ADD FULLTEXT (phrase),
ADD FULLTEXT (attribution),
ADD FULLTEXT (phrase, attribution);
自然语言 FULLTEXT 搜索
SELECT * FROM apothegm WHERE MATCH(attribution) AGAINST('roosevelt');
对表中每个元组执行MATCH(attribution) AGAINST(‘roosevelt’)，返回的结果是一个float类型的值。
默认的搜索模式是自然语言模式。
也可显式指定。
SELECT * FROM apothegm WHERE MATCH(phrase) AGAINST('hard soft' IN NATURAL LANGUAGE MODE);
布尔模式的全文搜索
布尔模式的全文搜索，可获得对多单词搜索的更多控制。
要完成此类型搜索，需在AGAINST()搜索字符串后添加 IN BOOLEAN MODE。
布尔模式搜索具有以下几个特点。
-
50%规则不再适用。
-
查询结果不再按相关程度排序。
-
搜索可要求短语里的所有单词都必须按某种特定的顺序出现。
'“xxxx”'可将xxx部分作为短语而非拆分后的多个单词进行匹配。 -
可对未包含在FULLTEXT索引里的列执行布尔模式的全文搜索。
-
停用词仍然保持被忽略。
布尔模式搜索下，可为搜索字符串里的单词加一些修饰符。
单词前加+，表该单词必须出现在匹配行。
单词前加-，表该单词不能出现在匹配行。
单词尾加*，表以此单词为前缀的所有单词均能匹配。
查询扩展全文搜索
- 第一阶段按自然语言搜索搜索。
- 相关程度高的行里的单词会与原来的搜索单词一起，完成第二阶段的搜索。
需要在搜索字符串的后面加上 WITH QUERY EXPANSION。
学习参考资料：
《MySQL技术内幕》第5版