数据库（存储 引擎、管理、表管理、账号管理）实施必看！！！

一、数据库存储引擎

1.1主要学习：(InnoDB、MEMORY、MylSAM、ARCHIVE)

通过：SHOW ENGINES去查看引擎

主要学习这四个

support字段说明
default的为默认的引擎
为YES表示可以使用
为NO表示不能使用

1.2InnoDB

• 使用场景：一般事务性，均使用该引擎，用途广，如果把握不准使用何种引擎，就使用该引擎
• 特点：
  1.修改快，支持事务–行锁
  2.存储限制：64TG
  3.事务支持：支持事务

1.3MylSAM

• 使用场景：大量查询，很少修改的场景
• 特点：
  1.强调了快速读取操作–表锁
  2.储存限制：256TG
  3.事务支持：不支持事务

1.4MEMORY

• 使用场景：由于易失性，可以用于存储在分析中产生的中间表
• 特点：
  1.所有的数据都保存在内存中，一旦服务器重启，所有memory存储引擎的表数据会消失但是表结构会保存下来
  2.存储限制：取决于RAM(随机存储器)
  3.事务支持：不支持事务

1.5Archive

• 使用场景：在日志和数据采集的时候可以使用
• 特点：
  1.只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入
  2.archive表比MylSAM表要小大约75%，比支持事务处理的innoDB表小大约83%
  3.不支持索引(自增ID列除外)

二、数据库管理

2.1元数据简介:

• 记录MySQL自身数据的数据库
  建数据库的时候自带的三个数据库
  

2.2元数据库的分类

• information_schema:信息数据库，保存mysql所维护的其他数据库信息。例如：数据库名、数据库的表、表栏的数据类型与访问权限等
• mysql:核心数据库，主要负责存储数库的用户、权限设置、关键字等。mysql自己需要使用的控制和管理信息
• performance_schema：用于mysql的监控数据的存放

2.3相关操作：数据库的增删改查及使用

• 使用哪个数据库：user数据库名
• 创建数据库(没有才创建完整写法)：create database if not exists 数据库名 default utf8_general_ci–设置字符集编码以及不区分大小写
• 查询所有数据库：show database
• 删除数据库:drop database 数据库名

2.4MySQL库：权限相关

1、user表(用户层权限)
用户表决定用户拥有哪些数据库的权限
2、db表(数据库层权限)
这个用户对于该数据库哪些表有操作权限
3、tables_priv表(表层权限)
决定这个用户对于哪些表拥有增删改查权限
4、columns_priv表(字段层权限)
决定了这个用户针对于某一张表的哪些字段拥有操作权限

三、数据库表管理

3.1三大范式：

建表的时候要遵循三大范式

• 第一范式：列不可分(原子性)
  例如：湖南长沙市岳麓区浪琴湾清水21栋
• 第二范式：主键约束
  例如：订单项表设计：id、单价数量、小计
• 第三范式：外键约束
  例如：患者id、患者姓名同时存在于一张关联表中

3.2基本数据类型

• 优化原则：
  1.更小通常更好：
  更小通常更快
  更小的磁盘空间、内存、CPU缓存
  更小的CPU周期
  2.简单就好：
  整形比字符串操作代价更小
  用时间类型表示日期(date、datetime等)而不用字符串
  3.尽量避免NULL：
  NULL为列的默认值，但除非确实需要，应尽量避免使用NULL
  尽量指定列为NOT NULL,特别是需要建索引的列
  查询中包括有NULL的类，MySQL更难优化：不利于使用索引；索引统计更复杂；值比较更复杂
  用整形值而不是字符串表示IP地址
• 值类型：
  1.整数：tinyint 8位、smallint 16位、mediumint 24位、int 32位、bigint 64位
  2.实数（带有小数点）：float 4个字节、double 8个字节、decimal 最多允许65个数字
  3.字符串：
  char：定长、MySQL根据定义字符串的长度一次分配足够的空间
  使用场景：较短的字符串，且所有的值接近同一长度
  varchar:比定长类型节约空间
  使用场景：字符串的最长长度比评估长度大很多，列的更新较少
  缺点：频繁修改，且字符串的长度变化大时，可能出现页分裂
• 用的少:
  4.text&blob:
  都为存放很大的数据而设计
  与其他数据不同，都作为独立的对象存储
  当值太大时，使用外部存储区储存
  text存储字符数据：tinytext、smalltext、mediumtext、text、longtext
  blob存储二进制数据：tinyblob、small blob、mediumblob、blob、longblob
  5.日期时间：
  datetime:
  精度：秒
  与时区无关，8个字节存储空间
  范围：1001至9999年
  timestamp:
  保存1970年1月1日午夜以来的秒数
  占用4个字节存储空间
  范围：1970年至2038年
  与时区有关
  默认为NOT NULL
  通常尽量使用timestamp
  精度：秒

data:
yyyy-MM-dd
time:
HH:mm:ss

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

四、数据库账号管理

4.1查询用户：SELECT * FROM USER

4.2查用户的信息

select host,user from user

4.3删除用户（慎用）

drop user 用户名；

4.4权限操作

①设置权限
语法：grant privileges on databasename.tablename to username@'host'
#给zs用户 赋予 数据库db_aa中的表t_user查询权限
grant SELECT on db_aa.t_user to zs@'%'
#给zs用户赋予数据库db_aa中的表t_user修改权限
grant UPDATE on db_aa.t_user to zs@'%'
#给zs用户赋予数据库db_aa中所有表查询权限
grant SELECT on db_aa.*to zs@'%'
#给zs用户赋予数据库db_aa中所有表 所有权限
grant ALL on db_aa.* to zs@'%'
②撤销权限
语法：revoke privileges on databasename.tablename from username@'host'
#啥也不能回收，不会对GRANT ALL PPIVILEGES ON db_aa.* To zs@‘%‘有任何影响
revoke DELETE on db_aa.t_user from zs@'%'
#可以回收GRANT SELECT,UPDATE ON
‘db_aa’,‘t_user’ To ‘zs’@’’’%‘这条权限语句
revoke all on db_aa.t_user from zs@'%'
注：revoke只能回收grants列表中更小的权限
③查看用户权限
#命令：show grants for 用户名
show grants for 'zs'@'%'

- 思维导图：