MySQL - 常见的数据类型和运算符

最新推荐文章于 2023-10-08 01:20:00 发布

祈望每天自然醒

最新推荐文章于 2023-10-08 01:20:00 发布

阅读量832

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/yeahPeng11/article/details/119486326

版权

MySQL 专栏收录该内容

14 篇文章 7 订阅

订阅专栏

第一部分：MySQL数据类型

概述

在MySQL数据库中，每一条数据都有数据类型，MySQL支持的数据类型主要分成3类：数值类型、字符串（字符）类型、日期和时间类型。数据类型描述使用以下约定：

示例：FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

M表示整数类型的最大显示宽度，对于浮点型和定点型M是存储的总位数，对于字符串类型M表示最大长度。允许的最大M值取决于数据类型。
D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。
[ ] 表示类型定义的可选部分。

一、约束条件

约束条件就是给字段加约束，使该字段存储的值更加符合我们的预期。常用的约束如下：

UNSIGNED ：无符号，值从0开始，无负数。
ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0填充至指定长度,字段会自动添加UNSIGNED。
NOT NULL：非空约束，表示该字段的值不能为空。
DEFAULT：表示如果插入数据时没有给该字段赋值，那么就使用默认值。
PRIMARY KEY：主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。一般都是用来约束id。
AUTO_INCREMENT：自增，只能用于数值列，而且配合索引使用，默认起始值从1开始，每次增长1。
UNIQUE KEY：唯一值，表示该字段下的值不能重复，null除外。比如身份证号一般都会用这个进行约束。
FOREIGN KEY：外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系。

二、数值类型

数字类型总体可分为整型、浮点型和定点型三类。

1.整型

数据类型	取值范围	单位	说明
TINYINT	有符号值：-2⁷~2⁷-1 无符号值：0~2⁸-1	1byte	最小的整数
BIT	0/1（false/ture）	1bit	二进制的一位
BOOL	有符号值：-2⁷~2⁷-1 无符号值：0~2⁸-1	1byte	实际上是TINYINT 插入false/true会存储为0/1
SMALLINT	有符号值：-2¹⁵~2¹⁵-1 无符号值：0~2¹⁶-1	2byte	小型整数
MEDIUMINT	有符号值：-2²³~2²³-1 无符号值：0~2²⁴-1	3byte	中型整数
INT	有符号值：-2³¹~2³¹-1 无符号值：0~2³²-1	4byte	标准整数
BIGINT	有符号值：-2⁶³~2⁶³-1 无符号值：0~2⁶⁴-1	8byte	大型整数

创建格式：

TINYINT[(M)] [UNSIGNED] [ZEROFILL]；
BOOL[(M)] [UNSIGNED] [ZEROFILL]；
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]；
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]；
INT[(M)] [UNSIGNED] [ZEROFILL]；
BIGINT[(M)] [UNSIGNED] [ZEROFILL]。

下面示例分析一下

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ul67YU6L-1628318150490)(images/数值类型分析.png)]

2.浮点型

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数。允许值是-3.402823466E+38 到-1.175494351E-38， 0以及1.175494351E-38 到3.402823466E+38,M是总位数，D是小数点后面的位数。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308，0以及 2.2250738585072014E-308到 1.7976931348623157E+308。M是总位数，D是小数点后面的位数。

3.定点型

DECIMAL[(M,D)] [UNSINED] [ZEROFILL]

存储精确的数值。它的范围的可变的，小数点和（负数） -符号不计入 M，M的范围是1到65。D范围为0到30，且D不得大于M。它可自定义长度，比如定义一个叫amount的列存储6为数值，小数点后2位：

--范围-9999.99~9999.99
amount DECIMAL(6,2)

**当D不写默认为0，M写默认为10，如DECIMAL(M)等价于DECIMAL(M,0)，如DECIMAL等价于DECIMAL(10,0)。**它经常配合ZEROFILL表示货币。DECIMAL也存在存储时精度丢失的问题。

超出范围和溢出处理

当MySQL将值存储在超出列数据类型允许范围的数值列中时，结果取决于当时生效的SQL模式：

严格的SQL模式：MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败。
未启用限制模式：MySQL会将值截断到列数据类型范围的相应端点，并存储结果值，并产生一个警告

在我们的配置文件中可以看到SQL模式的配置，关于SQL模式详情请看SQL模式官方文档(全英文，看不懂看不懂)。

在创建数据表时，能选小的数据类型尽量选择小的数据类型，节省内存的同时还能提高查询效率。

三、字符串类型

字符串类型分为3类：普通的文本字符串类型（CHAR和VARCHAR）、可变类型（TEXT和BLOB）和特殊类型（SET和ENUM）。

1.普通文本字符串类型

[national] CHAR[(M)] [binary|ASCII|unicode](0~255byte)

M表示字符数和字节数，取值范围0~255，存储时始终用空格填充指定长度，M不写默认为1。其中national关键字指定使用的默认字符集，binary指定是否区分大小写（默认区分），ASCII和unicode指定使用字符集。

[national] VARCHAR[(M)] [binary](0~65535byte)

可变长度的字符串，M表示字符最大长度和字节数，范围是0~65535，存储时实际占用**L+1（L<=M，L为实际长度）**个字节。

2.可变类型

类型	最大长度（字节数）	说明
TINYBLOB	2⁸-1	小BLOB字段
TINYTEXT	2⁸-1	小TEXT字段
BLOB	2¹⁶-1	常规的BLOB字段
TEXT	2¹⁶-1	常规的TEXT字段
MEDIUMBLOB	2²⁴-1	中型的BLOB字段
MEDIUTEXT	2²⁴-1	中型的TEXT字段
LONGBLOB	2³²-1	长型的BLOB字段
LONGTEXT	2³²-1	长型的TEXT字段

BLOB类型用于存储二进制文件，图片、音频、视频等。针对于BLOB系列类型的间接点我。

3.特殊类型SET和ENUM

类型	最大值	说明
ENUM(‘value1’,‘vaule2’,…)	65535	字符串对象，该对象可以是选取范围内的一个值或NULL
SET(‘value1’,‘value2’,…)	64	字符串对象，该对象可以是选取范围内的多个值或NULL

下面对于ENUM和SET类型演示一下使用方式：

ENUM

--1.创建数据库enum_db
create table enum_db(
	id int(4) not null primary key auto_increment,
	gender ENUM('男','女','x')
) auto_increment = 1;
--2.插入在范围内的数据
insert into enum_db(gender) values('男');
insert into enum_db(gender) values('x');
insert into enum_db(gender) values('女');
--3.查询表的数据
mysql> select * from enum_db;
+----+--------+
| id | gender |
+----+--------+
|  1 | 男     |
|  2 | x      |
|  3 | 女     |
+----+--------+
3 rows in set (0.00 sec)

SET

--1.创建数据库enum_set
create table set_db(
	id int(4) not null primary key auto_increment,
	options SET('钱','车','房','头发')
) auto_increment = 1;
--2.插入数据
insert into set_db(options) values('钱,车');
insert into set_db(options) values('钱,房');
insert into set_db(options) values('钱,车,房,头发');
--3.查询数据表
mysql> select * from set_db;
+----+--------------------+
| id | options            |
+----+--------------------+
|  1 | 钱,车              |
|  2 | 钱,房              |
|  3 | 钱,车,房,头发      |
+----+--------------------+
3 rows in set (0.00 sec)

看到这里，就再拿出来一点私货物。在创建表时，使用字符串类型时应遵循以下原则：

速度优先，使用CHAR。
节省空间，使用VARCHAR。
将列中的内容限制在一定范围内选择一种，使用ENUM。
将列中的内容限制在一定范围内选择多种，使用SET。
要搜索的内容不区分大小写，使用TEXT。
要搜索的内容区分大小写，使用BLOB。

四、日期时间类型

TIME ：范围是 -838:59:59 到838:59:59，日期格式D HH:mm:SS。D表示天。
DATE ：支持的范围是 1000-01-01到 9999-12-31，日期格式YYYY-MM-DD。
DATETIME ：日期和时间组合，支持范围是 1000-01-01 00:00:00到 9999-12-31 23:59:59。日期格式YYYY-MM-DD HH:mm:SS。
TIMESTAMP ：时间戳，范围是1970-01-01 00:00:00UTC到2037年某个时间。格式YYYY-MM-DD HH:mm:SS。
YEAR ：范围是 1901~2155。格式YYYY。

下面就展示一些示例：

TIME

TIME的范围是 -838:59:59 到838:59:59，所以TIME不仅仅可以表示一天中的时，还可以表示两个时间间隔。

--示例
--1.键表
create table time_db(
	id int(4) not null primary key auto_increment,
	mytime TIME
) auto_increment = 1;

--2.插入数据
--2.1正常时间插入
insert into time_db(mytime) values('21:08:06');
--2.2带天数的时间插入 -2*24-21 = -69h
insert into time_db(mytime) values('-2 21:08:06');
--2.3有一个冒号默认从小时开始算起
insert into time_db(mytime) values('22:08');
--2.4没有冒号没有天数从秒开始算
insert into time_db(mytime) values('30');
--2.5有天数，没有冒号，从小时开始算
insert into time_db(mytime) values('2 10');
--2.6直接使用数值代替也可
insert into time_db(mytime) values(210806);

--3.查看数据
mysql> select * from time_db;
+----+-----------+
| id | mytime    |
+----+-----------+
|  1 | 21:08:06  |
|  2 | -69:08:06 | -- (-2*24-21=-69)
|  3 | 22:08:00  |
|  4 | 00:00:30  |
|  5 | 58:00:00  |
|  6 | 21:08:06  |
+----+-----------+
6 rows in set (0.00 sec)

DATE

格式YYYY-MM-DD

--1.建表
create table data_db(
	id int(4) not null primary key auto_increment,
	mydata DATE
)auto_increment = 1;
--2.插入数据
insert into data_db(mydata) values(20210806);
insert into data_db(mydata) values('2021-06-01');
--3.查询数据
mysql> select * from data_db;
+----+------------+
| id | mydata     |
+----+------------+
|  1 | 2021-08-06 |
|  2 | 2021-06-01 |
+----+------------+
2 rows in set (0.00 sec)

DATETIME

格式YYYY-MM-DD HH:mm:SS

--1.建表
create table datatime_db(
	id int(4) not null primary key auto_increment,
	mydatatime DATETIME
)auto_increment = 1;

--2.插入数据
--2.1直接插入 年月日 时分秒 组合数字
insert into datatime_db(mydatatime) values(20210806210134);
--2.2正常插入
insert into datatime_db(mydatatime) values('2021-08-06 21:01:34');
--2.3没有时分秒默认为00:00:00
insert into datatime_db(mydatatime) values('2021-08-06');

--3.查看数据
mysql> select * from datatime_db;
+----+---------------------+
| id | mydatatime          |
+----+---------------------+
|  1 | 2021-08-06 21:01:34 |
|  2 | 2021-08-06 21:01:34 |
|  3 | 2021-08-06 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

TIMESTAMP

格式YYYY-MM-DD HH:mm:SS，和DATETIME使用差不多，但是范围相对较小

create table timestamp_db(
	id int(4) not null primary key auto_increment,
	mytimestamp TIMESTAMP
)auto_increment = 1;
insert into timestamp_db(mytimestamp) values(20210806210134);
insert into timestamp_db(mytimestamp) values('2021-08-06 21:01:34');
insert into timestamp_db(mytimestamp) values('2021-08-06');
--查询数据
mysql> select * from timestamp_db;
+----+---------------------+
| id | mytimestamp         |
+----+---------------------+
|  1 | 2021-08-06 21:01:34 |
|  2 | 2021-08-06 21:01:34 |
|  3 | 2021-08-06 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

YEAR就不演示了。建表时选择合适的字段非常的重要，尽量减少修改字段。

第二部分：MySQL运算符

MySQL中的运算符分为算术运算符、比较运算符、逻辑运算符和位运算符。

对于常见的运算符就不做使用演示，对于不常见的运算符就做出一些简单的使用演示

一、算术运算符

算术符号	作用
+	加法运算
-	减法运算
*	乘法运算
/	除法运算
%	求余运算
DIV	除法运算，同`/`
MOD	求余运算，同`%`

在除法运算和求余运算中，如果第二个参数是0，结果返回NULL，下面进行简单的使用演示

--1.DIV 查询id为 5 DIV 2的数据（5div2==2）
mysql> SELECT * FROM timestamp_db WHERE id = 5 DIV 2;
+----+---------------------+
| id | mytimestamp         |
+----+---------------------+
|  2 | 2021-08-06 21:01:34 |
+----+---------------------+
1 row in set (0.00 sec)

--2.MOD 查询id为 5 MOD 2的数据（5mod2==1）
mysql> SELECT * FROM timestamp_db where id = 5 MOD 2;
+----+---------------------+
| id | mytimestamp         |
+----+---------------------+
|  1 | 2021-08-06 21:01:34 |
+----+---------------------+
1 row in set (0.00 sec)

--3.如果第二个参数为0，div或mod得到结果为null
mysql> select 1 DIV 0,1 MOD 0;
+---------+---------+
| 1 DIV 0 | 1 MOD 0 |
+---------+---------+
|    NULL |    NULL |
+---------+---------+
1 row in set, 2 warnings (0.00 sec)

二、比较运算符

对于=，>，<，>=，<=这里就不再细说，大家都知道是它们是什么，也知道怎么用，下面详细看一下不常见的或者是数据库中独有的比较运算符：

比较运算符	名称	示例
=	等于	id=2
>	大于	id>2
>=	大于等于	id>=2
<	小于	id<2
<=	小于等于	id<=2
!= 或 <>	不大于	id!=2或id<>2
IS NULL	n/a(Not applicable)	id IS NULL
IS NOT NULL	n/a	id IS NOT NULL
BETWEEN … AND …	n/a	id BETWEEN 2 AND 12
IN	n/a	id IN(1,2,3)
NOT IN	n/a	id NOT IN(1,2,3)
LIKE	模式匹配	name LIKE(‘shi%’)
NOT LIKE	模式匹配	name NOT LIKE(‘shi%’)
REGEXP	正则表达式	name REGEXP ‘a$’

上面的LIKE示例中%做出解释：_表示匹配任意一个字符，%表示匹配任意多个字符。

对REGEXP进行使用演示：

使用REGEXP非常的简单，在正则表达式中**：^匹配字符串开头，$匹配字符串结尾，.匹配字符串中一个字符。且满足条件返回1，不满足条件返回0**。

--1.建表
CREATE TABLE user_db(
	id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(32) NOT NULL
);

--2.插入数据
INSERT INTO user_db(username) VALUES('abc'),('acb'),('bcd'),('bdc'),('cde'),('ced');

--3.使用正则表达式查询数据
--匹配的分别是：开头是a字符串；开头是b的字符串；末尾是c的字符串，字符+c+字符 组成的字符串
mysql> SELECT username,username REGEXP 'a' 开头a,username REGEXP '^b' 开头b,
			username REGEXP 'c$' 末尾c,username REGEXP '.c.' 字符c字符 FROM user_db;
+----------+---------+---------+---------+---------------+
| username | 开头a   | 开头b   | 末尾c   | 字符c字符     |
+----------+---------+---------+---------+---------------+
| abc      |       1 |       0 |       1 |             0 |
| acb      |       1 |       0 |       0 |             1 |
| bcd      |       0 |       1 |       0 |             1 |
| bdc      |       0 |       1 |       1 |             0 |
| cde      |       0 |       0 |       0 |             0 |
| ced      |       0 |       0 |       0 |             0 |
+----------+---------+---------+---------+---------------+
6 rows in set (0.00 sec)

三、逻辑运算符

逻辑运算符和位运算符都有 与(&)、或(|)和异或(^) 等操作，但是位运算符必须把数值先变成二进制在运算，逻辑运算符直接进行运算，只返回true/false(1/0)。

符号	作用
&& 或 AND	与
\|\| 或 OR	或
! 或 NOT（针对一个参数）	非
XOR	异或

使用示例，还是以上面比较运算符的数据库为例查询使用：

--1.查看数据库数据
mysql> SELECT * FROM user_db;
+----+----------+
| id | username |
+----+----------+
|  1 | abc      |
|  2 | acb      |
|  3 | bcd      |
|  4 | bdc      |
|  5 | cde      |
|  6 | ced      |
+----+----------+
6 rows in set (0.00 sec)
--2.进行&& AND,|| OR的演示
--&&和AND左右为真则返回1，||和OR左右有一个为真返回1
mysql> SELECT id,id && 1,id AND 1,id || 1,id OR 1 FROM user_db;
+----+---------+----------+---------+---------+
| id | id && 1 | id AND 1 | id || 1 | id OR 1 |
+----+---------+----------+---------+---------+
|  1 |       1 |        1 |       1 |       1 |
|  2 |       1 |        1 |       1 |       1 |
|  3 |       1 |        1 |       1 |       1 |
|  4 |       1 |        1 |       1 |       1 |
|  5 |       1 |        1 |       1 |       1 |
|  6 |       1 |        1 |       1 |       1 |
+----+---------+----------+---------+---------+
6 rows in set (0.00 sec)

--3.进行! NOR,XOR的使用演示
mysql> SELECT id,!id,NOT id,id XOR 1,id XOR 0 FROM user_db;
+----+-----+--------+----------+----------+
| id | !id | NOT id | id XOR 1 | id XOR 0 |
+----+-----+--------+----------+----------+
|  1 |   0 |      0 |        0 |        1 |
|  2 |   0 |      0 |        0 |        1 |
|  3 |   0 |      0 |        0 |        1 |
|  4 |   0 |      0 |        0 |        1 |
|  5 |   0 |      0 |        0 |        1 |
|  6 |   0 |      0 |        0 |        1 |
+----+-----+--------+----------+----------+
6 rows in set (0.00 sec)

四、位运算符

位运算符是在二进制数上进行计算的运算符，位运算符先将操作数变成二进制数，进行位运算以后，在变为十进制数放回结果。MySQL支持的位运算符有：&,|,~,^,<<,>>。针对于二进制位运算符的分析请点击我。

符号	作用
&	按位与，1与1得1，0与0得0
\|	按位或，1和任何数进行或运算得1，0或0得0
~	按位取反，1取反后变成0，0取反后变成1
^	按位异或，相同的两个数异或得0，不相同的两个数异或得1
<<	按位左移，`m<<n`表示m的二进制数向左移动n位，右边补n个0
>>	按位右移，`m>>n`表示m的二进制数向右移动n位，左边补n个0

五、运算符的优先级

优先级	运算符
最低	=（赋值运算）
	XOR
	&&,AND
	NOT
	BETWEEN、CASE、WHEN、THEN、ELSE
	=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
	&
	<<,>>
	-,+
	*,/,%,
	^
	~
最高	!

祈望每天自然醒

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL - 常见的数据类型和运算符

第一部分：MySQL数据类型概述在MySQL数据库中，每一条数据都有数据类型，MySQL支持的数据类型主要分成3类：数值类型、字符串（字符）类型、日期和时间类型。数据类型描述使用以下约定：示例：FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]M表示整数类型的最大显示宽度，对于浮点型和定点型M是存储的总位数，对于字符串类型M表示最大长度。允许的最大M值取决于数据类型。D适用于浮点和定点类型，并指示小数点后面的位数。最大可能值为30，但不应大于 M-2。[ ] 表示类型定义
复制链接

扫一扫