数据库---mysql数据类型和约束条件
目录
字符编码与配置文件
查看MySQL默认字符编码用的是命令:\s
mysql版本为5.x系列的显示的编码有多种，如果是8.X系列，显示的统一是utf8mb4（utf8mb4是utf8优化版本，支持存储表情）。
统一字符编码
5.X默认编码有多种，可能会导致乱码的情况，所以应该统一编码。
步骤1：进入mysql的目录下找到my-default.ini配置文件，拷贝一份该配置文件并修改名称为my.ini
步骤2:清空my.ini文件内的内容，添加下面固定的配置信息即可。
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
步骤4:保存并重启服务端即可生效，然后可以通过\s查看编码是否修改成功。
存储引擎
什么是存储引擎？
存储引擎就是处理数据的不同方式。
查看存储引擎
在mysql中查看存储引擎的代码是show engines;
MyISAM
5.1之前版本MySQL默认的存储引擎
特点:存取数据的速度快 但是功能很少 安全性较低
InnoDB
5.1之后版本MySQL默认的存储引擎
特点:有诸多功能 安全性较高 存取速度没有MyISAM快
BlackHole
任何写入的数据都会立刻消失(类似于垃圾回收处理站)
Memory
以内存作为数据存取地 速度快但是断电立刻丢失
自定义选择存储引擎---engine
create table t1(id int)engine=myisam;
create table t2(id int)engine=innodb;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;
创建表的完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
);
注意：
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件可以写多个，空格隔开即可(后面详细讲解)eg：字段名1 字段类型(数字) 约束条件1 约束条件2 约束条件3
4.最后一行字段结尾不能加逗号，极其容易被忽略!!!
字段类型之整型
在mysql中整形分为tinyint，smallint，mediumint，int和bigint。
上述整型的区别在于从上往下能够存储的数字范围越来越大。
注意事项
1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位
2.注意手机号如果使用整型来存储 需要使用bigint才可以
3.整形字段括号里的数字并不是用来限制长度，而是用来控制展示长度。
create table t12(id int(3));
insert into t12 values(1111); # 不会报错
create table t13(id int(3) zerofill); # 位数不够用0填充
insert into t13 values(1); # 001
insert into t13 values(1111); # 有几位就展示几位
结论：以后涉及到整型字段 都无需自己定义长度 直接使用自带的即可，而针对其他类型的字段 则需要自己添加数字
案例：
create table t5(id tinyint);
insert into t5 values(-129),(256);
# 如果是在5.6版本不会报错 会自动处理成最大范围(没有意义)
临时修改：
步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
步骤2:退出客户端 重新登录即可
永久修改：
步骤1：找到配置文件my.ini并打开
步骤2：在[mysqld]下面添加sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
步骤3：重启服务端永久生效
# 如果是在5.7及以上版本 则会直接报错(更加合理)
验证(结论)发现所有的整型都默认带有正负号。
如何修改不带正负号(约束条件)？
create table t6(id tinyint unsigned);
字段类型之浮点型
在mydql中浮点型分为float，double和decimal。
| 浮点型 | 范围 | 说明 |
|---|---|---|
| float | float(255,30) | 总共255位 小数位占30位 |
| double | double(255,30) | 总共255位 小数位占30位 |
| decimal | decimal(65,30) | 总共65位 小数位占30位 |
重点研究精确度问题
create table t7(id float(255,30));
create table t8(id double(255,30));
create table t9(id decimal(65,30));
insert into t7 values(1.11111111111111111);
insert into t8 values(1.11111111111111111);
insert into t9 values(1.11111111111111111);
精度结论： decimal>double>float。虽然三者精确度有差距，但是具体用哪个应该结合实际情况。
字段类型之字符类型
在mysql中字符类型就相当于python中的字符串，mysql中的字符类型有char和varchar。 上述两个字符类型的区别在于一个是定长一个是变长。
char(n) 定长 最大只能存储n个字符 超出则报错 不够则空格填充至n个
varchar(n) 变长 最大只能存储n个字符 超出则报错 不够则有几个存n个
验证定长与变长特性
create table t10(name char(4));
create table t11(name varchar(4));
insert into t10 values('jason'); # 报错 超出长度
insert into t11 values('jason'); # 报错 超出长度
insert into t10 values('j'); # 实际存储长度 4
insert into t11 values('j'); # 实际存储长度 1
两者的区别：
| 字符类型 | 优点 | 缺点 |
|---|---|---|
| char | 整存整取 速度快 | 会造成一定的存储空间浪费 |
| varchar | 节省存储空间 | 存取数据的速度没有char快(取数据不知道数据的精确长度) |
注意：varchar在存数据的时候会生成一个1bytes的报头，记录数据长度；varchar在取数据的时候先会读取1bytes的报头，从中获取真实数据长度。
工作中使用char还是varchar？注意根据使用场景决定使用哪个。比如:针对统一中国人的姓名应该采取那个类型varchar，规模较小，数据量相对固定的字典采用char。
字段类型之枚举与集合
枚举：多选一
create table t14(
id int,
name varchar(32),
gender enum('male','female','others')
);
insert into t14 value(1,'tom','男'); # ERROR 1265 (01000): Data truncated for column 'gender' at row 1
'''插入数据的时候 针对gender只能填写提前定义好的数值'''
集合:多选多
create table t15(
id int,
name varchar(32),
hobby set('篮球','足球','双色球','排球','水球','肉球')
);
insert into t15 value(1,'tom','篮球,足球,排球');
insert into t15 value(1,'tom','篮球,足球,排球,乒乓球'); # ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
字段类型之日期类型
在mysql中的日期类型有date，time，datetime，timestamp和year。
| 类型 | 格式 | 示例 |
|---|---|---|
| date | YYYY-MM-DD（年月日） | 2022-05-04 |
| datetime | YYY-MM-DD hh:mm:ss(年月日时分秒) | 2022-05-04 19:02:11 |
| time | hh:mm:ss(时分秒) | 19:02:11 |
| year | YYYY(年) | 2022 |
案例：
create table t16(
id int,
name varchar(32),
reg_time datetime,
birth date,
study_time time,
join_time year
);
insert into t16 values(1,'jason','2022-05-04 19:02:11','2022-05-04','19:02:11','2022');
约束条件
字段类型与约束条件的关系：约束条件是基于字段类型之上的额外限制。
eg：id int unsigned。字段类型int规定了id字段只能存整数，约束条件unsigned指的是整数基础之上还必须是正数。
1.无需正负号---unsigned
create table t6(id tinyint unsigned);
insert into t6 value(-1); # ERROR 1264 (22003): Out of range value for column 'id' at row 1
2.零填充---zerofill
create table t13(id int(3) zerofill); # 位数不够用0填充
insert into t13 values(1); # 001
3.非空---not null
create table t17(id int,name varchar(32));
'''插入数据的另外一种方式 打破字段顺序'''
insert into t17(name,id) values('jason',1);
insert into t17(id) values(2);
create table t18(id int,name varchar(32) not null);
insert into t18(id) values(2); # 报错
insert into t18(id,name) values(2,null); # 报错
insert into t18(id,name) values(2,''); # 不报错
4.默认值---default
create table t19(id int,name varchar(32) default 'jason');
insert into t19(id) values(1); # 不传值使用默认的值
insert into t19(id,name) values(2,'kevin'); # 传值则使用传入的值
5.唯一值---unique
'''单列唯一:某个字段下对应的数据不能重复 是唯一的'''
create table t20(
id int unique,
name varchar(32)
);
insert into t20 value(1,'tom');
insert into t20 value(1,'bob'); # ERROR 1062 (23000): Duplicate entry '1' for key 'id'
'''多列唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的'''
create table t21(
id int,
host varchar(32),
port int,
unique(host,port)
);
6.主键---primary key
'''单从约束层面上而言 相当于not null + unique(非空且唯一)'''
create table t22(id int primary key,name varchar(32));
insert into t22(name) value('tom'); # 没有传入id 报错
insert into t22 value(1,'tom');
insert into t22 value(1,'bob'); # id1语已经存在 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
'''
是InnoDB存储引擎规定的一张表有且必须要有一个主键,用于构建表.
主键可以加快数据的查询速度(类似于书的目录)
如果创建表创建的时候没有设置主键也没有其他的键 那么InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而无法使用 即无法加快数据查询)
如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
'''
create table t23(
tid int,
pid int not null unique,
cid int not null unique
); # pid 会成为表t23的主键
# 结论:创建表应该有一个序号字段(id\pid\cid)并且应该将该字段设置成主键
create table t24(
id int primary key,
name varchar(32)
);
# 也可以有联合主键(多个字段组合 本质还是一个主键) 了解即可
create table t24(
id int,
name varchar(32),
pwd int,
primary key(id,pwd)
);
7.自增---auto_increment
'''专门配合主键一起使用 用户以后在添加数据的时候就不需要自己记忆主键值'''
create table t25(
id int primary key auto_increment,
name varchar(32)
);
insert into t25(name) values('tom'),('jason'),('kevin');
select * from t25;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 2 | jason |
| 3 | kevin |
+----+-------+
浙公网安备 33010602011771号