java学习笔记整理-高级查询\视图\索引\序列\约束

一.高级查询
1.子查询
select 查询中,在where查询条件中的限制条件不是一个确定的值,而是来自与另外一个查询的结果
子查询:嵌入在其他SQL语句中的select语句,大部分时候出现在where子句中
子查询嵌入的语句称作主查询或父查询.主查询可以是select语句,也可以是其他类型的语句比如DML或DDL语句
子查询在from部分
如果要在子查询的结果中继续查询,则子查询出现在from子句中,这个子查询也称作行内视图或者匿名视图
2.分页查询
rownum
1,rownum被称作伪列,用于返回标识行数据顺序的数字
2.也就是将rownum先作为行内视图的一个列,在主查询中就可以使用这个列值作为条件
3.decode函数
decode函数的语法:decode(expr,search1,result1[,search2,result2…][,default])
它用于比较参数expr的值,如果匹配到哪一个search条件,就返回对应的result结果
可以有多组search和result的对应关系,如果任何一个search条件都没有匹配到,则返回最后default的值
decode函数在分组查询中的应用
1.decode函数可以按字段内容分组
2.decode函数可以按字段内容排序
4.排序函数
row_number
row_number语法:row_number() over(partition by col1 order yb col2)
表示根据col1分组,在分组内部根据col2排序,此函数计算的值就表示每组内部排序后的顺序编号,组内连续且唯一
6.集合操作
union\union all
union操作符会自动去掉合并后的重复记录
union all返回两个结果集中的所有行,包括重复的行
union操作符对查询结果排序,union all不排序
intersect
获得两个结果集的交集,只有同时存在于两个结果集中的数据,才会被显示输出
使用intersect操作符后的结果集会以第一列的数据作为升序排列
minus
只有在第一个结果集中存在,在第二个结果集中不存在的数据,才能够被显示出来

二.视图\序列\索引
1.视图VIEW
视图也被称做虚表,是一组数据的逻辑表示,其本质是对应于一条select语句,结果集被赋予一个名字,即视图名字.视图本身并不包含任何数据,它只包含映射到基表的一个查询语句,当基表数据发生变化,视图数据也随之改变
create view view_name as subquery;
subquery是select查询语句,对应的表称作基表
视图的作用
1.如果经常执行某项复杂查询,可以基于这个复杂查询建立视图,此后查询此视图即可,简化复杂查询;
2.此视图本质上就是一条select语句,所以当访问视图时,只能访问到所对应的select语句中涉及到的列,对基表中的其他列起到安全和保密的作用,可以限制数据访问.
授权创建视图
创建视图的DDL语句时create view,用户必须有create view系统权限才能创建视图.如果没有权限,创建视图时会提示:权限不足
管理员可以通过DCL语句授予用户创建视图的权限:grant create view to student
2.序列
什么是序列:sequence是一种用来生成唯一数字值的数据库对象.序列的值由oracle程序按递增或递减顺序自动生成,通常用来自动产生表的主键值,是一种高效率获得唯一键值的途径
序列是独立的数据库对象,和表是独立的对象,但序列并不依附于表
通常情况下,一个序列为一个表提供主键值,但一个序列也可以为多个表提供主键值
创建序列的语法:create sequence sequence_name
[start with i] [ increment by j]
[maxValue m |nomaxValue]
[minValue n|nominvalue]
[cycle|nocycle] [cache p | nocache]
sequence_name是序列名,将创建在schema方案下
序列的第一个序列值是i,步进是j,如果j是负数,表示递减
序列可生成的最大值是m,最小值是n
如果没有设置任何可选参数,序列的第一个值是1,步进是1
cycle表示在递增至最大值或递减至最小值之后是否继续生成序列号,默认是nocycle
cache用来指定先预取p个数据在缓存中,以提高序列值的生成效率,默认是20
序列有两个伪列:
nextval:获取序列的下个值
currval:获取序列的当前值
当序列创建以后,必须先执行一次nextval,之后才能使用currval
删除序列
删除序列的语法如下:drop sequence sequencr_name
索引
索引的原理：索引是一种允许直接访问数据表中某一行数据行的树形结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间中、索引记录中存有索引关键字和指向数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多
索引一旦被建立就将被oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制
rowid：伪列，唯一标识一条数据记录，可理解为行地址
创建索引的语法：create index index_name on table (column);
index_name索引的名字
table 表名
column 列名，可以建立单列索引或者复合索引
unique表示唯一索引
基于函数的索引：
如果需要在emp表的ename列执行大小写无关搜索，可以在此列上建立一个基于upper函数的索引
修改和删除索引
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率
重建索引语法如下：alter index index_name rebuild
删除索引 drop index index_name
创建和使用索引的原则：
1.为经常出现在where子句中的列创建索引
2.为经常出现在order by 、distinct后面的字段建立索引。如果建立的是复合索引、索引的字段顺序要和这些关键字后面的字段顺序一致
3.为经常作为表的连接条件的列上创建索引
4.不要再经常做dml操作的表上建立索引
5.不要在小表上建立索引
6.限制表上的索引数目，索引并不是越多越好
7.删除很少被使用的、不合理的索引
三.约束
约束（constraint）的全称是约束条件，也称作完整约束条件
约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行
约束条件可以保证表中数据的完整性，保证数据间的商业逻辑
约束的类型1.非空约束 not null 简称 nn
2.唯一性约束（unique），简称uk
3.主键约束（primary key），简称pk
4.外键约束（foreign key），简称fk
5.检查（check），简称ck
主键约束：
主键的意义
1.主键约束条件从功能上看相当于非空（not null）且唯一（unique）的组合
2.主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。
3.主键可以用来在表中唯一的确定一行数据。一个表上只允许建立一个主键，
而其他约束条件则没有明确的个数限制
主键选区的原则
1.主键应是对系统无意义的数据
2.永远不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
3.主键不应包含动态变化的数据，如时间戳
4.主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
5.主键尽量建立在单列上
外键约束：
外键约束的意义
1.外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
2.比如emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或字表
alter table emp add constraint emp_deptno_fk foreign key (deptno) references dept (deptno)
外键约束对一致性的维护
外键约束约束条件包括两个方面的数据约束：
从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为null
当主表参照列的值被从表参照时，主表的该行记录不允许被删除
外键：约束对性能的降低
1.如果在一个频繁DML操作的表上建立外键，每次此DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开心，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去
2.另外外键确定了主从表的先后生成关系，有时会影响业务逻辑
检查约束：
检查约束条件用来强制在字段上的每个值都要满足check中定义的条件
当定义了check约束的列新增或修改数据时，数据必须符合check约束中定义的条件
add constraint employees_salary_check check(salary>2000);