Oracle学习：常用命令集：SELECT,DML

1、使用dual表：

select 2*3 from dual;

 

2、使用｜｜符号作字符串连接

select ename || 'dddddd' from emp;

 

3、如果字符串里有单引号则用两个单引号

使用select ename || 'sfss''sfdd' from emp;

 

4、使用distinct去除重复的元组，注意MSSQL需要括号

select distinct deptno from emp;

 

5、修饰多个字段时去除组合起来的重复元组

select distinct deptno,job from emp;

 

6、使用rownum指定返回的行数

 select ename,hiredate from emp where rownum<2;

结果：

ENAME      HIREDATE
---------- --------------
SMITH      17-12月-80

 

注意oracle默认的日期类型：上面结果可以看出默认日期格式输出：17-12月-80

select ename,hiredate from emp where hiredate>'15-12月-80';

注意条件。。

 

7、使用union查询两个结果集，如果结果集中有重复的则只保留其中一个。

 select deptno from emp union select deptno from dept;

结果

   DEPTNO
---------
       10
       20
       30
       40

 

8、使用union all查询两个结果集中所有行。

select deptno from emp union all select deptno from dept;

在emp表中有14行，而dept表中有4行，结果返回了18行。

 

9、使用转义字符：

如select * from emp where ename like '%\%%';

第一个和第三个%表示有0个或多个字符，而我们要查询字段中含有%的记录，那么中间的%就必须用\进行转义

 

可以使用
 select ename from emp where ename like '%$%%' escape '$';

上面的$符号经过escape '$'声明之后就可以使用$进行转义了。也就是说使用$代替了\

 

10、使用函数

大小写转换：

select lower(ename) from emp;

select upper(ename) from emp;

字符串截取：

select substr(ename,1,3) from emp;

从第一个字母开始，共3个字母被截取如。SIMP截取后变成SIM

从ASCII码变成字符

select chr(65) from dual; 返回字母A，

 

从字符变成ASCII

select ascii('a') from dual; 返回97

 

 

四舍五入：

select round(23.62) from dual;    --24
select round(23.652,2) from dual;   --23.65
select round(23.65,0) from dual;   -- 24
select round(23.65,-1) from dual; --20
select round(26.4434,-1) from dual; --30

Oracle中round() 函数与trunc()函数的比较

 

 

 to_char:

 数据转换：

select sal from emp;   --1600.00
select to_char(sal,'$99,999.9999') from emp;  --  $1,600.0000

select to_char(sal,'L99,999.9999') from emp;  --  ￥1,600.0000

select to_char(sal,'L00,000.0000') from emp; --   ￥01,600.0000

99,999.9999中的9就代表一个数字位，对于整数位如果该位没有数字则不显示，但小数位如果没有这个位数则显示0

而'L00,000.0000'中的0代表一个数字位，不同的是如果位数不足则都用0补上。

 

时间转换：

首先说明一下：

在命令行的Sqlplus得到的系统时间：

select sysdate from dual;

结果为：
SYSDATE
--------------
07-6月 -10

而在P/L SQL DEVELOPER中得到的却是

2010-6-7 17:19:09

那么不管怎么样，使用时间时我们要进行转换：

如下：

select to_char(sysdate,'yyyy-MM-dd HH:mm:ss') from dual; --2010-06-07 05:06:48
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;  --2010-06-07 17:06:41
注意区分24小时格式。

to_date

转换成日期格式进行时间比较：

select * from emp where hiredate> to_date('1985-06-07 05:06:48','YYYY-MM-DD HH:MI:SS');

 

to_number

转换成特殊格式进行比较

select sal from emp  where sal > to_number('$1,250.00','$9,999.99');

select to_number('$1,250.00','$9,999.99') from dual;   --1250

select sal from emp  where sal > to_number('$1,250.00','$9,999.99');

 

NVL

在emp表中comm字段有的值为空值，那么计算的时候所有与它相运算的结果值都为空。如

select sal*2+comm from emp;

 

SAL*2+COMM
----------

      3500
      3000

      3900

 

      3000

 

那么这样的计算显然不是我们想要的结果。

select sal*2+NVL(comm,0) from emp;

结果如下：

SAL*2+NVL(COMM,0)
-----------------
             1600
             3500
             3000
             5950
             3900
             5700
             4900
             6000
            10000
             3000
             2200

组函数，统计函数--读取多条记录，但只返回一个结果

select max(sal) from emp;    --5000
select min(sal) from emp;   --800

select avg(sal) from emp;   --2073.21428571429
select to_char(avg(sal),'9999,9999.9999') from emp; -- 2073.2143

select sum(sal) from emp; --29025

select count(*) from emp;

select count(distinct deptno) from emp;    --3个
select count(comm) from emp;   --计算非空的行数

11.Group by

分组求每个部门的平均值：

select deptno,to_char(avg(sal),'999,9999.9999') from emp group by deptno

 

根据deptno与job的不同组合求平均值：

select deptno,job,to_char(avg(sal),'999,9999.9999') from emp group by deptno,job

共有9种不同的组合。

 

12.子查询

查询员工表中薪水最多的人的信息

select * from emp where sal = (select max(sal) from emp )

 

 

13、having 子句

对分组进行条件查询

select avg(sal) from emp group by deptno having avg(sal)>2000

 

14.子查询

查询各部门中薪水最高的人的信息

错误写法：select * from emp where sal in (select max(sal) from emp group by deptno)

正确写法如下（两个查询结果一样）：

select e.* from emp e,(select max(sal) sal,deptno from emp group by deptno) m where e.deptno = m.deptno and e.sal = m.sal
select e.* from emp e join (select max(sal) sal,deptno from emp group by deptno) m on (e.deptno = m.deptno and e.sal = m.sal);

 

查询每个部门平均薪水的等级

员工表emp

薪水等级表salgrade

GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

 

select a.deptno,a.sal,b.grade from (select deptno, avg(sal) sal from emp group by deptno ) a join salgrade b on a.sal between b.losal and b.hisal

结果：

    DEPTNO SAL GRADE
1 30 1566.66666666667 3
2 20 2175 4
3 10 2916.66666666667 4

 

查询每个人的薪水等级

select a.*,b.grade from emp a join salgrade b on a.sal between b.losal and b.hisal

查询每个部门的平均薪水等级

先查每个人的薪水等级，再按部门分组求平均值

select avg(c.grade) from (select a.*,b.grade from emp a join salgrade b on a.sal between b.losal and b.hisal) c
group by c.deptno

 

连接已表

如求emp表中员工的管理者姓名

EMPNO ENAME        MGR
----- ---------- -----
 7369 SMITH       7902
 7499 ALLEN       7698
 7521 WARD        7698
 7566 JONES       7839
 7654 MARTIN      7698
 7698 BLAKE       7839
 7782 CLARK       7839
 7788 SCOTT       7566
 7839 KING      
 7844 TURNER      7698
 7876 ADAMS       7788

 7902 FORD        7566

员工7369 的管理者7902的姓名是FORD

以下通过连接自己查询得到

select a.empno,a.ename,a.mgr,b.empno,b.ename from emp a join emp b on a.mgr = b.empno

或者

select a.ename,b.ename from emp a ,emp b where a.mgr=b.empno;

 

14.面试题

不使用组函数，查询emp表中sal的最大值。

使用自我连接。

select sal from emp where sal not in (select distinct a.sal sal from emp a,emp b where a.sal<b.sal)

上面实际上使用select distinct a.sal sal from emp a,emp b where a.sal<b.sal查询出所有非最大值的记录。

 

求平均薪水最大值的部门编号：

select deptno,sal from (select avg(sal) sal,deptno from emp group by deptno) where sal = (select max(sal) from (select avg(sal) sal,deptno from emp group by deptno))

 

求平均薪水等级最低的部门的部门名称：

select d.dname,d.deptno from dept d join
(
select m.deptno,m.grade from (select s.grade ,b.sal,b.deptno from salgrade s join (select avg(sal) sal,deptno from emp group by deptno) b on b.sal between s.losal and s.hisal
) m join
(select min(t.grade) mingrade from (select s.grade ,b.sal,b.deptno from salgrade s join (select avg(sal) sal,deptno from emp group by deptno) b on b.sal between s.losal and s.hisal) t) n
on m.grade= n.mingrade
) y on d.deptno = y.deptno

 

 求比普通员工最多薪水还高的管理员名称

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))

 

 

求薪水最高的第6到第10名员工

一定要注意在使用order by 的时候rownum的问题

关于rownum请看文章：http://quicker.iteye.com/blog/687821

select ename from emp where rownum<5 order by ename

返回结果：

ALLEN
JONES
SMITH
WARD

而实际上：我们使用select ename from emp order by ename

前五行是：

ADAMS
ALLEN
BLAKE
CLARK
FORD

显然使用rownum<5是不对的。必须用子查询：

 下面列出不排序和排序后的两种取前6到10条数据的语句：

select * from (
select rownum m,e.* from emp e where rownum<=10
) where m>=6

select * from (
select rownum m,e.* from (select * from emp order by ename) e where rownum<=10
) where m>=6

 

那么取薪水最高的6-10名员工：

select * from (
select rownum m,e.* from (select * from emp order by sal desc) e where rownum<=10
) where m>=6

 

在排序字段上加上rowid避免性能缺陷

 

select * from (
select rownum m,e.* from (select * from emp order by sal desc, rowid) e where rownum<=10
) where m>=6

 

 

 

15、DML

备份一张表：

create table emp2 as select * from emp

 

为对应表增加主键与外键

 alter table S2
  add constraint pk_ck primary key (CNO);
alter table S2
  add constraint sk_sk foreign key (SNO)
  references s1 (SNO);
 

增加字段及修改字段

alter table s2 add cname varchar2(200)
  alter table s2 modify cname varchar2(100)

 

创建索引：

create unique index pk_i on S2 (sno);

drop index pk_i

 

视图：

create or replace view l_view as
  select s1.sno,s1.age,s2.sname,s2.cno
      from s1,s2

  drop view l_view

 

创建序列：

-- Create sequence
create sequence seq
start with 1
increment by 1;