MySQL 十二章-MySQL数据类型精讲

#关于属性 ： character set name
#创建数据库试指明字符集

CREATE DATABASE IF NOT EXISTS dbtest2 CHARACTER SET 'utf8';

SHOW CREATE DATABASE dbtest2;

#创建表的时候，指明表的字符集

CREATE TABLE temp(id INT)CHARACTER SET 'utf8';

SHOW CREATE TABLE temp;

#创建表，指明表中的字段时，可以指定字符的字符集

CREATE TABLE temp1(id INT,`name` VARCHAR(15) CHARACTER SET 'utf8');

SHOW CREATE TABLE temp1;

#整数类型

USE dbtest12;
CREATE TABLE test_lint1(f1 TINYINT,f2 SMALLINT,f3 MEDIUMINT,f4 INTEGER,f5 BIGINT);

DESC test_lint1;

#2.2可选属性
#在MySQL5.7 中类型后可以添加可选属性 在可选属性后加上 ZEROFILL 才会补全#添加后输出的字段长度如果不足可选属性，则会补全，在前面加 0
#如果输出字符长度大于可选属性但是小于数据类型字段，输出则不发生改变

CREATE TABLE test_int2(f1 INT,f2 INT(5),f3 INT(5) ZEROFILL  );

INSERT INTO test_int2(f3)VALUES(123),(123456);

SELECT * FROM test_int2;

INSERT INTO test_int2(f1,f2)VALUES
(123,123),(123456,123456);

SELECT * FROM test_int2;

#2.3 UNSIGNED 无符号属性 没有负号

CREATE TABLE test_int3(f1 INT UNSIGNED );

INSERT TO test_int3VALUES (1234567890);

#3.浮点类型
#3.1 精度问题
#FLOAT(M,D),DOUBLE(M,D) M称为精度，D称为标度
#存在四舍五入

CREATE TABLE test_double(f1 FLOAT,f2 FLOAT(5,2),f3 DOUBLE,f4 DOUBLE(5,2));

INSERT INTO test_double(f1,f2)VALUES(123.23,231.32);

SELECT * FROM test_double;

INSERT INTO test_double(f3,f4)VALUES(123.456,456.135),(22,321);

#测试FLOAT和DOUBLE的精度问题

CREATE TABLE test_double2(f1 DOUBLE);

INSERT INTO test_double2VALUES
(0.47),(0.44),(0.19);

#会存在精度问题，输出不准确

SELECT SUM(f1) FROM test_double2;

#因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等

SELECT SUM(f1) = 1.1,1.1=1.1FROM test_double2;

#4。定点数类型 内部是以字符串存储#DECIMAL(M,D)的最大取值范围与DOUBLE类型一样

CREATE TABLE test_decimal1(f1 DECIMAL,f2 DECIMAL(5,2) );

#存在四舍五入

INSERT INTO test_decimal1(f1)VALUES
(23),(12.65);

SELECT * FROM test_decimal1;

INSERT INTO test_decimal1(f2)VALUES
(23),(12.651);

#测试DECIMAL精度问题

INSERT INTO test_decimal1(f2)VALUES
(0.47),(0.44),(0.19);

#不存在精度问题

SELECT SUM(f2) FROM test_decimal1;

#5.位类型 BIT(M)
#BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。
#这里(M)是表示二进制的位数，位数最小值为1，最大值为64。

CREATE TABLE test_bit1(f1 BIT,f2 BIT(5),f3 BIT(64) );

INSERT INTO test_bit1(f1)VALUES(0),(1);

SELECT * FROM test_bit1;

#BIN输出为2进制 HEX输出为16进制

SELECT BIN(f1),BIN(f2),HEX(f1),HEX(f2)FROM test_bit1;

#此时加 0 以后，可以显示十进制的方式显示数据

SELECT f1+0,f2+0FROM test_bit1;

#日期与时间类型#YEAR类型
#以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
#以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
#当取值为01到69时，表示2001到2069；
#当取值为70到99时，表示1970到1999；
#当取值整数的0或00添加的话，那么是0000年；
#当取值是日期/字符串的’0’添加的话，是2000年。
#6.1YEAR类型

CREATE TABLE test_year(f1 YEAR,f2 YEAR(4) );

DESC test_year;

INSERT INTO test_year(f1)VALUES('2001');

INSERT INTO test_year(f1)VALUES(1900),(2156);

SELECT * FROM test_year;

#6.2 DATE类型#以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
#以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
#使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期

CREATE TABLE test_date1(f1 DATE );

INSERT INTO test_date1VALUES 
('2020-10-01'), 
('20201001'),(20201001);

INSERT INTO test_date1VALUES 
('00-01-01'), ('000101'), ('69-10-01'), 
('691001'), ('70-01-01'), ('700101'), 
('99-01-01'), ('990101');

INSERT INTO test_date1VALUES (CURRENT_DATE()), (NOW());

SELECT * FROM test_date1;

#6.3 TIME类型
#(1)可以使用带有冒号的字符串，比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM ‘、’ D HH:MM ‘、’ D HH ‘或’ SS ‘格式，都能被正#确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串
#插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串#表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
#（2）可以使用不带有冒号的
#字符串或者数字，格式为’ HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存
#储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示
#00:12:10，而不是12:10:00。
#（3）使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。

CREATE TABLE test_time1(f1 TIME );

INSERT INTO test_time1VALUES
('2 12:30:29'), ('12:35:29'), 
('12:40'), ('2 12:40'),('1 05'), ('45');

INSERT INTO test_time1VALUES 
('123520'), (124011),(1210);

INSERT INTO test_time1VALUES (NOW()), (CURRENT_TIME());

SELECT * FROM test_time1;

#6.4 DATETIME类型
#以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
#以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
#以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，两位
#数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
#使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间。

CREATE TABLE test_datetime1(dt DATETIME );
INSERT INTO test_datetime1VALUES 
('2021-01-01 06:50:30'), ('20210101065030');

INSERT INTO test_datetime1VALUES 
('99-01-01 00:00:00'), ('990101000000'), 
('20-01-01 00:00:00'), ('200101000000');

INSERT INTO test_datetime1VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);

INSERT INTO test_datetime1VALUES (CURRENT_TIMESTAMP()), (NOW());

SELECT * FROM test_datetime1;

#6.5 TIMESTAMP类型
#需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

#存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

CREATE TABLE test_timestamp1(ts TIMESTAMP );INSERT INTO test_timestamp1VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');

INSERT INTO test_timestamp1VALUES (CURRENT_TIMESTAMP()), (NOW());        
                      
#Incorrect datetime value
INSERT INTO test_timestamp1VALUES ('2038-01-20 03:14:07');

SELECT * FROM test_timestamp1;

#6.6 TIMESTAMP和DATETIME的区别：#TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
#底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
#两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
#TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能
#反映出插入时当地的时区，其他时区的人查看数据必然会有误差的

CREATE TABLE temp_time(d1 DATETIME,d2 TIMESTAMP );

INSERT INTO temp_timeVALUES
('2021-9-2 14:45:52','2021-9-2 14:45:52');

INSERT INTO temp_timeVALUES(NOW(),NOW());

SELECT *FROM temp_time;

#修改当前的时区SET time_zone = ‘+9:00’;
#6.6 开发中经验SELECT UNIX_TIMESTAMP();
#7文本字符串类型
#7.1#CHAR类型 字符串需要在要求字符内 空格不保留
#固定长度 0<=M<=255

CREATE TABLE test_char1(c1 CHAR,  #默认添加一个一个字符
c2 CHAR(5));

DESC test_char1;

INSERT INTO test_char1(c1)VALUES('a');

INSERT INTO test_char1(c2)VALUES('hello');

SELECT *FROM test_char1;

#VARCHAR类型
#VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
#MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。

#检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节

CREATE TABLE test_varchar1(NAME VARCHAR #错误
);

#Column length too big for column 'NAME' (max = 21845);
CREATE TABLE test_varchar2(NAME VARCHAR(65535) #错误
);

CREATE TABLE test_varchar3(NAME VARCHAR(5));

INSERT INTO test_varchar3VALUES('尚硅谷'),('尚硅谷教育');

#Data too long for column 'NAME' at row 1
INSERT INTO test_varchar VALUES('尚硅谷IT教育');

#CHAR(M) 固定长度 浪费存储空间效率高 存储不大，速度要求高#VARCHAR(M) 可变长度 节省存储空间 效率低 非CHAR的情况
#7.2 TEXT类型#由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

CREATE TABLE test_text(tx TEXT );

INSERT INTO test_textVALUES('atguigu ');

SELECT CHAR_LENGTH(tx)FROM test_text; 

#8. ENUM类型
#只能选里面的，并且不能同时添加多个

CREATE TABLE test_enum(season ENUM('春','夏','秋','冬','unknow'));

INSERT INTO test_enumVALUES('春'),('秋');

#忽略大小写
INSERT INTO test_enumVALUES('UNKNOW');

#允许按照角标的方式获取指定索引位置的枚举值

INSERT INTO test_enumVALUES('1'),(3);

#Data truncated for column 'season' at row 1
INSERT INTO test_enumVALUES('ab');

#当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的

INSERT INTO test_enumVALUES(NULL);

SELECT * FROM test_enum;

#9. SET类型
#SET类型在存储数据时成员个数越多，其占用的存储空间越大。
#注意：SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同。

CREATE TABLE test_set(s SET ('A', 'B', 'C'));

INSERT INTO test_set (s)VALUES ('A'), ('A,B');

#插入重复的SET类型成员时，MySQL会自动删除重复的成员

INSERT INTO test_set (s)VALUES ('A,B,C,A');

#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。

INSERT INTO test_set (s)VALUES ('A,B,C,D');

SELECT * FROM test_set;

CREATE TABLE temp_mul(gender ENUM('男','女'),hobby SET('吃饭','睡觉','打豆豆','写代码'));

INSERT INTO temp_mul
VALUES('男','睡觉,打豆豆'); #成功

#Data truncated for column 'gender' at row 1
INSERT INTO temp_mul
VALUES('男,女','睡觉,写代码'); #失败

#Data truncated for column 'gender' at row 1
INSERT INTO temp_mul
VALUES('妖','睡觉,写代码');#失败

INSERT INTO temp_mulVALUES('男','睡觉,写代码,吃饭'); #成功

#10二进制字符串类型#BINARY与VARBINARY类型#BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。

CREATE TABLE test_binary1(f1 BINARY,f2 BINARY(3),
f3 VARBINARY,f4 VARBINARY(10));

INSERT INTO test_binary1(f1,f2)VALUES('a','abc');

INSERT INTO test_binary1(f2)VALUES('a  ');

SELECT LENGTH(f2),f2
FROM test_binary1;

INSERT INTO test_binary1(f4)
VALUES('a      '),('a');

SELECT LENGTH(f4),f4
FROM test_binary1;

SELECT * FROM test_binary1;

#10.2BLOB类型#BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。

CREATE TABLE test_blob1(id INT,img MEDIUMBLOB);

INSERT INTO test_blob1(id)
VALUES(1001);

SELECT * FROM test_blob1;

#11. JSON 类型

CREATE TABLE test_json(js JSON);

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city->FROM test_json;

#13. 小结及选择建议
#整数就使用 INT
#小数就用 DECIMAL(M,D)
#日期与时间 DATETIME