小白学习MySQL Day3 20240817

一、列选项/约束 Column Options/Constraint

首先，列必须指定一个数据类型 Data Type，如INT、VARCHAR、DATETIME等，这决定了列中可以存储的数据类型。

可以在创建表时规定约束（通过 CREATE TABLE 语句），

或者在表创建之后增加或删除（通过 ALTER TABLE 语句）。

1、NOT NULL

NOT NULL 约束强制字段始终包含值，不能接受NULL值。

若不向字段添加值，就无法插入新记录或者更新记录。

2、DEFAULT value

(1) 功能

为列指定一个默认值。如果插入行时没有为列指定值，将使用默认值。

可用于创建表时指定列的默认值，也可用于修改表结构时为现有列添加默认值。

确保数据的完整性和一致性。

(2) 语法

• CREATE TABLE 表名 (列名 Data_Type DEFAULT 值);

• ALTER TABLE 表名 

  MODIFY COLUMN 列名 Data_Type DEFAULT 值;

注：使用ALTER TABLE语句，修改表的结构。

(3) 实例

E.g. 1) 有一个名为students的表，其中有一个名为age的列，当前的数据类型是INT，没有默认值，想要将其数据类型保持不变，但添加一个默认值为18，可以使用以下SQL语句：

ALTER TABLE students

MODIFY COLUMN age INT DEFAULT 18;

这条语句不会改变age列的数据类型（它仍然是INT），但会添加一个默认值18。

        2) 如果还想同时修改数据类型，将age列的数据类型从INT改为SMALLINT，并设置默认值为18：

ALTER TABLE students

MODIFY COLUMN age SMALLINT DEFAULT 18;

        3) 删除age列的默认值：

ALTER TABLE students

MODIFY COLUMN age SMALLINT;

使用 MODIFY COLUMN 语句，但不指定新的默认值，

或者显式地指定为 DEFAULT NULL（如果列允许NULL的话）。

注：如果不确定列的数据类型，可以先使用 DESC 或 SHOW COLUMNS 命令来查询它。

        4) 创建一个名为user的表，其中email列的默认值为NULL，status列的默认值是'pending'。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50) DEFAULT NULL

    status VARCHAR(50) DEFAULT 'pending'
);

(4) 何时使用?

1) 一个列，在大多数情况下都使用相同的值时，使用DEFAULT来避免再次输入相同的值。

2) 在处理可选字段时，可以为这些字段设置默认值，以确保数据的完整性。

"可选字段"（Optional Fields）是指可以包含值，也可以不包含值（即NULL）的字段。

允许用户或系统在数据记录中忽略某些信息，而不是强制要求每个字段都必须有值。

3、AUTO_INCREMENT   

(1) 特点

通常与整数类型一起使用，用于自动生成唯一的数字，每次插入新行时自动增加。

• Must be applied to a key column (primary key, unique key). 

必须应用于主键列（Primary Key）或唯一键列（Unique Key）上。
• A value, usually max(col) + 1, is automatically inserted when data is added.

• First value is 1. 通常开始值是 1，每条新记录递增 1。

(2) 修改起始值

前提是该表已经存在且包含至少一个自增字段，确保你指定了正确的表名和字段名。

例如，将persons表中某个自增字段（假设该字段名为id）的起始值设置为100:

ALTER TABLE persons AUTO_INCREMENT = 100;

• 为什么没有直接指定字段名？

因为AUTO_INCREMENT是表级别的属性，通常关联于表的主键或某个特定的列。

（3）实例

CREATE TABLE persons (

  id INT UNIQUE NOT NULL AUTO_INCREMENT,

  lastname VARCHAR(255) NOT NULL,

  firstname VARCHAR(255),

  age INT DEFAULT 12,

  city VARCHAR(255)

) AUTO_INCREMENT = 5;  

4、UNIQUE  

（1）特点

UNIQUE是一个约束条件，用于保证列中的所有值都是唯一的，

不能有两行在该列上具有相同的值。

作用：提高数据的一致性和准确性，同时提高查询效率。

在MySQL中，UNIQUE KEY是实现UNIQUE约束的一种主要方式。

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

PRIMARY KEY 不允许有 NULL值，但是 UNIQUE KEY 可以。

（2）语法

CREATE TABLE 表名 (

列1 Data_Type UNIQUE KEY,

列2 Data_Type,

UNIQUE KEY unique_index_name (列3, 列4)

);

这个约束是应用在 列3和列4 这两列上的。

创建了一个复合唯一索引（组合唯一约束），这个约束是应用在 列3和列4 这两列上的。

在表中的任何两行中，列3和列4的组合值必须是唯一的，不允许有重复的组合。unique_index_name是这个唯一索引的名称。

这是一个可选的标识符，有助于在数据库管理系统中引用或修改这个索引。

ALTER TABLE 表名 ADD UNIQUE KEY unique_index_name (列名);

5、PRIMARY KEY  

每个表都应该有一个主键，并且每个表只能有一个主键。

指定列或列的组合作为表的主键。

唯一标识数据库表中的每条记录；

值必须唯一，且不能为NULL。

PRIMARY KEY自动具有UNIQUE和NOT NULLY约束。

6、FOREIGN KEY

指定列是另一个表的外键。这用于在两个表之间建立关系，并确保参照完整性。
 

7、ZEROFILL

当插入的数值 比定义的属性长度 小的时候，会在数值前面进行补零操作。

通常与数值类型的显示宽度（M）一起使用，格式为：

字段名 INT(M) ZEROFILL

M指定了字段值的显示宽度，但它并不限制字段值的大小，也不增加字段值占用的存储空间。

作用：为了满足某些特定的格式化需求，使得输出的数据在视觉上更加整齐。

注：

• 只适用于非负整数，如果插入负数会报错。

• 只适用于INT整数类型，对于其他数据类型（如浮点数）不适用。

二、键与索引 KEY and INDEX

1. KEY：
   • 键是在逻辑层面上定义的，它描述了表和数据库的设计。
   • 关注数据的逻辑结构，用于维护数据的完整性，特别是引用完整性。
   • 键分为几种类型，包括主键（Primary Key）、外键（Foreign Key）等。
   • 主键用于唯一标识表中的每一行，而外键用于在两个表之间建立关系。
2. 索引（INDEX）：
   • 索引是在物理层面上实现的，关注提高数据库操作的物理效率。
   • 索引可以大大加快数据检索的速度，尤其是对于大型数据库。
   • 索引可以是单列的，也可以是多列的（复合索引）。
3. 主键与索引的关系：
   • 在大多数数据库系统中，每当创建一个主键时，系统会自动在该主键列上创建一个唯一索引（UNIQUE INDEX）。
   • 这个唯一索引确保主键列中的每个值都是唯一的，并且加快了基于主键的查询速度。