select 进阶查询

1.1 分组查询

1.1.1 语法

# where 和 having 可以省略 SELECT col_name, group_function, ··· FROM tb_name [WHERE where_condition] GROUP BY group_expression [HAVING group_condition];

☞ 说明

col_name：列明 tb_name：表名 where_condition：where 后的过滤条件 group_function：聚合函数 group_expression：group by 后的分组条件 group_condition：having 后的分组过滤条件

☞ 注意

 ① 分组查询中，select 后面只能出现，在 group by 后出现过的列或者聚合函数。  ② where 是在分组前对记录进行筛选，而 having 是在分组结束后的结果里筛选，最后返回最终查询结果。  ③ having 后所接的字段必须经过过滤(即：该字段必须使用)，一般与 group by 连用  ④ 分组查询中，若一个字段在一个组内有多个结果，则后一个结果覆盖前一个结果

1.1.2 聚合函数

函数名说明max( )查询指定列的最大值min( )查询指定列的最小值count( )统计查询结果的行数sum( )求和，返回指定列的总和avg( )求平均值，返回指定列数据的平均值

1.1.3 示例

☞ 数据准备

mysql> create table mydata( -> number int(11), -> name varchar(20), -> money decimal(5,2) -> ); Query OK, 0 rows affected (0.21 sec) mysql> desc mydata; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | number | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | money | decimal(5,2) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) mysql> insert into mydata -> values -> (1, "张三", 411.56), -> (2, "张三", 422.56), -> (3, "李四", 160.12), -> (4, "李四", 159.12), -> (5, "王五", 262.11), -> (6, "王五", 262.11), -> (7, "麻六", 395.99); Query OK, 7 rows affected (0.16 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from mydata; +--------+------+--------+ | number | name | money | +--------+------+--------+ | 1 | 张三 | 411.56 | | 2 | 张三 | 422.56 | | 3 | 李四 | 160.12 | | 4 | 李四 | 159.12 | | 5 | 王五 | 262.11 | | 6 | 王五 | 262.11 | | 7 | 麻六 | 395.99 | +--------+------+--------+ 7 rows in set (0.04 sec)

☞ gruop by

mysql> select *, count(*) from mydata group by name having number > 2; +--------+------+--------+----------+ | number | name | money | count(*) | +--------+------+--------+----------+ | 3 | 李四 | 160.12 | 2 | | 5 | 王五 | 262.11 | 2 | | 7 | 麻六 | 395.99 | 1 | +--------+------+--------+----------+ 3 rows in set (0.04 sec)

☞ max( )

mysql> select max(money) from mydata; +------------+ | max(money) | +------------+ | 422.56 | +------------+ 1 row in set (0.04 sec)

☞ sum( )

mysql> select sum(money) as 合计 from mydata; +---------+ | 合计 | +---------+ | 2073.57 | +---------+ 1 row in set (0.04 sec)

1.2 排序

1.2.1 语法

# 排序方式可以省略，写默认为 asc select * from tb_name order by col_name [asc|desc], col_name [asc|desc];

☞ 注意

 ① asc|desc 表示排序的规则，asc：升序，desc：降序，默认为 asc；  ② 支持多个字段进行排序，多字段排序之间用逗号隔开。当第一个的值相等时才会按照第二个排序。

1.2.2 示例

mysql> select * from mydata order by money desc, number asc; +--------+------+--------+ | number | name | money | +--------+------+--------+ | 2 | 张三 | 422.56 | | 1 | 张三 | 411.56 | | 7 | 麻六 | 395.99 | | 5 | 王五 | 262.11 | | 6 | 王五 | 262.11 | | 3 | 李四 | 160.12 | | 4 | 李四 | 159.12 | +--------+------+--------+ 7 rows in set (0.03 sec) mysql> select * from mydata where money > 300 order by money desc; +--------+------+--------+ | number | name | money | +--------+------+--------+ | 2 | 张三 | 422.56 | | 1 | 张三 | 411.56 | | 7 | 麻六 | 395.99 | +--------+------+--------+ 3 rows in set (0.04 sec)

1.3 分页

1.3.1 语法

select * from tb_name limit limit_offset, limit_count;

☞ 注意

 ① limit_offset：表示偏移量，即：跳过多少行，limit_offset 可以省略，默认为 0；  ② limit_count：跳过 limit_offset 行之后开始取数据，取 limit_count 行记录；  ③ limit 中 limit_offset 和 limit_count 的值必须大于等于 0，也不能用表达式表示。  ④ limit 为 MySQL 的 “方言” 在其他 SQL 中不一定能用。

1.3.2 示例

   开发过程中，分页我们经常使用，分页一般有2个参数：page：表示第几页； pageSize：每页显示多少条记录。那么转化为 limit 进行分页就是 limit_offset = (page - 1) * pageSize，limit_count = pageSize。

mysql> select * from mydata limit 0, 3; +--------+------+--------+ | number | name | money | +--------+------+--------+ | 1 | 张三 | 411.56 | | 2 | 张三 | 422.56 | | 3 | 李四 | 160.12 | +--------+------+--------+ 3 rows in set (0.03 sec) mysql> select * from mydata limit 3, 3; +--------+------+--------+ | number | name | money | +--------+------+--------+ | 4 | 李四 | 159.12 | | 5 | 王五 | 262.11 | | 6 | 王五 | 262.11 | +--------+------+--------+ 3 rows in set (0.03 sec) mysql> select * from mydata limit 6, 3; +--------+------+--------+ | number | name | money | +--------+------+--------+ | 7 | 麻六 | 395.99 | +--------+------+--------+ 1 row in set (0.03 sec)

1.4 综合查询

1.4.1 语法

# 可以省略某些查询，但是顺序不能改变 select * from tb_name where where_condition group by group_expression having group_condition order by [asc|desc] limit limit_offset, limit_count;

1.4.2 示例

  查询 mydata 表中 money 小于 400 的数据中 number 大于 3 的数据并按 name 分组，按照每组数据条数倒序，从 0 号数据开始查询 3 条数据

mysql> select *, count(*) from mydata -> where money < 400 -> group by name -> aving number > 3 -> order by count(*) desc -> limit 0,3; +--------+------+--------+----------+ | number | name | money | count(*) | +--------+------+--------+----------+ | 5 | 王五 | 262.11 | 2 | | 7 | 麻六 | 395.99 | 1 | +--------+------+--------+----------+ 2 rows in set (0.03 sec)

---

关注公众号一起学习 Java 开发，跟大家分享我的学习成长过程