3.4 一篇文章带你了解MySQL表的管理小白必备（创建表、查看表结构、修改表、删除表）

---

3.4.1数据类型

MySQL支持多种数据类型，用于存储不同类型的数据。以下是一些常用的MySQL数据类型：

1. 整数类型：

   • 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 或 INTEGER：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（无符号）

2. 浮点数和小数类型：

   • FLOAT：单精度浮点数
   • DOUBLE：双精度浮点数
   • DECIMAL 或 NUMERIC：小数，可指定精度

3. 日期和时间类型：

   • DATE：日期，格式为 YYYY-MM-DD
   • TIME：时间，格式为 HH:MM:SS
   • DATETIME：日期和时间，格式为 YYYY-MM-DD HH:MM:SS
   • TIMESTAMP：时间戳，格式为 YYYY-MM-DD HH:MM:SS
   • YEAR：年份，格式为 YYYY 或 YY

4. 字符串类型：

   • CHAR：固定长度字符串，长度可变
   • VARCHAR：可变长度字符串，长度可变
   • TINYTEXT：最大长度 255 字符
   • TEXT：最大长度 65,535 字符
   • MEDIUMTEXT：最大长度 16,777,215 字符
   • LONGTEXT：最大长度 4,294,967,295 字符

5. 二进制数据类型：

   • BINARY：固定长度二进制字符串
   • VARBINARY：可变长度二进制字符串
   • TINYBLOB：最大长度 255 字节
   • BLOB：最大长度 65,535 字节
   • MEDIUMBLOB：最大长度 16,777,215 字节
   • LONGBLOB：最大长度 4,294,967,295 字节

6. 枚举类型：

   • ENUM：一个字符串对象，可从给定的值列表中选择

7. 集合类型：

   • SET：一个字符串对象，可从给定的值列表中选择多个值

8. 空间数据类型：

   • GEOMETRY：用于存储空间数据，如点、线、多边形等

选择正确的数据类型对于数据库性能和存储效率至关重要。例如，使用整数类型存储数值，使用日期和时间类型存储日期和时间信息，使用字符串类型存储文本数据。在设计数据库时，应根据数据的特性和查询需求选择合适的数据类型。

3.4.2 创建表

在MySQL中创建表是一个简单的过程，可以通过以下步骤完成：

1. 使用 CREATE TABLE 语句：这是创建新表的基本命令。

2. 定义列和数据类型：为表中的每个字段指定名称和数据类型。

3. 设置主键：为主键字段指定 PRIMARY KEY，以确保数据的唯一性和索引。

4. 添加索引：为提高查询效率，可以为表中的某些字段添加索引。

5. 设置外键：如果需要，可以为表设置外键，以维护与其他表的关系。

6. 定义其他约束：如 NOT NULL、UNIQUE、DEFAULT 等。

下面是一个创建表的示例：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE,
    hire_date DATE,
    department VARCHAR(50),
    salary DECIMAL(10, 2), -- 10位数字，其中2位是小数
    supervisor_id INT,
    INDEX idx_department (department),
    FOREIGN KEY (supervisor_id) REFERENCES employees(id)
);

在这个例子中，我们创建了一个名为 employees 的表，它有8个字段：id、first_name、last_name、birth_date、hire_date、department、salary 和 supervisor_id。id 字段被设置为主键，并且自动递增。first_name 和 last_name 字段不允许为空。我们还为 department 字段创建了一个索引，并设置了一个外键约束，将 supervisor_id 与同一表的 id 字段关联。

3.4.3 查看表结构

在MySQL中，查看表结构可以使用以下几种命令：

1. DESCRIBE 或 DESC：
   这个命令提供了表的列信息，包括列名、数据类型、是否可以为NULL、默认值以及是否是主键等。

   DESCRIBE table_name;
   

   或者

   DESC table_name;
   

2. SHOW COLUMNS：
   这个命令同样显示表的列信息，但输出格式可能略有不同。

   SHOW COLUMNS FROM table_name;
   

3. SHOW CREATE TABLE：
   这个命令显示创建表的完整SQL语句，包括表的所有属性、索引、外键等。

   SHOW CREATE TABLE table_name;
   

4. INFORMATION_SCHEMA：
   这是一个特殊的数据库，其中包含了所有其他数据库的元数据。你可以查询 INFORMATION_SCHEMA.COLUMNS 表来获取特定表的列信息。

   SELECT *
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';
   

5. SHOW INDEX：
   这个命令显示表的索引信息，包括索引的名字、列、类型等。

   SHOW INDEX FROM table_name;
   

3.4.4 修改表

在MySQL中，修改表结构可以通过多种方式进行，包括添加新列、删除列、修改列的数据类型、重命名表等。以下是一些常用的修改表的SQL命令：

1. 添加列：
   使用 ALTER TABLE 命令添加新列。

   ALTER TABLE table_name
   ADD column_name column_type;
   

2. 删除列：
   使用 ALTER TABLE 命令删除列。

   ALTER TABLE table_name
   DROP COLUMN column_name;
   

3. 修改列的数据类型：
   使用 ALTER TABLE 命令修改列的数据类型。

   ALTER TABLE table_name
   MODIFY column_name column_type;
   

4. 重命名列：
   使用 ALTER TABLE 命令重命名列。

   ALTER TABLE table_name
   CHANGE old_column_name new_column_name column_type;
   

5. 重命名表：
   使用 RENAME TABLE 命令重命名表。

   RENAME TABLE old_table_name TO new_table_name;
   

6. 添加索引：
   使用 ALTER TABLE 命令添加索引。

   ALTER TABLE table_name
   ADD INDEX index_name (column_name);
   

7. 删除索引：
   使用 ALTER TABLE 命令删除索引。

   ALTER TABLE table_name
   DROP INDEX index_name;
   

8. 添加外键约束：
   使用 ALTER TABLE 命令添加外键约束。

   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(other_column);
   

9. 删除外键约束：
   使用 ALTER TABLE 命令删除外键约束。

   ALTER TABLE table_name
   DROP FOREIGN KEY constraint_name;
   

10. 修改表的存储引擎：
    使用 ALTER TABLE 命令修改表的存储引擎。

    ALTER TABLE table_name
    ENGINE=storage_engine;
    

3.4.5 删除表

在MySQL中删除表非常简单，可以使用 DROP TABLE 语句。以下是删除表的基本语法：

DROP TABLE IF EXISTS table_name;

这里的 IF EXISTS 是一个可选的子句，如果存在指定的表，则删除它。如果不使用 IF EXISTS，而表不存在，那么MySQL会报错。

如果你想一次性删除多个表，可以列出所有要删除的表名，用逗号分隔：

DROP TABLE IF EXISTS table1, table2, table3;

警告：DROP TABLE 是一个不可逆的操作，一旦执行，表及其所有数据将被永久删除。在执行此操作之前，请确保已经备份了任何重要数据。

在删除表之前，如果表有外键约束，可能需要先删除或更新这些外键约束，否则可能会遇到错误。如果不确定，可以先查看表的外键约束：

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = 'your_database_name';

替换 your_database_name 为你的数据库名。这将列出所有有外键约束的表和列，以及它们引用的其他表和列。根据这些信息，你可能需要先删除或更新这些外键约束，然后再尝试删除表。