);

2.CREATE TABLE person(

pid NUMBER(5),

name VARCHAR2(20) NOT NULL UNIQUE,

CONSTRAINT person_name_uk UNIQUE (name)

);

3.ALTER TABLE person ADD CONSTRAINT person_name_uk UNIQUE (name); 修改唯一约束

四、检查约束(CHECK)

1.CREATE TABLE person(

pid NUMBER(5),

name VARCHAR2(20) NOT NULL UNIQUE,

age NUMBER(2) NOT NULL CHECK(age BETWEEN 0 AND 100),

sex VARCHAR2(2) NOT NULL DEFAULT '男' CHECK(sex IN('男','女'))

);

2.CREATE TABLE person(

pid NUMBER(5),

name VARCHAR2(20) NOT NULL UNIQUE,

age NUMBER(2) NOT NULL,

sex VARCHAR2(2) NOT NULL DEFAULT '男',

CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 100),

CONSTRAINT person_sex_ck CHECK(sex IN('男','女'))

);

3.ALTER TABLE person ADD CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 100);

ALTER TABLE person ADD CONSTRAINT person_sex_ck CHECK(sex IN('男','女'));

五、主-外键约束(FOREIGN KEY)

1.CREATE TABLE book(

bid NUMBER(3) NOT NULL,

person_id NUMBER(5) NOT NULL,

name VARCHAR2(50) NOT NULL,

price NUMBER(5,2) NOT NULL,

CONSTRAINT person_book_personid_fk FOREIGN KEY (price) REFERENCES person(pid) ON DELETE CASCADE

);

六、删除约束

ALTER TABLE table_name DROP CONSTRAINT constraint_name 根据提供的table_name表删除constraint_name约束

ROWNUM Oracle每个表中都会有一个ROWNUM列

视图管理:

CREATE VIEW view_name AS SELECT * FROM emp; 创建视图

DROP VIEW view_name 删除视图

CREATE OR REPLACE VIEW view_name AS SELECT .... 创建或替换视图

CREATE OR REPLACE VIEW view_name AS SELECT ....

WITH CHECK OPTION 不能更新创建视图时列的条件

WITH READ ONLY 只准许视图能读

序列管理:

CREATE SEQUENCE myseq;

INCREMENT BY 2　指定步长　 CREATE SEQUENCE myseq INCREMENT BY 2; 步长为:2

START WITH 0　 指定起始位置

MAXVALUE 10 指定最大值

CYCLE while

DROP SEQUENCE myseq; 删除序列

同义词:

CREATE SYNONYM 同义词名称FOR 用户名.表名称; 创建同义词

CREATE SYNONYM emp FOR SCOTT.emp;

DROP SYNONYM emp;

用户管理:

创建用户:

CREATE USER 用户名　IDENTIFIED BY　密码　　　　 创建用户和密码

CREATE USER lixing IDENTIFIED BY lixing 创建用户和密码

用户授权:

GRANT 权限1,权限2.....　TO 用户名 给用户授权

GRANT CREATE SESSION TO lixing; 为lixing用户创建session权限

GRANT CONNECT,RESOURCE TO lixing; 为lixing用户赋CONNECT RESOURCE权限

GRANT SELECT,DELETE,UPDATE ON scott.emp TO lixing; 把scott.emp表的增、删、改的权限赋给lixing用户

REVOKE SELECT,DELETE,UPDATE ON scott.emp FROM lixing; 回收scott.emp表赋给用户lixing的权限

修改用户：

ALTER USER 用户名IDENTIFIED BY 新密码; 修改密码

ALTER USER lixing IDENTIFIED BY newlixing; 修改用户lixing密码为newlixing

ALTER USER 用户名PASSWORD EXPIRE; 用户第一次登陆要求修改密码

ALTER USER lixing PASSWORD EXPIRE; 要求用户第一次登陆必须修改密码

ALTER USER lixing ACCOUNT LOCK; 锁定用户lixing

ALTER USER lixing ACCOUNT UNLOCK; 解除锁定用户

数据备份与还原

数据库备份：exp

数据库还原:imp

本文出自 “李新的博客” 博客