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

发布时间:2024年01月04日

目录

一、数据库存储引擎

1.1 存储引擎查看

1.2 InnoDB?

?1.3?MyISAM?

?1.4 MEMORY

1.5 Archive?

二、数据库管理

?2.1元数据库简介

2.2 数据库相关操作?

2.3 MySQL库

三、数据库表管理

3.1 三大范式

3.2 基本数据类型?

优先原则

值类型分类?

text&blob?

日期时间?

选择标识符?

四、数据库账号管理

相同

不同

?五、思维导图


一、数据库存储引擎

1.1 存储引擎查看

命令:SHOW ENGINE

support字段说明:

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

1.2 InnoDB?

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

特点:

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

?1.3?MyISAM?

使用场景:大量查询、很少修改的场景

特点:

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

?1.4 MEMORY

可以把一些常见的数据,要保证性能,就保存在memory存储引擎

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

特点:

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

1.5 Archive?

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

特点:

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

二、数据库管理

?2.1元数据库简介

1.这几个数据库就是数据库安装成功后,默认带的,就叫元数据库,MySQL自身数据的数据库

2.元数据库分类

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

2.2 数据库相关操作?

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

  • use数据库名
  • create database?if not exists 数据库名 default charse utf8 collate utf8_general_ci;——设置了字符集编码以及不区分大小写
  • show databases;
  • drop database?数据库名;

2.3 MySQL库

权限相关:

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

三、数据库表管理

3.1 三大范式

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

  • 例如:湖南省长沙市岳麓区浪琴湾燕联8栋
  • 如果开发人员不遵循第一范式:可能就是用一个列address
  • 如果遵循第一范式:provence省、city市、area区、address详细地址

第二范式:主键约束

  • 例如:订单项表设计:id、单价 数量、小计

第三范式:外键约束

  • 例如:患者id、患者姓名同时存在于一张关联表中

3.2 基本数据类型?

优先原则

更小通常更好

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

简单就好

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

尽量避免NULL

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

整数

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

实数(带有小数点):

  • float 4个字节
  • double 8个字节
  • decimal 最多允许65个数字:涉及到数字的运算使用decimal

字符串:

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

给来存放很大的数据而设计,与其他数据不同,都作为独立的对象存储

text存储字符数据

  • tinytext
  • smalltext
  • mediumtext
  • text
  • longtext

blob存储二进制数据:

  • tinyblob
  • smallblob
  • mediumblob
  • blob
  • longblob
日期时间?
  • datetime:精度:秒
    ? ? ? ? ? ? ? ? ?与时区无关,8个字节存储空间
    ? ? ? ? ? ? ? ? ?范围:1001至9999年
  • timestamp:保存1970年1月1日午夜以来的秒数
    ? ? ? ? ? ? ? ? ? ? 占用4个字节存储空间
    ? ? ? ? ? ? ? ? ? ? 范围:1970年至2038年
    ? ? ? ? ? ? ? ? ? ? 与时区有关
    ? ? ? ? ? ? ? ? ? ? 默认为NOT NULL
    ? ? ? ? ? ? ? ? ? ? 通常尽量使用timestamp
    ? ? ? ? ? ? ? ? ? ? 精度:秒
  • date:yyyy-MM-dd
  • time:HH:mm:ss
选择标识符?
  • 用来进行关联操作
  • 在其他表中作为外键
  • 整形通常是标识列的最后选择
  • 相关的表中使用相同的数据类型
  • 尽量避免字符串作为标识列,尤其是随机生成的字符串,(如:UUID)导致insert与select都很慢:
    ? ? ? ? ? ? ? 插入值被随机写到索引的不同位置,insert慢,容易导致页分裂,磁盘随机读取
    ? ? ? ? ? ? ? 辑上相邻的行被分布在磁盘和内存的不同地方,select慢
    ? ? ? ? ? ? ? 是MySQL查询缓存失效
    ? ? ? ? ? ? ? 如果需要存储UUID,则应将“-”去除
    ? ? ? ? ? ? ? ? ? ? ??

四、数据库账号管理

相同

查询用户: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`.* TO `zs`@`%`这条赋权语句带来的权限

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

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

? 查看用户权限

? ????????#命令:show grants for 用户名;

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

不同

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;

?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/2302_76638140/article/details/135347427
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。