一、 MySQL 进阶查询
- 在对MySQL数据库的增、删、改、查操作有一定了解之后,就可以学习一些 SQL 语句的高级使用方法。
- SQL语句的熟练使用,在平时的运维工作中可以提供不小的帮助,尤其是在一些规模较小的公司,运维身兼数职,可能会有不少数据库的相关工作。本章将从不 同的方面出发介绍 SQL 语句的高级运用方法。
1.1、常用查询介绍
对MySQL数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等,这些内容就是接下来要讲解的知识。
1.1.1、按关键字排序
使用SELECT语句可以将需要的数据从MySQL数据库中查询出来,如果对查询的结果进行排序,该如何去实现呢? 可以使用 ORDER BY 语句来完成排序,并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段,也可以针对多个字段。以下就是MySQL中ORDER BY语句的语法结构。
###语法结构###
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
排序的字段可以根据具体需求进行选择,没有限制。排序的关键字可以使用 ASC 或者DESC。ASC 是按照升序进行排序的,是默认的排序方式,即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式,则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一过滤。
例如,执行以下操作可查询等级大于等于 45 级的用户,并按降序进行排序。
1.1.2、数据库内容表插入
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;
+-----+----------------------+-------+
| 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)
对于排序要求,多数情况下一个字段就可以实现。其实 ORDER BY 语句也可以使用多个字段来进行排序,当排序的第一个字段相同的记录有多条的情况下,这些多条的记录再按 照第二个字段进行排序。
例如,执行以下操作可查询等级在 45 级及以上的用户,并以 level 降序排列和 id 降序排列。
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.00 sec)
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.1.3、对结果进行分组
通过 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)
从以上结果可以看出,每个等级的人数已经统计出来了,但是数量是无序的。如果结果条目变得更多,很难一眼看出哪个等级的人数是最多的,所以需要对数量进行排序。而GROUP BY 结合 ORDER BY 即可实现分组并排序的查询。例如,查询等级在 45 级及以上, 按等级进行分组,并将每个等级的人数按降序排序,具体操作如下。
mysql> select count(name),level from player where level>=45 group by level order by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
| 4 | 46 |
| 2 | 45 |
| 1 | 47 |
+-------------+-------+
3 rows in set (0.00 sec)
排序后的结果一目了然,方便用户获取用户最多的等级信息。
1.1.4、限制结果条目
在使用 MySQL SELECT 语句进行查询时,结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行,这时候就需要用到 LIMIT 子句。
###语法结构###
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
LIMIT 的第一个参数是位置偏移量(可选参数),是设置 MySQL 从哪一行开始显示。如果不设定第一个参数,将会从表中的第一条记录开始显示。需要注意的是,第一条记录的位置偏移量是 0,第二条是 1,以此类推。第二个参数是设置返回记录行的最大数目。如果 SELECT 查询出的结果记录比较多,用户查看不是很方便。这个时候可以返回固定的、有限的记录数量,使用 MySQL 数据库的 LIMIT 子句即可实现。LIMIT 子句是一种简单的分页方法,它的使用减少了数据结果的返回时间,提高了执行效率,也解决了由于数据量过大从而导致的性能问题。
例如,执行以下操作即可查询表的前 3 个用户的信息。
####环境创建###如果表已经创建,可以向表内插入明细数据
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 ('1','修欧拉卡',10);
insert into player (id,name,level) values ('2','起风了',10);
insert into player (id,name,level) values ('3','吊打低V',15);
insert into player (id,name,level) values ('4','小花',14);
insert into player (id,name,level) values ('5','小舞',35);
mysql> select id,name,level from player limit 3;
+----+--------------+-------+
| id | name | level |
+----+--------------+-------+
| 1 | 修欧拉卡 | 10 |
| 2 | 起风了 | 10 |
| 3 | 吊打低V | 15 |
+----+--------------+-------+
3 rows in set (0.00 sec)
LIMIT 子句的使用也可以结合 ORDER BY:先进行排序,然后再LIMIT限制固定的记录。也就是说LIMIT是放在最后的,将处理好的结果集按要求选出几行来。
例如,将查询记录按等级 level 降序排列,只取前三条记录。
mysql> select id,name,level from player order by level desc limit 3;
+----+----------------------+-------+
| id | name | level |
+----+----------------------+-------+
| 30 | 抢宝真多呀 | 47 |
| 15 | 新五皇·白胡子 | 46 |
| 63 | 新五皇–敬神 | 46 |
+----+----------------------+-------+
3 rows in set (0.00 sec)
在显示结果的时候也可以不从第一行开始,引入 offset 参数。例如,执行以下操作即可从第 3 条记录开始显示之后的 3 条数据。
mysql> select id,name,level from player limit 2,3;
+----+------------+-------+
| id | name | level |
+----+------------+-------+
| 3 | 吊打低V | 15 |
| 4 | 小花 | 14 |
| 5 | 小舞 | 35 |
+----+------------+-------+
3 rows in set (0.00 sec)
1.1.5、设置别名
在MySQL查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表,可以给字段列或表设置别名。使用的时候直接使用别名,简洁明了,增强可读性。设置别名使用AS语句,
列的别名语法结构:
语法结构
SELECT column_name AS alias_name FROM table_name;
表的别名语法结构:
SELECT column_name(s) FROM table_name AS alias_name;
在使用 AS 后,可以用 alias_name 代替 table_name,其中 AS 语句是可选的。AS之后的别名,主要是为表内的列或者表提供临时的名称,在查询过程中使用,库内实际的表名或字段名是不会被改变的。
例如,在统计表内所有记录共有多少条时,使用 count(*), 这么写不便于识别,可以将其别名设置为 number。
mysql> select count(*) as number from player;
+--------+
| number |
+--------+
| 12 |
+--------+
mysql> select count(*) number from player; ###省略as是一样的结果
+--------+
| number |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)
如果表的长度比较长,可以使用AS给表设置别名,在查询的过程中直接使用别名。
例如,执行以下操作即可将 player 表的别名设置成 p。
mysql> select p.id,p.name from player as p limit 3;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 修欧拉卡 |
| 2 | 起风了 |
| 3 | 吊打低V |
+----+--------------+
3 rows in set (0.00 sec)
mysql> select p.id,p.name from player p limit 3; ###省略 as 是一样的结果
+----+--------------+
| id | name |
+----+--------------+
| 1 | 修欧拉卡 |
| 2 | 起风了 |
| 3 | 吊打低V |
+----+--------------+
3 rows in set (0.00 sec)
此外,AS还可以作为连接语句的操作符。例如,执行以下操作即可实现用一条 SQL
语句完成在创建表tmp的时候将player表内的数据写入 tmp 表。
mysql> create table tmp as select * from player;
Query OK, 12 rows affected (0.02 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
注意:在为表设置别名时,要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的,而表的别名在结果中没有显示,只在执行查询时使用。
1.1.6、通配符
通配符主要用于替换字符串中的部分字符,通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE 一起使用的,并协同 WHERE 子句共同来完成查询任务。
常用的通配符有两个,分别是:
- %:百分号表示零个、一个或多个字符
- _:下划线表示单个字符
例如,查询 player 表内 name 字段分别以 s 开头的名字、以 s 结尾的名字和名字中间包含 es 的字段,具体操作如下所示。
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 ('238','sagou 轰总',7);
insert into player (id,name,level) values ('795','senoku',15);
insert into player (id,name,level)<