让人头大的MySQL数据库高级查询（挠头）

最新推荐文章于 2024-05-22 19:15:00 发布

偉大的渺小

最新推荐文章于 2024-05-22 19:15:00 发布

阅读量559

点赞数 2

分类专栏： mysql 高级查询文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_48185204/article/details/108701520

版权

mysql 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

高级查询

1 篇文章 0 订阅

订阅专栏

MySQL数据库高级查询

文章目录

前言

在对MySQL数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法。
SQL语句的熟练使用，在平时的运维工作中可以提供不小的帮助，尤其是在一些规模较小的公司，运维身兼数职，
可能会有不少数据库的相关工作。本章将从不同的方面出发介绍 SQL 语句的高级运用方法。

常用查询介绍

对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、
对查询结果进行排序或分组等等，这些内容就是接下来要讲解的知识。

1.1: 按关键字排序

使用SELECT语句可以将需要的数据从MySQL数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢？
可以使用 ORDER BY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，
也可以针对多个字段。以下就是MySQL中ORDER BY语句的语法结构。

语法结构

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

ASC：按照升序进行排序的，默认排序就是升序排列，如果没有特殊说明，selecy都是按照升序排列
DESC：按照降序进行排列

下面看一下实际演示结果:

首先我们需要一个建立一个数据表进行操作，在数据库内输入一下内容

create database player;
use player;
create table player (id int(4) not null,name varchar(10) not null,level int(3) not null,primary key (`id`));
insert into player (id,name,level) values ('30','抢宝真多呀',47);
insert into player (id,name,level) values ('15','新五皇·白胡子',46);
insert into player (id,name,level) values ('63','新五皇–敬神',46);
insert into player (id,name,level) values ('199','D 丶狙击王',46);
insert into player (id,name,level) values ('298','唐三',46);
insert into player (id,name,level) values ('51','新五皇·暴雪',45);
insert into player (id,name,level) values ('272','D 丶抢人头辅助',45);

mysql> select id,name,level from player where level>=45 order by level desc;     ###查询登记大于45的用户
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 抢宝真多呀           |    47 |
|  15 | 新五皇·白胡子        |    46 |
|  63 | 新五皇–敬神          |    46 |
| 199 | D 丶狙击王           |    46 |
| 298 | 唐三                 |    46 |
|  51 | 新五皇·暴雪          |    45 |
| 272 | D 丶抢人头辅助       |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

那么有同学家就有疑问了，这个排序只是针对一个字段进行排序，能不能对多个字段进行排序呢？答案是能

mysql>  select id,name,level from player where level>=45 order by level desc,id desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 抢宝真多呀           |    47 |
| 298 | 唐三                 |    46 |
| 199 | D 丶狙击王           |    46 |
|  63 | 新五皇–敬神          |    46 |
|  15 | 新五皇·白胡子        |    46 |
| 272 | D 丶抢人头辅助       |    45 |
|  51 | 新五皇·暴雪          |    45 |
+-----+----------------------+-------+
7 rows in set (0.01 sec)
            ###上面命令可以看出，首先对等级进行降序排列，接着在对id进行降序排列

我们这边将order命令后面的语句稍微整理了下：

 ###order命令可以整理如下：
 
ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，
优先级是按先后顺序而定。下面以A和B分别表示两个字段。
##ORDER BY A,B desc 指A用升序，B用降序；##
##ORDER BY A asc,B desc 指A用升序，B用降序；##
##ORDER BY A desc,B desc 指A用降序，B用降序；##

1.2: 对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。
GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。
GROUP BY通常都是结合聚合函数一起使用的，常用的聚合函数包括：
计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），
这些聚合函数的用法在后面函数小节会有更详细的讲解。GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

语句结构：

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;

GROUP BY除了配合聚合函数一起使用外，还可以引入WHERE子句。首先通过WHERE过滤掉一部分不符合需求的查询结果，
然后再对结果进行分组。如果有排序的需求，也可以引入ORDER BY语句。

例如：统计等级在 45 级及以上，以等级为分组，每个等级有多少人

mysql> select count(name),level from player where level >= 45 group by level;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           2 |    45 |
|           4 |    46 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

1.3: 限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句。

语句结构:

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。
如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，
第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。
如果 SELECT 查询出的结果记录比较多，用户查看不是很方便。这个时候可以返回固定的、有限的记录数量，
使用 MySQL 数据库的 LIMIT 子句即可实现。LIMIT 子句是一种简单的分页方法，
它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。

例如，查看表中前3行的数据：

mysql>  select id,name,level from player limit 3;
+----+----------------------+-------+
| id | name                 | level |
+----+----------------------+-------+
| 15 | 新五皇·白胡子        |    46 |
| 30 | 抢宝真多呀           |    47 |
| 51 | 新五皇·暴雪          |    45 |
+----+----------------------+-------+
3 rows in set (0.00 sec)

LIMIT 子句的使用也可以结合 ORDER BY：先进行排序，然后再LIMIT限制固定的记录。
也就是说LIMIT是放在最后的，将处理好的结果集按要求选出几行来。

mysql> select id,name,level from player limit 2,3;
+-----+--------------------+-------+
| id  | name               | level |
+-----+--------------------+-------+
|  51 | 新五皇·暴雪        |    45 |
|  63 | 新五皇–敬神        |    46 |
| 199 | D 丶狙击王         |    46 |
+-----+--------------------+-------+
3 rows in set (0.00 sec)

1.4: 设置别名

在MySQL查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，
可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。

语法结构：

SELECT column_name AS alias_name FROM table_name;   ###表的 SQL Alias 语法
SELECT column_name(s) FROM table_name AS alias_name;   ###列的 SQL Alias 语法

在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS之后的别名，
主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的。

例如，在统计表内所有记录共有多少条时，使用 count（*）， 这么写不便于识别，可以将其别名设置为 number。

mysql> select count(*) as number from player;
+--------+
| number |
+--------+
|     16 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) number from player;    ###省略as，结果是一样的
+--------+
| number |
+--------+
|     16 |
+--------+
1 row in set (0.00 sec)

如果表的长度比较长，可以使用AS给表设置别名，在查询的过程中直接使用别名。

例如，执行以下操作即可将 player 表的别名设置成 p。

mysql> select p.id,p.name from player as p limit 3;
+----+----------------------+
| id | name                 |
+----+----------------------+
| 15 | 新五皇·白胡子        |
| 30 | 抢宝真多呀           |
| 51 | 新五皇·暴雪          |
+----+----------------------+
3 rows in set (0.00 sec)

mysql> select p.id,p.name from player p limit 3;    ###省略as结果是一样的
+----+----------------------+
| id | name                 |
+----+----------------------+
| 15 | 新五皇·白胡子        |
| 30 | 抢宝真多呀           |
| 51 | 新五皇·暴雪          |
+----+----------------------+
3 rows in set (0.00 sec)

此外，AS还可以作为连接语句的操作符。

例如，执行以下操作即可实现用一条 SQL语句完成在创建表tmp的时候将player表内的数据写入 tmp 表。

mysql> create table tmp as select * from player;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

※※※注意※※※

在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用。

1.5: 通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。
常用的通配符有两个，分别是：

%：百分号表示零个、一个或多个字符
_：下划线表示单个字符

举个栗子：

例如，查询 player 表内 name 字段分别以 s 开头的名字、以 s 结尾的名字和名字中间包含 es 的字段：

mysql> select id,name,level from player where name like 's%';   ###查询name字段以s开头的记录
+------+--------------+-------+
| id   | name         | level |
+------+--------------+-------+
|  238 | sagou 轰总   |     7 |
|  795 | senoku       |    15 |
| 2460 | shirley      |     1 |
+------+--------------+-------+
3 rows in set (0.00 sec)

mysql> select id,name,level from player where name like '%s';   ###查询name字段以s结尾的记录
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
| 713 | guess   |    25 |
+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select id,name,level from player where name like'%es%';   ###查询name字段中间包含es的字段
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
|  448 | useless |     1 |
|  713 | guess   |    25 |
| 1979 | Theshy  |    24 |
| 2237 | leslieF |     3 |
+------+---------+-------+
4 rows in set (0.01 sec)

如果匹配name字段中某一个字符，可以使用%。但是MySQL提供的专门针对单个字符的通配符，就是“_”一个下划线，
使用单个下划线可以替换字符串中的某个单字符。

例如，替换表内name字段开头的字符、结尾的字符或者中间的字符。

mysql> select id,name,level from player where name like '_uess';   ###替换开头的一个字符
+-----+-------+-------+
| id  | name  | level |
+-----+-------+-------+
| 713 | guess |    25 |
+-----+-------+-------+
1 row in set (0.00 sec)

mysql> select id,name,level from player where name like 'use____';   ###替换结尾的四个字符
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
+-----+---------+-------+
1 row in set (0.00 sec)

※※※注意※※※
"_"这个通配符，只可以替换一个字符，若想替换多个字符，就需要多个"_"来替换，就如上面所示，我们替换了4个就需要4个"_"

通配符“%”和“_”不仅可以单独使用，也可以组合使用。

例如name 字段中，开头有一个字符，接着是es两个字符，后面再跟着零个、一个或多个字符，从player表中查询这样的数据。

mysql> select id,name,level from player where name like '_es%';   ###查询name字段中符合定义的字段
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2237 | leslieF |     3 |
+------+---------+-------+
1 row in set (0.00 sec)

1.6: 子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句子查询语句是先于主查询语句被执行的，
其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE
中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。
在开始实际的举例之前，先来学习一下IN 这个操作符的用法，IN用来判断某个值的是否。

在给定的结果集中，通常结合子查询来使用。IN 的语法结构如下：

<表达式> [NOT] IN <子查询>

当表达式与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回 FALSE。若启用了 NOT 关键字，
则返回值相反。需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。
多数情况下，子查询都是与SELECT语句一起使用的。

例如，先查出等级大于等于45级的 ID，然后在判断player表内的ID是不是在这个结果集内，如果在就打印此行的名字和等级

mysql> select name,level from player where id in (select id from player where level >= 45);
+----------------------+-------+
| name                 | level |
+----------------------+-------+
| 新五皇·白胡子        |    46 |
| 抢宝真多呀           |    47 |
| 新五皇·暴雪          |    45 |
| 新五皇–敬神          |    46 |
| D 丶狙击王           |    46 |
| D 丶抢人头辅助       |    45 |
| 唐三                 |    46 |
+----------------------+-------+
7 rows in set (0.01 sec)

从上例可以看到，子查询是被放到括号内的，这个括号是无法省略的，缺失则会报错，无法形成子查询。
子查询内要查询的字段通常都是一个，查询后形成结果集供主查询使用。

子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中。

例如，先清空之前使用的 tmp 表，然后通过子查询的方式将 player 的内容插入到 tmp 表中。

mysql> truncate table tmp;   ###清空tmp临时表
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tmp;   ###查看临时表是否被清空
Empty set (0.00 sec)

mysql> insert into tmp select * from player where id in (select id from player);   ###player表插入临时表
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into tmp select * from player;  ###这个步骤跟上面的可以达到同样的效果
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

UPDATE语句也可以使用子查询。UPDATE内的子查询，在set更新内容时，可以是单独的一列，也可以是多列。

例如，执行以下操作即可通过子查询实现将等级大于等于 47 的用户减去 7。

mysql> select id,name,level from tmp where id=30;
+----+-----------------+-------+
| id | name            | level |
+----+-----------------+-------+
| 30 | 抢宝真多呀      |    47 |
+----+-----------------+-------+
1 row in set (0.00 sec)

mysql> update tmp set level = level - 7 where id in (select id from tmp where level >= 47);
ERROR 1093 (HY000): You can't specify target table 'tmp' for update in FROM clause
类似 select 方式的子查询，MySQL不支持查询，需要多引入一层子查询

mysql> update tmp set level = level - 7 where id in(select a.id from (select id from tmp where level >= 47)a);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,name,level from tmp where id = 30;
+----+-----------------+-------+
| id | name            | level |
+----+-----------------+-------+
| 30 | 抢宝真多呀      |    40 |
+----+-----------------+-------+
1 row in set (0.00 sec)       ###可以看出等级这一行已经减少了7

DELETE也适用于子查询。例如，先将原来47级的用户等级恢复，然后通过子查询的方式将47级用户删除。
实现方式类似 UPDATE 的子查询，具体操作如下所示。

mysql> update tmp set level=47 where id=30;  ###将原来的值还原
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from tmp where id in(select a.id from(select id from tmp where level=47)a);  ###删除等级=47的字段
Query OK, 1 row affected (0.00 sec)

mysql> select id,name,level from tmp where id=30;   ###字段已经被删除
Empty set (0.00 sec)

除了以上所列举的 IN 操作符，在IN前面还可以添加NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）。

例如，执行以下操作即可查询用户等级不小于 45 级的用户。

mysql> select name,level from player where id not in (select id from tmp where level < 45);
+----------------------+-------+
| name                 | level |
+----------------------+-------+
| 新五皇·白胡子        |    46 |
| 抢宝真多呀           |    47 |
| 新五皇·暴雪          |    45 |
| 新五皇–敬神          |    46 |
| D 丶狙击王           |    46 |
| D 丶抢人头辅助       |    45 |
| 唐三                 |    46 |
+----------------------+-------+
7 rows in set (0.00 sec)

子查询上也可以使用比较运算符（=、<、>、>=、<=），这些运算符主要是对运算符前面的表达式和后面的子查询进行比较运算。

如，查询出名字是 shirley 的记录，并输出其 ID、名字和等级信息，具体操作如下所示。

mysql> select id,name,level  from tmp where id = (select id from tmp where name='shirley');
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2460 | shirley |     1 |
+------+---------+-------+
1 row in set (0.00 sec)

EXIST 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE；
反之，则返回FALSE。例如，先通过子查询判断返回是否为TRUE，如果用户shirley存在，则计算整个tmp表的总记录数量，具体操作如下所示。

mysql> select count(*) as number from tmp where EXISTS(select id from tmp where name='shirley');
+--------+
| number |
+--------+
|     15 |
+--------+
1 row in set (0.00 sec)