sql.txt

个人高级SQL开发的总结

delete from sysparas t
where rowid != (select max(t1.rowid) from sysparas t1
           where t1.paracode=t.paracode)
----------根据rowid来删除paracode重复记录
select t.* from sysparas t
where rowid != (select max(t1.rowid) from sysparas t1
           where t1.paracode=t.paracode)

----------取排序的[M,N]
select * from (
select areas.*,dense_rank() over (order by id) r from areas ) a
where a.r > 5 and a.r <10

在数据库触发器中几乎总是要使用触发器基表的列值，如果某条语句需要某列修改前的值，使用:OLD就可以了，使用某列修改后的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。

----------表中存在更新，不存在插入
merge into areas a
using (select 1 id,2 cityid from dual) b
on (a.id=b.id and a.cityid=b.cityid)
when matched then update set id=-1
when not matched then insert (id,cityid) values (-1,-1);

----------批量更新
update areas a set (id,cityid)=
       (select id,cityid from dual b where a.id=b.id and a.cityid=b.cityid )
where exists (select 1 from dual where a.id=b.id and a.cityid=b.cityid )

----------取超过数量的记录
select id,count(1) from areas group by id having count(1)>0

若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。

----------使用with子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用会报错。而且with子句获得的是一个临时表，如果在查询中使用，必须采用select  from with查询名
with a as (select sysdate from dual), 
b as (select sysdate from dual) 
select sysdate from dual 
union all select * from a
union all select * from b 

----------如何加空的几行
with a as (select 2 from dual)
select id from areas t where id=2
union all
select null from all_objects where rownum <= (select * from a)

----------分组统计
select decode(grouping(paracode),1,'allparacode',paracode) paracode,
decode(grouping(paraname),1,'allparaname',paraname) paraname,
sum(paravalue) paravalues
from sysparas group by ROLLUP (paracode,paraname)
--from sysparas group by CUBE (paracode,paraname)
其中 rollup 是先根据paracode分组，并且paracode求总，接着再paraname分组，
并且按paraname求总。
cube是返回所有列组合（m*n）的小计信息，是加上总计后的所有列组合

---------层次查询
select * from areas t
where t.layer=3
connect by prior t.parentid=t.id start with t.cityid=571;

---------分析函数
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
GROUP只能显示聚合列，partition能得到所有列（除partition by列以外）的组合的统计记录
parition by的多个列可以当作一个列使用

select A,B,NUMVALUE,sum(NUMVALUE) over (partition by A) G_S from 
 (select 100 A, 2 B, 3 NUMVALUE from dual
union all select 101 ,  2 ,  3  from dual
union all select 102 ,  1 ,  2  from dual
union all select 102 ,  2 ,  4  from dual
)-------按A分组，返回的记录数就与原来的记录数一样。
SUM.OVER.partition by NULL 不按字段分组，返回记录数与原来记录数一样

---------LAG LEAD函数
select A,lag(NUMVALUE,1,-1) over (partition by B order by A) from
         (select 100 A, 2 B, 1 NUMVALUE from dual
union all select 101 , 1 , 2  from dual
union all select 102 , 2 , 3  from dual)
SQL逻辑解析：根据B分组，分组后各组按A排序，在排序后某一组中
LAG取上一个序列的NUMVALUE的值

---------EXP IMP导出表
将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST  file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y  file=file= d:\data\newsmgnt.dmp ignore=y 
针对是否导出数据行rows=N
exp system/manager full=Y rows=N file=full.dmp
imp system/manager full=Y rows=N file=full.dmp

---------where子句嵌套子查询
where 子句中嵌套子查询,执行顺序是先执行子查询 再执行主查询
用子查询的结果,作为字段来出现,先执行主查询,再执行子查询


---------表结构设计的理解
外键引用的列一定是主键或有unique约束的列，
DDL语句 会自动提交以前未提交的事务，（create alter drop truncate）
事务何时存在 DML语句中除select以外都会有事务，除select外，其他DML操作都可以都关系到回滚段
update student set sal = null where xh =1000;
savepoint c111;   
insert into student(xh,name,sex) values (1004,'MIKE','男');
rollback to c111; --撤销了插入的数据
rollback;  --从c111这个点回滚到事务的开始点

----------约束是如何起作用的
create table cla( --班级表
  id number(2) constraint pk_cla primary key, --班级编号
  cname varchar2(20) constraint nn_cla not null --班级名字
);
      
create table stu( --学生表
  xh number(4) constraint pk_stu primary key, --学号是主键
  xm varchar2(20) constraint nn_stu not null, --姓名非空
  age number(2) constraint ck_stu check (age between 10 and 90),
  --年龄在10到90之间(10<= age  <=90 )
  birthday date,
  shenfenzheng number(18) constraint uq_stu unique, --身份证唯一  
  classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
  --(引用的一定是另外表的主键或唯一性约束unique的字段)
);
主键 = 非空 + 唯一
非空 
唯一 = 有值的话  值要不同
       null的话  都是可以的
外键 = 有值 一定要在被引用的表的数据中
       null的话  是可以的