MySQL高级 视图/引擎/事务/索引/账户管理/主从

MySQL 高级

测试数据：现有数据库test，包含三张表order，goods，goods2


视图
视图是是一张虚表，是一条SELECT语句返回的结果集，是对若干张基本表的引用。

• 创建视图，命名建议以 v_ 开头

  create view 视图名称 as select 语句；

   eg.  create view v_ognames as select orderid,total,gname,gname2 from `order`,goods,goods2 where `order`.gid = goods.goodid and `order`.gid2 = goods2.good_id2;
  
   show tables;
  

  
  v_ognames 即为我们创建的视图，是一张虚表，查看这张表

   select * from v_ognames;
  

  

• 删除视图
  drop view 视图名称；

   eg. drop view v_ognames;
  

  

引擎
查看引擎支持：

show engines；

• MyISAM：不支持事务、不支持外键；访问速度快，擅长插入及查询
• InnoDB：事务型存储引擎，行级锁，外键约束，自动增加，自动灾难恢复，多重并发更新
• MEMORY：数据存储在内存，超级快；断电数据丢失
• MERGE:MyISAM表集合，作为相同结构的MyISAM表的聚合器，聚合之后利于多表查询
• ARCHIVE：归档，只支持插入和查询，支持索引，用作仓库

创建使用其他引擎的表
CREATE TABLE 表名（字段1 约束1，字段2 约束2，…）engine=引擎名；

eg. create table users2(id smallint unsigned not null auto_increment,username varchar(15) not null,pwd varchar(15) not null,index using hash (username),primary key (id))engine=myisam;

事务 – 指一个操作序列，要么都执行，要么都不执行，该序列是不可分的单位
广泛应用与订单系统，银行系统等场景

四大特性（ACID）：

• 原子性：不可分割的执行单位
• 一致性：一个一致性的状态转换为另一个一致性的状态
• 隔离性：一个事务在工作时，对其他事务隔离
• 持久性：事务提交，结果永久保存到数据库。

用法：

START TRANSACTION; 或 BEGIN;
SQL1;
SQL2;
SQL3;
COMMIT; 或ROLLBACK; (事务失败，自动回滚 rollback)

索引
索引是一种特殊的文件（InnoDB表中，索引是其的一部分），是对数据表中记录的引用指针。
原理：B-tree算法

• 查看索引

  show index from 表名;
  eg. show index from order;
  

• 创建索引

   create index v_索引名称 on 表名（字段1（长度），字段2，字段3，......）;
   eg.  create index v_id on `order`(orderid);
  

• 删除索引

  drop index 索引名称 on 表名；
  eg. drop index v_id on jingjia_info;

账户管理
根据MySQL账户所具有的权限不同，分为

• 服务实例级账户：相当于root账户，可删除所有的库、表
• 数据库级别账户：对特定数据库执行增删改的所有操作
• 数据表级别账号：对特定表执行增删改等所有操作
• 字段级别账号：对某些特定字段进行增删改
• 存储程序级别账号：对存储程序进行增删改查的操作

账户权限分配：（需root用户进行登录操作）

• 查看所有用户

   # 登录root用户
   mysql -uroot -p密码
   # 切换到同名的mysql数据库
   use mysql;
   # 查看mysql数据库中有哪些表
   show tables；
   # 查看user表结构
   desc user;
   # 查询用户host，user，password
   select host,user,password from user;
  

• 创建用户、授权

   grant 权限列表 on 数据库 to ‘用户名‘@’访问主机’ identified by ‘密码’；
   eg.  grant create,alter,insert,update,select on test.* to 'nodel'@'localhost' identified by 'nodel';
   # 通常：test.\* 访问库下所有表
   # localhost换为ip则只有指定ip才可访问，换为%则任意ip都可访问
  

• 查看用户的权限

   show grants for nodel@localhost;
  

• 修改权限

   grant 权限名称 on 数据库 to 账户@主机 with grant option;
   eg.  grant select on test.* to nodel@localhost with grant option;
  

主从
使得一个数据库服务器可以复制到其他服务器上。主从之间异步连接。
好处：

• 增加服务器数量，提高性能，可以读写分离
• 提高数据安全

实现原理：基于二进制日志，主服务器通过二进制日志来记录，从服务器获取日志来追踪同步。日志相当于一个消息队列。