Day13 数据库中的索引、视图、外键
索引---index是一种数据结构，主要是添加在一些频繁被查询的数据字段（热字段）上，有点类似书本的目录，是可以有效的提高数据的查询速度
注意：
1、创建索引是在大量数据的前提下，添加索引可以提高查询速度
2、不是每个字段都要加索引的，如果每个字段都添加了索引就没有实际的意义，就算添加了也会导致消耗大量的系统资源
3、添加索引可以提高查询速度，但是添加索引之后都影响数据插入或者数据更新的速度
4、索引分为3种
（1）主键索引primary key PRI:一个表中只能存在一个主键索引，要求对应字段的值 唯一且不能为空
（2）普通索引index MUL:表中是可以存在多个普通索引，对索引字段的值是没有要求的
（3）唯一索引unique index UNI：表中是可以存在多个唯一索引，对索引字段的值要求不允许重复，允许为空
5、索引的优先级：PRI>UNI>MUL
创建普通索引---index
1、使用create index语句创建索引（只能创建普通索引）
desc emp；
给单个字段创建普通索引
create index aa on emp(name); --- 创建索引 aa 在emp表中的name字段上
show index from emp; --- 展示索引来自于 emp表 ==》查看emp表的索引情况
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjg-z7VlMKIlJr899t8jhC88\cd6cef1e626c4ac2b186cefc3de3a49d\hxg4lvjj732y6544){)f2{d.png
给多个字段创建普通索引--复合索引
create index bb on emp(age,incoming); --创建索引 bb 在emp表中的age和incoming字段上
删除索引：
alter table emp drop index bb;--修改表 emp表 丢弃索引 bb
2、通过alter table的方式创建索引
alter table emp add index bb(age); --修改表 emp表 添加 索引 bb在age字段上
唯一索引 unique index
1、通过create unique index创建
create unique index cc on emp(incoming); --创建唯一索引 cc 在 emp表的incoming字段上
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjg-z7VlMKIlJr899t8jhC88\a58b6e1d9860410e8be8d232305f49a1\~cxi$sqs@mnwe)bie4ru5@n.png
---create unique index ee on emp(dept2);
执行报错，因为在emp表的dept2字段，它的值是存在重复的，导致唯一索引ee创建失败
2、通过alter table创建
alter table emp add unique index ee(worktime_start);--修改表 emp表 添加 唯一索引 ee 在worktime_start字段上
删除唯一索引：（和删除普通索引是一样的）
alter table emp drop index ee;--修改表 emp表 丢弃索引 ee==》删除emp表上的唯一索引ee
alter table emp drop index aa,drop index bb,drop index cc; --同时删除emp表上的多个索引
create unique index ee on emp(name,dept2); -- 创建复合唯一索引ee，作用在emp表上的name和dept2字段上，要求name和dept2字段的值组合后是唯一，但允许为空
主键索引 primary key
在创建主键时，主键索引就同时创建成功
主键索引和主键约束其实是绑在一起的，如果有了主键索引，那么必然会产生主键约束，或者说主键约束是主键索引的一个特征
视图--view
视图是一个虚拟的表，由行和列组成的数据，但是它并不是用来存储数据，只是用来显示数据
视图是建立在实际的表之上，视图的数据是动态生成的（根据实际的表数据做实时计算得到的）
视图的作用：
1、为了安全，在公司中有些字段是保密字段，那么可以创建视图，限制对某些字段的查询
2、简化查询，因为有些查询可能很复杂（多表查询、分组查询），而且这种查询经常被使用，为了避免重复编写SQL时的错误，通过把这些复杂的SQL创建为视图的方法，避免重复编写SQL
视图的特点：
1、视图是由实际的表产生的
2、视图的查询本质上是对实际表进行查询操作，所以使用视图性能相对差
3、视图的创建和删除是不会影响实际的基础表的，但是数据是会互相影响的
4、当一个视图的数据来源是复杂的SQL时，那么视图的数据是不允许修改的
创建视图（可以理解为对基础表中的数据进行复制）
select * from grade;--grade表是本身就是存在于mysql中的一张实际的数据表
create view dcs as(select class,name,age from grade); --创建视图dcs 根据 查询class，name，age字段的值来源grade表
show create view dcs; -- 查看视图表dcs的结构（建视图的语句）
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjg-z7VlMKIlJr899t8jhC88\2b1a31093ee44089bd16a67e8f35eed6\7xc}cpfp9_1~c@k2_)r~
#基础表和视图的数据修改是可以互相影响的（只能是简单的SQL创建的视图表）
update grade set name='张三' where name='zhangsan'; -- 修改实际表中的数据
update dcs set age=99 where name='lisi'; -- 修改视图表中的数据
drop view dcs; -- 丢弃视图 dcs ==》删除dcs视图表
show table status where comment='view'; -- 通过SQL的方式查看当前库中有哪些视图
视图的数据来源复杂的查询时，是不允许修改视图中的数据的
select dept2,avg(incoming) as av from emp group by dept2
create view empavg as(select dept2,avg(incoming) as av from emp group by dept2);
select * from empavg; -- 执行时，本质上是执行了select dept2,avg(incoming) as av from emp group by dept2
（平常开发或者DBA去操作的）DDL ===》数据库定义语言 ===》对表的定义、表字段进行操作 ===》create（创建）、alter table（修改表）、drop（删除）
（我们平常使用数据库）DML ===》数据库操作语言 ===》对表中数据进行操作 ==》增insert into、删delete、查select、改update...set
------------------------------------
表内约束有哪些
主键约束
非空约束
默认值约束
自增长约束
外键约束 foreign key 外码
约束的是表和表之间的内容
作用：为了避免一张表的数据太过冗余，就要对表进行拆分解耦，而且为了避免解耦之后数据不完整，所以要引入外键，约束数据的一致性和完整性，保持被约束的表数据的一致性、完整性
创建两张表dcs1和dcs2
把dcs1设计为主表，dcs2设计为从表
1、先创建dcs1表--主表
create table dcs1(id int(4) primary key,name varchar(20)) engine=innodb; ---创建表 dcs1表里面包含 id字段属性为int(4) 主键，name字段属性为varchar(20)，数据引擎为innodb
因为MySQL普通的建表数据引擎默认为myisam是不支持外键，支持外键约束的是innodb
2、创建dcs2表--从表，加上外键，引用dcs1表中id字段
create table dcs2(sid int(4) primary key,sname varchar(20),constraint gz foreign key(sid) references dcs1(id)) engine=innodb;
--创建表 dcs2表里面包含 sid字段属性为int(4) 主键，sname字段属性为varchar(20)，创建约束名称为gz 外键定义在sid字段上 引用dcs1表中的id字段 数据引擎为innodb
constraint gz --创建约束名称为gz
foreign key(sid) --外键定义在sid字段上
references dcs1(id)--引用dcs1表中的id字段
当完成上述的建表语句之后，dcs2表中的sid字段的值就被dcs1表中的id字段约束着（当dcs1表中id字段没有x值时，那么dcs2的sid就不能存在x这个值）
3、往dcs1和dcs2分别插入数据
insert into dcs1 values(1,'xiaomai'),(2,'laomai'); ---往dcs1表插入两行数据
select * from dcs1;
insert into dcs2 values(1,'xxxxx'); --- 往dcs2表（主表）插入一条主表id字段存在的数据，可以插入成功的
select * from dcs2;
4、往dcs2表插入一条主表id字段不存在的数据
insert into dcs2 values(3,'yyyyy'); ---报错，因为3是不存在主表的id字段中
5、把dcs1主表中已经被从表引用的数据进行更新或者删除
update dcs1 set id=4 where name='xiaomai' --- 报错，因为id=1的值被从表的sid引用着
delete from dcs1 where id=1; ---报错，因为id=1的值被从表的sid引用着
delete from dcs2 where sid=1; --- 先把子表中的sid=1的值进行删除，后续就可以删除或者修改主表中对应的值
6、删除外键，只要对子表进行操作即可，因为外键是定义在子表上的
alter table dcs2 drop foreign key gz; -- 修改表 dcs2表 丢弃 外键 gz；
总结论：当主表中被引用的字段没有x值时，那么从表的外键约束字段就不能存在x这个值
主表不存在的数据，在与之相关的从表是无法插入对应的数据
1、对从表插入数据时，如果主表没有对应的数据，则无法插入
2、要删除或者修改主表的数据，要先确认主表的数据有没有被引用，如果被引用要先删除从表中对应的数据
页:
[1]