MySQL索引和视图

一、索引
索引的作用：

索引是对数据表中的一个或多个字段的值进行排序而创建的一种分散存储结构。创建索引的作用在于提高查询速度

索引中提供了顺序，在已经排好序的数据的数据集合中查找数据，可以使用二分法，查询的平均时间为log2n。而如果在没有排序的数据中进行查找，只能顺序查找，平均效率为n/2

1、主键索引的创建方法
1.在创建数据表时创建索引

        create table student(sno varchar(12) primary key);

2.修改数据表结构时创建索引

        alter table student add primary key(sno);

2、唯一索引的创建方法
1.在创建数据表时创建索引

        create table student(sno varchar(12) primary key,ssno varchar(12) unique);

2.修改数据表结构时创建索引

        alter table student add unique(ssno);

3.使用create语句在已有表上创建唯一索引（在数据表创建之后创建索引）

create unique index index_unique on student(ssno);（index_unique为索引名）

3、普通索引的创建方法
1.在创建数据表时创建索引

        create table student(sno varchar(12) primary key,ssno varchar(12),index(ssno) );

2.修改数据表结构时创建索引

        alter table student add index index_ssno(ssno);（index_ssno为索引名）

3.使用create语句在已有表上创建唯一索引（在数据表创建之后创建索引）

create index index_ssno on student(ssno);（index_ssno为索引名）

【例】为S,C,SC三个表建立索引，S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

CREATE UNIQUE INDEX StuNo ON S(Sno);

CREATE UNIQUE INDEX CourseNo ON C(Cno);

CREATE UNIQUE INDEX SCNo ON SC(Sno,Cno Desc);

ASC表示字段按升序排序；DESC表示字段按降序排序

【例】为选课表的'Sno'和'Cno'字段创建一个复合唯一索引，命名为sc_sno_cno_idx。

        create index sc_sno_cno_idx on sc (Sno,Cno);

4、查看索引和删除索引
1.查看索引

        show index from student;

2.删除索引

        drop index grade_index on sc;（grade_index为索引名）

5、索引总结
（1）索引的分类：聚集索引与非聚集索引的区别？

聚集索引：将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序完全一致。一个表只能建一个聚集索引

非聚集索引：完全独立于数据行的结构，不改变数据的物理存储顺序，通过指向数据行的指针寻找数据，可以建多个非聚集索引，最多249个

注：一个表中有聚集索引与非聚集索引，先建聚集索引

（2）唯一性索引与主键索引是一回事么？请举例说明。

唯一索引：索引列不能出现重复值。事实上，唯一索引最常用在primary key（主键）字段上

主键索引：主键索引是一种特殊的索引，当给表定义了主键时，将自动创建主键索引，主键索引一定是唯一索引，但既可以是非聚集索引，也可以是非聚集索引

复合索引：一个索引可以依据一个字段来创建，称为单值索引。也可以基于多个字段来创建，称之为复合索引。对于经常放在一起用来做查询条件的列组合，使用复合索引。复合索引同时也可以是唯一索引，即字段组合的值不能重复，

主键索引是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一条记录；唯一索引指的是索引列的所有值都只能出现一次，必须唯一

（3）为什么索引可以提高检索效率？

索引中提供了顺序，在已经排好序的数据的数据集合中查找数据，可以使用二分法，查询的平均时间为log2n。而如果在没有排序的数据中进行查找，只能顺序查找，平均效率为n/2。所以索引的作用在于提高查询速度

（4）索引创建的越多，数据库的操作效率越高么？为什么？

不是，因为索引自身也需要维护，并占用一定的资源，索引会减慢insert、update和delete语句的执行速度。

（5）视图存储数据吗？

视图中不存储具体的数据，而存储的是sql逻辑，基本表的数据发生改变，视图的展示结果也会随着发生改变，视图的展示结果也会随着发生改变，它占用较小的物理空间，而表中保存的是真实的数据，视图是窗口，只是逻辑概念的存在，视图只能有创建语句来修改，基本表是内模式，视图是外模式

二、视图
视图的概念：

视图是一个虚拟表，相当于一个定制的查询

视图的作用：

简化操作。视图被定义后，在以后的相同查询时，调用视图就可以了，不必每次构造查询
提高数据操作安全性。允许用通过视图访问数据，而不授予用户直接访问基本表的权限
实现数据库的逻辑独立性，当基础表结构发生改变时，通过修改视图即可保证应用程序不做改变
 1、创建视图
create view 视图名 [(列名[,列名]...)]

[with encryption] as <select 查询语句> [with check option]

说明：

视图的列名，可以显式命名，也可缺省查询结构中的列名
with encryption：对视图加密，一旦加密不能解密
with check option：当对视图进行插入、更新时，要检查新元组是否满足对应的条件
 【例】定义全体男生视图

        create view s_boy as select sno,sname,sex from student where sex='男';

        当执行insert into s_boy values('001','小白','女');时不会报错，但是视图中不会存在这条数据

【例】定义全体男生视图，并要求进行增删改操作时仍保证该视图只有男生信息。

        create view s_boy as select student where sex='男' with check option;

        当执行insert into s_boy values('001','小白','女');时会提示：CHECK OPTION failed `db_example`.`s_boy`

基于多个表的视图：

        create view v_score(sno,sname,cname,grade) as select s.sno,sname,cname,grade from s,sc,c where s.sno=sc.sno and sc.cno=c.cno;

基于视图的视图：

        create view v_score90 as select * from v_score where grade>=90;

视图的限制：
        一个视图 最多 只能有 250 列。
        视图可以进行增，删，改操作，但受到一定的限制。
如果视图的select目标列包含聚集函数，则不能更新
如果视图的select子句使用了unique或distinct，则不能更新
如果视图中包括了group by子句，则不能更新
如果视图中包括经算数表达式计算出来的列，则不能更新
如果视图由单个表的列构成，但并没有包含主键，则不能更新
对于由单个表的列构成的视图，并且包含了单个表的主键，则可以更新。
2、修改视图
alter view <视图名>[(<视图列表>)] as <子查询>

【例】修改男生视图，该视图包括学号、姓名和性别字段。

        alter view s_boy as select sno,sname,sex from s where sex='男';

3、删除视图
        drop view <视图名>;         drop view s_boy;

4、查询视图
        视图定义后，对视图的查询操作如同对基本表的查询操作一样

        select * from s_boy where sno='003';（视图的建立简化了查询操作）

        select * from s_boy;

【例】利用视图VIEW_SCG修改姓名为'王丽'的学生的数据库系统的分数为'90'。

        alter view VIEW_SCG as select Grade from sc join s on sc.Sno=s.Sno join c on

sc.Cno=c.Cno where Sname='王丽' and Cname='数据库系统';

        update VIEW_SCG set Grade='90';

        select * from VIEW_SCG;
————————————————
版权声明：本文为CSDN博主「weixin_47940048」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_47940048/article/details/128390744