mysql创建网上书店数据库
时间: 2023-10-18 17:03:07 浏览: 96
创建一个网上书店数据库时,可以考虑以下表格及其字段来组织数据:
1. 书籍表(books):
- 书籍ID(book_id)
- 书籍名称(book_name)
- 作者(author)
- 出版社(publisher)
- 出版日期(publish_date)
- 价格(price)
- 库存数量(stock)
2. 类别表(categories):
- 类别ID(category_id)
- 类别名称(category_name)
3. 顾客表(customers):
- 顾客ID(customer_id)
- 姓名(name)
- 地址(address)
- 邮箱(email)
- 电话号码(phone_number)
4. 订单表(orders):
- 订单ID(order_id)
- 顾客ID(customer_id)
- 书籍ID(book_id)
- 数量(quantity)
- 总价(total_price)
- 订单日期(order_date)
通过以上表格及其字段,可以构建一个可以管理网上书店的数据库。可以使用以下SQL语句创建上述表格:
```
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
book_name VARCHAR(100) NOT NULL,
author VARCHAR(50) NOT NULL,
publisher VARCHAR(50) NOT NULL,
publish_date DATE NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL
);
CREATE TABLE categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(50) NOT NULL
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
email VARCHAR(50) NOT NULL,
phone_number VARCHAR(20) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
book_id INT,
quantity INT,
total_price DECIMAL(10,2) NOT NULL,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (book_id) REFERENCES books(book_id)
);
```
通过以上的表格和SQL语句,可以实现一个简单的网上书店数据库,方便管理书籍、类别、顾客和订单等信息。