[大数据]MySQL 8.0 查询数据

PART1. 基本查询语句

MySQL从数据表中查询数据的基本语句为SELECT语句。

CREATE TABLE fruits(
f_id char(10) NOT NULL,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(8,2) NOT NULL,
PRIMARY KEY(f_id)
);
INSERT INTO fruits(f_id,s_id,f_name,f_price)
VALUES('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',2.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.7),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxxx',3.6);

SELECT f_id,f_name FROM fruits;

PART2. 单表查询

1. 查询所有字段

1. 在SELECT语句中使用星号（*）通配符查询所有字段。

SELECT * FROM 表名;

?2. 在SELECT语句中指定所有字段

SELECT f_id, s_id, f_name, f_price FROM fruits;

2. 查询指定字段

1. 查询单个字段

SELECT 列名 FROM 表名;

2. 查询多个字段

SELECT 字段名1,字段名2,……,字段名n FROM 表名;

注意：MySQL中的SQL语句是不区分大小写的，因此SELECT和select 的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，养成好的变成习惯，写出来的代码容易阅读和维护。

3. 查询指定记录

SELECT 字段名1,字段名2,……,字段名n
FROM 表名
WHEN 查询条件

4. 带IN关键字的查询

IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

5. 带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。

6. 带LIKE的字符匹配查询

1. 百分号通配符’%‘，匹配任意长度的字符，甚至包括零字符

?

2. 下划线通配符’_'，一次只能匹配任意一个字符

该通配符的用法和’%‘相同，区别是’%‘可以匹配多个字符，而’_‘只能匹配任意单个字符。如果要匹配多个字符，则需要使用相同个数的’_‘。

?7. 查询空值

在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。

8. 带AND的多条件查询

使用SELECT查询时，可以增加查询的限制条件，这压根可以使查询的结果更加精确。MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

9. 带OR的多条件查询

与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。

注意：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

10. 查询结果不重复

SELECT DISTINCT 字段名 FROM 表名;

11. 对查询结果排序

1. 单列排序

使用ORDER BY子句对指定的列数据进行排序。

2. 多列排序

对多列数据进行排序，要将需要排序的列之间用逗号隔开。

注意：在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值是唯一的，将不再对第二列进行排序。

3. 指定排序方向

默认情况下，查询数据按字母升序进行排序（A~Z），但数据的排序并不仅限于此，还可以使用ORDER BY对查询结果进行降序排序（Z~A），通过关键字DESC实现。

12. 分组查询

分组查询是对数据按照某个或多个字段进行分组。MySQL中使用GROUP BY关键字对数据进行分组，基本语法形式为：

[GROUP BY 字段] [HAVING <条件表达式>]

1. 创建分组

GROUP BY关机子通常和集合函数一起使用，比如MAX()、MIN()、COUNT()、SUM()、AVG()。

?2. 使用HAVING过滤分组

?GROUP BY 可以和HAVING 一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

3. 在GROUP BY子句中使用WITH ROLLUP?

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录查询出的所有记录的总和，即统计记录数量。

4. 多字段分组?

使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第一个字段分组，然后在第一个字段值相同的记录中再根据第二个字段的值进行分组，以此类推。

5. GROUP BY和ORDER BY一起使用

某些情况下需要对分组进行排序，在前面的介绍中，ORDER BY用来对查询到记录排序，如果和GROUP BY一起使用可以完成对分组的排序。

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY时互相排斥的。

13. 使用LIMIT限制查询结果的数量

LIMIT [位置偏移量,] 行数

注意：MySQL8.0中可以使用“LIMIT 4 OFFSET 3”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT4,3;”返回的结果相同。

PART3. 使用集合函数查询

1. COUNT() 函数

COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：

COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。

COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。

可以GROUP BY关键字先进行分组，然后计算每个分组中的总记录数。

2. SUM()函数

SUM()是一个求总数的函数，返回指定列值的总和。

可以GROUP BY关键字先进行分组，然后计算每个分组中的总量。

3. AVG()函数

AVG()函数通过计算返回的行数和每一列数据的和，求得指定列数据的平均值。

AVG()可以与GROUP BY一起使用，来计算每个分组的平均值。

注意：AVG()函数使用时，其参数为要计算的列名称，如果要得到多个列的多个平均值，则需要在每一列上使用AVG()函数。

4. MAX()函数

MAX()返回指定列中最大值。

MAX()也可以和GROUP BY关键字一起使用，求每个分组中的最大值。

MAX()函数不仅适用于查找数值类型，也可应用于字符类型。

注意：MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。

5. MIN()函数

MIN()返回查询列中的最小值。

MIN()也可以和GROUP BY关键字一起使用，求出每个分组中的最小值。

MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

PART4. 连接查询

1. 内连接查询

内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

use test;
drop table IF EXISTS suppliers;
CREATE TABLE suppliers(
s_id int NOT NULL AUTO_INCREMENT,
s_name char(50) NOT NULL,
s_city char(50) NULL,
s_zip char(50) NULL,
s_call char(50) NOT NULL,
PRIMARY KEY (s_id)
);
INSERT INTO suppliers (s_id,s_name,s_city,s_zip,s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000','22222'),
(106,'Just Eat Ours','Beijing','010','45678'),
(107,'DK Inc.','Zhengzhou','450000','33332');

?fruits表和suppliers表中有相同的字段s_id，因此在比较的时候需要完全限定表名（格式为“表名.列名”），如果只给出s_id，MySQL将不知道指的是哪一个，并且返回错误信息。

注意：使用WHERE子句定义连接条件比较简单明了，而INNER JOIN语法是ANSI SQL的标准规范，使用INNER JOIN连接语法能够确保不会忘记连接条件，而且WHERE 子句在某些时候会影响查询的性能。

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询成为自然连接查询。子链接是一种特殊的内连接，它是值相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

2. 外连接查询

外连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅是符合查询条件和连接条件的行。有时候需要包含没有关联的行中数据，即返回查询结果集合不仅包括符合连接条件的行，还包括左表（左外连接和左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接。

LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

1. LEFT JOIN左连接

左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列均为空值。

?2. RIGHT JOIN 右连接

右连接和左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

3. 复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件限制查询的结果，使查询的结果更加准确。

PART5. 子查询

1. 带ANY、SOME关键字的子查询

ANY和SOME关键字和同义词，表示满足其中任一条件，他们允许创建一个表达式对于子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

2. 带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。

ALL关键字姐在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。

3. 带EXISTS关键字的子查询

EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为TRUE，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS 返回的结果是FALSE，此时外层语句将不进行查询。

EXISTS关键字可以和条件表达式一起使用。

NOT EXISTS与EXISTS使用方法相同，返回结果相反。

注意：EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

4. 带IN关键字的子查询

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

SELECT语句中可以使用NOT IN关键字，其作用与IN正好相反。

注意：子查询的功能也可以通过连接查询完成，但是子查询使得MySQL代码更容易阅读和编写。

5. 带比较运算符的子查询

子查询时还可以使用其他的比较运算符，如"<"、 "<="、 "="、 ">=" 和 "!="等。

PART6. 合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将他们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION 不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。基本语法格式：

SELECT column,…… FROM table1
UNION [ALL]
SELECT column,…… FROM table2

注意：UNION 和UNION ALL的区别：使用UNION ALL的功能是不删除重复行，加上ALL关键字语句执行时所需要的资源少，所以尽可能地使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用UNION ALL以提高查询效率。

PART7. 为表和字段取别名

1. 为表取别名

表名 [AS] 表别名

MySQL可以同时为多个表取别名，而且表别名可以放在不同的位置，如WHERE子句、SELECT列表、ON子句以及ORDER BY子句等。

注意：在为表取别名时，要保证不能为数据库中其他表的名称冲突。

2. 为字段取别名

列名 [AS] 列别名

注意：表别名旨在执行查询的时候使用，并不在返回结果中显示，而列别名定义之后，将返回给客户端显示，显示的结果字段为字段列的别名。

PART8. 使用正则表达式查询

1. 查询以特定字符或字符串开头的记录

字符‘^'匹配以特定字符或者字符串开头的文本。

以字母’b'开头的记录：?

以字母‘be’开头的记录：

?2. 查询以特定字符或字符串结尾的记录

字符‘$'匹配以特定字符或者字符串结尾的文本。

以字母‘y'结尾的记录：

?以字母’rry'结尾的记录：

3. 用符号"."来代替字符串中的任意一个字符

字符'.'匹配任意一个字符。

查询f_name字段值包含字母'a'与'g'且两个字符之间只有一个字母的记录，SQL语句如下：

4. 使用"*"和"+"来匹配多个字符?

星号'*'匹配前面的字符任意多次，包括0次。加号'+'匹配前面的字符至少一次。

查询f_name字段值以字母’b'开头且‘b'后面出现字母’a'的记录：

查询f_name字段值以字母‘b'开头且’b'后面出现字母‘a'至少一次的记录：

5. 匹配指定字符串?

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中几个，如果匹配多个字符串，多个字符串之间使用分隔符'|'隔开。

查询f_name字段值包含字符串“on“的记录：?

查询f_name字段值包含字符串”on“或者”ap“的记录：

?注意：之前介绍过，LIKE运算符也可以匹配指定的字符串，但与REGEXP不同，LIKE匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。REGEXP在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP将会找到他，相应的行会被返回。

6. 匹配指定字符中的任意一个

方括号”[]“指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

查找f_name字段中包含字母‘o'或者’t'的记录：

方括号”[]“还可以指定数值集合。

查询s_id字段中包含4、5或者6的记录：

7. 匹配指定字符以外的字符?

”[^字符集合]“匹配不在指定集合中的任何字符。

查询f_id字段中包含字母a~e和数字1~2以外字符的记录：

?返回结果都不在a~e与1~2之间。

8. 使用{n,}或者{n,m}来指定字符串连续出现的次数

”字符串{n，}“表示至少匹配n次前面的字符：”字符串{n，m}“表示匹配前面的字符串不少于n次，不多于m次。例如，a{2,}表示字母a连续出现至少2次，也可以大于2次；a{2,4}表示字母a连续出现最少2次，最多不能超过4次。

查询f_name字段值出现字母’x'至少2次的记录：

查询f_name字段值出现字符串”ba”最少1次、最多3次的记录：

PART9. MySQL8.0 的新特性1——GROUP BY不再隐式排序

从MySQL 8.0 开始，MySQL对GROUP BY字段不再隐式排序。如果确实需要排序，必须加上ORDER BY子句。

PART10. MySQL8.0 的新特性2——通用表表达式

通用表表达式简称为CTE（Common Table Expressions）。CTE是命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。

CTE的语法格式如下：

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

使用WITH语句创建CTE的情况如下：

（1）SELECT、UPDATE、DELETE语句的开头：

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

(2) 在子查询的开头：

SELECT ... WHERE id IN (WITH ... SELECT ...) ... 
SELECT * FROM (WITH ... SELECT ...) AS dt ...

(3) 紧接SELECT，在包含SELECT声明的语句之前：

INSERT ... WITH ... SELECT ... 
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

正常的子查询：

?使用CTE的方式，完成上述功能：

?从结果可以参数，CTE是一个可以重复使用的结果集。相比于子查询，CTE的效率会更高，因为非递归的CTE只会擦汗寻一次并可以重复使用。

CTE可以引用其他CTE的结果。

还有一种特殊的CTE，就是递归CTE，其子查询会引用自身。WITH子句必须以WITH RECURSIVE开头。

CTE递归子查询包括两部分：seed查询和recursive查询，中间由union [all] 或 union distinct 分隔。seed查询会被执行一次，以创建初始数据子集。recursive查询会被重复执行以返回数据子集，直到获得完整结果集。当迭代不会生成任何新行时，递归会停止。

?递归的过程如下：

（1）首先执行SELECT 1 得到结果1，即当前n的值为1.

（2）接着执行SELECT n + 1 FROM cte WHERE n < 8，因为当前n为1，所以WHERE条件成立，生成新行，SELECT n+1 得到结果2，即当前n 的值为2.

（3）继续执行SELECT n+1 FROM cte WHERE n < 8，因为当前n为2，所以WHERE条件成立，生成新行，SELECT n+1 得到结果3，即当前n 的值为3.

（4）一直递归下去。

（5）直到当n为8时，where条件不成立，无法生成新行，递归停止。

?递归查询伟哥商品到顶级商品的层次：

查询一个指定商品的所有父级商品：

?

?

-- end