Oracle数据库学习笔记（五 —— 函数、视图、索引(1)，2024年最新mysql面试题写sql语句

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

1.2 首字符大写 initcap

无论参数由怎样的字母构成，都能将第一个字母大写，其他字母小写

语法：

initcap(待转换的字符串) as name

作业 1.1 ：

– 练习题 1

– 1. 以首字母大写的方式显示所有员工的姓名

select initcap(ename) from emp

– 2. 将员工的职位用小写显示

select lower(job) from emp

– 3. 将员工的名字分别用大写和小写显示

select upper(ename) 大写姓名,lower(ename) 小写姓名 from emp

– 4. 将员工名字，首字母小写，其他字母大写的方式显示

select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename))) from emp

– 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)

最后

毕竟工作也这么久了 ，除了途虎一轮，也七七八八面试了不少大厂，像阿里、饿了么、美团、滴滴这些面试过程就不一一写在这篇文章上了。我会整理一份详细的面试过程及大家想知道的一些问题细节

美团面试经验

字节面试经验

菜鸟面试经验

蚂蚁金服面试经验

唯品会面试经验

因篇幅有限，图文无法详细发出

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
sal,maxsal,avgsal)

最后

毕竟工作也这么久了 ，除了途虎一轮，也七七八八面试了不少大厂，像阿里、饿了么、美团、滴滴这些面试过程就不一一写在这篇文章上了。我会整理一份详细的面试过程及大家想知道的一些问题细节

美团面试经验

[外链图片转存中…(img-BJcZdX3m-1713568670797)]
字节面试经验
[外链图片转存中…(img-mQOPUU18-1713568670798)]
菜鸟面试经验
[外链图片转存中…(img-oGmyoT9M-1713568670798)]
蚂蚁金服面试经验
[外链图片转存中…(img-KGNderLZ-1713568670798)]
唯品会面试经验
[外链图片转存中…(img-oLYDCoEq-1713568670799)]

因篇幅有限，图文无法详细发出

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-FnUpWJcR-1713568670799)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！