sql总结——oracle

1】
SQL：结构化查询语言(Structured Query Language)


2】-----------------------------------------------------------------
在15(所建库端) 登录pl/sql 用超级用户登录并执行sql
#sys *sys 
SYSDBA
———————————————建表空间—————————————
create temporary tablespace sms_temp 
  tempfile 'F:\app\Administrator\oradata\orcl\sms_tmp.dbf'   size 32m  
   autoextend on   next 32m maxsize 4096m   extent management local; 

create tablespace sms   logging   
datafile 'F:\app\Administrator\oradata\orcl\sms.dbf'
   size 32m   autoextend on   next 32m maxsize 4096m
  extent management local; 

create user sms identified by sms --创建用户并指定默认表空间和临时表空间
  default tablespace sms   
  temporary tablespace sms_temp; 

grant connect,resource,dba to sms   --赋予用户权限
___________________________________
------------------------------------------------
密码过期时间：
1.查看口令失效用户的profile文件
SQL>SELECT username,profile FROM dba_users;
EM：服务器>用户，查看口令失效的用户对应的概要文件，这里假设为DEFAULT，下同。
2.查看对应的概要文件的口令有效期设置
SQL>SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
EM：服务器>概要文件>选择刚刚查到的概要文件DEFAULT>查看，查看口令下面的有效期值。
3.将口令有效期默认值180天修改成“无限制”
SQL>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
------------------------------------------------

3】
--删除指定用户所有表空间及数据
drop user gyforestry cascade;--删除用户
DROP TABLESPACE gyforestry INCLUDING CONTENTS AND DATAFILES;--删除表空间

4】动态性能表相关的权限没有被授权给当前用户：
grant select on V_$session to sms;
grant select on V_$sesstat to sms;
grant select on V_$statname to sms;

5】视图权限
grant create  view to sms; 

6】
查询所有用户及临时表空间。
select username,temporary_tablespace from dba_users

8】
一个用户user可以拥有多个表空间tablespace。 一个表空间可以有多个表table。
Oracle用户名，表空间名称是区分大小写。其中表空间名称不带引号的话默认大写。加了""才是小写。
----------------------------------------------
7】增：
：insert into table_lzf (id,姓名,age) values(1,'张三',16)
：insert into table_lzf (id ,name,age)select id,name,age from table_lzf2

---------------------------------------
8】
【删数据】delete from person where id=1
【删字段】alter table lz drop column name
【删全表】drop table person 

---------------------------------------------
9】
改：
【改数据】update person set nian=2016 where nian is null  
 update lz set bz =age+shouru ,n2=age+shouru
【修改表的属性】alter table xinb2 modify (id number(10))
【改表名】alter table table_lzf2 rename to person ;
【替换字符】update busi_lq_nmzyhzs set sfsjb=replace (sfsjb, ',', ';'); 用分号替换逗号。或：update busi_lq_nmzyhzs set sfsjb=replace (chr(0)||sfsjb,chr(0)||',',';');
-------------------------------------------------------------------------------
10】
查：【select from】
select * from table_lzf where rownum<=4
【where between and】select * from table_lzf where id between 4 and 8
【not】select * from table_lzf where id  not between 4and8
【内部连接 where】select table_lzf.name,table_lzf.age,table_lzf2.hoby from table_lzf,table_lzf2
where table_lzf.id=table_lzf2.id
【左连 left join+on】select table_lzf.name,table_lzf2.hoby
        from table_lzf
        left join table_lzf2
        on table_lzf.id=table_lzf2.id

--------------------------------------------------------------------------------
UNION 命令只会选取不同的值
UNION ALL选取所有的值
【备份create table as】：create table table_bf  as select * from table_lzf
————————————————————————
select * from table_lzf where name like  '[WI]%' 查找以W或I开头的name。
 
13】decode
表格按特定排序：order by decode(t2.name,'合计',1,'Ⅰ',2,'Ⅱ',3,'Ⅲ',4,'Ⅳ',5);注意sql里的引号是单引号。
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 
select decode(A.a,1,4,3,8,7),A.b,A.c form A 
意思:如果A.a的值为1，则将A.a的值查询为4，如果A.a的值为3，则将A.a的值查询为8，否则查询为7 

14】 concat(st1,st2)相当于操作符"||"

16】.oracle输出时间格式问题：to_char(字段,'yyyy-mm-dd') 字段,
17】(by 刘映)同字段表，合计问题。
select ducode ,sum(numperson_allyear),avg(avemonthpay_allyear),
sum(numperson_temporary),avg(avemonthpay_temporary), 
 avg(ghtime_temporary)    from busi_forestranger where year= 2015  
and procategoryid in (1,5)
group by ducode 
order by ducode asc 

18】对字段的操作
】添加字段的语法：
alter table tree add (gender integer);
comment on column tree.gender is '性别';
】删除字段的语法：alter table tablename drop (column);
】添加、修改、删除多列的话，用逗号隔开。

19】wm_concat(column)函数  

20】case 
when  then 
else end;
 【例子：
 SELECT  SUM(population),
CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
ELSE '其他' END;

21】distinct 选取唯一不同的值
select distinct gsname from busi_gsmm_gsxx 

【建表事项】
ID 用integer
普通的用 VARCHAR2(20)一般不含中文
中文较多，NVARCHAR2(20) 包含20个汉字或字母。

【复制表】
复制表结构：右键复制即可。注意更换主键。
复制表数据：INSERT INTO 新 SELECT * FROM 旧

【sum函数扩展】
sum(case when 字段三='x' then 1 else 0 end) 
：是x就加1 ，否则就加0
实例：
 select sum( case when gender='男' and country =1 then 1 else 0 end)as aa ，
 sum( case when gender='男' and country =2 then 1 else 0 end)as bb 
  from lz 

cast】数据类型转换。
数字转换为字符：cast(t.wltype as varchar(50))
select cast(round(jcfgl,3)as number(4,2) ) from busi_yhsw_pestinfo
update busi_yhsw_pestinfo set jcfgl=(cast(round(sjcmj/yjmj,2) as number(4,2)))
  
24】显示出树的级别查询
  select t.id,rpad(' ',(level-1)*4)||t.jgrs_name,level as ll from busi_jgrs    t 
 connect by pid = prior id   
 start with pid =1

25】
树-节点】
Oracle中start by prior子句用法 
connect by 是结构化查询中用到的，其基本语法是： 
select ... from tablename start with 条件1 
connect by 条件2 
where 条件3;  

例：
所有节点(全部)
select * from busi_jgrs  start with id = '1' connect by prior id = pid;

所有底节点（等级无关）
 select * from busi_jgrs where id in (select id from (select id, CONNECT_BY_ISLEAF isleaf from busi_jgrs 
start with id = '1' connect by prior id = pid)tt where tt.isleaf=1)

向上查询：
select * from TBL_TEST start with id=5 connect by prior pid = id
伪列：
select jgrs_name,level, CONNECT_BY_ISLEAF isleaf from busi_jgrs 
start with id = '1' connect by prior id = pid 

查询：等级为2且为底层节点
select t.*, level, CONNECT_BY_ISLEAF isleaf
  from tree t
 where level = 2
   and CONNECT_BY_ISLEAF = 1
 start with tid = '1'
connect by prior tid = pid;
27】
不等于的表示
<>是标准语法.可以移植到其他任何平台.
!=是非标准语法.可移植性差.
但都是表示不等于的意思.
应用中尽量用<>

28】
nvl(字段名,n)，若为null则为n.

29】同行左右合并
select A.*,B.* from (select ROW_NUMBER()over (ORDER BY A ) id,A,B,C,D from AA) A
INNER JOIN 
(select ROW_NUMBER()over (ORDER BY E ) id,E,F,G from BB )B
ON A.id=B.id

上下合并
select hjyi as gkq,gtmj,t.yild as ld,t.yisd as sd from busi_bigdata_sthx t  where dqcode=520100
union all
select hjyi as gkq,gtmj,t.hjer as ld,0 as sd from busi_bigdata_sthx t  where dqcode=520100

30】截取
substr(jgrs_name,0,length(jgrs_name)-3)
Oracle 字符串函数 substr(字符串,截取开始位置,截取长度)
 1. 如果最后一个截取长度参数为空，则表示从截取开始位置起截到最末
 2. 如果截取开始位置 为大于0的数字，则表示从字符串左数几位开始
 3. 如果截取开始位置 为小于0的数字，则表示从字符串右数几位开始

 31】
涨姿势：联合含父子关系的表。
select * from BS_FORESTPEST t,bs_dq t1,bs_dq t2 where t1.dqparerent=t2.dqcode and t1.dqcode=t.dqcode and t2.dqname = '黔南州'

32】Where是对单条纪录进行筛选,Having是对分组结果进行筛选.

33】运算符：
集合运算符： intersect ，union， union all， minus

34】lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格

35】删除重复值。思路：分组去其一并删除。
   delete from lz where lzid not in (select min(lzid) from lz group by name)

36】取前十条数据：select  * from lz where rownum <=10

37】
随机取10条数据
select * from (select * from person order by dbms_random.value) where rownum<11
按id的前十条数据：select * from (select * from person order by id) where rownum<11

38】取10到20条数据：//把ROWNUM 实例化才能用
SELECT * FROM (SELECT a.*, ROWNUM rn FROM lz a) WHERE rn >= 3 AND rn <= 6 
   
39】PIVOT命令可以实现数据表的【列转行】.UNPIVOT反之。
 oracle数据透视表：pivot ( sum( cnt) FOR tpclass IN )
SELECT * FROM ( SELECT ducode, level, count( * ) cnt FROM busi_artree t  GROUP BY ducode, level ) pivot ( sum( cnt) FOR level IN ( 'Ⅰ级' , 'Ⅱ级' , 'Ⅲ级'  ) ) ;
SELECT * FROM ( SELECT duname, MESUREID, sum( tasktotal ) tasktotal FROM fcpm_view t  GROUP BY duname, MESUREID ) pivot ( sum( tasktotal) FOR MESUREID IN (3,4) ) ;
思想：先对表进行三列分类  SELECT duname, MESUREID, sum( tasktotal ) tasktotal FROM fcpm_view t  GROUP BY duname, MESUREID
再进行转置。SELECT * FROM  三分列表 pivot ( sum( tasktotal) FOR MESUREID IN (3,4) ) ;  对MESUREID这个字段的值作为行。
//SELECT * FROM  (select * from busi_wlresource ) pivot ( max(name) FOR wltype IN (0,1,2,3) ) ;非数值
unpivot 行转列。
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )  
注意：  unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量
select name, value  from (select nvl(sum(area),0)总面积 ,nvl(sum(dyewpgd),0)大于25度坡耕地, nvl(sum(yld),0)宜林地 ,nvl(sum(cfhsjd),0)采伐火烧迹地 from busi_fc_kezaolin where year=2016 group by year) unpivot (value for name in (总面积, 大于25度坡耕地, 宜林地, 采伐火烧迹地) )  
40】去空格（oracle中包含半角空、全角空、换行符）总结：
update BUSI_GYL set DQXXID=REPLACE(DQXXID, ' ', '')
update lz set name=trim(name);半角空格
去除其他也一样：update lz set name=trim('区' from name)
update lz set name=replace(name,chr(10),'');
//translate 是 replace 所提供的功能的一个超集
制表符 chr(9) 
换行符 chr(10)
回车符 chr(13)
空格   chr(32)

41】跪舔正则regexp
update lz set name=regexp_replace(name,'\s','');  //所有的，前后中。

42】禁用所有外键关联。enable是启用。
select 'alter table '|| t.table_name||' disable constraint '||t.constraint_name||';'   
from user_constraints t where t.constraint_type = 'R'  
order by t.table_name

43】全部删除
select 'delete '|| table_name ||';' from user_tables;
   
44】
插入数据违反完整性约束
可能是导入表的顺序反了。应先导外键表。
   
45】user_tables 和all_tables
如果是用该用户登录使用以下语句：
该用户下所有表：SELECT * FROM USER_TABLES
如果是用其他用户：SELECT * FROM ALL_TABLES WHERE OWNER='USER_NAME' 
    
47】
大数据的删除。针对百万级数据的删除。truncate
TRUNCATE TABLE tbl_yunshu_xiangmu 
   
48】去除最后一个字符
RTRIM(fname,substr(fname,-1))as fname 
   
50】去除所有字段空格。（谨记：左中右的）
select 'update busi_fcplan set '|| cname || '=regexp_replace('||cname||',''\s'','''');' from col  where tname='BUSI_FCPLAN'

51】
对于更新未提交的表
在当前窗口查询为更新后的数据
换其他窗口则为未提交的数据（即未更新）

52】仿update left join 用法
update lz set lz.age=(select lz2.age from lz2 where lz.lzid=lz2.lzid)
//在市辖区前加上父级的名称；
update sys_dqcode t set t.name=(select t1.name from sys_dqcode t1 where t.pcode=t1.dqcode )|| t.name WHERE t.name='市辖区';

53】特殊表总结user_tab_comments,col,user_tables,all_tables
查看Oracle数据表中所有的 表名+注释
select table_name,comments from user_tab_comments; 
select table_name,comments from user_tab_comments where comments like '%覆盖率%'
取该表所有字段（谨记区分大小写）
select cname from col t where  t.tname='BUSI_FCPLAN' 

54】没值的也显示
注意两种写法的不同（）
关联条件要写在on子句中
select t1.dqname,round(sum(t.losesumarea),2)as area  from bs_dq t1 left join busi_forestfire t on t.ducode=t1.dqcode and t.year>2011  where t1.dqgrade=3  group by t1.dqname;
select t1.dqname,round(sum(t.losesumarea),2)as area  from bs_dq t1 left join busi_forestfire t on t.ducode=t1.dqcode  where t1.dqgrade=3 and t.year>2011 group by t1.dqname;

55】列出**
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
select t.dqparerent,listagg(dqname,',') within group(order by dqparerent) from bs_dq t group by t.dqparerent

56】
学习一下SQLSERVER 游标
declare @id int
declare @parentid int
declare @a int
set @a = 2000
declare cur cursor--定义一个游标
read_only
for select id,parentid
from tt --为所获得的数据集指定游标

open cur--打开游标
fetch next from cur into @id,@parentid--把提取操作的列数据放到局部变量中
while(@@fetch_status=0)--返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。

begin

update tt set id=@a   where id=@id;--执行操作
update tt set PARENTID=@a where PARENTID=@id;
set @a=@a+1
--提前下一位信息
fetch next from cur into @id,@parentid
end
close cur--关闭游标
deallocate cur--删除游标
Go
这个sql也可以像你那样更新

57】
实现：修改id，对应的父id也做相应改变。
excel中建辅助列，VLOOKUP两次使用即可。

58】
select substr(name,0,instr(name,' ',1,2)) from lz;
截取字符到第二个空格。
instr索引是从1开始的
select regexp_replace('123 456 789etd',' [^ ]+etd','') from dual;
return 123 456 

59】存储过程。
create or replace procedure 存储过程名（param1 in type，param2 out type）
as
变量1 类型（值范围）;
变量2 类型（值范围）;
Begin

End;
60】:=是赋值的意思。
v_sql :=TT
package允许多个procedure使用同一个变量和游标

61】
批量导出导入视图
select 'create or replace view '||view_name || ' as ',text,';' from all_views where view_name in('BUSI_CF_CFYS_VIEW',...);
csv导出后去除引号即可。