2、MySQL基础数据类型、完整性约束、sql_mode模式
1. MySQL常用数据类型
-
MySQL常用数据类型预览
1.数字：
整型：
tinyinit int bigint 小数：
float:在位数比较短的情况下不精准
double:在位数比较长的情况下不精准
decimal:精准、内部原理是以字符串形式去存,如果用小数，则用推荐使用decimal2.字符串：
char(10):
简单粗暴,浪费空间,存取速度快 varchar(10):
精准，节省空间，存取速度慢3.时间类型：
最常用：datetime4.枚举类型与集合类型
1. 1 数值类型
-
整数类型
1.整数类型：tinyint、smallint、mediumint、int、bigint
2.插入超出范围的数字会储存为能达到的最大数字，（默认为宽松模式）
3.作用：存储年龄，等级，id，各种号码等
1.tinyint[(m)] [unsigned] [zerofill]
-- 小整数，数据类型用于保存一些范围的整数数值范围：
-- 有符号范围：-128 ～ 127
-- 无符号范围：0 ～ 255 2.int[(m)][unsigned][zerofill]
-- 整数，数据类型用于保存一些范围的整数数值范围：
-- 有符号：-2147483648 ～ 2147483647
-- 无符号：0 ～ 4294967295 3.bigint[(m)][unsigned][zerofill]
-- 大整数，数据类型用于保存一些范围的整数数值范围：
-- 有符号：-9223372036854775808 ～ 9223372036854775807
-- 无符号：0 ～ 18446744073709551615sql语句示例：
-- 创建有符号数值类型示例:
mysql> create table t1(money tinyint); -- 创建无符号数值类型示例:unsigned/约束无符号
mysql> create table t2(id tinyint unsigned); -- 设置了数据类型,会有一个约束效果,只能表示自己范围内的数 -
浮点型
1.浮点型分类：float、double、decimal
2.作用：存储薪资、身高、温度、体重、体质参数等
1.FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
-- 单精度浮点数（非准确小数值），M是整数部分+小数部分的总个数，D是小数点后个数。
-- M最大值为255，D最大值为30，例如：float(255,30)/小数点不占位
-- 有符号：-3.402823466E+38 ～ -1.175494351E-38,
-- 1.175494351E-38 ～ 3.402823466E+38
-- 无符号：1.175494351E-38 ～ 3.402823466E+38
-- 精确度：**** 随着小数的增多，精度变得不准确 **** 2.DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
-- 双精度浮点数（非准确小数值），M是整数部分+小数部分的总个数，D是小数点后个数。
-- M最大值也为255，D最大值也为30
-- 有符号：-1.7976931348623157E+308 ～ -2.2250738585072014E-308
-- 2.2250738585072014E-308 ～ 1.7976931348623157E+308
-- 无符号： 2.2250738585072014E-308 ～ 1.7976931348623157E+308
-- 精确度：****随着小数的增多，精度比float要高，但也会变得不准确 **** 3.decimal[(M,D)] [unsigned] [zerofill]
-- 准确的小数值，M是整数部分+小数部分的总个数（负号不算），D是小数点后个数。
-- M最大值为65，D最大值为30。比float和double的整数个数少，但是小数位数都是30位
-- 精确度： **** 随着小数的增多，精度始终准确 ****
-- decimal能够存储精确值的原因在于其内部按照字符串存储，对于精确数值计算时需要用此类型 -- 总结：
-- 精度从高到低：decimal、double、float
-- decimal精度高，但是整数位数少
-- float和double精度低，但是整数位数多sql语句示例:
-- float示例：
mysql> create table t3(price float(60,30));
-- double示例：
mysql> create table t4(price double(60,30));
-- decimal示例：
mysql> create table t5(price decimal(60,30));
1.2 日期类型
-
日期类型定义：
1.类型：DATE，TIME，DATETIME ，IMESTAMP，YEAR
2.作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
1.YEAR：YYYY（范围：1901/2155）2018
2.DATE：YYYY-MM-DD（范围：1000-01-01/9999-12-31）例：2018-01-01
3.TIME：HH:MM:SS（范围：'-838:59:59'/'838:59:59'）例：12:09:32
4.DATETIME：YYYY-MM-DD HH:MM:SS（范围：1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
5.TIMESTAMP：YYYYMMDD HHMMSS（范围：1970-01-01 00:00:00/2037 年某时）/-- 由于日期马上到了，现在基本不怎么使用了 -
sql语句示例：
-- year、date、datetime示例：
mysql> create table t6(d1 year ,d2 date,d3 datetime);
-- 插入当前时间示例：
mysql> insert into t6 values(now(),now(),now());
1.3 字符串类型
-
字符串类型定义：
1.类型：char，varchar 内部存储的空间定长不定长
2.作用：名字，信息等等
3.注意：char和varchar括号内的参数指的都是字符的长度（5.6版本以前指的是字节，5.6版本以前指的是字符）
1.char类型：定长，简单粗暴，浪费空间，存取速度快
-- 字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
-- 存储：存储char类型的值时，会往右填充空格来满足长度
-- 例如：指定长度为10，存大于10个字符则报错(严格模式下)，存小于10个字符则用空格填充直到凑够10个字符存储
-- 检索：在检索或者查询时，查出的结果会自动删除尾部的空格，如果想看到它补全空格之后的内容，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';） 2.varchar类型：变长，精准，节省空间，存取速度慢
-- 字符长度范围：0-65535（如果大于21845会提示用其他类型，mysql行最大限制为65535字节，字符编码为utf-8）
-- 存储：varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
-- 如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
-- 如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
-- 检索：尾部有空格会保存下来，在检索或者查询时，也会正常显示包含空格在内的内容 -- 总结：
-- varchar在存数据的时候，会在每个数据前面加上一个头，这个头是1-2个bytes的数据，这个数据指的是后面跟着的这个数据的长度，1bytes能表示2**8=256，两个bytes表示2**16=65536，能表示0-65535的数字，所以存的时候会比较麻烦，导致效率比char慢，取的时候也慢，先拿长度，再取数据。
-- 优点：节省了一些硬盘空间，一个acsii码的字符用一个bytes长度就能表示，但是也并不一定比char省，看一下官网给出的一个表格对比数据，当存的数据正好是规定的字段长度的时候，varchar反而占用的空间比char要多char和varchar对比示例图：
1.5 枚举和集合类型
-
枚举和集合类型定义：
1.enum中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。enum只允许从值集合中选取单个值，而不能一次取多个值。如性别 sex： 男：male/女：female
2.set类型可以允许值集合中任意选择1或多个元素进行组合,里面可以包含0-64个成员,对超出范围的内容将不允许注入，而对重复的值将进行自动去重
-
sql语句示例：
mysql> create table consumer(
-> name varchar(50),
-> sex enum('male','female'),
-> hobby set('play','music','read','study') -- 在指定范围内，多选多
-> ); -- 错误示范:
create table t5(sex char(16) enum('male','female') default 'male');
-- 正确示范：
create table t5(sex enum('male','female') default 'male');
-- 注意：枚举前面的字段不需要加属性
2. 完整性约束
-
not null 不为空
create table t1(name char(10) not null); -
default 默认值
create table t1(name char(10) not null default 'zhangsan'); -- 设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值 -
unique 唯一/字段数据不能重复
create table t1(name char(10) unique); --设置联合唯一约束示例：
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) -- 联合唯一
); -
primary key 主键
-- 建表时指定主键：
create table t1(id int primary key);
-- 建表后指定主键：
constraint pk_name primary key(id); -- 创建主键并为其命名pk_name -- 约束效果:不为空且唯一 not null + unique -
联合主键
mysql> create table t1(
->id int,
->port int,
->primary key(id,port)
-> ); -- 和联合唯一是类似的， 看key，两个都写的是pri，两个联合起来作为主键，他们两个作为一个主键，不能再有其他的主键了，也就是在创建表的时候，只能出现一次primary key方法。 -
auto_increment 自增
create table t1(id int primary key auto_increment);
-- 前面必须是primary key -- 在创建完表后，修改自增字段的起始值
alter table student auto_increment=3; -- 创建表时指定自增字段的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3; -- 自增的步长相关：
-- 基于表级别
SET @@auto_increment_increment=10; -- 查看当前会话自增的步长：
show session variables like 'auto_inc%'; -- 基于会话级别/修改会话级别的步长
set session auto_increment_increment=2 -- 基于全局级别的/修改全局级别的步长（所有会话都生效）
set global auto_increment_increment=2 -
foreign key 外键 建立表关系使用的约束条件
多对一 / 一对多
foreign key -- 外键其实就是标明表和表之间的关系
-- 一对多或为多对一
-- 两张张表：出版社，书 -- 一个出版社可以出版多本书 create table press(
id int primary key auto_increment,
name varchar(20)
); create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);总结：
1、先要建立被关联的表才能建立关联表
2、在插入数据记录的时候，要先在被关联表中插入数据，才能往关联表里面插入数据
3、更新或者删除数据的时候，都需要考虑关联表和被关联表的关系
解决方案：
a.删除表的时候，先删除关联表，再删除被关联表
b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade一对一
-- 两张表：学生表和客户表
-- 一个学生是一个客户 create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> ); create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, -- 该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) -- 外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );多对多
-- 两张表：作者信息，书
-- 一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
-- 关联方式：foreign key+一张新的表 create table author(
id int primary key auto_increment,
name varchar(20)
); -- 这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);外键相关操作：
-- 1.查看外键关系名称
show create table book; -- 2.删除外键关系
alter table book drop foreign key book_ibfk_1（外键名称） -- 3.创建表完成之后，添加外键
alter table book add foreign key(pid) references publish(id); -- 4.创建外键时指定外键名称
1.create table t1(
id int,
pid int,
constraint fk_name foreign key(pid) references publish(id)
);
2.创建表完成之后,后添加外键关系
alter table book add constraint fk_name foreign key(pid) references
3.创建外键时指定外键名称（创建后不能修改外键名称，只能删除后再添加） -- 5.查看所有外键的信息
select REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,table_name,CONSTRAINT_NAME from information_schema.key_column_usage;外键约束之级联
级联的三种模式：级联中有一种模式是级联模式
外键约束的三种约束方式： -- 模式一：
district：严格约束(默认的)：父表不能删除或者更新已经被子表数据引用的记录 -- 模式二：
cascade：级联模式：父表的操作，对应的子表关联的数据也跟着操作 。
constraint fk_t1_publish foreign key(pid) references publish(id) on delete cascade on update cascade; -- 模式三：
set null：置空模式：父表操作之后，子表对应的数据（外键字段）也跟着被置空，被关联字段删除时，关联它的数据会置成null 1.通常的一个合理的约束模式是：删除的时候子表置空；更新的时候子表级联。
2.指定模式的语法：foreign key(外键字段)references 父表(主键字段)on delete 模式 on update 模式;
3.外键虽然很强大，能够进行各种约束，但是外键的约束降低了数据的可控性和可拓展性。通常在实际开发时，很少使用外键来约束。
3. MySQL的sql_mode模式说明以及设置
-
sql_mode简述：
sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的，在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题.
-
MySQL的sql_mode的四种模式
ANSI模式/宽松模式
对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告STRICT_TRANS_TABLES模式/严格模式
进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。STRICT_ALL_TABLES模式/严格模式
进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。TRADITIONAL模式/严格模式
当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事务时，会进行事务的回滚。 -
严格模式设置和修改
方式一：当前会话配置
-- 查看当前会话sql_mode模式
select @@sql_mode -- 设置当前会话sql_mode模式
set sql_mode = '修改后的值';
set session sql_mode='修改后的值';
例如：set session sql_mode='STRICT_TRANS_TABLES'; -- 改为严格模式,session可以不用写
-- 此方法只在当前会话中生效，关闭当前会话就不生效了。方式二：全局配置/当前服务
-- 查看全局sql_mode模式
select @@global.sql_mode
-- 设置全局sql_mode模式
set global sql_mode = '修改后的值'。 -- 此方法在当前服务中生效，重新MySQL服务后失效方法三：全局配置/永久
sql_mode = STRICT_TRANS_TABLES
-- 在mysql配置文件my.cnf文件(windows系统是my.ini文件),添加sql_mode -- 示例如下：
[mysqld] sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES -- ONLY_FULL_GROUP_BY：限制必须是分组依据，或统计结果
-- STRICT_TRANS_TABLES：严格模式