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

目录

一.四大引擎

? ? ? ? 1.1 InnoDB

? ? ? ? 1.2 MyISAM

? ? ? ? 1.3 MEMORY

? ? ? ? 1.4 Archive

二.数据库管理

? ? ? ? 2.1 元数据库简介

? ? ? ? 2.2 元数据库分类

? ? ? ? 2.3 数据库的增删改查及使用

? ? ? ? 2.4 MySQL库权限相关

三.数据表管理

? ? ? ? 3.1 三大范式

? ? ? ? 3.2 基本数据类型

? ? ? ????????? 3.2.1 优化原则

? ? ? ? ? ? ? ? 3.2.2 分类

四.数据库账号管理

? ? ? ? 4.1 相同

???????? 4.1 不同

五.思维导图

---

一.四大引擎

• 存储引擎查看：
  • 命令：SHOW ENGINES
  • support字段说明：default为默认的引擎，为YES表示可以使用，为NO表示不能使用。

? ? ? ? 1.1 InnoDB

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

2.特点：

• 修改快，支持事务——行锁
• 存储限制：64TG
• 事务支持：支持事务

? ? ? ? 1.2 MyISAM

1.使用场景：大量查询，很少修改的场景。（数据字典，系统参数）

2.特点：

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

? ? ? ? 1.3 MEMORY

1.使用场景：由于易失性，也可用于存储在分析中产生的中间表。

2.特点：

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

? ? ? ? 1.4 Archive

1.使用场景：在日志和数据采集的时候可以使用。

2.特点：

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

二.数据库管理

? ? ? ? 2.1 元数据库简介

记录MySQL自身数据的数据库。

? ? ? ? 2.2 元数据库分类

1.information_schema

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

2.mysql

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

3.performance_schema

• 用于mysql的监控数据的存放（读写分离）

? ? ? ? 2.3 数据库的增删改查及使用

1.use 数据库名（使用数据库）

2.create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;——设置了字符编码以及不区分大小写。（创建）

3.show databases；（查询所有数据库）

4.drop database 数据库名。（删除数据库）

? ? ? ? 2.4 MySQL库权限相关

1.user表（用户层权限）：决定用户拥有哪些数据库的权限

2.db表（数据库层权限）：决定用户对这个数据库里哪些表有操作权限

3.tables_priv表（表层权限）：决定用户对于某一个表具体的增删改查的权限

4.columns_priv表（字段层权限）：决定用户针对于某一张表的哪些字段具有操作权限。

三.数据表管理

? ? ? ? 3.1 三大范式

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

? ? ? ? 3.2 基本数据类型

? ? ? ????????? 3.2.1 优化原则

1.更小通常更好

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

2.简单就好

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

3.尽量避免NULL

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

? ? ? ? ? ? ? ? 3.2.2 分类

1.整数

• tinyint 8位（-128~127）
• smallint 16位（-32768~32767）
• mediumint 24位（-8388608~8388607）
• int 32位 大约正负21亿
• bigint 64位

2.实数（带有小数点）

• float 4个字节
• double 8个字节
• decimal 最多允许65个数字

3.字符串

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

4.text&blob（为存放很大的数据而设计）

• text存储字符数据
  • tinytext
  • smalltext
  • mediumtext
  • text
  • longtext
• blob存储二进制数据
  • tinyblob
  • smallblob
  • mediumblob
  • blob
  • longblob

5.日期时间

• datetime
  • 精度：秒
  • 与时区无关，8个字节存储空间
  • 范围：1001至9999年
• timestamp
  • 保存1970年1月1日午夜以来的秒数
  • 占用4个字节存储空间
  • 范围：1970年至2038年
  • 与时区有关
  • 默认为NOT NULL
  • 通常尽量使用timestamp
  • 精度：秒
• date
  • yyyy-MM-dd
• time
  • HH:mm:ss

6.选择标识符

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

四.数据库账号管理

? ? ? ? 4.1 相同

1.查询用户：SELECT * from user;

2.查看用户常用信息：select host,user from user;

3.删除用户（慎用）：#命令：drop user用户名；

4.权限操作：

• 设置权限（Grant）

  #语法：grant privileges on databasename.tablename to username@'host';

  ? ? ? ? ? #给zs用户 赋予 数据库db_xiaoli.t_p1_user 查询权限

  ? ? ? ? ? grant SELECT on db_xiaoli.t_p1_user to zs@'%';

  ? ? ? ? ? #给zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限

  ? ? ? ? ? grant UPDATE on db_xiaoli.t_p1_user to zs@'%';

  ? ? ? ? ? #给zs用户 赋予 数据库db_xiaoli中所有表 查询权限

  ? ? ? ? ? grant SELECT on db_xiaoli.* to zs@'%';

  ? ? ? ? ? #给zs用户 赋予 数据库db_xiaoli中所有表 所有权限

  ? ? ? ? ? grant ALL on db_xiaoli.* to zs@'%';

• ?撤销权限（Revoke）

  ? ? ? ? ? #语法：revoke privileges on databasename.tablename from username@'host';

  ? ? ? ? ? #啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响

  ? ? ? ? ? revoke DELETE on db_xiaoli.t_p1_user from zs@'%';

  ? ? ? ? ? #可以回收GRANT SELECT，UPDATE ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限

  ? ? ? ? ? revoke all on db_xiaoli.* from zs@'%';

  ? ? ? ? ? #注：revoke只能回收grants列表中更小的权限；

• 查看用户权限

  ? ????????#命令：show grants for 用户名；

  ? ????????show frants for 'zs'@'%';

???????? 4.1 不同

1.MySQL 5.7版本

• 创建用户并设置登录密码
  ? ? #命令：create user 用户名 identified by '密码';
  ? ? #注：identified by会将存文本密码加密作为散列值存储
  ? ? create user ls identified by '123456';
• 修改用户密码
  ? ? #命令：set password for 用户名=password('新密码')；
  ? ? set password for zs=password('123456');
  ? ? flush privileges;

2.?MySQL 8.0版本

• 创建用户并设置登录密码
  ? ? #用户名密码创建需要分开
  ? ? #命令：create user 用户名;
  ? ? create user ls;
• 修改用户密码
  ? ? ?#ALTER USER 用户 IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';
  ? ? ?ALTER USER 'ls'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';
  ? ? ?flush privileges;

五.思维导图