Orcale数据库（详细知识点总结）

一、表空间

  表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。 所有的数据库对象都存放在指定的表空间中。 但主要存放的是表， 所以称作表空间。 Oracle数据库 中至少存在一个表空间，即SYSTEM的表空间。

1、查看表空间

查看数据库默认创建的表空间

select tablespace_name,status,contents from dba_tablespaces;

status–表空间在线与否，是否能使用
contents–表空间状态
dba_tablespaces–管理员可操作的表空间，为所有表空间信息
dba_users–为所有的数据库用户信息

2、创建表空间

创建永久表空间

create tablespace lwq
	datafile'd:\sql.dbf'--保存路径
	size 100m--初始大小
	autoextend on --自动增加
	next 20m --每次增加20m
	maxsize 1025m--最大1g

创建临时表空间

create temporary tablespace temp_lwq
	tempfile'd:\sql.dbf'--保存路径
	size 100m--初始大小

3、删除表空间

drop tablespace tablespace_name

4、重命名表空间

alter tablespace_name rename to tablespace_name(new) 

5、将表放到某一个表空间中

alter table table_name move tablespace tablespace_name

二、约束

  表级约束：与列定义相互独立,不包含在列定义中;通常用于对多个列一起进行约束，与定义用,'分隔;必须指出要约束的列的名称
列级约束：包含在列定义中;直接跟在该列的其他定义之后,用空格分隔;不必指定列名

1、在定义字段的同时添加约束

create table lqq(id int primary key,name varchar(20) not null,price int unique,
sex varchar(20) default'男' check (sex='男' or sex='女'));

2、在定义完约束后添加约束

create table lqq(id int,name varchar(20),price int,sex varchar(20));

alter table lqq add primary key(id);
alter table lqq modify name varchar(20) not null;
alter table lqq add constraint u_price unique(price);
alter table lqq modify sex varchar(20) default '男';
alter table lqq add constraint c_sex check (sex in('男','女'));

3、删除约束

alter table table_name drop constraint 约束名

三、表操作

增删改查
增加一列

alter table table_name add dno number

将表添加到表空间

alter table table_name move tablespace tablespace_name

重命名表明

alter table table_name rename to table_name(new) 

插入date类型

insert into lwq(birthday) values(to_date('2001-10-01','yyyy-mm-dd'))	

四、序列

  序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

1、创建序列

create sequence sequence_name
start with 10--序列起始值
increment by 1--序列增量
minvalue5--最小值
maxvalue100--最大值
cycle--循环生成序列号，当循环递增到最大值后，重新从最小值生成序列号
cache 10 --指定在内存中预存储的序列号个数，默认20最小2

2、删除序列

drop sequence sequence_name

3、查看序列的值

select sequence_name.nextval from dual

五、视图

  视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

1、创建视图

create [or replace] [force] view view_name as select*from emp [with read only|with check option]  

or replace–在创建视图时，如果存在同名视图，则替换为现有视图
force–即使基表不存在或者没有权限也要强行创建视图
with red only–只能读取表，不能通过视图进行dml操作
with check option–只能进行dml操作

2、重命名视图

rename 旧视图 to 新视图

3、删除视图

drop view view_name

六、索引

  索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。

创建索引

create index index_name on table_name (column_name)

1、创建单列索引

create index i_emp on emp (empno)

2、创建基于函数的索引

create index i_emp_e on emp(lower(ename))

3、创建复合索引

create index i_emp_j emp(job,ename)

4、删除索引

drop index index_name

七、同义词

  同义词分为公有同义词和私有同义词，公有同义词被用户组public拥有，数据库所有的用户都可以使用公有同义词。私有同义词只能被创建它的用户所拥有，该用户可以控制其他用户是否有权使用该同义词。

1、创建私有同义词 具有create synonym的用户

create synonym 方案名 for 同义名

2、创建公有同义词 具有create public synonym的用户

create public synonym 方案名 for 同义名

3、删除同义词

drop synonym 同义名

如果删除公有同义词还需要指定public关键词

八、权限

1、创建角色

create role 角色名 identified by 密码

2、授予与角色系统权限

grant 系统权限 to 角色名

3、设置角色

grant 角色名 to 用户名

4、修改角色

alter role 角色名 identified by 密码

5、删除角色

drop role 角色名

6、授予用户系统权限

grant 系统权限名 to 用户名 identified by 密码 [with admin option]

with admin option 表示当前授权的用户还可以给其他用户进行系统权限的授予
7、撤销权限

revoke 系统权限名 from 用户名/角色名

8、锁定用户

alter user user_name account lock

create session to
select on c##scott.emp to
insert,delete,update on c##scott.emp to
all on c##scott.emp to

九、事务处理

提交事务 ：commit
回退事务：rollback
设置保存点：savepoint
回退到保存点：rollback to
设置自动提交：set autocommint on/off

(1)删除dept1中部门编号为10的记录，并提交事务

delete from dept1 where deptno=10;
commit;
select *from dept;

(2)将dept1表中部门编号为20的记录地址更改为BEIJING。然后回滚该事务以取消对dept1表的修改。

update dept1 set loc='BEIJING' where deptno=20;
rollback;

(3).向商品表中插入一条记录，设置保存点a,再插入一条记录，然后回滚到保存点a,

savepoint a;
insert into dept2(deptno) values(5);
rollback to a;

十、游标

  游标是SQL的一种数据访问机制。可以将游标简单的看成是查询的结果集的一个指针，可以根据需要在结果集上面来回滚动，浏览需要的数据。

1. 声明游标:cursor 游标名is select 语句;
2. 打开游标:声明的游标必须打开后才能使用 open 游标名称; 相当于执行查询语句
3. 使用游标:一般在循环语句中使用fetch语句提取游标中的记录来进行操作 fetch c_emp into v_ename,v_job; exit when c_emp%notfound; for src in c_emp; loop
   dbms_output.put_line(src.empno); end loop;
4. 关闭游标:游标使用完毕后，一定要关闭 close游标名称;

十一、过程

（一）语法

create or replace procedure name [parameter,…]
is｜as—两者都可，选其一
变量，常量声明;
begin
		sql语句和PL/SQL语句
[exception]
	异常处理语句
end;

（二）无参数的存储过程的调用
常用两种方法：

1. 匿名块调用
	Begin
		过程名();
	End;
2. exec命令	exec 过程名(); --该命令适合在命令窗口完成

十二、函数

（一）语法

create or replace function function_name[(argment in|out|in out type)]
return return_type
is|as
[declare_section]
begin
  function_body
	return 变量
exception
…

end;
（二）调用
通常使用两种方法：

1. 在sql语句中调用：
   select get_ename(7788) from dual; --get_ename为函数名
2. 使用输出语句调用函数，注意：输出语句必须在块的执行体中，不能单独执行。
   set serveroutput on
   begin
   dbms_output.put_line(‘员工姓名是：’||get_eanme(7788));
   end;

十三、触发器

1. 触发事件：DML操作、DDL操作和系统事件等
2. 触发时间：before after
3. 触发对象：表、视图、用户和数据库
4. 触发级别：
   语句级触发（表级触发）：无论操作影响了多少行，语句触发器只被调用一次
   行级触发器：操作影响到的每一行，都会执行触发器。
5. 触发限制：一个逻辑表达式，当该表达式为真时，触发器被触发执行

create or replace trigger <触发器名>
[before|after]
<触发事件> on <表名>
[for each row]
[when <条件表达式>]
<PL/SQL程序体>

知识点：三个谓词：instering deleting updating

create or replace	
trigger emptime2
before insert or delete or update on emp
declare
v_riqi varchar2(10);
v_hh number(2);
begin
  v_riqi:=to_char(sysdate,'day');
  v_hh:=to_number(to_char(sysdate,'hh24'));
  if(v_riqi in('星期六','星期日') or (v_hh>17 or v_hh<8)) THEN
    if inserting then
      raise_application_error(-20013,'非工作时间禁止插入');
    elsif deleting then
      raise_application_error(-20014,'非工作时间禁止删除');
    elsif updating then
      raise_application_error(-20015,'非工作时间禁止更新');
    else 
      raise_application_error(-20016,'非工作时间禁止任何操作');
    end if;
  end if;
end; 

知识点：两个伪记录变量 :old :new

这两个变量只在触发器内部有效，并且只有在DML触发表中字段时才有效。使用形式：:old.字段名和:new.字段名

:old和 :new 表示的是同一条记录。两个都是记录变量
:old 是这条记录被操作之前；
:new 是这条记录被操作之后。

create or replace trigger checksal
before update
on emp
for each row
begin
  if :old.sal>:new.sal then
    dbms_output.put_line('涨前：'||:old.sal||'，涨后：'||:new.sal);
    RAISE_application_error(-20002,'涨后的工资不能小于涨前的工资');
   end if;
end;