mysql数据-高级(视图/事物/索引/账户管理/主从配置)

视图（了解）
事务（重要）
索引（重要）
账户管理（了解）
主从（了解）

一、视图

1.概念：
	通俗的讲，视图就是一条select语句执行后返回的结果集。
	视图是对若干张基本表的引用，是一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性。

2.定义视图---建议以v_开头
	create view 视图名称 as select语句;

3.查看视图
	show tables;
	select * from v_stu_score;
	
4.删除视图
	drop view 视图名称;
	drop view v_stu_sco;

5.视图的作用
	用来方便查询
	1. 提供了重用性，就像一个函数
	2. 对数据库重构，却不影响程序的运行
	3. 提供了安全性能，可以对不同的用户
	4. 让数据更加清晰

6.实例
	创建视图
	create view v_goods as
	select g.*,c.name as cate_name,b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;
	查看表
	show tables;
	查看所有
	select * from v_goods;

二、事务

1. 概念
	所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的供作单位。

2事务四大特性(简称ACID)
	原子性(Atomicity)
		一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
	一致性(Consistency)
		数据库总是从一个一致性的状态转换到另一个一致性的状态。
	隔离性(Isolation)
		通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
	持久性(Durability)
		一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

3.事务命令
	表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎。
	查看表的创建语句，可以看到engine=innodb

	开启事务，命令如下：
		begin;
		或者
		start transaction;
	提交事务，命令如下
		commit;
	回滚事务，命令如下：
	放弃缓存中变更的数据
		rollback;
	注意
		1. 修改数据的命令会自动的触发事务，包括insert、update、delete
		2. 在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据。

三、索引

1.概念
	索引是一种特殊的文本件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
	更通俗的说，数据库索引好像是一本书目录，能加快数据库的查询速度。

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

3.索引原理
	利用特殊算法--分段查询

3.查看索引
	show index from 表名;
	
4.创建索引
	如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
	字段类型如果不是字符串，可以不填写长度部分
	create index 索引名称 on 表名(字段名称(长度))
	
5.删除索引：
	drop index 索引名称 on 表名;

6. 索引demo
创建测试表testindex
	create table test_index(title varchar(10));

使用python程序通过pymsql模块 向表中加十万条数据
	--------------------------------------------
	from pymysql import connect
	def main():
		# 创建Connection连接
		conn = connect(host='localhost',port=3306,database='jing_dong'
		# 获得Cursor对象
		cursor = conn.cursor()
		# 插入10万次数据
		for i in range(100000):
		cursor.execute("insert into test_index values('ha-%d')" % i)
		# 提交数据
		conn.commit()
	if __name__ == "__main__":
		main()
	------------------------------------------------
查询
	开启运行时间监测：
	set profiling=1;
	查找第1万条数据ha-99999
	select * from test_index where title='ha-99999';
	查看执行的时间：
	show profiles;

	为表title_index的title列创建索引：
	create index title_index on test_index(title(10));
	执行查询语句：
	select * from test_index where title='ha-99999';
	再次查看执行的时间
	show profiles;
	
7. 注意：
	建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

四、账户管理

1.账户管理简介
	在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud。
MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种：
	服务实例级账号：
		拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表。
	数据库级别账号：
		对特定数据库执行增删改查的所有操作
	数据表级别账号：
		对特定表执行增删改查等所有操作
	字段级别的权限：
		对某些表的特定字段进行操作
	存储程序级别的账号：
		对存储程序进行增删改查的操作。
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限

2.授予权限
	主要操作包括：
	查看所有用户
	修改密码
	删除用户
	
	2.1	查看所有用户
		创建用户后，用户会储存到mysql数据库的user表中
		use mysql;
		desc user;
		主要字段说明：
			Host表示允许访问的主机
			User表示用户名
			authentication_string表示密码，为加密后的值
		查看所有用户
		select host,user,authentication_string from user;

	2.2	创建账户、授权
	常用权限主要包括：create、alter、drop、insert、update、delete、select
	如果分配所有权限，可以使用all privileges
	
	创建账户&授权
	grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

	2.3 示例
	step1：使用root登录
		mysql -uroot -p
		回车后写密码，然后回车
	step2：创建账户并授予所有权限
		grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';
		查看用户有哪些权限
		show grants for laowang@localhost;
		说明：
		1.可以操作数据库的所有表，方式为:jing_dong.*
		2.访问主机通常使用‘%’，表示此账户可以使用任何ip的主机登录访问此数据库。
		3.访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问。
		
	step3：退出root的登录
		quit

	step4：使用laowang账户登录
		mysql -ulaowang -p
		回车后写密码，然后回车

3.账户操作

	3.1 修改权限
	grant 权限名称 on 数据库 to 账户@主机 with grant option;
	grant select,insert on jing_dong.* to laowang@localhost with grant option;
	flush privileges;-----提交权限
	
	3.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.3	远程登录（危险慎用）
		修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
		vim /etc/mysql/mysql.conf.d/mysqld.cnf
			注释掉 bind-address = 127.0.0.1
		然后重启msyql
		service mysql restart
		在另外一台Ubuntu中进行连接测试
			如果依然连不上，可能原因
			1) 网络不通
			通过 ping xxx.xxx.xx.xxx可以发现网络是否正常
			2)查看数据库是否配置了bind_address参数
			注释掉
			3)查看数据库是否设置了skip_networking参数
			如果设置了该参数，那么只能本地登录mysql数据库
			4)端口指定是否正确

	3.4. 删除账户
		语法1：使用root登录------推荐
		drop user '用户名'@'主机';
		drop user 'laowang'@'%';
		语法2：使用root登录，删除mysql数据库的user表中数据
		delete from user where user='用户名';
		delete from user where user='laowang';
		操作结束之后需要刷新权限
		flush privileges;

五、MySQL主从同步配置

1. 主从同步的定义
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数
据时，一个服务器充当主服务器（master），其余的服务器充当从服务器
（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务
器，从服务器甚止可以通过拨号断断续续地连接主服务器。通过配置文件，
可以指定复制所有的数据库，某个数据库，甚止是某个数据库上的某个表。
使⽤主从同步的好处：
通过增加从服务器来提供数据库的性能，在主服务器上执行写度和更
新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，
从而调整整个数据库的性能。
提供数据安全，因为数据已复制到从服务器，从服务器可以终⽌复制进
程，所以，可以在从服务器上备份而不破坏主服务器相应数据
在主服务器上完成实时数据，在从服务器上分析这些数据，从而提高
主服务器的性能

2. 主从同步的机制
Mysql服务器之间的主从同步是基于二进制机制，主服务器使⽤⼆进制⽇
志来记录数据库的变动情况，从服务器通过读取和执⾏该⽇志⽂件来保持和
主服务器的数据⼀致。
在使⽤⼆进制⽇志时，主服务器的所有操作都会被记录下来，然后从服务器
会接收到该⽇志的⼀个副本。从服务器可以指定执⾏该⽇志中的哪⼀类事件
（譬如只插⼊数据或者只更新数据），默认会执⾏⽇志中的所有语句。
每⼀个从服务器会记录关于⼆进制⽇志的信息：⽂件名和已经处理过的语
句，这样意味着不同的从服务器可以分别执⾏同⼀个⼆进制⽇志的不同部
分，并且从服务器可以随时连接或者中断和服务器的连接。
主服务器和每⼀个从服务器都必须配置⼀个唯⼀的ID号（在my.cnf⽂件的
[mysqld]模块下有⼀个server-id配置项），另外，每⼀个从服务器还需要通过
CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，⽇志⽂件
名称和该⽇志⾥⾯的位置（这些信息存储在主服务器的数据库⾥）

3. 配置主从同步的基本步骤
有很多种配置主从同步的⽅法，可以总结为如下的步骤：
1. 在主服务器上，必须开启⼆进制⽇志机制和配置⼀个独⽴的ID
2. 在每⼀个从服务器上，配置⼀个唯⼀的ID，创建⼀个⽤来专⻔复制主服
务器数据的账号
3. 在开始复制进程前，在主服务器上记录⼆进制⽂件的位置信息
4. 如果在开始复制之前，数据库中已经有数据，就必须先创建⼀个数据快
照（可以使⽤mysqldump导出数据库，或者直接复制数据⽂件）
5. 配置从服务器要连接的主服务器的IP地址和登陆授权，⼆进制⽇志⽂件
名和位置

4. 详细配置主从同步的⽅法
主和从的身份可以⾃⼰指定，我们将虚拟机Ubuntu中MySQL作为主服务器，
将Windows中的MySQL作为从服务器。 在主从设置前，要保证Ubuntu与
Windows间的⽹络连通。

	步骤：
	1.从主服务器进行数据备份
		备份命令
		mysqldump -uroot -pmysql --all-databases --lock-all-tables > /home/python/master_db.sql

	2.从库与主库保持数据一致
		进入从库mysql数据库文件夹
		cd /var/lib/mysql
		备份数据库
		mysql -uroot -pmysql < ./master_db.sql

	3.配置主库二进制日志文件
		管理员权限修改
		sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
		去掉注释
		server-id = 1
		log_bin = /var/log/mysql/myysql-bin.log
		
	4.重启主库mysql服务
		sudo service mysql restart

		
	5.登录主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号：
		mysql -uroot -pmysql
		grant replication slave on *.* to 'aaaa'@'%' identified by '123456';

	6.刷新权限：
		flush privileges;

	7.获取主服务器的⼆进制日志信息
		show master status;
		+------------------+----------+
		| File             | Position | 
		+------------------+----------+
		| mysql-bin.000005 |    3286  | 
		+------------------+----------+
		File为使用的日志文件名字
		Position为使用的日件位置， 这两个参数须记下， 配置从服务器时会用到

	8.进入从库mysql，设置连接到master主服务器
		change master to master_host='192.168.31.90',master_user='aaaa',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=3286;

		master_host： 主服务器Ubuntu的ip地址
		master_log_file: 前面查询到的主服务器日志文件名
		master_log_pos: 前面查询到的主服务器日志文件位置
		
	9.开启同步，检查同步状态
		start slave;
		show slave status \G;
			当以下都是yes时表示已经成功同步。
			Slave_IO_Running:Yes
			Slave_SQl_Running:Yes
			
		从库登录主库，查看是否可登录				
		mysql -h192.168.31.90 -uaaaa -p123456				
		克隆的ubunto做主从，要把数据库mysql文件夹中的auto.cnf配置文件删除。