MySQL快速回顾：高级查询操作
8.1 排序数据
检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到mysql重用回收存储空间的影响。因此，如果不明确控制的话，不能依赖该排序顺序。
关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
子句的解释：一个子句通常由一个关键字和所提供的数据组成。sql语句由子句构成，有些子句是必需的，而有些是可选的。
为了明确地排序用select语句检索出的数据，可使用order by子句。order by子句取一个或多个列的名字，据此对输出进行排序。排序顺序有两种：升序（asc关键字）和降序（desc关键字），
格式：
# 升序（默认不写就是升序） select field1,field2,... from <table_name> order by field1,field2,...; select field1,field2,... from <table_name> order by field1,field2,... ; # 降序 select field1,field2,... from <table_name> order by field1,field2,... desc; # 对field1降序，对field2升序（类似这样的例子也是可以的） select field1,field2,... from <table_name> order by field1 desc,field2 asc,... ;
注意：
- 通常，brder by子句中使用的列将是为显示所选择的列，但是，实际上并不一定要这样，用非检索的列排序数据也是完全合法的。
- 对于多列的排序，仅在多个行具有相同的field1值时才会按field2排序。也就是优先按order by子句后的第一个列进行排序，当遇到相同的列，则会按照order by子句后的第二列来排序，以此类推。
- 也可以先对order by子句后的第一个列进行升序或降序，对order by子句后的第二个列进行降序或升序。
- 在对文本数据性的数据进行排序时，a与a相同吗？a位于b之前还是位于z之后？这些问题不是理论问题，其答案取决于数据库如何设置。
- 在字典（dictionary）排序顺序中，a被视为与a相同，这是mysql（和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果数据库包含大量外语字符，可能必须这样做）。如果确实需要改变这种排序顺序，用order by子句做不到，必须要有数据库管理员的帮助。
- order by子句的位置，应该保证它位于from 子句之后。 因为很多关键字是可以组合一起的，比如使用limit，它必须位于order by子句之后。使用子句的次序不对将产生错误消息。
- 对于字符串（varchar）的排序：
- 对于英文字符串的排序，一般会根据字符串的首字母：大些字母>小写字母>特殊字符>数字。如果首位相同继续排列第二位，直到不同的位。
- 对于中文的排序：中文字符串都大于英文字符串，其他的还得根据数据库所设置的字符集。比如utf8,gbk等。对于gbk直接排序即可，对于utf8需要对字段进行转码后排序。然后会从中文字符串的第一个字符的首个字母开始排序。
# utf8
select fiedl1,... from <表名>
order by convert(field1 using gbk);
单列，多列的升序和降序排序例子就不说了，如果使用order by子句和limit的组合，则可以找出表的一个列中的最大或最小的值。
小结：学习了如何用select语句的order by子句对检索出的数据进行排序。
8.2 过滤数据
如果想要按某条件从表中查询数据，那么就需要使用where子句来指定搜索条件（过滤条件）进行过滤。where子句在表名（from子句）后给出。
格式：
select field1,field2,... from <表名> where condition1 and/or condition2 ...;
- 在同时使用order by和where子句时，应该让order by位于where之后，否则将会产生错误。
- mysql在执行匹配时默认不区分大小写。
- 用非检索的列做搜索条件也是可以的。
搜索条件是一个表达式，它支持以下操作符
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between..and.. | 在指定两个值之间（都包括） |
单值查询：查询学生表中名为李四的学生
select stu_name, stu_sex from student where stu_name='李四';
不匹配查询：查询学生表中女性的学生
select stu_name, stu_sex from student where stu_sex<>'男';
范围查询：查询学生表中id从2到4的学生
select stu_name, stu_sex from student where stu_id between 2 and 4;
- between匹配范围中所有的值，包括指定的开始值和结束值。
空值检查：空值就是null，空值不是0不是空字符串，使用where子句也可以查询，下面的例子不是很好，但搜索条件的格式就是：is null。
select stu_name, stu_sex from student where stu_sex is null;
小结：介绍了如何使用select语句的where子句过滤返回的数据。也学习了如何对相等、不相等、大于、小于、值得范围以及null值等测试。
8.3 组合where子句
可以使用逻辑操作符来进行多个条件的组合查询。
8.3.1 and操作符
and：用在where子句中的关键字，表示检索满足所有给定条件的行。
格式：
select field1,field2,... from <表名> where condition1 and condition2 and ...;
可以有多个过滤条件。
8.3.2 or操作符
or：用在where子句中的关键字，表示检索匹配任一给定条件的行。（也就是有任意一个条件满足就输出，不用全部同时匹配）
格式：
select field1,field2,... from <表名> where condition1 or condition2 or ...;
8.3.3 计算次序
where子句可包含任意数目的and和or操作符。并且允许两者结合以进行复杂和高级的过滤。
但是要注意这两个操作符的优先级问题：mysql优先处理and操作符.
任何时候使用具有and和or操作符的where子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的结果也是如此。而且使用圆括号也没什么坏处，还可以消除歧义。
8.3.4 in操作符
in：用来指定条件范围，范围中的每一个条件都可以进行匹配。in取合法值的由逗号分隔的清单，全都括在圆括号中。
格式：
select field1,field2,... from <表名> where field1 in(n,m,...);
in操作符跟or操作符的功能是一样的。
select field1,field2,... from <表名> where field1 in(n,m,...); # 等于 select field1,field2,... from <表名> where field1=n or field1=m or ...;
in操作符的优点如下：
- 在使用长的合法选项清单时，in操作符的语法更清楚且更直观。
- 在使用in时，计算的次序更容易管理（因为使用的操作符更少）。
- in操作符一般比or操作符清单执行更快。
- in的最大优点是可以包含其他select语句，使得能够更动态地建立where子句。（子查询以后再说）
8.3.5 not操作符
where子句中得not操作符有且只有一个功能，那就是否定它之后所跟得任何条件。
mysql支持使用not对in、between和exist子句取反，这与多数其他dbms允许使用not对各种条件取反有很大差别。
例子：
# 查询学生表中学生的性别不为空的学生 select stu_name, stu_sex from student where stu_sex is not null; # 查询学生表中学生的姓名不为小乔的学生 select stu_name, stu_sex from student where not stu_name='小乔'; # 如果是带有逻辑运算符，not只作用于一个搜索条件，除非用括号括起来 select stu_name, stu_sex from student where not (stu_name='小乔' or stu_name='大乔');
8.4 用通配符进行过滤
前面的都是针对具体值过滤的。但有时需要进行模糊查询，比如：查询某个字符或子字符串开头的字符串，有时查询包含某个字符或子字符串的字符串，有时查询某个字符或子字符串结尾的字符串。那么就需要构造一个通配符搜索模式来查询。
通配符（wildcard）：用来匹配值的一部分的特殊字符。通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。
搜索模式（search pattern）由字面值，通配符或两者组合构成的搜索条件
为在搜索子句中使用通配符，必须使用like操作符。like操作符表示mysql后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
8.4.1 百分号通配符
百分号通配符（%）：表示任何字符出现任意次数。也就是能够匹配出现0次以上的字符。
# 以xx开头或以xx开头的字符串 select field1,field2,... from <table_name> where field1 like 'xx%'; # 包含xx或包含xx的字符串 select field1,field2,... from <table_name> where field1 like '%xx%'; # 以xx结尾或以xx结尾的字符串 select field1,field2,... from <table_name> where field1 like '%xx';
注意：mysql是不区分大小写的，也就是'xx%'可以匹配'xx%'和'xx%'。
如果想要在查询时区分大小写，可以加个binary关键字来区分大小写：
# 包含xx的字符串 select field1,field2,... from <table_name> where binary field1 like '%xx%';
也可以在创建表的时候，在列定义上加上binary关键字，就表示该列区分大小写。
create table <table_name>(
field1 binary,
...
);
虽然看似%通配符可以匹配任何字符，但是有一个例外，即null。即使用搜索模式：'%'，也不会匹配到null。
还得注意尾空格，尾空格可能会干扰通配符匹配，比如搜索模式为：'%xx '（有个空格）就不会匹配以xx结尾或以xx结尾的字符串，而是会匹配以xx 结尾（有个空格）或以xx 结尾（有个空格）的字符串。即加了尾空格表示匹配具体的值了，即尾空格。如果想要避免，最好使用函数（trim(搜索模式)）来去掉。(函数待说，可以先看这个去空格的函数)
select stu_name from student where stu_name like trim('%xx ');
8.4.2 下划线通配符
下划线通配符（_）:只匹配单个字符。即1个，不能多也不能少，否则就没有。
# 以xx开头或以xx开头长度为3的字符串 select field1,field2,... from <table_name> where field1 like 'xx_'; # 包含xx或包含xx长度为4的字符串 select field1,field2,... from <table_name> where field1 like '_xx_'; # 以xx结尾或以xx结尾长度为3的的字符串 select field1,field2,... from <table_name> where field1 like '_xx';
百分号通配符和下划线通配符可以一起使用。
8.4.3 使用通配符的技巧
可能注意到了，在搜索模式中不使用通配符，即具体值也可以匹配，如下：
select field1,field2,... from <table_name> where field1 like 'xx'; # 等于 select field1,field2,... from <table_name> where field1 = 'xx%';
两者都可以搜索具体值，使用 哪种好？通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。
下面给出使用通配符要记住的技巧：
- 不要过渡使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
- 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
总结目前可以优化数据库的点子：
- 表定义数据类型的选择；
- 不要过渡使用通配符，要使用时，除非有必要，否则别使用通配符开搜索模式的开始处。
小结：介绍了如何用and和or操作符组合成where子句，而且还介绍了如何明确地管理计算的次序，如何使用in和not操作符。
8.5 使用正则表达式进行搜索
前面的过滤例子允许使用匹配、比较和通配操作符查询数据。对于基本的过滤，学这些就足够了。但是随着过滤条件的复杂性的增加，where子句本身的复杂性也有必要增加。
这就是正则表达式变得有用的地方。正则表达式是用来匹配文本的特殊的串（字符集合）。
下面只是做个基础的正则表达式学习，如果要完整覆盖正则表达式的以后再整。
正则表达式的作用是匹配文本，将一个模式（正则表达式）和一个文本串进行比较。mysql用where子句对正则表达式提供了初步的支持，，使用regexp关键字指定正则表达式，过滤select检索出的数据。
mysql仅支持多数正则表达式实现的一个很小的子集。
8.5.1 基本字符匹配
格式：
# fieldn的n表示一个整数。 select field1,field2,... from <table_name> where fieldn regexp '字符串';
比如：
# 会匹配包含乔的姓名的学生。 select stu_name from student where stu_name regexp '乔';
当然这个例子也可以使用like和通配符来操作。下面说一下它们的区别：
- like匹配整个列。如果被匹配的文本在列值中出现，like将不会找到它，相应的行也不被返回（除非使用通配符）。
- regexp关键字在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回。
mysql的正则表达式匹配还是不区分大小写。如果要区分大小写，使用binary关键字，上面也有说。比如：
..... where binary fieldn regexp '...'; # 或 ..... where fieldn regexp binary '...';
还可用使用 . （点），. （点）是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符。
# fieldn的n表示一个整数。 select field1,field2,... from <table_name> where fieldn regexp '.字符串';
注意：上面语句中的 .（点）是表示匹配任意一个字符。而不是表示普通键盘上的特殊字符.（点）。待会会将怎么匹配特殊字符。
8.5.2 进行or匹配
or操作符前面讲了，在这里的功能也是一样：为搜索两个串之一，但是注意写法：
select field1,field2,... from <table_name> where fieldn regexp '字符串1|字符串2|...';
即：使用 | 表示or操作符。
8.5.3 匹配几个字符之一
正则表达式or操作符的另一种写法：[]，比如：
select field1,field2,... from <table_name> where fieldn regexp '[字符1字符2...] ...';
[字符1字符2...]：定义一组字符，它的意思是 匹配字符1或匹配字符2等等。
也可以匹配字符串
select field1,field2,... from <table_name> where fieldn regexp '[字符串1, 字符串2...] ...';
[字符串1, 字符串2...]：定义一组字符串，匹配字符串1或匹配字符串1中的字符或匹配字符串2或匹配字符串2中的字符等等。
[] 形式的使用主要是用于有公共的部分，比如我要查询姓周，周后面带有 杰 或 雨 的同学（注意：不是包含）就可以这样使用。
select stu_name from student where stu_name regexp binary '周[雨杰]';
匹配：周杰、周雨、周杰xxx、周雨xxx等。
按上面的例子，下面的这种写法：
select stu_name from student where stu_name regexp binary '周杰|雨';
如果这样写，匹配的是：周杰或雨。
上面的例子除非把字符|括在一个集合中，如：(杰|雨)，否则它将应用于整个串。 懂了为什么有or为什么要有：[]的形式了吧。
字符集合还可以被否定，即，它们将匹配指定字符外的任何东西。只需要在集合的开始放置一个^即可。比如：[^123]表示匹配除1或2或3外的任何东西。
8.5.4 匹配范围
集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：
[0123456789]
为简化这种类型的集合，可使用-来定义个范围。下面的式子等同于上述数字列表：
[0-9]
范围不限于完整的集合，[1-3]和[4-9]都是合法的范围。
下面是一些基础的字符集总结：
[abc]：表示可能是字符a或是字符b或是字符c中的任意一位 [^abc]：表示不是字符a或是字符b或是字符c中的任意一位 [a-z]：所有的小写字母 [^a-z]: 不匹配所有的小写字母 [a-za-z]：字母中的任意一位 [^a-za-z]或[^(a-za-z)]：不匹配字母 [a-z]：所有的大写字母 [^a-z]：不匹配所有的大写字母 [0-9]：表示任意一位数字（只有一位，则范围为0到9） [^0-9]：表示不是任意一位数字
8.5.5 匹配特殊字符
正则表达式语言由具有特定含义的特殊字符构成。比如前面学的.（点）、[]、|、-等，还有一些其他字符。但是这些字符是有特殊含义的，如果要匹配这些特殊字符要怎么做？
为了匹配特殊字符，必须使用\\为前导。即：\\- 表示查询特殊字符-； \\. 表示查询特殊字符.（点），\\\ 表示查询特殊字符。
这种处理称为转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。
\\也用来引用元字符（具有特殊含义的字符）：
| 元字符 | 说明 |
|---|---|
| \\f | 换页 |
| \\n | 换行 |
| \\r | 回车 |
| \\t | 制表 |
| \\v | 纵向制表 |
多半正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但mysql要求两个反斜杠（mysql自己解释一个，正则表达式解释另一个）
8.5.6 匹配字符类
存在找出自己经常使用的数字、所有的字母字符或所有数字字符等的匹配。为了方便工作，可用使用预定义的字符集，称为字符类（character class）
| 类 | 说明 |
|---|---|
| [:alnum:] | 任意字母和数字（同[a-za-z0-9]） |
| [:alpha:] | 任意字符（同[a-za-z]） |
| [:blank:] | 空格和制表（同[\\t]） |
| [:cntrl:] | ascii控制字符（ascii 0到31和127） |
| [:digit:] | 任意数字（同[0-9） |
| [:graph:] | 与[:print:]相同，但不包括空格 |
| [:lower:] | 任意小写字母（同[a-z]） |
| [:print:] | 任意可打印字符 |
| [:punct:] | 即不在[:alnum:]又不在[:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]） |
| [:upper:] | 任意大写字母（同[a-z]） |
| [:xdigit:] | 任意十六进制数字（同[a-fa-f0-9]） |
不用死记，忘了就看。
8.5.7 匹配多个实例
到目前为止的所有正则表达式都是试图匹配单次出现。有时需要对匹配的数目进行更强的控制，那么就需要下面的重复元字符来完成：
| 元字符 | 说明 |
|---|---|
| * | 0个或多个匹配 |
| + | 1个或多个匹配（等一{1,}） |
| ? | 0个或1个匹配（等于{0,1}） |
| {n} | 指定数目的匹配 |
| {n,} | 不少于指定数目的匹配 |
| {n,m} | 匹配数目的范围（m不超过255） |
比如：
匹配17级学生的所有学号，17级都是以2017开头的，然后后面有8位任意数字
... regexp '2017[0-9]{8}';
# 或
... regexp '2017[[:digit:]]{8}';
[[:digit:]]{8}的解释：[:digit:]匹配任意数字，因而它为数字的一个集合。{8}确切要求它前面的字符（任意数字）出现8次，所以应该写成这样[[:digit:]]{8}：匹配连在一起的任意4位数字。
当然也可以这样写：
... regexp '2017[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
8.5.8 定位符
目前为止的所有例子都是匹配一个串中的任意位置的文本。为了匹配特定的位置，需要定位符：
元字符 | 说明
---|---
^ | 文本的开始（^有两种意义）
$ | 文本的结尾
[[:<:]] | 词的开始
[[:>:]] | 词的结尾
例如，如果想要找出以一个数（包括小数点开始的数）开始的所有产品，要怎么办？简单搜索[0-9\\.]（或[[:digit:]\\.]）吗？这样是不行的，因为它将在文本内的任意位置查找匹配。解决的方式就是使用^定位符，如下：
... regexp '^[0-9\\.]';
^有两种用法：
- 在集合中（用[和]定义），用它来否定改集合。比如：[^0-9] 表示查询除任意一个数字外的串，只要有包含一个数字的就不匹配，不论位置在哪。
- 用来指串的开始处。比如：^[0-9] 表示在文本中查找以一个数字开头的串。
前面说过，like匹配整个串而regexo匹配子串。利用定位符，通过用^开始每个表达式，用%结束每个表达式，可用是regexp的作用跟like一样。
额外：可用在不使用数据库表的情况下用select来测试正则表达式。regexp检查总是返回0（不匹配）或1（匹配）。可用用待文字串的regexp来测试表达式。比如
select 'hello' regexp '[0-9]';
结果很明显返回0。
小结：介绍了正则表达式的基础知识，学习了如何在mysql的select语句中通过regexp关键字使用它们。