Day9：关于MySQL的数据查询——聚合函数和数据分组

于 2025-04-10 15:41:52 发布
阅读量383 收藏 4
点赞数 7
分类专栏： 关于我在学习数据库这件事 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2301_77033793/article/details/147116135
版权

前言：先创建一个练习的数据库和数据

1.创建数据库并创建数据表的基本结构

-- 创建数据库
CREATE DATABASE sales_practice;
USE sales_practice;

-- 创建产品表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL
);

-- 创建客户表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    membership_level VARCHAR(20) NOT NULL
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 创建订单详情表
CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
mysql> CREATE DATABASE sales_pratice;
Query OK, 1 row affected (0.01 sec)
mysql> USE sales_pratice;
Database changed
mysql> CREATE TABLE products (
    -> product_id INT AUTO_INCREMENT PRIMARY KEY,
    -> product_name VARCHAR(100) NOT NULL,
    -> category VARCHAR(50) NOT NULL,
    -> price DECIMAL(10, 2) NOT NULL,
    -> stock_quantity INT NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE customers (
    -> customer_id INT AUTO_INCREMENT PRIMARY KEY,
    -> customer_name VARCHAR(100) NOT NULL,
    -> city VARCHAR(50) NOT NULL,
    -> membership_level VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE orders (
    -> order_id INT AUTO_INCREMENT PRIMARY KEY,
    -> customer_id INT NOT NULL,
    -> order_date DATE NOT NULL,
    -> FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE order_details (
    -> order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    -> order_id INT NOT NULL,
    -> product_id INT NOT NULL,
    -> quantity INT NOT NULL,
    -> unit_price DECIMAL(10, 2) NOT NULL,
    -> FOREIGN KEY (order_id) REFERENCES orders(order_id),
    -> FOREIGN KEY (product_id) REFERENCES products(product_id)
    -> );
Query OK, 0 rows affected (0.07 sec)

 2.导入实例数据

-- 插入产品数据
INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('笔记本电脑', '电子产品', 5999.00, 50),
('智能手机', '电子产品', 3999.00, 100),
('平板电脑', '电子产品', 2999.00, 30),
('无线耳机', '电子产品', 599.00, 200),
('智能手表', '电子产品', 1299.00, 80),
('T恤', '服装', 99.00, 300),
('牛仔裤', '服装', 199.00, 200),
('运动鞋', '服装', 299.00, 150),
('咖啡机', '家电', 899.00, 40),
('电饭煲', '家电', 399.00, 60),
('吹风机', '家电', 199.00, 120),
('电动牙刷', '个人护理', 299.00, 180);

-- 插入客户数据
INSERT INTO customers (customer_name, city, membership_level) VALUES
('张三', '北京', '黄金'),
('李四', '上海', '白银'),
('王五', '北京', '普通'),
('赵六', '广州', '黄金'),
('钱七', '深圳', '普通'),
('孙八', '北京', '白银'),
('周九', '上海', '黄金'),
('吴十', '广州', '普通'),
('郑十一', '深圳', '白银'),
('王十二', '北京', '黄金');

-- 插入订单数据
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-01-15'),
(2, '2023-01-16'),
(3, '2023-01-17'),
(4, '2023-01-18'),
(5, '2023-01-19'),
(6, '2023-01-20'),
(7, '2023-01-21'),
(1, '2023-02-10'),
(2, '2023-02-11'),
(3, '2023-02-12'),
(4, '2023-02-13'),
(5, '2023-02-14'),
(6, '2023-02-15'),
(7, '2023-02-16'),
(8, '2023-03-05'),
(9, '2023-03-06'),
(10, '2023-03-07'),
(1, '2023-03-08'),
(2, '2023-03-09'),
(3, '2023-03-10');

-- 插入订单详情数据
INSERT INTO order_details (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 5999.00),
(1, 3, 1, 2999.00),
(2, 2, 2, 3999.00),
(3, 5, 1, 1299.00),
(3, 6, 3, 99.00),
(4, 4, 2, 599.00),
(4, 7, 1, 199.00),
(5, 8, 1, 299.00),
(5, 9, 1, 899.00),
(6, 10, 1, 399.00),
(6, 11, 1, 199.00),
(7, 12, 2, 299.00),
(8, 1, 1, 5999.00),
(9, 3, 2, 2999.00),
(10, 5, 1, 1299.00),
(11, 7, 2, 199.00),
(12, 9, 1, 899.00),
(13, 11, 1, 199.00),
(14, 2, 1, 3999.00),
(15, 4, 1, 599.00),
(16, 6, 2, 99.00),
(17, 8, 1, 299.00),
(18, 10, 1, 399.00),
(19, 12, 1, 299.00),
(20, 1, 1, 5999.00);
mysql> INSERT INTO products (product_name, category, price, stock_quantity) VALUES
    -> ('笔记本电脑', '电子产品', 5999.00, 50),
    -> ('智能手机', '电子产品', 3999.00, 100),
    -> ('平板电脑', '电子产品', 2999.00, 30),
    -> ('无线耳机', '电子产品', 599.00, 200),
    -> ('智能手表', '电子产品', 1299.00, 80),
    -> ('T恤', '服装', 99.00, 300),
    -> ('牛仔裤', '服装', 199.00, 200),
    -> ('运动鞋', '服装', 299.00, 150),
    -> ('咖啡机', '家电', 899.00, 40),
    -> ('电饭煲', '家电', 399.00, 60),
    -> ('吹风机', '家电', 199.00, 120),
    -> ('电动牙刷', '个人护理', 299.00, 180);
Query OK, 12 rows affected (0.01 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> INSERT INTO customers (customer_name, city, membership_level) VALUES
    -> ('张三', '北京', '黄金'),
    -> ('李四', '上海', '白银'),
    -> ('王五', '北京', '普通'),
    -> ('赵六', '广州', '黄金'),
    -> ('钱七', '深圳', '普通'),
    -> ('孙八', '北京', '白银'),
    -> ('周九', '上海', '黄金'),
    -> ('吴十', '广州', '普通'),
    -> ('郑十一', '深圳', '白银'),
    -> ('王十二', '北京', '黄金');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO orders (customer_id, order_date) VALUES
    -> (1, '2023-01-15'),
    -> (2, '2023-01-16'),
    -> (3, '2023-01-17'),
    -> (4, '2023-01-18'),
    -> (5, '2023-01-19'),
    -> (6, '2023-01-20'),
    -> (7, '2023-01-21'),
    -> (1, '2023-02-10'),
    -> (2, '2023-02-11'),
    -> (3, '2023-02-12'),
    -> (4, '2023-02-13'),
    -> (5, '2023-02-14'),
    -> (6, '2023-02-15'),
    -> (7, '2023-02-16'),
    -> (8, '2023-03-05'),
    -> (9, '2023-03-06'),
    -> (10, '2023-03-07'),
    -> (1, '2023-03-08'),
    -> (2, '2023-03-09'),
    -> (3, '2023-03-10');
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> INSERT INTO order_details (order_id, product_id, quantity, unit_price) VALUES
    -> (1, 1, 1, 5999.00),
    -> (1, 3, 1, 2999.00),
    -> (2, 2, 2, 3999.00),
    -> (3, 5, 1, 1299.00),
    -> (3, 6, 3, 99.00),
    -> (4, 4, 2, 599.00),
    -> (4, 7, 1, 199.00),
    -> (5, 8, 1, 299.00),
    -> (5, 9, 1, 899.00),
    -> (6, 10, 1, 399.00),
    -> (6, 11, 1, 199.00),
    -> (7, 12, 2, 299.00),
    -> (8, 1, 1, 5999.00),
    -> (9, 3, 2, 2999.00),
    -> (10, 5, 1, 1299.00),
    -> (11, 7, 2, 199.00),
    -> (12, 9, 1, 899.00),
    -> (13, 11, 1, 199.00),
    -> (14, 2, 1, 3999.00),
    -> (15, 4, 1, 599.00),
    -> (16, 6, 2, 99.00),
    -> (17, 8, 1, 299.00),
    -> (18, 10, 1, 399.00),
    -> (19, 12, 1, 299.00),
    -> (20, 1, 1, 5999.00);
Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0

一、聚合函数

函数说明
COUNT求一组数据的个数
AVG求一组数据的平均值
SUM求一组数据的总和
MAX求一组数据的最大值
MIN求一组数据的最小值

1.COUNT函数

        用于统计表中满足条件的行数或总行数，返回SELECT语句检索到的行的数目，若找不到匹配的行，则返回0。

COUNT({[ ALL | DISTINCT ] 表达式 } | * )

（1）表达式：可以是常量、字段名称、函数或表达式。

（2）ALL | DISTINCT：ALL表示对所有值进行运算，是默认值；DISTINCT表示去除重复值。

（3）*：表示返回检索到的行的总数目。 

-- 计算产品总数
SELECT COUNT(*) AS total_products FROM products;

-- 计算不同类别产品的数量
SELECT COUNT(DISTINCT category) AS distinct_categories FROM products;

-- 计算有订单的客户数量
SELECT COUNT(DISTINCT customer_id) AS customers_with_orders FROM orders;

 计算产品表中的产品总数

mysql> SELECT COUNT(*) AS 产品总数 FROM products;
+----------+
| 产品总数 |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

计算产品表中不同类别产品数量

mysql> SELECT COUNT(DISTINCT category) AS 产品种类数量 FROM products;
+--------------+
| 产品种类数量 |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

计算有订单的客户数量

mysql> SELECT COUNT(DISTINCT customer_id) AS 有订单的客户 FROM orders;
+--------------+
| 有订单的客户 |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

2.SUM函数和AVG函数

        SUM函数用于求出一组数据的总和

        AVG函数用于求出一组数据的平均值

计算所有订单的总销售额

mysql> SELECT SUM(quantity * unit_price) AS 订单总销售额 FROM order_details;
+--------------+
| 订单总销售额 |
+--------------+
|     48967.00 |
+--------------+
1 row in set (0.00 sec)

计算产品的平均价格

mysql> SELECT AVG(price) AS 产品平均价格 FROM products;
+--------------+
| 产品平均价格 |
+--------------+
|  1440.666667 |
+--------------+
1 row in set (0.00 sec)

3.MAX函数和MIN函数

MAX函数用于求出一组数据的最大值

MIN函数用于求出一组数据的最小值。

MAX | MIN ( [ ALL  DISTINCT ] 表达式 )

SELECT MAX(price) AS "产品最高价",MIN(price) AS "产品最低价" 
FROM products;

mysql> SELECT MAX(price) AS "产品最高价",MIN(price) AS "产品最低价"
    -> FROM products;
+------------+------------+
| 产品最高价 | 产品最低价 |
+------------+------------+
|    5999.00 |      99.00 |
+------------+------------+
1 row in set (0.00 sec)

二、数据分组

1.单字段分组

GROUP BY子句中只有一个字段，则记录将按照该字段的值进行分组

按城市分组，计算每个城市的客户数量

SELECT city AS 所在城市,COUNT(customer_name) AS 人数
FROM customers
GROUP BY city;

mysql> SELECT city AS 所在城市,COUNT(customer_name) AS 人数
    -> FROM customers
    -> GROUP BY city;
+----------+------+
| 所在城市 | 人数 |
+----------+------+
| 北京     |    4 |
| 上海     |    2 |
| 广州     |    2 |
| 深圳     |    2 |
+----------+------+
4 rows in set (0.01 sec)

2.多字段分组 

查询等级、所在城市、人数，指定city,membership_level对数据进行分组，指定membership_level为排序 方式

SELECT membership_level AS 等级,city AS 所在城市,COUNT(customer_name) AS 人数
FROM customers
GROUP BY city,membership_level
ORDER BY membership_level;

mysql> SELECT membership_level AS 等级,city AS 所在城市,COUNT(customer_name) AS 人数
    -> FROM customers
    -> GROUP BY city,membership_level
    -> ORDER BY membership_level;
+------+----------+------+
| 等级 | 所在城市 | 人数 |
+------+----------+------+
| 普通 | 北京     |    1 |
| 普通 | 深圳     |    1 |
| 普通 | 广州     |    1 |
| 白银 | 上海     |    1 |
| 白银 | 北京     |    1 |
| 白银 | 深圳     |    1 |
| 黄金 | 北京     |    2 |
| 黄金 | 广州     |    1 |
| 黄金 | 上海     |    1 |
+------+----------+------+
9 rows in set (0.00 sec)

3.HAVING子句

HAVING子句必须和GROUP BY子句配合使用，用于指定分组需满足条件。

注意HAVING子句与WHERE子句区别。

（1）WHERE子句在GROUP BY分组之前起作用；HAVING子句在GROUP BY分组之后起作用。

（2）WHERE子句作用于表，从表中选择满足条件的记录；HAVING子句作用于GROUP BY分组，从分组中选择满足条件的组。

（3）WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数。

使用HAVING指定分组排序

SELECT city AS 所在城市,COUNT(customer_name) AS 人数
FROM customers
GROUP BY city HAVING COUNT(customer_name) <= 2;

mysql> SELECT city AS 所在城市,COUNT(customer_name) AS 人数
    -> FROM customers
    -> GROUP BY city HAVING COUNT(customer_name) <= 2;
+----------+------+
| 所在城市 | 人数 |
+----------+------+
| 上海     |    2 |
| 广州     |    2 |
| 深圳     |    2 |
+----------+------+
3 rows in set (0.00 sec)

 使用ORDER指定分组排序

SELECT city AS 所在城市,COUNT(customer_name) AS 人数
FROM customers
GROUP BY city 
ORDER BY COUNT(customer_name) DESC;

mysql> SELECT city AS 所在城市,COUNT(customer_name) AS 人数
    -> FROM customers
    -> GROUP BY city
    -> ORDER BY COUNT(customer_name) DESC;
+----------+------+
| 所在城市 | 人数 |
+----------+------+
| 北京     |    4 |
| 上海     |    2 |
| 广州     |    2 |
| 深圳     |    2 |
+----------+------+
4 rows in set (0.00 sec)

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值