Oracle查询（下）

分区表

说明

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
优点

• 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
• 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
• 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
• 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

分类
1.范围分区（分区只能使用 日期类型 或者数字类型）
–优点：分区表的优点
–缺点：没有maxvalue时，找不到对应的分区会报错，有maxvalue时，随着时间的变长，该分区的数据会越来越多，失去了分区的意义。
语法

create table 分区表名(
列1 数据类型,
列2 数据类型,
...
)
partition by range(列）
（
--无下限
partition 分区名1 values less than(范围1)
partition 分区名2 values less than(范围2)
--maxvalue 无上限
partition 分区名3 values less than(maxvalue)
);

例

create table emp_date_partition2(
empno number(10),
ename varchar2(20),
hiredate date
)
partition by range(empno)
INTERVAL (1000)
(
--无下限 p1区的数据 小于 1001
partition p1 values less than (1001) 
);   
insert into emp_date_partition2 values(1001,'张三',date'2024-10-10');

select * from user_tab_partitions where table_name = upper('emp_date_partition2');

2.间隔分区
–间隔分区 (分区只能使用== 日期类型 或者 数字类型==）
–是范围分区的升级版本，或者特殊范围分区
–优点，可以自动扩展新的分区
语法

create table 表名(
字段 字段类型,
...
)
partition by range(按照哪个字段分)
INTERVAL(指定范围）
(
--无下限
partition 分区名 values less than(范围)
...
);

按月间隔
NUMTOYMINTERVAL： 该函数可以指定 年 月
NUMTODSINTERVAL： 该函数可以指定 天 时 分 秒

--创建表 使用日期字段分区
create table emp_date_partition2(
empno number(10),
ename varchar2(20),
hiredate date
)
partition by range(hiredate)
--每一个月分一个区
interval (NUMTOYMINTERVAL(1,'month'))
(--无下限 p1区的数据 小于 '2024-4-1'
partition  p1 values less than(date '2024-4-1')
);
insert into emp_date_partition2 values(1,'smith',date'2023-10-10'); 
insert into emp_date_partition2 values(2,'word',date'2024-6-10'); 
insert into emp_date_partition2 values(3,'allen',date'2024-5-10'); 
insert into emp_date_partition2 values(4,'allen',date'2024-4-1');
insert into emp_date_partition2 values(2,'word',date'2024-6-1'); 
insert into emp_date_partition2 values(3,'allen',date'2024-5-31'); 
insert into emp_date_partition2 values(4,'allen',date'2024-4-10');
insert into emp_date_partition2 values(4,'allen',date'2024-8-10');

select * from user_tab_partitions where table_name = upper('emp_date_partition2');
select * from  emp_date_partition2 partition(sys_p41);
select * from  emp_date_partition2 partition(sys_p42); 
select * from  emp_date_partition2 partition(sys_p43);
select * from  emp_date_partition2 partition(sys_p44);

3列表分区
–使用表中的某个字段的数据进行分区，数据一样的放入同一个分区。
–可以使用任意类型的字段，但尽量选择数据相对平均的字段。

例
--创建员工表 使用部门号列表分区
create table emp_list(
empno number(10),
ename varchar2(20),
deptno number
) partition by list(deptno)(
partition p10 values(10),
partition p20 values(20),
partition p30 values(30)
);
--插入数据
insert into emp_list values(1,'a',10);
insert into emp_list values(2,'a',10);
insert into emp_list values(3,'a',20);
insert into emp_list values(4,'a',20);
insert into emp_list values(5,'a',30);
insert into emp_list values(6,'a',30);

–查看表分区信息

select * from user_tab_partitions where table_name = upper(‘emp_list’);

–hash分区（散列分区）
–可以选择任意类型的字段 但是尽量选择具有唯一性的字段（员工号 手机号 身份证号 主键字段）
–指定分区的数量
–分区的规则：

• hash值：数据相同 hash值一定相同 数据不同 hash值不同
• 根据分区字段的数据得到一个hash值（数字） 然后根据这个hash值对分区数量取余数，余数相同的进入同一个分区。
• 数据相同 hash值也相同 那么余数一定相同 最后分区也相同；数据不同 hash值不同 余数可能相同 可能进入同一个分区。

–注：

• 查询用hash分区创建的分区表时，只有根据分区字段查询，才能达到分区表提高查询效率的目的，
  –因为hash分区在查询时，如果根据分区字段查询，会自动计算对应的hash值，从而知道该字段在哪个区存储，
  –没有分区字段，就得不到分区信息。

```sql
--创建员工表 使用姓名进行hash分区
create table emp_hash (
empno number(10),
ename varchar2(20),
deptno number
)
--按姓名字段 hash分区 分4个区
partition by hash(ename) partitions 4;
--插入数据
insert into emp_hash values(1,'tom','');
insert into emp_hash values(2,'tom3','');
insert into emp_hash values(3,'tom4','');


--查看表分区信息
select * from user_tab_partitions where table_name = 'EMP_HASH';

select * from emp_hash partition(SYS_P24);
select * from emp_hash partition(SYS_P22);

递归查询 （一直查下去，直到查不到数据）

例：

 --查询SMITH的所有领导
--多个select语句
select mgr from emp where ename='SMITH'; --7782
select mgr from emp where empno = 7782; --7839
select mgr from emp where empno = 7839; --null
--子查询嵌套
 select mgr from emp where empno=
 (select mgr from emp where empno=
 (select mgr from emp where ename='SMITH'));
 --上述sql都没法将SMITH的所有领导都展示

–递归查询
关键字： connect by prior

select * from emp start with ename = 'SMITH' --start with 从smith开始，先查出smith所有信息
connect by empno =prior mgr; --将smith的领导编号 转为 员工号继续查询

–查出7839号员工的所有下属
select * from emp start with empno =7839 connect by mgr = prior empno;

–查出ford的所有领导
select * from emp start with ename = ‘FORD’ connect by empno = prior mgr;

–查出7839员工的所有下属 使用递归查询（要求只查出直接下属 和下属的下属）
–level 等级或者深度的意思，可以限定递归查询的查询深度
select emp.*,level from emp where level<4 start with empno = 7839 --level = 1
connect by mgr = prior empno;

分页+去重

–rowid 使用场景 删除重复数据
–删除重复数据 -修改了表中的数据 是DML语句
–去重 对查询结果进行修改，表中数据还是重复 是DQL语句

create table t1(
name varchar2(10),
age number(3)
);
insert into t1 values('张三',18);
insert into t1 values('张三',18);
insert into t1 values('张三',18);
insert into t1 values('李四',28);
insert into t1 values('李四',28);
insert into t1 values('王五',38);
insert into t1 values('李四',17);
commit;

select  *  from t1;
select t1.*,rowid from t1;
--查出每组重复的数据 最小的rowid
select name,age,min(rowid) from t1 group by name,age;

--保留每组最小的rowid 其他的都删除
delete from t1 where rowid not in (select min(rowid) from t1 group by name,age);
select * from t1;

--去重 （查询出来没有重复，但是表中的数据还是重复）
select name,age from t1 group by name,age;
select distinct name,age from t1;

--rownum 使用场景 分页查询
--不用排序函数 查询工资前5的员工
select * from 
(select * from emp order by sal desc) where rownum<6;

select * from 
(select t.*,rownum r from 
(select emp.* from emp order by sal desc) t) where r >=5 and r <=10;

select * from 
(select t.*,rownum r  from 
(select * from emp order by sal desc)t)
   where r >=5 and r <=10;
   

插入更新

–两张表的数据 进行对比 （a,b）
–如果a表的id在b表有 那么就更新a表的数据，如果没有 就插入数据

--1.正常模式
create table stu_a(
cid number,
cname varchar2(10)
);
create table stu_b(
cid number,
cname varchar2(10)
);
insert into stu_a values(1, 'a1111');
insert into stu_a values(2, 'a2222');
insert into stu_a values(3, 'a2225');
insert into stu_b values(4, 'b1111');
insert into stu_b values(5, 'b1112');
insert into stu_b values(6, 'b1113');

select * from stu_a;
select * from stu_b;   

语法

merge into 要更新或者插入的表 using 要对比的表 on（对比条件）
when matched then 
update xxx
when not matched then
insert xxxx
;
--参照b表，对a表的数据插入更新
merge into stu_a a using stu_b b on(a.cid = b.cid)
when matched then 
update set cname =b.cname
when not matched then 
insert (cid,cname) values(b.cid,b.cname);

select * from stu_a;

--全插入
merge into stu_a  a using stu_b b  on  (1=2) --随便写个永远为false 的条件
when not matched then
insert  (cid,cname)values(b.cid,b.cname);

create table stu_c as
(select * from stu_a
union all
select * from stu_b); -- 并集很像插入，但它只在查询层面，表的数据没有改变。

数据库设计

五大约束

作用：就是限制条件对表中的数据进行限定，保证数据的正确性，有效性，完整性。
–主键约束（primary key）: 保证该字段具有非空且唯一性，一张表中只能有一个主键，主键是表中
–字段的唯一标识，一个表只能有一个主键，但是主键字段可以有多个。

–非空约束（not null）：约束的字段不能是空值

–唯一约束（unique）：唯一约束保证表中的一列或多列的值是唯一的，允许为空值，空值可以有多个。

–外键约束（foreign key）：引用其他表的主键，为不同的表建立联系，（数据可以为null 但不能是
–指定引用的表的主键以外的值）

–检查约束（check） 某列取值范围限制、格式限制等等，必须满足条件 check(条件)

–默认约束（default+默认值）：没插入数据时会有默认值

–这些约束都是针对表中字段的，可以在创建表时设置，也可以创建表后添加

--创建表时添加约束
create table dept2(
deptno number(2) primary key,
dname varchar2(14) unique,
loc varchar2(13)
);
insert into dept2 values(1,'a','a2');
insert into dept2(dname,loc) values('b','b2');

create table dept3(
deptno number(2),
dname varchar2(14) unique,
loc varchar(13)
);
insert into dept3 values(10,'dev','深圳');
insert into dept3(dname,loc) values('d','s');
delete from dept3 where dname = 'd';
--创建表后添加主键约束
alter table 表名 add constraint 约束名 primary key (字段);
alter table dept3 add constraint cs1 primary key (deptno);
commit;


--外键约束语法 foreign key（外键字段）  references 其他表（主键字段）
create table emp0002(
empno number(2) primary key,
ename varchar2(10) not null,
deptno number(2),
foreign key(deptno) references dept3(deptno)
);

insert into emp0002 values(2,'zs',20);

create table dept6(
deptno number(2) unique,
dname varchar2(14),
loc varchar2(13)
);
insert into dept6(dname) values('test');
select * from dept6;
insert into dept6(dname) values('ddd');
select * from dept6;

三大范式

–第一范式：原子性 列不可再分
–第二范式：在第一范式的基础上，每个非主键必须完全依赖于主键，而不能依赖于其它字段
–第三范式：在第二范式的基础上，每个非主键必须直接依赖主键，不能产生传递依赖。

–字段的设计，一定要明确，不可再分 --第二范式 主要针对联合主键 其它字段必须依赖于联合主键这个整体

用户权限

• –用户
  system 123456
  scott 123456
• –查看所有用户
  select * from dba_users;
• –创建用户并设置密码
  create user data102 identified by 123456;
• –解锁用户
  alter user scott account unlock;
• –修改密码
  alter user scott identified by 111111;
• –删除用户
  drop user data102;

权限

• –授予，创建资源，连接权限
  grant resource,connect to data102;
• –收回 创建资源 连接权限
  revoke resource,connect from data102;

--给data102用户 授予scott下emp表的查询权限
grant select on scott.emp to data102;
revoke select on scott.emp from data102;

事务的四大特性

–数据库事务的四大特性
–持久性
–原子性
–一致性
–隔离性

• 原子性: 事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。一个事务内的操作要么全部成功要么全部失败.
• 一致性: 事务执行后，数据库状态与其它业务规则保持一致。其他特性都是为了给一致性服务的. 例如买东西,张三买李四的东西, 买卖前和买卖后张三和李四的所有钱数之和是保持不变的.
• 隔离性: 事务和事务之间是隔离开的. 一个事务看不到另一个事务正在操作的数据(正在进行中的状态)(两个人在两个房间考试)
• 持久性: 一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制将数据恢复到提交后的状态。 举例: 一般的数据操作只是在事务中记录需要进行这样的操作, 即使看到了表中的数据发生了改变, 实际上表中的数据也没有发生改变只是在事务中记录需要进行这样的操作, 真正提交了事务才去表中改变表中的数据.

序列

–序列：可以产生一组连续的数据，作用是为主键服务

--创建序列
create sequence 序列名
increment by x --递增数
start with y --开始数
maxvalue z --最大值
nocycle --不循环 cycle 循环
cache 10; --缓存10个，nocache 不缓存
select * from dept3;

--创建序列
create sequence seq_userid2
increment by 1
start with 11
maxvalue 9999
nocycle
cache 10;

--生成序列值 
select seq_userid2.nextval from dual;
--查看当前序列值
select seq_userid2.currval from dual;

select * from dept3;
insert into dept3 values(seq_userid2.nextval,'a','b');

索引
–索引，类似于书籍中的目录，作用是为了提高查询效率
–索引也是数据库中的对象，也会占用磁盘空间
–缺点：插入删除修改数据，需要维护索引
–什么字段要加索引
–1 经常加在where 或者 group by 或者 order by 后面的字段
–2 经常用来关联的字段
–什么字段不建议加索引
–1 字段的值重复性较高
–2 经常修改的字段
–3 表的数据量不大

– B树索引
–主键索引（特殊的唯一索引）
–唯一索引（字段加了唯一约束就自带唯一索引）
create unique index ename_index on emp(ename);
–普通索引
create index 索引名 on 表名（字段）;
create index job_index on emp(job);
–函数索引（查询时经常会用到函数）
create index ename_index2 on emp(upper(ename));
–复合索引（经常多个字段作为条件查询）（多字段创建的索引）
create index job_ename_index on emp(job,ename);
–反向索引，根据字段数据的末尾创建的索引（字段数据前面的重复性较高）
create index phone_index on emp(phone) reverse;
–位图索引（字段重复率较高)
create bitmap index emp_job_bitmap_index on emp(job);

执行计划
explain plan for select * from emp;
select * from table(DBMS_XPLAN.DISPLAY); – 用于显示执行计划（explain plan）
–执行计划
–id 执行编号
–operation 执行名称
–name 涉及的表
–rows 涉及的行数
–bytes 数据的大小
–cost cpu使用率
–time 时间
– TABLE ACCESS FULL 全表扫描
– INDEX FULL SCAN 索引全扫描
– INDEX UNIQUE SCAN 索引唯一扫描
– INDEX RANGE SCAN 索引扫描
– TABLE ACCESS　BY INDEX ROWID 回表（根据rowid再去表中找数据）
explain plan for select * from emp where empno=1;
select * from table(DBMS_XPLAN.DISPLAY);

explain plan for select ename ,sal from emp ;
select * from table(DBMS_XPLAN.DISPLAY);

select * from emp02 ;
update emp02 set ename =‘AAAA’ where sal<3000;
create index ename_index3 on emp02(ename);

explain plan for select * from emp02 WHERE ENAME =‘AAAA’;
select * from table(DBMS_XPLAN.DISPLAY);

explain plan for select ename from emp;
select * from table(DBMS_XPLAN.DISPLAY);

索引失效场景
–索引字段进行了计算
explain plan for select * form emp where empno-1 = 1;
–比较时与索引字段类型不一致
explain plan for select * from emp where ename = 2;
–索引字段使用了函数
explain plan for select * from emp where upper(ename) = ‘smith’;
–使用not in（排除）
explain plan for select * from emp where empno not in (1,2,3);
–使用不等于
explain plan for select * from emp where empno !=1;
–模糊查询时 %开头
explain plan for select * from emp where ename like ‘%abc’;
–比较null值会失效
explain plan for select * from emp where ename is not null;

1. 请查出各科得分情况，输出字段：课程名称，课程总分，课程最低分，课程最高分
   with
   first
   as(select c_id, sum(s_score) 课程总分, min(s_score)课程最低分, max(s_score) 课程最高分 from score group by c_id)
   select course 课程名称,课程总分, 课程最低分, 课程最高分 from course c,first where c.c_id = first.c_id;
2. 请查出至少有两门课程超过 75 分的学生姓名
   with
   first
   as(select s_id from score where s_score>75 group by s_id having count(1) >1)
   select s_name 学生姓名 from first,student where first.s_id = student.s_id;
3. 请查出各科的最高成绩，输出：课程名称、学生 ID、分数
   with
   first
   as(select c_id,s_id 学生ID,max(s_score)over(partition by c_id) 分数 from score)
   select course 课程名称,学生ID,分数 from course,first where course.c_id = first.c_id order by 课程名称,学生ID;
4. 请查出总分超过 200 分的学生分数并按平均分倒序，输出字段：学生id、总分数、平均分数
   select s_id 学生id,sum(s_score) 总分数,round(avg(s_score)) 平均分数
   from score group by s_id having sum(s_score)>200 order by avg(s_score) desc;
5. 请查出学生成绩排行榜，如没考得 0 分，输出字段：学生 id，语文、数学、英语，总分
   with
   first
   as(select s_id,
   sum(case when course = ‘语文’ then s_score else 0 end)语文,
   sum(case when course = ‘数学’ then s_score else 0 end)数学,
   sum(case when course = ‘英语’ then s_score else 0 end)英语,
   sum(s_score) 总分 from score right join course on score.c_id = course.c_id
   group by s_id)
   select student.s_id 学生id,语文,数学,英语,总分
   from student left join first on student.s_id = first.s_id order by 总分 desc;

select * from users;
select * from orders;
7. 请查出上图中连续 2 天都有下单的用户名单
select s.“uid”,uname from
(select f.*,create_time-lag(create_time,1,create_time) over(partition by “uid” order by “uid”)连续登录
from
(select “uid”,create_time from orders group by “uid”,create_time) f) s,users
where 连续登录 = 1 and s.“uid” = users.“uid”;
8. 请查出每天销售额情况，按日期升序、输出：
日期、当月累计销售额（当月1 日累计截止到当前日，如上图中 1 日=450,2 日=1 日+80,3 日=2 日+186）
select create_time 日期,当日销售额,
sum(当日销售额) over(order by create_time) 当月累计销售额
from (select create_time,sum(order_amount) 当日销售额 from orders group by create_time ) order by create_time;

select create_time,当天金额,
sum(当天金额) over(partition by to_char(create_time,‘yyyymm’) order by create_time) 累计金额 from
(select create_time,sum(order_amount) 当天金额 from orders group by create_time);
9. 请查出 1 月份销售额情况，按日期升序，输出：日期，当日销售额，当日下单人数，当月累计销售额，当月累计下单人数。
with
first
as(select create_time 日期,
sum(order_amount) 当日销售额,
count(distinct “uid”) 当日下单人数
from orders group by create_time)
select 日期,当日销售额,当日下单人数,
sum(当日销售额) over(partition by to_char(日期,‘yyyymm’) order by 日期) 当月累计销售额,
sum(当日下单人数) over(partition by to_char(日期,‘yyyymm’) order by 日期) 当月累计下单人数
from first order by 日期;