MySQL库与表的操作

目录

一、登录并进入数据库

1、登录 

2、USE 命令

检查当前数据库

二、库的操作 

1、创建数据库语法

2、举例演示

3、退出

三、字符集和校对规则

1、字符集（Character Set）

2、校对集（Collation）

总结

3、操作命令

查看系统默认字符集以及校验规则

查看数据库支持的字符集

查看数据库支持的字符集校验规则

校验规则对数据库的影响

查询示例

四、操纵数据库

1、查看当前数据库有哪些表

2、查看当前在哪个数据库

 3、查看数据库

4、修改数据库

实例：修改数据库字符集

五、数据库备份、恢复与连接情况

1、备份

2、还原

3、查看连接情况

六、表的操作

1、创建表

2、插入数据

不指定列名

插入多行数据

使用子查询插入数据

注意事项

3、存储引擎与文件

4、查看表结构

DESC 命令

SELECT * FROM 命令

SHOW CREATE TABLE  

注意事项

5、修改表

添加字段

修改字段

删除字段

更改表的字符集和存储引擎

重命名表

批量重命名

跨数据库重命名

6、删除表

---

一、登录并进入数据库

1、登录 

mysql 是一个命令行客户端工具，用于与 MySQL 数据库管理系统进行交互。当你需要通过命令行登录到 MySQL 数据库时，可以使用以下格式的命令：

mysql -h [hostname] -P [port] -u [username] -p[password]

这里的参数解释如下：

• -h [hostname]：指定 MySQL 数据库服务器的主机名或 IP 地址。如果省略此参数，默认值是本地主机 127.0.0.1 或 localhost。
• -P [port]：指定 MySQL 数据库服务器监听的端口号。默认端口号通常是 3306。注意 -P 是大写的 P。
• -u [username]：指定用于登录数据库的用户名。
• -p[password]：指定用户的密码。如果直接跟在 -p 后面，则不包含空格，整个字符串会被视为密码；如果 -p 和密码之间有空格，则会提示输入密码。

因此，命令 mysql -h 127.0.0.1 -P 3306 -u root -p 将尝试连接到运行在本地主机上的 MySQL 服务器，使用 root 用户名，并且会提示你输入密码。

示例

假设你想要登录到本地 MySQL 数据库，使用 root 用户，并且你的密码是 mysecretpassword，则可以使用下面的命令：

mysql -h 127.0.0.1 -P 3306 -u root -pmysecretpassword

或者，如果你不想直接在命令行上显示密码，可以这样：

mysql -h 127.0.0.1 -P 3306 -u root -p

然后当执行上述命令后，系统会提示你输入密码：

Enter password:

2、USE 命令

在 MySQL 中，一旦你使用命令行客户端或其他数据库管理工具登录到了 MySQL 服务器，你就可以选择一个具体的数据库来开始执行 SQL 查询。要选择或“进入”一个数据库，你需要使用 USE 命令。

USE 命令的基本语法如下：

USE database_name;

其中 database_name 是你要选择的数据库的名称。执行完这个命令后，你所有的后续 SQL 查询都将在所选的数据库中执行。

示例

假设你有一个名为 mydatabase 的数据库，你可以这样选择它：

USE mydatabase;

如果你成功选择了数据库，MySQL 将不会返回任何输出，只会显示一条消息说 Database changed，表示你当前正在使用的数据库已经被更改。

检查当前数据库

如果你想确认你当前正在使用的数据库是哪一个，可以使用以下命令：

SELECT DATABASE();

这将返回当前数据库的名称，如果没有选择任何数据库，则返回 NULL。

注意事项

• 在选择数据库之前，确保你拥有足够的权限访问该数据库。如果你没有权限，MySQL 将会返回一个错误。
• 如果你尝试选择一个不存在的数据库，MySQL 也会返回一个错误。在使用 USE 命令之前，你可以先用 SHOW DATABASES; 来列出所有可用的数据库，以确保你拼写正确。

二、库的操作 

在 MySQL 中，创建数据库是一个关键的步骤，它为存储数据提供了基础容器。下面是关于创建数据库的详细解析：

1、创建数据库语法

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...];

• [IF NOT EXISTS]：这是一个可选部分，用于确保如果数据库已经存在，不会报错。如果没有这个选项，尝试创建已存在的数据库会导致错误。
• db_name：你打算创建的数据库的名称。
• create_specification：一系列可选的规范，用于设定数据库的特定属性，包括字符集和校验规则。

create_specification 参数：

• [DEFAULT] CHARACTER SET charset_name：指定数据库使用的字符集。例如，CHARACTER SET utf8 将数据库的字符集设为 UTF-8。
• [DEFAULT] COLLATE collation_name：指定数据库字符集的校验规则。例如，COLLATE utf8_general_ci 设定了一个不区分大小写的校验规则。
• 创建数据库时，你可以选择是否指定字符集和校验规则。如果不指定，MySQL 将使用其默认设置。指定这些参数可以确保你的数据库能够正确处理和存储各种语言的文本数据，并按照预期的方式进行文本比较和排序。这对于国际化的应用尤其重要，可以避免由于字符集或校验规则不匹配导致的问题。

2、举例演示

创建一个默认字符集和校验规则的数据库

CREATE DATABASE db1;

• 当没有明确指定字符集和校验规则时，MySQL 使用其默认设置，通常是 UTF-8 (utf8) 和 utf8_general_ci。

创建一个使用UTF8字符集的数据库

CREATE DATABASE db2 CHARACTER SET utf8;

• 这条语句显式指定了数据库 db2 使用 UTF-8 字符集，但未指定校验规则，因此会使用默认的 utf8_general_ci。

创建一个使用UTF8字符集并带校对规则的数据库

CREATE DATABASE db3 CHARACTER SET utf8 COLLATE utf8_general_ci;

• 这里不仅指定了 db3 使用 UTF-8 字符集，还明确了校验规则为 utf8_general_ci，这意味着该数据库将不区分大小写地比较字符串。

3、退出

退出 MySQL 命令行客户端有几种方法，你可以根据自己的偏好选择：

1. 使用 EXIT 命令：

   输入 EXIT 并按 Enter 键，可以直接退出 MySQL 命令行环境。

   mysql> EXIT
   Bye

2. 使用 QUIT 命令：

   输入 QUIT 并按 Enter 键，同样可以退出 MySQL 命令行环境。

   mysql> QUIT
   Bye

3. 使用 Ctrl + C：

   连续按两次 Ctrl + C 组合键，可以中断当前正在执行的命令，并退出 MySQL 命令行环境。但通常这种方法用于中断正在执行的查询，而不作为常规的退出方式。

4. 使用 Ctrl + D：

   在 Unix/Linux 系统中，按一次 Ctrl + D 组合键可以退出 MySQL 命令行环境。

5. 使用退出命令的缩写：

   你也可以使用 EXIT 或 QUIT 命令的缩写 \q 或 \x 来退出。

   mysql> \q
   Bye

   mysql> \x
   Bye

无论你选择哪种方式，MySQL 命令行都会给出 "Bye" 的提示，表明你已经成功退出了命令行环境。在退出之前，任何未提交的事务将会被自动回滚，所以不用担心数据完整性问题。

三、字符集和校对规则

在创建数据库时，确实会涉及到两个与字符编码相关的概念：字符集（Character Set）和校对集（Collation）。这两个概念对确保数据正确存储和处理至关重要，尤其是在处理多语言或多字节字符时。 

1、字符集（Character Set）

字符集定义了一组字符及其对应的二进制编码，用于表示文本数据。不同的字符集可以支持不同的字符范围和语言，例如 ASCII、UTF-8、GBK、BIG5 等。在数据库中，字符集决定了如何在内部存储和解释文本数据。

当创建数据库或表时，可以选择一个字符集作为存储数据的基础编码。例如，UTF-8 是一种常见的多字节编码，能够支持全球大多数语言的字符，因此在国际化应用中非常受欢迎。

2、校对集（Collation）

校对集是字符集的一个子集，它规定了字符集中的字符如何进行比较和排序。校对集不仅决定了字符的排序规则，还可能影响字符的大小写敏感性、重音符号处理、以及特殊字符的识别等。例如，对于相同的 UTF-8 字符集，可以有 utf8_general_ci、utf8_unicode_ci、utf8_bin 等不同的校对集，它们在字符比较和排序时的行为各不相同。

• utf8_general_ci：大小写不敏感，对重音符号不敏感。
• utf8_unicode_ci：大小写不敏感，但对重音符号敏感。
• utf8_bin：完全按照字节进行比较，大小写敏感，适用于二进制数据比较。

选择正确的字符集和校对集非常重要，原因如下：

• 数据完整性：确保所有字符都能正确存储和显示，避免因字符集不匹配导致的数据损坏或乱码。
• 数据一致性：在比较和排序数据时，使用合适的校对集可以确保结果符合预期，尤其是在处理多语言数据时。
• 性能：某些字符集和校对集可能对性能有影响，例如，多字节字符集在存储和处理时可能比单字节字符集慢。

总结

在创建数据库时，选择字符集和校对集是一项基础但重要的配置。字符集决定了数据如何存储，而校对集则决定了数据如何比较和排序。为了确保数据的正确存储和处理，应根据数据库的实际需求和数据特征，合理选择字符集和校对集。在大多数情况下，使用通用且支持广泛字符的 UTF-8 字符集，配合适合的校对集，可以满足大部分应用场景的需求。

3、操作命令

字符集和校验规则（Collation）在数据库设计和数据处理中扮演着至关重要的角色。它们影响着数据的存储方式和比较操作。

查看系统默认字符集以及校验规则

• show variables like 'character_set_database'; 这个命令用于显示当前数据库使用的字符集。character_set_database 变量指定了数据库层面的字符集，默认通常是 utf8 或 utf8mb4。

• show variables like 'collation_database'; 这个命令用于显示当前数据库使用的校验规则。collation_database 变量决定了如何比较和排序字符，例如是否区分大小写。

查看数据库支持的字符集

• show charset; 此命令列出MySQL服务器支持的所有字符集。

查看数据库支持的字符集校验规则

• show collation; 列出所有可用的校验规则，每个校验规则对应一个特定的字符集，定义了字符的排序规则和比较方式。

校验规则对数据库的影响

不区分大小写

当你创建数据库时，如果使用了 utf8_general_ci 这样的校验规则，那么在该数据库中的数据比较和排序将不区分大小写。ci 是 Case Insensitive 的缩写，意味着“不区分大小写”。

示例：

create database test1 collate utf8_general_ci;

在这个数据库中，插入 'a' 和 'A' 将被视为相同的数据项，因为校验规则设定为不区分大小写。

区分大小写

另一方面，使用 utf8_bin 这样的校验规则将使数据库在比较和排序时区分大小写。bin 意味着二进制比较，即完全按照字节比较，区分大小写。

示例：

create database test2 collate utf8_bin;

在这个数据库中，'a' 和 'A' 被视为不同的数据项，因为在 utf8_bin 校验规则下，数据库严格区分大小写。

分别use test1 use test2
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');

查询示例

在 test1 数据库中，由于使用了 utf8_general_ci，查询 'a' 会返回所有包含 'a' 或 'A' 的记录，因为大小写被忽略。不区分大小写的查询以及结果：

mysql> use test1;
mysql> select * from person where name='a';
+------+
| name |
+------+
| a |
| A |
+------+
2 rows in set (0.01 sec)

在 test2 数据库中，由于使用了 utf8_bin，查询 'a' 只会返回实际包含小写 'a' 的记录，而不会匹配大写 'A'。区分大小写的查询以及结果：

mysql> use test2;
mysql> select * from person where name='a';
+------+
| name |
+------+
| a |
+------+
2 rows in set (0.01 sec)

选择正确的字符集和校验规则对于确保数据的一致性和正确性至关重要，特别是在多语言环境中。

 不区分大小写排序以及结果：

mysql> use test1;
mysql> select * from person order by name;
+------+
| name |
+------+
| a |
| A |
| b |
| B |
+------+

区分大小写排序以及结果：

mysql> use test2;
mysql> select * from person order by name;
+------+
| name |
+------+
| A |
| B |
| a |
| b |
+------+

四、操纵数据库

1、查看当前数据库有哪些表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

2、查看当前在哪个数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)mysql>

 3、查看数据库

SHOW DATABASES;

这条命令用于列出当前 MySQL 服务器上所有可用的数据库。当你首次登录到 MySQL 服务器时，如果没有明确选择数据库，你将处于无数据库选择状态，此时可以使用此命令查看服务器上存在的所有数据库。

SHOW CREATE DATABASE <database_name>;

这条命令用于显示创建指定数据库时所使用的 SQL 语句。这对于理解数据库的初始设置非常有用，特别是字符集和校对规则。例如：

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

这里，mytest 是要检查的数据库名。输出显示了创建数据库时使用的 SQL 语句，包括默认的字符集设置（在这里是 utf8）。

说明：

• 尽管 MySQL 不强制要求关键字必须使用大写，但在 SQL 语句中使用大写关键字是一种普遍接受的规范，有助于提高代码的可读性和一致性。

• 此外，当数据库名称或表名称与 MySQL 的保留关键字相同时，需要使用反引号（`）将它们包围起来，以避免语法冲突。

• /*!40100 ... */ 这种形式的注释并不是真正的注释，而是 MySQL 的一种特性，表示后面跟随的语句只在 MySQL 4.0.1 及更高版本中有效。这是 MySQL 对向后兼容性的一种处理方式，确保在不同版本间语句的正确执行。

4、修改数据库

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

这条命令用于修改现有数据库的特性，主要应用于改变数据库的字符集或校对规则。alter_specification 参数可以是：

• [DEFAULT] CHARACTER SET charset_name
• [DEFAULT] COLLATE collation_name

其中，charset_name 和 collation_name 分别是你希望数据库使用的新字符集和校对规则。

实例：修改数据库字符集

假设你想要将 mytest 数据库的字符集从 utf8 更改为 gbk，可以使用以下命令：

mysql> alter database mytest charset=gbk;
Query OK, 1 row affected (0.00 sec)

之后，再次使用 SHOW CREATE DATABASE mytest; 命令，你会看到数据库的字符集已经成功更改为 gbk：

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+

五、数据库备份、恢复与连接情况

数据库备份和恢复是数据库管理中的重要环节，确保数据的安全性和业务的连续性。在 MySQL 中，使用 mysqldump 工具进行备份是一种常见的做法。下面我们将详细解释备份、还原的过程以及一些注意事项。

1、备份

命令语法：

# mysqldump -P<port> -u<username> -p<password> -B <database_name> > <backup_file_path>

• -P 指定 MySQL 服务器的端口号，默认通常是 3306。
• -u 指定 MySQL 用户名。
• -p 后跟密码，或者直接 -p 后输入密码，如 -p123456。
• -B 表示只备份指定的数据库。
• <database_name> 是你要备份的数据库名称。
• > 符号将输出重定向到指定的文件，即 <backup_file_path>，这是你备份文件的路径。

示例：

# mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql

这个命令将 mytest 数据库的内容备份到 D:/mytest.sql 文件中。

2、还原

在需要恢复数据时，可以使用 source 命令将备份文件中的 SQL 语句重新执行。

命令语法：

mysql> source <backup_file_path>;

示例：

mysql> source D:/mysql-5.7.22/mytest.sql;

在执行此命令前，确保你已经连接到了正确的 MySQL 数据库实例，并且拥有执行 SQL 语句的权限。

注意事项

• 备份单个表：如果只想备份单个表，可以使用 -t 参数并指定表名，例如：

  # mysqldump -P3306 -u root -p123456 -B mytest -t mytable > D:/mytable.sql

• 备份多个数据库：可以通过指定多个数据库名称来备份多个数据库，例如：

  # mysqldump -u root -p -B db1 db2 db3 > multiple_databases.sql

• 未使用 -B 参数：如果不使用 -B 参数进行备份，那么备份文件将包含所有数据库的信息。在这种情况下，恢复时需要先创建相应的空数据库，然后再使用 source 命令来导入数据。

3、查看连接情况

show processlist 命令可以显示当前所有连接到 MySQL 服务器的会话，包括每个会话的 ID、用户、主机、当前操作的数据库、正在执行的命令、持续时间等信息。

命令语法：

mysql> show processlist;

输出示例：

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 2 | root | localhost | test | Sleep | 1386 | | NULL |
| 3 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

六、表的操作

在 MySQL 中，创建表是一个基本但重要的操作，它允许你定义数据库中的数据结构。下面是对创建表语法和相关概念的详细解释：

1、创建表

命令语法：

CREATE TABLE table_name (field1 datatype options,field2 datatype options,field3 datatype options,...
) CHARACTER SET charset_nameCOLLATE collation_nameENGINE = storage_engine;

• table_name：你想要创建的表的名称。
• field：表示列名，也就是表中的字段。
• datatype：指定字段的数据类型，例如 INT, VARCHAR(20), DATE 等。
• options：可以附加在数据类型后面，用于定义字段的额外属性，如 NOT NULL, AUTO_INCREMENT, DEFAULT 'value' 等。
• CHARACTER SET charset_name：定义表的字符集。如果省略，将使用数据库的默认字符集。
• COLLATE collation_name：定义表的校对规则。如果省略，将使用数据库的默认校对规则。
• ENGINE = storage_engine：指定表的存储引擎，如 MyISAM, InnoDB 等。

举例

CREATE TABLE users (id INT,name VARCHAR(20) COMMENT '用户名',password CHAR(32) COMMENT '密码是32位的MD5值',birthday DATE COMMENT '生日'
) CHARACTER SET utf8ENGINE = MyISAM;

说明：

• users 表有四个字段：id, name, password, birthday。
• COMMENT 关键字用于添加字段注释，这有助于理解字段的用途。
• CHARACTER SET utf8 指定了表的字符集为 UTF-8，能够支持全球大部分语言。
• ENGINE = MyISAM 指定了表的存储引擎为 MyISAM。

2、插入数据

在数据库中向表中插入数据是数据库管理中最常见的操作之一。不同的数据库管理系统（DBMS）如 MySQL、PostgreSQL、SQL Server 等，提供了相似但略有不同的语法来执行这一操作。这里，我将主要讲解在 MySQL 中如何插入数据到表中。

插入数据的基本语法

在 MySQL 中，使用 INSERT 语句来向表中插入数据。基本语法如下：

INSERT INTO table_name [(column1, column2, column3,...)]
VALUES (value1, value2, value3,...);

这里的 table_name 是你想要插入数据的表的名称，括号内的 column1, column2, column3,... 是表中的列名，而 value1, value2, value3,... 是你想要插入的相应列的值。

示例

假设你有一个名为 users 的表，该表有以下列：id, username, email, 和 registration_date。现在，你想要插入一条新的用户记录。

INSERT INTO users (id, username, email, registration_date)
VALUES (1, 'john_doe', '[e-mail]', '2023-01-01');

不指定列名

如果插入的值与表中的列一一对应，你可以省略列名列表，如下所示：

INSERT INTO users VALUES (1, 'john_doe', '[e-mail]', '2023-01-01');

但是，这种方法要求你对表结构有充分的了解，以确保值的顺序与列的顺序相匹配。

插入多行数据

你也可以一次性插入多行数据，这可以通过在 VALUES 关键字后提供多组值来实现：

INSERT INTO users (id, username, email, registration_date)
VALUES (1, 'john_doe', '[e-mail]', '2023-01-01'),(2, 'jane_doe', '[e-mail]', '2023-01-02');

使用子查询插入数据

你还可以从一个查询的结果中插入数据到另一个表中：

INSERT INTO users (id, username, email)
SELECT id, username, email FROM temp_users;

这会将 temp_users 表中所有记录的 id, username, 和 email 字段值插入到 users 表中。

注意事项

• 当插入数据时，需要确保数据类型与列的定义相符，例如，日期字段应插入日期值，字符串字段应插入字符串值。
• 如果表中有主键或唯一索引，插入的数据不能违反这些约束。
• 对于具有 AUTO_INCREMENT 属性的列，你不需要指定值，因为数据库会自动为其生成一个唯一的值。
• 插入数据时，如果表中有外键约束，确保引用的外键值在相应的表中存在。

3、存储引擎与文件

不同的存储引擎会影响表数据的存储方式和文件类型。例如，如果你创建的是 MyISAM 引擎的表，MySQL 会在数据目录下生成三个与表相关的文件：

• users.frm：包含表结构的定义。
• users.MYD：存储表的数据。
• users.MYI：存储表的索引信息。

注意

• 如果你创建的是 InnoDB 引擎的表，数据和索引通常不会以独立的文件形式存储，而是存储在 InnoDB 表空间中，除非启用了 innodb_file_per_table 配置选项，此时每个 InnoDB 表也会有自己的 .ibd 文件。

4、查看表结构

DESC 命令

DESC 命令（实际上是 DESCRIBE 的简写）用于描述一个表的结构，它显示了表中的所有列以及它们的数据类型、是否允许为空、是否有默认值、是否是主键等信息。这对于理解表的结构非常有帮助。

示例：

假设我们有一个名为 employees 的表，包含以下字段：id, first_name, last_name, email, hire_date, job_id, salary。

DESC employees;

执行上述命令后，你可能会得到类似这样的输出：

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| first_name| varchar(20) | NO   |     | NULL    |                |
| last_name| varchar(25) | NO   |     | NULL    |                |
| email    | varchar(25) | NO   | UNI | NULL    |                |
| hire_date| date        | NO   |     | NULL    |                |
| job_id   | varchar(10) | YES  | MUL | NULL    |                |
| salary   | decimal(8,2)| NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

SELECT * FROM 命令

SELECT * FROM 命令用于从一个或多个表中选择所有的列和行。* 表示所有列，FROM 后面跟着表的名称。这是最常见的 SQL 查询之一，用于获取数据。

示例：

如果你想查看 employees 表中的所有记录，你可以使用以下命令：

SELECT * FROM employees;

这将返回表中所有列的所有数据行。例如，你可能得到这样的结果：

+----+------------+-----------+----------------+------------+---------+--------+
| id | first_name | last_name | email          | hire_date  | job_id  | salary |
+----+------------+-----------+----------------+------------+---------+--------+
|  1 | John       | Doe       | john.doe@email | 2010-05-01| IT_PROG | 8000.00|
|  2 | Jane       | Smith     | jane.smith@eml | 2012-06-15| HR_REP  | 7000.00|
|  3 | Michael    | Brown     | mike.brown@eml | 2015-01-10| ACCT    | 6000.00|
+----+------------+-----------+----------------+------------+---------+--------+

SHOW CREATE TABLE  

在 MySQL 中，SHOW CREATE TABLE 是一个非常有用的命令，它用于显示一个或多个表的创建语句。这可以帮助你了解一个表是如何被定义的，包括其字段的数据类型、索引、约束、存储引擎等详细信息。

基本语法

SHOW CREATE TABLE 命令的基本语法如下：

SHOW CREATE TABLE table_name;

其中 table_name 是你想要查看创建语句的表的名字。如果需要查看多个表的创建语句，你可以使用通配符或者列出多个表名：

SHOW CREATE TABLE table_name1, table_name2, ...;

或者对于一个数据库下的所有表，你可以使用：

SHOW CREATE TABLE database_name.*;

但是，通常我们只对单个表使用此命令。

输出格式

当你运行 SHOW CREATE TABLE 命令后，MySQL 将返回两列数据：Table 和 Create Table。

• Table 列将显示表的名称。
• Create Table 列将包含创建该表的完整 SQL 语句。

示例

假设你有一个名为 employees 的表，你可以这样运行命令：

SHOW CREATE TABLE employees;

输出可能如下所示：

+------------+---------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                        |
+------------+---------------------------------------------------------------------------------------------------------------------+
| employees  | CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`position` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+---------------------------------------------------------------------------------------------------------------------+

这里，Create Table 列显示了创建 employees 表的 SQL 语句，包括字段定义、主键和其他表选项。

注意事项

• 为了方便读取输出，你可以在命令末尾加上 \G，这样输出将以垂直格式显示，使输出更易读。
• 创建语句中的表名和列名可能会被单引号括起来，这是正常的，因为 SQL 语句中使用单引号来标识字符串，而在创建语句中，表名和列名被视为字符串处理。
• 如果你打算复制 Create Table 中的语句来重新创建表，请确保正确处理这些引号，以免出现语法错误。

5、修改表

在实际项目开发中，随着业务需求的变化，可能需要频繁地调整数据库表的结构。MySQL 提供了 ALTER TABLE 语句来帮助我们修改现有表的结构，包括添加字段、修改字段、删除字段、更改表的字符集和存储引擎等。下面将详细介绍这些操作及其语法，并给出具体的例子。

添加字段

如果需要向现有的表中添加新的字段，可以使用 ADD COLUMN 子句。

语法：

ALTER TABLE table_name
ADD [COLUMN] column_name data_type [constraint];

示例：

假设有一个 employees 表，现在需要增加一个 phone_number 字段。

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(15);

修改字段

当需要改变字段的数据类型、长度或其它属性时，可以使用 MODIFY COLUMN 或 CHANGE COLUMN 子句。

语法：

ALTER TABLE table_name
MODIFY [COLUMN] column_name new_data_type [new_constraint];

或者

ALTER TABLE table_name
CHANGE [COLUMN] old_column_name new_column_name new_data_type [new_constraint];

示例：

假设需要将 employees 表中的 email 字段从 VARCHAR(50) 改为 VARCHAR(100)。

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100);

或者，如果要将 email 字段改名为 contact_email 并同时修改类型：

ALTER TABLE employees
CHANGE COLUMN email contact_email VARCHAR(100);

删除字段

当不再需要某字段时，可以使用 DROP COLUMN 子句来删除它。

语法：

ALTER TABLE table_name
DROP [COLUMN] column_name;

示例：

假设决定从 employees 表中删除 phone_number 字段。

ALTER TABLE employees
DROP COLUMN phone_number;

更改表的字符集和存储引擎

你还可以使用 ALTER TABLE 来更改表的字符集和存储引擎。

更改字符集：

ALTER TABLE table_name
CONVERT TO CHARACTER SET charset_name
COLLATE collation_name;

更改存储引擎：

ALTER TABLE table_name
ENGINE = storage_engine;

示例：

假设需要将 employees 表的字符集改为 utf8mb4，并使用 InnoDB 存储引擎。

ALTER TABLE employees
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;ALTER TABLE employees
ENGINE = InnoDB;

重命名表

ALTER TABLE 语句不仅可以用来修改表的结构，如添加、修改或删除字段，还可以用来重命名表。重命名表是一项常见的数据库维护任务，特别是在重构数据库模式或迁移数据时。

语法

重命名表的基本语法如下：

RENAME TABLE old_table_name TO new_table_name;

或者，你也可以使用 ALTER TABLE 结合 RENAME TO 来实现相同的效果：

ALTER TABLE old_table_name RENAME TO new_table_name;

示例

假设你有一个名为 customers 的表，你决定将其重命名为 clients。可以使用以下 SQL 语句：

RENAME TABLE customers TO clients;

或者

ALTER TABLE customers RENAME TO clients;

批量重命名

如果你需要同时重命名多个表，可以在 RENAME TABLE 语句中一次指定多个表：

RENAME TABLE old_table1 TO new_table1, old_table2 TO new_table2, ...;

例如：

RENAME TABLE orders TO sales, products TO inventory;

跨数据库重命名

如果需要将表从一个数据库移动到另一个数据库并同时重命名，可以使用完整的数据库表名格式：

RENAME TABLE old_database.old_table TO new_database.new_table;

例如，将 old_database.orders 表重命名为 new_database.sales：

RENAME TABLE old_database.orders TO new_database.sales;

注意事项

• 在重命名表之前，请确保没有任何外键约束引用了旧的表名。如果存在外键依赖，你需要先删除这些约束，然后重命名表，最后重建约束。
• 重命名操作可能会影响依赖于该表的应用程序代码和脚本。确保在重命名之后更新所有相关的引用。
• 重命名操作可能需要一定的执行时间，特别是对于大型表，因此在生产环境中执行此类操作时要谨慎，并考虑执行时间和潜在的性能影响。

6、删除表

删除表：DROP TABLE

在数据库管理中，删除不再需要的表是一种常见的操作。MySQL 提供了 DROP TABLE 语句来实现这一功能。这个命令可以从数据库中永久删除一个或多个表，连同其中的所有数据和结构信息。

语法格式：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

这里的关键组成部分包括：

• DROP: 表示你打算删除一个数据库对象。
• TEMPORARY: 这个关键字是可选的，用于指定删除临时表。如果省略此关键字，DROP TABLE 命令将删除持久表。
• IF EXISTS: 这个子句也是可选的，用于防止在表不存在的情况下抛出错误。如果表不存在，DROP TABLE 命令将无操作并成功执行，不会产生错误信息。
• tbl_name: 表明你要删除的表的名称。你可以一次指定多个表名，用逗号 , 分隔。

示例：

假设你有一个名为 t1 的表，现在不再需要它了，可以使用以下命令来删除它：

DROP TABLE t1;

如果想在表不存在时也不报错，可以使用：

DROP TABLE IF EXISTS t1;

注意事项：

• 删除表是一个不可逆的操作，一旦执行，所有与该表相关联的数据、索引、约束和触发器都将被永久删除。因此，在执行 DROP TABLE 命令之前，一定要确认你不再需要这个表，或者已经备份了必要的数据。
• 如果表中存在外键约束或其他依赖关系，直接删除可能会失败。在这种情况下，你可能需要首先删除或修改这些依赖关系，然后再删除表。
• 如果试图删除的表正在被其他连接使用，操作可能会失败。确保在删除表之前，没有活跃的事务或查询在使用该表。