createtable.sqlpage

网上购物系统的基本实现

DROP TABLE CART_USER_YE;
drop TABLE CART_PRODUCT_YE
DROP TABLE CART_ORDER_YE;
DROP TABLE CART_ITEM_YE;
CREATE TABLE CART_USER_YE(
	ID INTEGER PRIMARY KEY,
	NAME VARCHAR(32) NOT NULL,
	PASSWORD VARCHAR(32) NOT NULL,
	BIRTHDAY DATE,
	ADDRESS VARCHAR(512) NOT NULL,
	POSTCODE VARCHAR(10) NOT NULL,
	EMAIL VARCHAR(32),
	TELEPHONE VARCHAR(32) NOT NULL
);
INSERT INTO CART_USER_YE VALUES(2,'yewen','tarena','1988-01-01','dazhongsi','100081','[e-mail]','62136369');
CREATE TABLE CART_PRODUCT_YE(
	ID INTEGER PRIMARY KEY,
	NAME VARCHAR(32) NOT NULL,
	DESCRIPTION VARCHAR(64),
	PRICE DOUBLE NOT NULL
);
INSERT INTO CART_PRODUCT_YE VALUES(1,'pencil','pencil',2.00);
INSERT INTO CART_PRODUCT_YE VALUES(2,'pen','pen',5.00);
INSERT INTO CART_PRODUCT_YE VALUES(3,'rubber','rubber',1.00);
INSERT INTO CART_PRODUCT_YE VALUES(4,'notebook','notebook',2.00);
INSERT INTO CART_PRODUCT_YE VALUES(5,'gluewater','gluewater',5.00);
INSERT INTO CART_PRODUCT_YE VALUES(6,'pencilcase','pencilcase',10.00);
INSERT INTO CART_PRODUCT_YE VALUES(7,'ballpen','ballpen',2.00);
CREATE TABLE CART_ORDER_YE(
	ID INTEGER PRIMARY KEY,
	COST DOUBLE NOT NULL,
	STATUS INTEGER NOT NULL,
	USER_ID INTEGER REFERENCES CART_USER(ID)
);



CREATE TABLE CART_ITEM_YE(

	ID INTEGER PRIMARY KEY,

	AMOUNT INTEGER,

	PRODUCT_ID INTEGER REFERENCES CART_PRODUCT(ID),

	ORDER_ID INTEGER REFERENCES CART_ORDER(ID)

);


INSERT INTO CART_USER_YE VALUES(1,'tarena','tarena','1988-1-1','dazhongsi','100081','[e-mail]','62136369');



CREATE TABLE CART_PRODUCT_YE(

	ID INTEGER PRIMARY KEY,

	NAME VARCHAR(32) NOT NULL,

	DESCRIPTION VARCHAR(64),

	PRICE DOUBLE NOT NULL

);