msgboard.sql

来自「JDBC数据库高级编程源代码」· SQL 代码 · 共 54 行

-- 创建用户表空间msgboard
CREATE TABLESPACE "MSGBOARD" 
    LOGGING 
    DATAFILE 'E:\ORACLE\ORADATA\TEST\MSGBOARD.ora' SIZE 10M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;

-- 创建用户MSGBOARD
CREATE USER "MSGBOARD"  PROFILE "DEFAULT" 
    IDENTIFIED BY "msgboard" DEFAULT TABLESPACE "MSGBOARD" 
    ACCOUNT UNLOCK;

-- 授予用户权限
GRANT CREATE SESSION TO "MSGBOARD";
GRANT "CONNECT" TO "MSGBOARD";
GRANT "RESOURCE" TO "MSGBOARD";

-- 使用MSGBOARD用户连接数据库
CONNECT msgboard/msgboard

-- 创建主键信息表
CREATE TABLE tableseq
(
    tablename	VARCHAR(32),
    maxid	INTEGER,
    CONSTRAINTS pk_tableseq_tablename PRIMARY KEY ( tablename )	
);

-- 创建用户信息表
CREATE TABLE userinfo
(
    userid	INTEGER,
    username	VARCHAR(32) NOT NULL,
    nickname	NVARCHAR2(16) NOT NULL,
    password	VARCHAR(32) NOT NULL,
    email	VARCHAR(64) NOT NULL,
    type	INTEGER NOT NULL,
    status	INTEGER NOT NULL,
    CONSTRAINTS	pk_user_userid PRIMARY KEY ( userid )
);

-- 创建用户留言信息表
CREATE TABLE article
(
    articleid	INTEGER,
    title	NVARCHAR2(32) NOT NULL,
    content	NVARCHAR2(1000) NOT NULL,
    pubdate	DATE NOT NULL,
    userid	INTEGER NOT NULL,
    hostip	VARCHAR(16),
    farticleid	INTEGER NOT NULL,
    CONSTRAINTS pk_article_articleid PRIMARY KEY ( articleid ),
    CONSTRAINTS fk_article_userinfo FOREIGN KEY (userid) REFERENCES userinfo( userid ),
    CONSTRAINTS fk_article_article FOREIGN KEY (farticleid) REFERENCES article( articleid )
);