# 数据库之字段类型和约束条件
一.存储引擎
存储引擎是用于根据不同的机制处理不同的数据。
查看mysql中所有引擎:
- show engines;
-
innodb:默认存储引擎,
支持事物,支持行锁,支持外键 -
myisam: MySQL老版本在用的存储引擎
-
blackhole: 黑洞,存任何数据，都会立即消失，相当于经过队列，数据存入就会被取走
-
memory: 内存引擎(数据存放在内存中,断电即消失,即关闭服务端)
举例说明四种不同的存储引擎
- 插入四种不同的存储引擎
- create table t1(id int)engine=innodb; - create table t2(id int)engine=myisam; - create table t3(id int)engine=blackhole; - create table t4(id int)engine=memory;- 插入数据并查表内容
- insert into t1 values(1); select * from t1; - insert into t2 values(1); select * from t2; - insert into t3 values(1); select * from t3; - insert into t4 values(1); select * from t4;这个需要内存中还有，需要关掉服务端才能销毁清除。
-
二.创建表完整的语法
create table 表名(字段名1 字段类型(宽度) 约束条件,字段名2 字段类型(宽度) 约束条件);
# 宽度指对存储数据的限制
create table userinfo(name char);
insert into userinfo values('tank');
'''
1)没有安全模式的数据库版本，能够存放数据但只存存进去一个t。
2)最新数据的版本直接报错提示无法储存: Data too long for column 'name'
'''
# 约束条件非必须,可有可无
例如null和not null
create table t1(id int, name char not null)
# 正常存储
insert into t1 values(1, 'tank');
# 错误存储
insert into t1 values(2, null);
#报错信息:ERROR 1048 (23000): Column 'name' cannot be null
总结:
- 创建表的时候字段名不能重复
create table test(
id int,
id int
);
- 最后一个字段不能在末尾加逗号
create table test(
id int,
age int,
);
- 字段名必须有字段类型与宽度
create table test(
id int,
name char
);
insert into test values(1, 'tank');
alter table test modify name char(4);
insert into test values(2, 'sean');
三.字段类型
- 确定表结构
- 字段与字段类型
补充:设置严格模式
在一些低版本的mysql里面,即时输入的数据不符合规格,也会存入进字段里面,所以可以通过设置严格模式来解决这个问题
# 查看数据库配置中变量名包含mode的配置参数:
show variables like "%mode%";
# 修改安全模式:
set session; # 局部有效，只在你当前操作的窗口有效
set global session; # 全局有效，永久有效
# 修改严格模式,以管理员修改完之后退出当前客户端重新登录即可
set global sql_mode = 'STRICT_TRANS_TABLES';
1)整型
- 分类:tinyint、smallint、mediumint、int、bigint
- 应用场景: 存储年龄,等级,id,号码等
- 典型存储范围
- 验证整型字段有无符号及范围
# TINYINT tinyint范围( -128, 127)
1)默认有正负符号限制
create table t2(id TINYINT);
desc t2;
# 超出限制, 插不进去,低版本设置严格模式后会报错,不设置严格模式进去会被自动修改数值为-128或127
insert into t2 values(-129), (256);
# 默认范围是(-128, 127)
insert into t2 values(-128), (127);
select * from t2;
2) 无正负符号限制: unsigne
create table t3(number tinyint unsigned);
# 输入负号或超出范围都会报错
insert into t3 values(-10);
insert into t3 values(127);
# INT: int默认最大展示的位数是11位
create table t4(id int);
# 默认范围是(-2147483648， 2147483647)
insert into t4 values(-2147483648), (2147483647);
# 超出默认范围,报错
insert into t4 values(-2147483648), (122412451251251);
# 问题: 整型后的宽度能否改变字段存储的大小？
# create table t5(number int(10));
# int(8)不是限制长度，而是控制展示数据的位数，8位以内以空格补全，超出则正常显示。
create table t5(id int(8));
insert into t5 values(2147483647);
select * from t5;
+------------+
| id |
+------------+
| 2147483647 |
| 21474 |
+------------+
# 显示不够8位用0补全, 并且只能插入无符号的值
create table t6(id int(8) unsigned zerofill);
+----------+
| id |
+----------+
| 42949672 |
| 00429496 |
+----------+
'''
总结:
- 注意1:
- not null: 字段值不能为空
- unsigned: 无正负符号
- zerofill: 不够以0填充空格
'''
2)浮点型:
- 分类: float、double、 decimal
- 应用场景: 身高、体重、薪资
- 三者最大的区别是存储精确度不一样
注意: 字段限制特点(5,3)前一位表示所有的位数,后一位表示小数个数。
# 存储限制:
- float(255, 30)
- double(255, 30)
- decimal(65, 30)
# 精确度验证:
create table t7(x float(255, 30));
create table t8(x double(255, 30));
create table t9(x decimal(65, 30));
insert into t7 values(1.1111111111111111111111111111);
'''
mysql> select *from t7;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
'''
insert into t8 values(1.1111111111111111111111111111);
'''
mysql> select *from t8;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
'''
insert into t9 values(1.1111111111111111111111111111);
'''
mysql> select *from t9;
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111100 |
+----------------------------------+
1 row in set (0.00 sec)
'''
总结:三种浮点型的精确度不一样,最好的是decimal,但是float也够用了
3)字符类型
-
分类：varchar 、char
-
应用场景: 姓名、地址、描述信息
1)char:定长字符
# 超出四个字符报错，不够四个字符空格补全
create table t10(name char(4));
insert into t10 values('tank');
insert into t10 values('lol');
# 报错
insert into t10 values('hello');
优点:
存取速度快
缺点:
浪费空间。
insert into t11 values(1, 't'); # t+三个空格
# 注意: 无法查看字符真实长度
# 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动去掉末尾的空格。
2)varchar: 不定长
- 存几个字符，就是几个字符的大小，每个字符前都要+1bytes
create table t11(name varchar(4));
- insert into t11 values('tank');
insert into t11 values('lol');
# 报错
insert into t10 values('hello');
优点:
节省空间
缺点:
存取速度慢
4）日期类型
- 分类:
- date: 2019-12-10
- time: 11:11:11
- datetime: 2019-12-10 11:11:11
- year: 2019
- timestamp:时间戳
# 建立表格
create table teacher1(id int, name varchar(16),born_year year,brith date,work_time time,register_time datetime, local_time timestamp);
# 插入记录:
insert into teacher1 values(1,'tank','2000','2000-12-12','12:11:10','2000-12-12 11:11:11',null);
# 时间戳可以用null代替
# 注意:插入时间需要以字符串格式
表的内容:
mysql> select *from teacher1;
+------+------+-----------+------------+-----------+---------------------+---------------------+
| id | name | born_year | brith | work_time | register_time | local_time |
|---|---|---|---|---|---|---|
+------+------+-----------+------------+-----------+---------------------+---------------------+
| 1 | tank | 2000 | 2000-12-12 | 12:11:10 | 2000-12-12 11:11:11 | 2019-12-11 18:37:00 |
|---|---|---|---|---|---|---|
+------+------+-----------+------------+-----------+---------------------+---------------------+
5)枚举和集合类型
分类:
- 枚举enum 多选一
- 集合set 多选一 或 多选多
- enum: 可以 多选一
create table t13(
id int,
name varchar(4),
gender enum('male', 'female', 'others')
);
# insert into 表名(字段名) values(字段名对应的值);
insert into t13(id, name, gender) values(1, 'tank', 'male');
# 严格模式下，选择枚举以外的值会报错
insert into t13(id, name, gender) values(2, 'gd', 'animal');
- set: 可 多选一 或 多选多
create table t14(
id int,
name varchar(4),
gender enum('male', 'female', 'others'),
hobbies set('read', 'sing', '生蚝', 'HSNM', '架子鼓')
);
# 多选一
insert into t14 values(1, '大鸡J', 'others', 'HSNM');
# 多选多
insert into t14 values(2, 'tank', 'male', 'read,架子鼓,sing,生蚝');
# 多选多的顺序可不一
insert into t14 values(2, 'tank', 'male', 'read,架子鼓,sing,生蚝');
四.约束条件
约束条件就是，对于数据库表插入数据时加以约束限制。
1)约束条件介绍
- primary key (pk)
标识该字段为该表的主键，主键可以是唯一的表示记录
- foreign key (fk)
标识该字段为该表的外键
- not null
标识该字段不能为空
- unique key (uk)
标识该字段的值是唯一的
- auto_increment
标识该字段的值自动增长 （整数类型，并且为主键）
- default
为该字段设置默认值
- unsigned
无正负符号
- zerofill
使用0填充空格
2)not null + unique (非空唯一)
create table user1(id int not null,name varchar(4));
#报错,不能输入空
insert into user1 values(null,'tank');
# 正确
insert into user1 values(1,'tank');
3)unique 将某个字段设置为唯一的值
create table user3(id int not null unique,name varchar(4));
# 报错
insert into user3 values(1,'tank'),(1,'sean');
# 正确
insert into user3 values(1,'tank'),(2,'sean');
4)primary key (主键)
相等于 :not null + unique
pk就是表中的索引: 可以通过索引快速查找某些数据。 - 提高查询效率
create table user4(
id int primary key,
name varchar(4)
);
# 唯一
insert into user4 values(1);
insert into user4 values(1);
# 不能为空
insert into user4 values(null);
# 强调:
'''
1.一张表必须只有一个主键，如果没有设置主键，会从上到下搜索，直到遇到 "第一个"非空且唯一的字段自动设置为主键。
'''
create table user8(
id int,
name varchar(16),
age int not null unique
)engine=innodb;
desc user8;
'''
2.如果包里没有指定任何可以设置主键的字段，那innodb会采用自己默认的一个隐藏关键字作为主键，隐藏意味着查询你的时候通过这个加速查询。
索引: 类似于书的目录, 没有主键就相当于一页一页翻着查询。
3.一张表中通常都应该有一个id字段，并且通常将id字段作为主键。
'''
# 联合主键: 多个字段联合起来作为一个主键，本质上还是一个主键
create table user9(
id int,
name varchar(16),
primary key(id, name)
);
desc user9;
# 主键id作为数据的编号，应该设置为自动递增
create table user10(
id int primary key auto_increment,
name varchar(16)
);
desc user10;
insert into user10(name) values('tank');
insert into user10(name) values('sean'),('jason'),('大饼');
# 若想自增从指定值开始，可插入第一条数据时先指定id的值;
insert into user4(id, name) values(10, 'tank');
insert into user4(name) values('sean'); # 11
insert into user4(name) values('egon'); # 12
insert into user4(name) values('大鸡哥'); # 13
# 注意: auto_increment通常加在主键上，并且只能设置给primary key字段。
# 补充:
delete from user10;
# delete删除表的记录或者指定记录，但id不会重置为0
# 删除某一条记录
delete from user10 where id='4';
# 若想要清空id重置为0，可使用truncate user10;
5)unsigned
无符号
create table user5(
id int unsigned
);
# 报错
insert into user5 values(-100);
insert into user5 values(0);
insert into user5 values(100);
有符号
create table user6(
id int
);
insert into user6 values(-100);
6)zerofill
使用0填充空格
create table user7(
id int zerofill
);
insert into user7 values(100);
7)删除记录
create table user8(
id int primary key auto_increment,
name varchar(4)
);
insert into user8(name) values('tank');
insert into user8(name) values('大大大'), ('egon');
- delete:
# 清空user8表中的所有记录
delete from user8;
- truncate:
# 清空user8表中的所以记录，并且id重置为0
truncate table user8;
补充：
MySQL表级约束和列级约束
对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束
列级约束即可以在列定义时生命，也可以在列定义后声明。
表级约束只能在列定义后声明。
NOT NULL和DEFAULT只存在列级约束。
PRIMARY KEY、UNIQUE、FOREIGN KEY同时存在表级约束和列级约束。
添加主键约束
alter table tb_name modify col_name type primary key；
alter table tb_name add primary key (ID);
删除主键约束
alter table tb_name drop primary key ();
添加唯一约束
alter table tb_name add unique (col_name);
删除唯一约束
alter table tb_name drop index col_name;
添加外键约束
alter table tb_name1 add foreign key (col_name1) references tb_name2 (col_name2);
查看外键
show create table name\G;
删除外键约束
alter table tb_name drop foreign key fk_symbol;
注意系统赋予的外键命名fk_symbol
设置默认约束
alter table tb_name alter col_name set default value;
删除默认约束
alter table tb_name alter column_name drop default;
