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

2024-01-03 09:39:43

目录

一. 数据库存储引擎

? ? ? ? 1.1?存储引擎查看

? ? ? ? 1.2?InnoDB

? ? ? ? 1.3?MyISAM

? ? ? ? 1.4?MEMORY

? ? ? ? 1.5?ARCHIVE

二. 数据库管理

? ? ? ? 2.1?元数据库简介

? ? ? ? 2.2?元数据库分类

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

? ? ? ? 2.4?MySQL库的权限

三. 数据表管理

? ? ? ? 3.1?三大范式

? ? ? ? 3.2?基本数据类型

? ? ? ? 3.2.1?优化原则

? ? ? ? 3.2.2?值类型分类

? ? ? ? 3.2.3?text&blob

? ? ? ? 3.2.4?日期时间

? ? ? ? 3.2 5?选择标识符

四. 数据库账号管理

? ? ? ? 4.1?相同

? ? ? ? 4.2?不同

? ? ? ? 4.2.1 MySQL 5.7版本

? ? ? ? 4.2.2 MySQL 8.0版本

五. 思维导图


一. 数据库存储引擎

? ? ? ? 1.1?存储引擎查看

命令:SHOW?ENGINES

support字段说明:

  • default的为默认的引擎
  • 为YES表示可以使用
  • 为NO表示不能使用

? ? ? ? 1.2?InnoDB

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

特点:

  • 修改快,支持事务(行锁)
  • 存储限制:64TG
  • 事务支持:支持事务

? ? ? ? 1.3?MyISAM

使用场景:大量查询,很少修改的场景。例如:数据字典,系统参数

特点:

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

? ? ? ? 1.4?MEMORY

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

特点:

  • 所有的数据都保存在内存中,一旦服务器重启,所有memory存储引擎的表数据会消失但是表结构会保存下来
  • 存储限制:取决于RAM(随机存储器)
  • 事务支持:不支持事务

? ? ? ? 1.5?ARCHIVE

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

特点:

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

二. 数据库管理

? ? ? ? 2.1?元数据库简介

MySQL自身数据的数据库,就是数据库安装成功后,默认带的,就叫元数据库。

? ? ? ? 2.2?元数据库分类

  • information_schema:信息数据库,保存mysql所维护的其他数据库信息。例如:数据库名,数据库的表,表栏的数据类型与访问权限等
  • mysql:核心数据库,主要负责存储数据库的用户、权限设置、关键字等。mysql自己需要使用的控制和管理信息。
  • performance_schema:用于mysql的监控数据的存放。
  • sys:Sys库所有的数据源来自:performance_schema。目标是把performance_schema 的把复杂度降低,让 DBA 能更好的阅读这个库里的内容。让DBA更快的了解 DB 的运行情况。

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

  • 使用数据库:use 数据库名
  • 创建数据库:create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;(设置了字符集编码以及不区分大小写)
  • 显示所有的数据库:show databases;
  • 删除数据库:drop database 数据库名;

? ? ? ? 2.4?MySQL库的权限

  • user表(用户表权限):用户有哪些数据库的操作权限
  • db表(数据库层权限):用户对这个数据库的哪些表有操作权限
  • tables_priv表(表层权限):用户对于表有哪些操作权限:增加、删除、修改、查询、创表、删除表
  • columns_priv表(字段层权限):决定了用户针对于某一张表的哪些字段有操作权限

三. 数据表管理

? ? ? ? 3.1?三大范式

第一范式:列不可再分(原子性)

  • 例如:湖南省长沙市岳麓区浪琴湾清水4栋

第二范式:主键约束

  • 例如:订单项表设计: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个数字:涉及到数字的运算使用decimal

3.?字符串:

  • char: 定长,MySQL根据定义字符串的长度一次分配足够的空间

    ? ? ? ? ? ? 适用场景:较短的字符串,且所有值接近同一长度

  • varchar: 比定长类型节约空间

    ? ? ? ? ? ? ? ? 适用场景:字符串的最长长度比评估长度大很多,列的更新较少

    ? ? ? ? ? ? ? ? 缺点:频繁修改,且字符串的长度变化大时,可能出现页分裂

? ? ? ? 3.2.3?text&blob

1. 都为存放很大的数据而设计
2.?与其他数据不同,都作为独立的对象存储
3. 当值太大时,使用外部存储区存储

1. text存储字符数据:

  • tinytext
  • smalltext
  • mediumtext
  • text
  • longtext

2.?blob存储二进制数据

  • tinyblob? ? ??
  • smallblob
  • mediumblob
  • blob
  • longblob
? ? ? ? 3.2.4?日期时间

1.?datetime:

  • 精度:秒
  • 与时区无关,8个字节存储空间
  • 范围:1001至9999年

2.?timestamp:

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

3.?date:yyyy-MM-dd

4.?time:HH:mm:ss

? ? ? ? 3.2 5?选择标识符
  • 用来进行关联操作
  • 在其他表中作为外键
  • 整形通常是标识列的最后选择
  • 相关的表中使用相同的数据类型
  • 尽量避免字符串作为标识列,尤其是随机生成的字符串,(如:UUID)导致insert与select都很慢

? ? ? ? 1. 插入值被随机写到索引的不同位置,insert慢,容易导致页分裂,磁盘随机读取

? ? ? ? 2. 逻辑上相邻的行被分布在磁盘和内存的不同地方,select慢

? ? ? ? 3. 是MySQL查询缓存失效

? ? ? ? 4. 如果需要存储UUID,则应将“-”去除

四. 数据库账号管理

? ? ? ? 4.1?相同

查询用户:SELECT?*?from?user;

查看用户常用信息:select?host,user?from?user;

删除用户(慎用):#命令:drop?user?用户名;

????????????????????????????????#drop?user?ls;

权限操作:

? ? ? ? ①?设置权限(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`.`t_p1_use` TO?`zs`@`%`这条赋权语句带来的权限
????????revoke?all?on?db_xiaoli.t_p1_use 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.2?不同

? ? ? ? 4.2.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;

? ? ? ? 4.2.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;

五. 思维导图

文章来源:https://blog.csdn.net/2301_79278613/article/details/135353702
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。