--一、权限
--1.用户
--登录系统管理员

conn sys/change_on_install as sysdba
conn system/manager as sysdba
create user 用户名 IDENTIFIED by 密码 [default tablespace 表空间名]---创建用户
grant create session to  用户名;--给用户授权登录
drop user 用户名;--删除用户

--授予用户操作表空间的权限：

grant unlimited tablespace to 用户名;
grant create tablespace to 用户名;
grant alter tablespace to 用户名;
grant drop tablespace to 用户名;
grant manage tablespace to 用户名;

--授予用户操作表的权限：

grant create table to 用户名; --(包含有create index权限, alter table, drop table权限)

--授予用户操作视图的权限:

grant create view to 用户名; --(包含有alter view, drop view权限)

--授予用户操作触发器的权限：

grant create trigger to 用户名; --(包含有alter trigger, drop trigger权限)

--授予用户操作存储过程的权限：

grant create procedure to 用户名;--(包含有alter procedure, drop procedure 和function 以及 package权限)

--授予用户操作序列的权限：

grant create sequence to 用户名; --(包含有创建、修改、删除以及选择序列)

--授予用户回退段权限：

grant create rollback segment to 用户名;
grant alter rollback segment to 用户名;
grant drop rollback segment to 用户名;

--授予用户同义词权限：

grant create synonym to 用户名;(包含drop synonym权限)
grant create public synonym to 用户名;
grant drop public synonym to 用户名;

--授予用户关于用户的权限：

grant create user to 用户名;
grant alter user to 用户名;
grant become user to 用户名;
grant drop user to 用户名;

--授予用户关于角色的权限：

grant create role to 用户名;

--授予用户操作概要文件的权限

grant create profile to 用户名;
grant alter profile to 用户名;
grant drop profile to 用户名;

--允许从sys用户所拥有的数据字典表中进行选择

grant select any dictionary to 用户名;

--2.表空间

create tablespace 表间名 datafile '数据文件名'(具体为止) size 表空间大小

--3.角色

create role 角色名 identified  by 密码;--创建角色
grant 权限[,权限,..] to 角色名;--给角色授权
grant ppc_role to ppc;--把角色授权于用户

--4.一个用户给另一个用户授权

conn 用户名1/密码
grant  select on "表名" to 用户2
conn 用户名2/密码 
select * from 用户1.表名

--5.账户被锁
--登录系统管理员

conn sys/change_on_install as sysdba
conn system/manager as sysdba
alter user 用户 account unlock;

--查看权限
--查看所有用户

SELECT * FROM DBA_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;

--查看用户系统权限

SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

--查看用户对象或角色权限

SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

--查看所有角色

SELECT * FROM DBA_ROLES;

--查看用户或角色所拥有的角色

SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

--遇到no privileges on tablespace ‘tablespace ‘

alter user userquota 10M[unlimited] on tablespace;

--6.限制用户登录链接数量

--登录管理员
--查询资源限制开关是否打开
1.show parameter resource_limit;
--若是关上了就打开
alter system set resource_limit=true;
--创建限制profile数据
create profile pro名称 limit session_per_user 数量;
--修改
alter profile pro名称 limit session_per_user 数量;
--应用到具体用户
alter user 用户名 profile 上面的pro名称

--二、表数据相关操作
--1.建表
--学生表

create table stu(
  --sno varchar2(50) unique,默认无名唯一约束
  --sno varchar2(50),constraint stu_unique unique(sno),自定义约束名字
  --sno varchar2(50) primary key,--默认主键名
  sno varchar2(50) not null,
  constraint pk_stu primary  key(sno),--自定义主键名 
  sname varchar2(20) not null,
  sage varchar2(2) check(sage>0),--check()约束 必须是布尔表达式 值可以为null
  ssex varchar2(2) not null,--不为空约束
  sdept varchar(20)    ,
  --foreign key(tno) references teac(tno) 无外键名
  constraint fk_stu_teac foreign key (tno) references teac(tno) --自定义外键名
  
);

--老师表

create table teac(
  tno varchar2(50) primary key,
  tname varchar2(20) not null,
  tage varchar2(2),
  tsex varchar2(2) not null,
  tdept varchar(20)    
);
alter table stu modify sno primary key / unique;--后添加主键、唯一约束
alter table stu add constraint stu_pk primary key(sno[,...])/unique(sno[,...]);--后添加主键也可以是联合主键、唯一约束
alter table stu add constraint fk_stu_teac foreign key(tno) references teac(tno);--后添加外键
alter table stu drop constraint 主键名、唯一约束名、外键;--删除主键名、唯一约束名、外键

-- 启用/禁用约束

alter table t enable/disable constraint t_id_unique;

--(1) NOT NULI约束
--NOT NULI即非空约束,主要用于防止NULL值进入到指定的列。这些类型的约束是在单列基础上定义的。在默认情况下, Oracle允许在任何列中有NULL值。NOTNULL约束具有如下特点·定义了 NOT NULI约束的列中不能包含 NULI值或无值。在默认情况下Oracle允许在任何列中有NULL值或无值。如果某个列上定义了 NOT NULL约束,则插入数据时就必须为该列提供数据只能在单个列上定义 NOT NULL约束。在同一个表中可以在多个列上分别定义 NOT NULI约束
--(2) UNIQUE约束
--UNIQUE即唯一约束,该约束用于保证在该表中指定的各列的组合中没有重复的其主要特点如下:定义了 UNIQUE约束的列中不能包含重复值,但如果在一个列上仅定义了UNIQUE约束,而没有定义 NOT NULL约束,则该列可以包含多个NULL值或无值可以为单个列定义 UNIQUE约束,也可以为多个列的组合定义 UNIQUE约束。因此, UNIQUE约束既可以在列级定义,也可以在表级定义。Oracle会自动为具有 UNIQUE约束的列建立一个唯一索引( unique index)。如果这个列已经具有唯一或非唯一索引, Oracle将使用已有的索引。对同一个列,可以同时定义 UNIQUE约束和 NOT NULL约束在定义 UNIQUE约束时可以为它的索引指定存储位置和存储参数
--(3) CHECK约束
--CHECK约束即检查约束,其用于检查在约束中指定的条件是否得到了满足CHECK约束具有如下特点定义了 CHECK约束的列必须满足约束表达式中指定的条件但允许为NULL在约束表达式中必须引用表中的单个列或多个列,并且约束表达式的计算结果必须是一个布尔值。在约束表达式中不能包含子查询在约束表达式中不能包含 SYSDATE、UID、USER、 USERENV等内置的soL函数,也不能包含 ROWID、 ROWNUM等伪列CHECK约束既可以在列级定义,也可以在表级定义对同一个列,可以定义多个 CHECK约束,也可以同时定义 CHECK和NOTNULL约束。
--(4) PRIMARY KEY约束
--PRIMARY。KEY约束即主键约束,其用来唯一地标识出表的每一行,并且防止出现NULL值。一个表只能有一个主键约束。 PRIMARY KEY约束具有如下特点:定义了 PRIMARY KEY约束的列(或列组合)不能包含重复值,并且不能包含NULL值。Oracle会自动为具有 PRIMARY KEY约束的列(或列组合)建立一个唯一索引( unique index)和一个 NOT NULL约束同一个表中只能够定义一个 PRIMARY KEY约束的列(或列组合)可以在一个列上定义 PRIMARY KEY约束,也可以在多个列的组合上定义PRIMARY KEY约束。因此, PRIMARY KEY约束既可以在列级定义,也可以在表级定义。
--(5) FOREIGN KEY约束
--FOREIGN KEY约束即外键约束,通过使用外键,保证表与表之间的参照完整性。参照表上定义的外键需要参照主表的主键。该约束具有如下特点:定义了 FOREIGN KEY约束的列中只能包含相应的在其他表中引用的列的值,或为NULL。·定义了 FOREIGN KEY约束的外键列和相应的引用列可以存在于同一个表中,这种情况称为“自引用”。对同一个列,可以同时定义 FOREIGN KEY约束和 NOT NULL约束。FOREIGN KEY约束必须参照一个 PRIMARY KEY约束或 UNIQUE约束。可以在单个列上定义 FOREIGN KEY约束,也可以在多个列的组合上定义FOREIGN KEY约束。因此, FOREIGN KEY约束既可以在列级定义,也可以在表级定义

--2.修改表名；

 rename 原表名 to 新表名;

--3.添加列、删除列

alter table 表名 add 字段名 类型[,字段名 类型];
alter table 表名 drop column 列名;

--当一个表里数据比较多时，删除一列可能浪费的时间、资源较多，影响线上产品的操作，可以把要删除的列设置成unused，不影响添加同名新列

alter table 表名 set unused column 字段名;

--可以在这三个view查询哪个表有几个unused字段

select * from user_unused_col_tabs;
select * from all_unused_col_tabs;
select * from dba_unused_col_tabs;--需登录管理员权限

--4.修改列的数据类型、修改列名

alter table  表名 modify 列名 类型;
alter table 表名 rename column 原列名 to 新列名;

--5.创建索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用
--重命名索引
alter index 索引名 rename to 新索引名;

--合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index 索引名 coalesce;
--重建索引
--方式一：删除原来的索引，重新建立索引
--方式二：
alter index 索引名 rebuild;
--删除索引
drop index 索引名;
--查看表所有索引
select index_name,index_type,tablespace_name, uniqueness,table_name from all_indexes where table_name ='表名（大写）';

--索引建立原则总结
--　　1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
--　　2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
--　　3. 小表不要简历索引
--　　4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引
--　　5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引
--　　6. 经常进行连接查询的列应该创建索引
--　　7. 使用create index时要将最常查询的列放在最前面
--　　8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
--　　9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）
--      10.对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

--以下几种不走索引
--1.不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）

--2.通配符在搜索词首出现时，oracle不能使用索引

select * from student where name like '%wish%';--不走索引
select * from student where name like 'wish%';--走索引

--3.索引上使用空值比较将停止使用索引

select * from student where score is not null;

--4.索引列上不要使用函数,

SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC' 

--5.索引列上不能进行计算

SELECT Col FROM tbl WHERE col / 10 > 10 --则会使索引失效，应该改成
SELECT Col FROM tbl WHERE col > 10 * 10

--6.索引列上不要使用NOT （ != 、 <> ）如:

SELECT Col FROM tbl WHERE col ! = 10 

--应该 改成：

SELECT Col FROM tbl WHERE col > 10 
union
SELECT Col FROM tbl WHERE col < 10

--7.少使用OR,用UNION替换OR(适用于索引列)
--8.in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外IN时不对NULL进行处理。in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
--9.not in 和not exists：如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

索引的类型

--（1）       索引唯一扫描(index unique scan) 返回单条数据 如果是组合索引必须包含组合键第一字段否则不走索引
--（2）       索引范围扫描(index range scan) (a) 在唯一索引列上使用了range操作符(> < <> >= <= between)。(b) 在组合索引上，只使用部分列进行查询，导致查询出多行。(c) 对非唯一索引列上进行的任何查询。
--（3）       索引全扫描(index full scan) 与全表扫描对应，全Oracle索引扫描，查询出的数据都必须从索引中可以直接得到
--（4）       索引快速扫描(index fast full scan) 与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。
--（5）     索引跳跃扫描（INDEX SKIP SCAN）Index skip scan 仅是在组合索引的引导列，即第一列没有指定，并且非引导列指定的情况下。

--注：执行计划中会出现TABLE ACCESS BY INDEX ROWID索引中保存了字段值和该值对应的rowid，我们根据索引进行查找，索引范围扫面后，就会返回该区域内的rowid，然后根据rowid去查找区域内的数据

--优缺点:
--　1、索引主要进行提高数据的查询速度。 当进行DML时，会更新索引。因此索引越多，则DML越慢，其需要维护索引。 因此在创建索引及DML需要权衡。

--查看sql执行计划

select * from V_$SQL t where t.SQL_fullTEXT LIKE '%sql片段%';
select * from table(dbms_xplan.display_cursor('上述查询中对用的SQL_ID'));

--多表连接的三种方式

参考：多表连接的三种方式--hash join、merge join、 nested loop

--索引执行顺序

1	SQL_ID  72frmutb0d3h6, child number 0
2	-------------------------------------
3	select s.sname,s.sage,s.ssex,t.tname from stu s,teac t where 
4	s.tno=t.tno and s.sno < '018'
5	 
6	Plan hash value: 1261768707
7	 
8	----------------------------------------------------------------------------------------------
9	| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
10	----------------------------------------------------------------------------------------------
11	|   0 | SELECT STATEMENT              |              |       |       |     5 (100)|          |
12	|   1 |  NESTED LOOPS                 |              |       |       |            |          |
13	|   2 |   NESTED LOOPS                |              |     2 |    54 |     5   (0)| 00:00:01 |
14	|   3 |    TABLE ACCESS BY INDEX ROWID| STU          |     2 |    38 |     3   (0)| 00:00:01 |
15	|*  4 |     INDEX RANGE SCAN          | SYS_C0011781 |     2 |       |     2   (0)| 00:00:01 |
16	|*  5 |    INDEX UNIQUE SCAN          | SYS_C0011734 |     1 |       |     0   (0)|          |
17	|   6 |   TABLE ACCESS BY INDEX ROWID | TEAC         |     1 |     8 |     1   (0)| 00:00:01 |
18	----------------------------------------------------------------------------------------------
19	 
20	Predicate Information (identified by operation id):
21	---------------------------------------------------
22	 
23	   4 - access("S"."SNO"<'018')
24	   5 - access("S"."TNO"="T"."TNO")
25	

索引执行顺序：先里后外、自上而下
先执行4->3->5->2->6->1->0

假设出现如下执行计划：

0 | SELECT STATEMENT              
1 |  NESTED LOOPS                 
2 |   NESTED LOOPS                
3 |    TABLE ACCESS BY INDEX ROWID
4 |     INDEX RANGE SCAN          
5 |    INDEX UNIQUE SCAN
6 |     INDEX RANGE SCAN          
7 |    	   INDEX UNIQUE SCAN
8 |	INDEX RANGE SCAN             
9 |    TABLE ACCESS BY INDEX ROWID

执行顺序为：
4->3->7->6->8->5->9->2->1->0

 

未完待续 . . .

参考文献：https://www.cnblogs.com/liangyihui/p/5886619.html