aselect e.empno, e.ename, e.hiredate, e.sal, e.deptno, d.deptno, d.dname,d.loc
from emp e, dept d
where e.deptno = d.deptno
----------------------------------
select e.empno, e.ename, e.hiredate, e.sal, e.deptno, d.deptno, d.dname,d.loc
from emp e, dept d
where e.deptno(+) = d.deptno
-----------------------------------
select e.empno, e.ename, e.hiredate, e.sal, e.deptno, d.deptno, d.dname,d.loc
from emp e, dept d
where e.deptno = d.deptno(+)
-----------------------------------
hr/hr
-----------------------------------
select e.employee_id,e.first_name,e.last_name,
d.department_id
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100
-------------------------------------
scott/tiger
-------------------------------------
select 'worker '||worker.ename||' manager is '||manager.ename
from emp worker, emp manager
where worker.mgr = manager.empno
-------------- cross join --> 做一个笛卡尔积 -----------------------
select e.empno, e.ename, d.deptno, d.dname
from emp e
cross join dept d
-------------------------------------
select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
--------- 这两个SQL语句相互等价 -----
--------- inner join ----------------
select e.empno, e.ename, d.deptno, d.dname
from emp e
inner join dept d
on e.deptno = d.deptno
----------- 等价于 ---------------
--------- natural join -----------
select empno, ename,deptno, dname
from emp e
natural join dept
----------- join using ------------------------
select e.empno, e.ename, deptno, d.dname
from emp e
join dept d
using(deptno)
-------------------------
select e.empno, e.ename, deptno, d.dname
from emp e
join dept d
using(deptno)
where d.dname = 'SALES'
--and deptno = 30
------------------------------
select e.ename, e.empno, e.sal, e.deptno, d.dname
from emp e
join dept d
on e.deptno = d.deptno
and e.empno = 7839
------------------------------
hr/hr
------------------------------
select e.employee_id, e.first_name, e.last_name,
department_id, d.department_name,
location_id, street_address, postal_code
from employees e
join departments d
using(department_id) --same column name
join locations l
using (location_id) --same column name
----------- 等价于 ------------------------
select e.employee_id, e.first_name, e.last_name,
e.department_id, d.department_name,
d.location_id, street_address, postal_code
from employees e, departments d, locations l
where e.department_id = d.department_id --not same column name
and d.location_id = l.location_id --not same column name
-------------------------------------------
select e.ename, e.empno, e.sal, e.deptno, d.dname
from emp e
left join dept d
on e.deptno = d.deptno
--------------- 等价于 ----------------------------
select e.ename, e.empno, e.sal, e.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno(+)
---------------------------------------------------
select e.ename, e.empno, e.sal, e.deptno, d.dname
from emp e
right join dept d
on e.deptno = d.deptno
--------------- 等价于 ----------------------------
select e.ename, e.empno, e.sal, e.deptno, d.dname
from emp e, dept d
where e.deptno(+) = d.deptno
---------------------------------------------------
-- scott/tiger ---------------------------
-- 有下级的领导者 ----------------
select e.empno, e.ename, e.sal, e.deptno, e.mgr
from emp e
where e.empno in
( select distinct e2.mgr
from emp e2
)
----------------------------------
-- 如果求没有下级的被领导者 ------
----------------------------------
-- 这样，无求出结果
select e.empno, e.ename, e.sal, e.deptno
from emp e
where e.empno not in
( select distinct e2.mgr
from emp e2
)
-- 这样，就可以求出结果了
select e.empno, e.ename, e.sal, e.deptno,e.mgr
from emp e
where e.empno not in
( select distinct e2.mgr
from emp e2
where e2.mgr is not null
)
------------------------------
> any 表示大于集合中最小的, 则满足条件；
< any 表示小于集合中最大的，则满足条件；
------------------------------
select e.empno, avg(e.sal)
from emp e
group by empno
having avg(sal) >= any(
select max(avg(sal))
from emp
group by deptno
)
---------------------------------
select t.deptno, t.pingjun
from (select e2.deptno, avg(e2.sal) pingjun
from emp e2
group by e2.deptno
) t
where t.pingjun > any(3000, 2000, 1000);
---------------------------------- 这个语句没有结果
select t.empno, t.pingjun
from ( select e2.empno, avg(sal) pingjun
from emp e2
group by empno
) t
where t.pingjun> any (select sal from emp where sal is not null)
---------------------------------------------这个语句同样没有结果
select t.deptno, t.pingjun
from (select deptno, avg(sal) pingjun
from emp e
group by deptno
) t
where t.pingjun>= any(select sal
from emp
where sal is not null
)
----------------------------------------
select * from emp where empno = &p_empno;
--- & 在oracle解析 SQL 语句时，获取变量
----------------------------------------
select * from emp where ename='&p_ename';
-- 比较
select * from emp where ename = &p_ename;
-- ename='&p_ename' 输入参数为 KING ；
-- ename = &p_ename 输入参数为 'KING' ；
-----------------------------------------
--define p_ename = 'KING' 在命令窗口中可以使用
-------------------------------------------------
数据对象
oracle 数据库可以包含多种数据结构。
（1）表：用于存储数据；
（2）视图：一个或多个表中数据的子集；
（3）序列：数据值生成器；
（4）索引：提高某些查询的性能；
（5）同义词：给出对象的替代名称；
oracle9i表结构
（1）在任何时候都可以创建表，即使当用户正当使用数据库也是如此。
（2）无需指定任何表空间的大小，表的大小最终由整体分配给数据的空间量决定。
但是，估计表将来使用的空间大小很重要；
（3）表结构可以联机修改；
命名规则
（1）必须以字母开头；
（2）长度必须是1-30 个字符；
（3）只能包括A-Z、a-z、0-9、_、$ 和 # ；
（4）不能与同一用户拥有的其他对象重名；
（5）不能是 oracle 服务器的保留字；
注：名称不区分大小写。
create table 语句
必须有一下条件才能使用此命令；
（1）create table 权限；
（2）一个存储区；
create table [schema.] table
(column datatype[default expr][, ……])；
schema 是搜有者的姓名；
table 是表名称；
default expr 指定一个默认值，当insert语句中没有指定值时，则使用该默认值；
column 是列的名称；
dataType 是列的数据类型和长度
-------------------------------------------------
引用其他用户的表
eg.
select * from scott.emp;
-------------------------------------------------
--- 查看数据表的 表名，表类型 和 注释 -----------
select *from user_tab_comments
-- USER_TAB_COMMENTS describes comments on the tables and views owned by the current user.
-- Its columns are the same as those in "ALL_TAB_COMMENTS".
select *from ALL_TAB_COMMENTS
-------------------------------------------------------------
-- Create table
create table TEST
(
ID VARCHAR2(5) not null,
NAME VARCHAR2(10) not null
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST
add constraint TEST_UNIQUE unique (ID, NAME)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into test values('22',null);
insert into test values('22',null);
----------------------------------------------------------------------------
-- 可以通过查看 user_constraint 数据字典表来了解为特定的表定义的约束 ----
select * from user_constraints
select u.owner,u.table_name, u.constraint_name,u.constraint_type
from user_constraints u
where table_name = 'JOBS'
-- USER_CONSTRAINTS describes all constraint definitions on tables owned by the current user.
-- Its columns are the same as those in "ALL_CONSTRAINTS".
/* 比较重要的几个列
Column Datatype NULL Description
------------------------------------------------------------
OWNER VARCHAR2(30) NOT NULL Owner of the constraint definition
CONSTRAINT_NAME VARCHAR2(30) NOT NULL Name of the constraint definition
CONSTRAINT_TYPE VARCHAR2(1) Type of constraint definition:
(1)C (check constraint on a table)
(2)P (primary key)
(3)U (unique key)
(4)R (referential integrity)
(5)V (with check option, on a view)
(6)O (with read only, on a view)
TABLE_NAME VARCHAR2(30) NOT NULL Name associated with the table (or view)
with constraint definition
*/
---------------------------------------------
-- 常用的计算系统时间的方式 --
-- 用时间戳的方式
select to_timestamp(sysdate,'yyyy-mm-dd hh24:mi:ss.ff3')
from dual;
-- 不可以这样写
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss.ff3') from dual; *
-- ORA-01821: date format not recognized（时间格式无法识别）
-------------------------------
-- 这样 就可以了
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss.') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss.ffz') from dual;
-- date format not recognized
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss.fz') from ;
-- date format not recognized
create table test_timestamp (dd timestamp(6));
insert into test_timestamp values(sysdate ) ;
commit;
select *from test_timestamp
-- 25-NOV-08 09.30.44.000000 PM
select to_char(dd,'yyyy-mm-dd hh24:mi:ss') from test_timestamp;
-- 2008-11-25 21:30:44
select to_char(dd,'yyyy-mm-dd hh24:mi:ss.ff3') from test_timestamp;
-- 2008-11-25 21:30:44.000
select to_char(dd,'yyyy-mm-dd hh24:mi:ss.ff6') from test_timestamp;
-- 2008-11-25 21:30:44.000000
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
operation result description
---------------------------------------------
date + number date Adds a number of days to a date
date - number date Subtracts a number of days from a date
date - date Number of days Subtracts one date from another
date + number/24 date Adds a number of hours to a date
-----------------------------------------------------
SYSDATE is a SQL function that returns the current date and time
Date Functions
Function Description
---------------------------------
mounths_between Number of months between teo dates
add_months Add calendar months to date
Next_day Next day of the date specified
last_day Last day of the month
round Round date
trunc Truncate date
--------------------------------
-- 检查当前用户的权限
select * from session_privs
--SESSION_PRIVS lists the privileges that are currently available to the user.
---------------------------------
-- show parameter cache_size
oracle 数据库中的表
(1)用户表；
是由用户创建的和维护的表的集合。
包含用户信息；
(2)数据字典
是由oracle server 创建和维护的表的集合。
包含数据库的信息；
注：所有的数据字典都归属 SYS 用户所有。
数据字典视图共有四类，每一类都有反映其预期用途的独特前缀。
前缀 说明
---------------------------------
user_ 这些视图包括关于应户拥有的对象的信息。
all_ 这些视图包括关于用户可以访问的所有表(对象表和关系表)
DBA_ 这些视图是受限视图，它们只能由被指定了 DBA 角色的人员访问。
V$_ 这些视图是动态性能视图，反映数据库服务器、内存和锁的动态性能。
-----------------------------------------
查询数据字典
查看用户拥有的表名称
select t.table_name from user_tables t
-- USER_TABLES describes all relational tables owned by the current user.
-- Its columns are the same as those in ALL_TABLES.
-- To gather statistics for this view, use the SQL ANALYZE statement.
查看用户拥有的独特的对象类型。
select distinct uo.object_type from user_objects uo
select uo.object_name, uo.object_type from user_objects uo
查看用户拥有的表、视图、同义词和序列
select * from user_catalog
或
select * from cat
数据类型 说明
----------------------------------------------
varchar(size) 可变长度字符数据(必须指定最大 size；最小 size 为1；最大 size 为 4000)
char(size) 固定长度字符数据(默认和最小 size 为 1；最大 size 为 2000 )
number(p,s) 可变长度数字（精度是指十进制数字的总数，而小数位数是指小数位数点右边的数字的位数
精度范围可以从 1 到 38，而小数位数范围可以从 -84 到 127）
date 日期和时间值（公元前 4712 年 1 月 1 日到公元 9999 年 12 月 31 日之间的日期和时间值，
精确到最接近的那一秒）。
long 可变长度字符数据，最多为2千兆字节
clob 字节数据，最多为4千兆字节
RAW and lONG RAW 原始（Raw）二进制数据
（RAW(size)必须指定最大 size，最大的 size 为 2000;
lONG RAW 可变长度的原始二进制数据，最多为 2 千兆字节）
blob 二进制数据，最多为4千兆字节
bfile 存储在外部文件中的二进制数据；最多为4千兆字节；
rowid 64位基本编号系统（64 base number system）,表示行在表中的唯一地址。
注：
(1) 当使用子查询创建表时，不会复制 long 列。
(2) long 列不能包含在 group by 或 order by 子句中。
(3) 每个表只能使用一个 long 列。
(4) 最好使用 clob 列，而不是 long 列。
-----------------------------------------
日期时间数据类型
数据类型 说明
----------------------------------------------------------------------------
timestamp 允许将时间存储为带有零点几秒的日期。
interval year to mounth 允许将时间存储为年和月的隔离。用于表示两个日期值之间的差异，
其中有效的取值部分只是年和月。
interval day to second 允许将时间存储为天、小时、分钟和秒的间隔。用于表示两个日期
时间值之间的精确差异。
-----------------------------------------------------------------------------
日期时间数据类型
(1) timestamp 数据类型是 date 数据类型的扩展。
(2) 它可以存储 date 数据类型的年、月和日，加上小时、分钟和秒钟以及零点几秒的值。
(3) timestamp [(fractional_seconds_precision)]
fractional_seconds_precision 执行 second 日期时间字段的小数部分的位数，它可以是
从 0 到 9 的任意一个数字。默认值为 6 。
eg.
create table test(
employee_id number,
frist_name varchar2(15),
last_name varchar2(15),
start_date timestamp(7),
end_date timestamp -- 默认值为 6
);
insert into test values(
1001.10,
'yy',
'mm',
to_date('2008-11-01','yyyy-mm-dd'),
sysdate
);
commit;
/*insert into test values(
1001.1234567901234567890123456789012345678901234567890,
'yy',
'mm',
to_date('2008-11-01','yyyy-mm-dd'),
sysdate
);
commit;*/
select * from test;
/*insert into test
(1001,
'yy',
'mm',
to_timestamp('20080112', 'yyyy-mm-dd hh24:mi:ss.ff3'),
to_timestamp(sysdate, 'yyyy-mm-dd hh24:mi:ss.ff3'));*/
(1) timestamp with time zone 数据类型是 timestamp 变体，它的值中包含了时区偏移量。
(2) 时区偏移量是当地时间和 UTC 之间的差值(以小时和分钟数表示)。
timestamp[(fractional_seconds_precision)] with time zone
UTC 代表UTC代表世界协调时，以前成为格林尼治标准时间。
如果两个 timestamp with time zone 值是完全代表 UTC 中的同一时刻，
则无论存储在该数据中的 time zone 偏移量是多少，都认为这两个值是完全相同的。
由于 timestamp with time zone 也可以存储时区信息，所以它特别适合记录必须
跨地理区域收集或协调的日期信息。
eg.
timestamp '1999-04-15 8:00:00 -8:00' 和 timestamp '1999-04-15 11:00:00 -5:00'
是相同的。即，太平洋时间8:00 -am 和 东部标准时间 11:00 am 是相同的。
timestamp '199-04-15 8:00:00 us/pacific'
注：可以使用 fractional_seconds_precision 指定 secon 日期时间字段的小数部分，
它可以是从 0 到 9 的任一数字。默认值为 6。
timestamp with local time 数据类型
(1) timestamp with local time 是 timestamp 的另一种变体，它的值中包含了时区偏移量。
(2) 存储在数据库中的数据会被标准化数据库时区。
(3) 时区偏移量不是作为列数据的一部分进行存储的；
oracle 将按用户的本地会话时区返回数据。
(4) timestamp with local time zone 数据类型通过以下方式指定；
指定方式为 timestamp[(fractional_seconds_precision)] with local time zone
注意：与 timestamp with time zone 不同，您可以指定 timestamp with local time zone
类型的列作为唯一关键字的一部分。时区偏移是当地时间和 UTC 之间的差值(以小时和分钟表示)。
timestamp with local time zone 没有文字值。
可以使用 fractional_seconds_pecision 指定 second 日期时间字段的小数位部分的位数，
它可以是从 0 到 9 的任一数字。默认值为 6.
eg.
create table time_exampe
(order_date timestamp with local time zone);
insert into time_exampe values('15-nov-00 09:34:28 AM');
commit;
select * from time_exampe;
timestamp with local time zone 类型适合与两层应用程序，在两层应用程序中，
您要使用客户端系统的时区来显示日期和时间。
interval year to month 数据类型
interval year to month 数据类型通过使用 year 和 month
日期时间字段存储一个时段。
interval year [(year_percision)] to month
eg.
interval '123-2' year(3) to month 指明间隔为 123 年零 2 个月；
interval '123' year(3) 指明间隔为 123 年整；
interval '300' month(3) 指明间隔为 300 个月；
interval '123' year 这样返回一个错误，因为默认精度为 2，而 '123' 有 3 位数字；
interval year to month 数据类型通过使用 year 和 month 日期时间字段存储一个时段。
使用 interval year to month 可以表示两个日期值之间的差异，其中有效的取值部分只是
年和月。例如，您可以使用此值来度额定120 个月之后进行提交提示，
或检查是否在特定日期之后经过了 6 个月。
指定 interval year to month 如下：
interval year [(year_precision)] to month
在此语法中，year_precision 是 year 时间日期字段中的位数。year_precision 的默认值为 2。
eg.
create table time_example2
(locan_duration interval year(3) to month );
insert into time_example2(locan_duration)
values(interval '120' month(3));
commit;
select to_char(sysdate + locan_duration,'yyyy-mm-dd')
from time_example2;
interval day to second 数据类型
interval day [(day_precision)] to second [(fractional_seconds_precision)]
eg.
interval '45:12:10.222' day to second(3)
指定 4 天 5 小时 12 分 10 秒 千分之 222 秒。
interval '123' year(3)
指明 123 年
interval '7' day
指明 7 天
interval '180' day(3)
指明 180 天
interval day to second 数据类型
interval day to second 用于按照日期、小时、分钟和秒存储时段。
使用 interval day to second 可以表示两个时间的精确差值。
例如，您可以使用此值设置在 36 小时后进行提示，或记录赛跑的开始和结束的时间。
要以高精度表示包含多年的长跨度的时间，可以对天数部分使用大的数值。
请按照以下的方式指定 interval day to second:
interval day [(day_precision)]
to second [(fractional_seconds_precision)]
在此用法中：
day_precision 是 day 时间日期字段的位数。可以接受的值是 0 到 9。默认值是2；
fractional_seconds_precision 是 second 日期时间字段中的零点几秒部分的位数。
可以接受的 0 到 9 。默认值为 6。
interval day to second 数据类型
(1)interval day to second 用于按照日、小时、分钟、秒和存储时段。
eg1.
interval '4 5:12:10:222' day to second(3)
指明 4 天 5 小时 12 分 10 秒 千分之 222 秒
interval '4 5:12' day to minute
指明 4 天零 5 小时 12 分钟
interval '400 5' day(3) to hour
指定 400 天零 5 小时
interval '11:12:10.2222222' hour to second(7)
指明 11 小时 12 分钟 10.2222222 秒。
eg2.
create table time_example3
(day_duration interval day(3) to second);
insert into time_example3 (day_duration)
values (interval '180' day(3));
commit;
select sysdate+day_duration as "Half Year" from time_example3
使用子查询语法创建表
(1) 通过组合 createtable 语句和 as subquery 选项可以创建表并插入行。
语法为： create table table_name
[(column,column,……)]
as subquery;
(1-1) 此语法中：
(1-1-1) table 是表名称；
(1-1-2) column 是列名、默认值和完整性约束；
(1-1-3) subquery 是一条 select 语句，它定义要插入新标中到表中的一组行。
(1-2) 准则
(1-2-1) 该表在创建时具有指定的列名，并且 select 语句检索到的行将插入到该表中；
(1-2-2) 列定义只能包含列名和默认值；
(1-2-3) 如果已经给出了列的风格，则列数必须等于子查询 select 列表中的列数；
(1-2-4) 如果没有给出列规格，则新创建表的列名和子查询的列名相同；
(1-2-5) 完整性则不会传递到新表中，只有列数据类型定义才会被传递；
eg.
create table dept_80
as
select e.employee_id, e.last_name,
e.salary*12 annsal,
e.hire_date
from employees e
where e.employee_id = 180;
select * from dept_80;
create table test
as
select e.EMPNO,e.ENAME, e.JOB, e.sal*12 annsal, e.DEPTNO
from emp e
where e.JOB = 'SALESMAN';
select *from test;
注意：(1)如果数空表只会复制一个表结构,而不会报错；
(2)当选择表达式时，确保要给出列别名。
表达式 salary*12 被赋予了别名 annsal 如果没有别名，系统将会产生以下错误：
ERROR at line 3:
ORA-00998: must name this expression with a column alias
(2) 使用指定列的数量与子查询列匹配。
(3) 定义具有列名和默认值的列。
alter table 语句
使用 alter table 语句可以执行一下任务：
(1) 添加新列； (2) 修改现有列；
(3) 为新列定义默认值； (4) 删除列；
定义如下：
alter table table_name add(
columndatatype[default expr],)
[,column datatype]……
);
alter table table_name modify (
column datatype [default expr]
[,column datatype]……
)；
alter table table_name drop (column);
在此语法中：
table 是表的名称；
add|modify|drop 是修改的类型；
column 是新列的名称
datatype 是新列的数据类型和长度
eg.
alter table test
add (job_id varchar2(9));
commit;
alter table test
modify (last_name varchar2(30));
commit;
alter table test
drop column job;
commit;
注意：
alter table add
(1) 添加的新列将添加到表中的最后的一列；
alter table modify
(2-1)可以增大数字列的宽度或精度；
(2-2)可以增大字符列的宽度；
(2-3)只有在列仅包括空格或表没有行时，才可以减少列的宽度；
(2-3)只有列包含空格时，才可以更改数据类型；
(2-4)只有列包含空格或没有更改列的大小时，
才可以将 char 类型的列转换为 varchar2 数据类型，
或将 varchar2 类型的列转换为 char 数据类型。
(2-5)对列的默认值的更改只会影响以后对表的插入操作。
alter table drop column
(3-1)在 alter table …… drop column 子句删除一个列，
这是 oracle8i 以后版本中的一项功能。
(3-2)要删除的列可以包含数据，也可以不包含数据。
(3-3)使用 alter table 语句时，每次只能删除一列。
(3-4)表在更改之后，必须至少余下一列。
(3-5)表一旦被删除，就无法恢复。
set unused 选项
(1)可以使用 set unused 选项将一个或多个列标记为“不使用（unused）”。
(2)可以使用 drop unused columns 选项删除标记为“不使用（unused）”的列。
alter table table_name
set unused (column);
或者
alter table table_name
set unused column column_name;
或者
alter table table_name
set unused column;
set unused 选项可以将一个或多个列标记为“unused”,
这样可以在资源需求较低时删除这些列(oracle8i以后版本)。
eg.
-- 设置表 test中的 job_id 不可用；
alter table test
set unused column job_id;
-- 删除表 test 中不可用的列
alter table test
drop unused columns;
select tc.column_name, tc.data_type
from user_tab_cols tc
where tc.table_name = 'TEST'
删除表
(1)该表的所有数据和表中的结构都会被删除；
(2)所有特定事物处理都会被提交。
(3)所有索引都会被删除；
(4)无法回退 drop table 语句；
语法：
drop table table_name;
eg.
drop table test;
准则：
(1)所有数据都会从该表中删除
(2)所有视图和同义词都会保留，但却不再有效；
舍去表的结构 truncate table 语句
(1)删除表的所有行；
(2)释放该表使用的存储空间；
eg.
truncate table test;
(1)如果使用 truncate ,将无法回退行删除操作。
(2)您也可以使用 delete 语句删除行。
注：要舍去表的内容，必须有该表的所有者或 delete table 系统权限。
与 delete 语句的区别: delete 语句也可以删除表的所有的行，但它不可以释放空间。
truncate 命令删除行比用 delete 语句删除行速度更快一些。其原因是：
(1)truncate 语法是数据定义语言(DDL)语句，它不生成回退信息；
(2)舍去表的内容并不会激活表的删除触发器；
(3)如果表是引用完整性约束的父表，则您无法舍去该表的内容。您可以在发出 truncate
语句之前禁用该约束；
向表中添加注释
(1)可以使用 comment 语句向表或列添加备注；
语法为：
comment on table table_name|comment on table.column
is 'test';
comment on table test
is 'Employee Information 123';
comment on column TEST.EMPNO
is 'this is test.empno';
修改注释，同样是这样的命令。
(2)可以通过数据字典视图查看备注：
(2-1)all_col_comments;
(2-2)user_col_comments;
(2-3)all_tab_comments;
(2-4)user_tab_comments;
-----------------------------------------------------------
约束
(1)约束会在表级上强制执行的规则；
在对表执行插入、更新或删除行操作时，对表中的数据强制执行规则。必须满足约束，
操作才能成功。
(2)约束可以防止存在相关性时删除表；
(3)以下约束是有效的：
(3-1)not null 指定该列不能为空
(3-2)unique 指定一个列或列组合中的值对于该表中的所有行来说必须是唯一的。
(3-3)primary key 唯一表示表中的每一行
(3-4)foreign key 在列和被引用表的列之间创建创建并实施一个外键关系。
(3-5)check 指定条件必须为真。
约束准则
(1)可以给约束命名，也可以由 oracle 服务器使用 SYS_Cn 格式产生一个名称。
(2)在创建表的同时创建约束，或在表创建之后创建约束；
(3)可以在列或表级别上定义约束。
(4)可以在数据字典视图中查看约束。
可以通过查看 user_constraints 数据字典表来了解为特定的表定义的约束。
数据字典 user_constraints 定义如下：
SQL> desc user_constraints
Name Type Nullable Default Comments
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER VARCHAR2(30) Owner of the table
CONSTRAINT_NAME VARCHAR2(30) Name associated with constraint definition
CONSTRAINT_TYPE VARCHAR2(1) Y Type of constraint definition
TABLE_NAME VARCHAR2(30) Name associated with table with constraint definition
SEARCH_CONDITION LONG Y Text of search condition for table check
R_OWNER VARCHAR2(30) Y Owner of table used in referential constraint
R_CONSTRAINT_NAME VARCHAR2(30) Y Name of unique constraint definition for referenced table
DELETE_RULE VARCHAR2(9) Y The delete rule for a referential constraint
STATUS VARCHAR2(8) Y Enforcement status of constraint - ENABLED or DISABLED
DEFERRABLE VARCHAR2(14) Y Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE
DEFERRED VARCHAR2(9) Y Is the constraint deferred by default - DEFERRED or IMMEDIATE
VALIDATED VARCHAR2(13) Y Was this constraint system validated? - VALIDATED or NOT VALIDATED
GENERATED VARCHAR2(14) Y Was the constraint name system generated? - GENERATED NAME or USER NAME
BAD VARCHAR2(3) Y Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.
RELY VARCHAR2(4) Y If set, this flag will be used in optimizer
LAST_CHANGE DATE Y The date when this column was last enabled or disabled
INDEX_OWNER VARCHAR2(30) Y The owner of the index used by the constraint
INDEX_NAME VARCHAR2(30) Y The index used by the constraint
INVALID VARCHAR2(7) Y
VIEW_RELATED VARCHAR2(14) Y
约束
约束准则
(1)可以给约束命名，也可以由 oracle 服务器使用 SYS_Cn 格式产生一个名称。
其中 n 是一个整数，这样的约束就是唯一的。
(2)可以在以下时刻创建约束；
(2-1)在创建表的同时创建约束；
(2-2)在创建表之后创建约束；
(3)可以在列或表级别上定义约束。
(4)可以在数据字典中查看约束。
注：如果为约束给出有意义的名称，在引用他们是会较为容易。
约束名必须遵循标准的对象命名规则。
可以通过 user_constraints 数据字典表来了解表定义的约束。
eg.
SQL> set linesize 500;
SQL> select * from user_constraints;
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --------- -------- -------------- --------- ------------- -------------- --- ---- --------- ------------------------------ ------------------------------ ------- --------------
SCOTT PK_DEPT P DEPT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 12-MAY-02 SCOTT PK_DEPT
SCOTT PK_EMP P EMP ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 12-MAY-02 SCOTT PK_EMP
SCOTT FK_DEPTNO R EMP SCOTT PK_DEPT NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 12-MAY-02
SQL> desc user_constraints
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
--------------------------------------------------------------------------
--------------------------------------------------------------------------
约束的定义
create table [schema.] table
( column datatype [default expr]
[column_constraint],
……
[table_constraint]
[, ……]
)；
在此语法中：
schema 是所有者的姓名
table 是表的名称
default expr 指定一个默认值，当 insert 语句中没有指定只时，则使用该默认值。
column 列的名字
datatype 列的数据类型和长度
column_constraint 是作为列定义的一部分的完整性约束。
table_constraint 是作为表一部分完整性约束。
eg.
create table employees(
employee_id number(6),
frist_name varchar2(20),
……
job_id varchar2(10) not null,
constraint emp_emp_id_pk primary key (employee_id)
);
(1)列约束级别
column [constraint constraint_name] constraint_type,
(2)表约束级别
column, ……
[constraint constraint_name] constraint_type
(column, ……)，
在此用法中
constraint_name 是约束的名称
constraint_type 是约束的类型
约束通常是和表同时创建的。约束可以在创建表之后，在创建。也可以临时禁用。
约束级别 说明
-----------------------------------------------------------
column 引用一个列，并拥有的列的范围内定义；
可以定义任何类型的完整性约束；
table 引用一个或多个列，并在该表定义中分别进行定义；
可以定义任何约束，但 not null 除外
-----------------------------------------------------------
not null 约束：确保该列不允许有空值；
没有 not null 约束的列在默认情况下可以包含空值；
not null 约束只能在列级别指定， 不能在表级别指定；
unique 约束
unique 关键字完整性约束要求列或列集合中的每个值都是唯一的，
既，表的任意两行在指定列或列集合中都没有重复的值。
注：由于多列上的 unique 约束的搜索机制，在部分空值组合 unique 关键字约束
的非空列中不能有完全相同的控制。
eg。
create table TEST_2
(
ID VARCHAR2(5),
NAME VARCHAR2(10),
constraint TEST_UNIQUE unique (ID,name)
);
insert into TEST_2 values('22',null);
insert into TEST_2 values('22',null);--ERROR: unique constraint (SCOTT.TEST_UNIQUE) violated
/*
SQL> insert into test_2 values('22',null);
insert into test_2 values('22',null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST_UNIQUE) violated
*/
注:虽然 null != null ,但是插入第二条记录依然违反了 unique 约束。
create table TEST_3
(
ID VARCHAR2(5),
NAME VARCHAR2(10),
constraint TEST_3_UNIQUE unique (name)
);
insert into TEST_3 values('22',null);
insert into TEST_3 values('23',null);
-- 因为 null != null ，所以不违反 unique 约束 。
oracle 服务器可以通过在一个或多个关键字列上隐式创建唯一索引，
从而强制实行 unique 约束。
primary key 约束
primary key 约束将为表创建一个主键。每个表只能创建一个主键。
primary key 表示表中每行的一个列或列集合，此约束将强制列或列组合的唯一性，
并且确保作为主键的一部分的列不包含空值。
primary key 在表级别或列级别上定义：
eg.
create table test_departments(
department_id number(4),
department_name varchar2(30) constraint dept_name_nn not null,
manager_id number(6),
location_id number(4),
constraint dept_id_pk primary key (department_id)
);
注：
(1) 一个表只能有一个 primary key 约束，但可以有多个 unique 约束。
(2) 在创建 primary key 列自动创建 unique 索引。
foreign key 约束
(1)foreign key 将指定一个列或列组合作为外键，并建立与统一表或不同表中
主键或唯一关键字之间的关系。外键值必须与父表中的现有值匹配或为 null。
(2)外键基于数据值,是纯逻辑指针，而不是实际指针；
create table test_employees(
employee_id number(6),
last_name varchar2(25) not null, -- 非空约束
email varchar2(25),
SALARY NUMBER(8,2),
commision_pct number(2,2),
hire_date date not null,
department_id number(4),
constraint emp_dept_fk foreign key (department_id)
references departments(department_id), -- 外键约束
constraint emp_email_uk unique (email) -- 唯一约束
);
注：
(1)foreign key 约束可以在列或表约束级别上定义,
而自合外键必须通过使用表级别定义来创建。
(2)外键也可以在类级别定义，只要该约束是基于单列即可。
语法区别在于没有关键字 foreign key 。
eg.
create table employees (
department_id number(4) constraint emp_deptId_fk
references department(department_id),
);
foreign key ：在表约束级别上定义子表中的列；
references ：表示父表中的表和列；
on delete cascade : 当删除父表中的行时，也删除子表中的相关的相关行；
on delete set null : 将相关外键值转换为空值；
外键在子表中定义的，而包含被引用列的表是父表。
默认的行为别称为限制规则，它禁止更新或删除被引用的数据。
在没有 on delete cascade 或 no delete set null 选项的情况下，如果
在子表中引用了父表的行，则无法删除该行。
check 约束
(1)用于定义每行都必须满足的一个条件
(2)以下表达式是不允许的；
(2-1)对于 currval,nextval,level,rownum 位列的引用；
(2-2)对 sysdate,uid,user,userenv 函数的调用；
(2-3)涉及到其他行中的其他的查询；
单独一列可以有多个 check 约束，这些约束在他们的定义中涉及到该列。
对于在一列上可以定义的 check 约束的数量没有限制。
check 约束可以在列级别或表级别上定义。
create table emplees(
……
selery number(8,2) constraint emp_salary_min check (salary > 0),
……
)；
eg.
create table test_4(
salary number(8,2) constraint emp_salary_min
check (salary > 0),check (salary < 1000)
);
添加约束语法
使用 alter table 语句可以执行以下任务；
(1)添加或删除约束，但不能修改它的结构；
(2)启动或禁用约束；
(3)使用 modify 子句添加 not null 约束；
语法为：
alter table table_name
add [constraint constraint_name] type(column);
在此语法中:
table 是表的名称；
constraint 是约束的名称；
type 是约束的名称；
column 是受约束影响的列的名称；
约束名 是可选的。但建议使用它。
如果您没有为约束命名，系统会自动生成约束名。
准则：
(1)可以添加、删除、启动或禁用约束，但不能修改它的结构。
(2)通过使用 alter table 语句的 modify 子句
可以向现有列添加一个 not null 约束。
注：只有在表为空或该列对于每行都有值时，才可以定义 not null 列。
添加约束
向 employees 添加一个 foreign key 约束，指明经理必须已经作为一个
有效的员工存在于 employees 表中。
alter table employees
add constraint emp_manager_fk foreign key (manager_id)
references employees (employee_id);
删除约束
(1)从 employees 表中删除经理约束
alter table employees
drop constraint emp_manager_fk;
(2)删除 departments 表上的 primary key 约束，并删除
employees.department_id 列上关联的 foreign key 约束。
alter table departments
drop primary key cascade;
在删除约束时
(1)要删除约束时可以从 user_constraints 和 user_cons_columns
eg.
select * from user_constraints;
select * from user_cons_columns;
数据字典视图中确定约束名。
(2)然后在 alter table 语句中使用 drop 子句。
(3)如果在 drop 子句中使用 cascade 选项，将同时删除所有相关的约束。
语法
alter table table_name
drop primary key | unique (column)|
constraint constraint [cascade];
在此语法中；
table 是表的名称；
column 是受约束影响的列的名称；
constraint 是约束的名称；
如果删除了完整性约束，oracle 服务器将不再强制实行该约束，
数据字典中也不会有该约束。
禁用约束
(1)执行 alter table 语句的 disable 子句可以停用完整性约束。
(2)应用 cascade 选项可以禁用相关的完整性约束。
eg.
alter table emplees
disable constraint emp_emp_id_pk cascade;
注:
(1)通过在 alter table 语句中使用 disable 子句
可以禁用约束，而无需删除它或重新创建它。
语法为：
alter table table_name
disable constraint constraint[cascade];
此语法中;
table 是表名称；
constraint 是约束的名称；
准则:
(1)在 create table 语句和 alter table 语句中都可以使用 disable 子句。
(2)cascade 子句将禁用相关的完整性约束。
(3)禁用唯一关键字或主键约束将会删除唯一索引。
启动约束
(1)使用 enable 子句可以激活表定义中当前禁用的索引的完整性索引约束。
eg.
alter table employees
enable constraint emp_emp_id_pk;
(2)如果启用了 unique 关键字或 primary key 约束，系统会自动创建一个 unique
或 primary key 索引。
启用约束
通过在 alter table 语句使用 enable 子句可以启用约束，而无需删除或重建。
语法为：
alter table table_name
enable constraint constraint;
准则：
(1)如果启用了一个约束，则该约束将应用于表中的所有数据。表中的所有数据必须都满足该约束。
(2)如果启用了 unique 关键字或 primary key 约束，系统会自动创建一个 unique
或 primary key 索引。
(3)在 create table 语句和 alter table 语句中都可以使用 enable 子句。
(4)启用 cascade 选项禁用德尔主键约束并不会启用依赖于该主键的任何外键。
级联约束
(1)cascade constraints 子句是和 drop column 子句一起使用的。
(2)cascade constraints 子句会删除涉及到在已删除列上定义的主键或唯一
关键字所有引用完整性约束。
(3)cacade constraints 子句还会将删除已删除列上定义的所有多列约束。
级联约束
下面语句说明了 cascade constraints 子句的用法，假设用下面的语句创建了表 test1:
create table test1 (
pk number primary key,
fk number,
col1 number,
col2 number,
constraint fk_constrait foreign key (fk) references test1,
constraint ck1 check (pk > 0 and col1 > 0),
constraint ck2 check (col2 > 0)
);
以下语句会返回错误:
alter table test1 drop (pk); -- pk 是一个父键
alter table test1 drop (col1); -- col1 被多列约束 ck1 所引用
eg.
-- table altered
alter table test1
drop (pk) cascade constraint;
-- alter table test1
alter table test1
drop (pk, fk, col1) cascade constraints
提交下面的语句将删除列 PK , 主键约束, fk_constrait 外键约束和检查约束 CK1;
alter table test1 drop (pk) cascade constraints;
如果已删除列表定义的约束，所引用的列也已经被删除，则不需要使用 cascade constraints.
例如，假设其它表的其他引用约束都不涉及 PK 列，则提交以下没有 cascade constraints 子句的
语句是有效的：
alter table tast1 drop (pk, fk, col1);
查看约束
查询 user_constraints 数据字典视图可以查看所有约束定义和名称。
eg.
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'EMPLOYEES';
在创建表之后，可以发出 describe 命令来确认它是否存在。您唯一可以验证的约束是 not null 约束。
要查看表上的所有约束，请查看 user_constraints 表。
注：如果表的所有者没有命名约束，则约束将接受系统分配的约束名。在约束类型中，
C 代表 check;
P 代表 primary key;
R 代表完整性；
U 代表 unipue 关键字。
这里，not null 约束实际上是一个 check 约束。
在 user_cons_columns 字典视图中查看与约束名关联的列。
select constraint_name, column_name
from user_cons_columns
where table_name = 'EMPLOYEES';
-----------------------------------------------------
创建视图
-----------------------------------------------------
数据库对象
对象 | 说明
————————————————————————————————————————————————————————
表 | 基本的存储单元；由行和列组成；
视图 | 在逻辑上代表来自一个或多个表的数据的子集
序列 | 生成主键值
索引 | 提高某些查询的性能
同义词 | 对象的替代名称
——————————————————————————————————————————————————————————
视图：通过创建表的视图可以显示数据的逻辑子集或集合。视图是基于表或另一个视图的逻辑表。
视图没有自己的数据，但他有如同一个窗口，通过它可以查看或更改的数据。视图所基于的表成为基本表。
视图以 select 语句的形式存在数据字典中。
数据字典的作用
(1) 限制数据访问；
(2) 使复杂的查询变得容易；
(3) 提供数据的独立性；
(4) 提供相同数据的不同视图；
视图的有点；
(1) 由于视图能够选择性地显示表中的列，因而可以限制对数据的访问；
(2) 视图可以用于进行简单的查询，从而检索复杂查询的结果。例如，用户可以通过视图查询多个
表中的信息，而无需了解如何编写语句。
(3) 视图可以支持用户根据其特定标准来访问数据。
简单视图和复杂视图
特性 简单视图 复杂视图
------------------------------------------------------
表的数量 一个 一个或多个
是否包含函数 否 是
是否包含数据组 否 是
能否通过视图 能 不一定
执行 DML 操作
------------------------------------------------------
视图有两类:简单视图 和 复杂视图。
他们基本差别与 DML( insert, update 和 delete ) 操作有关。
(1)简单视图有如下特点：
(1-1)只从一个表导出数据；
(1-2)不包括函数或数组；
(1-3)可以通过该视图执行 DML 操作；
(2)复杂视图有如下特点：
(2-1)从多个表中导出数据；
(2-2)包括函数或数组；
(2-3)不一定能够通过该视图执行 DML 操作；
在 create view 语句中嵌入了一个子查询。
create [or replace] [force|noforce] view view_name
[(alias [, alias]……)]
as subquery
[with check option [constraint constraint_name]]
[with read only [constraint constraint_name]]
注：该子查询可以包括复杂的 select 语句。
在此语法中：
or replace 如果该视图已经存在则重新创建它；
force 不管基表是否存在都创建视图；
noforce 仅当基表存在时才创建视图( 这是默认设定)
view 是视图的名称
alias 指定由视图的查询所选择的表达式的名称
(别名的数量必须与视图选择的表达式的数量匹配)
subquery 一个完整的 select 语句 (可以在 select 列表中使用列的别名)。
with check option 指定只能插入或更新那些视图可以访问的名称；
constraint 是为 check option 约束指定的别名；
with read only 确保不能对此视图执行 DML 操作。
---------------------------------------------------------
-- 为了保证能够正确在 view 视图中正确插入数据，必须将主键添加到子查询中
create view test_emp
as select e.empno, e.ename, e.job,
e.mgr, e.sal
from emp e
where e.job = 'SALESMAN'
with check option;
-- 下面插入数据语句报错；
-- ora-01402: 视图 with check option 违反 where 子句；
insert into test_emp
values (5050,'1','2',7698,1600.00);
-- 修改
insert into test_emp
values (5050,'1','SALESMAN',7698,1600.00);
创建视图
创建视图 empvu80, 该视图要包括部门 80 中员工的详细信息。
eg.
create view empvu80
as select employee_ID, Last_Name, Salary
from employees
where deparment_ID = 80;
(2) 使用iSQL * Plus 中的 describe 命令描述视图的结构；
eg.
describe empvu80;
创建视图的准则：
(1) 定义视图的子查询可以包含复杂的 select 语法，包括连接，分组和子查询。
(2) 定义视图的子查询不能包含 order by 子句。 order by 子句是在从视图中
检索数据时指定的。
(3) 如果不为用 with check option 创建的视图指定约束名。系统会以 sys_Cn
的格式指定一个默认的名称。
(4) 可以使用 or replace 选项更改视图定义，而不必先删除在创建视图，也不必
重新授权以前授予它的对象权限。
在子查询中使用列别名来创建视图。
create table salvu50
as select employee_id, id_number, last_name name, salary*12 ann_salary
from employees
where department_id = 50;
通过在子查询中包含列别名可以控制列名。
除了以上的方法外，还可以在 create 语句之后，select 子查询之前使用别名。
列出的别名数量与在子查询中选择的表达式数量匹配。
-- 前一种方法等价位：
create view salvu50 ( ID_number, name, ann_salary )
as select employee_id, last_name, salary*12
from employees
where department_id = 50;
从视图检索方法为：
select * from salvu50;
视图查询的原理
select * __________/ (USER_VIEWS rmpvu80)
from empvu80; / select employee_id, last_name, salary
/ / from employees
| where department_id = 80;
| |
| |
| / /
|___________________ employees
数据字典中的视图
一旦创建了视图，您就可以查询 user_views 的数据字典视图来查看视图名称和视图定义。
可以在视图中看到 select 语句文本存储在列名为 text 类型为 long 的列中。
当使用视图访问数据时,oracle 服务器将执行下列操作；
eg.
select * from user_views;
使用视图访问数据
(1) 它从 user_views 数据字典表中检索视图定义。
(2) 它检查视图基表的访问权限。
(3) 它将视图查询转换成为对基表的等同操作。
也就是说，数据从基表中检索，或对基表进行更新。
修改视图
(1) 使用 create or replace view 子句修改 empvu80 视图。为每个列添加一个别名。
create or replace view empvu80
(id_number, name, sal, department_id)
as select employee_id, first_name||' '||last_name,
salary, department_id
from employees
where department_id = 80;
(2) create view 子句中的列别名与子查询中的排列顺序相同。
注：当 create view 子句中指定列别名时，要记住列别名应与子查询中的
列排列顺序相同。
创建复杂视图
创建包含分组函数的复杂视图以显示两个表中的值。
create view dept_sum_vn
(name, minsal, maxsal, avgsal)
as select d.partment_name, min(e.salary), max(e.salary), AVG(e.salary)
from employees, departments d
where e.department_id = d.department_id
group by d.department_name;
注意：如果视图中有从函数和表达式中导出的列，则为列其别名则是必须的。
在视图中执行 DML 操作的规则
(1) 可以在简单视图上执行 DML 操作。
(2) 如果视图包含以下内容，则不能删除行；
(2-1) 分组函数；
(2-2) group by 子句；
(2-3) distinct 关键字；
(2-4) 伪列 ROWNUM 关键字；
(2-5) 由表达式定义的列/
使用 with check option 子句
(1) 使用 with check option 子句可以确保在视图上执行的 DML 操作发生在视图的范围。
create or replace view empvu20
as select *
from employees
where department _id = 20
with check option constraint empvu20_ck;
如果，尝试在视图中更改除了部门 ID 为 20 的任一行的部门编号都不会成功，因为它
违反了 with check option 约束。
注：可以通过视图进行引用完整性检查。还可以在数据库级别上强制执行约束。视图可以
用来保护数据德尔完整性，但使用是有限制的。
with check option 子句指定通过视图的 insert 和 update 命令不能创建该视图
无法选择的行。从而允许对插入或更新的数据强制执行的完整性约束和数据验证检查。
如果视图对视图为选中的执行 DML 操作，则会显示错误消息，并且如果指定了约束名，
也会显示出来。
uodate empvu20
set department_id = 10
where employee_id = 201;
-- 错误信息为：
update empvu20
*
ERROR at line 1:
ORA-01402: view with check option where-clause violation
拒绝 DML 操作
(1) 通过在视图定义中添加 with read only 选项可以确保不能执行 DML 操作。
(2) 任何对视图中的行执行 DML 的尝试都会导致 oracle 服务器错误。
通过在创建视图时使用 with read only 选项，从而防止对该视图执行任何 DML 操作。
eg.
create or replace view empvu10
(emploee_number, employee_name, job_title)
as select employee_id, last_name, job_id
from employees
where department_id = 10
with read only;
从带有只读约束的视图中删除行的任何操作都会导致错误。
eg.
delete from empvu10
where employee_number = 200;
-- 错误信息为：
Delete from empvu10
*
ERROR at line:1
ORA-01752: cannot delete from vew without exactly one key- preserved table
使用带有只读约束的视图插入行或修改的任何尝试都将导致 oracle 服务器错误；
01733: virtual column not allowed here .
删除视图
因为视图是基于数据库中的基表，所以删除视图不会导致对视数据。
语法为：
drop view view_name;
在此语法中：
view 是视图名称；
eg.
drop view empvu80
view dropped;
可以使用 drop view 语句删除视图。该语句从数据库中删除视图定义。删除视图不会影响该视图
所基于德尔表。基于被删除视图的视图或其他程序则变得无效。另外，只有创建者或具有 drop any view
权限的用户才能删除视图。
内联视图
(1) 内联视图是带有可以在 SQL 语句中使用的别名（或者相关名称）的子查询。
(2) 主查询 from 子句中的命名子查询就是一个内联视图实例。
(3) 内联视图不是方案对象(schema object).
内联视图是通过在 from 子句中放置一个子查询并给该子查询指定一个别名创建的。子查询定义一个
可以在主查询中引用的数据源。
在下面示例中，内联视图 b从 employees 表中返回了所有的部门编号的详细信息以及每个部门的最高
工资。 主要查询中的 where a.department_id = d.department_id and a.salary < b.maxsal 子句，
显示工资低于本部门最高的所有员工的姓名，薪金，部门编号和最高薪金。
eg.
select a.last_name, a.salary, a.department_id, b.maxsal
from employees a,
( select ee.department_id, max(ee.salary) maxsal
from employees ee
group by ee.department_id
) b
where a.employee_id = b.department_id
and a.salary < b.maxsal;
排序 top-N 分析
(1) 排序 top-N 查询可以获得某个列的 n 的最大或 n 个最小值。
(2) 最大值和最小值的集合被成为排序 top-N 查询。
在需要基于某个条件仅显示表中 n 个最高纪录或最低纪录的情况下，
排序 top-N 查询很有用。此结果集可进一步分析。
select [column_list], ROWNUM
from ( select [column_list]
from table_name
order by top-N_column
)
where rownum <= N;
执行排序 top-N 分析
执行 top-N 查询使用一致的具有以下元素的嵌套查询结果；
(1) 子查询或内联视图，用于生成排序的列表。子查询或内联视图包含
order by 子句。用以确保按照所需顺序排列。对于检索最大值的结果，
需要使用 desc 参数。
(2) 外部查询，用于限制最终结果集的行数。外部查询包括以下组成部分：
(2-1) ROWNUM 位列，为子查询返回的每一行指定一个顺序值，值从 1 开始；
(2-2) where 子句，指定返回 n 行。外部 where 子句必须使用 <= 或 < 运算符
eg.
显示 employees 表中收入最高的三个人的姓名和薪金。
select ROWnum as RANK, last_name, salary
from (
select last_name, salary
from employees
order by salary desc
)
where ROWNUM <= 3;
显示公司内四个资历最深的员工。
select rownum as senior, e.last_name, e.hire_date
from ( select last_name, hire_date
from employees
order by hire_date
) e
where rownum <= 4
数据库对象
------------------------------------------------------
对象 说明
------------------------------------------------------
表 基本的存储单元，由行和列组成
视图 在逻辑上代表一个或多个表的数据子集
序列 生成主键值
索引 提高某些查询的性能
同义词 对象的代替名称
------------------------------------------------------
数据库对象
许多应用程序要求使用唯一编号作为主键值。您可以将代码编入到应用程序中来
处理这种要求，也可以使用序列生成唯一编号。
如果提高某些查询的性能，则应该考虑创建一个索引。您也可以使用索引对一个
列或列的集合强制实行唯一性。
可以通过使用同义词为对象提供代替名称。
什么是序列
序列具有以下特性：
(1) 自动生成唯一编号；
(2) 是一个可共享的对象；
(3) 通常用于创建主键值；
(4) 替换应用程序代码；
(5) 如果将序列高速缓冲到内存中，则可以提高访问序列值的效率；
序列是用户创建的数据库对象，它可以有多个用户共享，用来生成唯一的整数。
序列的通常用途是创建主键值，主键值对于每行必须是唯一的。序列由内部的oracle
例程程序生成和递增（或递减）。使用该对象可以节省时间，这是因为它可以减少
编写生成例程所需的应用程序代码量。
另外，序列号的存储和表无关。因此，同一序列可以用于多个表。
create sequence 语句语法
定义一个可以自动生成序号的序列：
create sequence squence_name
[increment by n]
[start with n]
[{maxvalue n| nomaxvalue}]
[{minvalue n| nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}];
此语法为：
sequence_name 是序列生成器的名称；
increment by n 指定序列号之间的间隔，其中 n 是一个整数
（如果省略此子句，则序列每次递增 1 ）
start with n 指定生成的第一个序列号（如果省略此语句，则序列从 1 开始）
maxvalue n 指定序列可以生成的最大值
nomaxvalue 指定 10^27 作为递增的最大值，-1 作为递减序列的最大值
（这是默认选项）
clcle | nocycle 指定在达到最大值或最小值之后，序列是否继续生成值
cache n | nocache 指定 oracle 服务器预先分配并保留在内存中的值的数量
（在默认情况下，oracle 服务器高速缓存20 个值）
eg.
(1) 创建一个名为 dept_deptID_seq 的序列，将它作用表 departments 的主键。
(2) 不使用 cycle 选项。
create sequence dept_deptID_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle;
如果该序列用于生成主键值，则不要使用 cycle 选项，除非有一个可靠的机制，
能够比序列循环更快地清楚旧行。
注：序列并不与表关联。通常，您应该按序列的用途来命名它；
但是，无论序列的名称是什么，它都可以在任何地方使用。
确认序列
(1) 在 user_sequences 数据字典表中验证序列值。
select us.sequence_name, us.min_value, us.max_value,
us.increment_by, us.last_number
from user_sequences us;
(2) last_number 列将显示下一个可用的序列号（如果指定了 nocache ）。
一旦创建了序列，系统就会在数据字典中对它进行纪录。因为序列是一个数据对象，所以您可以
在 user_objects 数据字典中表示它。
也可以从 user_sequences 数据字典视图中进行选择，从而确定序列的设置。
nextval 和 currval 伪列
(1) nextval 会返回下一个可用的序列值。每次被引用时它都会返回一个唯一的值，
即使对于不同的用户也是这样。
(2) currval 会获得当前序列值。
(3) 必须对该序列发出 nextval 后, 然后 currval 才能包含值。
注：在创建序列之后，它会生成可以在表中使用的序列号。可以通过 nextval 和 currval
伪列引用序列值。
nextval 和 currval 伪列
nextval 伪列用于从指定序列中抽取连续的序列号。在使用时，必须用序列名来限定 nextval .
当您引用 sequence.nextval 时，系统会生成新的序列号，并且将当前序列号放置在 currval 中。
currval 伪列用于引用当前用户刚刚生成的序列号。但，必须之前必须使用 nextval ，在当前用户
会话中生成一个序列号，然后才能过引用 currval。另外，必须用徐列名来限定 currval ,
在 sequence.currval 时，会显示返回给指定用户进程的最后一个值。
使用 nextval 和 currval 的规则
可以在以下上下文中使用 nextval 和 currval :
(1) 不属于子查询中的 select 语句中的 select 列表；
(2) insert 语句中的子查询的 select 列表；
(3) insert 语句的 value 子句；
(4) update 语句的 set 子句；
在下列情况中不能使用 nextval 和 currval :
(1) 视图中 select 列表；
(2) 带有 distinct 关键字的 select 语句；
(3) 带有 group by, having 或 order by 子句的 select 语句
(4) select, delete 或 update 语句中的子查询；
(5) create table 或 alter table 语句中的 default 表达式；
eg.
在地点表示 2500 中插入一个名为 “Support”的新部门。
insert into departments
(fdepartment_id, department_name, location_id)
values
(dept_deptid_seq.nextval, 'Support', 2500);
查看 dept_deptID_seq 序列的当前值 (但执行 currval 前，必须先执行一个 nextval )
select dept_deptID_seq.currval
from dual;
eg.
select DEPARTMENTS_SEQ.CURRVAL
from dual;
假设现在您要聘用员工来作为新部门的工作人员。在要对所有的新员工执行的 insert 语句中
包含以下代码
insert into employees
(employees_id, department_id, ……)
values
(employees_seq.nextval, dept_deptid_seq.currval, ……)；
注：上面实例假设已经创建了employees_seq 的序列，用于生成新员工编号。
使用序列
在内存中缓存序列可以更快地访问那些值；
但是，当发生下列情况时，序列值会出现简短：
(1) 发生回退；
(2) 系统崩溃；
(3) 在其他表中使用了序列；
如果创建序列时使用了 nocache 选项，可以通过查询 user_sequences 表来查看
下一个可用的值。
在内存中缓存序列可以更快地访问那些序列值。当首次引用序列时，系统会将序列值
填充到缓冲中，要求查看下一个序列值的每个请求都会从缓存中检索到所需的值。在
使用了最后一个序列之后，对序列的下一个请求会将序列的另一组值放入内存中的缓存中。
序列中断
虽然序列生成器是发生没有间断的连续序号，但是此操作受到提交或回退操作的影响。
因此，如果回退一个包含序列的语句，则该序号将会丢失。
另一个可能导致序列中出现间断的事件是系统崩溃。如果序列将值缓存到内存中，在系统崩溃时，
它们将丢失。
最后一种可能，因为序列不是与表直接关联的，所以同一个序列可以用于多个表中，如果将同一个
序列用于多个表中，则每个表都会包含序列的间断。
如果创建序列时使用了 nocache 选项，则通过查询 user_sequences 表就可以查看下一个可用的序列值，
而不使它递增。
eg.
select * from user_sequences;
select departments_seq.currval from dual;
修改序列
更改增量值，最大值， 最小值，循环选项或缓存值选项。
eg.
alter sequence dept_deptId_seq
increment by 20
maxvalue 999999
nocache
nocycle;
语法
alter sequence sequence_name
[increment by n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}];
修改序列准则
(1) 必须是序列的所有者或对该序列具有 alter 权限。
(2) 修改只会影响以后生成的序列号；
(3) 如果要从不同的序号处重新开始，则必须删除原有的序列，然后新建。
使用 alter sequence 语句不能更改 start with 选项。如果需要
从不同的序号处开始，则必须删除原有的序列然后重新创建。
(4) 系统会执行一些验证操作。
例如，系统无法强加一个小于当前序列号的新 maxvalue 。
eg.
alter sequence dept_deptID_seq
increment by 20
maxvalue 90
nocache
nocycle;
alter sequence dept_deptID_seq
*
ORA-04009: MAXALUE cannot be made to be less then the current value.
删除序列
(1) 通过使用 drop sequence 语句可以从数据字典中删除序列。
(2) 序列一旦删除，就不能再引用它。
eg.
dorp sequence dept_deptID_seq ;
注：要从数据再点中删除序列，可以使用 drop sequence 语句。要删除序列，您必须
是 序列的所有者或者 具有 drop any sequence 权限。
索引具有的以下特性；
(1) 它是一个方案对象；
(2) oracle 服务器使用它来快速路径访问方法来快速查询数据，从而减少
磁盘 I/order by 操作；
(3) 与其它索引的表无关；
(4) 由 oracle 服务器自动使用和维护；
注：在 oracle 服务器上， 索引是一个方案对象， 它通过使用指针加速对行的检索。索引即可以
显示创建，也可以自动创建。如果在列上没有索引，则系统会对整个表进行扫描。
使用索引可以直接和快速地访问表中的行。
索引的目的是使用一个索引的路径来快速查找到数据，而减少必需的磁盘 I/O 操作。
索引是由 oracle 服务器自动使用和维护的。索引一旦创建，用户就不需要执行直接的
操作了。
索引在逻辑上和物理上都独立于他们的表。这意味着可以在任意时刻创建和删除索引，而
不会对基表或其他索引产生影响。
另外，当删除表时，相应的索引也会被删除。
如何创建索引
(1) 自动创建：如果表中定义了 primary key 或 unique 约束， 则系统会自动创建唯一的索引。
(2) 手工创建：用户可以在列上创建非唯一的索引，以加速对行的访问。
索引类型
可以创建两种类型的索引:
(1) 唯一索引：如果表中定义的列具有 primary key 或 unique 关键字约束，则 oracle 服务器会
自动创建这种索引。索引的名称和该约束的名称一样。
(2) 用户可以创建的非唯一索引。eg. 您可以在查询中为连接创建一个 foreign key 列索引。以提高
检索速度。
注：您可以手工创建唯一索引，但是建议创建一个唯一约束，而该约束会隐式地创建一个唯一索引。
创建索引
(1) 在一个或多个列上创建索引。
create index index_name
on table (column[, column]……)；
eg. 提高对表 employees 的 last_name 列的查询访问速度。
create index emp_last_name_idx
on employees(last_name);
在以下情况下，应该创建索引：
(1) 列包含较大范围的值；
(2) 列包含大量的空值；
(3) 在 where 子句或联结条件或联结条件中频繁使用一个或多个列；
(4) 表相当大，但是预计多数的查询检索的行不到总行数的百分之二至百分之四；
索引多未必好：
并不是表的索引越多越好，查询就会越快。对具有索引的表提交的每次 DML 操作，
都意味着必须更新索引。与表关联的索引越多，在 DML 操作之后， oracle 服务器
为更新全部索引所作的工作就越多。
注意：如果您要强制实行唯一性，则应在表定义中定义唯一约束。然后，系统会自动创建
一个索引。
在下列情况下，不创建索引：
(1) 表比较小；
(2) 在查询中不经常使用列作为条件；
(3) 预计多数查询检索的行要超过表中总是的百分之二至百分之四；
(4) 表更新比较频繁；
(5) 被索引的列将作为表达式的一部分进行引用；
确认索引
(1) user_indexes 数据字典视图包含索引的名称及其唯一性。
(2) user_ind_columns 视图包含索引名、表名和列名。
select * from user_indexes;
select * from user_indexes;
select * from user_ind_columns
select ic.index_name, ic.column_name,
ic.column_position col_pos,
ix.uniqueness
from user_indexes ix, user_ind_columns ic
where ic.index_name = ix.index_name
and ic.table_name = 'DEPT';-- EXPLOYEES
从 user_index 数据字典视图中可以确认索引是否存在。
也可以通过查询 user_ind_columns 视图检查索引涉及的列。
基于函数的索引
(1) 基于函数的索引就是基于表达式的索引；
(2) 索引表达式是表列、常数、SQL 函数和自定义函数；
create index upper_dept_name_idx
on department (upper(department));
select *
from departments
where upper(department_name)= 'SALES';
基于函数的索引
(1) 基于函数的索引就是基于表达式的索引。
(2) 索引表达式是列表、常数、SQL函数和自定义函数构建的。
eg.
create index upper_dept_name_idx
on deptartment(upper(department_name));
-- index created
select *
from departments
where upper(department_name) = 'sales';
用于 upper(column_name) 或 lower(column_name)
关键字定义的基于函数的索引允许进行不区分大小写的索引。
例如，下面的索引：
create index upper_last_name_idx
on employees(upper(last_name));
可以方便地处理如下查询：
select * from employees where upper(last_name) = 'HING'；
要确保 oracle 服务器使用索引，而不扫描整个表，这就要确保后来的查询中
使用的函数不是空值。
eg. 下面的语句将确保使用索引，但是如果没有 where 子句，oracle 服务器
将会扫描整个表；
select * from employees
where upper(last_name) is not null
order by upper(last_name);
oracle 服务器将带有 DESC 标记的列的索引当作基于函数的索引进行处理。
带有 desc 标记的列会按降序进行排序。
删除索引
(1) 使用 drop index 命令可以从数据字典中删除索引。
drop index index_name;
eg. 从数据字典中删除 upper_last_name_idx 索引。
drop index upper_last_name_idx;
(2) 要删除索引，必须是该索引的所有者或具有 drop any index 权限。
注：不能修改索引。如果要更改索引，只能删除，然后在重新改建。
通过发出 drop index 语句可以从数据字典中删除索引定义。
您必须时该索引的所用者或有 drop any index 权限。
如果删除了一个表，则索引和约束也将自动删除，但是试图和序列仍然保留。
同义词
通过创建同义词（对象的另一个名称）可以简化对对象的访问。使用同义词具有以下优点；
(1) 易于引用其他用户所有的表；
(2) 缩短冗长的对象名；
eg.
create [public] synonym synonym_name
for object;
在次语法中；
public 创建一个所有用户可以访问的同义词；
synonym_name 是要创建的同义词的名称；
object 表示要为其创建同义词的对象；
在引用其他用户拥有的表，需要在表名前缀上加一个前缀，即该表创建者的姓名，再加上一个句号。
如果创建了同义词，就可以不需要利用方案来限定对象名了，它可以提供表、试图、序列、过程或
其他对象的替代名称。此方法对于冗长的对象名（例如试图）特别有用。
创建和删除同义词
(1) 为 dept_sum_vu 试图创建一个简短的名称
create synonym d_sum
for dept_sum_vu;
(2) 删除同义词
drop synonym d_sum；
数据库管理员可以创建所有用户都可以访问的公共同义词。
下面的例子中将为 Alice 的 departments 表创建一个名为 dept 的公用同义词；
create public synonym dept
for alice.departments;
删除同义词
只有数据库管理员可以删除公用同义词。
控制用户访问
在多用户环境中，要维护数据库访问和使用的安全性。通过 Oracle 服务器数据库安全性，您可以执行以下任务：
(1) 控制数据库访问；
(2) 授予对数据库中特定对象的访问权限；
(3) 通过 Oracle 数据字典确认给定的和接受的权限；
(4) 为数据库创建同义词；
数据库安全性可以分为以下两类：系统安全性 和 数据安全性。
系统安全性包括在系统级别上对数据库进行访问和使用，
eg.y用户名和口令、分配给用户的磁盘空间和用户可移执行的操作。
数据库安全性包括 对数据库对象的访问和使用，以及用户可以对数据库对象的执行操作。
权限
(1) 数据库安全性；
(1-1) 系统安全性；
(1-2) 数据安全性；
(2) 系统权限：获得数据库访问的权限；
(3) 对象权限：处理数据库对象的内容；
(4) 方案：对象的集合，例如表、试图和序列的集合；
权限：是执行特定 SQL 语句的权利。数据库管理员（DBA）是高级别的用户，他可以授予用户访问数据库及其对象的权限。
用户需要具有系统权限才能访问数据库，需要具有对象权限才能处理数据库中对象的内容。
用户还可以将额外权限授予其他用户或角色，这些用户或角色是相关权限的指定组。
方案：是对象的集合，例如，表，视图和序列的集合。方案归数据库所有，并且与该用户同名。
系统权限
(1) 可用的系统权限超过100个；
(2) 系统管理员对于诸如以下的任务具有高级别的系统权限：
(2-1) 创建新用户；
(2-2) 删除用户；
(2-3) 删除表；
(2-4) 备份表；
系统权限
共有超过100个的不同系统权限可供用户和角色使用。系统权限通常由数据库管理员提供。
系统权限 授权的操作
----------------------------------------------------
create user 被授予者可以创建其他 Oracle 用户（DBA 角色所需的权限）。
drop user 被授予者可以删除另一个用户。
drop any table 被授予者可以从任何方案中删除表
backup any table 被授予者可以用导出实用程序备份任何方案中的任何表。
select any table 被授予者可以查询任何方案中的表、试图或快照。
create any table 被授予者可以在任何方案中创建表。
--------------------------------------------------------------------------
创建用户
DBA 可以使用 create user 语句创建用户。
create user user_name
identified by password ;
在此语法中：
user 是要创建的用户名；
password 指定该用户登录所需的口令；
DBA 可以通过执行 create user 语句创建一个户。创建的用户此时没有任何权限。
之后，DBA 可以将权限授予给用户。这些权限决定了用户可以在数据库级别上执行的操作。
create user scott2
identified by tiger2;
用户系统权限
(1) 一旦创建了用户，DBA 就可以将特定的系统权限授予该用户。
grant privileges [,privilege……]
to user [,user/role, public ……]；
在此语法中：
privileges 是否授予系统的权限
user| role|public 是否授予权限的用户名或角色，而 public 则表示将该权限授予每个用户。
注：当前系统可以在字典试图 session_privs 中找到。
eg.
select * from session_privs
例如，应用程序开发人员可以具有以下系统权限；
create session
create table
create sequence
create view
create procedure
常见的用户权限
既然已经创建了用户，DBA就可以将权限分配给应户。
系统权限 授权的操作
------------------------------------------------
create session 连接到数据库
create table 在用户方案中创建表
create sequence 在用户方案中创建序列
create view 在用户方案中创建试图
create procedure 在用户方案中创建处处过程、函数或程序包
授予系统权限
DBA 可以将特殊的系统权限授予用户。
grant create session， create table， create sequence， create view
to scott;
DBA 可以使用 grant 语句将系统分配给用户。一旦将权限授予用户后，用户就可以立即使用那些权限。
角色
什么是角色？
角色 是 可以授予用户的相关权限的制定组。 此方法使得撤销和维护权限变得更容易。
一个用户可以访问几个角色，而同一个角色也可以分配几个用户。角色通常是为数据库应用程序创建的。
创建和分配角色
首先，DBA 必须创建角色，然后，DBA 可以将权限分配给角色。在将用户分配给角色。
语法
create role role_name ;
在此语法中;
role_name 是要创建的角色名称。
既然已经创建了角色，DBA 就可以使用 grant 语句将用户分配给角色，以及权限给角色。
创建角色和将权限授予角色
(1) 创建角色；
create role manager;
(2) 将授权授予角色；
grant create table, create view
to manager;
(3) 将角色授予用户
grant manager to dehaan ,kochhar;
注：如果向用户授予了多个角色，则他们会受到与所有这些角色关联的所有权限。
更改口令
(1) DBA 会创建您的用户账户，并为您初始设置一个口令。
(2) 您可以使用使用 alter user 语句来更改口令。
语法为：
alter user user_name identified by password;
在此语法中：
user_name 是用户名；
password 指定新口令；
注：此语句虽然用来更改口令，但它还有其他的选项。必须具有 alter user 权限，才能更改它的任一选项。
eg.
alter user scott
identified by lion;
对象权限
--------------------------------------------------------------------------------
对象权限 表 试图 序列 过程
--------------------------------------------------------------------------------
alter --- ---
delete --- ---
execute ----
index ---
insert --- ---
references --- ---
select --- --- ----
update --- ---
----------------------------------------------------
对象权限 是能够对特定表、试图、序列或过程执行特殊操作的权限或权利。
每个对象都有特定的一组可授予的权限。
注意：适用于序列的权限只有 select 和 alter 。
通过指定可更新列的子集可以限定 update, references 和 insert 权限。
可以通过下面的方法限制 select 权限：
用列的子集创建一个试图，然后只授予对该试图的 select 权限。
授予的同义词的权限会转换为对于该同义词引用的基表权限。
(1) 对象权限会根据对象的不同而变化。
(2) 对象的所有者对其具有全部的权限。
(3) 所有者可以将属于它的对象的权限授予其他用户。
grant object_priv [(columns)]
on object
to {user|role|public}
[with grant option];
在此语法中：
object_priv 是要授予的对象的权限；
all 指定所有的对象权限；
columns 指定授予权限的表或试图中的列；
no object 是授予权限的对象；
to 说明权限授给谁；
public 将对象权限授予所有的用户；
with grant option 允许被授予者将对象权限授予其他用户和角色；
eg.授予对 employees 表的查询的权限。
授予了用户 sue 和 Rich 查询 employees 表的权限。
grant select
on employees
to sue, rich;
eg.将更新特定列的权限授予用户和角色，
将对表 departments 的特定列的 update 权限授予 scott 和经理（manager）角色。
grant update （department_name,location_id）
on departments
to scott, manager;
如果 sue 或 rich 要对 employees 表中的数据进行 select 操作，则他们必须使用下面的语法：
select *
from scott.employees;
或者，为他们（sue 或 rich）创建一个同义词，然后从同义词中进行 select 操作；
准则
(1) 要授予对某个对象的权限，则该对象必须在自己的方案中，或者必须被授予 with grant option 对象权限；
(2) 对象所有者可以将对该对象的任何权限授予数据库中的任何其他用户或角色。
(3) 对象所有者会自动获得对该对象的所有对象权限。
使用 with grant option 和 public 关键字
(1) 给用户特权以传递传递权限。
grant select insert
on departments
to scott
with grant option ;
(2) 允许系统上的所有用户查询 alice 的 departments 表中的数据。
grant select
on alice.departments
to public;
with grant option 关键字
使用 with grant option 子句授予的权限可以由被授予者授予其他用户和角色。如果撤销了
授予者的权限，则使用 with grant option 子句授予的对象权限也将别撤销。
public 关键字
表的所有者可以使用 public 关键字将访问权限授予所有应户。
第二个例子中，允许系统上所有用户查询 Alice 的 departments 表中的数据。
确认授予的权限
数据字典试图 说明
-------------------------------------------------------
role_sys_privs 授予角色的系统权限
role_tab_privs 授予角色的表权限
user_role_privs 用户可以访问的角色
user_tab_privs_made 授予的对用户对象的对象权限
user_tab_privs_recd 授予用户的对象权限
user_col_privs_made 授予的对用户对象的列的对象权限
user_col_privs_recd 授予用户的系统列的对象权限
User_Sys_Privs 列出授予用户的系统权限
------------------------------------------------------
确认授予的权限
如果试图执行未授权的操作，eg.在对某个表没有 delete 权限的情况下，要从该表中
删除一行，那么 Oracle 服务器将禁止执行此操作。
如果受到 Oracle 服务器返回的“table or view does exist”错误信息，则证明执行了以下操作之一：
(1) 指定了不存在的表或视图；
(2) 尝试对不具有相应权限的表或视图执行某个操作，这样可以通过访问数据库字典来查看您具有的权限。
如何撤销对象的权限
(1) 使用 revoke 语句可以撤销授予其它用户的权限；
(2) 通过 with grant option 子句授权于其他用户的权限也会被撤销；
revoke {privilege [,privilege …… ]|all}
on object
from {user[,user ……]|role|public}
[cascade constraints];
在此语法中:
cascade constraints
如果要删除通过 references 权限对该对象实行的任何引用完整性约束，则此选项是必须的。
作为用户 Alice ，撤销授予用户 scott 对 departments 表的 select 和 insert 权限。
revoke select, insert
on departments
from scott;
注: 如果用户是通过 with grant option 子句获得某项权限，则该用户也可以用 with grant option
子句授予其他用户该权限，这样就会可能出现一长串的被授予者，但不允许循环授予权限。如果所有者从
用户处撤销了某项权限，而该用户将此权限授予了其他用户，则撤销操作会级练到所有授权的权限。
eg.如果用户 A 将对某个表的 select 的权限授予用户 B，并且授予操作使用了 with grant option 子句，
则用户 B 也可以使用 with grant option 子句将该 select 权限授予用户 C，然后过户 C 也可以将该 select
权限再授予用户 D。如果用户 A 从用户 B 处撤销了权限，则用户 C 和 D 具有的这些权限也将被撤销。
数据库连接
数据库连接允许本地用户访问远程数据库中的数据。
(未完)
扫一扫
2240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
