小说网站数据库

秒速五厘米 2023-02-17 11:10 1阅读 0赞

# 小说网站 数据库 SQL语句 #

### 一、创建数据库 ###

create database novel;

### 二、数据表的建立 ###

#### 小说表 ####

create table `n_book`(
    	`book_id` int auto_increment,
    	`book_name` char(10) unique not null,
    	`book_intro` tinytext not null,
    	`book_author_id` int,
    	`book_class_id` int,  
    	`book_star` int default 0,
    	primary key (`book_id`),
    	foreign key (`book_author_id`) references n_author(`author_id`),
    	foreign key (`book_class_id`) references n_class(`class_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 章节表 ####

create table n_chapter(
    	`chapter_id` int auto_increment,
    	`chapter_number` int not null,
    	`chapter_name` char(10) not null,
    	`chapter_content` text not null,
    	`chapter_book_id` int,
    	primary key (`chapter_id`),
    	foreign key (`chapter_book_id`) references n_book(`book_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 读者表 ####

create table n_reader(
    	`reader_id` int auto_increment,
    	`reader_name` char(10) unique not null,
    	primary key(`reader_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 作者表 ####

create table n_author(
    	`author_id` int auto_increment,
    	`author_name` char(10) unique not null,
    	primary key (`author_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 评论表 ####

create table n_comment(
    	`comment_id` int auto_increment,
    	`comment_content` tinytext not null,
    	`comment_reader_id` int,
    	`comment_book_id` int,
    	primary key(`comment_id`),
    	foreign key(`comment_reader_id`) references n_reader(`reader_id`),
    	foreign key(``comment_book_id) references n_book(`book_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 类别表 ####

create table n_class(
    	`class_id` int auto_increment,
    	`class_name` char(2) unique not null,
    	primary key (`class_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 收藏表 ####

create table n_star(
    	`star_id` int auto_increment,
    	`star_reader_id` int,
    	`star_book_id` int,
    	primary key(`star_id`),
    	foreign key(`star_reader_id`) references n_reader(`reader_id`),
    	foreign key(`star_book_id`) references n_book(`book_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;