【MySQL】MySQL表的操作

1.创建表

SQL创建表的基本语法

CREATE TABLE [IF NOT EXISTS] table_name (  
field1 datatype1 [COMMENT '注释信息'],  
field2 datatype2 [COMMENT '注释信息'],  
field3 datatype3 [COMMENT '注释信息'],  
...  
) [CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name]; 

详细说明

CREATE TABLE: 这是创建表的关键字。

[IF NOT EXISTS]: 这是一个可选项，用于防止在表已经存在的情况下执行创建表的操作时出现错误。如果表已经存在，并且使用了这个选项，SQL语句将不会执行，也不会报错。

**table_name: **这是你要创建的表的名称。表名必须符合数据库的命名规则，通常是以字母开头，可以包含字母、数字、下划线等字符。

field1 datatype1 COMMENT '注释信息':

1. field1: 列名，即表中字段的名称。
2. datatype1: 列的数据类型，如 INT, VARCHAR(255), DATE 等。

CHARSET=charset_name:

1. CHARSET: 指定表的字符集。字符集决定了表中存储的字符数据的编码方式。
2. charset_name: 字符集的名称，如 utf8, utf8mb4, latin1 等。

COLLATE=collation_name:

1. COLLATE: 指定表的校验规则，即字符的比较规则。
2. collation_name: 校验规则的名称，如 utf8_general_ci, utf8mb4_unicode_ci 等。

ENGINE=engine_name:

1. ENGINE: 指定表的存储引擎。存储引擎决定了表如何存储数据、如何支持事务等。
2. engine_name: 存储引擎的名称，如 InnoDB, MyISAM, MEMORY 等。

示例
假设我们要创建一个名为 users 的表，包含 id, username, 和 email 三个字段，并指定字符集为 utf8mb4，校验规则为 utf8mb4_unicode_ci，存储引擎为 InnoDB，并为每个字段添加注释：

CREATE TABLE IF NOT EXISTS users (  
id INT AUTO_INCREMENT COMMENT '用户ID',  
username VARCHAR(255) NOT NULL COMMENT '用户名',  
email VARCHAR(255) NOT NULL COMMENT '电子邮件'  
) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ENGINE=InnoDB; 

这个语句创建了一个 users 表，其中 id 是自动递增的主键，username 和 email 是非空字段，并且每个字段都有相应的注释。表的字符集是 utf8mb4，校验规则是 utf8mb4_unicode_ci，存储引擎是 InnoDB。

使用show enginesSQL可以查看当前MySQL支持的存储引擎。如下：

show engines;

可以看到我这里当前的MySQL默认使用的是InnoDB存储引擎，当我建表时没有指定使用哪种存储引擎，那么就会默认使用InnoDB存储引擎。

2.创建表案例

在创建表之前我们要先有一个数据库，并且使用这个数据库，这里我们创建一个db1的数据库并使用。

create database db1;
show databases;
use db1;

下面我们就来创建一个表：

create table user(
id int,
name varchar(20) comment '用户名',
password char(32) comment '密码',
birthday date comment '生日'
)charset=utf8mb4 engine=MyISAM;

表创建完毕后在数据库的数据存储路径/var/lib/mysql下的db1目录中，就会对应增加三个文件。如下：

而当您使用 MyISAM 存储引擎创建表时，会产生以下文件：

在MySQL数据库中，/var/lib/mysql/db1目录下的文件内容反映了数据库db1中表的数据存储情况。该目录下包含以下文件：

• user_379.sdi
• user.MYD
• user.MYI

这些文件的存在和它们的内容与MySQL的存储引擎有关，特别是与MyISAM存储引擎相关。下面是对这些文件的解释：

1. user_379.sdi：在MySQL 8.0及更高版本中，.sdi（Server Data Dictionary Information）文件用于存储与表结构相关的信息。这个文件是可读的，并且包含了数据库名、表名以及各个字段的详细信息。它是MySQL 8.0引入的新特性，用于替代旧版本中的.frm文件。.frm文件也用于存储表结构定义信息，但在MySQL 8.0中，.sdi文件提供了更为丰富和可读的表结构信息。
2. user.MYD：.MYD（MYData）文件是MyISAM存储引擎专用的数据文件，用于存储MyISAM表的数据。每个MyISAM表都会有一个对应的.MYD文件。在这个例子中，user.MYD文件存储了user表的数据。
3. user.MYI：.MYI（MYIndex）文件也是MyISAM存储引擎专用的，用于存储MyISAM表的索引相关信息。对于MyISAM存储引擎来说，索引是可以被缓存的内容，主要来源于.MYI文件。在这个例子中，user.MYI文件存储了user表的索引信息。

综上所述，这些文件的存在和它们的内容反映了MySQL数据库db1中使用MyISAM存储引擎的user表的数据和索引存储情况。.sdi文件提供了表结构的详细信息，而.MYD和.MYI文件分别存储了表的数据和索引。

3、查看当前数据库下所有的表

show tables;

4、查看表结构

desc 表名;

以下是对这些术语的详细解释：

Field（字段）：这代表表中的列名，即字段的名称。

Type（类型）：这表示字段的数据类型，例如int、varchar、date等。数据类型决定了字段能够存储的数据类型及其范围。

Null（空值）：如果此字段显示YES，则表示该字段可以接受空值（NULL）。如果显示NO，则意味着该字段不允许为空，即必须有一个有效的值。

Key（索引）：这表示字段是否被索引。常见的索引类型包括：PRI：主键索引（Primary Key），表中每一行都必须有一个唯一的值。

1. UNI：唯一索引（Unique），字段中的值必须是唯一的，但允许有空值。
2. MUL：表示该字段在别的表中作为外键被引用，或者该字段是某个复合索引的一部分。
3. 如果没有这些标记，则表示该字段没有索引。

Default（默认值）：这表示字段的默认值。如果在插入新记录时没有为该字段提供值，则会使用此默认值。如果字段没有默认值，则此列通常显示为空。

Extra（额外信息）：这提供了关于字段的额外信息。

• 例如：auto_increment：表示该字段的值会自动增加，通常用于主键字段。
• on update CURRENT_TIMESTAMP：表示每次更新记录时，该字段会自动更新为当前时间戳。
• 如果此列为空，则表示没有额外的字段信息。

5、查看创建表时的相关细节

show create table 表名 \G

6、修改表

这里的修改表包括了修改表的名字，新增列，修改列类型，删除列，修改列名

6.1.修改表名

RENAME TABLE table_name TO 新表名;

比如我们吧abc修改为person

6.2.新增列

ALTER TABLE table_name ADD 新增列名 数据类型 [约束条件];

1. table_name 是你要修改的表的名称。
2. 新增列名 是你想要添加的新列的名称。
3. 数据类型 是新列将要存储的数据类型，如 VARCHAR(255)、INT、DATE 等。
4. [约束条件] 是可选的，用于指定新列的约束，如 NOT NULL、UNIQUE、DEFAULT 值等。

修改表的过程中可能会影响到表中的数据，为了演示这个过程，我们在修改表之前先使用desc person;显示表的结构，在使用select * from 表名显示表里面的内容。如下：

desc person;

select * from person;

在employee表中新增一列用于保存用户的电话。如下：

alter table person add number varchar(6) comment '这是员工的电话' after name;
select * from person;

1. 新增列SQL中的after表示将该列新增到哪一列之后，如果想要将新增的列放到第一列，可以将after及其之后的SQL换成not null first。
2. **可以看到在新增列之前插入表中的两条记录对应的image_path值为NULL，因此新增列后可能还需要对原来插入的记录进行修改。 **

6.3.修改列类型

在 MySQL 中，你可以使用以下语法来修改列的属性：

ALTER TABLE table_name MODIFY COLUMN 列名 修改后的列属性; 

或者，如果你还想改变列的名称，可以使用 CHANGE 关键字：

ALTER TABLE table_name CHANGE COLUMN 旧列名 新列名 修改后的列属性; 

示例
假设你有一个名为 employees 的表，其中有一个名为 salary 的列，数据类型为 INT。现在你想要将 salary 列的数据类型修改为 DECIMAL(10, 2)，以支持小数点后的数字：

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10, 2); 

如果你还想将列名从 salary 改为 employee_salary，你可以这样做：

ALTER TABLE employees CHANGE COLUMN salary employee_salary DECIMAL(10, 2); 

我们自己做一次

在修改之前我们可以先看一看表的结构相关信息：

现在我要将person表中name列的类型由varchar(20)改成varchar(60)。如下：

alter table person modify number varchar(60);

我们再看看表的信息

可以看出modify不是简单的修改，而是覆盖式的修改，所以如果我们还要保留注释的话就要再修改时加上comment。

6.4.删除列

SQL语句如下

ALTER TABLE table_name DROP 列名;

例如将person表中的number列删除。如下：

我们先看看删除之前的

然后我们删除

alter table person drop number;

我们检查一下结果

注意： 删除列后，该列对应的所有数据都没有了。

6.5.修改列名

SQL语句如下

ALTER TABLE table_name CHANGE 列名 新列名 新列数据类型;

先看看当前的

我们把person表的name列修改为abc列

alter table person change name abc varchar(20);

6.6.删除行

** 在SQL中，删除表中的行（记录）通常使用 DELETE 语句。DELETE 语句会删除符合指定条件的行，如果不指定条件，则会删除表中的所有行（但通常不建议这样做，因为它会清空整个表）。**

以下是一些关于如何使用 DELETE 语句删除表中行的示例：

• 删除特定条件的行

假设你有一个名为 purchase 的表，并且你想要删除某个特定客户的所有购买记录。你可以使用如下SQL语句：

delete from purchase where customer_id = 123; 

这条语句会删除 purchase 表中 customer_id 为 123 的所有行。

• 删除所有行（清空表）

虽然通常不推荐这样做，但如果你确实需要清空整个表，可以使用以下语句：

delete from purchase; 

或者，更推荐使用 TRUNCATE TABLE 语句来清空表，因为它通常比 DELETE 更快，并且不会逐行删除记录（这可能会影响性能），同时它也不会触发与 DELETE 相关的任何触发器：

truncate table purchase; 

但是，请注意，TRUNCATE TABLE 不能用于有外键依赖的表，除非你先删除了这些外键约束。

7.删除表

大部分SQL语句是这样子的

DROP TABLE [IF EXISTS] table_name;

但是有时我们能见到下面这样做的

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

说明一下：

1. 在创建表语句中加上TEMPORARY关键字，那么服务器将创建出一个临时表，该表会在你与服务器的会话终止时自动消失。
2. TEMPORARY表的名字可以与某个已有的永久表相同，当有TEMPORARY表存在时，对应的永久表会隐藏起来（即无法访问）。
3. 为了避免重新连接后（TEMPORARY已经不存在），在未做检测的情况下调用DROP误删了对应永久表，因此在使用DROP删除临时表时需要带上TEMPORARY关键字。

例如，我们把person表删除

show tables;
drop table person;
show tables;