mysql——mysql高级SQL语句之进阶查询（条件查询，NULL值，正则表达式，运算符，连接查询）

一、MySQL 进阶查询

在对 MySQL 数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法。SQL 语句的熟练使用，在平时的运维工作中可以提供不小的帮助，尤其是在一些规模较小的公司，运维身兼数职，可能会有不少数据库的相关工作。本章将从不 同的方面出发介绍 SQL 语句的高级运用方法。

1. 常用查询介绍

对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等，这些内容就是接下来要讲解的知识。

1. 按关键字排序

使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢？可以使用 ORDER BY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。以下就 是 MySQL 中 ORDER BY 语句的语法结构。

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

排序的字段可以根据具体需求进行选择，没有限制。排序的关键字可以使用 ASC 或者DESC。ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。例如，执行以下操作可查询等级大于等于 45 级的用户，并按降序进行排序。

mysql> select id,name,level from player where level>=45 order by level desc;

对于排序要求，多数情况下一个字段就可以实现。其实 ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按 照第二个字段进行排序。例如，执行以下操作可查询等级在 45 级及以上的用户，并以 level 降序排列和 id 降序排列。

mysql> select id,name,level from player where level>=45 order by level desc, id desc;

ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定。下面以 A 和 B 分别表示两个字段。

• ORDER BY A,B desc 指 A 用升序，B 用降序；
• ORDER BY A asc,B desc 指 A 用升序，B 用降序；
• ORDER BY A desc,B desc 指 A 用降序，B 用降序；

2.对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。
GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。
GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），这些聚合函数的用法在后面函数小节会有更详细的讲解。GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。其语法结构如下。

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;

GROUP BY 除了配合聚合函数一起使用外，还可以引入 WHERE 子句。首先通过WHERE 过滤掉一部分不符合需求的查询结果，然后再对结果进行分组。如果有排序的需求， 也可以引入 ORDER BY 语句。例如，执行以下操作即可统计等级在 45 级及以上，以等级为分组，每个等级有多少人。

mysql> select count(name),level from player where level>=45 group by level;

从以上结果可以看出，每个等级的人数已经统计出来了，但是数量是无序的。如果结果条目变得更多，很难一眼看出哪个等级的人数是最多的，所以需要对数量进行排序。而GROUP BY 结合 ORDER BY 即可实现分组并排序的查询。例如，查询等级在 45 级及以上， 按等级进行分组，并将每个等级的人数按降序排序，具体操作如下所示。

mysql> select count(name),level from player where level>=45 group by level order by count(name) desc;

排序后的结果一目了然，方便用户获取用户最多的等级信息。

3.限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。其语法结构如下。

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。
如果 SELECT 查询出的结果记录比较多，用户查看不是很方便。这个时候可以返回固定的、有限的记录数量，使用 MySQL 数据库的 LIMIT 子句即可实现。LIMIT 子句是一种简单的分页方法，它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。例如，执行以下操作即可查询表的前 3 个用户的信息。

mysql> select id,name,level from player limit 3;

LIMIT 子句的使用也可以结合 ORDER BY：先进行排序，然后再 LIMIT 限制固定的记录。也就是说 LIMIT 是放在最后的，将处理好的结果集按要求选出几行来。例如，将查询记录按等级 level 降序排列，只取前三条记录。

mysql> select id,name,level from player order by level desc limit 3;

在显示结果的时候也可以不从第一行开始，引入 offset 参数。例如，执行以下操作即可从第 3 条记录开始显示之后的 3 条数据。

mysql> select id,name,level from player limit 2,3;//位置偏移量从 0 开始

4.设置别名

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增 强可读性。设置别名使用 AS 语句，其语法格式如下。
列的别名语法结构：

SELECT column_name AS alias_nameFROM table_name;

表的别名语法结构：

SELECT column_name(s)FROM table_name AS alias_name;

在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS 之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的。例如，在统计表内所有记录共有多少条时，使用 count（*）， 这么写不便于识别，可以将其别名设置为 number，具体操作如下所示。

mysql> select count(*) as number from player;

mysql>select count(*) number from player;//省略 as 是一样的结果

如果表的长度比较长，可以使用 AS 给表设置别名，在查询的过程中直接使用别名。例如，执行以下操作即可将 player 表的别名设置成 p。

mysql> select p.id,p.name from player as p limit 3;
mysql>select p.id,p.name from player p limit 3; //省略 as 是一样的结果

此外，AS 还可以作为连接语句的操作符。例如，执行以下操作即可实现用一条 SQL
语句完成在创建表 tmp 的时候将 player 表内的数据写入 tmp 表。

mysql> create table tmp as select * from player; Query OK, 3218 rows affected (0.15 sec) Records: 3218 Duplicates: 0 Warnings: 0 mysql>select count(*) from tmp;
+----------+
| count(*) |
+----------+
|	3218 |
+----------+
1 row in set (0.00 sec)

注意：

• 在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。
• 列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在 执行查询时使用。

5.通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。 通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个，分别是：

• %：百分号表示零个、一个或多个字符
• _：下划线表示单个字符
  例如，查询 player 表内 name 字段分别以 s 开头的名字、以 s 结尾的名字和名字中间包含 es 的字段，具体操作如下所示。

mysql> select id,name,level from player where name like 's%';//name 字段以 s 开头的记录
mysql> select id,name,level from player where name like '%s';//name 字段以 s 结尾的记录
mysql> select id,name,level from player where name like '%es%';//name 字段中间含 es 的记录

如果匹配 name 字段中某一个字符，可以使用%。但是 MySQL 提供的专门针对单个字符的通配符，就是“_”一个下划线，使用单个下划线可以替换字符串中的某个单字符。例如， 替换表内 name 字段开头的字符、结尾的字符或者中间的字符，具体操作如下所示。

mysql> select id,name,level from player where name like '_uess';//替换开头的一个字符
mysql> select id,name,level from player where name like 'use 	';//替换结尾的四个字符
mysql> select id,name,level from player where name like 'shi_ley'; //替换中间的一个字符

通配符“%”和“_”不仅可以单独使用，也可以组合使用。例如，name 字段中，开头有一个字符，接着是 es 两个字符，后面再跟着零个、一个或多个字符，从 player 表中查询这样的数据。具体操作如下所示。

mysql> select id,name,level from player where name like '_es%';

6.子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层 嵌套。
在开始实际的举例之前，先来学习一下 IN 这个操作符的用法，IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用。IN 的语法结构如下。

<表达式> [NOT] IN <子查询>

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE。若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。
多数情况下，子查询都是与 SELECT 语句一起使用的。例如，先查出等级大于等于 45 级的 ID，然后在判断 player 表内的 ID 是不是在这个结果集内，如果在就打印此行的名字和等级，具体操作如下所示。

mysql> select name,level from player where id in (select id from player where level>=45);

从上例可以看到，子查询是被放到括号内的，这个括号是无法省略的，缺失则会报错， 无法形成子查询。子查询内要查询的字段通常都是一个，查询后形成结果集供主查询使用。
子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中。例如，先清空之前使用的 tmp 表，然后通过子查询的方式将 player 的内容插入到 tmp 表中。具体操作如下所示。

mysql> truncate table tmp;//清空 tmp 表
mysql> select * from tmp;
mysql> insert into tmp select * from player where id in (select id from player); 
Query OK, 3218 rows affected (0.12 sec)// 将 player 表 的 内 容 插 入 tmp 表 
mysql> insert into tmp select * from player;	//可跟上面子查询 SQL 达到同样的效果

UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。例如，执行以下操作即可通过子查询实现将等级大于等于 47 的用户减去 7。

mysql>select id,name,level from tmp where id=30;//ID 是 30 的用户等级是 47 级，最大级别
mysql> update tmp set level = level - 7 where id in (select id from tmp where level >= 47);
ERROR 1093 (HY000): You can't specify target table 'tmp' for update in FROM clause
// 类似 select 方式的子查询，MySQL 不支持，需要多引入一层子查询
mysql> update tmp set level = level - 7 where id in (select a.id from (select id from tmp where level >= 47) a);
mysql> select id,name,level from tmp where id=30;
//可以看到等级已经少了 7 级

DELETE 也适用于子查询。例如，先将原来 47 级的用户等级恢复，然后通过子查询的方式将 47 级用户删除。实现方式类似 UPDATE 的子查询，具体操作如下所示。

mysql> update tmp set level=47 where id=30;

mysql> delete from tmp where id in (select a.id from (select id from tmp where level=47) a);
mysql>select id,name,level from tmp where id=30;
Empty set (0.00 sec)	//等级为 47 的用户已经被删除

除了以上所列举的 IN 操作符，在 IN 前面还可以添加 NOT，其作用与 IN 相反，表示否定（即不在子查询的结果集里面）。例如，执行以下操作即可查询用户等级不小于 45 级的用户。

mysql> select name,level from tmp where id not in (select id from tmp where level < 45);

子查询上也可以使用比较运算符（=、<、>、>=、<=），这些运算符主要是对运算符前面的表达式和后面的子查询进行比较运算。例如，查询出名字是 shirley 的记录，并输出其 ID、名字和等级信息，具体操作如下所示。

mysql> select id,name,level from tmp where id = (select id from tmp where name='shirley');

EXIST 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 则返回 TRUE；反之，则返回 FALSE。例如，先通过子查询判断返回是否为 TRUE，如果用户 shirley 存在，则计算整个 tmp 表的总记录数量，具体操作如下所示。

mysql> select count(*) as number from tmp where EXISTS (select id from tmp where name='shirley');

2. NULL 值

在 SQL 语句使用过程中，经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，限制某些字段不为空，则可以使用 NOT NULL 关键字，不使用则默认可以为空。在向表内插入记录或者更新记录时，如果该字段没有 NOT NULL 并且没有值，这时候新记录的该字段将被保存为 NULL。需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用IS NOT NULL 可以判断不是 NULL 值。
例如，创建一个表 test，该表包含不设置 NOT NULL 的字段，然后向表中插入不同的记录值，其中包括 NULL 值和实际有值的记录，最后通过 SELECT 查询字段中包括 NULL 和不包括 NULL 的记录值，具体操作如下所示。

mysql>drop table test;
Query OK, 0 rows affected (0.01 sec)


mysql>CREATE TABLE `test` (
->`id` int(10) NOT NULL AUTO_INCREMENT,
->`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
->`level` int(10) NOT NULL,
->`coin` int(32),//默认未设置 NOT NULL
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test(name,level) values('aa',10);	//插入的记录中不包括 coin 字段
Query OK, 1 row affected (0.01 sec)
mysql>insert into test(name,level,coin) values('ab',20,100);	//插入的记录中包括 coin 字段
Query OK, 1 row affected (0.00 sec)
mysql>select * from test where coin is null;	//查询 coin 字段为空值的记录
+----+------+-------+------+
| id | NAME | level | coin |
+----+------+-------+------+
|    1 | aa	|	10 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)
mysql> select * from test where coin is not null;	//查询coin 字段不为空的记录
+----+------+-------+------+
| id | NAME | level | coin |
+----+------+-------+------+
|    2 | ab	|	20 | 100 |
+----+------+-------+------+
1 row in set (0.00 sec)

提到 NULL 值就不得不说下空值，空值就是在表的字段中存储空字符（‘’），那么 NULL
值和空值有什么区别呢？二者的区别如下：

• 空值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
  -IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是空值的。
  -空值的判断使用=’’或者<>’’来处理。
• 在通过 count()计算有多少记录数时，如果遇到 NULL 值会自动忽略掉，遇到空值会加入到记录中进行计算。

3. 正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如表
正则表达式匹配表

匹配模式	描述	实例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何s 和t 之间有一个字符的字
符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个 o
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个
m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
p1	p2	匹配 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 次

了解正则表达式的匹配规则之后，就可以将这些规则技巧应用于 SQL 语句中，从而可以更准确、更快速的查出所需的记录。下面通过示例的方式详细介绍 MySQL 正则表达式的使用方法。

1.以特定字符串开头的记录

例如，在 player 表中查询以 nb 开头的 name 字段并打印对应的 id、name 和 level 记录，具体操作如下所示。

mysql>select id,name,level from player where name REGEXP '^nb';

2.以特定字符串结尾的记录

例如，在 player 表中查询以 ss 结尾的 name 字段并打印对应的 id、name 和 level 记录，具体操作如下所示。

mysql> select id,name,level from player where name REGEXP 'ss$';

3.包含指定字符串的记录

例如，在 player 表中查询包含 on 字符串的 name 字段并打印对应的 id、name 和 level记录，具体操作如下所示。

mysql>select id,name,level from player where name REGEXP 'on';

4.以“.”代替字符串中的任意一个字符的记录

例如，在 player 表中查询包含字符串 shir 与 ey，且两个字符串之间只有一个字符的name 字段并打印对应的 id、name 和 level 记录，具体操作如下所示。

mysql>select id,name,level from player where name REGEXP 'shir.ey';

5.匹配包含或者关系的记录

例如，在 player 表中查询包含字符串 on 或者 in 的 name 字段并打印对应的 id、name和 level 记录，具体操作如下所示。

mysql> select id,name,level from player where name REGEXP 'on|in';

6.“*”匹配前面字符的任意多次

例如，在 player 表中查询包含三个或者更多个连续的 o 的 name 字段并打印对应的 id、name 和 level 记录，具体操作如下所示。

mysql>select id,name,level from player where name REGEXP 'oooo*';

7.“+”匹配前面字符至少一次

例如，在 player 表中查询包含四个或者更多个 o 的 name 字段并打印对应的 id、name和 level 记录，具体操作如下所示。

mysql>select id,name,level from player where name REGEXP 'oooo+';

8.匹配指定字符集中的任意一个

例如，在 player 表中查询包含以 d、e、f 开头的 name 字段并打印对应的 id、name 和level 记录，具体操作如下所示。

mysql>select id,name,level from player where name REGEXP '^[d-f]';

方括号“[]”指定了一个字符集合，只匹配其中的一个字符。“^”不仅可以放到左侧，也可以放到方括号内，放到左侧表示以这些字符集内的字符开头，而放到方括号内则表示不在指 定的字符集合内的字符。例如“[^d-f]”表示除 d、e、f 以外的任何字符。

4. 运算符

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

1.算术运算符

MySQL 支持使用的算术运算符

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

例如，以 SELECT 命令来实现最基础的加减乘除运算，具体操作如下所示。

mysql>select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;
+----------+-------------+----------------+----------+-----------+
| addition | subtraction | multiplication | division | remainder |
+----------+-------------+----------------+----------+-----------+
|	3 |	1 |	6 |	2.0000 |	1 |
+----------+-------------+----------------+----------+-----------+
1 row in set (0.01 sec)

在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算 符没有先后顺序。
在 MySQL 的字段值内还有一种情况：某些字符串类型的字段存储的数字型字符串，这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字，在转 换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串，无法转换为数字 时，将被转换为 0。这些细节在进行算术运算时需多加注意。

2.比较运算符

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

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

下面对几种常用的比较运算符进行一些讲解和举例。
（1）等于运算符
等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回 1，如果不相等则返回 0。如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的，如果 ASCII 码相等，则表示两个字符相同；如果 ASCII 码不相等，则表示两个字符不相同。例如，等于运算符在数字、字符串和表达式上的使用，具 体操作如下所示。

mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL;
+-----+-------+---------+-------------+----------+
| 2=4 | 2='2' | 'e'='e' | (2+2)=(3+1) | 'r'=NULL |
+-----+-------+---------+-------------+----------+
|	0 |	1 |	1 |	1 |	NULL |
+-----+-------+---------+-------------+----------+
1 row in set (0.00 sec)

从以上查询可以看出来：

• 如果两者都是整数，则按照整数值进行比较。
• 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
• 如果两者都是字符串，则按照字符串进行比较。
• 如果两者中至少有一个值是 NULL，则比较的结果是 NULL。
  （2）不等于运算符
  不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较。如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL。例如，关于数字、字符串和表达式的不等于运算符的使用， 具体操作如下所示。

mysql>SELECT 'kgc'<>'bdqn', 1<>2, 3!=3, 2.5!=2, NULL<>NULL ;
+---------------+------+------+--------+------------+
| 'kgc'<>'bdqn' | 1<>2 | 3!=3 | 2.5!=2 | NULL<>NULL |
+---------------+------+------+--------+------------+
|	1 |	1 |	0 |	1 |	NULL |
+---------------+------+------+--------+------------+
1 row in set (0.00 sec)

（3）大于、大于等于、小于、小于等于运算符

• 大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL。
• 小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL。
• 大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL。
• 小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL。
  例如，关于大于、大于等于、小于、小于等于运算符的使用，具体操作如下所示。

mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>4 | 'a'>'b' | 2>=3 | (2+3)>=(1+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
|	1 |	0 |	0 |	1 |	0 |	1 |	1 |	1 |	NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)

（4）IS NULL、IS NOT NULL

• IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0。
• IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0。
  例如，关于数字、字符和 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)

IS NULL 和 IS NOT NULL 一个判断为空，另一个判断不为空，只是有无 NOT 这个关键字的区别，同时返回值不同。
（5）BETWEEN AND
BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间，具体操作 如下所示。

mysql>select 4 BETWEEN 2 AND 6,5 BETWEEN 6 AND 8,'c' BETWEEN 'a' AND 'f';
+-------------------+-------------------+-------------------------+
| 4 BETWEEN 2 AND 6 | 5 BETWEEN 6 AND 8 | 'c' BETWEEN 'a' AND 'f' |
+-------------------+-------------------+-------------------------+
|	1 |	0 |	1 |
+-------------------+-------------------+-------------------------+
1 row in set (0.00 sec)

（6）LEAST、GREATEST

• LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL。
  -GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL。
  例如，若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和GREATEST 来实现，具体操作如下所示。

mysql>SELECT least(1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c');
+--------------+--------------------+-----------------+-----------------------+
| least(1,2,3) | least('a','b','c') | greatest(1,2,3) | greatest('a','b','c') |
+--------------+--------------------+-----------------+-----------------------+
|	1 | a	|	3 | c	|
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)

从以上结果可以看出，LEAST 比较的参数为数字时，返回的是其中最小的值；当比较的参数为字符串时，返回字母表中顺序最靠前的字符。GREATEST 比较的参数为数字时， 返回的是其中最大的值；当比较的参数为字符串时，返回字母表中顺序最靠后的字符。
（7）IN、NOT IN

• IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0。
• NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0。
  例如，判断某数字是否在一组数字中，也可判断某字符是否在一组字符中，具体操作如 下所示。

mysql>SELECT 2 in (1,2,3,4,5),'c' not in ('a','b','c');
+------------------+--------------------------+
| 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
|	1 |	0 |
+------------------+--------------------------+
1 row in set (0.00 sec)

从以上结果可以看出，IN 和 NOT IN 的返回值正好相反。
（8）LIKE、NOT LIKE
LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0。
例如，若要判断某字符串能否匹配成功，分单字符匹配和多字符匹配，也可以判断不匹 配，具体操作如下所示。

mysql>SELECT 'bdqn' LIKE 'bdq_','kgc' LIKE '%c','etc' NOT LIKE '%th';
+--------------------+-----------------+----------------------+
| 'bdqn' LIKE 'bdq_' | 'kgc' LIKE '%c' | 'etc' NOT LIKE '%th' |
+--------------------+-----------------+----------------------+
|	1 |	1 |	1 |
+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)

3.逻辑运算符

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

运算符	描述
NOT 或 ！	逻辑非
AND 或 &&	逻辑与
OR 或 ll	逻辑或
XOR	逻辑异或

逻辑运算符	逻辑功能
&(并且)	有false则false
l(或者)	有true则true。
!(非)	非false则true，非true则false。
^(异或)	相同为false，不同为true。
&&(短路与)	有false则false,若&&左边表达式或者值为false则右边不进行计算
ll(短路或)	有true则true,若

（1）逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示。逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。例如，对非 0 值和 0 值分别作逻辑非运算，具体操作如下所示。

mysql>SELECT not 2,!3,not 0,!(4-4);
+-------+----+-------+--------+
| not 2 | !3 | not 0 | !(4-4) |
+-------+----+-------+--------+
|	0 |   0 |	1 |	1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)

（2）逻辑与

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0。逻辑与使用 AND 或者&&表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑与运算，具体操作如下所示。

mysql>SELECT 2 AND 3,4 && 0,0 && NULL,1 AND NULL;
+---------+--------+-----------+------------+
| 2 AND 3 | 4 && 0 | 0 && NULL | 1 AND NULL |
+---------+--------+-----------+------------+
|	1 |	0 |	0 |	NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)

（3）逻辑或

逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。逻辑或通常使用 OR 或者||来表示。例如，对非 0 值、0 值和 NULL 值分别作逻辑或运算， 具体操作如下所示。

mysql>SELECT 2 OR 3,4 || 0,0 OR NULL,1 || NULL;
+--------+--------+-----------+-----------+
| 2 OR 3 | 4 || 0 | 0 OR NULL | 1 || NULL |
+--------+--------+-----------+-----------+
|	1 |	1 |	NULL |	1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

（4）逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL。例如，对非0 值、0 值和 NULL 值分别作逻辑异或运算，具体操作如下所示。

mysql>SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;
+---------+---------+---------+------------+---------------+
| 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL |
+---------+---------+---------+------------+---------------+
|	0 |	0 |	1 |	NULL |	NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)

4.位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查 看。MySQL 支持 6 种位运算符

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

例如，对数字进行按位与、或和取反运算，具体操作如下所示。

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。
  例如，对数字进行左移或右移的运算，具体操作如下所示。

mysql>SELECT 1<<2, 2<<2,10>>2,15>>2;
+------+------+-------+-------+
| 1<<2 | 2<<2 | 10>>2 | 15>>2 |
+------+------+-------+-------+
|	4 |	8 |	2 |	3 |
+------+------+-------+-------+
1row in set (0.00 sec)

左移或右移运算符，都是将数转换为二进制后，然后在左移或右移指定的位数，超出的位数将被移除并丢弃，空出来的位置用 0 补齐。例如，“2<<2”将数字 2 的二进制数 0010， 向左移动两位后变成 10，右侧用 00 补齐，最终变为二进制的 1000，转换为十进制是 8。“15>>2”将数字 15 转换为二进制是 1111，向右移动两位，右侧的两位 11 被丢弃，变为 11， 左侧用 00 补齐，最终变为二进制的 0011，转换为十进制就是 3。
以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级，可以使用（）改变优先级。常用的运算符优先级情况如表 5-6 所示。“!”的优先级最高，而“:=”的优先级最低。
运算符的优先级

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

5. 连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接。
为了便于理解，这里使用两个表 a_player 和 b_player 来进行演示。首先需要创建这两个表，然后插入一些初始数据。

CREATE TABLE `a_player` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(32) DEFAULT NULL,
`a_level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b_player` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(32) DEFAULT NULL,
`b_level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into a_player(a_id, a_name, a_level) values(1, 'aaaa', 10); insert into a_player(a_id, a_name, a_level) values(2, 'bbbb', 20); insert into a_player(a_id, a_name, a_level) values(3, 'cccc', 30); insert into a_player(a_id, a_name, a_level) values(4, 'dddd', 40);

insert into b_player(b_id, b_name, b_level) values(2, 'bbbb', 20); insert into b_player(b_id, b_name, b_level) values(3, 'cccc', 30); insert into b_player(b_id, b_name, b_level) values(5, 'eeee', 50); insert into b_player(b_id, b_name, b_level) values(6, 'ffff', 60);

1.内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件。内连接的语法格式如下。

SELECT column_name(s)FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。例如，在刚才创建的 a_player 和 b_player 表中使用内连接查询出通过判断 a_id 和 b_id 相等，包含在两个表内的部分，具体操作如下所示。

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

上述操作是将 a_player 表的每一行与 b_player 表的每一行进行比较，以检查它们是否都满足条件。当满足条件时，INNER JOIN 将返回由 a_player 表和 b_player 表中的列组成的新行。如果没有匹配项，查询将返回一个空的结果集。INNER JOIN 结果集中的行必须出现在两个表 a_player 和 b_player 中，是这两个表的交叉部分，具体的 INNER JOIN 工作原理如图

如果表中至少有一个匹配项，INNER JOIN 关键字将返回一行。如果没有匹配项，则不会有输出结果。

2.左连接

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分， 有左连接和右连接之分。
左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。例如，从a_player 和 b_player 表中，查询出 a_player 表中所有内容，并且查询出通过 a_id 和 b_id 相等判断出的 b_player 中的部分，具体操作如下。

+------+--------+---------+------+--------+---------+
| 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 |
+------+--------+---------+------+--------+---------+

从以上左连接查询结果可以看出，除了匹配的行，a_id 和 b_id 是 2 和 3 那两行，还包括了左表中有但右表中没有的行，如果有表没有对应值，则使用 NULL 代替。也就是说在左连接查询中，使用 NULL 值表示右表中没有找到与左表中匹配的记录。左连接的查询原理如图

从原理图可以看出，左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索 条件的记录，也就是图中交叉的部分。右表记录不足的地方均为 NULL。

3.右连接

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。例如，从 a_player 和 b_player 表中，查询出在 b_player 表内的所有记录，并且通过判断 a_id 和 b_id 相等，在 a_player 表内的部分，具体的实现操作如下所示。

mysql>select * from a_player a right join b_player b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| 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 |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹 配的行，这些记录在左表中以 NULL 补足。右连接的实现原理如图