ORACLE SQL脚本

来源：互联网发布：linux 获取cpu函数编辑：程序博客网时间：2024/06/05 15:29

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

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 写于此以防备用没有太多营养大家随便看看吧

0 0