oracle数据库（五）

LESSON 12  Altering Tables and Constraints(修改表结构和约束)DDL
目标：
1.添加和修改列
2.添加，enable，disable，或者remove约束
3.删除表
4.删除表中所有数据并回到表定义的初始状态（截断表）
5.修改对象的名字
6.给对象添加注释，从数据字典中查看注释
用到的命令：
1.Alter table ：
1.添加和修改列
2.添加和删除约束
3.enable，disable约束
2.drop table命令移除表中所有行和表结构
3.rename，truncate，comment
4.当执行以上DDL语句时，事务自动提交(create)
功能：
1.增加列
  语法：
alter table tb_name
add column datatype [default val] constraint .....
note:
1.如果添加not null(primary key约束要求值也不能为null)约束，需要保证当前表中没有数据存在。
2.新添加的列，相当于表定义中最后一个定义的列。
   例如：
create table test_ddl2(
id number(7) constraint pk_testddl2_id primary key,
age number(3)
);
insert into test_ddl2 values(1,12);
alter table test_ddl2 add name varchar2(10) not null;//error
alter table test_ddl2 add name varchar2(10) default 'test' constraint nn_testddl2_name not null;
alter table test_ddl2 add (gender varchar2(2) check(gender in('F','M')),t_id number references test_tcl2(id));
2.删除列：
语法：alter table tableName drop column column_name;
例如：alter table test_ddl2 drop column gender;
3.修改列属性：(数据类型和约束)
语法：ALTER TABLE tableName
 MODIFY (column datatype [DEFAULT expr][NOT NULL]
[, column datatype]...);
note：
修改列的规则：
1.可以增加字段的宽度或者精度
2.如果列的值为null或者表中没有数据，可以降低宽度和精度
3.给当前列，后续添加的数据指定默认值。
4.当且仅当当前列中没有null值时，可以定义当前列为not null.
5.当前列中的值为null时，可以修改列的数据类型
6.如果需要给某个字段添加not null约束，只能使用modify。
例如：
insert into test_ddl2(id) values(2);
alter table test_ddl2 modify age number(3) not null;//error
alter table test_ddl2 modify age number(2);//error number(4)
4.增加约束
语法：alter table tb_name add 约束的完整定义
只能增加能够使用表级约束的约束,非空约束使用modify
alter table test_ddl2 add constraint test_pk_id unique(name);
5.删除约束：
语法：alter table tb_name drop 约束名。
例如：
alter table test drop constraint test_pk_id;
删除组件约束时，同时删除和他依赖的外键约束
create table test_ca1(
id number(3) constraint pk_ca1_id primary key
);
insert into test_ca1 values(1);
create table test_ca2(
id number(3) constraint fk_ca2_id references test_ca1(id)
);
insert into test_ca2 values(1);
insert into test_ca2 values(2);//error
alter table test_ca1 drop constraint pk_ca1_id cascade;//被引用做为外键时加关键字cascade删除
insert into test_ca2 values(2);
6.使一个约束失效：
   语法：alter table tb_name disable constraint constraint_name [cascade];


alter table test_ca2 disable constraint fk_ca2_id;
alter table test_ca1 disable constraint pk_ca1_id;
note：添加cascade表明要让所有的依赖约束都失效。
alter table test_ca1 disable constraint pk_ca1_id cascade;
依赖于test_ca1的pk_ca1_id的外键也将失效
7.使一个约束生效：
   语法：alter table tb_name enable constraint constraint_name;
note：
1.当启用unique和primary key约束时，会自动创建索引。
例如：alter table test enable constraint test_id_pk;
8.删除表：
  drop table tb_name [cascade constraint];
  note：
1.删除表中所有数据
2.所有的索引被删除
3.使用cascade constraint,级联删除所有的依赖完整性约束
  例如：
drop table test_ca1;//主键被引用做为外键不可直接删除
drop table test_ca2;
drop table test_ca1;
或者:
drop table test_ca1 cascade constraint;//级联删除，子表中的外键约束也被删除。
删除之后，可以通过：
select column_name,constraint_name
from user_cons_columns
where table_name = 'TEST_CA2';
查看是否约束还在。
9.重命名：rename
重命名表：
rename old_tb_name to new_tb_name;
重命名列：
alter table tb_name rename column old_col_name to new_col_name;
note:
1.重命名可以用来修改table，view，sequence，synonym
2.只有是这个对象的拥有者，才能重命名。
例如：
rename test_ca2 to test_ca3;  将表名重命名为emp2
alter table test_ca3 rename column id to eid;
10.截断表：truncate
语法：truncate table tb_name
note:
1.清空表记录
2.释放当前表所占用的表空间。返回建表初始状态
3.是一个DDL命令。
4.一旦删除，事务不能回滚。
例如：truncate table test_ca3;


delete和truncate的比较：
delete：可以指定删除某些列,在事务没有提交之前可以回滚。
truncate：只能清空表,不能回滚。
11.给表加注释：comments
语法：
comment on table talbe_name is '注释内容'
comment on column table_name.column_name is '注释内容';
例如：
comment on table test_ca3 is '测试表';
comment on column test_ca3.eid  is '测试列';
查看注释：
select * from user_tab_comments
where lower(table_name)='test_ca3';


select * from user_col_comments
where lower(table_name) = 'test_ca3';


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


LESSON 13  Creating Sequences (创建序列)


概念：
所谓序列，在oracle中就是一个对象，这个对象用来提供一个有序的数据列，这个有序的数据列的值都不重复。


1.序列可以自动生成唯一值
2.是一个可以被共享的对象
3.典型的用来生成主键值的一个对象
4.可以替代应用程序代码
5.当sequence的值存放在缓存中时可以提高访问效率。


创建序列语法：
CREATE SEQUENCE name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]


note:
1.increment by n:表明值每次增长n(步长),如果n是正数就递增,如果是负数就递减 默认是1
2.start with n: 从n开始
3.{MAXVALUE n | NOMAXVALUE}： 设置最大值
4.{MINVALUE n | NOMINVALUE}： 设置最小值，start with不能小于最小值。
5.CYCLE | NOCYCLE          ： 是否循环，建议不使用
6.CACHE n | NOCACHE  ： 是否启用缓存,每次缓存n个值
序列的属性(伪列)：
1.nextval : 返回下一个可用的序列值。
就算是被不同的用户调用，每次也返回一个唯一的值。
2.currval ：获取序列当前的值。
在currval调用之前，必须保证nextval已经获取过一次值。


例：create sequence testseq_id_seqt1
maxvalue 5
minvalue 2
CYCLE
NOCACHE;
//第一个值先nextval 后 currval
select testseq_id_seqt1.nextval from dual;
select testseq_id_seqt1.currval from dual;
例如：
create sequence testseq_id_seq
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
nocache;
使用sequence：


create table test_seq(
id number(3) primary key
);
1.向表中插入数据
insert into test_seq values(testseq_id_seq.nextval);
select * from test_seq;
2.查看序列的当前值
select testseq_id_seq.currval from dual;
3.获取序列的下一个值。
select testseq_id_seq.nextval from dual;
note:可以通过数据字典user_sequences查看当前用户所拥有的序列信息。
例如：
select sequence_name,min_value,max_value,last_number,INCREMENT_BY,cache_size
from user_sequences
where lower(sequence_name) = 'testseq_id_seq';


修改sequence：


ALTER SEQUENCE name
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]


note：
1.必须是序列的拥有者，或者具有alter权限
2.修改后的序列，只对之后的值起作用。
3.不能修改start with，如果想改，只能删除，重新创建，启动。


alter sequence testseq_id_seqt1
nomaxvalue
minvalue 2
nocycle
NOCACHE;


删除sequence：
drop sequence seq_name;
例如：
drop sequence testseq_id_seq;


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


LESSON 14 creating  view (创建视图)


概念：
视图：所谓视图就是提取一张或者多张表的数据生成一个映射，管理视图可以同样达到操作原表的效果，方便数据的管理以及安全操作。(是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表)
视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储查询结果的一个虚拟表。视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束。
视图的存储
与表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间。


视图的优势：
1.信息隐藏
      比如s_emp表中有工资，可以创建视图，隐藏工资信息。（可以配合权限，让某个用户只能查看
 视图，不能查看表。）通过视图可以设定允许用户访问的列和数据行。
2.使复杂查询变得简单,简化用户的SQL 语句,实际上就是SQL语句操作的结果作为视图的基表来使用。
3.数据独立。
4.相同数据的不同展示形式。


视图的分类：
1.简单视图
2.复杂视图
比较：
1、简单视图只从单表里获取数据，复杂视图从多表；
2、简单视图不包含函数和数据组，复杂视图包含；
3、简单视图可以实现DML操作，复杂视图不可以
简单视图 复杂视图
涉及到的表个数  1 1个或多个
包含函数 不包含包含
包含组数据  不包含包含
通过视图使用DML  可以 不可以




视图的创建：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]...)]
AS
select 。。。。
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]


note:
1.or replace:若所创建的试图已经存在，ORACLE自动重建该视图
2.force| noforce: 即使基表不存在也要建立该视图 | 基表不存在就不建立此视图，默认值。
3.alias: 为视图产生的列定义的别名(相当于给子查询的结果列起别名)
4.子查询中可以包含复杂的查询语法，这些细节都对用户隐藏。
5.子查询中不能包含order by子句。
6.WITH CHECK OPTION 插入或修改的数据行必须满足视图定义时的约束；换句话说，加上该关键词表示对view进行dml操作的时候，只能操作select语句中where条件限制的内容
7.WITH READ ONLY ：该视图只读，不能在这个视图上进行任何DML操作。
//在没有WITH CHECK OPTION和 READ ONLY 的情况下，子查询中不能使用ORDER BY 子句
8.查看视图结构： desc view_name;


例如创建简单视图（可以通过DML语句修改）：
如果权限不足，sqlplus "/as sysdba"-->grant create view to briupz(表示用户名)


create or replace view myView
as
select id,last_name,start_date
from s_emp
where id <= 4;




此时可以使用：
1.查看视图中所有数据信息
select * from myView;
SELECT view_name,text from user_views;
2.执行插入：
insert into myView values(111,'haha','03-5月-16'); 插入成功！
3.再次查看，找不到刚插入的数据，因为这条数据不满足id<=4,但是查看原始表s_emp，有这条数据。
如果：
create or replace view myView
(id,name,s_date)
as
select id,last_name,start_date
from s_emp
where id <= 4
with check option;


此时可以使用：
1.查看视图中所有数据信息
select * from myView；
2.执行插入：
insert into myView values(121,'haha','03-5月-16'); 插入失败！，因为视图的约束时id<=4,现在插入的id值为121，所以失败！
with check option 插入或修改的数据行必须满足视图定义时的约束


视图上的DML操作应遵循的原则：
1.简单视图可以执行DML操作；
2.在视图包含GROUP 函数，GROUP BY子句，DISTINCT关键字时不能删除数据行；
3.在视图不出现下列情况时可通过视图修改基表数据或插入数据：
a.视图中包含GROUP 函数，GROUP BY子句，DISTINCT关键字；
b.使用表达式定义的列；
c.ROWNUM伪列。
d.基表中未在视图中选择的其他列定义为非空且无默认值。
创建复杂视图(不可以通过DML语句修改)：
复杂视图可能包含分组，组函数，多表连接等。
例如：
CREATE or replace VIEW myView
(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;


create or replace view myView2
(id,name,dept_name,s_date)
as
select se.id,last_name,name,start_date
from s_emp se,s_dept sd
where se.dept_id=sd.id and se.id <= 4
with check option;
insert into myView2 values(112,'aaa','Sales','01-1月-92');//error


查看视图信息
可以使用数据字典user_views;


删除视图对象：
DROP VIEW view_name;


---------------------------------------------------------------
LESSON 15 创建index
   索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。
   建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的
   性能直接与索引的合理直接有关
概念：
1. 类似书的目录结构
2、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度
3、 索引直接指向包含所查询值的行的位置，减少磁盘I/O
4、 与所索引的表是相互独立的物理结构
5、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引
//索引由数据库自动使用


创建：
    1.自动创建
当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。
2.用户创建。
用户可以创建非唯一值所在以提高在访问行时的效率。
语法：
CREATE INDEX index_name
ON table_name (column[, column]...);
例如：
create index myIndex on s_emp (last_name);


创建成功后可以通过如下语句查看：
select index_name,index_type from user_indexes;
select * from user_ind_columns;
创建索引的原则：
1.列经常作为where子句的限定条件或者作为连接条件
2.列包含的数据量很大，并且很多非空的值。空值不能被索引，只有唯一索引才真正提高速度,
一般的索引只能提高30%左右
3.两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
4.列总是作为搜索条件
5.索引用于查出的数据量占2%~4%的情况
6.索引不是越多越好，不是索引越多越能加速查找。
7.要索引的表不经常进行修改操作


删除索引：
语法：DROP INDEX index_name;
例如：drop index myIndex;




-----------------------------------------------------------------------
LESSON 16  Controlling User Access


权限允许用户访问属于其它用户的对象或执行程序，
ORACLE系统提供权限：Object 对象级、System 系统级
查看权限的数据字典：
字典名 含义
ROLE_SYS_PRIVSSystem privileges granted to roles
ROLE_TAB_PRIVSTable privileges granted to roles
USER_ROLE_PRIVSRoles accessible by the user
USER_TAB_PRIVS_MADEObject privileges granted on the user's objects
USER_TAB_PRIVS_RECDObject privileges granted to the user
USER_COL_PRIVS_MADEObject privileges granted on the columns of the user's objects
USER_COL_PRIVS_RECDObject privileges granted to the user on specific columns


1.系统权限（系统权限是对用户而言）：
系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
DBA拥有最高的系统权限：
1，可以创建用户
语法：create user username identified by password;
例如：create user briup identified by briup;


当用户创建成功之后，此用户什么权限都没有，甚至不能登录数据库。


2. 赋予权限：
一个用户应该具有的基本权限包含：
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
如果有多个用户他们都具有相同的权限(create session,create table,create sequence),赋予权限的动作过于麻烦，要给每个用户分别制定这些权限，因此oracle提出角色的概念，可以将权限赋值给角色，然后再将角色赋值给用户。
角色：一组权限的集合
例如，我们当初在进行操作时写的：
grant resource,connect to briup;
此时resource，connect就是角色。
查询resource,connect 具有哪些权限可以使用：
select privilege,role
from role_sys_privs
where role = 'CONNECT' or role ='RESOURCE';
语法：
grant xxxx to user_name ;
例如：
grant create view to briup;
3.回收权限
语法：revoke xxx  from user_name;
例如：
 revoke create view from jd1702;
4.修改密码：
语法：alter user xxx identified by xxxx;
例如：
alert user briupz identified by briupzz;
5.删除用户：
语法：drop user username [cascade];
note: cascade:当用户下有表的时候，必须使用cascade级联删除。
例如： drop user test cascade;
//查询有哪些用户:select username from dba_users;//dba登录
2.对象权限(针对对象，类似表对象等)：
对象权限：select, update, insert, alter, index, delete, all  //all包括所有权限
对象的 拥有者拥有所有的权限。


1.给用户赋予操作对象的权限：
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION]; //允许分配到权限的用户继续将权限分配给其它用户


 例如：
 创建test用户，创建表test22
grant select on test22 to jd1702;
给jd1702用户赋予在test22表上进行查询的权利。


grant update(id) on test22 to jd1702;
给jd1702赋予能够更新test22表上id列的权限。
/insert
2.回收权限：同系统权限。
语法：revoke xxx on obj from user;
例如：
revoke select , update  on test22 from jd1702;




3.创建同义词： 相当于给对象起别名(权限:create synonym)，不同用户之间也不能创建同名的
语法：create[or replace][public] synonym sy_name for obje_name;
note:只有dba才有权利创建public的同义词
例如：grant  create synonym to test;//授权
test用户：create synonym testsy for test22;
select * from testsy;//可以查看
jd1702用户:select * from testsy;//无法查看


dba用户：create public synonym testpsy for test.test22;
grant select on testpsy to public;
test用户：select * from testsy;//可以查看
jd1702用户:select * from testsy;//可以查看


查看当前用户有哪些同义词： select * from user_synonyms;
4.删除同义词：
语法: drop synonym syn_name;
例如：
drop synonym testsy;
dba:drop public synonym testpsy;