oracle使用大全

第一章：从数据库表中检索信息

1. any、all 操作符

any操作符将一个值与某个列表中的任何值进行比较，此时必须在any之前添加一个=、<>、<、>、<=或>=操作符

例如：select * from customers where customer_id > any (2,3,4);

all操作符将一个值与某个列表中的所有值进行比较，此时必须在any之前添加一个=、<>、<、>、<=或>=操作符

例如：select * from customers where customer_id > all (2,3,4);

2. SQL操作符

like、in、between、is null、is nan(匹配nan这个特殊值，意思是“非数字”)、is infinite(匹配binary_float和binary_double中的“无穷”值)

其相反的操作符：not like、not in、not between、is not null、is not nan、is not infinite

like：下划线(_)匹配指定位置的一个字符；百分号字符(%)匹配从指定位置开始的任意多个字符

如果需要对字符串中实际的下划线或百分号字符进行搜索，可以使用escape选项来标识这些字符，

例如：'%\%%' escape '\'

select name from promotions where name like '%\%%' escape '\';

between: 包含在指定的值区间内，区间是闭区间。

例如：select * from customers where customer_id between 1 and 3;

3.逻辑操作符的优先级

如果在同一个表达式中同时使用and和or操作符，那么and的优先级要高于or

4.使用using关键字简化连接

只有在查询满足一下限制时才能进行简化：

1）.查询必须是等值连接的

2）.等值连接中的列必须同名

例如：select p.name,pt.name from products p inner join product_types pt using(product_type_id);

5.全外连接

全外连接使用连接表中所有的行，包括连接中使用的列为空值得那些行

select p.name,pt.name from products p full outer join product_types pt using(product_type_id) order by p.name

第二章：使用简单函数

1.字符函数

accii(x)：返回字符x的ascii码

chr(x)：返回ascii码为x的字符

concat(x)：将y添加到x的后面，并将得到的字符串作为结果返回

initcap(x)：将x中每个单词的首字母都转换成大写，并返回得到的字符串

instr(x,find_string[,start][,occurrence])：在x中查找字符串find_string，然后返回find_string所在的位置。可以提供下列可选参数：

start位置用来指定该函数从这个位置开始查找。x中的第一个位置是1。start位置可以是正数或负数。正的start表示相对x开头的位置偏移量，负的start表示相对x结尾的位置偏移量

occurrence参数用来说明应该返回find_string第几次出现的位置

length(x)：返回x中字符的个数

lower(x)：将x中的字母换成小写，并返回得到的字符串

lpad(x,width[,pad_string])：在x的左边补齐空格，得到总长为width个字符的字符串。该函数可以提供可选的pad_string参数，这个参数用于指定重复使用哪个字符串来补齐x左边的空位。返回x被补齐之后的结果字符串

ltrim(x[,trim_string])：从x的左边截去一些字符。可以使用可选参数trim_string来指定要截去的字符；如果不指定trim_string参数，就默认截去空格

nanvl(x,value)：如果x匹配nan这个特殊值（非数字），就返回value；否则返回x(这是oracle 10g新增的一个函数)

nvl(x,value)：如果x为空，就返回value；否则返回x

nvl2(x,value1,value2)：如果x非空，就返回value1；否则返回value2

replace(x,search_string,replace_string)：在x中查找search_string，并将其替换为replace_string

rpad(x,width[,pad_string])：在x的右边补齐空格，得到总长度为width个字符的字符串。该函数可以提供可选的pad_string参数，这个参数用于指定重复使用哪个字符串来补齐x右边的空位。返回x被补齐之后的结果字符串

rtrim(x[,trim_string])：从x的y右边截去一些字符。可以使用可选参数trim_string来指定要截去的字符；如果不指定trim_string参数，就默认截去空格

soundex(x)：返回一个包含x的发音的字符串。该函数用于对英文拼写不同但发音却比较接近的单词进行比较

substr(x,start[,length])：返回x中的一个子字符串，这个子字符串从start开始。x中的第一个位置是1。start位置可以是整数或负数。正的start表示相对x开头的位置偏移量；负的start表示相对x结尾的位置偏移量。还可以为这个子字符串指定可选参数length

trim([trim_char FROM] x)：从x的左边和右边同时截去一些字符。可以使用可选参数trim_char指定要截去的字符；如果不指定trim_string参数，就默认截去空格

upper(x)：将x中的字母转换为大写，并返回得到的字符串

第三章：日期和时间的存储与处理

1.设置默认的日期格式：

可以修改NLS_DATE_FORMAT参数的设置，在数据库的init.ora或spfile.ora文件中设置这个参数的值。还可以使用alter system 命令对NLS_DATE_FORMAT参数的值进行设置

2.使用时间值函数：

add_months(x,y)：返回x加上y个月后的结果。如果y是负数，就从x中减去y个月

last_day(x)：返回包含x的月份的最后一天

months_between(x,y)：返回x和y之间有几个月。如果x在日历中比y晚，则返回值为整数；如果x在日历中比y早，则返回负数，否则返回0

next_day(x,day)：返回从x开始，下一个day的时间值；day是一个文本字符串，例如SATURDAY

round(x[,unit])：对x取整。默认情况下，x取整为最近一天。unit是一个可选的字符串参数，它指明要取整的单元。例如，YYYY表示将x取整为最近一年的第一天

sysdate：返回数据库服务器所在操作系统中设置的当前时间值

trunc(x[,unit])：对x截断。默认情况下，x被截断为当天的开始时间。unit是一个可选的字符串参数，它指明要截断的单元。例如，MM表示将x截断为该月的第一天

3.使用时间戳

timestamp[(seconds_precision)]：存储4位的年、月、日、时(以24小时格式)、分和秒。seconds_precision为可选参数，用于指定精度，该参数为整数，范围从0~9，默认值为9；意思是表示秒的数字的小数点右边可以存储9位数字。如果试图插入的行的秒的小数位数超过TIMESTAMP存储精度，那么该小数将被四舍五入

timestamp[(seconds_precision)] with time zone：扩展timestamp，用于存储时区

timestamp[(seconds_precision)] with local time zone：扩展timestamp，将给定的时间值转换成数据库的本地时区。转换过程称为时间值得规格化

第四章：子查询

1.在关联子查询中使用exists和not exists

exists操作符用于检查子查询返回行的存在性。虽然exists也可以在非关联子查询中使用，但是exists通常用于关联子查询，需要注意的是：我们并不关心子查询返回多少行，而只关心子查询是否有任何行被返回

例如：

select employee_id,last_name from employees outer where exists (select employee_id from employees inner where inner.manager_id = outer.employee_id) order by employee_id

由于exists只是检查子查询返回的行的存在性，因此子查询不必返回一行，可以只返回字面值。这样可以提高查询性能

not exists执行的操作在逻辑上刚好与exists相反。在需要检查行是否不存在与子查询返回的结果中时，就可以使用not exists

例如：

select product_id,name from products outer where not exists (select 1 from purchases inner where inner.product_id = outer.product_id) order by product_id

2.exists和not exists 与 in和not in 的比较

in操作符用来检查特定的值是否存在于值列表中。exists与in不同：exists只是检查行的存在性，而in则要检查实际值。

通常来讲，exists的性能比in高，因此应该尽可能地使用exists，而不是in

not exists 和 not in 之间有如下重要区别：当值列表包含空值时，not exists 返回true，而not in 则返回false

例如：

select product_type_id,name from product_types outer where not exists (select 1 from products inner where inner.product_type_id = outer.product_type_id) order by product_type_id;

product_type_id                   name

------------------                    ------

5                                             Magazine

下面用not in重写上面的例子：

select product_type_id,name from product_types where product_type_id not in (select product_type_id from products) order by product_type_id;

结果：no rows selected

3.使用子查询因子化

可以将子查询放在with子句中并在with子句的外部引用这些子查询，这就成为子查询因子化

例如：

with customer_purchases as
 (select cu.customer_id, sum(pr.price * pu.quantity) as purchase_total
    from customers cu, purchases pu, products pr
   where cu.customer_id = pu.customer_id
     and pu.product_id = pr.product_id
   group by cu.customer_id)
select * from customer_purchases order by customer_id;

with customer_purchases as
 (select cu.customer_id, sum(pr.price * pu.quantity) as purchase_total
    from customers cu, purchases pu, products pr
   where cu.customer_id pu.customer_id
     and pu.product_id = pr.product_id
   group by cu.customer_id),
average_purchase as
 (select sum(purchase_total) / count(*) as average from customer_purchases)
select *
  from customer_purchases
 where purchase_total < (select average from average_purchase)
 order by customer_id;

第五章：高级查询

1.使用集合操作符

union all： 返回各个查询检索出的所有行，包括重复的行

union： 返回各个查询检索出的所有行，不包括重复行

intersect：返回两个查询检索出的共有行

minus：返回从第一个查询检索出的行中减去第二个查询检索出的行之后剩余的行

例子：

union all：

select product_id, product_type_id, name
  from products
union all
select prd_id, prd_type_id, name
  from more_products;

union：

select product_id, product_type_id, name
  from products
union
select prd_id, prd_type_id, name
  from more_products;

intersect：

select product_id, product_type_id, name
  from products
intersect
select prd_id, prd_type_id, name
  from more_products;

minus：

select product_id, product_type_id, name
  from products
minus
select prd_id, prd_type_id, name
  from more_products;

2.使用translate()函数

translate(x,from_string,to_string)函数在x中查找from_string中的字符，并将其转换成to_string中对应的字符

例如：

select translate('SECRET MESSAGE: MEET ME IN THE PARK','ABCDEFGHIJKLMNOPQRSTUVWXYZ','EFGHIJKLMNOPQRSTUVWXYZABCD') from dual;

运行结果：WIGVIX QIWWEKI: QIIX QI MR XLI TEVO

translate()函数也可以用来转换数字：

select translate(12345,54321,67890) from dual

运行结果：09876

3.使用connect by 和 start with 子句

语法：

select [level,] column, expression, ... from table

[where where_clause]

[[start with start_condition] [connect by prior prior_condition]];

其中：

• level是“伪列”，代表树的第几层。对于根节点来说，level返回1，根节点的子节点返回2，依次类推
• start_condition 定义了层次化查询的起点。例如，可以将start_condition定义为employee_id=1，表示查询从employee_id为1的员工开始

• prior_condition 定义了父行和子行之间的关系。例如，可以将prior_condition定义为employee_id=manager_id，表示父节点的employee_id和子节点的manager_id之间存在关系，也就是说，子节点的manager_id指向父节点的employee_id

SQL语句：

select employee_id,manager_id,first_name,last_name from more_employees start with employee_id=1 connect by prior employee_id = manager_id;

        语句直白意思就是，先拿到 start with 后面条件的行，然后拿到 prior 后面条件的值，拿到值后，再以非 prior 后面的条件等于刚才拿到的值去递归查找

4.使用伪列level

例如：

select level,employee_id,manager_id,first_name,last_name from more_employees start with employee_id=1 connect by prior employee_id = manager_id order by level;

select count(distinct level) from more_employees start with employee_id = 1 connect by prior employee_id = manager_id  //将level去重后计算总共有多少个数

5.使用递归子查询因子化查询分层数据

with reporting_hierarchy (employee_id,manager_id,reporting_level,first_name,last_name) as (select employee_id,manager_id,0 reporting_level,first_name,last_name from more_employees where employee_id=1 union all select e.employee_id,e.manager_id,reporting_level+1,e.first_name,e.last_name from reporting_hierarchy r,more_employees e where r.employee_id=e.manager_id) select employee_id,manager_id,reporting_level,first_name,last_name from reporting_hierarchy order by employee_id;

6.使用rollup和cube子句

rollup：为每个分组返回小计行，同时为所有分组返回总计行

cube：返回每一个列组合的小计行，同时在末尾增加总计行

示例表：

divisions：存储商店中的部门信息

jobs：存储商店中的职位信息

employees2：存储员工信息

select * from divisions

结果集：

div        name

-----      ---------------

sal         sales

ope       operations

sup        support

bus        business

select * from jobs

结果集：

job         name

-----       ---------------

wor        worker

mgr        manager

eng         engineer

tec          technologist

pre          president

select * from employees2 where rownum <= 5

结果集：

employee_id    div      job     first_name        last_name           salary

--------------    ------   ------   ---------------  ---------------      ------------------

1      bus      pre      James              Smith                 800000

2      sal       mgr    Ron                  Johnson             350000

3      sal       wor     Fred                 Hobbs                140000

4      sup      mgr    Susan               Jones                  200000

5      sal       wor     Rob                  Green                 350000

使用rollup子句：

rollup子句扩展了group by 子句，为每一个分组返回一行小计，并为全部分组返回总计

下面这个查询使用rollup，注意最后多了一行，它包含所有分组的工资总计

select division_id,sum(salary) from employees2 group by rollup(division_id) order by division_id

结果集：

div             sum(salary)

------          --------------

bus             1610000

ope             1320000

sal               4936000

sup             1015000

   8881000

select division_id,job_id,sum(salary) from employees2 group by rollup(division_id,job_id) order by division_id,job_id;

结果集：

div        job        sum(salary)

------    ------      -------------

bus       mgr       530000

bus       pre         800000

bus       wor        280000

bus                      1610000

ope       eng        245000

ope       mgr        805000

ope       wor        270000

ope                      1320000

sal         mgr        4446000

sal         wor        490000

sal                       4936000

sup        mgr       465000

sup        tec         115000

sup        wor        435000

sup                       1015000

                             8881000

使用cube子句：

cube子句对group by 进行扩展，返回cube中所有列组合的小计信息，同时在最后显示总计信息

例如：

select division_id,job_id,sum(salary) from employees2 group by cube(division_id,job_id) order by division_id,job_id;

结果集：

div       job       sum(salary)

------   ------     -------------

bus      mgr      530000

bus      pre        800000

bus      wor       280000

bus                    1610000

ope      eng       245000

ope      mgr      805000

ope      wor       270000

ope                   1320000

sal        mgr       4446000

sal        wor        490000

sal                      4936000

sup      mgr       465000

sup      tec         115000

sup      wor        435000

sup                     1015000

            eng        245000

            mgr        6246000

            pre         800000

            tec          115000

            wor         1475000

                           8881000

7.使用grouping()函数

grouping()函数可以接受一列，返回0或1。如果列值为空，那么grouping()函数返回1；如果列值非空，返回0。gruoping()函数只能在使用rollup或cube的查询中使用

在rollup中对单列使用grouping()函数

select grouping(division_id),division_id,sum(salary) from employees2 group by rollup(division_id);

结果集：

grouping(division_id)     div      sum(salary)

-------------------------   -------   --------------

0      bus      1610000

0      ope      1320000

0      sal       4936000

0      sup      1015000

1                  8881000

在cube中使用grouping()函数

select case grouping(division_id) when 1 then 'All divisions' else division_id end as div,

case grouping(job_id) when 1 then 'All jobs' else job_id end as job,

sum(salary) from employees2 group by cube(division_id,job_id) order by division_id,job_id;

8.使用grouping sets 子句

使用grouping sets 子句可以得到小计行

select division_id,job_id,sum(salary) from employees2 group by grouping sets(division_id,job_id) order by division_id,job_id;

结果集：

div          job          sum(salary)

------     -------      ---------------

bus                         1610000

ope                         1320000

sal                          4936000

sup                         1015000

               eng          245000

               mgr         6246000

               pre          800000

               tec           115000

               wor          1475000

提示：grouping sets 子句的性能一般比cube好，因此，应该尽可能使用grouping sets，少使用cube

第六章：分析数据

1.使用评级函数

rank()：返回数据项在分组中的排名。rank()在排名相等的情况下会在名词中留下空位

dense_rank()：返回数据项在分组中的排名。dense_rank()在排名相等的情况下不会在名次中留下空位

row_number()：为分组中的每一行返回一个数字

①.使用rank()和dense_rank()函数

如果根据产品类型评定销售等级，两种产品类型并列第一名，那么rank()将这两种类型都设置为第一名，而下一个产品类型是第三名。dense_rank()也把这两种类型全部设为第一名，而下一个产品类型则是第二名

例如：

select prd_type_id,sum(amount),rank() over(order by sum(amount) desc) as rank,

dense_rank() over(order by sum(amount) desc) as dense_rank from all_sales 

where year = 2003 and amount is not null group by prd_type_id order by prd_type_id;

结果集：

prd_type_id    sum(amount)               rank             dense_rank

-------------     ---------------                -----              ------------

1      905081.84                      1                        1

2      186381.22                      4                        4

3      478270.91                      2                        2

4      402751.16                      3                        3

②.使用nulls first 和 nulls last 子句控制空值的排名

在使用分析函数时，可以用nulls first 和 nulls last 显式地控制空值是分组中最高还是最低的排名。

例如：

select prd_type_id,sum(amount),rank() over(order by sum(amount) desc nulls last) as rank,

dense_rank() over(order by sum(amount) desc nulls last) as dense_rank from all_sales where year = 2003 group by prd_type_id order by prd_type_id;

结果集：

prd_type_id    sum(amount)               rank             dense_rank

-------------     ---------------                -----              ------------

1      905081.84                      1                        1

2      186381.22                      4                        4

3      478270.91                      2                        2

4      402751.16                      3                        3

5                                               5                        5

③.分析函数与partition by 子句结合使用

在需要将分组划分为自分组时，可以将partition by子句和分析函数结合起来使用

例如：

select prd_type_id,month,sum(amount),

rank() over(partition by month order by sum(amount) desc) as rank

from all_sales

where year = 2003

and amount is not null

group by prd_type_id,month

order by prd_type_id,month;

④.使用row_number()函数

row_number()函数从1开始，为分组中的每行返回一个数字(行号)

例如：

select prd_type_id,sum(amount),

row_number() over(order by sum(amount) desc) as row_number

from all_sales

where year = 2003

group by prd_prd_type_id

order by prd_type_id;

2.使用窗口函数

①.计算累积和

select month,sum(amount) as month_amount,sum(sum(amount)) over(order by month rows between unbounded preceding and current row) as cumulative_amount

from all_sales where year=2003 group by month order by month;

下面计算6月到12

select month,sum(amount) as month_amount,sum(sum(amount)) over(order by month rows between unbounded preceding and current row) as cumulative_amount

from all_sales where year=2003 and month 6 and 12 group by month order by month;

②.计算移动平均值

下面这个查询计算本月与前3个月之间(共4个月)销售额的移动平均值

select month,sum(amount) as month_amount,avg(sum(amount)) over(order by month rows between 3 preceding and current row) as moving_average from all_sales where year = 2003 group by month order by month;

③.计算中心平均值

下面这个查询计算当前月份前后各一个月内(共3个月)销售额的中心移动平均值

select month,sum(amount) as month_amount,avg(sum(amount)) over(order by month rows between 1 preceding and 1 following) as moving_average from all_sales where year=2003 group by month order by month;

④.用first_value()和last_value()获取第一行和最后一行

first_value()和last_value()函数可以获取窗口中的第一行和最后一行数据

例如：

select month,sum(amount) as month_amount,first_value(sum(amount)) over(order by month rows between 1 preceding and 1 following) as previous_month_amount,last_value(sum(amount)) over(order by month rows between 1 preceding and 1 following) as next_month_amount from all_sales where year = 2003 group by month order by month;

⑤.使用lag()和lead()函数

lag()和lead()函数可获得位于距当前行指定距离的哪一行中的数据，下面这个查询用lag()和lead()函数获得前一个月和后一个月的销售额

select month,sum(amount) as month_amount,lag(sum(amount),1) over(order by month) as previous_month_amount,

lead(sum(amount),1) over(order by month) as next_month_amount from all_sales where year = 2003 group by month order by month;

⑥.使用first和last函数

first函数和last函数可获得排序分组中的第一个值和最后一个值。

下面这个查询用first和last函数获得2003年中销售额最高和最低的月份

select min(month) keep(dense_rank first order by sum(amount)) as highest_sales_month,

min(month) keep(dense_rank last order by sum(amount)) as lowest_sales_month from sales where year = 2003 group by month order by month;

第七章：修改表的内容

1.使用returning子句返回聚合函数的计算结果

例如：

variable average_product_price number

update products set price=price*0.75 returning avg(price) into : average_product_price;

print average_product_price

2.数据库的完整性

①.主键约束

create table test(id integer constraint id_pk primary key,   //主键

name varchar2(10),

student_number varchar2(10) constraint sn_fk_grades references grades(id));  //外键

②.外键约束

A表中的列引用了B表中的列，则B表为父表，A表为子表

如果从父表中删除已经有依赖子行的一行，数据库就会返回ORA-02292错误

3.使用merge合并行

merge语句可以用来将一个表中的行合并到另一个表中

例如：

merge into products p 

using product_changes pc on(

p.product_id = pc.product_id

)

when matched then

update set p.product_type_id = pc.product_type_id,

p.name = pc.name,

p.description = pc.description,

p.price = pc.price

when not matched then

insert ( p.product_id,p.product_type_id,p.name,p.description,p.price) 

values(pc.product_id,pc.product_type_id,pc.name,pc.description,pc.price);

merge into products p

using (select nvl(product_id,'nulldata') as product_id from dual) x on (

p.product_id = x.product_id

)

when matched then

update set p.product_type_id = 'a',

p.name = 'b',

p.description = 'c',

p.price = 'd'

when not matched then

insert (p.product_id,p.product_type_id,p.name,p.description,p.price)

values('a','b','c','d','e');

4.数据库事务

①.事务的提交和回滚

执行commit语句，永久性地保存对数据库进行的修改

执行rollback语句，取消对数据库进行的修改

②.事务的开始与结束

开始：

• 连接数据库，并执行一条DML语句。注意merge也是DML语句
• 前一个事务结束后，又输入了另一条DML语句

结束：

• 执行commit或rollback语句
• 执行一条DDL语句，例如create table语句；这种情况下，会自动执行commit语句
• 执行一条DCL语句，例如grant语句，会自动执行commit语句
• 执行一条DML语句，该语句却失败了；在这种情况下，会自动为这条无效的DML语句执行rollback语句

③.保存点

在事务中的任何地方都可以设置保存点，这样可以将修改回滚到保存点处。

例如：

update product set price = price * 1.2 where product_id = 4;

savepoint savel;

下面这条语句将这个事务回滚到刚才设置的保存点处：

rollback to savepoint savel;

④.事务的ACID特性

• 原子性(Atomic)：事务是原子的，这就是说一个事务中包含的所有SQL语句组成一个工作单元
• 一致性(Consist)：事务必须确保数据库的状态保持一致，这就是说事务开始时，数据库的状态是一致的；在事务结束时，数据库的状态也必须是一致的
• 隔离性(Isolated)：多个事务可以独立运行，而不会彼此产生影响
• 持久性(Durable)：一旦事务被提交之后，数据库的变化就会被永久保留下来，即使运行数据库软件的机器后来崩溃也是如此

⑤.事务的隔离级别

事务隔离级别是一个事务对数据库的修改与并行的另外一个事务的隔离程度

• 幻象读：事务T1读取一条指定的where子句返回的结果集。然后事务T2新插入一行，这行恰好可以满足T1所使用查询中的where子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就成为“幻想”，因为对于T1来说这一行就像是变魔术似的突然出现了一样
• 不可重复读：事务T1读取一行，紧接着事务T2修改了T1刚才读取的那一行的内容，然后T1又再次读取这一行，发现它与刚才读取的结果不同了。这种现象称为“不可重复”读，因为T1原来读取的那一行已经发生了变化。
• 脏读：事务T1更新了一行的内容，但是并没有提交所做的修改。事务T2读取更新后的行。然后T1执行回滚操作，取消了刚才所做的修改。现在T2读取的行就无效了(也称为“脏”数据)，因为在T2读取这行时，T1所做的修改并没有提交

按照隔离级别从低到高依次为：

• read uncommitted：幻象读、不可重复读和脏读都允许
• read committed：允许幻象读和不可重复读，但是不允许脏读
• repeatable read：允许幻象读，但是不允许不可重复读和脏读
• serializable：幻象读、不可重复读和脏读都不允许

第八章：用户、特权和角色

1.用户

①.创建用户

create user jason identified by price;

②.修改密码

alter user jason identified by marcus;

③.删除用户

drop user jason

2.系统特权

系统权限允许用户在数据库中执行特定的操作，比如执行DDL语句

系统特权：

create session：连接到数据库

create sequence：创建序列。序列是一系列数字，通常用来自动填充主键列

create synonym：创建同义词。同义词用于引用其他模式中的表

create table：在用户模式中创建表

create any table：在任何模式中创建表

drop table：删除用户模式中的表

drop any table：删除任何模式中的表

create procedure：创建存储过程

execute any procedure：执行任何模式中的存储过程

create user：创建用户

drop user：删除用户

create view：创建视图。视图是存储的查询，可以用来对多个表和对列进行访问。然后就可以像查询表一样查询视图

①.向用户授予系统特权

connect system/password

grant create session, create user, create table to steve;

此处可以使用with admin option选项，这样被授权的用户就可以把这种特权再授予其他用户，例如：

grant execute any procedure to steve with admin option

②.检查授予用户的系统特权

通过查询user_sys_privs可以检查某个用户具有哪些系统特权，例如：

select username,privilege,admin_option from user_sys_privs order by username,privilege;

其中：

username：表示当前用户的用户名

privilege：表示用户拥有的系统特权

admin_option：表示用户是否能将这种特权授予其他用户

③.使用系统特权

create user roy identified by red;

④.撤销用户的系统特权

可以使用revoke语句撤销某个用户的系统特权。例如：

revoke create table from steve;

revoke execute any procedure from steve;

3.对象特权

对象特权允许用户对数据库对象执行特定的操作，例如对表执行DML语句

对象特权：

select：执行查询操作

insert：执行插入操作

update：执行修改操作

delete：执行删除操作

execute：执行存储过程

①.向用户授予对象特权

可以使用grant语句向用户授予对象特权，例如：

connect store/password

grant select,insert,update on store.products to steve;

grant select on store.employees to steve;

下面将对last_name和salary列的update特权授予steve用户：

grant update(last_name,salary) on store.employees to steve;

可以使用grant选项，这样被授权的用户就可以把这种特权再授予其他用户，即指定with grant option。例如：

grant select on store.customers to steve with grant option;

②.检查已授权的对象特权

通过查询user_tab_privs_made可以检查某个用户对哪些表向其他用户开放了哪些对象特权，例如：

select grantee,table_name,grantor,privilege,grantable,hierarchy from user_tab_privs_made where table_name = 'PRODUCTS';

其中：

grantee：代表特权被授予的用户

table_name：由授予的特权操作的对象(例如表)名

grantor：授权者

privilege：对象上的特权

grantable：特权被授予的用户是否可以将这种特权再授予其他用户。该值等于YES或NO

hierarchy：特权是否构成层次关系。该值等于YES或NO

③.检查已接受的对象特权

通过查询user_tab_privs_recd表可以检查某个用户被授予了哪些表上的哪些对象特权。例如：

select owner,table_name,grantor,privilege,grantable,hierarchy from user_tab_privs_recd order by table_name,privilege;

其中：

owner：代表拥有该对象的用户

table_name：由授予的特权操作的对象名

grantor：授权者

privilege：对象上的特权

grantable：特权被授予的用户是否可以将这种特权再授予其他用户。该值等于YES或NO

hierarchy：特权是否构成层次关系。该值等于YES或NO

4.创建同义词

我们已经看到可以通过制定模式名后跟表名的方式来访问其他模式中的表。例如，当steve用户对store模式中的customers表进行检索时，就要对store.customers执行查询。为表创建同义词后就可以不用再输入模式名了，同义词是使用create synonym语句创建的。例如

首先将create synonym系统特权授予steve用户

connect system/password

grant create synonym to steve;

然后以steve用户的身份连接到数据库，并执行一条create synonym语句

connect steve/brown

create synonym customers for store.customers;

创建公共同义词：

我们也可以为表创建公共同义词。在创建公共同义词之后，所有的用户都可以看到这个同义词。例如

connect system/password

grant create public synonym to store;

connect store/password

create public synonym products for store.products;

5.撤销用户的对象特权

可以使用revoke语句撤销某个用户的对象特权。例如：

connect store/password

revoke insert on products from steve;

6.角色

角色就是一组特权，可以分配给用户或其他角色。角色的优点：

• 并不是一次一个地将特权直接授予用户，而是先创建角色，向角色授予一些特权，然后再将角色授予多个用户和角色
• 在增加或删除角色的某种特权时，被授予该角色的所有用户和角色都会自动获得新增加的特权或自动失去这种特权
• 可以将多个角色授予用户或角色
• 可以为角色设置密码

①.创建角色

首先连接到system为用户分配create role系统特权，然后用户才可以创建角色。例如：

connect system/password

grant create role to store;

connect store/password;

create role product_manager;

create role hr_manager;

create role overall_manager identified by manager_password;

②.为角色授权

grant语句可以用来将特权授予角色。既可以将系统特权和对象特权授予角色，也可以将一个角色授予另一个角色。例如：

grant select,insert,update,delete on product_types to product_manager;

grant select,insert,update,delete on products to product_manager;

grant select,insert,update,delete on salary_grades to hr_manager;

grant select,insert,update,delete on employees to hr_manager;

grant create user to hr_manager;

grant product_manager,hr_manager to overall_manager;

③.将角色授予用户

connect system/password

create user john identified by brown;

create user harry identified by blue;

grant create session to john;

grant create session to harry;

grant语句可以用来将角色授予用户，例如：

grant hr_manager to john;

grant overall_manager to harry;

④.检查授予用户的角色

通过查询user_role_privs可以检查已经授予用户哪些角色。例如：

connect john/password

select username,granted_role,admin_option,default_role,os_granted from user_role_privs;

其中：

username：表示该角色被授予的用户名

granted_role：表示授予该用户的角色名

admin_option：该用户是否可以将该角色授予其他用户或角色。该列的值为YES或NO

default_role：当用户登录到数据库时，是否默认启用该角色。该列的值为YES或NO

os_granted：该角色是否由操作系统授予。该列的值为YES或NO

注意：

密码保护的角色是禁用的。前一个例子的default_role列显示overall_manager角色是禁用的。这个角色是密码保护的，必须输入角色密码才能使用角色

⑤.检查授予角色的系统特权

通过查询role_sys_privs可以检查已经授予角色哪些系统特权。例如：

select role,privilege,admin_option from role_sys_privs order by role,privilege;

其中：

role：角色名

privilege：授予该角色的系统特权

admin_option：授予该特权时是否使用了with admin option选项。该列的值为YES或NO

⑥.检查授予角色的对象特权

通过查询role_tab_privs可以检查已经授予角色哪些对象特权。例如：

select role,owner,table_name,column_name,privilege,grantable from role_tab_privis where role='HR_MANAGER' order by table_name,column_name,privilege;

其中：

role：该特权被授予的用户

owner：拥有该对象的用户

table_name：由授予的特权操作的对象名

column_name：列名

privilege：对象上的特权

grantable：授予特权时是否使用with grant option选项。该列的值为YES或NO

⑦.使用已授予角色的特权

对于无密码保护的角色，通过角色将特权授予用户之后，用户连接到数据库后就可以立即使用这种特权了。

对于密码保护的角色，用户必须输入角色密码才能使用这个角色，必须使用set role命令并提供密码来启用该角色。例如：

connect harry/password

set role overall_manager identified by manager_password;

⑧.启用和禁用角色

禁用角色：

connect system/password

alter user john default role all except hr_manager

启用角色：

set role hr_manager;

禁用角色后没有把hr_manager角色设置为默认角色，这表示当john用户退出登录后再次重现登录回来时，该角色将不可用。下面将hr_manager角色设置为默认角色，它在退出登录后仍然保留

connect system/password

alter user john default role hr_manager;

可以将角色设置为none，这表示没有角色

connect john/password

set role none;

也可以使用下面语句将角色设置为除hr_manager之外的任何角色

set role all except hr_manager

⑨.撤销角色

connect store/password

revoke overall_manager from harry

⑩.从角色中撤销特权

connect store/password

revoke all on products from product_manager;

revoke all on product_types from product_manager;

⑪.删除角色

drop role语句可以用来删除角色

connect store/password

drop role oveall_manager;

drop role hr_manager;

第九章：创建表、序列、索引和视图

1.表

①.创建表

语法：

create [global temporary] table table_name(

column_name type [constraint constraint_def default default_exp]

[,column_name type [constraint constraint_def default default_exp] ...]

)

[on commit {delete | preserve} rows]

tablespace tab_space;

说明：

global temporary：说明该表的行都是临时的，这种表成为临时表。临时表中的行是特定于某个用户会话的，行的有效期由on commit子句指定。

on commit：控制临时表中行的有效期。delete说明这些行在事务结束时被删除。preserve说明这些行被保留到用户会话结束时才被删除。如果对临时表没有指定on commit选项，那么默认值是delete

tab_space：为该表指定表空间。如果没有指定表空间，该表就被存储在该用户的默认表空间中

②.获得表中列的信息

从user_tab_columns视图中可以获得有关表中各列的信息。例如：

select column_name,data_type,date_length,data_precision,data_scale from user_tab_columns where table_name = 'PRODUCTS';

其中：

table_name：表名

column_name：列名

data_type：列的数据类型

data_length：数据的长度

data_precision：如果为数值列指定了精度，该列就是其精度

data_scale：数值列小数部分的位数

2.修改表

①.添加列

alter table table_name add column_name integer(type)

②.修改列

修改列的长度：

alter table order_status2 modify status varchar2(15);

修改数值列的精度：

alter table order_status2 modify id number(5);

修改列的数据类型：

alter table order_status2 modify status char(15);

修改列的默认值：

alter table order_status2 modify last_modified default sysdate-1 ;

③.删除列

alter table order_status2 drop column initially_created;

3.约束

create table test(id varchar2(32)  default sys_guid() constraint id_pk primary key,
name varchar2(100)  default user not null,
status varchar2(1) constraint s_ck check(status in ('0','1')));

create table test(id varchar2(32)  default sys_guid(),
name varchar2(100)  default user not null,
status varchar2(1),
constraint id_pk primary key(id),
constraint s_ck check(status in ('0','1')));

check：指定一列或一组列的值必须满足某种条件

not null：指定一列不允许存储空值。这实际上是作为一种check约束强制执行的

primary key：指定表的主键。主键由一列或多列组成，主键唯一标识了表中的一行

foreign key：指定表的外键。外键引用另一个表中的一列，在自引用的情况中，则引用本表中的一列

unique：指定一列或一组列只能存储唯一的值(换言之，各行的值互不相同，但可以有多行为空)

check option：通过视图对表行所做的改变必须先经过检测

read only：指定视图是只读的

①.添加check约束

alter table order_status2 add constraint order_status2_status_ck check(status in ('placed','pending','shipped'));

②.添加not null 约束

alter table order_status2 modify status constraint order_status2_status_nn not null;

③.添加foreign key约束

alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employees(employee_id);

使用带有on delete cascade 子句的foreign key 约束，可以指定在父表中删除一行时，子表中匹配的所有行也都被删除。例如：

alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employees(employee_id) on delete cascade

使用带有on delete set null子句的foreign key 约束，可以指定在父表中删除一行时，子表中匹配行的外键都将被设置为空值。例如：

alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employees(employee_id) on delete set null;

④.删除约束

alter table order_status2 drop constraint order_status2_status_uq;

⑤.禁用约束

默认情况下，约束在创建时是启用的。在创建约束时可以在constraint子句的末尾添加disable来禁用约束。例如：

alter table order_status2 add constraint order_status2_status_uq unique(status) disable;

使用alter table语句的disable constraint子句可以禁用现有的约束。例如：

alter table order_status2 disable constraint order_status2_status_nn ;

⑥.启用约束

alter table order_status2 enable constraint order_status2_status_uq;

要启用约束，表中的所有行必须都能满足约束条件，通过enable novalidate，可以选择只对新数据应用某个约束，例如：

alter table order_status2 enable novalidate constraint order_status2_status_uq;

⑦.获得有关约束的信息

通过查询user_constraints视图可以获得有关约束的信息，例如；

select constraint_name,constraint_type,status from user_constraints;

通过查询user_cons_columns视图可以获得有关列的约束的信息，例如：

select owner,constraint_name,table_name,column_name from user_cons_columns ; 

owner表示约束的所有者

4.表的其他信息

①.重命名表

rename order_status2 to order_status;

②.向表中添加注释

comment on table order_status2 is '订单状态表' ;

获得表的注释：select * from user_tab_comments where table_name = 'order_status2';

获得列的注释：select * from user_col_comments where table_name = 'order_status2';

③.截断表

truncate table order_status2。

这将删除表中的所有行，并重置表的存储区

5.序列

①.创建序列

create sequence语句可以用来创建序列，语法如下：

create sequence sequence_nae

[start with start_num]

[increment by increment_num]

[{maxvalue maximum_num | nomaxvalue}]

[{minvalue minimum_num | nominvalue}]

[{cycle | nocycle}]

[{cache cache_num | nocache}]

[{order | noorder}];

其中：

start with：指定序列从哪个整数开始，起始值默认为1

increment_num：指定序列每次增加的整数数量，增量值默认为1。increment_num绝对值必须小于maximum_num与minimum_num之差

maximum_num：指定序列的最大整数。maximum_num必须大于或等于start_num，而且必须大于minimum_num

nomaxvalue：指定升序列的最大值为10的28次方-1，降序序列的最大值为-1。nomaxvalue为默认值

minimum_num：指定序列的最小整数。minimum_num必须小于或等于start_num，而且minimum_num必须小于maximum_num

nominvalue：指定升序序列的最小值为1，降序序列的最小值为-(10的27次方-1)。nominvalue为默认值

cycle：指定序列即使已经达到最大值或最小值也继续生成整数。当升序序列达到最大值时，下一个生成的值是最小值。当降序序列达到最小值时，下一个生成的值是最大值

nocycle：指定序列在达到最大值或最小值之后就不能再生成整数了。nocycle是默认值

cache：指定缓存。这个选项为序列预分配整数

cache_num：指定要保留在内存中的整数的个数。默认要缓存的整数位20个。可以缓存的整数最少为2个

nocache：指定不缓存任何整数。这可以阻止数据库为序列预分配值，从而避免序列产生不连续的情况，但这会降低性能。序列不连续的情况之所以会发生，是因为在关闭数据库时，缓存的值将全部丢失。如果省略cache和nocache，则数据库默认缓存的整数位20个

order：确保安装请求次序生成整数。

noorder：不确保安装请求次序生成整数。noorder为默认值

举例：

create sequence s_test2 start with 10 increment by 5 minvalue 10 maxvalue 20 cycle cache 2 order

②.获取有关序列的信息

从user_sequences视图中可以获得有关序列的信息

③.使用序列

序列中包含两个“伪列”，分别是currval和nextval，可以分别用来获取序列的当前值和下一个值。

例如：

select s_test.nextval from dual;

select s_test.currval from dual;

使用序列填充主键：

insert into order_status2(id,status,last_modified) values(s_test.nextval,'placed','01-JAN-2006');

使用序列指定默认列值：

create sequence s_default_value_for_column;

create table test_with_sequence(id integer constraint test_with_sequence_pk primary key,sequence_value integer default s_default_value_for_column.nextval);

④.修改序列

限制：

不能修改序列的初值

序列的最小值不能大于当前值

序列的最大值不能小于当前值

alter sequence s_test increment by 2；将序列的增量修改为2

⑤.删除序列

drop sequence s_test;

6.索引

索引的缺点：在向表中添加新行时，必须花费额外的时间来更新该行的索引

①.创建索引

create [unique] index index_name on table_name(column_name[,column_name ...])

tablespace tab_space;

其中：

unique：指定索引列中的值必须是唯一的

index_name：指定索引名

table_name：指定数据库表

column_name：指定要对哪个列创建索引。也可以对多列创建索引，这种索引就成为复合索引

tab_space：指定存储索引的表空间。如果没有提供表空间，那么索引就被存储在用户的默认表空间中

举例：

create index i_customers_last on customers(last_name);

create unique index i_customers_phone on customers(phone);

②.创建基于函数的索引

如果使用函数，则索引不会起作用，必须创建函数索引。例如：

create index i_func_customers_last_name on customers(upper(last_name));

③.修改索引

alter index i_customers_phone rename to i_customers_phone_number;

④.删除索引

drop index i_customers_phone_number;

7.视图

①.创建并使用视图

create [or replace] view [{force | noforce}] view view_name

[(alias_name[,alias_name ...)] as subquery

[with {check option | read only} constraint constraint_name];

其中：

• or replace：说明如果视图已经存在，就替换现有视图
• force：说明即使基表不存在也要创建视图
• noforce：说明如果基表不存在就不创建视图；noforce是默认值
• view_name：指定视图名
• alias_name：为子查询的表达式指定别名。别名的个数必须与子查询中表达式的个数相同
• subquery：指定子查询，用于对基表进行检索。如果已经提供了别名，可以在select之后的列表中使用别名
• with check option：说明只有子查询检索的行才能被插入、修改或删除。默认情况下，在插入、更新或删除行之前并不会检查这些行是否能被子查询检索
• with read only：说明只能对基表中的行进行只读访问
• constraint_name：指定约束的名称

举例：

connect system/password

grant create view to store;

connect store/password

create view cheap_products_view as select * from products where price < 15;

注意：只能对简单视图执行DML操作；复杂视图不支持DML操作

由于cheap_products_view没有使用with check option，因此可以插入、更新或删除视图不能检索的行。下面这个例子插入一行，价格为16.50(该价格大于15，因此视图不能检索出该行)

insert into cheap_products_view(product_id,product_type_id,name,price) 

values(14,1,'EF',16.50);

②.创建具有check option约束的视图

通过为视图添加check option约束，可以指定对视图执行的DML操作不行满足子查询的条件

例如；

create view cheap_product_view2 as select * from products where price < 15 with check option constraint cheap_products_view2_price;

下面这个例子视图使用cheap_products_view2视图插入价格为19.50的一行。会返回错误

insert into cheap_products_view2(product_id,product_type_id,name,price)

values(15,1,'SF',19.5);

③.创建具有read only约束的视图

通过为视图添加read only约束，可以指定视图是只读的。例如：

create veiw cheap_products_view3 as select * from products where price<15 with read only constraint cheap_products_view3_read_only;

④.修改视图

create or replace可以彻底替换视图。例如：

create or replace view product_average_view as select product_type_id, avg(price) average_price from products where price<12 group by product_type_id having avg(price) > 11 order by product_type_id;

也可以用alter view来删除视图。例如：

alter view cheap_products_view2 drop constraint cheap_products_view2_price;

⑤.删除视图

drop view veiw_name;