MySQL必知必会,四数据检索

用通配符举办过滤

mysql-4 数据检索(贰),mysql-4数据检索

用通配符举办过滤

前言

like操作符  %通配符   %方可包容任性字符

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'

997755.com澳门葡京 1

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';

997755.com澳门葡京 2

用通配符进行过滤

like操作符  %通配符   %得以相配大四字符

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'

997755.com澳门葡京 3

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';

997755.com澳门葡京 4

997755.com澳门葡京 5

下划线通配符   下划线只好够合营1个字符

SELECT prod_name , prod_id FROM products WHERE prod_name LIKE '_ ton anvil';

997755.com澳门葡京 6

like操作符  %通配符   %方可包容任意字符

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%'

997755.com澳门葡京 7

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';

997755.com澳门葡京 8

下划线通配符   下划线只可以够相称3个字符

SELECT prod_name , prod_id FROM products WHERE prod_name LIKE '_ ton anvil';

997755.com澳门葡京 9

图为和讯某答主的高分答案,私感到很有道理,于是乎奔《MySQL
必知必会的》而来~

用正则表达式进行查找

下划线通配符   下划线只好够同盟2个字符

SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';

997755.com澳门葡京 10

用正则表明式举行检索

一、基础知识
1.精晓数据库
数据库(database):保存有团体的多少的容器
表(table):某种特定项目数据的结构化清单
情势(schema):关于数据库和表的布局及特点的新闻
列(column):表中的一个字段。全部表都以由2个或八个列组成的
数据类型((datatype):所大概的数据类型。每一个列表都有照顾的数据类型,它界定该列中贮存的数额。
行(row):表中的三个记下
主键(primary
key):一列(或一组列),其值能够唯一区分表中各样行。唯一标记表中每行的那些列称为主键。应该总是定义主键。
表中的任何列都得以看作主键,只要它满意以下规则(定义主键规则):
一)肆意两行都不具备同样的主键值
二)每一种行都必须怀有三个主键值(主键列不一样意NULL值)
2.DBMS
DBMS可分为两类:一依照共享文件系统的DBMS ②基于客户机-服务器的DBMS。
MySQL是1个客户机-服务器DBMS。
3.选拔数据库
USE关键字,如下:
输入USE crashcourse
出口Database changed //阐明数据库选取成功后呈现
4.刺探数据库和表
SHOW命令能够呈现数据库,表,列,用户,权限等消息
SHOW DATABASES;//重临数据库的三个列表。
SHOW TABLES;//重临当前接纳的数据库内可用的列表
SHOW COLUMNS FROM curtomers;//给出内定表名的列音信
SQL语句不区分轻重缓急写

中央字符相称

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

检索prod_name包含文本1000的装有行

 997755.com澳门葡京 11

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

997755.com澳门葡京 12

注意

 SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’ ORDER BY
prod_name;

 997755.com澳门葡京 13

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY
prod_name;

997755.com澳门葡京 14

何以第3like的语句会展现为空呢,那是因为like在协作整个列,假使被相配的文书在列值中冒出,like将不会找它,相应的行也不会被重回,除非是用通配符;而REGEXP在列值内张开相配,要是被匹配的文件在列值中冒出,REGEXP将会找到它,相应的就要被重回,那是二个可怜主要的差距。

 

用正则表明式实行寻找

MySQL必知必会,四数据检索。主干字符相配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

检索prod_name蕴涵文本1000的有所行

 997755.com澳门葡京 15

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

997755.com澳门葡京 16

注意

 SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’ ORDER BY
prod_name;

 997755.com澳门葡京 17

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY
prod_name;

997755.com澳门葡京 18

为什么第2like的语句会呈现为空呢,那是因为like在十一分整个列,如若被相称的文本在列值中冒出,like将不会找它,相应的行也不会被再次来到,除非是用通配符;而REGEXP在列值内开始展览相称,假设被相配的文本在列值中冒出,REGEXP将会找到它,相应的将在被再次来到,那是二个不行重要的歧异。

 

二、找寻数据

进行OR匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

997755.com澳门葡京 19

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

997755.com澳门葡京 20

匹配范围  [1-9]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

997755.com澳门葡京 21

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;   .是匹配任意字符

997755.com澳门葡京 22

为了协作特殊字符,供给运用\\为前导,    \\- 代表查找-

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

997755.com澳门葡京 23

元字符 说明
 * 0个或者多个匹配
+ 1个或多个匹配(等于{1,})
0个或者1个匹配  (等于{0,1})
{n} 制定数目匹配
{n,} 不少于制定数目匹配
{n,m} 匹配数目的范围n到m   m不超过255
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

997755.com澳门葡京 24

sticks?匹配stick 或者sticks   \\(    相称小括号

 

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;

997755.com澳门葡京 25

说明
[:alnum:] 任意字母和数字(同[a-zA-Z0-9])
[:alpha:] 任意字符  (同[a-zA-Z])
[:blank:] 空格和字符表 (同  \\t)
[:cntrl:] ASCLL控制字符,
[:digit:] 任意数字  (同[0-9])
[:graph:] 与print相同,但是不包含空格
[:lower:] 任意小写字母   同([a-z])
[:pirnt:] 任意可以打印的字符
[:upper:] 任意大写字母  同[A-Z]
 [:xdigit:]  任意十六进制数字(同[a-fA-F0-9])

 

主导字符相称

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

检索prod_name蕴含文本一千的保有行

 997755.com澳门葡京 26

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

997755.com澳门葡京 27

注意

 SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’ ORDER BY
prod_name;

 997755.com澳门葡京 28

SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY
prod_name;

997755.com澳门葡京 29

为何第一like的语句会显示为空呢,那是因为like在协作整个列,假诺被相称的文书在列值中冒出,like将不会找它,相应的行也不会被再次回到,除非是用通配符;而REGEXP在列值内张开相称,假诺被相配的文件在列值中出现,REGEXP将会找到它,相应的将在被重临,那是三个特别重大的出入。

 

进行OR匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

997755.com澳门葡京 30

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

997755.com澳门葡京 31

协作范围  [1-9]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

997755.com澳门葡京 32

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;   .是匹配任意字符

997755.com澳门葡京 33

为了同盟特殊字符,必要运用\\为前导,    \\- 代表查找-

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

997755.com澳门葡京 34

元字符 说明
 * 0个或者多个匹配
+ 1个或多个匹配(等于{1,})
0个或者1个匹配  (等于{0,1})
{n} 制定数目匹配
{n,} 不少于制定数目匹配
{n,m} 匹配数目的范围n到m   m不超过255
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

997755.com澳门葡京 35

sticks?匹配stick 或者sticks   \\(    相称小括号

 

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;

997755.com澳门葡京 36

说明
[:alnum:] 任意字母和数字(同[a-zA-Z0-9])
[:alpha:] 任意字符  (同[a-zA-Z])
[:blank:] 空格和字符表 (同  \\t)
[:cntrl:] ASCLL控制字符,
[:digit:] 任意数字  (同[0-9])
[:graph:] 与print相同,但是不包含空格
[:lower:] 任意小写字母   同([a-z])
[:pirnt:] 任意可以打印的字符
[:upper:] 任意大写字母  同[A-Z]
 [:xdigit:]  任意十六进制数字(同[a-fA-F0-9])

 

  1. SELECT语句
    SELECT prod_name FROM products;//检索单个列
    SELECT prod_id, prod_name,prod_price FROM products;//检索八个列
    SELECT * FROM proucts;//检索全部列
    2.mysql> SELECT DISTINCT vend_id FROM
    products;//检索不一致的行,DISTINCT重在字必须一直放在列名的前头。
    3.使用LIMIT界定结果:
    mysql> SELECT prod_name FROM products LIMIT
    五;//限制出口不多于五行
    mysql> SELECT prod_name FROM products LIMIT
    5,5;//从行5开始的5行
    =SELECT prod_name FROM products LIMIT 5 OFFSET 5;
    瞩目索引都以从0开端,所以LIMIT1,壹是从二行而不是率先行初步
    4.完全限定的表名
    SELECT products.prod_name FROM crashcourse.products;

 定位符

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

 

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

以三个数字(包涵小数点开端的数)起头的享有产品

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
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;
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

 

进行OR匹配

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

997755.com澳门葡京 37

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

997755.com澳门葡京 38

相称范围  [1-9]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

997755.com澳门葡京 39

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;   .是匹配任意字符

997755.com澳门葡京 40

为了同盟特殊字符,须求运用\\为前导,    \\- 代表查找-

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

997755.com澳门葡京 41

元字符 说明
 * 0个或者多个匹配
+ 1个或多个匹配(等于{1,})
0个或者1个匹配  (等于{0,1})
{n} 制定数目匹配
{n,} 不少于制定数目匹配
{n,m} 匹配数目的范围n到m   m不超过255
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

997755.com澳门葡京 42

sticks?匹配stick 或者sticks   \\(    相配小括号

 

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;

997755.com澳门葡京 43

说明
[:alnum:] 任意字母和数字(同[a-zA-Z0-9])
[:alpha:] 任意字符  (同[a-zA-Z])
[:blank:] 空格和字符表 (同  \\t)
[:cntrl:] ASCLL控制字符,
[:digit:] 任意数字  (同[0-9])
[:graph:] 与print相同,但是不包含空格
[:lower:] 任意小写字母   同([a-z])
[:pirnt:] 任意可以打印的字符
[:upper:] 任意大写字母  同[A-Z]
 [:xdigit:]  任意十六进制数字(同[a-fA-F0-9])

 

 定位符

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

 

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

以2个数字(包涵小数点开头的数)开头的富有成品

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
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;
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

 

三、排序检索数据
1.** 排序数据**
ORDER BY
SELECT prod_name FROM products ORDER BY prod_name; //排序检索
SELECT prod_id,prod_price,prod_name FROM products ORDER BY
prod_price,prod_name;//按四个列排序
2.** 钦赐排序方向**
DESC降序 ** ASC升序**
SELECT prod_id,prod_price,prod_name FROM products ORDER BY
prod_price DESC;//降序排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY
prod_price DESC,prod_name DESC;//七个列降序
三.应用O福睿斯DECR-V BY与LIMIT的组合能搜索最高或最低值
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT
壹;//获取最值钱的价钱

 定位符

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

 

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

以3个数字(包蕴小数点伊始的数)开始的具有产品

SELECT prod_id , prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id , prod_name FROM products WHERE prod_name LIKE '%anvil%';
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
SELECT prod_name prod_id FROM products WHERE prod_name LIKE '_ ton anvil';
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.' ORDER BY vend_name;
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
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;
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

 

数据检索(贰),mysql-四数据检索
用通配符举行过滤 like操作符 %通配符 %足以合作放四字符 SELECT prod_id ,
prod_name FROM products WHERE prod_name L…

四、过滤数据
1.使用WHERE子句
SELECT prod_name FROM products WHERE prod_price =
2.5;//只返回prod_price值为2.5的行
WHERE子句的岗位:应该在OPAJERODE奥迪Q叁 BY在此之前
2.WHERE子句操作符
=,<>(不等于),!=(不等于), <,<=, >, >=,
BETWEEN(在钦命的多个值时期)
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 3
AND 10;
3.空值检查
在开创表时,表设计职员能够钦命个中的列是不是能够不蕴含值。在一个列不带有值时,称其为带有空值NULL。IS
NULL子句来检查有着NULL值的列
输入SELECT prod_name FROM products WHERE prod_price IS NULL;
输出Empty set (0.00 sec) //表达价格必须非空

五、数码过滤
1.组合WHERE子句
MySQL允许给出三个WHERE子句,这么些子句以二种方法结合:AND和O奔驰M级
1.1 AND操作符
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003
AND prod_price <=10;//五个原则都要满意
1.2 OR操作符
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003
OR vend_id = 100二;//七个规格满意1个就足以
一.三测算次序
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003
OR vend_id = 1002 AND prod_price>=10;//AND操作符优先级高于O途胜
据此地点清楚为:由供应商十0二创立的别的价位为十卢比以上的出品,大概由供应商十0三塑造的其他产品,而任由价格怎么
SELECT prod_id,prod_price,prod_name FROM products WHERE
(vend_id=1003 OR vend_id = 1002) AND
prod_price>=10;//()优先级最高
地点语句精通为:价格在10欧元以上且有由100二或1003塑造的兼具成品

  1. IN操作符
    IN操作符用来钦命条件限制,其效果于O奥迪Q伍操作符格外,但更驾驭直观,而且能够包涵其余SELECT语句,是的能够退换态的建立where子句。
    SELECT prod_price,prod_name FROM products WHERE vend_id IN
    (1001,1002,1003) ORDER BY prod_name;
    3.** NOT操作符**
    NOT操作符:否定它之后所跟的其余条件。
    SELECT prod_price,prod_name FROM products WHERE vend_id NOT
    IN(1002,1003,1001) ORDER BY prod_name;

六、通用配符进行过滤

  1. LIKE操作符
    通配符(wildcard):用来相配值的壹某个的特殊字符
    追寻情势(searchpattern):由字面值、通配符或双方结合构成的追寻条件(能够分别轻重缓急写)
    LIKE提醒MySQL,后跟的探求方式应用通配符相称而不是一向相等相配进行相比较
    1.1 百分号(%)通配符
    %代表别的字符出现任性次数,但不可能相称NULL
    SELECT prod_price,prod_name FROM products WHERE prod_name
    LIKE’JET%’;
    SELECT prod_price,prod_name FROM products WHERE prod_name
    LIKE’%ANVIL%’;
    %代表寻觅情势中加以地方的0个、2个恐怕三个字符。
    1.2下划线(_)通配符
    下划线只相称单个字符
    二.通配符本事
    一)不要过于使用通配符
    2)把通配符放在起初处,搜索速度最慢

七、用正则表明式举办检索
正则表明式是用来协作文本的相当规的串(字符群集)。
一.使用mysql正则表明式
主干字符相称
SELECT prod_name FROM products WHERE prod_name REGEXP ‘.000’ ORDER BY
prod_name;
+————–+
| prod_name |
+————–+
| JetPack 1000 |
| JetPack 2000 |
+————–+
上边语句使用了正则表明式.000。.是正则表明式语言中一个卓绝的字符。他表示非凡大四二个字符,由此,一千和两千都匹配且重临。
正则表明式相称不区分轻重缓急写,要分化轻重缓急写能够在REGEXP前面使用BINAEscortY关键字。
2.进行or匹配
SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000 |
2000’;
这里的|是正则表达式的O卡宴操作符
3.相配多少个字符之一
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name;
这里的[123]意味着相配1或二或三。
4.协作范围
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[1-5] Ton’;
这里[1-5]概念了2个限制
5.合营特殊字符
SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\.’ ORDER BY
vend_name;
为了协作特殊字符,必须用[\为前导]
\-表示-
\.表示.
\\表示
\997755.com澳门葡京,f表示换页
\n表示换行
\r表示回车
\t表示制表
\v表示纵向制表
6.相称字符类

997755.com澳门葡京 44

7.分外多少个实例

997755.com澳门葡京 45

看下边七个例子:
SELECT prod_name FROM products WHERE prod_name REGEXP ‘\([0-9]
sticks?\)’ ORDER BY prod_name;//结果如下
+—————-+
| prod_name |
+—————-+
| TNT (1 stick) |
| TNT (5 sticks) |
+—————-+

正则表达式[\([0-9]sticks?\]内需证实一下。\(匹配(,[0-9]相配任性数字(这些例子中为一和5),sticks?相称stick和sticks(s后的?使s可选,因为?相配它后面包车型大巴别样字符的0次或3遍面世),\)匹配。
SELECT prod_name FROM products WHERE prod_name REGEXP
‘[[:digit:]]{4}’ ORDER BY prod_name;
+————–+
| prod_name |
+————–+
| JetPack 1000 |
| JetPack 2000 |
+————–+
2 rows in set (0.00 sec)
[[:digit:]]{四}中{四}供给它前边的字符(放肆数字)现身柒次,所以[[:digit:]]{4}表示连在一齐的即兴3位数
8.定位符
^ 文本的起始
$ 文本的末段
[[:<:]] 词的开头
[[:>:]] 词的结尾
SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9\.]’
ORDER BY prod_name;
^[0-9\.]意味着只在.或专擅数字为串中首先个字符时才相称它们。
^的重复用途
①用在[]里表示否认该群集,贰用来指串的起始处。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website