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

已于 2023-11-01 14:13:22 修改
阅读量106 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql
于 2023-06-13 17:40:32 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_46192417/article/details/131183597
版权

一、数据类型

1.1 什么是数据类型

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

1.1.1 MySQL中的数据类型：

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

数据类型描述
TINYINT1个字节的有符号整数，取值范围为-128到127（无符号：0到255）
SMALLINT2个字节的有符号整数，取值范围为-32,768到32,767（无符号：0到65,535）
INT4个字节的有符号整数，取值范围为-2,147,483,648到2,147,483,647（无符号：0到4,294,967,295）
BIGINT8个字节的有符号整数，取值范围为-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表示小数点后的位数
FLOAT4字节的浮点数
DOUBLE8字节的浮点数
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;

Tina@Qian
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
K3 11版 数据库 系统 数据 字典
04-25
数据字典会列出所有表及其字段，包括字段名、数据类型、长度、是否为主键、是否允许为空等属性。 3. **关系模型**：K3系统中的表通常通过外键关联，形成复杂的关系模型。数据字典会揭示这些关联，帮助用户理解不同...
数据库常用的数据类型和约束条件
aiheshuicxy的博客
12-03 1854
数据库常用的数据类型和约束条件
MySQL数据库02(数据类型 字段的约束及属性 表的DML语句 )
VanChaoi的博客
06-12 1296
MySQL数据类型--数值类型类型说明取值范围存储需求TINYINT非常小的数据有符值:-2(7次方)~ 2(7次方)-1  无符号值：0 ~ 2(8次方)-11字节SMALLINT较小的数据有符值：  -2(15次方) ~ 2(15次方)-1  无符号值：  0 ~ 2(16次方)-1 2字节MEDIUMINT中等大小的数据有符值：  -2(23次方) ~ 2(23次方)-1  无符号值：  0...
MySQL中的数据类型和字段约束
weixin_47155221的博客
10-11 136
一 select * from users ; select 关键字 代表查询的意思 *
数据库的创建表时，字段的数据类型和约束条件
热门推荐
起风了
03-08 1万+
文章目录数值类型：整数类型：浮点型：日期类型：字符串类型：枚举类型与集合类型： 数值类型： 整数类型： tinyint 默认有符号，[unsigned]代表无符号，超出范围显示最大范围。 语法： tinyint[(m)] [unsigned] [zerofill] int类型无符号的存储宽度4个字节， 创建表整形类型不指定宽度。指定宽度对存储宽度没用，只是更改显示宽度。显示宽度是指...
简洁好用的数据库表结构文档生成器.zip
01-01
1. 数据库表结构：在关系型数据库中，表结构是数据存储的核心，由一系列列（字段）组成，每个列都有特定的数据类型，如整数、字符串、日期等。表之间可以通过主键和外键建立关联，形成复杂的数据库模型。 2. 数据库...
读取数据库表生成SQL脚本.zip
01-19
2. **SQL脚本生成**：这个工具可能能读取现有数据库表的结构（包括字段名、数据类型、约束等），并自动生成创建这些表的SQL脚本。这对于在新的环境中重建数据库结构非常有用。 3. **数据迁移**：在系统升级或部署新...
数据库自用，知识点整理
05-13
- **字段**: 每一列称为一个字段，定义了数据的类型和约束。 - **记录**: 表中的每一行称为一个记录，代表了一组相关数据。 2. **数据库设计** - **范式理论**: 包括第一范式（1NF）、第二范式（2NF）、第三范式...
MySQL 数据库设计优化完善版本.doc
08-19
数据库逻辑设计的规范化是指达到一定的范式，我们可以简单地理解范式为： * 第 1 规范：没有重复的组或多值的列，这是数据库设计的最低要求。 * 第 2 规范：每个非关键字段必须依赖于主关键字，不能依赖于一个组合...
初始C++
2302_81016149的博客
07-08 805
在C语言我们学习函数的时候，我们学习过生命周期这个概念。那么，什么是生命周期呢？生命周期通常指的是一个对象或变量从创建到销毁所经历的时间段。那么，命名空间是什么？咱们可以这样理解：在这个空间的生命周期内，对这个空间内的全部标识符起一个名字，可对其进行引用，可类似与C语言中的结构体。#include<stdlib.h>//此处不包此头文件代码可正常运行，那该如何解决此处情况呢？int main()//此处编译不通过原因为：在不包头文件前为变量，包含后为函数。return 0;
set user & password for database 设置数据库 用户和密码
DavidsonGong的博客
07-12 96
USE sbms;
mysql笔记1
m0_62975692的博客
07-11 246
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
docker-compose安装PolarDB-PG数据库
wnfff的博客
07-12 201
docker-compose安装PolarDB-PG数据库
Redis数据结构-String篇
最新发布
时间如瑾 代码如诗
07-12 508
对⼀个内部表示成long型的string执行append, setbit, getrange这些命令，针对的仍然是string的值（即⼗进制表示的字符串），而不是针对内部表⽰的long型进⾏操作。因此，在这些命令的实现中，会把long型先转成字符串再进行相应的操作。String 的内部存储结构⼀般是 sds（Simple Dynamic String，可以动态扩展内存），但是如果⼀个String 类型的 value 的值是数字，那么 Redis 内部会把它转成 long 类型来存储，从⽽减少内存的使用。
系统架构设计师教程 第二章 计算机系统基础知识-2.3计算机软件
Remon的专栏
07-09 720
《系统架构设计师教程》清华第二版 2.3计算机软件 章节的详细解读
Java：使用synchronized和Redis实现并发控制的区别
好幸运
07-08 460
而Redis则适用于分布式环境下的同步需求，虽然操作相对复杂，但它能够解决跨JVM的资源竞争问题。: 是Java语言内置的关键字，用于实现线程间的同步。这种同步是** JVM层面**的，对本地内存中的对象进行操作。Redis: 由于是基于网络通信的分布式存储，它的性能受到网络延迟和带宽的影响。然而，在分布式环境下，Redis提供的分布式锁机制能够有效解决跨JVM的同步问题，尽管相比。Redis: 使用Redis实现同步（如通过SETNX命令设置锁），需要手动编写代码来获取和释放锁。
CH-benCHmark：一个HTAP数据库测试基准
算力测评
07-09 257
这一基准测试，旨在弥合TPC-C（专注于联机事务处理，即OLTP）与TPC-H（侧重于联机分析处理，即OLAP）这两项业界权威但单一的工作负载测试之间的鸿沟。它巧妙地结合了TPC-C的订单录入流程产生的交易负载，以及与之对应的、等同于TPC-H级别的OLAP查询套件，并在单一数据库系统内的同一数据集上并行执行。正因为继承自两个最广泛应用的TPC标准，CH-benCHmark的测试结果不仅对混合负载系统具有重大参考价值，同时也适用于传统的单一工作负载场景。
数据库db文件损坏修复方法（sqlite3：database disk image is malformed）
Littlehero_121的博客
07-10 466
一、第一种方式：通过 使用SQLite Expert Professional软件来修复。二、通过命令行，将db文件数据导出到.sql语句，然后建个新的db文件，放进去这样的流程。通过New file ->Start就可以完成，如果文件比较大，那就比较耗时，等待即可。再去看repair.db已经不再是空的了，修复完成。直接拖入受损的db文件，然后进行Repair。（2）在受损的db文件路径中，使用终端打开。发现新生成一个 repair.db文件。发现多了个repair.sql。
在centos7.9下静默安装Oracle19c详细流程
欢迎来到我的学习笔记薄
07-12 142
HOST修改成自己的主机名或IP。
MySQL数据库基础面试知识点
10-08
MySQL基本操作及语法！

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Tina@Qian

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值