oracle.sql

来自「是关于struts+hibernate+spring开发宝典的源代码,希望对大家」· SQL 代码 · 共 44 行

-- 创建表空间
CREATE TABLESPACE blog
DATAFILE '/opt/oracle/oradata/test/blog01.dbf' size 200M
EXTENT MANAGEMENT local;

-- 创建用户
CREATE USER blog IDENTIFIED BY blog
DEFAULT TABLESPACE blog;

-- 授予权限
GRANT connect, resource TO blog;

-- 使用blog用户连接数据库
CONNECT blog/blog;

-- 创建博客信息表
CREATE TABLE userinfo (
	id		VARCHAR2(32) NOT NULL,
	name		VARCHAR2(32) NOT NULL,
	password	VARCHAR2(32) NOT NULL,
	CONSTRAINT userinfo_pk PRIMARY KEY (id),
	CONSTRAINT userinfo_unique_name UNIQUE ( name )
);

-- 创建网络日志表
CREATE TABLE message (
	id			VARCHAR2(32) NOT NULL,
	title			VARCHAR2(32) NOT NULL,
	content		VARCHAR2(2000),
	user_id		VARCHAR2(32),
	CONSTRAINT message_pk PRIMARY KEY (id),
	CONSTRAINT message_user FOREIGN KEY (user_id) REFERENCES userinfo(id) 
);

-- 创建读者评论信息表
CREATE TABLE replymessage (
	id			VARCHAR2(32) NOT NULL,
	username	VARCHAR2(64) NOT NULL,
	title		VARCHAR2(32) NOT NULL,
	content		VARCHAR2(2000),
	message_id	VARCHAR2(32) NOT NULL,
	CONSTRAINT replymessage_pk PRIMARY KEY (id),
	CONSTRAINT replymessage_message FOREIGN KEY (message_id) REFERENCES message(id)
);