请给出对应表格构建代码
时间: 2024-02-21 14:57:26 浏览: 11
好的,以下是对应表格构建代码,我以 MySQL 数据库为例:
```sql
-- 用户表
CREATE TABLE user (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(20)
);
-- 书籍表
CREATE TABLE book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
author_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
publisher_id INT UNSIGNED NOT NULL,
isbn VARCHAR(20),
price DECIMAL(8,2),
FOREIGN KEY (author_id) REFERENCES author(id),
FOREIGN KEY (category_id) REFERENCES category(id),
FOREIGN KEY (publisher_id) REFERENCES publisher(id)
);
-- 借阅表
CREATE TABLE borrow (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
book_id INT UNSIGNED NOT NULL,
borrow_date DATE NOT NULL,
return_date DATE,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (book_id) REFERENCES book(id)
);
-- 公告表
CREATE TABLE notice (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
content TEXT NOT NULL,
publish_date DATE NOT NULL,
admin_id INT UNSIGNED NOT NULL,
FOREIGN KEY (admin_id) REFERENCES admin(id)
);
-- 评论表
CREATE TABLE comment (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
book_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
rating INT UNSIGNED,
comment_date DATE NOT NULL,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (book_id) REFERENCES book(id)
);
-- 分类表
CREATE TABLE category (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- 出版社表
CREATE TABLE publisher (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
address VARCHAR(200),
phone VARCHAR(20)
);
-- 作者表
CREATE TABLE author (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(10),
nationality VARCHAR(50)
);
-- 借阅记录表
CREATE TABLE borrow_history (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
book_id INT UNSIGNED NOT NULL,
borrow_date DATE NOT NULL,
return_date DATE,
FOREIGN KEY (user_id) REFERENCES user(id),
FOREIGN KEY (book_id) REFERENCES book(id)
);
-- 图书馆管理员表
CREATE TABLE admin (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(20)
);
```
希望以上代码对您有所帮助。