查询基础
基本查询语句
MySQL 从数据表中查询数据的基本语句为 SELECT 语句。SELECT 语句的基本格式是:
1
2
3
4
5
6
7
8
9
10
11
12
13 | SELECT
{* | <字段列表>}
[
FROM <表1>,<表2>...
[WHERE <表达式>]
[GROUP BY <group by definition>]
[HAVING <expression> [{<operator> <expression>}]...]
[ORDER BY <order by definition>]
[LIMIT [<offset,>] <row count>]
]
SELECT [字段1,字段2,...,字段n]
FROM [表或视图]
WHERE [查询条件];
|
其中，各条子句的含义如下:
{* | <字段列表>} 包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号
FROM <表1>,<表2>... 表 1 和表 2 表示查询数据的来源，可以是单个或者多个
WHERE 子句是可选项，如果选择该项，将限定查询行必须满足查询条件
GROUP BY <字段>，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组
[ORDER BY <字段>], 该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序(ASC)、降序(DESC)
[LIMIT [<offset>,] <row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数
下面以一个例子说明如何使用 SELECT 从单个表中获取数据。
首先定义数据表，输入语句如下:
| CREATE TABLE fruits (
f_id char(10) NOT NULL,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(8,2) NOT NULL,
PRIMARY KEY(f_id)
);
|
为了演示如何使用 SELECT 语句，需要插入如下数据:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 | INSERT INTO fruits (f_id,s_id,f_name,f_price)
VALUES
('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',2.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.7),
('m2',105,'xbaby',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxxx',3.6);
|
使用 SELECT 语句查询 f_id 字段的数据:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | SELECT f_id, f_name FROM fruits;
+------+------------+
| f_id | f_name |
+------+------------+
| a1 | apple |
| a2 | apricot |
| b1 | blackberry |
| b2 | berry |
| b5 | xxxx |
| bs1 | orange |
| bs2 | melon |
| c0 | cherry |
| l2 | lemon |
| m1 | mango |
| m2 | xbaby |
| m3 | xxtt |
| o2 | coconut |
| t1 | banana |
| t2 | grape |
| t4 | xbababa |
+------+------------+
16 rows in set (0.00 sec)
|
该语句的执行过程是，SELECT 语句决定了要查询的列值，在这里查询 f_id 和 f_name 两个字段的值，FROM 子句指定了数据的来源，这里指定数据表 fruits，因此返回结果为 fuits 表中 f_id 和 f_name 两个字段下所有的数据。
其显示顺序为添加到表中的顺序
单表查询
单表查询是指从一张表数据中查询所需的数据。
查询所有字段
在 SELECT 语句中使用星号(*)通配符查询所有字段
SELECT 查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号(*)通配符指定查找所有列的名称。语法格式如下:
在 SELECT 语句中指定所有字段:
SELECT 关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在 SELECT 子句后面，如果忘记了字段名称，可以使用 DESC 命令查看表的结构。
有时候，表中的字段可能比较多，不一定能记得所有字段的名称，因此该方法会很不方便，不建议使用。
例如，查询 fruits 表中的所有数据，SQL 语句也可以书写如下:
| SELECT f_id, s_id, f_name, f_price FROM fruits;
|
提示:
一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符 *。
使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。
通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
查询指定字段
查询单个字段:
查询表中的某一个字段，语法格式为:
查询多个字段:
使用 SELECT 声明，可以获取多个字段下的数据，只需要在关键字 SELECT 后面指定要查找的字段的名称，不同字段名称之间用逗号分隔开，最后一个字段后面不需要加逗号，语法格式如下:
| SELECT 字段名1,字段名2,...,字段名n FROM 表名;
|
提示:
MySQL 中的 SQL 语句是不区分大小写的，因此 SELECT 和 select 的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，我们应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护
查询指定记录
数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。
在 SELECT 语句中，通过 WHERE 子句可以对数据进行过滤，语法格式为:
| SELECT 字段名1,字段名2,...,字段名n
FROM 表名
WHERE 查询条件;
|
例: 查询价格为 10.2 的水果的名称:
| SELECT f_name, f_price
FROM fruits
WHERE f_price = 10.2;
+------------+---------+
| f_name | f_price |
+------------+---------+
| blackberry | 10.20 |
+------------+---------+
1 row in set (0.04 sec)
|
例: 查找名称为 "apple" 的水果的价格:
| SELECT f_name, f_price
FROM fruits
WHERE f_name = 'apple';
+--------+---------+
| f_name | f_price |
+--------+---------+
| apple | 5.20 |
+--------+---------+
1 row in set (0.00 sec)
|
例: 查询价格小于 10 的水果的名称:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | SELECT f_name, f_price
FROM fruits
WHERE f_price < 10;
+---------+---------+
| f_name | f_price |
+---------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbaby | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+---------+---------+
11 rows in set (0.01 sec)
|
带 IN 关键字的查询
IN 操作符用来查询满足指定范围内的条件的记录，使用 IN 操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件内的一个值即为匹配项。
例: 查询 s_id 为 101 和 102 的记录:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN (101,102)
ORDER BY f_name;
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 102 | banana | 10.30 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 102 | grape | 5.30 |
| 102 | orange | 11.20 |
+------+------------+---------+
6 rows in set (0.02 sec)
|
例: 查询所有不等于 101 也不等于 102 的记录:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id NOT IN (101,102)
ORDER BY f_name;
+------+---------+---------+
| s_id | f_name | f_price |
+------+---------+---------+
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 103 | coconut | 9.20 |
| 104 | lemon | 6.40 |
| 106 | mango | 15.70 |
| 105 | melon | 8.20 |
| 107 | xbababa | 3.60 |
| 105 | xbaby | 2.60 |
| 105 | xxtt | 11.60 |
| 107 | xxxx | 3.60 |
+------+---------+---------+
10 rows in set (0.00 sec)
|
带 BETWEEN AND 的范围查询
BETWEEN AND 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回
例: 查询价格在 2.00 元到 10.20 元之间的水果名称和价格:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 | SELECT f_name, f_price
FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;
+------------+---------+
| f_name | f_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 |
| xbaby | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
12 rows in set (0.02 sec)
|
BETWEEN AND 操作符前可以加关键字 NOT，表示指定范围之外的值，如果字段值不满足指定的范围内的值，则这些记录被返回
例: 查询价格在 2.00 元到 10.20 元之外的水果名称和价格
1
2
3
4
5
6
7
8
9
10
11
12
13 | SELECT f_name, f_price
FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;
+--------+---------+
| f_name | f_price |
+--------+---------+
| orange | 11.20 |
| mango | 15.70 |
| xxtt | 11.60 |
| banana | 10.30 |
+--------+---------+
4 rows in set (0.00 sec)
|
带 LIKE 的字符匹配查询
在前面的检索操作中讲述了如何查询多个字段的记录，如何进行比较查询或者是查询一个条件范围内的记录，如果要查找所有包含字符 "ge" 的水果名称，该如何查找呢？
简单的比较操作在这里已经行不通了，需要使用通配符进行匹配查找，通过创建查找模式对表中的数据进行比较。
执行这个任务的关键字是 LIKE
通配符是一种在 SQL 的 WHERE 条件子句中拥有特殊意思的字符。
SQL 语句中支持多种通配符，可以和 LIKE 一起使用的通配符有 % 和 _
百分号通配符 %，匹配任意长度的字符，甚至包括零字符
例: 查找所有以 b 字母开头的水果:
1
2
3
4
5
6
7
8
9
10
11
12 | SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%';
+------+------------+
| f_id | f_name |
+------+------------+
| b1 | blackberry |
| b2 | berry |
| t1 | banana |
+------+------------+
3 rows in set (0.01 sec)
|
例: 在 fruits 表中，查询 f_name 中包含字母 g 的记录
1
2
3
4
5
6
7
8
9
10
11
12 | SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '%g%';
+------+--------+
| f_id | f_name |
+------+--------+
| bs1 | orange |
| m1 | mango |
| t2 | grape |
+------+--------+
3 rows in set (0.00 sec)
|
例: 查询以 b 开头并以 y 结尾的水果的名称
| SELECT f_name
FROM fruits
WHERE f_name LIKE 'b%y';
+------------+
| f_name |
+------------+
| blackberry |
| berry |
+------------+
2 rows in set (0.00 sec)
|
下划线通配符 _，一次只能匹配任意一个字符
该通配符的用法和 % 相同，区别是 % 可以匹配多个字符，而 _ 只能匹配任意单个字符
例: 在 fruits 表中，查询以字母 y 结尾，且 y 前面只有 4 个字母的记录:
| SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '____y';
+------+--------+
| f_id | f_name |
+------+--------+
| b2 | berry |
| m2 | xbaby |
+------+--------+
2 rows in set (0.00 sec)
|
查询空值
数据表创建的时候，设计者可以指定某列中是否包含空值(NULL)。空值不同于 0，也不同于空字符串。
空值一般表示数据未知、不适用或将在以后添加数据。
在 SELECT 语句中适用 IS NULL 子句，可以查询某字段内容为空记录
创建数据表 customers:
| CREATE TABLE customers (
c_id INT NOT NULL AUTO_INCREMENT,
c_name CHAR(50) NOT NULL,
c_address CHAR(50) NULL,
c_city CHAR(50) NULL,
c_zip CHAR(10) NULL,
c_contact CHAR(50) NULL,
c_email CHAR(255) NULL,
PRIMARY KEY (c_id)
);
|
为了演示，插入数据:
| INSERT INTO customers (c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)
VALUES
(10001,'RedHook','200 Street ','Tiianjin','300000','LiMing','LMing@163.com'),
(10002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotmail.com'),
(10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',NULL),
(10004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');
|
例: 查询 customers 表中 c_email 为空的记录的 c_id、c_name 和 c_email 字段值
| SELECT c_id, c_name, c_email
FROM customers
WHERE c_email IS NULL;
+-------+----------+---------+
| c_id | c_name | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL |
+-------+----------+---------+
1 row in set (0.00 sec)
|
例: 查询 customers 表中 c_email 不为空的记录的 c_id、c_name 和 c_email 字段值
1
2
3
4
5
6
7
8
9
10
11
12 | SELECT c_id, c_name, c_email
FROM customers
WHERE c_email IS NOT NULL;
+-------+---------+-------------------+
| c_id | c_name | c_email |
+-------+---------+-------------------+
| 10001 | RedHook | LMing@163.com |
| 10002 | Stars | Jerry@hotmail.com |
| 10004 | JOTO | sam@hotmail.com |
+-------+---------+-------------------+
3 rows in set (0.00 sec)
|
带 AND 的多条件查询
使用 SELECT 查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。
MySQL 在 WHERE 子句中使用 AND 操作符限定只有满足所有查询条件的记录才会被返回。
可以使用 AND 连接两个甚至多个查询条件，多个条件表达式之间用 AND 分开。
例: 在 fruits 表中查询 s_id = 101 并且 f_price 大于等于 5 的水果 id、价格和名称:
| SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id = '101' AND f_price >= 5;
+------+---------+------------+
| f_id | f_price | f_name |
+------+---------+------------+
| a1 | 5.20 | apple |
| b1 | 10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.01 sec)
|
例: 在 fruits 表中查询 s_id = 101 或者 102，并且 f_price 大于等于 5、f_name = 'apple' 的水果价格和名称:
| SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id IN ('101','102') AND f_price >= 5 AND f_name = 'apple';
+------+---------+--------+
| f_id | f_price | f_name |
+------+---------+--------+
| a1 | 5.20 | apple |
+------+---------+--------+
1 row in set (0.02 sec)
|
带 OR 的多条件查询
与 AND 相反，在 WHERE 声明中使用 OR 操作符，表示只需要满足其中一个条件的记录即可返回。
OR 也可以连接两个甚至多个查询条件，多个条件表达式之间用 OR 分开。
例: 查询 s_id=101 或者 s_id = 102 的水果供应商的 f_price 和 f_name:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 | SELECT s_id,f_name,f_price
FROM fruits
WHERE s_id = 101 OR s_id = 102;
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 101 | cherry | 3.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
+------+------------+---------+
6 rows in set (0.00 sec)
|
OR 操作符和前面使用 IN 操作符结果是一样的，它们可以实现相同的功能，
但是使用 IN 操作符使得检索语句更加简洁明了，并且 IN 执行的速度要快于 OR。
更重要的是，使用 IN 操作符可以执行更加复杂的嵌套查询
查询结果不重复
在 SELECT 语句中，可以使用 DISTINCT 关键字指示 MySQL 消除重复的记录值
语法格式:
| SELECT DISTINCT 字段名 FROM 表名;
|
例: 查询 fruits 表中 s_id 字段值，返回 s_id 字段值且不得重复：
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | SELECT DISTINCT s_id FROM fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
7 rows in set (0.00 sec)
|
对查询结果排序
单列排序:
例如，查询 f_name 字段，并对其进行排序:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | SELECT f_name
FROM fruits
ORDER BY f_name;
+------------+
| f_name |
+------------+
| apple |
| apricot |
| banana |
| berry |
| blackberry |
| cherry |
| coconut |
| grape |
| lemon |
| mango |
| melon |
| orange |
| xbababa |
| xbaby |
| xxtt |
| xxxx |
+------------+
16 rows in set (0.04 sec)
|
多列排序
有时，需要根据多列值进行排序。
比如，如果要显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。
对多列数据进行排序，要将需要排序的列之间用逗号隔开。
例: 查询 fruits 表中的 f_name 和 f_price 字段，先按 f_name 排序，再按 f_price 排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | SELECT f_name, f_price
FROM fruits
ORDER BY f_name, f_price;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.70 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbaby | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
16 rows in set (0.01 sec)
|
指定排序方向:
可以通过关键字 DESC 实现
例: 查询 fruits 表中的 f_name 和 f_price 字段，对结果按 f_price 降序方式排序:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | SELECT f_name, f_price
FROM fruits
ORDER BY f_price DESC;
+------------+---------+
| f_name | f_price |
+------------+---------+
| mango | 15.70 |
| xxtt | 11.60 |
| orange | 11.20 |
| banana | 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 |
| xbaby | 2.60 |
| apricot | 2.20 |
+------------+---------+
16 rows in set (0.01 sec)
|
例: 查询 fruits 表，先按 f_price 降序排列，f_price 相同再按 f_name 升序排列:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | SELECT f_price, f_name
FROM fruits
ORDER BY f_price DESC, f_name;
+---------+------------+
| f_price | f_name |
+---------+------------+
| 15.70 | mango |
| 11.60 | xxtt |
| 11.20 | orange |
| 10.30 | banana |
| 10.20 | blackberry |
| 9.20 | coconut |
| 8.20 | melon |
| 7.60 | berry |
| 6.40 | lemon |
| 5.30 | grape |
| 5.20 | apple |
| 3.60 | xbababa |
| 3.60 | xxxx |
| 3.20 | cherry |
| 2.60 | xbaby |
| 2.20 | apricot |
+---------+------------+
16 rows in set (0.01 sec)
|
分组查询
分组查询是对数据按照某个或多个字段进行分组。
MySQL 中使用 GROUP BY 关键字对数据进行分组，基本语法形式为:
| [GROUP BY 字段] [HAVING <条件表达式>]
|
字段值为进行分组时所依据的列名称；HAVING <条件表达式 指定满足表达式限定条件的结果将被显示
创建分组
GROUP BY 关键字通常和集合函数一起使用，比如 MAX()、MIN()、COUNT()、SUM()、AVG()。
例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到 COUNT() 函数，把数据分为多个逻辑组，并对每个组进行集合计算
例: 根据 s_id 对 fruits 表中的数据进行分组:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id;
+------+-------+
| s_id | Total |
+------+-------+
| 101 | 3 |
| 103 | 2 |
| 104 | 2 |
| 107 | 2 |
| 102 | 3 |
| 105 | 3 |
| 106 | 1 |
+------+-------+
7 rows in set (0.03 sec)
|
如果要查看每个供应商提供的水果的种类名称，该怎么办呢？
在 MySQL 中，可以在 GROUP BY 子句中使用 GROUP_CONCAT() 函数，将每个分组中各个字段的值显示出来:
例: 根据 s_id 对 fruits 表中的数据进行分组，将每个供应商的水果名称显示出来:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id;
+------+-------------------------+
| s_id | Names |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbaby,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
+------+-------------------------+
7 rows in set (0.03 sec)
|
使用 HAVING 过滤分组
GROUP BY 可以和 HAVING 一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示:
例: 根据 s_id 对 fruits 表中的数据进行分组，并显示水果种类大于 1 的分组信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 | SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id HAVING COUNT(f_name) > 1;
+------+-------------------------+
| s_id | Names |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbaby,xxtt |
| 107 | xxxx,xbababa |
+------+-------------------------+
6 rows in set (0.00 sec)
|
提示:
HAVING 关键字与 WHERE 关键字都是用来过滤数据的，两者有什么区别呢？
其中重要的一点是，HAVING 在数据分组之后进行过滤来选择分组，而 WHERE 在分组之前来选择记录。
另外，WHERE 排除的记录不再包括在分组中。
在 GROUP BY 子句中使用 WITH ROLLUP
使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
例: 根据 s_id 对 fruits 表中的数据进行分组，并显示记录数量:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id WITH ROLLUP;
+------+-------+
| s_id | Total |
+------+-------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
| NULL | 16 |
+------+-------+
8 rows in set (0.03 sec)
|
多字段分组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48 | SELECT GROUP_CONCAT(f_id) AS f_id,s_id,f_name,GROUP_CONCAT(f_price) AS f_price
FROM fruits
GROUP BY s_id,f_name;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 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 | xbaby | 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)
mysql> SELECT s_id,f_name,COUNT(f_price) AS cnt FROM fruits GROUP BY s_id,f_name;
+------+------------+-----+
| s_id | f_name | cnt |
+------+------------+-----+
| 101 | apple | 1 |
| 103 | apricot | 1 |
| 101 | blackberry | 1 |
| 104 | berry | 1 |
| 107 | xxxx | 1 |
| 102 | orange | 1 |
| 105 | melon | 1 |
| 101 | cherry | 1 |
| 104 | lemon | 1 |
| 106 | mango | 1 |
| 105 | xbaby | 1 |
| 105 | xxtt | 1 |
| 103 | coconut | 1 |
| 102 | banana | 1 |
| 102 | grape | 1 |
| 107 | xbababa | 1 |
+------+------------+-----+
16 rows in set (0.00 sec)
|
GROUP BY 和 ORDER BY 一起使用
创建数据表:
| 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)
);
|
插入数据:
1
2
3
4
5
6
7
8
9
10
11
12
13 | INSERT INTO orderitems(o_num, o_item,f_id,quantity,item_price)
VALUES
(30001,1,'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);
|
例: 查询订单价格大于 100 的订单号和总订单价格:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | 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.03 sec)
|
使用 ORDER BY 关键字按总订单价格排序:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 | SELECT o_num, SUM(quantity * item_price) AS orderTotal
FROM orderitems
GROUP BY o_num
HAVING orderTotal >= 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)
|
提示:
当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 时互相排斥的
使用 LIMIT 限制查询结果的数量
SELECT 返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回第一行或者前几行，可使用 LIMIT 关键字，基本语法格式如下:
第一个 "位置偏移量" 参数指示 MySQL 从哪一行开始显示，是一个可选参数，如果不指定 "位置偏移量"，将会从表中的第一条记录开始(第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推)；
第二个参数 “行数” 指示返回的记录条数
例: 显示 fruits 表查询结果的前 4 行:
1
2
3
4
5
6
7
8
9
10
11
12
13 | SELECT *
FROM fruits
LIMIT 4;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
+------+------+------------+---------+
4 rows in set (0.01 sec)
|
例: 在 fruits 表中，使用 LIMIT 子句，返回从第 5 个记录开始的行数长度为 3 的记录:
1
2
3
4
5
6
7
8
9
10
11
12 | SELECT *
FROM fruits
LIMIT 4, 3;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
+------+------+--------+---------+
3 rows in set (0.00 sec)
|
提示:
MySQL 8.0 中可以使用 LIMIT 4 OFFSET 3，和 LIMIT 4,3 返回的结果相同
使用集合函数查询
COUNT() 函数
例: 查询 customers 表中总的行数:
| SELECT COUNT(*) AS cust_num
FROM customers;
+----------+
| cust_num |
+----------+
| 4 |
+----------+
1 row in set (0.05 sec)
|
例: 查询 customers 表中有电子邮箱的顾客总数:
| SELECT COUNT(c_email) AS email_num
FROM customers;
+-----------+
| email_num |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
|
email 为空值 NULL 的记录没有被 COUNT() 函数计算
例: 在 orderitems 表中，使用 COUNT() 函数统计不同订单中订购的水果种类:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | SELECT o_num, COUNT(f_id)
FROM orderitems
GROUP BY o_num;
+-------+-------------+
| o_num | COUNT(f_id) |
+-------+-------------+
| 30001 | 4 |
| 30002 | 1 |
| 30003 | 1 |
| 30004 | 1 |
| 30005 | 4 |
+-------+-------------+
5 rows in set (0.00 sec)
|
SUM()函数
例: 在 orderitems 表中查询 3005 号订单一共购买的水果总量
| SELECT SUM(quantity) AS item_total
FROM orderitems
WHERE o_num = 30005;
+------------+
| item_total |
+------------+
| 30 |
+------------+
1 row in set (0.01 sec)
|
例: 在 orderitems 表中，使用 SUM() 函数统计不同订单号中订购的水果总量:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | SELECT o_num,SUM(quantity) AS item_total
FROM orderitems
GROUP BY o_num;
+-------+------------+
| o_num | item_total |
+-------+------------+
| 30001 | 33 |
| 30002 | 2 |
| 30003 | 100 |
| 30004 | 50 |
| 30005 | 30 |
+-------+------------+
5 rows in set (0.00 sec)
|
提示:
SUM() 函数在计算时，忽略列值为 NULL 的行
AVG() 函数
例: 在 fruits 表中，查询 s_id = 103 的供应商的水果价格的平均值:
| SELECT AVG(f_price) AS avg_price
FROM fruits
WHERE s_id = 103;
+-----------+
| avg_price |
+-----------+
| 5.700000 |
+-----------+
1 row in set (0.00 sec)
|
例: 在 fruits 表中，查询每一个供应商的水果价格的平均值:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | SELECT s_id, AVG(f_price) AS avg_price
FROM fruits
GROUP BY s_id;
+------+-----------+
| s_id | avg_price |
+------+-----------+
| 101 | 6.200000 |
| 103 | 5.700000 |
| 104 | 7.000000 |
| 107 | 3.600000 |
| 102 | 8.933333 |
| 105 | 7.466667 |
| 106 | 15.700000 |
+------+-----------+
7 rows in set (0.00 sec)
|
MAX() 函数
例: 在 fruits 表中查找市场上价格最高的水果价格
| SELECT MAX(f_price) AS max_price
FROM fruits;
+-----------+
| max_price |
+-----------+
| 15.70 |
+-----------+
1 row in set (0.01 sec)
|
例: 在 fruits 表中查找不同供应商提供的价格的最大值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | SELECT s_id, MAX(f_price) AS max_price
FROM fruits
GROUP BY s_id;
+------+-----------+
| s_id | max_price |
+------+-----------+
| 101 | 10.20 |
| 103 | 9.20 |
| 104 | 7.60 |
| 107 | 3.60 |
| 102 | 11.20 |
| 105 | 11.60 |
| 106 | 15.70 |
+------+-----------+
7 rows in set (0.00 sec)
|
MAX() 函数不仅适用于查找数值类型，也可应用于字符类型
例: 在 fruits 表中查找 f_name 的最大值:
| SELECT MAX(f_name)
FROM fruits;
+-------------+
| MAX(f_name) |
+-------------+
| xxxx |
+-------------+
1 row in set (0.00 sec)
|