小说网站 数据库

小说网站 数据库 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;