oracle sql脚本大全,ORACLE SQL脚本

创建的一个脚本 用于创建图书馆系统表

CREATE TABLE type

(

typeid NUMBER(10) PRIMARY KEY,

typename VARCHAR2(20) UNIQUE NOT NULL

)

CREATE TABLE grade

(

gradeid NUMBER(10) PRIMARY KEY,

gradename VARCHAR2(20) UNIQUE NOT NULL

)

CREATE TABLE book

(

bookid NUMBER(10) PRIMARY KEY,

booknumber CHAR(8) UNIQUE NOT NULL,

bookname VARCHAR2(30) NOT NULL,

bookpress VARCHAR2(40) NOT NULL,

bookprice NUMBER(8,2) NOT NULL,

typeid NUMBER(10) DEFAULT 1 NOT NULL,

booktime DATE NOT NULL,

CONSTRAINT book_type FOREIGN KEY(typeid)

REFERENCES type(typeid)

)

CREATE TABLE manager

(

managerid NUMBER(10) PRIMARY KEY,

realname VARCHAR2(10) NOT NULL,

sex CHAR(2) NOT NULL,

username VARCHAR2(16) NOT NULL,

password VARCHAR2(16) NOT NULL,

registertime DATE NOT NULL,

gradeid NUMBER(10) DEFAULT 1 NOT NULL,

CONSTRAINT manager_grade FOREIGN KEY(gradeid)

REFERENCES grade(gradeid)

)

CREATE TABLE student

(

studentid NUMBER(10) PRIMARY KEY,

studentnumber CHAR(7) UNIQUE NOT NULL,

studentname VARCHAR2(10) NOT NULL,

studentsex CHAR(2) NOT NULL

CHECK (studentsex IN ('男','女'))

)

CREATE TABLE  card

(

cardid NUMBER(10) PRIMARY KEY,

cardnumber CHAR(9) UNIQUE NOT NULL,

studentid NUMBER(10) UNIQUE NOT NULL,

registertime DATE NOT NULL,

CONSTRAINT card_student FOREIGN KEY(studentid)

REFERENCES student(studentid)

)

CREATE TABLE lend

(

lendid NUMBER(10) PRIMARY KEY,

cardid NUMBER(10) NOT NULL,

bookid NUMBER(10) NOT NULL,

lendtime DATE NOT NULL,

CONSTRAINT lend_card FOREIGN KEY(cardid)

REFERENCES card(cardid),

CONSTRAINT lend_book FOREIGN KEY(bookid)

REFERENCES book(bookid)

)

其中最后可以加

TABLESPACE bookspace;

如果不加的话 就是建立在当前默认表空间之下

当前用户角色 也就是大家常常见到的模式[schema.]

2014-9-22 写于此 以防备用 没有太多营养大家随便看看吧