本文共 5120 字，大约阅读时间需要 17 分钟。

MySQL虚拟书店数据库搭建指南

1. 创建虚拟书店数据库

数据库名称：mysql_practice

数据库创建语法：

CREATE DATABASE IF NOT EXISTS mysql_practice CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

数据库操作命令：

• 登录数据库：

  mysql -u root -D mysql_practice -p

• 切换数据库：

  use mysql_practice;

• 查看当前数据库：

  SELECT database();

• 创建数据库：

  create database if not exists mysql_practice;

• 删除数据库：

  drop database if exists mysql_practice;

2. 数据表结构设计

2.1 表结构定义

USE mysql_practice;-- region表（三级地址）CREATE TABLE IF NOT EXISTS region (    id INT AUTO_INCREMENT PRIMARY KEY,    pid INT NOT NULL,    deep INT NOT NULL,    name VARCHAR(200) NOT NULL,    pinyin_prefix VARCHAR(10) NOT NULL,    pinyin VARCHAR(200) NOT NULL,    ext_id VARCHAR(100) NOT NULL,    ext_name VARCHAR(200) NOT NULL);-- customer表（客户信息）CREATE TABLE IF NOT EXISTS customer (    id INT AUTO_INCREMENT PRIMARY KEY,    no VARCHAR(50) NOT NULL UNIQUE,    first_name VARCHAR(255) NOT NULL,    last_name VARCHAR(255) NOT NULL,    status VARCHAR(20) NOT NULL,    phone_number VARCHAR(20) NULL,    updated_at DATETIME NOT NULL,    created_at DATETIME NOT NULL);-- customer_address表（客户地址）CREATE TABLE IF NOT EXISTS customer_address (    id INT AUTO_INCREMENT PRIMARY KEY,    customer_id INT NOT NULL,    area_id INT NULL,    address_detail VARCHAR(200) NULL,    is_default BOOLEAN NOT NULL,    updated_at DATETIME NOT NULL,    created_at DATETIME NOT NULL,    FOREIGN KEY (customer_id) REFERENCES customer(id) ON UPDATE RESTRICT ON DELETE CASCADE);-- book_category表（图书分类）CREATE TABLE IF NOT EXISTS book_category (    id INT AUTO_INCREMENT PRIMARY KEY,    code VARCHAR(200) NOT NULL,    name VARCHAR(200) NOT NULL,    parent_id INT NULL,    deep INT NULL,    updated_at DATETIME NOT NULL,    created_at DATETIME NOT NULL);-- book表（图书信息）CREATE TABLE IF NOT EXISTS book (    id INT AUTO_INCREMENT PRIMARY KEY,    category_id INT NOT NULL,    no VARCHAR(50) NOT NULL,    name VARCHAR(200) NOT NULL,    status VARCHAR(50) NOT NULL,    unit_price DOUBLE NOT NULL,    author VARCHAR(50) NULL,    publish_date DATETIME NULL,    publisher VARCHAR(200) NOT NULL,    updated_at DATETIME NOT NULL,    created_at DATETIME NOT NULL,    FOREIGN KEY (category_id) REFERENCES book_category(id) ON UPDATE RESTRICT ON DELETE CASCADE);-- customer_order表（订单信息）CREATE TABLE IF NOT EXISTS customer_order (    id INT AUTO_INCREMENT PRIMARY KEY,    no VARCHAR(50) NOT NULL,    customer_id INT NOT NULL,    book_id INT NOT NULL,    quantity INT NOT NULL,    total_price DOUBLE NOT NULL,    discount DOUBLE NULL,    order_date DATETIME NOT NULL,    updated_at DATETIME NOT NULL,    created_at DATETIME NOT NULL,    FOREIGN KEY (customer_id) REFERENCES customer(id) ON UPDATE RESTRICT ON DELETE CASCADE,    FOREIGN KEY (book_id) REFERENCES book(id) ON UPDATE RESTRICT ON DELETE CASCADE);

2.2 数据导入

• 导入区域数据：

  LOAD DATA LOCAL INFILE '/path/to/region.csv' INTO TABLE region    FIELDS TERMINATED BY ',' ENCLOSED BY '"'    LINES TERMINATED BY 'n'    IGNORE 1 ROWS;

• 使用SP生成客户数据：

  USE mysql_practice;call sp_generate_customers();

• 手动插入分类数据：

  INSERT INTO book_category (code, name, parent_id, deep, updated_at, created_at)VALUES('BOOK', 'Book', 0, 0, CURDATE(), CURDATE()),       ('BOOK_CODE', 'Code Book', 1, 1, CURDATE(), CURDATE()),       ('BOOK_CHILREN', 'Children Book', 1, 1, CURDATE(), CURDATE()),       ('BOOK_SCIENCE', 'Science Book', 1, 1, CURDATE(), CURDATE());

• 使用Python抓取并导入科学类书籍数据：

  import requestsimport csvdef crawl(url):    res = requests.get(url, encoding='gb18030')    soup = BeautifulSoup(res.text, 'html.parser')    csv_writer = csv.writer(open('output_science.csv', 'w', encoding='utf8'), delimiter='#')    csv_writer.writerow(['序号', '书名', '价格', '作者', '出版社', '出版时间'])    for books in soup.find('ul', id='component_59').find_all('li'):        title = books.select('.name')[0].text.strip().split(' ', 1)[0].strip()        price = books.select('.search_pre_price')[0].text.strip('¥')        author_info = books.select('.search_book_author')[0].text.strip().split('/')        author = author_info[0]        publish_date = author_info[1]        publisher = author_info[2]        n += 1        csv_writer.writerow([n, title, price, author, publish_date, publisher])crawl('http://search.dangdang.com/?key=科学')

• 导入科学类书籍数据到book表：

  INSERT INTO book (category_id, no, name, status, unit_price, author, publish_date, publisher, updated_at, created_at)SELECT 4, REPLACE(LEFT(uuid(), 16), '-', ''), name, 'ACTIVE', price, author, publish_date, publisher, CURDATE(), CURDATE()FROM mock_science;

2.3 生成订单数据

• 使用SP随机生成订单数据：

  USE mysql_practice;call sp_generate_orders();

• 更新订单总价和订单编号：

  UPDATE customer_orderJOIN book b ON customer_order.book_id = b.idSET total_price = quantity * unit_price;

3. 数据库优化

• 添加索引：

  ALTER TABLE book ADD INDEX idx_unit_price (unit_price);ALTER TABLE customer_order ADD INDEX idx_order_no (no);ALTER TABLE customer_order ADD INDEX idx_order_date (order_date);ALTER TABLE customer_order ADD INDEX idx_quantity (quantity);

• 更新订单状态：

  UPDATE customer_orderSET no = CONCAT(REPLACE(LEFT(uuid(), 16), '-', ''), customer_id, book_id);

4. 数据备份

• 使用mysqldump备份数据库：

  mysqldump -u root -D mysql_practice > backup.sql;

5. 下一步操作

• 创建视图、存储过程、函数、触发器和定时任务等。
• 针对不同业务需求扩展数据库架构。
• 定期备份数据库，确保数据安全性。

转载地址：http://cskbz.baihongyu.com/