mysqldb.sql

来自「Spring2.0宝典」· SQL 代码 · 共 54 行

drop database if exists newsboard;

create database newsboard;

use newsboard;

# 建用户表

create table app_user(
username varchar(20),
password varchar(255),
primary key(username));

insert into app_user values('tomcat' , '123');
insert into app_user values('yeeku' , '123');


# 建新闻分类表

create table category(
id bigint(20),
name varchar(50),
primary key(id));
# 插入两条分类数据
insert into category values(1,'社会新闻');
insert into category values(2,'娱乐新闻');


# 建新闻表

create table news(
id                bigint(20),
category_id       bigint(20),
last_modify_date  datetime,
post_date         datetime,
username          varchar(20),
content           text,
title             varchar(50),
primary key(id),
foreign key(category_id) references category(id),
foreign key(username) references app_user(username));

# 建回复表

create table news_review(
id                bigint(20),
content           text,
last_modify_date  datetime,
news_id           bigint(20),
post_date         datetime,
username          varchar(20),
primary key(id),
foreign key(news_id) references news(id),
foreign key(username) references app_user(username));