5.2 创建与管理表

数据库技术5.2创建与管理表5.2创建与管理表

在关系型数据库中，表是最基本的数据存储单元，用于以行和列的形式组织数据。表的创建是数据库设计的重要步骤，涉及定义表结构、字段数据类型及约束条件等。表的管理包括查看表结构、修改表结构（如添加、删除字段，修改字段数据类型）、复制表以及删除表等操作。这些操作赋予数据库管理员和开发人员根据业务需求高效地管理数据库表的能力。5.2.1

表的创建5.2.1表的创建在MySQL中，使用CREATETABLE语句可以创建一个新表。在创建表时，需要指定表名、字段名、数据类型、约束条件等。CREATETABLE语句的完整语句结构如下：

CREATETABLEtable_name(col1_namecol1_datatypecol1_constraints,col2_namecol2_datatypecol2_constraints,...colN_namecolN_datatypecolN_constraints,PRIMARYKEY(primary_key_col),FOREIGNKEY(foreign_key_col)REFERENCESother_table(referenced_col),UNIQUE(unique_col),CHECK(condition),INDEX(index_col))ENGINE=...DEFAULTCHARSET=...;5.2.1表的创建

例如在本书的教学实例中使用sims（学生信息管理系统）作为示例数据库，在这个库中的部份表包括classes（班级表）、students（学生表）、courses（课程表）、scores（成绩表）。各表结构如下表5.1-5.4所示：表5.1classes表结构表5.2students表结构表5.3courses表结构表5.4scores表结构5.2.1表的创建【示例1】创建表classes。CREATETABLEclasses(cls_idCHAR(8)PRIMARYKEY,--班级编号，数据类型为CHAR(8)，并设置为主键cls_nameVARCHAR(10)NOTNULL--班级名称，数据类型为VARCHAR(10)，并设置为非空约束)ENGINE=InnoDBDEFAULTCHARSET=gb2312;5.2.1表的创建【示例2】创建表students。CREATETABLEstudents(stu_idCHAR(14)PRIMARYKEY,--学号，数据类型为CHAR(14)，并设置为主键stu_nameVARCHAR(10)NOTNULL,--姓名，数据类型为VARCHAR(10)，并设置为非空约束stu_genderCHAR(1),--性别，数据类型为CHAR(1)stu_nationCHAR(10),--民族，数据类型为CHAR(10)stu_birthDATE,--出生日期，数据类型为DATEstu_entrancedateDATE,--入学日期，数据类型为DATEcls_idCHAR(8),--班级编号，数据类型为CHAR(8)，此处先不设置外键约束，后续通过ALTERTABLE添加stu_addressVARCHAR(32),--家庭地址，数据类型为VARCHAR(32)stu_phoneCHAR(11),--联系电话，数据类型为CHAR(11)stu_resumeVARCHAR(256)--备注，数据类型为VARCHAR(256))ENGINE=InnoDBDEFAULTCHARSET=gb2312;

本示例在实现students表的创建时并没有设置外键约束，这是因为添加外键约束通常需要确保被引用的表（即classes表）已经存在，并且其主键字段（即cls_id）与当前表的cls_id字段类型一致。因此一般使用ALTERTABLE命令在表创建后添加外键约束。这一内容将在后续的课程中学习。示例4中scores表的创建也是因为相同的原因而没有设置外键。5.2.1表的创建【示例3】创建表courses。CREATETABLEcourses(crs_idCHAR(6)PRIMARYKEY,--课程编号，数据类型为CHAR(6)，并设置为主键crs_nameVARCHAR(20)NOTNULL,--课程名，数据类型为VARCHAR(20)，并设置为非空约束crs_hoursTINYINT,--学时数，数据类型为TINYINTcrs_semsterTINYINT,--学期，数据类型为TINYINTcrs_creditTINYINT,--学分，数据类型为TINYINTcrs_natureVARCHAR(10)--课程性质，数据类型为VARCHAR(10))ENGINE=InnoDBDEFAULTCHARSET=gb2312;5.2.1表的创建【示例4】创建表scores。CREATETABLEscores(stu_idCHAR(14),--学号，数据类型为CHAR(14)，引用students表的外键crs_idCHAR(6),--课程编号，数据类型为CHAR(6)，引用courses表的外键sc_gradeDECIMAL(5,2),--期末成绩，数据类型为DECIMAL(5,2)，可存储最多5位数字，其中小数点后有2位sc_rebuildDECIMAL(5,2),--补考成绩，数据类型为DECIMAL(5,2)，同样可存储最多5位数字，小数点后有2位PRIMARYKEY(stu_id,crs_id)--将stu_id和crs_id的组合设置为主键，确保每个学生在每门课程中的成绩记录是唯一的)ENGINE=InnoDBDEFAULTCHARSET=gb2312;--指定存储引擎为InnoDB，以支持外键等高级功能

本示例在实现students表的创建时使用了PRIMARYKEY(stu_id,crs_id)子句来实现复合主键的设置，这是因为在标准的数据库设计实践中，通常不会将两个外键同时设置为主键，而是会创建一个复合主键或单独的主键（例如自增ID）。5.2.1表的创建Navicat图形化界面创建表：1.打开Navicat并连接到你的MySQL服务器。2.展开连接并进一步展开目标数据库，右键单击表→

选择新建表。如图5.3。3.在工作区编辑字段，包括字段名、类型、键、索引等。如图5.4。（注意观察Navicat设计表窗口工作区，索引、外键、检查等均在此操作）4.使用添加字段、插入字段、删除字段编辑多个字段。

5.保存表，在弹出的对话框内输入表名。图5.3Navicat新建表图5.4Navicat设计表窗口工作区5.2.2

表的查看5.2.2表的查看

创建数据表之后，可以使用SHOWTABLES命令查询数据库中已经创建的表的列表。如果要查看某一个表的表结构则可以使用DESCRIBEtable_name（可简写为DESC）命令或SHOWCOLUMNSFROMtable_name命令。另外，使用SHOWCREATETABLEtable_name命令会显示创建表的完整SQL语句，包括所有列的定义、索引、外键等。此处table_name为待查看表结构的表名。mysql>SHOWTABLES;运行结果如图5-5所示。图5.5SHOWTABLES运行结果5.2.2表的查看mysql>DESCstudents;或mysql>DESCRIBEstudents;或mysql>SHOWCOLUMNSFROMstudents;运行结果如图5-6所示。mysql>SHOWCREATETABLEcourses;运行结果如图5-7所示。图5.6查看表结构运行结果图5.5SHOWTABLES运行结果图5.7查看表创建SQL语句运行结果5.2.3表的修改5.2.3表的修改

在MySQL中，ALTERTABLE语句用于修改现有表的结构，例如添加、删除或修改列，或者添加、删除索引等。ALTERTABLE语法格式如下：ALTERTABLEtable_nameaction;

table_name：要修改的表的名称。

action：要执行的操作，如添加列、删除列、修改列等。5.2.3表的修改1.添加列ALTERTABLEtable_nameADDcolumn_namecolumn_definition[FIRST|AFTERexisting_column];

column_name：是新列的名称。

column_definition：指定新列的数据类型和其他属性（如NOTNULL,DEFAULT等）。

FIRST或AFTERexisting_column：用于指定新列的位置，existing_column是表中已存在的列。可选。【示例】向名为students的表中添加一个名为email的列，使其位于stu_phone字段之后，并查看运行结果。ALTERTABLEstudentsADDCOLUMNemailVARCHAR(255)AFTERstu_phone;DESCstudents；运行结果如图5-8图5.8添加列运行结果5.2.3表的修改2.删除列ALTERTABLEtable_nameDROPCOLUMNcolumn_name;

column_name：要删除的列的名称。【示例】删除students表中的名为email的列ALTERTABLEstudentsDROPCOLUMNemail;3.修改列的数据类型或属性ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namenew_column_definition;

new_column_definition

指定新列的数据类型和其他属性。【示例】修改courses表中crs_semster列的数据类型为CHAR（10），且值不为空。ALTERTABLEcoursesMODIFYCOLUMNcrs_semsterCHAR(10)NOTNULL;5.2.3表的修改4.重命名列ALTERTABLEtable_nameCHANGECOLUMNold_column_namenew_column_namenew_column_definition;或者在MySQL８.０以上版本中，也可以使用：ALTERTABLEtable_nameRENAMECOLUMNold_column_nameTOnew_column_name;

old_column_name

是要重命名的列的原始名称。

new_column_name

是新列的名称。

new_column_definition

指定新列的数据类型和其他属性，使用CHANGE子句实现时可以同时更改列的其他属性。【示例】重命名students表中stu_entrancedate列的列名为stu_enrollDate。ALTERTABLEstudentsCHANGECOLUMNstu_entrancedatestu_enrollDatedateNOTNULL;或ALTERTABLEstudentsRENAMECOLUMNstu_enrollDateTOstu_entrancedate;5.2.3表的修改５.重命名表ALTERTABLEold_table_nameRENAMETOnew_table_name;你也可以使用简化的表重命名命令：RENAMETABLEold_table_nameTOnew_table_name;

old_table_name

是当前表的名称。

new_table_name

是新表的名称。【示例】将表名从students改为student_info。ALTERTABLEstudentsRENAMETOstudent_info;或将表名从student_info改为students。RENAMETABLEstudent_infoTOstudents;5.2.3表的修改

以上是ALTERTABLE语句的一些常见用法。除此之外，ALTERTABLE命令还可以用于添加索引、删除索引、修改表的ENGINE以及DEFAULTCHARSET等。根据具体需求，可以选择合适的语法来修改表的结构。但要记住在执行这些操作之前一定要备份相关数据，以防止数据丢失或损坏。Navicat图形化界面修改表：1.右键单击目标表→

选择设计表。（注意表的右键菜单可实现的操作，如复制、删除等）2.在如图5.4设计表中执行表设计操作。3.保存表。5.2.4

表的复制5.2.4表的复制在MySQL中，表的复制可以仅复制表结构或同时复制表的数据。表的复制实际上是通过新建表操作来实现的。具体的实现方法如下：1.仅复制表结构--创建空表new_table_name，结构与old_table_name一致

CREATETABLEnew_tableLIKEold_table_name;2.复制表结构及数据--创建表new_table_name，结构与old_table_name一致，并复制所有数据

CREATETABLEnew_table_nameASSELECT...FROMold_table_nameWHERE...;需要注意的是，ASSELECT可自定义筛选条件（如WHEREage>18），但可能丢失部分约束（如自增属性）。【示例】将表名为students的表及其全部数据复制到表student_info。CREATETABLEstudent_infoASSELECT*FROMstudents;5.2.5表的删除5.2.5表的删除

在MySQL中，删除表的操作可以通过DROPTABLE语句来完成。这个操作会永久删除数据库中的表及其所有的数据。--直接删除表（不可逆！）

DROPTABLEtable_name;--安全删除：仅当表存在时执行

DROPTABLEIFEXISTStable_