oracle 视频笔记1(select)

下面是用scott用户测试的。

如果不知道当前用户是什么用户,可以输入: show user ,这样它就显示出当前用户是什么用户了.
如果想编辑已经输入到sqlplus中的代码,可以输入“ed”，然后回车就能弹出一个记事本，记事本里面有上一步输入的代码,修改代码并保存后关闭记事本,最后在sqlplus中输入“/”并击回车就执行了。

1.不等于号是:<>
    如:select ename, sal from emp where deptno <> 10;

2.SQL中的字符串大小写不一样
    如:select ename ,sal from emp where ename = 'CLASD';
 与 select ename ,sal from emp where ename = 'clasd'; 
 是不一样的.

3.between 与 >=和<= 
    如: select ename, sal from emp where sal between 800 and 1500; 的意思与
  select ename, sal from emp where sal >= 800 and sal <= 1500; 一样 

4.把空值选出来
    如: select ename, sal, comm from emp where comm is null;

5.把非空值选出来
    如: select ename, sal, comm from emp where comm is not null;

6.in的用法
    如:select ename, sal, comm from emp where sal in (800, 1500, 2000); 意思是把sal等于800       或者1500或者2000的 ename、sal、comm 拿出来。
     select ename, sal, comm from emp where sal not in (800, 1500, 2000); 意思是把sal不等
     800或者1500或者2000的ename、sl、comm 拿出来。

7.日期:1981年2月20日以后的表示为: select ename, sal, hiredate from emp where hiredate >   '20-2月-81'; 或:select ename, sal, hiredate from emp where hiredate > '20-2月-1981';
   先转换格式再取日期与时间:select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

8.或者的连接用"or"
    如:select ename, sal, comm from emp where sal>1000 or deptno=10;

9.模糊查找:like表示模糊查找,"%"表示零个或多个字母,"_"代表一个字母.
    如:select ename from emp where ename like '%ALL%'; 表示查找符合ALL前面和后面有零个或多      个字母的ename;
       select ename from emp where ename like '_A%';表示查找符合每二个字母是A的ename;
  查找带有%的字符串,用转义字符"\",如:select ename from emp where ename like '%\%%';表示查找   带有"%"的字符串。也可以自己设置    转义字符，如：select ename from emp where ename like '%$%' escape '$'; 表示用$作为转义字  符.

10.排序:order by 。
   降序：desc， 如：select * from dept order by deptno desc;
   升序：asc， 默认情况下是升序，如：select empno, ename from emp order by empno asc;
         或：select empno, ename from emp；
   升降综合：select ename, sal, deptno from emp order by deptno asc, ename desc;表示先按   deptno的升序,如果有相同的deptno,再按照ename的降序排序。

11.select lower(ename) from emp; 表示取出的ename的字段的字母都是小写字母。

12.取子字符串(substr)。如：select substr(ename, 2, 3) from emp;表示取出ename中的从每2个字母开始的3个字母的字符串。 

13.将chr形转换成ascii码。如：select chr(65) from dual; 表示将65转换成ascii码（A）。
   将ascii形转换成chr码。如：select ascii('A') from dual; 表示将ascii码（A）转换成65。  

14.select round(25.562) from dual;表示取到25.562的整数部分
   select round(25.562, 2) from dual;表示取到25.562的两位小数
   select round(25.562, -1) from dual;表示取到25.562的十位

15.to_char：
 如：select to_char(sysdate, 'YYYY-HH-DD HH:MI:SS') from dual; 表示将sysdate时间格式转化为YYYY-HH-DD HH:MI:SS
 select to_char(sysdate, 'YYYY-HH-DD HH24:MI:SS') from dual; 表示将sysdate时间格式转化为YYYY-HH-DD HH:MI:SS 其中时间是24小时制

16.select ename, sal*12 + comm from emp;这句SQL语句中,如果comm是空值,则加起来的结果也是空值 。为了避免这种情况,可以select ename, sal*12 + nvl(comm, 0) from emp; nvl(comm, 0)的意思是如果comm是空值,则用0来取代它.

17.求最大值(max)。如：select max(sal) from emp; 表示从emp中求出最大的sal.

18.求最小值(min)。如：select min(sal) from emp; 表示从emp中求出最小的sal.

19.求平均值(avg)。如：select avg(sal) from emp; 表示从emp中求出平均的sal.

20.精确到小数点后两位。如：select to_char(avg(sal), '99999999.99') from emp;

21.四舍五入到小数点后两位。如：select round(avg(sal), 2) from emp;

22.求和。如：select sum(sal) from emp; 表示sal的和为sum(sal)。

23.求出表中一共有多少条记录。
 如：select count(*) from emp; 表示emp表中一共有多少条记录。
     select count(*) from emp where deptno=10; 表示求出emp表中deptno=10的记录一共有多少条。
 select count(*) from emp; 与select count(comm) from emp; 是不一样的。前一个*表示所有的记录；后面一个表示comm的记录。
 select count(distinct deptno) from emp; 表示除去重复的deptno后一共有多少条记录。

24.分组函数（group)。
 如：select deptno, avg(sal) from emp group by deptno; 表示按deptno分组显示sal的平均值。
     select deptno, job, max(sal) from emp group by deptno, job; 表示按照deptno和job的组合分组显示deptno, job, max(sal) 。
        select ename, max(sal) from emp;这句SQL是不对的，因为如果最大的sal有不同的ename,那么就会出错（由于输出是只有一条数据的）；所以要改成select ename from emp where sal =  (select max(sal) from emp);
     
25.having。where是对单条数据进行限制的，如果要限制查询出来后的多条数据，一般用having。如果一条SQL中有where和其他限制条件，那么先执行where限制条件。
 如：select avg(sal), deptno from emp group by deptno having avg(sal) >2000; 表示通过deptno取出acv(sal)和deptno后，再从取出的数据中查询出avg(sal) 大于2000的数据。

26.限制词的执行顺序：where --> group by--> having --> order by 
 如：select deptno, avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc; 表示按组deptno 取出sal > 1200 的deptno, avg(sal)，然后选出avg(sal)大于1500的deptno, avg(sal)，最后按agv(sal)的倒序排列。

27.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); 表示取出各组（deptno)中sal最大的ename和sal。

28.自连接,给一张表起两个别名,把一张表当成两张表用。
 如：select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

29.左外连接(left (outer)) outer可以省略。
 如：select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.deptno);
表示把左边e1的没有符合连接的数据也拿出来（全部数据）。

30.右外连接（right (outer)) outer可以省略。
 如：select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno); 表示把右边d的没有符合连接的数据也拿出来（全部数据）。

31.全外连接(full)。
 如：select ename, dname from emp e full join dept d on (e.deptno = d.deptno); 表示把表e表d的两个没有正确连接的数据全部取出来。

32.部门平均薪水的等级：select deptno, avg_sal, grade from (select avg(sal) avg_sal, deptno
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, grade, sal from emp join
salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno

33.三个表建立关系。
 如：select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%'；

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

35.不用组函数求最高薪水
 select distinct sal from emp where sal not in (select distinct e1.sal from
emp e1 join emp e2 on (e1.sal < e2.sal));

36.求出平均薪水最高的部门编号
 写法一：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 deptno from
  (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal = 
  (select max(avg(sal)) from  emp group by deptno);
   

37.求出平均薪水最高的部门名称
 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)
   )
 )

38.平均薪水的等级最低的部门名称
 方法一：select  dname, t1.deptno, grade, avg_sal from
  (
  select deptno, avg_sal, grade from
    (select deptno deptno, avg(sal) avg_sal from emp group by deptno) t 
    join salgrade s on 
    (t.avg_sal between s.losal and s.hisal)
  ) t1
  join dept on (t1.deptno = dept.deptno) 
  where t1.grade = 
  (
   select min(grade) from
   (
    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 dname from dept where deptno =
 (
  select deptno from 
   (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)
    ) 
   where avg_sal = 
    (select min(avg_sal) from
    (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)
     )
   ) 
 )
 
39.使用视图简化SQL
 将下面这段代码简化:
 平均薪水的等级最低的部门名称
 方法一：select  dname, t1.deptno, grade, avg_sal from
  (
  select deptno, avg_sal, grade from
    (select deptno deptno, avg(sal) avg_sal from emp group by deptno) t 
    join salgrade s on 
    (t.avg_sal between s.losal and s.hisal)
  ) t1
  join dept on (t1.deptno = dept.deptno) 
  where t1.grade = 
  (
   select min(grade) from
   (
    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)
   )
  )
 简化为:授权
  conn sys/520419 as sysdba
  grant create table, create view to scott;
  conn scott/tiger
  创作视图
  create view v$_dept_avg_sal_info as
   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  dname, t1.deptno, grade, avg_sal from
   v$_dept_avg_sal_info t1
   join dept on (t1.deptno = dept.deptno) 
   where t1.grade = 
   (
    select min(grade) from v$_dept_avg_sal_info
   )

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

41.rownum,其实每个表中都有一个隐藏的列，是用来标记行号的，但是它只能与<、<=在一起用，不能与>、>=、=在一起用。
 如：select empno, ename from emp where rownum <=5;

42.求薪水最高的前5名雇员。
 方法一：select ename, sal from 
    (select ename, sal from emp order by sal desc) 
  where rownum <= 5
 方法二：select ename, sal, r from 
  (
   select ename, sal, rownum r from 
     (select ename, sal from emp order by sal desc) 
  )
  where r <=5

43.求薪水最高的第6到每10名雇员（重点掌握）。
 select ename, sal, r from 
 (
  select ename, sal, rownum r from 
    (select ename, sal from emp order by sal desc) 
 )
 where r >=6 and r<=10



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/l_rui_ci/archive/2009/03/10/3977054.aspx