oracle数据库创建表、序列、视图、约束要点

1. 系统开发的阶段:
Strategy and Analysis
Design
Build and Document
Transition
Production


2. 数据模型
Model of system in client's mind
Entity model of client's model
Table model of entity model
Tables on disk


3. 实体关系模型 (ERM)概念
ERM ( entity relationship modeling)
实体 存有特定信息的目标和事件 例如: 客户,订单等
属性 描述实体的属性 例如: 姓名,电话号码等
关系 两个实体间的关系 例如:订单和产品等
实体关系模型图表里的约定
Dashed line (虚线) 可选参数 “may be”
Solid line (实线) 必选参数 “must be”
Crow's foot (多线) 程度参数 “one or more”
Single line (单线) 程度参数 “one and only one”


4. 实体关系模型例子
每个订单都必须有一个或几个客户
每个客户可能是一个或几个订单的申请者


5. 实体关系的类型
1:1 一对一 例如: 的士和司机
M:1 多对一 例如: 乘客和飞机
1:M 一对多 例如: 员工和技能


6. 校正实体关系的原则
属性是单一值的, 不会有重复
属性必须依存于实体, 要有唯一标记
没有非唯一属性依赖于另一个非唯一的属性

7. 定义结构时的注意事项
减少数据冗余
减少完整性约束产生的问题
确认省略的实体,关系和属性


8. 完整性约束的要求
Primary key 主关键字 唯一非NULL
Foreign key 外键 依赖于另一个Primary key,可能为NULL
Column 字段名 符合定义的类型和长度
Constraint 约束条件 用户自定义的约束条件,要符合工作流要求
例如: 一个销售人员的提成不能超过它的基本工资
Candidate key 候选主关键字 多个字段名可组成候选主关键字, 其组合是唯一和非NULL的


9. 把实体关系图映射到关系数据库对象的方法
把简单实体映射到数据库里的表
把属性映射到数据库里的表的字段, 标明类型和注释
把唯一标记映射到数据库里的唯一关键字
把实体间的关系映射到数据库里的外键

其它的考虑:
设计索引,使查询更快
建立视图,使信息有不同的呈现面, 减少复杂的SQL语句
计划存储空间的分配
重新定义完整性约束条件


10. 实体关系图里符号的含义
PK 唯一关键字的字段
FK 外键的字段
FK1,FK2 同一个表的两个不同的外键
FK1,FK1 两个字段共同组成一个外键
NN 非null字段
U 唯一字段
U1,U1 两个字段共同组成一个唯一字段


九、创建表
1. ORACLE常用的字段类型
ORACLE常用的字段类型有
VARCHAR2 (size) 可变长度的字符串, 必须规定长度
CHAR(size) 固定长度的字符串, 不规定长度默认值为１
NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数
最长38位. 不够位时会四舍五入.
DATE 日期和时间类型
LOB 超长字符, 最大可达4G
CLOB 超长文本字符串
BLOB 超长二进制字符串
BFILE 超长二进制字符串, 保存在数据库外的文件里是只读的.
数字字段类型位数及其四舍五入的结果
原始数值1234567.89
数字字段类型位数 存储的值
Number 1234567.89
Number 12345678
Number  错
Number(9,1) 1234567.9
Number(9,3) 错
Number(7,2) 错
Number(5,-2) 1234600
Number(5,-4) 1230000
Number(*,1) 1234567.9


2. 创建表时给字段加默认值 和约束条件
创建表时可以给字段加上默认值
例如 : 日期字段 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如: 非空 NOT NULL
不允许重复 UNIQUE
关键字 PRIMARY KEY
按条件检查 CHECK (条件)
外键 REFERENCES 表名(字段名)

3. 创建表的例子
CREATE TABLE DEPT(
EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)) ;
CREATE TABLE region(
ID number(2) NOT NULL PRIMARY KEY,
postcode number default '0' NOT NULL,
areaname varchar2(30) default ' ' NOT NULL);


4. 创建表时的命名规则和注意事项
表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
大小写不区分
不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．
用和实体或属性相关的英文符号长度有一定的限制
注意事项:
建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
一个表的最多字段个数也是有限制的，254个.


5. 约束名的命名规则和语法
约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)
约束名字符串的命名规则同于表和字段名的命名规则


6. 使用约束时的注意事项
约束里不能用系统函数,如SYSDATE和别的表的字段比较
可以用本表内字段的比较
想在事务处理后, 做约束的检查
SQL> alter session set constraints deferred.


7. 由实体关系图到创建表的例子 s_dept
前提条件:已有region表且含唯一关键字的字段id
SQL> CREATE TABLE s_dept
(id NUMBER(7)
CONSTRAINT s_dept_id_pk PRIMARY KEY,
name VARCHAR2(25)
CONSTRAINT s_dept_name_nn NOT NULL,
region_id NUMBER(7)
CONSTRAINT s_dept_region_id_fk REFERENCES region (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));


8. 较复杂的创建表例子
SQL> CREATE TABLE s_emp (
id NUMBER(7)   CONSTRAINT s_emp_id_pk PRIMARY KEY,
last_name VARCHAR2(25)  CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2 CONSTRAINT s_emp_userid_nn NOT NULL
CONSTRAINT s_emp_userid_uk UNIQUE,
start_date DATE DEFAULT SYSDATE,
comments VARCHAR2(25),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7)   CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),
salary NUMBER(11,2),
commission_pct NUMBER(4,2)  CONSTRAINT s_emp_commission_pct_ck CHECK
(commission_pct IN(10,12.5,15,17.5,20))
);


9. 通过子查询建表
通过子查询建表的例子
SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date
FROM s_emp WHERE dept_id = 41;
SQL> CREATE TABLE A as select * from B where 1=2;
只要表的结构.


10. 用子查询建表的注意事项
可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。
用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来,
其它的约束条件和默认值都没有继承过来.
根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.


11. Foreign Key的可选参数ON DELETE CASCADE
在创建Foreign Key时可以加可选参数:
ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.
如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.


12. 如果数据库表里有不满足的记录存在，建立约束条件将不会成功.


13. 给表创建和删除同义词的例子
SQL> CREATE SYNONYM d_sum
2 FOR dept_sum_vu;
SQL> CREATE PUBLIC SYNONYM s_dept
2 FOR alice.s_dept;
SQL> DROP SYNONYM s_dept;


十、ORACLE里的数据字典
1. 什么是数据字典 ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库
的产生而产生, 随着数据库的变化而变化, 体现为sys用户下所有的一些表和视图.


2. 数据字典里存了以下内容：
用户信息
用户的权限信息
所有数据对象信息表的约束条件统计分析数据库的视图等
不能手工修改数据字典里的信息. 


3. 常用的数据字典
Dictionary 存放所有数据表，视图，同义词名称和解释
Dict_columns 数据字典里字段名称的和解释
Dba_users 用户 Dba_tablespaces 表空间
Dba_data_files 数据库的文件 Dba_free_space 空闲表空间
Dba_rollback_segs 回滚段
User_objects 数据对象 User_constraints 约束条件
User_sequences 序列号 User_views 视图
User_indexes 索引 User_synonyms 同义词
Session_roles 用户的角色 User_role_privs 用户的角色权限
User_sys_privs 用户的系统权限 User_tab_privs 用户的表级权限
V$session 实时用户情况 V$sysstat 实时系统统计
V$sesstat 实时用户统计 V$sgastat 实时SGA使用
V$locked_object 实时锁 V$controlfile 控制文件
V$logfile 日志文件 V$parameter 参数文件


4. 数据字典的分类
数据字典四大类别
User_ 用户下所有数据库对象
All_ 用户权限范围内所有的数据库对象
Dba_ 所有的数据库对象
V$Content$nbsp; 统计分析数据库的视图 赋于oem_monitor权限非DBA用户也可查询V$*视图


5. 查询数据字典
SQL> select * from dictionary where instr(comments,'index')>0;
SQL> select constraint_name, constraint_type,
2 search_condition, r_constraint_name
3 from user_constraints
4 where table_name = ‘&table_name';


十一. 控制数据


1 、INSERT(往数据表里插入记录的语句)
SQL> insert into 表名(字段名1, 字段名2, ……) values ( 值1, 值2, ……);
SQL> insert into 表名(字段名1, 字段名2, ……) select (字段名1, 字段名2, ……)
from 另外的表名 where 条件;
可以用&标记变量的方法多次输入记录
快速插入数据的方法, 一般用于大于128M的数据转移
SQL> insert /*+ append */ into 表名
select * from 另外的用户名 .另外的表名 WHERE 条件;
SQL> commit;
注意事项：
用INSERT /*+ APPEND */ 的方法会对target_tablename产生级别为6的独占锁，
如果运行此命令时还有对target_tablename的DML操作会排队在它后面,
对OLTP系统在用的表操作是不合适的。


2. 插入字符串类型的字段的注意事项:
字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个 单引号’ ’
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验
‘’ 标记是NULL, user 标明当前用户
日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒
用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE( )还有很多种日期格式, 可以参看ORACLE DOC.
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS
NSERT时最大可操作的字符串长度小于等于4000个单字节,
如果要插入更长的字符串, 请考虑字段用CLOB类型,
方法借用ORACLE里自带的DBMS_LOB程序包.


3、UPDATE (修改数据表里记录的语句)
SQL> UPDATE 表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL,
最好在修改前进行非空校验;
值N超过定义的长度会出错, 最好在插入前进行长度校验.
新功能，可以修改子查询后的结果集
例子：SQL> update (select * from s_dept) set id=50 where id=60;


4、DELETE (删除数据表里记录的语句)
SQL> DELETE FROM 表名 WHERE 条件;
注意：删除记录并不能释放ORACLE里被占用的数据块表空间.
它只把那些 被删除的数据块标成unused.
如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
SQL> TRUNCATE TABLE 表名;
此操作不可回退.


5、 SQL语句的分类
数据定义语言(DDL)：create、alter、drop（创建、修改结构、删除）（其他：rename）
数据操纵语言(DML)：insert、delete、select、update（增、删、查、改）（其他：truncate）
数据控制语言(DCL)：grant、revoke（授权、回收）、set role
事务控制：commit、rollback、savepoint（其他：lock table、set constraint、set transaction）
审计控制：audit、noaudit
系统控制：alter system 会话控制：alter session
其他语句：comment（添加注释）、explain plan、analyze、validate、call


6、ORACLE里事务控制
Commit 提交事务
Rollback 回退事务
Savepoint 设置断点, 在事务中标记位置, 事务结束, 断点释放
事务结束的情况遇到commit或者rollback遇到DDL和DCL语句发现错误，如死锁用户退出
SQL*PLUS系统重启或崩溃


7. DML操作的注意事项
以上SQL语句对表都加上了行级锁, 确认完成后,
必须加上事物处理结束的命令COMMIT 才能正式生效,
否则改变不一定写入数据库里.行级锁也未能得到释放.
如果想撤回这些操作, 可以用命令 ROLLBACK 复原.
在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,
应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作,
可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理.
太过频繁的commit不好


十二、改变表和约束条件


1. 改变表的几种情况(1) 运行时会加表级锁
改变表的名称
SQL> RENAME 表名1 TO 表名2; SQL> ALTER TABLE 表名1 RENAME TO 表名2;
在表的后面增加一个字段
SQL> ALTER TABLE 表名 ADD 字段名 字段名描述
[ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……];
修改表里字段的定义描述
SQL> ALTER TABLE 表名 MODIFY 字段名1 字段名1描述 [ DEFAULT expr ][ NOT NULL ]
[ ,字段名2 ……]; 记录为空时，可以减少字段长度，
改变字段类型修改DEFAULT值只作用于修改后的INSERT和UPDATE的记录修改NOT NULL
约束只对现存含非空记录的字段起作用
1. 改变表的几种情况(2) 运行时会加表级锁
删除表里的某个字段
SQL> ALTER TABLE 表名 DROP 字段名;
给表里的字段加上/禁止/启用约束条件
SQL> ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名
PRIMARY KEY (字段名1[,字段名2 ……]);
SQL> ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名
UNIQUE (字段名1[,字段名2 ……]);
加唯一关键字或者唯一约束条件时自动建立索引
说明：禁止唯一关键字和唯一约束时索引仍然存在，可以被使用.
1. 改变表的几种情况(3) 运行时会加表级锁
删除表里的约束条件
SQL> ALTER TABLE 表名 DROP CONSTRAINTS 约束名 [CASCADE];
会把约束相关的索引一起删除. CASCADE能同时删去外键的约束条件.
把表放在或取出数据库的内存区
SQL> ALTER TABLE 表名 CACHE;
SQL> ALTER TABLE 表名 NOCACHE;
改变表存储的表空间
SQL> ALTER TABLE 表名 MOVE TABLESPACE 表空间名 ;
注意: 如果被转移表空间的表含有索引, 表转移后索引变得不可用.
我们要删除旧索引,建立新索引


2. 删除表及表里的数据
删除表
SQL> DROP TABLE 表名 [CASCADE CONSTRAINTS];
清空表里的记录
SQL> TRUNCATE TABLE 表名;
按时间清空日志表里的记录，使用重新命名的方法
(应用程序可能有短暂出错, 可以选择在不繁忙的时间执行)
按原来表A的建表语句创建新表A1,把表A重命名为A2（如果表A上有较频繁的DML操作,
会对表加上行级锁，重命名过程用递归的方式循环做，直到DML操作结束，命名成功).
把创建新表A1重命名为A
历史记录表A2备份或删除


3. 删除表后应该注意的问题
删除表后把表里的索引一起删去.
删除表后会结束基于它的悬而未决的事物
删除表后根据表创建的views,synonym,stored procedure,stored function依然存在，
但views,synonym变成非法的. 需要手工找出它们并删除
如果用了CASCADE CONSTRAINTS会把与它相关的约束一起删除
此操作不可回退


4. 给表加注释
加注释的语法
SQL> COMMENT ON TABLE 表名 | COLUMN表名.字段名 IS ‘text‘
加注释的例子
SQL> comment on table s_emp is ‘Enployee information‘;
SQL> comment on column s_emp.last_name is ‘‘;


十三、创建序列号


1. 创建序列号里各参数的解释
SQL> CREATE SEQUENCE name [INCREMENT BY n]
[START WITH n] [{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
INCREMENT BY n 一次增长n 个数字
NOMAXVALUE 缺省值10E+27
NOMINVALUE 缺省值1
NOCYCLE 不循环, 常用于唯一关键字
CACHE n 在内存里缓存n个序列,出错回退时会丢失
oracle8i里默认的n是20
序列号的名称一般可以采用“表名_字段名”的命名规则


2. 插入自动增长序列号字段的方法
INSERT时如果要用到从1开始自动增长的数字做唯一关键字, 应该先建立一个序列号.
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 NOCYCLE NOCACHE;
其中最大的值按字段的长度来定,比如定义的自动增长的序列NUMBER , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL
例子: SQL> insert into s_dept(id, name, region_id) values (s_dept_id.nextval, 'finance', 2);
1 row created.
只有运行了序列号的名称. nextval后序列号的名称. currval 才有效才有值.


3. 查询序列号的情况
SQL> select sequence_name, min_value, max_value, increment_by, last_number from user_sequences;
其中last_number指的是序列号的下一个值.


4. 改变序列号
SQL> ALTER SEQUENCE sequence [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
注意: 不能改变它的起始值
如果要改变序列的起始值, 先把序列号删除掉, 再新建一个.


5. 删除序列号
SQL>DROP SEQUENCE sequence;


6. 不能用序列号的nextval和currval的地方
视图的查询
有distinct的查询
有group by,having,order by的查询
有子查询的查询
表里的缺省值


十四、创建视图


1. 视图的概念和优点
视图是基于一个或多个表及视图的一些查询语句, 它象显示数据的视窗, 它本身是不存储数据的.
视图可以限制数据库的访问, 更好的控制权限
使用户使用简单的查询语句
数据的非依赖性
同一数据的不同表现形式


2. 创建视图的语法
SQL> CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
参数解释:
FORCE 表不存在时,视图仍然可以创建成功
WITH CHECK OPTION 只有符合视图定义的记录才能被插入或修改
WITH READ ONLY 不允许DML操作
Oracle8i以后创建视图可以用order by


3. 创建修改视图的例子
SQL> CREATE OR REPLACE VIEW salvu41 AS SELECT id, first_name FIRST,
last_name LAST, salary MONTHLY_SALARY
FROM s_emp WHERE dept_id = 41;
SQL> CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.name, MIN(e.salary),
MAX(e.salary),
AVG(e.salary) FROM s_emp e, s_dept d WHERE e.dept_id = d.id GROUP BY d.name;
注意: 如果用select * from table_name创建的视图
table_name的结构改变后 view要重建或compile后才能显示新的字段内容


4. 查询视图的数据字典
SQL> set long 1600;
SQL> select view_name,text from user_views;
说明: 可以根据视图text_length来设置set long 数字;
User_updatable_columns视图能查询视图里能被修改的字段


5. 简单和复杂的视图对比
特 性 简单视图 复杂视图
表的数量 一个 多个
有函数吗 没有 有
有分组操作吗 没有 有
有基于视图的DML操作吗 有 没有


6. 在视图上可以用DML命令吗
可以, 但有一定的限制条件
没有下面的情况, 可以删除view里的记录. group function, group by, distinct
没有上面和下面的情况, 可以修改view里的记录. 字段表达式,
例如: salary*12 含rownum的view
没有上面两种情况, 且view里含基表里所有非空字段的情况, 可以往view里插入记录.


7. 在视图里使用 WITH CHECK OPTION约束条件
SQL> create or replace view empvu41
as select * from s_emp where dept_id = 41
with check option constraint empvu41_ck;
如果运行下面命令会出错ora-01402
SQL> update empvu41 set dept_id=42 where id=16;
原因: 视图empvu41里规定只能看部门号为41的记录 修改后会把记录排除在视图empvu41以外
与它的约束条件冲突


8. 删除视图
SQL> DROP VIEW view_name;


十五、创建索引
1.索引的概念
索引是数据库里的一种数据对象
它利用B*树, hash, bitmap结构直接快速地访问数据
它和表是分开存放的两个实体
索引创建好了后, 由系统自动调用和管理


2. 什么时候创建索引
自动创建的索引:唯一关键字, 唯一的约束条件
手工需要创建的索引:大表查询时, sql语句where后经常用到的字段或字段组合
字段内容差别很大有大量NULL值表很大, 返回记录数较少


3. B*树索引的结构 每个索引由字段值和指针或ROWID组成


4.创建索引的语法
CREATE INDEX 索引名 ON 表名 ( 字段1, [字段2, ……] ) TABLESPACE 表空间名;


5.创建索引的注意事项
创建索引时会加行级独占锁
一个表的索引最好不要超过三个 (特殊的大表除外)
最好用单字段索引
索引最好和表分不同的表空间存放
结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引
大表的索引会占用很大的存储空间
不要建唯一的索引, 而应该加唯一的约束条件


6.查询索引的方法
查询数据字典user_indexes和user_ind_columns
例子:
SQL> SELECT ic.index_name, ic.column_name,
2 ic.column_position col_pos,ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = 'S_EMP';
注意: 数据字典里存放的字符都是大写的.



7. 不用索引的地方
表很小
where后不经常使用的比较字段
表被频繁修改
返回记录数很多
where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件


8. 重建索引的语法
ALTER INDEX 索引名 REBUILD TABLESPACE 原来表空间名 NOLOGGING;
定期重建索引可以减少索引的碎片, 更有效地使用表空间.

9. 删除索引
SQL> drop index 索引名;
SQL> alter table 表名 drop constraint 约束名;


十六、控制用户访问


1.权限的类别
系统级权限: 针对整个系统操作的权限
如: 用户名/密码, 使用表空间的限额等
对象级权限: 针对某个具体object操作的权限
如: 针对某个表, 视图, 表的某个字段的select, update, delete权限

2. 查看当前数据库的用户信息
SQL>select username,default_tablespace,temporary_tablespace from dba_users;
查看在线用户信息
SQL>select count “number”,username “current username” from v$session group by username;
用户查看自己的缺省表空间SQL>select username,default_tablespace from user_users;

3. 创建新用户
SQL> create user username identified by password
default tablespace tablespace_name temporary tablespace temp
quota unlimited on tablespace_name
quota 1k on system
[quota 1k on other_tablespace_name ……] ;
给用户赋权限
SQL> grant connect, resource to username;
查看当前用户的权限角色
SQL> select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL> select * from user_sys_privs;SQL> select * from user_tab_privs;

4 、常用的角色及其权限
CONNECT 8 privs 连上Oracle,做最基本操作
RESOURCE 8 privs 具有程序开发最的权限
DBA 114 privs 数据库管理员所有权限
EXP_FULL_DATABASE 5 privs 数据库整个备份输出的权限
IMP_FULL_DATABASE 64 privs 数据库整个备份输入的权限
查看角色明细的系统权限
SQL> select * from role_sys_privs;

5、改变老用户 可以改变老用户的密码, 缺省表空间, 临时表空间, 空间限额.
SQL> alter user username identified by password
default tablespace tablespace_name
temporary tablespace temp
quota unlimited on tablespace_name
quota 1k on system
[quota 1k on other_tablespace_name ……] ;
撤销用户的角色或权限
SQL> revoke role_name or priv_name from username;
注意事项
撤消用户的角色dba时, 同时撤消了用户unlimited tablespace的系统权限,
切记要再次赋予resource角色给此用户
SQL> grant resource to username;

6、删除用户
如果用户下没有任何数据对象
SQL> drop user username;
如果用户下有数据对象
SQL> drop user username cascade;
注意事项
如果用户下有含clob,blob字段的表, 应该先删除这些表后,才能用cascade选项完全删除.

7、角色的概念和管理
角色是命名多个相关权限的组合. 能把它赋于其它的用户或角色我们能创建角色,
使权限管理更容易一些.

8、赋于系统的权限语法和例子
语法:
SQL> GRANT sys_priv TO {user|role|PUBLIC} [WITH ADMIN OPTION];
例子:
SQL> GRANT create session TO sue, rich;
SQL> GRANT create table To scott, manager;
注意:
如果用WITH ADMIN OPTION通过中间用户赋于的系统权限中间用户删除后, 系统权限仍然存在.

9、赋于数据对象级的权限语法和例子
语法:
SQL> GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION];
例子:
SQL> GRANT select ON s_emp TO sue, rich;
SQL> GRANT update (name, region_id)
ON s_dept TO scott, manager;
注意: 如果用WITH GRANT OPTION通过中间用户赋于的对象权限
中间用户删除后,对象权限就不存在了.

10、rownum表中的行号，自动生成的，只能用<,<=操作符
rowid用来唯一表示数据库表中的一行
在oralce上的分页需要用到伪列,只所以这么做，是因为oralce不支持rownum比较
比如说找出第三行纪录，那就是
select last_name, salary
from (select rownum a, b.*
from s_emp b)
where a=3
如果找出第１０行到第２０行的数据的话，那就必须用到
select last_name, salary
from (select rownum a, b.*
from s_emp b)
where a > 10 and a < 2;
这是sql级别的分页，优点是速度快，缺点是可移植性差；


11、











COURSEID COURSENAME SCORE
---------- ---------- ----------
1  java  70
2  oracle  90
3  xml  40
4  jsp 30
5  servlet  80


SQL> select courseid, coursename ,score ,decode(sign(score-61),-1,'fail','pass') as mark from course_v;
COURSEID  COURSENAME  SCORE  MARK
----------  ----------  ----------  ----
1   java  70  pass
2  oracle   90  pass
3  xml  40   fail
4  jsp   30   fail
5   servlet    80  pass


12、

已经知道原表
           year       salary
------------------   ---------------------
        2000           1000
        2001           2000
        2002           3000
        2003           4000
显示查询结果
           year       salary
------------------   ---------------------
        2000           1000
        2001           3000
        2002           6000
        2003           10000
即salary为以前年的工资的和；
解答过程如下：
  select b.year,sum(a.salary)
  from test a,test b
  where a.year<=b.year
  group by b.year
  order by year