oracle第三天

子查询

seselect deptno avg(sal) from t1 group by deptno having avg(sal)=select min(avg(sal))from t1 group by deptno;

select name from t1 where salary > any (select salary from t1 where name=’tom’);

==select name from t1 where salary < select min(salary)from t1 ;

插入子查询。

insert into t1 seltct xm,xb,id from t2;

insert into t1(xm,xb,id) select xm,xb,id from t2;

create t1 (xm varchar(20),xb varchar(20),id varchar2(20) as select xm,xb,id from t2;这也是一种好的方法。

create tale t1 as select xm,xb,dz from t2;经常用于做测试的场合。

create table t1 as select xm name,xb,dz from t2;这时给t1表的第一列用了一个新的列,列名师别名name。表达式必须给别名！！t1中有t2中没有的东西，必须给别名，经过计算的列，或者其他形式得到的给alias。

根据一个表创建另一个表

create table t as select rownum id ,xm name,xb,dz form t2 where 0=1;这样就创建了一个表结构，数据为空。然后

insert into t1 select rownum id ,xm name,xb,dz from t2;这样就分两步来进行。从一个表到另一个表这种操作成为：CTAS。create table as

insertinto (select xm,xb,dz from t1 where xb=’女’ with check option )values (‘tom’,’男’,’beijing’);

加不加 with check option 有很大的区别 不加的话可以添加进去。

oracle 将所有的子查询都看做是视图。

更新与子查询

update t1 set sal=select avg(sal) from t1;

这里只能返回一个固定值。

update (select * from t1 where age <51) set sal=sal+100;没有with check option 的操作时没有用的 因为它属于视图的范围。

表的改名：alter tale t3 rename to t44;

delete 和truncate：delete会把所有的数据块读到内存，然后将各个值设置为没有，或者说删除，而truncate则直接清空。将他所占用的块清掉。

还有一种形式；

如果不是any而是or就将min换为max

如果oracle发现了更高的效率，则oracle不一定会执行你的sql他会从执行计划里选择更好的效率更高的sql。但大多数情况下是不会的，而是极少数情况下才会发生。

in只做等于的判断或者的意思。

还有not in

sqlplus环境变量的设置

oci oracle call interface oracle调用接口 &aaa,sqlplus会停下来，让你输入语句。输入aaa的数字。如select &a +&b from dual;会提示你输入Enter value for a:22,Enter value for b:44;

格式控制命令

select name ,sal form t1;col 列名for [a]格式控制符.

col name for a10;name列宽度10个字节，当宽度过窄的时候，会自动拐行，换行。字符用a来表示。

对数字的话可以直接用格式 col sal for 9,999,999.99同时也可以添加￥等符号。如果过短的话呢，将会显示为############。想恢复正常的话呢，可以用格式9999999999999.多个9

set 但数据过多的时候oracle会自动分页 想显示前多少行可以用rownum<20.

set pagesize 10000设置页大小。会再出现中间的分页。

set linesize 1000设置行大小。oracle 会自动换行

DML： merge，insert,update ,delete。

default用在表创建的时候default ‘无’

create table ( name varchar2(10) default ‘无’,id varchar2(10));

merge 、

merge into t2 using t1 on (t1.id=t2.id) when matched then update se t2.name=t1.name,t2,id=t1.id, when not matched then insert values (t1.name,t1,id);

事务：transaction 事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。 　　原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。 　　一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 　　隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 　　持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

操作处理的数据被放在了回滚段。

回滚和保留点。savepoint。

savepoint s1;的命令在你每次的操作之后。

savepoing s2;

这个时候可以回滚到s1,s2也可以用rollback 全部回滚。

rollback to s1;

语句级回滚

针对某些错误，或者不想要的语句的。

DCL database control l language.

commit

DDL/DCL

commit

在DDL之前的操作会别默认地commit，所以前面如果有操作，如update的话呢会是回滚不了的，因为已经提交了。

客户端的异常退出和正常退出也自动commit或者rollback。

数字字段的长度可以缺省。字符型字段不可以。

锁

锁是有级别的，锁有行锁和表锁。主要用的是行锁。delete 与drop的不同。

死锁：oracle会马上发现死锁。deadlock 他会马上回滚等待时间较长的语句。ORA-00060：deadlock detected while waiting for resource。此时会产生很多的dump文件，可以自己查看dump。

show parameter dump;

backgourd_core_dump。在这个文件里面。cd/u10/admin/prod/bdump

vi alert_orcl

更多的信息放在了.trc文件中了（trace 文件）。

用more 命令来查看。里面会有语句以及其他 的一些信息。里面很清楚

data type

char is more fast ,fill the word or string with space;

timestamp is some thing like time which is longer than date.

more accurate.不受nls_date_format的控制，有自己的格式。不会变动的。

带时区的时间戳。with time zone/ with local time zone.时区信息不会显示在你的文字上，而是将时间的大小改变。with local time zone会以数据库为准，然后改变时间参数。

间隔型数据之年月日间隔。

interval year

year to month: 数据类型。

约束

约束是针对列的。在用户添加或修改列数据时，它用来对列数据限定。

not null。constraint 约束名字 not null

create table a(id number constraint a_id_nn not null);

alter table aa modify(id constraint aa_id_nn not null);

添加约束可以和修改表的操作一起，以为都用alter 和modify

约束可以没有名字，oracle会自动给约束添加名字。

alter table aa drop constraint aa_id_nn;

CHECK 条件检查约束

create table aa (id number(10) constraint aa_id_c check(id>=1,name varchar2(20));

在实际应用的过程中一般id不能为复数。

约束不跟在一个列之后，而是跟在所有的列之后。这样的约束是表约束。

约束可以有很多个，oracle不会去检查约束之间的冲突。

unique约束、

constraint aa_id_u unique.在列后。表级可以多个列 如constraint aa_id_age_u unique(id,age)放在所哟列的最后面。

primary foreign key

alter table dept add(constraint dept_dept_p primary key(dept));

alter table dp add(constrait dakfj_f key (dd) references dep1t(dept) ;

alter table ddd add (constraint dskf_f key (dsf) references dept1(dept) on delete cascade);关联，删除。字表的含有 这个字段的数据会被删除。

约束可以删除，不可以修改。

删除主表的时候必须级联。

drop table dept1 cascade constraint.这时候会删除主表的时候删除级联关系，但是不会删除外表。

怎么看约束的进一步的关系。或者说错误在什么地方。这个时候可以用视图。

DBA_

ALL_

USER_

有很多这样的表。如DBA_TABLES，ALL_TABLES,USER_TBLES这些都是视图。

select count(*) form USER_TABLES;

SELECT TABLE_NAME FROM USER_TABLES;

通过这几个视图来了解更多自己的数据库。

DBA_CONSTRAINT

禁用约束。

有可能禁用一个的同时必须cascade级联关系。

当禁用disable和enable的时候注意顺序。

视图

视图的创建语法：create or replace view 视图名 as select * nul(spll,’未定’,name ,id,sum(nvl(jp,0)) sum from product;

显示出来和表示一样的。

可以将大的sql做成视图，这样可以直接用视图来操作了。从视图里抽取结果。

让用户只针对视图查找的话可以隐藏列。

视图里面并没有行，oracle、会用的时候替换为相应的子查询。在创建复杂视图的时候alias是必须要有的。表达式的列必须有别名；

视图时可以嵌套的。

向视图插入的话，必须保证，列在视图创建的时候有被select

只读视图。有些视图不希望用户更改它。后面添加with read only

不允许操作。禁止DML操作。

with check option

添加满足条件

、top-n

oracle 先执行条件再排序。

rownum是结果集中的行数，而不是表中的行数。

如果当前的行满足条件则保留在结果集，如果不等于则不考虑此行。此时结果集行数不增加。

如select name id from t1 where rownum=3;则不管t1表有多少记录，结果都为空

select name id from t1 where rownum<>3,则会显示第一行和第二行的数据，其余不显示。这一点和top-n很相似。