oracle复习总结

一、1.储存模式是一种包含了诸如段。

视图。

过程。

函数程序包。

触发器。

用户自定义的对象集合类型序列同义词和数据连接对象的逻辑结构。

2.用户角色，对象权限系统权限Select，insert，update，delete可以使用的权限是execute二．SQL语言1.创建表create table 表名2.约束（1）非空约束not null（2）唯一约束unique(3)主键约束primary key 最多只能有一个主键约束主键约束可以使用一列组成，不能有重复的不能为空（4）外键约束foreign key外键是指引用一个表中的某个列或某几个列，或本表中另一个列或者几个列被引用的列应该是主键列或者唯一性约束列。

约束关键词constraint 名称foreign key references引用表名引用表主键（5）检查约束check constraint名称check表达式（6）缺省约束default3.修改表（1）增加列Alter table 表名add 新列名数据类型（2）更新列Alter table 表名modify 列名数据类型（3）删除列Alter table 表名drop column 要删除的列名（4）删除表Drop table 表名2.DML1.检索所有列Select *from empSelect ename,job from emp;Select ename,to_char(hiredate,‟yyyy-mm-dd‟) from empSelect distinct deptno,job from emp取消重复行Select ename,sal*12from mep处理nullSelect ename,sal,comm.,(sal+comm)from emp连接字符串Select ename||‟is a ‟||job as”employ detail”from emp使用简单where子句Select ename,sal from emp where sal>2000Select job,sal from emp where ename=‟SCOTT‟;Select job ,sal from emp where lower (ename)=‟scott‟;Where 子句和between andSelect ename,sal ,hiredate,job from emp where sal between 1000 and 2000Where 子句中使用like 操作符Select ename,sal from emp where ename like‟S%‟;Order by 子句1,升序排列Select ename ,sal from emp where deptno=30 order by sal;2.降序排列Select ename,sal ,comm. From emp where deptno=30 order by sal desc3.使用多列排序Select ename ,sal ,commm from emp where deptno=30 order by sal asc ,comm. Desc4.数据分组Grop by1,分组函数Select max(sal),min(sal)from emp2, 取消重复值Select count (distinct deptno)as distinct_dept from mep3.group by having 子句Select column,group_function from 表名where condition group by groupby_expression having group_condition使用group by进行单列分组select deptno,avg(sal),mac(sal)from emp group by deptno使用groupby进行多列分组Select deptno,job,avg(sal),max(sal)from emp group by deptno ,job;使用having子句限制分组显示select deptno,avg(sal),max(sal)from emp group by deptno having avg(sal)<20004.连接查询1.不使用表名Select dept.dname,emp.ename from dept,emp where dept.deptno=emp.deptno2.相等连接Select table1.column,table2.column from table1,table2 where table.column=table2.column3.使用and指定其他条件Select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and d.deptno=104不等连接Select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal5自连接Select manager.ename from emp manager ,emp worker where manager .empno=worker.mgr and worker.ename=‟BLAKE‟6内连接外连接Select table1.coulmn,table2.coulmn from table1 inner left\right\\\full join table2 on table1.coulmn=table2.coulmn内连接Select d.dname,e.ename from dept d ,emp e where d.deptno=e.deptno and d.deptno=10左外连Select d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno and d.deptno=10嵌套查询Select ename,sal,deptno from emp where deptno=(select deptno from emp where ename=‟SCOTT‟)多行子查询Select ename,job,al,deptno from emp where jon in (select distinct job from emp where deptno=10) 多列子查询Select ename ,job ,sal,deptno from emp where (deptno ,job )=(select deptno ,job from emp whereename=‟SMITH‟)成对比较Select ename ,sal,comm.,deptno from emp where (sal,nvl(comm.,-1))in (select sal,nvl(comm.,-1)from emp where deptno=30)4.合并查询Union 并集走掉结果集中的重复行union all两个结果记的并集不会取消重复行intersect两个结果集的交集minus两个结果集的差集Insert（1）插入单行数insert into table column1 coulmn2 values values1 values2不使用列插入单行数据Insert into dept values(50,‟TRAIN‟‟BOSTON‟)使用列插入单行数据Insert into emp (emp,ename ,job hiredate )values(1244,‟john‟,‟clerk‟,‟01-3-86‟)使用子查询插入数据Insert into employee(empno,ename,sal,deptno)select empno,enmae,sal,deptno from emp where deptno=20使用firstSelect first when deptno =10 then into dept 10 when deptno=20then into dept 20 else into other select *from emp555 update2.3事务锁事务用于确保数据库的一致性主要由insert update delete select。

for update提交事务commit回退事务rollback三．数据库对象1，同义词Create synonym 名字for object_name2序列创建序列Create sequence 名字Start with 名字increment by 数字（初始值）maxvalue 数字cycle和nocycle使用序列Select s_test.nextval from dualSelect s_test.currval form dual使用序列填充主键Creaete table student(Id integer constraints_test primary key,Name varchar2(20);Insert into student (id,name)values (s_test.nextval,‟fendou‟)3.视图1.视点集2.简化操作3.定制数据4.合并分割数据5.安全性4.创建并使用视图Create or replace view view_name as subquery constranint constraint_nameCreate view emp_view as select empno ,ename,deptno from emp where deptno=30Insert into emp_view values(2009,‟fendou‟,30)创建具有check option 约束的视图Creaete or replace emp_view as select empno,ename,deptno from emp where deptno=30 wieh check option constraint emp_view_ck5.索引索引加快数据的一种有效方式Creaete unique index index_name on table_name column_name (column_name…) Tablespace tablespace_nameCreate index i_emp_index on emp(deptno)Create endex i_emp_edno on emp(empno,deptno)修改索引Alter index i_emp_edno rename to i_emp_noDrop index i_emp_no;临时表Create global temporary 表名列类型On commit preserve rows 会话中断时Commit时Create global temporary table temp_test2 (tempId int)On commit delete rows;四．PL SQL 编程简介1，块结构Declare名称类型值Begin执行异常处理部分End已知矩形面积和高求宽度DeclareV_width int;V_heitht int:=2;V_area int:=6;BeginSet the width eaual to the area divided by the heightV_width:=v_area/v_height;Dbms_output.put_line(“v_width=”||v_width);ExceptionWhen zero_divide thenDbms_output.put_line(…divison by zero‟);End2,变量和类型Int varchar2 number条件逻辑If then else elseif end if循环While循环for循环简单循环LoopStatementsEnd loopWhile循环。