#7.1 char类型
CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);
DESC test_char1;
INSERT INTO test_char1(c1)
VALUES('a');
#Data too long for column 'c1' at row 1,超出长度
INSERT INTO test_char1(c1)
VALUES('ab');
SELECT * FROM test_char1;
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('尚硅谷教育');
#Data too long for column 'c2' at row 1
INSERT INTO test_char1(c2)
VALUES('尚硅谷IT教育');
SELECT CONCAT(c2,'***')
FROM test_char1;
SELECT * 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 #错误,未指明varchar的长度
);
#Column length too big for column 'NAME' (max = 21845); use BLOB or TEXT instead
CREATE TABLE test_varchar2(
NAME VARCHAR(65535) #错误 一个汉字占3个字节 21845x3=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('尚硅谷IT教育');
2.text类型
#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类型
#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类型
#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;
#报错,枚举类型只能选一个
INSERT INTO temp_mul
VALUES('男,女','睡觉,打豆豆');
7.文本字符串类型1 .CHAR与VARCHAR类型#7.1 char类型CREATE TABLE test_char1(c1 CHAR,c2 CHAR(5));DESC test_char1;INSERT INTO test_char1(c1)VALUES('a');#Data too long for column 'c1' at row 1,超出长度INSERT INTO test_char1(c1)VALUES('ab');SELECT * FROM test.