Oracle——SQL语言（二）

客官°小女子只卖身不卖艺 2024-04-19 09:41 0阅读 0赞

# 一、基本介绍 #

## 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中常见的运算符如下：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:middle;">操作符</td> 
   <td style="vertical-align:middle;">含义</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">=</td> 
   <td style="vertical-align:middle;">等于</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">:=</td> 
   <td style="vertical-align:middle;">赋值</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">&gt;</td> 
   <td style="vertical-align:middle;">大于</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">&gt;=</td> 
   <td style="vertical-align:middle;">大于、等于</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">&lt;</td> 
   <td style="vertical-align:middle;">小于</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">&lt;=</td> 
   <td style="vertical-align:middle;">小于、等于</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;【或者!=】</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>AND</td> 
   <td>逻辑并</td> 
  </tr> 
  <tr> 
   <td>OR</td> 
   <td>逻辑或</td> 
  </tr> 
  <tr> 
   <td>NOT</td> 
   <td>逻辑否</td> 
  </tr> 
  <tr> 
   <td>BETWEEN ...AND...</td> 
   <td>在两个值之间（包含边界）</td> 
  </tr> 
  <tr> 
   <td>IN(set)</td> 
   <td>等于值列表中的一个</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>模糊查询</td> 
  </tr> 
  <tr> 
   <td>IS NULL</td> 
   <td>空值</td> 
  </tr> 
 </tbody> 
</table>

他们的优先级如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMTcyMTMz_size_16_color_FFFFFF_t_70][]

注意：

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加入有效的连接条件，连接的类型有如下几种：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:middle;">orade的连接</td> 
   <td style="vertical-align:middle;">SQL99的连接</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">等值连接</td> 
   <td style="vertical-align:middle;">Cross&nbsp;joins</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">不等值连接</td> 
   <td style="vertical-align:middle;">Natural joins</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">外连接</td> 
   <td style="vertical-align:middle;">Using&nbsp;clause</td> 
  </tr> 
  <tr> 
   <td style="vertical-align:middle;">自连接</td> 
   <td style="vertical-align:middle;">Full&nbsp;or&nbsp;two&nbsp;sided&nbsp;outer&nbsp;joins</td> 
  </tr> 
 </tbody> 
</table>

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMTcyMTMz_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/c5217b0cea8d48b1a9dd4562321649b0.png