文章目录
一、数据库存储引擎
1.1主要学习:(InnoDB、MEMORY、MylSAM、ARCHIVE)
通过:SHOW ENGINES
去查看引擎
主要学习这四个
support
字段说明
default
的为默认的引擎
为YES
表示可以使用
为NO
表示不能使用
1.2InnoDB
- 使用场景:一般事务性,均使用该引擎,用途广,如果把握不准使用何种引擎,就使用该引擎
- 特点:
1.修改快,支持事务–行锁
2.存储限制:64TG
3.事务支持:支持事务
1.3MylSAM
- 使用场景:大量查询,很少修改的场景
- 特点:
1.强调了快速读取操作–表锁
2.储存限制:256TG
3.事务支持:不支持事务
1.4MEMORY
- 使用场景:由于易失性,可以用于存储在分析中产生的中间表
- 特点:
1.所有的数据都保存在内存中,一旦服务器重启,所有memory存储引擎的表数据会消失但是表结构会保存下来
2.存储限制:取决于RAM(随机存储器)
3.事务支持:不支持事务
1.5Archive
- 使用场景:在日志和数据采集的时候可以使用
- 特点:
1.只允许插入和查询,不允许修改和删除,压缩存储,节约空间,可以实现高并发的插入
2.archive表比MylSAM表要小大约75%,比支持事务处理的innoDB表小大约83%
3.不支持索引(自增ID列除外)
二、数据库管理
2.1元数据简介:
- 记录MySQL自身数据的数据库
建数据库的时候自带的三个数据库
2.2元数据库的分类
information_schema
:信息数据库,保存mysql所维护的其他数据库信息。例如:数据库名、数据库的表、表栏的数据类型与访问权限等mysql
:核心数据库,主要负责存储数库的用户、权限设置、关键字等。mysql自己需要使用的控制和管理信息performance_schema
:用于mysql的监控数据的存放
2.3相关操作:数据库的增删改查及使用
- 使用哪个数据库:
user
数据库名 - 创建数据库(没有才创建完整写法):
create database if not exists 数据库名 default utf8_general_ci
–设置字符集编码以及不区分大小写 - 查询所有数据库:
show database
- 删除数据库:
drop database 数据库名
2.4MySQL库:权限相关
1、user表(用户层权限)
用户表决定用户拥有哪些数据库的权限
2、db表(数据库层权限)
这个用户对于该数据库哪些表有操作权限
3、tables_priv表(表层权限)
决定这个用户对于哪些表拥有增删改查权限
4、columns_priv表(字段层权限)
决定了这个用户针对于某一张表的哪些字段拥有操作权限
三、数据库表管理
3.1三大范式:
建表的时候要遵循三大范式
- 第一范式:列不可分(原子性)
例如:湖南长沙市岳麓区浪琴湾清水21栋 - 第二范式:主键约束
例如:订单项表设计:id、单价数量、小计 - 第三范式:外键约束
例如:患者id、患者姓名同时存在于一张关联表中
3.2基本数据类型
- 优化原则:
1.更小通常更好:
更小通常更快
更小的磁盘空间、内存、CPU缓存
更小的CPU周期
2.简单就好:
整形比字符串操作代价更小
用时间类型表示日期(date、datetime等)而不用字符串
3.尽量避免NULL:
NULL为列的默认值,但除非确实需要,应尽量避免使用NULL
尽量指定列为NOT NULL,特别是需要建索引的列
查询中包括有NULL的类,MySQL更难优化:不利于使用索引;索引统计更复杂;值比较更复杂
用整形值而不是字符串表示IP地址 - 值类型:
1.整数:tinyint 8位、smallint 16位、mediumint 24位、int 32位、bigint 64位
2.实数(带有小数点):float
4个字节、double
8个字节、decimal 最多允许65个数字
3.字符串:
char
:定长、MySQL根据定义字符串的长度一次分配足够的空间
使用场景:较短的字符串,且所有的值接近同一长度
varchar
:比定长类型节约空间
使用场景:字符串的最长长度比评估长度大很多,列的更新较少
缺点:频繁修改,且字符串的长度变化大时,可能出现页分裂 - 用的少:
4.text&blob:
都为存放很大的数据而设计
与其他数据不同,都作为独立的对象存储
当值太大时,使用外部存储区储存
text存储字符数据:tinytext、smalltext、mediumtext、text、longtext
blob存储二进制数据:tinyblob、small blob、mediumblob、blob、longblob
5.日期时间:
datetime
:
精度:秒
与时区无关,8个字节存储空间
范围:1001至9999年
timestamp
:
保存1970年1月1日午夜以来的秒数
占用4个字节存储空间
范围:1970年至2038年
与时区有关
默认为NOT NULL
通常尽量使用timestamp
精度:秒
data:
yyyy-MM-dd
time:
HH:mm:ss
- 选择标识列:
用来进行关联操作
在其他表中作为外键
整形通常是标识列的最好选择
相关的表中使用相同的数据类型
尽量避免字符串作为标识列,尤其是随机生成的字符串,如:(UUID)导致insert与select都很慢:
①插入值被随机写到索引的不同位置,insert慢,容易导致页分裂,磁盘随机读取
②逻辑上相邻的行被分布在磁盘和内存的不同地方,select慢
③是MySQL查询缓存失效
④如果需要存储UUID,则应将“-”去除
四、数据库账号管理
4.1查询用户:SELECT * FROM USER
4.2查用户的信息
select host,user from user
4.3删除用户(慎用)
drop user 用户名
;
4.4权限操作
①设置权限
语法:grant privileges on databasename.tablename to username@'host'
#给zs用户 赋予 数据库db_aa中的表t_user查询权限
grant SELECT on db_aa.t_user to zs@'%'
#给zs用户赋予数据库db_aa中的表t_user修改权限
grant UPDATE on db_aa.t_user to zs@'%'
#给zs用户赋予数据库db_aa中所有表查询权限
grant SELECT on db_aa.*to zs@'%'
#给zs用户赋予数据库db_aa中所有表 所有权限
grant ALL on db_aa.* to zs@'%'
②撤销权限
语法:revoke privileges on databasename.tablename from username@'host'
#啥也不能回收,不会对GRANT ALL PPIVILEGES ON db_aa.* To zs@‘%‘有任何影响
revoke DELETE on db_aa.t_user from zs@'%'
#可以回收GRANT SELECT,UPDATE ON
‘db_aa’,‘t_user’ To ‘zs’@’’’%‘这条权限语句
revoke all on db_aa.t_user from zs@'%'
注:revoke只能回收grants列表中更小的权限
③查看用户权限
#命令:show grants for 用户名
show grants for 'zs'@'%'