文章目录
1 数据类型的分类
数据类型本质是一种约束 : 进行插入时如果数据越界(插入了不符合对应数据类型数据范围的数据),sql将直接终止
2 数值类型
tinyint
// 建表,num1有符号,num2无符号
mysql> create table if not exists ttinyint(num1 tinyint,num2 tinyint unsigned);
mysql> desc ttinyint;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| num1 | tinyint(4) | YES | | NULL | |
| num2 | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
mysql> insert into ttinyint (num1) values (-128),(127);// 成功
mysql> insert into ttinyint (num1) values (-129);// 失败
mysql> insert into ttinyint (num1) values (128);// 失败
mysql> insert into ttinyint (num2) values (0),(255);// 成功
mysql> insert into ttinyint (num2) values (256);// 失败
mysql> insert into ttinyint (num2) values (-1);// 失败
mysql> select * from ttinyint;
+------+------+
| num1 | num2 |
+------+------+
| -128 | NULL |
| 127 | NULL |
| NULL | 0 |
| NULL | 255 |
+------+------+
bit
位字段类型,bit[(m)],默认bit则为1位
可以定义bit(1)来存放只有0或1的值,插入2则越界,比如定义性别
mysql> create table tbit(
-> id int,
-> a bit(8)
-> );
mysql> desc tbit;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| a | bit(8) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
// 发现表中显示的是ascii码的对应值(A、\n)
mysql> insert into tbit (id,a) values (65,65);
mysql> insert into tbit (id,a) values (10,10);
mysql> select * from tbit;
+------+------+
| id | a |
+------+------+
| 65 | A |
| 10 |
|
+------+------+
int
整数类型,无符号最大值为232-1=4294967296
float
float[(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节
create table if not exists tfloat(
-> id int,
-> salary float(4,2)
-> )engine=INNODB default charset=utf8mb4;
insert into tfloat (id,salary) values (100,99.99);// 成功
insert into tfloat (id,salary) values (100,99.995);// 失败
insert into tfloat (id,salary) values (100,99.994);// 成功
insert into tfloat (id,salary) values (100,-99.99);
insert into tfloat (id,salary) values (100,-99.995);
insert into tfloat (id,salary) values (100,-99.994);
插入发现,mysql会进行四舍五入操作
decimal
decimal(m, d) [unsigned] : 定点数m指定长度,d表示小数点的位数
语法同float一致,区别在于精度不一致,float精度大约是7位,decimal精度更高,decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略,默认为0.如果m被省略,默认是10
mysql> create table t ( id int, salary float(10,8), salary2 decimal(10,8));
mysql> insert into t values(100,23.12345612, 23.12345612);
mysql> select * from t;
+------+-------------+-------------+
| id | salary | salary2 |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345612 | # 发现decimal的精度更准确,因此如果我们希望某个数据表示高精
度,选择decimal
+------+-------------+-------------+
char
char(L): 固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255
MySQL中一个字母或一个汉字都是一个字符
create table if not exists tchar( id int, name char(2) )engine=INNoDB default charset=utf8;
insert into tchar (id,name) values (100,'ab');
insert into tchar (id,name) values (100,'中国');
mysql> insert into tchar (id,name) values (100,'abb'); // 失败
mysql> insert into tchar (id,name) values (100,'试一试'); //失败
varchar
varchar(L): 可变长度字符串,L表示字符长度(最大),最大长度65535个字节
utf8中一个汉字占一个字符,三个字节;一个字母占一个字符,一个字节
gbk中一个汉字占两个字节
char和varchar的比较
char
定长字符串,直接开辟好对应的空间,适用于身份证、手机号、md5等固定长度的字符串存取
特点:浪费空间,效率高
varchar
变长字符串,在不超过自定义长度范围的情况下,用多少,开辟多少,其中有1-3个字节用于记录数据大小,定义时要保证最长的数据能存入
特点:节约空间,效率低
3 日期和时间类型
常用的日期有如下三个:
date :日期 ‘yyyy-mm-dd’ ,占用三字节
datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ,占用八字节
timestamp :时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致,占用四字节
mysql> create table tdate(t1 date,t2 datetime,t3 timestamp);
// 插入数据时时间戳类型t3默认值为当前时间,更新某行记录时它也会自动更新为修改数据的时间
mysql> desc tdate;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1 | date | YES | | NULL | |
| t2 | datetime | YES | | NULL | |
| t3 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> insert into tdate (t1,t2) values ('2022-01-01','2022-01-01 12:00:00');
mysql> insert into tdate (t1,t2) values ('22/01/01','2022:1:2 12/00/00');// 不受分隔符限制
mysql> select * from tdate;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2022-01-01 | 2022-01-01 12:00:00 | 2022-08-13 18:38:35 |
| 2022-01-01 | 2022-01-02 12:00:00 | 2022-08-13 18:42:34 |
+------------+---------------------+---------------------+
4 String类型
ENUM
枚举,单选
mysql> create table tenum(name char(5),gender enum('男','女','未知'));
mysql> desc tenum;
+--------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+-------+
| name | char(5) | YES | | NULL | |
| gender | enum('男','女','未知') | YES | | NULL | |
+--------+----------------------------+------+-----+---------+-------+
mysql> insert into tenum (name,gender) values('a','男'),
('b','女'),('c','未知');// 成功
mysql> insert into tenum (name,gender) values('a','其他');// 失败
mysql> insert into tenum (name,gender) values('d',1),
('e',2),('f',3);//成功,说明可以用数字对应选项
mysql> select * from tenum;
+------+--------+
| name | gender |
+------+--------+
| a | 男 |
| b | 女 |
| c | 未知 |
| d | 男 |
| e | 女 |
| f | 未知 |
+------+--------+
// 查询时也可以用数字查
mysql> select * from tenum where gender=1;
+------+--------+
| name | gender |
+------+--------+
| a | 男 |
| d | 男 |
+------+--------+
SET
集合,多选
mysql> create table tset(name char(5),hobby set('唱歌','跳舞','游泳','做饭'));
mysql> desc tset;
+-------+------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------------+------+-----+---------+-------+
| name | char(5) | YES | | NULL | |
| hobby | set('唱歌','跳舞','游泳','做饭') | YES | | NULL | |
+-------+------------------------------------------+------+-----+---------+-------+
mysql> insert into tset (name,hobby) values('a','唱歌,跳舞');
// set的选项也可以对应数字,分别是1,2,4,...
mysql> insert into tset (name,hobby) values('b',7);
mysql> select * from tset;;
+------+----------------------+
| name | hobby |
+------+----------------------+
| a | 唱歌,跳舞 |
| b | 唱歌,跳舞,游泳 |
+------+----------------------+
set查询使用“=”时只会匹配完全相等的结果,因此可以采用find_in_set(sub,str_list)函数进行查询
mysql> select * from tset;
+------+----------------------+
| name | hobby |
+------+----------------------+
| a | 唱歌,跳舞 |
| b | 唱歌,跳舞,游泳 |
| c | 跳舞,游泳 |
| d | 做饭 |
| d | 跳舞,做饭 |
+------+----------------------+
mysql> select * from tset where hobby='做饭';
+------+--------+
| name | hobby |
+------+--------+
| d | 做饭 |
+------+--------+
// 多个条件用and和or多次使用函数
mysql> select * from tset where find_in_set('做饭',hobby);
+------+---------------+
| name | hobby |
+------+---------------+
| d | 做饭 |
| d | 跳舞,做饭 |
+------+---------------+