建设隔离变压器移动网站,西安十大网站制作公司,wordpress菜单横排,豫建市2021 42号分区表
说明
当表中的数据量不断增大#xff0c;查询数据的速度就会变慢#xff0c;应用程序的性能就会下降#xff0c;这时就应该考虑对表进行分区。表进行分区后#xff0c;逻辑上表仍然是一张完整的表#xff0c;只是将表中的数据在物理上存放到多个表空间(物理文件上…分区表
说明
当表中的数据量不断增大查询数据的速度就会变慢应用程序的性能就会下降这时就应该考虑对表进行分区。表进行分区后逻辑上表仍然是一张完整的表只是将表中的数据在物理上存放到多个表空间(物理文件上)这样查询数据时不至于每次都扫描整张表。 优点
改善查询性能对分区对象的查询可以仅搜索自己关心的分区提高检索速度。增强可用性如果表的某个分区出现故障表在其他分区的数据仍然可用维护方便如果表的某个分区出现故障需要修复数据只修复该分区即可均衡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,张三,date2024-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,date2023-10-10);
insert into emp_date_partition2 values(2,word,date2024-6-10);
insert into emp_date_partition2 values(3,allen,date2024-5-10);
insert into emp_date_partition2 values(4,allen,date2024-4-1);
insert into emp_date_partition2 values(2,word,date2024-6-1);
insert into emp_date_partition2 values(3,allen,date2024-5-31);
insert into emp_date_partition2 values(4,allen,date2024-4-10);
insert into emp_date_partition2 values(4,allen,date2024-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 enameSMITH; --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 enameSMITH));--上述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 level4 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 rownum6;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 (12) --随便写个永远为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);索引
索引类似于书籍中的目录作用是为了提高查询效率索引也是数据库中的对象也会占用磁盘空间缺点插入删除修改数据需要维护索引
什么字段要加索引
经常加在where 或者 group by 或者 order by 后面的字段经常用来关联的字段
什么字段不建议加索引
字段的值重复性较高经常修改的字段表的数据量不大
B树索引 create index 索引名 on 表名字段;
主键索引特殊的唯一索引唯一索引字段加了唯一约束就自带唯一索引 create unique index ename_index on emp(ename);普通索引 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);执行计划
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 empno1;
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 sal3000;
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;
练习
请查出各科得分情况输出字段课程名称课程总分课程最低分课程最高分 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;请查出至少有两门课程超过 75 分的学生姓名 with first as(select s_id from score where s_score75 group by s_id having count(1) 1) select s_name 学生姓名 from first,student where first.s_id student.s_id;请查出各科的最高成绩输出课程名称、学生 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;请查出总分超过 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;请查出学生成绩排行榜如没考得 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 日期;
