oracle笔记

这是以前学习ORACLE的时候做的笔记，现在拿出来供以后参考：

 

讲到数据库的时候就要讲到数据库三范式：

第一范式：
    每一个表都应该有主键；
    列不可分；具体问题具体分析；

第二范式：当表中有多个主键时，其他数据不能依赖于部分主键，否则这样的表会产生数据冗余；

第三范式：不能存在传递依赖；也就是说其他字段的值不能依赖于非主键；

 

 

1.select sal*12 anuualsal from emp    该语句是用anuual指定别名，记住anuual与别名中间不能有空格，如果别名中有特殊字符（如中文、空格等）则需用双引号把anuual和别名括起来！

2.任何含有空值的表达式最后的计算结果都是空值

3.用||来连接两个字符串，Oracle里面的字符串是用单引号括起来的，如果所要表示的字符串里面本身就有一个单引号，那么需要在字符串中有单引号的那个位置再加一个单引号！

4.在select语句中可以用distinct去除某一字段重复的行，如果distinct后接多个字段，则表示当多个字段都相同的时候只显示其中的一条记录，如select distinc deptno，job from emp 则表示所选的记录中当deptno和job的组合相同的时候只显示其中的一条记录；

5.当需要选择某一字段中记录为空的时候条件里面应选isnull，而不是判断某一字段值是否为空，如select ename，sal，comm from emp where comm is null；是正确的，而select ename，sal，comm from emp where comm=null；则是错误的；

6.百分号表示一个或多个字符，下划线表示一个字符，当所要select的字段值中含有通配符的时候就要使用转义字符了，默认的转义字符反斜杠，也可以使用escape自己指定转义字符，如 select ename from emp where ename like '%$%%' escape '$' ，则表示select名字里面含有一个%的人的记录，其中的$是转义字符；

7.函数：to_char()转换为某一格式，其中9代表一位数字，0也代表一位数字，但是当使用9时如果小数点前面写了9的那一位没有数字时则该位不显示，而如果使用的是0 的话则如果小数点前面写了0的那一位如果没有数字则该位显示0；L代表本地货币符号；to_char（）使用的格式为to_char(字段，‘格式’)；如select to_char(hiredate,‘YYYY-MM-DD HH-MI-SS')这样的时间格式是十二进制的，如果在HH后面加上24就转为二十四进制的了！
 to_date()函数将字符串转换为时间格式，其使用格式为to_date('时间'，'所要转换的格式’）。都需用单引号括起来；
 to_number()将特定格式的字符串转换为数字，其使用格式为to_number('特定格式的字符串'，‘所要转换的格式，）如to_number('$1,250.00','$9,999.99');
 函数nvl（aaa，0）的含义是当aaa的值为空时就用0代替，当不为空时则为aaa，aaa可以是一个字段；

8.在等值连接中还可以使用join 表 using（字段名）来连接两个表；也可以使用join 表 on 条件；using不推荐使用；

9.表连接与子查询示例：
--求部门中哪些人的薪水最高
select ename sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno)

--求部门平均薪水的等级
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

--求部门平均的薪水等级
select deptno,avg(grade) from
(select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno

--雇员中哪些人是经理人
select ename from emp where empno in (select distinct mgr from emp);

--不准用组函数，求薪水的最高值（面试题）
select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));

--求平均薪水最高的部门的部门编号
select deptno from
 (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal =
 (select max(avg_sal) from
   (select avg(sal) avg_sal,deptno from emp group by deptno))

--求平均薪水最高的部门的部门名称
select dname from dept where deptno =
(
 select deptno from
 (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal =
 (select max(avg_sal) from
   (select avg(sal) avg_sal,deptno from emp group by deptno)))

--求平均薪水的等级最低的部门的部门名称
select dname from dept where deptno = (select deptno from (select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) e join salgrade s on (e.avg_sal between s.losal and s.hisal)) where grade = (select min(grade) from (select deptno,avg_sal,grade from (select avg(sal) avg_sal,deptno from emp group by deptno) e join salgrade s on (e.avg_sal between s.losal and s.hisal))));(嵌套好厉害！)

--求部门经理人中平均薪水最低的部门名称

--求比普通员工的最高薪水还要高的经理人的名称
select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal>(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));

--求薪水最高的前五名雇员
select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=5;

--求薪水最高的第6到第10名雇员
select ename,sal from
(
 select ename,sal,rownum r from
  (select ename,sal from emp order by sal desc)
) where r>=6 and r<=10;
--求最后入职的5个人

10.rownum表示的是某一行在此表中的行号，第一行为一，但rounum只能与<=或<号一起用，如果要查找某一行或大于某一行的记录则需要使用嵌套查询。例如，如果要查找某一表中的前五行的记录则可以这样表示，select * from emp where rownum<=5;

 

sequence是用来自动产生一个顺序数的
create sequence seq;
select seq.nextval;

oracle 中有一张空表dual；

在Oracle中“--”可以注释一行