05.MySQL视图、事务、索引、账户管理

一、视图：

• 视图是什么

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

如果要查询姓张的同学的名字，学科，成绩：

select s.name,c.c_name,c.grade from student as s join score as c on s.id=c.stu_id and s.name like '张%';

（上面一条与下面两条执行结果一致）

建立视图：

create view v_name_grade as 
select s.name,c.c_name,c.grade from student as s join score as c on s.id=c.stu_id and s.name like '张%';

查询视图：

select * from v_name_grade;

• 定义视图：create view 视图名称 as select语句（视图名称建议v_开头）

• 修改视图：alter view 视图名称 as select语句;

• 查看视图：show tables; （查看表会将所有的视图也列出来）

• 例如：

• 视图的作用：找了一个帖子，比较清晰：https://blog.csdn.net/hxnlyw/article/details/81669964

作用一：提高了重用性（相当于封装select语句，简化了查询语句）

作用二：对数据库重构（如果数据库发生了改变，可以用视图给封装一下，让他看上去跟没变化一样）

作用三：提高了安全性能（如果想要限制用户查询可以对不同的用户，设定不同的视图）

作用四：让数据更清晰

 

二、事务

• 四大特性（ACID）：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

1、原子性(Atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

2、一致性(Consistency)：数据库总是从一个一致性的状态转换到另一个一致性的状态。（事务最终没有提交的话，事务中所做的修改也不会保存到数据库中。）

3、隔离性(Isolation)：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

4、持久性(Durability)：一旦事务提交，则其所做的修改会永久保存到数据库。

• 表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎。show create table goods;可以看到engine=innodb

• 开启事务：begin;或者start transaction;

• 提交事务：commit;

• 回滚事务：rollback;

• 注意：

1. 修改数据的命令会自动的触发事务，包括insert、update、delete

2. 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

 

三、索引

• 索引是什么：索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

• 查看索引：show index from 表名

• 创建索引：create index 索引名称 on 表名(字段名称(长度))

• 删除索引：drop index 索引名称 on 表名;

• 建立索引会占用磁盘空间

• 索引demo：

1、创建测试表test_index：create table test_index(title varchar(10));

2、使用source命令导入一万条数据：source C:\Users\Xpeng\Desktop\python_resource\python_test.sql（已经提供了python_test.sql文件了）

3、开启运行时间监测：set profiling=1;

4、普通查询第1w条数据：select * from test_index where title='ha-9999';

5、查看执行时间：show profiles;

6、创建索引：create index title_index on text_index(title(10));（如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致，字段类型如果不是字符串，可以不填写长度部分）

7、索引查询第1w条数据（跟普通查询一样的语句）：select * from test_index where title='ha-9999';

8、再次查看执行时间：show profiles;

 

四、账户管理---授予权限

• 查看所有用户：所有用户及权限信息存储在mysql库的user表中，authentication_string表示密码，为加密后的值

select host,user,authentication_string from mysql.user;

• 创建用户：create user '用户名'@'允许访问的主机(本地为localhost)' identified '密码';

• 给用户授权：grant 权限 on 数据库名.表名 to '用户名'@'主机';

1、需要使用实例级账户登录后操作，以root为例

2、常用权限主要包括：create、alter、drop、insert、update、delete、select

3、如果分配所有权限，可以使用all privileges

• 查看用户权限：show grants for mx@localhost;

• 退出root登录：quit

• 使用mx登录：mysql -u mx -p

 

五、账户管理---账户操作

• 修改权限：grant 权限名称 on 数据库 to 账户@主机 with grant option;

• 修改用户密码（用root用户修改）：update mysql.user set authentication_string=password('新密码') where user='用户名';

• 注意修改完成后需要刷新权限：flush privileges

• 删除用户1：drop user '用户名'@'主机';（如果删除失败用2）

• 删除用户2：delete from user where user='用户名';然后刷新权限flush privileges