3-4-2 创建数据表的脚本
代码如下:
____________________________________________________________________________
//图书分类基本信息表
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
);
代码如下:
____________________________________________________________________________
//图书分类基本信息表
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
);
-- 这种语法,ORACLE 支持的 ,据说 DM 也支持
DROP TABLE bookcat CASCADE CONSTRAINTS;