oracle 11g 常规sql
简单的SQL语句应用
一、数据查询、插入、更新、删除
1.查询：
备份表：create table xsjbxxb_bak as select * from xsjbxxb;
备份表结构：create table xsjbxxb_bak as select * from xsjbxxb where 1=2;
2.导数据
表前20条记录：select * from xsjbxxb where rownum<21;
查重（分组）：select bjmc from bjdmb group by bjmc having count(*)>1;
单行子查询返回多行，违反唯一性约束
3.查重复数据：
select * from bjdmb where bjmc in (select bjmc from bjdmb group by bjmc having count(*)>1);
4.查重复次数：
select distinct bjmc,count(*) from bjdmb group by bjmc having count(*)>1;
5.字符串连接：
select 'drop table '||table_name||' purge;' from user_tab_comments where table_name like 'AAA%';
6.查询排序：
select * from bjdmb order by nj asc,bjmc desc;
select * from bjdmb order by 4 asc,2 desc；
7.in与exists: 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in
select * from jw_jh_kcdmb where (kch,xf,kcmc) in (select kch,xf,kcmc from jw_cj_xscjb)
and tkbj='1'；
select * from jw_jh_kcdmb a where exists (select 'X' from jw_cj_xscjb b where a.kch=b.kch and a.xf=b.xf and a.kcmc=b.kcmc)
and tkbj='1'
8.并集、差集、交集：
select * from xqdmb union select * from xqdmb1 ----并集过滤重复
select * from xqdmb union all select * from xqdmb1 ----并集不过滤重复
select * from xqdmb intersect select * from xqdmb1 ----交集
select * from xqdmb Minus select * from xqdmb1 ----差集
9.关联查询：
内连接：select a.*,b.* from xqdmb a inner join xqdmb1 b on a.xqdm=b.xqdm
select a.*,b.* from xqdmb a,xqdmb1 b where a.xqdm=b.xqdm
左连接：select a.*,b.* from xqdmb a left join xqdmb1 b on a.xqdm=b.xqdm
select a.*,b.* from xqdmb a,xqdmb1 b where a.xqdm=b.xqdm(+)
右连接：select a.*,b.* from xqdmb a right join xqdmb1 b on a.xqdm=b.xqdm
select a.*,b.* from xqdmb a,xqdmb1 b where a.xqdm(+)=b.xqdm
全连接：select a.*,b.* from xqdmb a full join xqdmb1 b on a.xqdm=b.xqdm
10.开窗函数：
select row_number() over(partition by bjmc order by bynd) sxh,a.*
from bjdmb a where bjmc='16药学1' or bjmc='16眼专1';
select rank() over(partition by bjmc order by bynd) sxh,a.*
from bjdmb a where bjmc='16药学1' or bjmc='16眼专1';
select dense_rank() over(partition by bjmc order by bynd) sxh,a.*
from bjdmb a where bjmc='16药学1' or bjmc='16眼专1';
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
11.插入：
insert into jw_xs_xsjbxxb（xh,xm）values ('001','张三');
insert into jw_xjgl_xsxjxxb( xh,xm,xnm,xqm ) --------------学籍信息表（时盒）
select xh,xm,'2019','3' from jw_xjgl_xsjbxxb where xnm||xqm=’201812’ -------学生信息表
insert into bjdmb select * from bjdmb;
-----------------用户课程数据范围-----------------
select * from ZFTAL_XTGL_YHSJFWB
create table ZFTAL_XTGL_YHSJFWB as
select * from jw_user.ZFTAL_XTGL_YHSJFWB where yhm like 'LS%' and yhm not like 'XG%' 用户数据范围表 sjfwz_id 关联下面的组表
create table zftal_xtgl_sjfwzb as
select * from jw_user.zftal_xtgl_sjfwzb where kzdx='kc' 数据范围组表 sjfwz_id
insert into ZFTAL_XTGL_YHSJFWB(js_id,sjfwz_id,sfqy,yhm)
select b.jsdm,a.yhm sffwz_id,'1',a.yhm from jw_user.zftal_xtgl_yhb a left join jw_user.zftal_xtgl_yhjsb b on a.yhm=b.yhm where a.yhm like 'LS%';
insert into zftal_xtgl_sjfwzb
select yhm,(select jgmc from jw_user.zftal_xtgl_jgdmb b where a.jgdm=b.jgdm)||'【课程数据】','kkbm_id='||jgdm,'kc' from jw_user.zftal_xtgl_yhb a where yhm like 'LS%'
select * from zftal_xtgl_sjfwzb
12.更新：
update AAA_jxrwb_z set zxs='0.0-12.0' where zxs='0.0-12.';
select * from zydmb 专业代码所属学院是对的
select * from bjdmb_gx 班级代码表所属学院是错的
update bjdmb_gx a set ssxydm=(select ssxydm from zydmb b where a.sszydm=b.zydm) --有问题
13.删除：
删除表： drop table kcdmb1 purge
删除数据：delete from kcmdb1;
Truncate table kcdmb1;
删除重复数据：(先查询后删除)
select * from bjdmb
where bjmc in (select bjmc from bjdmb group by bjmc having count(bjmc)> 1)
and rowid not in (select min(rowid) from bjdmb group by bjmc having count(bjmc)>1);
delete from bjdmb
where bjmc in (select bjmc from bjdmb group by bjmc having count(bjmc)> 1)
and rowid not in (select min(rowid) from bjdmb group by bjmc having count(bjmc)>1);
select * from bjdmb
where ( bjmc,bjdm) in (select bjmc,bjdm from bjdmb group by bjmc,bjdm having count(bjmc)> 1)
and bynd not in (select min(bynd) from bjdmb group by bjmc,bjdm having count(bjmc)>1)
自连接：
select * from bjdmb a where exists (select 'X' from bjdmb b where a.bjdm=b.bjdm and a.bjmc=b.bjmc and a.rowid>b.rowid)
delete from bjdmb a where exists (select 'X' from bjdmb b where a.bjdm=b.bjdm and a.bjmc=b.bjmc and a.rowid>b.rowid)
delete from bjdmb a where exists (select 'X' from bjdmb b where a.bjdm=b.bjdm and a.bjmc=b.bjmc and a.bynd>b.bynd)
2、常用函数应用学习
左补齐：select lpad('123',10,'X') from dual
右补齐：select rpad('123',10,'X') from dual
去空格： select trim(' 123 ') from dual
select RTRIM(' 123 ') from dual
select LTRIM(' 123 ') from dual
NVL和Coalesce ：select nvl('','1') from dual
select Coalesce('','','5','4','1') from dual
case when then else end:
select
case when bfzcj<=100 and bfzcj>=90 then '优秀'
when bfzcj<90 and bfzcj>=80 then '良好'
when bfzcj<80 and bfzcj>=70 then '中等'
when bfzcj<70 and bfzcj>=60 then '及格'
else '不及格' end 五级制成绩
from jw_user.jw_cj_xscjb;
Deceode:
select decode(nvl(xb,'9'),'男','1','女','2','9','其他') from xsjbxxb
Replace: select replace('he love you','he','i') from dual;
TRANSLATE:
select xqj, translate(xqj,'1234567','一二三四五六七') from jw_jh_jsjxrlsjb
select xqj, translate(xqj,'1234567','一 三四五六七') from jw_jh_jsjxrlsjb
substr()和instr()
length()
3、系统自定义函数学习
create or replace function Get_zxs(
vZxs varchar2) return varchar2
as
vNewzxs varchar2(20);
vBj varchar2(20);
begin
vBj :=translate(vZxs,'0123456789.','aaaaaaaaaab');
vNewzxs :=vZxs;
if vBj like '%-%'then
if substr(vBj,1,INSTR(vBj,'-',1)) like '%b-' then
vNewzxs:= substr(vNewzxs,1,INSTR(vNewzxs,'-',1)-1)||'0'||substr(vNewzxs,instr(vNewzxs,'-',1),length(vNewzxs)-instr(vNewzxs,'-',1)+1);
end if;
if substr(vBj,-2,2)='ab' then vNewzxs:= vNewzxs||'0';
end if;
if substr(vBj,1,INSTR(vBj,'-',1)-1) not like '%b%' then
vNewzxs:= substr(vNewzxs,1,INSTR(vNewzxs,'-',1)-1)||'.0'||substr(vNewzxs,instr(vNewzxs,'-',1),length(vNewzxs)-instr(vNewzxs,'-',1)+1);
end if;
if substr(vBj,INSTR(vBj,'-',1)) not like '%b%' then
vNewzxs:=vNewzxs||'.0';
end if;
else
if vBj like '%b%' then
vNewzxs := vNewzxs||'-0.0';
else
vNewzxs := vNewzxs||'.0-0.0';
end if;
end if;
return vNewzxs;
--end;
end Get_zxs;