常用sql

1.获取系统日期：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

2.设置日期格式：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

3.获取系统时间戳：
select systimestamp from dual;

4.sql语句的if语句：
decode:
产品放在哪个仓库
select product_id,decode(warehouse_id,1,'Southlake',
2,'San Francisco',
3,'New Jersey',
4,'Seattle',
'unknow'
) "Location of inventory" from inventories;

简单case:

select cust_last_name,case credit_limit when 100 then 'low'
when 5000 then 'high'
else 'medium' end
from customers;

复杂case:

select name,(case when score<60 then 'D'
when score>=60 and score score<70 then 'C'
when score>=70 and score score<80 then 'B'
when score>=80 then 'A'
else 'no score' end
) from score;

5.把表student2与student3的数据插入到表tar中
insert into tar(id,first_name,last_name,major)
(select id,first_name,last_name,major)
from student2 where rownum<=5
union all
select id,first_name,last_name,major)
from student3 where rownum<=5
)

6.如何插入日期型数据

insert into d values('mary',to_date('2009-09-07 16:47:00','yyyy-mm-dd hh24:mi:ss'));

7.删除表中的重复记录

delete from table student where rowid not in(select min(rowid) from student group by id)

8.创建一个临时表
create table tem_student2
as
select * from student2
where 1=0;

1=0表示创建的临时表tem_student2中没有数据

9.得到两个表中不相同的记录
方法1:
select id,major from source
where id not in(select id from target)
union
select id,major from target
where id not in(select id from source);

方法2:
select id,major from (
select id,major from source
union
select id,major from target
)
group by id,major
having count(*)=1