Oracle数据库学习笔记（五 —— 函数、视图、索引

– 5. 显示员工姓名为5个字符的员工

select ename from emp where length(ename) = 5

– 6. 显示所有员工姓名的前三个字符

select substr(ename,1,3) from emp

– 7. 显示所有员工姓名的后三个字符

select substr(ename,length(ename)-3,3) from emp

– 8. 以字符长度为10的方式显示员工职位，多余的位数在右边以*来填充

select rpad(job,10,‘+’) from emp

– 9. 找出字符串"oracle training"中第二个ra出现的位置 instr 查找函数

– 四个参数，

– 第一个参数代表：被查找的字符串

– 第二个参数代表：要查找的字符串

– 第三个参数代表：查找的起始位置

– 第四个参数达标：第几次出现

select instr(‘oracle training’,‘ra’,1,2) from dual

– 10. 去除字符串‘ aadde gf ’两边的空格

select trim(’ aadde gf ') from dual

– 11. 以指定格式显示员工的工资（格式：SMITH 的工资是 800）

select e.ename || ’ 的工资是 ’ || e.sal from emp e;

– 12 显示所有员工的姓名,用a替换所有"A"

select ename from emp

select replace(ename,‘A’,‘a’) from emp

– 13 显示员工姓名中包含‘H’的员工

select ename from emp where ename like ‘%H%’

– 14 显示员工姓名中第二个字符是‘L’的员工

select ename from emp where ename like ‘_L%’

– 15 显示员工姓名中最后一个字符是‘T’的员工

select ename from emp where ename like ‘%T’

作业 1.2 数字函数

– 1 显示在一个月为30天的情况所有员工的日薪,忽略余数

select ename 员工,ceil(sal/30) 日期 from emp

– 2 显示员工的工资为800倍数的员工信息

select * from emp where mod(sal,800) = 0

– 3 对345.543进行向上取整 向下取整 四舍五入 直接舍去

– 向上取整

select ceil(345.543) from dual

– 向下取整

select floor(345.543) from dual

– 四舍五入

select round(345.543) from dual

– 直接舍去

select trunc(345.543,0) from dual

作业 1.3 日期函数

– 1 查询一个月前入职的员工（修改或添加员工表信息 出现上个月入职的员工记录再进行查询）

select ename,hiredate from emp where hiredate =to_date(‘1982/1/1’,‘yyyy/mm/dd’)

– 2 显示员工在此公司工作了几个月（要求结果是整数）

select ename 员工,floor(months_between(sysdate,hiredate)) 工作月份 from emp

– 3 显示每月倒数第3天入职的所有员工

select * from emp where last_day(hiredate)-2 = hiredate

– 4 显示入职满10年的员工的姓名和受雇日期。

select ename 员工,hiredate 入职日期 from emp where floor(months_between(sysdate, hiredate) / 12)>=10

作业 1.4 转换函数

– 1 显示所有12月份入职的员工

select * from emp where to_char(hiredate,‘mm’) = 12

– 2 显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序

select ename 员工姓名,to_char(hiredate,‘yyyy-mm’) from emp order by to_char(hiredate,‘yyyy’) desc

– 3 显示所有1981年2月20日之前入职的员工

select ename 姓名,to_char(hiredate,‘yyyy-mm-dd’) 入职年份 from emp where to_char(hiredate,‘yyyy’) <=1981 and to_char(hiredate,‘mm’)<=2 and to_char(hiredate,‘dd’)<=20

作业 1.5 其他函数

– 1 显示员工的年薪（12个月的工资+补贴）comm为补贴

select ename 姓名,sal*12+nvl(comm,0) 年薪 from emp

– 2 根据员工工资 显示缴税金额

– 工资0-1000 缴税1%

– 工资1000-1500 缴税5%

– 工资1500-3000 缴税10%

– 工资3000以上 缴税20%

select empno,

ename,

sal,

case

when sal >= 3000 then

sal*0.2

when sal >=1500 then

sal*0.1

when sal >= 1000 then

sal*0.05

else

sal*0.01

end salLevel

from emp

二、Oralce 的索引

===========================================================================

2.1 索引的概念及使用规则

索引的概念可以应用到数据库表上。当一个表含有大量的记录时，Oracle 查找该表的特写记录需要花费大量时间 （类比花费大量的时间来查找书中的主题一样）。我们可以在 Oracle 中建立一个次隐藏表，该表包含主表中一个或多个重要的列。以及主表中相应行的指针。这里，与书中的页码一一对应，该隐藏的此表（索引表）中的指针就是行号。

通过索引表，Oracle 可以精确地知道要查中安的特定数据在哪一行上，由于索引比引用表要小得多，因此用索引表查找表中数据比不用索引表查找来的快喝多。在一个大表中，建立该表的索引，查询速度能加快几十倍

• 索引是一种与表相关的数据库逻辑存储结构

• 如果将表看成一本书，则索引的作用类似于书中的目录

• 合理安排索引列

• 在create index语句中，列的排序会影响通过索引进行查询的性能，我们通常把最常用的列放在前面。

• 限制表中索引的数量

• 对于必须读取表中内容的命令，索引改善了它们的响应时间。这意味着 SELECT、UPDATE 和 DELETE 命令都能够更快地进行运行，如果该表有对应于这些命令的列的索引。

• 但是增加表的索引并不能提高 INSERT 命令的输入数据的速度，相反还要降低运行速度。因为索引本身实际上是一个表，因此当对表添加一个记录时，Oracle必须做两次插入。

反之如果需要进行频繁插入的表 使用过多索引会降低插入速度

2.2 索引的分类

从使用方式来区分

• 单列索引与符合索引

• 一个索引可以呦一个或多个列组成，用于创建索引的列被称为 “索引列”

• 单列索引是基于单个列所创建的索引，符合索引是基于多列所创建的索引

• 唯一索引与非唯一索引

• 唯一索引是索引列值不能重复的索引，非唯一索引是索引列之可以重复的索引

• 无论是唯一索引还是非唯一索引，索引都允许取 null 值

从索引类型分

• 标准（B-tree index ，B树）索引

在使用CREATE INDEX语句创建索引时，默认创建的就是B树索引B树索引能够适应多种查询条件，包括使用“=”的精确匹配、使用“LIKE”的模糊匹配、使用“<”或“>”的比较条件。

• 位图索引

基数 : 是指某个列可能拥有的不重复值的个数。例如，性别列的基数为2（性别只能是男或女），婚姻状况列的基数为3（婚姻状况只能是未婚、已婚、离异）

对于一些基数很小的列，B树索引处理方式的效率比较低

对于基数很小、只存在有限的几个固定值的列（如性别、婚姻状态、行政区、职称），为了加快查询效率，应该在这些列上创建位图索引

2.3 索引的语法

• 创建索引的语法

CREATE [UNIQUE] INDEX [SCHEMA.]index_name ON table_name (col_name)

[TABLESPACE ts] --表示索引存储的表空间

[STORAGE s] --表示存储参数

[PCTFREE pf] --表示索引数据块空闲空间的百分比 ，一个班30人，

[NOSORT ns] --表示不再排序

[SCHEMA] --表示Oracle模式，缺省默认为当前账户

2.4 索引的创建

• 唯一索引

• 在 emp 表的 ename 列上创建一个唯一索引

idx_emp_ename，创建之后该表中 ename就不允许出现重复值

create unique index idx_emp_ename on emp(ename);

inser into emp(empno,ename,job) values(7800,‘jack’,‘cleark’); – 插入数据失败

• 复合索引

• 如果 select 语句中 where 子句引用了复合索引中的所有列或者大多数列，则使用复合索引可以显著地提高查询速度

• 创建此类索引时，应该注意定义中使用的列的顺序

create index idx_emp_ename_job on emp(ename,job);

• 位图索引

• 由于emp表的job列、deptno列的取值范围有限，并且经常要基于这些列进行查询、统计、汇总工作，所以应该基于这些列创建位图索引

create bitmap index idx_bm_job on emp(job);

• 在 sal 字段创建 B树索引 index_test_city

• 在 job 字段创建位图索引 index_test_age;

create index IDX_EMP_SAL on emp(sal); – B树索引

create bitmap index IDX_EMP_JOB on emp(job); --位图索引

2.5 索引的修改和删除

• 修改索引

ALTER [UNIQUE] INDEX index_name

[INITRANS n] --一个块内同时访问的初始事务的入口数

[MAXTRANS n] --一个块内同时访问的最大事务的入口数

REBUILD --表示根据原来的索引结构重建索引

[STORAGE ] --表示存储数据

alter index IDX_ID rebuild storage(initial 1M next 512k);

–数据库对IDX_ID重新生成，并申请1M空间，超出1M每次额外申请512kb的空间来保存索引

Drop index SCHEMA.index_name;

–表结构被删除,那么与该表相关的索引也会一起被删除

三、Oracle 视图

==========================================================================

3.1 视图的基本概念

1. 什么是视图？

答: 视图（view）：也称虚表，不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。

2. 什么是基表？

视图是从一个或多个实际表中获得。这些表的数据存放在数据库中，那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

3. 视图数据的修改

视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。

注意：

查询视图没有什么限制,插入/更新/删除视图的操作会受到一定的限制;所有针对视图的操作都会影响到视图的基表;为了防止用户通过视图间接修改基表的数据,可以将视图创建为只读视图(带上with read only选项)

3.2 视图的创建

使用视图的前提，需要进入 sys 用户，给 scott 用户设置创建视图的权限

grant create view to scott --首先授予scott账号能够创造视图的权限

–管理员进入的方法

–账号:sys

–密码:sys as sysdba

–选择管理员的角色sysdba 进入即可

select max(SAL),min(SAL),avg(SAL),count(*),sum(SAL) from emp;

create or replace view vw_emp_sum

(maxsal,minsal,avgsal,count1,sumsal)

as select max(SAL),min(SAL),avg(SAL),count(*),sum(SAL) from emp with read only;

select * from vw_emp_sum – 通过视图查询

–创建统计各部门最小工资 最大工资平均工资的视图

CREATE OR REPLACE VIEW dept_sum_vw

(name,minsal,maxsal,avgsal)

AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname

WITH READ ONLY;

通过视图查询不同部门的工资统计情况

select * from dept_sum_vw

3.3 视图的原则

视图的定义原则

1. 视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询

2. 在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用 ORDER BY 子句

3. OR REPLACE选项可以不删除原视图便可更改其定义并重建。

4. 视图主要用来存储 需要查询的复杂的 数据关系 ，而不希望用户通过视图修改数据，所以通常可以加上 READ ONLY

3.4 视图的修改和删除

修改视图：

复习的面试资料

这些面试全部出自大厂面试真题和面试合集当中，小编已经为大家整理完毕（PDF版）

• 第一部分：Java基础-中级-高级

• 第二部分：开源框架（SSM：Spring+SpringMVC+MyBatis）

• 第三部分：性能调优（JVM+MySQL+Tomcat）

• 第四部分：分布式（限流：ZK+Nginx；缓存：Redis+MongoDB+Memcached；通讯：MQ+kafka）

• 第五部分：微服务（SpringBoot+SpringCloud+Dubbo）

• 第六部分：其他：并发编程+设计模式+数据结构与算法+网络

进阶学习笔记pdf

• Java架构进阶之架构筑基篇（Java基础+并发编程+JVM+MySQL+Tomcat+网络+数据结构与算法）

• Java架构进阶之开源框架篇（设计模式+Spring+SpringMVC+MyBatis）

• Java架构进阶之分布式架构篇 （限流（ZK/Nginx）+缓存（Redis/MongoDB/Memcached）+通讯（MQ/kafka））

• Java架构进阶之微服务架构篇（RPC+SpringBoot+SpringCloud+Dubbo+K8s）

Java架构进阶之架构筑基篇（Java基础+并发编程+JVM+MySQL+Tomcat+网络+数据结构与算法）

[外链图片转存中…(img-8llvdPg4-1714436084515)]

• Java架构进阶之开源框架篇（设计模式+Spring+SpringMVC+MyBatis）

[外链图片转存中…(img-WncKquqv-1714436084516)]

[外链图片转存中…(img-Owe5qDvC-1714436084516)]

[外链图片转存中…(img-04GpIgvx-1714436084516)]

• Java架构进阶之分布式架构篇 （限流（ZK/Nginx）+缓存（Redis/MongoDB/Memcached）+通讯（MQ/kafka））

[外链图片转存中…(img-wwbzvdMj-1714436084517)]

[外链图片转存中…(img-fyS7vD1V-1714436084517)]

[外链图片转存中…(img-EFdJMcjj-1714436084517)]

• Java架构进阶之微服务架构篇（RPC+SpringBoot+SpringCloud+Dubbo+K8s）

[外链图片转存中…(img-k9i6vbjR-1714436084518)]

[外链图片转存中…(img-LMvPa8tm-1714436084518)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录