select 进阶查询

刺骨的言语ヽ痛彻心扉 2023-02-22 03:55 38阅读 0赞

![在这里插入图片描述][20200701144742472.jpg]

## 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 聚合函数 ###

<table> 
 <thead> 
  <tr> 
   <th align="left">函数名</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">max( )</td> 
   <td align="left">查询指定列的最大值</td> 
  </tr> 
  <tr> 
   <td align="left">min( )</td> 
   <td align="left">查询指定列的最小值</td> 
  </tr> 
  <tr> 
   <td align="left">count( )</td> 
   <td align="left">统计查询结果的行数</td> 
  </tr> 
  <tr> 
   <td align="left">sum( )</td> 
   <td align="left">求和，返回指定列的总和</td> 
  </tr> 
  <tr> 
   <td align="left">avg( )</td> 
   <td align="left">求平均值，返回指定列数据的平均值</td> 
  </tr> 
 </tbody> 
</table>

### 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 开发，跟大家分享我的学习成长过程

![在这里插入图片描述][2020060821282046.jpg_pic_center]

![在这里插入图片描述][20200701151243618.png]

[20200701144742472.jpg]: https://img-blog.csdnimg.cn/20200701144742472.jpg
[2020060821282046.jpg_pic_center]: https://img-blog.csdnimg.cn/2020060821282046.jpg#pic_center
[20200701151243618.png]: https://img-blog.csdnimg.cn/20200701151243618.png