MySQL数据库——数据表操作

最新推荐文章于 2024-09-17 11:26:40 发布

原创最新推荐文章于 2024-09-17 11:26:40 发布 · 6.2k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文详细介绍了在MySQL中如何进行数据表操作，包括创建数据表、查看数据表、修改表结构（如改变字段名、类型、位置）以及删除数据表。讲解了CREATE TABLE语句的使用，以及DESCRIBE、ALTER TABLE、SHOW COLUMNS等SQL语句在查看和修改表结构中的应用。

在MySQL数据库中，所有的数据都存储在数据表中，若要对数据执行添加、查看、修改、删除等操作，首先需要在指定的数据库中准备一张数据表。下面将详细地讲解如何在MySQL中创建、查看、修改以及删除数据表。

1、创建数据表

创建数据表指的是在已存在的数据库中建立新表。MySQL既可以根据开发需求创建新的表，又可以根据已有的表复制相同的表结构。其中依据已有的表创建相同结构的新表方式会在后面的章节中讲解，此处仅讲解如何根据需求创建一个简单的新表。
在MySQL数据库中，使用CREATETABLE语句可以完成数据表的创建，基本语法格式如下。
语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名
(字段名字段类型[字段属性]…) [表选项]

在上述语法中，可选项TEMPORARY表示临时表，仅在当前会话中可见，并且在会话关闭时自动删除。“字段名”指的是数据表的列名;“字段类型”设置字段中保存的数据类型，如时间日期类型等；可选项“字段属性”指的是字段的某些特殊约束条件。可选的“表选项”用于设置表的相关特性，如存储引擎(ENGINE)、字符集(CHARSET)和校对集(COLLATE)。
其中，字段类型、字段属性以及表选项的设置和相关注意事项会在后面的章节中讲解，此处读者了解即可。
需要注意的是，在操作数据表之前，应该使用“USE数据库名”指定操作是在哪个数据库中进行，否则会抛出No database selected错误。
下面在mydb数据库中，创建一个名称为goods的数据表，保存商品信息，具体SQL语句及执行结果如下。

#①创建mydb数据库
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
#②选择mydb数据库
mysql> USE mydb;
Database changed
#③创建goods数据表
mysql> CREATE TABLE goods (
    ->   id INT COMMENT '编号',
    ->   name VARCHAR(32) COMMENT '商品名',
    ->   price INT COMMENT '价格',
    ->   description VARCHAR(255) COMMENT '商品描述'
    -> );
Query OK, 0 rows affected (0.01 sec)

上述SQL语句中，INT用于设置字段数据类型是整型;VARCHAR(L)表示可变长度的字符串，L表示字符数，如VARCHAR(32)表示可变的字符数是32;COMMENT用于在创建表时添加注释内容，并将其保存到表结构中。
值得一提的是，在操作数据表时，可以不使用“USE选择数据库”的方式选择数据库，直接将表名的位置改为“数据库.表名”的形式，就可以在任何数据库下访问其他数据库中的表。例如，省略以上第②步的操作，将第③步创建goods数据表的语句修改成如下形式。
CREATE TABLE mydb.goods (此处省略字段的定义);
上述语句中，mydb.goods表示mydb数据库中的goods数据表。

2、查看数据表

MySQL中提供了专门的SQL语句，用于查看某数据库中存在的所有数据表、指定模式的数据表或数据表的相关信息。下面分别对其进行详细讲解。
1．查看数据表
选择数据库后，可以通过MySQL提供的SQL语句进行查看，基本语法格式如下。
语法

SHOW TABLES [LIKE匹配模式];

上述语法中，若不添加可选项“LIKE匹配模式”，表示查看当前数据库中的所有数据表；若添加则按照“匹配模式”查看数据表。其中，匹配模式符有两种，分别为“%”和“_”。前者表示匹配一个或多个字符，代表任意长度的字符串，长度也可以为0，后者仅可以匹配一个字符。
为了读者更好地理解，下面以mydb数据库中数据表的查询为例进行演示。首先为mydb数据库再添加一张数据表new_goods，方便读者对以下示例的理解。new_goods表的创建语句如下。

mysql> CREATE TABLE new_goods (
    ->   id INT COMMENT '编号',
    ->   name VARCHAR(32) COMMENT '商品名',
    ->   price INT COMMENT '价格',
    ->   description VARCHAR(255) COMMENT '商品描述'
    -> );
Query OK, 0 rows affected (0.01 sec)

数据表准备完成后，接下来分别查看mydb数据表中的所有数据表和名称中含有new的数据表。具体SQL语句如下。

① 查看所有数据表

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| goods          |
| new_goods      |
+----------------+
1 rows in set (0.00 sec)

② 查看名称中含有new的数据表

mysql> SHOW TABLES LIKE '%new%';
+------------------------+
| Tables_in_mydb (%new%) |
+------------------------+
| new_goods              |
+------------------------+
1 row in set (0.00 sec)

从以上输出结果可以看出，mydb数据库中一共有两个数据表，而名字中含有new的数据表仅有一个。需要注意的是，LIKE后的匹配模式必须使用单引号或双引号包裹。

2．查看数据表的相关信息
除了查看数据库下有哪些数据表外，还可以利用MySQL提供的SQL语句查看数据表的相关信息，如数据表的名称、存储引擎、创建时间等，基本语法格式如下。
语法

SHOW TABLE STATUS [FROM 数据库名] [LIKE 匹配模式];

下面查看mydb数据库下含有new的数据表的详细信息，具体SQL语句如下。

mysql>SHOW TABLE STATUS FROM mydb LIKE '%new%'\G

上述SQL语句中，“\G”是MySQL客户端可以使用的结束符中的一种，用于将显示结果纵向排列，适合字段非常多的情况。输出结果中含有值的字段含义如表2-1所示，其他字段的含义读者可在MySQL手册中查看，此处不再赘述。

表2-1 数据表的相关信息

字段名称	描述
Name	数据表的名称
Engine	数据表的存储引擎
Version	数据表的结构文件（如lib_user_temp.frm）版本号
Row_format	记录的存储格式，Dynamic表示动态
Data_length	数据文件的长度（MyISAM存储引擎）或为集群索引分配的内存（InnoDB存储引擎），均以字节为单位
Create_time	数据表的创建时间
Collation	数据表的校对集

在表2-1中，Row_format 字段的值除Dynamic外，还有Fixed(固定)、Compressed(压缩)、Redundant(冗余)和Compact(紧凑)。

3、修改数据表

在实际开发时，若创建的数据表不符合当前项目的开发要求时，可以通过修改数据表来实现。如修改数据表的名称和表选项。下面将分别讲解如何修改数据表。
1．修改数据表名称
在MySQL中，提供了两种修改数据表名称的方式，基本语法格式如下。
语法
#语法格式1

ALTER TABLE旧表名RENAME [TOIAS] 新表名:

#语法格式2

`RENAME TABLE旧表名1 TO新表名1[,旧表名2TO新表名2] .`.

在上述语法中，ALTERTABLE修改数据表名称时，可以直接使用RENAME或在其后添加TO或AS。而RENAMETABLE则必须使用TO，另外此语法可以同时修改多个数据表的名称。
下面使用RENAMETABLE将new_goods表的名称修改为my_goods，具体SQL语句与执行结果如下。

mysql> RENAME TABLE new_goods TO my_goods;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| goods          |
| my_goods       |
+----------------+
2 rows in set (0.00 sec)

执行，上述SQL语句后，使用SHOW TABLES;可查看修改后的数据表。
2．修改表选项
数据表中的表选项字符集、存储引擎以及校对集也可以通过ALTER TABLE修改，基本语法格式如下。

ALTER TABLE表名表选项[=]值;

下面以修改my_goods数据表的字符集为例进行演示，具体SQL语句如下。

① 将my_goods数据表的字符集改为utf8

mysql> ALTER TABLE my_goods CHARSET = utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

② 查看修改结果

mysql> SHOW CREATE TABLE my_goods \G
*************************** 1. row ***************************
        Table: my_goods
Create Table: CREATE TABLE `my_goods` (
  `id` int(11) DEFAULT NULL COMMENT '编号',
  `name` varchar(32) CHARACTER SET latin1 DEFAULT NULL COMMENT '商品名',
  `price` int(11) DEFAULT NULL COMMENT '价格',
  `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL COMMENT '商品描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在上述SQL语句中，第②步使用SHOW CREATE TABLE mygoods\G查看表的字符集，该语句会在下一节中详细讲解，此处读者会使用即可。

4、查看表结构

1．查看数据表的字段信息
MySQL提供的DESCRIBE语句可以查看数据表中所有字段或指定字段的信息，包括字段名、字段类型等。其中，DESCRIBE语句可以简写成DESC。基本语法格式如下。
#语法格式1:查看所有字段的信息

{ DESCRIBE | DESC }数据表名;

#语法格式2:查看指定字段的信息

{ DESCRIBE | DESC }数据表名 字段名;

下面以查看数据表my_goods中的所有字段和指定字段name为例演示。具体SQL语句及执行结果如下。

① 所有字段

mysql> DESC my_goods;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | YES  |     | NULL    |       |
| name        | varchar(32)  | YES  |     | NULL    |       |
| price       | int(11)      | YES  |     | NULL    |       |
| description | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

② name字段

mysql> DESC my_goods name;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

在上述执行结果中，Field表示字段名称， Type表示字段的数据类型，Null表示该字段是否可以为空，Key表示该字段是否已设置了索引，Default表示该字段是否有默认值，Extra表示获取到的与该字段相关的附加信息。
2．查看数据表的创建语句
若想要查看创建数据表的具体SQL语句以及表的字符编码，可以使用以下的SQL语句，基本语法格式如下。

SHOW CREATE TABLE表名;

接下来查看my_goods数据表的创建语句，具体SQL语句及执行结果如下。

mysql> SHOW CREATE TABLE my_goods \G
*************************** 1. row ***************************
        Table: my_goods
Create Table: CREATE TABLE `my_goods` (
  `id` int(11) DEFAULT NULL COMMENT '编号',
  `name` varchar(32) CHARACTER SET latin1 DEFAULT NULL COMMENT '商品名',
  `price` int(11) DEFAULT NULL COMMENT '价格',
  `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL COMMENT '商品描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在上述执行结果中，Table表示查询的表名称，CreateTable表示创建该数据表的SQL语句。在SQL语句中，包含了字段信息、COMMENT(注释)、ENGINE(存储引擎)以及DEFAULT CHARSET(字符集)等内容。
3．查看数据表结构
MySQL数据库中的SHOWCOLUMNS语句也可以查看表结构，基本语法格式如下。

语法格式1

SHOW [FULL] COLUMNS  FROM 数据表名 [FROM 数据库名];

语法格式2

SHOW [FULL] COLUMNS  FROM 数据库名.数据表名;

在上述语法格式中，可选项FULL表示显示详细内容，在不添加的情况下查询结果与DESC的结果相同；在添加FULL选项时此语句不仅可以查看到DESC语句查看的信息，还可以查看到字段的权限.COMMENT字段的注释信息等。
另外，在SQL语句中可以通过“FROM数据库名”或“数据库名.数据表名”的方式查看任意数据库下的数据表结构信息。
下面查看my_goods数据表结构的详细信息，具体SQL语句如下。

mysql> SHOW FULL COLUMNS FROM my_goods;
+-------------+--------------+-------------------+------+-----+
| Field       | Type         | Collation         | Null | Key |
+-------------+--------------+-------------------+------+-----+
| id          | int(11)      | NULL              | YES  |     |
| name        | varchar(32)  | latin1_swedish_ci | YES  |     |
| price       | int(11)      | NULL              | YES  |     |
| description | varchar(255) | latin1_swedish_ci | YES  |     |
+-------------+--------------+-------------------+------+-----+
+---------+-------+---------------------------------+----------+
| Default | Extra | Privileges                      | Comment  |
+---------+-------+---------------------------------+----------+
| NULL    |       | select,insert,update,references | 编号      |
| NULL    |       | select,insert,update,references | 商品名    |
| NULL    |       | select,insert,update,references | 价格      |
| NULL    |       | select,insert,update,references | 商品描述   |
+---------+------+----------------------------------+----------+
4 rows in set (0.00 sec)

从上述执行结果可以看出，SHOWFULLCOLUMNS语句除与DESC语句查询出的相同字段外，还包括Collation(校对集)字段、Privileges(权限)字段和Comment(注释)字段。

5、修改表结构

在创建完数据表后，除了可以修改数据表的名称及表选项外，还可以利用MySQL提供的ALTER TABLE语法对字段名称、类型、位置等进行修改、增加或删除。下面分别讲解几种常用的使用方式。
1．修改字段名
在MySQL中仅修改数据表中的字段名称，使用CHANGE实现，基本语法格式如下。

ALTER TABLE数据表名CHANGE [COLUMN] 旧字段名新字段名字段类型[字段属性];

在上述语法中，“旧字段名”指的是字段修改前的名称，“新字段名”指的是字段修改后的名称。“数据类型"表示新字段名的数据类型，不能为空，即使与旧字段的数据类型相同，也必须重新设置。
下面将my_goods 数据表中名为description 的字段修改为des，具体SQL语句如下。

mysql> ALTER TABLE my_goods CHANGE description des VARCHAR(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行上述SQL语句后，查看字段名的修改情况，具体结果如下。

mysql> DESC my_goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| des   | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2．修改字段类型
在MySQL中仅修改数据表中的字段类型，通常使用MODIFY实现，基本语法格式如下。

ALTER TABLE 数据表名 MODIFY [COLUMN]字段名新类型[字段属性];

下面修改my_ goods 数据表中des字段的数据类型，将VARCHAR (255)修改为CHAR(255)，具体SQL语句如下。

mysql> ALTER TABLE my_goods MODIFY des CHAR(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行上述SQL语句后，查看字段类型的修改情况，具体结果如下。

mysql> DESC my_goods des;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| des   | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

3．修改字段的位置
数据表在创建时，字段编写的先后顺序就是其在数据库中存储的顺序，若需要调整某个字段的位置，也可以使用MODIFY实现，基本语法格式如下。

ALTER TABLE数据表名
MODIFY [COLUMN]字段名1数据类型 [字段属性] [FIRST | AFTER 字段名2];

从上述语法可知，修改字段的位置就是在修改字段类型的后面添加“FIRST”或“AFTER 字段名2”。前者表示将“字段名1”调整为数据表的第1个字段，后者表示将“字段名1”插入到“字段名2”的后面。
下面将my_goods 表中最后一个字段des移动到name字段后，具体SQL语句如下。

mysql> ALTER TABLE my_goods MODIFY des VARCHAR(255) AFTER name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行上述SQL语句后，查看字段位置的修改结果，具体结果如下。

mysql> DESC my_goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| des   | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4．新增字段
对于已经创建好的数据表，也可以根据业务需求利用ADD新增字段，基本语法格式如下。
语法

语法格式1：新增一个字段，并可指定其位置

ALTER TABLE 数据表名
ADD [COLUMN] 新字段名 字段类型 [FIRST | AFTER 字段名];

语法格式2：同时新增多个字段

ALTER TABLE 数据表名
ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2, ...);

在上述语法中，在不指定位置的情况下，新增的字段默认添加到表的最后。另外，同时新增多个字段时不能指定字段的位置。
下面在my_ goods 数据表中字段name后新增一个num字段，表示商品的数量，具体SQL语句如下。

mysql> ALTER TABLE my_goods ADD num INT AFTER name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行上述SQL语句后，查看新增的字段，具体结果如下。

mysql> DESC my_goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| num   | int(11)      | YES  |     | NULL    |       |
| des   | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5．删除字段
删除字段指的是将某个字段从数据表中删除，MySQL中可以通过DROP完成。基本语法格式如下。

ALTER TABLE数据表名DROP [COLUMN] 字段名:

下面以删除my_ goods数据表中num字段为例演示，具体SQL语句如下。

mysql> ALTER TABLE my_goods DROP num;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行上述SQL语句后，查看删除num字段后数据表中的字段，具体结果如下。

mysql> DESC my_goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| des   | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

6、删除数据表

删除数据表操作指的是删除指定数据库中已经存在的表。另外，在删除数据表的同时，存储在数据表中的数据都将被删除，基本语法格式如下。.

DROP [TEMPORARY] TABLE [IF EXISTS]数据表1 [,数据表2] .;

从上述语法可知，删除数据表时，可同时删除多个数据表，多个数据表之间使用逗号分隔。可选项IFEXISTS用于在删除一个不存在的数据表时，防止产生错误。
下面以删除数据表my__goods为例进行演示，具体SQL语句及执行结果如下。

mysql> DROP TABLE IF EXISTS my_goods;
Query OK, 0 rows affected (0.01 sec)

值得一提的是，在开发时应谨慎使用数据表删除操作，因为数据表一旦删除，表中的所有数据都将被清除。

超全面的测试IT技术课程，0元立即加入学习！有需要的朋友戳：

腾讯课堂测试技术学习地址

作者：kellyred

出处：https://blog.youkuaiyun.com/kellyred

欢迎转载，但未经作者同意请保留此段声明，并在文章页面明显位置给出原文链接。