ORACLE.docx
《ORACLE.docx》由会员分享,可在线阅读,更多相关《ORACLE.docx(5页珍藏版)》请在冰豆网上搜索。
![ORACLE.docx](https://file1.bdocx.com/fileroot1/2022-11/16/94d3f2ab-850f-4ffb-a3f5-4557397f0925/94d3f2ab-850f-4ffb-a3f5-4557397f09251.gif)
ORACLE
创建表
Createtabletablename(
列名类型(长度)notnull,
列名类型(长度)
)
增加一列
altertabletablenameadd列名类型(长度)
删除一列
altertabletablenamedropcolumn列名
非空约束
altertableTB070602modifyC2notnull;
主键约束
altertableTB070601
addconstraintPRI_TB070601primarykey(C1,C2);
altertableTB070602
addconstraintUNI_070602unique(C4)
altertableTB070602
addconstraintCHE_070602
check(C5='1'ORC5='0');
数据保存
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·Ö×é×Ö¶Î1[£¬·Ö×é×Ö¶Î2]¡¡HAVING
·Ö×éÌõ¼þ
ORDERBYÁÐ1[£¬ÁÐ2]¡¡
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
select*fromdemo_xsqkwherezxf>=50andzxf<=70
select*fromdemo_xsqkwherexhin('001101','001102','001404')andzxf>50
select*fromdemo_xsqkwhere(xh='001101'orxh='001102'orxh='001404')andzxf>50
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
then'¼°¸ñ'
else'²»¼°¸ñ'endasxk
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('aaa'),
trim('aaa')fromdual
selectupper('aRJFlaaaII'),lower('aRJFlaaaII')fromdual
selectinitcap('aRJFlaaaII')fromdual
selectconcat('111','222')fromdual
select'111'||'222'fromdual
selectlpad('100',6,'0'),rpad('100',6,'0')fromdual
selectlength('aafaf')fromdual
selectinstr('abcdefg','c')fromdual
selectsubstr('abcdefg',4)fromdual
selectsubstr('abcdefg',4,1)fromdual
selectto_char(sysdate,'yyyy-mm-dd')fromdual
selectto_date('2010-11-11','yyyy-mm-dd')fromdual
selectCHR(76)fromdual
selectASCII('a')fromdual
//数学函数
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','yyyy-mm-dd'))fromdual
//返回两个日期中比较大的那一个
selectgreatest('2012-02-03','2011-02-03')fromdual
//返回两个日期中比较小的那一个
selectleast('2012-02-03','2011-02-03')fromdual
//两个日期相差的月数
selectMONTHS_BETWEEN(sysdate,to_date('2011-02-03','yyyy-mm-dd'))fromdual
//日期截取
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
//右连接,以右表为主,右表所有数据都出来,左表和右表匹配上的数据出来
SELECTt1.*,t2.*FROMdemo_kc_1t1
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')