数据语言
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)
