疑难杂症_SQL_数据类型、约束条件

最新推荐文章于 2024-05-23 20:23:59 发布

逸而歌

最新推荐文章于 2024-05-23 20:23:59 发布

阅读量162

点赞数

分类专栏：疑难杂症_Java 文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/yierge_geeryi/article/details/130377986

版权

疑难杂症_Java 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

前言

设计一张表，要为表中每一个字段指定类型以确保该字段可以保存正确类型的数据

注！类型本身属于方言，不同的数据类型不完全一样！

我们可以为表添加约束条件，这样可以限制对标的必须符合我们施加的约束条件，否则数据库会拒绝该操作

SQL_数据类型

数字类型

整数类型

INT类型

占用4个字节，保存范围为正负21亿
语法：
INT(m)
-- m是一个数字，可以指定保存的位数

BIGINT类型

占用8个字节
语法：

BIGINT(m)
-- m是一个数字，可以指定保存的位数

浮点数类型

DOUBLE类型

语法：

DOUBLE(m,n)
-- m是一个数字，表示double类型的总位数(包括小数点后)
-- n是一个数字，表示double类型的小数的位数

注：

如果小数位的精度超过了允许精度时，会进行四舍五入
如果四舍五入后得到的值超过了该字段可以保存的最大值时会报错(即四舍五入后的总位数>m时会报错)
整数部分超出范围会直接报错

字符类型

定长字符串

CHAR类型
CHAR类型在磁盘中开辟的长度是固定的，例如：CHAR(3)，若字符集为UTF-8，则固定开辟12字节。若实际保存的数据不足则会补充若干个空格来填充长度
语法：
CHAR(m)
-- m是一个数字，表示长度，单位是字符，最大可取255
优缺点：
优点
在磁盘中保存每条记录的长度是固定的，因此读取速度快
缺点
在磁盘中占用空间固定，无论实际保存数据多少，都要占够空间，因此对磁盘空间有浪费

变长字符

VARCHAR类型
VARCHAR类型占用磁盘空间按照实际保存的数据而定，数据转换出来的字节是多少就保存多少
语法：
VARCHAR(m)
-- m是一个数字，表示字符串的长度，单位是字节，最大65535
优缺点
优点
由于磁盘空间占用取决于实际保存的数据，因此不造成磁盘空间浪费
缺点
由于数据长度不一致，会导致读取效率低

TEXT类型
占用磁盘空间空间规则同VARCHAR类型
语法
TEXT(m)
-- m是一个数字，单位是字符，最大值为65535
优缺点同VARCHAR类型

日期类型

DATE类型

用于保存年月日

语法:
DATE

TIME类型

用于保存时分秒

语法:
TIME

DATETIME类型

用于保存年月日时分秒

语法:
DATETIME

TIMESTAMP类型

时间戳，保存UTC时间，记录自1970年元旦至表示的时间所经过的毫秒

语法:
TIMESTAMP

注！(以DATETIME为例)

插入一个日期类型的数据时，DATETIME类型为例(可以保存年月日时分秒)，可以使用字符串来表示，但是格式必须为yyyy-MM-dd hh:mm:ss，其中，M为月，m为分
DATETIME类型可以忽略时分秒，时分秒默认为0
但是DATETIME类型不能忽略年月日

SQL_约束条件

主键约束_PRIMARY KEY

主键约束要求该字段必须非空且唯一
主键约束在一张表中只能为一个字段施加
主键约束会为表中第一个字段添加，并且通常该字段取名为id

示例：

CREATE TABLE person(
	id INT PRIMARY KEY,
	name VARCHAR(30),
	age INT(3)
)

注意！

主键不能插入重复的值
主键不能插入NULL值
插入数据时不能忽略主键字段(会使用默认值NULL)，如果主键字段有对应默认值生成机制可以忽略
修改表记录时，不可以将重复的值修改到主键字段
修改表记录时也不可以将NULL值修改到主键字段
永远不应当更新主键字段

自增

具有主键约束的字段通常主键的生成方式由系统完成，而自增就是其中一种方式

示例：
创建表时为主键设置自增

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30),
	age INT(3)
)

修改表时为主键设置自增

-- 修改表person时为id字段添加主键约束以及自增模式
ALTER TABLE person CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;
-- 修改表时仅添加自增
ALTER TABLE person CHANGE id id INT AUTO_INCREMENT;

注意：

当主键字段设有自增时，插入数据则可以忽略主键字段
当主键字段具有自增时，可以显式的向主键中插入NULL值，但此时主键字段仍然使用自增(不推荐)

非空约束_NOT NULL

被非空约束施加的字段，该字段的值不能为NULL

示例：
创建表时为字段添加非空约束

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30) NOT NULL,
	age INT(3)
)

修改表时为字段添加非空约束

ALTER TABLE person CHANGE name name VARCHAR(30) NOT NULL;

注意：

插入数据时，不能向具有非空约束的字段中插入NULL值
也不可以修改NULL值到具有非空约束的字段中

唯一性约束_UNIQUE

具有唯一性约束的字段值不允许重复，但是可以为NULL

示例：
创建表时为字段添加唯一性约束

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30) UNIQUE,
	age INT(3)
)

修改表时为字段添加唯一性约束

-- 当字段本身具有非空约束时添加唯一性约束会取消非空约束，所以不希望非空约束取消的话需要与唯一性约束一并声明
ALTER TABLE person CHANGE name name VARCHAR(30) UNIQUE NOT NULL;

注意：

插入数据时，不可以将重复的值插入到具有唯一性约束的字段中
可以插入NULL值到具有唯一性约束的字段中，且可以多次
修改记录，也不能将重复的值修改到具有唯一性约束的字段中

检查约束_CHECK

检查约束允许我们自定义约束条件，仅当满足我们的指定条件下才可以进行操作

示例：
创建表时添加CHECK约束

CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
age INT(3)
)

注意：

插入数据时，所插入数据必须符合CHECK约束
更新表数据时，也不能违反CHECK约束

外键约束

外键约束是在关系型数据库中常用的一种数据完整性约束，它用于确保表之间的关系不被破坏。简单来说，外键约束要求一个表的某些列值必须存在于另一个表的主键或唯一索引中。

示例：
假设有两个表格：客户表格（Customer）和订单表格（Order），其中客户表格的主键是“CustomerID”，订单表格需要引用客户表格中的“CustomerID”列作为外键以确保数据一致性。
-- 创建客户表格
CREATE TABLE Customer (
	CustomerID INT PRIMARY KEY,
	Name VARCHAR(50),
	Email VARCHAR(50)
);
-- 创建订单表格并添加外键约束
CREATE TABLE [Order] (
	OrderID INT PRIMARY KEY,
	OrderDate DATE,
	CustomerID INT,
	CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID)
		REFERENCES Customer(CustomerID)
);
通过这样的方式，当我们尝试在订单表格中插入一个客户ID不存在的记录时，外键约束会阻止该操作，从而确保了订单表格中的数据与客户表格中的数据一致性。