摘星小筑

DBA MySQL单表查询

前戏工作

数据准备

​ 单表查询相关数据：

CREATE TABLE userInfo(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
    name CHAR(32) NOT NULL COMMENT "用户姓名",
    gender ENUM("MALE", "FEMALE") NOT NULL DEFAULT "MALE" COMMENT "用户性别",
    age TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT "用户年龄",
    role ENUM("USER", "ADMIN", "BOSS") NOT NULL DEFAULT "USER" COMMENT "用户角色",
    fans_quantinty INT NOT NULL DEFAULT 0 COMMENT "粉丝数量",
    create_time DATETIME NOT NULL DEFAULT NOW() COMMENT "创建时间",
    delete_status ENUM("0", "1") NOT NULL DEFAULT "0" COMMENT "是否注销"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

INSERT INTO
    userInfo(name, gender, age, role, fans_quantinty, create_time, delete_status)
VALUES
    ("YunYa", "MALE", 18, "BOSS", 0, "2002-01-28 19:18:22", "0"),
    ("Jack", "MALE", 21, "ADMIN", 2000, "2006-03-05 08:30:00", "0"),
    ("David", "MALE", 18, "ADMIN", 3689, "2004-02-21 14:30:00", "0"),
    ("Dairis", "FEMALE", 19, "ADMIN", 5232, "2008-09-21 08:30:00", "0"),
    ("Kyle", "FEMALE", 21, "USER", 123021, "2002-08-22 12:02:31", "1"),
    ("Alice", "FEMALE", 24, "USER", 293042, "2010-03-07 23:02:21", "0"),
    ("Ken", "MALE", 23, "USER", 231, "2020-11-13 09:06:32", "0"),
    ("Jason", "MALE", 22, "USER", 32132134, "2002-02-01 12:32:45", "0"),
    ("Tom", "MALE", 28, "USER", 32, "2021-01-01 02:30:21", "1"),
    ("Fiona", "FEMALE", 18, "USER", 321234, "2012-05-28 19:21:32", "0");

查询语法

​ 查询语法如下：

SELECT
    DISTINCT(字段名1, 字段名2...)
FROM
    表名
WHERE
    过滤条件
GROUP BY
    分组字段名
HAVING
    筛选条件
ORDER BY
    排序字段名 ASC / DESC
LIMIT
    限制条数;

执行顺序

​ 内部执行顺序如下所示：

1.通过FROM找到将要查询的表
2.WHERE规定查询条件，在表记录中逐行进行查询并过滤出符合规则的记录
3.将过滤到的记录信息按照字段进行GROUP BY分组，如果没有进行分组，则默认按照主键分为1组
4.将分组得到的结果进行HAVING筛选，此时可使用聚合函数。WHERE时不可使用聚合函数
5.执行SELECT准备打印
6.执行DISTINCT()函数对打印结果进行去重
7.执行ORDERY BY对结果进行排序，升序ASC，降序DESC
8.执行LIMIT对打印结果进行条数限制

SELECT

功能概述

​ SELECT主要负责打印相关的工作。

​ 除了查表外，也可以单独使用，如以下命令：

SELECT DATABASE();

全部查询

​ 全部查询的语句格式如下：

SELECT * FROM 表名;

​ 示例演示：

M > SELECT * FROM userinfo;
+----+--------+--------+-----+-------+----------------+---------------------+---------------+
| id | name   | gender | age | role | fans_quantinty | create_time         | delete_status |
+----+--------+--------+-----+-------+----------------+---------------------+---------------+
|  1 | YunYa  | MALE   |  18 | BOSS  |              0 | 2002-01-28 19:18:22 | 0             |
|  2 | Jack   | MALE   |  21 | ADMIN |           2000 | 2006-03-05 08:30:00 | 0             |
|  3 | David  | MALE   |  18 | ADMIN |           3689 | 2004-02-21 14:30:00 | 0             |
|  4 | Dairis | FEMALE |  19 | ADMIN |           5232 | 2008-09-21 08:30:00 | 0             |
|  5 | Kyle   | FEMALE |  21 | USER  |         123021 | 2002-08-22 12:02:31 | 1             |
|  6 | Alice  | FEMALE |  24 | USER  |         293042 | 2010-03-07 23:02:21 | 0             |
|  7 | Ken    | MALE   |  23 | USER  |            231 | 2020-11-13 09:06:32 | 0             |
|  8 | Jason  | MALE   |  22 | USER  |       32132134 | 2002-02-01 12:32:45 | 0             |
|  9 | Tom    | MALE   |  28 | USER  |             32 | 2021-01-01 02:30:21 | 1             |
| 10 | Fiona  | FEMALE |  18 | USER  |         321234 | 2012-05-28 19:21:32 | 0             |
+----+--------+--------+-----+-------+----------------+---------------------+---------------+

字段查询

​ 使用以下语句格式进行查询，可拿到特定的字段及其下所对应的数据。

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

​ 示例演示，查询每个用户的角色：

M > SELECT name,role FROM userinfo;
+--------+-------+
| name   | role |
+--------+-------+
| YunYa  | BOSS  |
| Jack   | ADMIN |
| David  | ADMIN |
| Dairis | ADMIN |
| Kyle   | USER  |
| Alice  | USER  |
| Ken    | USER  |
| Jason  | USER  |
| Tom    | USER  |
| Fiona  | USER  |
+--------+-------+

别名使用

​ 使用AS进行别名定制，除了在SELECT语句中使用，多表查询时也经常使用到。

​ 使用格式如下：

SELECT 字段名1 AS 别名1, 字段名2 AS 别名2 FROM 表名;

​ 示例演示，查询每个用户的姓名，年龄，角色，注意观察首行字段的名字：

M > SELECT
           name AS "姓名",
           age AS "年龄",
           gender AS "性别",
           role AS "用户角色"
       FROM
           userInfo;
+--------+--------+--------+--------------+
| 姓名   | 年龄   | 性别   | 用户角色     |
+--------+--------+--------+--------------+
| YunYa  |     18 | MALE   | BOSS         |
| Jack   |     21 | MALE   | ADMIN        |
| David  |     18 | MALE   | ADMIN        |
| Dairis |     19 | FEMALE | ADMIN        |
| Kyle   |     21 | FEMALE | USER         |
| Alice  |     24 | FEMALE | USER         |
| Ken    |     23 | MALE   | USER         |
| Jason  |     22 | MALE   | USER         |
| Tom    |     28 | MALE   | USER         |
| Fiona  |     18 | FEMALE | USER         |
+--------+--------+--------+--------------+

DISTINCT()

​ 使用DISTINCT()函数可进行去重，如下示例将展示该表中拥有多少个用户角色：

M > SELECT
           DISTINCT(role) AS "用户角色"
       FROM
           userInfo;
+--------------+
| 用户角色     |
+--------------+
| BOSS         |
| ADMIN        |
| USER         |
+--------------+

四则运算

​ 查询结果可进行四则运算，以下示例将展示该表中所有人的粉丝数+1000是多少：

M > SELECT
           name AS "用户名",
           fans_quantinty + 1000 AS "增加1000粉丝后的结果"
       FROM
           userInfo;
+-----------+------------------------------+
| 用户名    | 增加1000粉丝后的结果         |
+-----------+------------------------------+
| YunYa     |                         1000 |
| Jack      |                         3000 |
| David     |                         4689 |
| Dairis    |                         6232 |
| Kyle      |                       124021 |
| Alice     |                       294042 |
| Ken       |                         1231 |
| Jason     |                     32133134 |
| Tom       |                         1032 |
| Fiona     |                       322234 |
+-----------+------------------------------+

显示格式

​ 这里有两个关于显示格式的函数，其中第一个函数尤为常用：

函数 描述
CONCAT() 可将查询结果与任意字符串进行拼接
CONCAT_WS() 可指定连接符进行拼接，第1个参数是连接符

​ 以下示例将展示CONCAT()函数如何使用：

#  打印任意格式的数据
M > SELECT
           CONCAT("用户名:", name, "-------", "粉丝数:", fans_quantinty)
       FROM
           userInfo;
+---------------------------------------------------------------------+
| CONCAT("用户名:", name, "-------", "粉丝数:", fans_quantinty)       |
+---------------------------------------------------------------------+
| 用户名:YunYa-------粉丝数:0                                         |
| 用户名:Jack-------粉丝数:2000                                       |
| 用户名:David-------粉丝数:3689                                      |
| 用户名:Dairis-------粉丝数:5232                                     |
| 用户名:Kyle-------粉丝数:123021                                     |
| 用户名:Alice-------粉丝数:293042                                    |
| 用户名:Ken-------粉丝数:231                                         |
| 用户名:Jason-------粉丝数:32132134                                  |
| 用户名:Tom-------粉丝数:32                                          |
| 用户名:Fiona-------粉丝数:321234                                    |
+---------------------------------------------------------------------+

​ 以下示例将展示CONCAT_WS()函数如何使用：

#  使用*****为字段进行分割
M > SELECT
           CONCAT_ws("*****", name, fans_quantinty)
       FROM
           userInfo;
+------------------------------------------+
| CONCAT_ws("*****", name, fans_quantinty) |
+------------------------------------------+
| YunYa*****0                              |
| Jack*****2000                            |
| David*****3689                           |
| Dairis*****5232                          |
| Kyle*****123021                          |
| Alice*****293042                         |
| Ken*****231                              |
| Jason*****32132134                       |
| Tom*****32                               |
| Fiona*****321234                         |
+------------------------------------------+

WHERE

功能概述

​ WHERE能够有效过滤出我们想要的任意数据，但是要注意语句优化的使用。

​ 比如在比较运算中，尽量避免使用!=，少使用范围查询> <等操作。

比较运算

​ WHERE中支持的比较运算符> < = >= <= !=，以下示例将展示过滤出粉丝大于10000的用户。

M > SELECT name FROM userInfo WHERE fans_quantinty > 10000;
+-------+
| name  |
+-------+
| Kyle  |
| Alice |
| Jason |
| Fiona |
+-------+

逻辑运算

​ 使用AND OR NOT可进行逻辑运算与多条件查询。

​ 以下示例中将展示找到年龄大于18并且年龄小于20的用户：

M > SELECT name, age FROM userInfo WHERE age > 18 and age < 20;
+--------+-----+
| name   | age |
+--------+-----+
| Dairis |  19 |
+--------+-----+

成员运算

​ IN可以在特定的值中进行获取，如IN(80,90,100)则代表只取80或者90或者100的这几条记录。

​ 以下示例中将展示找到role为BOOS和ADMIN的用户：

M > SELECT name FROM userInfo WHERE role in ("ADMIN","BOSS");
+--------+
| name   |
+--------+
| YunYa  |
| Jack   |
| David  |
| Dairis |
+--------+

BETWEEN&AND

​ BETWEEN AND是取区间的意思。

​ 与>=并且<的意思是相同的。

​ 以下示例中将展示找到年龄大于等于20，且小于25的用户：

M > SELECT name, age FROM userInfo WHERE age BETWEEN 20 AND 25;
+-------+-----+
| name  | age |
+-------+-----+
| Jack  |  21 |
| Kyle  |  21 |
| Alice |  24 |
| Ken   |  23 |
| Jason |  22 |
+-------+-----+

LIKE查询

​ LIKE是模糊查询。

​ 其中%代表任意多个字符（类似于贪婪匹配的通配符.+）。

​ _代表任意一个字符（类似于非贪婪匹配的通配符.+?）。

不要出现类似于%xx%这种前后都有百分号的语句

因为不走索引，所以性能极差

​ 以下示例将展示使用LIKE匹配出姓名以k开头的所有用户的名字：

M > SELECT name FROM userInfo WHERE name LIKE "K%";
+------+
| name |
+------+
| Kyle |
| Ken  |
+------+

REGEXP

​ REGEXP可用作正则匹配。

mysql8版本以下不允许出现?的取消贪婪符号

​ 以下示例将展示使用REGEXP匹配出姓名中带有k字符的所有用户的名字：

M > SELECT name FROM userInfo WHERE name REGEXP "K+";
+------+
| name |
+------+
| Kyle |
| Ken  |
+------+

GROUP BY

功能概述

​ 分组行为发生在WHERE条件之后，我们可以将查询到的记录按照某个相同字段进行归类，一般分组都会配合聚合函数进行使用。

​ 需要注意的是SELECT语句是排在GROUP BY条件之后的，因此聚合函数也能在SELECT语句中使用。

​ 如果不使用GROUP BY语句进行分组，则默认会将所有记录分成一组！详情参见分组模式。

基本使用

​ 以下示例将展示对role字段进行分组。

我们按照role字段进行分组时，受分组模式影响，SELECT后打印的字段只能是role字段或者是被聚合函数包裹住的其余字段。

如果想要直接获取组内其它的字段信息，需要借助函数GROUNP_CONCAT()或修改分组模式来完成。

​ 正常的情况下，role是分组条件，SELECT后只能出现role：

M > SELECT
           role
       FROM
           userInfo
       GROUP BY
           role;
+-------+
| role |
+-------+
| USER  |
| ADMIN |
| BOSS  |
+-------+

​ 如果还想直接打印其他字段信息而不包裹聚合函数的话，如直接打印name的操作，就会抛出模式错误的异常：

M > SELECT
           role,
           name
       FROM
           userInfo
       GROUP BY
           role;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db1.userInfo.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

显示函数

​ 受到分组模式的影响，我们只能在SELECT语句后直接打印出GROUNP BY指定的字段，这很不方便。

​ 使用分组显示函数GROUP_CONCAT()可改变这种状况，配合其他显示函数食用效果更佳，如下所示，展示role分组与其中的成员信息：

M > SELECT
           role AS "等级",
           GROUP_CONCAT(CONCAT("", name, "|", age, "")) AS "成员信息(name,age)"
       FROM
           userInfo
       GROUP BY
           role;
+--------+--------------------------------------------------------------+
| 等级   | 成员信息(name,age)                                             |
+--------+--------------------------------------------------------------+
| USER   | Kyle|21,Alice|24,Ken|23,Jason|22,Tom|28,Fiona|18             |
| ADMIN  | Jack|21,David|18,Dairis|19                                   |
| BOSS   | YunYa|18                                                     |
+--------+--------------------------------------------------------------+

分组模式

​ ONLY_FULL_GROUP_BY要求SELECT中出现的字段必须是在GROUP BY中使用的字段，或者是被聚合函数包裹，否则抛出异常。

​ 解决办法如下所示：

  1. 使用函数GROUP_CONCAT()将位于SELECT语句后且不在GROUP BY分组条件中的字段进行包裹
  2. 使用函数ANY_VALUE()将位于SELECT语句后且不在GROUP BY分组条件中的字段进行包裹
  3. 使用聚合函数将位于SELECT语句后且不在GROUP BY分组条件中的字段进行包裹
  4. 通过更改分组模式sql_mode，允许位于SELECT语句后且不在GROUP BY分组条件中的字段进行显示
  5. 如果GROUP BY后的字段是主键或拥有NOT NULL UNIQUE约束时可以在SELECT后列出其他字段而不必使用上述手段进行处理
# 查看mysql5.7中默认的sql_mode:
M > SELECT @@GLOBAL.SQL_MODE;

# 修改sql_mode，去掉ONLY_FULL_GROUP_BY模式，本次mysqld.service服务运行中生效，重启服务失效
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

聚合函数

​ 聚合函数一般放在SELECT语句后和HAVING语句后使用。

​ 聚合函数一般是同分组进行配套使用，以下是常用的聚合函数。

注意：不使用分组，则默认为一组，所以也可以在SELECT语句后面使用聚合函数

函数名 作用
COUNT() 对组内成员某一字段求个数
MAX() 对组内成员某一字段求最大值
MIN() 对组内成员某一字段求最小值
AVG() 对组内成员某一字段求平均值
SUM() 对组内成员某一字段求和

​ 示例演示，以role进行分组，查看每个组中年纪最大的人：

M > SELECT
           role AS "等级",
           MAX(age) AS "最大年纪"
       FROM
           userInfo
       GROUP BY
           role;
+--------+--------------+
| 等级   | 最大年纪     |
+--------+--------------+
| USER   |           28 |
| ADMIN  |           21 |
| BOSS   |           18 |
+--------+--------------+

​ 示例演示，以age分组，查看不同年龄段中有多少人：

M > SELECT
           age AS "年龄",
           COUNT(id) AS "人数"
       FROM
           userInfo
       GROUP BY
           age;
+--------+--------+
| 年龄   | 人数   |
+--------+--------+
|     18 |      3 |
|     19 |      1 |
|     21 |      2 |
|     22 |      1 |
|     23 |      1 |
|     24 |      1 |
|     28 |      1 |
+--------+--------+

HAVING

使用介绍

​ HAVING可以对分组后的数据进行筛选，和WHERE最大的区别就是能够使用聚合函数进行筛选。

​ 执行优先级从高到低：WHERE> GROUNP BY > HAVING，先分组，后HAVING过滤。

  1. WHERE发生在分组GROUNP BY之前，因而WHERE中可以有任意字段，但是绝对不能使用聚合函数。
    2. HAVING发生在分组GROUNP BY之后，因而HAVING中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数
    3. 如果不使用分组GROUNP BY而单纯使用HAVING并且同时使用聚合函数作为筛选的话，SELECT将会受到分组模式的影响，可查看上面章节进行解决

​ 过滤出role是ADMIN的用户，且归到一个组中：

M > SELECT
           GROUP_CONCAT(name)
       FROM
           userInfo
       GROUP BY
           role
       HAVING
           role = "ADMIN";
+--------------------+
| GROUP_CONCAT(name) |
+--------------------+
| Jack,David,Dairis  |
+--------------------+

ORDER BY

排序规则

​ 默认排序是按照id进行排序，也就是主键。

​ 它的比对规则是对比ASCII码来产生的。

​ 排序可以有多重规则。

ASC

​ 升序排序，也是默认的排序规则，以下示例中将展示按照粉丝数量进行升序排序：

M > SELECT
           name,
           fans_quantinty
       FROM
           userInfo
       ORDER BY
           fans_quantinty;  -- 默认的排序规则，所以不用指定ASC
+--------+----------------+
| name   | fans_quantinty |
+--------+----------------+
| YunYa  |              0 |
| Tom    |             32 |
| Ken    |            231 |
| Jack   |           2000 |
| David  |           3689 |
| Dairis |           5232 |
| Kyle   |         123021 |
| Alice  |         293042 |
| Fiona  |         321234 |
| Jason  |       32132134 |
+--------+----------------+

DESC

​ 降序排序，以下示例中将展示用户年龄进行降序排序，如年龄相同则按照粉丝数量进行比对：

M > SELECT
           name,
           age,
           fans_quantinty
       FROM
           userInfo
       ORDER BY
           age DESC,
           fans_quantinty DESC;
+--------+-----+----------------+
| name   | age | fans_quantinty |
+--------+-----+----------------+
| Tom    |  28 |             32 |
| Alice  |  24 |         293042 |
| Ken    |  23 |            231 |
| Jason  |  22 |       32132134 |
| Kyle   |  21 |         123021 |
| Jack   |  21 |           2000 |
| Dairis |  19 |           5232 |
| Fiona  |  18 |         321234 |
| David  |  18 |           3689 |
| YunYa  |  18 |              0 |
+--------+-----+----------------+

LIMIT

基本演示

​ LIMIT用于控制SELECT打印的条目数量。

​ 一般来说我们会对查询都会做上LIMIT来提升性能。

​ 以下示例中，将展示控制显示条目为1条，以此来获得表中年龄最小，粉丝最少的一个人：

M > SELECT
           name,
           age,
           fans_quantinty
       FROM
           userInfo
       ORDER BY
           age,
           fans_quantinty
       LIMIT
           1;
+-------+-----+----------------+
| name  | age | fans_quantinty |
+-------+-----+----------------+
| YunYa |  18 |              0 |
+-------+-----+----------------+

​ 以下示例中，将展示全表中从第5条开始（不包含5）向后取3条的数据：

M > SELECT
           *
       FROM
           userInfo
       LIMIT
           5, 3;
+----+-------+--------+-----+-------+----------------+---------------------+---------------+
| id | name  | gender | age | role | fans_quantinty | create_time         | delete_status |
+----+-------+--------+-----+-------+----------------+---------------------+---------------+
|  6 | Alice | FEMALE |  24 | USER  |         293042 | 2010-03-07 23:02:21 | 0             |
|  7 | Ken   | MALE   |  23 | USER  |            231 | 2020-11-13 09:06:32 | 0             |
|  8 | Jason | MALE   |  22 | USER  |       32132134 | 2002-02-01 12:32:45 | 0             |
+----+-------+--------+-----+-------+----------------+---------------------+---------------+
posted @ 2021-02-18 00:41  云崖君  阅读(67)  评论(0)  编辑  收藏  举报