三篇基础sql之3

/*
视图

*/
--带子查询的create table
create table new_student as select * from student where id=1;
drop table new_student;
select * from new_student;
--根据子查询语句创建表并插入数据
--表结构由子查询的select语句决定，create table 指定的列的数量要跟select语句指定的列的数量一致
--create table 定义列只能定义列名，缺省值，完整性约束，非空约束不需要定义可以直接复制过来

--带子查询的insert语句
--根据子查询语句向表中插入数据
--insert指定的列的数量跟seelct语句指定的列的数量一致
--一次可以插入多条记录，不能用values子句

insert into new_student(id,name,age,c_class,c_school) select * from student where id in (2,25);

select * from new_student;

commit;


--视图在数据库中不存储数据值，即不占空间
--只在系统表中存储对视图的定义
--视图实际就是一条select语句
--类似windows的快捷方式

--视图的分类
--简单视图，基于单张表，不包含函数和表达式的视图，可以增删改查
--复杂视图，包含函数和表达式的视图,必须要符合条件才能增删改查，必须为函数或表达式定义别名
--连接视图，基于多表建立的视图，一般不会在该视图上做增，改，删的操作



--视图的ddl语句
--create or replace view view_name
--alter view
--drop view 
/*
create or replace view account_cost_v
as
select a.real_name,s.unix_host,c.id,c.descr
from account a join service s
on a.id = s.account_id
join cost c
on s.cost_id = c.id;
*/

create or replace view student_view1
as
select * from student;

select * from student_view1;
insert into student_view1(name,age,C_CLASS,C_SCHOOL) values('viewname','1','5','河南');
commit;

create or replace view student_view2
as
select s.*,c.class_name from student s join t_class c on s.c_class=c.id;


select * from student_view2;


/*
若将源表删除,基于源表的视图会发生怎样的变化?

视图是一个依赖表的数据库对象，查询视图最终都要通过查询源表实现。如果源表的结构发生变化，对视图的操作就有可能出问题。
查看视图的状态是帮助我们发现视图是否可用的方法。

*/

select view_name,text from user_views
where view_name = 'STUDENT_VIEW1';

--status为VALID有效，为INVALID无效
select object_name,object_type,status from user_objects
where object_name = 'STUDENT_VIEW1';


select * from user_errors;

--通过视图test_v1可以插入（2,3）,但从视图中不能查询到该记录，这样的情况不符合逻辑，怎样避免？

-- 视图中的with check option约束

/*

在创建视图时增加with check option约束，该约束要求通过视图插入的记录必须符合where条件。

create or replace view test_ck
as
select * from test
where c1 = 1 with check option;

insert into test_ck values (2,3);
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

*/

--视图中的with read only约束
/*

在创建视图时增加with read only约束，该约束要求对视图只能查询，不能做DML操作。

create or replace view test_ro
as
select * from test
where c1 = 1 with read only;

insert into test_ro values (1,5);
ERROR at line 1:
ORA-01733: virtual column not allowed here

*/


/*
索引
创建索引
create index index_name
on table_name (colname);

那些列需要建立索引:
经常出现在where子句的列
经常用于表连接的列
该列是高基数数据列
该列包含很多null值
表很大，查询的结果集小
主键列，唯一键列
外键列
经常需要排序和分组的列
索引不是万能的

*/


/*
索引的类型:

唯一性索引(类似唯一性约束)
                   oracle提供了一种索引形式是唯一性索引，语法是：
                   create unique index indname on tabname (colname);
                   alter table test drop primary key;
                   create unique index test_c1_uniidx on test(c1);
非唯一性索引

单列索引
联合索引
                   create index test_c2_c3_idx on test(c2,c3);
                   
                   
函数索引
                   若在c2列上创建普通索引，where round(c2) = 10是用不了该索引的，
                   oracle仍然会用全表扫描的方式查询数据，要想提高查询效率，必须使用函数索引。     
                   create index test_c2_funidx on test(round(c2));              

*/

/*
哪些写法导致索引用不了

函数
表达式
部分隐式数据类型
like和substr
查询所有的null值
否定形式

*/




/*
序列号

创建sequence
create sequence seq_name
[increment by 1 \ integer]
[start with integer]
[maxvalue integer\ nomaxvalue]
[minvalue integer \ nominvalue]
[cycle \ nocycle]
[cache 20 \ integer \ no cache]

缺省nocycle
执行完最大值后再执行报错
cycle 循环 

*/

drop sequence s_test_c1; 

create sequence s_test_c1 start with 1302001;