MySQL基本操作（续）

第3章：MySQL基本操作（续）

3.3 表操作

表是关系型数据库中存储数据的基本结构，由行和列组成。在MySQL中，表操作包括创建表、查看表结构、修改表和删除表等。本节将详细介绍这些操作。

3.3.1 创建表

在MySQL中，使用CREATE TABLE语句创建新表。

基本语法

CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype [constraints],column2 datatype [constraints],...,[table_constraints]
) [table_options];

参数说明：

• IF NOT EXISTS：可选，如果表已存在，则不会创建新表，也不会报错
• table_name：表名
• column1, column2, ...：列名
• datatype：列的数据类型
• constraints：列级约束条件，如NOT NULL、UNIQUE等
• table_constraints：表级约束条件，如PRIMARY KEY、FOREIGN KEY等
• table_options：表选项，如存储引擎、字符集等

示例

创建基本表：

CREATE TABLE employees (id INT,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100),hire_date DATE
);

创建带约束条件的表：

CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,last_name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,hire_date DATE NOT NULL,department_id INT,salary DECIMAL(10,2) DEFAULT 0.00,FOREIGN KEY (department_id) REFERENCES departments(id)
);

创建表时指定表选项：

CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL,last_name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,hire_date DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

从现有表创建新表

可以基于现有表的结构或查询结果创建新表：

-- 复制表结构（不包含数据）
CREATE TABLE new_employees LIKE employees;-- 基于查询结果创建表（包含数据）
CREATE TABLE senior_employees AS
SELECT * FROM employees WHERE hire_date < '2010-01-01';

临时表

临时表在会话结束时自动删除，对其他会话不可见：

CREATE TEMPORARY TABLE temp_employees (id INT,name VARCHAR(100)
);

3.3.2 查看表结构

MySQL提供了多种方法来查看表的结构和信息。

列出数据库中的表

使用SHOW TABLES命令列出当前数据库中的所有表：

SHOW TABLES;

输出示例：

+-------------------+
| Tables_in_mydb    |
+-------------------+
| departments       |
| employees         |
| projects          |
+-------------------+

可以使用LIKE子句筛选表名：

SHOW TABLES LIKE 'emp%';

查看表结构

使用DESCRIBE或DESC命令查看表的列结构：

DESCRIBE employees;
-- 或
DESC employees;

输出示例：

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| first_name  | varchar(50)  | NO   |     | NULL    |                |
| last_name   | varchar(50)  | NO   |     | NULL    |                |
| email       | varchar(100) | YES  | UNI | NULL    |                |
| hire_date   | date         | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

查看表的创建语句

使用SHOW CREATE TABLE命令查看创建表的完整SQL语句：

SHOW CREATE TABLE employees;

输出示例：

+------------+----------------------------------------------------+
| Table      | Create Table                                       |
+------------+----------------------------------------------------+
| employees  | CREATE TABLE `employees` (`id` int NOT NULL AUTO_INCREMENT,`first_name` varchar(50) NOT NULL,`last_name` varchar(50) NOT NULL,`email` varchar(100) DEFAULT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+----------------------------------------------------+

查看表信息

使用INFORMATION_SCHEMA数据库查询表的详细信息：

-- 查询表的基本信息
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, AVG_ROW_LENGTH,DATA_LENGTH, INDEX_LENGTH, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'employees';-- 查询表的列信息
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT,CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'employees';

3.3.3 修改表

在MySQL中，使用ALTER TABLE语句修改现有表的结构。

添加列

向表中添加新列：

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);-- 在特定位置添加列
ALTER TABLE employees
ADD COLUMN address VARCHAR(200) AFTER email;-- 在第一列位置添加列
ALTER TABLE employees
ADD COLUMN employee_code VARCHAR(10) FIRST;

修改列

修改列的数据类型、约束或位置：

-- 修改列的数据类型
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(30);-- 修改列的数据类型和约束
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(30) NOT NULL DEFAULT '';-- 修改列的位置
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(30) AFTER last_name;

重命名列

MySQL 8.0及以上版本可以直接重命名列：

ALTER TABLE employees
RENAME COLUMN phone TO contact_number;

在旧版本中，需要使用CHANGE命令：

ALTER TABLE employees
CHANGE COLUMN phone contact_number VARCHAR(30);

删除列

从表中删除列：

ALTER TABLE employees
DROP COLUMN address;

添加约束

向表中添加约束：

-- 添加主键约束
ALTER TABLE employees
ADD PRIMARY KEY (id);-- 添加唯一约束
ALTER TABLE employees
ADD CONSTRAINT uk_email UNIQUE (email);-- 添加外键约束
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id);-- 添加检查约束（MySQL 8.0.16及以上版本）
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

删除约束

从表中删除约束：

-- 删除主键约束
ALTER TABLE employees
DROP PRIMARY KEY;-- 删除唯一约束
ALTER TABLE employees
DROP INDEX uk_email;-- 删除外键约束
ALTER TABLE employees
DROP FOREIGN KEY fk_department;-- 删除检查约束（MySQL 8.0.16及以上版本）
ALTER TABLE employees
DROP CONSTRAINT chk_salary;

修改表选项

修改表的存储引擎、字符集等选项：

-- 修改存储引擎
ALTER TABLE employees
ENGINE = MyISAM;-- 修改字符集和排序规则
ALTER TABLE employees
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;-- 修改表注释
ALTER TABLE employees
COMMENT = 'Employee information table';

重命名表

重命名表：

-- 方法1
ALTER TABLE employees
RENAME TO staff;-- 方法2
RENAME TABLE employees TO staff;

3.3.4 删除表

当不再需要某个表时，可以使用DROP TABLE语句将其删除。

基本语法

DROP TABLE [IF EXISTS] table_name [, table_name2, ...];

参数说明：

• IF EXISTS：可选，如果表不存在，则不会报错
• table_name：要删除的表名，可以同时删除多个表

示例

删除单个表：

DROP TABLE employees;

删除表（如果存在）：

DROP TABLE IF EXISTS employees;

同时删除多个表：

DROP TABLE IF EXISTS employees, departments, projects;

警告：DROP TABLE会永久删除表及其所有数据。此操作不可逆，执行前应确保有备份或确实不再需要该表。

截断表

如果只想删除表中的所有数据，但保留表结构，可以使用TRUNCATE TABLE：

TRUNCATE TABLE employees;

TRUNCATE TABLE比DELETE FROM更快，因为它不记录单个行删除操作，而是直接重新创建表。但它不能用于有外键约束的表，除非先禁用外键检查。

3.3.5 表的复制和备份

有时需要复制表结构或数据，用于测试、备份或数据迁移。

复制表结构

只复制表结构，不复制数据：

CREATE TABLE employees_backup LIKE employees;

复制表结构和数据

复制表结构和所有数据：

CREATE TABLE employees_backup AS
SELECT * FROM employees;

复制表结构和部分数据：

CREATE TABLE senior_employees AS
SELECT * FROM employees WHERE hire_date < '2010-01-01';

复制到现有表

将数据复制到已存在的表：

INSERT INTO employees_backup
SELECT * FROM employees;

使用mysqldump备份表

使用mysqldump命令行工具备份特定表：

# 备份单个表
mysqldump -u username -p database_name table_name > table_backup.sql# 备份多个表
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

3.3.6 表的分区

表分区是将大表分成更小、更易管理的部分的技术。MySQL支持多种分区类型。

分区类型

MySQL支持以下分区类型：

• RANGE：基于连续范围的分区
• LIST：基于离散值列表的分区
• HASH：基于哈希函数的分区
• KEY：类似于HASH，但使用MySQL的内部哈希函数

创建分区表

使用RANGE分区创建表：

CREATE TABLE sales (id INT NOT NULL,sale_date DATE NOT NULL,amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022),PARTITION p3 VALUES LESS THAN (2023),PARTITION p4 VALUES LESS THAN MAXVALUE
);

使用LIST分区创建表：

CREATE TABLE employees (id INT NOT NULL,name VARCHAR(50),department VARCHAR(20)
)
PARTITION BY LIST (department) (PARTITION p_sales VALUES IN ('Sales', 'Marketing'),PARTITION p_tech VALUES IN ('IT', 'Engineering'),PARTITION p_admin VALUES IN ('HR', 'Finance', 'Admin')
);

使用HASH分区创建表：

CREATE TABLE orders (id INT NOT NULL,customer_id INT,order_date DATE
)
PARTITION BY HASH (id)
PARTITIONS 4;

管理分区

添加分区：

ALTER TABLE sales
ADD PARTITION (PARTITION p5 VALUES LESS THAN (2024));

删除分区：

ALTER TABLE sales
DROP PARTITION p0;

重组分区：

ALTER TABLE sales
REORGANIZE PARTITION p1, p2 INTO (PARTITION p12 VALUES LESS THAN (2022)
);

3.3.7 表的索引

索引是提高查询性能的重要工具。MySQL支持多种类型的索引。

创建索引

在创建表时添加索引：

CREATE TABLE employees (id INT NOT NULL,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100),hire_date DATE,PRIMARY KEY (id),INDEX idx_name (first_name, last_name),UNIQUE INDEX idx_email (email)
);

在现有表上添加索引：

-- 添加普通索引
CREATE INDEX idx_hire_date ON employees (hire_date);-- 添加唯一索引
CREATE UNIQUE INDEX idx_email ON employees (email);-- 添加复合索引
CREATE INDEX idx_name ON employees (first_name, last_name);-- 使用ALTER TABLE添加索引
ALTER TABLE employees
ADD INDEX idx_department (department_id);

查看索引

查看表的所有索引：

SHOW INDEX FROM employees;

删除索引

删除索引：

-- 使用DROP INDEX
DROP INDEX idx_hire_date ON employees;-- 使用ALTER TABLE
ALTER TABLE employees
DROP INDEX idx_department;

3.4 数据类型

MySQL提供了多种数据类型，用于存储不同类型的数据。选择合适的数据类型对于优化存储空间和提高查询性能至关重要。

3.4.1 数值类型

MySQL支持所有标准SQL数值数据类型，包括精确数值类型和近似数值类型。

整数类型

数据类型	存储空间	最小值（有符号）	最大值（有符号）	最小值（无符号）	最大值（无符号）
TINYINT	1字节	-128	127	0	255
SMALLINT	2字节	-32,768	32,767	0	65,535
MEDIUMINT	3字节	-8,388,608	8,388,607	0	16,777,215
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

使用示例：

-- 有符号整数
id INT,
small_value SMALLINT,
big_value BIGINT,-- 无符号整数
age TINYINT UNSIGNED,
population INT UNSIGNED,-- 指定显示宽度（不影响存储空间）
product_code INT(6) ZEROFILL

注意：在MySQL 8.0.17及以上版本中，整数类型的显示宽度属性已被弃用。

浮点和定点类型

数据类型	存储空间	精度范围	说明
FLOAT	4字节	单精度，约7位数字	近似值，可能有舍入误差
DOUBLE	8字节	双精度，约15位数字	近似值，可能有舍入误差
DECIMAL(M,D)	变长	取决于M和D	精确值，用于需要精确计算的场景

参数说明：

• M：总位数（精度），最大值为65
• D：小数位数（标度），最大值为30且不能大于M

使用示例：

-- 浮点类型
height FLOAT,
distance DOUBLE,-- 定点类型（精确值）
price DECIMAL(10,2),  -- 总共10位，其中2位小数
tax_rate DECIMAL(5,4)  -- 总共5位，其中4位小数

最佳实践：对于货币和需要精确计算的数值，应使用DECIMAL类型而非FLOAT或DOUBLE，以避免舍入误差。

位值类型

BIT(M)类型用于存储位值，其中M表示每个值的位数，范围从1到64。

使用示例：

flag BIT(1),  -- 可以存储0或1
options BIT(8)  -- 可以存储8位的值

3.4.2 字符串类型

MySQL提供了多种字符串类型，用于存储文本和二进制数据。

定长和变长字符串

数据类型	最大长度	存储特点	适用场景
CHAR(M)	255个字符	固定长度，不足部分用空格填充	长度固定或接近固定的短字符串
VARCHAR(M)	65,535个字节	可变长度，实际使用多少存多少	长度可变的字符串

参数说明：

• M：最大字符数，对于CHAR范围是0到255，对于VARCHAR最大可达65,535（受行大小限制）

使用示例：

-- 定长字符串
country_code CHAR(2),
state_code CHAR(2),-- 变长字符串
name VARCHAR(100),
address VARCHAR(255),
description VARCHAR(1000)

注意：实际可存储的最大长度受字符集影响。例如，utf8mb4字符集中，一个字符最多占4个字节，因此VARCHAR(1000)最多可存储约16,000个字节。

文本类型

对于大文本数据，MySQL提供了以下类型：

数据类型	最大长度	存储特点
TINYTEXT	255个字节	可变长度
TEXT	65,535个字节	可变长度
MEDIUMTEXT	16,777,215个字节	可变长度
LONGTEXT	4,294,967,295个字节	可变长度

使用示例：

comment TEXT,
article_content MEDIUMTEXT,
book_content LONGTEXT

二进制类型

对于二进制数据，MySQL提供了以下类型：

数据类型	对应的字符串类型	存储特点
BINARY(M)	CHAR(M)	固定长度二进制数据
VARBINARY(M)	VARCHAR(M)	可变长度二进制数据
TINYBLOB	TINYTEXT	最大255字节的二进制数据
BLOB	TEXT	最大65,535字节的二进制数据
MEDIUMBLOB	MEDIUMTEXT	最大16,777,215字节的二进制数据
LONGBLOB	LONGTEXT	最大4,294,967,295字节的二进制数据

使用示例：

file_data BLOB,
large_file_data LONGBLOB

枚举和集合类型

ENUM和SET类型用于存储预定义的值列表：

数据类型	特点	最大值数量
ENUM(‘value1’, ‘value2’, …)	只能从列表中选择一个值	65,535
SET(‘value1’, ‘value2’, …)	可以选择多个值的组合	64

使用示例：

-- 枚举类型
status ENUM('active', 'inactive', 'suspended'),
gender ENUM('male', 'female', 'other'),-- 集合类型
permissions SET('read', 'write', 'execute', 'delete')

3.4.3 日期和时间类型

MySQL提供了多种日期和时间数据类型，用于存储时间相关的数据。

数据类型	格式	范围	存储空间
DATE	‘YYYY-MM-DD’	‘1000-01-01’ 到 ‘9999-12-31’	3字节
TIME	‘HH:MM:SS’	‘-838:59:59’ 到 ‘838:59:59’	3字节
DATETIME	‘YYYY-MM-DD HH:MM:SS’	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	8字节
TIMESTAMP	‘YYYY-MM-DD HH:MM:SS’	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC	4字节
YEAR	‘YYYY’	1901 到 2155	1字节

使用示例：

birth_date DATE,
appointment_time TIME,
created_at DATETIME,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
graduation_year YEAR

DATETIME与TIMESTAMP的区别

• DATETIME：与时区无关，存储实际输入的日期和时间
• TIMESTAMP：与时区有关，存储UTC时间，显示时会根据当前会话的时区进行转换
• TIMESTAMP范围较小，但会自动更新（如果设置了ON UPDATE CURRENT_TIMESTAMP）

3.4.4 JSON类型

MySQL 5.7.8及以上版本支持原生JSON数据类型，用于存储JSON（JavaScript Object Notation）文档。

使用示例：

CREATE TABLE products (id INT PRIMARY KEY,name VARCHAR(100),attributes JSON
);INSERT INTO products VALUES (1, 'Laptop', '{"color": "black", "weight": 1.5, "features": ["backlit keyboard", "touch screen"]}');

JSON类型提供了以下优势：

• 自动验证JSON格式
• 优化的存储格式
• 使用JSON函数和运算符进行高效访问

访问JSON数据：

-- 使用->运算符（返回JSON值）
SELECT id, name, attributes->'$.color' AS color FROM products;-- 使用->>运算符（返回字符串值）
SELECT id, name, attributes->>'$.color' AS color FROM products;-- 访问数组元素
SELECT id, name, attributes->'$.features[0]' AS first_feature FROM products;

3.4.5 空间数据类型

MySQL支持空间数据类型，用于存储地理空间数据：

数据类型	描述
GEOMETRY	任何类型的空间值
POINT	点（单个坐标）
LINESTRING	线（多个点连接）
POLYGON	多边形
MULTIPOINT	多个点的集合
MULTILINESTRING	多条线的集合
MULTIPOLYGON	多个多边形的集合
GEOMETRYCOLLECTION	多种空间对象的集合

使用示例：

CREATE TABLE locations (id INT PRIMARY KEY,name VARCHAR(100),location POINT
);INSERT INTO locations VALUES (1, 'Office', ST_GeomFromText('POINT(40.7128 -74.0060)'));

3.4.6 数据类型选择原则

选择合适的数据类型对于数据库性能和存储效率至关重要。以下是一些选择数据类型的原则：

1. 使用最小满足需求的数据类型：例如，如果一个整数列的值永远不会超过127，使用TINYINT而不是INT。

2. 对于字符串，优先考虑VARCHAR而非CHAR：除非字符串长度几乎总是固定的。

3. 对于大文本，使用适当的TEXT类型：根据预期的最大长度选择TEXT、MEDIUMTEXT或LONGTEXT。

4. 对于日期和时间：

   • 只需要日期，使用DATE
   • 只需要时间，使用TIME
   • 需要日期和时间但不关心时区，使用DATETIME
   • 需要自动记录创建/修改时间且关心时区，使用TIMESTAMP

5. 对于货币和精确计算：使用DECIMAL而不是FLOAT或DOUBLE。

6. 对于布尔值：使用TINYINT(1)或BOOLEAN（实际上是TINYINT(1)的别名）。

7. 对于枚举值：如果列只能取有限的几个值，考虑使用ENUM。

8. 对于二进制数据：小文件使用BLOB，大文件考虑存储文件路径而不是文件本身。

9. 考虑索引限制：某些数据类型（如TEXT和BLOB）不能完全索引，只能使用前缀索引。

10. 考虑存储空间和性能平衡：更小的数据类型通常意味着更好的性能，但不应以牺牲功能为代价。

3.5 约束条件

约束是对表中数据的限制，用于确保数据的准确性和一致性。MySQL支持多种类型的约束。

3.5.1 主键约束

主键唯一标识表中的每一行，每个表只能有一个主键。

创建主键

在创建表时定义主键：

-- 方法1：在列定义中指定
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100)
);-- 方法2：使用表级约束
CREATE TABLE employees (id INT,name VARCHAR(100),PRIMARY KEY (id)
);-- 复合主键
CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id)
);

在现有表上添加主键：

ALTER TABLE employees
ADD PRIMARY KEY (id);

自动递增主键

使用AUTO_INCREMENT属性创建自动递增的主键：

CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100)
);

设置自动递增的起始值：

ALTER TABLE employees AUTO_INCREMENT = 1000;

删除主键

删除表的主键：

ALTER TABLE employees
DROP PRIMARY KEY;

注意：如果主键列是AUTO_INCREMENT，必须先移除AUTO_INCREMENT属性，然后才能删除主键。

3.5.2 唯一约束

唯一约束确保列或列组合中的所有值都是唯一的。

创建唯一约束

在创建表时定义唯一约束：

-- 方法1：在列定义中指定
CREATE TABLE employees (id INT PRIMARY KEY,email VARCHAR(100) UNIQUE
);-- 方法2：使用表级约束
CREATE TABLE employees (id INT PRIMARY KEY,email VARCHAR(100),UNIQUE KEY (email)
);-- 命名唯一约束
CREATE TABLE employees (id INT PRIMARY KEY,email VARCHAR(100),CONSTRAINT uk_email UNIQUE (email)
);-- 复合唯一约束
CREATE TABLE employees (id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),UNIQUE KEY (first_name, last_name)
);

在现有表上添加唯一约束：

ALTER TABLE employees
ADD CONSTRAINT uk_email UNIQUE (email);

删除唯一约束

删除唯一约束：

ALTER TABLE employees
DROP INDEX uk_email;

3.5.3 外键约束

外键约束用于维护表之间的引用完整性，确保一个表中的列值与另一个表中的列值匹配。

创建外键约束

在创建表时定义外键约束：

CREATE TABLE departments (id INT PRIMARY KEY,name VARCHAR(100)
);CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(id)
);-- 命名外键约束
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),department_id INT,CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id)
);

在现有表上添加外键约束：

ALTER TABLE employees
ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id);

外键约束选项

外键约束可以包含ON DELETE和ON UPDATE子句，指定当引用的行被删除或更新时的行为：

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(id)ON DELETE CASCADEON UPDATE CASCADE
);

可用的选项包括：

• CASCADE：自动删除或更新相关行
• SET NULL：将外键列设置为NULL
• RESTRICT：阻止删除或更新（默认）
• NO ACTION：类似于RESTRICT
• SET DEFAULT：将外键列设置为默认值（InnoDB不支持）

删除外键约束

删除外键约束：

ALTER TABLE employees
DROP FOREIGN KEY fk_department;

3.5.4 非空约束

非空约束确保列不能包含NULL值。

创建非空约束

在创建表时定义非空约束：

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,email VARCHAR(100) NOT NULL
);

在现有表上添加非空约束：

ALTER TABLE employees
MODIFY COLUMN name VARCHAR(100) NOT NULL;

删除非空约束

删除非空约束：

ALTER TABLE employees
MODIFY COLUMN name VARCHAR(100) NULL;

3.5.5 默认值约束

默认值约束为列指定默认值，当插入新行时如果未提供值，将使用默认值。

创建默认值约束

在创建表时定义默认值约束：

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,status VARCHAR(20) DEFAULT 'active',hire_date DATE DEFAULT CURRENT_DATE()
);

在现有表上添加默认值约束：

ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'active';

删除默认值约束

删除默认值约束：

ALTER TABLE employees
ALTER COLUMN status DROP DEFAULT;

3.5.6 检查约束

检查约束（MySQL 8.0.16及以上版本支持）用于限制列中可接受的值范围。

创建检查约束

在创建表时定义检查约束：

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,age INT CHECK (age >= 18),salary DECIMAL(10,2) CHECK (salary > 0)
);-- 命名检查约束
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,age INT,salary DECIMAL(10,2),CONSTRAINT chk_age CHECK (age >= 18),CONSTRAINT chk_salary CHECK (salary > 0)
);

在现有表上添加检查约束：

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);

删除检查约束

删除检查约束：

ALTER TABLE employees
DROP CONSTRAINT chk_age;

3.5.7 生成列

生成列（MySQL 5.7及以上版本支持）是基于其他列的表达式自动计算的列。

创建生成列

在创建表时定义生成列：

-- 虚拟生成列（不存储，每次查询时计算）
CREATE TABLE rectangles (id INT PRIMARY KEY,width DECIMAL(10,2) NOT NULL,height DECIMAL(10,2) NOT NULL,area DECIMAL(20,4) AS (width * height) VIRTUAL
);-- 存储生成列（计算后存储，查询时不再计算）
CREATE TABLE rectangles (id INT PRIMARY KEY,width DECIMAL(10,2) NOT NULL,height DECIMAL(10,2) NOT NULL,area DECIMAL(20,4) AS (width * height) STORED
);

在现有表上添加生成列：

ALTER TABLE rectangles
ADD COLUMN perimeter DECIMAL(20,4) AS (2 * (width + height)) VIRTUAL;

3.5.8 约束命名约定

为约束命名可以使维护更容易，特别是在需要删除或修改约束时。以下是一些常用的命名约定：

• 主键：pk_表名或pk_列名
• 唯一约束：uk_表名_列名或uk_列名
• 外键：fk_当前表_引用表或fk_当前列_引用列
• 检查约束：chk_表名_列名或chk_列名_规则

例如：

CREATE TABLE orders (id INT,customer_id INT,order_date DATE,total DECIMAL(10,2),CONSTRAINT pk_orders PRIMARY KEY (id),CONSTRAINT uk_orders_reference UNIQUE (reference_number),CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(id),CONSTRAINT chk_orders_total CHECK (total > 0)
);

通过合理使用约束条件，可以确保数据库中的数据符合业务规则，提高数据的准确性和一致性。约束条件是数据库设计中的重要组成部分，应该在设计阶段就仔细考虑和规划。