03.授权、事务、视图和索引

DCL（数据控制语言）

DCL主要提供授权和召回授权以及事务等相关功能

1.用户管理（root账号才有的权限）

1）创建用户

create user '用户名'@'登录地址'； - 创建数据库用户，该用户登录不需要密码

create user '用户名'@'登录地址' identified by '密码'；

说明：

用户名 - 随便命名

登录地址 - ip地址/localhost（本机）/%（任意位置）

CREATE USER 'user1'@'localhost';
CREATE user 'user2'@'%' IDENTIFIED by '123456';

2）删除用户

drop user '用户名'；

drop user 'user2';
drop user 'user1'@'localhost';

2.授权管理

1）授权

grant 权限类型 on 数据库.表 to 用户名；

说明：

权限类型 - insert，delete，update，select，create，drop，all privileges

grant select on school.tb_student to 'user1'@'localhost';
grant update,delete on school.tb_student to 'user1'@'localhost';
grant select on hrs.* to 'user1'@'localhost';

2）召回授权

revoke 权限类型 on 数据库.表 from 用户；

revoke update on school.tb_student from 'user1'@'localhost';

3.事务

如果完成一个任务需要多个操作，但是要求多个操作中，只要有一个失败，整个任务就会取消，同时让数据库回到任务开始前的状态，只有所有的操作都成功的情况下，数据库才会更新。这个时候我们就需要用到事务。

-- 开启事务环境
begin;
-- 提交事务
commit;
-- 事务回滚
rollback;

视图

视图是用来存储一个sql查询语句的结果的。就相当于给查询语句的结果创建了一张临时表，但是该临时表不占物理内存

1.创建视图

create view 视图名 as sql查询语句； - 给sql查询语句的结果创建一张临时表

eg:获取学生学科的成绩

select stuid,stuname,couid,couname,score from tb_student,tb_record,tb_course where tb_student.stuid=tb_record.sid and tb_record.cid=tb_course.couid;

--对以上查询创建视图

create view vw_stu_info as (select stuid,stuname,couid,couname,score from tb_student,tb_record,tb_course where tb_student.stuid=tb_record.sid and tb_record.cid=tb_course.couid);

-- 当视图被创建之后，可以直接对该视图进行查询操作

2.使用视图

视图在使用的时候和表的使用方法一样（注意：视图一般只用于查询操作，而不进行增删改的操作）

-- 查询视图和查询表一样
select stuname,avg(score)from vw_stu_info group by stuid;

注：当对用户进行授权的时候，可以只针对视图进行授权操作。

create view vw_emp_info as select eno,ename,job,mgr,dno from tb_emp;

grant select on hrs.vw_emp_info to 'user1'@'localhost';

索引

索引就像书的目录，记录了数据的位置，可以提高查询的速度（用空间换时间），一般需要给使用频率较高的字段添加索引；（主键自带索引 - 唯一索引）

1.添加索引

create index 索引名 on 表名 （字段）； - 给指定的字段添加普通的索引

create unique index 索引名 on 表名 （字段）； - 给指定字段添加唯一索引（字段是唯一的时候才能添加唯一索引）

EXPLAIN SELECT * FROM tb_student where stuname='项少龙';
EXPLAIN SELECT * FROM tb_student WHERE stuid =3755;
-- explain 获取sql语句的执行计划，是用来查看执行性能的方法

create index idx_stuname on tb_student(stuname);
EXPLAIN SELECT * FROM tb_student where stuname='项少龙';

注意：模糊查询的时候以%，_开头时，相较于无索引查询，效率几乎不会提高

2.删除索引

alter table 表名 drop index 索引名；

alter table tb_student drop index idx_stuname;

注意：

索引不能滥用

• 索引会占用额外的空间
• 索引会让增删改的操作变得更慢

如果哪个列经常被用于查询操作，就给那个列添加索引。