oracle常用的sql

一、:插入insert语句

给指定列插入数据：insert into dept(deptno,dname)values(50,'xx'); 

插入全部列数据：insert into dept(deptno,dname,loc)values(60,'xx','lll'); 简写 insert into dept values(70,'xxx','llll');

 二、更新update语句

更新指定数据：update dept set dname='司法部'where deptno=50; 

update dept set dname='司法部',loc='china' where  deptno=50;

三、删除delete语句

删除指定数据：delete from dept where deptno=70; 

删除指定条件的数据：delet efrom dept where deptno>40;

四、查询select语句

查询所有：select * from emp; 

指定字段查询：select ename,sal from emp; 

加 where 条件：select * from emp where sal>=800; 

select * from emp where sal>=1500 and job='SALESMAN'; 

Distinct 去重复记录；

select distinct * from emp;

 Groupby 分组查询：select  job,count(ename) as num from EMP t group by job; 

Having 过滤分组：select job,count(ename) as num from EMP t group by job having count(ename)>=2; 

Orderby 排序：select *from emp order by sal desc;

子查询：查询出基本工资大于平均工资的员工：select * from emp  where sal>(select avg(sal) from emp)
联合查询： 并集（去重复）： 

select * from t_user1

 union  

select  * from t_user2;

并集：

 select * from t_user1 

union  all 

select * from t_user2;
交集： 

select * from t_user1 

intersect 

select * from t_user2;
差集： 

select * from t_user1 

minus 

select * from t_user2;
内连接： 

select * from emp t, dept  d   where   t.deptno=d.deptno; 类似：select * from  emp e  inner  join  deptdon   e.deptno=d.deptno   ;inner 可以省略；
外连接： 

左外连接：select  *   from   emp   e   left   join    dept d  on   e.deptno=d.deptno; 

右外连接：select   *   from    emp   e   right    join   dept   d    one.deptno=d.deptno;