一、常用查询
对 MySQL 数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理。 例如只取 10 条数据、对查询结果进行排序或分组等等。
1. 按关键字排序
使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来,如果对查询的结果进行排序,可以使用 ORDER BY 语句来对语句实现排序,并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段,也可以针对多个字段。
(1) 语法
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ...
1
ASC | DESC:
ASC 是按照升序进行排序的,是默认的排序方式,即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式,则默认按 ASC 方式进行排序。
DESC 是按降序方式进行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
12
(2) 创建模板表
数据库有一张 info 表,记录了学生的 id,姓名,分数,地址和爱好
create table info (id int(10) primary key not null auto_increment,name varchar(20),score decimal(5,2),address varchar(40),hobby varchar(20));
insert into info values(1,'liuyi',80,'beijing',2);
insert into info values(2,'wangwu',90,'shengzheng',2);
insert into info values(3,'lisi',60,'shanghai',4);
insert into info values(4,'tianqi',99,'hangzhou',5);
insert into info values(5,'jiaoshou',98,'laowo',3);
insert into info values(6,'hanmeimei',10,'nanjing',3);
insert into info values(7,'lilei',11,'nanjing',5);
insert into info values(8,'caicai',16,'nanjing',5);
12345678910
(3) 单字段排序
按分数排序,默认不指定是升序排列 ASC
mysql> select id,name,score from info order by score;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 6 | hanmeimei | 10.00 |
| 7 | lilei | 11.00 |
| 8 | caicai | 16.00 |
| 3 | lisi | 60.00 |
| 1 | liuyi | 80.00 |
| 2 | wangwu | 90.00 |
| 5 | jiaoshou | 98.00 |
| 4 | tianqi | 99.00 |
+----+-----------+-------+
8 rows in set (0.01 sec)
1234567891011121314
分数按降序排列,使用 DESC
mysql> select id,name,score from info order by score desc;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 4 | tianqi | 99.00 |
| 5 | jiaoshou | 98.00 |
| 2 | wangwu | 90.00 |
| 1 | liuyi | 80.00 |
| 3 | lisi | 60.00 |
| 8 | caicai | 16.00 |
| 7 | lilei | 11.00 |
| 6 | hanmeimei | 10.00 |
+----+-----------+-------+
8 rows in set (0.01 sec)
1234567891011121314
(4) 条件查询
order by 还可以结合 where 进行条件过滤,筛选地址是杭州的学生按分数降序排列
mysql> select name,score from info where address='hangzhou' order by score desc;
+--------+-------+
| name | score |
+--------+-------+
| tianqi | 99.00 |
+--------+-------+
1 row in set (0.00 sec)
1234567
(5) 多字段排序
order by 之后的参数,使用 “,” 分割,优先级是按先后顺序而定
mysql> select id,name,hobby from info order by hobby desc,id asc;
+----+-----------+-------+
| id | name | hobby |
+----+-----------+-------+
| 4 | tianqi | 5 |
| 7 | lilei | 5 |
| 8 | caicai | 5 |
| 3 | lisi | 4 |
| 5 | jiaoshou | 3 |
| 6 | hanmeimei | 3 |
| 1 | liuyi | 2 |
| 2 | wangwu | 2 |
+----+-----------+-------+
8 rows in set (0.00 sec)
1234567891011121314
order by 之后的第一个参数只有在出现相同的数值,第二个字段才有意义
mysql> select id,hobby from info order by id asc,hobby desc;
+----+-------+
| id | hobby |
+----+-------+
| 1 | 2 |
| 2 | 2 |
| 3 | 4 |
| 4 | 5 |
| 5 | 3 |
| 6 | 3 |
| 7 | 5 |
| 8 | 5 |
+----+-------+
8 rows in set (0.00 sec)
1234567891011121314
(6) 区间判断及查询不重复记录
AND / OR —— 且 / 或
mysql> select * from info where score > 10 and score <= 90;
+----+--------+-------+------------+-------+
| id | name | score | address | hobby |
+----+--------+-------+------------+-------+
| 1 | liuyi | 80.00 | beijing | 2 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 8 | caicai | 16.00 | nanjing | 5 |
+----+--------+-------+------------+-------+
5 rows in set (0.00 sec)
mysql> select * from info where score > 70 or score <= 90;
+----+-----------+-------+------------+-------+
| id | name | score | address | hobby |
+----+-----------+-------+------------+-------+
| 1 | liuyi | 80.00 | beijing | 2 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 8 | caicai | 16.00 | nanjing | 5 |
+----+-----------+-------+------------+-------+
8 rows in set (0.00 sec)
1234567891011121314151617181920212223242526
嵌套 / 多条件
mysql> select * from info where score > 70 or (score > 50 and score <= 80);
+----+----------+-------+------------+-------+
| id | name | score | address | hobby |
+----+----------+-------+------------+-------+
| 1 | liuyi | 80.00 | beijing | 2 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
+----+----------+-------+------------+-------+
5 rows in set (0.00 sec)
1234567891011
(7) distinct 查询不重复记录
语法:
select distinct 字段 from 表名﹔
1
示例:
mysql> select distinct hobby from info;
+-------+
| hobby |
+-------+
| 2 |
| 4 |
| 5 |
| 3 |
+-------+
4 rows in set (0.00 sec)
12345678910
PS:以下语句是否可以筛重
select name,hobby from info where hobby in (select distinct hobby from info);
1
示例:
mysql> select name,hobby from info where hobby in (select distinct hobby from info);
+-----------+-------+
| name | hobby |
+-----------+-------+
| liuyi | 2 |
| wangwu | 2 |
| lisi | 4 |
| tianqi | 5 |
| jiaoshou | 3 |
| hanmeimei | 3 |
| lilei | 5 |
| caicai | 5 |
+-----------+-------+
8 rows in set (0.00 sec)
1234567891011121314
distinct 必须放在最开头。
distinct 只能使用需要去重的字段进行操作 ---- 也就是说我 distinct 了 name,hobby 两个字段,我后面想根据 id 进行排序,是不可以的,因为只能 name,hobby 两个字段进行操作。
distinct 去重多个字段时,几个字段同时重复时才会被过滤。
2. 对结果进行分组
通过 SQL 查询出来的结果,还可以对其进行分组,使用 GROUP BY 语句来实现,GROUP BY 通常都是结合聚合函数一起使用的,常用的聚合函数包括:
计数(COUNT)
求和(SUM)
求平均数(AVG)
最大值(MAX)
最小值(MIN)
12345
GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。
语法
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator valueGROUP BY column_name;
selet字段,聚合函数from表名,(where字段名(匹配)数值)group by字段名;
12
对 info 进行分组,筛选范围/条件是 score 大于等于 45 的 'name',score 相同的会默认分在一个组
mysql> select count(name),score from info where score >= 45 group by score;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 1 | 60.00 |
| 1 | 80.00 |
| 1 | 90.00 |
| 1 | 98.00 |
| 1 | 99.00 |
+-------------+-------+
5 rows in set (0.00 sec)
1234567891011
分组排序
对 info 表中 hobby 相同的 id 进行数量统讦,并按照相同 hobby 进行分组
mysql> select count(id),hobby from info group by hobby;
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
| 2 | 2 |
| 2 | 3 |
| 1 | 4 |
| 3 | 5 |
+-----------+-------+
4 rows in set (0.00 sec)
12345678910
基于上一条操作,结合 order by 把统计的 id 数量进行降序排列
mysql> select count(id),hobby from info group by hobby order by count(id) desc;
+-----------+-------+
| count(id) | hobby |
+-----------+-------+
| 3 | 5 |
| 2 | 2 |
| 2 | 3 |
| 1 | 4 |
+-----------+-------+
4 rows in set (0.00 sec)
12345678910
分组条件
结合 where 语句,筛选分数大于等于 80 的分组,计算学生个数按降序排列
mysql> select count(name),score from info where score >= 80 group by hobby order by count(name) desc;
+-------------+-------+
| count(name) | score |
+-------------+-------+
| 2 | 80.00 |
| 1 | 99.00 |
| 1 | 98.00 |
+-------------+-------+
3 rows in set (0.00 sec)
123456789
3. 限制结果条目(limit)
limit 限制输出的结果记录,在使用 MySQL SELECT 语句进行查询时,结果集返回的是所有匹配的记录(行)。有时候仅需要返回第一行或者前几行,这时候就需要用到 LIMIT 子句。
语法
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
1
LIMIT 的第一个参数是位置偏移量(可选参数),是设置 MySQL 从哪一行开始显示。 如果不设定第一个参数,将会从表中的第一条记录开始显示。需要注意的是,第一条记录的位置偏移量是 0,第二条是 1,以此类推。第二个参数是设置返回记录行的最大数目。
查询所有信息显示前 4 行记录