Oracle创建表空间、用户、表、约束

裘子惠

DROP TABLESPACE PETSHOP INCLUDING CONTENTS AND DATAFILES
/
CREATE TABLESPACE PETSHOP
DATAFILE 'E:\app\xxxxx\oradata\orcl\PETSHOP.dbf'
SIZE 20M
AUTOEXTEND ON NEXT 5M
MAXSIZE 500M
/
CREATE USER lsz
IDENTIFIED BY szli
DEFAULT TABLESPACE PETSHOP
PASSWORD EXPIRE
ACCOUNT LOCK
/
ALTER USER lsz ACCOUNT UNLOCK
/
GRANT CONNECT TO lsz
/
GRANT CREATE VIEW,CREATE TABLE,CREATE SESSION,CREATE USER,ALTER USER,DROP USER,CREATE TABLESPACE,ALTER TABLESPACE,DROP TABLESPACE,CREATE PROCEDURE,CREATE ROLE,RESOURCE TO lsz WITH ADMIN OPTION
/
CREATE TABLE lsz. pet(
petId VARCHAR2(4000) PRIMARY KEY,
petName VARCHAR2(25) NOT NULL UNIQUE,
petPrice NUMBER DEFAULT(0),
petAge NUMBER NOT NULL,
petSex CHAR(2) NOT NULL,
petIsSale CHAR(2) NOT NULL)TABLESPACE PETSHOP
/
create table lsz."user"(
userId VARCHAR2(4000) PRIMARY KEY,
userName VARCHAR2(25) NOT NULL UNIQUE,
userAccount NUMBER NOT NULL,
userPassword VARCHAR2(100) NOT NULL,
userBalance NUMBER DEFAULT(1000),
userImage VARCHAR2(1000))TABLESPACE PETSHOP
/
ALTER TABLE "user" MODIFY userAccount VARCHAR2(100)
/
ALTER TABLE pet add userId VARCHAR2(4000)
/
ALTER TABLE pet ADD CONSTRAINT FK_USERID FOREIGN KEY (userId) REFERENCES "user"(userId)
/
ALTER TABLE pet MODIFY userId not null
/
CREATE TABLE lsz. petType(
petTypeId VARCHAR2(4000) PRIMARY KEY,
petTypeName VARCHAR2(100) NOT NULL UNIQUE,
petTypeLevel VARCHAR2(30) NOT NULL,
petTypeImage VARCHAR2(1000))TABLESPACE PETSHOP
/
ALTER TABLE pet add petTypeId VARCHAR2(4000)
/
ALTER TABLE pet add CONSTRAINT FK_PETTYPEID FOREIGN KEY (petTypeId) REFERENCES petType(petTypeId)
/
ALTER TABLE pet MODIFY petTypeId NOT NULL
/
alter table pet add constraint CHECK_PETSEX check (petSex in ('0','1'))
/
CREATE SEQUENCE sequence_user
start with 1000
increment by 1
nocache
nocycle
order
/