目录
- 1.关于属性:character set name
- 1.2、创建表的时候,可以指明表的字符集
- 1.3、创建表,指明表中的字段时,可以指定字段的字符集
- 2.整型数据类型
- 3.浮点类型
- 4. 定点数类型
- 5. 位类型:BIT
- 6 日期与时间类型
- 6.1 YEAR类型(建议写成四位的)
- 6.2 DATE类型
- 6.3 TIME类型
- 6.4 DATETIME类型
- 6.5 TIMESTAMP类型
- 7.文本字符串类型
- 7.1 CHAR类型
- 7.2 VARCHAR类型
- 7.3 TEXT类型
- 8. ENUM类型
- 9. SET类型
- 10二进制字符串类型
- 10.1 BINARY 与 VARBINARY类型
- 10.2 Blob类型
- 11. JSON类型
本章的内容测试建议使用 MySQL5.7进行测试。(因为会显示数据的宽度)
(添加的数据的范围,不能超过数据的宽度,并且要在取值范围内)
1.关于属性:character set name
SHOW VARIABLES LIKE 'character_%';
# 1.1、创建数据库时指名字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';
SHOW CREATE DATABASE dbtest12;
USE dbtest12
1.2、创建表的时候,可以指明表的字符集
CREATE TABLE temp(
id INT
) CHARACTER SET 'utf8';
SHOW CREATE TABLE temp;
1.3、创建表,指明表中的字段时,可以指定字段的字符集
CREATE TABLE temp1(
id INT,
NAME VARCHAR(15) CHARACTER SET 'gbk'
);
SHOW CREATE TABLE temp1;
/*
字段如果没有声明字符集,那就按照所在表的字符集,
表如果没有声明字符集,那就按照所在数据库的字符集
数据库也没有指明,那么默认的字符集在my.ini配置文件中了
一般在创建数据库的时候指明字符集。
*/
2.整型数据类型
#(具体解释看pdf)
#(具体解释看pdf)
#(具体解释看pdf)
#整数类型的默认显示宽度与其有符号数的最小值的宽度相同。如:(-128~127)4位
USE dbtest12;
CREATE TABLE test_int1(
f1 TINYINT,
f2 SMALLINT,
f3 MEDIUMINT,
f4 INTEGER,
f5 BIGINT
);
DESC test_int1;
INSERT INTO test_int1(f1)
VALUES(12),(-12),(-128),(127);
SELECT * FROM test_int1;
#Out of range value for column 'f1' at row 1
INSERT INTO test_int1(f1)
VALUES(128);
CREATE TABLE test_int2(
f1 INT,
f2 INT(5),#这种写法没有意义,下边的写法有意义
f3 INT(5) ZEROFILL #① 显示宽度为5。当insert的值不足5位时,使用0填充。
#②当使用ZEROFILL时,自动会添加UNSIGNED(通过SHOW CREATE TABLE test_int2;查看)
)
INSERT INTO test_int2(f1,f2)
VALUES(123,123),(123456,123456);
SELECT * FROM test_int2;
INSERT INTO test_int2(f3)
VALUES(123),(123456);
SHOW CREATE TABLE test_int2;
CREATE TABLE test_int3(
f1 INT UNSIGNED
);
DESC test_int3;
INSERT INTO test_int3
VALUES(2412321);
#Out of range value for column 'f1' at row 1
INSERT INTO test_int3
VALUES(4294967296);
SELECT * FROM test_int3
3.浮点类型
CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
);
DESC test_double1;
INSERT INTO test_double1(f1,f2)
VALUES(123.45,123.45);
SELECT * FROM test_double1;
INSERT INTO test_double1(f3,f4)
VALUES(123.45,123.456); #存在四舍五入
#Out of range value for column 'f4' at row 1
INSERT INTO test_double1(f3,f4)
VALUES(123.45,1234.456);
#Out of range value for column 'f4' at row 1
INSERT INTO test_double1(f3,f4)
VALUES(123.45,999.995);
#测试FLOAT和DOUBLE的精度问题
CREATE TABLE test_double2(
f1 DOUBLE
);
INSERT INTO test_double2
VALUES(0.47),(0.44),(0.19);
SELECT SUM(f1)
FROM test_double2;
SELECT SUM(f1) = 1.1,1.1 = 1.1
FROM test_double2;
4. 定点数类型
CREATE TABLE test_decimal1(
f1 DECIMAL,
f2 DECIMAL(5,2)
);
DESC test_decimal1;
INSERT INTO test_decimal1(f1)
VALUES(123),(123.45);#小数部分四舍五入
SELECT * FROM test_decimal1;
INSERT INTO test_decimal1(f2)
VALUES(999.99);
INSERT INTO test_decimal1(f2)
VALUES(67.567);#存在四舍五入
#Out of range value for column 'f2' at row 1
INSERT INTO test_decimal1(f2)
VALUES(1267.567);
#Out of range value for column 'f2' at row 1
INSERT INTO test_decimal1(f2)
VALUES(999.995);
#演示DECIMAL替换DOUBLE,体现精度
SELECT * FROM test_double2
ALTER TABLE test_double2
MODIFY f1 DECIMAL(5,2);
DESC test_double2;
SELECT SUM(f1)
FROM test_double2;
SELECT SUM(f1) = 1.1,1.1 = 1.1
FROM test_double2;
5. 位类型:BIT
CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
DESC test_bit1;
INSERT INTO test_bit1(f1)
VALUES(0),(1);
SELECT *
FROM test_bit1;
#Data too long for column 'f1' at row 1
INSERT INTO test_bit1(f1)
VALUES(2);#此为十进制数,转化成二进制是10,超出范围
INSERT INTO test_bit1(f2)
VALUES(31);
#Data too long for column 'f2' at row 1
INSERT INTO test_bit1(f2)
VALUES(32);
#bin二进制,hex十六进制(命令行里默认是十六进制进行显示的)
SELECT BIN(f1),BIN(f2),HEX(f1),HEX(f2)
FROM test_bit1;
#此时+0以后,可以以十进制的方式显示数据(相当于和十进制做运算默认转化成十进制了)
SELECT f1 + 0, f2 + 0
FROM test_bit1;
USE dbtest12
6 日期与时间类型
6.1 YEAR类型(建议写成四位的)
CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);
DESC test_year;
INSERT INTO test_year(f1)
VALUES('2021'),(2022);#(2022)有隐式的转换。
SELECT * FROM test_year;
INSERT INTO test_year(f1)
VALUES ('2155');
#Out of range value for column 'f1' at row 1
INSERT INTO test_year(f1)
VALUES ('2156');
INSERT INTO test_year(f1)
VALUES ('69'),('70');
INSERT INTO test_year(f1)
VALUES (0),('00');
/*
注:
1、
INSERT INTO year_test
VALUES
(0),#0000
(00),#0000
('0'),#2000
('00');#2000
*/
6.2 DATE类型
CREATE TABLE test_date1(
f1 DATE
);
DESC test_date1;
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301); #存在隐式转换
INSERT INTO test_date1
VALUES (CURDATE()),(CURRENT_DATE()),(NOW());
SELECT * FROM test_date1;
6.3 TIME类型
CREATE TABLE test_time1(
f1 TIME
);
DESC test_time1;
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME()),(CURTIME());
SELECT * FROM test_time1;
6.4 DATETIME类型
CREATE TABLE test_datetime1(
dt DATETIME
);
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW()),(SYSDATE());
SELECT * FROM test_datetime1;
6.5 TIMESTAMP类型
CREATE TABLE test_timestamp1(
ts TIMESTAMP
);
INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'), ('990101030405');
INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');
SELECT * FROM test_timestamp1;
#对比DATETIME 和 TIMESTAMP
CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);
INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());
SELECT * FROM temp_time;
#修改当前的时区(我们在东八区,即加了个八)
SET time_zone = '+9:00';
SELECT * FROM temp_time;
/*
存储数据的时候需要对当前时间所在的时区进行转换(在底层转换成毫秒数),
查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,
在不同的时区查询时会显示不同的时间。
*/
#获取时间戳。
SELECT UNIX_TIMESTAMP();
7.文本字符串类型
7.1 CHAR类型
CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);
DESC test_char1;
INSERT INTO test_char1(c1)
VALUES('a');
SELECT * FROM test_char1;
#Data too long for column 'c1' at row 1
INSERT INTO test_char1(c1)
VALUES('ab');
INSERT INTO test_char1(c2)
VALUES('ab');
INSERT INTO test_char1(c2)
VALUES('hello');
INSERT INTO test_char1(c2)
VALUES('尚');
INSERT INTO test_char1(c2)
VALUES('硅谷');
INSERT INTO test_char1(c2)
VALUES(');
#Data too long for column 'c2' at row 1
INSERT INTO test_char1(c2)
VALUES('); # 超出了字符规定长度
SELECT * FROM test_char1;
SELECT CONCAT(c2,'***')
FROM test_char1;
INSERT INTO test_char1(c2)
VALUES('ab ');#是不能看到空格的,这种情况会认为是系统自动添加的空格。
SELECT CHAR_LENGTH(c2)
FROM test_char1;
7.2 VARCHAR类型
CREATE TABLE test_varchar1(
NAME VARCHAR #错误,必须指定 长度M,
);
#Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
CREATE TABLE test_varchar2(
NAME VARCHAR(65535)
);
CREATE TABLE test_varchar3(
NAME VARCHAR(5)
);
INSERT INTO test_varchar3
VALUES(''),('');
#Data too long for column 'NAME' at row 1
INSERT INTO test_varchar3
VALUES('');
SELECT * FROM test_varchar3
/*
注:1、char : M * (一个字符所占的字节数)
varchar : (实际的字符个数)*(一个字符所占的字节数)+1(用于记录字符串的长度)
2、MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字
节) ;MySQL5.0版本以上,varchar(20):指的是20字符;
以上情况也适用于 char 类型
*/
7.3 TEXT类型
CREATE TABLE test_text(
tx TEXT
);
INSERT INTO test_text
VALUES('atguigu ');
SELECT CHAR_LENGTH(tx)
FROM test_text; #10
8. ENUM类型
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋');
SELECT * FROM test_enum;
#Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('春,秋');
#Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('人');
INSERT INTO test_enum
VALUES('unknow');
#忽略大小写的
INSERT INTO test_enum
VALUES('UNKNOW');
#可以使用索引进行枚举元素的调用
INSERT INTO test_enum
VALUES(1),('3');
# 没有限制非空的情况下,可以添加null值
INSERT INTO test_enum
VALUES (NULL);
9. SET类型
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('男','睡觉,打豆豆');
SELECT *
FROM temp_mul;
#Data truncated for column 'gender' at row 1
INSERT INTO temp_mul
VALUES('男,女','睡觉,打豆豆');
10二进制字符串类型
10.1 BINARY 与 VARBINARY类型
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
#f3 VARBINARY, #需要指明长度。
f4 VARBINARY(10)
);
DESC test_binary1;
INSERT INTO test_binary1(f1,f2)
VALUES('a','abc');
SELECT * FROM test_binary1;
#Data too long for column 'f1' at row 1
INSERT INTO test_binary1(f1)
VALUES('ab');
INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');
SELECT LENGTH(f2),LENGTH(f4)
FROM test_binary1;
#对于f4,存‘ab’,length显式2,但是实际占用的空间是2+1=3字节
#这个1是用来记录一下存了几个。
10.2 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 * FROM test_json;
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city
FROM test_json;