文章目录
第9章 使用正则表达式搜索
提要: 本章主要介绍了正则表达式在where子句中的使用
正则表达式: 是用来匹配文本的特殊的串(字符集合)。
1:
查询地址包含北京的数据
SELECT * FROM `user` WHERE address REGEXP '北京';
2:
LIKE 与 REGEXP
SELECT * FROM `user` WHERE address LIKE '北京';
SELECT * FROM `user` WHERE address REGEXP '北京';
SELECT * FROM `user` WHERE address REGEXP '^北京$';
注意:第一条sql查到0条数据,第二条查到2条数据, 第三条sql查到0条数据。
3:
BINARY:区分大小写
SELECT * FROM `user` WHERE `name` REGEXP BINARY 'd'; -- 两条
SELECT * FROM `user` WHERE `name` REGEXP 'd'; -- 三条
4:
“|”:或的使用
SELECT * FROM `user` WHERE address REGEXP '昌平|东城'; -- 两条
5:
“[]”:匹配某一个
SELECT * FROM `user` WHERE address REGEXP '[东西南北]'; -- 三条:北京市和东城区
6:
“^”:非的使用
SELECT * FROM `user` WHERE address REGEXP '[^东西南北]'; -- 匹配全部数据
SELECT * FROM `user` WHERE address REGEXP '[^东西南|北京市]'; -- 除北京市的数据
注意:以上说明“[东西南北]”和“[^东西南北]”并不是完全对立的。
7:
“-”:范围
SELECT * FROM `user` WHERE `name` REGEXP '^[a-h]'; -- 4条,名字以d和h开头的
8:
“\\”:转义
SELECT * FROM `user` WHERE address REGEXP '[a-b]'; -- 0条
SELECT * FROM `user` WHERE address REGEXP '[a\\-b]'; -- 1条
空白元字符
元字符 | 说明 |
---|---|
\\f | 换页 |
\\n | 换行 |
\\r | 回车 |
\\t | 制表 |
\\v | 纵向制表 |
**9:**匹配字符类
SELECT * FROM `user` WHERE `age` REGEXP '[:alpha:]'; -- 0条
SELECT * FROM `user` WHERE `age` REGEXP '[:alnum:]'; -- 7条
字符类
类 | 说明 |
---|---|
[: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]) |
10:
匹配多个
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配(等于{1,}) |
? | 0个或1个匹配(等于{0,1}) |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围(m不超过255) |
SELECT * FROM `user` WHERE `name` REGEXP 'da+'; -- daa
SELECT * FROM `user` WHERE `name` REGEXP 'da*'; -- ding/daa/DFJ
SELECT * FROM `user` WHERE `name` REGEXP 'da?'; -- ding/daa/DFJ
注意:为什么后两条语句可以查询出三条数据?因为他们都符合匹配了0个并且没有$结尾,所以在目标后有字符仍可以匹配出。
11:
定位符
元字符 | 说明 |
---|---|
^ | 文本的开始 |
& | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
SELECT * FROM `user` WHERE `name` REGEXP '.e'; --maek/jen
SELECT * FROM `user` WHERE `name` REGEXP '^.e'; -- jen
12:
正则表达式测试
SELECT '字符串' REGEXP '正则式'; -- 匹配返回1,不匹配返回0;
SELECT 'hello' REGEXP '[0-9]'; -- 0
第10章 创建计算字段
提要: 本章主要介绍了什么是计算字段,怎样创建,以及使用别名引用他们。
字段(field) 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
拼接(concatenate) 将值联结到一起构成单个值。
1:
CONCAT()函数
SELECT CONCAT(id,name) FROM `user`;
注意:很多DBMS使用+或||拼接。
2:
TRIM()/RTRIM()/LTRIM()函数:去掉两/右/左边空格。
3:
**别名(alias)**是一个字段或值的替换名。别名用 AS 关键字赋予。有时也称导出列。
SELECT CONCAT(id,name) AS a FROM `user`;
4:
执行算术计算
SELECT `name`,price * discount*number AS sum FROM `order`;
5:
测试计算
SELECT 2*4; -- 8
SELECT NOW(); -- 当前时间
第11章 使用数据处理函数
提要: 本章主要介绍了函数的定义及使用。
能运行在多个系统上的代码称为可移植的(portable),函数的移植性较差。
1:
函数分类:
- 文本函数
- 数值函数
- 日期和时间函数
- 系统函数
2:
文本函数
SELECT UPPER(`name`) FROM `user`; -- 转为大写
函数 | 说明 |
---|---|
LEFT(str,len)/RIGHT(str,len) | 从左/右边返回str中len个字符 |
LENGTH(str) | str的长度 |
LOCATE(substr,str) | substr在str中第一次出现的索引(从1开始) |
LOWER(str)/UPPER(str) | 转小/大写 |
TRIM(str)/LTRIM(str)/RTRIM(str) | 去掉两/左/右边的空格 |
SOUNDEX(str) | str的SOUNDEX值 |
SUBSTRING(str,pos,len) | 截取str从pos位置开始的len个字符 |
附加:
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法,比如将make错误的输入meke时,仍然能查询到该条数据,但是其对中文不支持。
SUBSTRING()函数使用
select substring("jason",1,2); -- ja
select substring("jason",1); -- jason
select substring("jason",-1); -- n
select substring("jason",-3); -- son
select substring("jason",-3,2); -- so
select substring("jason",-3,-1); -- 空
select substring("jason",-3,0); -- 空
-- 思想:pos表示位置,可以为负数,并从该位置向后截取,len可以为负数,但是没有大的意义
3:
日期和时间处理函数
函数 | 说明 |
---|---|
ADDDATE(expr,days) | 向expr中添加days天 |
ADDDATE(date,INTERVAL expr unit) | 向date添加expr个unit |
ADDTIME(expr1,expr2) | 向expr1中添加,expr2时间 |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
Date(expr) | 返回expr中的日期部分 |
DATEDIFF(expr1,expr2) | expr1比expr2多的天数 |
DATE_ADD(date,INTERVAL expr unit) | 向date添加expr个unit |
DATE_FORMAT(date,format) | 按照format格式化date |
DAY(date) | 返回date的天数部分 |
DAYOFWEEK(date) | 返回对应的星期 |
HOUR(time) | 返回time的小时部分 |
MINUTE(time) | 返回time的分钟部分 |
MONTH(date) | 返回date的月部分 |
Now() | 返回当前日期和时间 |
SECOND(time) | 返回time的秒部分 |
TIME(expr) | 返回expr的时间部分 |
YEAR(date) | 返回date的年部分 |
注意:
- 日期的详细使用见数据库总结
- 日期必须yyyy-mm-dd格式
SELECT * FROM `order` where date(`date`) = '2020-06-23'; -- 3条
SELECT * FROM `order` where `date` = '2020-06-23'; -- 2条
-- 由此可见,当要使用日期判等时,应该使用date()函数获得日期部分再比较
获得某年某月下的订单
SELECT * FROM `order` where YEAR(`date`) = '2020' AND MONTH(`date`) = '6';
SELECT * FROM `order` where DATE(`date`) BETWEEN '2020-06-01' AND '2020-06-30';
4:
数值处理函数
函数 | 说明 |
---|---|
abs(x) | x绝对值 |
exp(x) | x指数值 |
MOD(N,M) | n/m的余数 |
pi() | 圆周率 |
RAND() | 0-1的随机小数 |
第12章 汇总数据
提要: 本章主要介绍了聚集函数及利用他们汇总表的数据。
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值的和 |
1:
AVG()
SELECT AVG(age) FROM `user` -- 19.5714
SELECT AVG(age) FROM `user` WHERE sex = '男' -- 19.6000
NULL 值 AVG() 函数忽略列值为 NULL 的行。
2:
COUNT()
- 使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空值( NULL )还是非空值。
- 使用 COUNT(column) 对特定列中具有值的行进行计数,忽略NULL 值。
SELECT COUNT(*) FROM `user` -- 7
NULL 值 如果指定列名,则指定列的值为空的行被 COUNT()函数忽略。
3:
MAX()/MIN()
SELECT MAX(age) FROM `user` -- 24
SELECT max(name) FROM `user` -- ming
SELECT MIN(age) FROM `user` -- 15
SELECT MIN(name) FROM `user` -- daa
文本数据 MAX()/MIN() 一般用来找出最大的数值或日期值,但MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。
NULL 值 MAX() /MIN()函数忽略列值为 NULL 的行。
4:
SUM()
SELECT SUM(age) FROM `user` -- 137
SELECT SUM(age) FROM `user` WHERE sex = '女' -- 39
NULL 值 SUM() 函数忽略列值为 NULL 的行。
5:
DISTINCT
-
对所有的行执行计算,指定 ALL 参数或不给参数(因为 ALL 是默认行为);
-
只包含不同的值,指定 DISTINCT 参数。
-
DISTINCT不能用于 COUNT(*)
SELECT COUNT(DISTINCT age) FROM `user` -- 5
6:
组合使用&使用别名
SELECT COUNT(*) AS num,MAX(age) AS max_age FROM `user` -- 7 24
第13章 分组数据
**提要:**本章介绍了 SELECT 语句的两个子句: GROUP BY 子句和 HAVING 子句。
SELECT sex ,COUNT(*) FROM `user` GROUP BY sex
-
GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制;
-
如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
-
GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。
-
除聚集计算语句外, SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
-
如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL 值,它们将分为一组。
-
GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前。
SELECT COALESCE(age,'总计') age ,COUNT(*) FROM `user` GROUP BY age WITH ROLLUP
使用 WITH ROLLUP 关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值。
2:
- WHERE 过滤指定的是行而不是分组;
- HAVING 支持所有 WHERE 操作符 。
SELECT sex ,COUNT(*) FROM `user` GROUP BY sex HAVING COUNT(*)>3 -- 1条
**HAVING 和 WHERE 的差别 **
WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤。这是一个重要的区别, WHERE 排除的行不包括在分组中。这可能会改变计算值,从而影响 HAVING 子句中基于这些值过滤掉的分组。
SELECT sex ,COUNT(*) FROM `user` WHERE age > 20 GROUP BY sex HAVING COUNT(*)>3
3:
GROUP BY结合ORDER BY使用
SELECT sex,COUNT(*) AS sum FROM `user` GROUP BY sex ORDER BY sum
4:
SELECT子句及其顺序
子句 | 说明 | 是否必须 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
第14章 使用子查询
**提要:**本章介绍什么是子查询以及如何使用它们。
查询(query) 任何SQL语句都是查询。但此术语一般指 SELECT语句。
SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。
1:
查询学习英语的名字
SELECT `name` FROM `user` WHERE id IN (SELECT user_id FROM course WHERE `name` = '英语')
SELECT `name` FROM `user` WHERE (id,id) IN (SELECT id,user_id FROM course WHERE `name` = '英语') -- 返回多列
-
列必须匹配,一般子查询返回单列,但是也可以返回多列
-
子查询的性能并不是最优的
2:
作为查询 字段
SELECT `name` ,( SELECT COUNT( * ) FROM course WHERE user_id = `user`.id) AS '课程数' FROM `user`
相关子查询(correlated subquery) 涉及外部查询的子查询。任何时候只要列名可能有多义性,就必须使用这种语法(表名和列名由一个句点分隔)。
技巧:
-
建立和测试子查询,确认正确
-
用硬编码的方式建立和测试外层查询,确认正确
-
嵌入子查询,然后重复上述步骤
第15章 联结表
提要: 本章将介绍什么是联结,为什么要使用联结,如何编写使用联结的SELECT 语句。
关系表: 将信息分解成多个表,一类数据一个表。各表通过某些常用的值(即关系设计中的关系(relational))互相关联。
外键(foreign key) 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
通过供应商和产品的例子介绍关系表/分表的好处:
- 供应商信息不重复,从而不浪费时间和空间;
- 如果供应商信息变动,可以只更新供应商表中的单个记录,相关表中的数据不用改动;
- 由于数据无重复,保证了数据的一致性;
联结: 联结是一种机制,用来在一条 SELECT语句中关联表。
完全限定列名 在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名)。
1:
使用FROM…WHERE…结构
SELECT * FROM `user`,course WHERE `user`.id = course.user_id
2:
笛卡儿积
SELECT * FROM `user`,course
笛卡儿积(cartesian product) 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
3:
内部联结
SELECT * FROM `user` INNER JOIN course ON `user`.id = course.user_id
4:
多表联结
SELECT * FROM `user` INNER JOIN course ON `user`.id = course.user_id INNER JOIN `order` ON `user`.id = `order`.id
第16章 创建高级联结
**提要:**本章介绍了其他的联结类型以及如何对表使用别名和聚集函数。
1:
别名
SELECT * FROM `user` AS u INNER JOIN course AS c ON `u`.id = c.user_id
2:
自联结:在一条SELECT语句中不止一次使用相同的表
SELECT c1.id FROM course c1 WHERE user_id = (SELECT user_id FROM course c2 WHERE c2.id = 1) -- 子查询
SELECT c1.id FROM course c1 INNER JOIN course c2 on c1.user_id = c2.user_id AND c2.id =1
-- 自联结查询,必须使用别名
3:
自然联结:每个列仅出现一次(系统无法判断,人工选择)
SELECT u.*,c.id,c.name FROM `user` u INNER JOIN course c ON u.id = c.user_id
4:
外部联结 联结包含了那些在相关表中没有关联行的行。
SELECT * FROM `user` u LEFT JOIN course c ON u.id = c.user_id -- 左联结
SELECT * FROM `course` c RIGHT JOIN `user` u ON u.id = c.user_id -- 右联结
5:
使用聚集函数
SELECT u.name, COUNT(c.name)FROM `course` c RIGHT JOIN `user` u ON u.id = c.user_id GROUP BY u.id -- 统计学生选的课数
总结:
- 注意所使用的联结类型。一般我们使用内部联结,但使用外部联结也是有效的。
- 保证使用正确的联结条件。
- 应该总是提供联结条件,否则会得出笛卡儿积。
- 在一个联结中可以包含多个表,但应该在一起测试它们前,分别测试每个联结。
第17章 组合查询
提要: 本章主要讲述了 UNION 操作符。
复合查询(compound query) 多数SQL查询都只包含从一个或多个表中返回数据的单条 SELECT 语
句。MySQL也允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。
使用场景:
- 在单个查询中从不同的表返回类似结构的数据;
- 对单个表执行多个查询,按单个查询返回数据。
SELECT * FROM `user` WHERE sex = '男' UNION SELECT * FROM `user` WHERE age > 20
使用规则:
- UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔;
- UNION 中的每个查询必须包含相同的列、表达式或聚集函数;
- 列数据类型必须兼容。
SELECT * FROM `user` WHERE sex = '男' UNION ALL SELECT * FROM `user` WHERE age > 20
-- 不去重
排序:
SELECT * FROM `user` WHERE sex = '男' UNION ALL SELECT * FROM `user` WHERE age > 20 ORDER BY age -- 只在最后一条语句上使用ORDER BY,排序所有结果
第18章 全文本搜索
提要: 本章主要介绍了使用MYSQL的全文本搜索进行高级的数据查询。
1:
LIKE和正则表达式的缺陷:
- 性能:通常要求MySQL尝试匹配表中所有行,导致非常耗时;
- 明确控制:很难(而且并不总是能)明确地控制匹配什么和不匹配什么;
- 智能化的结果:并不能将结果分级,并由级别从高到低排序。
2:
使用全文本搜索,需要添加 FULLTEXT索引
CREATE TABLE productnotes(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_test text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_test)
)ENGINE = MYISAM;
注意:不要在导入数据时使用 FULLTEXT,应该在导入后使用,这样耗用的时间最少。
3:
基本使用
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('rabbit')
-- MATCH:指定列;多个列时必须按次序列出 Against:指定搜索的文本 区分大小写: BINARY
4:
智能排序
SELECT note_text, MATCH(note_text) AS rank Against('rabbit') FROM productnotes
-- 等级越高,排序越前
-- 级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算
5:
查询扩展
流程:
- 首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
- 其次,MySQL检查这些匹配行并选择所有有用的词;
- 再其次,MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词。
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('rabbit' WITH QUERY EXPANSION);
-- 会将包含‘rabbit’行中的词作为有用的词进行第二次搜索
6:
布尔文本搜索
内容细节:
- 要匹配的词;
- 要排斥的词;
- 排列提示(指定某些词比其他词更重要,更重要的词等级更高);
- 表达式分组;
- 即使没有 FULLTEXT 索引也可以使用;
- 不按等级值降序排序返回的行;
- ……
布尔操作符 | 说明 |
---|---|
+ | 包含,词必须存在 |
- | 排除,词必须不出现 |
> | 包含,而且增加等级值 |
< | 包含,且减少等级值 |
() | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等) |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
“” | 定义一个短语 |
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('+rabbit +bait' IN BOOLEAN MODE); -- 必须包含词 rabbit 和 bait 的行
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('rabbit bait' IN BOOLEAN MODE); -- 必须包含词 rabbit 和 bait 的至少一个行
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('"rabbit bait"' IN BOOLEAN MODE); -- 匹配短语 rabbit bait 而不是匹配两个词 rabbit 和bait
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('>rabbit <bait' IN BOOLEAN MODE); -- 增加前者等级,降低后者等级
SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('+rabbit +(<bait)' IN BOOLEAN MODE); -- 必须包含rabbit和bait,并降低后者的等级值
总结:
- 小于4的词被排除,可以在配置文件修改;
- MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略;
- 如果一个词出现在50%以上的行中,则将它作为一个非用词忽略。50%规则不用于 IN BOOLEAN MODE ;
- 如果表中的行数少于3行,则全文本搜索不返回结果;
- 忽略词中的单引号;
- MyISAM 和 InnoDB 目前都支持全文搜索;
- 自5版本后支持中文搜索。