138 张图带你 MySQL 入门(二)

接续上篇《138 张图带你 MySQL 入门（一）》

日期时间类型

MySQL 中的日期与时间类型，主要包括：「YEAR、TIME、DATE、DATETIME、TIMESTAMP」，每个版本可能不同。下表中列出了这几种类型的属性。

下面分别来介绍一下

YEAR

YEAR 可以使用三种方式来表示

• 用 4 位的数字或者字符串表示，两者效果相同，表示范围 1901 - 2155，插入超出范围的数据会报错。
• 以 2 位字符串格式表示，范围为 ‘00’~‘99’。‘00’~‘69’ 表示 2000~2069，‘70’~‘99’ 表示1970~1999。‘0’ 和 ‘00’ 都会被识别为 2000，超出范围的数据也会被识别为 2000。
• 以 2 位数字格式表示，范围为 1~99。1~69 表示 2001~2069, 70~99 表示 1970~1999。但 0 值会被识别为0000，这和 2 位字符串被识别为 2000 有所不同

下面我们来演示一下 YEAR 的用法，创建一个 test4 表

create table test4(id year);

然后我们看一下 test4 的表结构

默认创建的 year 就是 4 位，下面我们向 test4 中插入数据

insert into test4 values(2020),('2020');

然后进行查询，发现表示形式是一样的

使用两位字符串来表示

delete from test4;

insert into test4 values ('0'),('00'),('11'),('88'),('20'),('21');

使用两位数字来表示

delete from test4;

insert into test4 values (0),(00),(11),(88),(20),(21);

发现只有前两项不一样。

TIME

TIME 所表示的范围和我们预想的不一样

我们把 test4 改为 TIME 类型，下面是 TIME 的示例

alter table test4 modify id TIME;

insert into test4 values ('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');

结果如下

DATE

DATE 表示的类型有很多种，下面是 DATE 的几个示例

create table test5 (id date);

查看一下 test5 表

然后插入部分数据

insert into test5 values ('2020-06-13'),('20200613'),(20200613);

DATE 的表示一般很多种，如下所示 DATE 的所有形式

• 'YYYY-MM-DD'
• 'YYYYMMDD'
• YYYYMMDD
• 'YY-MM-DD'
• 'YYMMDD'
• YYMMDD

DATETIME

DATETIME 类型，包含日期和时间部分，可以使用引用字符串或者数字，年份可以是 4 位也可以是 2 位。

下面是 DATETIME 的示例

create table test6 (id datetime);

insert into test4 values ('2020-06-13 11:11:11'),(20200613111111),('20200613111111'),(20200613080808);

TIMESTAMP

TIMESTAMP 类型和 DATETIME 类型的格式相同，存储 4 个字节（比DATETIME少），取值范围比 DATETIME 小。

下面来说一下各个时间类型的使用场景

• 一般表示年月日，通常用 DATE 类型；
• 用来表示时分秒，通常用 TIME 表示；
• 年月日时分秒 ，通常用 DATETIME 来表示；
• 如果需要插入的是当前时间，通常使用 TIMESTAMP 来表示，TIMESTAMP 值返回后显示为 YYYY-MM-DD HH:MM:SS 格式的字符串，
• 如果只表示年份、则应该使用 YEAR，它比 DATE 类型需要更小的空间。

每种日期类型都有一个范围，如果超出这个范围，在默认的 SQLMode 下，系统会提示错误，并进行零值存储。

下面来解释一下 SQLMode 是什么

MySQL 中有一个环境变量是 sql_mode ，sql_mode 支持了 MySQL 的语法、数据校验，我们可以通过下面这种方式来查看当前数据库使用的 sql_mode

select @@sql_mode;

一共有下面这几种模式

来源于 https://www.cnblogs.com/Zender/p/8270833.html

字符串类型

MySQL 提供了很多种字符串类型，下面是字符串类型的汇总

下面我们对这些数据类型做一个详细的介绍

CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 类型很相似，导致很多同学都会忽略他们之间的差别，首先他俩都是用来保存字符串的数据类型，他俩的主要区别在于存储方式不同。CHAR 类型的长度就是你定义多少显示多少。占用 M 字节，比如你声明一个 CHAR(20)的字符串类型，那么每个字符串占用 20 字节，M 的取值范围时 0 - 255。VARCHAR 是可变长的字符串，范围是 0 - 65535，在字符串检索的时候，CHAR 会去掉尾部的空格，而 VARCHAR 会保留这些空格。下面是演示例子

create table vctest1 (vc varchar(6),ch char(6));

insert into vctest1 values("abc  ","abc  ");

select length(vc),length(ch) from vctest1;

结果如下

可以看到 vc 的字符串类型是 varchar ，长度是 5，ch 的字符串类型是 char，长度是 3。可以得出结论，varchar 会保留最后的空格，char 会去掉最后的空格。

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 与 CHAR 和 VARCHAR 非常类似，不同的是它们包含二进制字符串而不包含非二进制字符串。BINARY 与 VARBINARY 的最大长度和 CHAR 与 VARCHAR 是一样的，只不过他们是定义字节长度，而 CHAR 和 VARCHAR 对应的是字符长度。

BLOB 类型

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

TEXT 类型

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

ENUM 类型

ENUM 我们在 Java 中经常会用到，它表示的是枚举类型。它的范围需要在创建表时显示指定，对 1 - 255 的枚举需要 1 个字节存储；对于 255 - 65535 的枚举需要 2 个字节存储。ENUM 会忽略大小写，在存储时都会转换为大写。

SET 类型

SET 类型和 ENUM 类型有两处不同

• 存储方式

SET 对于每 0 - 8 个成员，分别占用 1 个字节，最大到 64 ，占用 8 个字节

• Set 和 ENUM 除了存储之外，最主要的区别在于 Set 类型一次可以选取多个成员，而 ENUM 则只能选一个。

MySQL 运算符

MySQL 中有多种运算符，下面对 MySQL 运算符进行分类

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符

下面那我们对各个运算符进行介绍

算术运算符

MySQL 支持的算术运算符包括加、减、乘、除和取余，这类运算符的使用频率比较高

下面是运算符的分类

运算符作用+加法-减法*乘法/, DIV除法，返回商%, MOD除法，返回余数

下面简单描述了这些运算符的使用方法

• + 用于获得一个或多个值的和
• - 用于从一个值减去另一个值
• * 用于两数相乘，得到两个或多个值的乘积
• / 用一个值除以另一个值得到商
• % 用于一个值除以另一个值得到余数

在除法和取余需要注意一点，如果除数是 0 ，将是非法除数，返回结果为 NULL。

比较运算符

熟悉了运算符，下面来聊一聊比较运算符，使用 SELECT 语句进行查询时，MySQL 允许用户对表达式的两侧的操作数进行比较，比较结果为真，返回 1， 比较结果为假，返回 0 ，比较结果不确定返回 NULL。下面是所有的比较运算符

运算符描述=等于<> 或者是 !=不等于<=>NULL 安全的等于，也就是 NULL-safe<小于<=小于等于>大于>=大于等于BETWEEN在指定范围内IS NULL是否为 NULLIS NOT NULL是否为 NULLIN存在于指定集合LIKE通配符匹配REGEXP 或 RLIKE正则表达式匹配

比较运算符可以用来比较数字、字符串或者表达式。数字作为浮点数进行比较，字符串以不区分大小写的方式进行比较。

• = 号运算符，用于比较运算符两侧的操作数是否相等，如果相等则返回 1， 如果不相等则返回 0 ，下面是具体的示例，NULL 不能用于比较，会直接返回 NULL

• <> 号用于表示不等于，和 = 号相反，示例如下

• <=> NULL-safe 的等于运算符，与 = 号最大的区别在于可以比较 NULL 值

• < 号运算符，当左侧操作数小于右侧操作数时，返回值为 1， 否则其返回值为 0。

• 和上面同理，只不过是满足 <= 的时候返回 1 ，否则 > 返回 0。这里我有个疑问，为什么

select 'a' <= 'b';  /* 返回 1 */

/*而*/

select 'a' >= 'b'; /* 返回 0 呢*/

• 关于 > 和 >= 是同理
• BETWEEN 运算符的使用格式是 「a BETWEEN min AND max」 ，当 a 大于等于 min 并且小于等于 max 时，返回 1，否则返回 0 。操作数类型不同的时候，会转换成相同的数据类型再进行处理。比如

• IS NULL 和 IS NOT NULL 表示的是是否为 NULL，ISNULL 为 true 返回 1，否则返回 0 ；IS NOT NULL 同理

• IN 这个比较操作符判断某个值是否在一个集合中，使用方式是 xxx in (value1,value2,value3)

• LIKE 运算符的格式是 xxx LIKE %123%，比如如下

当 like 后面跟的是 123% 的时候， xxx 如果是 123 则返回 1，如果是 123xxx 也返回 1，如果是 12 或者 1 就返回 0 。123 是一个整体。

• REGEX 运算符的格式是 s REGEXP str ，匹配时返回值为 1，否则返回 0 。

后面会详细介绍 regexp 的用法。

逻辑运算符

逻辑运算符指的就是布尔运算符，布尔运算符指返回真和假。MySQL 支持四种逻辑运算符

运算符作用NOT 或 ！逻辑非AND 或者是 &&逻辑与OR 或者是 ||逻辑或XOR逻辑异或

下面分别来介绍一下

• NOT 或者是 ! 表示的是逻辑非，当操作数为 0（假） ，则返回值为 1，否则值为 0。但是有一点除外，那就是 NOT NULL 的返回值为 NULL

• AND 和 && 表示的是逻辑与的逻辑，当所有操作数为非零值并且不为 NULL 时，结果为 1，但凡是有一个 0 则返回 0，操作数中有一个 null 则返回 null

• OR 和 || 表示的是逻辑或，当两个操作数均为非 NULL 值时，如有任意一个操作数为非零值，则结果为 1，否则结果为 0。

• XOR 表示逻辑异或，当任意一个操作数为 NULL 时，返回值为 NULL。对于非 NULL 的操作数，如果两个的逻辑真假值相异，则返回结果 1；否则返回 0。

位运算符

一听说位运算，就知道是和二进制有关的运算符了，位运算就是将给定的操作数转换为二进制后，对各个操作数的每一位都进行指定的逻辑运算，得到的二进制结果转换为十进制后就说是位运算的结果，下面是所有的位运算。

运算符作用&位与|位或^位异或～位取反>>位右移<<位左移

下面分别来演示一下这些例子

• 位与 指的就是按位与，把 & 双方转换为二进制再进行 & 操作

按位与是一个数值减小的操作

• 位或 指的就是按位或，把 | 双方转换为二进制再进行 | 操作

位或是一个数值增大的操作

• 位异或 指的就是对操作数的二进制位做异或操作

• 位取反 指的就是对操作数的二进制位做 NOT 操作，这里的操作数只能是一位，下面看一个经典的取反例子：对 1 做位取反，具体如下所示：

为什么会有这种现象，因为在 MySQL 中，常量数字默认会以 8 个字节来显示，8 个字节就是 64 位，常量 1 的二进制表示 63 个 0，加 1 个 1 ， 位取反后就是 63 个 1 加一个 0 ， 转换为二进制后就是 18446744073709551614，我们可以使用 「select bin()」 查看一下

• 位右移 是对左操作数向右移动指定位数，例如 50 >> 3，就是对 50 取其二进制然后向右移三位，左边补上 0 ，转换结果如下

• 位左移 与位右移相反，是对左操作数向左移动指定位数，例如 20 << 2

MySQL 常用函数

下面我们来了解一下 MySQL 函数，MySQL 函数也是我们日常开发过程中经常使用的，选用合适的函数能够提高我们的开发效率，下面我们就来一起认识一下这些函数

字符串函数

字符串函数是最常用的一种函数了，MySQL 也是支持很多种字符串函数，下面是 MySQL 支持的字符串函数表

函数功能LOWER将字符串所有字符变为小写UPPER将字符串所有字符变为大写CONCAT进行字符串拼接LEFT返回字符串最左边的字符RIGHT返回字符串最右边的字符INSERT字符串替换LTRIM去掉字符串左边的空格RTRIM去掉字符串右边的空格REPEAT返回重复的结果TRIM去掉字符串行尾和行头的空格SUBSTRING返回指定的字符串LPAD用字符串对最左边进行填充RPAD用字符串对最右边进行填充STRCMP比较字符串 s1 和 s2REPLACE进行字符串替换

下面通过具体的示例演示一下每个函数的用法

• LOWER(str) 和 UPPER(str) 函数：用于转换大小写

• CONCAT(s1,s2 ... sn) ：把传入的参数拼接成一个字符串

上面把 c xu an 拼接成为了一个字符串，另外需要注意一点，任何和 NULL 进行字符串拼接的结果都是 NULL。

• LEFT(str,x) 和 RIGHT(str,x) 函数：分别返回字符串最左边的 x 个字符和最右边的 x 个字符。如果第二个参数是 NULL，那么将不会返回任何字符串

• INSERT(str,x,y,instr) ：将字符串 str 从指定 x 的位置开始， 取 y 个长度的字串替换为 instr。

• LTRIM(str) 和 RTRIM(str) 分别表示去掉字符串 str 左侧和右侧的空格

• REPEAT(str,x) 函数：返回 str 重复 x 次的结果

• TRIM(str) 函数：用于去掉目标字符串的空格

• SUBSTRING(str,x,y) 函数：返回从字符串 str 中第 x 位置起 y 个字符长度的字符串

• LPAD(str,n,pad) 和 RPAD(str,n,pad) 函数：用字符串 pad 对 str 左边和右边进行填充，直到长度为 n 个字符长度

• STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的 ASCII 值大小。如果 s1 < s2，则返回 -1；如果 s1 = s2 ，返回 0 ；如果 s1 > s2 ，返回 1。

• REPLACE(str,a,b) : 用字符串 b 替换字符串 str 种所有出现的字符串 a

数值函数

MySQL 支持数值函数，这些函数能够处理很多数值运算。下面我们一起来学习一下 MySQL 中的数值函数，下面是所有的数值函数

函数功能ABS返回绝对值CEIL返回大于某个值的最大整数值MOD返回模ROUND四舍五入FLOOR返回小于某个值的最大整数值TRUNCATE返回数字截断小数的结果RAND返回 0 - 1 的随机值

下面我们还是以实践为主来聊一聊这些用法

• ABS(x) 函数：返回 x 的绝对值

• CEIL(x) 函数：返回大于 x 的整数

• MOD(x,y)，对 x 和 y 进行取模操作

• ROUND(x,y) 返回 x 四舍五入后保留 y 位小数的值；如果是整数，那么 y 位就是 0 ；如果不指定 y ，那么 y 默认也是 0 。

• FLOOR(x) : 返回小于 x 的最大整数，用法与 CEIL 相反

• TRUNCATE(x,y): 返回数字 x 截断为 y 位小数的结果， TRUNCATE 知识截断，并不是四舍五入。

• RAND() ：返回 0 到 1 的随机值

日期和时间函数

日期和时间函数也是 MySQL 中非常重要的一部分，下面我们就来一起认识一下这些函数

函数功能NOW返回当前的日期和时间WEEK返回一年中的第几周YEAR返回日期的年份HOUR返回小时值MINUTE返回分钟值MONTHNAME返回月份名CURDATE返回当前日期CURTIME返回当前时间UNIX_TIMESTAMP返回日期 UNIX 时间戳DATE_FORMAT返回按照字符串格式化的日期FROM_UNIXTIME返回 UNIX 时间戳的日期值DATE_ADD返回日期时间 + 上一个时间间隔DATEDIFF返回起始时间和结束时间之间的天数

下面结合示例来讲解一下每个函数的使用

• NOW(): 返回当前的日期和时间

• WEEK(DATE) 和 YEAR(DATE) ：前者返回的是一年中的第几周，后者返回的是给定日期的哪一年

• HOUR(time) 和 MINUTE(time) : 返回给定时间的小时，后者返回给定时间的分钟

• MONTHNAME(date) 函数：返回 date 的英文月份

• CURDATE() 函数：返回当前日期，只包含年月日

• CURTIME() 函数：返回当前时间，只包含时分秒

• UNIX_TIMESTAMP(date) : 返回 UNIX 的时间戳

• FROM_UNIXTIME(date) : 返回 UNIXTIME 时间戳的日期值，和 UNIX_TIMESTAMP 相反

• DATE_FORMAT(date,fmt) 函数：按照字符串 fmt 对 date 进行格式化，格式化后按照指定日期格式显示

具体的日期格式可以参考这篇文章 https://blog.csdn.net/weixin_38703170/article/details/82177837

我们演示一下将当前日期显示为「年月日」的这种形式，使用的日期格式是 「%M %D %Y」。

• DATE_ADD(date, interval, expr type) 函数：返回与所给日期 date 相差 interval 时间段的日期

interval 表示间隔类型的关键字，expr 是表达式，这个表达式对应后面的类型，type 是间隔类型，MySQL 提供了 13 种时间间隔类型

表达式类型描述格式YEAR年YYMONTH月MMDAY日DDHOUR小时hhMINUTE分mmSECOND秒ssYEAR_MONTH年和月YY-MMDAY_HOUR日和小时DD hhDAY_MINUTE日和分钟DD hh : mmDAY_SECOND日和秒DD hh ：mm ：ssHOUR_MINUTE小时和分hh:mmHOUR_SECOND小时和秒hh:ssMINUTE_SECOND分钟和秒mm:ss

• DATE_DIFF(date1, date2) 用来计算两个日期之间相差的天数

查看离 2021 - 01 - 01 还有多少天

流程函数

流程函数也是很常用的一类函数，用户可以使用这类函数在 SQL 中实现条件选择。这样做能够提高查询效率。下表列出了这些流程函数

函数功能IF(value,t f)如果 value 是真，返回 t；否则返回 fIFNULL(value1,value2)如果 value1 不为 NULL，返回 value1，否则返回 value2。CASE WHEN[value1] THEN[result1] ...ELSE[default] END如果 value1 是真，返回 result1，否则返回 defaultCASE[expr] WHEN[value1] THEN [result1]... ELSE[default] END如果 expr 等于 value1， 返回 result1， 否则返回 default

其他函数

除了我们介绍过的字符串函数、日期和时间函数、流程函数，还有一些函数并不属于上面三类函数，它们是

函数功能VERSION返回当前数据库的版本DATABASE返回当前数据库名USER返回当前登陆用户名PASSWORD返回字符串的加密版本MD5返回 MD5 值INET_ATON(IP)返回 IP 地址的数字表示INET_NTOA(num)返回数字代表的 IP 地址

下面来看一下具体的使用

• VERSION: 返回当前数据库版本

• DATABASE: 返回当前的数据库名

• USER : 返回当前登录用户名

• PASSWORD(str) : 返回字符串的加密版本，例如

• MD5(str) 函数：返回字符串 str 的 MD5 值

• INET_ATON(IP): 返回 IP 的网络字节序列

• INET_NTOA(num)函数：返回网络字节序列代表的 IP 地址，与 INET_ATON 相对

end