文章详情页_MySQL 表管理

表（Table）是数据库存储数据的主要形式，由行（Row）和列（Column）组成，类似于常见的电子表格。

MySQL 中的表与其他数据库的最大区别在于它们可以使用不同的存储引擎（Storage Engine）。存储引擎是 MySQL 中用于管理、访问和修改物理数据的组件，不同的存储引擎提供了不同的功能和特性。

从 MySQL 5.5 开始，默认使用 InnoDB 存储引擎，支持事务处理（ACID）、行级锁定、故障恢复、多版本并发控制（MVCC）以及外键约束。大多数情况下，推荐使用默认的 InnoDB 存储引擎。

创建表

在 MySQL 数据库中创建一张表的基本语法如下：

CREATE TABLE [IF NOT EXISTS] table_name (
   column_1 data_type constraints,
   column_2 data_type constraints,
   ...,
   column_n data_type constraints
);

table_name：指定新建的表名，表名在数据库中必须唯一。如果该表已经存在，系统将会提示错误；此时可以使用 IF NOT EXISTS 选项，创建之前检查是否已经存在同名的表，避免产生错误信息
column_name：指定列的名称
data_type：指定列的数据类型
constraints：指定列的约束条件

create table if not exists emp (
  empno    int(4)   not null comment '员工编号',
  ename    varchar(10)       comment '员工姓名',
  job      varchar(9)        comment '工作',
  mgr      int(4)            comment '经理',
  hiredate date              comment '雇佣日期',
  sal      decimal(7,2)      comment '薪水',
  comm     decimal(7,2)      comment '奖金',
  deptno   int(2)            comment '部门编号'
) 
comment '员工表';

查看表定义

执行完建表语句之后，可以使用 DESC 命令查看表的结构

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

使用 SHOW 命令查看建表语句

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `empno` int(4) NOT NULL COMMENT '员工编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '工作',
  `mgr` int(4) DEFAULT NULL COMMENT '经理',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪水',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) DEFAULT NULL COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
1 row in set (0.00 sec)

注：\G 选项的含义是使得记录能够按照字段竖向排列，以便更好地显示内容较长地记录。

修改表

对于已经创建好的表，尤其是已经有大量数据的表，如果需要做一些结构上的改变，可以先将表删除，然后再按照新的表定义重建表。

在大多数情况下，表结构的更改都使用 ALTER 语句。

修改表名称

更改表名称，语法如下：

ALTER TABLE table_name RENAME TO new_table_name;

例 1：将表 emp 重命名为 emp1，命令如下：

mysql> alter table emp rename to emp1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc emp;
ERROR 1146 (42S02): Table 'scott.emp' doesn't exist

增加表字段

增加表字段，语法如下：

ALTER TABLE table_name ADD [COLUMN] column_name data_type [ FIRST | AFTER other_column];

增加字段时的选项和创建表时的选项相同；FIRST 表示将新增的字段作为表的第一个字段，AFTER 表示在某个字段之后新增一个字段，默认在表的最后新增字段。

例 1：在表 emp 中新增字段 age，类型为 int(3)

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| age      | int(3)       | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

例 2：在表 emp 中新增字段 address，类型为 varchar(100)，并指定其顺序在 deptno 字段之后

mysql> alter table emp add column address varchar(100) after deptno;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| age      | int(3)       | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

例 3：在表 emp 中新增字段 gender，类型为 varchar(1)，并添加约束条件

mysql> alter table emp add column gender varchar(1) not null;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| age      | int(3)       | YES  |     | NULL    |       |
| gender   | varchar(1)   | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

删除表字段

删除表字段，语法如下：

ALTER TABLE table_name DROP [COLUMN] column_name;

例 1：删除字段 age

mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| gender   | varchar(1)   | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

修改字段类型

修改表字段类型，语法如下：

ALTER TABLE table_name MODIFY [COLUMN] column_name data_type [ FIRST | AFTER other_column];

例1：修改表 emp 的 ename 字段定义，将 varchar(10) 修改为 varchar(20)

mysql> alter table emp modify column ename varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| gender   | varchar(1)   | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

例 2：当表中存在数据时，不支持将字段精度调整至该字段允许的最小精度

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| gender   | varchar(1)   | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)


mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | address | gender |
+-------+--------+-----------+------+------------+---------+---------+--------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | NULL    |        |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | NULL    |        |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | NULL    |        |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | NULL    |        |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | NULL    |        |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | NULL    |        |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | NULL    |        |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | NULL    |        |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | NULL    |        |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | NULL    |        |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | NULL    |        |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | NULL    |        |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | NULL    |        |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | NULL    |        |
+-------+--------+-----------+------+------------+---------+---------+--------+---------+--------+
14 rows in set (0.00 sec)


mysql> alter table emp modify job varchar(5);
ERROR 1265 (01000): Data truncated for column 'job' at row 2


--表 emp 的 sal 字段原始类型为 decimal(7,2)，由上述结果集可知，调整为 decimal(5,2) 会失败
mysql> alter table emp modify sal decimal(5,2);
ERROR 1264 (22003): Out of range value for column 'sal' at row 2


--但调整为 decimal(6.2) 是允许的
mysql> alter table emp modify sal decimal(6,2);
Query OK, 14 rows affected (0.05 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(6,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address  | varchar(100) | YES  |     | NULL    |       |
| gender   | varchar(1)   | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

修改字段名

删除字段名称，语法如下：

ALTER TABLE table_name CHANGE [COLUMN] old_column_name new_column_name data_type [ FIRST | AFTER other_column];

例 1：将字段 address 重命名为 address1，同时修改字段类型为 varchar(200)

mysql> alter table emp change column address address1 varchar(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(6,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address1 | varchar(200) | YES  |     | NULL    |       |
| gender   | varchar(1)   | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

例 2：仅修改 gender 字段的类型为 varchar(10)

mysql> alter table emp change column gender gender varchar(10);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(4)       | NO   |     | NULL    |       |
| ename    | varchar(20)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | int(4)       | YES  |     | NULL    |       |
| hiredate | date         | YES  |     | NULL    |       |
| sal      | decimal(6,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(2)       | YES  |     | NULL    |       |
| address1 | varchar(200) | YES  |     | NULL    |       |
| gender   | varchar(10)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

注：change 和 mofidy 都可以修改表的定义，不同的是 change 后面需要写两次列名，但是 change 的优点是可以同时修改列名称，modify 则不能。

修改字段排列顺序

字段的增加和修改语法中，都有一个可选项 [ FIRST | AFTER other_column]，这个选项可以用来修改字段在表中的位置，ADD 操作增加的新字段默认是加在表的最后位置，而 CHANGE/MODIFY 操作默认都不会改变字段的位置。

例 1：表 emp 新增字段 birth_day，其位置在 ename 后

mysql> alter table emp add column birth_day date after ename;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| empno     | int(4)       | NO   |     | NULL    |       |
| ename     | varchar(20)  | YES  |     | NULL    |       |
| birth_day | date         | YES  |     | NULL    |       |
| job       | varchar(9)   | YES  |     | NULL    |       |
| mgr       | int(4)       | YES  |     | NULL    |       |
| hiredate  | date         | YES  |     | NULL    |       |
| sal       | decimal(6,2) | YES  |     | NULL    |       |
| comm      | decimal(7,2) | YES  |     | NULL    |       |
| deptno    | int(2)       | YES  |     | NULL    |       |
| address1  | varchar(200) | YES  |     | NULL    |       |
| gender    | varchar(10)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

修改字段 gender，将其放在最前面

mysql> alter table emp modify gender varchar(10) first;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| gender    | varchar(10)  | YES  |     | NULL    |       |
| empno     | int(4)       | NO   |     | NULL    |       |
| ename     | varchar(20)  | YES  |     | NULL    |       |
| birth_day | date         | YES  |     | NULL    |       |
| job       | varchar(9)   | YES  |     | NULL    |       |
| mgr       | int(4)       | YES  |     | NULL    |       |
| hiredate  | date         | YES  |     | NULL    |       |
| sal       | decimal(6,2) | YES  |     | NULL    |       |
| comm      | decimal(7,2) | YES  |     | NULL    |       |
| deptno    | int(2)       | YES  |     | NULL    |       |
| address1  | varchar(200) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

删除表

删除表的语法如下：

DROP TABLE table_name;

例 1：删除表 emp

mysql> drop table emp;
Query OK, 0 rows affected (0.01 sec)

注释

在创建表时，可以指定字段及表的注释，也可以在表创建好后，修改表及字段的注释。

表注释

修改表注释

mysql> alter table emp comment '员工表';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表注释

--在生成的 SQL 语句中看
mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `empno` int(4) NOT NULL COMMENT '员工编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '工作',
  `mgr` int(4) DEFAULT NULL COMMENT '经理',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪水',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) DEFAULT NULL COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
1 row in set (0.00 sec)


--在元数据的表里面看
mysql> use information_schema;
Database changed

mysql> select * from tables where table_schema = 'scott' and table_name = 'EMP' \G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: scott
     TABLE_NAME: emp
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 14
 AVG_ROW_LENGTH: 1170
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2021-11-23 14:40:28
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: 员工表
1 row in set (0.00 sec)

字段注释

修改字段的注释

mysql> alter table emp modify empno int(4) comment '员工编号';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看字段的注释

mysql> show full columns from emp;
+----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+----------+
| Field    | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment  |
+----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+----------+
| empno    | int(4)       | NULL            | YES  |     | NULL    |       | select,insert,update,references | 员工编号 |
| ename    | varchar(10)  | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references | 员工姓名 |
| job      | varchar(9)   | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references | 工作     |
| mgr      | int(4)       | NULL            | YES  |     | NULL    |       | select,insert,update,references | 经理     |
| hiredate | date         | NULL            | YES  |     | NULL    |       | select,insert,update,references | 雇佣日期 |
| sal      | decimal(7,2) | NULL            | YES  |     | NULL    |       | select,insert,update,references | 薪水     |
| comm     | decimal(7,2) | NULL            | YES  |     | NULL    |       | select,insert,update,references | 奖金     |
| deptno   | int(2)       | NULL            | YES  |     | NULL    |       | select,insert,update,references | 部门编号 |
+----------+--------------+-----------------+------+-----+---------+-------+---------------------------------+----------+
8 rows in set (0.00 sec)

原创文章，转载请注明出处：http://www.opcoder.cn/article/52/