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

Oracle 函数、视图、索引、同义词

• 一、字符串函数
• 二、Oralce 的索引
• • • 2.1 索引的概念及使用规则
    • 2.2 索引的分类
    • 2.3 索引的语法
    • 2.4 索引的创建
    • 2.5 索引的修改和删除
• 三、Oracle 视图
• 第四节 Oracle 同义词

引言

在前面的学习过程中，我们接触过一些特定函数，比如

聚合函数 max() min() count() sum() avg()

但是函数具有什么作用?

答： 在 Oracle中，函数分为系统函数 和 自定义函数。通过函数，我们能够封装一些方法，而达到简化程序的作用，同理在 Oracle 中通过函数，我们能够实现更多的骚操作

一、字符串函数

常见函数及功能一览表

函数名	函数功能
RPAD	在列的右边粘贴字符
LPAD	在列的左边粘贴字符
LTRIM	删除左边出现的字符串
RTRIM	删除右边出现的字符串
SUBSTR	取子字符串，截取字符串
REPLACE	将一个字符串中的子字符串替换成其他的字符串
TRIM	删除字符串两边的字符串，如删除字符串两边的空格，删除字符串两边的#字符
CONCAT	字符串连接函数
INITCAP	首字符大写函数
INSTR	字符串查找函数
UPPER、LOWER	字符串全部大写、小写函数
LENGTH	直接获取字符串长度

1.1 concat （字符串链接函数）

基本语法：
concat(带拼接的字符串1，带拼接的字符串2)

示例1：

select concat('1234-','5678') from dual

-- result 1234-5678

注意：

1. concat 只能连接两个字符串
2. 如果要连接两个以上的字符串，就要使用 ‘||’ 双竖杠的方法

示例2：

select '123' || '4567' || '8907' from dual

-- result: 12345678907

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 <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 视图的修改和删除

修改视图：
通过 or replace 重新创建同名视图即可

修改视图：
DROP VIEW VIEW_NAME语句删除视图
删除视图的定义不影响基表中的数据
只有视图所有者和具备DROP VIEW权限的用户可以删除视图
视图被删除后，基于被删除视图的其他视图或应用将无效。

3.5 实践练习

创建视图查询不同部门的最小缴税额
最大缴税额
平均缴税额（保留两位小数）
通过视图查询此信息

-- 只会查询一条数据
 create or replace view vw_emp_rs
  (maxsal,minsal,avgsal,)   
   as  select max(SAL),min(SAL),avg(SAL) from emp with read only;
   
   select * from vw_emp_rs;

第四节 Oracle 同义词

4.1 同义词定义

Oracle的同义词（synonyms）
从字面上理解就是别名的意思，和视图的功能类似，就是一种映射关系。它扩展了数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;Oracle数据库中提供了同义词管理的功能。同义词是数据库对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应方案对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、物化视图、序列、函数、存储过程、包、同义词等等，数据库管理员都可以根据实际情况为他们定义同义词。

4.2 同义词分类

Oracle同义词有两种类型，分别是Oracle公用同义词与Oracle私有同义词。普通用户创建的同义词一般都是私有同义词，公有同义词一般由 DBA创建，普通用户如果希望创建同义词，则需要CREATE PUBLIC SYNONYM这个系统权限。

1)Oracle公用同义词:由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。

2)Oracle私有同义词:它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。

4.3 同义词的创建与删除

-- 对 scott 用户和其他用户赋予访问其他用户的表的权限
grant select any table to scott（用户名）

-- 1. 创建公共同义词（create public synonym table_name for user.table_name;）

create public synonym emp for scott.emp;

-- 2.创建私有同义词（create synonym table_name for user.table_name;）

create synonym myemp for scott.emp;

-- 不同的用户通过同义词进行查询
select * from emp
select * from myemp

-- 删除同义词
drop synonym emp;
drop public synonym myemp

4.4 完成以下练习

使用不同的用户分别创建公共同义词和私有同义词
并使用不同用户查询同义词 
查看结果

总结：

Oracle函数
     --字符串函数
     --数字函数
     --日期函数
     --转换函数
     --其他函数（通用函数）

Oracle索引
    --索引的作用和特点
    --按使用方式区分
    --按索引类型区分
    --索引的创建修改删除

Oracle视图
    --视图的作用和特点
    --视图的分类
    --视图的创建和修改删除

Oracle同义词
    --同义词的作用
    --同义词的创建和修改