---

引言

正则表达式是用来匹配文本的特殊的串（字符集合)。如果你想从一个文本文件中提取电话号码，可以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词，可以使用一个正则表达式。所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。

---

一、正则表达式

MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如下所示

匹配	描述	示例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个 o
%	匹配所有	\
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
p1Ip2	匹配 p1 或 p2	\
[…]	匹配字符集合中的任意一个字符	‘[abc]’ 匹配 a 或者 b 或者 c
[^…]	匹配不在括号中的任何字符	‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n}	匹配前面的字符串 n 次	‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}	匹配前面的字符串至少 n 次，至多m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

• 案例

#查询以li开头的学生信息
mysql> select id,name from info where name regexp '^li';
+----+-------+
| id | name  |
+----+-------+
|  2 | lier  |
|  3 | lisan |
|  7 | lilei |
+----+-------+
3 rows in set (0.01 sec)
-----------------------------------------------------------------------------------------------
#查询以n结尾的学生信息
mysql> select id,name from info where name regexp 'n$';
+----+--------+
| id | name   |
+----+--------+
|  3 | lisan  |
|  5 | goudan |
| 10 | shidan |
+----+--------+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#查询名字中包含an的学生信息
mysql> select id,name from info where name regexp 'n$';
+----+--------+
| id | name   |
+----+--------+
|  3 | lisan  |
|  5 | goudan |
| 10 | shidan |
+----+--------+
3 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
#查询名字是wang开头，a结尾，中间不知道是一个什么字符的学生信息
mysql> select id,name from info where name regexp 'wang.a';
+----+--------+
| id | name   |
+----+--------+
|  4 | wangya |
+----+--------+
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------
#查询名字中包含sh或者li的学生信息
mysql> select id,name from info where name regexp 'sh|li';
+----+--------+
| id | name   |
+----+--------+
|  2 | lier   |
|  3 | lisan  |
|  7 | lilei  |
|  9 | shabi  |
| 10 | shidan |
+----+--------+
5 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
#查询名字中有sh,a可有可无的学生信息,必须要有的部分是'sh' 而'a'可有可无
mysql> select id,name from info where name regexp 'sha*';
+----+--------+
| id | name   |
+----+--------+
|  9 | shabi  |
| 10 | shidan |
+----+--------+
2 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
#查询名字中含有go，u至少出现一次的信息
mysql> select id,name from info where name regexp 'gou+';
+----+--------+
| id | name   |
+----+--------+
|  5 | goudan |
| 11 | goush  |
+----+--------+
2 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------
#查询名字以s-x开头的学生信息
mysql> select id,name from info where name regexp '^[s-x]';
+----+--------+
| id | name   |
+----+--------+
|  4 | wangya |
|  9 | shabi  |
| 10 | shidan |
+----+--------+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#查询名字不是lisi的信息
mysql> select id,name from info where name regexp '[^lisi]';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | guyi      |
|  2 | lier      |
|  3 | lisan     |
|  4 | wangya    |
|  5 | goudan    |
|  6 | hanmeimei |
|  7 | lilei     |
|  8 | caicai    |
|  9 | shabi     |
| 10 | shidan    |
| 11 | goush     |
| 12 | gobud     |
+----+-----------+
12 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------
#查询名字不是以sh开头的信息
mysql> select id,name from info where name regexp '^[^sh]';
+----+--------+
| id | name   |
+----+--------+
|  1 | guyi   |
|  2 | lier   |
|  3 | lisan  |
|  4 | wangya |
|  5 | goudan |
|  7 | lilei  |
|  8 | caicai |
| 11 | goush  |
| 12 | gobud  |
+----+--------+
9 rows in set (0.00 sec)

二、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符

1. 算术运算符

以 SELECT 命令来实现最基础的加减乘除运算，MySQL 支持使用的算术运算符，如表所示：

运算符	描述
+	加法
-	减法
*	乘法
/	除法
%	取余

• 在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序。

mysql> select 1+1,3-2,2*3,6/3,7%2;
+-----+-----+-----+--------+------+
| 1+1 | 3-2 | 2*3 | 6/3    | 7%2  |
+-----+-----+-----+--------+------+
|   2 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

2. 比较运算符

• 比较运算符是查询数据记录时经常使用的一类运算符
• 通过使用比较运算符可以判断出表中有哪些记录是符合条件的，如果比较的结果(以布尔值的方式进行返回判断)为真则返回1，如果为假则返回0，比较的结果如果不确定则返回NULL
• 其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过binary关键字来实现

运算符	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!=或<>	不等于
is null	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
BETWEEN AND	两者之间
IN	在集合中
LIKE	通配符匹配
GREATEST	两个或多个参数时返回最大值
LEAST	两个或多个参数时返回最小值
REGEXP	正则表达式

• 等号（=）：

• 是用来判断数字、字符串和表达式是否相等的，如果相等则返回1(true)，如果不相等则返回0（flase)。
  如果比较的两者有一个值是NULL，则比较的结果就是NULL。
  其中字符的比较是根据ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同
• 例如字符串（字母）比较：
  （‘a’>‘b’）其实比较的就是底层的ASCII码，需要关注的是ascii码有：a、A、0（97、65、48）
  那么： 如果比较的是多字符串，如:‘abc’=‘acb’,如何比较（字符个数、字符顺序）
  如果比较的是多字符，如：‘abc’ <'baa’如何比较
• 与linux返回值表达相反，linux 中运行正常返回值是0，运行异常返回值是非0

mysql> select 'abc'='acb';
+-------------+
| 'abc'='acb' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

• 案例

mysql> select 2=4,2=2,2='2','e'='e','r'=null;
+-----+-----+-------+---------+----------+
| 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null |
+-----+-----+-------+---------+----------+
|   0 |   1 |     1 |       1 |     NULL |
+-----+-----+-------+---------+----------+
1 row in set (0.00 sec)#如果两者都是整数，则按照整数值进行比较。
#如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。（在程序中，一般是不会吧这两者进行相比较的）
#如果两者都是字符串，则按照字符串进行比较。
#如果两者中至少有一个值是 NULL，则比较的结果是 NULL。

• 不等于（<>,!=）

不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。
如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。
需要注意的是不等于运算符不能用于判断 NULL
也就是说，不相等即“成立不等于”即返回1，相等即“不成立不等于”即返回 0

mysql> select 'abc'<>'cba',2<>2,3!=2,null<>null;
+--------------+------+------+------------+
| 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null |
+--------------+------+------+------------+
|            1 |    0 |    1 |       NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)mysql> select 5>4,3<4,'a'<'b',4.4<5,'u'>=null;
+-----+-----+---------+-------+-----------+
| 5>4 | 3<4 | 'a'<'b' | 4.4<5 | 'u'>=null |
+-----+-----+---------+-------+-----------+
|   1 |   1 |       1 |     1 |      NULL |
+-----+-----+---------+-------+-----------+
1 row in set (0.00 sec)#大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回0，同样不能用于判断NULL。
#小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回0，同样不能用于判断NULL。
#大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断NULL。
#小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL

• 判断一个值为不为null

mysql> select 2 is null,'f' is not null,null is null;
+-----------+-----------------+--------------+
| 2 is null | 'f' is not null | null is null |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

• between and 比较运算

通常用于判断一个值是否落在某两个值之间；即覆盖的范围是>= 和 <=关系
判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间
案例：

#判断6是否在5和7之间，c是否a和b之间，Between and 覆盖的范围是>= 和 <=关系mysql> select 6 between 5 and 7,'c' between 'a' and 'b';
+-------------------+-------------------------+
| 6 between 5 and 7 | 'c' between 'a' and 'b' |
+-------------------+-------------------------+
|                 1 |                       0 |
+-------------------+-------------------------+

• least 和greatest（取最小值、取最大值）

#least(取最小值)：
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则 返回结果就为 NULL。
#greatest(取最大值)：
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL， 则返回结果就为 NULL。案例
mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c') |
+--------------+-----------------+--------------------+-----------------------+
|            1 |               3 | a                  | c                     |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.00 sec)

• IN、NOT IN

#判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
#判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0
mysql>  select 3 in(1,2,3),1 not in(1,2,3);
+-------------+-----------------+
| 3 in(1,2,3) | 1 not in(1,2,3) |
+-------------+-----------------+
|           1 |               0 |
+-------------+-----------------+
1 row in set (0.00 sec)

• like、NOT LIKE

like
#用来匹配字符串，如果匹配成功则返回 1，反之返回 0
#LIKE 支持两种通配符:
’%’ ：用于匹配任意数目的字符（*匹配的是前面一个字符）
’_’：只能匹配一个字符。NOT LIKE
#正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0案例
mysql> select 'abc' like 'ab%','abc' like 'a_c','abc' not like 'abc';
+------------------+------------------+----------------------+
| 'abc' like 'ab%' | 'abc' like 'a_c' | 'abc' not like 'abc' |
+------------------+------------------+----------------------+
|                1 |                1 |                    0 |
+------------------+------------------+----------------------+
1 row in set (0.00 sec)

3. 逻辑运算符（布尔值）

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则 返回 0，真和假也可以用 TRUE 和 FALSE 表示
MySQL 中支持使用的逻辑运算符有四种， 具体如表所示：

运算符	描述
not 或 !	逻辑非
and 或 &&	逻辑与
or	逻辑或
xor	逻辑异或

3.1 逻辑非

#逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示
逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真
如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL案例
mysql> select not 0,!3,!null;
+-------+----+-------+
| not 0 | !3 | !null |
+-------+----+-------+
|     1 |  0 |  NULL |
+-------+----+-------+
1 row in set (0.00 sec)

3.2 逻辑与

#当所有的操作数都为非0值且不为null时，返回值为1，否则为0（null与0比较特殊）
逻辑与使用 AND 或者&&表示案例
mysql> select 3 and 4,null and 5,0 && null,6 && 0;
+---------+------------+-----------+--------+
| 3 and 4 | null and 5 | 0 && null | 6 && 0 |
+---------+------------+-----------+--------+
|       1 |       NULL |         0 |      0 |
+---------+------------+-----------+--------+
1 row in set (0.00 sec)#输出结果可以看出：and和&&的作用相同
3 and 4 ：没有0或null，返回值为1
null and 5：有null，返回值null
0 && null：返回值为0
6 && 0：中有0，返回值为0

3.3 逻辑或(or)

#逻辑或通常使用 OR
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回 0。
当有一个操作数为null时，如果另一个操作数为非0值，则返回值为1，否则为null
如两个操作数均为null，则返回值为null案例
mysql> select 3 or 4,null or 5,0 or null,6 or 0,0 or 0;
+--------+-----------+-----------+--------+--------+
| 3 or 4 | null or 5 | 0 or null | 6 or 0 | 0 or 0 |
+--------+-----------+-----------+--------+--------+
|      1 |         1 |      NULL |      1 |      0 |
+--------+-----------+-----------+--------+--------+
1 row in set (0.00 sec)#多个比较
mysql> select 0 or null or 2;
+----------------+
| 0 or null or 2 |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

3.4 逻辑异(xor)

#两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0
如果一个为 0， 另一个为非 0，则返回结果为 1
当任意一个值为 NULL 时，返回值为 NULL案例
mysql> select 0 xor 0,0 xor 5,3 xor 4,3 xor null;
+---------+---------+---------+------------+
| 0 xor 0 | 0 xor 5 | 3 xor 4 | 3 xor null |
+---------+---------+---------+------------+
|       0 |       1 |       0 |       NULL |
+---------+---------+---------+------------+
1 row in set (0.00 sec)

4. 位运算符

• 位运算符实际上是对二进制数进行计算的运算符
• MySQL 内位运算会先将操作数变成二进制格式（1010 1111），然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看
• MySQL 支持 6 种位运算符，具体如表所示：

位运算符	描述
&	按位与
I	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

案例：
mysql> select 10 & 15,10 | 15,10^15,5 &~1;
+---------+---------+-------+-------+
| 10 & 15 | 10 | 15 | 10^15 | 5 &~1 |
+---------+---------+-------+-------+
|      10 |      15 |     5 |     4 |
+---------+---------+-------+-------+
1 row in set (0.00 sec)具体过程:
10 转换为二进制数是 1010, 15 转换为二进制数是 1111#按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10
#按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0， 所以 10 | 15 的结果为 15
#按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15 的结果为 5
#按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。
数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，
其结果是二进制的 0100，转换为十进制就是 4

5. 优先级

• 不管哪种运算符，在使用过程中都有优先级问题。
• 运算符的优先级决定了不同的运算符在计算过程中的先后顺序
• 级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算
• 优先级如下表所示

优先级	运算符
1	！
2	~
3	^
4	*、/、%
5	+,-
6	>>,<<
7	&
8	I
9	=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
10	BETWEEN,CASE,WHEN,THEN,ELSE
11	NOT
12	&&,AND
13	II,OR,XOR
14	:=

三、连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的共同字段，进行数据的拼接。
首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括：内连接、左连接和右连接

以左边表格为主表，叫左连查询
以右边表格为主表，叫右连查询

• 案例准备

mysql> create database info;
mysql> use info;
mysql> create table test1 (-> a_id int(11) default null,-> a_name varchar(32) default null,-> a_level int(11) default null);mysql> create table test2 (-> b_id int(11) default null,-> b_name varchar(32) default null,-> b_level int(11) default null);insert into test1 values (1,'aaaa',10);
insert into test1 values (2,'bbbb',20);
insert into test1 values (3,'cccc',30);
insert into test1 values (4,'dddd',40);
insert into test2 values (2,'bbbb',20);
insert into test2 values (3,'cccc',30);
insert into test2 values (5,'eeee',50);
insert into test2 values (6,'ffff',60);mysql> select * from test1;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    1 | aaaa   |      10 |
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    4 | dddd   |      40 |
+------+--------+---------+
4 rows in set (0.00 sec)mysql> select * from test2;
+------+--------+---------+
| b_id | b_name | b_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    5 | eeee   |      50 |
|    6 | ffff   |      60 |
+------+--------+---------+
4 rows in set (0.00 sec)

1. 内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合
通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN
同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
简而言之：
内连查询就是通过 inner join 的方式将两张表指定的相同字段的记录行输出出来

语法格式：
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

mysql> select a.a_id,a.a_name,a.a_level from test1 a inner join test2 b on a.a_id=b.b_id;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
+------+--------+---------+
2 rows in set (0.00 sec)

2. 左连接

• 左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示
• 左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

mysql> select * from test1 a left join test2 b on a.a_name=b.b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
|    1 | aaaa   |      10 | NULL | NULL   |    NULL |
|    4 | dddd   |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
#join左表的test1表作为主表全部显示出来，右表test2只会显示符合搜索条件的记录，右表记录不足的地方均为NULL

3. 右连接

• 右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。
• 右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

mysql> select * from test1 a right join test2 b on a.a_name=b.b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
| NULL | NULL   |    NULL |    5 | eeee   |      50 |
| NULL | NULL   |    NULL |    6 | ffff   |      60 |
+------+--------+---------+------+--------+---------+
#join右边的test2表作为主表全部显示出来，左表test1只会显示符合搜索条件的记录，坐表记录不足的地方均为NULL

总结

• 连接查询是同时查询多张表,通过多张表之间的关系得到最终的结果。
• 内连接:从左表中取出每一条记录,去右表中与所有的记录进行匹配;匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留。
• 外连接是以某张表为主表，取出主表中的所有记录，然后每一条记录都与另外一张表进行连接。主表中的结果将全部保留，从表中内匹配的结果保留，不能匹配的结果都将置为null。
• 外连接分成两种，左外连接(left join)和右外连接(right join)
• 左外连接：左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为NULL。
• 右外连接：与左连接相反，右连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来，左表记录不足的地方均为NULL。