Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

-- 创建表 CREATE TABLE 用户表 ( 用户id INT PRIMARY KEY AUTOINCREMENT, 用户账号 VARCHAR(50) NOT NULL,…

  1. User

    -- 创建表 CREATE TABLE 用户表 ( 用户id INT PRIMARY KEY AUTO_INCREMENT, 用户账号 VARCHAR(50) NOT NULL, 用户名 VARCHAR(50) NOT NULL, 用户密码 VARCHAR(100) NOT NULL, 地址 VARCHAR(200), 电话号码 VARCHAR(15) ); ALTER TABLE 用户表 ADD 角色 ENUM('admin', 'manager', 'customer') NOT NULL DEFAULT 'customer';

    CREATE TABLE 产品表 ( 书id INT PRIMARY KEY AUTO_INCREMENT, 书名 VARCHAR(100) NOT NULL, 作者 VARCHAR(100) NOT NULL, 出版社 VARCHAR(100) NOT NULL, 图书类型 VARCHAR(50), 库存 INT DEFAULT 0, 单价 DECIMAL(10, 2), 上架时间 DATE );

    CREATE TABLE 图书类型表 ( 图书类型 VARCHAR(50) PRIMARY KEY );

    CREATE TABLE 购物车表 ( 用户id INT, 书id INT, 书名 VARCHAR(100), 作者 VARCHAR(100), 出版社 VARCHAR(100), 购买数量 INT, 价格 DECIMAL(10, 2), PRIMARY KEY (用户id, 书id), FOREIGN KEY (用户id) REFERENCES 用户表(用户id), FOREIGN KEY (书id) REFERENCES 产品表(书id) );

    CREATE TABLE 商家订单表 ( 书id INT, 书名 VARCHAR(100), 购买数量 INT, 买家的用户id INT, 买家的用户名 VARCHAR(50), 买家的地址 VARCHAR(200), 买家的电话号码 VARCHAR(15), 书籍总价 DECIMAL(10, 2), FOREIGN KEY (书id) REFERENCES 产品表(书id), FOREIGN KEY (买家的用户id) REFERENCES 用户表(用户id) );

    CREATE TABLE 购买评论 ( 书id INT, 买家的用户id INT, 买家的用户名 VARCHAR(50), 买家的评论 TEXT, 买家的评分 TINYINT CHECK (买家的评分 BETWEEN 1 AND 5), FOREIGN KEY (书id) REFERENCES 产品表(书id), FOREIGN KEY (买家的用户id) REFERENCES 用户表(用户id) );

    -- 插入用户表 INSERT INTO 用户表 (用户账号, 用户名, 用户密码, 地址, 电话号码, 角色) VALUES ('user1', '张三', 'password123', '北京市海淀区', '13800000001', 'customer'), ('user2', '李四', 'password123', '上海市浦东新区', '13800000002', 'customer'), ('user3', '王五', 'password123', '广州市天河区', '13800000003', 'customer'), ('admin1', '管理员1', 'adminpass', '系统地址', '00000000000', 'admin'), ('manager1', '经理1', 'managerpass', '公司地址', '11111111111', 'manager'), ('user4', '赵六', 'password123', '深圳市南山区', '13800000004', 'customer'), ('user5', '钱七', 'password123', '杭州市西湖区', '13800000005', 'customer'), ('user6', '孙八', 'password123', '武汉市洪山区', '13800000006', 'customer'), ('user7', '周九', 'password123', '成都市武侯区', '13800000007', 'customer'), ('user8', '吴十', 'password123', '南京市玄武区', '13800000008', 'customer');

    -- 授予权限 GRANT CREATE USER ON . TO 'your_user'@'localhost'; FLUSH PRIVILEGES;

    -- 权限分配 DELIMITER $$

    CREATE PROCEDURE AssignRolePermissions() BEGIN DECLARE done INT DEFAULT 0; DECLARE userAccount VARCHAR(50); DECLARE userRole ENUM('admin', 'manager', 'customer'); DECLARE cur CURSOR FOR SELECT 用户账号, 角色 FROM 用户表; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO userAccount, userRole;
        IF NOT done THEN
            -- 检查用户是否已存在
            SET @check_user_sql = CONCAT('SELECT COUNT(*) INTO @user_exists FROM mysql.user WHERE User = \'', userAccount, '\' AND Host = \'localhost\';');
            PREPARE stmt FROM @check_user_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
    
            -- 如果用户不存在,创建新用户
            IF @user_exists = 0 THEN
                SET @create_sql = CONCAT('CREATE USER \'', userAccount, '\'@\'localhost\' IDENTIFIED BY \'password123\';');
                PREPARE stmt FROM @create_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
    
            -- 只分配权限,创建用户过程在外部进行
            IF userRole = 'admin' THEN
                SET @grant_sql = CONCAT('GRANT ALL PRIVILEGES ON *.* TO \'', userAccount, '\'@\'localhost\' WITH GRANT OPTION;');
                PREPARE stmt FROM @grant_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
            ELSEIF userRole = 'manager' THEN
                SET @grant_sql1 = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON 产品表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql1;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql2 = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql2;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql3 = CONCAT('GRANT SELECT ON 用户表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql3;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
            ELSEIF userRole = 'customer' THEN
                SET @grant_sql1 = CONCAT('GRANT SELECT ON 产品表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql1;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql2 = CONCAT('GRANT INSERT ON 购物车表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql2;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql3 = CONCAT('GRANT SELECT ON 购物车表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql3;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql4 = CONCAT('GRANT SELECT ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql4;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql5 = CONCAT('GRANT INSERT ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql5;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;
    UNTIL done END REPEAT;
    
    CLOSE cur;
    FLUSH PRIVILEGES;
    

    END$$

    DELIMITER ;

    -- 调用存储过程 CALL AssignRolePermissions();

    -- 插入产品表 INSERT INTO 产品表 (书名, 作者, 出版社, 图书类型, 库存, 单价, 上架时间) VALUES ('计算机基础', '作者A', '出版社X', '计算机', 100, 50.00, '2023-01-01'), ('高等数学', '作者B', '出版社Y', '数学', 80, 30.00, '2023-02-01'), ('物理学原理', '作者C', '出版社Z', '物理', 50, 60.00, '2023-03-01'), ('化学基础', '作者D', '出版社X', '化学', 70, 40.00, '2023-04-01'), ('英语语法', '作者E', '出版社Y', '语言', 60, 35.00, '2023-05-01'), ('文学概论', '作者F', '出版社Z', '文学', 40, 45.00, '2023-06-01'), ('历史探索', '作者G', '出版社X', '历史', 30, 55.00, '2023-07-01'), ('心理学入门', '作者H', '出版社Y', '心理学', 90, 65.00, '2023-08-01'), ('编程入门', '作者I', '出版社Z', '计算机', 120, 75.00, '2023-09-01'), ('数据科学', '作者J', '出版社X', '计算机', 110, 85.00, '2023-10-01');

    -- 插入购物车表 INSERT INTO 购物车表 (用户id, 书id, 书名, 作者, 出版社, 购买数量, 价格) VALUES (1, 1, '计算机基础', '作者A', '出版社X', 2, 50.00), (2, 2, '高等数学', '作者B', '出版社Y', 1, 30.00), (3, 3, '物理学原理', '作者C', '出版社Z', 3, 60.00), (1, 4, '化学基础', '作者D', '出版社X', 1, 40.00), (2, 5, '英语语法', '作者E', '出版社Y', 2, 35.00), (3, 6, '文学概论', '作者F', '出版社Z', 1, 45.00), (4, 7, '历史探索', '作者G', '出版社X', 1, 55.00), (5, 8, '心理学入门', '作者H', '出版社Y', 1, 65.00), (6, 9, '编程入门', '作者I', '出版社Z', 2, 75.00), (7, 10, '数据科学', '作者J', '出版社X', 1, 85.00);

    -- 插入商家订单表 INSERT INTO 商家订单表 (书id, 书名, 购买数量, 买家的用户id, 买家的用户名, 买家的地址, 买家的电话号码, 书籍总价) VALUES (1, '计算机基础', 1, 1, '张三', '北京市海淀区', '13800000001', 50.00), (2, '高等数学', 2, 2, '李四', '上海市浦东新区', '13800000002', 60.00), (3, '物理学原理', 1, 3, '王五', '广州市天河区', '13800000003', 60.00), (4, '化学基础', 1, 1, '张三', '北京市海淀区', '13800000001', 40.00), (5, '英语语法', 2, 2, '李四', '上海市浦东新区', '13800000002', 70.00), (6, '文学概论', 1, 3, '王五', '广州市天河区', '13800000003', 45.00), (7, '历史探索', 1, 4, '赵六', '深圳市南山区', '13800000004', 55.00), (8, '心理学入门', 1, 5, '钱七', '杭州市西湖区', '13800000005', 65.00), (9, '编程入门', 1, 6, '孙八', '武汉市洪山区', '13800000006', 75.00), (10, '数据科学', 2, 7, '周九', '成都市武侯区', '13800000007', 170.00);

    -- 插入购买评论表 INSERT INTO 购买评论 (书id, 买家的用户id, 买家的用户名, 买家的评论, 买家的评分) VALUES (1, 1, '张三', '非常好!', 5), (2, 2, '李四', '不错', 4), (3, 3, '王五', '一般般', 3), (4, 1, '张三', '内容丰富', 5), (5, 2, '李四', '值得推荐', 4), (6, 3, '王五', '没有想象中好', 3), (7, 4, '赵六', '还行', 4), (8, 5, '钱七', '非常专业', 5), (9, 6, '孙八', '很实用', 5), (10, 7, '周九', '不错的书籍', 4);

    -- 创建视图 CREATE VIEW 高评分图书畅销排行榜 AS SELECT p.书id, p.书名, p.作者, p.出版社, AVG(c.买家的评分) AS 平均评分 FROM 产品表 p JOIN 购买评论 c ON p.书id = c.书id GROUP BY p.书id, p.书名, p.作者, p.出版社 HAVING AVG(c.买家的评分) >= 4 ORDER BY 平均评分 DESC;

    CREATE VIEW 购物记录 AS SELECT c.用户id, c.书名, c.作者, c.出版社, c.购买数量, c.价格, u.地址, u.电话号码 FROM 购物车表 c JOIN 用户表 u ON c.用户id = u.用户id;

    CREATE VIEW 图书库存销售统计 AS SELECT p.书id, p.书名, p.库存, IFNULL(SUM(o.购买数量), 0) AS 销售数量 FROM 产品表 p LEFT JOIN 商家订单表 o ON p.书id = o.书id GROUP BY p.书id, p.书名;

    -- 更改分隔符 DELIMITER //

    -- 存储过程1:加入购物车 CREATE PROCEDURE 加入购物车(IN 用户 INT, IN 书 INT, IN 数量 INT) BEGIN IF EXISTS (SELECT * FROM 购物车表 WHERE 用户id = 用户 AND 书id = 书) THEN UPDATE 购物车表 SET 购买数量 = 购买数量 + 数量 WHERE 用户id = 用户 AND 书id = 书; ELSE INSERT INTO 购物车表 (用户id, 书id, 购买数量) VALUES (用户, 书, 数量); END IF; END //

    -- 存储过程2:创建订单 CREATE PROCEDURE 创建订单(IN 用户 INT) BEGIN INSERT INTO 商家订单表 (书id, 书名, 购买数量, 买家的用户id, 买家的用户名, 买家的地址, 买家的电话号码, 书籍总价) SELECT c.书id, c.书名, c.购买数量, u.用户id, u.用户名, u.地址, u.电话号码, c.购买数量 * c.价格 FROM 购物车表 c JOIN 用户表 u ON c.用户id = u.用户id WHERE c.用户id = 用户;

    DELETE FROM 购物车表 WHERE 用户id = 用户;
    

    END //

    -- 存储过程3:删除购物车条目 CREATE PROCEDURE 删除购物车条目(IN 用户 INT, IN 书 INT) BEGIN DELETE FROM 购物车表 WHERE 用户id = 用户 AND 书id = 书; END //

    -- 存储过程4:添加新书籍 CREATE PROCEDURE 添加新书籍(IN 书名 VARCHAR(100), IN 作者 VARCHAR(100), IN 出版社 VARCHAR(100), IN 类型 VARCHAR(50), IN 库存量 INT, IN 价格 DECIMAL(10,2), IN 上架日期 DATE) BEGIN INSERT INTO 产品表 (书名, 作者, 出版社, 图书类型, 库存, 单价, 上架时间) VALUES (书名, 作者, 出版社, 类型, 库存量, 价格, 上架日期); END //

    -- 存储过程5:计算用户评分平均值 CREATE PROCEDURE 计算平均评分(IN 书 INT, OUT 平均评分 DECIMAL(3,2)) BEGIN SELECT AVG(买家的评分) INTO 平均评分 FROM 购买评论 WHERE 书id = 书; END //

    -- 存储过程6:查看用户购物车内容 CREATE PROCEDURE 查看购物车(IN 用户 INT) BEGIN SELECT * FROM 购物车表 WHERE 用户id = 用户; END //

    -- 存储过程7:查询购买历史记录 CREATE PROCEDURE 查询购买历史记录(IN 用户 INT) BEGIN SELECT * FROM 商家订单表 WHERE 买家的用户id = 用户; END //

    -- 存储过程8:统计图书类型销量 CREATE PROCEDURE 统计图书类型销量() BEGIN SELECT p.图书类型, SUM(o.购买数量) AS 总销量 FROM 产品表 p JOIN 商家订单表 o ON p.书id = o.书id GROUP BY p.图书类型; END //

    -- 存储过程9:批量更新书籍价格 CREATE PROCEDURE 批量更新价格(IN 类型 VARCHAR(50), IN 调整比例 DECIMAL(4,2)) BEGIN UPDATE 产品表 SET 单价 = 单价 * 调整比例 WHERE 图书类型 = 类型; END //

    -- 恢复分隔符 DELIMITER ;

    -- 创建触发器 DELIMITER //

    -- 触发器1:更新库存 CREATE TRIGGER 更新库存 AFTER INSERT ON 商家订单表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 - NEW.购买数量 WHERE 书id = NEW.书id; END //

    -- 触发器2:删除购物车条目时恢复库存 CREATE TRIGGER 恢复库存 AFTER DELETE ON 购物车表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 + OLD.购买数量 WHERE 书id = OLD.书id; END //

    -- 触发器3:自动更新高评分排行榜 CREATE TRIGGER 更新排行榜 AFTER INSERT ON 购买评论 FOR EACH ROW BEGIN CALL 计算平均评分(NEW.书id, @平均评分); IF @平均评分 >= 4 THEN REPLACE INTO 高评分图书畅销排行榜 (书id, 书名, 作者, 出版社, 平均评分) SELECT 书id, 书名, 作者, 出版社, @平均评分 FROM 产品表 WHERE 书id = NEW.书id; END IF; END //

    -- 触发器4:自动更新书籍平均评分 CREATE TRIGGER 更新平均评分 AFTER INSERT ON 购买评论 FOR EACH ROW BEGIN UPDATE 产品表 SET 平均评分 = (SELECT AVG(买家的评分) FROM 购买评论 WHERE 书id = NEW.书id) WHERE 书id = NEW.书id; END //

    -- 触发器5:删除订单时恢复库存 CREATE TRIGGER 删除订单恢复库存 AFTER DELETE ON 商家订单表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 + OLD.购买数量 WHERE 书id = OLD.书id; END //

    -- 恢复分隔符 DELIMITER ; 数据库设计说明书 1.引言 1.1编写目的 [说明编写这份数据设计说明书的目的,指出预期的读者。] 1.2背景 a.[待开发数据库的名称和使用此数据库的软件系统的名称;] b.[列出本项目的任务提出者、开发者、用户。] 1.3定义 [列出本文件中用到的专门术语的定义和外文首字母组词的原词组。] 1.4参考资料 [列出有关的参考资料。] 2.外部设计 1.1标识符的状态 [联系用途,详细说明用于唯一地标识该数据库的代码、名称或标识符,附加的描述性信息亦要给出。如果该数据库属于尚在实验中、尚在测试中或是暂时使用的,则要说明这一特点及其有效时间范围。] 2.2使用它的程序 [列出将要使用或访问此数据库的所有应用程序,对于这些应用程序的每一个,给出它的名称和版本号。] 2.3约定 [陈述一个程序员或一个系统分析员为了能使用此数据库而需要了解的建立标号、标识的约定。] 2.4专门指导 [向准备从事此数据库的生成、从事此数据库的测试、维护人员提供专门的指导。] 2.5支持软件 [简单介绍同此数据库直接有关的支持软件。说明这些软件的名称、版本号的主要功能特性。列出这些支持软件的技术文件的标题、编号及来源]

    3.结构设计 3.1概念结构设计 [说明本数据库将反映的现实世界中的实体、属性和它们之间的关系等的原始数据形式,包括各数据项、记录、系、文卷的标识符、定义、类型、度量单位和值域,建立本数据库的每一幅用户视图。]

    3.2逻辑结构设计 [说明把上述原始数据进行分解、合并后重新组织起来的数据库全局逻辑结构。] 3.3物理结构设计 [建立系统程序员视图。] 4.运用设计 4.1数据字典设计学习是成就事业的基石 [对数据库设计中涉及到的各种项目一般要建立起数据字典,以说明它们的标识符、同义名及有关信息。] 4.2安全保密设计 [说明在数据库的设计中,将如何通过区分不同的访问者、不同的访问类型和不同的数据对象,进行分别对待而获得的数据库安全保密的设计考虑。]

    根据上面的提示和sql语句,写一份数据库设计说明书

  2. Assistant

    数据库设计说明书

    1. 引言

    1.1 编写目的

    本数据库设计说明书旨在清晰地定义数据库的结构、功能及相关约定,作为开发人员、数据库管理员及未来维护人员的参考资料。

    1.2 背景

    a. 数据库名称:图书管理系统数据库
    使用此数据库的软件系统:图书管理系统

    b. 项目任务提出者:张三
    开发者:李四、王五
    用户:图书馆管理员、读者

    1.3 定义

    • 用户表:存储用户的基本信息,包括账号、密码等。
    • 产品表:存储图书的详细信息。
    • 购物车表:存储用户已选购的图书信息。
    • 商家订单表:存储用户的购买订单。
    • 购买评论:存储用户对图书的评价及评分。
    • 图书类型表:存储图书的类别。

    1.4 参考资料

    • MySQL官方文档
    • 数据库设计最佳实践指南

    2. 外部设计

    2.1 标识符的状态

    该数据库为正式上线状态,拥有唯一的数据库名称“图书管理系统数据库”。数据库将长期使用,支持图书馆的运营。

    2.2 使用它的程序

    • 图书管理系统 (版本1.0)
    • 用户管理系统 (版本1.0)

    2.3 约定

    • 用户id、书id等标识符均为整数类型,作为主键标识。
    • 所有字符串类型的字段均以UTF-8编码存储。
    • 角色字段保留‘admin’, ‘manager’, ‘customer’三种权限角色。

    2.4 专门指导

    • 数据库管理员需定期备份数据库,确保数据安全。
    • 对于涉及用户数据的表,需加强权限管理,确保信息安全。

    2.5 支持软件

    • MySQL 数据库管理系统 (版本8.0)
    • 数据备份工具 (版本1.1)

    3. 结构设计

    3.1 概念结构设计

    数据库包括以下基本实体及其属性:

    1. 用户表

      • 用户id (INT, 主键)
      • 用户账号 (VARCHAR(50), 唯一,非空)
      • 用户名 (VARCHAR(50), 非空)
      • 用户密码 (VARCHAR(100), 非空)
      • 地址 (VARCHAR(200))
      • 电话号码 (VARCHAR(15))
      • 角色 (ENUM)
    2. 产品表

      • 书id (INT, 主键)
      • 书名 (VARCHAR(100), 非空)
      • 作者 (VARCHAR(100), 非空)
      • 出版社 (VARCHAR(100), 非空)
      • 图书类型 (VARCHAR(50))
      • 库存 (INT)
      • 单价 (DECIMAL(10, 2))
      • 上架时间 (DATE)
    3. 购物车表

      • 用户id (INT, 外键)
      • 书id (INT, 外键)
      • 书名 (VARCHAR(100), 非空)
      • 作者 (VARCHAR(100), 非空)
      • 出版社 (VARCHAR(100), 非空)
      • 购买数量 (INT)
      • 价格 (DECIMAL(10, 2))
    4. 商家订单表

      • 书id (INT, 外键)
      • 买家的用户id (INT, 外键)
      • 买家的用户名 (VARCHAR(50))
      • 买家的地址 (VARCHAR(200))
      • 买家的电话号码 (VARCHAR(15))
      • 书籍总价 (DECIMAL(10, 2))
    5. 购买评论

      • 书id (INT, 外键)
      • 买家的用户id (INT, 外键)
      • 买家的用户名 (VARCHAR(50))
      • 买家的评论 (TEXT)
      • 买家的评分 (TINYINT)

    3.2 逻辑结构设计

    逻辑结构将上述实体进行归类和关系连接,形成关系模型。用户与订单一对多,用户与购物车一对多,订单与图书一对多,图书与评论一对多。

    3.3 物理结构设计

    物理设计充分考虑数据库性能,合理选择索引,对主键及关键字段添加索引,以提高查询效率。

    4. 运用设计

    4.1 数据字典设计

    表名字段名数据类型描述
    用户表用户idINT用户的唯一标识
    用户表用户账号VARCHAR(50)用户登录账号
    产品表书idINT书籍的唯一标识
    产品表书名VARCHAR(100)书籍名称
    购物车表购买数量INT购物车中该书籍的数量
    商家订单表书籍总价DECIMAL(10, 2)购买该书籍的总价格

    4.2 安全保密设计

    • 通过角色管理机制对不同用户执行不同的访问权限,确保用户数据与操作的安全性。
    • 对于敏感字段如用户密码,使用加密存储。
    • 定期审计用户访问记录,监控潜在的安全隐患。

    以上为“图书管理系统数据库”的设计说明书,后续如需修改或扩展请及时更新。

  3. User

    -- 创建表 CREATE TABLE 用户表 ( 用户id INT PRIMARY KEY AUTO_INCREMENT, 用户账号 VARCHAR(50) NOT NULL, 用户名 VARCHAR(50) NOT NULL, 用户密码 VARCHAR(100) NOT NULL, 地址 VARCHAR(200), 电话号码 VARCHAR(15) ); ALTER TABLE 用户表 ADD 角色 ENUM('admin', 'manager', 'customer') NOT NULL DEFAULT 'customer';

    CREATE TABLE 产品表 ( 书id INT PRIMARY KEY AUTO_INCREMENT, 书名 VARCHAR(100) NOT NULL, 作者 VARCHAR(100) NOT NULL, 出版社 VARCHAR(100) NOT NULL, 图书类型 VARCHAR(50), 库存 INT DEFAULT 0, 单价 DECIMAL(10, 2), 上架时间 DATE );

    CREATE TABLE 图书类型表 ( 图书类型 VARCHAR(50) PRIMARY KEY );

    CREATE TABLE 购物车表 ( 用户id INT, 书id INT, 书名 VARCHAR(100), 作者 VARCHAR(100), 出版社 VARCHAR(100), 购买数量 INT, 价格 DECIMAL(10, 2), PRIMARY KEY (用户id, 书id), FOREIGN KEY (用户id) REFERENCES 用户表(用户id), FOREIGN KEY (书id) REFERENCES 产品表(书id) );

    CREATE TABLE 商家订单表 ( 书id INT, 书名 VARCHAR(100), 购买数量 INT, 买家的用户id INT, 买家的用户名 VARCHAR(50), 买家的地址 VARCHAR(200), 买家的电话号码 VARCHAR(15), 书籍总价 DECIMAL(10, 2), FOREIGN KEY (书id) REFERENCES 产品表(书id), FOREIGN KEY (买家的用户id) REFERENCES 用户表(用户id) );

    CREATE TABLE 购买评论 ( 书id INT, 买家的用户id INT, 买家的用户名 VARCHAR(50), 买家的评论 TEXT, 买家的评分 TINYINT CHECK (买家的评分 BETWEEN 1 AND 5), FOREIGN KEY (书id) REFERENCES 产品表(书id), FOREIGN KEY (买家的用户id) REFERENCES 用户表(用户id) );

    -- 插入用户表 INSERT INTO 用户表 (用户账号, 用户名, 用户密码, 地址, 电话号码, 角色) VALUES ('user1', '张三', 'password123', '北京市海淀区', '13800000001', 'customer'), ('user2', '李四', 'password123', '上海市浦东新区', '13800000002', 'customer'), ('user3', '王五', 'password123', '广州市天河区', '13800000003', 'customer'), ('admin1', '管理员1', 'adminpass', '系统地址', '00000000000', 'admin'), ('manager1', '经理1', 'managerpass', '公司地址', '11111111111', 'manager'), ('user4', '赵六', 'password123', '深圳市南山区', '13800000004', 'customer'), ('user5', '钱七', 'password123', '杭州市西湖区', '13800000005', 'customer'), ('user6', '孙八', 'password123', '武汉市洪山区', '13800000006', 'customer'), ('user7', '周九', 'password123', '成都市武侯区', '13800000007', 'customer'), ('user8', '吴十', 'password123', '南京市玄武区', '13800000008', 'customer');

    -- 授予权限 GRANT CREATE USER ON . TO 'your_user'@'localhost'; FLUSH PRIVILEGES;

    -- 权限分配 DELIMITER $$

    CREATE PROCEDURE AssignRolePermissions() BEGIN DECLARE done INT DEFAULT 0; DECLARE userAccount VARCHAR(50); DECLARE userRole ENUM('admin', 'manager', 'customer'); DECLARE cur CURSOR FOR SELECT 用户账号, 角色 FROM 用户表; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO userAccount, userRole;
        IF NOT done THEN
            -- 检查用户是否已存在
            SET @check_user_sql = CONCAT('SELECT COUNT(*) INTO @user_exists FROM mysql.user WHERE User = \'', userAccount, '\' AND Host = \'localhost\';');
            PREPARE stmt FROM @check_user_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
    
            -- 如果用户不存在,创建新用户
            IF @user_exists = 0 THEN
                SET @create_sql = CONCAT('CREATE USER \'', userAccount, '\'@\'localhost\' IDENTIFIED BY \'password123\';');
                PREPARE stmt FROM @create_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
    
            -- 只分配权限,创建用户过程在外部进行
            IF userRole = 'admin' THEN
                SET @grant_sql = CONCAT('GRANT ALL PRIVILEGES ON *.* TO \'', userAccount, '\'@\'localhost\' WITH GRANT OPTION;');
                PREPARE stmt FROM @grant_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
            ELSEIF userRole = 'manager' THEN
                SET @grant_sql1 = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON 产品表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql1;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql2 = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql2;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql3 = CONCAT('GRANT SELECT ON 用户表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql3;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
            ELSEIF userRole = 'customer' THEN
                SET @grant_sql1 = CONCAT('GRANT SELECT ON 产品表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql1;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql2 = CONCAT('GRANT INSERT ON 购物车表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql2;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql3 = CONCAT('GRANT SELECT ON 购物车表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql3;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql4 = CONCAT('GRANT SELECT ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql4;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql5 = CONCAT('GRANT INSERT ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql5;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;
    UNTIL done END REPEAT;
    
    CLOSE cur;
    FLUSH PRIVILEGES;
    

    END$$

    DELIMITER ;

    -- 调用存储过程 CALL AssignRolePermissions();

    -- 插入产品表 INSERT INTO 产品表 (书名, 作者, 出版社, 图书类型, 库存, 单价, 上架时间) VALUES ('计算机基础', '作者A', '出版社X', '计算机', 100, 50.00, '2023-01-01'), ('高等数学', '作者B', '出版社Y', '数学', 80, 30.00, '2023-02-01'), ('物理学原理', '作者C', '出版社Z', '物理', 50, 60.00, '2023-03-01'), ('化学基础', '作者D', '出版社X', '化学', 70, 40.00, '2023-04-01'), ('英语语法', '作者E', '出版社Y', '语言', 60, 35.00, '2023-05-01'), ('文学概论', '作者F', '出版社Z', '文学', 40, 45.00, '2023-06-01'), ('历史探索', '作者G', '出版社X', '历史', 30, 55.00, '2023-07-01'), ('心理学入门', '作者H', '出版社Y', '心理学', 90, 65.00, '2023-08-01'), ('编程入门', '作者I', '出版社Z', '计算机', 120, 75.00, '2023-09-01'), ('数据科学', '作者J', '出版社X', '计算机', 110, 85.00, '2023-10-01');

    -- 插入购物车表 INSERT INTO 购物车表 (用户id, 书id, 书名, 作者, 出版社, 购买数量, 价格) VALUES (1, 1, '计算机基础', '作者A', '出版社X', 2, 50.00), (2, 2, '高等数学', '作者B', '出版社Y', 1, 30.00), (3, 3, '物理学原理', '作者C', '出版社Z', 3, 60.00), (1, 4, '化学基础', '作者D', '出版社X', 1, 40.00), (2, 5, '英语语法', '作者E', '出版社Y', 2, 35.00), (3, 6, '文学概论', '作者F', '出版社Z', 1, 45.00), (4, 7, '历史探索', '作者G', '出版社X', 1, 55.00), (5, 8, '心理学入门', '作者H', '出版社Y', 1, 65.00), (6, 9, '编程入门', '作者I', '出版社Z', 2, 75.00), (7, 10, '数据科学', '作者J', '出版社X', 1, 85.00);

    -- 插入商家订单表 INSERT INTO 商家订单表 (书id, 书名, 购买数量, 买家的用户id, 买家的用户名, 买家的地址, 买家的电话号码, 书籍总价) VALUES (1, '计算机基础', 1, 1, '张三', '北京市海淀区', '13800000001', 50.00), (2, '高等数学', 2, 2, '李四', '上海市浦东新区', '13800000002', 60.00), (3, '物理学原理', 1, 3, '王五', '广州市天河区', '13800000003', 60.00), (4, '化学基础', 1, 1, '张三', '北京市海淀区', '13800000001', 40.00), (5, '英语语法', 2, 2, '李四', '上海市浦东新区', '13800000002', 70.00), (6, '文学概论', 1, 3, '王五', '广州市天河区', '13800000003', 45.00), (7, '历史探索', 1, 4, '赵六', '深圳市南山区', '13800000004', 55.00), (8, '心理学入门', 1, 5, '钱七', '杭州市西湖区', '13800000005', 65.00), (9, '编程入门', 1, 6, '孙八', '武汉市洪山区', '13800000006', 75.00), (10, '数据科学', 2, 7, '周九', '成都市武侯区', '13800000007', 170.00);

    -- 插入购买评论表 INSERT INTO 购买评论 (书id, 买家的用户id, 买家的用户名, 买家的评论, 买家的评分) VALUES (1, 1, '张三', '非常好!', 5), (2, 2, '李四', '不错', 4), (3, 3, '王五', '一般般', 3), (4, 1, '张三', '内容丰富', 5), (5, 2, '李四', '值得推荐', 4), (6, 3, '王五', '没有想象中好', 3), (7, 4, '赵六', '还行', 4), (8, 5, '钱七', '非常专业', 5), (9, 6, '孙八', '很实用', 5), (10, 7, '周九', '不错的书籍', 4);

    -- 创建视图 CREATE VIEW 高评分图书畅销排行榜 AS SELECT p.书id, p.书名, p.作者, p.出版社, AVG(c.买家的评分) AS 平均评分 FROM 产品表 p JOIN 购买评论 c ON p.书id = c.书id GROUP BY p.书id, p.书名, p.作者, p.出版社 HAVING AVG(c.买家的评分) >= 4 ORDER BY 平均评分 DESC;

    CREATE VIEW 购物记录 AS SELECT c.用户id, c.书名, c.作者, c.出版社, c.购买数量, c.价格, u.地址, u.电话号码 FROM 购物车表 c JOIN 用户表 u ON c.用户id = u.用户id;

    CREATE VIEW 图书库存销售统计 AS SELECT p.书id, p.书名, p.库存, IFNULL(SUM(o.购买数量), 0) AS 销售数量 FROM 产品表 p LEFT JOIN 商家订单表 o ON p.书id = o.书id GROUP BY p.书id, p.书名;

    -- 更改分隔符 DELIMITER //

    -- 存储过程1:加入购物车 CREATE PROCEDURE 加入购物车(IN 用户 INT, IN 书 INT, IN 数量 INT) BEGIN IF EXISTS (SELECT * FROM 购物车表 WHERE 用户id = 用户 AND 书id = 书) THEN UPDATE 购物车表 SET 购买数量 = 购买数量 + 数量 WHERE 用户id = 用户 AND 书id = 书; ELSE INSERT INTO 购物车表 (用户id, 书id, 购买数量) VALUES (用户, 书, 数量); END IF; END //

    -- 存储过程2:创建订单 CREATE PROCEDURE 创建订单(IN 用户 INT) BEGIN INSERT INTO 商家订单表 (书id, 书名, 购买数量, 买家的用户id, 买家的用户名, 买家的地址, 买家的电话号码, 书籍总价) SELECT c.书id, c.书名, c.购买数量, u.用户id, u.用户名, u.地址, u.电话号码, c.购买数量 * c.价格 FROM 购物车表 c JOIN 用户表 u ON c.用户id = u.用户id WHERE c.用户id = 用户;

    DELETE FROM 购物车表 WHERE 用户id = 用户;
    

    END //

    -- 存储过程3:删除购物车条目 CREATE PROCEDURE 删除购物车条目(IN 用户 INT, IN 书 INT) BEGIN DELETE FROM 购物车表 WHERE 用户id = 用户 AND 书id = 书; END //

    -- 存储过程4:添加新书籍 CREATE PROCEDURE 添加新书籍(IN 书名 VARCHAR(100), IN 作者 VARCHAR(100), IN 出版社 VARCHAR(100), IN 类型 VARCHAR(50), IN 库存量 INT, IN 价格 DECIMAL(10,2), IN 上架日期 DATE) BEGIN INSERT INTO 产品表 (书名, 作者, 出版社, 图书类型, 库存, 单价, 上架时间) VALUES (书名, 作者, 出版社, 类型, 库存量, 价格, 上架日期); END //

    -- 存储过程5:计算用户评分平均值 CREATE PROCEDURE 计算平均评分(IN 书 INT, OUT 平均评分 DECIMAL(3,2)) BEGIN SELECT AVG(买家的评分) INTO 平均评分 FROM 购买评论 WHERE 书id = 书; END //

    -- 存储过程6:查看用户购物车内容 CREATE PROCEDURE 查看购物车(IN 用户 INT) BEGIN SELECT * FROM 购物车表 WHERE 用户id = 用户; END //

    -- 存储过程7:查询购买历史记录 CREATE PROCEDURE 查询购买历史记录(IN 用户 INT) BEGIN SELECT * FROM 商家订单表 WHERE 买家的用户id = 用户; END //

    -- 存储过程8:统计图书类型销量 CREATE PROCEDURE 统计图书类型销量() BEGIN SELECT p.图书类型, SUM(o.购买数量) AS 总销量 FROM 产品表 p JOIN 商家订单表 o ON p.书id = o.书id GROUP BY p.图书类型; END //

    -- 存储过程9:批量更新书籍价格 CREATE PROCEDURE 批量更新价格(IN 类型 VARCHAR(50), IN 调整比例 DECIMAL(4,2)) BEGIN UPDATE 产品表 SET 单价 = 单价 * 调整比例 WHERE 图书类型 = 类型; END //

    -- 恢复分隔符 DELIMITER ;

    -- 创建触发器 DELIMITER //

    -- 触发器1:更新库存 CREATE TRIGGER 更新库存 AFTER INSERT ON 商家订单表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 - NEW.购买数量 WHERE 书id = NEW.书id; END //

    -- 触发器2:删除购物车条目时恢复库存 CREATE TRIGGER 恢复库存 AFTER DELETE ON 购物车表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 + OLD.购买数量 WHERE 书id = OLD.书id; END //

    -- 触发器3:自动更新高评分排行榜 CREATE TRIGGER 更新排行榜 AFTER INSERT ON 购买评论 FOR EACH ROW BEGIN CALL 计算平均评分(NEW.书id, @平均评分); IF @平均评分 >= 4 THEN REPLACE INTO 高评分图书畅销排行榜 (书id, 书名, 作者, 出版社, 平均评分) SELECT 书id, 书名, 作者, 出版社, @平均评分 FROM 产品表 WHERE 书id = NEW.书id; END IF; END //

    -- 触发器4:自动更新书籍平均评分 CREATE TRIGGER 更新平均评分 AFTER INSERT ON 购买评论 FOR EACH ROW BEGIN UPDATE 产品表 SET 平均评分 = (SELECT AVG(买家的评分) FROM 购买评论 WHERE 书id = NEW.书id) WHERE 书id = NEW.书id; END //

    -- 触发器5:删除订单时恢复库存 CREATE TRIGGER 删除订单恢复库存 AFTER DELETE ON 商家订单表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 + OLD.购买数量 WHERE 书id = OLD.书id; END //

    -- 恢复分隔符 DELIMITER ;

    数据库设计说明书 1.引言 1.1编写目的 [说明编写这份数据设计说明书的目的,指出预期的读者。] 1.2背景 a.[待开发数据库的名称和使用此数据库的软件系统的名称;] b.[列出本项目的任务提出者、开发者、用户。] 1.3定义 [列出本文件中用到的专门术语的定义和外文首字母组词的原词组。] 1.4参考资料 [列出有关的参考资料。] 2.外部设计 1.1标识符的状态 [联系用途,详细说明用于唯一地标识该数据库的代码、名称或标识符,附加的描述性信息亦要给出。如果该数据库属于尚在实验中、尚在测试中或是暂时使用的,则要说明这一特点及其有效时间范围。] 2.2使用它的程序 [列出将要使用或访问此数据库的所有应用程序,对于这些应用程序的每一个,给出它的名称和版本号。] 2.3约定 [陈述一个程序员或一个系统分析员为了能使用此数据库而需要了解的建立标号、标识的约定。] 2.4专门指导 [向准备从事此数据库的生成、从事此数据库的测试、维护人员提供专门的指导。] 2.5支持软件 [简单介绍同此数据库直接有关的支持软件。说明这些软件的名称、版本号的主要功能特性。列出这些支持软件的技术文件的标题、编号及来源]

    3.结构设计 3.1概念结构设计 [说明本数据库将反映的现实世界中的实体、属性和它们之间的关系等的原始数据形式,包括各数据项、记录、系、文卷的标识符、定义、类型、度量单位和值域,建立本数据库的每一幅用户视图。]

    3.2逻辑结构设计 [说明把上述原始数据进行分解、合并后重新组织起来的数据库全局逻辑结构。] 3.3物理结构设计 [建立系统程序员视图。] 4.运用设计 4.1数据字典设计学习是成就事业的基石 [对数据库设计中涉及到的各种项目一般要建立起数据字典,以说明它们的标识符、同义名及有关信息。] 4.2安全保密设计 [说明在数据库的设计中,将如何通过区分不同的访问者、不同的访问类型和不同的数据对象,进行分别对待而获得的数据库安全保密的设计考虑。]

  4. Assistant

    数据库设计说明书

    1. 引言

    1.1 编写目的

    本数据库设计说明书的目的在于详细记录和说明图书管理系统数据库的设计方案,包括其结构、功能、使用及维护注意事项,确保开发人员、数据库管理员和未来的维护人员能够充分理解和有效使用该数据库。

    1.2 背景

    a. 数据库名称:图书管理系统数据库
    使用此数据库的软件系统:图书管理系统

    b. 本项目的任务提出者:张三
    开发者:李四、王五
    用户:图书馆人员、读者

    1.3 定义

    • 用户表:记录用户的基本信息,包括账号、密码及角色。
    • 产品表:记录图书的详细信息,包括书名、作者、出版社等。
    • 购物车表:记录用户选择的图书信息。
    • 商家订单表:记录用户购买的图书订单。
    • 购买评论:记录用户对图书的评价和评分。
    • 图书类型表:记录图书的类型。

    1.4 参考资料

    • MySQL官方文档
    • 数据库设计与管理实务
    • 软件工程实践教程

    2. 外部设计

    2.1 标识符的状态

    数据库的唯一标识符为“图书管理系统数据库”。该数据库为正式状态,将用于日常运营,也将持续更新以满足用户需求。

    2.2 使用它的程序

    • 图书管理系统 (版本1.0)
    • 用户管理系统 (版本1.0)
    • 订单管理系统 (版本1.0)

    2.3 约定

    • 所有用户id和书id为整数类型,作为主键。
    • 所有字符串字段使用UTF-8编码。
    • 角色字段的值限制在 'admin', 'manager', 'customer' 三个选项内。

    2.4 专门指导

    • 数据库管理员需要定期进行数据备份和恢复,确保数据安全。
    • 设计中所有的密码字段需要使用哈希算法进行加密存储,避免明文存储。

    2.5 支持软件

    • MySQL 数据库管理系统 (版本8.0),主要用于数据的存储、查询和管理。
    • 数据备份工具 (版本1.1),用于定期备份数据库的数据。

    3. 结构设计

    3.1 概念结构设计

    本数据库包括以下主要实体及其属性:

    1. 用户表

      • 用户id (INT, 主键)
      • 用户账号 (VARCHAR(50), 非空)
      • 用户名 (VARCHAR(50), 非空)
      • 用户密码 (VARCHAR(100), 非空)
      • 地址 (VARCHAR(200))
      • 电话号码 (VARCHAR(15))
      • 角色 (ENUM)
    2. 产品表

      • 书id (INT, 主键)
      • 书名 (VARCHAR(100), 非空)
      • 作者 (VARCHAR(100), 非空)
      • 出版社 (VARCHAR(100), 非空)
      • 图书类型 (VARCHAR(50))
      • 库存 (INT, 默认0)
      • 单价 (DECIMAL(10, 2))
      • 上架时间 (DATE)
    3. 购物车表

      • 用户id (INT, 外键)
      • 书id (INT, 外键)
      • 书名 (VARCHAR(100), 非空)
      • 作者 (VARCHAR(100), 非空)
      • 出版社 (VARCHAR(100), 非空)
      • 购买数量 (INT)
      • 价格 (DECIMAL(10, 2))
    4. 商家订单表

      • 书id (INT, 外键)
      • 买家的用户id (INT, 外键)
      • 买家的用户名 (VARCHAR(50))
      • 买家的地址 (VARCHAR(200))
      • 买家的电话号码 (VARCHAR(15))
      • 书籍总价 (DECIMAL(10, 2))
    5. 购买评论表

      • 书id (INT, 外键)
      • 买家的用户id (INT, 外键)
      • 买家的用户名 (VARCHAR(50))
      • 买家的评论 (TEXT)
      • 买家的评分 (TINYINT)

    3.2 逻辑结构设计

    逻辑结构按照实体间的关系进行组织。用户与订单形成一对多关系,用户与购物车也形成一对多关系,订单与图书之间为多对多关系,通过商家订单表进行连接。同时,评论表与图书之间构成多对一关系。

    3.3 物理结构设计

    物理结构设计结合性能提高考虑,适当添加索引以提升查询速度。数据存储表设计也遵循范式原则,以避免冗余和更新异议。

    4. 运用设计

    4.1 数据字典设计

    表名字段名数据类型描述
    用户表用户idINT用户的唯一标识
    用户表用户账号VARCHAR(50)用户登录账号
    产品表书idINT书籍的唯一标识
    产品表书名VARCHAR(100)书籍名称
    购买评论表买家的评分TINYINT买家对书籍的评分,范围1-5

    4.2 安全保密设计

    • 对不同角色的访问权限进行严格控制,根据用户角色规定访问权限,确保系统的安全。
    • 所有用户的敏感信息(如密码)采用加密方式存储;使用HTTPS协议进行加密传输。
    • 定期审核和维护数据库的用户访问权限,确保只有必要的用户能够访问敏感数据。

    本数据库设计说明书为“图书管理系统”的初步设计方案,后续如有需要进行变更或升级,请及时更新以保证数据结构的合理性和安全性。

  5. User

    -- 创建表 CREATE TABLE 用户表 ( 用户id INT PRIMARY KEY AUTO_INCREMENT, 用户账号 VARCHAR(50) NOT NULL, 用户名 VARCHAR(50) NOT NULL, 用户密码 VARCHAR(100) NOT NULL, 地址 VARCHAR(200), 电话号码 VARCHAR(15) ); ALTER TABLE 用户表 ADD 角色 ENUM('admin', 'manager', 'customer') NOT NULL DEFAULT 'customer';

    CREATE TABLE 产品表 ( 书id INT PRIMARY KEY AUTO_INCREMENT, 书名 VARCHAR(100) NOT NULL, 作者 VARCHAR(100) NOT NULL, 出版社 VARCHAR(100) NOT NULL, 图书类型 VARCHAR(50), 库存 INT DEFAULT 0, 单价 DECIMAL(10, 2), 上架时间 DATE );

    CREATE TABLE 图书类型表 ( 图书类型 VARCHAR(50) PRIMARY KEY );

    CREATE TABLE 购物车表 ( 用户id INT, 书id INT, 书名 VARCHAR(100), 作者 VARCHAR(100), 出版社 VARCHAR(100), 购买数量 INT, 价格 DECIMAL(10, 2), PRIMARY KEY (用户id, 书id), FOREIGN KEY (用户id) REFERENCES 用户表(用户id), FOREIGN KEY (书id) REFERENCES 产品表(书id) );

    CREATE TABLE 商家订单表 ( 书id INT, 书名 VARCHAR(100), 购买数量 INT, 买家的用户id INT, 买家的用户名 VARCHAR(50), 买家的地址 VARCHAR(200), 买家的电话号码 VARCHAR(15), 书籍总价 DECIMAL(10, 2), FOREIGN KEY (书id) REFERENCES 产品表(书id), FOREIGN KEY (买家的用户id) REFERENCES 用户表(用户id) );

    CREATE TABLE 购买评论 ( 书id INT, 买家的用户id INT, 买家的用户名 VARCHAR(50), 买家的评论 TEXT, 买家的评分 TINYINT CHECK (买家的评分 BETWEEN 1 AND 5), FOREIGN KEY (书id) REFERENCES 产品表(书id), FOREIGN KEY (买家的用户id) REFERENCES 用户表(用户id) );

    -- 插入用户表 INSERT INTO 用户表 (用户账号, 用户名, 用户密码, 地址, 电话号码, 角色) VALUES ('user1', '张三', 'password123', '北京市海淀区', '13800000001', 'customer'), ('user2', '李四', 'password123', '上海市浦东新区', '13800000002', 'customer'), ('user3', '王五', 'password123', '广州市天河区', '13800000003', 'customer'), ('admin1', '管理员1', 'adminpass', '系统地址', '00000000000', 'admin'), ('manager1', '经理1', 'managerpass', '公司地址', '11111111111', 'manager'), ('user4', '赵六', 'password123', '深圳市南山区', '13800000004', 'customer'), ('user5', '钱七', 'password123', '杭州市西湖区', '13800000005', 'customer'), ('user6', '孙八', 'password123', '武汉市洪山区', '13800000006', 'customer'), ('user7', '周九', 'password123', '成都市武侯区', '13800000007', 'customer'), ('user8', '吴十', 'password123', '南京市玄武区', '13800000008', 'customer');

    -- 授予权限 GRANT CREATE USER ON . TO 'your_user'@'localhost'; FLUSH PRIVILEGES;

    -- 权限分配 DELIMITER $$

    CREATE PROCEDURE AssignRolePermissions() BEGIN DECLARE done INT DEFAULT 0; DECLARE userAccount VARCHAR(50); DECLARE userRole ENUM('admin', 'manager', 'customer'); DECLARE cur CURSOR FOR SELECT 用户账号, 角色 FROM 用户表; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO userAccount, userRole;
        IF NOT done THEN
            -- 检查用户是否已存在
            SET @check_user_sql = CONCAT('SELECT COUNT(*) INTO @user_exists FROM mysql.user WHERE User = \'', userAccount, '\' AND Host = \'localhost\';');
            PREPARE stmt FROM @check_user_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
    
            -- 如果用户不存在,创建新用户
            IF @user_exists = 0 THEN
                SET @create_sql = CONCAT('CREATE USER \'', userAccount, '\'@\'localhost\' IDENTIFIED BY \'password123\';');
                PREPARE stmt FROM @create_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
    
            -- 只分配权限,创建用户过程在外部进行
            IF userRole = 'admin' THEN
                SET @grant_sql = CONCAT('GRANT ALL PRIVILEGES ON *.* TO \'', userAccount, '\'@\'localhost\' WITH GRANT OPTION;');
                PREPARE stmt FROM @grant_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
            ELSEIF userRole = 'manager' THEN
                SET @grant_sql1 = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON 产品表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql1;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql2 = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql2;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql3 = CONCAT('GRANT SELECT ON 用户表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql3;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
            ELSEIF userRole = 'customer' THEN
                SET @grant_sql1 = CONCAT('GRANT SELECT ON 产品表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql1;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql2 = CONCAT('GRANT INSERT ON 购物车表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql2;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql3 = CONCAT('GRANT SELECT ON 购物车表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql3;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql4 = CONCAT('GRANT SELECT ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql4;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    
                SET @grant_sql5 = CONCAT('GRANT INSERT ON 商家订单表 TO \'', userAccount, '\'@\'localhost\';');
                PREPARE stmt FROM @grant_sql5;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;
    UNTIL done END REPEAT;
    
    CLOSE cur;
    FLUSH PRIVILEGES;
    

    END$$

    DELIMITER ;

    -- 调用存储过程 CALL AssignRolePermissions();

    -- 插入产品表 INSERT INTO 产品表 (书名, 作者, 出版社, 图书类型, 库存, 单价, 上架时间) VALUES ('计算机基础', '作者A', '出版社X', '计算机', 100, 50.00, '2023-01-01'), ('高等数学', '作者B', '出版社Y', '数学', 80, 30.00, '2023-02-01'), ('物理学原理', '作者C', '出版社Z', '物理', 50, 60.00, '2023-03-01'), ('化学基础', '作者D', '出版社X', '化学', 70, 40.00, '2023-04-01'), ('英语语法', '作者E', '出版社Y', '语言', 60, 35.00, '2023-05-01'), ('文学概论', '作者F', '出版社Z', '文学', 40, 45.00, '2023-06-01'), ('历史探索', '作者G', '出版社X', '历史', 30, 55.00, '2023-07-01'), ('心理学入门', '作者H', '出版社Y', '心理学', 90, 65.00, '2023-08-01'), ('编程入门', '作者I', '出版社Z', '计算机', 120, 75.00, '2023-09-01'), ('数据科学', '作者J', '出版社X', '计算机', 110, 85.00, '2023-10-01');

    -- 插入购物车表 INSERT INTO 购物车表 (用户id, 书id, 书名, 作者, 出版社, 购买数量, 价格) VALUES (1, 1, '计算机基础', '作者A', '出版社X', 2, 50.00), (2, 2, '高等数学', '作者B', '出版社Y', 1, 30.00), (3, 3, '物理学原理', '作者C', '出版社Z', 3, 60.00), (1, 4, '化学基础', '作者D', '出版社X', 1, 40.00), (2, 5, '英语语法', '作者E', '出版社Y', 2, 35.00), (3, 6, '文学概论', '作者F', '出版社Z', 1, 45.00), (4, 7, '历史探索', '作者G', '出版社X', 1, 55.00), (5, 8, '心理学入门', '作者H', '出版社Y', 1, 65.00), (6, 9, '编程入门', '作者I', '出版社Z', 2, 75.00), (7, 10, '数据科学', '作者J', '出版社X', 1, 85.00);

    -- 插入商家订单表 INSERT INTO 商家订单表 (书id, 书名, 购买数量, 买家的用户id, 买家的用户名, 买家的地址, 买家的电话号码, 书籍总价) VALUES (1, '计算机基础', 1, 1, '张三', '北京市海淀区', '13800000001', 50.00), (2, '高等数学', 2, 2, '李四', '上海市浦东新区', '13800000002', 60.00), (3, '物理学原理', 1, 3, '王五', '广州市天河区', '13800000003', 60.00), (4, '化学基础', 1, 1, '张三', '北京市海淀区', '13800000001', 40.00), (5, '英语语法', 2, 2, '李四', '上海市浦东新区', '13800000002', 70.00), (6, '文学概论', 1, 3, '王五', '广州市天河区', '13800000003', 45.00), (7, '历史探索', 1, 4, '赵六', '深圳市南山区', '13800000004', 55.00), (8, '心理学入门', 1, 5, '钱七', '杭州市西湖区', '13800000005', 65.00), (9, '编程入门', 1, 6, '孙八', '武汉市洪山区', '13800000006', 75.00), (10, '数据科学', 2, 7, '周九', '成都市武侯区', '13800000007', 170.00);

    -- 插入购买评论表 INSERT INTO 购买评论 (书id, 买家的用户id, 买家的用户名, 买家的评论, 买家的评分) VALUES (1, 1, '张三', '非常好!', 5), (2, 2, '李四', '不错', 4), (3, 3, '王五', '一般般', 3), (4, 1, '张三', '内容丰富', 5), (5, 2, '李四', '值得推荐', 4), (6, 3, '王五', '没有想象中好', 3), (7, 4, '赵六', '还行', 4), (8, 5, '钱七', '非常专业', 5), (9, 6, '孙八', '很实用', 5), (10, 7, '周九', '不错的书籍', 4);

    -- 创建视图 CREATE VIEW 高评分图书畅销排行榜 AS SELECT p.书id, p.书名, p.作者, p.出版社, AVG(c.买家的评分) AS 平均评分 FROM 产品表 p JOIN 购买评论 c ON p.书id = c.书id GROUP BY p.书id, p.书名, p.作者, p.出版社 HAVING AVG(c.买家的评分) >= 4 ORDER BY 平均评分 DESC;

    CREATE VIEW 购物记录 AS SELECT c.用户id, c.书名, c.作者, c.出版社, c.购买数量, c.价格, u.地址, u.电话号码 FROM 购物车表 c JOIN 用户表 u ON c.用户id = u.用户id;

    CREATE VIEW 图书库存销售统计 AS SELECT p.书id, p.书名, p.库存, IFNULL(SUM(o.购买数量), 0) AS 销售数量 FROM 产品表 p LEFT JOIN 商家订单表 o ON p.书id = o.书id GROUP BY p.书id, p.书名;

    -- 更改分隔符 DELIMITER //

    -- 存储过程1:加入购物车 CREATE PROCEDURE 加入购物车(IN 用户 INT, IN 书 INT, IN 数量 INT) BEGIN IF EXISTS (SELECT * FROM 购物车表 WHERE 用户id = 用户 AND 书id = 书) THEN UPDATE 购物车表 SET 购买数量 = 购买数量 + 数量 WHERE 用户id = 用户 AND 书id = 书; ELSE INSERT INTO 购物车表 (用户id, 书id, 购买数量) VALUES (用户, 书, 数量); END IF; END //

    -- 存储过程2:创建订单 CREATE PROCEDURE 创建订单(IN 用户 INT) BEGIN INSERT INTO 商家订单表 (书id, 书名, 购买数量, 买家的用户id, 买家的用户名, 买家的地址, 买家的电话号码, 书籍总价) SELECT c.书id, c.书名, c.购买数量, u.用户id, u.用户名, u.地址, u.电话号码, c.购买数量 * c.价格 FROM 购物车表 c JOIN 用户表 u ON c.用户id = u.用户id WHERE c.用户id = 用户;

    DELETE FROM 购物车表 WHERE 用户id = 用户;
    

    END //

    -- 存储过程3:删除购物车条目 CREATE PROCEDURE 删除购物车条目(IN 用户 INT, IN 书 INT) BEGIN DELETE FROM 购物车表 WHERE 用户id = 用户 AND 书id = 书; END //

    -- 存储过程4:添加新书籍 CREATE PROCEDURE 添加新书籍(IN 书名 VARCHAR(100), IN 作者 VARCHAR(100), IN 出版社 VARCHAR(100), IN 类型 VARCHAR(50), IN 库存量 INT, IN 价格 DECIMAL(10,2), IN 上架日期 DATE) BEGIN INSERT INTO 产品表 (书名, 作者, 出版社, 图书类型, 库存, 单价, 上架时间) VALUES (书名, 作者, 出版社, 类型, 库存量, 价格, 上架日期); END //

    -- 存储过程5:计算用户评分平均值 CREATE PROCEDURE 计算平均评分(IN 书 INT, OUT 平均评分 DECIMAL(3,2)) BEGIN SELECT AVG(买家的评分) INTO 平均评分 FROM 购买评论 WHERE 书id = 书; END //

    -- 存储过程6:查看用户购物车内容 CREATE PROCEDURE 查看购物车(IN 用户 INT) BEGIN SELECT * FROM 购物车表 WHERE 用户id = 用户; END //

    -- 存储过程7:查询购买历史记录 CREATE PROCEDURE 查询购买历史记录(IN 用户 INT) BEGIN SELECT * FROM 商家订单表 WHERE 买家的用户id = 用户; END //

    -- 存储过程8:统计图书类型销量 CREATE PROCEDURE 统计图书类型销量() BEGIN SELECT p.图书类型, SUM(o.购买数量) AS 总销量 FROM 产品表 p JOIN 商家订单表 o ON p.书id = o.书id GROUP BY p.图书类型; END //

    -- 存储过程9:批量更新书籍价格 CREATE PROCEDURE 批量更新价格(IN 类型 VARCHAR(50), IN 调整比例 DECIMAL(4,2)) BEGIN UPDATE 产品表 SET 单价 = 单价 * 调整比例 WHERE 图书类型 = 类型; END //

    -- 恢复分隔符 DELIMITER ;

    -- 创建触发器 DELIMITER //

    -- 触发器1:更新库存 CREATE TRIGGER 更新库存 AFTER INSERT ON 商家订单表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 - NEW.购买数量 WHERE 书id = NEW.书id; END //

    -- 触发器2:删除购物车条目时恢复库存 CREATE TRIGGER 恢复库存 AFTER DELETE ON 购物车表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 + OLD.购买数量 WHERE 书id = OLD.书id; END //

    -- 触发器3:自动更新高评分排行榜 CREATE TRIGGER 更新排行榜 AFTER INSERT ON 购买评论 FOR EACH ROW BEGIN CALL 计算平均评分(NEW.书id, @平均评分); IF @平均评分 >= 4 THEN REPLACE INTO 高评分图书畅销排行榜 (书id, 书名, 作者, 出版社, 平均评分) SELECT 书id, 书名, 作者, 出版社, @平均评分 FROM 产品表 WHERE 书id = NEW.书id; END IF; END //

    -- 触发器4:自动更新书籍平均评分 CREATE TRIGGER 更新平均评分 AFTER INSERT ON 购买评论 FOR EACH ROW BEGIN UPDATE 产品表 SET 平均评分 = (SELECT AVG(买家的评分) FROM 购买评论 WHERE 书id = NEW.书id) WHERE 书id = NEW.书id; END //

    -- 触发器5:删除订单时恢复库存 CREATE TRIGGER 删除订单恢复库存 AFTER DELETE ON 商家订单表 FOR EACH ROW BEGIN UPDATE 产品表 SET 库存 = 库存 + OLD.购买数量 WHERE 书id = OLD.书id; END //

    -- 恢复分隔符 DELIMITER ; 根据sql语句帮我写一个1.1 编写目的

  6. Assistant

    1.1 编写目的

    本数据库设计说明书旨在提供对图书管理系统数据库的全面介绍与说明,包括数据库的结构、功能、操作及管理要求。该说明书的编写目的是为了:

    1. 明确数据库设计目标:通过详细记录数据库的各项功能和结构,确保数据库能够高效支持图书管理系统的业务需求。

    2. 促进团队协作:为开发人员、数据库管理员和相关团队成员提供统一的参考文档,从而确保所有参与者对数据库设计的理解一致,减少沟通和实施的障碍。

    3. 规范数据库操作:通过定义各个表及其字段、视图、存储过程和触发器的具体信息,确保数据库的使用符合设计原则和最佳实践,提升数据库的安全性、稳定性和可维护性。

    4. 支持后续开发和扩展:为未来的功能扩展、性能优化或数据库维护提供基础资料,便于迅速理解当前设计并在必要时进行改进。

    总之,本说明书不仅是数据库设计的重要文档,也是确保图书管理系统高效运作和持续发展的关键参考资料。