Oracle【基础函数、视图、索引】

1. 认识oracle数据库

Oracle是非常强大的数据库软件。默认端口：1521。与MySQL不同的是，Oracle数据库的概念是一个操作系统装的就是一个大的数据库。一个数据库可以有很多个实例，每个实例占用一系列的进程和内存。通常一台机器只用一个实例。每个实例可以有很多个用户。每个用户可以有很多张表，这个用户类似MySQL的databases。在建表的时候指定对应的表空间（逻辑空间），该表的数据就会都存在表空间对应的数据文件上。

2. oracle基本概念

• 实例：(Oracle Instance)包括进程和内存，一个oracle数据库有多个实例。
• 表空间：oracle对物理上的相关数据文件的逻辑映射

设计自来水收费系统完成oracle基础需求
表设计：

– 创建表空间

create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m

– 创建用户

create user hfnu112
identified by root
default tablespace waterboss

– 给用户赋予权限dba

grant dba to hfnu112

单表查询

-- PLSQL中文乱码问题（显示问号）解决办法
select userenv('language') from dual;
-- 1. 简单条件查询
-- 1.1 精确查询
-- 查询水表编号30408的业主记录
select * from t_owners where watermeter = '30408'; 
select id,name,adddate from t_owners where watermeter = '30408'; 
-- 1.2 模糊查询
-- 查询业主名称包含姓刘的业主记录
select * from t_owners where name like '%刘%';   
-- 1.3 and运算符
-- 查询业主名称包含刘并且门牌号包含1的业主记录
select * from t_owners where name like '%刘%' and housenumber = '%1%';
-- 1.4 or运算符
-- 查询业主名称包含刘或者门牌号包含1的业主记录
select * from t_owners where name like '%刘%' or housenumber = '%1%';
-- 1.5 and 和 or 运算符混合使用
-- 查询业主名称包含刘或门牌号包含1的业主记录, 并且地址编号为3
select * from t_owners where (name like '%刘%' or housenumber like '%1%') and address_id = 3;
-- 1.6 范围查询
-- 查询台账记录用水字数大于等于3000， 并且小于等于5000的记录
select * from t_account where usenum >= 3000 and usenum <= 5000;
select * from t_account where usenum between 3000 and 5000;
-- 1.6 范围查询
select * from t_account;
-- 查询台账记录用水字数大于等于3000， 并且小于等于5000的记录
select * from t_account where usenum >= 3000 and usenum <= 5000;
select * from t_account where usenum between 3000 and 5000;

2. 去除重复记录
-- 查询业主表的地址id, 不重复显示
select distinct address_id from t_owners;
select distinct address_id, ownertype_id from t_owners;

-- 3. 排序
select * from t_account;
-- 对t_account表按使用量进行升序排序
select * from t_account order by usenum;

-- 对t_account表按使用量进行降序排序
select * from t_account order by usenum desc;

-- 4. 基于伪列的查询
-- 伪列就是像表中的列一样，但是在表中并部存储。伪列只能用于查询
-- ROWID：伪列返回的就是该行的物理地址，可以唯一的标识表中的一行
select rowid, own.* from t_owners own;  -- 给表名起别名
select rowid, id, name, address_id from t_owners;

-- ROWNUM: 结果集中每一行标识一个行号，第一行返回1，第二行返回2， ... 通过rownum可以限制查询结果集中返回的行
select rownum, id, name, address_id from t_owners;

-- 5. 聚合统计
-- 5.1 聚合函数
select * from t_account;
-- 统计2022年所有的用户的用水量总和
select sum(usenum) from t_owners where year = '2022';

-- 统计2022年所有的用户的平均用水量
select avg(usenum) from t_owners where year = '2022';

-- 统计2022年所有的用户的最高用水量
select maximize(usenum) from t_owners where year = '2022';

-- 统计2022年所有的用户的最低用水量
select minimize(usenum) from t_owners where year = '2022';

-- 统计业主类型为1的业主数量
select count(*) from t_owners where ownertype_id = 1;

-- 5.2 分组聚合
select * from t_area;
select * from t_account;
-- 按区域分组统计水费的合计数 求和   
select area_id, sum(money) from t_account group by area_id;

-- 查询水费合计大于100的区域统计
select area_id, sum(money) from t_account group by area_id having sum(money)>100;

3. 连接查询

-- 1 内连接查询
-- 查询显示业主编号，业主名称，业主类型名称
select id, name from t_owners;
select name from t_ownertype;

select t_owners.id, t_owners.name,t_ownertype.name  
from t_owners, t_ownertype 
where t_owners.ownertype_id = t_ownertype.id;

-- 查询显示业主编号，业主名称、地址和业主类型
select * from t_owners
select * from t_ownertype
select * from t_area

select ow.id, ow.name, ad.name, ot.name 
from t_owners ow, t_ownertype ot, t_address ad 
where ow.address_id = ad.id and ow.ownertype_id = ot.id;

-- 查询显示业主编号、业主名称、地址、所在区域，业主分类
select * from t_owners
select * from t_address
select * from t_area
select * from t_ownertype


select ow.id 业主编号, ow.name 业主名称, ad.name 地址, ar.name 区域, ot.name 业主分类 
from t_owners ow, t_address ad, t_area ar, t_ownertype ot 
where ow.address_id = ad.id and ow.ownertype_id = ot.id and ad.area_id = ar.id;  

--查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类
select * from t_owners
select * from t_address
select * from t_area
select * from t_ownertype
select * from t_operator

select ow.id 业主编号, ow.name 业主名称, ad.name 地址, ar.name 区域, op.name 收费员, ot.name 业主分类 
from t_owners ow, t_address ad, t_area ar, t_ownertype ot, t_operator op 
where ow.address_id = ad.id and ow.ownertype_id = ot.id and ad.area_id = ar.id and ad.operator_id = op.id;  

-- 2. 左外连接
-- 查询业主的的账务记录，显示业主的编号、名称、年、月、金额，如果此业主没有账务记录也要显示姓名
select * from t_account
select * from t_owners
-- sql1999语法
select ow.id 业主编号, ow.name, ac.year, ac.month, ac.money from t_owners ow left join t_account ac on ow.id = ac.owner_id;
-- oracle语法
select ow.id 业主编号, ow.name, ac.year, ac.month, ac.money from t_owners ow, t_account ac where ow.id = ac.owner_id(+);

-- 3. 右外连接
-- 查询业主的的账务记录，显示业主的编号、名称、年、月、金额，如果此业主没有账务记录也要显示姓名
-- sql1999语法
select ow.id 业主编号, ow.name, ac.year, ac.month, ac.money from t_owners ow right join t_account ac on ow.id = ac.owner_id;
-- oracle语法
select ow.id 业主编号, ow.name, ac.year, ac.month, ac.money from t_owners ow, t_account ac where ow.id(+) = ac.owner_id;

4. 子查询(嵌套查询)

-- 1 where子句单行子查询
-- 查询2022年7月份用水量大于平均值的台账记录
select * from t_account;
-- 查询2022年7月用水量的平均值
select avg(usenum) from t_account where year = '2022' and month = '07';

-- 查询2022年7月份用水量大于3833.33333333333
select *  from t_account 
where year = '2022' and month = '07' and usenum > (select avg(usenum) from t_account where year = '2022' and month = '07');

-- 2 where子句多行子查询
-- 查询地址编号1，3，4的业主记录
select * from t_owners where address_id in (1,3,4);

-- 查询地址含有花园的地址编号 2, 3
select id from t_address where name like '%花园%'; 

select * from t_owners where address_id in (select id from t_address where name like '%花园%');

-- 查询地址不含有花园的业主记录
select * from t_owners where address_id not in (select id from t_address where name like '%花园%');

-- 3  from子句的子查询
-- 查询显示业主编号，业主名称，业主类型名，条件为业主类型为居民  id=1
-- 多行子查询
select * 
from 
(select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型 from t_owners ow, t_ownertype ot where ow.ownertype_id = ot.id)
where 业主类型 = '个人客户';

-- 4 select子句子查询
-- 单行子查询
-- 列出业主信息，包括id、名称、所属地址
select id,name from t_owners;
select name from t_address;

select id,name,
(select name from t_address where id = address_id) 
from t_owners;

-- 列出业主信息包括id 名称 所属地址  所属区域
select id, name,
(select name from t_address where id = address_id) addressname,
(select (select name from t_area where id = area_id) from t_address where id = address_id) areaname
from t_owners;

5. 分页查询

-- 分页查询台账表t_account，每页5条记录
-- 1.简单分页
select rownum, ac.* from t_account ac where rownum <= 5;

select * from
(select rownum r, ac.* from t_account ac)
where r>5 and r <=10;

-- 2.基于排序的分页
select * from t_account order by usenum desc;

select * from
(select rownum r, ac.* from (select * from t_account ac order by usenum desc) ac)
where r <= 10 and r > 5;

6. 单行函数

-- 1. 字符函数
-- 伪表: 测试字符函数时，为了使sql语法完整，没有实际意义 
select * from dual;
-- length 获取字符串的长度
select length('abcd') from dual;

-- substr 截取字符串 起始位置1
select substr('meitong', 2, 2) from dual;

-- concat 拼接字符串 
select concat('hfnu', 'cn') from dual;
select concat(concat('hfnu', 'cn'), 'edu') from dual;
select 'hfnu'||'cn'||'aaa'||'bbb' from dual;

-- 2. 数值函数
-- 四舍五入round
select round(123.567) from dual;
select round(123.456, 2) from dual;

-- 截取数字trunc
select trunc(1001.456) from dual;
select trunc(1001.456, 2) from dual;

-- 取模mod
select mod(618,7) from dual;

-- 3. 日期函数
-- 获取当前日期和时间 sysdate
select sysdate from dual;

-- 在当前日期加上指定月add_months
select add_months(sysdate, 2) from dual;
select add_months(sysdate, -2) from dual;

-- 求所在月的最后一天last_day
select last_day(sysdate) from dual;
select last_day(sysdate-4) from dual;

-- 日期截取trunc
select trunc(sysdate) from dual;
select trunc(sysdate,'mm') from dual;

-- 4. 转换函数
-- 数字转字符串
select to_char(100)||'abc' from dual;

select 100||'' from dual;
-- 日期转字符串
select to_char(sysdate) from dual;
select to_char(sysdate, 'yyyy-MM-dd') from dual;
select to_char(sysdate, 'yyyy-MM-dd hh:mi:ss') from dual;

-- 字符串转日期
select to_date('2022-07-07', 'yyyy-mm-dd') from dual;

-- 字符串转数字
select to_number('123') from dual;
select to_number('123')+10 from dual;
select '100'+10 from dual;

-- 5. 其他函数
-- 空值处理
select nvl(null, 0) from dual;

-- 从显示价格表中业主类型id为1的价格记录，如果上限值为null,则显示00000
select nvl(maxnum, 0) from t_price where ownertype_id = 1;
select nvl(maxnum, 9999999999) from t_price where ownertype_id = 1;
select nvl2(maxnum, to_char(maxnum), '不限') from t_price where ownertype_id = 1;

7. 条件取值

-- 显示以下信息，不要关联查询业主类型表
select name,ownertype_id from t_owners;
select name,decode(ownertype_id, 1, '个人客户', 2, '企业客户') from t_owners;
select name,decode(ownertype_id, 1, '个人客户',100) from t_owners;

select name, (case ownertype_id
                   when 1 then '个人'
                   when 2 then '企业'
                   else '其他'  
                   end) from t_owners;    

8. 行列转换

-- 按月份的水费账务
select 
(select name from t_area where id = area_id) 区域, 
sum(case when month = '01' then money else 0 end),
sum(case when month = '02' then money else 0 end),
sum(case when month = '03' then money else 0 end),
sum(case when month = '04' then money else 0 end),
sum(case when month = '05' then money else 0 end),
sum(case when month = '06' then money else 0 end),
sum(case when month = '07' then money else 0 end),
sum(case when month = '08' then money else 0 end),
sum(case when month = '09' then money else 0 end),
sum(case when month = '10' then money else 0 end),
sum(case when month = '11' then money else 0 end),
sum(case when month = '12' then money else 0 end)
from t_account where year = '2022' group by area_id;
-- 按季度
select 
(select name from t_area where id = area_id) 区域,
sum(case when month >= '01' and month <= '03' then money else 0 end),
sum(case when month >= '04' and month <= '06' then money else 0 end),
sum(case when month >= '07' and month <= '09' then money else 0 end),
sum(case when month >= '10' and month <= '12' then money else 0 end)
from t_account where year = '2022' group by area_id;

9. 分析函数

-- 1. rank 相同的值排名，排名跳跃
-- 对t_account表的usenum字段排序，相同的值排名相同，排名跳跃
select t.* from t_account t order by usenum desc;
select rank() over(order by usenum desc) 排名, t.* from t_account t;

-- 对t_account表的usenum字段排序，值相同排名相同，排名连续
select dense_rank() over(order by usenum desc) 排名, t.* from t_account t;

-- 对t_account表的usenum字段排序，返回连续的排名，无论值是否相等
-- 用row_number实现分页查询相对三层嵌套子查询语法简单
select row_number() over(order by usenum desc) 排名, t.* from t_account t;
-- 分页查询
select * 
from  
(select row_number() over(order by usenum desc) 排名, t.* from t_account t)
where 排名 > 5 and 排名 <=10; 

10. 集合运算

-- 1. 并集运算
-- 业主表
-- union all 包括重复的记录  ；  union 不包括重复记录
select * from t_owners where id > 1004
union all
select * from t_owners where id < 1009;

-- 2. 交集运算
select * from t_owners where id > 1004
intersect
select * from t_owners where id < 1009;

-- 3. 差集运算
select * from t_owners where id < 1009
minus
select * from t_owners where id > 1004;

-- 差集分页查询
select rownum, t.* from t_account t where rownum <= 10
minus
select rownum, t.* from t_account t where rownum <= 5;

11. 视图

视图是从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中，用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE语句修改记录。视图可以使用户操作方便，而且可以保障数据库系统的安全。

• 视图就是封装了一条复杂查询的sql语句。

-- 1）简单视图
-- 创建视图：业主类型为1的业主信息
create view view_owner1 as
select * from t_owners where ownertype_id = 1;
-- 查询视图view_owner1 业主类型为1的业主记录
select * from view_owner1 where ownertype_id = 1;

-- 修改视图view_owner1
update view_owner1 set name = '张三' where id = 1002;
commit;

select * from t_owners;

-- 2) 带检查约束的视图
-- 根据地址表，创建视图，内容为区域id为2的记录
create view view_address2 as
select * from t_address where area_id = 2
with check option;

select * from view_address2 where area_id =2;

-- 修改视图view_address2 ; area_id这个条件不能修改 
update view_address2 set area_id = 3 where name = '景尚花园';
commit;

-- 3）只读视图
-- 根据地址表，创建视图，内容为区域id为2的记录
create or replace view view_address2 as
select * from t_address where area_id = 2
with read only;

select * from view_address2 where area_id =2;

-- 修改视图view_address2  
update view_address2 set name = '花园小区' where id = 2;
commit;
-- 创建带错误的视图
create force view view_stu as
select * from student;
drop view view_stu;

多表关联视图

-- 创建视图，查询业主编号，业主名称、业主类型名称
create or replace view view_owner2 as
select ow.id 业主编号, ow.name 业主名称, ot.name 业主类型名称 
from t_owners ow, t_ownertype ot 
where ow.ownertype_id = ot.id;

select * from view_owner2 where 业主类型名称 like '%个人%';

-- 修改视图view_owner2
update view_owner2 set 业主名称='刘明' where 业主编号 = '1008';
commit; 
-- 键保留表

-- 5） 分组聚合统计
-- 创建视图，按年月统计水费金额
create or replace view view_account1 as
select year, month, sum(money) 水费 
from t_account 
group by year, month
order by year, month;

select * from view_account1;
-- 修改视图view_account1  聚合统计不能修改
update view_account1 set 水费='80' where year='2022' and month='08'
commit;

12. 索引

（1）是一种快速查询表中内容的机制，类似于新华字典的目录 （2）运用在表中某个/些字段上，但存储时，独立于表之外

为什么用索引?

• 1）通过指针加速Oracle服务器的查询速度
• 2）通过rowid快速定位数据的方法，减少磁盘I/O

什么时候创建索引？

	1）表经常进行 SELECT 操作
	2）表很大(记录超多)，记录内容分布范围很广
	3）列名经常在 WHERE 子句或连接条件中出现

-- 1） 普通索引
-- 创建索引
create index index_owners_name on t_owners(name);

select * from t_owners where name = '刘华';

-- 2) 唯一索引
-- 主键就是唯一索引
-- 在业主表中水表编号创建唯一索引; 对应一个唯一约束
create unique index index_owners_watermete on t_owners(watermeter);

update t_owners set watermeter = '30406' where id=1001;
commit;

-- 3） 复合索引
create index idx_compo_owner on t_owners(address_id, housenumber);

-- 4) 反向键索引:   当字段值连续增长
create index idx_id on t_owners(id) reverse;

-- 5) 位图索引:  低基数  不能进行范围查询
create bitmap index idx_owner_type on t_owners(ownertype_id);