【MySQL】MySQL表的操作

目录
1.创建表
2.创建表案例
3、检察当前数据库下所有的表
4、检察表结构
5、检察创建表时的相关细节
 6、修改表
6.1.修改表名
6.2.新增列
6.3.修改列范例
6.4.删除列
6.5.修改列名
6.6.删除行
7.删除表

---

1.创建表

SQL创建表的根本语法

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

复制代码

具体说明
CREATE TABLE: 这是创建表的关键字。
[IF NOT EXISTS]: 这是一个可选项，用于防止在表已经存在的环境下执行创建表的操作时出现错误。假如表已经存在，并且使用了这个选项，SQL语句将不会执行，也不会报错。
table_name: 这是你要创建的表的名称。表名必须符合数据库的命名规则，通常是以字母开头，可以包含字母、数字、下划线等字符。
field1 datatype1 [COMMENT '注释信息']:

• field1: 列名，即表中字段的名称。
  
• datatype1: 列的数据范例，如 INT, VARCHAR(255), DATE 等。
  
• [COMMENT '注释信息']: 可选项，用于为字段添加注释，方便明确字段的用途。
  

CHARSET=charset_name:

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

COLLATE=collation_name:

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

ENGINE=engine_name:

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

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

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

复制代码

这个语句创建了一个 users 表，其中 id 是主动递增的主键，username 和 email 黑白空字段，并且每个字段都有相应的注释。表的字符集是 utf8mb4，校验规则是 utf8mb4_unicode_ci，存储引擎是 InnoDB。
 
   使用show enginesSQL可以检察当前MySQL支持的存储引擎。如下：

1. show engines;

复制代码

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

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

1. create database db1;
2. show databases;
3. use db1;

复制代码

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

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

复制代码

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

而当您使用 MyISAM 存储引擎创建表时，会产生以下文件：
在MySQL数据库中，/var/lib/mysql/db1目录下的文件内容反映了数据库db1中表的数据存储环境。该目录下包含以下文件：

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

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

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

综上所述，这些文件的存在和它们的内容反映了MySQL数据库db1中使用MyISAM存储引擎的user表的数据和索引存储环境。.sdi文件提供了表结构的具体信息，而.MYD和.MYI文件分别存储了表的数据和索引。
3、检察当前数据库下所有的表

1. show tables;

复制代码

4、检察表结构

1. desc 表名;

复制代码

 

以下是对这些术语的具体表明：
Field（字段）：这代表表中的列名，即字段的名称。
Type（范例）：这体现字段的数据范例，比方int、varchar、date等。数据范例决定了字段能够存储的数据范例及其范围。
Null（空值）：假如此字段体现YES，则体现该字段可以接受空值（NULL）。假如体现NO，则意味着该字段不允许为空，即必须有一个有效的值。
Key（索引）：这体现字段是否被索引。常见的索引范例包括：PRI：主键索引（Primary Key），表中每一行都必须有一个唯一的值。

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

Default（默认值）：这体现字段的默认值。假如在插入新记载时没有为该字段提供值，则会使用此默认值。假如字段没有默认值，则此列通常体现为空。
Extra（额外信息）：这提供了关于字段的额外信息。

• 比方：auto_increment：体现该字段的值会主动增加，通常用于主键字段。
  
• on update CURRENT_TIMESTAMP：体现每次更新记载时，该字段会主动更新为当前时间戳。
  
• 假如此列为空，则体现没有额外的字段信息。
  

5、检察创建表时的相关细节

1. show create table 表名 \G

复制代码

 6、修改表

这里的修改表包括了修改表的名字，新增列，修改列范例，删除列，修改列名
6.1.修改表名

1. RENAME TABLE table_name TO 新表名;

复制代码

比如我们吧abc修改为person 

6.2.新增列

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

复制代码

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

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

1. desc person;

复制代码

1. select * from person;

复制代码

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

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

复制代码

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

6.3.修改列范例

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

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

复制代码

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

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

复制代码

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

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

复制代码

假如你还想将列名从 salary 改为 employee_salary，你可以如许做：

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

复制代码

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

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

1. alter table person modify number varchar(60);

复制代码

 

我们再看看表的信息

可以看出modify不是简单的修改，而是覆盖式的修改，所以假如我们还要生存注释的话就要再修改时加上comment。
6.4.删除列

SQL语句如下

1. ALTER TABLE table_name DROP 列名;

复制代码

比方将person表中的number列删除。如下：
我们先看看删除之前的

然后我们删除

1. alter table person drop number;

复制代码

 

我们检查一下结果

   留意： 删除列后，该列对应的所有数据都没有了。
  6.5.修改列名

 SQL语句如下

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

复制代码

先看看当前的 

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

1. alter table person change name abc varchar(20);

复制代码

6.6.删除行

        在SQL中，删除表中的行（记载）通常使用 DELETE 语句。DELETE 语句会删除符合指定条件的行，假如不指定条件，则会删除表中的所有行（但通常不建议如许做，因为它会清空整个表）。
以下是一些关于如何使用 DELETE 语句删除表中行的示例：

• 删除特定条件的行
  

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

1. delete from purchase where customer_id = 123; 

复制代码

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

• 删除所有行（清空表）
  

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

1. delete from purchase; 

复制代码

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

1. truncate table purchase; 

复制代码

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

大部分SQL语句是如许子的

1. DROP TABLE [IF EXISTS] table_name;

复制代码

但是有时我们能见到下面如许做的

1. DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

复制代码

说明一下：

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

比方，我们把person表删除

1. show tables;
2. drop table person;
3. show tables;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。