Oracle——SQL语言（二）

一、基本介绍

1、分类

• 数据查询语言DQL：select
• 数据操纵语言DML：insert，update，delete
• 事务控制语言TCL：commit，rollback
• 数据控制语言DCL：grant，revoke
• 数据定义语言DDL：create，alter

2、数据类型

• 字符串

   char，定长

   varchar2，变长

• 数字

  number(m,n)

• 日期

  date

  timestamp，时间戳

3、运算符

Oracle中常见的运算符如下：

操作符	含义
=	等于
:=	赋值
>	大于
>=	大于、等于
<	小于
<=	小于、等于
<>【或者!=】	不等于
AND	逻辑并
OR	逻辑或
NOT	逻辑否
BETWEEN ...AND...	在两个值之间（包含边界）
IN(set)	等于值列表中的一个
LIKE	模糊查询
IS NULL	空值

他们的优先级如下：

 

 注意：

1.赋值运算符为":="

2."between  and"要注意两点：含有边界 ；小值在前 大值在后

3.在使用in时，如果集合中含有null，不能使用not in， 但可以使用in(null可以当为一个值，但是，不是null不可以)

4、字符的连接

如连接"Hello"和" World"，

select concat('Hello','  World') from dual;

在如查询员工信息： ***的薪水是****，SQL为：

select ename||'的薪水是'||sal 信息 from emp;

5、日期的处理

        oracle默认的日期格式为"DD-MON-RR",例如查询“95年11月8日”入职的员工，

select * from emp where hiredate='08-11月-95';

如果想修改Oracle的日期默认格式，可以

alter session|system set NLS_DATE_FORMAT="yyyy-mm-zz";

那么查询日期将变成"1995-11-8"，注意如果是session那么该日期格式只在当前登录用户有效，如果是system将会在所有用户生效。

二、基本操作

1、索引

        创建索引是为了提高检索速度，在主键或唯一键中，Oracle会自动建立一个唯一的索引，创建索引的语法为：

CREATE INDEX index
ON table (column [，column ] );

index ：索引的名称

例：

create index emp_idx
on emp ( empname );

删除索引：

drop INDEX index;

说明：

以下情况可以创建索引：

• 列中数据值分布范围很广
• 列经常在WHERE子句或连接条件中出现
• 表经常被访问而且数据量很大,访问的数据大概占总数据量的2%到4%

以下情况不要创建索引： 

• 表很小
• 列不经常作为连接条件或出现在wHERE子句中
• 查询的数据大于2%到4%
• 表经常更新

2、序列

       oracle没有自增列，使用序列sequence，来完成自增的功能。序列就是一个增长数字，只能前进不能后退

附加：

• MySQL中有自增列，increment
• SQLServer自增列，identity

首先创建序列：

create sequence myseq
  minvalue 1
  start with 1
  nomaxvalue
  increment by 1
  nocycle
  cache 30--表示一次生成30个

使用序列，第一次使用，必须先.nextval生成一个值

select myseq.nextval from dual

如果想查看当前序列的值则为：

select myseq.currval from dual

如果想修改序列：

alter sequence myseq
  maxvalue 9999999
  minvalue 1

删除序列：

drop sequence myseq

举例，使用序列完成自增列的实现：

create table student(
  id number primary key,
  name varchar2(20),
  gender char(2),
  age number(3),
  enrolldate date,
  address varchar2(500) default '地址不详'
)
--
insert into student values(myseq.nextval,'张三','男',22,to_date('2013-09-01','YYYY-MM-DD'),'河南新郑')
insert into t_class values(myseq.nextval,'java一班')

3、集合运算

        Oracle中的集合运算分为3种，为并集、交集、差集，我们以集合A和B举例，其中并集又有两种分别为union和union all，union表示A+B，并去掉重复后的内容（两个重复的内容只保留一份），而union all则返回所有值（重复的都显示），而交集为interest，表示A和B之间的，差集，则为minus，表示A-B。案例如下：

 

案例，查询10和20号部门的员工：

select * from emp where deptno=10
 union
 select * from emp where deptno=20;

案例，查询没有员工的部门信息：

select * from dept where deptno in(
  select deptno from dept--所有的部门编号
  minus
  select distinct deptno from emp--员工表中的部门编号（去重）
)

案例，利用union插入多条数据：

insert into dept
  select 50,'公关部','中国台湾' from dual
  union select 60,'研发部','中国西安' from dual
  union select 70,'培训部','中国郑州' from dual

注意：

• 参与运算的各个集合必须列数相同且类型一致，如果查询的列数不齐，需要自己补齐，如下：

select deptno,job,sum(sal) from emp group by deptno,job
  union
  select deptno,to_char(null),sum(sal) from emp group by deptno
  union
  select to_number(null),to_char(null),sum(sal) from emp;

• 采用第一个集合作为最后的表头
• order by 永远在最后

4、连接查询

         在多表查询中将会产生笛卡尔集，办笛卡尔集会在下面条件下产生

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

在实际运行环境下，应避免使用全笛卡尔全集 。为了避免笛卡尔集，可以在WHERE加入有效的连接条件，连接的类型有如下几种：

orade的连接	SQL99的连接
等值连接	Cross joins
不等值连接	Natural joins
外连接	Using clause
自连接	Full or two sided outer joins

说明：以上只是规范不同，功能相同。

1.等值连接

查询员工信息： 员工号 姓名 月薪 部门名称：

select e.empno,e.ename,e.sal,d.dname
 from emp e,dept d
 where e.deptno=d.deptno;

2.不等值连接

查询员工信息： 员工号 姓名 月薪 工资级别

select e.empno,e.ename,e.sal,s.grade
 from emp e,salgrade s
 where e.sal between s.losal and s.hisal;

3.外连接

      在查询中 对于某些不成立的记录，任然希望包含在最后的结果中，此时可以使用外连接，如按部门统计员工人数，假如此时某个部门中没有对应的员工数据，那么在普通查询中，将会无法显示该部门，如果要显示该部门信息可以采用左外连接。即：

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
  from emp e,dept d
  where e.deptno(+)=d.deptno
  group by d.deptno,d.dname;

说明：

• 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含

    写法:where e.deptno=d.deptno(+)

• 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含

    写法: where e.deptno(+)=d.deptno

4.自连接

      自连接，即通过表的别名，将同一张表视为多张表，如查询员工信息，员工姓名、老板姓名

 select e.ename 员工姓名,b.ename 老板姓名
  from emp e,emp b
  where e.mgr=b.empno;

但是这样导致，一张表的n个数据在查询时变成了n*n（笛卡儿积）个数据，效率低下，所以自连接不适合操作大表，而如果想解决该问题可以采用层次查询，还是上面的问题，SQL可以写为如下：

 select level,empno,ename,mgr
  from emp
  connect by prior empno=mgr
  start with mgr is null
  order by 1;

5.案例

案例，查询工资大于2000的员工姓名，部门名称，工资：

select ename,dname,sal from emp inner join dept on emp.deptno=dept.deptno
  where sal>2000

或者

select ename,dname,sal from emp,dept
  where emp.deptno=dept.deptno and sal>2000

案例，查询每个部门下的员工姓名和工资（没有部门的员工不显示）：

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

或者

select dname,ename,sal from dept,emp
  where emp.deptno(+)=dept.deptno

5、子查询

1.入门

       子查询所要解决的问题就是不能一步求解查询条件，如查询工资比SCOTT高的员工信息，正常情况下可以分为两步进行完成，首先查询SCOTT的工资

select sal from emp where ename='SCOTT';

如果得到的结果为300，之后再查询工资比3000高的员工

select * from emp where sal > 3000;

而如果采用子查询则可以：

select *
  from emp
  where sal > (select sal
               from emp
               where ename='SCOTT');

子查询需要注意的问题：

• 可以在where  select having from后面 都可以使用子查询，但不可以在group by后面使用子查询
• 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
• 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
• 一般先执行子查询，再执行主查询；但相关子查询例外
• 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

2.子查询的使用

• 单行子查询，子查询的结果为一行，使用=,>,<
• 多行子查询，子查询的结果为多行，使用in
• all，所有
• any，任意一个

查询部门名称是SALES和ACCOUNTING的员工：

select *
  from emp
  where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

查询工资比30号部门任意一个员工高的员工信息：

select *
  from emp
  where sal > any (select sal from emp where deptno=30);

查询工资比30号部门所有员工高的员工信息：

select *
  from emp
  where sal > all (select sal from emp where deptno=30);

查询不是老板的员工：

select *
  from emp
  where empno not in (select mgr from emp);

6、伪劣和分页

• rowid，为一行都会自动创建一个固定的rowid，物理地址编号
• rownum，为每次查询的每一行都会自动创建一个临时的编号

select rowid,ename,sal from emp
select rowid,ename,sal from emp where sal<2000
select rownum,ename,sal from emp
select rownum,ename,sal from emp where sal<2000

说明：rownum只能使用 < <=; 不能使用> >=

 

案例，查询emp表，14行，5行为1页，显示第2页(6~10行)：

select * from (select rownum R,ename,job,sal from emp where rownum<=10) where R>=6

起始行=页大小*(页码-1)+1

结束行=起始行+页大小-1