视图~触发器~索引

视图

• 视图的概念
  就是由数据库中的一张表或多张表根据指定条件查询出的数据构造成的虚拟表
• 视图的作用
  • 安全性 ：如果我们直接将数据表授权给用户操作，那么用户可以CRUD数据表中所有数据。如果我们想要对数据表中的部分数据进行保护，可以将公开的的数据生成视图，授权用户访问视图；用户通过查询视图可以获取数据表中的公开数据，从而达到将数据表中的部分数据对用户隐藏。
  • 简单性 ：如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图获取多表数据，操作更便捷。
• 创建视图
  • 语法

  *** 语法
  create view v_name
  as
  select_statement;---SQL语句
  

  • 实例 :

  *** 创建视图 ：将学生表中性别为男的学生看成一个视图
  create view v_name1
  as
  select * from student where s_gender='男';
  *** 查询视图
  select * from v_name1;
  

  *** 创建视图 ：查询学生借书信息
  create view v_name2
  as
  select s.s_name,b.b_name,borrow_num 
  from book b inner join records r inner join student s
  on b.book_id=r.bid and r.snum=s.s_num;
  *** 查询视图 
  select * from v_name2;
  

• 查询视图的特性
  查询操作 ：如果在数据表中添加了新的数据，而且这个数据满足创建视图时的查询语句的条件，通过查询视图也可以查询出新增的数据；
  新增数据 ：如果在视图中添加数据，数据会被添加到原数据表。
  删除数据 ：如果从视图中删除数据，数据也将从原表中删除，当删除原表中满足查询条件的数据时，也会从视图中删除。
  修改操作 ：如果通过修改数据，则将修改原数据表中的数据。
  视图使用的建议 ：对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图
• 查询视图结构

desc v_name;

• 修改视图

*** 方式一
create or replace view v_name1
as
select * from student where s_name='女'；
*** 方式二
alter view v_name1
as
select * from student where s_name='男';

• 删除视图

*** 删除数据表时会同时删除数据表中的数据，删除视图时不会影响原数据表中的数据
drop view v_name1;

触发器

       触发器，就是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据表中的数据执行DQL操作时自动触发这个SQL片段的执行，无需手动调用。

触发器的使用

• 案例

*** 学生信息表
create table student(
	s_num varchar(4) primary key,
	s_name varchar(20) not null,
	s_gender varchar(20) not null,
	s_age int not null
);
*** 学生信息操作日志表
create table slog(
	id int primary key auto_increment,
	time timestamp,
	log varchar(200)
);
*** 当向学生表中添加学生信息时，同时要在日志表中添加一条操作日志
insert into student(s_num,s_name,s_gender,s_age) values('2021','小机灵鬼儿','女',18);
*** 手动进行日志记录
insert into slog(time,log) values(now(),'添加2021学生信息');

创建触发器

• 语法

create trigger t_name
<before|after> ---定义触发时机
<insert|delete|update> ---定义DML类型
on <tablename> 
for each row ---声明为行级触发器（只要操作一条记录就能触发触发器执行一次）
sql_statement ---触发器操作

*** 创建触发器 ：当学生信息表发生添加操作时，则向日志信息表中记录一条日志
create trigger t_name1
after insert on student
for each row
insert into slog(time,log) values(now(),concat('添加',new.s_num,'学生信息'));

查看触发器

show trigger;

测试触发器

• 我们创建触发器是在student表发生insert操作时触发，我们只需要执行学生信息的添加操作

*** 测试1 ：添加一个学生信息，触发器执行了一次
insert into student values('2022','娃哈哈','男',19);
*** 测试2 ：一条SQL指令添加了2条学生信息，触发器就执行了两次
insert into student values('2020','QQ星','女',18),('2019','养乐多','女',17);

删除触发器

drop trigger t_name;

NEW与OLD

       触发器用于监听对数据表中的数据的insert，delete，update操作，再出发其中通常处理一些DML的关联操作；我们可以使用NEW和OLD关键字在触发器中获取这个触发器的DML操作的数据

• NEW ：在触发器中用于获取insert操作添加的数据、update操作修改后的数据
• OLD ：在触发器中用于获取delete操作删除前的数据、update操作修改前的数据

NEW

• insert操作中 ：NEW表示添加的新纪录

create trigger t_name2
after insert on student
for each row 
insert into slog(time,log) values(now(),concat('添加',NEW.s_name,'学生信息'));

• update : NEW表示修改后的数据

*** 创建触发器 ：在监听update操作的触发器中，可以使用NEW获取修改后的数据
create trigger t_name3
after update on student 
for each row
insert into slog(time,log) values(now(),concat('修改学生信息为:',NEW.s_num,NEW.s_name));

OLD

• delete操作中 ：OLD表示删除的记录

create trigger t_name4
after delete on student 
for each row
insert into slog(time,log) values(now(),concat('删除',OLD.s_num,'学生信息'));

• update操作中 ：OLD表示修改前的记录

create trigger t_name5
after update on student
for each row
insert into slog(time,log) values(now(),concat('将学生姓名从【',OLD.s_name,'】修改为【',NEW.s_name,'】'));

触发器使用总结

优点

• 触发器是自动执行的，党对处罚其相关的执行相应的DML操作时立即执行；
• 触发器可以实现表中的数据级联操作（关联操作），有利于保证数据的完整性；
• 触发器可以对DML操作的数据进行更为复杂的合法性校验

缺点

• 使用触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难；
• 大量使用触发器容易导致代码结构杂乱，增加了程序的复杂性；
• 当触发器操作的数据量比较大时，执行效率会大大降低

使用建议

• 在互联网项目中，应避免使用触发器
• 对于并发量不大的项目可以选择使用存储过程，但是在互联网引用中不提倡使用存储过程（原因 ：存储过程是将实现业务的逻辑交给数据库处理，一则增建了数据库的负载，二则不利于数据库的迁移）

索引

       数据库是用来存储数据的，在互联网应用中数据库可能会存出大量数据，数据表中数据的查询速度会随着数据量的增长逐渐变慢，从而导致响应用户请求的速度变慢----用户体验性差，而索引就是用来提高数据表中数据的查询效率的
       索引 ：将数据表中某一列/某几列的值取出来构造成便于查找的结构进行存储，生成数据表的目录。
       当我们进行数据查询的时候，则先在目录中进行查找得到对应的数据地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描

索引的分类

• 主键索引 ：在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有一个主键。
• 唯一索引 ：在数据表中的唯一列创建的索引（unique），此列的所有值只能出现一次，可以为null。
• 普通索引 ：在普通字段上创建的索引，没有唯一性的限制。
• 组合索引 ：两个及以上字段联合起来创建的索引。
  说明 ：
  1.在创建数据表时，将字段声明为主键（添加主键约束），会自动在主键字段创建主键索引；
  2.在创建数据表时，将字段声明为唯一键（添加唯一约束），会自动在唯一字段创建唯一索引；

创建索引

唯一索引

*** 创建唯一索引 ：创建唯一索引的列的值不能重复
*** create unique index <indexname> on 表名（列名）
create unique index i_name1 on t_nameindex(tid);

普通索引

*** 创建普通索引 ：不要求创建索引的列值的唯一性
*** create index <indexname> on 表名(列名);
create index i_name2 on t_nameindex(name);

组合索引

*** 创建组合索引
*** create index <indexname> on 表名(列名1，列名2...);
create index i_name3 on t_nameindex(tid);

全文索引

5.6版本新增，可以通过此索引进行全文检所操作，但mysql全文检索不支持中文操作
create fulltext index <indexname> on 表名(列名);

索引的使用

       索引创建完成之后无需调用，当根据创建索引的列进行数据查询的时候，会自动使用索引；
       组合索引需要根据创建索引的所有字段进行查询时触发；

• 在命令行中查看查询语句的查询规划

explain select * from t_nameindex where tid=数据\G;

查看索引

*** 命令行
show create table t_nameindex\G;
*** 查询数据表的索引
show indexes from t_nameindex;
*** 查询索引
show keys from t_nameindex;

删除索引操作

*** 删除索引 ：索引是建立在表的字段上的，不同的表中可能会出现相同名称的索引
***          因此删除索引时需要指定表名
drop index i_name3 on t_nameindex;

总结

优点

• 索引大大降低了数据库服务器在执行查询操作时扫描的数据，提高查询效率
• 索引可以避免服务器排序，将随机IO编程顺序IO

缺点

• 索引是根据数据表的列创建的，当数据表中数据发生DML操作室，索引页需要更新
• 索引文件会占用磁盘空间

注意事项

• 数据表中数据不多时，全表扫描可能更快，不需要使用索引
• 数据量大但是DML操作很频繁，不建议使用
• 不要在数据重复度高的列上创建索引
• 创建索引之后，要注意查询语句的编写，避免失效