createtableagain.sqlpage

网上购物系统的基本实现

<?xml version="1.0" encoding="UTF-8"?>
<SQLPage nameConnection="MYSQL">
<Statement><![CDATA[DROP TABLE CART_ORDER_YE;
DROP TABLE CART_ITEM_YE;
DROP TABLE CART_USER_YE;
DROP TABLE CART_PRODUCT_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,
	NUMBER INTEGER,
	PRODUCT_ID INTEGER REFERENCES CART_PRODUCT(ID),
	
	cost double(10,2) not null,
	
	ORDER_ID INTEGER REFERENCES CART_ORDER(ID)
);

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

]]></Statement>
</SQLPage>