MySQl基础入门⑭

上一遍文章内容

查询的分组与汇总

创建一个员工信息表，字段包括员工号（主键、自增长、无符号）、姓名、性别、出生日期、部门和手机号码，可以使用以下SQL语句：

CREATE TABLE 员工信息 (
    员工号 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    性别 ENUM('男', '女') NOT NULL,
    出生日期 DATE NOT NULL,
    部门 VARCHAR(50) NOT NULL,
    手机号码 VARCHAR(20) NOT NULL,
    -- 根据数据库不同，可能需要为手机号码字段添加UNIQUE约束以确保唯一性
    UNIQUE KEY `unique_phone` (`手机号码`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

请注意：

• 在MySQL中，主键默认是无符号的，所以不需要特别指定UNSIGNED关键字。
• ENUM类型用于表示性别，但实际应用中根据需求也可选择使用VARCHAR类型存储，并在业务层进行校验。
• 手机号码字段这里假设每个员工的手机号码都是唯一的，因此添加了UNIQUE约束来保证唯一性。如果手机号码可以重复，则无需这个约束。

创建一个销售表，字段包括销售员ID、销售业绩金额、部门，可以使用以下SQL语句：

CREATE TABLE 销售表 (
    销售员ID INT(11) NOT NULL,
    -- 假设销售员ID为外键，引用员工信息表的员工号
    FOREIGN KEY (销售员ID) REFERENCES 员工信息(员工号),
    销售业绩金额 DECIMAL(10, 2) NOT NULL,
    部门 VARCHAR(50) NOT NULL,
    -- 可以根据需要添加其他字段，如销售日期、产品信息等
    销售日期 DATE,
    产品名称 VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里假设：

• 销售员ID是另一个表（例如上面提到的员工信息表）中的主键，在本表中作为外键。
• 销售业绩金额使用DECIMAL类型存储货币值，其中(10, 2)表示总共有10位数，其中小数点后有2位。
• 根据实际业务需求，你可能还需要添加更多的字段来记录销售相关的详细信息。

创建一个进货表，字段包括收货人ID、货号、总数量、总金额，可以使用以下SQL语句：

CREATE TABLE 进货表 (
    进货记录ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -- 假设收货人为公司员工，因此收货人ID为外键，引用员工信息表的员工号
    收货人ID INT(11) NOT NULL,
    FOREIGN KEY (收货人ID) REFERENCES 员工信息(员工号),
    货号 VARCHAR(50) NOT NULL,
    总数量 INT(11) NOT NULL,
    总金额 DECIMAL(10, 2) NOT NULL,
    -- 可以根据需要添加其他字段，如进货日期、供应商信息等
    进货日期 DATE,
    供应商名称 VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里假设：

• 收货人ID是另一个表（例如上面提到的员工信息表）中的主键，在本表中作为外键。
• 总数量使用整数类型存储商品的数量。
• 总金额使用DECIMAL类型存储货币值，其中(10, 2)表示总共有10位数，其中小数点后有2位。

填入数值

由于我们已经创建了员工表、销售表和进货表，接下来将分别为这三张表插入6条示例数据。请注意，这些示例数据是假设的，并且在实际应用中需要根据具体情况进行调整。

员工表（员工信息）

INSERT INTO 员工信息 (员工号, 姓名, 性别, 出生日期, 部门, 手机号码)
VALUES 
(1, '张三', '男', '1990-01-01', '销售部', '13800138010'),
(2, '李四', '女', '1992-02-02', '采购部', '13900139200'),
(3, '王五', '男', '1994-03-03', '财务部', '15000150030'),
(4, '赵六', '女', '1996-04-04', '市场部', '15100151000'),
(5, '孙七', '男', '1998-05-05', '技术部', '15200152000'),
(6, '周八', '女', '2000-06-06', '行政部', '15310153070'),
(7, '周7', '女', '2001-06-06', '行政部',  '15310153010'),
(8, '周8', '女', '2002-06-06', '行政部',  '15310153020'),
(9, '周9', '女', '2003-06-06', '行政部',  '15310153030');

销售表

INSERT INTO 销售表 (销售员ID, 销售业绩金额, 部门, 销售日期, 产品名称)
VALUES 
(1, 10000.00, '销售部', '2022-01-01', '产品A'),
(2, 15000.00, '销售部', '2022-02-01', '产品B'),
(3, 12000.00, '销售部', '2022-03-01', '产品C'),
(4, 18000.00, '销售部', '2022-04-01', '产品D'),
(5, 13000.00, '销售部', '2022-05-01', '产品E'),
(6, 16000.00, '销售部', '2022-06-01', '产品F');

进货表

INSERT INTO 进货表 (收货人ID, 货号, 总数量, 总金额, 进货日期, 供应商名称)
VALUES 
(2, 'G001', 100, 5000.00, '2022-01-15', '供应商甲'),
(3, 'H002', 150, 7500.00, '2022-02-15', '供应商乙'),
(4, 'I003', 200, 10000.00, '2022-03-15', '供应商丙'),
(5, 'J004', 250, 12500.00, '2022-04-15', '供应商丁'),
(6, 'K005', 300, 15000.00, '2022-05-15', '供应商戊'),
(1, 'L006', 350, 17500.00, '2022-06-15', '供应商己');

---

练习

1. AVG（）

题目：计算进货表中每个收货人的平均进货总金额。

SQL查询语句：

SELECT 员工信息.姓名, AVG(进货表.总金额) AS '平均进货总金额'
FROM 进货表
JOIN 员工信息 ON 进货表.收货人ID = 员工信息.员工号 GROUP BY 进货表.收货人ID;

2. COUNT()

题目：统计销售表中的销售记录总数。

SQL查询语句：

SELECT COUNT(*) AS '销售记录总数' FROM 销售表;

3. MAX()

题目：找出销售表中单笔销售业绩的最大金额。

SQL查询语句：

SELECT MAX(销售业绩金额) AS '最大单笔销售业绩' FROM 销售表;

4. MIN()

题目：在进货表中找到最低的单次进货总金额。

SQL查询语句：

SELECT MIN(总金额) AS '最低单次进货总金额' FROM 进货表;

5. SUM()

题目：计算销售表中所有销售员的累计销售业绩总额。

SQL查询语句：

SELECT SUM(销售业绩金额) AS '累计销售业绩总额' FROM 销售表;

---

分组练习

1. 题目：找出销售额最高的销售员及其对应的总销售额。

SQL查询语句：

SELECT 员工信息.姓名, SUM(销售表.销售业绩金额) AS '总销售额'
FROM 销售表
JOIN 员工信息 ON 销售表.销售员ID = 员工信息.员工号
GROUP BY 员工信息.员工号
HAVING SUM(销售表.销售业绩金额) = (
    SELECT MAX(total_sales)
    FROM (
        SELECT 销售员ID, SUM(销售业绩金额) AS total_sales
        FROM 销售表
        GROUP BY 销售员ID
    ) AS sales_summary
);

2. 题目：统计各部门中，至少有3名员工的部门名称及员工人数。

SQL查询语句：

SELECT 部门, COUNT(*) AS '部门员工数' FROM 员工信息 GROUP BY 部门 HAVING COUNT(*) >= 3;

3. 题目：找出在过去一年内（假设当前日期为’2023-06-01’），哪个供应商提供的商品平均单价最高。

   SQL查询语句：

   SELECT 
       进货表.供应商名称,
       AVG(进货表.总金额 / 进货表.总数量) AS '平均单价'
   FROM 
       进货表
   WHERE 
       进货表.进货日期 BETWEEN '2022-06-01' AND '2023-05-31'
   GROUP BY 
       进货表.供应商名称
   ORDER BY 
       AVG(进货表.总金额 / 进货表.总数量) DESC
   LIMIT 1;
   

4. 题目：使用COUNT()函数，查询各部门员工人数。

SQL查询语句：

SELECT 部门, COUNT(*) AS '部门员工数'   FROM 员工信息    GROUP BY 部门;

5. 题目：使用SUM()函数，统计销售表中所有销售业绩的总金额。

SQL查询语句：

SELECT SUM(销售业绩金额) AS '总销售额' FROM 销售表;

别名练习

在SQL查询语句中，e.部门表示从名为e的表（或视图、子查询结果）中选择名为“部门”的字段。在这个上下文中，“e”是员工信息表的一个别名，用于简化后续查询中的引用。所以 e.部门 指的是 员工信息 表中的 部门 字段值。

例如，在进行分组查询时，我们可能这样使用：

SELECT e.部门, COUNT(*) AS '部门人数' FROM 员工信息 AS e GROUP BY e.部门;

在这个查询中，我们通过给员工信息表定义一个别名e，然后使用e.部门来表示要按哪个字段进行分组，并统计每个部门的人数。

6. 题目：使用AVG()函数，计算每个部门在过去一年内的月平均销售额。

SQL查询语句（假设当前日期为’2023-06-01’）：

SELECT 
    e.部门,
    AVG(s.销售业绩金额) AS '月均销售额'
FROM 
    销售表 s
JOIN 
    员工信息 e ON s.销售员ID = e.员工号
WHERE 
    s.销售日期 BETWEEN '2022-06-01' AND '2023-05-31'
GROUP BY 
    e.部门;

7. 题目：使用MIN()函数，找出进货表中单次进货总金额最低的一条记录对应的供应商名称和进货金额。

SQL查询语句：

SELECT 进货表.供应商名称, MIN(进货表.总金额) AS '最低进货金额'
FROM 进货表
GROUP BY 进货表.供应商名称
ORDER BY MIN(进货表.总金额)
LIMIT 1;

8. 题目：使用MAX()函数，找出销售表中单个销售员最高的单笔销售业绩及其姓名。

SQL查询语句：

SELECT 员工信息.姓名, MAX(销售业绩金额) AS '最高销售业绩'
FROM 销售表
JOIN 员工信息 ON 销售表.销售员ID = 员工信息.员工号
GROUP BY 销售表.销售员ID
ORDER BY MAX(销售业绩金额) DESC
LIMIT 1;

9. 题目：使用HAVING配合COUNT()函数，查找至少有5名员工的部门。

SQL查询语句：

SELECT 部门, COUNT(*) AS '部门员工数' FROM 员工信息 GROUP BY 部门 HAVING COUNT(*) >= 3;