1、表管理

-- DDL：data definition language -数据定义语言，DDL会自动提交，不能回滚

-- 查看用户定义的表
select * from user_tables

-- 查看用户定义的表, 视图, 同义词和序列
select * from user_catalog

-- 使用子查询创建表：如果只需要空表可以将条件置为false
create table emp1 as select * from employees where 1=2;

-- 改变表结构：alter table 
-- 增(add)、删(drop column)、改(modify)、不可用(set unused column)
alter table 	dept80 modify	(salary number(9,2) default 1000);

-- 清空表：与delete的区别在于，truncate不能回滚

-- 重命名
-- 表
rename employees2 to emp5
-- 列
alter table dept80  rename column salary to salaryB

-- 临时表
-- 事务级临时表，commit之后数据自动清除
create global temporary table temp2(id number) on commit delete rows;
create global temporary table temp3 as select  employees_id  from employees
-- 会话级临时表，用户退出会话后数据自动清除
create global temporary table temp4(id number) on commit preserve rows;
create global temporary table temp5 on commit preserve rows    as  select id from employees；

2、约束

-- 五大约束：not null，unique，primary key，foreign key，check，默认约束名为SYS_Cn...

-- 建表时约束
create table  emp_new(
	emp_id						varchar(50),
	emp_name 					varchar2(50),
	salary						number(10),
  department_id     number(4),
  department_name   varchar2(30) constraint dept_name_nn not null,
	constraint emp_salary_min  check (salary > 0),
  constraint dept_id_pk primary key(emp_id),
	constraint emp_dept_fk foreign key (department_id) references  departments(department_id) [on delete cascade / on delete set null ]);

-- 建表后添加和删除约束，约束是无法修改的，只能删后重新建

-- 新增not null约束需要使用modify，
alter table emp_new modify(emp_name varchar2(50) not null);
-- 新增其他约束用add constraint
alter table   employees add constraint  emp_manager_fk foreign key(manager_id) references employees(employee_id);
-- 删除约束(drop)/停用约束(disable)/激活约束(enable)
alter table emp_new drop constraint  emp_manager_fk

-- 查询约束
select	table_name, constraint_name, constraint_type, search_condition from	user_constraints
select	table_name, constraint_name, column_name from user_cons_columns

3、视图

-- 创建/修改视图，嵌套子查询，
create or replace view empview 
as 
	select employee_id emp_id,last_name name,department_name
	from employees e inner join departments d on e.department_id = d.department_id
-- with read only

-- 在视图中支持有限DML，当存在group/distinct/rownum/表达式 时不支持DML

-- Top-N 分析
-- 例子：查询工资最高的三名员工
select rownum as RANK, last_name, salary 
from  (select last_name,salary from employees
       order by salary desc)
where rownum <= 3;
-- 对 rownum 只能使用 < 或 <=, 如果需要其他操作，需要使用子查询
select * from (
	select rownum rn,employee_id,salary from (
		select employee_id,salary,last_name from employees order by salary desc ))
where rn <=50 and rn >40;

3、序列

-- 除了表、视图，oracle中的对象还有序列、索引和同义词

-- 创建序列create,maxvalue/minvalue/[no]cache/[no]cycle可选，修改序列alter
create sequence  dept_deptid_seq
increment by  10
start with  10

-- 使用序列
select dept_deptid_seq.nextval from dual
select dept_deptid_seq.currval from dual

-- 删除序列drop
drop sequence dept_deptid_seq

-- 查询序列
select	sequence_name, min_value, max_value, increment_by, last_number from user_sequences;

4、索引

-- 索引独立于表，删除索引只影响查询效率；索引由oracle自动维护和使用，通过索引快速定位数据减少磁盘IO

-- 创建索引：通过primary key和unique约束创建唯一索引，也可以通过显示创建非唯一索引
create index 	emp_last_name_idx on employees(last_name);

-- 创建索引的条件
--   列中数据值分布范围很广，列经常在 WHERE 子句或连接条件中出现，
--   表经常被访问而且数据量很大 ，访问的数据大概占数据总量不超过4%。
--   表插入、更新的频率较低	

-- 查询索引
select	ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
from	user_indexes ix, user_ind_columns ic
where	ic.index_name = ix.index_name
and	ic.table_name = 'EMPLOYEES';

-- 同义词
create synonym  e FOR employees;

5、权限

-- 创建用户
create user  scott identified by   tiger;

-- 用户权限
-- 应用程序开发者
grant  create session, create table,   create sequence, create view, create procedure to   scott;

-- 创建表空间
alter user scott quota unlimited  ON users

-- 创建角色
create role development;

-- 角色授权
grant  create session, create table,   create sequence, create view, create procedure to   development;

-- 用户添加角色
grant development to scott;

-- 修改密码
alter user scott identified by  tiger;

-- 对象的拥有者拥有所有权限并可以对外分配权限
--   查询权限
grant  select on    employees to sue, rich;
--   某字段更新权限
grant update on scott.departments to david;
--   授权权限
grant   select, insert on   departments to  scott  with grant option;
--   授权所有用户
grant  select on   employees to public;

-- 回收权限
revoke  select, insert ON   departments from    scott;

Q.E.D.