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

一、mysql之四大引擎

1.1存储引擎查看

①命令:SHOW ENGINES

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

1.2四大引擎

①InnoDB

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

特点:修改块，支持事务;存储限制:64TG;事务支持:支持事务

②MyISAM

使用场景:大量查询，很少修改的场景；例：开发系统时的数据字典和系统参数表

特点:强调了快速读取操作；存储限制:256TG;事务支持:不支持事务

③MEMORY

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

特点:所有的数据都保存在内存中，一旦服务器重启，所有memory存储引擎的表数据会消失但；存储限制：取决于RAM（随机存储器）;事务支持:不支持事务

④Archive

使用场景:在日志和数据采集的时候可以使用
例：微信花钱后的账单

特点:只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增；archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%；不支持索引(自增ID列除外)

二、数据库管理

2.1元数据库简介

记录MySQL自身数据的数据库

2.2元数据库分类

①information_schema
信息数据库，保存mysql所维护的其他数据库信息

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

③performance_schema
用于mysql的监控数据的存放

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

查-----use 数据库名;
增-----create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;—设置了字符集编码以及不区分大小写
查询-----show databases;
删-----drop database 数据库名;

2.4权限相关

①user表(用户层权限):因为字段太多，只截取了一部分。首先登录的时候验证Host,User
②db表(数据库层权限)
③tables_priv表(表层权限)
④columns_priv表(字段层权限)

环环相扣

三、数据库表管理

3.1三大范式

①列不可再分（原子性）
②主键约束
③外键约束

3.2基本数据类型

3.2.1优化原则

1、更小通常更好
①更小通常更快
②更小的磁盘空间、内存、CPU缓存
③更少的CPU周期

2、简单就好
①整型比字符串操作代价更小
②用事件类型表示日期(data、datatime等)而不用字符串

3、尽量避免NULL
①NULL为列的默认值，但除非确实需要，应尽量避免使用NULL
②尽量指定列为NOT NULL,特别是需要建索引的列
③查询中包括有NULL的类，MySQL更难优化
④用整型值而不是字符串表示IP地址

3.2.2分类

3.2.2.1整型

①tinyint 8位(-128~127)
②smallimt 6位(-32768~32767)
③mediumint 24位(-8388608~8388607)
④int 32位 大约正负21亿
⑤bigint 64位

3.2.2.2实数(带有小数点)

①float 4个字节
②double 8个字节
③decimal 最多允许65个数字，例:decimal(5,2);说明：5位长度，2位小数精度，如果没有2位小数，则报错；如果只是小数部分超出2位，则四舍五入到2位

3.2.2.3字符串

①char
定长，MySQL根据定义字符串的长度一次分配足够的空间
使用场景:较短的字符串，且所有值接近统一长度

②varchar
比定长类型节约空间
使用场景:字符串的最长长度比评估长度大很多，列的更小较少
缺点:频繁修改，且字符串的长度变化大时，可能出现页分裂

3.2.2.4text&blob(为存放很大的数据而设计的)

1、text存储字符数据
①tinytext
②smalltext
③mediumtext
④text
⑤longtext

2、blob存储二进制数据
①tinyblob
②smallblob
③mediumblob
④blob
⑤longblob

3.2.2.5日期时间

1、datetime
进度:秒
与时区无关，8个字节存储空间
范围:1001至9999年

2、timestamp
保存1970年1月1日午夜以来的秒数
占用4个字节存储空间
范围:1979年至2038年
与时区有关
默认为NOT NULL
通常尽量使用timestamp
精度:秒

3、data
yyyy-MM-dd

4、time
HH:mm:ss

3.3权限操作

3.3.1设置权限(Grant)

#语法：grant privileges on databasename 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 sz@‘%’;
#给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限
grant ALL on db_xiaoli.* tozs@‘%’;

3.3.2撤销权限(Revoke)

#语法：revoke privileges on databasename.tablename from username@‘host’;
#啥也不能回收,不会对GRANT ALL PRIVILEGES ON ‘db_xiaoli’.‘t_p1_user’。* TO ‘zs’@‘%‘有任何影响
revoke DELETE on db_xiao.t_p1_user from zs@’%’;
#可以回收GRANT SELECT，UPDATE ON ‘db_xiaoli’.‘t_p1_user’ TO ‘zs’@‘%‘这条权限语句
revoke all on db_xiaoli.t_p1_user from zs@’%’;
#可以回收GRANT ALL PRIVILEGES ON ‘db_xiaoli’.* TO ‘zs’@‘%‘这条赋权语句带来的权限
revoke all on db_xiaoli.* from zs@’%’;
#注:revoke只能回收grants列表中更小的权限

3.3.3查看用户权限

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

四、MySQL不同版本创建用户并设置登录密码&修改用户密码

4.1MySQL5.7版本

创建用户并设置登录密码

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

修改用户密码

#命令：set password for 用户名=password(‘新密码’);
set password for zs=password(‘123456’);
–刷新
flush privileges;

4.2MySQL8.0版本

创建用户并设置登录密码

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

修改用户密码

#ALTER USER 用户 IDENTIFIED WITH mysql_native_password BY ‘密码’;
ALTER USER ‘ls’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
–刷新
flush privileges;

五、思维导图