MySQL必知必会

1. 设置自增字段

创建时设置自动增量：

CREATE TABLE tbl_name
(	id int PRIMARY KEY AUTO_INCREMENT,
 	...
); 

分析：上述代码创建表，设置表主键id自动增长，默认自动增长的起始值为1开始。

修改表中列为自动增量：

ALTER TABLE 表名 MODIFY 字段名 字段类型 AUTO_INCREMENT;

修改后从下一条记录开始自动增长。如果想让原来的自动增长就得复制现有表的结构(无id），添加id并加上AUTO_INCREMENT，然后通过循环，添加n条空记录，然后对应先前表的id，依次插入数据。

注意：只有 int 类型且为 primary key 才可以使用 auto_increment。如果使用auto_increment=1001，表示从1001开始自增增量，默认自动变量从1开始。使用自动增量时需要获得最后一次添加记录的 auto_increment 值，可以使用last_insert_id()函数获得这个值。

2. 利用SQL脚本文件填充数据库

首先创建数据库，然后在use数据库后运行*.sql的脚本文件，即执行如下代码：

source 脚本文件路径

3. SHOW语句用法

3.1 常用show语句（< = >表示等价关系）

-- 显示所有数据库
show databases
-- 显示所有表格（首先应use 数据库）
show tables
-- 显示表的列详细信息（字段名、数据类型、是否运行NULL、键信息、默认值以及其他信息）
show columns from tbl_name < = >  describe tbl_name
-- 显示创建数据库时的详细信息
show create database db_name
-- 显示创建表时的详细信息
show create table tbl_name
-- 显示授权用户的安全权限
show grants

3.2 完整show用法

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]★
SHOW CREATE DATABASE db_name★
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name★
SHOW CREATE TABLE tbl_name★
SHOW CREATE TRIGGER trigger_name★
SHOW CREATE VIEW view_name★
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name★
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]★
SHOW TRIGGERS [FROM db_name] [like_or_where]★
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

4. 常用引擎类型

1. InnoDB：一个可靠的事务处理引擎，它不支持全文本搜索；
2. MyISAM：一个性能极高的引擎，支持全文本搜索，但是不支持事务处理。（默认）
3. MEMORY：在功能上等同于MyISAM，但是数据存储在内存中，速度很快（特别适用于临时表）。

使用方法：

CREATE TABLE tbl_name
( ...
  ...
) ENGINE=InnoDB;

CREATE TABLE tbl_name
( ...
  ...
) ENGINE=MyISAM;

注意：引擎类型可以混用，即不同的表可以使用不用的引擎类型。但是外键不能跨引擎，也就是使用一个引擎的表不能引用使用不同引擎表的外键。

5. ALTER修改表

1. 增加列

ALTER TABLE tbl_name
ADD col_name char(20);

2. 删除列

ALTER TABLE tbl_name
DROP COLUMN col_name;

3. 定义外键约束

ALTER TABLE tbl_name1
ADD CONSTRAINT fk_tblname1_tblname2
FOREIGN KEY (col_name1) REFERENCES tbl_name2 (col_name2);

6. LIMIT限制查询行数

6.1 LIMIT n用法

SELECT col_name
FROM tbl_name
LIMIT 5;

其中LIMIT 5指示MySQL从行0开始，一共返回5行的数据。

6.2 LIMIT m, n用法

SELECT col_name
FROM tbl_name
LIMIT 5,3;

其中LIMIT 5,3表示为MySQL从行5开始，一共返回3行数据。

注意：检索出来的第一行的行号为行0而不是行1。如LIMIT 1,1返回的其实是检索结果中的第二行数据，但是第二行数据的行号为行1。

6.3 LIMIT n OFFSET m用法

为了可读性更强，可以使用如下代码表示从行号5开始返回3行数据：

LIMIT 3 OFFSET 5

其中的OFFSET 5表示为起始行号为5，3表示为返回的数据行数为3。

7. MySQL中的正则表达式

正则表达式的作用是匹配文本，将一个模式（pattern，也即正则表达式）与一个文本串（text）比较，如果text中含有此pattern，则返回1，否则返回0。

可利用此性质在where子句中使用正则表达式获得相应的匹配数据行。

用法： REGEXP '正则表达式'，表示利用正则表达式进行匹配。

7.1 基本字符的匹配

SELECT col_name
FROM tbl_name
WHERE col_name REGEXP '1000'

分析：上述语句中的REGEXP '1000'表示匹配正则表达式1000，如果相应列值内容中含有1000子串，则返回此数据记录。

SELECT col_name
FROM tbl_name
WHERE REGEXP '.000'

分析：上述语句中的REGEXP '.000'表示匹配正则表达式.000，其中的.表示匹配任意一个字符，所以如果相应列值内容中含有*000子串(星号代表任意字符），则返回此数据记录。

LIKE和 REGEXP 重要区别：

• LIKE匹配整个串。如果被匹配的文本在列值中出现，LIKE不会找到它，相应的行也不会被返回。即如果LIKE后的匹配串中如果不带有通配符（ % 或 _ ），则不会返回任何结果。
• REGEXP匹配子串。如果被匹配的文本在列值中出现，REGEXP会找到它并将相应的行返回。

现在假如数据库中有表mytable，表中有id号为1000的数据记录，则下列代码会有不同的返回结果：

-- 无返回结果
SELECT *
FROM mytable
WHERE id LIKE '1000';

-- 有返回id为1000的数据行
SELECT *
FROM mytable
WHERE id REGEXP '1000';

注意：正则表达式默认匹配不区分大小写，若要区分大小写，用法为REGEXP BINARY '正则表达式'，即在关键字REGEXP后添加BINARY关键字。

7.2 OR匹配

1. 使用|符号

|为正则表达式中的OR操作符，它表示匹配其中之一。如下代码将匹配1或2或3：

REGEXP '1|2|3'

2. 使用[]符号

[]是另一种形式的OR语句。上述代码可改写成：

REGEXP '[123]'

使用^符号可以对字符集合进行否定，如下代码表示匹配除1、2、3外的其它任何东西：

REGEXP '[^123]'

3. 使用-定义范围

集合可用来定义要匹配的一个或多个字符，也可以定义一个要匹配的集合范围，为了简化这种范围的表示，可以使用-来定义集合的范围。如下：

REGEXP '[1-9]' < = > REGEXP '[123456789]'
REGEXP '[a-z]' -- 匹配任意字母字符（如果数据库配置为不区分大小写）

7.3 匹配特殊字符

正则表达式中包含了许多特殊的字符，如上述我们学到的.、[]、|、-等，如果我们要匹配的正好是这些特殊字符，该怎么办呢？

⭐️ 为了匹配正则表达式中的特殊字符，在MySQL中必须使用\\进行转义。多数正则表达式实现用一个\，但在MySQL中要用两个，MySQL解释一个，正则表达式库解释另一个。

如：

REGEXP '\\.' -- 匹配.字符
REGEXP '\\|' -- 匹配|字符
REGEXP '\\\' -- 匹配\字符

空白元字符的转义：

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

7.4 匹配字符类

为了更方便的工作，可以使用预定义的字符集，称为字符类。

字符类：

类	说明
[:alpha:]	任意字符（同[a-z][A-Z]）
[:lower:]	任意小写字母（同[a-z]）
[:upper:]	任意大写字母（同[A-Z]）
[:digit:]	任意数字（同[0-9]）
[:alnum:]	任意字母和数字（同[a-z][A-Z][0-9]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:punct:]	不在[:alnum:]和[:cntrl:]中的其他任意字符
[:blank:]	空格和制表（同[\\t]）
[:space:]	包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）
[:print:]	任意可打印字符
[:graph:]	与[:print:]相同，但不包括空格

字符类的使用，如下语句代码：

REGEXP '[[:digit:]]{3}' < = > REGEXP '[0-9]{3}' < = > REGEXP '[0-9][0-9][0-9]'

上述代码表示匹配连在一起的任意3位数。

7.5 多实例匹配

正如上述匹配任意3位数的代码，其中就用到了匹配字符的3次出现，除了上述的[0-9]{3}用法之外还有另一些匹配多实例的元字符。

重复元字符：

元字符	说明
*	匹配0个或多个
+	匹配1个或多个（同{1,}）
？	匹配0个或1个（同{0,1}）
{n}	匹配n个
{n,}	匹配[n,+∞]个
{n,m}	匹配[n,m]个

注意：使用上述重复元字符时一定要注意其涵盖的范围，如123?和(123)?表示不同的匹配含义。

7.6 定位符

为了匹配特定位置的文本，需要使用定位符。

定位符：

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

1. ^的双重用途

在上述7.2中的第2个例子中，我们使用了正则表达式[^123]用来匹配除1、2、3外的其他字符，如果我们把正则表达式改为^[123]则表示匹配开始字符为数字1或2或3的文本。

总结：^符号用在[]外边，用来指串的开始处；用在[]里边，用来否定该集合。

2. 使用^和$匹配整个数据串，此时发挥和LIKE一样得效果。

REGEXP '^1000$' < = > LIKE '1000' -- 参照7.1 LIKE和REGEXP区别

7.8 简单的正则表达式测试

可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并实验他们。相应的语法如下：

SELECT 'hello' REGEXP '[0-9]'

上述代码显然返回0，因为文本’hello’中没有数字。