MySQL--表操作(innodb表字段数据类型、约束条件)、sql_mode操作
一、创建表的完整语法
#[]内的可有可无,即创建表时字段名和类型是必须填写的,宽度与约束条件是可选择填写的。
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#解释:
类型:使用限制字段必须以什么样的数据类型传值
约束条件:约束条件是在类型之外添加一种额外的限制
类型:使用限制字段必须以什么样的数据类型传值
约束条件:约束条件是在类型之外添加一种额外的限制
# 注意:
a、在同一张表中,字段名是不能相同
b、宽度和约束条件可选,字段名和类型是必须的
c、最后一个字段后不加逗号
a、在同一张表中,字段名是不能相同
b、宽度和约束条件可选,字段名和类型是必须的
c、最后一个字段后不加逗号
二、基本数据类型之整型
1、作用:id号,各种号码,年龄,等级
1、作用:id号,各种号码,年龄,等级
2、分类:
tinyint(**): 1个字节,有符号可表示(-128,127),无符号可表示(0,255)
int (*****): int为最常用类型;4个字节
bigint(***): 8个字节
tinyint(**): 1个字节,有符号可表示(-128,127),无符号可表示(0,255)
int (*****): int为最常用类型;4个字节
bigint(***): 8个字节
3、默认整型都是有符号的
create table t1(x tinyint);
insert into t1 values(128),(-129); #默认整型在非严格模式下,超出范围不报错,存成127和-128
create table t1(x tinyint);
insert into t1 values(128),(-129); #默认整型在非严格模式下,超出范围不报错,存成127和-128
create table t2(x tinyint unsigned);#约束条件
insert into t2 values(-1),(256); #无符号整型在非严格模式下,超出范围不报错,存成0和255
insert into t2 values(-1),(256); #无符号整型在非严格模式下,超出范围不报错,存成0和255
create table t3(x int unsigned);
insert into t3 values(4294967296); #在非严格模式下,超出范围不报错,存成4294967295
insert into t3 values(4294967296); #在非严格模式下,超出范围不报错,存成4294967295
create table t4(x int(12) unsigned);
insert into t4 values(4294967296123); #对于整型来说宽度不是存储限制,这里的宽度是显示宽度。
insert into t4 values(4294967296123); #对于整型来说宽度不是存储限制,这里的宽度是显示宽度。
4、强调:对于整型来说,数据类型后的宽度并不是存储限制,而是显示限制。所以在创建表示,如果字段采用的是整型类型,完全无需指定显示宽度,默认的显示宽度,足够显示完整当初存放的数据。
对于其他类型,宽度为存储限制。
create table t5(x int(8) unsigned zerofill); # 显示时,不够8位用0填充,如果超出8位则正常显示; zerofill只能用于数字模式(如int)
insert into t5 values(4294967296123); # 13位在严格模式下,报错
insert into t5 values(1); # 00000001
对于其他类型,宽度为存储限制。
create table t5(x int(8) unsigned zerofill); # 显示时,不够8位用0填充,如果超出8位则正常显示; zerofill只能用于数字模式(如int)
insert into t5 values(4294967296123); # 13位在严格模式下,报错
insert into t5 values(1); # 00000001
三、补充sql_mode
a、查看sql_mode
mysql> show variables like "%sql_mode%";
+----------------------------+---------------------+
| Variable_name | Value |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.00 sec)
a、查看sql_mode
mysql> show variables like "%sql_mode%";
+----------------------------+---------------------+
| Variable_name | Value |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.00 sec)
b、修改sql_mode为严格模式:在该模式下,如果插入的数据超过限制,则会立即报错
mysql> set global sql_mode="strict_trans_tables"; #global为全局,session为当前
mysql> set global sql_mode="strict_trans_tables"; #global为全局,session为当前
四、浮点型
1、作用:存储身高、体重、薪资
1、作用:存储身高、体重、薪资
2、分类:
float (*****):最为常用 FLOAT(M,D) M:数字个数 max 255, D:小数位个数 max 30
double (**):DOUBLE(M,D) M:数字个数 max 255, D:小数位个数 max 30
decimal (**):DECIMAL(M,D) M:数字个数 max 65, D:小数位个数 max 30 (即整数位个数 max 35)
float (*****):最为常用 FLOAT(M,D) M:数字个数 max 255, D:小数位个数 max 30
double (**):DOUBLE(M,D) M:数字个数 max 255, D:小数位个数 max 30
decimal (**):DECIMAL(M,D) M:数字个数 max 65, D:小数位个数 max 30 (即整数位个数 max 35)
3、相同点
a、对于三者来说,都能存放30位小数,
a、对于三者来说,都能存放30位小数,
4、不同点:
a、精度的排序从低到高:float,double,decimal
b、float与double类型能存放的整数位比decimal更多
a、精度的排序从低到高:float,double,decimal
b、float与double类型能存放的整数位比decimal更多
create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));
insert into t9 values(1.111111111111111111111111111111); # 1.111111164093017600000000000000
insert into t10 values(1.111111111111111111111111111111); # 1.111111111111111200000000000000
insert into t11 values(1.111111111111111111111111111111); # 1.111111111111111111111111111111
create table t10(x double(255,30));
create table t11(x decimal(65,30));
insert into t9 values(1.111111111111111111111111111111); # 1.111111164093017600000000000000
insert into t10 values(1.111111111111111111111111111111); # 1.111111111111111200000000000000
insert into t11 values(1.111111111111111111111111111111); # 1.111111111111111111111111111111
五、字符类型
1、作用:姓名,地址,描述类的信息
1、作用:姓名,地址,描述类的信息
2、分类:
char: 定长
varchar: 变长
#推荐使用char;不推荐混用,如果混用需定长在前、变长在后。
char: 定长
varchar: 变长
#推荐使用char;不推荐混用,如果混用需定长在前、变长在后。
3、字符的宽度限制单位是字符个数
create table t12(x char(4)); # 超出4个字符则报错,不够4个字符则用空格补全成4个字符
create table t13(y varchar(4)); # 超出4个字符则报错,不够4个字符那么字符有几个就存几个
create table t12(x char(4)); # 超出4个字符则报错,不够4个字符则用空格补全成4个字符
create table t13(y varchar(4)); # 超出4个字符则报错,不够4个字符那么字符有几个就存几个
insert into t12 values('hello'); #报错
insert into t13 values('hello'); #报错
insert into t13 values('hello'); #报错
insert into t12 values('a'); #'a '
insert into t13 values('a'); #'a'
insert into t13 values('a'); #'a'
select char_length(x) from t12; #1 #在mysql中进行了处理,即使硬盘中已存入'a ',在此查看仍是1个字符,需要做以下设置方可还原真实效果
select char_length(y) from t13; #1
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; #在屏蔽mysql中的处理,还原真实效果
select char_length(x) from t12; #4
select char_length(y) from t13; #1
select char_length(y) from t13; #1
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; #在屏蔽mysql中的处理,还原真实效果
select char_length(x) from t12; #4
select char_length(y) from t13; #1
4、针对char类型,mysql在存储时会将数据用空格补全存放到硬盘中。但会在读出结果时自动去掉末尾的空格,因为末尾的空格在以下场景中是无用。
mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值,该值后填充空格是没有用; 即能搜索到
mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值,该值后填充空格是有用的: 即搜索不到,需搜索"lxx__", 或"lxx ",或"lxx%"
mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值,该值后填充空格是没有用; 即能搜索到
mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值,该值后填充空格是有用的: 即搜索不到,需搜索"lxx__", 或"lxx ",或"lxx%"
5、模糊匹配 like (以下非正则)
%: 任意长度的任意字符
_: 任意一个字符
%: 任意长度的任意字符
_: 任意一个字符
6、对比char与varchar
name char(5)
# 缺点:浪费空间
# 优点:存取速度都快
egon alex lxx wxx yx
name char(5)
# 缺点:浪费空间
# 优点:存取速度都快
egon alex lxx wxx yx
name varchar(5)
# 缺点:存取速度都慢
# 优点:节省空间(it depends); 需要一个头(1-2个字节)存长度 # 2bytes可存65535个字符,mysql中所有字符串(char)中字符数皆不超过65535
(1bytes+egon)(1bytes+alex)(1bytes+lxx)
# 缺点:存取速度都慢
# 优点:节省空间(it depends); 需要一个头(1-2个字节)存长度 # 2bytes可存65535个字符,mysql中所有字符串(char)中字符数皆不超过65535
(1bytes+egon)(1bytes+alex)(1bytes+lxx)
6、日期类型
1、作用:时间相关
1、作用:时间相关
2、分类:
date:1999-01-27
time: 11:11:11
datetime:1999-01-27 11:11:11
year:1999
date:1999-01-27
time: 11:11:11
datetime:1999-01-27 11:11:11
year:1999
3、
create table student(
id int,
name char(16),
born_year year,
birth date,
class_time time,
reg_time datetime
);
create table student(
id int,
name char(16),
born_year year,
birth date,
class_time time,
reg_time datetime
);
insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');
七、枚举与分类
1、作用与分类:
枚举enum,多选一个
集合set,多选多
1、作用与分类:
枚举enum,多选一个
集合set,多选多
2、
create table teacher(
id int,
name char(16),
sex enum('male','female','others'),
hobbies set('play','read','music','piao')
);
create table teacher(
id int,
name char(16),
sex enum('male','female','others'),
hobbies set('play','read','music','piao')
);
八、not null + default
# 默认null (可传空)
# 默认null (可传空)
create table t15(
id int,
name char(16) not null,
sex enum('male','female','other') not null default "male" # 常用用法: not null 和 default连用
);
id int,
name char(16) not null,
sex enum('male','female','other') not null default "male" # 常用用法: not null 和 default连用
);
insert into t15(id,name) values #另一种传值方法
(1,'egon1'),
(2,'egon2'),
(3,'egon3');
(1,'egon1'),
(2,'egon2'),
(3,'egon3');
九、unique: 限制字段的值唯一(约束); 加速查询
1、单列唯一
create table t16(
id int unique, #key: PRI
name char(16)
);
1、单列唯一
create table t16(
id int unique, #key: PRI
name char(16)
);
2、联合唯一
create table server(
id int unique,
ip char(15),
port int,
unique(ip,port) #ip可以重复,port可以重复,ip+port不可重复
);
create table server(
id int unique,
ip char(15),
port int,
unique(ip,port) #ip可以重复,port可以重复,ip+port不可重复
);
十、primary key: primary key就等同于not null unique(约束);加速查询;innodb 用主键(一种索引)字段为依据组织数据,形成一种树型结构,从而加速查询
1、强调(******)
a、一张表中必须有,并且只能有一个主键
#如果没有主动指定primary key,从上至下把找到的第一个not null unique变成primary key
#如果没有主动指定primary key,且没有not null unique,默认一个隐藏字段(7bytes)为主键,并根据其组织数据结构;进而丧失了加速查询优势
1、强调(******)
a、一张表中必须有,并且只能有一个主键
#如果没有主动指定primary key,从上至下把找到的第一个not null unique变成primary key
#如果没有主动指定primary key,且没有not null unique,默认一个隐藏字段(7bytes)为主键,并根据其组织数据结构;进而丧失了加速查询优势
b、一张表中都应该有一个id字段,而且应该把id字段做成主键
create table t17(
id int primary key, #key:PRI
name char(16),
age int,
sex char(6)
)engine=innodb;
id int primary key, #key:PRI
name char(16),
age int,
sex char(6)
)engine=innodb;
2、联合主键 (联合主键可看作一个主键)
create table t19(
ip char(15),
port int,
primary key(ip,port)
);
create table t19(
ip char(15),
port int,
primary key(ip,port)
);
3、primary key auto_increment
a、通常与primary key连用,而且通常是给id字段加
b、auto_incremnt只能给被定义成key(unique key,primary key)的字段加
a、通常与primary key连用,而且通常是给id字段加
b、auto_incremnt只能给被定义成key(unique key,primary key)的字段加
create table t20(
id int primary key auto_increment,
name char(16)
)engine=innodb;
id int primary key auto_increment,
name char(16)
)engine=innodb;
4、总结
key(索引)为mysql中一种特殊的数据结构
a、unique 约束条件,加速查询
b、primary key 约束条件,加速查询,innodb组织数据结构的依据
c、index key 加速查询
d、foreign key 没有加速查询功能
key(索引)为mysql中一种特殊的数据结构
a、unique 约束条件,加速查询
b、primary key 约束条件,加速查询,innodb组织数据结构的依据
c、index key 加速查询
d、foreign key 没有加速查询功能