Oracle数据库学习笔记（五 —— 函数、视图、索引(1)-CSDN博客

本文链接：https://blog.csdn.net/ghfgjfg679/article/details/138333523

– 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 视图的基本概念

什么是视图？

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

什么是基表？

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

视图数据的修改

视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些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 视图的原则

视图的定义原则

视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询
在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用 ORDER BY 子句
OR REPLACE选项可以不删除原视图便可更改其定义并重建。

最后

现在其实从大厂招聘需求可见，在招聘要求上有高并发经验优先，包括很多朋友之前都是做传统行业或者外包项目，一直在小公司，技术搞的比较简单，没有怎么搞过分布式系统，但是现在互联网公司一般都是做分布式系统。

所以说，如果你想进大厂，想脱离传统行业，这些技术知识都是你必备的，下面自己手打了一份Java并发体系思维导图，希望对你有所帮助。

ECT d.dname,min(e.sal),max(e.sal),avg(e.sal)