[TOC]

0x00 快速入门

问:什么是数据库(Database)?

  • 1.数据库就是一个文件系统,需要利用特定的命令SQL语句来进行操作;
  • 2.数据库是按照数据结构来组织、存储和管理数据库的建立在计算机存储设备上的仓库(数据库是数据的结构化集合);
  • 3.数据库赛长期存储在PC中,有组织、可共享的数据集合,数据库中的数据指的是以一定的数据模型组织、描述和存储在一起、尽可能小的冗余度,较高的数据独立性和易扩展性的特点并可在一定的范围内为多个用户共享;
  • 4.具有对象(例如数据库,表,视图,行和列)的逻辑模型提供了灵活的编程环境

问:数据库功能作用?

  1. 存储数据:数据仓库持久化存储;
  2. 访问权限:限制不同的角色访问不同的数据库以及表字段;
  3. 查询数据:数据查询方便、快捷;


(1) SQL介绍

描述:Structured Query Language 结构化查询语言(非过程性语言):SQL是用来存取关系数据库的语言,具有查询、操纵、定义和控制关系型数据库的四方面功能;
为加强SQL的语言能力,各厂商增强了过程性语言的特征如Oracle的PL/SQL 过程性处理能力,SQL Server、Sybase的T-SQL;


(2) MySQL介绍

描述:MySQL是最流行的开源SQL数据库管理系统(关系型数据库/NoSQL数据库),MySQL以联合创始人Monty Widenius的女儿My命名,现在已经被Oracle收购所以由Oracle Corporation开发,分发和支持。有免费的社区版本和收费的EnterPrise版本存在有闭源的风险,但是它的开发者又采用收购前的分支开发出它的兄弟Mariadb数据库两则有一定的异同,并且在CentOS6/7发型版本中默认已不再是MySQL;

MySQL软件提供了一个非常快速的多线程,多用户、健壮的SQL(结构化查询语言)数据库,MySQL服务器用于任务关键型、高负载 生产系统,以及嵌入到大规模部署;

  • 1.MySQL是一个数据库管理系统。
  • 2.MySQL数据库是关系型的。
  • 3.MySQL软件是开源的:软件使用GPL(GNU通用公共许可证)
  • 4.MySQL数据库服务器非常快速,可靠,可扩展且易于使用。
  • 5.MySQL-Server在客户端/服务器或嵌入式系统中运行。
  • 6.MySQL相关支持组件,比如采用PHP和JAVA通过通用数据访问对象进行对数据库进行操作;

MySQL的主要功能:

  • 内部结构和可移植性
    • 1.用C和C ++编写适用于不同的编译器和各个系统平台;
    • 2.使用具有独立模块的多层服务器设计,设计为使用内核线程完全多线程,可以轻松使用多个CPU(如果有)。
    • 3.使用MyISAM索引压缩非常快的B树磁盘表(),使用非常快速的基于线程的内存分配系统。
    • 4.使用优化的嵌套循环联接执行非常快速的联接,实现内存中的哈希表,用作临时表。
    • 5.提供事务性和非事务性存储引擎
    • 6.使用高度优化的类库来实现SQL函数,该类库应尽可能快。
    • 7.提供服务器作为在客户端/服务器网络环境中使用的单独程序,以及作为可嵌入(链接)到独立应用程序中的库
  • 繁多的数据类型:
    • 1.(有符号/无符号)整型 / 浮点型 / 字符型 / 文本类型 / 二进制类型 / 日期时间类型和开放GIS空间类型
    • 2.固定长度和可变长度的字符串类型
  • 声明和函数
    • 1.SQL的DQL中支持完全运算符计算和函数支持,如WHERE id % 2 = 0;.
    • 2.字符类函数CONNCAT(),统计类函数COUNT(),时间类函数TIMESTAMP(),编码加密类函数md5();
    • 3.支持标准的SQL语法和ODBC语法,比如:GROUP BY和 ORDER BY子句
    • 4.支持左右连接,交叉连接,全连接:LEFT OUTER JOIN和 支持RIGHT OUTER JOIN标准SQL
    • 5.函数名称与表或列名称的独立性。例如,ABS是一个有效的列名。唯一的限制是对于函数调用,函数名称和其后的“ (”之间不允许有空格
  • 安全
    • 1.连接服务器时,通过对所有密码通信进行加密来实现密码安全。连接验证非常灵活和安全,并且可以进行基于主机的验证。
  • 可扩展性和限制
    • 1.支持大型数据库:包含5000万条记录的数据库,每个有200,000个表和大约5,000,000,000行,MySQL对数据库的数量本身赛没有限制,但是基础文件系统可能对目录数量有所限制。
    • 2.每个表最多支持64个索引,每个索引可以包含1到16列或部分列。InnoDB表的最大索引宽度为767字节或3072字节
  • 连接协议
    • 客户端可以使用多种协议连接到MySQL Server,使用TCP / IP套接字进行连接,在Windows系统上使用命名管道进行连接, Windows服务器还支持共享内存连接, Unix系统上客户端可以使用Unix域套接字文件进行连接
    • MySQL客户端程序可以用多种语言编写:C,C ++,Eiffel,Java,Perl,PHP,Python,Ruby和Tcl的API
    • Connector / ODBC(MyODBC)接口为使用ODBC(开放数据库连接)连接的客户端程序提供MySQL支持
    • Connector / J 接口为使用JDBC连接的Java客户端程序提供MySQL支持
    • Connector / NET 使开发人员可以轻松创建需要与MySQL安全,高性能数据连接的.NET应用程序。它实现了必需的ADO.NET接口,并集成到ADO.NET感知工具中。
  • 本土化定位
    • 1.支持不同的字符集可以在编译时和运行时指定它们,可以设置多种语言项客户端提供错误信息;
    • 2.排序和比较是根据默认字符集和排序规则完成的。
    • 3.可以指定时区以及服务器时区的动态修改。
  • 客户端和工具
    • 1.命令行: mysqldump | mysqladmin
    • 2.图形化程序: MySQL Workbench
    • 3.检查优化以及表修复: mysqlcheck
    • 4.MyISAM引擎操作工具 myisamchk


常见的关系化数据库:


问:什么是关系型数据库?
答:主要是用来描述实体entity与实体之间的关系,比如学生和班级;我们常用E-R关系图来进行数据库表字段设计,E-R()关系图是一个DBA必须需要了解并且掌握的:

问:什么是E-R图?
答:E-R图也称实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型。

  • 实体(Entity):方框
  • 属性(Property):椭圆
  • 关系(Relation):菱型

WeiyiGeek.

问:什么是非关系型数据库(NoSQL)?
答:它是一个Key:Value类型的数据库;


(3) 版本特性

MySQL发行版本:

  • MySQL Community Server: 社区Community版本免费遵循GPL协议
  • MySQL Enterprise Server: 企业版本商业用途;

MySQL Community 版本:

  • 5.7.x 系列 (当前版本 5.7.28)
  • 8.0.x 系列 (当前版本 8.0.20)

问:MySQL 5.7.x 与 8.0.x 版本有什么区别?

  • 1.速度:MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍 (光这一个理由即可)
  • 2.性能:读/写工作负载、IO 密集型工作负载、以及高竞争(”hot spot”热点竞争问题)工作负载
  • 3.NoSQL:MySQL 从 5.7 版本开始提供 NoSQL 存储功能,但是8.0系列版本得到了更大的改进该项功能消除了对独立的 NoSQL 文档数据库的需求,而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。
  • 4.窗口函数(Window Functions):8.0版本新增概念,它可以用来实现若干新的查询方式;它与 SUM()、COUNT() 这种集合函数类似,但它不会将多行查询结果合并为一行,而是将结果放回多行当中,即窗口函数不需要 GROUP BY。
  • 5.隐藏索引:8.0支持索引可以被“隐藏”和“显示”,当对索引进行隐藏时,它不会被查询优化器所使用,可以使用这个特性用于性能调试
    • 例如:我们先隐藏一个索引,然后观察其对数据库的影响。如果数据库性能有所下降,说明这个索引是有用的,然后将其“恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多余的可以考虑删掉。
  • 6.降序索引:为索引提供按降序方式进行排序的支持,在这种索引中的值也会按降序的方式进行排序;
  • 7.UTF-8 编码:从 MySQL 8 开始,使用 utf8mb4 作为 MySQL 的默认字符集。
  • 8.JSON:MySQL 8 大幅改进了对 JSON 的支持,添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数,以及用于将数据分别组合到 JSON 数组和对象中的JSON_ARRAYAGG() 和 JSON_OBJECTAGG()聚合函数(重点关注) 。
  • 9.可靠性:InnoDB 现在支持表 DDL 的原子性(InnoDB 表上的 DDL 也可以实现事务完整性了),要么失败回滚要么成功提交,不至于出现 DDL 时部分成功的问题,此外还支持 crash-safe 特性,元数据存储在单个事务数据字典中(重点关注)。
  • 10.安全性:对 OpenSSL 的改进、新的默认身份验证、SQL角色、密码强度、授权。
  • 11.高可用性(High Availability):InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。
  • 12.通用表表达式(Common Table Expressions CTE):在复杂的查询中使用嵌入式表时,使用 CTE 使得查询语句更清晰。
  • 13.MySQL 8.0不支持通过在 数据目录下手动创建目录(例如,使用mkdir)来创建数据库目录 。

参考资料:


0x01 数据库架构

描述:了解了MySQL数据库架构有助于我们深入学习MySQL,以及后期的性能调优;

MySQL的逻辑架构主要分为三个层次:

  • 服务层:为客户端请求做连接处理,授权认证,安全等;
  • 核心层:查询解析,分析,优化,缓存,所有内建函数,存储过程,触发器,视图;
  • 存储引擎层:存储和提取数据以及事务处理;

WeiyiGeek.逻辑架构


名词解释:

  • 客户端:MySQL客户端并不是单单只指界面或者命令行的MySQL数据库连接软件,它是一种复合概念包含了不同的编程语言编写的后端查询存储应用程序以及所调用的API接口;
    • 比如:MySQL-Client命令行,Native 数据库连接软件,以及PHP的PDO扩展和JAVA的JDBC数据库连接驱动等;


1.服务层
描述:MySQL数据库是一个单进程多线程的应用程序;
WeiyiGeek.服务层


2.核心层
描述:主要针对于SQL语句进行解析优化,并且查看是否存在缓存记录以提高快速数据查询的效率;

WeiyiGeek.核心层


3.存储引擎层
描述:有了改层不同的存储引擎,可以根据多个应用场景进行选择不同的存储引擎,他们之间的算法以及IO执行效率都是不同需要根据场景选择。并且他们完美支持WINDOWS/LINUX的文件系统,后面再学习优化的时候重点了解;
WeiyiGeek.存储引擎层


WeiyiGeek.MySQL架构总图


0x02 库设计标准和原理

MySQL数据库服务器、数据库和表的关系:

  • 所谓安装数据库服务器,只是在机器上装了一个数据库管理程序,这个管理程序可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。
  • 为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体的数据。
  • 数据在数据库中的存储方式:表的一行称之为一条记录;
    WeiyiGeek.DB数据库和表的关系


MySQL数据库设计标准(重要):

  • 1.数据库:采用小写为首加_下划线连接,例如database_project;
  • 2.数据表:采用小写为首加_下划线连接,例如table_project;
  • 3.数据列:驼峰式命名法第一个单词小写,后面首字母大写,例如displayName;
  • 4.主键(Primary Key):每一个表都应设立主键(快速定位,保证唯一性),主键字段Values一般用整数或短字符串
  • 5.索引(Index):为了能加快某列查询的速度就需要将某列字段添加索引,例如为了加快按日期查询的速度,可以建立生日的索引;
  • 6.连接(JOIN):连接不同表的字段内外连接,左右连接,全连接,交叉链接;
  • 7.外键(FOREIGN KEY):作为一种约束条件用于维护数据的完整性(不让用户乱删、错改)。


0x03 库管理工具

1.phpMyAdmin
安装环境:CentOS Linux release 7.6.1810 (Core) / httpd / php
官网:https://www.phpmyadmin.net/

流程步骤

#php版本
php -v
# PHP 7.3.6 (cli) (built: May 28 2019 09:32:59) ( NTS )
# Copyright (c) 1997-2018 The PHP Group
# Zend Engine v3.3.6, Copyright (c) 1998-2018 Zend Technologies

#我也安装php73的httpd解析php模块
$ ll /etc/httpd/modules/
总用量 6940
-rwxr-xr-x. 1 root root 4588224 10月 31 2018 libphp7.so

#下载phpmyadmin并解压到/var/html/www/中
wget https://files.phpmyadmin.net/phpMyAdmin/4.8.5/phpMyAdmin-4.8.5-all-languages.zip
unzip /opt/phpMyAdmin-4.8.5-all-languages.zip -d /var/www/html/
mv phpMyAdmin-4.8.5-all-languages/ phpMyAdmin/

#调整防火墙
[[email protected] html]# firewall-cmd --add-port=80/tcp --permanent
success
[[email protected] html]# firewall-cmd --reload
success

#修改配置使其可以连接到其他的机器上:
vim /var/www/html/phpMyAdmin/libraries/config.default.php
$cfg['AllowArbitraryServer'] = true; #修改为true

查看phpmyadmin后台:
WeiyiGeek.phpmyadmin

登录成功后:
WeiyiGeek.phpmyadmin后台


入坑解决
问题1:phpMyAdmin - 错误缺少 mysqli 扩展
WeiyiGeek.错误

原因:由于phpmyadmin需要用到mysqli扩展,而默认php7.3未安装
解决方法:yum --enablerepo=remi-php73 install -y php php-mysqli

问题2:phpMyAdmin配置文件现在需要一个短语密码的解决方法;

#在phpMyAdmin目录中找到“config.inc.php”,然后用编辑器打开 config.inc.php (如果没有,那就找到另一个文件“config.sample.inc.php”,重命名为“config.inc.php”)搜索下面一行代码:
phpMyAdmin/config.sample.inc.php
phpMyAdmin/libraries/config.default.php
$cfg['blowfish_secret'] = '[email protected]@2019';

问题3:变量 $cfg[‘TempDir’] (./tmp/)无法访问, phpMyAdmin无法缓存模板文件,所以会运行缓慢。

[[email protected] phpMyAdmin]# mkdir tmp
[[email protected] phpMyAdmin]# chmod 777 tmp


2.Adminer
描述:Adminer(原phpMinAdmin)是用PHP编写的一个功能完备的数据库管理工具,它由一个单一的文件准备部署到目标服务器。
Adminer可用于 MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch and MongoDB.

官网地址:https://www.adminer.org/en/
Adminer主题文件:https://raw.githubusercontent.com/vrana/adminer/master/designs/flat/adminer.css

Adminer特点:

  • 1.安全
  • 2.用户体验
  • 3.性能
  • 4.功能设置
  • 5.大小

Admier功能:

  • 基础SQL语句数据库增删改查,在表中列出搜索、聚合、排序和限制结果的数据
  • 列出表的字段、索引、外键和触发器
  • 添加和删除表和列
  • 更改表的名称、引擎、排序规则、auto_increment和注释
  • 更改列的名称、类型、排序规则、注释和默认值
  • 创建,修改,删除和搜索索引包括全文 、外键、视图、存储过程和函数、触发器;
  • 支持通过文件传输的所有数据类型,SQL语句执行导出表结构,数据,视图,例程,数据库到SQL或CSV;
  • 管理事件和表分区(MySQL 5.1)

WeiyiGeek.demo

Adminer安装:

#方式1:采用XMAPP集成环境进行安装然后将其拖入webRoot中
#方式2:采用Docker容器运行它
docker pull adminer #默认lastest版本及最新版本
docker run --restart=always -P 80:8080 adminer


3.SQLyou
描述:无代理和经济有效的MySQL客户端用于管理(SQLyog)和监视性能的工具(SQLDiagnostic Managerfor MySQL (formerly Monyog))
官网地址:https://www.webyog.com/
下载地址:https://static.webyog.com/downloads/SQLyog-13.1.5-0.x64Trial.exe


0x04 数据类型

描述:什么是数据类型?为什么要出现数据类型?
答:定义数据类型的本质是在定义列,因为不同的数据类型可以存储并且高效处理各类数据,并且进行数据处理的方式算法也是有所不同的;

数据分类的意义:

  • 分配合适得存储空间,不同的数据类型其存储空间是不一致的;
  • 采用不同的操作方法,不同的数据类型其数据操作处理是不一致的;
  • 对应不同的需求,不同的应用对于数据类型的需求是不一致的;

MySQL数据类型大致分为以下几类:

  • 整型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
  • 浮点型:FLOAT、DOUBLE、DECIMAL(M,D)
  • 布尔型:BOOL、BOOLEAN
  • 文本型:CHAR(M)、VARCHAR(M),TINYTEXT,MEDIUMTEXT,TEXT,LONGTEXT
  • 二进制型:TINYBLOB、MEDIUMBLOB、BLOB、LONGBLOB
  • 日期时间型:YEAR、TIME、DATE、DATETIME、TIMESTAMP
  • 枚举类型:SET、ENUM


整型
MySQL中提供多种对于数值的数据类型,不同的数据类型取值范围是不同的,其取值范围越大需要的空间也就越多(注意有无符号数据取值范围);
类型如下:

#查看帮助 help decimal
TINYINT 微小 1B 0-255 -128~127 年龄(age)
SMALLINT 小 2B 0-65535 -32768~32767 技能(skills),员工(id)
MEDIUMINT 中等大小 3B 0-2^24-1 -2^23~2^23-1 行数多(rownum)建议自增长
INT 普通大小 4B 0-2^32-1 -2^31~2^31-1 金钱(money)
BIGINT 大 8B 0-2^64-1 -2^63~2^63-1 人口(Population)

#定义关键字
UNSIGNED:有符号的数据类型
ZEROFILL:长度不够填充置0

基础示例:

-- 整型无符号与有符号定义
CREATE TABLE tinyintsigned(
col1 TINYINT -- 默认是有符号
);

CREATE TABLE tinyintUnsigned(
col1 TINYINT UNSIGNED -- 定义无符号
);

-- 设置显示位数一般没有作用,只有在设置填充时候
CREATE TABLE intbit(
col1 INT(3) ZEROFILL -- 长度不够则填充0
);
INSERT INTO intbit VALUES (123),(12),(1);
SELECT * FROM intbit;
-- 执行结果:
-- col1
-- 123
-- 012
-- 001


浮点型
描述:主要为了存储带小数的类型,常常用浮点数和定点数类型用来存储带小数,并且使用(M,D)定点数方式设定M精度和D标度(小数位数);

类型如下:

FLOAT  单精度  4B
DOUBLE 双精度 8B
DECIMAL 定点数 M>D(M+2),M<D(D+2) 其默认值(10,0) [UNSIGNED|ZEROFILL]

基础示例:

-- 浮点数的案例
CREATE TABLE floatDemo(
col1 FLOAT(3,2) -- 定点数方式
);
INSERT INTO floatDemo VALUES (3.3),(3.33),(3.333),(3.335);
SELECT * FROM floatDemo;
-- 执行效果:
-- col1
-- 3.30 位数不够采用0补齐
-- 3.33
-- 3.33 超出了精度范围,只留下两位小数
-- 3.34 四舍五入第三位小数

-- 注意事项:插入数值小数点前的数值长度大于精度长度而还需要满足小数点后面的两位则会查询出错超出定义的范围;
INSERT INTO floatDemo VALUES (33.33)
INSERT INTO floatDemo VALUES (33.3)
查询出错 (1264): Out of range value for column 'col1' at row 1


-- 定点数案例
CREATE TABLE decimalDemo(
col1 DECIMAL(5,3) ZEROFILL-- 表示 2位整数,3位小数
);
INSERT INTO decimalDemo VALUES (79.55),(79.555),(70.554),(79.5556);
SELECT * FROM decimalDemo;
-- 执行结果:col1
-- 79.550
-- 79.555
-- 70.554
-- 79.556 也是四舍五入


文本类型
描述:字符串类型是一个统称它可以包含多种不同的数据类型,分为文本字符串类型二进制字符串类型,它不仅可以存储字符串还可以存储其他的数据类型;
基础类型:

CHAR(M)     定长字符串   0~255B    1<= M <= 255
VARCHAR(M) 变长字符串 0~65535B L+1B L <= M

#TEXT类型及其子类型用于存储比较长的非二进制字符串,例如文章或者评论可将TEXT当做VARCHAR加长增强版;
TINYTEXT 0~255B 短文本字符串 < 2^8
TEXT 0-65535B 长文本数据 < 2^16
MEDIUMTEXT 0-16777215B 中等长度文本数据 < 2^24
LONGTEXT 0~4294967295B 极大文本数据 < 2^32

基础示例:

-- CAHR 与 VARCHAR 类型异同
CREATE TABLE chardemo (
col1 CHAR(4), -- 非常注意定义存储长度
col2 VARCHAR(4)
);
INSERT INTO chardemo VALUES ('ab ','ab '),('abc','abc'),('abcd','abcd');;
SELECT concat(col1,'!'),concat(col2,'!') FROM chardemo;
-- 执行结果:
-- concat(col1,'!') concat(col2,'!')
-- ab! ab ! 前者char后者varchar

SELECT col1,length(col1),col2,length(col2) FROM chardemo;
-- 不同类型存入长度结果
-- col1 length(col1) col2 length(col2)
-- ab 2 ab 4 区别之处在于补空格占位前者不算,而后者是存入了空格
-- abc 3 abc 3
-- abcd 4 abcd 4

-- TEXT类型与其子类型案例演示
CREATE TABLE textdemo(
col1 TEXT
);
INSERT INTO textdemo VALUES ('abc'),('ABC');
SELECT * FROM textdemo WHERE col1 like 'ab'; -- 关键点:模糊查询 MySQL 8.0 Text文本类型是区分大小写的;
SELECT * FROM textdemo WHERE col1 like '%'; -- 关键点:模糊查询;
SELECT * FROM textdemo WHERE col1 like 'a%'
-- 执行结果 col1
-- abc

注意事项:

  • 1.字符串可以区分或者不区分大小写(TEXT类型-MySQL 5.7以下)的串比较匹配查询,并且支持进行模式匹配查询。
  • 2.CHAR类型需要补空格占位,而VARCHAR则是不需要的存入是什么样的显示就是什么样的;
  • 3.在插入数据不满足设定的值长度示进行截断,但是需要依赖sql_mode变量参数的值为空就可不报错插入否则不满足则执行严格的模式;
    SHOW variables like 'sql_mode';
    SET sql_mode='';
    INSERT INTO chardemo VALUES ('ABCDEFG''ABCDEFG'); -- 修改sql_mode后可以根据值设置进行截断插入
    SELECT * FROM chardemo;
    SHOW warnings;

    -- 执行结果:
    -- Variable_name Value
    -- sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    -- col1 col2
    -- ABCD ABCD


二进制类型
描述:二进制类型及其子类型是用于存储二进制数据的类型(no character set)以bytes为单位存储和比较,是文本类型的另外一种存储类型,可以存储声音/图像以及视频的数据;

基础类型:

TINYBLOB     0~255B 不超过255个字符的二进制字符串    < 2^8
BLOB 0~65535 二进制形式的长文本数据 < 2^16
MEDIUMBLOB 0-16777215B 二进制形式中等长文本数据 < 2^24
BIGBLOB 0-4294967295B 二进制形式极大文本数据 < 2^32


日期时间类型
描述:提供多种用于存储日期和时间的类型,根据需求在开发时候进行选择并且注意格式;

YEAR(M) YY|YYYY(M代表2|4)  1B  1901~2155(4位)/1970~2069(2位) MySQL5.X
TIME HH:MM:SS 3B -838:59:59~838:59:59
DATE YYYY:MM:DD 3B 1000-01-01 ~ 9999-12-31
DATETIME 年月日时分秒 8B 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 #实际开发中不建议使用此种类型
TIMESTAMP 年月日时分秒 4B 1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC #会受到系统时区影响

基础示例:

-- 特殊类型插入值演示
CREATE TABLE yeardemo(
col1 YEAR(4),
col2 YEAR -- MySQL 8.0 没有YEAR(2),默认同上
);
INSERT INTO yeardemo VALUE (2020,20),(0,0),('0','0'); -- 注意此处的‘’
SELECT * FROM yeardemo;
-- 执行结果:
-- col1 col2
-- 2020 2020
-- 0000 0000
-- 2000 2000 关键点


-- TIME类型演示
CREATE TABLE timedemo(
col1 TIME
);
INSERT INTO timedemo VALUES ('10:59:59'),(1122),('1122'),('2 10:10'),('2 10'),('10'); -- 注意单引号和不加单引号的区别
SELECT * FROM timedemo;
-- 执行结果:
-- col1
-- 10:59:59
-- 00:11:22
-- 00:11:22
-- 58:10:00 表示两天+10小时零10分钟,最右边的数字开头是从小时开始计算
-- 58:00:00
-- 00:00:10 不加:符号时候默认右侧是秒

-- 插入值不能转换成为时间时候会报错(不正确的时间值)
INSERT INTO timedemo VALUES (6565);
查询出错 (1292): Incorrect time value: '6565' for column 'col1' at row 4


-- DATE类型演示
CREATE TABLE datedemo(
col1 DATE
);
INSERT INTO datedemo VALUES ('2020-2-2'),('20-02-02'),('20-2-2'); -- 注意引号
SELECT * FROM datedemo; -- 执行结果: col1 都是 2020-02-02


-- DATETIME 类型演示:在实际开发中不建议使用此种类型由于其每次插入都多占2子节相对比与date 、 TIME 类型
CREATE TABLE datetimedemo(
col1 DATETIME
);
INSERT INTO datetimedemo VALUES ('2020-02-2'),('20-2-2 10:00:59'),('10$10$10 23:59:59'); -- 注意此处可以不采用-作为日期分割符号
SELECT * FROM datetimedemo;
-- 执行结果col1:
-- 2020-02-02 00:00:00
-- 2020-02-02 10:00:59
-- 2010-10-10 23:59:59


-- TIMESTMP 类型演示:该值受到系统时区的影响
SHOW variables like 'time_zone'
SET time_zone='+8:00' -- 设置当前时区加8小时
-- Variable_name Value
-- time_zone SYSTEM

CREATE TABLE IF NOT EXISTS timestampdemo(
col1 TIMESTAMP
);
INSERT INTO timestampdemo VALUES (NOW());
SELECT * FROM timestampdemo;
SET time_zone='+8:00'; -- 会将时间+8小时
SELECT * FROM timestampdemo;
-- 执行结果:
-- col1 2020-02-02 06:44:04
-- col1 2020-02-02 14:44:04 直接在源数据上+8小时

注意事项:

  • 在年份中使用’’单引号的时候,会将其中数值当做年份的最右侧的值,比如’0’默认值为2000年,MySQL默认匹配更多的值;
  • 由于MySQL支持不严格的语句匹配的特性所以输入日期类型值是非常灵活的;
  • 在实际开发中建议使用DATE+TIME或者TIMESTAMP代替DATETIME类型;


枚举类型
描述:其本质是文本字符串类型的一种延伸类型;

  • 1.ENUM类型:存储的数据好比是单项选择题,其存储的值为表创建定义的枚举中的值选取一个,输入其他值则会报错;
  • 2.SET类型:存储的数据好比是多项选择题,其存储的值也必须是表创建定义的一系列的值,但是不同于ENUM类型的是它可以存入单个或者多个定义的枚举值;
ENUM 枚举类型 1~2B 存储需求1 or 2 bytes
SET 设置类型 1~8B 存储需求取决于集合成员数量;


实际案例:

-- 1.ENUM类型
CREATE TABLE enumDemo(
sex ENUM('F','M','UN') -- 插入的值只能是其中之一
);

-- 2.SET类型
CREATE TABLE setDemo(
kemu SET('a','b','c') -- 可以插入单个或者多个枚举值;
);
INSERT INTO setDemo VALUES ('a');
INSERT INTO setDemo VALUES ('a,b'); -- 关键点不是需要包含在一起


0x05 类型选择

(1) 字符串类型的选择

  • 1.不同的数据引擎选择倾向不同;
    • MYISAM:读多写少的场景 char性能 > varchar性能;
    • InnoDB:两种性能差不大varchar更节省空间;
    • MEMORY:内存IO效率高但是费用贵,一般也选择varchar类型
  • 2.不同的用途选择倾向不同;
    • CHAR存放经常变更的短字符串,VARCHAR则恰恰相反但是会产生数据空洞,如果是在磁盘上存在这样的问题则会导致磁盘碎片增多磁盘IO效率变低;


(2) 浮点数类型的选择

  • 1.精度要求不同选择也不同;
  • 2.相比较而言定点数的计算代价要昂贵很多,使用最多是在科学计算和金钱汇率相关的应用中;


(3) 大数据类型的选择

  • 1.能不用尽量不用TEXT与BLOB有很多替代选择,如果文件确实很大一般采用静态化为文本文件,数据库只记录其路径读取时候采用系统API读取即可效率会更改;
  • 2.如果存在经常的删除和更新,建议定期使用optimize table命令进行数据库层面的碎片整理;


(4) 日期类型的选择

  • 1.建议选择DATE和TIME来替代DATETIME数据类型;
  • 2.也可以采用TIMESTAMP类型来替代DATETIME类型但是需要注意各个机器的时区问题;

总结

  • 1.定义数据类型就是定义列,数据类型决定了数据的特性;
  • 2.数据类型主要分为字符串类型,整数类型,浮点数、定点数类型、日期类型、二进制类型以及枚举类型;
  • 3.根据需求以及空间占用和性能等多个方面来考虑;
  • 4.数据类型在不同的存储引擎上表现和处理的方式都是不同的;


0x06 事务处理介绍

描述:什么是事务?
事务是由一组SQL语句组成的逻辑处理单元,我们常常进行简读为ACID;

  • 原子性(Atommicity):事务是一个原子操作单元,对其数据的修改要么全部执行要么全都不执行;
  • 一致性(Consistent):在事务开始和结束时,数据修改都必须保持一致状态;
  • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务处理在不受外部并发操作时候影响它是独立执行的,意味着事务处理的过程中的中间状态对外部是不可见反之亦然;
  • 持久性(Durable):事务完成之后对于数据的修改是永久性,即使出现系统故障;

事务处理例子: 假如A/B用户汇款事件,A汇款1000给B用户在数据库中对应了两条UPDATE语句一个-1000另外一个则加1000,需要将这两个操作属于一个事务;否则会出现A账号钱被扣了,然后B用户并未收到1000,这可能是由于网络环境和物理环境共同作用的结果;