mysql DQL

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()函数没有价值。

多个聚集函数可以在一个查询语句中组合使用。