Oracle day05

in
not in

*：因为有时候in的效率低，所以用exists代替
exists：存在
not exists：不存在
select id,name from school where exists(select sid from student where student.sid = school.id);

some/any：用法和in相同，in用在无符号的情况下，some/any用在有符号的情况下

select id,name from school where id = some(select sid from student);
select id,name from school where id > any(select sid from student);

all：比所有值都大，或者比所有值都小
select id,name from school where id > all(select sid from student);

***：
>any  >min
<any  <max
>all    >max
<all    <min

练习：
--36查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资
--36查询工资高于30号部门中工作的某个员工的工资的员工姓名和工资

联合关键字：
union：结果唯一，并且按照默认顺序排序
union all：结果不唯一
select id,name from school where name like '山东%'
union
select id,name from school where name like '%大学';

select id,name from school where name like '山东%'
union all
select id,name from school where name like '%大学';

intersect：求交集
select id,name from school where name like '山东%'
intersect
select id,name from school where name like '%大学';

minus：从第一个结果集中减去第二个结果集中重复的数据
select id,name from school where name like '山东%'
minus
select id,name from school where name like '%大学';

视图：view
*：经过函数修饰的列一定要起别名

创建视图：
create view 视图名 as 查询语句;
create view v_name as
select school.name schname,student.name stuname from SCHOOL
left join student on student.sid = school.id;

select schname,stuname from v_name;

删除视图：
drop view v_name;

视图可以减少编译时间，提高查询效率，避免没有权限的用户查看隐私字段
简单视图数据可以动态来源于原表，复杂视图需要手动编译(alter view 视图名 compile;)

练习：
面试题第四题

序列：sequence
*：Oracle通过调用序列实现主键自增，一个有序的整数列值
*：在一个新的会话中需要调用序列的下一个值才能查看序列的当前值
*：调用序列的下一个值会作为下一次调用的初始值

创建序列：
create sequence seq_test  --序列名
increment by 1                 -- 一次增长1
start with 1                       -- 从1开始
minvalue 1                       -- 最小值
maxvalue 100                   -- 最大值
nocycle                             -- 不循环
nocache;                           -- 不缓存                  

1.消除延迟段创建特性
alter system set deferred_segment_creation = false;
2.创建表
create table test2(id number(5),name varchar2(20));
3.创建序列
create sequence seq_test2;
4.新增数据
insert into test2 values(seq_test2.nextval,'橘子');
insert into test2 values(seq_test2.nextval,'火龙果');
5.查询数据
select * from test2;

查看序列的当前值：
select seq_test.currval from dual;
查看序列的下一个值：
select seq_test.nextval from dual;
查看当前用户有哪些序列：
select * from user_sequences;
删除序列：
drop sequence seq_test;

索引：index
*：数据库会在具有唯一约束的列上添加唯一索引

创建索引：
create index 索引名 on 表名(列名);
create index ind_name on student(name);

索引类型：
普通索引：normal
create index 索引名 on 表名(列名);
create index ind_name on student(name);

唯一索引：unique
create unique index 索引名 on 表名(列名);
create unique index ind_sid on student(sid);

位图索引(分类索引)：bitmap
create bitmap index 索引名 on 表名(列名);
create bitmap index ind_cid on student(cid);

函数索引：
create index 索引名 on 表名(函数(列名));
create index ind_birthday on student(to_char(birthday,'yyyy'));

数据量大查询多的列适合添加索引,增改的列会让数据耗费资源维护索引

10.什么情况会使索引失效？
10.1使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like ‘%文’–索引不起作用）
10.2使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效
10.3使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效。
10.4尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
10.5对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。
10.6应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
10.7尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。
10.8不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.9并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引。
10.10索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，
若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

表空间：tablespace
在一段内存中多数存储的是表，所以称为表空间

创建表空间：
create tablespace table_user   -- 表空间的名字
datafile 'D:/table_user.dbf'      -- 表空间的位置
size 5M                                    -- 初始大小
autoextend on next 5M          -- 下一次自动拓展多少
maxsize 100M;                        -- 最大值

创建用户并给分配默认的表空间：
create user ET identified by etoak default tablespace table_user;
创建用户未指定表空间：
create user ET2 identified by etoak;
给用户修改表空间：
alter user ET2 default tablespace table_user;
删除表空间：
如果删除用户指定的表空间，该用户仍指向原表空间位置，需要手动指定一个有效的表空间位置
drop tablespace table_user including contents and datafiles;

sql语句优化：
1.建议少用‘*’代替列名
2.用exists代替in
3.多表连接时，尽量减少表的查询次数
4.删除全表数据的时候用truncate代替delete
5.合理使用索引（详见索引）
6.sql语句尽量大写，Oracle会默认把小写转换成大写在执行
7.在保证语句完整的情况下，多使用commit(begin...end)
8.优化group by,将不需要的数据尽量在分组之前过滤掉
9.连表查询的时候尽量使用表的别名，减少解析时间
10.表连接在where之前，where条件过滤顺序，能够更多的过滤数据的放在前面