10）索引、视图

一个数据库可以创建多个索引；为了提升效率，索引需排序；索引会占用额外空间；（索引可以看作 字典的检索目录）

索引和约束：

系统会自动为主键、唯一和外键字段创建索引；

索引选取原则：

 

 创建数据库测试；

 

1、创建索引：两种方法；

　　1）创建表的同时创建索引：

create table 表名(
    字段名1 数据类型[约束条件],
    ...
    [其他约束条件],
    [unique|fulltext] index [索引名](字段名[(长度)])
);

　　2）在已有表上创建索引：

create [unique|fulltext] index 索引名 on 表名 (字段名[(长度)]);

2、查看索引：

show index from 表名\G

3、删除索引：

drop index 索引名 on 表名;

 

例如：我们新建一个学生表stu；

create table stu select * from student;
create unique index ix_stuno on stu(student_no);#多了一个pri主键索引；

第一行首先复制一张表；数据都会复制；但是约束类型没有；第二行，我们增加一个pri主键，并创建索引；

接下来我们显示索引号；

show index from stu\G  #按列显示

table表示表名；

Non_unique表示唯一值，则为0，若有重复值，为1；

key_name表示索引名称；

Seq_in_index:索引序列；

column_name表示该列的字段名称；

collation表示列以什么方式存储再索引中，其中A表示升序；

cardinality表示唯一值数目的估计值；

sub_part表示 整列做索引则为null，若是部分做索引，则为创建索引时的长度；

packed表示关键字如何被压缩；

nul表示是否有空值；

index_type表示使用什么方法创建索引，此处是b树；

 

接着我们删除这个索引；

drop index ix_stuno on stu;

 

 4、创建视图：

视图中保存的仅仅是一条select语句，该select语句的数据源可以是基表，也可以是另一个视图；

create view 视图名[(视图字段列表)]
as
    select语句;

　　

例如：对新表stu执行创建视图操作；

create view vw_stu(学号,姓名,联系方式)
as
    select student_no,student_name,student_contact from stu;

要注意，对视图 的增删改查就是 对源表的 增删改查 ；这是针对单张表的，这种情况一定正确；

insert into vw_stu values('2023007','小白','2220000007');

 

update vw_stu set 姓名='大白' where 学号='2023007';

 但是针对 连接表的情况：增删改查不一定正确；

create table cls select * from classes;
create view vw_stu2
as
    select s.student_no,s.student_name,c.class_name
    from student s,classes c
    where c.class_no = s.class_no;

 执行 插入、删除操作：可以发现都报错；

insert into vw_stu2 values('2023001','三张','2023自动化2班');
delete from vw_stu2 where student_no='2023004';

 但是执行更新操作：发现可以操作，但是不一定每次都成功；

update vw_stu2 set class_name='2023机械1班' where student_no = '2023003';

 当们执行下面更新操作时，就发现是错误的；他把两个自动化1班的都变为机械1班；

 

5、查看视图：

使用查看表结构的方式查看视图的定义：

desc 视图名;

我们在系统数据库 information_schema 的 views 存储了所有视图的定义；

 我们查看views中我们创建的vw_stu 视图;

use information_schema;
select * from views where table_name='vw_stu'\G

 

6、删除视图：

drop view 视图名;

drop view vw_stu;

 

7、视图的作用：

使操作变简单：可以简化数据查询操作；

避免数据冗余：由表派生出来许多视图，这些视图只由用户关心的那部分组成；

增强数据安全性：派生出来的视图，用户只能修改自己对应的那部分；

提高数据的逻辑独立性：数据库表和视图之间实现一定的隔离；