本文共 5755 字，大约阅读时间需要 19 分钟。

本文在前面的文章里面已经把Oracle的初步使用讲解了，从这篇开始将系统的讲解其他的语法知识。这里推荐学习:

一.Oracle函数

1.单行函数

字符操作

1.lower():转小写

2.upper():转大写 3.Initcap:首字母大写 4.substr(str,index):从str里面的第index个字符往后截取，index从1开始 substr还有一种用法:substr(str,index,length):从str的第index个字符开始截取length长度的字符，包括第index字符 5.instr(str1,str2):获取str2在str1里面的位置，索引从1开始 6.length():获取字符串长度 7.lengthb():获取字符所占的字节数 8.lpad(str,length,str2):在str左边填充字符串str2直到字符长度为length 对于中文，一个中文占两个位置长度 一个字母占一个位置长度 对于不可以在添加时遇到要分割str2的情况时，应该从左往右切割知道满足length为止 9.rpad:右填充，同左填充 10trim(char from str2):去掉字符串str2中首尾的字符char 11.replace(str,str1,str2):把str里面的str1全部替换为str2

浮点操作

1.round(number,index):四舍五入

index	精确的位置
大于0	精确到小数点后第index位
等于0	精确到小数点前第1个位
小于0	精确到小数点前第(-index+1)位

2.trunc(number,index):截断操作 index介绍同上

时间操作

时间格式化

函数常用格式 1.sysdate:本地时间(记住当前日期是2020/8/18,后面会用到) 2.to_char(date,’formate_model’):日期格式转换或数字转换 3.时间的加减 两个时间相减得到的是相差的天数。 4.months_between():计算两个时间相差的天数 5.add_months(date,n):当前日期加上n个月后的时间 6.last_day(date):当前月的最后一天 7.next_day(date,arg):下个星期几的日期，arg可以填1-7(注意1是星期天，不是星期一)或者monday-sunday

空操作

1.nvl2(a,b,c):a不为空返回b,为空返回c

2.nvl(a,b):如果a为空，返回b，否则返回a 3.nullif(a,b):a与b相等返回null,否则返回a 4.coalesce(a,b,c,...):从左到右第一个部不为空的值

条件判断操作

1.case when then else end的使用

2.decode() 下面实现MANAGER加300，SALESMAN加100

2.分组查询

常用组函数

组函数计算时会自动忽略空值。

1.avg():求平均值 2.count():求记录数 3.max()求最大值 4.min():求最小值 5.sum():求和 在使用having与where的时候，where后不可接分组函数，having可以

group by的使用

注意:

①group by后面的分组字段如果有空值，空值被当做额外的一组 ②使用了group by之后，select后面的字段如果不是在分组函数里面作为参数，那么该字段只可以为group by后面的字段 1.查询部门平均工资

2.查询平均工资超过2000的部门

3.rollup:汇总 从上面图片中我们可以看到加了rollup()后多了4行。这里rollup(deptno,job)等效于先按照(deptno,job)分组，这样得到的是我们第一张图片的结果，接下来按照deptno在一次分组，得到前面多余的三行，最后整张表当做一组我们得到了最后一行。 如果这样解释看不出来含义，可以参见 4.break on 字段 skip number:按照字段隔number行格式化显示

二.查询

1.多表查询

连接查询

1.等值连接:有外键关联

2.不等值连接:无外键关联 3.左外连接:对于某些不成立的记录，希望包含在最后的结果中(针对左表) 左外连接: 4.右外连接:对于某些不成立的记录，希望包含在最后的结果中(针对右表) 右外连接: 5.自连接

层次查询

我们来看一下emp表的等级结构图(红色数字代表层次):

我们把emp表的内容贴在这里方便讲解: select level,empno,ename,mgr from emp start with mgr is null connect by prior mgr=empno; 解析:

start with:接上作为根节点的条件，这里是mgr is null，			自然找到了king这一条作为根节点connect by prior:接上递归遍历的条件，这里是mgr = empno,就是找到empno与				我们当前记录(也即king的这一条记录)的mgr相等记录，很明显				没有找到，与最后只找到了一条记录如上图。level:是一个伪例，他来显示当前记录在树里面的层次

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

解析:

这里只把上一个演示的mgr = empno改为了empno=mgr，那么就相当与就是找到mgr与				我们当前记录(也即king的这一条记录)的empno相等记录，我们找				到了jones这一条记录，接着再按照上面的规则找到SCOTT，接下来				一直这样下去直到找完所有的记录(最后我们可以发现它是遵循深度				优先搜索的)

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

这一个演示是为了看的更加清晰加上了排序语句。 如果想更加深入的了解请参见:

2.子查询

子查询语法：

select select_list from table where expr operator(select select_list from table)

1.子查询需要括号

要求:查询工资比SCOTT高的员工信息 2.select后可以添加子查询 要求:查询员工记录后，每一行后面显示上级 3.from后面的子查询 要求:查询员工信息：员工号 姓名 月薪 注意:select empno,ename,sal from emp与上面的语句效果不仅一样，在性能上也是一样的，因为Oracle内部会进行重构。 4.in 在集合中 要求:查询部门名称是SALES和ACCOUNTING的员工 5.any: 和集合中的任意一个值比较 要求:查询工资比30号部门至少一个员工高的员工信息 any的作用是输出最小值 6.all 和集合中的所有值比较 要求:查询工资比30号部门所有员工高的员工信息 all的作用是输出最大值 7.多行子查询中的null in可以有null 要求:查询是老板（管理层）的员工 not in 里面不可以有null 要求:查询不是老板(管理层)的员工

3.集合运算(用的比较少)

并集

并集使用union(去重)与union all(不去重)，注意事项是:

1.参与运算的各个集合必须列数相同且类型一致2. 采用第一个集合作为最后的表头3. order by 永远在最后

推荐:少用，性能差,提到性能可以使用Set timing on,每次操作后在最后面会显示时间: 不想显示可以用:Set timing off

差集

交集

4.行号与行地址

1.行号(rownum伪例)

注意是:select rownum,e.* from emp e;而不是select rownum,* from emp； 后者会报错。 使用rownum后他都是默认顺序的存在，不会因为排序改变位置而改变: rownum只能使用 < <=; 不能使用> >=: 2.rowid:行地址 上面的rowid是一个默认的伪例

5.临时表

create global temporary table 表名:创建临时表

on commit delete rows;表示提交后就删除临时表里面的数据。

6.行转列

这一个用到了wm_concat(字段名)函数，一般与group by连用。

7.Oracle分页处理

要求获取薪资前3

我们执行下面语句会发现，虽然薪资顺序对了，但是行名除了问题，我们前面讲过了rownum是默认的。 我们改用下面: 要求获取薪资前4-6： 我的思路是：先利用我们获取前三名时用到过的语句(红框里面的)，这个时候红框里面就是一个新表，rownum我们取别名为r,那么这个时候用r，就可以避免rownum不可以用>与>=的问题了。

三.数据处理

1.地址符的使用

作为列名使用: 作为表名使用:

2.批处理

1.复制整张表

2.只复制表的结构 3.插入数据 将emp表里面所有deptno=10的数据插入到emp2里面。

四.事务相关

1.delete与truncate的区别

1.delete是逐条逐条的删除，truncate是直接删除整张表2.本质上delete是DML操作，可以回滚，而truncate是DDL操作，不可以回滚3.delete可以flashback(闪回),而truncate不可以

下面我们做一个简单的测试:

我们重新导入表执行下面: 从耗时上来看，delete要快一点。在测试的过程当中我发现了下面问题: 原因在于,没有清理回收站，解决如下: 补:show recyclebin是显示回收站。 2.事务的标志

起始标志:事务中第一条DML语句结束标志:	提交： 显式 commit           隐式 正常退出（exit），DDL，DCL    回滚:  显式 rollback           隐式 非正常退出，掉电，宕机

3.保存点

五.表的创建与管理

1.添加字段

当前表结构是: 我们需要添加一个字段pic,类型是blob(二进制) 2.删除字段 删除pic字段 3.修改表字段类型 4.重命名字段 5.修改表名 6.删除表 7.显示回收站与清理回收站 8.恢复删除的表 9.check约束 方案1:表已经创建，直接修改 方案2：创建表的时候就约束 例子:create table ta(id number check(id between 1 and 20)); 关于check的更多用法可参见: 10.外键约束

六.常见数据库对象

1.视图

视图是一张虚表，他基于已有的表(这些表我们称之为基表)，视图是向用户提供基表的另外一种形式。视图的数据来自于select语句，因此我们可以将视图理解为存储起来的select语句。

视图的优点:	限制数据访问	简化复杂查询	提供数据的相互独立	同样的数据可以有不同的显示方式缺点:	但视图不能提高查询性能

开启视图权限

前面的讲解我们都是以scott与hr两个用户来登录连接数据库的，但是它们本身不具有使用视图的权限，因此我们需要开启权限。

我们在主系统上面可以连接数据库，使用的是instantclient_12_1这个工具。现在我们将其压缩共享到我们的虚拟机里面去。然后解压后配置环境变量即可。 下面是查看1环境变量是否配置成功: 接下来我们以数据库管理员的身份来登录: sqlplus sys/manager as sysdba; 授权如下: 接下来我们在主系统重新连接数据库:

创建视图

查询视图结构

创建只读视图

视图一般只做查询，所以一般设置为read only.

还有另外一种写法就是创建视图时如果存在可以替换掉

删除视图

2.序列

序列的特点:

可供多个用户用来产生唯一数值的数据库对象自动提供惟一的数值共享对象 主要用于提供主键将序列值装入内存可以提高访问效率

创建序列

创建好了序列以后我们来查看序列的值。

删除序列

利用序列来插入表数据

利用序列来插入表数据可以保证标识唯一性。

3.索引

索引时一种独立于表的模式对象，可以存储在与表不同的磁盘或者表空间。

1.索引可以提高查询的速度(实际是通过指针来提高的),但他的存在与否不会对表产生除速度外的的其他影响，2.Oracle会帮我们管理与使用索引，我们不需要去指定何时使用索引3.一张表被删除，与他相关的索引也会被删除4.减少磁盘I/O(比如没有所索引，那么可能会需要多次I/O操作来确定是否为需要的数据，但是有了索引可以一次性的找到)

索引我们只需要创建即可，其他的我们不要管。

创建索引的时机:

1.列中数据值分布很广2.列经常在where语句或连接条件中出现3.表经常被访问，而且量很大，访问的数据大概占总量的2%~4%

什么时候不推荐创建索引:

1.表很小2.经常更新3.列不经常在where语句或连接条件中出现

4.同义词

同义词的作用就取别名，他可实现不同用户下的数据交互，但是使用同义词和我们的视图一样，我们需要设置权限:

现在我们要求scott用户可以访问到hr用户的数据。 首先以hr用户的身份登录数据库，然后开启访问权限给scott: 上面这个授权允许scott用户操作jobs表，并且只允许查询操作，如果要授权其他操作，可以把select改为insert,update. 接下来我们退出hr登录以scott登录，然后对jobs表进行查询操作: 接下来我们取别名在进行操作: 那么我们本章的内容就讲到这里结束了，下一篇我会继续讲解Oracle的图形化界面的使用。如果你认为本文对你有帮助，记得给博主点个赞呀！

转载地址：http://iolzi.baihongyu.com/