数据库的视图与索引
视图
视图:从一个或多个表中导出的虚拟的表,其内容由查询定义,具有表的结构,但不实现数据存储。
一视图应用
视图的创建 create view v_xx as select * from t_xx;
视图的修改 create or replace view v_xx as select * from t_xx;
视图的删除 drop view v_xx;
从视图中检索数据 select * from v_xx;
查看视图 show tables;
查看视图详情 show field v_xx;或 desc v_xx;
二视图与数据变更
2.1表格数据变更
表格数据变化后,在通过视图检索,得到的结果也同步发生了变化
2.2通过视图变更数据
2.2.1插入数据
INSERT INTO v_order(pid,pname,price) VALUES('p010','柴油','34');
发现插入了数据,视图不是表,不保存数据,知识虚拟表
2.2.2跨表插入数据
通过实践,我们可以看到,跨表插入数据系统反馈报错,提示不能修改超过一个表的数据。
因此,可以通过视图插入数据,但是只能基于一个基础表进行插入,不能跨表更新数据。
2.2.3WITH CHECK OPTION
如果在创建视图的时候制定了“WITH CHECK OPTION”,那么更新数据时不能插入或更新不符合视图限制条件的记录。
三 视图的作用与缺点
作用:
1 使用视图,可以定制用户数据,聚焦特定的数据。
在实际过程中,公司有不同角色的工作人员,我们以销售公司为例的话, 采购人员,可以需要一些与其有关的数据,而与他无关的数据,对他没 有任何意义,我们可以根据这一实际情况,专门为采购人员创建一个视 图,以后他在查询数据时,只需select * from view_caigou 就可以啦。
2 使用视图,可以简化数据操作。
在使用查询时,在很多时候我们要使用聚合函数,同时还要 显示其它字段的信息,可能还会需要关联到其它表,这时写的语句可能 会很长,如果这个动作频繁发生的话,我们可以创建视图,这以后,我 们只需要select * from view1就可以
3 使用视图,基表中的数据就有了一定的安全性
因为视图是虚拟的,物理上是不存在的,只是存储了数据的集合,我们可以 将基表中重要的字段信息,可以不通过视图给用户,视图是动态的数据的集 合,数据是随着基表的更新而更新。同时,用户对视图,不可以随意的更改 和删除,可以保证数据的安全性。
4 可以合并分离的数据,创建分区视图
随着社会的发展,公司的业务量的不断的扩大,一个大公司,下属都设有很 多的分公司,为了管理方便,我们需要统一表的结构,定期查看各公司业务 情况,而分别看各个公司的数据很不方便,没有很好的可比性,如果将这些 数据合并为一个表格里,就方便多啦,这时我们就可以使用union关键字, 将各分公司的数据合并为一个视图。
缺点:
1性能差
必须把视图查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,即使是视图的一个简单查询,sql server也要把它变成一个复杂的结合体,需要花费一定的时间。
2修改限制
当用户试图修改试图的某些信息时,数据库必须把它转化为对基本表的某些信息的修改,对于简单的试图来说,这是很方便的,但是,对于比较复杂的试图,可能是不可修改的。
索引
索引(index)是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
索引操作指令
创建索引
CREATE INDEX 索引名 ON 表名(列名);
查看索引
SHOW INDEX FROM 表名;
删除索引
DROP INDEX 索引名 ON 表名;
eg:为表employee的lname_pinyin创建一个索引:
>CREATE INDEX idx_lpy ON employee(lname_pinyin);
创建多列复合索引
eg:为表employee的lname_pinyin,fname_pinyin创建一个复合索引:
>CREATE INDEX idx_lpy ON employee(lname_pinyin,fname_pinyin);
创建唯一性索引 如果对index制定了UNIQUE关键字,则表示创建了不可重复的索引。
如为表employee的fname创建一个唯一性索引:
>CREATE UNIQUE INDEX idx_lpy ON employee(fname);
分析索引优势当创建了唯一性索引,插入和该列相同的记录,系统会报错。
如果已有数据重复,创建唯一性索引也会报错
但是,如果创建了一个复合的唯一性索引,则只要两列不完全相同,数据可以成功插入
通过EXPLAIN可以确认索引的使用情况;
>EXPLAIN SELECT ...;
eg:查看检索查询lname_pinyin的情况;
然后删除索引,再次查看检索查询name_pinyin 的情况:
>EXPALIN SELECT * FROM employee WHERE lname_pinyin='wang'\G
...
>DROP INDEX idx_pinyin ON employee;
...
>EXPALIN SELECT * FROM employee WHERE lname_pinyin='wang'\G
>...
我们可以看到,当建立索引时,遍历次数为2;而当删除索引后,遍历次数为9,和表中记录数相同。
因此,
索引可以大大提高检索的效率。