索引、视图、序列

1.索引

索引：类似于书的目录，提高查询效率

创建索引语法：

create index index_name on table_name(col_name[,col_name[,col_name,]]);

create index:index索引关键字

index_name:索引名称

table_name:表名

col_name:索引列名

create index ind_emp_name on emp(ename);

索引它并不是在所有情况下都会优化查询，它是在大量数据查询时，每次查询的数据量在表的总数据量30%以下时，会提高效率，

索引会提高查询效率，但是会降低添加和修改的执行效率

索引一般是给增删改操作比较少表上建

单列索引：给某个列建一个索引，叫单行索引

联合索引：给多个列建一个索引，叫联合索引，如果联合索引，在查询时，使用了第一个索引列作为条件，那么它会使用索引提高查询效率，

           如果使用了非第一个索引列作为条件，它不会使用索引

注意：使用索引列作为查询条件时，不要在索引列上进行数学运算

create index ind_emp_sal on emp(sal);

select * from emp where sal*12>10000;   --这样写条件不会使用索引

select * from emp where sal>10000/12;    --这样它就会使用索引

为什么用索引？

          1.加快检索空间：数据库允许系统直接定位到包含特定值的行，无需扫描整个表；

          2.提升查询的性能：有索引可以加快查询；

          3.支持唯一约束：唯一列上创唯一索引；

          4.优化连接操作：作为表之间连接查询时，索引可以加快连接速度，直接在连接字段上创建索引即可；

          5.加速分组和排序：索引可以在执行聚合函数和排序时候提供更快结果；

          6.获取内容的速度：避免临时表的创建和额外的排序操作；

索引的优缺点：

         优点：提高查询效率；

         缺点：

                1.占用存储空间：需要额外的空间来寸索引，一旦包含多个索引时，会占用很多空间；

                2.增加写操作成本：索引需要手动创建，不包含自动创建的哈。如主键 唯一

                3.更新频繁的表的性能下降：对于经常操作数据的表，索引更新会影响旗标的性能；

                4.索引选择不当：过多的索引或者不必要的索引会降低查询的性能，增加了数据库管理和维护成本；

                5.索引的维护：多个索引维护需要大量的成本；

创建索引的原则：

                1.最左前缀匹配原则，组合索引非常重要原则，MySQL会一直向右匹配；

                2.较频繁作为查询条件的字段才会去创建索引；

                3.更新频繁字段不适合创建索引；

                4.若是不能有效区分数据的列不适合创建索引；

                 5.尽量的扩展索引  不要新建；

                 6.定义有外键的数据列一定要建立索引；

                 7.对于查询中涉及较少的列，重复多的列 不要建立索引；

                 8.对于定义text、image、bit的列不要建立索引；

删除索引：

drop index 索引名;

drop index ind_emp_sal;

drop index ind_emp_name;

2.视图

视图以经过定制的方式显示来自一个或多个表的数据

视图可以视为“虚拟表”或“存储的查询”

创建视图所依据的表称为“基表”

视图的优点有：

提供了另外一种级别的表安全性

隐藏的数据的复杂性

简化的用户的SQL命令

隔离基表结构的改变

通过重命名列，从另一个角度提供数据

创建视图的语法：

 CREATE [OR REPLACE] [FORCE] VIEW

    view_name [(alias[, alias]...)]

  AS select_statement

  [WITH CHECK OPTION]

  [WITH READ ONLY];

create view:view视图关键字

OR REPLACE：如果视图已经存在，则替换旧视图。

FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。 基表==原始表

view_name:视图名称，符合标识符命名规范

别名：别名的数量要和后面的查询语句的列相同

WITH CHECK OPTION 子句可以保证让你只能在视图的条件之内对视图进行DML

WITH READ ONLY:表示是一个只读视图

create view view_dept_emp as select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

select * from emp;

注：

创建视图中可以使用单行函数、分组函数和表达式必须为使用函数或者表达式的字段指定名字

视图的使用：

select * from view_dept_emp;

select empno,ename,job,sal,deptno,dname from view_dept_emp;

视图上的DML语句有如下限制：

只能修改一个底层的基表

如果修改违反了基表的约束条件，则无法更新视图

如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图

如果视图包含伪列或表达式，则将无法更新视图

复杂视图中，可以更新键保留表的列，不可以更新非键保留表的列。

使用DROP VIEW语句删除视图

3.序列

序列：是oracle中自动生成一个序列的对象，一般用来生成表的主键的值

创建序列语法：

create sequence sequence_name

    start with startValue

    increment by inrValue

    minvalue minValue

    maxvalue maxValue

    cache|nocache

    cycle|no cycle

create sequence:sequence序列关键字

sequence_name:序列名称，符合标识符命名规范

start with satrtValue:指定序列的初始值,startValue是一个自然数

increment by inrValue:指定序列的值每次增加多少，inrValue 是一个自然数 一般用1

minvalue minValue:指定序列的最小值 minValue是一个自然数据

maxvalue maxValue:指定序列的最大值 maxValue是一个自然数据

cache|no cache:指定序列缓存，默认缓存20个

cycle|no cycle:指定序列是否循环生成

创建一个序列

create sequence my_seq start with 1 increment by 1;

序列的两个属性

nextval:取序列的值，并且序列的值增加

currval:取序列的当前值，序列值不会变

序列名.属性

select my_seq.nextval from dual;

select my_seq.currval from dual;  --不能直接取当前值

insert into 表名 values(my_seq.nextval,'值','值');

使用ALTER SEQUENCE语句修改序列，

不能更改序列的START WITH参数

 ALTER SEQUENCE stu_seq MAXVALUE 5000 CYCLE;

使用DROP SEQUENCE语句删除序列

DROP SEQUENCE stu_seq;