[MySQL] 零基础学MySQL10

本博客是【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门视频的学习笔记

MySQL表类型和存储引擎

基本介绍

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

图片来自【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门

这里重点介绍三种：MyISAM、InnoDB、Memory

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

三种存储引擎表使用案例

#innoDB存储引擎是前面一直使用过的，不再多说
#支持事务，支持外键，支持行级锁

#myisam存储引擎
#1.添加速度快2.不支持外键和事务3.表级锁
CREATE TABLE t28(
	id INT,
	`name` VARCHAR(32)) ENGINE MYISAM;
START TRANSACTION;
SAVEPOINT t1;
INSERT INTO t28 VALUES(1,'jack');
SELECT * FROM t28;
ROLLBACK TO t1;
#查一下表，发现根本没回滚成功
SELECT * FROM t28;

#memory存储引擎
#1.数据存储在内存中(关闭了mysql服务，数据就丢失了，表结构还在)
#2.执行速度很快（没有IO读写）3.默认支持索引(hash表)
CREATE TABLE t29(
	id INT,
	`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t29 VALUES(1,'tom'),(2,'jack'),(3,'lhq');
SELECT * FROM t29;

如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快
2. 如果需要支持事务，选择InnoDB
3. Memory存储引擎就是将数据存储在内存中，由于没有I/O的等待，速度极快。但是由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态）

如果一个用户的状态发生变化，我们就去修改user表，而用户的状态是频繁变化，操作就会非常频繁。我们把用户的在线状态用memory存储引擎存放。用户的在线状态不是很重要的数据，没了就没了。

修改存储引擎

ALTER TABLE `表名` ENGINE=存储引擎;

视图(view)

看一个需求

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

其中一个方案就是视图。

基本概念

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

图片来自【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门

视图的基本使用

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;

#查看创建视图的指令
SHOW CREATE VIEW emp_view01;
#删除视图
DROP VIEW emp_view01;

视图细节讨论

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

#修改视图会影响到基表
UPDATE emp_view01
	SET job='manager'
	WHERE empno=7369;
#查询基表
SELECT * FROM emp;

修改基表会影响视图，这是毋庸置疑的。

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

#视图中可以再使用视图
#比如从emp_view01视图中选出empno和ename做出新的视图
CREATE VIEW emp_view02
	AS SELECT empno,ename FROM emp_view01;
SELECT * FROM emp_view02;
#emp_view02还是和基表emp有映射关系

视图应用实例

视图最佳实践

1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接JOIN。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

练习

针对emp，dept和salgrade三张表，创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别。
即使用三张表构建一个视图

/*
分析：使用三表联合查询得到结果
*/
SELECT empno,ename,dname,grade
	FROM emp,dept,salgrade
	WHERE emp.deptno=dept.deptno AND 
	(sal BETWEEN losal AND hisal);
#将得到的结果构建成视图
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.1.100
2. user：用户名
3. authentication_string：密码，是通过mysql的password()函数加密之后的密码

创建用户

create user ‘用户名’@‘允许登录位置’ identified by ‘密码’;

删除用户

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

#mysql用户的管理
#原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限
#所以mysql数据库管理人员(root)，根据需要创建不同的用户，赋予相应的权限供开发人员使用
#1.创建新的用户
#（1）'lhq_edu' @ 'localhost' 表示用户的完整信息 'lhq_edu'用户名 'localhost'登录的ip
#（2）123456 密码 但是存放到mysql.user表时，是password('123456')加密后的密码
CREATE USER 'lhq_edu'@'localhost' IDENTIFIED BY '123456';
SELECT `host`,`user`,`authentication_string`
	FROM mysql.user;
#2.删除用户
DROP USER 'lhq_edu'@'localhost';

图片来自【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门

用户修改密码

修改自己的密码：set password=password(‘密码’);
修改他人的密码（需要有修改用户密码的权限）：set password for ‘用户名’@‘登录位置’=password(‘密码’);

MySQL权限管理

给用户授权

基本语法

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

说明：

1. 权限列表，多个权限用逗号分开
   grant select on…
   grant select,delete,create on…
   grant all on… //表示赋予该用户在该对象上的所有权限
2. 特别说明
   *.*：代表本系统中所有数据库的所有数据对象（表，视图，存储过程等）
   库.*：表示某个数据库中的所有数据对象（表，视图，存储过程等）
3. identified by可以省略，也可以写出
   如果用户存在，就是修改该用户的密码
   如果该用户不存在，就是创建该用户

回收用户权限

基本语法

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

权限生效指令

#如果权限没有生效，可以执行下面命令
FLUSH PRIVILEGES;

练习

1. 创建一个用户（名字拼音），密码为123，并且只可以从本地登录，不让远程登录mysql
2. 使用root用户创建库和表[testdb下的news表]
3. 给用户分配查看news表和添加数据的权限
4. 测试看看用户是否只有这几个权限
5. 使用root用户修改密码为abc
6. 重新登录
7. 回收权限
8. 使用root用户删除你的用户

#root用户
#创建用户
CREATE USER 'bigdog'@'localhost' IDENTIFIED BY '123';
#创建测试数据库和测试表
CREATE DATABASE testdb;
CREATE TABLE news(
	id INT,
	content VARCHAR(32));
INSERT INTO news VALUES(100,'fake news');
SELECT * FROM news;
#授予权限
GRANT SELECT,INSERT ON testdb.news TO 'bigdog'@'localhost';
#修改密码
SET PASSWORD FOR 'bigdog'@'localhost' =PASSWORD('abc'); 
#回收权限
REVOKE SELECT,INSERT ON testdb.news FROM 'bigdog'@'localhost';
#删除用户
DROP USER 'bigdog'@'localhost';

#创建的用户
#可以查看
SELECT * FROM news;
#可以添加
INSERT INTO news VALUES(200,'true news');
#不能修改
UPDATE news SET content='abcd' WHERE id=100;
#UPDATE command denied to user 'bigdog'@'localhost' for table 'news'

细节说明

1. 在创建用户的时候，如果不指定host，则为%，%表示所有ip都有连接权限
   create user xxx;
2. 也可以这样指定
   create user ‘xxx’@‘192.168.1.%’ 表示xxx用户在192.168.1.*的ip可以登录mysql
3. 在删除用户的时候，如果host不是%，需要明确指定’用户’@‘host值’