【数据库笔记】MySQL必知必会：chapter 4-9 检索/排序/过滤数据 select语句 | order by 子句| where子句 | 通配符过滤 | 正则表达式过滤

---

chapter 4 检索数据–select 语句

select prod_name from products;--检索单列
select prod_id,prod_name from products;--检索多列
select * from products;--检索所有列

select distinct prod_id from products;--检索不同行

select prod_name from products limit 5;--限制结果,返回前5行（索引[0,4]）
select prod_name from products limit 5,5;--限制结果,返回第二个5行（索引[5,9]）,第一个数为开始位置，第二个数为检索行数

select products.prod_name from products;--使用完全限定的名字来引用列
select products.prod_name from database_name.products;--使用完全限定的名字来引用表

limit 用法的补充说明

• 行0：检索出来的第一行为行0，而不是行1，因此limit 1,1将返回第二行。
• 行数不够时：如limit 10,5 而实际中只有13行，那么MySQL只返回能返回的行数。
• offset：limit 4 offset 3 意为从行3（索引为3的行，实际中的第4行）开始取4行。
  也就是说下面两种写法是等价的。

select * from salaries
limit 3,4;

select * from salaries
limit 4 offset 3;

chapter 5 排序检索数据–order by子句

select 出的数据如果不排序，数据一般以它在底层表中的出现顺序显示，可以使数据最初添加到表中的顺序。但是如果数据之后经过更新或删除，则此顺序会受到MySQL重用回收存储空间的影响。

因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。

select prod_name 
from products
order by prod_name--以prod_name列字母顺序排序

select prod_name 
from products
order by prod_id,prod_name--以多个列排序,先按id,再按名字

select prod_name 
from products
order by prod_id desc,prod_name asc --以多个列排序,先按id降序,再按名字升序

chapter 6 过滤数据–where子句

数据库表中包含大量数据，很少需要检索表中所有行。通常会根据需求提取数据的子集。

只检索所需数据需要制定搜索条件（search criteria）,搜索条件也称为过滤条件（filter condition）。

select prod_name,prod_price
from products
where prod_price=2.50--where过滤条件,单值
order by prod_id

select prod_name,prod_price
from products
where prod_price between 5 and 10--where过滤条件，范围

select prod_name,prod_price
from products
where prod_price is NULL--where过滤条件，空值检查
--NULL值与0,空字符串或空格 不同

操作符	说明
=	等于
<>、!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	在两个值之间（闭区间）

chapter 7 过滤数据–where子句高阶用法

操作符（operator）：用来联结或改变where子句中的子句的关键字。也称逻辑操作符（logical operator）。

select prod_id,prod_name,prod_price
from products
where prod_id=1003 and prod_price<=10 --AND操作符

select prod_id,prod_name,prod_price
from products
where prod_id=1002 or prod_id=1003 --OR操作符

select prod_id,prod_name,prod_price
from products
where prod_id=1002 or prod_id=1003 and prod_price<=10 --计算次序：目标找出10元以下且由1002或1003组成的产品
--该查询不能满足要求,因为SQL优先处理AND,再处理OR
--该查询结果为1003的价格低于10的产品 或者1002的产品无论价格如何

--解决方案：加括号
select prod_id,prod_name,prod_price
from products
where (prod_id=1002 or prod_id=1003) and prod_price<=10

--括号的另一种用法：in操作符来制定条件范围
select prod_id,prod_name,prod_price
from products
where prod_id in (1002,1003)--IN操作符 相当于 OR

为什么要用IN操作符

1. 使用较长的合法选项清单时，IN操作符更清晰直观；
2. 计算的次序更容易管理；
3. IN操作符一般比OR操作符执行快；
4. IN操作符最大优点——可以包含其他的select语句，建立更动态的where子句。

--NOT操作符:否定它之后的任何条件
select prod_id,prod_name,prod_price
from products
where prod_id not in (1002,1003)--NOT操作符

为什么要用NOT操作符

1. 复杂情况下与in连用
2. MySQL 的NOT 支持与IN / BETWEEN / EXISTS子句取反。

chapter 8 用通配符进行过滤

之前涉及的操作符（operator）【and,or,not,in】都是对已知值进行过滤的。无论是单值、多值，或者检查某个范围，使用的值都是已知的。但这种方法不是任何时候的好用。

比如，怎么搜索产品名中包含文本‘anvil’的所有产品？

通配符（wildcard）：用来匹配值的一部分的特殊字符。本身是SQL的where子句中有特殊含义的字符，使用时必须搭配like操作符。

搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。

操作符何时不是操作符？
答案是：当它作为谓词时。从技术上说like是谓词而不是操作符。

百分号（%）通配符

select prod_id,prod_name
from products
where prod_name like 'jet%'--百分号(%)通配符,表示任何字符出现任意次数,此处意为找出所以以jet开头的产品
--区分大小写,此与JetPack不匹配

select prod_id,prod_name
from products
where prod_name like '%jet%'--通配符可以在搜索模式中的任意位置使用,并且可以使用多个通配符,此处%位于模式的两端,表示匹配任何位置包含文本jet的值,无论前后出现什么

select prod_id,prod_name
from products
where prod_name like 'j%t'--通配符可以在搜索模式中的任意位置使用

% 能匹配0个、1个或多个字符
但是有一个例外——NULL
即使是where prod_name like ‘%’ 也不能匹配NULL

下划线（_）通配符

下划线（_）通配符的用途与%一样，但是只能匹配 1 个字符，而不是多个。

select prod_id,prod_name
from products
where prod_name like '_ ton anvil' --匹配到的结果有
--1 ton anvil
--2 ton anvil

select prod_id,prod_name
from products
where prod_name like '% ton anvil' --匹配到的结果有
--.5 ton anvil
--1 ton anvil
--2 ton anvil

MySQL的通配符很有用，但是这种功能是有代价的：通配搜索的处理时间一般比较长。

使用通配符的技巧：

• 不要过度使用通配符。如果其他操作符能达到同样的结果，应使用其他的操作符。
• 除非绝对必要，否则不要把他们用在搜索模式的最开始处。放在最开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错，可能不会返回想要的结果。

chapter 9 用正则表达式进行搜索

where子句中使用正则表达式来更好地控制数据过滤。

更透彻的介绍：《正则表达式必知必会》

9.1 正则表达式介绍

前两章中，过滤使用到匹配、比较和通配操作符。对于基本的过滤，这样足够了。但是随着过滤复杂性的增加，where子句本身的复杂性也有必要增加。

正则表达式：用来匹配文本的特殊的串（字符集合）。

9.2 使用MySQL正则表达式

9.2.1 基本字符匹配

select prod_name
from products
where prod_name regexp '1000'
order by prod_name-- 除关键字 like 被 regexp 代替,这条语句非常像like语句
--此意为：regexp后面跟的东西作为正则表达式处理（与文字正文1000匹配的一个正则表达式）
--返回结果
--JetPack 1000

上例中，正则表达式并未带来太多好处，可能还会降低性能，但是在复杂的实际场景中，它会发挥巨大的价值。如下例：

select prod_name
from products
where prod_name regexp '.000'
order by prod_name
--此处应用了正则表达式'.000','.'是正则表达式中一个特殊的字符,表示匹配任意一个字符,因此1000和2000都匹配返回
--返回结果
--JetPack 1000
--JetPack 2000

like与regexp的区别

select prod_name
from products
where prod_name like '1000'
order by prod_name;
--返回结果
--不返回数据
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
--返回结果
--JetPack 1000

like匹配整个列，如果被匹配的文本在列值中出现，like不会找到它，相应的行也不会返回（除非使用通配符）。

regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp会找到它，相应的行将被返回。

regexp能否用于匹配整个列值（发挥与like相同的作用）？
可以，使用^和$定位符（anchor）即可。

9.2.2 OR匹配

为搜索两个串之一（这个串，或另一个串），使用 | 。

select prod_name
from products
where prod_name regexp '1000|2000|3000'
order by prod_name;
--返回结果
--JetPack 1000
--JetPack 2000
--JetPack 3000

9.2.3 匹配几个字符之一

匹配任何单一字符。

如果只想匹配特定字符怎么办？ 可通过指定一组用[和]括起来的字符来完成。

select prod_name
from products
where prod_name regexp '[123] Ton'
order by prod_name;
--regexp 对大小写不敏感,且需要注意空格

--正则表达式[123]意为：匹配1或2或3
--[] 是另一种形式的OR语句,[123]是[1|2|3]的缩写
--[1|2|3] ton 的意思与1|2|3 ton 的意思不同，1|2|3 ton 表示1或2或3 ton

--字符集合也可以被否定
--正则表达式[^123]意为：匹配除却1/2/3这些字符外的任何东西

--返回结果
--1 ton anvil
--2 ton anvil
--3 ton anvil

9.2.4 匹配范围

集合可用来定义要匹配的1个或多个字符.

如[0123456789]可简化为[0-9].
同理，[a-z]匹配任意字母字符。

select prod_name
from products
where prod_name regexp '[1-5] Ton'
order by prod_name;
--正则表达式'[1-5] Ton',[1-5]定义了一个范围

--返回结果
--.5 ton anvil
--1 ton anvil
--2 ton anvil

9.2.5 匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成，如’.’、’[]’、‘|’、‘-’等。如果要匹配’.’、’[]’、‘|’、‘-’这些字符，应该怎么办？

select vend_name
from vendors
where vend_name regexp '.'
order by vend_name
--此处每个行都被检索出来,'.'匹配任意字符
--为了匹配特殊字符,必须用\\为前导,\\-表示查找-,\\.表示查找.
--返回结果
--ACME
--Anvils R Us
--Furball Inc.
--Jet Set
--Jouets Et Ours
--LT Supplies

select vend_name
from vendors
where vend_name regexp '\\.'
order by vend_name
--这种处理就是所谓的转义(escaping)
--返回结果
--Furball Inc.

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\	制表
\\v	纵向制表
\\\	匹配反斜杠\

9.2.6 匹配特殊字符

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

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

9.2.7 匹配多个实例

目前为止，所有正则表达式都试图匹配单词出现。对于重复的情况，可以用重复元字符来完成。

重复元字符	说明
*	0个或多个匹配
+	1个或多个匹配（同{1,}）
?	0个或1个匹配（同{0,1}）
{n}	指定数目匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m<=255）

select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name

--返回结果
--TNT (1 stick)
--TNT (5 sticks)

正则表达式’\\([0-9] sticks?\\)'
\\( 匹配(
[0-9] 匹配 任意数字
sticks? 匹配stick 或 sticks
因为?匹配它前面的字符0次或1次
\\) 匹配)

select prod_name
from products
where prod_name regexp '[[:digit:]]{4}'
order by prod_name

--等价于

select prod_name
from products
where prod_name regexp '[0-9][0-9][0-9][0-9]'
order by prod_name

--返回结果
--JetPack 1000
--JetPack 2000

正则表达式'[[:digit:]]{4}'
[:digit:] 匹配任意数字，因此它是数字的一个集合
{4} 确切地要求它前面的字符（任意数字）出现4次
所以在外层再套一层[] 得到[[:digit:]]，最终匹配连在一起的任意4位数字。

9.2.8 定位符

目前为止，所有正则表达式都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用定位符。

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

select prod_name
from products
where prod_name regexp '^[0-9\\.]'
order by prod_name

--返回结果
--.5 ton anvil
--1 ton anvil
--2 ton anvil

正则表达式'^[0-9\\.]'
^只在.或任意数字为串中第一个字符时才匹配它们。如果没有^，则还要多检索出别的行，即那些中间有数字或.的行。

^的双重用途
（1）在集合中（用[和]定义），用它来否定该集合； （2）在集合外，用它来指串的开始处。

简单的正则表达式测试

在不适用数据库表的情况下（不用from table_name）,直接用select来测试正则表达式。

regexp总是返回0（没有匹配）或1（匹配）。

可以用带文字串的regexp来测试表达式，并试验它们。

select 'hello' regexp '[0-9]'

--因为hello中没有数字,所以返回0 (没有匹配)

--返回结果
--0