Oracle知识点整理

1，sqlplus 常用命令

连接命令 conn

用法: conn 用户名/密码@网络服务名 [as sysdba/sysoper]
当用特权用户身份连接时，必须带上 as sysdba 或是 as sysoper []表示可以写也可以不写

断开命令 disc

show user 显示当前用户名

edit 该命令可以编辑指定的sql脚本 案例:sql>edit d:\a.sql

显示和设置环境变量

linesize

说明:设置显示行的宽度,默认是80个字符
sql>show linesize
sql>set linesize 90

pagesize

用法和linesize一样

2 创建用户

简洁版：create user 用户名 identified by  密码

细节版： create user 用户名 identified by 密码

               default tablespace ***

               temporary tablespace ***

               quota 3m on users;

identified by 表明该用户将用数据库方式验证 default tablespace users //用户的表空间在users上
temporary tablespace temp //用户的临时表健在temp 空间
quota 3m on users //表明用户建立的数据对象(表,索引,视图,pl/sql块..)最大只能是3m
刚刚创建的用户是没有任何权限的,因此,需要dba给该用户授权.

3 用户管理
概述：创建的新用户是没有任何权限的，甚至连登录的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令 revoke。

grant connect,resource to user; revoke connect, resource from user;

4 使用profile管理用户口令

概述：profile是口令限制,资源限制的命令集合,当建立数据时,oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户。

(1)帐户锁定
概述: 指定该帐户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令
例子:指定scott这个用户最多只能尝试3次登陆,锁定时间为2天，让我们看看怎么实现。

sql> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
sql>alter user scott profile lock_account;

(2)给帐户(用户)解锁
sql> alter user scott account unlock;

2 管理权限和角色

权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种:

系统权限：系统权限是指执行特定类型sql命令的权利.它用于控制用户可以执行的一个或是一组数据库操作.比如当用户具有create table权限时，可以在其方案中建表,当用户具有create any table权限时,可以在任何方案中建表.oracle提供了100多中系统权限。

显示系统权限：

select * from system_privilege_map order by name;

授予系统权限

一般情况，授予系统权限是有dba完成的，如果用其它用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。

！！！系统权限的回收不是级联回收

对象权限介绍

指访问其它方案对象的权利,用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必须具有对象的权限. 比如smith用户要访问scott.emp表(scott:方案,emp :表)
则必须在scott.emp表上具有对象的权限。常用的有：
alter 修改    delete    删除    select    查询      insert 添加
update 修改    index    索引    references 引用 execute 执行

在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的(with grant option )权限,从oracle9i开始,dba,sys,system 可以将任何对象上的对象权限授予其它用户.授予对象权限是用grant命令来完成的.

语法： grant 对象权限 on 表名[(列名)] to user[角色名];

使用with grant option选项
该选项用于转授对象权限.但是该选项只能被授予用户,而不能授予角色;

！！！对象权限的回收是级联回收；

角色：角色就是相关权限的命令集合,使用角色的主要目的就是为了简化权限的管理.假定有用户1,2,3为了让他们都拥有权限
①连接数据库
②在scott.emp表上select,insert,update,,,,
如果采用直接授权操作，则需要进行12次授权。

我们如果采用角色就可以简化：
首先将create session , select on scott.emp, insert on scott.emp,update on scott.emp授予角色，然后将该角色授予a,b,c用户，这样就可以三次授权搞定.

角色分为预定义和自定义角色两类：

预定义角色是指oracle所提供的角色,每种角色都用于执行一些特定的管理任务,常用的预定义角色有connect,resource,dba

顾名思义就是自己定义的角色，根据自己的需要来定义.一般是dba来建立，如果用的别的用户来建立，则需要具有create role的系统权限.在建立角色时可以指定验证方式(不验证，数据库验证等)

㈠建立角色(不验证)
如果角色是公用的角色，可以采用不验证的方式建立角色.
create role 角色名 not identified;

㈡建立角色(数据库验证)
采用这样的方式时,角色名、口令存放在数据库中。当激活该角色时，必须提供口令.在建立这种角色时，需要为其提供口令
create role 角色名 identified by 123456；

显示角色信息
①显示所有角色
sql>select * from dba_roles;
②显示角色具有的系统权限
sql>select privilege,admin_option from role_sys_privs where role=‘角色名';
③显示角色具有的对象权限
通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列的权限。
④显示用户具有的角色，及默认角色
当以用户的身份连接到数据库时，oracle会自动的激活默认的角色,通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认的角色 sql>select granted_role,default_role from dba_role_privs where grantee=‘角色名';

3,表的管理

可以使用RENAME语句对表进行重命名

rename table_oldname to table_newname;

查看表结构

desc tablename;

给表或列添加备注，可以帮助用户理解表或列的作用。

comment on table table_name  is  '   '; //给表添加备注

comment on column table_name.column_name is  '    ';//给列添加备注

查看表的备注

可以从user_tab_comments视图中查看表的备注信息

select * from user_tab_comments;

查看列的备注

可以从user_col_comments视图中查看列的备注信息
select * from user_col_commects where table_name = '   ';

从已有数据创建表

create table tablename as select * from tablename;

只复制表结构

create table tablename as select * from tablename where 1=2;

给表添加一列

alter table tablename add(columnname int(1) not null);

修改列的大小

alter table tablename modify(columnname int(1) not null);

修改列名

alter table tablename rename column oldcolumn to newcolumn;

删除列

alter table tablename drop(columnname);

向表中插入数据

insert into tablename (columnname) values();

蠕虫复制

insert into tablename select * from tablename //同一张表 快速复制自身表的大量数据

更新表

update table set columnname= ' ',columnname = ' ' where ...;

删除表数据

delete from table name where ..; truncate慎用！！！删除只有不能恢复

4 表的约束

数据完整性
主键、非空、默认、Check 、外键
表关系与主外键约束及关联查询
一对一
一对多
多对多
添加、删除约束
启用、禁用约束

实体完整性   主要是对表的行进行约束
域完整性      主要是对表的列进行约束
引用完整性   主要是对整张表进行约束
自定义完整性   用户自定义的一些约束通常使用存储过程、触发器完成

5 数据库对象

序列、索引、视图、同义词

序列是一个数据库对象，用于生成一系列的整数。

创建序列的语法：

--创建一个最简单的序列
create sequence s1;

--创建一个详细的序列

create or replace sequence

start with ***, increment by * , maxvalue,minvalue, cycle/nocycle,cache/nocache是否开启缓存，

order：保证整数的请求顺序是按照生成顺序得到的。
 

--查看创建的序列
SELECT * FROM user_sequences;

--获取下一个值
select s1.nextval from dual;
--通过currval获取当前值
select s1.currval from dual;
--修改序列每次增加2
alter sequence s1 increment by 2;
--修改序列每次最大值为20
alter sequence s1 maxvalue 20;
--修改序列为cycle,且设置为不缓存
alter sequence s1 cycle nocache;

desc t2;
--在表中使用序列
insert into t2 values(s1.nextval,33);
select * from t2;

--删除序列
drop sequence s1;

索引：索引是与表关联的可选结构。可以创建索引以加快对表执行SQL语句的速度。就像书的索引可以帮助我们更快速的查找信息一样，Oracle中的索引也提供了一种更快地访问表数据的途径。

应该建索引列的特点：
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该建索引列的特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。
第二，对于那些只有很少数据值的列也不应该增加索引。
第三，对于那些定义为blob数据类型的列不应该增加索引。
建立一个简单的索引：

create or replace index on tablename(columnname);

视图： 视图就是一个select查询结果为了方便使用而把这个select查询结果作为了视图

要创建视图，必须给用户授予创建视图CREATE VIEW的权利。可以使用SYS给用户scott授予该权利。

--创建视图
create or replace view v1 as select * from p3;
--查询视图
select * from v1;
--创建视图
create view v2 as
  select p.name as pname,c.name as cname from
  person p inner join car c on p.id=c.pid;
--查询
select * from v2;

如果想DML语句在视图上进行安全的操作，那么可以在查询上设置CHECK OPTION。

--width check option
desc t2;
create or replace view v3
  as select * from t2 where age<10
  with check option;
select * from v3;
--因为设置了with check option所以age>=10时将不能写入
insert into v3 values(33,22);

创建视图的时候使用READ ONLY，那么该将为只读视图

create view emp_dept_view3 AS
select*
from dept
where deptno < 15
with read only;

同义词：方便访问其它用户的对象
              缩短对象名字的长度 //感觉同义词的意义不是太大

语法： create sysnonym newname for oldname;

--创建同义词
create synonym ty for scott.emp;
--使用同义词
select * from ty;