mysql DQL
DQL
Data Query Language
使用
SELECT
关键字查询数据
如果没有明确排序查询结果,则返回的数据的顺序没有特殊意义。即查询结果默认无序显示,类似伪随机。
关于大小写字母的排序,这依据数据库的设置。默认行为是遵照字典排序顺序,a
和A
是不区分大小写。
通配符*
索引所有列,但是不推荐,会影响性能。
DISTINCT
关键字,用于去重。必须紧跟SELECT
,放在列名的前面。
LIMIT
子句,用于限定查询结果返回的行数。这里有序。
LIMIT num
:返回结果,取不多于num
行,行号从0计数。LIMIT a, b
:返回结果,从a
行开始,取不多于b
行,索引从0开始,左闭右开。- 等价于
LIMIT b OFFSET a
.
- 等价于
- "不多于"的意思就是,查询结果数量不多于限定数量时,返回所有的结果。
ORDER BY
子句:对检索出的数据排序。
-
用法:
SELCET * FROM table_name ORDER BY column_1 [, column_2, ...]
排序完全按所规定的顺序进行,也就是按照
ORDER BY
子句的列的顺序来排列。 -
位置:
在
FROM
子句之后,在WHER
子句之后,LIMIT
子句之前,错了会出问题。
DESC
关键字,表示降序排序,即从高到低排。
- 紧跟在
ORDER BY
子句的列名的后面,表示该列采用降序排序,其他未使用DESC
关键字的列仍然默认升序排序。
ASC
关键字,表示升序排序。但是这是默认的,所以没什么用。
WHERE
子句,用于过滤数据,指定(操作、处理)的是行级别。
-
位置:
WHERE
子句在FROM
子句之后,ORDER BY
子句之前 -
子句操作符
操作符 描述 = 等于 <> 不等于 != 不等于 < 小于 <= 小于等于 > 大于 >= 大于等于 BETWEEN … AND … 在指定的两个值之间(左闭右闭) -
字符值要用单引号限定
-
空值
NULL
检查... WHERE column_name IS NULL;
必须用
IS NULL
,使用= NULL
不行。 -
多重条件过滤:以
AND
子句的方式或OR
子句的方式使用-
AND
,表示与 -
OR
,表示或 -
AND
的优先级高于OR
xxx WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; # 等价于 xxx WHERE vend_id = 1002 OR (vend_id = 1003 AND prod_price >= 10);
-
-
IN
操作符:表示集合范围,比如WHERE column_name IN (val_1, val_2);
IN
操作符一般比OR
操作符清单执行更快。(性能更好)
IN
的最大优点是可以包含其他SELECT
语句,使得能够更动态地建立WHERE
子句。(可以包含其他SELECT
子句,因为查询的结果也是集合) -
NOT
操作符:表示否定。MySQL支持使用
NOT
对IN
、BETWEEN
和EXISTS
子句取反。 -
LIKE
操作符:用于通配搜索,过滤数据通配符有:
-
%
:代表搜索模式中给定位置的0个、1个或多个字符。
不会匹配
NULL
。可以使用在多个地方。
-
_
:代表只能匹配一个字符
使用通配符搜索,一般很慢,因此在其他通配符能实现需求时,就不要用通配符。
-
-
REGEXP
关键字WHERE column_name REGEXP regexp;
正则表达式匹配。不区分大小写(即,大写和小写都匹配)。为区分大小写,可使用
BINARY
关键字,如:WHERE prod_name REGEXP BINARY 'JetPack .000';
数据分组,获取总表内容的子集:GROUP BY
子句和HAVING
子句
-
GROUP BY
子句:用于创建分组-
GROUP BY
子句,可以包含任意数目的列。即允许多列分组 -
如果分组列中具有
NULL
值,则NULL
将作为一个分组返回。如果列中有多行NULL
值,它们将分为一组。 -
GROUP BY
子句,在WHERE
子句之后,ORDER BY
子句之前。 -
WITH ROLLUP
关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值。(就是查询结果末尾添加一行汇总统计)SELECT vend_id, COUNT(*), SUM(prod_price) AS sum_val FROM products GROUP BY vend_id WITH ROLLUP; +---------+---------+---------+ | vend_id | count(*) | sum_val | +---------+---------+---------+ | 1001 | 3 | 30.97 | | 1002 | 2 | 12.41 | | 1003 | 7 | 92.49 | | 1005 | 2 | 90.00 | | NULL | 14 | 225.87 | +---------+---------+---------+ 5 rows in set (0.11 sec)
-
与
ORDER BY
相比- 顺序上:
GROUP BY
的输出以分组顺序输出,但不具有顺序意义。排序需要用ORDER BY
确定。
- 顺序上:
-
-
HAVING
子句:用于过滤分组,指定(操作、处理)的是分组级别。-
与
WHERE
相比-
WHERE
过滤行,而HAVING
过滤分组。可以使用HAVING
替代WHERE
达到一样的效果。 -
HAVING
支持所有WHERE
操作符。也就是WHERE
的用法HAVING
都能用。 -
WHERE
在数据分组前进行过滤,HAVING
在数据分组后进行过滤。可以同时使用。mysql> select vend_id, count(*), sum(prod_price) as sum_val from products group by vend_id having sum(prod_price)>30; +---------+---------+---------+ | vend_id | count(*) | sum_val | +---------+---------+---------+ | 1001 | 3 | 30.97 | | 1003 | 7 | 92.49 | | 1005 | 2 | 90.00 | +---------+---------+---------+ 3 rows in set (0.13 sec) mysql> select vend_id, count(*), sum(prod_price) as sum_val from products where ven_id != '1005' group by vend_id having sum(prod_price)>30; 1054 - Unknown column 'ven_id' in 'where clause' mysql> select vend_id, count(*), sum(prod_price) as sum_val from products where vend_id != '1005' group by vend_id having sum(prod_price)>30; +---------+---------+---------+ | vend_id | count(*) | sum_val | +---------+---------+---------+ | 1001 | 3 | 30.97 | | 1003 | 7 | 92.49 | +---------+---------+---------+ 2 rows in set (0.16 sec)
注意,
AS
取的别名,仅作显示使用,在别的地方使用mysql识别不了
-
-
在
GROUP BY
子句之后,可以简记:先分组(GROUP BY
)后过滤(HAVING
)WHERE
…GROUP BY
…HAVING
-
SELECT子句顺序
子 句 | 说 明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
子查询
和联结不同,相当于分步查,每次个子查询只操作一个表,
然后条件是可以跨越了多个表(
where customers.cust_id = orders.cust_id
),也可以只是一个表
列必须匹配。子查询中,应该保证SELECT
语句具有与WHERE
子句中相同数目的列。
通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列。
子查询和性能。使用子查询并不总是执行这种类型的数据检索的最有效的方法;可以使用表连接。
作为计算字段使用子查询。就是子查询的结果作为一个字段(一列),一起显示。
mysql> select cust_name, cust_state, (select count(*) from orders where customers.cust_id = orders.cust_id) from customers;
+---------------+-----------+------------------------------------------------------------+
| cust_name | cust_state | (select count(*) from orders where customers.cust_id = orders.cust_id) |
+---------------+-----------+------------------------------------------------------------+
| Coyote Inc. | MI | 2 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
| E Fudd | IL | 1 |
+---------------+-----------+------------------------------------------------------------+
5 rows in set (0.09 sec)
# 子查询的地方,使用`customers.cust_id = orders.cust_id`来做匹配。
# 该子查询执行了5次,因为检索出了5个客户。
# 涉及外部查询,因此是 相关子查询
调试和测试技巧:
首先,建立和测试最内层的查询。
然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。
这时,再次测试它。对于要增加的每个查询,重复这些步骤。
联结(jion)
连接表;内联结(等值联结)、自联结、自然联结、外联结
JOIN
和ON
连用
联结(join)表:
显示多个表的不同列信息;联结条件放在WHERE
子句;联结的表越多,性能下降越厉害。
select * from customers,orders where customers.cust_id = orders.cust_id;
内联结(INNER JOIN
):也叫等值联结(equijoin),可能返回重复列
用法:INNER JOIN ... ON condition
,这里使用ON
!
SELECT ... FROM table_name1 INNER JOIN table_name2 ON condition;
其他三种联结:自联结、自然联结和外部联结
-
自联结
表和本身作联结,只操作一个表。可以是看作在一个表上需要操作多次才能拿到需要的查询结果。
# 子查询方法 mysql> select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = 'FU1'); +--------+-----------+ | prod_id | prod_name | +--------+-----------+ | FU1 | Fuses | | OL1 | Oil can | +--------+-----------+ 2 rows in set (0.11 sec) # 自联结方法 mysql> select p1.prod_id, p1.prod_name from products as p1, products as p2 where p1.prod_id = p2.prod_id and p1.prod_id = 'Fu1'; +--------+-----------+ | prod_id | prod_name | +--------+-----------+ | FU1 | Fuses | +--------+-----------+ 1 row in set (0.07 sec) # 列名未指定表,报错<ambiguous>! mysql> select prod_id, prod_name from products as p1, products as p2 where p1.prod_id = p2.prod_id and p1.prod_id = 'Fu1'; 1052 - Column 'prod_id' in field list is ambiguous
【性能比较】一般来说,自联结比子查询性能好
-
自然联结
自然联结排除多次出现,使每个列只返回一次。但这个需要自己控制。
因此,内联结和自然联结是高度重合的。
一般是通过对表使用通配符(SELECT *),对所有其他表的列使用明确的子集来完成的。SELECT c.*,o.order_num,o.order_date, oi.prod_id,oi.quantity,oi.item_price FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_numAND prod_id = 'FB' ; # 一个表使用通配符`table_alias.*`,其他两个表明确指定列`table_alias.column_name`
-
外部联结
许多联结将一个表中的行与另一个表中的行相关联。
联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。
-
右外部联结
RIGHT OUTER JOIN
为了从右边的表中选择所有行,应该使用
RIGHT OUTER JOIN
。 -
左外部联结
LEFT OUTER JOIN
为了从左边的表中选择所有行,应该使用
LEFT OUTER JOIN
。 -
过滤条件使用
ON
关键字。 -
左右外部联结的需要依表的顺序而定
-
聚集函数也可以用于联结之中
组合查询
UNION
操作符,用于联结多个SELECT
子句,表示并操作
UNION
中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。
在使用UNION
时,重复的行被自动取消。即默认去重。
UNION ALL
不会去重。
在用UNION组合查询时,只能使用一条ORDER BY
子句,它必须出现在最后一条SELECT语句之后。会对查询结果做统一排序。
全文本搜索
全文搜索依据引擎支持,MyISAM支持而InnoDB不支持
启用全文本搜索;全文本搜索;查询扩展;布尔文本搜索
在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。这样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们),哪些词不匹配,它们匹配的频率,等等。
支持全文搜索的列必须建立索引,mysql会自动进行所有所有的索引和重新索引。在索引之后,SELECT可与Match()和Against()一起使用以实际执行搜索。
由于数据是索引的,全文本搜索还相当快。
启用全文本搜索:
使用FULLTEXT
子句在创建表(CREATE TABLE
)的时候启动全文本搜索;或者导入数据后再指定(比先启用再导入省时间)。
FULLTEXT
可以索引单个列,也可以指定多个列。mysql会自动维护索引,在增、删、改数据时自动更新索引。
CREATE TABLE productnotes(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MyISAM;
使用全文本搜索:
Match()
函数:指定被搜索的列- 一定要是
FULLTEXT
子句定义的列 - 可以指定多列(要次序正确)
- 一定要是
Against()
函数:指定要使用的搜索表达式- 除非使用BINARY方式(本章中没有介绍),否则全文本搜索不区分大小写。
Match()
和Against()
在WHERE
子句中使用。
# 这里报错,因为`note_date`这一列没有启动全文本搜索
mysql> select note_id from productnotes where match(note_date) against('08-');
1191 - Can't find FULLTEXT index matching the column list
# 这里正常,匹配`sack`这个词的行
mysql> select note_id from productnotes where match(note_text) against('sack');
+--------+
| note_id |
+--------+
| 104 |
+--------+
1 row in set (0.10 sec)
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能是你真正想要的行)。
等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来
文本中词靠前的行的等级值比词靠后的行的等级值高。
mysql> select note_id, match(note_text) against('rabbit') as rank from productnotes;
+--------+--------------------+
| note_id | rank |
+--------+--------------------+
| 101 | 0 |
| 102 | 0 |
| 103 | 0 |
| 104 | 1.5905543565750122 |
| 105 | 0 |
| 106 | 0 |
| 107 | 0 |
| 108 | 0 |
| 109 | 0 |
| 110 | 1.6408053636550903 |
| 111 | 0 |
| 112 | 0 |
| 113 | 0 |
| 114 | 0 |
+--------+--------------------+
14 rows in set (0.09 sec)
# 实际上`match(note_text) against('rabbit')`会返回一个rank,而查询结果就会依据这个,按优先级从高到低的顺序显示
# 所以全文本搜索的结果是 有序的!
使用查询扩展:
查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词。
在Against
子句中的匹配表达式后紧跟WITH QUERY EXPANSION
查询步骤,在使用查询扩展时,MySQL对数据和索引进行两遍扫描来完成搜索:
- 首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
- 其次,MySQL检查这些匹配行并选择所有有用的词。
- 再其次,MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词。
布尔文本搜索:
全文本搜索的另一种形式,可以规定需要匹配的词、要排斥的词(即使包含要匹配的词也不返回该行)、排列提示(重要程度、等级高低)、表达式分组、其他内容等。
在Against
子句中的匹配表达式后紧跟IN BOOLEAN MODE
最重要的2点:
- 即使没有
FULLTEXT
索引也可以使用。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。 - 排列而不排序。在布尔方式中,不按等级值降序排序返回的行。
全文本布尔操作符:
+
包含,词必须存在
-
排除,词必须不出现
>
包含,而且增加等级值
<
包含,且减少等级值
()
把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~
取消一个词的排序值
*
词尾的通配符
""
定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)
# 'heavy -rope* ':匹配串'heavy' && 不包含任意以rope开始的词
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope* ' IN BOOLEAN MODE);
# 搜索匹配包含'rabbit'和'bait'的词
... WHERE Match(note_text) Against( '+rabbit +bait' IN BOOLEAN MODE);
# 默认情况下,是【或】的意思
... WHERE Match(note_text) Against( 'rabbit bait' IN BOOLEAN MODE);
# 使用 "" 匹配短语,必须整体匹配才算
... WHERE Match(note_text) Against( ' "rabbit bait"' IN BOOLEAN MODE);
# rabbit等级升高,carrot等级降低,也就是rabbit的匹配等级更高
... WHERE Match(note_text) Against( '>rabbit <carrot' IN BOOLEAN MODE);
# () 组合使用
... WHERE Match(note_text) Against( '+safe +(<combination)' IN BOOLEAN MODE);
全文本搜索的一些重要说明
在索引全文本数据时,短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表(请参
阅MySQL文档以了解如何完成此工作)。
许多词出现的频率很高,搜索它们没有用处(返回太多的结果)。因此,MySQL规定了一条50%规则,如果一个词出现在50%以上
的行中,则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
。
如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)。???
忽略词中的单引号。例如,don’t索引为dont。
不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
仅在MyISAM数据库引擎中支持全文本搜索。
DQL-计算字段(函数相关)
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
用于格式化显示由几列拼接联合的信息
通过函数构建
函数习惯首字母大小,其他小写;聚集函数全大写。
Concat()
函数:在MySQL的SELECT
语句中,可使用Concat()
函数来拼接两个列。
-
用法:在查询子句(
SELECT
)中使用Concat(column_name1 | 'const_str'[, column_name2 | 'const_str', ...]) ...
-
demo
mysql> select Concat(cust_id,'_', cust_name, '_', cust_country) from customers; +-----------------------------------------+ | concat(cust_id,'_', cust_name, '_', cust_country) | +-----------------------------------------+ | 10001_Coyote Inc._USA | | 10002_Mouse House_USA | | 10003_Wascals_USA | | 10004_Yosemite Place_USA | | 10005_E Fudd_USA | +-----------------------------------------+ 5 rows in set (0.09 sec) mysql> select concat(cust_id,'_', cust_name, '_', cust_country) AS base_info from customers; +-------------------------+ | base_info | +-------------------------+ | 10001_Coyote Inc._USA | | 10002_Mouse House_USA | | 10003_Wascals_USA | | 10004_Yosemite Place_USA | | 10005_E Fudd_USA | +-------------------------+ 5 rows in set (0.07 sec)
RTrim()
函数:删除数据右侧多余的空格。LTrim()
函数:删除数据左侧多余的空格。Trim()
函数:删除数据两侧多余的空格。
-
用法:在查询子句(
SELECT
)中使用RTrim(column_name)
别名:
As
关键字,用于给字段、计算字段或表取别名。紧跟需要取别名的字段后面。
常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它,等等。
列别名,不能用于WHERE
、HAVING
等其他子句中,识别不了。
表别名不仅能用于WHERE
子句,它还可以用于SELECT
的列表、ORDER BY
子句以及语句的其他部分。
列别名会在查询结果(客户机)上显示,表别名只在查询执行中使用,不在客户机上显示。
算术计算:
# 算术操作符
+ - * /
测试计算:虽然SELECT通常用来从表中检索数据,但可以省略FROM子句以便简单地访问和处理表达式。
SELECT 3*2; # 将返回6,
SELECT Trim(' abc '); # 将返回abc
SELECT Now(); # 利用Now()函数返回当前日期和时间。
Now()
函数:返回当前日期和时间。2022-10-14 09:51:54
默认这种格式。
SELECT 'a'+1; # 返回1
,自动忽略串。
DQL-函数
多用在查询操作上
数据处理函数
函数一般是在数据上执行的,它给数据的转换和处理提供了方便。
函数没有SQL的可移植性强。
SQL语句中写比较的时候,按串的格式书写。
函数种类:
- 文本函数:处理文本串
- 数值函数:处理数值数据,进行算术操作
- 日期和时间函数:处理日期和时间
- 系统函数:返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)
文本函数:
Upper()
函数,将文本转换为大写Lower()
函数,将串转换为小写Length()
函数,返回串的长度Locate()
函数,找出串的一个子串LTrim()
函数,去掉串左边的空格RTrim()
函数,去掉串右边的空格Right()
函数,返回串右边的字符Left()
函数,返回串左边的字符Soundex()
函数,返回串的SOUNDEX
值SubString()
函数,返回子串的字符
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。(按发音匹配)
WHERE Soundex(column_name) = Soundex('Y Lie') # 可以查到'Y.Lee'
数值函数:
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
时间和日期函数:
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date()
返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day()
返回一个日期的天数部分
DayOfWeek() 对于一个日期,返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month()
返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time()
返回一个日期时间的时间部分
Year()
返回一个日期的年份部分
聚集函数
聚集函数(aggregate function)
运行在行组上,计算和返回单个值的函数。
汇总表数据
利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算。
聚集函数:
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
-
AVG()
函数- 参数是
column_name
,只针对一列,而且是特定数值列 - 忽略列值为NULL的行。
- 参数是
-
COUNT()
函数- 确定表中行的数目或符合特定条件的行的数目。
COUNT(*)
对表中行的数目进行计数,包含有NULL
值的行。COUNT(column)
对特定列中具有值的行进行计数,忽略NULL
值的行。- 如果指定列名,忽略指定列的值为
NULL
的行。
-
MAX()
函数-
对非数值数据
-
最大的数值或日期值
-
在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行
经测试,文本按字母顺序对比。
-
-
忽略列值为NULL的行。
-
-
MIN()
函数同
MAX()
函数 -
SUM()
函数- 忽略列值为NULL的行
聚集函数的DISTINCT
的使用:会先去重再进行函数计算。将DISTINCT
用于MIN()
和MAX()
函数没有价值。
多个聚集函数可以在一个查询语句中组合使用。