-- 尝试创建 Bookstore 数据库，若已存在则不重复创建
CREATE DATABASE IF NOT EXISTS Bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT 'Database Bookstore created or already exists.';

-- 使用 Bookstore 数据库
USE Bookstore;
SELECT 'Switched to the Bookstore database.';

-- 创建 book 表，用于存储图书信息
CREATE TABLE IF NOT EXISTS book (
    -- 图书编号，作为主键唯一标识每本图书
    图书编号 VARCHAR(20) PRIMARY KEY COMMENT '图书编号',
    -- 图书类别，如计算机、文学等
    图书类别 VARCHAR(50) COMMENT '图书类别',
    -- 图书名称
    书名 VARCHAR(100) COMMENT '书名',
    -- 图书作者
    作者 VARCHAR(50) COMMENT '作者',
    -- 图书出版社
    出版社 VARCHAR(100) COMMENT '出版社',
    -- 图书出版时间
    出版时间 DATETIME COMMENT '出版时间',
    -- 图书单价
    单价 DECIMAL(10, 2) COMMENT '单价',
    -- 图书库存数量
    数量 INT COMMENT '数量',
    -- 图书折扣
    折扣 DECIMAL(3, 2) COMMENT '折扣',
    -- 图书封面图片的存储路径
    封面图片 VARCHAR(255) COMMENT '封面图片'
) COMMENT '图书信息表';
SELECT 'Table book created or already exists.';

-- 创建 members 表，用于存储会员信息
CREATE TABLE IF NOT EXISTS members (
    -- 用户号，作为主键唯一标识每个会员
    用户号 VARCHAR(20) PRIMARY KEY COMMENT '用户号',
    -- 会员姓名
    姓名 VARCHAR(50) COMMENT '姓名',
    -- 会员性别，使用单个字符表示
    性别 CHAR(1) COMMENT '性别',
    -- 会员登录密码
    密码 VARCHAR(50) COMMENT '密码',
    -- 会员联系电话
    联系电话 VARCHAR(20) COMMENT '联系电话',
    -- 会员注册时间
    注册时间 DATETIME COMMENT '注册时间'
) COMMENT '会员信息表';
SELECT 'Table members created or already exists.';

-- 创建 sell 表，用于存储销售订单信息
CREATE TABLE IF NOT EXISTS sell (
    -- 订单号，自增主键，自动生成唯一订单编号
    订单号 INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单号',
    -- 下单用户的用户号，关联 members 表的用户号
    用户号 VARCHAR(20) COMMENT '用户号',
    -- 所购图书的编号，关联 book 表的图书编号
    图书编号 VARCHAR(20) COMMENT '图书编号',
    -- 订购的图书册数
    订购册数 INT COMMENT '订购册数',
    -- 订购时的图书单价
    订购单价 DECIMAL(10, 2) COMMENT '订购单价',
    -- 订单下单时间
    订购时间 DATETIME COMMENT '订购时间',
    -- 订单是否发货状态
    是否发货 VARCHAR(20) COMMENT '是否发货',
    -- 订单是否收货状态
    是否收货 VARCHAR(20) COMMENT '是否收货',
    -- 订单是否结清款项，0 表示未结清，1 表示已结清
    是否结清 TINYINT COMMENT '是否结清',
    -- 外键约束，关联 members 表的用户号
    FOREIGN KEY (用户号) REFERENCES members(用户号),
    -- 外键约束，关联 book 表的图书编号
    FOREIGN KEY (图书编号) REFERENCES book(图书编号)
) COMMENT '销售订单信息表';
SELECT 'Table sell created or already exists.';

-- 向 book 表插入数据
SELECT 'Inserting data into the book table...';
INSERT INTO book (图书编号, 图书类别, 书名, 作者, 出版社, 出版时间, 单价, 数量, 折扣, 封面图片)
VALUES 
('TP.2462', '计算机', '计算机应用基础', '陆大一', '清华大学出版社', '2022-10-19', 45.00, 45, 0.80, NULL),
('TP.2463', '计算机', '计算机网络技术', '林力尔', '清华大学出版社', '2021-10-16', 25.50, 31, 0.80, 'D:/pic/ll.jpg'),
('TP.2525', '计算机', 'PHP高级语言', '王大山', '中国青年出版社', '2022-06-20', 33.25, 3, 0.80, 'D:/pic/js.jpg'),
('TP.6625', '计算机', 'JavaScript编程', '谢为士', '中国青年出版社', '2021-08-05', 33.00, 60, 0.80, NULL),
('Ts.3010', '数据库', 'Oracle', '张小五', '北京大学出版社', '2022-08-02', 28.00, NULL, NULL, NULL),
('Ts.3035', '数据库', 'MySQL数据库', '李陛', '北京大学出版社', '2020-12-26', 20.00, 500, 0.80, 'D:/pic/jp.jpg'),
('Tw.1283', '网页设计', 'DW网站制作', '李七', '人民邮电出版社', '2021-10-01', 27.00, NULL, NULL, NULL),
('Tw.2562', '网页设计', 'ASP网站制作', '胡莉芭', '中国青年出版社', '2022-07-24', 30.50, 50, 0.80, NULL),
('Tw.3020', '网页设计', '网页程序设计', '刘玖', '清华大学出版社', '2023-02-15', 25.00, NULL, NULL, NULL);
SELECT 'Data inserted into the book table.';

-- 向 members 表插入数据
SELECT 'Inserting data into the members table...';
INSERT INTO members (用户号, 姓名, 性别, 密码, 联系电话, 注册时间)
VALUES 
('A0012', '赵宏宇', '男', '080100', '13601234123', '2023-03-04 18:23:45'),
('A3013', '张凯', '男', '080100', '13611320001', '2023-01-15 09:12:23'),
('B0022', '王林', '男', '080100', '12501234123', '2023-01-12 08:12:30'),
('B2023', '李小冰', '女', '080100', '13651111081', '2023-01-18 08:57:18'),
('C0132', '张莉', '女', '123456', '13822555432', '2022-09-23 00:00:00'),
('C0138', '李华', '女', '123456', '13822551234', '2022-08-23 00:00:00'),
('D1963', '张三', '男', '222222', '51985523', '2022-01-23 08:15:45');
SELECT 'Data inserted into the members table.';

-- 向 sell 表插入数据，省略自增的订单号字段
SELECT 'Inserting data into the sell table...';
INSERT INTO sell (用户号, 图书编号, 订购册数, 订购单价, 订购时间, 是否发货, 是否收货, 是否结清)
VALUES 
('C0132', 'TP.2525', 13, 20, '2023-11-14 12:13:49', '已发货', NULL, 0),
('D1963', 'TP.2463', 3, 31.5, '2023-11-21 12:25:12', '已发货', NULL, 0),
('D1963', 'TP.2525', 6, 23.45, '2023-03-26 12:25:23', '已发货', '已收货', 0),
('C0138', 'Ts.3035', 10, 23.5, '2023-08-01 12:13:49', '已发货', '已收货', 1),
('C0138', 'TP.2525', 133, 33.5, '2023-08-01 12:13:49', NULL, NULL, 0),
('A3013', 'Tw.2562', 4, 89, '2023-08-20 00:00:00', NULL, NULL, 0),
('C0138', 'TP.2463', 43, 30, '2023-11-08 12:13:49', '已发货', NULL, 0),
('C0138', 'Ts.3035', 5, 45.5, '2023-11-21 00:00:00', NULL, NULL, 0),
('C0132', 'Tw.1283', 6, 23, '2023-11-28 18:23:35', '已发货', '已收货', 1);
SELECT 'Data inserted into the sell table.';

-- 查询 book 表数据
SELECT 'Querying data from the book table...';
SELECT * FROM book;

-- 查询 members 表数据
SELECT 'Querying data from the members table...';
SELECT * FROM members;

-- 查询 sell 表数据
SELECT 'Querying data from the sell table...';
SELECT * FROM sell;