oracle10g- oracle数据语言 常用函数 日期格式

数据语言 

ddl create alter drop定义

dcl grant revoke控制

dml select delete update insert操作

create table tt(c1 varchar2(10),c2 varchar(6));

alter table tt add cc number;

alter table tt drop column c2;

grant select on tt to scott;

revoke select on tt from scott;

insert into tt values('dfs','ss');

update tt set c2='dd' where c1='dfs';

select substr('01234567',1,4) from dual;//0123

不支持left right函数 

select substr('01234567',1,4) from dual //相当于left

select substr ('01234567',length('01234567')-4+1,4) from dual; --相当于right

**************

select sysdate from dual;设置时间 格式

alter session set nls_date_fromat='dd-mon-yyyy hh:mi:ss';

select next_day(sysdate,'星期三') from dual;//星期几不能是数字 像3 或星期3 表示从今天开始下一个星期三是哪一天

select to_char(sysdate,'yyyy-mm-dd') from dual;

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select to_date('2010-3-2') from dual;

select to_number('343') from dual;

select sum(decode(sex,'男',1,0)) 男生数,sum(decode(sex,'女',1,0)) 女生数 from tablename;

如果有的列有空值可以用其他数据来代替

select id,nvl(name,'待输入') from t1;

select id from t1 where name is null;//这里不能用=号

select id from t1 where name is not null;

select * from t1 order by id asc;

select * from t1 order by id desc;

select * from t1 exists (select * from t1);

select distinct name from t1;

select * from t1 exists (select * from t1 where t1.name is not null);

oracle 中常用的系统函数

1.字符 length,lengthb, ltrim. replace, rtrim, substr, trim.

2.日期 sysdate current_date,next_day.

3.转换 to_char ,to_date,to_number

4.聚焦 sum,avg,max,min,count

5.其他 user,decode,nvl

select lenght('司要民a') from dual;

select ltrime('  ddd') from dual;

select rtrime('ddd     ') from dual;

select trime('  ddd   ') from dual; 常与varchar char 一块用

聚焦函数不能做为条件 写在where子句的后面

如 select pub from books where sum(price)>90 会报错

应该这样写

select pub from books group by pub heaving sum(price)>90;

select id ,name from t1 

union 取并集

select id,name from t2

select id ,name from t1

interect

select id,name from t2//取交集

insert into t1(name) select name from t2;

create tablet3 as (select id ,name from t3)