day36 引索index/过程/视图/触发器/DCL

• 1.索引（index）
  -- 索引可以加速查询所以应该在经常用于查询筛选条件的列上建立索引
  -- 索引会使用额外的存储空间而且会让增删改变得更慢（因为要更新索引）
  -- 所以不能够滥用索引

create index idx_emp_ename on tb_emp (ename);
drop index idx_emp_ename on tb_emp;

• 2.（存储）过程/函数：把一系列的SQL可以封装到一个过程中，而且可以加上分支和循环，将来通过过程的名字直接调用过程即可，因为创建过程时已经提前编译了SQL语句，所以比直接执行SQL语句性能更好

---

delimiter $$

create procedure sp_dept_avg_sal(deptno int, out avgsal float)
begin
    select avg(sal) into avgsal from tb_emp where dno=deptno;
end$$

delimiter ;

call sp_dept_avg_sal(20, @a);

---

• 4.视图：查询的快照（简化查询操作）
  -- 通过视图可以将用户的访问权限限制到某些指定的列上

create view vw_emp_dept as 
select eno, ename, dname from tb_emp t1 inner join tb_dept t2 on t1.dno=t2.dno;

select ename, dname from vw_emp_dept;

  drop view vw_emp_dept ;

---

• 4.explain生成执行计划

explain select eno, ename from tb_emp where eno=7800;
explain select eno, ename from tb_emp where eno<>7900;
explain select eno, ename from tb_emp where ename='张三丰';
explain select eno, ename from tb_emp where ename like '张%';
explain select eno, ename from tb_emp where ename like '%张';
explain select eno, ename from tb_emp where ename<>'张三丰';

---

• 5.触发器：在执行增删改操作时可以触发其他的级联操作，但是有可能导致“锁表”现象，实际开发中应该尽量避免使用触发器

-- update tb_dept set dno=11 where dno=10;
-- delete from tb_dept where dno=11;

delimiter $$

create trigger tr_dept_update 
after update on tb_dept for each row
begin
    update tb_emp set dno=new.dno where dno=old.dno;
end$$

delimiter ;

drop trigger tr_dept_update;

• 6.DCL：授予权限(grant to)和召回权限(revoke from)

create user 'hellokitty'@'%' identified by '123123';

grant all privileges on hrs.* to 'hellokitty'@'%';

revoke insert, delete, update on hrs.* from 'hellokitty'@'%';

drop user 'hellokitty'@'%';

• 6.事务

-- 事务（transaction）- 把多个增删改的操作做成不可分割的原子性操作
-- 要么全部都做，要么全都不做

-- start transaction;
-- 开启事务环境
begin;

delete from tb_emp;

-- 提交（事务中的所有操作全都生效）
commit;
-- 回滚（事务中的所有操作全部撤销）
rollback;