photo.sql

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

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

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

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

-- 使用photo用户连接数据库
CONNECT photo/photo

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

-- 创建照片分类表
CREATE TABLE category
(
    categoryid	INTEGER,
    name	NVARCHAR2(32),
    CONSTRAINTS	pk_category_categoryid PRIMARY KEY ( categoryid )
);

-- 创建照片信息表
CREATE TABLE photo
(
    photoid	INTEGER,
    title	NVARCHAR2(32) NOT NULL,
    content	NVARCHAR2(1000) NOT NULL,
    pubdate	DATE NOT NULL,
    categoryid	INTEGER NOT NULL,
    photo	BLOB DEFAULT empty_blob(),
    filetype	VARCHAR(16),
    filename	NVARCHAR2(255),
    CONSTRAINTS pk_photo_photoid PRIMARY KEY ( photoid ),
    CONSTRAINTS fk_photo_category FOREIGN KEY (categoryid) REFERENCES category( categoryid )
);