建表、约束、视图、序列、集合操作、ROWNUM、同义词、用户管理

1.2、本次预计讲解的知识点

1、表的建立及常用的字段类型，表结构的修改

2、 SQL语法中的五种约束，约束的更改

3、 ROWNUM类型（分页）

4、数据的集合操作：交、差、并

5、视图的作用及使用

6、同义词、序列

7、了解用户的创建及简单的管理

1、具体内容

1.1、ROWNUM（重点）

ROWNUM在Oracle中的任何位置都可以使用。只要是查询语句。

例如：有如下的查询语句：

SELECT * FROM emp ;

在以上的查询结果前，希望可以有一个编号存在。所以在Oracle中实际上为每个表提供了这样的一个序列列，此列的类型就是ROWNUM。

例如：验证ROWNUM的作用

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp ;

例如：现在要求查询出前10条记录

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM<=10 ;

例如：要查询后10条记录

• 第一种做法：指定ROWNUM的范围

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM BETWEEN 11 AND 20 ;

但是不能查询出任何的数据，如果要想正确的显示后面的部分，只能使用子查询完成。

• 子查询

SELECT * FROM (SELECT ROWNUM rn,empno,ename,job,sal,hiredate FROM emp WHERE ROWNUM<=20) A WHERE a.rn>10 ;

1.2、表的创建及管理（重点）

在SQL语句之中已经明确的规定出了表的创建语法，那么此语法在各个数据库上都是通用的。

1.2.1、建立及删除表

要想创建一张表，则必须使用CREATE TABLE语法，此语法定义如下：

CREATE TABLE 表名称

(

字段名称（列名称）数据类型 ,

字段名称（列名称）数据类型 [DEFAULT 默认值]

)

那么在Oracle中有多少种数据类型呢？实际上Oracle本身提供的数据类型非常多，但是常用的就以下几种：

No. 数据类型描述

1 VARCHAR2 表示的是一个字符串，在使用的时候必须指定出此字符串的长度：VARCHAR2(30)

2 NUMBER 表示整数：NUMBER(3)，表示数字的长度为3，或者直接使用INT也可以

表示小数：NUMBER(7,2)，表示整数位长度为5，小数位的长度为2，或者直接使用float

3 DATE 表示的是日期，日期必须按照日期的具体格式插入

4 BLOB 表示二进制数据，例如：存放电影、MP3、图片等。

5 CLOB 表示大文本数据，可以存放海量的文字

例如：下面创建一张表：Person

• 人员的ID pid

• 姓名 name

• 年龄 age

• 生日 birthday

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) ,

age INT ,

birthday DATE

) ;

例如：查看以上的表结构

desc person ;

例如：向表中插入若干个数据

INSERT INTO person VALUES (1,'张三',30,to_date('1975-09-02','yyyy-mm-dd')) ;

INSERT INTO person(pid,name,age,birthday) VALUES (2,'李四',31,to_date('1975-09-02','yyyy-mm-dd')) ;

删除表：

表不用就可以直接删除，删除表的语法如下：

DROP TABLE 表名称 ;

例如：删除person表

DROP TABLE person ;

如果按之前的方式将表中的全部数据删除，可以使用DELETE语句，但是此语句使用之后数据依然可以通过事务的回滚进行恢复，在Oracle中提供了一个专门用于截断表的语法，如果表被截断了，则所有的数据将不能使用Transaction进行恢复。

截断表语法（Oracle专用）：

TRUNCATE TABLE 表名称 ;

例如：截断Person表

TRUNCATE TABLE person ;

如果是截断，则表示彻底释放存储空间，不可恢复。

在Oracle中还提供了复制表的命令，语法如下：

CREATE TABLE 表名称 AS (子查询)

例如：要求复制person表，但是不要里面的数据，只要结构

• 在子查询中设置一个条件，此条件永远不满足即可。

CREATE TABLE myperson AS SELECT * FROM person WHERE 1=2 ;

注意：此语法只在Oracle中有效

1.2.2、修改表结构

如果表已经建立完成，但发现建立的表功能不足，则可以修改表结构，但是一般情况下，是不建议去修改表结构的。

修改表结构的语法如下：

• 为表中增加列

ALTER TABLE 表名称 ADD (增加的列的名称类型 [DEFAULT 默认值], 增加的列的名称类型 [DEFAULT 默认值])

例如：为Person表中增加身份证编号和salary两列，如果没有工资的话，则应该按最低工资800进行插入，所以在增加列的时候需要指定salary的默认值。

ALTER TABLE person ADD(idcard VARCHAR(18),salary FLOAT DEFAULT 800) ;

• 修改已有的列

ALTER TABLE MODIFY(修改列的名称，修改列的类型 [DEFAULT 默认值]) ;

例如：修改birthday列，让其默认值为sysdate

ALTER TABLE person MODIFY(birthday DATE DEFAULT sysdate) ;

1.3、约束（重点）

1.3.1、约束的概念及分类

约束（CONSTRAINT）：是对表中插入数据的一种限制。

例如：人员的身份证编号是不能重复的，人员的性别

那么约束一般情况下都是在表建立之初就已经添加好的，当然也可以为已建立的表添加约束。

在SQL中，对于约束有以下几种分类：

• 主键约束：在一张表中只能存在一个，使用PRIMARY KEY 声明。主键约束中已经包含了唯一约束。

• 非空约束：在一张表中可以有多个，表示此字段不能为空。使用NOT NULL声明。

• 唯一约束：在一张表中的此字段的内容不可重复。使用UNIQUE声明。

• 检查约束：对于一个列的某个字段的内容进行限制，使用CHECK声明。

• 主-外键约束：之前的所有约束都是针对于一张表的，而主-外键是针对于两张表的。

1.3.2、主键约束（PK）

主键约束一般在表的第一个字段中使用，例如：ID，表示是一个永久的唯一的编号，本身内容是不能重复的，而且只能存在一个，不能为空。

例如：之前的person表，应该pid设置成主键约束，表示不可重复

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER PRIMARY KEY,

name VARCHAR(50) ,

age INT ,

birthday DATE

) ;

例如：插入两条记录

INSERT INTO person(pid,name,age,birthday) VALUES (1,'张三',30,sysdate) ;

INSERT INTO person(pid,name,age,birthday) VALUES (1,'李四',31,sysdate) ;

以上的两条记录主键重复了，则插入的时候会出现以下的错误提示：

SQL> INSERT INTO person(pid,name,age,birthday) VALUES (1,'张三',30,sysdate) ;

已创建 1 行。

SQL> INSERT INTO person(pid,name,age,birthday) VALUES (1,'李四',31,sysdate) ;

INSERT INTO person(pid,name,age,birthday) VALUES (1,'李四',31,sysdate)

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005270)

第一条sql语句可以正确的执行，但是第二条语句，因为pid的内容重复了，所以不能插入。如果想要为表的约束起一个可以有意义的名字，则必须使用另外一种语法声明约束，此语法如下：

CONSTRAINT [约束的名称] 约束的类型(约束的字段)

约束的名称最好按照以下的格式编写：表名称_列名称_约束代号。

例如：修改以上的数据库创建脚本

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) ,

age INT ,

birthday DATE ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid)

) ;

例如：向表中继续执行插入语句

INSERT INTO person(pid,name,age,birthday) VALUES (1,'张三',30,sysdate) ;

INSERT INTO person(pid,name,age,birthday) VALUES (1,'李四',31,sysdate) ;

观察错误提示：

SQL> INSERT INTO person(pid,name,age,birthday) VALUES (1,'张三',30,sysdate) ;

已创建 1 行。

SQL> INSERT INTO person(pid,name,age,birthday) VALUES (1,'李四',31,sysdate) ;

INSERT INTO person(pid,name,age,birthday) VALUES (1,'李四',31,sysdate)

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (SCOTT.PERSON_PID_PK)

1.3.3、非空约束（NK）

一个列的内容不允许为空，称为非空约束，一般在建立一张表的时候，如果表中的字段没有指定非空约束，则表示允许为空。

注意：在为表添加列时，如果表中已有数据，则不能指定该列为not null；

例如：将person表中的姓名和生日两个列设置成不能为空

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT,

birthday DATE NOT NULL ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid)

) ;

例如：插入记录，一个是名字为空，一个是生日为空

INSERT INTO person(pid,name,age,birthday) VALUES (1,null,30,sysdate) ;

INSERT INTO person(pid,name,age,birthday) VALUES (2,'李四',31,null) ;

执行时将出现以下的错误提示：

SQL> INSERT INTO person(pid,name,age,birthday) VALUES (1,null,30,sysdate) ;

INSERT INTO person(pid,name,age,birthday) VALUES (1,null,30,sysdate)

*

第 1 行出现错误:

ORA-01400: 无法将 NULL 插入 ("SCOTT"."PERSON"."NAME")

SQL> INSERT INTO person(pid,name,age,birthday) VALUES (2,'李四',31,null) ;

INSERT INTO person(pid,name,age,birthday) VALUES (2,'李四',31,null)

*

第 1 行出现错误:

ORA-01400: 无法将 NULL 插入 ("SCOTT"."PERSON"."BIRTHDAY")

3.3.4、唯一约束（UK）

列中的数据不能重复。例如：人员有身份证编号，那么此编号是不能重复的。

例如：修改之前的表结构，加入身份证编号

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL ,

birthday DATE ,

idcard VARCHAR(18) UNIQUE,

CONSTRAINT person_pid_pk PRIMARY KEY(pid)

) ;

例如：执行以下两条语句，身份证编号重复

INSERT INTO person(pid,name,age,birthday,idcard) VALUES (1, '张三',30,sysdate,'110102197201025689') ;

INSERT INTO person(pid,name,age,birthday,idcard) VALUES (2,'李四',31, sysdate,'110102197201025689') ;

以上的两个身份证编号是重复的，所以插入时，会出现以下的错误提示：

SQL> INSERT INTO person(pid,name,age,birthday,idcard) VALUES (1, '张三',30,sysdate,'110102197201025689') ;

已创建 1 行。

SQL> INSERT INTO person(pid,name,age,birthday,idcard) VALUES (2,'李四',31, sysdate,'110102197201025689') ;

INSERT INTO person(pid,name,age,birthday,idcard) VALUES (2,'李四',31, sysdate,'110102197201025689')

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005278)

以上的错误提示不是很明确，所以对于唯一约束也可以使用CONSTRAINT语法进行加入。

例如：修改之前的创建脚本

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL ,

birthday DATE ,

idcard VARCHAR(18) ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT person_idcard_uk UNIQUE(idcard)

) ;

1.3.5、检查约束（CK）

检查约束指的是对插入的内容进行限制，:列如：性别，就只有：“男”、“女”、“中”，年龄的范围在0~200岁。

例如：在person表中增加sex字段，同时将sex及age进行检查约束的配置

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL CHECK (age BETWEEN 0 AND 200) ,

birthday DATE ,

sex VARCHAR(10) CHECK(sex IN ('男','女','中')),

idcard VARCHAR(18) ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT person_idcard_uk UNIQUE(idcard)

) ;

例如：向表中增加以下两条记录

INSERT INTO person(pid,name,age,birthday,idcard,sex) VALUES (1, '张三',3000,sysdate,'110102197201025689','男') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex) VALUES (2,'李四',31, sysdate,'110102197201025699','不知道') ;

则执行以上的插入语句之后，会出现如下的错误提示：

SQL> INSERT INTO person(pid,name,age,birthday,idcard,sex) VALUES (1, '张三',3000,sysdate,'110102197201025689','男') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex) VALUES (1, '张三',3000,sysdate,'110102197201025

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005285)

SQL> INSERT INTO person(pid,name,age,birthday,idcard,sex) VALUES (2,'李四',31, sysdate,'110102197201025699','不知道') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex) VALUES (2,'李四',31, sysdate,'11010219720102569

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (SCOTT.SYS_C005286)

例如：为检查约束设置约束名称

DROP TABLE person ;

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL ,

birthday DATE ,

sex VARCHAR(10) ,

idcard VARCHAR(18) ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT person_idcard_uk UNIQUE(idcard) ,

CONSTRAINT person_age_ck CHECK (age BETWEEN 0 AND 200) ,

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

) ;

1.3.6、主-外键约束（FK）

之前的所有约束都是在一张表上建立的，主-外键约束是在两张表中，并且做表关联使用。

例如：一个人属于一个小组

• 如果要完成以上对于表，则需要两张：一个是Person表，另外一个是Group表。

DROP TABLE person ;

DROP TABLE team;

CREATE TABLE team

(

tid VARCHAR(30) PRIMARY KEY ,

name VARCHAR(50)

);

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL ,

birthday DATE ,

sex VARCHAR(10) ,

idcard VARCHAR(18) ,

tid VARCHAR(30) ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT person_idcard_uk UNIQUE(idcard) ,

CONSTRAINT person_age_ck CHECK (age BETWEEN 0 AND 200) ,

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

) ;

为表中插入以下的数据：

INSERT INTO team(tid,name) VALUES ('001','第一组') ;

INSERT INTO team(tid,name) VALUES ('002','第一组') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (1, '张三',30,sysdate,'110102197201025689','男','003') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (2,'李四',31, sysdate,'110102197201025699','女','005') ;

从实际的效果来看，003和005两个组并不存在，所以以上的数据无效，也就是说，实际上在插入人所属的组编号时，必须在组表中已经有了明确的定义，所以此时就必须使用主-外键关联。

DROP TABLE person ;

DROP TABLE team;

CREATE TABLE team

(

tid VARCHAR(30) PRIMARY KEY ,

name VARCHAR(50)

);

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL ,

birthday DATE ,

sex VARCHAR(10) ,

idcard VARCHAR(18) ,

tid VARCHAR(30) ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT person_idcard_uk UNIQUE(idcard) ,

CONSTRAINT person_age_ck CHECK (age BETWEEN 0 AND 200) ,

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

CONSTRAINT person_tid_fk FOREIGN KEY(tid) REFERENCES team(tid)

) ;

上面已经为表中成功的加入好了关系，下面再执行以上的插入数据的语句，会出现以下的错误：

SQL> INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (1, '张三',30,sysdate,'110102197201025689','男','003') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (1, '张三',30,sysdate,'1101021972010

*

第 1 行出现错误:

ORA-02291: 违反完整约束条件 (SCOTT.PERSON_TID_FK) - 未找到父项关键字

SQL> INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (2,'李四',31, sysdate,'110102197201025699','女','005') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (2,'李四',31, sysdate,'1101021972010

*

第 1 行出现错误:

ORA-02291: 违反完整约束条件 (SCOTT.PERSON_TID_FK) - 未找到父项关键字

因为“003”和“005”这两个编号在team中根本不存在。所以此时要想插入，则必须换上有意义的编号。

但是在使用主-外键关联的时候有以下注意点：

1、主表中的ID必须是主键，才能使用FOREIGN KEY

2、删除的时候应该先删除子表（Person），再删除主表（Team）

• 如果删除顺序出错：

DROP TABLE team;

DROP TABLE person ;

则删除时会出现以下的错误：

DROP TABLE team

*

第 1 行出现错误:

ORA-02449: 表中的唯一/主键被外键引用

3、当然，主表也可以进行强制性的删除，语句如下：

DROP TABLE team CASCADE CONSTRAINT ;

4、如果现在要想删除主表中的一条记录，但是此主表的记录已经被子表所使用，则无法删除

SQL> DELETE FROM team WHERE tid='001' ;

DELETE FROM team WHERE tid='001'

*

第 1 行出现错误:

ORA-02292: 违反完整约束条件 (SCOTT.PERSON_TID_FK) - 已找到子记录

5、如果现在希望主表的数据被删除之后，子表的数据也同时被删除，则可以在建立表的同时指定好级联删除：ON DELETE CASCADE指定。

例如：修改数据库创建脚本

DROP TABLE person ;

DROP TABLE team;

CREATE TABLE team

(

tid VARCHAR(30) PRIMARY KEY ,

name VARCHAR(50)

);

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) NOT NULL ,

age INT NOT NULL ,

birthday DATE ,

sex VARCHAR(10) ,

idcard VARCHAR(18) ,

tid VARCHAR(30) ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,

CONSTRAINT person_idcard_uk UNIQUE(idcard) ,

CONSTRAINT person_age_ck CHECK (age BETWEEN 0 AND 200) ,

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

CONSTRAINT person_tid_fk FOREIGN KEY(tid) REFERENCES team(tid) ON DELETE CASCADE

) ;

INSERT INTO team(tid,name) VALUES ('001','第一组') ;

INSERT INTO team(tid,name) VALUES ('002','第一组') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (1, '张三',30,sysdate,'110102197201025689','男','001') ;

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (2,'李四',31, sysdate,'110102197201025699','女','002') ;

但是：级联删除一般只在需要的地方使用，如果主表的数据消失后，希望子表也同时消失才指定，如果不希望，则不要设置，另外需要说明的是，在主-外键关联中，可以将子表的关联字段设置成“null”。

INSERT INTO person(pid,name,age,birthday,idcard,sex,tid) VALUES (3, 'Xxx',30,sysdate,'110102197201035689','男',null) ;

1.3.7、更改约束

可以直接为已建立好的表增加约束，也可以删除约束。

增加约束的语法：

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称约束类型(约束字段)

例如：将之前最早的Person创建脚本取出

CREATE TABLE person

(

pid NUMBER ,

name VARCHAR(50) ,

age INT ,

birthday DATE

) ;

为person表中增加主键约束

ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid) ;

为age增加检查约束

ALTER TABLE person ADD CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 200) ;

删除约束：

ALTER TABLE DROP CONSTRAINT 约束名称

例如：删除Person表中的检查约束

ALTER TABLE person DROP CONSTRAINT person_age_ck ;

例如：删除主键约束

ALTER TABLE person DROP CONSTRAINT person_pid_pk ;

注意：

为表增加约束的时候，一定要保证表中的数据没有违反约束

例如：为person表中增加两个重复的主键

INSERT INTO person VALUES (1,'张三',3000,sysdate) ;

则再执行增加约束时，出现以下的问题：

ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid)

*

第 1 行出现错误:

ORA-02437: 无法验证 (SCOTT.PERSON_PID_PK) - 违反主键

1.4、集合（掌握）

SQL中支持三种集合操作：并、交、差

• UNION：表示并，将多个结果组合到一个查询中，不包含重复行

• UNION ALL：表示将多个结果组合到一个查询中，包含重复行

• INTERSECT：交，只返回多个结果集中相同的部分

• MINUS：差，返回不一样的地方

例如：现在建立一张只包含20部门雇员的雇员表

CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20 ;

1、验证union

将emp与emp20两张表关联

SELECT * FROM emp UNION SELECT * FROM emp20 ;

因为emp20表中的记录在emp中已经存在了，是不会包含重复记录的，如果要想显示出所有的记录，则可以使用UNION ALL。

2、验证union all

SELECT * FROM emp UNION ALL SELECT * FROM emp20 ;

3、验证INTERSECT

SELECT * FROM emp INTERSECT SELECT * FROM emp20 ;

4、验证MINUS

SELECT * FROM emp MINUS SELECT * FROM emp20 ;

1.5、视图（掌握）

视图：封装一条复杂的查询语句。

例如：有如下的一条查询语句

SELECT d.dname,e.ename,dm.avg,dm.min,dm.count

FROM dept d,emp e,(SELECT deptno,AVG(sal) avg,MIN(sal) min,COUNT(empno) count FROM emp GROUP BY deptno) dm

WHERE d.deptno=e.deptno AND d.deptno=dm.deptno AND e.sal=dm.min ;

如果现在有多个地方都要查询出此信息，每次编写的话，则会很麻烦，所以此时，可以将以上的查询语句封装成一个视图。

创建视图的语法：

CREATE VIEW 视图的名称 AS 子查询

例如：将上面的查询封装成一个视图

CREATE VIEW myview AS

SELECT d.dname,e.ename,dm.avg,dm.min,dm.count

FROM dept d,emp e,(SELECT deptno,AVG(sal) avg,MIN(sal) min,COUNT(empno) count FROM emp GROUP BY deptno) dm

WHERE d.deptno=e.deptno AND d.deptno=dm.deptno AND e.sal=dm.min ;

则以后直接查询此视图即可：

SELECT * FROM myview ;

例如：创建一张只包含20部门雇员信息的视图

CREATE VIEW myview AS SELECT * FROM emp WHERE deptno=20 ;

但是执行之后，提示，此名称已被使用。此时可以先将视图进行删除之后再创建一个新的视图：

DROP VIEW myview ;

但是如果现在每次修改视图的时候都需要先删除，再重新创建，则肯定很麻烦，所以在视图的创建语法之中，提供了替换功能，语法如下：

CREATE OR REPLACE 视图名称 AS 子查询

如果视图不存在，则直接创建，如果视图存在了，则自动删除之后再进行创建。

CREATE OR REPLACE VIEW myview AS SELECT * FROM emp WHERE deptno=20 ;

视图可以更新吗？

例如：更新视图，将雇员编号为7369的部门编号修改为30

UPDATE myview SET deptno=30 WHERE empno=7369 ;

执行以上的语句之后，发现视图已经被更新了。再查询视图时，发现雇员编号为7369的雇员不在了，因为编号已经更改了 —— 创建视图的条件被修改了。但是此种做法明显不合适，因为要更新的话，至少创建视图的条件字段不能被更新，更准确的来说，视图也不应该被更新。

在创建视图可以使用以下两个属性：

• WITH CHECK OPTION：视图的创建条件不能更改

• WITH READ ONLY：只能是只读

例如：验证WITH CHECK OPTION

CREATE OR REPLACE VIEW myview AS SELECT * FROM emp WHERE deptno=20

WITH CHECK OPTION ;

此时，再执行以上的更新语句：

UPDATE myview SET deptno=30 WHERE empno=7369

*

第 1 行出现错误:

ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

发现，此时视图创建的条件不能更改了。

但是，如果此时更改其他字段呢？

例如：将7369的雇员姓名修改为张三

UPDATE myview SET ename='张三' WHERE empno=7369 ;

但是以上的做法也不是很合适，所以最好的做法是让视图设置成只读，不可更改。

例如：建立只读视图

CREATE OR REPLACE VIEW myview AS SELECT * FROM emp WHERE deptno=20

WITH READ ONLY ;

再执行以上的更新语句：

UPDATE myview SET ename='张三' WHERE empno=7369

*

第 1 行出现错误:

ORA-01733: 此处不允许虚拟列

出现了错误提示，不允许更新视图。

明确：视图具备的只是提供了方便的查询功能，不应该具备更新的操作，更新应该在表中完成。

1.6、序列（重点）

一般的数据库中都会有一种字段属于自动增长列，在Oracle中如果要想实现自动增长功能，则必须使用序列（sequence）。

创建序列的语法：

CREATE SEQUENCE sequence

[INCREMENT BY n][START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE n|NOCACHE}] ;

最简单的创建序列语法：

CREATE SEQUENCE 序列名称 ;

参数：

• INCREMENT BY n：每次增长的幅度

• START WITH n：从数字几开始

• MAXVALUE n：序列的最大值

• NOMAXVALUE：没有最大值

• MINVALUE n：序列的最小值

• NOMINVALUE：没有最小值

• CYCLE、NOCYCLE：是否循环。1~1000

例如：创建一个myseq的序列

CREATE SEQUENCE myseq ;

删除序列：

DROP SEQUENCE myseq ;

但是创建完的序列是一个单独的对象，本身并没有和表有什么关系，如果要想与表产生关系，则必须在插入数据的时候手工调用序列，在序列中有两个操作：

• currVal：表示当前的序列值

• nextVal：表示下一个值

例如：创建一张表

CREATE TABLE mytab(

id NUMBER PRIMARY KEY NOT NULL ,

age NUMBER

) ;

向表中增加数据，id要进行自动的增长：

INSERT INTO mytab(id,age) VALUES (myseq.nextVal,myseq.currVal) ;

1.7、同义词（了解）

例如：以前经常使用如下代码

SELECT sysdate FROM dual ;

但是dual表到底是什么呢？找到此表的位置

1、使用system用户进行登陆

CONN system/manager ;

证明：此表现在不是system的

2、使用sys用户进行登陆

CONN sys/change_on_install AS SYSDBA ;

在sys用户下有此表。但是之前讲解过，如果不同的用户想访问其他用户的表，则在访问前必须加上用户的名称。但是现在sys下的dual表并没有此名称，那么实际上这就是同义词的作用。

同义词就是可以通过一个名字表示一个用户名.表名称（模式.表名称）。

创建同义词的语法：

CREATE SYNONYM 同义词的名称 FOR 模式.表名称 ;

例如：创建scott用户下的emp为一个同义词

CREATE SYNONYM emp FOR scott.emp ;

删除同义词：

DROP SYNONYM 同义词的名称

1.8、用户管理（了解）

回顾：Oracle的基本用户

• 超级管理员：sys / change_on_install

• 普通管理员：syste / manager

• 普通用户：scott / tiger

如果现在用户不够了，需要一个新的用户的话，则就必须进行创建，但是如果要想创建一个用户，则一定要在管理员登陆的session中完成。

增加新用户：

CREATE USER 用户名 IDENTIFIED BY 密码（密码不能以数字开头）

例如：创建一个test用户，密码为test123

CREATE USER test IDENTIFIED BY test123 ;

此时，已经成功的创建好了一个用户，但是此用户没有创建session的权限，不能创建session就不能进行登陆。如果想让用户使用则必须对用户进行授权操作。

授权的语法：

GRANT 权限1,权限2 TO 用户

例如：将创建session的权限授予test用户

GRANT CREATE SESSION TO test ;

那么此时，就可以利用test进行登陆了。登陆之后实际上还是什么都做不了，因为还需要给用户资源访问的权限，如果要想创建表，则必须有对应的表空间。如果此时要依次的授权给用户，会很麻烦，所以在Oracle中提供了两个角色（一组权限）：CONNECT，RESOURCE。可以直接把这两个角色授权给新用户，那么就具备了连接、访问资源的权限了。

例如：将CONNECTION和RESOURCE角色赋予test用户

GRANT CONNECT,RESOURCE TO test ;

那么，此时，test用户就具备了创建表的能力。

用户的锁定还有解锁，以及用户密码的修改也都可以通过用户的更改完成。

例如：修改test用户的密码，密码修改为abc

ALTER USER test IDENTIFIED BY abc ;

例如：将一个用户暂时锁定

ALTER USER test ACCOUNT LOCK ;

例如：将一个用户解锁

ALTER USER test ACCOUNT UNLOCK ;

例如：让一个用户的密码失效

ALTER USER test PASSWORD EXPIRE ;

但是，此时，此用户只是具备了自己操作的能力，即：只能查询自己的表。之前可以在system用户下查询scott的表。但是在新用户下就不能查询了，这是因为scott没有把对应的查询权限给新用户，所以如果在新用户下想进行其他用户的表查询，就必须为其授权

GRANT SELECT,DELETE ON scott.emp TO test ;

以上是把scott.emp表的查询及删除的权限给test用户。

既然可以授权给用户，那么就可以回收权限

例如：将scott.emp表的查询权限从test用户上回收

REVOKE SELECT ON scott.emp FROM test ;

ITPub博客

建表、约束、视图、序列、集合操作、ROWNUM、同义词、用户管理