当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
优点
分类
1.范围分区（分区只能使用 日期类型 或者数字类型）
–优点：分区表的优点
–缺点：没有maxvalue时，找不到对应的分区会报错，有maxvalue时，随着时间的变长，该分区的数据会越来越多，失去了分区的意义。
语法
createtable 分区表名(
列1 数据类型,
列2 数据类型,...)partitionby range(列）
（
--无下限partition 分区名1values less than(范围1)partition 分区名2values less than(范围2)--maxvalue 无上限partition 分区名3values 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.间隔分区
–间隔分区 (分区只能使用== 日期类型 或者 数字类型==）
–是范围分区的升级版本，或者特殊范围分区
–优点，可以自动扩展新的分区
语法
createtable 表名(
字段 字段类型,...)partitionby 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列表分区
–使用表中的某个字段的数据进行分区，数据一样的放入同一个分区。
–可以使用任意类型的字段，但尽量选择数据相对平均的字段。
例
--创建员工表 使用部门号列表分区createtable emp_list(
empno number(10),
ename varchar2(20),
deptno number
)partitionby list(deptno)(partition p10 values(10),partition p20 values(20),partition p30 values(30));--插入数据insertinto emp_list values(1,'a',10);insertinto emp_list values(2,'a',10);insertinto emp_list values(3,'a',20);insertinto emp_list values(4,'a',20);insertinto emp_list values(5,'a',30);insertinto emp_list values(6,'a',30);
–查看表分区信息
select * from user_tab_partitions where table_name = upper(‘emp_list’);
–hash分区（散列分区）
–可以选择任意类型的字段 但是尽量选择具有唯一性的字段（员工号 手机号 身份证号 主键字段）
–指定分区的数量
–分区的规则：
–注：
```sql--创建员工表 使用姓名进行hash分区createtable emp_hash (
empno number(10),
ename varchar2(20),
deptno number
)--按姓名字段 hash分区 分4个区partitionbyhash(ename) partitions 4;--插入数据insertinto emp_hash values(1,'tom','');insertinto emp_hash values(2,'tom3','');insertinto 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';--7782select mgr from emp where empno =7782;--7839select 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;
–第一范式：原子性 列不可再分
–第二范式：在第一范式的基础上，每个非主键必须完全依赖于主键，而不能依赖于其它字段
–第三范式：在第二范式的基础上，每个非主键必须直接依赖主键，不能产生传递依赖。
–字段的设计，一定要明确，不可再分 --第二范式 主要针对联合主键 其它字段必须依赖于联合主键这个整体
--给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');
== create index 索引名 on 表名（字段）;==
createuniqueindex ename_index on emp(ename);
createindex job_index on emp(job);
createindex ename_index2 on emp(upper(ename));
createindex job_ename_index on emp(job,ename);
createindex phone_index on emp(phone) reverse;
create bitmap index emp_job_bitmap_index on emp(job);
select * from table(DBMS_XPLAN.DISPLAY); -- 用于显示执行计划（explain plan）
参数
explain plan for select * from emp;
–执行计划
–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);
练习
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 日期;
提示：请勿发布广告垃圾评论，否则封号处理！！