《MySQL数据库》 数据类型、约束、键的使用—/—＜5＞

一、常见数据类型

1、数值类型：

INT、BIGINT、FLOAT、DOUBLE，DECIMAL等。

        INT（整型）：通常占用4个字节，可以存储范围为-2^31到2^31-1的整数。

        BIGINT（大整型）：通常占用8个字节，可以存储范围为-2^63到2^63-1的整数。

        FLOAT和DOUBLE（浮点型）：表示带有小数点的数值。FLOAT通常占用4个字节，DOUBLE通常占用8个字节。FLOAT可以存储7位有效数字，DOUBLE可以存储15位有效数字。浮点型数值存储时通常会有一定的精度损失。在银行里常见。

        DECIMAL（精确数值）：也表示带有小数点的数值，但是它是以字符串或者数字数组的形式存储的，可以存储非常大的数值，并且不会有精度损失。DECIMAL常用于存储货币金额或者需要高精度计算的场景。

2、字符串类型：

CHAR、VARCHAR、TEXT等。

        CHAR（定长字符串）：CHAR类型用于存储定长的字符串。它需要指定固定的长度，占用的存储空间是固定的。如果存储的字符串长度小于指定长度，会用空格进行填充。例如，CHAR(10)表示存储长度为10的字符串。

        VARCHAR（可变长度字符串）：VARCHAR类型用于存储可变长度的字符串。它需要指定最大长度，占用的存储空间根据实际存储的字符串长度来动态调整。VARCHAR类型存储的字符串长度可以小于指定的最大长度，不会有额外的空间浪费。例如，VARCHAR(255)表示最大长度为255的字符串。

        TEXT（大文本类型）：TEXT类型用于存储较大的文本内容。它可以存储非常长的字符串，不需要指定固定或最大长度。TEXT类型通常用于存储文章、日志等较长的文本数据。

注意：utf8中一个汉字3个字节，gbk中一个汉字2个字节

3、日期和时间类型

DATE、DATETIME、TIMESTAMP等。

        DATE：DATE类型用于存储日期值，包括年、月、日。它通常以YYYY-MM-DD的格式进行存储。

        DATETIME：DATETIME类型用于存储日期和时间值，包括年、月、日、时、分、秒。它通常以YYYY-MM-DD HH:MM:SS的格式进行存储。

        TIMESTAMP：时间戳，TIMESTAMP类型用于存储日期和时间值，包括年、月、日、时、分、秒。它以YYYY-MM-DD HH:MM:SS的格式进行存储，但与DATETIME有一些重要区别。TIMESTAMP在存储时会自动将时间转换为GMT（格林尼治标准时间）来存储，然后在检索时会根据时区进行转换。另外，TIMESTAMP还具有自动更新的功能，可以在每次更新时自动更新为当前时间。在使用TIMESTAMP时，需要根据具体的数据库设置时区和自动更新属性。

类型	大小	范围	格式	用途
	( bytes)
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳
		结束时间是第2147483647秒，北京时间2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

4、二进制类型

BLOB、LONGBLOB、VARBINARY等。

        BLOB：BLOB代表二进制大对象，用于存储大型二进制数据，如图像、音频、视频等。BLOB类型可以存储最大长度为65,535字节的数据。

        LONGBLOB：LONGBLOB是一个较大的BLOB数据类型，可以存储最大长度为4GB的二进制数据。它用于存储更大的二进制对象。

        VARBINARY：VARBINARY是一种可变长度的二进制数据类型，可以存储变长二进制数据，如图像、文件等。它的最大长度取决于具体的数据库系统。

5、枚举类型

ENUM。

        ENUM是一种在编程语言中表示一组具有名称的值的数据类型。这些具有名称的值通常是常量，并且具有特定的顺序，可以用于比较或迭代的目的。

        在ENUM中，每个具有名称的值都被分配一个整数值，称为枚举常量。这些常量可以在代码中使用，表示特定变量或参数的可能选项。

6、集合类型

SET

二、约束

1、主键约束（PRIMARY KEY）

用于唯一标识表中的每一行数据，通常使用整数类型的列。

例如：身份证号，它对于每个人来说是唯一的，不可重复的也是非空的

2、唯一约束（UNIQUE）

保证列中的所有值都是唯一的，可以包含空值。

同样是如身份证一样唯一

3、外键约束（FOREIGN KEY）

用于定义列与其他表的关系，保证引用完整性。

4、非空约束（NOT NULL）

确保列中不包含空值。

5、默认值约束（DEFAULT ）

在插入新行时，如果没有指定列的值，则使用默认值。

如下图所示，即默认值为空

6、检查约束（CHECK）

用于确保列中的值满足特定的条件，可以使用各种表达式和运算符来定义

例如可在上述图片中的字段名age后增加一个check age>18,即该列数据都要大于18，否则会报错

实例演示：

CREATE TABLE users (
-- 主键
  id INT PRIMARY KEY, 
-- 非空
  name VARCHAR(50) NOT NULL, 
-- 唯一
  email VARCHAR(100) UNIQUE,
-- 也是非空
  password VARCHAR(100) NOT NULL,
-- 字段是时间类型，设置了默认值是current-timestamp是时间戳的意思
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-- 同样是时间戳类型，后面的on update表示如果值发生更新，当前时间戳同样发生更新
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 检查，检查age是否大于等于18
  age INT CHECK(age >= 18),
-- 文本类型
  address TEXT,
-- 枚举类型，从admin 和user中选择一个，默认是user用户
  role ENUM('admin', 'user') DEFAULT 'user'
);

运行后即可创建一个新表，然后写入数据，有些没有限制为not null 的可以不写，枚举类型只能选择不可写入，

三、属性的介绍和使用

1、PRIMARY KEY使用

        PRIMARY KEY 是一列或一组列，其值唯一标识表中的每一行。一个表只能有一个PRIMARY KEY。在表创建时，可以指定其中的一列为主键，也可以使用多列来组合作为主键。对于多列组合主键，它们的值的组合必须是唯一的。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

2、AUTO_INCREMENT使用

        AUTO_INCREMENT是一种MySQL特有的属性，它允许一个整数列自动增长，每次插入新行时自动递增。这通常用于为表创建一个唯一的、自动生成的数字标识符。必须将AUTO_INCREMENT属性与INTEGER数据类型一起使用，通常是使用INT或BIGINT数据类型。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

        在上面的代码中，每次插入一行时，MySQL将自动为id列生成一个新的唯一值，而不需要手动为该列指定一个值。这使得插入新行时更加方便，同时也确保每个行都有一个唯一的标识符。

实例代码：

CREATE table auto(
	id int primary key auto_increment
    ,coun int
	,fil VARCHAR(255) not NULL
	,county varchar(255)
)

创建一个表，定义id 为主键，并给他一个自增的属性，创建完成后，点击导入向导，对其进行导入数据

使用navicat可视化导入，注意其中导入字段的时候不要选id这个字段，我们设置了四个字段，自增的字段不用选择，如下图所示

导入的txt文件内数据样式为

导入结束后可视化显示为：

其中的字段id所对应的值为自增序列，如果对这个表进行插入数据操作，id对应的值会自定增加，如果手动删除一行数据，后面再增加一行，那么此时的id值是从之前那个值的后面开始递增，此时可以使用截断表来重置自增id，也可以使用查询命令TRUNCATE TABLE table_name;来截断。

3、UNIQUE使用

        假设有一个“学生”数据表，其中包含学生ID、姓名和电子邮件地址。为了确保每个学生的电子邮件地址是唯一的，可以在“电子邮件地址”列上创建UNIQUE约束。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

        在上面的代码中，我们创建了一个名为“students”的数据表，并在“email”列上创建了一个UNIQUE约束。这将确保每个学生的电子邮件地址是唯一的。

4、FOREIGN KEY使用

        假设有一个“订单”数据表和一个“产品”数据表，其中“订单”表包含订单ID、订单日期和产品ID，而“产品”表包含产品ID、产品名称和产品价格。为了确保订单中的每个产品ID都是有效的，可以在“订单”表中为“产品ID”列创建FOREIGN KEY约束，该约束参照“产品”表中的“产品ID”列。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

        在上面的代码中，我们创建了一个名为“products”的数据表和一个名为“orders”的数据表，并在“orders”表中为“product_id”列创建了一个FOREIGN KEY约束。这将确保订单中的每个产品ID都是有效的，并且参照了“products”表中的产品ID列。