MySQL之四大引擎、建库建表以及账号管理
目录
一、数据库存储引擎
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;
?五、思维导图
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!