MySQL：管理和操作数据表

ZachOn1y

于 2024-08-01 22:30:09 发布

阅读量560

点赞数 19

分类专栏： MySQL 文章标签： mysql 数据库数据库开发 sql 数据库系统

本文链接：https://blog.csdn.net/Zachyy/article/details/140858911

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

数据表是数据库的重要组成部分，每一个数据库都是由若干个数据表组成的。没有数据表就无法在数据库中存放数据。MySQL数据表的管理和操作是数据库管理员和开发人员日常工作中不可或缺的一部分。

创建数据表 CREATE

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。

基本语法

在MySQL中，可以使用 CREATE TABLE 语句创建表:

CREATE TABLE (IF NOT EXISTS)<表名> (表定义选项)(表选项)(分区选项);
'''
`[表定义选项]`的格式为：  

<列名1> <类型1> [,…] <列名n> <类型n>
'''

CREATE TABLE 命令语法比较多，其主要是由
表创建定义（create-definition）、
表选项（table-options）
分区选项（partition-options）所组成的。

使用CREATE TABLE创建表时，必须指定以下信息：

要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、
ALTER、INSERT等。
数据表中每个列（字段）的名称和数据类型，创建多个列要用逗号隔开。

使用说明

CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
<表名>：指定要创建表的名称，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。
在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，‘mydb’.‘mytbl’ 是合法的，但 ‘mydb.mytbl’ 不合法。
<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。
默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

示例

CREATE TABLE demo(
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(10) (DEFAULT '匿名' COMMENT '姓名',
	···
	(CONSTRAINT fk_demo0_demo FOREIGN KEY (demoID) REFERENCES demo(id)) 
	'''建立名为fk_demo0_demo的外键约束，demo为父表，
	   其主键demoID被demo关联'''
)ENGINE=INNODB DEFAULT CAHRSET=utf8;
# “COMMENT” 关键字后跟一个字符串来为表添加注释，提供关于表的额外信息

修改数据表 ALTER

修改数据表的前提是数据库中已经存在该表。修改表指的是修改数据库中已经存在的数据表的结构。

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等：

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

'''
修改选项的语法格式如下：

| ADD COLUMN <列名> <类型> [完整性约束]
| CHANGE COLUMN <旧列名> <新列名> <新列类型>  
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }  
| MODIFY COLUMN <列名> <类型>  
| DROP COLUMN <列名> [CASCADE|RESTRICT] 
| ADD CONSTRINT<完整性约束名> <完整性约束>
| DROP CONSTRINT<完整性约束名> [CASCADE|RESTRICT] 
| RENAME TO <新表名>  
| CHARACTER SET <字符集名>  
| COLLATE <校对规则名> 

'''

修改表名 RENAME

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

#TO 为可选参数，使用与否不影响结果

'''示例'''
ALTER TABLE student RENAME TO student_info;

修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。

注意事项：

在重命名表时，确保新的表名在数据库中是唯一的，以避免与其他表重名。
如果表有外键关联或被其他对象引用，需要确保相应的关联和引用也进行了更新。

修改存储引擎 ENGINE

ALTER TABLE [表名] ENGINE = [新引擎];

存储引擎的更换可能会影响表的性能、事务支持以及其他功能。在更换存储引擎之前，确保了解新存储引擎的特性和限制，并在生产环境中进行充分的测试。

另外，不同的数据库管理系统支持不同的存储引擎，并且某些存储引擎可能不可用或受到特定数据库版本的限制。

修改字符集 CHARACTER SET

MySQL 通过 ALTER TABLE 语句来实现表字符集的修改：

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

# DEFAULT 为可选参数，使用与否均不影响结果。

'''示例'''
ALTER TABLE student CHARACTER SET gb2312 
DEFAULT COLLATE gb2312_chinese_ci;

#将数据表 student 的字符集修改为 gb2312，校对规则修改为 gb2312_chinese_ci

通过修改表的字符集和排序规则，可以确保表能够存储和处理特定编码的数据。这对于需要支持不同语言或从不同字符集的数据库迁移数据时非常有用。

需要注意的是，修改表的字符集和排序规则可能会影响现有数据和查询的行为。在进行这些修改之前，请务必备份数据并仔细考虑其影响。

添加字段 ADD

MySQL 数据表是由行和列构成的，通常把表的 “列” 称为字段（Field）
把表的 “行” 称为记录（Record）。随着业务的变化，可能需要在已有的表中添加新的字段。

MySQL 允许在开头、中间和结尾处添加字段。一个完整的字段包括字段名、数据类型和 [[MySQL Constraint|约束条件]]。

在末尾添加字段 ADD

末尾添加字段的语法格式如下：

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

<表名> 为数据表的名字；
<新字段名> 为所要添加的字段的名字；
<数据类型> 为所要添加

ALTER TABLE student ADD new_data VARCHAR(10);

在开头添加字段 ADD…FIRST

在开头位置（第一列的前面）添加新字段，语法格式如下：

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

'''示例'''

alter table student ADD new_data INT(4) FIRST;

在中间位置添加字段 ADD…AFTER

在中间位置（指定的字段之后）添加字段，语法格式如下：

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

# AFTER 的作用是将新字段添加到某个已有字段后面。

'''示例'''
ALTER TABLE student brand_new_demo INT(4) AFTER new_data;

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

修改字段

修改字段名 CHANGE COLUMN

MySQL 中修改表字段名称的语法规则如下：

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

旧字段名：指修改前的字段名；
新字段名：指修改后的字段名；
新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的 “新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”
由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

"""示例"""
ALTER TABLE student CHANGE old new INT(4);
ALTER TABLE student CHANGE old old INT(4);
ALTER TABLE student CHANGE old new VARCHAR(10);

修改字段数据类型 MODIFY

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。

在 MySQL 中修改字段数据类型的语法规则如下：

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

表名：指要修改数据类型的字段所在表的名称；
字段名：指需要修改的字段；
数据类型：指修改后字段的新数据类型。

ALTER TABLE student MODIFY _data_ INT(4);

'''等价于'''

ALTER TABLE student CHANGE _data_ _data_ INT(4);

更换字段位置 MODIFY AFTER

将字段更换到目标字段后，在MySQL中无法使用BEFORE关键字，将字段更换到目标字段前，只能使用AFTER

ALTER TABLE table_name
MODIFY column_name column_datatype AFTER destinate_column_name

删除字段 DROP

删除字段是将数据表中的某个字段从表中移除，语法格式如下：

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

“字段名”指需要从表中删除的字段的名称。

删除数据表 DROP

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

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

基本语法

使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

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

表名1, 表名2, 表名3 ... 表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。
用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
表被删除时，用户在该表上的权限不会自动删除。

删除被其他表关联的主表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

当主表在存在外键约束时，不能被直接删除。

删除父表有以下两种方法：

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

ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp4_emp5;
#解除子表 tb_emp5 的外键约束

DROP TABLE tb_emp4;
#删除父表tb_emp4

查看表结构 DESC

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

DESCRIBE：以表格的形式展示表结构

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

DESCRIBE <表名>;

或简写成：

DESC <表名>;

![[MySQL DESC example.png]]

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