目录
Oracle函数
- 字符串函数
- 数字函数
- 日期函数
- 转换函数
- 其他函数（通用函数）
Oracle索引
- 索引的作用和特点
- 按使用方式区分
- 按索引类型区分
- 索引的创建修改删除
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
注意：
- concat 只能连接两个字符串
- 如果要连接两个以上的字符串，就要使用 ‘||’ 双竖杠的方法
示例2：
select '123' || '4567' || '8907' from dual
-- result: 12345678907
1.2 首字符大写 initcap
无论参数由怎样的字母构成，都能将第一个字母大写，其他字母小写
语法：
initcap(待转换的字符串) as name
示例1：
作业 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就不允许出现重复值
- 在 emp 表的 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 视图的基本概念
- 什么是视图？
答: 视图（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选项可以不删除原视图便可更改其定义并重建。
- 视图主要用来存储 需要查询的复杂的 数据关系 ，而不希望用户通过视图修改数据，所以通常可以加上 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同义词
--同义词的作用
--同义词的创建和修改