一罐可乐

1
drop table if exists Rider;
2
create table Rider (
3
    RiderID        int primary key,
4
    RiderName      varchar(20) not null,
5
    IdentityNumber char(18)    not null unique,
6
    Phone          char(11)    not null,
7
    Address        text        not null,
8
    SiteID         int         not null,
9
    Birthdate      date as (date(concat(substring(IdentityNumber, 7, 4), '-', substring(IdentityNumber, 11, 2), '-',
10
                                        substring(IdentityNumber, 13, 2)))),
11
    Gender         enum ('男','女') as (if(substring(IdentityNumber, 17, 1) % 2 = 1, '男', '女')),
12
    foreign key (SiteID) references Station (SiteID)
13
);

1
DROP PROCEDURE IF EXISTS p1;
2
DELIMITER $$
3
CREATE PROCEDURE p1(
4
    IN $xdate VARCHAR(7)
5
)
6
BEGIN
7
    -- 1. 预计算每个省份 (Region) 在该月份的销售额汇总
8
    WITH tmp1 AS (
9
        SELECT RegionID, SUM(Amount) AS Amount1
10
        FROM v1
11
        WHERE DATE_FORMAT(OrderDate, '%Y-%m') = $xdate
12
        GROUP BY RegionID
13
        HAVING SUM(Amount) > 0
14
    ),
15
    -- 2. 预计算每个客户 (Customer) 在该月份的销售额汇总
16
    tmp2 AS (
17
        SELECT CustomerID, SUM(Amount) AS Amount2
18
        FROM v1
19
        WHERE DATE_FORMAT(OrderDate, '%Y-%m') = $xdate
20
        GROUP BY CustomerID
21
        HAVING SUM(Amount) > 0
22
    ),
23
    -- 3. 预计算每个订单 (Order) 的销售额汇总
24
    tmp3 AS (
25
        SELECT OrderID, SUM(Amount) AS Amount3
26
        FROM v1
27
        WHERE DATE_FORMAT(OrderDate, '%Y-%m') = $xdate
28
        GROUP BY OrderID
29
        HAVING SUM(Amount) > 0
30
    )
31

32
    -- 第一层：省份结点
33
    SELECT DISTINCT a.RegionID                                                    AS `ID`,
34
                    CONCAT(b.AreaName, '(', ROUND(c.Amount1 / 10000, 2), '万元)') AS `Text`,
35
                    ''                                                            AS `Parentnodeid`,
36
                    1                                                             AS `Level`,
37
                    1                                                             AS `Isparentflag`,
38
                    ''                                                            AS `Ancestor`
39
    FROM v1    AS a
40
    JOIN Areas AS b ON a.RegionID = b.AreaID
41
    JOIN tmp1  AS c ON c.RegionID = b.AreaID
42

43
    UNION ALL
44
    -- 第二层：客户结点
45
    SELECT DISTINCT a.CustomerID                                                     AS `ID`,
46
                    CONCAT(a.Companyname, '(', ROUND(c.Amount2 / 10000, 2), '万元)') AS `Text`,
47
                    a.RegionID                                                       AS `Parentnodeid`,
48
                    2                                                                AS `Level`,
49
                    1                                                                AS `Isparentflag`,
50
                    CONCAT(a.RegionID, '#')                                          AS `Ancestor`
51
    FROM v1    AS a
52
    JOIN Areas AS b ON a.CityID = b.AreaID
53
    JOIN tmp2  AS c ON c.CustomerID = a.CustomerID
54
    WHERE DATE_FORMAT(a.OrderDate, '%Y-%m') = $xdate
55

56
    UNION ALL
57
    -- 第三层：订单结点
58
    SELECT DISTINCT a.OrderID                                                AS `ID`,
59
                    CONCAT(a.OrderID, ' ', a.OrderDate, '(', c.Amount3, ')') AS `Text`,
60
                    a.CustomerID                                             AS `Parentnodeid`,
61
                    3                                                        AS `Level`,
62
                    0                                                        AS `Isparentflag`,
63
                    CONCAT(a.RegionID, '#', a.CustomerID, '#')               AS `Ancestor`
64
    FROM v1   AS a
65
    JOIN tmp3 AS c ON c.OrderID = a.OrderID
66
    WHERE DATE_FORMAT(a.OrderDate, '%Y-%m') = $xdate
67

68
    -- 按照路径进行深度优先排序，使子结点紧跟在父结点下方
69
    ORDER BY CONCAT(TRIM(Ancestor), ID);
70

71
END $$
72
DELIMITER ;

1
DROP TABLE IF EXISTS Customers_tmp;
2
CREATE TEMPORARY TABLE Customers_tmp AS
3
SELECT *
4
FROM customers;

1
DROP PROCEDURE IF EXISTS p3;
2
DELIMITER $$
3
CREATE PROCEDURE p3(
4
    IN $data VARCHAR(1000)
5
)
6
BEGIN
7
    DECLARE $cid CHAR(6);
8
    DECLARE $maxRowno INT;
9
    DECLARE $lastRowno INT;
10
    DECLARE $cid_next CHAR(6);
11
    DECLARE $cid_prev CHAR(6);
12

13
    -- 创建包含行号的视图（默认按主键/客户编码顺序）
14
    CREATE OR REPLACE VIEW v1 AS
15
    SELECT ROW_NUMBER() OVER () AS Rowno, a.*
16
    FROM Customers AS a;
17

18
    -- 追加分号作为截取结束符
19
    SET $data = CONCAT($data, ';');
20

21
    -- 循环拆分字符串并逐个删除临时表中的客户
22
    WHILE (LOCATE(';', $data) > 0) DO
23
        SET $cid = SUBSTRING($data, 1, LOCATE(';', $data) - 1);
24
        SET $data = SUBSTRING($data, LOCATE(';', $data) + 1);
25

26
        DELETE FROM Customers_tmp WHERE CustomerID = $cid;
27
    END WHILE;
28

29
    -- 获取最后一个被删客户在视图中的行序号
30
    SELECT MAX(Rowno) INTO $maxRowno FROM v1;
31
    SELECT MAX(Rowno) INTO $lastRowno FROM v1 WHERE CustomerID = $cid;
32

33
    -- 尝试获取下一个客户的编码
34
    SELECT CustomerID INTO $cid_next FROM v1 WHERE Rowno = $lastRowno + 1;
35

36
    -- 输出目标客户（若没有下一个客户则输出上一个客户）
37
    IF $cid_next IS NULL THEN
38
        SELECT CustomerID INTO $cid_prev FROM v1 WHERE Rowno = $lastRowno - 1;
39
        -- 从视图 v1 查询以包含 Rowno 行序号
40
        SELECT * FROM v1 WHERE CustomerID = $cid_prev;
41
    ELSE
42
        SELECT * FROM v1 WHERE CustomerID = $cid_next;
43
    END IF;
44
END $$
45
DELIMITER ;

1
DROP TABLE IF EXISTS myOrders;
2
CREATE TABLE myOrders LIKE orders;
3
ALTER TABLE myOrders MODIFY OrderID CHAR(12);

1
DROP TRIGGER IF EXISTS tr2;
2
DELIMITER $$
3
CREATE TRIGGER tr2
4
    BEFORE INSERT
5
    ON myOrders
6
    FOR EACH ROW
7
BEGIN
8
    DECLARE DateNum CHAR(6);
9
    DECLARE SerialNum INT;
10
    DECLARE OrderNum CHAR(12);
11

12
    -- 提取待插入订单日期的年份与月份，格式化为 YYYYMM
13
    SET DateNum = DATE_FORMAT(NEW.OrderDate, '%Y%m');
14

15
        -- 查询该月份当前已有的最大序号，如果当月没有订单，则从 1 开始
16
        SELECT IFNULL(MAX(CAST(RIGHT(TRIM(OrderID), 4) AS UNSIGNED)), 0) + 1
17
        INTO SerialNum
18
        FROM myOrders
19
        WHERE OrderID LIKE CONCAT(DateNum, '%');
20

21
    -- 拼接年份月份与前导补零的 4 位序号
22
    SET OrderNum = CONCAT(DateNum, LPAD(SerialNum, 4, '0'));
23

24
    -- 自动填充新纪录的 OrderID
25
    SET NEW.OrderID = OrderNum;
26
END $$
27
DELIMITER ;

1
DROP TRIGGER IF EXISTS tr3;
2
DELIMITER $$
3
CREATE TRIGGER tr3
4
    BEFORE INSERT
5
    ON Customers
6
    FOR EACH ROW
7
BEGIN
8
    DECLARE RegionCode CHAR(2);
9
    DECLARE NameCode CHAR(4);
10

11
    -- 提取 CustomerID 的前 2 位 (省份拼音首字母) 和后 4 位 (名称拼音首字母)
12
    SET RegionCode = LEFT(NEW.CustomerID, 2);
13
    SET NameCode = RIGHT(NEW.CustomerID, 4);
14

15
    -- 1. 验证格式：客户编码必须由 8 位字符组成，前 2 位为大写字母，中间 2 位为数字，后 4 位为大写字母
16
    IF NOT (NEW.CustomerID REGEXP '^[A-Z]{2}[0-9]{2}[A-Z]{4}$') THEN
17
        SIGNAL SQLSTATE '45000'
18
        SET MESSAGE_TEXT = '数据校验失败：客户编码必须由前 2 位字母、中间 2 位数字及后 4 位字母组成！';
19

20
    -- 2. 验证前 2 位省份拼音首字母：查询客户 RegionID 对应省份 (取前 2 个字，如 "浙江省" 取 "浙江") 的首拼音字母
21
    ELSEIF NOT (RegionCode IN (
22
        SELECT sys_getFirstPyCode(LEFT(AreaName, 2))
23
        FROM areas
24
        WHERE AreaID = NEW.RegionID
25
    )) THEN
26
        SIGNAL SQLSTATE '45000'
27
        SET MESSAGE_TEXT = '数据校验失败：客户编码前 2 位必须为所属省份中文拼音的首字母！';
28

29
    -- 3. 验证后 4 位名称拼音首字母：与联系人姓名 Contactname 的首拼音字母一致
30
    ELSEIF NOT (NameCode IN (sys_getFirstPyCode(NEW.Contactname))) THEN
31
        SIGNAL SQLSTATE '45000'
32
        SET MESSAGE_TEXT = '数据校验失败：客户编码后 4 位必须与客户名称拼音的首字母一致！';
33

34
    -- 4. 验证唯一性：确保该 CustomerID 在表中不重复
35
    ELSEIF EXISTS (
36
        SELECT 1
37
        FROM Customers
38
        WHERE CustomerID = NEW.CustomerID
39
    ) THEN
40
        SIGNAL SQLSTATE '45000'
41
        SET MESSAGE_TEXT = '数据校验失败：客户编码已存在，必须保证唯一性！';
42
    END IF;
43
END $$
44
DELIMITER ;

1
DROP PROCEDURE IF EXISTS p2;
2
DELIMITER $$
3
CREATE PROCEDURE p2(
4
    IN $productname VARCHAR(255),
5
    IN $quantityperunit VARCHAR(255),
6
    IN $unit VARCHAR(255),
7
    IN $unitprice DECIMAL(12, 2),
8
    IN $supplierid VARCHAR(20),
9
    IN $categoryid VARCHAR(2),
10
    IN $subcategoryid VARCHAR(5)
11
)
12
BEGIN
13
    DECLARE $x INT;
14
    SET $x = 1;
15

16
    -- 1. 数据验证：商品名称、规格型号和计量单位不能为空，商品名称不超过 100 字符且不能包含 @、#、$ 字符
17
    IF $productname IS NULL OR $quantityperunit IS NULL OR $unit IS NULL OR CHAR_LENGTH($productname) > 100 OR
18
       INSTR($productname, '@') > 0 OR INSTR($productname, '#') > 0 OR INSTR($productname, '$') > 0 THEN
19
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据校验失败：商品名称、规格型号、计量单位非法或包含敏感字符！';
20
    END IF;
21

22
    -- 2. 数据验证：商品单价为大于 0 的数值
23
    IF $unitprice <= 0 THEN
24
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据校验失败：商品单价必须大于 0！';
25
    END IF;
26

27
    -- 3. 数据验证：大类编码在 Categories 表中合法存在
28
    SELECT COUNT(*) INTO $x FROM categories WHERE CategoryID = $categoryid;
29
    IF $x = 0 THEN
30
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据校验失败：商品大类编码不存在！';
31
    END IF;
32

33
    -- 4. 数据验证：小类编码在 CategoryTree 表中合法存在
34
    SELECT COUNT(*) INTO $x FROM categorytree WHERE CategoryID = $subcategoryid;
35
    IF $x = 0 THEN
36
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据校验失败：商品小类编码不存在！';
37
    END IF;
38

39
    -- 5. 数据验证：供应商编码在 Suppliers 表中合法存在
40
    SELECT COUNT(*) INTO $x FROM suppliers WHERE SupplierID = $supplierid;
41
    IF $x = 0 THEN
42
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据校验失败：供应商编码不存在！';
43
    END IF;
44

45
    -- 校验全部通过，在商品表 products 中执行插入
46
    INSERT INTO products (ProductName, QuantityPerunit, Unit, Unitprice, SupplierID, CategoryID, SubcategoryID)
47
    VALUES ($productname, $quantityperunit, $unit, $unitprice, $supplierid, $categoryid, $subcategoryid);
48

49
    -- 6. 输出新插入的商品详细信息（关联大类表与供应商表，取得类别名称与供应商名称）
50
    SELECT a.*
51
           b.CategoryName,
52
           c.CompanyName
53
    FROM products AS a
54
    LEFT JOIN categories AS b ON a.CategoryID = b.CategoryID
55
    LEFT JOIN suppliers  AS c ON a.SupplierID = c.SupplierID
56
    WHERE a.ProductID = LAST_INSERT_ID();
57

58
END $$
59
DELIMITER ;

1
DROP PROCEDURE IF EXISTS p1;
2
DELIMITER $$
3

4
CREATE PROCEDURE p1(
5
    IN $year INT
6
)
7
BEGIN
8
    -- 当前行变量
9
    DECLARE $cid VARCHAR(10);          -- 客户编码
10
    DECLARE $cname VARCHAR(255);       -- 客户名称
11
    DECLARE $amt DECIMAL(18, 2);       -- 客户销售额
12

13
    -- 上一行变量
14
    DECLARE $prev_cid VARCHAR(10) DEFAULT NULL;
15
    DECLARE $prev_cname VARCHAR(255) DEFAULT NULL;
16
    DECLARE $prev_amt DECIMAL(18, 2) DEFAULT NULL;
17

18
    DECLARE $diff DECIMAL(18, 2) DEFAULT 0;       -- 当前差值
19
    DECLARE $max_diff DECIMAL(18, 2) DEFAULT -1;  -- 记录的最大差值
20

21
    -- 记录最大差值的客户 1 属性
22
    DECLARE $max_cid_1 VARCHAR(10) DEFAULT NULL;
23
    DECLARE $max_cname_1 VARCHAR(255) DEFAULT NULL;
24
    DECLARE $max_amt_1 DECIMAL(18, 2) DEFAULT NULL;
25

26
    -- 记录最大差值的客户 2 属性
27
    DECLARE $max_cid_2 VARCHAR(10) DEFAULT NULL;
28
    DECLARE $max_cname_2 VARCHAR(255) DEFAULT NULL;
29
    DECLARE $max_amt_2 DECIMAL(18, 2) DEFAULT NULL;
30

31
    DECLARE $flag INT DEFAULT 1;       -- 游标终止状态标识
32

33
    -- 定义游标：查询指定年份所有客户的销售额并从小到大排序
34
    DECLARE cur1 CURSOR FOR
35
        SELECT t.Customerid, c.Companyname, t.Amt
36
        FROM (
37
            SELECT a.Customerid, SUM(b.Amount) AS Amt
38
            FROM orders AS a
39
            JOIN orderitems AS b ON b.Orderid = a.Orderid
40
            WHERE YEAR(a.Orderdate) = $year
41
            GROUP BY a.Customerid
42
        ) AS t
43
        JOIN customers AS c ON c.Customerid = t.Customerid
44
        ORDER BY t.Amt, t.Customerid;
45

46
    -- 游标溢出处理句柄
47
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET $flag = 0;
48

49
    OPEN cur1;
50
    -- 读取第一条记录
51
    FETCH cur1 INTO $cid, $cname, $amt;
52

53
    WHILE ($flag = 1) DO
54
        IF $prev_cid IS NOT NULL THEN       -- 从第二行开始计算差值
55
            SET $diff = $amt - $prev_amt;
56

57
            -- 如果发现更大的差值，则更新缓存
58
            IF $diff > $max_diff THEN
59
                SET $max_diff = $diff;
60

61
                SET $max_cid_1 = $prev_cid;
62
                SET $max_cname_1 = $prev_cname;
63
                SET $max_amt_1 = $prev_amt;
64

65
                SET $max_cid_2 = $cid;
66
                SET $max_cname_2 = $cname;
67
                SET $max_amt_2 = $amt;
68
            END IF;
69
        END IF;
70

71
        -- 将当前记录赋值给上一行变量，作为下一次循环对比基础
72
        SET $prev_cid = $cid;
73
        SET $prev_cname = $cname;
74
        SET $prev_amt = $amt;
75

76
        -- 读取下一行
77
        FETCH cur1 INTO $cid, $cname, $amt;
78
    END WHILE;
79
    CLOSE cur1;
80

81
    -- 输出最大差值及对应的两个客户的编码、名称与销售额
82
    IF $max_diff < 0 THEN
83
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '数据校验失败：指定年份的销售客户数据不足，无法计算差值！';
84
    ELSE
85
        SELECT
86
            $year AS SalesYear,
87
            $max_diff AS MaxAdjacentDiff,
88
            $max_cid_1 AS CustomerId_1,
89
            $max_cname_1 AS CustomerName_1,
90
            $max_amt_1 AS Amount_1,
91
            $max_cid_2 AS CustomerId_2,
92
            $max_cname_2 AS CustomerName_2,
93
            $max_amt_2 AS Amount_2;
94
    END IF;
95
END $$
96
DELIMITER ;