oracle总结

DML（数据操作语言）语句 （insert,update,delete,merge）
DDL（数据定义语言）语句 (create,alter,drop,truncate)
DCL（数据控制语言）语句 (grant,revoke)
事物控制语句 (commit,rollback,savepoint)

以emp表操作为例

select语句
基本执行顺序

select ____ from emp where ____ group by ____ having ____ order by____;

insert语句

insert into student ('name','number') values ('zhulin',12345);

update语句

update student set name = 'zhangming' where number = 12345;

delete语句

delete from student where name = 'zz';

分组函数
count,avg,sum,max,min

两个表的连接
db1 join db2 on ____

数据字典
查询某用户下面所有的表

select table_name from all_tables where owner='SCOTT';

查询EMP表中所有字段

select * from all_table_columns where table_name='EMP';

列出表中的约束

select * from all_constraints where table_name='EMP';

列出表中的约束和字段名

select constraint_name,column_name from user_cons_columns where table_name = 'EMP';

修改字段

alter table student modify age number(10);

删除字段

alter table student drop column age;

清空表中的数据

truncate table student;

删除表

drop table student;

重命名表

rename student to student1;

约束
非空，主键（不能重复，不能为空），unique（不能重复，null除外），check（插入的数据必须满足某些条件），外键
在外键后面添加 on delete cascade进行级联删除
添加约束

alter table person add constraint person_pid_pk primary key(pid);

删除约束

alter table person drop constraint person_pid_pk;

启用\禁用约束
enable\disable

创建视图

create or replace view dept as (select ename,sal,deptno,empno from emp);

索引
优点：
1.提升查询效率
2.创建唯一性索引，保证每行数据的唯一性
3.加速表与表之间的连接
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
缺点：
1.索引需要占据物理空间
2.当对表进行增加删除修改的时候需要额外维护索引
创建索引的原则：
1.表所占的内存较大
2.在select,where语句中使用的较多的列上创建
创建索引

create index abc on student(sid,sname);

删除索引

drop index abc;

序列
创建序列

create sequence myseq;

获取序列值

select myseq.nextval from dual;

每选择一次，值自增1
删除序列

drop sequence myseq;

PL SQL
set serveroutput on;在屏幕上显示输出

declare
  v_str varchar2(20) := 'hello world';
begin
  dbms_output.put_line(v_str);
exception
  dbms_output.put_line('error');
end;

循环loop
do-while循环
loop
exit when
end loop;
while循环
while () loop
end loop;
for循环

declare
  v_c number := 10;
begin
  for v_i in 1..v_c loop
    dbms_output.put_line(v_i);
    exit when (v_i = 8);
  end loop;
end;

游标，函数
游标

declare 
  cursor c is select * from emp;
begin
  for v_temp in c loop
  end loop;
end;

函数

create or replace function myfun(v_eno,emp.empno%type) return number is
  v_no number := 3;
begin
  v_no := v_no + v_eno;
  return v_no;
end;

测试

select myfun(3) from dual;

存储过程

create or replace procedure p is
  v_no number := 3;
  begin
    dbms_output.put_line(v_no);
  end;

测试

exec p;

带参数的procedure

create or replace procedure p(v_a in number,v_b in number,v_ret out number) is
  begin
    v_ret := v_a + v_b;
  end;

测试

declare
  v_ret number;
begin
  p(3,5,v_ret);
  dbms_output.put_line(v_ret);
end;

产看存储过程

select text from user_source where name='P';

触发器
创建触发器

create table emp2_log (auser varchar2(20),action varchar2(20),adate date);

create or replace trigger trig
  after insert or update or delete on emp2;
  begin
    if inserting then
      insert into emp2_log values (USER,'insert',sysdate);
    end if;
    if deleting then
      insert into emp2_log values (USER,'insert',sysdate);
    end if;
    if updating then
      insert into emp2_log values (USER,'insert',sysdate);
    end if;
  end;

测试

update emp2 set deptno = 11 where deptno = 10;
select * from emp2;

用户管理
以管理员身份登陆

conn sys/12345 as sysdba;

创建用户

create user test identified by 123456;

授权

grant create session,resource to test;

删除用户

drop user test;

如果该用户创建了表，则必须使用级联删除

drop user test cascade;

—————————————–2017-4-30——————————————–
集合操作 （执行顺序：从左至右）
union all （并集，重复）
union （并集，不可重复）
minus （差集）
intersect （交集）

varchar和varchar2的区别：varchar定长，最大2000，varchar2不定长，最大4000，oracle版本前后兼容

floor,ceil,round,trunc区别
floor取左边最近整数，ceil取右边最近整数
round作四舍五入，trunc不作舍入
select trunc(98.27) from dual 98
select trunc(98.27,1) from dual 98.2
select round(98.87) from dual 99
select round(98.87,1) from dual 98.9

oralce层次化查询
表t2

   ROOT_ID         ID NAME
---------- ---------- ----------
         0          1 a
         1          2 a1
         1          3 a2
         0          4 b
         4          5 b1
         4          6 b2

选取a的所有子节点

select * from tt start with id = 1 connect by prior id = root_id;

   ROOT_ID         ID NAME
---------- ---------- ----------
         0          1 a
         1          2 a1
         1          3 a2

选取b1节点的父节点

select * from tt start with id = 5 connect by prior root_id = id;

   ROOT_ID         ID NAME
---------- ---------- ----------
         4          5 b1
         0          4 b

总结：选取a的所有子节点，这个出发点是a，所以start with id = 1，以a为出发点，所以connect by prior id = root_id；选取b1的父节点，这个出发点是b1，所以start with id = 5，以b1为出发点，所以connect by prior root_id = id。

三范式
1.字段设计不可再分
2.两个表的关系在第三张表中体现
3.多张表中只存在关系，不存具体信息