数据库之 基本数据类型
【零】补充
【1】创建语法
- 创建表的字段名唯一,即不能重复
- 创建表必须要写字段名和类型
- 创建表的括号内最后一行不能写逗号
【2】严格模式
(1)查看严格模式
- 语法
show variables like'sql_mode';
select @@sql_mode;
- ONLY_FULL_GROUP_BY:
- 启用了严格的GROUP BY模式,要求在SELECT语句中的GROUP BY子句中的列必须是SELECT列表中的列。避免使用隐式的、非确定性的GROUP BY操作。
- STRICT_TRANS_TABLES:
- 启用了严格的事务表模式,要求在事务中执行的表操作必须符合表的定义,包括插入、更新等。确保事务中的数据满足表的定义。
- NO_ZERO_IN_DATE:
- 禁止使用 ‘0000-00-00’ 作为日期的合法值。当启用时,将拒绝插入或更新日期列中的 ‘0000-00-00’。
- NO_ZERO_DATE:
- 禁止使用 ‘0000-00-00’ 作为日期的默认值。当启用时,将拒绝将 ‘0000-00-00’ 用作日期列的默认值。
- ERROR_FOR_DIVISION_BY_ZERO:
- 当除以零时,产生错误而不是返回 NULL。在数学计算中,遇到除数为零的情况会抛出错误。
- NO_AUTO_CREATE_USER:
- 禁止通过GRANT语句自动创建用户。当启用时,不允许在GRANT语句中创建新用户。
- NO_ENGINE_SUBSTITUTION:
- 如果指定的存储引擎不可用,则拒绝替代为其他存储引擎。当启用时,MySQL 不会自动替代表中的存储引擎。
(2)修改严格模式
1.临时修改
- 当前窗口有效
set session sql_mode= 'STRICT_TRANS_TABLES';
2.全局修改
- 修改配置文件
[mysqld]
# 修改严格模式
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
(3)建议开启严格模式
- 5.7之后的版本默认是开启严格模式的
- 可以减小数据库的负担,不给数据库增加额外的压力
【4】宽度
- 宽度(或长度)通常用于描述字符型数据类型(例如
CHAR
和VARCHAR
)的大小,指定存储的字符个数或字节数。 - 不同的数据类型和定义方式会影响宽度的含义。
(1)char类型
CHAR
类型是一种固定长度的字符串类型。当你定义CHAR
类型时,需要指定宽度,该宽度表示存储的字符个数。- 例如,
CHAR(10)
表示存储固定长度为10的字符串。
(2)VARCHAR 类型
VARCHAR
类型是一种可变长度的字符串类型。当你定义VARCHAR
类型时,需要指定最大宽度,该宽度表示存储的字符个数的最大值。- 例如,
VARCHAR(255)
表示最大存储长度为255的可变长度字符串。
(3)BINARY 和 VARBINARY 类型
- 类似于
CHAR
和VARCHAR
,但是用于存储二进制数据。指定宽度时表示存储的字节数。 - 例如,
BINARY(10)
表示存储固定长度为10字节的二进制数据。
(4)数字类型
- 对于数字类型(如
INT
、DECIMAL
),宽度通常表示显示的宽度,即在查询结果中显示的位数。这不影响存储大小或范围。 - 例如,
DECIMAL(8,2)
表示总共8位,其中有2位是小数部分。
【一】基本数据类型
【1】整型
(1)分类
整数类型 | 字节 | 无符号数的取值范围 | 有符号数的取值范围(默认) |
---|---|---|---|
TINYINT | 1 | 0~255 | -128~127 |
SMALLINT | 2 | 0~65535 | -32768~32767 |
MEDIUMINT | 3 | 0~16777215 | -8388608~8388607 |
INT | 4 | 0~4294967295 | -2147483648~2147483647 |
BIGINT | 8 | 0~18446744073709551615 | -9223372036854774808~9223372036854774807 |
(2)默认有符号
- 默认是有符号的,超出范围会报错
mysql> create table t1 (id1 tinyint); --建表
Query OK, 0 rows affected (0.03 sec)
mysql> insert t1 values(-128); --插入负数
Query OK, 1 row affected (0.01 sec)
mysql> insert t1 values(128); --插入超出范围的数字
ERROR 1264 (22003): Out of range value for column 'id1' at row 1
(3)约束条件 无符号数
- 约束条件,unsigned(无符号数)
mysql> alter table t1 add id2 tinyint unsigned; --添加无符号
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert t1 values(1, 128); --128可以在无符号的范围里面
Query OK, 1 row affected (0.01 sec)
(4)类型后面括号里面的数字的含义
- 显示长度,并不是限制位数
- 如果长度超出,有几位显示几位
mysql> alter table t1 modify id1 int(8); --修改类型并限制显示8位数
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert t1 values(1), (123456789); --添加一个1位数和一个8位数
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1; --查看结果
+-----------+
| id1 |
+-----------+
| -128 |
| 1 |
| 1 |
| 123456789 |
+-----------+
4 rows in set (0.00 sec)
(5)自动补零zerofill
- 自动填充0到指定位数
- 不能为负数
mysql> delete from t1 where id=-128; -- 先把负数删除
Query OK, 1 row affected (0.01 sec)
mysql> alter table t1 modify column id int(8) zerofill; --添加约束条件
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1; --查看结果
+-----------+
| id |
+-----------+
| 00000001 |
| 00000001 |
| 123456789 |
+-----------+
3 rows in set (0.00 sec)
mysql> insert t1 values(-128); --尝试添加负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1
(补充)填充查看
-
左侧填充指定字符到指定位数查看
-
由于填充的可以是字符,所以原数据类型不会影响,可以为负数等
-
超出范围的内容将省略,从右开始往左省
-
语法
select lpad(字段名, 显示长度, '填充字符') from 表名;
- 示例
mysql> select lpad(id, 10, 'x') from t1;
+-------------------+
| lpad(id, 10, 'x') |
+-------------------+
| xx00000001 |
| xx00000001 |
| x123456789 |
+-------------------+
3 rows in set (0.03 sec)
【2】浮点型
(1)分类
-
数据类型 字节数 取值范围 FLOAT 4 -2^128到2 ^128
即-3.40E+38到3.40E+38DOUBLE 8 -2^1024到 2^1024
即-1.79E+308到1.79E+308DECIMAL
定点数设置位数和精度。 65 ~ 30
总共65位数,小数部分不能超过30位 -
decimal
-
存储方式: 使用固定点表示法,即将数字分为整数部分和小数部分,每一部分用固定的位数表示。
-
精度: DECIMAL 提供高精度的十进制算术,不会有浮点数的精度损失。
-
范围: 虽然 DECIMAL 提供了高精度,但是在存储非常大或非常小的数值时,FLOAT 类型可能提供更大的范围。
-
-
float 和 double:
-
存储方式: 使用二进制浮点表示法,可以表示非常大或非常小的数值,但是在表示精确的小数时可能存在精度损失。
-
**精度:**是近似数值的存储方式,可能存在精度损失,尤其在需要高精度的计算时。
-
范围: 类型提供了更大的范围,但是在某些场景下,由于浮点数的特性,可能会导致精度问题。
-
(2)括号内的数字含义
- 无论是decimal还是float和double
- 括号内都是两个数字
- 第一个数字表示总位数,第二个数字表示
- 整数部分不能超出范围,小数部分可以超出但是会导致精度不准确
create table t2(id1 float(8, 3); --创建表,浮点型最多8位,小数最多3位
insert t2 values(12345.789); --插入正常值,可以
insert t2 values(123456.78); --整数部分6位数,报错
ERROR 1264 (22003): Out of range value for column 'id1' at row 1
insert t2 values(12345.7899); --小数部分超出范围,没有报错
mysql> select * from t2; --查看结果
+-----------+
| id1 |
+-----------+
| 12345.789 |
| 12345.790 |
+-----------+
(3)精度查看
- 添加double和decimal
mysql> alter table t2 modify id1 float(65,30); --将原本的8,3该长位65,30
mysql> alter table t2 add id2 double(65, 30); --添加double(65,30)
mysql> alter table t2 add id3 decimal(65,30); --添加decimal(65,30)
- 插入数据,都插入20位的相同小数
mysql> insert t2 values (1.11111111111111111111,
-> 1.11111111111111111111,
-> 1.11111111111111111111);
- 查看结果
mysql> select * from t2;
+--------------------------------------+----------------------------------+----------------------------------+
| id1 | id2 | id3 |
+--------------------------------------+----------------------------------+----------------------------------+
| 12345.789062500000000000000000000000 | NULL | NULL |
| 12345.790039062500000000000000000000 | NULL | NULL |
| 1.110999941825866700000000000000 | NULL | NULL |
| 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111110000000000 |
+--------------------------------------+----------------------------------+----------------------------------+
- 小结:
- 精度:float < double < decimal
- 取值范围double>float>decimal
【3】字符串类型
(1)分类
数据类型 | 字符数 | 含义 |
---|---|---|
char | 默认一个字符 | 定长,超出范围报错 不够自动空格补全 |
varchar | 必须指定字符数 | 变长,超出范围报错 不够,有几个存几个 |
(2)基本演示
- 创建表
mysql> create table t3(
-> name1 char, --可以不写字符个数,默认1
-> name2 char(4), --限制4个字符
-> name3 varchar(4) --必须填写一个数字
-> );
- 插入数据
insert t3 values ('t', 'tom', 'tom'); --超出范围将会报错
- 查看结果
mysql> select * from t3;
+-------+-------+-------+
| name1 | name2 | name3 |
+-------+-------+-------+
| t | tom | tom | --空格我们并不能看出来
+-------+-------+-------+
1 row in set (0.00 sec)
(3)查询长度
- char_length:统计字符串长度
mysql> select char_length(name1), char_length(name2), char_length(name3) from t3;
+--------------------+--------------------+--------------------+
| char_length(name1) | char_length(name2) | char_length(name3) |
+--------------------+--------------------+--------------------+
| 1 | 3 | 3 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
- 为什么char是三位字符呢?不是定长自动补空格吗?
- 因为严格模式的自动踢出了多余的空格
(4)修改严格模式
- 全局修改
# 先查询
show variables like 'sql_mode';
# 在原有的基础上添加
set session sql_mode=concat(@@global.sql_mode, "PAD_CHAR_TO_FULL_LENGTH");
- 再次查看
mysql> select char_length(name1), char_length(name2), char_length(name3) from t3;
+--------------------+--------------------+--------------------+
| char_length(name1) | char_length(name2) | char_length(name3) |
+--------------------+--------------------+--------------------+
| 1 | 4 | 3 |
+--------------------+--------------------+--------------------+
(5)小结
-
char:
- 浪费空间
- 存储简单:直接按照固定字符数存储
-
varchar
- 节省空间
- 存储麻烦:存储数据需要在数据前添加报头(大小)
-
目前更倾向于用varchar
【4】时间类型
(1)分类
数据类型 | 显示样式 |
---|---|
date | 年-月-日 |
time | 时:分:秒 |
datetime | 年-月-日 时:分:秒 |
year | 年,自动补充置四位 |
(2)基本演示
- 创建表
mysql> create table t4(
-> birth_date date,
-> birth_time time,
-> birth_datetime datetime,
-> birth_year year
-> );
- 查看表结构
mysql> desc t4;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| birth_date | date | YES | | NULL | |
| birth_time | time | YES | | NULL | |
| birth_datetime | datetime | YES | | NULL | |
| birth_year | year(4) | YES | | NULL | |
+----------------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
- 插入数据
insert t4 values ('2000-1-1', '12:00:00', '2000-5-10 5:6:20', '00'); --年份简写
insert t4 values ('2000-1-1', '12:00:00', '2000-5-10 5:6:20', '98'); --年份简写
- 查看表数据
mysql> select * from t4;
+------------+------------+---------------------+------------+
| birth_date | birth_time | birth_datetime | birth_year |
+------------+------------+---------------------+------------+
| 2000-01-01 | 12:00:00 | 2000-05-10 05:06:20 | 2000 |
| 2000-01-01 | 12:00:00 | 2000-05-10 05:06:20 | 1998 |
+------------+------------+---------------------+------------+
【5】枚举enum
和集合set
(1)说明
- 枚举是一种字符串对象,只能包含在定义时列举的值。
- 可选内容不能相同
- 必须选值
- 不允许错选
- 只能单选
- 集合是一组字符串对象,可以包含在定义时列举的零个或多个值。
- 可选内容不能相同
- 必须选值
- 不允许错选
- 可以多选
- 逗号隔开不能有空格
(2)示例说明
- 创建枚举和集合表
mysql> create table t5(
-> sex enum('female', 'male'), --性别枚举
-> color set('black', 'green', 'yellow') --颜色集合
-> );
- 查看表结构
mysql> desc t5;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| sex | enum('female','male') | YES | | NULL | |
| color | set('black','green','yellow') | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
- 插入数据
mysql> insert t5 values('female', 'black'), ('male', 'green, black'); --含有空格,导致报错
ERROR 1265 (01000): Data truncated for column 'color' at row 2
mysql> insert t5 values('female', 'black'), ('male', 'green,black'); --正常用逗号隔开
Query OK, 2 rows affected (0.00 sec)
- 查看数据记录
mysql> select * from t5;
+--------+-------------+
| sex | color |
+--------+-------------+
| female | black |
| male | black,green |
+--------+-------------+
2 rows in set (0.01 sec)