MySQL攻略 - 表类型和存储引擎，视图，与Mysql用户管理综合细节案例展示

mysql表类型和存储引擎

基本介绍

1. MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB
3. 这六种又分为两类，一类是“事务安全性”（transaction-safe），比如：InnoDB；其余都属于第二类，称为“非事务安全型”（non-transaction-safe）【mysiam 和 memory】。

查看所有存储引擎指令

show engines

存储引擎/表类型特点（表格）

特点	Myisam	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

细节说明

重点介绍三种：MyISAM、InnoDB、MEMORY

1. MyISAM不支持事务、不也支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。

演示MYISAM存储引擎

-- 表类型和存储引擎

-- 查看所有的存储引擎
show engines

-- innodb 存储引擎，是前面使用过。
-- 1.支持事务 2.支持外键 3.支持行级锁

-- myisam 存储引擎
create table t2(
			id int,
			`name` varchar(32)) engine MYISAM		-- 设置引擎
			
-- 1.添加速度快 2不支持外键和事务 3.支持表级锁

start transaction;
savepoint t1;
insert into t2 values(1,'jack');
select * from t2;
rollback;
select * from t2;		-- 不支持事务回滚

演示MEMORY存储引擎

-- memory 存储引擎
-- 1.数据存储在内存中[关闭了mysql服务，数据丢失，但是表结构还在] 
-- 2.执行速度很快(没有IO读写) 3.默认支持索引

create table t3(
		id int,
		`name` varchar(32)) engine memory

insert into t3
		values(1,'tom'),(2,'jack'),(3,'taotao');
select * from t3;

如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的crud操作，那么myisam是不二选择，速度快
2. 如果需要支持事务，选择innodb
3. memory存储引擎就是将数据存储在内存中，由于没有磁盘i/o的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失

修改存储引擎指令

alter table `表名` engine = 存储引擎;

alter table t3 engine = innodb;

MySQL视图（view）

思考引出视图

emp表的列信息很多，有些信息是个人重要信息（比如sal，mgr）如果我们希望某个用户只能查询emp表的（empno、ename）信息，有什么办法？

视图基本概念

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列,其数据来自对应的真实表(基表)
2. 视图可以来自多个表
3. 视图和基表（真实表）关系的示意图

视图、基表关系图（韩顺平）

视图的基本使用

1. create view 视图名 as select语句
2. alter view 视图名 as select 语句
3. show create view视图名
4. drop view 视图名1，视图名2

案例演示

 -- 视图的使用
-- 创建一个视图emp_view01,只能查询emp表的（empno、ename，job 和 deptno）信息
-- 创建视图
create view emp_view01
		as 
		select empno,ename,job,deptno from emp;
		
-- 查看视图
DESC emp_view01;

select * from emp_view01;
select empno from emp_view01;

-- 更新视图
alter view emp_view01
		as 
		select empno,ename from emp;
-- 查看视图
select * from emp_view01;

-- 查看创建视图的指令
show create view emp_view01;

-- 删除视图
drop view emp_view01;

细节

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm）
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert update delete】

create view myview as select empno,ename,job,comm from emp;
select * from myview;
update myview set comm = 200 where empno = 7369; //修改视图，对基表都有变化
update emp set comm = 100 where empno = 7369; //修改基表，对视频也有变化

3. 视图中可以再使用视图

-- 视图的使用
-- 创建一个视图emp_view01,只能查询emp表的（empno、ename，job 和 deptno）信息
-- 创建视图
create view emp_view01
		as 
		select empno,ename,job,deptno from emp;
		
-- 查看视图
DESC emp_view01;

-- 视图中可以再使用视图，比如从emp_view01中，选出empno，ename做出新视图
create view emp_view02
		as
		select empno,ename from emp_view01;
		
-- 查看视图
DESC emp_view02;

视图实践

安全

一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。

性能

关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。

灵活

如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

案例练习

-- 针对emp , dept，和salgrade张三表.创建一个视图emp_viewo3，可以显示雇员编号，雇员名，雇员部门名称和薪水级别
-- 方式一

create view emp_view03
			as
			select empno,ename,dname,grade
			from emp,dept,salgrade
			where emp.deptno = dept.deptno and (sal between losal and hisal)
			
select * from emp_view03;

MySQL用户管理

Mysql用户

mysql中的用户，都存储在系统数据库mysql中user表中

其中user表的重要字段说明：

1. host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.100
2. user： 用户名；
3. authentication_string：密码，是通过mysql的password（）函数加密之后的密码

用户管理语法

• 创建用户

create user `用户名` @ `允许登录位置` identified by `密码`;
-- 创建用户，同时指定密码

• 删除用户

drop user `用户名` @ `允许登录位置`;

案例演示

 -- mysql用户管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，付给他相应的mysql操作权限
-- 所以，mysql数据库管理人员（root），根据需要创建不同的用户，赋给相应的权限，供人员使用

-- 1.创建用户
-- 解读：（1）taotao_edu @ localhost表示用户的完整信息，taotao_edu用户名，localhost 登录的ip
-- （2）123456 密码，但是注意 存放到mysql.user表时，是password(`123456`)加密后的密码
create user 'taotao_edu'@'localhost' identified by '123456';

select * from 
			mysql.user;
			
-- 解析password加密
select password('123456');		-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

-- 2.删除用户
drop user 'taotao_edu'@'localost';

-- 3.登录
-- 重新连接用户可以看到
-- 不同的数据库用户，操作的库和表不相同

-- 3.修改密码
-- 修改自己的密码，没问题
set password = password('12345');

-- 修改其他人的密码，需要权限,但是，如果当前连接为root的话，则权限足够
set password for 'root'@'localhost' = password('12345');

MySQL权限（表格）

给用户权限

基本语法

grant 权限列表 on 库.对象名 to '用户名' @'登录位置' 【identified by '密码'】

说明：

1. 权限列表，多个权限用逗号分开

grant selet on......
grant select,delete,create on ......
grant all【privileges】 on ......   //表示赋予该用户在该对象上的所有权限

2. 特别说明

-- *.*：代表本系统中的数据库的所有对象（表，试图，存储过程）
grant 权限列表 on *.* to '用户名' @'登录位置'

-- 库.*:表示某个数据库中的所有数据对象（表，视图，存储过程等）
grant 权限列表 on 库.* to '用户名' @'登录位置'

-- 表示授予此用户所有权限
grant all on *.* to '用户名' @'登录位置'

3. identified by 可以省略，也可以写出

（1）如果用户存在，就是修改该用户的密码。

（2）如果该用户不存在，就是创建该用户！

回收用户权限

-- 基本语法：
revoke 权限列表 on 库.对象名 from '用户名'@ '登录位置';

权限生效指令

-- 如果权限没有生效，可以执行下面指令
-- 基本语法：
FLUSH privileges;

用户权限管理案例（练习）

-- 1，创建一个用户liuhongtao，密码123，并且只可以从本地登录，不让远程登录mysql
create user 'liuhongtao'@'localhost' identified by '12345';

-- 2.创建testdb数据库，并创建表news，要求:使用root用户创建
create database testdb;
create table news(
			id int,
			content varchar(32)
);

-- 添加一条测试数据
insert into news values(100,'北京新闻');

-- 3。给liuhongtao用户分配查看news 表和添加数据的权限
grant select , insert 
				on testdb.news
				to 'liuhongtao'@'localhost';
-- 此时liuhongtao用户可以看到testdb数据库，并可以查询添加数据
-- 增加updata权限
grant update
				on testdb.news
				to 'liuhongtao'@'localhost'

-- 4.修改liuhongtao密码为abc,要求:使用root用户完成
set password for 'liuhongtao'@'localhost' = password('abc');

-- 此时需要重新连接liuhongtao用户

-- 5.演示权限回收
-- 回收liuhongtao用户在 testdb.news 表的所有权限
-- 方式一
revoke select ,update,insert on testdb.news from 'liuhongtao'@'localhost';
-- 方式二
revoke all on testdb.news from 'liuhongtao'@'localhost';

-- 权限刷新（低版本无法适用时使用此命令）
flush privileges;

-- 6.使用root用户删除你的用户
drop user 'liuhongtao'@'localhost';

用户管理细节

1. 在创建用户的时候，如果不指定Host,则为%，%表示表示所有IP都有连接权限create user xxx;

2. 你也可以这样指定

create user 'xxx'@'192.168.1.%’表示 xxx用户在192.168.1.*的ip可以登录mysql

3. 在删除用户的时候,如果host不是%需要明确指定‘用户’@‘host值’

案例

-- 1.
create user jack;

select * from mysql.user;

-- 2.
create user 'smith'@'192.168.1.%';

select * from mysql.user;

-- 3.
drop user jack -- 默认就是drop user 'jack'@'%'

drop user 'smith'@'192.168.1.%'