MySQL 教程

MySQL 数据表基本操作

🎉摘要：本文详细介绍了在MySQL数据库中如何创建数据表，包括CREATE TABLE基本语法、字段定义、约束和注释。同时讲解了如何使用SHOW、DESC等命令查看表结构，以及使用ALTER TABLE语句添加、修改、删除字段和索引，最后说明了DROP TABLE和TRUNCATE TABLE删除表的区别与操作。

创建好数据库后，下一步就是创建数据表。表是存储数据的基本单位，设计良好的表结构是数据库应用成功的基础。下面将详细介绍如何在 MySQL 创建数据表。

创建数据表

创建数据表使用 CREATE TABLE，语法如下：

CREATE TABLE [IF NOT EXISTS] 表名 (
    字段1 数据类型 [约束条件] [COMMENT '字段注释'],
    字段2 数据类型 [约束条件] [COMMENT '字段注释'],
    ...
    [主键约束],
    [唯一索引],
    [外键约束],
    [索引]
) [ENGINE=InnoDB]
  [DEFAULT CHARSET=utf8mb4]
  [COLLATE=utf8mb4_unicode_ci]
  [COMMENT='表注释'];

我们先来看一个简单的例子，创建一个用户表：

-- 切换到 shop 数据库
mysql> USE shop;
Database changed

-- 创建 users 数据表
mysql> CREATE TABLE users (
    ->     user_id INT AUTO_INCREMENT PRIMARY KEY,
    ->     username VARCHAR(50) NOT NULL,
    ->     email VARCHAR(100) NOT NULL,
    ->     phone VARCHAR(20),
    ->     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

下面逐行解释上述的SQL语句

  • create table users：该语句表示要创建名为 users 的数据表，其中 create table 是语法。

  • user_id INT AUTO_INCREMENT PRIMARY KEY：创建用户ID字段，类型是整数类型，字段的值自动递增（1，2，3，4，.... 逐渐递增），并且设为主键。

  • username VARCHAR(50) NOT NULL：创建用户名字段，最多存储50个字符，不允许为空。

  • email VARCHAR(100) NOT NULL：创建邮箱字段，最多存储100个字符，不允许为空。

  • phone VARCHAR(20)：创建电话字段，最多存储20个字符，允许为空。

  • created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP：创建时间，默认为当前时间。

  • ENGINE=InnoDB：使用 InnoDB 存储引擎（MySQL 8.0 默认就是 InnoDB）

  • DEFAULT CHARSET=utf8mb4：使用 utf8mb4 字符集

再创建一个商品表，稍微复杂一些，如下：

-- 创建商品表
mysql> CREATE TABLE products (
    ->     product_id INT AUTO_INCREMENT PRIMARY KEY,
    ->     product_name VARCHAR(200) NOT NULL COMMENT '产品名称',
    ->     category_id INT COMMENT '分类ID',
    ->     price DECIMAL(10, 2) NOT NULL COMMENT '价格',
    ->     cost DECIMAL(10, 2) COMMENT '',
    ->     stock INT DEFAULT 0 COMMENT '',
    ->     description TEXT COMMENT '描述',
    ->     is_on_sale TINYINT(1) DEFAULT 1 COMMENT '',
    ->     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ->     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     INDEX idx_category (category_id),
    ->     INDEX idx_price (price)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
Query OK, 0 rows affected, 1 warning (0.01 sec)

这次的 CREATE TABLE 语句和上面创建用户表的存在一些差异，差异如下：

  • COMMENT：给字段或表添加注释，方便理解用途，例如  price DECIMAL(10, 2) NOT NULL COMMENT '价格'

  • DECIMAL(10, 2)：定点数，即浮点数，共 10 位数字，其中 2 位小数，适合存储金额。

  • TINYINT(1)：很小的整数，这里用来表示布尔值（0 或 1）

  • ON UPDATE CURRENT_TIMESTAMP：记录更新时间，数据修改时自动更新。

  • INDEX：创建索引，加快查询速度。

更多关于数据类型、索引等知识后续将专题介绍，这里只需简单了解即可。

查看表结构

创建数据表后，我们可以通过一些命令查看表信息，以及表结构，如下：

（1）查看当前数据库中的所有表

mysql> show tables;
+----------------+
| Tables_in_shop |
+----------------+
| products       |
| users          |
+----------------+
2 rows in set (0.00 sec)

上面查询出来的不就是我们前面创建的两张数据表的表名吗。

（2）查看表结构

使用 describe 或者 desc 命令查看表结构信息，如下：

mysql> describe users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| user_id    | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   |     | NULL              |                   |
| email      | varchar(100) | NO   |     | NULL              |                   |
| phone      | varchar(20)  | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.01 sec)

mysql> desc users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| user_id    | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   |     | NULL              |                   |
| email      | varchar(100) | NO   |     | NULL              |                   |
| phone      | varchar(20)  | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

还可以使用 show columns from 命令，例如：

mysql> show columns from users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| user_id    | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   |     | NULL              |                   |
| email      | varchar(100) | NO   |     | NULL              |                   |
| phone      | varchar(20)  | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

如果要查看创建表的完整SQL语句，请使用 show create table 语句，例如：

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

上面语句最后的 \G 用来格式化输出的。

还可以使用 show table status like 查看表的状态信息，例如：

mysql> show table status like 'users' \G
*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2026-07-04 07:07:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

修改表结构

实际开发中，需求变更经常需要修改表结构。MySQL提供了 ALTER TABLE 语句来完成这类操作。下面将介绍如何添加、修改、删除字段，以及修改表名。

添加字段

使用“alter table 数据表 add column”语句添加字段，例如：

-- 在 users 表添加一个年龄字段
mysql> ALTER TABLE users ADD COLUMN age INT COMMENT '年龄';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 指定添加的位置（在第一列）
mysql> ALTER TABLE users ADD COLUMN user_code VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 添加到某列之后
mysql> ALTER TABLE users ADD COLUMN nickname VARCHAR(50) AFTER username;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用 desc 命令查看 users 表结构，如下：

mysql> desc users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| user_code  | varchar(20)  | YES  |     | NULL              |                   |
| user_id    | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   |     | NULL              |                   |
| nickname   | varchar(50)  | YES  |     | NULL              |                   |
| email      | varchar(100) | NO   |     | NULL              |                   |
| phone      | varchar(20)  | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| age        | int          | YES  |     | NULL              |                   |
+------------+--------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)

其中，user_code 字段被添加到第一位置，age 默认在最后，nickname 位于 username 字段后面。

修改字段

使用“alter table 表名 modify column”语句修改已经存在的字段，例如：

-- 修改字段类型
mysql> ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改字段名和类型
-- 将 phone 字段改为 mobile 字段
mysql> ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30) COMMENT '手机号';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改字段的默认值
mysql> ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行完上面语句后，使用 desc 命令再次查看 users 表的结构，如下：

mysql> desc users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| user_code  | varchar(20)  | YES  |     | NULL              |                   |
| user_id    | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   |     | NULL              |                   |
| nickname   | varchar(50)  | YES  |     | NULL              |                   |
| email      | varchar(100) | NO   |     | NULL              |                   |
| mobile     | varchar(30)  | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| age        | int          | YES  |     | 0                 |                   |
+------------+--------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)

上面结果中，存在了 mobile 字段，而且 age 字段的默认值为 0，上面所有操作均成功了。

删除字段

可以使用“alter table 表名 drop column”语句删除数据表中的指定的字段，例如：

-- 删除 age 字段
mysql> ALTER TABLE users DROP COLUMN age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构
mysql> desc users;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| user_code  | varchar(20)  | YES  |     | NULL              |                   |
| user_id    | int          | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)  | NO   |     | NULL              |                   |
| nickname   | varchar(50)  | YES  |     | NULL              |                   |
| email      | varchar(100) | NO   |     | NULL              |                   |
| mobile     | varchar(30)  | YES  |     | NULL              |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
7 rows in set (0.00 sec)

上面输出可知，age 字段已经被成功删除。

添加和删除索引

可以使用“alter table 表名 index”语句为表添加索引，也可以使用“alter table 表名 drop index”语句删除索引，例如：

（1）添加普通索引，使用“alter table 表名 add index”语句，例如：

-- 为 email 字段添加索引
mysql> ALTER TABLE users ADD INDEX idx_email (email);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

（2）添加唯一索引，使用“alter table 表名 add unique index”语句，例如：

-- 为 username 字段添加唯一索引
mysql> ALTER TABLE users ADD UNIQUE INDEX uk_username (username);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

（3）删除索引，使用“alter table 表名 drop index”语句，例如：

-- 删除上面为 email 字段创建的索引
mysql> ALTER TABLE users DROP INDEX idx_email;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表名

使用“alter table 表名 rename to”语句修改表名称，例如：

-- 将 users 表重命名为 user_info
mysql> ALTER TABLE users RENAME TO user_info;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_shop |
+----------------+
| products       |
| user_info      |
+----------------+
2 rows in set (0.00 sec)

或者使用 rename table 语句重命名表，例如：

-- 将 user_info 表重命名为 users
mysql> rename table user_info to users;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_shop |
+----------------+
| products       |
| users          |
+----------------+
2 rows in set (0.00 sec)

删除数据表

删除数据表会连同数据和结构一起删除，因此需要特别谨慎操作，删除表的语句为“drop table”，例如：

-- 删除表（连同数据和结构一起删除）
mysql> DROP TABLE users;
Query OK, 0 rows affected (0.01 sec)

-- 如果存在才删除
mysql> DROP TABLE IF EXISTS users;
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 清空表数据但保留表结构（速度快，不可回滚）
mysql> truncate table users;
Query OK, 0 rows affected (0.01 sec)

DROP TABLE 和 TRUNCATE TABLE 的区别：

  • DROP TABLE：删除整个表，包括结构、数据、索引等，不可恢复。

  • TRUNCATE TABLE：只删除数据，保留表结构，速度比 DELETE 快，但不可回滚。

更多知识请阅读后续章节……谢谢！！！

  

上一章：MySQL 数据库基本操作
说说我的看法
全部评论（）
没有评论
关于
本网站专注于 Java、数据库（MySQL、Oracle）、Linux、软件架构及大数据等多领域技术知识分享。涵盖丰富的原创与精选技术文章，助力技术传播与交流。无论是技术新手渴望入门，还是资深开发者寻求进阶，这里都能为您提供深度见解与实用经验，让复杂编码变得轻松易懂，携手共赴技术提升新高度。如有侵权，请来信告知：hxstrive@outlook.com
链接
其他应用
公众号
©2018-2020 hxstrive.com 版权所有 ICP证：蜀ICP备14031461号-2   川公网安备51010802032098