表结构操作

一、创建表

1.1 基本语法

1.1.1 CREATE TABLE

语法格式：

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [列级约束],
    列名2 数据类型 [列级约束],
    ...
    [表级约束]
) [表选项];

参数说明：

1.1.2 创建示例

创建学生表：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    gender ENUM('男', '女') DEFAULT '男',
    email VARCHAR(100) UNIQUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建课程表：

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credit TINYINT UNSIGNED DEFAULT 1,
    teacher VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建成绩表：

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    score DECIMAL(5, 2),
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.2 表选项

1.2.1 存储引擎

语法格式：

ENGINE = 存储引擎名

常用引擎：

-- InnoDB引擎（默认）
CREATE TABLE innodb_table (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

-- MyISAM引擎
CREATE TABLE myisam_table (
    id INT PRIMARY KEY
) ENGINE=MyISAM;

-- MEMORY引擎
CREATE TABLE memory_table (
    id INT PRIMARY KEY
) ENGINE=MEMORY;

1.2.2 字符集

语法格式：

DEFAULT CHARSET = 字符集名

示例：

CREATE TABLE utf8_table (
    id INT PRIMARY KEY,
    content TEXT
) DEFAULT CHARSET=utf8mb4;

1.2.3 自增起始值

语法格式：

AUTO_INCREMENT = 起始值

示例：

CREATE TABLE auto_start (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) AUTO_INCREMENT = 1000;

1.2.4 表注释

语法格式：

COMMENT = '表注释'

示例：

CREATE TABLE comment_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) COMMENT = '这是一个示例表';

1.3 复制表

1.3.1 复制表结构

语法格式：

CREATE TABLE 新表名 LIKE 源表名;

示例：

CREATE TABLE students_backup LIKE students;

1.3.2 复制表结构和数据

语法格式：

CREATE TABLE 新表名 AS SELECT * FROM 源表名;

示例：

CREATE TABLE students_copy AS SELECT * FROM students;

1.3.3 复制部分数据

CREATE TABLE students_2024 AS 
SELECT * FROM students 
WHERE YEAR(create_time) = 2024;

二、查看表

2.1 查看所有表

语法格式：

SHOW TABLES;

输出示例：

+------------------+
| Tables_in_school |
+------------------+
| courses          |
| scores           |
| students         |
+------------------+

2.2 查看表结构

2.2.1 DESC命令

语法格式：

DESC 表名;

或

DESCRIBE 表名;

示例：

DESC students;

输出示例：

+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| id          | int                  | NO   | PRI | NULL              | auto_increment              |
| name        | varchar(50)          | NO   |     | NULL              |                             |
| age         | tinyint unsigned     | YES  |     | NULL              |                             |
| gender      | enum('男','女')      | YES  |     | 男                |                             |
| email       | varchar(100)         | YES  | UNI | NULL              |                             |
| create_time | timestamp            | YES  |     | CURRENT_TIMESTAMP |                             |
+-------------+----------------------+------+-----+-------------------+-----------------------------+

2.2.2 SHOW COLUMNS

语法格式：

SHOW COLUMNS FROM 表名;

示例：

SHOW COLUMNS FROM students;

2.2.3 SHOW CREATE TABLE

语法格式：

SHOW CREATE TABLE 表名\G

示例：

SHOW CREATE TABLE students\G

输出示例：

*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint unsigned DEFAULT NULL,
  `gender` enum('男','女') DEFAULT '男',
  `email` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2.3 查看表状态

语法格式：

SHOW TABLE STATUS LIKE '表名'\G

示例：

SHOW TABLE STATUS LIKE 'students'\G

输出示例：

*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 100
 Avg_row_length: 163
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 101
    Create_time: 2024-01-01 00:00:00
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 

三、修改表

3.1 ALTER TABLE语法

3.1.1 添加列

语法格式：

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束];

示例：

-- 添加单列
ALTER TABLE students ADD COLUMN phone VARCHAR(20);

-- 添加到指定位置
ALTER TABLE students ADD COLUMN address VARCHAR(200) AFTER email;

-- 添加到第一列
ALTER TABLE students ADD COLUMN student_no VARCHAR(20) FIRST;

3.1.2 修改列

语法格式：

ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型 [新约束];

或

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新数据类型 [新约束];

示例：

-- 修改列类型
ALTER TABLE students MODIFY COLUMN phone VARCHAR(50);

-- 修改列名和类型
ALTER TABLE students CHANGE COLUMN phone mobile VARCHAR(50);

3.1.3 删除列

语法格式：

ALTER TABLE 表名 DROP COLUMN 列名;

示例：

ALTER TABLE students DROP COLUMN address;

3.2 修改约束

3.2.1 添加主键

语法格式：

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

示例：

ALTER TABLE students ADD PRIMARY KEY (id);

3.2.2 删除主键

语法格式：

ALTER TABLE 表名 DROP PRIMARY KEY;

示例：

ALTER TABLE students DROP PRIMARY KEY;

3.2.3 添加外键

语法格式：

ALTER TABLE 表名 
ADD CONSTRAINT 外键名 
FOREIGN KEY (列名) REFERENCES 主表(主键列);

示例：

ALTER TABLE scores
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(id);

3.2.4 删除外键

语法格式：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

示例：

ALTER TABLE scores DROP FOREIGN KEY fk_student;

3.2.5 添加唯一约束

语法格式：

ALTER TABLE 表名 ADD UNIQUE (列名);

示例：

ALTER TABLE students ADD UNIQUE (email);

3.2.6 添加索引

语法格式：

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

示例：

ALTER TABLE students ADD INDEX idx_name (name);

3.3 修改表名

3.3.1 RENAME语法

语法格式：

ALTER TABLE 旧表名 RENAME TO 新表名;

或

RENAME TABLE 旧表名 TO 新表名;

示例：

ALTER TABLE students RENAME TO student_info;

或

RENAME TABLE students TO student_info;

3.4 修改表选项

3.4.1 修改存储引擎

语法格式：

ALTER TABLE 表名 ENGINE = 存储引擎名;

示例：

ALTER TABLE students ENGINE = InnoDB;

3.4.2 修改字符集

语法格式：

ALTER TABLE 表名 DEFAULT CHARSET = 字符集名;

示例：

ALTER TABLE students DEFAULT CHARSET = utf8mb4;

四、删除表

4.1 DROP TABLE

4.1.1 基本语法

语法格式：

DROP TABLE [IF EXISTS] 表名1, 表名2, ...;

参数说明：

删除单个表：

DROP TABLE students;

删除多个表：

DROP TABLE students, courses, scores;

避免错误：

DROP TABLE IF EXISTS students;

4.1.2 删除被外键引用的表

错误示例：

DROP TABLE students;

错误信息：

ERROR 3730 (HY000): Cannot drop table 'students' because it is referenced by foreign key constraint 'fk_student' on table 'scores'.

正确方法：

-- 方法1：先删除外键
ALTER TABLE scores DROP FOREIGN KEY fk_student;
DROP TABLE students;

-- 方法2：先删除引用表
DROP TABLE scores;
DROP TABLE students;

4.2 TRUNCATE TABLE

4.2.1 清空表数据

语法格式：

TRUNCATE TABLE 表名;

示例：

TRUNCATE TABLE students;

4.2.2 TRUNCATE与DELETE区别

五、表约束

5.1 主键约束

5.1.1 PRIMARY KEY

特点：

• 唯一标识一行
• 不能为NULL
• 每个表只能有一个主键
• 自动创建索引

列级约束：

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

表级约束：

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

复合主键：

CREATE TABLE composite_key (
    student_id INT,
    course_id INT,
    score DECIMAL(5, 2),
    PRIMARY KEY (student_id, course_id)
);

5.2 外键约束

5.2.1 FOREIGN KEY

特点：

• 保证数据一致性
• 建立表间关系
• 自动创建索引

语法格式：

[CONSTRAINT 外键名]
FOREIGN KEY (外键列)
REFERENCES 主表(主键列)
[ON DELETE 参照动作]
[ON UPDATE 参照动作]

参照动作：

示例：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

5.3 唯一约束

5.3.1 UNIQUE

特点：

• 值必须唯一
• 允许NULL（多个NULL）
• 自动创建索引

列级约束：

CREATE TABLE unique_test (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

表级约束：

CREATE TABLE unique_test (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE (email)
);

复合唯一约束：

CREATE TABLE unique_composite (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    UNIQUE (user_id, product_id)
);

5.4 非空约束

5.4.1 NOT NULL

特点：

• 值不能为NULL
• 每列单独设置

示例：

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

5.5 默认约束

5.5.1 DEFAULT

特点：

• 设置默认值
• 插入时不指定则使用默认值

示例：

CREATE TABLE default_test (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5.6 检查约束

5.6.1 CHECK

特点：

• MySQL 8.0.16+支持
• 限制列值范围

示例：

CREATE TABLE check_test (
    id INT PRIMARY KEY,
    age TINYINT,
    CHECK (age >= 0 AND age <= 120)
);

六、临时表

6.1 创建临时表

6.1.1 TEMPORARY关键字

语法格式：

CREATE TEMPORARY TABLE 表名 (
    ...
);

示例：

CREATE TEMPORARY TABLE temp_students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

6.2 临时表特点

特点：

• 仅在当前会话可见
• 会话结束自动删除
• 与普通表同名时优先使用临时表

示例：

-- 创建临时表
CREATE TEMPORARY TABLE temp_data (
    id INT,
    value VARCHAR(100)
);

-- 插入数据
INSERT INTO temp_data VALUES (1, '测试数据');

-- 查询数据
SELECT * FROM temp_data;

-- 断开连接后临时表自动删除

七、本章小结

7.1 核心要点

✅ CREATE TABLE创建表 ✅ ALTER TABLE修改表结构 ✅ DROP TABLE删除表 ✅ 约束保证数据完整性 ✅ 临时表用于临时存储

7.2 验证清单

完成本章学习后，请确认您能够：

• 创建包含各种约束的表
• 查看表结构和状态
• 修改表结构
• 删除表和清空表数据
• 使用各种约束保证数据完整性