www.pudn.com > bookstore.rar > bookstore.sql

DROP TABLE bookcat CASCADE CONSTRAINTS; 
CREATE TABLE bookcat(	 
	id		NUMBER      	PRIMARY KEY, 
	catname		VARCHAR2 (40) 	NOT NULL 
); 
 
DROP TABLE book CASCADE CONSTRAINTS; 
CREATE TABLE book(	 
	id		NUMBER      	PRIMARY KEY, 
	catid		NUMBER      	NOT NULL 
			REFERENCES bookcat(id) ON DELETE CASCADE, 
	name		VARCHAR2 (50) 	NOT NULL, 
	price		NUMBER       	NOT NULL, 
	saleprice	NUMBER        	NOT NULL, 
	descript	VARCHAR2 (500)  NOT NULL, 
	author		VARCHAR2 (50)   NOT NULL, 
	contents	VARCHAR2 (2000) NULL, 
	image		VARCHAR2 (50)  	NULL 
); 
 
DROP TABLE storeuser CASCADE CONSTRAINTS; 
CREATE TABLE storeuser(	 
	username	VARCHAR2 (20) 	PRIMARY KEY, 
	password	VARCHAR2 (20) 	NOT NULL, 
	name		VARCHAR2 (20) 	NOT NULL, 
	tel		VARCHAR2 (20)  	NOT NULL, 
	address		VARCHAR2 (100)  NOT NULL, 
	email		VARCHAR2 (50)   NULL 
); 
 
 
DROP TABLE orders CASCADE CONSTRAINTS; 
CREATE TABLE orders(	 
	id		NUMBER      	PRIMARY KEY, 
	username	VARCHAR2 (20)   NOT NULL 
			REFERENCES storeuser(username) ON DELETE CASCADE, 
	time		DATE 	    	NOT NULL, 
	delivery	VARCHAR2 (20)   NOT NULL, 
	payment		VARCHAR2 (20)   NOT NULL  
); 
 
DROP TABLE orderdetail CASCADE CONSTRAINTS; 
CREATE TABLE orderdetail(	 
	orderid		NUMBER      NOT NULL 
			REFERENCES orders(id) ON DELETE CASCADE, 
	bookid		NUMBER      NOT NULL 
			REFERENCES book(id) ON DELETE CASCADE, 
	count		NUMBER      NOT NULL, 
	price		NUMBER      NOT NULL 
); 
 
insert into bookcat  values(1,'计算机'); 
insert into bookcat  values(2,'社科类'); 
insert into bookcat  values(3,'科技类'); 
 
 
insert into book values(1,1,'Struts入门与实践',45,45,'中较为全面地介绍了Struts架构下进行Web应用开发的程序设计技术。本书的特色是以案例为主，将相关知识点作为主线贯穿其中。','张桂元','目录内容',''); 
insert into book values(2,1,'Eclipse入门与实践',45,45,'中较为全面地介绍了Eclipse架构下进行Web应用开发的程序设计技术。本书的特色是以案例为主，将相关知识点作为主线贯穿其中。','贾燕枫','目录内容','');