在线咨询
QQ咨询
服务热线
服务热线:13125520620
TOP

PL/SQL学习笔记(六)-数据库

发布时间:2011-11-12 浏览:4942

序列:

自动产生的唯一值;
一个共享的对象;
典型的用法是作为主键的值;
insert into 给主键提供值。
替代了应用的代码;
通过将序列CACHE(预先生成一部分序列号,放入到内存中)到内存中,可以加速对序列的访问。

CREATE SEQUENCE sequence_name
  [increment by n]//步长
  [start with n]//起始点
  [maxvalue n/nomaxvalue]//递增
  [minvalue n/nominvalue]//递减
  [cycle/nocycle]//循环
  [cache n/nocache];//n为预先生成序列号的个数,默认为20。

查询序列:
user_sequences
last_number 序列将要产生的下一个号是多少;

select sequence_name,min_value,max_value,increment_by last_number from user_sequences;

伪列:NEXTVAL 引用下一个可用的序列值,不同的用户每次引用都会获得一个唯一的值。
      CURRVAL 得到当前的值(刚被领走的号)。
在CURRVAL执行前必须先通过NEXTVAL得到一个初始的值。

序列名.NEXTVAL/CURRVAL
  
序列发生间隙是正常的,保证唯一即可。


序列的修改:
   ALTER SEQUENCE sequence_name 
   increment by
  maxvalue
  cycle    
  cache;

start with 不能修改。

删除序列:

drop  SEQUENCE sequence_name;

 


索引:

一个方案中的对象;
被ORACLE服务器用来加速对表的查询;
通过使用快速路径访问方法快速定位数据;
与表独立存放;
被ORACLE服务器使用和维护。

一定是 WHERE 条件的才有可能使用索引。


手动创建索引:
CREATE INDEX index_name on table_name (col_name);

考虑创建索引的情况:
*、包含了大量不同值的列;
*、包含了大量空值的列;
*、一个或者多个列经常被一起出现在 WHERE 条件中或者作为连接的条件出现;
*、表的数据量很大,而且对表的查询经常是得到表中数据的2%到4%(少量数据).


不应该创建索引的情况:
*、一个很小的表;
*、列很少被用于查询的条件;
*、表上的大多数查询是得到大量数据的;
*、表中的数据经常发生变动;
*、要被索引的列被作为条件表达式的一部分。


查看:
user_indexes得到索引的定义和唯一性。

user_ind_columns 得到索引的名称,表名和列名。
select ic.index_name,ic.column_name,ic.column_position,
 ic.uniquenes
from user_indexes ix,user_ind_columns ic
where ic.index_name=ix.index_name and ic.table_name='table_name';

删除索引:
DROP INDEX index_name;
为了删除索引,必须拥有索引或者拥有DROP ANY INDEX 权限。 

 

控制用户的访问

数据库的安全性

系统安全性:
 系统权限(system privilege),获得访问数据库的能力。
  超过一百个
  创建新用户:
   CREATE USER user_name IDENTIFIED BY password;
  删除用户
  删除表
 授予权限:
  GRANT priv_list TO user/public/role(角色);
  grant create session ,create table,create sequence to user_name;   
  在授予建表权限的同时也应该赋予存储空间。
 分配配额:ALTER  USER user_name QUOTA nM ON space_name;

数据安全性:
 对象权限(object privilege),获得维护数据库的能力。
 
 每种对象的权限都不相同。
 对象的所有者拥有对象的所有权限。
 对象的所有者可以将自己的对象权限赋予其他人。   

 GRANT object_priv_list [(col_list)]
 ON owner.object TO user/role/public
 [WITH GRANT OPTION];--将权限授予用户的同时,该用户也拥有了授予其他用户对象权限的功能。
 (及联授予)会导致及联移除。

 移除权限:
 REVOKE priv_list/all  ON object FROM user;
 
方案:数据库对象的集合,包括表、视图、序列.....。


角色:
 CREATE ROLE role_name;
 GRANT priv_list TO role_name;
 GRANT role_name TO user_list/role_list;

修改口令:
方法 (1)ALTER USER user_name IDENTIFIED BY password;
 (2)password + 回车
 
USER_SYS_PRIVS 当前用户的系统权限。
USER_ROLE_PRIVS 当前用户的角色权限。
USER_tab_privs_made 用户对象被授予的他人的信息。

 


使用集合操作

UNION

select employee_id,job_id from employees
union
select employee_id,job_id from job_history;

两个表的并集,但不显示重复行。
执行的时候要先排序再剔重,所以结果集是有序的。

union all

也是两个表的并集,而且显示重复行。
语法同上。

intersect

select employee_id,job_id from employees
intersect
select employee_id,job_id from job_history;


minus

select employee_id,job_id from employees
minus
select employee_id,job_id from job_history;

e-j=e-e与j的交集;
j-e=j-j与e的交集;

注:
在select 列表中的表达式必须有同样的数目和类型。

 匹配 SELECT 语句
 select employee_id,job_id,salary from employees
 union
 select employee_id,job_id,0      from job_history; 

括号可以用拉修改序列的执行顺序。

ORDER BY 子句:
 只能在整个集合的最后出现;
 可以按照第一个 SELECT 语句中的列名,别名或者位置号排序。  

 

 


GROUP BY 子句的增强

CUBE 操作符的 GROUP BY

在 GROUP BY 子句中使用 ROLLUP 或者 CUBE 来产生分组小计;


ROLLUP 分组产生包括规则的分组结果和小计的结果的组合;
GROUP BY [ROLLUP](col_name_list)
ROLLUP:
a       ab abc

  abc
 ab ab
a a a
all all all

CUBE 分组产生包括 ROLLUP 产生的结果和交叉分组小计。
cube:

a ab abc

a ab abc
all a ab
 b ac
 all bc
  a
  b
  c
  all

GROUPING 函数
参数一定是在CUBE或ROLLUP里进行分组排序的字段或表达式之一。

通过1或0来判断结果集中的空值是由于本身列的值是空的,还是由于使用CUBE或ROLLUP产生的空值。
1 代表是由于分组产生的空值,没有参与分组。
0 代表是由于列本身产生的空值,参与了分组,但分组中没有包含它。

GROUPING SETS

可以使用 GROUPING SETS 在同一个语句中定义多个组集。

只需要访问一次基表。
不需要写很复杂的UNION语句。
GROUPING SETS 子句中组合的元素越多,语句的执行性能就越好。

group by GROUPING SETS((abc),(ab),(bc),(a),(b))


组合列:

是一个列的组合,在分组计算时被作为一个单元处理。

 


高级子查询

成对子查询:
行内视图的性能比成对子查询的性能高。

相关子查询:
主查询的字段在子查询里做条件(特征)。
主查询先执行,取出第一条数据,把该数据传入子查询做比较,返回查询结果给主查询,主查询根据这个结果再做查询
依次类推
直到主查询中没有可查询列为止。

 

EXISTS操作符

EXISTS 操作符测试子查询的结果是否存在;
返回 TRUE 或 FALSE
查询机制:


如果一个子查询找到了结果:

在内部子查询中不在继续执行
条件被设为TRUE

如果一个子查询没有找到结果:
条件被设为FALSE

select col_list from table_name tab_alias

where exists (select 'x' from table_name where col=tab_alias.col);
用的是相关子查询

NO EXISTS操作符

和NOT IN 相对应,速度要快,性能好。

UPDATE 中的相关子查询

update emp e
set department_name in(select d.department_name from departments d where e.department_id=d.department_id);

delete 中的相关子查询

TAG
软件定制,软件开发,瀚森HANSEN,辽宁,沈阳,抚顺
0
该内容对我有帮助