【MySQL系统学习专栏】- 数据表操作

---

前言

在 MySQL 数据库中，表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。

对于数据表的操作，所涉及到的知识比较多，本篇只记录常用知识点，不常用的会稍作提醒，等用到的时候再单独做补充
由于篇幅原因，表定义选项和其中的索引内容偏多，整体数据表操作分成了三部分，在学习到对应部分时可跳转相应链接

一、创建数据表

sql语句基本语法：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项]; 

CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的，具体有多个方面，包括：

• 表名

• 表定义选项

• 表选项

• 表分区选项

• 临时表

• 表克隆和复制

（一）表名

官方文档：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)   #表定义选项部分
    [table_options]           #表选项部分   	（可选）
    [partition_options]       #表分区选项部分 （可选）

解释说明：

tbl_name：可以将表名指定为db_name.tbl_name，在特定数据库中创建表。不管是否存在默认数据库（当前是否选择了数据库），此方法都有效，但是不能在不存在的数据库上创建。如果使用带引号的标识符，请分别为数据库和表名加上引号。例如'mydb'.'mytbl',而不是'mydb.mytbl'

IF NOT EXISTS:如果表存在还创建，防止发生此类错误。但是，这里并没有验证现有表的结构是否与该语句指示的结构相同。

简单运用实例：

实际上简单的使用掌握最基本的就ok了
 

（二）表定义选项

【MySQL系统学习专栏】- 数据表操作 - 表定义选项
 

（三）表格选项

官方文档：

table_options:
    table_option [[,] table_option] ...
    
table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | START TRANSACTION 
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | tablespace_option
  | UNION [=] (tbl_name[,tbl_name]...)
}

tablespace_option:
    TABLESPACE tablespace_name [STORAGE DISK]
  | [TABLESPACE tablespace_name] STORAGE MEMORY

少用，涉及到再补充
 

（四）表分区

官方文档：

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

少用，涉及到再补充
 

（五）临时表

官方文档：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)  
    [table_options]           
    [partition_options]      

CREATE TEMPORARY TABLE：可以在创建表时使用关键字TEMPORARY。

解释说明：

1.一个表仅在当前会话中可见，并且在关闭会话时会自动删除。这意味着两个不同的会话可以使用相同的临时表名，

2.临时表可以和普通表同名

3.一个会话中有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。

5.show tables命令不显示临时表。

简单运用实例：

注意第二次show tables访问的是临时表
 

（六）表克隆和复制

官方文档：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression  
 
 query_expression:
    SELECT ...   (Some valid select or union statement)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

解释说明：

1.LIKE克隆：

CREATE TABLE new_tbl LIKE orig_tbl;用于根据另一个表的定义创建一个空表，包括在原始表中定义的任何列属性和索引 (不会拷贝表中的数据)

2.[AS] query_expression复制：

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;该语句会拷贝数据到新表中。 注意：这个语句其实只是把select语句的结果建一个表。所以newadmin这个表不会有主键，索引等。

也可以先CREATE TABLE new_tbl LIKE orig_tbl；再INSERT INTO new_tbl SELECT * FROM orig_tbl;克隆表的结构的同时，将所有的数据拷贝过去，其实就是多了一条数据插入的sql语句，在数据操作部分会进行讲解

对于这种语法，MySQL 实际上是在已有目标表中增加新的字段。所以对于目标表，有原字段和新添字段的概念

3.IGNORE|REPLACE选项：

该选项指示在复制表时如何处理使用SELECT语句复制的重复唯一键值的行。指原字段有唯一键字段，而新添字段也有唯一键字段。可以在SELECT语句中通过IGNORE或REPLACE选项指示如何处理重复唯一键值的行。

使用IGNORE，一个一个字段添加，如果表中已经具有和新行相同唯一键值的行，新行将被丢弃。

使用REPLACE，一个一个添加，新行替换具有相同唯一键值的行。如果既不指定IGNORE也不指定REPLACE，重复的唯一键值会导致错误。

简单运用实例：

1.orig_tbl：

2.like：

可以看到，克隆的class1是没有任何数据的

3.[AS] query_expression：

可以看到，数据已经全部拷贝过来，并且表的结构也是一样的

4.IGNORE|REPLACE：

（1）使用IGNORE进行复制：

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment primary key ,
    username varchar(128) not null,
    password varchar(256) not null
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k', '123456'),('k', '1234567'),
       ('k', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');
create table jack.kaven2(
    id       integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null
) ignore select username , password from jack.kaven;
select * from jack.kaven2;

（2）使用REPLACE进行复制：

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment primary key ,
    username varchar(128) not null,
    password varchar(256) not null
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k', '123456'),('k', '1234567'),
       ('k', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');
create table jack.kaven2(
    id       integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null
) replace select username , password from jack.kaven;
select * from jack.kaven2;

（3）如果既不指定IGNORE也不REPLACE指定，在出现重复的唯一键值会导致错误。

drop database if exists jack;
create database jack;
create table jack.kaven
(
    id       integer auto_increment primary key ,
    username varchar(128) not null,
    password varchar(256) not null
) engine 'innodb' character set 'utf8mb4';

insert into jack.kaven(username, password) values ('k', '123456'),('k', '1234567'),
       ('k', '1234567890'),('qsdd', '1234563210.'),
       ('qskd', '1234563210.');
create table jack.kaven2(
    id       integer auto_increment primary key ,
    username varchar(128) not null unique ,
    password varchar(256) not null
) select username , password from jack.kaven;
select * from jack.kaven2;

 

二、显示数据表

SQL语句基础用法：

SHOW TABLES;

官方文档：

SHOW [EXTENDED] [FULL] TABLES
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

解释说明：

1.SHOW TABLES列出给定数据库中的非临时表，要先选择数据库，显示默认数据库中的数据表。

2.如果有LIKE子句，则表示要匹配哪些表名。还可以用WHERE子句使用更通用的条件来选择行，

3.可选的EXTENDED修饰符会导致SHOW TABLES列出失败的ALTER TABLE语句创建的隐藏表。这些临时表的名称以#sql开头，可以使用DROP TABLE删除它们。

4.该语句还可以列出数据库中的任何视图。可选的FULL修饰符会导致SHOW TABLES显示第二个输出列，值包含BASE TABLE（表示一个表）、VIEW（表示一个视图）、SYSTEM VIEW（表示一个 INFORMATION_SCHEMA 表）。

简单运用实例：

 

三、查看表的结构

创建完数据表之后，经常需要查看表结构（表信息）。在 MySQL 中，可以使用 DESCRIBE 和 SHOW CREATE TABLE 命令来查看数据表的结构。

1.DESCRIBE

DESCRIBE 和 EXPLAIN 语句是同义词

DESCRIBE/DESC 语句会以表格的形式来展示表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法格式如下：

DESCRIBE tab_name;

或简写成：

DESC tab_name;

分别使用 DESCRIBE 和 DESC 查看表 tb_emp1 的表结构，SQL 语句和运行结果如下：

mysql> DESCRIBE tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

其中，各个字段的含义如下：

• Null：表示该列是否可以存储 NULL 值。
• Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

2.SHOW CREATE TABLE

SHOW CREATE TABLE 命令会以 SQL 语句的形式来展示表信息。和 DESCRIBE 相比，SHOW CREATE TABLE 展示的内容更加丰富，它可以查看表的存储引擎和字符编码；另外，你还可以通过\g或者\G参数来控制展示格式。

SHOW CREATE TABLE 的语法格式如下：

SHOW CREATE TABLE tab_name;

在 SHOW CREATE TABLE 语句的结尾处（分号前面）添加\g或者\G参数可以改变展示形式。

使用 SHOW CREATE TABLE 语句查看表 tb_emp1 的详细信息，一次使用\g结尾，一次不使用：

mysql> SHOW CREATE TABLE tb_emp1;
+---------+------------------------------------------------+
| Table   | Create Table                                   |
+---------+------------------------------------------------+
| tb_emp1 | CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE tb_emp1 \g;
+---------+------------------------------------------------+
| Table   | Create Table                                   |
+---------+------------------------------------------------+
| tb_emp1 | CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------+
1 row in set (0.00 sec)

SHOW CREATE TABLE 使用\G结尾的 SQL 语句和运行结果如下：

mysql> SHOW CREATE TABLE tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.03 sec)

 

四、修改数据表

SQL语句基础用法：

ALTER TABLE <表名> [修改选项]

#修改选项：
{ ADD [COLUMN] <列名> <类型>    
| CHANGE [COLUMN] <旧列名> <新列名> <新列类型>   
| ALTER [COLUMN] <列名> { SET DEFAULT <默认值> | DROP DEFAULT }  
| MODIFY [COLUMN] <列名> <类型>    
| DROP [COLUMN] <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

1.ALTER TABLE用于更改原有表的结构。例如，您可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。您还可以更改表的评注和表的类型。

2.ALTER TABLE运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。在执行ALTER TABLE时，其它用户可以阅读原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。

3.允许进行的变更中，许多子句的语法与CREATE TABLE中的子句的语法相近。其中包括table_options修改，官方文档中给出了以下几个方面，这里只对常用语法介绍，其它只做了解。

• 表格选项
• 性能和空间要求
• 并发控制
• 添加和删除列
• 重命名，重新定义和重新排序列
• 主键和索引
• 外键和其他约束
• 更改字符集
• 导入InnoDB表
• MyISAM表的行顺序
• 分区选项

（一）修改表名

SQL语句：

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

简单运用实例：

修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。用户可以使用 DESC 命令查看修改后的表结构，

（二）修改表字符集

SQL语句：

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

（三）修改字段

1.修改字段名称

SQL语句：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

其中：

• 旧字段名：指修改前的字段名；
• 新字段名：指修改后的字段名；
• 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

2.修改字段数据类型

SQL语句：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

其中：

• 修改字段的数据类型就是把字段的数据类型转换成另一种数据类型，可以在第一种方法修改字段名称后面设置新的数据类型。
• 表名：指要修改数据类型的字段所在表的名称；
• 字段名：指需要修改的字段；
• 数据类型：指修改后字段的新数据类型。

3.修改字段约束

在表的定义选项中已讲解

（四）添加字段

1.在末尾添加字段

SQL语句：

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];

对语法格式的说明如下：

• <表名> 为数据表的名字；
• <新字段名> 为所要添加的字段的名字；
• <数据类型> 为所要添加的字段能存储数据的数据类型；
• [约束条件] 是可选的，用来对添加的字段进行约束。

这种语法格式默认在表的最后位置（最后一列的后面）添加新字段。

2.在开头添加字段

SQL语句：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字，语法格式如下：FIRST 关键字一般放在语句的末尾。

3.在中间位置添加字段

SQL语句：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
1

MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字，语法格式如下：AFTER 的作用是将新字段添加到某个已有字段后面。

注意，只能在某个已有字段的后面添加新字段，不能在它的前面添加新字段。

（五）删除字段

SQL语句：

ALTER TABLE <表名> DROP <字段名>；

删除字段是将数据表中的某个字段从表中移除，其中，“字段名”指需要从表中删除的字段的名称。
 

五、删除数据表

SQL语句基础用法：

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

官方文档：

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

解释说明：

• DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。

• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。

• 表被删除时，用户在该表上的权限不会自动删除。

• 目前，RESTRICT和CASCADE不起作用。

1.在 MySQL 数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。

2.在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

3.数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。删除父表有以下两种方法：

• 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
• 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

4.如何取消关联表的外键约束并删除主表，也就是上面所说的删除父表的第二种方法：

（1）在数据库中创建两个关联表。创建表 tb_emp4 的 SQL 语句如下：

CREATE TABLE tb_emp4
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR (50)
);

（2）接下来创建表 tb_emp5，SQL 语句如下：

CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp4_emp5 FOREIGN KEY (deptId) REFERENCES tb_emp4(id)
);

（3）使用 SHOW CREATE TABLE 命令查看表 tb_ emp5 的外键约束，SQL 语句和运行结果如下：

mysql> SHOW CREATE TABLE tb_emp5\G;
*************************** 1. row ***************************
       Table: tb_emp5
Create Table: CREATE TABLE `tb_emp5` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp4_emp5` (`deptId`),
  CONSTRAINT `fk_emp4_emp5` FOREIGN KEY (`deptId`) REFERENCES `tb_emp4` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

由运行结果可以看出，tb_emp5 表为子表，具有名称为 fk_emp4_emp5 的外键约束；tb_emp4 为父表，其主键 id 被子表 tb_ emp5 所关联。

（4）删除被数据表 tb_emp5 关联的数据表 tb_emp4，SQL 语句如下：

mysql> DROP TABLE tb_emp4;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

由运行结果可以看出，当主表在存在外键约束时，不能被直接删除。

（5）下面解除子表 tb_emp5 的外键约束，SQL语句和运行结果如下：

mysql> ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp4_emp5;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

语句成功执行后，会取消表 tb_emp4 和表 tb_emp5 之间的关联关系。

（6）解除关联关系后，可以使用 DROP TABLE 语句直接删除父表 tb_emp4，SQL 语句如下：

DROP TABLE tb_emp4;

（7）最后通过 SHOW TABLES 命令查看数据表列表，如下所示：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_emp5        |
| temp           |
+----------------+
2 rows in set (0.00 sec)

可以发现，数据库列表中已经不存在名称为 tb_emp4 的表，删除成功。

---

参考文章1：Mysql 8 - SQL全新版本之CREATE TABLE（表名、临时表、表克隆和复制、列数据类型和属性）

参考文章1：MySql基础，MySql表的基本操作