ORACLE.docx

1、ORACLE创建表Create table tablename( 列名 类型（长度） not null，列名 类型（长度）)增加一列alter table tablename add 列名 类型（长度）删除一列alter table tablename drop column 列名 非空约束alter table TB070602 modify C2 not null;主键约束alter table TB070601 add constraint PRI_TB070601 primary key (C1, C2);alter table TB070602 add constraint UNI_

2、070602 unique (C4)alter table TB070602 add constraint CHE_070602 check (C5=1 OR C5 =0);数据保存1、 如果为数据库中某个表所有列都插入数据，那么在写插入语句时，可以不用写列名.insert into demo_kc values(103,aaa,3,78,6)2、 如果为数据库中某个表部分列插入数据，那么在写插入语句时，只把用的列名写一下.insert into demo_kc(kch,kcm,xf) values(104,bbb,4)3、 批量将一张表中的部分列（或全部列）的数据保存到另外一张表中inser

3、t into demo_kc_1 SELECT kch,kcm FROM demo_kc4、 批量将一张表中的部分列（或全部列）的数据保存到另外一张表中，连带创建表create table demo_kc_2 as SELECT kch,kcm,xf FROM demo_kc数据修改1、 如果要修改多列，中间要加逗号.update demo_kc_2 set kcm=new_aaa,xf=6 where kch=1032、修改时一定要注意，加WHERE条件，否则是全表数据修改数据删除1、 删除.Delete from demo_kc_2 where kch=1032、 修改时一定要注意，加WH

4、ERE条件，否则是全表数据删除SELECT 12 FROM 12 WHERE GROUP BY12HAVING ORDER BY 12 select * from demo_xsqkselect xh,xm,zym from demo_xsqk where xh001101 order by xh descselect zxf+1,zxf-2,zxf*3,zxf/2 from demo_xsqkselect xh,xm,zym,xh|,|xm|,|xh from demo_xsqkselect sysdate,sysdate-1,sysdate+1 from dualselect * from

5、 demo_xsqk where xb=select * from demo_xsqk where xbselect * from demo_xsqk where zxf =70select * from demo_xsqk where zxf BETWEEN 50 AND 70select * from demo_xsqk where zxf = 50 and zxf 50select * from demo_xsqk where (xh=001101 or xh=001102 or xh = 001404) and zxf50select * from demo_xsqk where xm

6、 like %select * from demo_xsqk where bz like bz%select distinct zym from demo_xsqkselect kch,xh,cj from demo_xskccj order by kch,cj desc,xh descselect kch as kch111,xh xh2222222,cj from demo_xskccj order by kch,cj desc,xh descselect to_char(sysdate,yyyy-mm-dd) as currdate from dualselect a.xm,c.kcm,

7、-b.cj,c.xs, case when b.cj = c.xs then else end as xk from demo_xsqk a,demo_xskccj b,demo_kc cwhere a.xh = b.xh and b.kch = c.kchorder by a.xmselect t1.xm,t2.kcm,case when t1.cj = t2.xs then else end as xkfrom (select a.xh,b.kch,a.xm,b.cj from demo_xsqk a,demo_xskccj bwhere a.xh = b.xh ) t1,(select

8、b.xh,b.kch,c.kcm,c.xs from demo_xskccj b,demo_kc cwhere b.kch = c.kch) t2where t1.xh = t2.xh and t1.kch = t2.kchorder by t1.xm/字符函数select aa a ,ltrim( a aa ),rtrim( a aa ),trim( aaa ) from dualselect upper(aRJFlaaaII),lower(aRJFlaaaII) from dualselect initcap(aRJFlaaaII) from dualselect concat(111,2

9、22) from dualselect 111|222 from dualselect lpad(100,6,0),rpad(100,6,0) from dualselect length(aafaf) from dualselect instr(abcdefg,c) from dualselect substr(abcdefg,4) from dualselect substr(abcdefg,4,1) from dualselect to_char(sysdate,yyyy-mm-dd) from dualselect to_date(2010-11-11,yyyy-mm-dd) from

10、 dualselect CHR(76) from dualselect ASCII(a) from dual/数学函数select abs(-25) from dual/返回大于或等于当前数字的最小整数select ceil(25.4777) from dual/返回小于或等于当前数字的最小整数select floor(5.9) from dual/四舍五入,第二个参数表示小数位select round(33.5651,2) from dual/取模select mod(5,2) from dual;/日期函数select sysdate from dualselect add_months(

11、sysdate,2) from dual/某个月的最后一天select last_day(sysdate) from dualselect last_day(to_date(2012-02-03,yyyy-mm-dd) from dual/返回两个日期中比较大的那一个select greatest(2012-02-03,2011-02-03) from dual/返回两个日期中比较小的那一个select least(2012-02-03,2011-02-03) from dual/两个日期相差的月数select MONTHS_BETWEEN(sysdate,to_date(2011-02-03

12、,yyyy-mm-dd) from dual/日期截取select sysdate,TRUNC(sysdate) from dual/集合函数select min(cj),max(cj),AVG(cj),sum(cj),count(0) from demo_xskccj/在检索列中出现的列（除集合函数），一定要在分组中出现select xh,max(cj) from demo_xskccj group by xh having max(cj)=80/左连接，以左表为主，左表所有数据都出来，右表和左表匹配上的数据出来SELECT t1.*,t2.* FROM demo_kc_1 t1left j

13、oin demo_kc_2 t2 on t1.kch = t2.kch/右连接，以右表为主，右表所有数据都出来，左表和右表匹配上的数据出来SELECT t1.*,t2.* FROM demo_kc_1 t1right join demo_kc_2 t2 on t1.kch = t2.kch/SELECT t1.*,t2.* FROM demo_kc_1 t1,demo_kc_2 t2where t1.kch(+) = t2.kchinsert into demo_kc3(id,name) values(select max(id)+1 from demo_kc3),vbvv)insert into demo_kc3(id,name) values(seq_demo_kc3.nextval,afafaf)/用触发器来维护主键自增长insert into demo_kc3(name) values(tttttttt)