本文共 5226 字，大约阅读时间需要 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 requests
  import csv
  def 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_order
  JOIN book b ON customer_order.book_id = b.id
  SET 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_order
  SET 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/