视图、事务、索引

视图

• 通俗的讲，视图就是一条SELECT语句执行后返回的结果集（试图是对若干张基本表的引用，一张虚表，查询语句执行的结果；基表改变，视图也改变；可以理解为镜像）

# 定义视图建议以v_开头
create view 试图名称 as select语句；
# 使用试图（视图的用途就是查询）
   select * from v_stu_score;
# 删除试图
   drop view 试图名称；
   例：drop ciew v_stu_sco;

视图的作用

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

事务

• 所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

事务的四大特征（简称ACID）

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

事务命令

• 表的引擎类型必须是innodb类型才可以看到使用事务，这是mysql表的默认引擎

# 查看表的创建语句可以看到
   - engine = innodb
-- 选择数据库
   - use jing_dong;
-- 查看goods 表
   - show create table goods;
# 开启事务命令
   - 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
   - begin 或者 start transaction;
# 提交事务命令 
   - 将缓存中的数据变更维护到物理表中
   - commit
# 回滚事务命令
   - 放弃缓存中变更的数据
   - rollback

注意：

• 修改数据的命令会自动触发事务，包括insert、 update 、 delete
• 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起回滚到之前数据

索引

• 索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好不比是一本书前面的目录，能加快数据库的查询数据

• 目的：在于提高查询效率

• 原理：通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据

索引的使用

# 查看索引
   - show index from 表名
# 创建索引
   - 如果指定字段是字符串，需要指定长度，建议长度与定义字段的长度一致
   - 字段类型如果不是自妇产么可以不填写长度部分
   - create index 索引名称 on 表名（字段名称（长度））
# 删除索引
   - drop index 索引名称 on 表名；

• 注意：建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件；建立索引会占用磁盘空间

账户管理

• 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud

MySQL账户体系

• 服务实例级帐号：启动了一个mysqld,即为一个数据库实例；如果某用户如root，拥有服务实例级分配权限，那么该帐号就可以删除所有的数据库、连同这些库中的表
• 数据库级别帐号：对特定数据库执行增删改查的所有操作
• 数据表级别帐号：对特定表执行增删该查等所有操作
• 字段级别的权限：对某些表特定字段进行操作
• 存储程序级别的帐号：对存储程序进行增删该茶的操作

账户的操作

• 主要包括创建账户、删除账户、修改密码、授权权限等

# 授权权限
   - 查看所有用户(所有用户及权限信息存储在mysql数据库的user表中)
      - select host,user,authentication_string from user;
   - 主要字段说明
      - Host 表示允许访问的主机
      - User表示用户名
      - authentication_string白哦是密码，为加密后的值
   - 创建账户、授权
      - 需要使用实例级账户登录后操作，以root为例
      - 常用权限主要包括：create、alter、drop、insert、update、delete、select
      - 如果分配所有权限，可以使用all privileges
      - grant 权限列表 on 数据库 to "用户名"@"访问主机" identified by "密码";
   - 修改密码
      - 使用password()函数惊醒密码加密
      - update user set authentication_string=password("新密码") where user = "用户名";
      - 例：update user set authentication_string=password("123") where user = "localhost";
      - 注意修改完密码后需要刷新权限
      -  刷新权限：flush pricileges       
   - 删除用户
      # 语法1：使用root 登录
      - drop user "用户名"@"主机";
      - 例： drop user "laowang"@"%";
      # 语法2： 使用root 登录，删除mysql数据库的user表中数据
      - delete from user where user= "用户名";
      - 例：delete from user where user = "laowang";
      # 操作结束后需要刷新权限
      - flush privileges
# 修改权限
   - grant 权限名称 on 数据库 to 账户@主机 with grant option;
# 远程登录（危险慎用）
   - 修改 /ect/mysql/mysql.conf.d/mysqld,cnf文件
   - 然后重启mysql
      - service mysql restart
   - 连不上原因
      - 网络不通
      - 查看数据库是否配置了bind_address参数
      - 查看数据库是否设置了skip_networking参数
      - 端口指定是否正确

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

MySQL主从同步配置

主从同步定义

• 主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
• 主从同步的好处

- 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态调整从服务器的数量，从而调整整个数据库的性能
- 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份不破坏主服务器响应数据
- 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

主从同步机制

• MySQL服务器之间的主从同步是基于二进制日志机制，主服务器只用二进制日志来记录数据库的变动情况，从服务器通过读取和执行日志文件来保持和主服务器的数据一致
• 主服务器和每个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项）

配置主从服务器的步骤

1.在主服务器上，必须开启二进制日志机制和配置一个独立的ID
2.在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的帐号
3.在开始复制进程前，在主服务器上记录二进制文件的位置信息
4.如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
5.配置从服务器要连接的主服务器的IP地址和登录授权，二进制日志文件名和位置

详细配置主从同步的方法

1.备份主服务器原有数据到从服务器

# 在数服务器Ubuntu上备份，执行命令
mysqldump -uroot -pmysql
--all-databases
--lock-all-tables > ~/master_db.sql
# 说明
* - u:用户名
* - p:密码
* --all-databases: 导出所有数据库
* --lock-all-tables: 执行操作时锁定所有表，防止操作时有数据修改
* ~/master_db.sql: 导出的备份数据（sql文件）位置，可以自己指定
# 在从服务器Windows上进行数据还原
mysql -uroot -pmysql < master_db.sql

2.配置主服务器master(Ubuntu中的MySQL)

# 编辑设置mysqld的配置文件，设置log_bin和server-id
sudo vim  /etc/mysql/mysql.conf.d/mysqld.cnf
# 重启mysql服务器
sudo service mysql restart
# 登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号
mysql - uroot -pmysql
GRANT REPLICAION SLAVE NO *.* TO "slave"@"%" identified by "slave";
FLUSH PRIVLEGES;
# 获取主服务器的二进制日志信息
SHOW MASTER STATUS;

3.配置从服务器slave(Windows中的MySQL)

# 找到Windows 中的MySQL的配置文件
# 编辑my.ini文件，将server-id修改为2,并保存退出
# 打开windows服务管理器
# 在打开的服务管理中找到MySQL57,并重启该服务

进入windows的mysql,社会资连接到master主服务器

change master to master_host= "10.211.55.5", master_user= "slave", master_password= "slave", master_log_file = "mysql-bin.000006", master_log_pos=590;
* mastr_host: 主服务器Ubuntu的ip地址
* master_log_file:前面查询到的主服务器日志文件名
* master_log_pos: 前面查询到的主服务器日志文件位置