ORACLE文档格式.docx
《ORACLE文档格式.docx》由会员分享,可在线阅读,更多相关《ORACLE文档格式.docx(5页珍藏版)》请在冰豆网上搜索。
);
数据保存
1、如果为数据库中某个表所有列都插入数据,那么在写插入语句时,可以不用写列名.
insertintodemo_kcvalues('
103'
'
aaa'
3,78,6)
2、如果为数据库中某个表部分列插入数据,那么在写插入语句时,只把用的列名写一下.
insertintodemo_kc(kch,kcm,xf)values('
104'
bbb'
4)
3、批量将一张表中的部分列(或全部列)的数据保存到另外一张表中
insertintodemo_kc_1SELECTkch,kcmFROMdemo_kc
4、批量将一张表中的部分列(或全部列)的数据保存到另外一张表中,连带创建表
createtabledemo_kc_2asSELECTkch,kcm,xfFROMdemo_kc
数据修改
1、如果要修改多列,中间要加逗号.
updatedemo_kc_2setkcm='
new_aaa'
xf=6wherekch=103
2、修改时一定要注意,加WHERE条件,否则是全表数据修改
数据删除
1、删除.
Deletefromdemo_kc_2wherekch=103
2、修改时一定要注意,加WHERE条件,否则是全表数据删除
SELECT×
Ö
¶
Î
1£
¬
×
2£
¡
¡
FROM±
í
1[£
±
2]¡
WHERE²
é
Ñ
¯
Ì
õ
¼
þ
GROUPBY·
·
HAVING
ORDERBYÁ
Ð
Á
select*fromdemo_xsqk
selectxh,xm,zymfromdemo_xsqkwherexh>
'
001101'
orderbyxhdesc
selectzxf+1,zxf-2,zxf*3,zxf/2fromdemo_xsqk
selectxh,xm,zym,xh||'
||xm||'
||xhfromdemo_xsqk
selectsysdate,sysdate-1,sysdate+1fromdual
select*fromdemo_xsqkwherexb='
Ä
select*fromdemo_xsqkwherexb<
>
select*fromdemo_xsqkwherezxf>
=70
select*fromdemo_xsqkwherezxfBETWEEN50AND70
=50andzxf<
select*fromdemo_xsqkwherexhin('
001102'
001404'
)andzxf>
50
select*fromdemo_xsqkwhere(xh='
orxh='
orxh='
select*fromdemo_xsqkwherexmlike'
Í
%'
select*fromdemo_xsqkwherebzlike'
bz%'
selectdistinctzymfromdemo_xsqk
selectkch,xh,cjfromdemo_xskccjorderbykch,cjdesc,xhdesc
selectkchaskch111,xhxh2222222,cjfromdemo_xskccjorderbykch,cjdesc,xhdesc
selectto_char(sysdate,'
yyyy-mm-dd'
)ascurrdatefromdual
selecta.xm,c.kcm,--b.cj,c.xs,
casewhenb.cj>
=c.xs
then'
°
¸
ñ
else'
²
»
endasxk
fromdemo_xsqka,demo_xskccjb,demo_kcc
wherea.xh=b.xhandb.kch=c.kch
orderbya.xm
selectt1.xm,t2.kcm,
casewhent1.cj>
=t2.xs
from
(
selecta.xh,b.kch,a.xm,b.cj
fromdemo_xsqka,demo_xskccjb
wherea.xh=b.xh
)t1,
selectb.xh,b.kch,c.kcm,c.xs
fromdemo_xskccjb,demo_kcc
whereb.kch=c.kch
)t2
wheret1.xh=t2.xhandt1.kch=t2.kch
orderbyt1.xm
//字符函数
select'
aaa'
ltrim('
aaa'
),rtrim('
),
trim('
aaa'
)fromdual
selectupper('
aRJFlaaaII'
),lower('
selectinitcap('
selectconcat('
111'
222'
||'
fromdual
selectlpad('
100'
6,'
),rpad('
selectlength('
aafaf'
selectinstr('
abcdefg'
c'
selectsubstr('
4)fromdual
4,1)fromdual
selectto_date('
2010-11-11'
selectCHR(76)fromdual
selectASCII('
a'
//数学函数
selectabs(-25)fromdual
//返回大于或等于当前数字的最小整数
selectceil(25.4777)fromdual
//返回小于或等于当前数字的最小整数
selectfloor(5.9)fromdual
//四舍五入,第二个参数表示小数位
selectround(33.5651,2)fromdual
//取模
selectmod(5,2)fromdual;
//日期函数
selectsysdatefromdual
selectadd_months(sysdate,2)fromdual
//某个月的最后一天
selectlast_day(sysdate)fromdual
selectlast_day(to_date('
2012-02-03'
))fromdual
//返回两个日期中比较大的那一个
selectgreatest('
2011-02-03'
//返回两个日期中比较小的那一个
selectleast('
//两个日期相差的月数
selectMONTHS_BETWEEN(sysdate,to_date('
//日期截取
selectsysdate,TRUNC(sysdate)fromdual
//集合函数
selectmin(cj),max(cj),AVG(cj),sum(cj),count(0)fromdemo_xskccj
//在检索列中出现的列(除集合函数),一定要在分组中出现
selectxh,max(cj)fromdemo_xskccjgroupbyxhhavingmax(cj)>
=80
//左连接,以左表为主,左表所有数据都出来,右表和左表匹配上的数据出来
SELECTt1.*,t2.*FROMdemo_kc_1t1
leftjoindemo_kc_2t2ont1.kch=t2.kch
//右连接,以右表为主,右表所有数据都出来,左表和右表匹配上的数据出来
rightjoindemo_kc_2t2ont1.kch=t2.kch
//
SELECTt1.*,t2.*FROMdemo_kc_1t1,demo_kc_2t2
wheret1.kch(+)=t2.kch
insertintodemo_kc3(id,name)values((selectmax(id)+1fromdemo_kc3),'
vbvv'
insertintodemo_kc3(id,name)values(seq_demo_kc3.nextval,'
afafaf'
//用触发器来维护主键自增长
insertintodemo_kc3(name)values('
tttttttt'