数据库数据类型、约束、范式、数据迁移

一、数据类型

1.1 什么是数据类型

        在数据库中，数据类型是指用于定义数据库表中列（字段）的属性和取值范围的规定。数据类型确定了该列可以存储的数据种类和格式，以及对这些数据可以执行的操作。

1.1.1 MySQL中的数据类型：

       MySQL是一种流行的关系型数据库管理系统，它支持多种数据类型用于定义表中的列。以下是MySQL中常见的数据类型：

数据类型	描述
TINYINT	1个字节的有符号整数，取值范围为-128到127（无符号：0到255）
SMALLINT	2个字节的有符号整数，取值范围为-32,768到32,767（无符号：0到65,535）
INT	4个字节的有符号整数，取值范围为-2,147,483,648到2,147,483,647（无符号：0到4,294,967,295）
BIGINT	8个字节的有符号整数，取值范围为-9,223,372,036,854,775,808到9,223,372,036,854,775,807（无符号：0到18,446,744,073,709,551,615）
DECIMAL(M, D)	固定精度的小数，M表示总位数，D表示小数点后的位数
FLOAT	4字节的浮点数
DOUBLE	8字节的浮点数
CHAR(N)	固定长度的字符，最大长度为255个字符，可以存255个值
VARCHAR(N)	可变长度的字符，最大长度为65535个字符，具体存值个数由编码格式决定。比如8.x版本的编码格式是utf8mb4--4个字节，就可以存65535/4=16383个值
TEXT	可变长度的文本，最大长度为65535个字符，默认值是65535
TINYTEXT	短文本字符串，0-255字节
LONGTEXT	极大文本数据，0-4 294 967 295字节，可以简化为LONG（所以long是字符穿类型，在MySQL数据库中）
DATE	日期，格式为'YYYY-MM-DD'，3个字节
TIME	时间，格式为'HH:MM:SS'，3个字节
DATETIME	日期和时间，格式为'YYYY-MM-DD HH:MM:SS'，8个字节
TIMESTAMP	自动记录时间戳，存储为UTC时间
BOOL或BOOLEAN	存储布尔值，可以是TRUE、FALSE或NULL
BINARY(N)	固定长度的二进制数据
VARBINARY(N)	可变长度的二进制数据
BLOB	可变长度的二进制大对象
ENUM('value1', 'value2', …)	限制列的取值范围为预定义的枚举值之一
SET('value1', 'value2', …)	存储多个选项值的集合

1.1.2 CHAR、VARCHAR、TEXT的区别：

（1）char 和 varchar中括号的n代表可以存多少个字符：

  char可以存放255个字符

  text默认为65535个字符

  varchar最多可以存放16383个字符（65535/4），最终占多数个字节和当前字段的编码有关系：

编码名称	描述
latin1	单字符最大占 1个字节
gbk	单字符最大可占 2个字节
utf8	单字符最大可占用3个字节
utf8mb4	单字符最大可占用4个字节

（2）创建表的时候设置字段类型:

create table tb_user(
    name varchar(20)
)charset = utf8;

（3）查看字段所占字节：

-- 语法格式：select length(字段) from 表

1.1.3 日期类型的使用：

案例1：

创建一个订单表（id，下单时间）；
① 查询今年5月份订单        ② 查询今年第一季度的订单        ③ 查询近30天订单
④ 查询3个月之内的订单        ⑤ 查询今年开始到现在的订单

（1）创建一个订单表（id，下单时间）：

create table tb_order(
    id int unsigned,
    order_time date
);

（2）插入数据：

insert into tb_order values(1, '2023-2-18');
insert into tb_order values(2, '2023-2-19');
insert into tb_order values(3, '2023-4-8');
insert into tb_order values(4, '2023-4-9');
insert into tb_order values(5, '2023-4-29');
insert into tb_order values(6, '2023-4-30');
insert into tb_order values(7, '2023-5-1');
insert into tb_order values(8, '2023-5-2');
insert into tb_order values(9, '2023-5-3');
insert into tb_order values(10, '2022-5-3');
insert into tb_order values(11, '2023-6-3');

（3）查询插入结果：

select * from tb_order;

（4）根据案例中的要求查询：

-- 查询今年5月份订单
select * from tb_order where year(order_time) = year(now()) and month(order_time) = 5;

-- 查询今年第一季度的订单
select * from tb_order where QUARTER(order_time) = 1;

-- 查询近30天订单
select * from tb_order where DATEDIFF(now(), order_time)<31;

-- 查询3个月之内的订单
select * from tb_order where PERIOD_DIFF(
                        DATE_FORMAT(NOW(), '%Y%m'),
                        DATE_FORMAT(order_time, '%Y%m'))<4;

-- 查询今年开始到现在的订单
select * from tb_order where year(order_time) = year(now());

查询今年5月份订单

查询今年第一季度的订单

查询近30天订单

查询3个月之内的订单

查询今年开始到现在的订单

二、约束

        约束是一种限制，通过对表的列插入的数据做出限制，来确保表的数据的完整性、唯一性。

2.1.1 主键约束

        每个表都应该设有主键，主键不可以为null，也不可以重复，而且会自动添加主键索引，以便来优化查询。

（1）主键约束的添加

-- 可以在字段创建的时候添加(单个主键)
create table tb_teacher(
    tid int primary key ,
    tname varchar(20),
    pwd varchar(20)
);

-- 也可以在最后添加（单个主键）
create table tb_teacher(
   tid int ,
   tname varchar(20),
   pwd varchar(20),
   primary key(tid)
);

-- 组合模式（多个主键）
create table tb_teacher(
tid int ,
tname varchar(20),
pwd varchar(20),
primary key(id, name)
);

存在多个主键时，插入的值是要同时一样才不可以，其余都可以，如：

-- 可以
insert into tb_teacher values(1, '小明', '123');
insert into tb_teacher values(2, '小明', '123');

-- 不可以
insert into tb_teacher values(1, '小明', '123');
insert into tb_teacher values(1, '小明', '123');

（2）其他操作：

-- 删除主键约束
alter table tb_teacher drop primary key;

-- 添加主键约束
alter table tb_teacher add primary key(tid,tname);

-- 修改主键约束
alter table tb_teacher modify tid int primary key；

2.1.2 序列（设置在主键上，可以实现主键的自动增长）：

（1）基本使用：

create table tb_teacher(
		tid int primary key  auto_increment,
		tname varchar(20),
        pwd varchar(20)
)

（2）设置步长（auto_increment：可以设置当前主键自动增长）：

alter table t auto_increment = 2;

2.1.3 唯一约束：

        唯一约束是指定table的列或列组合不能重复，保证数据的唯一性；其不允许出现重复的值，但是可以为多个null；同一个表可以有多个唯一约束，多个列组合的约束；在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同；唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

（1）设置唯一约束：

-- 创建表时设置，表示id、名称不能重复
    create table tb_teacher(
    tid int not null ,
    tname varchar(20),
    pwd varchar(20),
    unique(tid, tname)
);

（2）其他操作：

-- 添加唯一约束
alter table tb_teacher add unique (tid, tname);
-- 修改唯一约束
alter table tb_teacher modify tname varchar(20) unique;
-- 删除约束
alter table tb_teacher drop index tname;

2.1.4 非空约束：

        非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。如果字段是null，那么可能会影响查询性能。一般情况下，可以和default结合使用来设置默认值。

（1）设置非空约束：

-- 创建table表，tid为非空约束，tname为非空约束 且默认值为abc
create table tb_teacher(
           tid int not null,
           tname varchar(255) not null default  'abc',
           pwd varchar(20)
)；

（2）其他操作：

-- 增加非空约束
alter table tb_teacher modify pwd varchar(20) not null;

-- 取消非空约束
alter table tb_teacher modify pwd varchar(20) null;

-- 取消非空约束，增加默认值
alter table tb_teacher modify pwd varchar(20) default 'abc' null;

2.1.5 外键约束:

        外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系（当两个表，需要建立联系，就需要使用外键 ）。

（1）没有外键约束时：

-- 教师表
create table tb_teacher(
    tid int primary key auto_increment,
    tname varchar(20)
);
-- 学生表
create table tb_student(
    sid int primary key auto_increment,
    sname varchar(20),
    teacher_id int
);

以上学生表需要依教师表：

insert into tb_teacher values (1,'李老师');
insert into tb_teacher values (2,'赵老师');

insert into tb_student values (1,'小明',1);
insert into tb_student values (2,'小红',1);
insert into tb_student values (3,'小李',3); 

在没有约束的情况下可以插入教师3，但是业务上是有问题的，为了处理业务问题，可以添加外键约束：

-- 在创建表的时候添加
create table tb_student(
    sid int primary key auto_increment,
    sname varchar(50),
    teacher_id int,
    foreign key (teacher_id) references tb_teacher(tid)
);

-- 删除外键约束
alter table tb_student drop foreign key teacher_id;

-- 添加外键约束指定名称
alter table tb_student add
    CONSTRAINT fk_xxx
    foreign key tb_student(teacher_id) references tb_teacher(tid);

三、设计范式

        是指将一个不符合要求的关系型数据库设计转化为符合标准的关系数据库设计的过程。数据库设计范式通常分为三个级别：

3.1.1 第一范式：

        第一范式（1NF）是关系型数据库设计的基本要求之一，指的是每个数据表中的所有列都是原子性的，即不可再分解。具体来说，就是要求每一列都只有一个值，不允许存在重复的属性和组合属性。如果出现了重复的属性或组合属性，需要将其分解成独立的列，组成新的数据表。这样可以减少数据冗余，避免数据插入、更新和删除时的异常情况等问题。

        例如，对于一个存储商品信息的数据表，如果在一列中包含了多个商品名称，如"苹果，香蕉，梨"，这就违反了第一范式。应将每个商品名称单独拆成一列，这样就符合了第一范式要求。

         遵循第一范式可以确保数据表的结构清晰简洁，方便数据的查询、修改和管理，提高了系统的可靠性和性能。

3.1.2 第二范式:

        第二范式（2NF）是关系型数据库设计的进一步要求，它建立在第一范式（1NF）的基础之上，要求数据表中的每个非主键字段都应该完全依赖于主键，而不是部分依赖于主键。具体来说，就是要求数据表中有一个主键，并且每个非主键字段必须都与主键相关，这样才能确保数据表中每个记录的唯一性。

        例如，对于一个订单管理系统，如果存在一个订单详情的数据表，其中包含订单号、商品编号和商品名称，那么这个数据表就没有符合第二范式。因为商品名称列不仅依赖于商品编号列，还依赖于订单号列。此时需要将数据表拆分成两个数据表，一个存储订单信息，一个存储商品信息，然后通过主键建立联系，这样就符合了第二范式的要求。

        遵循第二范式可以进一步优化数据库设计结构，消除数据冗余和不一致性，提高数据的可维护性和可扩展性，同时也能提高查询效率。

3.1.3 第三范式：

        第三范式（3NF）是关系型数据库设计的更高级别要求，它要求数据表中的每个非主键字段都不能传递依赖于主键，即非主键字段只能依赖于主键或其他唯一性字段，不能依赖于非唯一性字段。具体来说，就是要求数据表中的非主键字段之间不能存在函数依赖或传递依赖的关系。如果出现这种情况，需要将其分解成独立的数据表，去除冗余数据。

        例如，一个订单详情表包含商品编号、商品名称、商品单价和数量，其中商品单价和数量可以根据商品编号计算得出，这就存在函数依赖关系。此时需要将商品单价和数量分解成一个新数据表，它们以商品编号作为主键，与订单详情表通过外键联系起来，这样就符合了第三范式的要求。

        遵循第三范式可以消除数据库中冗余数据和不一致性，提高数据存储效率、维护效率和查询效率。同时，也能使数据库的设计更加灵活和易于扩展。

四、数据迁移

（1）在cmd命令中，从数据库将数据导出到sql文件中：

-- mysqldump -uroot -p123 数据库名称 >文件导出路径
mysqldump -uroot -p123 db2302 >db.sql

  IDEA中导出：

 

（2）在cmd命令中，从sql语句导入到数据库中:

-- mysqldump -uroot -p123 数据库名称 < 文件路径
mysql -uroot -p123 db2302 <db.sql

 IDEA中导入：

（3）也可以在mysql的命令中，直接导入sql到数据库中:

source db.sql;