数据库管理系统的一个最重要的功能就是数据查询,数据查询不应只是简单查询数据库中存储的数据,还应该根据需要对数据进行筛选,以及确定数据以什么样的格式显示.MySQL提供了功能强大、灵活的语句来实现这些操作,下面将介绍如何使用SELECT语句查询数据表中的一列或多列数据、使用集合函数显示查询结果.
查询语句简介
首先,MySQL从数据表中查询数据的基本语句为SELECT语句,SELECT语句的基本格式如下:
MariaDB [none]> SELECT [predicate]
{ * | table.* | [table.]field1 [AS alias1] [,[table.]field2 [AS alias2] [,...]]}
FROM tableexpression [,...] [IN externaldatabase]
[WHERE...]
[GROUP BY...]
[HAVING...]
[ORDER BY...]
[WITH OWNERACCESS OPTION]
SELECT语句的可选参数比较多,对于一般的运维工作,大路边上的就足够了,接下来我们将从最简单的查询语句开始一步步深入学习各种语句的灵活运用,在开始之前请创建以下表结构,方便后期的查询练习.
MariaDB [none]> create database lyshark;
MariaDB [lyshark]> use lyshark;
MariaDB [lyshark]> create table lyshark
-> (
-> Uid char(10) not null,
-> Gid int not null,
-> Name char(255) not null,
-> Price decimal(8,2) not null,
-> primary key(Uid)
-> );
Query OK, 0 rows affected (0.01 sec)
并在表结构中插入以下语句,也就是更新一下数据,下面的数据直接复制粘贴到数据库即可,无需一个个插入.
insert into lyshark(Uid,Gid,Name,Price) values('a1',101,'apple',5.2);
insert into lyshark(Uid,Gid,Name,Price) values('b1',101,'blackberry',10.2);
insert into lyshark(Uid,Gid,Name,Price) values('bs1',102,'orange',11.2);
insert into lyshark(Uid,Gid,Name,Price) values('bs2',105,'melon',8.2);
insert into lyshark(Uid,Gid,Name,Price) values('t1',102,'bannana',10.3);
insert into lyshark(Uid,Gid,Name,Price) values('t2',102,'grape',5.3);
insert into lyshark(Uid,Gid,Name,Price) values('o2',103,'coconut',9.2);
insert into lyshark(Uid,Gid,Name,Price) values('c0',101,'cherry',3.2);
insert into lyshark(Uid,Gid,Name,Price) values('a2',103,'apricot',2.2);
insert into lyshark(Uid,Gid,Name,Price) values('l2',104,'lemon',6.4);
insert into lyshark(Uid,Gid,Name,Price) values('b2',104,'berry',7.6);
insert into lyshark(Uid,Gid,Name,Price) values('m1',106,'mango',15.7);
insert into lyshark(Uid,Gid,Name,Price) values('m2',105,'xbabay',2.6);
insert into lyshark(Uid,Gid,Name,Price) values('t4',107,'xbababa',3.6);
insert into lyshark(Uid,Gid,Name,Price) values('m3',105,'xxtt',11.6);
insert into lyshark(Uid,Gid,Name,Price) values('b5',107,'xxxx',3.6);
insert into lyshark(Uid,Gid,Name,Price) values('lyshark',999,'lysharks',999);
MariaDB 常规查询
◆基本的字段查询◆
select 语句查询记录最简单的检索方式,实现的方法是使用(*)通配符,指定查找所有列的名称,,语法规则如下:
select * from 表名;
查询所有字段: 使用*通配符,查询lyshark表中的所有字段.
MariaDB [lyshark]> select * from lyshark;
+---------+-----+------------+--------+
| Uid | Gid | Name | Price |
+---------+-----+------------+--------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| lyshark | 999 | lysharks | 999.00 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | bannana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+---------+-----+------------+--------+
17 rows in set (0.00 sec)
查询指定字段: 使用Uid,Name来指定查询的字段.
MariaDB [lyshark]> select Uid,Name from lyshark;
+---------+------------+
| Uid | Name |
+---------+------------+
| a1 | apple |
| a2 | apricot |
| b1 | blackberry |
| b2 | berry |
| b5 | xxxx |
| bs1 | orange |
| bs2 | melon |
| c0 | cherry |
| l2 | lemon |
| lyshark | lysharks |
| m1 | mango |
| m2 | xbabay |
| m3 | xxtt |
| o2 | coconut |
| t1 | bannana |
| t2 | grape |
| t4 | xbababa |
+---------+------------+
17 rows in set (0.00 sec)
◆查询指定的记录◆
我们也可以根据自己的需求通过添加where过滤数据,遍历我们需要的指定数据,语法规则如下:
select 字段名1,字段名2....字段名n from 表名 where 查询条件
在我们的where子句中,MySQL支持条件判断符,支持如下内容:
| 操作符 | 说明信息 |
|---|---|
| = | 相等 |
| <>,!= | 不等于 |
| < | 小于 |
| <= | 小于或等于 |
| > | 大于 |
| >= | 大于或等于 |
| BETWEEN | 位于两值之间 |
实例1: 查询lyshark表中,价格正好为10.2元的水果的名称,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark where price=10.2;
+------------+-------+
| Name | Price |
+------------+-------+
| blackberry | 10.20 |
+------------+-------+
1 row in set (0.01 sec)
实例2: 查询lyshark表中,apple水果的价格,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark where Name='apple';
+-------+-------+
| Name | Price |
+-------+-------+
| apple | 5.20 |
+-------+-------+
1 row in set (0.00 sec)
实例3: 查询lyshark表中,价格小于10元的水果的名称列表,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark where Price <10;
+---------+-------+
| Name | Price |
+---------+-------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+---------+-------+
11 rows in set (0.00 sec)
◆带IN关键字查询◆
in关键字用来查询指定的范围,使用in操作符应将所有检索条件用括号括起来,in的语法规则如下:
select 字段名 from 表名称 where 字段名 in(关键字1,关键字2) order by 字段名(以那个字段作为排序方式);
实例1: 查询字段Gid为101,102的所有记录,并以Name字段排序,SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Gid in(101,102) order by Name;
+-----+------------+-------+
| Gid | Name | Price |
+-----+------------+-------+
| 101 | apple | 5.20 |
| 102 | bannana | 10.30 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 102 | grape | 5.30 |
| 102 | orange | 11.20 |
+-----+------------+-------+
6 rows in set (0.00 sec)
实例3: 查询字段Gid为101,103的所有记录,并以Price字段排序,SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Gid in(101,103) order by Price;
+-----+------------+-------+
| Gid | Name | Price |
+-----+------------+-------+
| 103 | apricot | 2.20 |
| 101 | cherry | 3.20 |
| 101 | apple | 5.20 |
| 103 | coconut | 9.20 |
| 101 | blackberry | 10.20 |
+-----+------------+-------+
5 rows in set (0.00 sec)
实例4: 查询字段Gid不等于101,103的所有记录,并以Price字段排序,SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Gid not in(101,103) order by Price;
+-----+----------+--------+
| Gid | Name | Price |
+-----+----------+--------+
| 105 | xbabay | 2.60 |
| 107 | xbababa | 3.60 |
| 107 | xxxx | 3.60 |
| 102 | grape | 5.30 |
| 104 | lemon | 6.40 |
| 104 | berry | 7.60 |
| 105 | melon | 8.20 |
| 102 | bannana | 10.30 |
| 102 | orange | 11.20 |
| 105 | xxtt | 11.60 |
| 106 | mango | 15.70 |
| 999 | lysharks | 999.00 |
+-----+----------+--------+
12 rows in set (0.00 sec)
◆带BETWEEN范围查询◆
Bettween And用于查询某个范围内的值,该关键字有两个参数,即范围的开始值和结束值,满足字段的条件才会返回字段,SQL语句如下:
select 字段名 from 表名称 where 字段名 between 范围1 and 范围2 ;
实例1: 查询价格在2.00元 到 10.20元之间的水果名称和价格,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark where Price between 2.00 and 10.20;
+------------+-------+
| Name | Price |
+------------+-------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+-------+
12 rows in set (0.00 sec)
实例2: 查询价格不在2.00元 到 10.20元之间的水果名称和价格,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark where Price not between 2.00 and 10.20;
+----------+--------+
| Name | Price |
+----------+--------+
| orange | 11.20 |
| lysharks | 999.00 |
| mango | 15.70 |
| xxtt | 11.60 |
| bannana | 10.30 |
+----------+--------+
5 rows in set (0.00 sec)
◆带LIKE的字符匹配查询◆
Like通配符可以匹配指定字段模糊查询,SQL语句中支持多种通配符,可以和Like一起使用的通配符有% 和_,语法规则如下:
select 字段名 from 表名称 where 字段名 Like 'b%';
%:匹配任意长度的字符,包括零字符: 查询Name字段中,所有以b字母开头的水果名称,SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Name like 'b%' order by Price;
+-----+------------+-------+
| Gid | Name | Price |
+-----+------------+-------+
| 104 | berry | 7.60 |
| 101 | blackberry | 10.20 |
| 102 | bannana | 10.30 |
+-----+------------+-------+
3 rows in set (0.01 sec)
%:匹配任意长度的字符,包括零字符: 查询Name字段中,包含所有g字母的水果(注意不是开头,只要Name字段包含g字母通通匹配),SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Name like '%g%' order by Price;
+-----+--------+-------+
| Gid | Name | Price |
+-----+--------+-------+
| 102 | grape | 5.30 |
| 102 | orange | 11.20 |
| 106 | mango | 15.70 |
+-----+--------+-------+
3 rows in set (0.00 sec)
%:匹配任意长度的字符,包括零字符: 查询Name字段中,以b开头,并且以y 结尾的水果的名称,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark where Name like 'b%y' order by Price;
+------------+-------+
| Name | Price |
+------------+-------+
| berry | 7.60 |
| blackberry | 10.20 |
+------------+-------+
2 rows in set (0.00 sec)
注释:通过以上查询结果,可以看出%用于匹配在指定位置的任意数目的字符.
下划线通配符_,一次只能匹配任意一个字符: 查询以字母y结尾,并且y 前面只有4个字母的记录<SQL语句如下:
MariaDB [lyshark]> select Gid,Name from lyshark where Name like '____y';
+-----+-------+
| Gid | Name |
+-----+-------+
| 104 | berry |
+-----+-------+
1 row in set (0.00 sec)
如上:下划线通配符,一次只能匹配任意单个字符,如果要匹配多个字符,则需要使用相同个数的下划线代替.
◆匹配查询一个空值◆
空值不同于0,也不同于空字符串,空值一般表示数据未知、不适用或将在以后添加数据，,在SELECT语句中使用IS NULL子句,可以查询某字段内容为空的记录,这里为了方便演示,需要再次创建一个数据表.
MariaDB [lyshark]> create table test
-> (
-> Uid int not null auto_increment,
-> Name char(50) not null,
-> Email char(255) null,
-> primary key(Uid)
-> );
Query OK, 0 rows affected (0.01 sec)
insert into test(Name,Email) values("admin","123123@");
insert into test(Name,Email) values("lyshark","1181506874@");
insert into test(Name) values("root");
实例1: 查询test表中,Email字段为空的记录,并显示出所有字段的值.
MariaDB [lyshark]> select * from test where Email is null;
+-----+------+-------+
| Uid | Name | Email |
+-----+------+-------+
| 3 | root | NULL |
+-----+------+-------+
1 row in set (0.00 sec)
实例2: 查询test表中,Email字段不为空的记录,并显示出所有字段的值.
MariaDB [lyshark]> select * from test where Email is not null;
+-----+---------+-------------------+
| Uid | Name | Email |
+-----+---------+-------------------+
| 1 | admin | 123123@ |
| 2 | lyshark | 1181506874@ |
+-----+---------+-------------------+
2 rows in set (0.00 sec)
◆带AND多条件查询◆
在使用Select语句时,可以增加查询的限制条件,这样可以使查询的结果更加精确,我们可以在where 子句中使用and操作符,来限定相应的查询条件,使用and连接多个查询条件时,不同表达式之间应该用and分割开来,语法如下:
select 表名称 from 字段名 where 字段名 = "xx" and 字段名 >= 10;
实例1: 在lyshark表中,查询Gid等于101,并且Price大于等于5的水果价格和名称,SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Gid='101' and Price >=5;
+-----+------------+-------+
| Gid | Name | Price |
+-----+------------+-------+
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
+-----+------------+-------+
2 rows in set (0.00 sec)
实例2: 在lyshark表中,查询Gid=101 或者 Gid=102,并且Price > 5 并且 Name='apple'的水果的价格和名称,SQL语句如下:
MariaDB [lyshark]> select Gid,Name,Price from lyshark where Gid in('101','102') and Price >= 5 and Name='apple';
+-----+-------+-------+
| Gid | Name | Price |
+-----+-------+-------+
| 101 | apple | 5.20 |
+-----+-------+-------+
1 row in set (0.00 sec)
◆带OR的多条件查询◆
在使用Select语句时,可以增加查询的限制条件,这样可以使查询的结果更加精确,我们可以在where 子句中使用or操作符,来限定相应的查询条件,通过or连接多个查询条件,不同表达式之间应该用or分割开来,语法规则如下:
select 表名称 from 字段名 where 字段名 = "xx" or 字段名 >= 10;
实例1: 在lyshark表中,查询Gid=101 或者 Gid=102的水果记录,SQL语句如下:
MariaDB [lyshark]> select * from lyshark where Gid=101 or Gid=102;
+-----+-----+------------+-------+
| Uid | Gid | Name | Price |
+-----+-----+------------+-------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | bannana | 10.30 |
| t2 | 102 | grape | 5.30 |
+-----+-----+------------+-------+
6 rows in set (0.00 sec)
实例2: 另一种查询方式,使用in语句查询,SQL语句如下:
MariaDB [lyshark]> select * from lyshark where Gid in(101,102);
+-----+-----+------------+-------+
| Uid | Gid | Name | Price |
+-----+-----+------------+-------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | bannana | 10.30 |
| t2 | 102 | grape | 5.30 |
+-----+-----+------------+-------+
6 rows in set (0.00 sec)
◆使查询结果不重复◆
有时候我们的查询结果有重复的选项,当我们不想看重复数据时可以使用distinct关键字,实现去重,SQL语法如下:
select distinct 指定不重复的字段 From 表名称;
实例:查询lyshark表中Gid字段的值,且Gid字段如果有重复的则不显示,SQL语句如下:
MariaDB [lyshark]> select distinct Gid from lyshark;
+------+
| Gid |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 9999 |
| 106 |
+------+
8 rows in set (0.00 sec)
◆LIMIT限制查询结果◆
在select返回所有匹配的行,有可能是表中所有的行,如仅需要返回第一行或者是前几行数据,那么使用limit关键字即可实现,先看一下SQL语法规则:
select * from 表名称 limit [位置偏移,]输出的行数;
#其中位置偏移是表示从哪一行开始显示，如果不指定默认从1开始
实例1: 显示lyshark表的前5行数据,SQL语句如下:
MariaDB [lyshark]> select * from lyshark limit 5;
+-----+-----+------------+-------+
| Uid | Gid | Name | Price |
+-----+-----+------------+-------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
+-----+-----+------------+-------+
5 rows in set (0.00 sec)
实例2: 显示lyshark表,从第5个记录的位置开始显示,行数长度为3的记录,SQL语句如下:
MariaDB [lyshark]> select * from lyshark limit 5,3; #从第5行开始获取,获取后面的三条数据
+-----+-----+--------+-------+
| Uid | Gid | Name | Price |
+-----+-----+--------+-------+
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
+-----+-----+--------+-------+
3 rows in set (0.00 sec)
MariaDB 查询排序
从前面的章节中,我们可以使用order by来进行排列的规划,下面具体说明:排序分为,单列排序,多列排序,指定排序方向,这几种模式,想来看一下它的SQL写法吧:
select * from 表名称order by 指定排序字段;
单列排序: 查询lyshark表中的所有内容,且以Gid字段为基准排序,SQL语句如下:
MariaDB [lyshark]> select * from lyshark order by Gid;
+---------+-----+------------+--------+
| Uid | Gid | Name | Price |
+---------+-----+------------+--------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
| t2 | 102 | grape | 5.30 |
| t1 | 102 | bannana | 10.30 |
| bs1 | 102 | orange | 11.20 |
| a2 | 103 | apricot | 2.20 |
| o2 | 103 | coconut | 9.20 |
| l2 | 104 | lemon | 6.40 |
| b2 | 104 | berry | 7.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| bs2 | 105 | melon | 8.20 |
| m1 | 106 | mango | 15.70 |
| b5 | 107 | xxxx | 3.60 |
| t4 | 107 | xbababa | 3.60 |
| lyshark | 999 | lysharks | 999.00 |
+---------+-----+------------+--------+
17 rows in set (0.01 sec)
多列排序: 查询lyshark表中的所有内容,先按照Gid排序,再按照Price排序,SQL语句如下:
MariaDB [lyshark]> select * from lyshark order by Gid,Price;
+---------+-----+------------+--------+
| Uid | Gid | Name | Price |
+---------+-----+------------+--------+
| c0 | 101 | cherry | 3.20 |
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| t2 | 102 | grape | 5.30 |
| t1 | 102 | bannana | 10.30 |
| bs1 | 102 | orange | 11.20 |
| a2 | 103 | apricot | 2.20 |
| o2 | 103 | coconut | 9.20 |
| l2 | 104 | lemon | 6.40 |
| b2 | 104 | berry | 7.60 |
| m2 | 105 | xbabay | 2.60 |
| bs2 | 105 | melon | 8.20 |
| m3 | 105 | xxtt | 11.60 |
| m1 | 106 | mango | 15.70 |
| b5 | 107 | xxxx | 3.60 |
| t4 | 107 | xbababa | 3.60 |
| lyshark | 999 | lysharks | 999.00 |
+---------+-----+------------+--------+
17 rows in set (0.00 sec)
上方排序的注意事项:
在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序
如果第一列数据中所有值都是唯一的,将不再对第二列进行排序
指定排序方向:
默认情况下,查询数据是按照字幕升序进行排序的(A-Z),一般而言降序时使用(desc),升序时使用(asc)
1.查询lyshark表中的Name和Price字段,对结果按Price降序方式排列,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark order by Price desc;
+------------+-----------+
| Name | Price |
+------------+-----------+
| lysharks | 999999.99 |
| mango | 15.70 |
| xxtt | 11.60 |
| orange | 11.20 |
| bannana | 10.30 |
| blackberry | 10.20 |
| coconut | 9.20 |
| melon | 8.20 |
| berry | 7.60 |
| lemon | 6.40 |
| grape | 5.30 |
| apple | 5.20 |
| xxxx | 3.60 |
| xbababa | 3.60 |
| cherry | 3.20 |
| xbabay | 2.60 |
| apricot | 2.20 |
+------------+-----------+
17 rows in set (0.00 sec)
2.查询lyshark表中的Name和Price字段,先按照Price降序排列,再按照Name字段升序排列,SQL语句如下:
MariaDB [lyshark]> select Name,Price from lyshark order by Price desc,Name asc;
+------------+-----------+
| Name | Price |
+------------+-----------+
| lysharks | 999999.99 |
| mango | 15.70 |
| xxtt | 11.60 |
| orange | 11.20 |
| bannana | 10.30 |
| blackberry | 10.20 |
| coconut | 9.20 |
| melon | 8.20 |
| berry | 7.60 |
| lemon | 6.40 |
| grape | 5.30 |
| apple | 5.20 |
| xbababa | 3.60 |
| xxxx | 3.60 |
| cherry | 3.20 |
| xbabay | 2.60 |
| apricot | 2.20 |
+------------+-----------+
17 rows in set (0.00 sec)
MariaDB 分组查询
分组查询,是对数据按照某个或多个字段进行分组,MySQL中使用group by关键字对数据进行分组,基本语法形式为:
[group by 字段] [having <条件表达式>]
#字段值为进行分组时所依据的列名称：having<条件表达式>指定满足表达式限定条件的结果将被显示
◆GROUP BY创建分组◆
GROUP BY关键字通常和集合函数一起使用,例如:MAXO、MINO、COUNTO、SUMO、AVGO.例如,要返回每个水果供应商提供的水果种类,这时就要在分组过程中用到COUNTO函数,把数据分为多个逻辑组,并对每个组进行集合计算.
实例1: 根据Gid对lyshark表中的数据进行分组,并显示出每个编号对应水果的个数,SQL语句如下:
MariaDB [lyshark]> select Gid,count(*) as Name from lyshark group by Gid;
+-----+------+
| Gid | Name |
+-----+------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
| 999 | 1 |
+-----+------+
8 rows in set (0.08 sec)
上面的查询结果,显示Gid表示供应商的ID Name字段使用COUNTO函数计算得出.GROUP BY子句按照Gid排序并对数据分组,可以看到ID为10l、102、105的供应商分别提供3种水果,ID为103、104、107的供应商分别提供2种水果,ID为106的供应商只提供1种水果.
实例2: 根据Gid对lyshark表中的数据进行分组,并显示出每个编号对应水果的具体信息,SQL语句如下:
如果要查看每个供应商提供的水果的种类的名称,该怎么办呢? MySQL中可以在GROUP BY子句中使用GROUP CONCATO函数,将每个分组中各个字段的值显示出来.
MariaDB [lyshark]> select Gid,group_concat(Name) as Name from lyshark group by Gid;
+-----+-------------------------+
| Gid | Name |
+-----+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | grape,bannana,orange |
| 103 | apricot,coconut |
| 104 | lemon,berry |
| 105 | xbabay,xxtt,melon |
| 106 | mango |
| 107 | xxxx,xbababa |
+-----+-------------------------+
7 rows in set (0.00 sec)
◆使用having过滤分组◆
group by可以与having一起限定显示记录所需满足的条件,只有满足条件的分组才会被显示.
实例: 根据Gid对lyshark表中的数据进行分组,并显示水果种类大于1的分组信息,SQL语句如下:
MariaDB [lyshark]> select Gid,group_concat(Name) as Name
-> from lyshark
-> group by Gid having count(Name) > 1;
+-----+-------------------------+
| Gid | Name |
+-----+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | grape,bannana,orange |
| 103 | apricot,coconut |
| 104 | lemon,berry |
| 105 | xbabay,xxtt,melon |
| 107 | xxxx,xbababa |
+-----+-------------------------+
6 rows in set (0.00 sec)
◆在Group by子句中使用with rollup◆
使用with rollup关键字之后,在所有查询的分组记录之后添加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量.
实例: 根据Gid对lyshark表中的数据进行分组,并显示记录数量,SQL语句如下:
MariaDB [lyshark]> select Gid,count(*) as Name
-> from lyshark
-> group by Gid with rollup;
+-----+------+
| Gid | Name |
+-----+------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
| NULL | 16 | #本行就是记录上面的统计总和
+-----+------+
8 rows in set (0.00 sec)
◆使用多字段分组◆
使用GROUP BY可以对多个字段进行分组,GROUP BY关键字后面跟需要分组的字段,MySQL根据多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,然后在第1个字段值相同的记录中,再根据第2个字段的值进行分组依次类推.
实例: 根据Gid和Name字段对lyshark表中的数据进行分组,SQL语句如下:
MariaDB [lyshark]> select * from lyshark group by Gid,Name;
+-----+-----+------------+-------+
| Uid | Gid | Name | Price |
+-----+-----+------------+-------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | bannana | 10.30 |
| t2 | 102 | grape | 5.30 |
| bs1 | 102 | orange | 11.20 |
| a2 | 103 | apricot | 2.20 |
| o2 | 103 | coconut | 9.20 |
| b2 | 104 | berry | 7.60 |
| l2 | 104 | lemon | 6.40 |
| bs2 | 105 | melon | 8.20 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| m1 | 106 | mango | 15.70 |
| t4 | 107 | xbababa | 3.60 |
| b5 | 107 | xxxx | 3.60 |
+-----+-----+------------+-------+
16 rows in set (0.00 sec)
从上面可以看到,查询记录先按照Gid进行分组,在对Name字段不同的取值进行分组.
◆group by和order by 一起使用◆
某些情况下,需要对分组进行排序,order by用来对查询的记录排序,如果和group by一起使用可以完成对分组的排序,为了演示效果,首先创建一个表结构,SQL语句如下:
MariaDB [lyshark]> create table orderitems
-> (
-> o_num int not null,
-> o_item int not null,
-> f_id char(10) not null,
-> quantity int not null,
-> item_price decimal(8,2) not null,
-> primary key(o_num,o_item)
-> );
Query OK, 0 rows affected (0.02 sec)
MariaDB [lyshark]> desc orderitems;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| o_num | int(11) | NO | PRI | NULL | |
| o_item | int(11) | NO | PRI | NULL | |
| f_id | char(10) | NO | | NULL | |
| quantity | int(11) | NO | | NULL | |
| item_price | decimal(8,2) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values(30001,1,'a1',10,5.2);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values(30001,2,'b2',3,7.6);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30001,3,'bs1',5,11.2);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30001,4,'bs2',15,9.2);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30002,1,'b3',2,20.0);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30003,1,'c0',100,10);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30004,1,'o2',50,2.50);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30005,1,'c0',5,10);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30005,2,'b1',10,8.99);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30005,3,'a2',10,2.2);
insert into orderitems(o_num,o_item,f_id,quantity,item_price) values (30005,4,'m1',5,14.99);
实例1: 查询订单价格大于100的订单号和总订单价格,SQL语句如下:
MariaDB [lyshark]> select o_num,sum(quantity * item_price) as orderTotal
-> from orderitems
-> group by o_num
-> having sum(quantity * item_price) >=100;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30001 | 268.80 |
| 30003 | 1000.00 |
| 30004 | 125.00 |
| 30005 | 236.85 |
+-------+------------+
4 rows in set (0.00 sec)
以上可以看到,返回结果中并没有按照大小顺序排列,接下来使用order by关键字实现自动排序.
MariaDB [lyshark]> select o_num,sum(quantity * item_price) as orderTotal
-> from orderitems
-> group by o_num
-> having sum(quantity * item_price) >=100
-> order by orderTotal;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30004 | 125.00 |
| 30005 | 236.85 |
| 30001 | 268.80 |
| 30003 | 1000.00 |
+-------+------------+
4 rows in set (0.00 sec)
MariaDB 聚合查询
有时候我们并不需要返回实际表中的数据,而只是对数据进行总结.MySQL提供一些查询功能,可以对获取的数据进行分析和报告,这些函数的功能有:计算数据表中记录行数的总数、计算某个字段列下数据的总和,以及计算表中某个字段下的最大值、最小值或者平均值,下面将依次演示说明.
◆构建练习环境◆
为了演示效果,首先创建一个表结构,SQL语句如下:
MariaDB [lyshark]> create table test1
-> (
-> o_num int not null,
-> o_item int not null,
-> f_id char(10) not null,
-> quantity int not null,
-> item_price decimal(8,2) not null,
-> primary key(o_num,o_item)
-> );
Query OK, 0 rows affected (0.02 sec)
MariaDB [lyshark]> desc test1;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| o_num | int(11) | NO | PRI | NULL | |
| o_item | int(11) | NO | PRI | NULL | |
| f_id | char(10) | NO | | NULL | |
| quantity | int(11) | NO | | NULL | |
| item_price | decimal(8,2) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)
然后插入一些测试数据,SQL语句如下:
insert into test1(o_num,o_item,f_id,quantity,item_price) values(30001,1,'a1',10,5.2);
insert into test1(o_num,o_item,f_id,quantity,item_price) values(30001,2,'b2',3,7.6);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30001,3,'bs1',5,11.2);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30001,4,'bs2',15,9.2);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30002,1,'b3',2,20.0);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30003,1,'c0',100,10);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30004,1,'o2',50,2.50);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30005,1,'c0',5,10);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30005,2,'b1',10,8.99);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30005,3,'a2',10,2.2);
insert into test1(o_num,o_item,f_id,quantity,item_price) values (30005,4,'m1',5,14.99);
◆COUNT()函数◆
简介：返回某列的行数
功能：此函数统计表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数
计算总行数:count(*) 查询test1表总行数,SQL语句如下:
MariaDB [lyshark]> select count(*) as temp from test1; #as temp ->表示定义总数的名称是temp
+------+
| temp |
+------+
| 11 |
+------+
1 row in set (0.00 sec)
计算指定字段总行数:count(字段名) 查询test1表中o_num字段总数,SQL语句如下:
MariaDB [lyshark]> select count(o_num) as temp from test1;
+------+
| temp |
+------+
| 11 |
+------+
1 row in set (0.00 sec) 注释：如果列中有NULL则不会被记录
综合起来: 查询test1表,group by关键字先以o_num进行分组,然后计算每个分组中的总记录条数,SQL语句如下：
MariaDB [lyshark]> select o_num,count(o_item) from test1 group by o_num;
+-------+---------------+
| o_num | count(o_item) |
+-------+---------------+
| 30001 | 4 |
| 30002 | 1 |
| 30003 | 1 |
| 30004 | 1 |
| 30005 | 4 |
+-------+---------------+
5 rows in set (0.00 sec)
◆SUM()函数◆
简介：返回某列的和
功能：此函数是一个求总和的函数,返回指定列值的总和
实例1: 在test1表中查询30005号订单,一共购买的水果总量,SQL语句如下:
MariaDB [lyshark]> desc test1;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| o_num | int(11) | NO | PRI | NULL | |
| o_item | int(11) | NO | PRI | NULL | |
| f_id | char(10) | NO | | NULL | |
| quantity | int(11) | NO | | NULL | |
| item_price | decimal(8,2) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
MariaDB [lyshark]> select sum(quantity) as temp
-> from test1
-> where o_num=30005;
+------+
| temp |
+------+
| 30 |
+------+
1 row in set (0.00 sec)
以上可看到,sum(quantity)函数返回订单中所有水果数量之和,where子句指定查询的订单号为30005.
实例2: 在test1表中,使用sum()函数,统计不同订单号中订购水果总量,SQL语句如下:
MariaDB [lyshark]> desc test1;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| o_num | int(11) | NO | PRI | NULL | |
| o_item | int(11) | NO | PRI | NULL | |
| f_id | char(10) | NO | | NULL | |
| quantity | int(11) | NO | | NULL | |
| item_price | decimal(8,2) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
MariaDB [lyshark]> select o_num,sum(quantity) as temp
-> from test1
-> group by o_num;
+-------+------+
| o_num | temp |
+-------+------+
| 30001 | 33 |
| 30002 | 2 |
| 30003 | 100 |
| 30004 | 50 |
| 30005 | 30 |
+-------+------+
5 rows in set (0.00 sec)
以上可知,group by按照订单号o_num进行了分组,sum()函数计算每个分组中订购水果的总量.
◆AVG()函数◆
简介：返回某列的平均值
功能：avg()函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值
实例1: 在test1表中,查询编号o_num为30005的,字段的平均值,SQL语句如下:
MariaDB [lyshark]> select * from test1;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 | 1 | a1 | 10 | 5.20 |
| 30001 | 2 | b2 | 3 | 7.60 |
| 30001 | 3 | bs1 | 5 | 11.20 |
| 30001 | 4 | bs2 | 15 | 9.20 |
| 30002 | 1 | b3 | 2 | 20.00 |
| 30003 | 1 | c0 | 100 | 10.00 |
| 30004 | 1 | o2 | 50 | 2.50 |
| 30005 | 1 | c0 | 5 | 10.00 |
| 30005 | 2 | b1 | 10 | 8.99 |
| 30005 | 3 | a2 | 10 | 2.20 |
| 30005 | 4 | m1 | 5 | 14.99 |
+-------+--------+------+----------+------------+
11 rows in set (0.00 sec)
MariaDB [lyshark]> select avg(item_price) as temp
-> from test1
-> where o_num=30005;
+----------+
| temp |
+----------+
| 9.045000 |
+----------+
1 row in set (0.00 sec)
实例2: 在test1表中,查询重复字段o_num的所有item_price字段的平均值,SQL语句如下:
MariaDB [lyshark]> select * from test1;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 | 1 | a1 | 10 | 5.20 |
| 30001 | 2 | b2 | 3 | 7.60 |
| 30001 | 3 | bs1 | 5 | 11.20 |
| 30001 | 4 | bs2 | 15 | 9.20 |
| 30002 | 1 | b3 | 2 | 20.00 |
| 30003 | 1 | c0 | 100 | 10.00 |
| 30004 | 1 | o2 | 50 | 2.50 |
| 30005 | 1 | c0 | 5 | 10.00 |
| 30005 | 2 | b1 | 10 | 8.99 |
| 30005 | 3 | a2 | 10 | 2.20 |
| 30005 | 4 | m1 | 5 | 14.99 |
+-------+--------+------+----------+------------+
11 rows in set (0.00 sec)
MariaDB [lyshark]> select o_num,avg(item_price)
-> from test1
-> group by o_num;
+-------+-----------------+
| o_num | avg(item_price) |
+-------+-----------------+
| 30001 | 8.300000 |
| 30002 | 20.000000 |
| 30003 | 10.000000 |
| 30004 | 2.500000 |
| 30005 | 9.045000 |
+-------+-----------------+
5 rows in set (0.00 sec)
◆MAX()函数◆
简介：返回某列的最大值
功能：max()返回指定列中的最大值
实例1: 在test1表中,查询item_price字段中最大的数据,SQL语句如下:
MariaDB [lyshark]> select * from test1;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 | 1 | a1 | 10 | 5.20 |
| 30001 | 2 | b2 | 3 | 7.60 |
| 30001 | 3 | bs1 | 5 | 11.20 |
| 30001 | 4 | bs2 | 15 | 9.20 |
| 30002 | 1 | b3 | 2 | 20.00 |
| 30003 | 1 | c0 | 100 | 10.00 |
| 30004 | 1 | o2 | 50 | 2.50 |
| 30005 | 1 | c0 | 5 | 10.00 |
| 30005 | 2 | b1 | 10 | 8.99 |
| 30005 | 3 | a2 | 10 | 2.20 |
| 30005 | 4 | m1 | 5 | 14.99 |
+-------+--------+------+----------+------------+
11 rows in set (0.00 sec)
MariaDB [lyshark]> select max(item_price) as max from test1;
+-------+
| max |
+-------+
| 20.00 |
+-------+
1 row in set (0.00 sec)
MariaDB [lyshark]> select o_num,max(item_price) as max from test1; #加上ID号，方便区分
+-------+-------+
| o_num | max |
+-------+-------+
| 30001 | 20.00 |
+-------+-------+
1 row in set (0.01 sec)
也可以对上面的查询结果进行排序,SQL语句如下:
MariaDB [lyshark]> select o_num,max(item_price) as max from test1 group by o_num;
+-------+-------+
| o_num | max |
+-------+-------+
| 30001 | 11.20 |
| 30002 | 20.00 |
| 30003 | 10.00 |
| 30004 | 2.50 |
| 30005 | 14.99 |
+-------+-------+
5 rows in set (0.00 sec)
◆MIN()函数◆
简介：返回某列的最小值
功能：min()函数返回查询列中的最小值
实例1: 在test1表中,查询item_price字段,最小的值,SQL语句如下:
MariaDB [lyshark]> select * from test1;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 | 1 | a1 | 10 | 5.20 |
| 30001 | 2 | b2 | 3 | 7.60 |
| 30001 | 3 | bs1 | 5 | 11.20 |
| 30001 | 4 | bs2 | 15 | 9.20 |
| 30002 | 1 | b3 | 2 | 20.00 |
| 30003 | 1 | c0 | 100 | 10.00 |
| 30004 | 1 | o2 | 50 | 2.50 |
| 30005 | 1 | c0 | 5 | 10.00 |
| 30005 | 2 | b1 | 10 | 8.99 |
| 30005 | 3 | a2 | 10 | 2.20 |
| 30005 | 4 | m1 | 5 | 14.99 |
+-------+--------+------+----------+------------+
11 rows in set (0.00 sec)
MariaDB [lyshark]> select min(item_price) as min from test1;
+------+
| min |
+------+
| 2.20 |
+------+
1 row in set (0.00 sec)
也可以对上面的结果排序输出,SQL语句如下:
MariaDB [lyshark]> select o_num,min(item_price) as min from lyshark group by item_price;
+-------+-------+
| o_num | min |
+-------+-------+
| 30005 | 2.20 |
| 30004 | 2.50 |
| 30001 | 5.20 |
| 30001 | 7.60 |
| 30005 | 8.99 |
| 30001 | 9.20 |
| 30003 | 10.00 |
| 30001 | 11.20 |
| 30005 | 14.99 |
| 30002 | 20.00 |
+-------+-------+
10 rows in set (0.00 sec)
