oracle 11g培训文档,oracle 11g培训

简单的SQL语句应用

一、数据查询、插入、更新、删除

1、查询：

备份表：create table xsjbxxb_bak as select * from xsjbxxb ；

备份表结构：create table xsjbxxb_bak as select * from xsjbxxb where 1=2；

导数据

表前20条记录：select * from xsjbxxb where rownum<21；

查重(分组)：select bjmc from bjdmb group by bjmc having count(*)>1；

单行子查询返回多行，违反唯一性约束

查重复数据：

select * from bjdmb where bjmc in (select bjmc from bjdmb group by bjmc having count(*)>1)；

查重复次数：

select distinct bjmc,count(*) from bjdmb group by bjmc having count(*)>1；

字符串连接：

select 'drop table '||table_name||' purge;' from user_tab_comments where table_name like 'AAA%'；

查询排序：

select * from bjdmb order by nj asc,bjmc desc;

select * from bjdmb order by 4 asc,2 desc；

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'

并集、差集、交集：

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 ----差集

关联查询：

内连接：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

开窗函数：

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 ...)

插入：

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

更新：

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) --有问题

删除：

删除表： 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;