oracle知识总结



全球化、跨平台的数据库
支持多用户、高性能的事务处理
强大的安全性控制和完整性控制
支持分布式数据库和分布处理

客户端远程连接数据库

sqlplus sys/oracle@192.168.11.100/orcl as sysdba

常用命令

select * from tab  查询当前用户下的所有对象

show user 显示当前用户

desc tablename;显示当前表的表结构

DML 语句（数据操作语言）Insert、Update、  Delete、Merge
DDL 语句（数据定义语言）Create、Alter、  Drop、Truncate
DCL 语句（数据控制语言）Grant、Revoke
事务控制语句 Commit  、Rollback、Savepoint

%  表示零或多个字符
_   表示一个字符
对于特殊符号可使用 ESCAPE  标识符来查找
select * from emp where ename like '%*_%' escape '*'
上面的 escape 表示*后面的那个符号不当成特殊字符处理，就是查找普通的_符号

数值函数

Round
select round(412,-2) from dual; 400
select round(412.313,2) from dual;412.3
Mod
Trunc
select trunc(412.13,-2) from dual;   400  （取整）

日期函数

Months_between()
select months_between(sysdate,hiredate) from emp;

//当前系统时间加一个月
Add_months()
select add_months(sysdate,1) from dual;
//从当前算起的下一个星期一
Next_day()
select next_day(sysdate,'星期一') from dual;

//当前系统时间的当月的最后一天的日期
Last_day
select last_day(sysdate) from dual;

转化函数

select to_char(sysdate,'yyyy') from dual;    //2014

select to_char(sysdate,'fmyyyy-mm-dd') from dual; //2014-06-10

select to_char(sal,'L999,999,999') from emp;  //￥1300

select to_char(sysdate-1,'D') from dual;  //返回星期

To_number

select to_number('13')+to_number('14') from dual; //27

To_date

select to_date('20100908','yyyyMMdd')from dual;  // 10-2月 -09

通用函数

select nvl(comm,0) from emp; //虑空函数，如果为空补0

CASE 表达式
select empno, ename, sal,
case deptno
when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
else '未知部门'  
end  部门
from emp;

========================

select empno, ename, sal,
decode(deptno, 10, '财务部',
20, '研发部',
30, '销售部',
'未知部门')  
部门
from emp;

=========================

结果：

 EMPNO ENAME             SAL 部门
------ ---------- ---------- ------
  7369 SMITH             800 研发部
  7499 ALLEN            1600 销售部
  7521 WARD             1250 销售部

===============================

分组函数：

count(*)   如果没有数据，返回值不为null,而是0.

Avg() max() min() sum()

滤空函数的使用

select avg(nvl(comm, 0)) from emp;

GROUP BY 子句
出现在 SELECT 列表中的字段或者出现在 order by  后面的字段，如果不是包含在分组函数
中，那么该字段必须同时在 GROUP BY 子句中出现。
包含在  GROUP BY 子句中的字段则不必须出现在 SELECT 列表中。
可使用 where 字句限定查询条件
可使用 Order by 子句指定排序方式
如果没有 GROUP BY 子句，SELECT 列表中不允许出现字
段（单行函数）与分组函数混用的情况。

不允许在  WHERE  子句中使用分组函数。
select deptno, avg(sal)
from emp
where avg(sal) > 2000;
group by deptno;

HAVING 子句

select deptno, job, avg(sal)
from emp
where hiredate >= to_date('1981-05-01','yyyy-mm-dd')
group by deptno,job
having avg(sal) > 1200
order by deptno,job;

分组函数嵌套

select max(avg(sal))
from emp
group by deptno;

左外连接

select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid=s1.sid(+);  左外连接的+在右边

select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno;

右外联接（Right Outer Join）
select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid(+)=s1.sid;

select empno,ename,dname from emp right outer join dept on emp.deptno = dept.deptno;

集合操作
·   UNION：并集，所有的内容都查询，重复的显示一次
·  UNION  ALL：并集，所有的内容都显示，包括重复的
·  INTERSECT：交集：只显示重复的
·  MINUS：差集：只显示对方没有的（跟顺序是有关系的）
首先建立一张只包含 20 部门员工信息的表：
CREATE  TABLE  emp20  AS  SELECT  *  FROM  emp  WHERE  deptno=20  ;
1、  验证 UNION 及 UNION  ALL
UNION：SELECT  *  FROM  emp  UNION  SELECT  *  FROM  emp20  ;
使用此语句重复的内容不再显示了
UNION  ALL：SELECT  *  FROM  emp  UNION  ALL  SELECT  *  FROM  emp20  ;
重复的内容依然显示
2、  验证 INTERSECT
SELECT  *  FROM  emp  INTERSECT  SELECT  *  FROM  emp20  ;
只显示了两个表中彼此重复的记录。
3、  MINUS：返回差异的记录
SELECT  *  FROM  emp  MINUS  SELECT  *  FROM  emp20  ;
只显示了两张表中的不同记录
Oracle 教程
-24-
满链接也可以用以下的方式来表示：
select  t1.id,t2.id  from  table1  t1,table  t2  where  t1.id=t2.id(+)
union
select  t1.id,t2.id  from  table1  t1,table  t2  where  t1.id(+)=t2.id

分页查询

select * from (select rownum no,e.* from
(select * from emp order by sal desc) e where rownum<=5 ) where no>=3;