MySQL 分组|排序

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms                |
| db_com             |
| db_test1           |
| lb_db              |
| lb_db1             |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test3              |
| test5              |
| testdb             |
+--------------------+
13 rows in set (0.08 sec)

mysql>
mysql>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bms                |
| db_com             |
| db_test1           |
| lb_db              |
| lb_db1             |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test3              |
| test5              |
| testdb             |
+--------------------+
13 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql>
mysql> select * form user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'form
user' at line 1
mysql> select * from user;
+----+--------+------+-------+
| id | name   | sex  | score |
+----+--------+------+-------+
|  1 | 小明   | 男   |    55 |
|  2 | 小红   | 女   |    72 |
|  3 | 小张   | 男   |    88 |
|  4 | 张三   | 男   |    80 |
|  5 | 李四   | 女   |    40 |
|  6 | 李梅   | 女   |    60 |
|  7 | 小美   | 男   |    99 |
+----+--------+------+-------+
7 rows in set (0.04 sec)

mysql> select avg(score) from user;
+-------------------+
| avg(score)        |
+-------------------+
| 70.57142857142857 |
+-------------------+
1 row in set (0.02 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select * from user group by sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and c
ontains nonaggregated column 'testdb.user.id' which is not functionally dependen
t on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_gr
oup_by
mysql> select id,name,sex,score from user group by sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and c
ontains nonaggregated column 'testdb.user.id' which is not functionally dependen
t on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_gr
oup_by
mysql> select sex from user group by sex;
+------+
| sex  |
+------+
| 女   |
| 男   |
+------+
2 rows in set (0.02 sec)

mysql> select sex,avg(score) from user group by sex;
+------+--------------------+
| sex  | avg(score)         |
+------+--------------------+
| 女   | 57.333333333333336 |
| 男   |               80.5 |
+------+--------------------+
2 rows in set (0.02 sec)

mysql> select sex,avg(score) from user group by sex order by avg(score) desc;
+------+--------------------+
| sex  | avg(score)         |
+------+--------------------+
| 男   |               80.5 |
| 女   | 57.333333333333336 |
+------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from user  order by score desc;
+----+--------+------+-------+
| id | name   | sex  | score |
+----+--------+------+-------+
|  7 | 小美   | 男   |    99 |
|  3 | 小张   | 男   |    88 |
|  4 | 张三   | 男   |    80 |
|  2 | 小红   | 女   |    72 |
|  6 | 李梅   | 女   |    60 |
|  1 | 小明   | 男   |    55 |
|  5 | 李四   | 女   |    40 |
+----+--------+------+-------+
7 rows in set (0.00 sec)

mysql>

复习

mysql> create database if not exists chapter character set utf8mb4;
Query OK, 1 row affected (0.10 sec)

mysql> use chapter;
Database changed
mysql> create table stdt  (id int primary key auto_increment comment '学号',name
 char(8) unique comment '姓名',gender char(2) comment '性别',score int comment '
成绩');
Query OK, 0 rows affected (0.40 sec)

mysql> desc stdt;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| name   | char(8) | YES  | UNI | NULL    |                |
| gender | char(2) | YES  |     | NULL    |                |
| score  | int(11) | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

mysql> show full columns from stdt\G;
*************************** 1. row ***************************
     Field: id
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment: 学号
*************************** 2. row ***************************
     Field: name
      Type: char(8)
 Collation: utf8mb4_general_ci
      Null: YES
       Key: UNI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 姓名
*************************** 3. row ***************************
     Field: gender
      Type: char(2)
 Collation: utf8mb4_general_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 性别
*************************** 4. row ***************************
     Field: score
      Type: int(11)
 Collation: NULL
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment: 成绩
4 rows in set (0.02 sec)

ERROR:
No query specified

mysql> insert into stdt (name,gender,score) values
    -> ('张三','男',89),
    -> ('赵三','男',95),
    -> ('张四','女',76);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into stdt (name,gender,score) values
    -> ('王五','男',100);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stdt;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  1 | 张三   | 男     |    89 |
|  2 | 赵三   | 男     |    95 |
|  3 | 张四   | 女     |    76 |
|  4 | 王五   | 男     |   100 |
+----+--------+--------+-------+
4 rows in set (0.00 sec)

mysql> update stdt set gender='女' where name='王五';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stdt;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  1 | 张三   | 男     |    89 |
|  2 | 赵三   | 男     |    95 |
|  3 | 张四   | 女     |    76 |
|  4 | 王五   | 女     |   100 |
+----+--------+--------+-------+
4 rows in set (0.00 sec)

mysql> alter table stdt drop index name;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stdt;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| name   | char(8) | YES  |     | NULL    |                |
| gender | char(2) | YES  |     | NULL    |                |
| score  | int(11) | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table stdt change name sname char(8);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stdt;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| sname  | char(8) | YES  |     | NULL    |                |
| gender | char(2) | YES  |     | NULL    |                |
| score  | int(11) | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> drop table stdt;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
Empty set (0.02 sec)

mysql>

在MySQL中，GROUP BY子句用于将查询结果集按照一个或多个列的值进行分组，以便能对每个组执行聚合操作，如计算总和、平均值、最大值、最小值或计数等。使用 GROUP BY时，通常会结合聚合函数（如 SUM(), AVG(), COUNT(), MAX(), MIN()等）来获取每组的特定统计信息。

基本语法

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE conditions
GROUP BY column1, [column2, ...]
[HAVING conditions];

column1: 是用于分组的列名，也可以是多个列名，用逗号分隔。
aggregate_function(column2): 聚合函数应用于分组后的数据，column2是要进行聚合操作的列。
HAVING conditions: 可选子句，用于过滤聚合后的结果组，类似于 WHERE但作用于分组后的数据。

示例

假设有一个 orders表，包含 customer_id（顾客ID）和 order_amount（订单金额）两个字段，你想按顾客ID分组并计算每个顾客的总订单金额：

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

多列分组

如果需要根据多个列进行分组，只需在 GROUP BY后列出所有列名：

SELECT customer_id, product_category, SUM(order_amount) AS category_total
FROM orders
GROUP BY customer_id, product_category;

这样，结果将首先按 customer_id分组，然后在每个顾客内部再按 product_category进一步分组。

使用HAVING

如果你想对分组后的结果进行过滤，比如只显示总金额大于1000的顾客：

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount > 1000;

注意事项

分组列应选择那些在逻辑上能够将数据合理划分的列。
在使用 HAVING子句时，不能直接使用未在 GROUP BY中列出的列或聚合函数之外的列。
WHERE子句用于在分组前过滤行，而 HAVING则是在分组后过滤组。

通过 GROUP BY，你可以对数据进行高级分析，获得汇总统计数据，这对于报告、数据分析和业务洞察非常有用。

在MySQL中，使用 ORDER BY子句可以对查询结果按照一个或多个字段进行排序。当需要对多个字段进行排序时，字段之间使用逗号分隔，并且MySQL会按照你指定的顺序依次进行排序。排序规则可以明确指定为 ASC（升序，默认）或 DESC（降序）。

基本语法

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

示例

假设有一个 students表，包含字段 sName(学生姓名), sAge(年龄), 和 sGrade(成绩)。

按年龄升序，成绩降序排序：

SELECT * FROM students
ORDER BY sAge ASC, sGrade DESC;

这个查询会首先按照 sAge字段升序排序所有记录，对于年龄相同的记录，则会按照 sGrade字段降序排序。

只指定第一个字段排序方向：

SELECT * FROM students
ORDER BY sAge DESC, sGrade;

在这个例子中，sAge会降序排序，而 sGrade默认升序排序。对于年龄相同的记录，它们会按照成绩升序排列。

处理NULL值：

在MySQL中，ORDER BY处理NULL值时，通常将NULL视为最小值。这意味着，如果某个字段有NULL值，那么NULL值的记录会排在非NULL值的记录之前（在升序排序中），或者之后（在降序排序中）。

注意事项

性能考虑： 对大量数据进行多字段排序可能会很慢，特别是如果无法使用索引时。确保排序字段上有合适的索引可以提高性能。
短路行为： MySQL在实际执行时，如果第一个排序字段已经能够唯一确定排序结果，那么后面的排序字段将不会起作用。例如，如果 sAge字段的所有值都是唯一的，那么 sGrade的排序指令将被忽略。
明确指定排序方向： 即使默认是升序，也建议明确写出每个字段的排序方向，以增强代码的可读性。

通过合理利用 ORDER BY子句，你可以灵活地控制查询结果的展示顺序，满足各种业务需求。

MySQL高级查询参见:

MySQL第四章节单表查询.pptx
https://www.alipan.com/s/3F31d12DNok
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

菜单

分享

MySQL 高级查询分组|排序

MySQL 分组|排序

复习

基本语法

示例

多列分组

使用HAVING

注意事项

基本语法

示例

注意事项

MySQL高级查询参见:

评论

arpl编译下载速度过慢？修改两个文件拯救你的下载速度（转载）

用.bat批处理文件运行conda环境下的Python程序

nat打洞(p2p)

PHP 命名空间(namespace)详解

PHP|Python 循环与条件语句内变量作用域及global、nonlocal关键字解析

Linux 使用 ‘useradd’ 命令的 ‘-g’ 选项指定组时，新用户为何不在 /etc/group 中作为组成员显示？

MySQL 外键约束

Linux输入输出(错误)重定向，分界符，解释，实例，综合实例，实际应用 cat命令解释

mysql设置约束|删除约束

MySQL 索引详解

分享

MySQL 高级查询 分组|排序

MySQL 分组|排序

复习

基本语法

示例

多列分组

使用HAVING

注意事项

基本语法

示例

注意事项

MySQL高级查询参见:

评论

MySQL 高级查询分组|排序