目录
前言:
一.数据库存储引擎
1.存储引擎简介
存储引擎查看
support字段说明
2.四大引擎详细介绍
InnoDB
MylSAM
MEMORY
Archive
二.数据库管理
1.元数据库简介
2.元数据库分类
infomation_schema
mysql
performance_schema
编辑3.数据库的增删改查
创建数据库
查看数据库
删除数据库
4.MySQL权限相关表
user表(用户层权限)
db表(数据库层权限)
table_priv(表层权限)
colummns_prvi(字段层权限)
三.数据库表管理
1.三大范式
2.基本数据类型
优化原则
3.字符串类型
整数
实数(有小数点)
字符串
text和blob(大数据应用)
TINYTEXT TEXT MEDIUMTEXT LONGTEXT
时间日期
datetime
timestamp
date(yyy-MM-dd)
time(HH:MM:ss)
选择标识符
四.数据库账号管理
1.用户的管理操作
查询用户
查询用户常见信息
2.权限的相关操作
设置权限
五思维导图
前言:
随着大数据和云计算的迅速发展,MySQL作为开源关系型数据库的代表,已经成为了许多企业和开发者的首选。在第一篇博客中,我们简要介绍了MySQL的基本概念和安装。今天,我们将深入探讨MySQL的四大存储引擎、账号管理和建库实战。
二、账号管理:安全与权限
在MySQL中,账号和权限管理是确保数据安全的关键环节。通过GRANT
和REVOKE
命令,可以精确地控制哪些账号具备哪些权限。例如,可以为特定账号设置只读权限或限制其只能访问特定数据库。
三、建库实战:从零到一构建你的数据库
- 需求分析: 在开始建库之前,明确需求是至关重要的。考虑你的应用场景、数据规模和查询频率。
- 设计数据库结构: 根据需求设计表结构,定义字段、数据类型和约束。
- 创建数据库和表: 使用
CREATE DATABASE
和CREATE TABLE
命令创建数据库和表。 - 导入数据: 如果已有数据,可以使用
LOAD DATA INFILE
命令导入。 - 优化与维护: 根据实际使用情况,进行必要的优化和维护。
通过这篇博客,我们希望能够为你提供一个关于MySQL深入学习的概览。在接下来的文章中,我们将详细介绍每个部分的内容,帮助你全面掌握MySQL的核心知识。
一.数据库存储引擎
1.存储引擎简介
四大存储引擎:MyISAM、InnoDB、Memory和Merge
- MyISAM: 这是MySQL的默认存储引擎。它支持全文索引、高速缓存和压缩,但不支持事务处理。
- InnoDB: 提供了事务安全(ACID兼容)的表,行级锁定和外键约束。
- Memory: 数据存储在内存中,速度快但数据是临时的。
- Merge: 允许用户将多个MyISAM表合并为一个表。
存储引擎查看
show ENGINES
support字段说明
default 代表为默认搜索引擎
YES 代表可以使用
NO 代表不可以使用
2.四大引擎详细介绍
InnoDB
使用场景: 一般事务性均使用,用途最广,如果不能把握使用何种就是用innoDB
特点:修改快,支持事务(行锁);存储限制64TG;支持事务
MylSAM
使用场景:大量查询,很少修改(数据字典,系统参数)
特点:强调快速读取操作(表锁);存储限制为256TG;不支持事务
MEMORY
使用场景:由于易失性,可以存储用于分析中产生的中间表
特点:所有数据存储在内存中,一点服务器重启,所有memory存储引擎的表数据会消失但是表结构会保存下来;存储限制取决于RAM(随机存储器);不支持事务
Archive
使用场景:日志和数据采集使用
特点:只允许插入和查询,压缩存储,节约空间,可以实现高并发的插入,支持在自增ID上建立索引;不支持索引;Archive表比MylSAM表要小大约75%,比支持事务表格小大约83%
二.数据库管理
1.元数据库简介
MySQL安装后自带的数据库,记录MySQL数据库自身的数据库
2.元数据库分类
infomation_schema
简介
信息数据库,保存MySQL所维护的其他数据库信息。比如数据库名,数据库表等等
mysql
简介
核心数据库,主要用于存储数据库用户,权限设置关键字等等MySQL自己需要控制和管理的信息
performance_schema
简介
用于mysql的检查数据存放 (MySQL集群需要mycat)
3.数据库的增删改查
使用数据库
use 数据库名字
创建数据库
#一般情况下
create database 数据库名字;
#全称
create database if not exists 数据库名字
default utf8 collate utf8_general_ci;(不区分大小写)
查看数据库
show database;
删除数据库
drop 数据库名字;
4.MySQL权限相关表
user表(用户层权限)
用户有哪些权限
db表(数据库层权限)
对于数据库操作的授权
table_priv(表层权限)
对于表的操作权限(增加,删除,修改,查询,创建表,生产表)
colummns_prvi(字段层权限)
对于字段名的操作权限
三.数据库表管理
1.三大范式
第一范式:列不可在分(原子性)
举例:地址(湖南长沙望城)实际上要在分为几个字段 省份,城市,地区
第二范式:主键约束
举例:订单表单价,数量,小计
第三范式外键约束
举例:患者id和患者名字存储在同一张表中(修改需要同时修改两张表格)
2.基本数据类型
优化原则
1.更小通常更好:更小通常更快;更小的磁盘空间,内存缓存;更小的cpu周期
2.简单就好:整型比字符串操作代价更小,用时间类型表示日期(date,datetime等等)不用字符串
3.避免NULL: NULL为列的默认值,单除非非常需要;尽量避免使用NULL;尽量知道列为NOT NULL,特别是需要构建索引的列;查询中出现MULL的类,MySQL更难优化(不利于使用索引,索引统计更复杂,值比较更复杂);用整型而不是字符串表示ip(INET_ATON(),INET_NTOA())
3.字符串类型
整数
TINYINT(8位):
- 范围: -128 到 127 或 0 到 255(无符号)
- 存储需求: 1 字节
SMALLINT(16位):
- 范围: -32,768 到 32,767 或 0 到 65,535(无符号)
- 存储需求: 2 字节
MEDIUMINT(24位):
- 范围: -8,388,608 到 8,388,607 或 0 到 16,777,215(无符号)
- 存储需求: 3 字节
INT 或 INTEGER(24位):
- 范围: -2,147,483,648 到 2,147,483,647 或 0 到 4,294,967,295(无符号)
- 存储需求: 4 字节
BIGINT(64):
- 范围: -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 或 0 到 18,446,744,073,709,551,615(无符号)
- 存储需求: 8 字节
实数(有小数点)
FLOAT(4字节):
- 存储需求: 取决于总位数和小数点后的位数。例如,
FLOAT(7,4)
表示总长度为7位,其中小数点后有4位。- 范围: -3.40282e+38 到 3.40282e+38
DOUBLE(8字节):
- 存储需求: 取决于总位数和小数点后的位数。例如,
DOUBLE(15,4)
表示总长度为15位,其中小数点后有4位。- 范围: ±1.7E-308 到 ±1.7E+308
DECIMAL(最多65位数):
- 这是一个固定精度的类型,允许你指定总的数字数量和小数点后的数字数量。例如,
DECIMAL(10,2)
表示总共10位数字,其中小数点后有2位。- 范围: 与DOUBLE相同,但精度更高。
字符串
CHAR:
- 定长字符串。长度范围是 0 到 255 个字符。
- 如果存储的字符串长度小于定义的长度,MySQL会用空格填充剩余的空间。
VARCHAR:
- 变长字符串。长度范围是 0 到 65,535 个字符。
- 只存储实际需要的字符,不会用空格填充。
- 频繁修改且字符串变化长度大时,可能会出现页分裂
text和blob(大数据应用)
text类型
TINYTEXT TEXT MEDIUMTEXT LONGTEXT
blob类型
TINYBLOB BLOB MEDIUMBLOB LONGBLOB
时间日期
datetime
特点:精度:秒 和时区无关,8个字节,范围 1001-9999年
timestamp
特点:保存1970.1.1午夜的秒数,4字节,范围1970-2038年,和时区有关默认为:NOT NULL ,通常使用即可
date(yyy-MM-dd)
time(HH:MM:ss)
选择标识符
- 用来进行关联操作
- 在其他表中为外键
- 整型是最好的标志类类型
- 相关吧中使用相同数据类型
- 尽量避免字符串作为标志列,尤其是随机生成的字符串,会导致insert和selest都很慢
四.数据库账号管理
1.用户的管理操作
查询用户
select *from user;
查询用户常见信息
select user,host from user;
2.权限的相关操作
设置权限
创建用户设置密码
create user lz identified by '123';
给lz用户授权
增加授权
grant ALL on mysql.* to lz@'%';
回收权限
revoke all on mysql.* from lz@'%';
授予查看一张表格权限
赋予一张表权限
grant SELECT on mysql.t_vue_user to lz@'%';
赋予修改权限
grant UPDATE on mysql.t_vue_user to lz@'%';
查看授权情况(GRANT USAGE ON *.* TO 'lz'@'%' 游客模式只能登录)
show grants for 'lz'@'%';