MySQL高级

1.视图

1.对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦

解决办法：定义视图
		
2. 视图是什么
通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）

3.	定义视图
create view 视图名称 as select语句
4. 查看视图(查看表会将所有的视图也列出来)
 show tables;
 5.使用视图(视图的用途就是查询)
 select * from v_stu_score;
6.删除视图
 drop view 视图名称;
例：
drop view v_stu_sco;


视图的作用
提高了重用性，就像一个函数
对数据库重构，却不影响程序的运行
提高了安全性能，可以对不同的用户
让数据更加清晰

2.事务

事务广泛的运用于订单系统、银行系统等多种场景

事务四大特性
	原子性(Atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
	
	一致性(Consistency)：数据库总是从一个一致性的状态转换到另一个一致性的状态。
	
	隔离性(Isolation)：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

	持久性(Durability)：一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）



事务的隔离级别
Read uncommitted

字面意思是读未提交，一个事务可以读取另一个未提交事务的数据，即出现脏读。

Read committed

字面意思是读提交，一个事务要等另一个事务提交后才能读取数据，也就是不可重复读（每次读取的结果不一样）。若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。

Repeatable read

字面意思是重复读，就是在开始读取数据（事务开启）时，不再允许修改操作，也就是幻读。重复读可以解决不可重复读问题。不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

Serializable

字面意思序列化，Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。MySQL的默认隔离级别是Repeatable read。

以上4种事务隔离级别，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、+Serializable 。 






事务命令

开启事务：开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

	begin;
	或者
	start transaction;
提交事务：将缓存中的数据变更维护到物理表中
		commit;
回滚事务，命令如下：
				放弃缓存中变更的数据	
				rollback;

3.提交

为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表（用到之前的jing_dong数据，可以回到mysql第3天中查看）

1）step1：连接
终端1：查询商品分类信息
select * from goods_cates;

2)step2：增加数据
终端2：开启事务，插入数据
begin;
insert into goods_cates(name) values('小霸王游戏机');

终端2：查询数据，此时有新增的数据
select * from goods_cates;

3)step3：查询
终端1：查询数据，发现并没有新增的数据
select * from goods_cates;

4)step4：提交
终端2：完成提交
commit;

5)step5：查询
终端1：查询，发现有新增的数据
select * from goods_cates;

4.回滚

为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表
#step1：连接
1)终端1
select * from goods_cates;

2)step2：增加数据
终端2：开启事务，插入数据
begin;
insert into goods_cates(name) values('小霸王游戏机');

终端2：查询数据，此时有新增的数据
select * from goods_cates;

3)step3：查询
终端1：查询数据，发现并没有新增的数据
select * from goods_cates;

4)step4：回滚
终端2：完成回滚
rollback;

5)step5：查询
终端1：查询数据，发现没有新增的数据
select * from goods_cates;

5.索引:索引的目的在于提高查询效率

1).索引的使用
查看索引
show index from 表名;

创建索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))

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


注意：要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间

6账户管理

进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限
通常都使用数据库级操作权限

7.授予权限

 创建账户&授权
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';

8.账户操作

1）修改权限
grant 权限名称 on 数据库 to 账户@主机 with grant option;
grant select，insert jing_dong.* to laowang@localhost with grant option;


2）修改密码
使用root登录，修改mysql数据库的user表
使用password()函数进行密码加密

update user set authentication_string=password('新密码') where user='用户名';
例：
update user set authentication_string=password('123') where user='laowang';

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

3)远程登录（危险慎用）
如果向在一个Ubuntu中使用msyql命令远程连接另外一台mysql服务器的话，通过以下方式即可完成，但是此方法仅仅了解就好了，不要在实际生产环境中使用
	1）修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件

2）

	3)然后重启msyql
	service mysql restart			
	4)在另外一台Ubuntu中进行连接测试	



4)删除账户

	语法1：使用root登录	
	drop user '用户名'@'主机';
	例：
	drop user 'laowang'@'%';
	
	法2：使用root登录，删除mysql数据库的user表中数据
	delete from user where user='用户名';
	例：
	delete from user where user='laowang';
	注意：
	推荐使用语法1删除用户, 如果使用语法1删除失败，采用语法2方式