數據庫建立步驟，JavaWeb—作業【建立新聞數據庫以及插入數據】

spool c:\DBCreateInfofornews.log;
-----新聞發布系統
-----用戶表
CREATE TABLE news_user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(20) DEFAULT NULL,
password varchar(20) DEFAULT NULL,
email varchar(100) DEFAULT NULL,
usertype tinyint(8) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO NEWS_USER VALUES(1,‘admin’,‘admin’,‘admin@bdqn.cn’,0);
INSERT INTO NEWS_USER VALUES(2,‘user’,‘user’,‘user@bdqn.cn’,1);
INSERT INTO NEWS_USER VALUES(3,‘test’,‘test’,‘test@bdqn.cn’,1);

-----新聞分類表， 有外鍵存在，因此先刪除子表
drop table NEWS_COMMENT;
drop table NEWS_DETAIL;
drop table NEWS_CATEGORY;
CREATE TABLE news_category (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
createdate date NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(1,‘國內’,SYSDATE());
INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(2,‘國際’,SYSDATE());
INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(3,‘娛樂’,SYSDATE());
INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(4,‘軍事’,SYSDATE());
INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(5,‘財經’,SYSDATE());
INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(6,‘天氣’,SYSDATE());

-----新聞明細表
CREATE TABLE news_detail (
id int(11) NOT NULL AUTO_INCREMENT,
categoryId int(11) NOT NULL,
title varchar(100) NOT NULL,
summary varchar(255) DEFAULT NULL,
content varchar(2000) DEFAULT NULL,
picpath varchar(255) DEFAULT NULL,
author varchar(50) DEFAULT NULL,
createdate date DEFAULT NULL,
modifydate date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO NEWS_DETAIL VALUES(1,1,‘尼日利亞一架客機墜毀’,‘尼日利亞一架客機墜毀，傷亡慘重’,‘尼日利亞一架客機墜毀，傷亡慘重,10人重傷’,’’,‘admin’,SYSDATE(),SYSDATE());

-----新聞評論表
CREATE TABLE news_comment (
id int(11) NOT NULL AUTO_INCREMENT,
newsId int(10) NOT NULL,
content varchar(2000) DEFAULT NULL,
author varchar(50) DEFAULT NULL,
ip varchar(15) DEFAULT NULL,
createdate date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT * FROM news_detail;
DELETE FROM news_detail WHERE ID=2