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\\.]';

^有两种用法:

  1. 在集合中(用[和]定义),用它来否定改集合。比如:[^0-9] 表示查询除任意一个数字外的串,只要有包含一个数字的就不匹配,不论位置在哪。
  2. 用来指串的开始处。比如:^[0-9] 表示在文本中查找以一个数字开头的串。

前面说过,LIKE匹配整个串而REGEXO匹配子串。利用定位符,通过用^开始每个表达式,用%结束每个表达式,可用是REGEXP的作用跟LIKE一样。

额外:可用在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0(不匹配)或1(匹配)。可用用待文字串的REGEXP来测试表达式。比如

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

结果很明显返回0。


小结:介绍了正则表达式的基础知识,学习了如何在MySQL的SELECT语句中通过REGEXP关键字使用它们。

赞(0)
微信
支付宝
微信二维码图片

微信扫描二维码打赏

支付宝二维码图片

支付宝扫描二维码打赏

版权属于: sbboke版权所有。

转载时必须以链接形式注明作者和原始出处及本声明。

张贴在生活杂烩标签:

相关文章

0 0 投票数
文章评分
订阅评论
提醒
0 评论
内联反馈
查看所有评论