MySQL之四大引擎、建库建表以及账号管理

1.四大引擎

1.1.存储引擎查看

1.1.1.命令

SHOW ENGINES

1.1.2.support字段说明

1、default的为默认的引擎
2、为YES表示可以使用
3、为NO表示不能使用

1.2.InnoDB

1.2.1.InnoDB的使用场景

一般事务性，均使用该引擎，用途最广，如果把握不准使用何种引擎，就使用该引擎。

1.2.2.InnoDB的特点

1、修改快，支持事务——行锁
2、存储限制：64TG
3、事务支持：支持事务

1.3.MyISAM

1.3.1.MyISAM的使用场景

大量查询，很少修改的场景

1.3.2.MyISAM的特点

1、强调了快速读取操作——表锁
2、存储限制：256TG
3、事务支持：不支持事务

1.4.MEMORY

1.4.1.MEMORY的使用场景

由于易失性，可以用于存储在分析中产生的中间表

1.4.2.MEMORY的特点

1、所有的数据都保存在内存中，一旦服务器重启，所有memory存储引擎的表数据会消失但是表结构会保存下来。
2、存储限制：取决于RAM（随机存储器）
3、事务支持：不支持事务

1.5.Archive

1.5.1.Archive的使用场景

在日志和数据采集的时候可以使用

1.5.2.Archive的特点

1、只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增ID上建立索引。
2、archive表比MylSAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
3、不支持索引（自增ID列除外）

2.数据库管理

2.1.元数据库简介

记录MySQL自身数据的数据库

2.2.元数据库分类

2.2.1.information_schema

信息数据库，保存mysql所维护的其他数据库信息。例如：数据库名，数据库的表，表栏的数据类型与访问权限等。

2.2.2.mysql

核心数据库，主要负责存储数据库的用户、权限设置、关键字等。mysql自己需要使用的控制和管理信息。

2.2.3.performance_schema

用于mysql的监控数据的存放。

2.3.相关操作

2.3.1.数据库的增删改查及使用

1、use 数据库名
2、create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;
3、show databases
4、drop database 数据库名

2.4.MySQL库

2.4.1.权限相关

1、user表（用户层权限）——相当于his、oa、wuliu
2、db表（数据库层权限）——相当于患者记录表、门诊卡、看病流程表…
3、tables_priv表（表层权限）——相当于增加、删除、修改、查询、创表、删除表…
4、columns_priv表（字段层权限）——相当于患者id、患者姓名、患者年龄…

3.数据表管理

3.1.三大范式

1、第一范式：列不可再分（原子性）——例如：xx省xx市xx区xx栋xx单元xxx
2、第二范式：主键约束——例如：订单项表设计：id、单价 数量、小计
3、第三范式：外键约束——例如：患者id、患者姓名同时存在于一张关联表中

3.2.基本数据类型

3.2.1.优化原则

3.2.1.1.更小通常更好

1、更小通常更快
2、更小的磁盘空间、内存、CPU缓存
3、更少的CPU周期

3.2.1.2.简单就好

1、整型比字符串操作代价更小
2、用时间类型表示日期（date、datetime等）而不用字符串

3.2.1.3.尽量避免NULL

1、NULL为列的默认值，但除非确实需要，应尽量避免使用NULL
2、尽量指定列为NOT NULL，特别是需要建索引的列
3、查询中包括有NULL的类，MySQL更难优化，例如：
3.1、不利于使用索引
3.2、索引统计更复杂
3.3、值比较更复杂
4、用整型值而不是字符串表示IP地址，例如
4.1、INET_ATON()
4.2、INET_NTOA()

3.2.2.分类

3.2.2.1.整数

1、tinyint 8位（-128~127）
2、smallint 16位（-32768~32767）
3、mediumint 24位（-8388608~8388607）
4、int 32位 大约正负21亿
5、bigint 64位

3.2.2.2.实数（带有小数点）

1、float 4个字节
2、double 8个字节
3、decimal 最多允许65个数字——实例：decimal（5，2）；说明：5位长度，2位小数，否则报错；如果只是小数部分超出2位，则四舍五入

3.2.2.3.字符串

1、char
1.1、char的定长，MySQL根据定义字符串的长度依次分配足够的空间。
1.2、char的使用场景：较短的字符串，且所有制接近同一长度。
2、varchar
2.1、比定长类型节约空间。
2.2、varchar的适用场景：字符串的最长长度比评估长度大很多，列的更新较少。
2.3、varchar的缺点：频繁修改，且字符串的长度变化大时，可能出现页分裂。

3.2.2.4.text&blob

1、text存储字符数据
1.1、tinytext
1.2、smalltext
1.3、mediumtext
1.4、text
1.5、longtext
2、blob存储二进制数据
2.1、tinyblob
2.2、smallblob
2.3、mediumblob
2.4、blob

3.2.2.5.日期时间

1、datetime
1.1、精度:秒
1.2、与时区无关，8个字节存储空间
1.3、范围：1001至9999年
2、timestamp
2.1、保存1970年1月1日午夜以来的秒数
2.2、占用4个字节存储空间
2.3、范围：1970年至2038年
2.4、与时区有关
2.5、默认位NOT NULL
2.6、通常尽量使用timestamp
2.7、精度：秒
3、date——yyyy-MM-dd
4、time——HH:mm:ss

3.2.2.6.选择标识符

1、用来进行关联操作
2、在其他表中作为外键
3、整型通常时标识列的最好选择
4、相关的表中使用相同的数据类型
5、尽量避免字符串作为标识列，尤其时随机生成的字符串，（如：UUID）导致insert与select都很慢
5.1、插入值被随机写到索引的不同位置，insert慢，容易导致页分裂，磁盘随机读取
5.2、逻辑上相邻的行被分布在磁盘和内存的不同地方，select慢
5.3、是MySQL上查询缓存失效
5.4、如果需要存储UUID，则应将"-"去除

4.账号管理

4.1.相同

4.1.1.查询用户

SELECT * from user

4.1.2.查看用户常用信息

select host,user from user

4.1.3.删除用户（慎用）

#命令：drop user 用户表;#drop user ls;

4.1.4.设置权限（Grant）

1、grant SELECT on db_xiaoli.t_p1_user to zs@‘%’ ; #给zs用户 赋予 数据库db_xiaoli中的表t_p1_user查询权限
2、revoke all on db_xiaoli t_p1_user from zs@‘%’; #可以回收GRANT SELECT UPDATE ON
3、grant UPDATE on db_xiaoli.t_p1_user to zs@‘%’ ; #给 zs用户赋予修改权限
4、grant SELECT on db_xiaoli * to zs@‘%’ ; #给zs用户 赋予所有表的查询权限
5、grant ALL on db_xiaoli * to zs@‘%’; #给zs用户赋予 数据库db_xiaoli中所有表所有权限

4.1.5.撤销权限（Revoke）

1、revoke DELETE on db_xiaoli t_p1_user from zs@‘%’; #啥也不能回收，不会对GRANT ALL PRIVILEGES ON ‘db_xiaoli’ * TO ‘zs’@‘%‘有任
2、revoke all on db_xiaoli * from zs@’%’; #可以回收GRANT ALL PRIVILEGES ON ‘db_xiaoli’ * TO ‘zx’@'%'这条赋权语句

4.1.6.查看用户权限

#命令：show grants for 用户名； show grants for ‘zs’@‘%’;

4.2.不相同

4.2.1.5.7版本

4.2.1.1.创建用户并设置登录密码

1、#命令：create user 用户名 identified by ‘密码’;
2、#注：identified by会将纯文本密码加密作为散列值存储
3、create user is identified by ‘123456’;

4.2.1.2.修改用户密码

1、#命令：set password for 用户名=password（‘新密码’）
2、set password for zs=password(‘123456’);
3、flush privileges;

4.2.2.8.0版本

4.2.2.1.创建用户并设置登录密码

1、#用户名密码创建需要分开
2、#命令：create user 用户名;
3、create user ls;

4.2.2.2.修改用户密码

1、#ALTER USER 用户 IDENTIFIED WITH mysql_password BY ‘密码’;
2、ALTER USER ‘ls’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘123456’
3、flush privileges;

5.思维导图