oracle

Oracle第一天
v3.1
整体安排（4天）
第一天：Oracle的安装配置（服务端和客户端），SQL增强（单表查询）。SQL增强（子查询、伪列-分页）索引
第二天：plsql编程入门, 存储过程,数据库备份和还原

1.Oracle概述
1.1.什么是数据库
数据库的英文是DataBase，简称DB，顾名思义，就是数据（包括数字、文字、图像、声音、视频等）存放的地方。因此，数据库的作用只要就是用来存储数据的。

1.2.关系型数据库管理系统（RDBMS）
RDBMS即关系数据库管理系统(Relational Database Management System)，是将数据组织为相关的行和列的系统，而管理关系数据库的计算机软件就是关系数据库管理系统，常用的数据库软件有Oracle，Microsoft SQL Server，DB2，Sybase，Informix，MySQL，ProgresSQL……等。
关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

1.3.什么是Oracle

Oracle公司-甲骨文公司，全称甲骨文股份有限公司(甲骨文软件系统有限公司)，是全球最大的企业级软件公司，总部位于美国加利福尼亚州的红木滩。1989年正式进入中国市场。2013年，甲骨文已超越 IBM ，成为继 Microsoft 后全球第二大软件公司。
Oracle公司的网址为http://www.oracle.com。
Oracle在企业的大型应用、海量存储、高性能、高并发、安全性等方面都相当不错，被广泛应用于各个领域。Oracle不仅有数据库产品，而且也是ERP软件的供应商。
我们本次课程所说的Oracle都是指其数据库产品。
1.4.Oracle的地位
2013年:

1.5.Oracle的应用场景
Oracle数据库方案在大量的企业，涉及航空与国防、汽车、化学、消费品、高科技、工业制造、生命科学、自然资源、石油和天然气、公用事业、通信（移动、联通、电信）、媒体和娱乐、教育和人力资源、工程建筑、金融服务（银行、证券）、卫生医疗、公共部门、零售、交通运输和专业服务等诸多行业。
2.Oracle服务端的安装和配置
2.1.版本和下载

Oracle的版本发展8、9i，10g，11g，12c。 grid

Oracle分免费简化版本（Oracle Database Express）和完整版本（Oracle Database），都可以自由下载。

Express版本你可以免费用于商业用途，但这个版本对cpu/内存/数据量的有限制。
常见的关系型数据库的Express版本对比：

完整版本可以免费用于学习、教学等，如果商用则需要收费，那么就可以享受Oracle公司提供的服务，Licence按照CPU数量报价(2009年)，一个CPU大约50w左右，打完折，25w以上。

Oracle可以在大部分主流的操作系统上安装，比如Linux和Windows。官方的网站是：www.oracle.com。

完整版本Oracle的windows版本（32位）是从官方直接下载的参考网址：
http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_1of2.zip
http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_2of2.zip
2.2.软件安装

我们安装简化版本：

安装过程中：
安装目录 不能出现中文和空格

这个口令是sys和system这两个超级用户的密码。sys相当于mysql的root。

Oracle的服务端口，默认是1521
提醒：简化版安装完成之后，你的8080端口可能会被占用。你可以将这个服务禁用，也可以将端口改掉。可以参考：

sys和system是系统的管理员帐号，默认密码是安装Oracle时指定的密码。

测试是否安装成功：
在安装有oracle的机器上，运行cmd，输入：
sqlplus sys/itcast@localhost:1521/xe as sysdba

如果出现了以下情况:

重启电脑或者使用命令提示符(管理员) 打开窗口:

软件的卸载（再运行一遍简化版的安装程序，直到出现下面的界面）：

2.3.Oracle的服务_了解
在windows下打开“运行”，输入“Services.msc”，找到Oracle相关的服务。
完整版本：

简化版本：

关于这些服务：数据库实例服务和监听服务必须启动，其他都可以禁用。(如果安装到本机，那么建议全手动。)
新手如何记忆这两个服务的名字呢？
数据库实例服务：Oracle+Service+SID，简化版的Oracle默认的sid是xe(邪恶)，完整版的Oracle默认的SID是orcl。
监听服务：Oracle+…+TNSListener。
手动启动或重启服务的顺序问题：
建议先启动实例服务：OracleServiceXE，再启动监听服务

提醒：每次重启或开启服务的时候，需要等一会再连接。
3.Oracle客户端的安装和配置
3.1.PL/SQL Developer
3.1.1.工具的安装
该工具是第三方的工具，非官方的。后面都简称“工具”；
本次教学提供安装版本和绿色版本，推荐使用绿色版本，直接解压即可使用。

直接解压到一个目录即可(不要出现中文)，主执行文件为plsqldev.exe，可以自行创建快捷方式。

3.1.2.使用工具连接Oracle数据库
打开该工具，输入用户名，密码，数据库连接字符串（这里可以省略端口号1521）：

3.1.3.工具的使用优化设置

对象的显示顺序：

Tools->Preferences->

显示行号:

3.2.Oracle客户端连接知识（客户端连接验证机制、客户端登录身份）。
3.2.1.客户端连接验证机制
Oracle有三种连接验证机制：
操作系统验证（具有sysdba和sysopera的用户）
密码文件验证（具有sysdba和sysopera的用户）
数据库密码验证（普通用户和超级用户）

超级管理员连接数据库的方式：

也成功登录。（免登录必须在安装有Oracle服务的服务器上才有效）

免密码登录的原因是：oracle安装时自动创建了一个ora_dba的组，并将当前用户放入该组。如果你连接oracle的时候，会优先使用来查找当前用户是否在组中。如果在组中，则不需要密码就可以登录。
免登录要求：必须在安装有Oracle的本机上才能使用。

注意：
我们平时用的连接机制是数据库密码验证的机制。

关于验证的优先级顺序：
对于 SYSDBA身份 和 SYSOPER来说，OS验证优先于密码文件认证。因此，在服务器的机器上，你可以无需指定登录用户名或者任意指定登录用户名，均可以登录。
该特性的一个应用场景就是免登录解锁用户：sqlplus / as sysdba
【案例】
【示例】通过超级管理员对Oracle用户解锁和找回密码
比如sys用户密码忘记了
Sqlplus / as sysdba
–免登陆超级管理员的密码
语法：
alter user 用户名 identified by 密码
示例：
–更改用户密码：
Alter user sys identified by itcast;

加锁和解锁用户：
语法：
alter user 用户名 account lock;–加锁：该用户不能登录。
alter user 用户名 account unlock;–解锁：该用户可以登录了。
示例：
Alter user scott account lock;
Alter user scott account unlock;

提示：
因为不需要密码是不安全的，所以一般用作服务器的话，要在计算机管理中的用户组ora_dba把administrator删除，删除之后登录就需要输入密码了。

提示：
conn / as sysdba连接时，使用的是当前登陆操作系统的用户名和密码认证。如果oracle是由当前用户安装的，那它可以成功登陆，通俗点说，当你用“as sysdba”登录时是按照操作系统用户验证的，也就是说oracle认为你都已经是这台电脑的老大了，我oracle没法限制你。

3.2.2.客户端登录的身份
Oracle有三种身份登录方式：Normal、sysdba、sysoper。

normal身份：普通用户身份，默认选项(默认可以不写)，用于普通用户登录使用。—记录日志（你的任何操作oracle都会记录一份日志）
sqlplus scott/tiger@localhost:1521/xe
sysdba身份：数据库管理员身份，用于给拥有DBA权限的用户登录使用。(拥有数据库)
该身份可以进行的操作： 打开、关闭数据库服务器， 备份、恢复数据库， 日志归档，会话限制，管理功能，创建数据库等。----操作是不计日志的。

sysoper身份：数据库操作员身份，用于给拥有DBA权限的用户登录使用。
该身份可以进行的操作： 打开、关闭数据库服务器， 备份、恢复数据库， 日志归档，会话限制等。—记录日志

作为开发人员，如何选择Oracle的登录身份？
普通用户进行数据操作，就用normal。（默认值）
Sys用户就用sysdba。–(sys相当于mysql：root)

4.贯穿学习过程中的用户和表。

本次课程中主要涉及到一个用户下的表：scott
【了解】：默认情况下，完整版的Oracle安装完成后，只解锁有效两个用户：sys,system， scott用户需要解锁。而简化版的Oracle安装完成之后，无scott用户。
【新建Scott用户】
scott用户：需要我们手动建立和导入：
先建立scott用户（必须是sys用户登录进的）：

设置用户名和密码：用户名为scott密码为tiger

添加两个角色：

完成后，点击apply：
使用该用户登录（普通用户登录）：

登录进去之后，到导入样例数据：

查询数据：

scott是上课时用的，默认密码是tiger。

4.1.scott用户下的表结构
DEPT 部门表
字段 类型 描述
DEPTNO NUMBER(2) 部门编号，长度为2
DNAME Varchar2(14) 部门名称，长度为14
LOC Varchar2(13) 位置

EMP 雇员表
字段 类型 描述
EMPNO NUMBER(4) 雇员编号 长度为4
Ename Varchar2(10) 雇员姓名 长度为10
Job Varchar2(9) 职位 长度为9
mgr Number(4) 领导编号 领导也是雇员
hiredate date 入职日期
sal Number(7,2) 雇员基本工资
comm Number(7,2) 奖金
deptno Number(4) 部门编号
和部门表中的编号关联

Salgrade 工资等级表
字段 类型 描述
grade number 等级
losal number 最低工资
hisal number 最高工次

bonus工资表
字段 类型 描述
ENAME VARCHAR2(10) 雇员姓名
JOB Varchar2(9) 职位
sal number 基本工资
comm number 奖金

4.2.ORACLE常用数据类型
类型名称 ORACLE类型 描述
整型 Number(n) N表示数字的长度，默认值number-相当于number(11)
小数 Number(n,m) N表示总长度，m小数位，number(5,2)
字符串(固定长度) Char(n) N表示长度
字符串(可变长度) Varchar2(n) N表示长度 最高存储2000字符
日期 date ORACLE日期格式是固定。日期在存储时有一个本地化操作
大文本类型 Clob 存储海量文本数据。最大值可达4G
大量二进制类型 blob 存储二进制数据，最大4g

常见的Oracle数据类型了解：
Char类型：定义长度时，如果存储的数据小于长度，空位补空格。固定长度的类型
缺点：浪费存储空间 char(3) m 空格空格m
Char类型中存在空格，那么在程序取值比较时容易出错
在ORACLE中使用trim()函数，会造成char类型字段上的索引失效
优点：char类型在效率上比着其它字符类型的会快一点
Varchar2类型： 可变长度。存储时如果数据小于长度，varchar的长度按实际存储长度计算
优点：节省存储空间 varchar2(3) 你好 你好
缺点：效率比char类型低
注意：varchar2只有Oracle才有的。带2的是Oracle自己增强的数据类型，不是sql99规范中的。
Clob是longtext类型的代替品,存储超大量的字符串。如果varchar存储不了，可以用这个。但效率很低下，而且不能使用上索引。这玩意的存储大小不是按长度存储，按字节数存储的。
Blob 存储海量二进制类型。例：电影、音乐、高清图片。
缺点：每次存储时都需要转换为二进制进行存储
获取时把二进制转为数据
通常存储电影、音乐时存储的是文件的路径
Oracle所有的数据类型：

课外：试试各种语句和命令与mysql是否一样，比如crud的。
5.SQL增强-单表查询
5.1.基本(基础)查询
学习目标：
简单（基本）查询中涉及的几个注意点、不同点以及Plsql Developer工具的使用（后面简称工具）
后面的所有基本练习，除非特别说明，都使用scott用户登录。

5.1.1.基本查询语法
基本查询是指最基本的SQL select语句。
【语法】

使用sqlplus登录普通用户：
sqlplus scott/tiger@localhost:1521/xe
sqlplus scott/tiger@192.168.0.249/xe–省略端口号，默认1521

【知识点】如何使用工具进行查询

在plsql developer中打开查询窗口：

执行语句的操作：
选中要执行的语句，点击执行按钮或者按快捷键F8，下方会显示查询的结果。

显示下一页记录和显示剩余的所有页的记录：

提示：
本课程不单独讲解该工具的详细使用，只是在用到哪些功能的时候再讲解相应的功能。如果想学习工具的详情使用，可参阅文档《plsql developer中文手册.pdf》

使用工具来格式化语句：
选中要格式化的语句，点击工具栏上的“美化”按钮，工具会自动将语句格式化：

格式化美化功能非常适合比较长的、复杂的语句的格式化。

5.1.2.字符串连接符||

【示例】
需求1：查询出员工的名字，要求显示的员工名字前面加上“姓名：”的字符串，显示结果参考：姓名：scott
需求2：将员工的编号和员工的姓名都放在一个结果字段中显示。

–需求1：查询出员工的名字，要求显示的员工名字前面加上“姓名：”的字符串，显示结果参考：姓名：scott
SELECT ‘姓名：’|| ename 姓名 FROM emp;
–需求2：将和员工的编号和员工的姓名都放在一个结果字段中显示。合成列
SELECT empno||’ '||ename FROM emp;
提示：单引号代表的是字符串。

【知识点】
引号的问题。Oracle中如何选择单引号和双引号呢？基本上，只要是别名或不需要Oracle解析（运算）的字符串，用双引号，剩下的都用单引号（比如字符串）。

5.1.3.伪表-dual(oracle独有)
mysql查询当前系统时间：SELECT SYSDATE();
但在Oracle中会报错：

提示：sysdate代表系统时间函数。

报错原因：
Oracle和mysql的一个区别：
Oralce的查询语句必须是完整的，即必须满足语法select from

【示例】
需求：查询显示当前的日期：
SELECT SYSDATE FROM dual;–sysdate代表当前日期的一个系统函数，dual是伪表，主要用来占位的，补充sql的。
SELECT ‘a’||‘b’ FROM dual;
SELECT 1+2 FROM dual;

DUAL 是一个‘伪表’（也称之为万能表），可以用来测试函数和表达式。也有人称之为万能表。
使用的时候可以用来占个语法的位置，来补充完整的sql。
伪表也是一张表，只是做了一些特殊处理。我们来看看：

注意：大家不要手动来维护这张表，这个表是由Oracle自动维护的。

5.1.4.空值运算问题

【示例】
需求：查询所有员工的月薪（月薪=基本工资+奖金）
–需求：查询所有员工的月薪（月薪=基本工资+奖金）
SELECT ename, sal+comm 月薪 FROM emp;–原因：与null运算的结果都是null

问题：
为何有的人月薪没值？但这些人明明有基本薪资（sal）。
原因：和null进行运算的都是null。
如何解决呢？我们会在单行函数中这一章节中进行解决。

提示：
后续课程中会有更多与null相关的例子。
5.1.5.SQL语句和SQL*Plus命令
目标：
了解什么是命令，什么是语句。

两者对比如下：

关键字可以缩写，比如显示表结构的命令：

【示例】
需求：分别用完整命令和简写命令来显示emp表的结构：

【关于工具窗口使用选择】
工具的命令行窗口下，既能执行命令，也能执行sql，但在sql窗口下只能运行sql，如果在sql窗口运行命令，会出现错误信息：

工具中的命令窗口的调用方法：

友情提示：
工具的命令窗口和sqlplus自带的命令窗口在有时候还是有少许区别的。后面会提到。

6.单行函数
6.1.1.函数的分类
Oracle的内置函数分为单行函数和多行函数（多行函数还称之为组函数、聚集函数等）。

6.1.2.单行函数的概念

6.1.3.单行函数的分类

6.1.4.字符函数

大小写控制函数
【示例】
需求1：查询出KING的这个员工的信息。
–需求1：查询出KING的这个员工的信息。
SELECT * FROM emp WHERE ename =‘king’;
SELECT * FROM emp WHERE LOWER(ename) =‘king’;–会将数据库的值转换成小写
SELECT * FROM emp WHERE ename =UPPER(‘KinG’);–不管用户输入的是大写还是小写，还是大小混合写
SELECT UPPER(‘KiNg’) FROM dual;
SELECT empno,INITCAP(ename) FROM emp --首字母大写

【讨论】：
上述的需求，到底是使用upper还是使用lower呢？
一般根据需求来选择的。
如果将函数放到字段上，会每行的该字段都会转换，效率低一些。–sql优化
因此，一般情况下，建议将转换函数放到固定值上面(好处之一就是只需要转换一次，还有一个好处，就是你不知道用户到底输入的是大写还是小写还是混合写，更适应业务)。

字符控制函数：

【示例】演示部分，其他课后练习！
需求1：替换字符串’abcd’中的’bc’为’ITCAST’，最终显示为’aITCASTd’
需求2：去掉’ Hello World ‘前后的空格
需求3：去掉’Hello WorldH’前后的H字符（提示：使用from关键字）
–需求1：替换字符串’abcd’中的’bc’为’ITCAST’，最终显示为’aITCASTd’
SELECT REPLACE (‘abcd’,‘bc’,‘ITCAST’) FROM dual;
–需求2：去掉’ Hello World ‘前后的空格
SELECT TRIM(’ Hello World ') FROM dual;
–需求3：去掉’Hello WorldH’前后的H字符（提示：使用from关键字）
SELECT TRIM(‘H’ FROM ‘Hello WorldH’) FROM dual;

【提示】：
Oracle的函数非常多，建议大家只记住课堂上讲解的常用的几个就基本够用了，其他的可以查阅手册：

6.1.5.数字函数

【示例】
需求：钱数：1385.56，分别根据不同场景进行处理显示不同结果：买东西（抹零头：1385，1380）、发工资（发钱了：1386）
–需求：钱数：1385.56，分别根据不同场景进行处理显示不同结果：买东西（抹零头：1385，1380）、发工资（发钱了：1386）
SELECT TRUNC(1385.56) 买东西抹零头,TRUNC(1385.56,-1) 抹零头,
ROUND(1385.56) 发钱,ROUND(1385.56,1) 发钱
FROM dual;

【提示】：
Round和trunc函数，除了对数字起作用外，对于日期也是起作用的。（后面会提到）

6.1.6.日期函数
【示例】
问题：日期可以相减么？日期可以相加么？
–问题：日期可以相减么？日期可以相加么？
SELECT SYSDATE-SYSDATE FROM dual;–日期相减一般是为了计算两个日期之间间隔
SELECT SYSDATE+SYSDATE FROM dual;–日期相加没意义

常用函数（了解，用时查询）

next_day(基础日期,星期几)
星期几,是从周日开始，分别数字为1，2，3。。。。

【示例】
需求1：计算员工的工龄（工龄：当前的日期和入职的日期的差），要求分别显示员工入职的天数、多少月、多少年。
需求2：查看当月最后一天的日期。
需求3：查看指定日期的下一个星期天或星期一的日期。（next_day(基础日期,星期几)）
–需求1：计算员工的工龄（工龄：当前的日期和入职的日期的差），要求分别显示员工入职的天数、多少月、多少年。
SELECT ename, SYSDATE-hiredate 工龄天,
(SYSDATE-hiredate)/30 工龄月不精确,months_between(SYSDATE,hiredate) 工龄月精确,
TRUNC (months_between(SYSDATE,hiredate)/12) 工龄年精确
FROM emp;

–需求2：查看当月最后一天的日期。

SELECT last_day(SYSDATE) FROM dual;

–需求3：查看指定日期的下一个星期天或星期一的日期。（next_day(基础日期,星期几)）

SELECT next_day(SYSDATE,7) FROM dual;

【扩展知识】
扩展：时间戳systimestamp关键字。
【示例】
查看当前系统默认精度的日期时间和更高精度的时间戳，要求显示结果如下：

SELECT SYSDATE,Systimestamp FROM dual;

6.1.7.转换函数
数据类型转换分类：

隐式转换
【示例】
需求：查询10号部门的信息，分别使用数字和字符串作为条件的值。
–需求：查询10号部门的信息，分别使用数字和字符串作为条件的值。
SELECT * FROM emp WHERE deptno=10;
SELECT * FROM emp WHERE deptno=‘10’;–字符串隐式转换为数字了
SELECT * FROM emp WHERE deptno=‘10q’;–隐式转换的前提，是能转换才可以。

【工具的使用补充】在查询数据的时候，通过工具来快捷查看字段的数据类型：

隐式转换的条件：
Oracle可以自动的完成下列类型（三种）的转换：

非法转换：
隐式转换的前提是：被转换的对象是可以转换的。下面的语句会报错：

运行会抛一个异常：

显示转换（三个函数）

三个转换函数的语法：
将日期或数字转换成字符

将字符转换成日期

将字符转换成数字

【提示：记忆方式】：第一个参数都是要转换的目标（到底用哪个函数，跟目标有关系），第二个都是转换的格式。

【示例】
需求1：显示今天的完整日期，结果参考：“2015-07-06 11:07:25”。
需求2：显示今天是几号，不包含年月和时间，结果参考：“8日”。
需求3：显示当月最后一天是几号，结果参考：”30“。
需求4：xiaoming的入职日期是2015-03-15，由于其入职日期当时忘记录入，现在请将其插入到emp表中。

–需求1：显示今天的完整日期，结果参考：“2015-07-06 11:07:25”。
SELECT to_char(SYSDATE,‘yyYy-MM-dd HH24:mi:ss’) FROM dual;–oracle的日期格式和java的日期格式不一样,
–oracle的日期格式不区分大小写

–需求2：显示今天是几号，不包含年月和时间，结果参考：“8日”。
SELECT to_char(SYSDATE,‘dd’)||‘日’ FROM dual;

–需求3：显示当月最后一天是几号，结果参考：”30“。
SELECT to_char(last_day(SYSDATE),‘dd’) FROM dual;

–需求4：xiaoming的入职日期是2015-03-15，由于其入职日期当时忘记录入，现在请将其插入到emp表中。

UPDATE emp SET hiredate=to_date(‘2015-03-15’,‘yyyy-mm-dd’) WHERE ename = ‘xiao_ming’;

SELECT * FROM emp;

【注意】和java不同，Oracle的日期格式对大小写不敏感。

【使用上的选择】
到底要用哪个函数，关键是传进来的目标的类型和最终需要的结果类型。

日期格式的常见元素：

【示例】
需求：查看显示今天是星期几
SELECT to_char(SYSDATE,‘day’) FROM dual;

数字格式的常见元素：

提示：9代表任意数字，可以不存在。0代表数字，如果该位置不存在，则用0占位。

【示例】
需求：查询员工的薪水，格式要求：两位小数，千位数分割，本地货币代码。
–需求：查询员工的薪水，格式要求：两位小数，千位数分割，本地货币代码。
SELECT ename,sal,to_char(sal,‘L99,999.00’) FROM emp;
SELECT ename,sal,to_char(sal,‘L00,000.00’) FROM emp;

6.1.8.滤空函数（通用函数）
滤空函数也称为通用函数，其特点是：适用于任何数据类型，同时也适用于空值。
常见的滤空函数：

使用方法：
nvl(a,c)，当a为null的时候，返回c，否则，返回a本身。
nvl2(a,b,c),当a为null的时候，返回c，否则返回b—三元运算
其中，nvl2中的2是增强的意思，类似于varchar2。
nullif(a,b),当a=b的时候，返回null，否则返回a
coalesce(a,b,c,d)，从左往右查找，当找到第一个不为null的值的时候，就显示这第一个有值的值。

【示例】
需求：查询员工的月收入（基本薪资+奖金）
–需求：查询员工的月收入（基本薪资+奖金）
SELECT ename,sal+nvl(comm,0) 月收入 FROM emp;
SELECT ename ,NVL2(sal,sal,0)+nvl(comm,0) FROM emp;–为了小明
SELECT coalesce(NULL,NULL,1,2) FROM dual;–返回第一个不为空的值

6.1.9.条件表达式
条件表达式的作用是：在SQL语句中使用判断的逻辑（类似于IF-THEN-ELSE）来呈现个性化的数据。
条件判断语句有两种：
CASE 表达式：SQL99的语法，类似Basic，比较繁琐

DECODE 函数：Oracle自己的语法，类似Java，比较简单
1.Decode函数
也可以理解为解码翻译函数。
语法：

语法解释：
decode (字段名,要翻译的原始值1，翻译后的值1，…，其他不满足翻译条件的默认值)

【示例】
需求:要将工种job的英文转换为中文
–需求:要将工种job的英文转换为中文
SELECT ename,job,
DECODE(job,‘CLERK’,‘职员’,‘SALESMAN’,‘销售人员’,‘MANAGER’,‘经理’,‘其他工种’)
FROM emp;

业务场景补充：
比如人的性别：一般数据库存放的是：0和1，2，在直接出报表的时候，就需要转换显示。
SELECT NAME 姓名,DECODE(gender,1,‘男’,0,‘女’,‘凹凸MAN’) 性别 FROM TABLE;

2.Case子句
语法：

语法解释：
case 字段 when 要翻译的值 then 翻译的结果
when 要翻译的值 then 翻译的结果
…
else 默认的结果值
end
【示例】

SELECT ename,job,

CASE job WHEN ‘CLERK’ THEN ‘职员’
WHEN ‘SALESMAN’ THEN ‘卖男’
ELSE ‘其他工种’
end

FROM emp;

3.case子句增强
需求：查看公司员工的工资情况，要求显示员工的姓名、职位、工资、以及工资情况。如果是工资小于1000，则显示“工资过低”，工资大于1000小于5000为“工资适中”，工资大于5000的，则显示“工资过高”：

SELECT ename,job,sal,
CASE WHEN sal<1000 THEN ‘工资过低’
WHEN sal BETWEEN 1000 AND 5000 THEN ‘工资适中’
when sal IS NULL THEN ‘没工资酱油瓶’
ELSE ‘工资太高’
END
FROM emp;

Decode和Case的使用选择：
在Oracle中，翻译值的这种条件判断，优先使用decode，因为简单明了，且Oracle有一定的优化；更复杂的条件判断或者其他的关系型数据库，只能使用Case子句。

7.子查询
子查询也称之为嵌套子句查询。
1.1.语法

语法上的运行使用规则：
子查询 (内查询、嵌套子句) 在主查询之前一次执行完成。（子查询先执行）
子查询的结果被主查询使用 (外查询)。
子查询要包含在括号内。
将子查询放在比较条件的右侧。
1.2.为什么要使用子查询？
【需求】谁的工资比scott高？
采用连接的方式写（这里是自连接，见下图）：

–【需求】谁的工资比scott高？
–多表关联查询：自连接的不等值连接
SELECT * FROM emp t1,emp t2 WHERE t2.ename=‘SCOTT’ AND t1.sal>t2.sal
–不等值连接

采用子查询的方式写：
–子查询
–分析一下:谁的工资比scott高？—>1,scott工资是多少2，谁的工资比3000高
SELECT sal FROM emp WHERE ename=‘SCOTT’;
SELECT * FROM emp WHERE sal >3000;
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename=‘SCOTT’);

对比可以发现：在某些业务上，子查询比连接查询更容易理解。

1.3.子查询的分类

单行操作符（> = <）对应单行子查询，多行操作符(in,not in)对应多行子查询。
1.4.单行子查询
1.4.1.语法要求：
只返回一行。
使用单行比较操作符。

其中<>也可以可以用!=代替，意思一样。
【示例】
–查询部门名称是SALES的员工信息
–查询部门名称是SALES的员工信息
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM DEPT WHERE dname =‘SALES’)
了解：子查询可以是一张表的数据，也可以是不同表的数据。
1.4.2.空值问题

【代码】
需求:查找工作和’Rose’ 这个人的工作一样的员工信息
需求:查找工作和’Rose’ 这个人的工作不一样的员工信息
–需求:查找工作和’Rose’ 这个人的工作一样的员工信息
SELECT job FROM emp WHERE ename = ‘Rose’;

SELECT * FROM emp WHERE job =(SELECT job FROM emp WHERE ename = ‘Rose’);

SELECT * FROM emp;

–需求:查找工作和’Rose’ 这个人的工作不一样的员工信息
SELECT * FROM emp WHERE job !=(SELECT job FROM emp WHERE ename = ‘Rose’);

–结论: 只要子查询返回的结果是null的话, 那么主查询的结果就一定是null
注意：使用子查询的时候，一定要保证子查询不能为空，否则数据就会出现异常。
1.4.3.非法使用单行子查询
【示例】需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作一样的员工信息

1.5.多行子查询
1.5.1.语法要求：
返回多行。
使用多行比较操作符。

1.5.2.In操作符
【示例】
需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作一样的员工信息
–需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作不一样的员工信息
–需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作一样的员工信息

SELECT JOB FROM emp WHERE ename IN(‘SMITH’,‘ALLEN’);
SELECT * FROM emp WHERE job IN(SELECT JOB FROM emp WHERE ename IN(‘SMITH’,‘ALLEN’));

–需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作不一样的员工信息
SELECT * FROM emp WHERE job NOT IN(SELECT JOB FROM emp WHERE ename IN(‘SMITH’,‘ALLEN’));

1.5.3.Any和all操作符
【示例】需求:查询工资比30号部门任意一个员工的工资高的员工信息。–面试题
【示例】需求:查询工资比30号部门所有员工的工资高的员工信息。
–需求:查询工资比30号部门任意一个员工的工资高的员工信息。–面试题
SELECT * FROM emp WHERE deptno =30;
–任意一个：比最低的那个高就ok。
SELECT * FROM emp WHERE sal >(SELECT MIN(sal) FROM emp WHERE deptno=30);
–any(多行函数)
SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);
–【示例】需求:查询工资比30号部门所有员工的工资高的员工信息。
SELECT * FROM emp WHERE sal>(SELECT MAX (sal) FROM emp WHERE deptno=30);
–all(多个返回记录)–max(sal)
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

分析结果：

1.6.子查询注意事项
关于格式：子查询要包含在括号内，最好有合理的书写风格。

子查询的位置：可以放在主查询的where、select、having、from的后面。不可以放在主查询的group by后面。
子查询和主查询可以是同一张表，也可以不是是不同一张表，只要子查询返回的结果在主查询中能使用即可。
关于使用操作符：单行操作符对应单行子查询，多行操作符对应多行子查询。
执行顺序：一般子查询先执行，再执行主查询；
关于排序：一般不在子查询中使用order by；但在top-N分析问题中，必须在子查询中使用order by。
多行子查询一般用于from后面，作为一张新的虚拟临时表来使用。

虚拟临时表是临时表的一种，是运行过程中，内存中虚拟出来的一张临时表，用于sql的操作。
【示例】
–虚拟表
SELECT * FROM
(
SELECT * FROM emp WHERE deptno=30 --虚表:将查询结果再作为一张表来使用。
) t
WHERE sal>2000

1.7.子查询和多表关联查询的选择
理论上，在都可以实现需求的情况下尽量选择多表查询。
原因：子查询会操作两次，多表查询只操作一次。多表的效率高。
但要注意的是，多表查询如果产生了笛卡尔集（语句上要注意条件的使用），则会出现严重的效率问题。
一般不在子查询中使用排序（order by），但在top-N分析问题中必须在子查询中使用排序。

8.伪列
8.1.什么是伪列
伪列是在ORACLE中的一个虚拟的列。
列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
所有的伪列要得到值必须要显式的指定。

最常用的两个伪列：rownum和rowid。

8.2.ROWNUM
ROWNUM（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）
rownum永远按照默认的顺序生成。（不受orderby的影响）
rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。
8.2.1.行号的产生
【示例】需求：查询出所有员工信息，并且显示默认的行号列信息。
–需求：查询出所有员工信息，并且显示默认的行号列信息。
SELECT ROWNUM,t.* FROM emp t;–* 和指定的列一起显示的时候，必须加别名

提示两点：
ROWNUM是由数据库自己产生的。
ROWNUM查询的时候自动产生的。

8.2.2.行号的排序
【示例】–需求：查询出所有员工信息，按部门号正序排列，并且显示默认的行号列信息。
–需求：查询出所有员工信息，按部门号正序排列，并且显示默认的行号列信息。
SELECT ROWNUM,t.* FROM emp t ORDER BY deptno;–order by 的原理：将查询结果（此时行号已经有了，已经和每一行数据绑定了）进行排序。

• –order by是查询语句出来的结果之后再排序的，，rownum是在查询出来结果的时候产生。order by不会影响到行号
  –先排序，再查询
  SELECT ROWNUM,t.* FROM
  (
  SELECT * FROM emp ORDER BY deptno
  ) t

结论：
order by排序，不会影响到rownum的顺序。rownum永远按照默认的顺序生成。
所谓的“默认的顺序”，是指系统按照记录插入时的顺序（其实是rowid）。
8.2.3.利用行号进行数据分页-重点
/*
mysql :
select *from emp limit m,n
m: 起始索引
n: 查询条数

select *from emp limit 3,3

查询 3+1 条开始往后查询3条 4~6 条

*/

–查询 emp 表中的第4~6 条数据

SELECT ROWNUM, T.* FROM EMP T WHERE ROWNUM <=6;

SELECT * FROM (SELECT ROWNUM r, T.* FROM EMP T WHERE ROWNUM <=6) WHERE r>=4;

/*
查询第2页 每页3条

mysql:
select *from emp limit m,n

page = 2
pageSize = 3;

–起始索引
startIndex = (page-1)*pageSize;
–每页条数
maxCount = pageSize;
select *from emp limit startIndex,maxCount

oracle 需要利用行号分页:查询第2页 每页3条

–起始行号
startRownum = (page-1)pageSize+1;
–结束行号
endRownum = pagepageSize;
SELECT * FROM (SELECT ROWNUM r, T.* FROM EMP T WHERE ROWNUM <=endRownum) WHERE r>=startRownum;

–根据收入排序查询第2页

*/

SELECT * FROM emp ORDER BY sal ;

SELECT ROWNUM,t.* FROM(SELECT * FROM emp ORDER BY sal ) t WHERE ROWNUM<=6;

SELECT * FROM (SELECT ROWNUM r,t.* FROM(SELECT * FROM emp ORDER BY sal ) t WHERE ROWNUM<=6) WHERE r>=4;

【分析原因：
rownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

【提示】：
如何记忆编写Oracle的分页？建议写的时候从里到外来写，即先写小于的条件的子查询（过滤掉rownum大于指定值的数据），再写大于的条件的查询（过滤掉rownum小于的值）。
Oracle的分页中如果需要排序显示，要先排序操作,再分页操作。(再嵌套一个子查询)
性能优化方面:建议在最里层的子查询中就直接指定字段或者其他的条件，减少数据的处理量。

8.3.ROWID
ROWID（记录编号）：是表的伪列，是用来标识表中唯一的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。
主键:标识唯一的一条业务数据的标识。主键是给业务给用户用的。不是给数据库用的。
记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。

8.3.1.ROWID的查看
【示例】

8.3.2.ROWID的产生
使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。
这与rownum有很大不同，rownum不是表中原本的数据，只是在查询的时候才生成的。

提示：rownum默认的排序就是根据rowid
8.3.3.ROWID的作用
这里列举两个常见的应用：
去除重复数据。–面试题—了解

关于主键和rowid的区别：
相同点：为了标识唯一一条记录的。
不同点：
主键：针对业务数据，用来标识不同的一条业务数据。
rowid：针对具体数据的，用来标识不同的唯一的一条数据，跟业务无关。

【示例】需求：删除表中的重复数据，要求保留重复记录中最早插入的那条。（DBA面试题）
–需求：删除表中的重复数据，要求保留重复记录中最早插入的那条。（DBA面试题）
–准备测试表和测试数据：
–参考建表语句如下：
– Create table
create table test
(
id number,
name varchar2(50)
)
;
–插入测试数据
INSERT INTO TEST VALUES(1,‘xiaoming’);
INSERT INTO TEST VALUES(2,‘xiaoming’);
INSERT INTO TEST VALUES(3,‘xiaoming’);
COMMIT;
SELECT * FROM TEST ;
–通过rowid，剔除重复xiaoming，保留最早插入的xiaoming
SELECT t.,ROWID FROM TEST t;
–删除的的时候，可以先查询你要删除的东东
SELECT t.,ROWID FROM TEST t WHERE ROWID > (SELECT MIN(ROWID) FROM TEST);
DELETE FROM TEST t WHERE ROWID > (SELECT MIN(ROWID) FROM TEST);

–语句有缺点：条件不足，会只保留一条数据，误删其他数据
–重新插入测试数据
INSERT INTO TEST VALUES(1,‘xiaoming’);
INSERT INTO TEST VALUES(2,‘xiaoming’);
INSERT INTO TEST VALUES(3,‘xiaoming’);
INSERT INTO TEST VALUES(4,‘Rose’);
INSERT INTO TEST VALUES(5,‘Rose’);
COMMIT;
–剔除重复数据
SELECT * FROM TEST WHERE ROWID NOT in(SELECT MIN(ROWID) FROM TEST GROUP BY NAME);
DELETE TEST WHERE ROWID NOT in(SELECT MIN(ROWID) FROM TEST GROUP BY NAME);

注意:删除重复记录一定要小心,万一你的条件有问题,就会删错数据.建议删除之前，可以先用查询查一下，看是否是目标数据。
数据一旦删除恢复比较麻烦，但可以恢复，采用日志回滚。一般不要轻易用。

9.序列-sequence
需求:
Mysql中主键有自增长的特性.
Oracle中,主键没有自增长这个特性.那么如何解决这个问题.使用序列可以解决.
9.1.概念和作用
序列：可供多个用户来产生唯一数值的数据库对象
. 自动提供唯一的数值
. 共享对象
. 主要用于提供主键值
. 将序列值装入内存可以提高访问效率

这个是Oracle特色的。Mysql是没有的。
简单的说，他可以用来高效的生成主键值。

9.2.语法

将序列提前装入内存，可以提高效率。
9.3.创建序列
【示例】
创建一个简单的序列
CREATE SEQUENCE seq_test;

9.4.序列的使用
在ORACLE中为序列提供了两个伪列：
1，NEXTVAL 获取序列对象的下一个值（指针向前移动一个，并且获取到当前的值。）
2，CURRVAL 获取序列对象当前的值

【示例】–查询序列的当前值

为什么？
原因是：序列初始化之后指针在第一个数之前。必须先向前移动才可以查询的到。
数组的指针默认在1之前，并没有指向第一个值，要想使用必须向前移动一下。（指针只能向前不能向后）
[1,2,3….20][
*
操作指针：
[1,2,3….20][
*

SELECT seq_test.nextval FROM dual;

移动一位并且取值。

9.5.序列的应用
Oracle建表的时候是否能像mysql那样设定一个自增长的列吗？
不行！
那如何解决呢？使用序列！

【示例】在插入数据的时候插入序列主键.
–在插入数据的时候插入序列主键.
INSERT INTO TEST VALUES(seq_test.nextval,‘Jack’);

问题：为什么这个值不是从1开始？

原因: 共享对象 序列是个独立对象.谁都能用,谁都能共享它.

9.6.序列的裂缝
1，序列是一个共有对象，多个表都可以调用。但实际开发中，可以避免多个表用一个序列（创建多个序列）。序列是独立的对象。任意表都可以使用，但是编号就不能保证有序。
2，当插入记录时报错，序列对象值也被使用，下一次再使用时，序列的值就会+1

【示例】序列的裂缝
INSERT INTO T_TEST VALUES(seq_test.nextval,‘张三1’);
ROLLBACK;
INSERT INTO T_TEST VALUES(seq_test.nextval,‘张三2’);
COMMIT;
SELECT * FROM T_TEST ;
也就是说，用序列插入数据库的值不一定是连续的。
补充：
Mysql的自增长列也可以是不连续的.

序列出现裂缝的条件：
事务回滚。
系统异常。
多个表同时使用同一个序列。
这个序列是公用的对象。如果你很在意的话，就一个表用一个序列，但大多数情况下，这个主键值（代理主键）没有什么意义的话，可以多个表公用一个序列。

10.索引INDEX
10.1.索引的概念特性和作用
概念:
简单的说，相当于一本书的目录。(数据库中的索引相当于字典的目录(索引)),它的作用就是提升查询效率。
特性：
一种独立于表的模式（数据库）对象, 可以存储在与表不同的磁盘或表空间中。
索引被删除或损坏, 不会对表（数据）产生影响, 其影响的只是查询的速度。
索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引。
在删除一个表时, 所有基于该表的索引会自动被删除。
如果建立索引的时候,没有指定表空间,那么默认索引会存储在所属用户默认的表空间.
作用：
通过指针（地址）加速Oracle 服务器的查询速度。
提升服务器的i/o性能（减少了查询的次数）；

10.2.索引的工作原理

10.3.操作索引
索引的常见操作可以创建、删除。
10.3.1.创建索引
索引有两种创建方式：
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引。
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询。

唯一索引的数据不重复，一般用于主键或唯一约束上创建。
其他普通的列的数据，一般可以重复，那么就不要创建唯一索引，创建一个非唯一、普通索引。

1. 自动创建
   【示例】主键自动创建索引
   –在emp表的empno上增加主键PK_EMP

思考：
创建主键的同时自动创建索引的好处(数据库的机制)：是为了提高查询速度，一般业务中通过主键查询的比较频繁。

2.手动创建
语法：

提示：可以在一个列或多个列上同时创建索引。
提示：如果在多个列上创建索引，也称之为联合索引。（类似联合主键的概念）
【示例】手动创建索引

–在指定的表空间上创建索引
–使用工具：

–生成的sql：
– Create/Recreate indexes
create index idx_emp_ename on EMP (ename);

–缺点：没有指定表空间，生产环境下一般要将索引单独指定表空间。
create index idx_emp_ename on EMP (ename) tablespace USERS;

–如果在生产环境下，创建表空间的脚本最好不要指定表空间的具体参数，例如将下图的深蓝色的地方去掉。

10.3.2.删除索引
语法：

【示例】删除上例建立的索引idx_emp_ename
Drop index idx_emp_ename;

10.4.执行计划Explain Plan
如何来查看索引是否生效以及预估索引性能效果呢？
采用执行计划。这里我们使用工具的执行计划功能。（sqlplus的执行计划功能可以课后自己了解，此处不做赘述）。

打开执行计划窗口有两种方式：
直接打开执行计划的空窗口，然后输入需要执行的SQL查询语句。
在SQL查询窗口中的语句上，按F5，可以自动打开执行计划窗口，并且会将选中的SQL语句自动填入执行计划窗口中自动执行。

下面我们演示一下如何查看索引的使用情况和效率
【准备数据】–补充知识：脚本的执行

将准备好的脚本在数据库执行。执行脚本有两种方式：
1.使用command窗口的editor子窗口执行。
将脚本直接粘贴到editor窗口，然后点击执行按钮或按F8：

2.在命令行使用@方式执行。
在命令行下执行：@sql脚本路径，如：
@E:\教学资料\上课资料\oracle\2017-03-22(期oracle课件)\day3\课前资料\上课测试数据\bigdatatest.sql
@E:\教学资料\上课资料\oracle\2017-03-22(期oracle课件)\day3\课前资料\上课测试数据\bigdatatest.sql
@E:\教学资料\上课资料\oracle\2017-03-22(期oracle课件)\day3\课前资料\上课测试数据\bigdatatest.sql
@E:\教学资料\上课资料\oracle\2017-03-22(期oracle课件)\day3\课前资料\上课测试数据\bigdatatest.sql

第二种方法适合大量脚本的批量执行。推荐

【示例】演示索引的效率查看

–在testname字段上创建索引

–查询语句：

–创建索引前后的结果对比：

10.5.强制索引—了解
在查询字段上增加索引后，查询操作时索引一定会生效么？
将上面的语句修改一下，执行后发现索引没有生效：

效果：索引失效！

解决方案是：使用强制索引。
强制索引的语法：
/+INDEX(表名,索引名字)/
【示例】
SELECT /+index(bigdatatest,idx_bdt_testname)/ * FROM bigdatatest WHERE testname <> ‘name0’;

强制索引对于超大的数据量的表来说是有一定的作用的，虽然它是全索引扫描，但扫描索引比扫描表速度还是快。

强制索引的使用注意：
1．尽量少用强制索引，如何避免使用，条件上尽量不要用<>, like ‘%aa%’
2．如果要用强制索引，在非常大的数据量的情况下使用。

10.6.索引的创建场景
索引不是万能！
以下情况可以创建索引:
列中数据值分布范围很广
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
表经常频繁更新（看需求，如果表经常不断的再更新，Oracle会频繁的重新改动索引，反而降低了数据库性能。但如系统日志历史表，就必须增加索引，效率超高）

面试题：

1. 索引的作用是什么？
   主要是提高查询效率,减少磁盘的读写，从而提高数据库性能。
2. 创建索引一定能提高查询速度么？
   未必！得看你创建的索引的合理性。
3. 索引创建的越多越好么？
   不是！索引也是需要占用存储空间的，过多的索引不但不会加速查询速度，反而还会降低效率。