项目4创建与管理MySQL数据表

项目4创建与维护数据表《MySQL数据库案例教程》

理解MySQL的常用字段类型和字段属性。掌握创建和维护数据表的SQL语法。会使用MySQLWorkbench创建和管理数据表。

学习目标根据业务需求选择合适的字段类型和属性创建数据表，体会设计的规范性、安全性，以及以用户为中心的服务精神。通过对MySQL语法的学习和实践，培养严谨的学习态度和科学精神。

素质目标任务4.1创建数据表任务4.2管理数据表任务4.3使用图形化工具创建与管理数据表

项目主要内容任务4.1创建数据表4.1.1创建数据表4.1.2字段的数据类型4.1.3字段的属性4.1.1创建数据表MySQL中创建数据表的语句是CREATETABLE。1.语法格式CREATE[TEMPORARY]TABLE[IFNOTEXISTS]数据表名(字段名字段类型[字段属性]…)[表选项];2.语句功能在当前数据库中创建数据表。

知识学习3.语法说明(1)“数据表名”是要创建的数据表的名字。(2)(字段名数据类型[字段属性]…)是数据表中字段的名称、字段的数据类型与属性等。(3)“表选项”用于设置表的相关特性，如设置该数据表的存储引擎（ENGINE）、字符集（CHARSET）和校对规则（COLLATE）等。(4)可选项TEMPORARY表示创建的数据表是个临时表，仅在当前会话中可见，在会话关闭时会自动删除。(5)可选项IFNOTEXSITS用于判断要创建的数据表是否已经存在。在语句中使用该选项可以避免发生创建同名数据表的错误。(6)在创建数据表之前应该先使用“USE数据库名”语句打开要创建的数据表所在的数据库。

知识学习4.1.2字段的数据类型在创建数据表时，必须为每个字段指定它的数据类型。字段的数据类型决定了每个字段能够存储的数据的类型以及占用的存储空间的大小。MySQL提供了多种数据类型，主要分为三类：数值类型、字符串类型、日期与时间类型。

知识学习1.数值类型MySQL中的数值类型大体可以分为整数类型和浮点类型。为了数据空间的有效使用，根据字段取值范围的不同，整数类型又分为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT五种。

知识学习表4-1MySQL的整数类型

知识学习数据类型字节数取值范围说明TINYINT1有符号：-128~127无符号：0~255最小整数SMALLINT2有符号：-32768~32767无符号：0~65535小型整数MEDIUMINT3有符号：-8388608~8388607无符号：0~16777215中型整数INT4有符号：-2147483648~2147483647无符号：0~4294967295常规整数BIGINT8有符号：-9223372036854775808~9223372036854775807无符号：0~18446744073709551615较大整数1.数值类型浮点类型又分为FLOAT、DOUBLE、DECIMAL等。

知识学习表4-2MySQL的浮点类型

知识学习数据类型字节数取值范围说明FLOAT4有符号：-3.402823466E+38~-1.175494351E-38无符号：0/1.175494351E-38~3.402823466E+38单精度DOUBLE8有符号：-1.7976931348623157E+308~-2.2250738585072014E-308无符号：0/-2.2250738585072014E-308~1.7976931348623157E+308双精度DECIMAL（M,D）M+2有符号：-1.7976931348623157E+308~-2.2250738585072014E-308无符号：0/-2.2250738585072014E-308~1.7976931348623157E+308定点数2.字符串类型MySQL提供了多种字符串类型，包括CHAR、VARCHAR、TEXT、ENUM、SET等，可以存储的字符串从简单的一个字符到巨大的文本块或二进制字符串数据。

知识学习表4-3MySQL的字符串类型

知识学习数据类型可存储字符个数说明CHAR0~28-1固定长度的字符串VARCHAR0~216-1可变长度的字符串TINYTEXT0~28-1短文本字符串TEXT0~216-1长文本数据MEDIUMTEXT0~224-1中等长度文本数据LONGTEXT0~232-1极大文本数据ENUM枚举类型，只能存储一个值，枚举语法：ENUM(元素1,元素2,...元素N)SET集合类型，可以存储最多64个值。集合语法：SET(元素1,元素2,...元素N)3.时间日期类型MySQL表示日期时间的数据类型常用的有YEAR、DATE、TIME、DATETIME和TIMESTAMP。

知识学习表4-4MySQL的日期时间类型

知识学习类型存储格式功能说明YEARYYYY年份值DATEYYYY-MM-DD（年月日）日期值TIMEHH:MM:SS（时分秒）时间值或持续时间DATETIMEYYYY-MM-DDHH:MM:SS（年月日时分秒）混合日期和时间值TIMESTAMPYYYY-MM-DDHH:MM:SS混合日期和时间值，时间戳4.1.3字段的属性字段属性用于对数据表中字段进行规范和约束，当数据进行增删改操作时，需要满足字段类型和字段属性的要求，这样能够保证数据的有效性和完整性。

知识学习表4-5MySQL的字段属性

知识学习字段属性语法格式功能说明非空NOTNULL字段的值不允许为空唯一键UNIQUE字段的值不能重复出现默认值DEFAULT插入一条新记录时，如果没有给字段赋值，会自动使用默认值主键PRIMARYKEY唯一键属性与非空属性的组合，一张表中只能有一个自增长AUTO_INCREMENT插入一条新记录时，字段的值自动增长，只能对应于整数类型字段，通常为主键备注COMMENT用文字描述字段的含义，方便团队合作及后期应用程序的开发

案例活动在项目3中已经完成了实训基地管理系统数据库basemanagement的创建和管理工作，根据业务需求接下来要在这个数据库中，创建4张数据表，它们分别是：实训基地信息表baseinfo管理员信息表basemanager设备信息表deviceinfo维修记录表maintaininfo各数据表的结构描述如表4-6至表4-9所示。表4-6实训基地信息表baseinfo表结构

案例活动字段类型空默认注释lab_id(主键)varchar(6)否实训室IDlab_namevarchar(45)是NULL实训室名称lab_areadecimal(10,0)是NULL实训室面积locationvarchar(45)是NULL实训室地点manager_idvarchar(4)是NULL管理员ID表4-7管理员信息表basemanager

案例活动字段类型空默认注释manager_id(主键)varchar(4)否管理员IDmanager_namevarchar(45)是NULL管理员姓名gendervarchar(1)是NULL性别dateofbirthdate是NULL出生日期表4-8设备信息表deviceinfo

案例活动字段类型空默认注释device_id(主键)varchar(10)否设备IDdevice_namevarchar(45)是NULL设备名称manufacturervarchar(45)是NULL设备品牌device_pricedecimal(10,0)是NULL设备价格lab_idvarchar(6)是NULL实训室IDentry_timedate是NULL登记日期statusint(11)是NULL设备状态表4-9维修记录表maintaininfo

案例活动字段类型空默认注释device_id(主键)varchar(10)否设备IDmaintaintimedate是NULL维修时间faultcausevarchar(45)是NULL故障原因solutionvarchar(45)是NULL解决办法

案例活动【例4.1】根据表4-6和表4-7所示的数据表结构创建“实训基地信息表baseinfo”和“管理员信息表basemanager”。操作步骤：(1)打开数据库basemanagement。USEbasemanagement;(2)使用CREATETABLE语句创建baseinfo数据表DROPTABLEIFEXISTSbaseinfo;CREATETABLEIFNOTEXISTSbaseinfo(lab_idVARCHAR(6)PRIMARYKEYCOMMENT'实训室ID',lab_nameVARCHAR(45)DEFAULTNULLCOMMENT'实训室名称',base_areaDECIMAL(10,0)DEFAULTNULLCOMMENT'实训室面积',locationVARCHAR(45)DEFAULTNULLCOMMENT'实训室地点',manager_idVARCHAR(4)DEFAULTNULLCOMMENT'管理员ID')COMMENT='实训基地信息表';

案例活动【例4.1】根据表4-6和表4-7所示的数据表结构创建“实训基地信息表baseinfo”和“管理员信息表basemanager”。运行结果：

案例活动【例4.1】根据表4-6和表4-7所示的数据表结构创建“实训基地信息表baseinfo”和“管理员信息表basemanager”。操作步骤：(3)使用CREATETABLE语句创建basemanager数据表DROPTABLEIFEXISTSbasemanager;CREATETABLEIFNOTEXISTSbasemanager(manager_idVARCHAR(4)PRIMARYKEYCOMMENT'管理员ID',manager_nameVARCHAR(45)DEFAULTNULLCOMMENT'管理员姓名',genderVARCHAR(1)DEFAULTNULLCOMMENT'性别',dateofbirthDATEDEFAULTNULLCOMMENT'出生日期')COMMENT='管理员信息表';(4)显示“QueryOK”，即表示创建成功。

案例活动【例4.1】根据表4-6和表4-7所示的数据表结构创建“实训基地信息表baseinfo”和“管理员信息表basemanager”。说明：DROPTABLEIFEXISTSbaseinfo;和DROPTABLEIFEXISTSbasemanager;语句的作用是如果baseinfo和basemanager数据表存在的话就将它们删除。这么做的目的是防止创建已经存在的同名数据表，否则在创建数据表时会出现错误。basemanagement数据库中的“设备信息表deviceinfo”和“维修记录表maintaininfo”可以使用相同的方法创建。任务4.2管理数据表4.2.1查看数据表及表结构4.2.2修改数据表及表结构4.2.3删除数据表4.2.1查看数据表及表结构1.查看当前数据库中的数据表名称通过SHOWTABLES语句查看数据表相关信息。(1)语法格式SHOWTABLES[LIKE匹配模式];(2)语句功能查看当前数据库中的数据表名称。

知识学习1.查看当前数据库中的数据表名称通过SHOWTABLES语句查看数据表相关信息。(3)语法说明若不使用可选项“LIKE匹配模式”，则表示查看当前数据库中的所有数据表；若使用则按照“匹配模式”查看数据表。匹配模式符有两种：“%”表示匹配一个或多个字符，代表任意长度的字符串；“_”（下划线）表示匹配一个字符。

知识学习

案例活动【例4.2】查看数据库中的数据表。(1)查看basemanagement数据库中的所有数据表USEbasemanagement;SHOWTABLES;运行结果：前期创建的两个数据表都在当前数据库中。

案例活动【例4.2】查看数据库中的数据表。(2)按照匹配模式查看数据表，查看以“base”开头的数据表。SHOWTABLESLIKE'base%';运行结果：匹配到两个数据表。

案例活动【例4.2】查看数据库中的数据表。(3)按照匹配模式查看数据表，查看以“base”开头，后面匹配一个字符的数据表。SHOWTABLESLIKE'base_';运行结果：没有匹配到合适的数据表。2.查看当前数据库中数据表的状态除了可以査看当前数据库中有哪些数据表外，还可以査看数据表的相关信息，如数据表的名称、存储引擎、创建时间等。

知识学习2.查看当前数据库中数据表的状态(1)语法格式SHOWTABLESTATUS[FROM数据库名][LIKE匹配模式];(2)语句功能查看当前数据库中数据表的状态。(3)语法说明可选项“FROM数据库名”表示需要查看的数据表所在的数据库。如果省略该选项，则表示是当前数据库。

知识学习

案例活动【例4.3】查看basemanagement数据库下表名含有“info”的数据表的状态信息。(1)按照匹配模式查看数据表信息。SHOWTABLESTATUSLIKE'%info'\G

案例活动【例4.3】运行结果：表4-10数据表的相关信息字段名称描述Name数据表的名称Engine数据表的存储引擎Version数据表的结构文件版本号Row_format记录的存储格式，有Dynamic（动态）、Fixed（固定）、Compressed（压缩）、Redundant（冗余）和Compact（紧凑）等Data_length数据文件的长度（MyISAM存储引擎）或为集群索引分配的内存（InnoDB存储引擎），均以字节为单位。Create_time数据表的创建时间Collation数据表的校对集3.查看数据表的字段信息MySQL提供的DESCRIBE语句可以查看数据表中所有字段或指定字段的信息，包括字段名、字段的数据类型等。其中，DESCRIBE语句可以简写成DESC。(1)语法格式语法格式1：DESCRIBE|DESC数据表名;语法格式2：DESCRIBE|DESC数据表名字段名;

知识学习3.查看数据表的字段信息(2)语句功能查看指定数据表中字段的相关信息。(3)语法说明第一种语法格式的语句用于查看指定数据表中所有字段的信息，第二种语法格式用于查看指定数据表中指定字段的信息。

知识学习

案例活动【例4.4】查看数据表basemanager的字段信息。(1)SQL语句：DESCbasemanager;运行结果：

案例活动【例4.4】查看数据表baseinfo中manager_name字段的信息。(1)SQL语句：DESCbasemanagermanager_name;运行结果：4.查看数据表的创建语句通过SHOWCREATETABLE语句查看创建数据表所用的语句。(1)语法格式SHOWCREATETABLE数据表名\G(2)语句功能查看指定数据表的创建语句。

知识学习4.查看数据表的创建语句(3)语法说明①通过SHOWCREATETABLE语句不仅可以查看创建数据表的SQL语句，还可以查看数据表的存储引擎和字符编码等信息。②\G可以使输出结果信息更加美观，方便查看和阅读。

知识学习

案例活动【例4.6】查看数据表basemanager的创建语句。(1)SQL语句：SHOWCREATETABLEbasemanager\G运行结果：5.查看数据表的结构通过SHOWCOLUMNS语句査看数据表的结构。(1)语法格式SHOW[FULL]COLUMNSFROM[数据库名].数据表名;(2)语句功能查看指定数据表的结构。

知识学习5.查看数据表的结构(3)语法说明①可选项FULL表示显示详细内容。不使用该选项时，语句运行结果与DESC语句的结果相同；使用该选项时，语句运行结果中可以查看到字段的权限以及COMMENT字段的注释信息等。②“数据表名”若有“数据库名.”表示查看指定数据库中指定数据表的结构，若无此项则查看的是当前数据库中指定数据表的结构。

知识学习

案例活动【例4.7】使用两种方式查看数据表basemanager的表结构并进行比较。(1)SQL语句：SHOWCOLUMNSFROMbasemanager;运行结果：

案例活动【例4.7】使用两种方式查看数据表basemanager的表结构并进行比较。(1)SQL语句：SHOWFULLCOLUMNSFROMbasemanager;运行结果：总结：使用FULL选项后，显示的信息增加了权限和注释。4.2.2修改数据表及表结构数据表在创建成功后还可以被修改。MySQL使用ALTERTABLE语句修改数据表的结构。常用的修改数据表结构的操作有修改表名、修改字段名或字段的数据类型、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

知识学习1.修改数据表语句的一般形式(1)语法格式ALTERTABLE数据表名[修改选项];(2)语句功能修改指定数据表中的相关内容。

知识学习(3)语法说明表4-11修改选项

知识学习功能语法格式添加新字段ADDCOLUMN字段名类型[FIRST|AFTER字段名]修改字段名称CHANGECOLUMN旧字段名新字段名新字段类型修改字段属性MODIFYCOLUMN字段名类型字段属性删除字段DROPCOLUMN字段名更改表名RENAMETO新表名2.修改数据表名称(1)语法格式语法格式1：ALTERTABLE旧表名RENAME[TO|AS]新表名；语法格式2：RENAMETABLE旧表名1TO新表名1[,旧表名2TO新表名2]...(2)语句功能将旧表名改为新表名。

知识学习2.修改数据表名称(3)语法说明使用第一种方法修改数据表名时，RENAME后的TO或AS可以省略，而使用第二种方法改名时RENAMETABLE和必须使用TO，另外第二种方法可以同时修改多个数据表的名称。

知识学习

案例活动(1)使用RENAMETABLE语句修改表名RENAMETABLEbaseinfotolabinfo,basemanagertolabmanager;SHOWTABLES;运行结果：【例4.8】使用两种方式修改数据表名称。使用语句格式2将baseinfo表的名称修改为labinfo，basemanager表的名称修改为labmanager，再使用语句格式1将它们修改回来。

案例活动(2)使用ALTERTABLE语句修改表名ALTERTABLElabinfoRENAMETObaseinfo;ALTERTABLElabmanagerRENAMETObasemanager;SHOWTABLES;运行结果：【例4.8】使用两种方式修改数据表名称。使用语句格式2将baseinfo表的名称修改为labinfo，basemanager表的名称修改为labmanager，再使用语句格式1将它们修改回来。3.修改数据表的选项数据表中的表选项有字符集、存储引擎以及校对规则集等，可以通过ALTERTABLE语句来修改。(1)语法格式ALTERTABLE数据表名表选项[=]值;(2)语句功能修改指定数据表的字符集、存储引擎以及校对规则集等选项。

知识学习3.修改数据表的选项(3)语法说明①“表选项[=]值”表示是将“表选项”修改成指定的“值”。②表选项可以是CHARACTERSET、ENGINE、COLLATE，分别表示字符集、存储引擎和校对规则集等。

知识学习

案例活动(1)SQL语句：ALTERTABLEbaseinfoENGINE=InnoDBCHARACTERSET=utf8COLLATE=utf8_general_ci;ALTERTABLEbasemanagerENGINE=InnoDBCHARACTERSET=utf8COLLATE=utf8_general_ci;SHOWTABLESTATUS\G【例4.9】使用ALTERTABLE将baseinfo表的表选项修改为：存储引擎为InnoDB，字符编码为utf8，校对集为utf8_general_ci。

案例活动运行结果：【例4.9】使用ALTERTABLE将baseinfo表的表选项修改为：存储引擎为InnoDB，字符编码为utf8，校对集为utf8_general_ci。4.修改字段名通过使用ALTERTABLE语句中CHANGE选项来实现。(1)语法格式ALTERTABLE数据表名CHANGE[COLUMN]旧字段名新字段名数据类型[字段属性](2)语句功能将指定数据表中旧字段名修改为新字段名，同时也可以修改字段的数据类型以及字段的属性。

知识学习4.修改字段名(3)语法说明①在语句中“旧字段名”指的是字段修改前的名称，“新字段名”指的是字段修改后的名称。②“数据类型”表示新字段名的数据类型，不能省略，即使与旧字段的数据类型相同，也必须重新设置。

知识学习

案例活动(1)SQL语句：ALTERTABLEbaseinfoCHANGEbase_arealab_areaDECIMAL(10,0);DESCbaseinfo;【例4.10】将数据表baseinfo中的base_area字段改名为lab_area，并查看修改结果。运行结果：5.修改字段的数据类型通过使用ALTERTABLE语句中的MODIFY选项实现。(1)语法格式ALTERTABLE数据表名MODIFY[COLUMN]字段名新数据类型[字段属性];(2)语句功能将数据表中某个字段的数据类型修改成新的数据类型。(3)语法说明①语句中的COLUMN选项可以省略。②如果在修改字段类型的同时不修改字段的属性，则字段属性选项可以省略。

知识学习

案例活动(1)将数据表baseinfo中lab_area字段的类型修改为DECIMAL(10,2)。ALTERTABLEbaseinfoMODIFYlab_areaDECIMAL(10,2)NOTNULLdefault0;DESCbaseinfo;【例4.11】修改字段的数据类型。运行结果：

案例活动(2)将数据表baseinfo中gender字段的数据类型修改为ENUM。ALTERTABLEbasemanagerMODIFYgenderENUM('m','f');DESCbasemanager;【例4.11】修改字段的数据类型。运行结果：6.修改字段的位置通过使用ALTERTABLE语句中的MODIFY选项实现。(1)语法格式ALTERTABLE数据表名MODIFY[COLUMN]字段名1数据类型[字段属性][FIRST|AFTER字段名2];(2)语句功能修改字段的位置。(3)语法说明语句中使用FIRST表示将“字段名1”调整为数据表的第1个字段，使用AFTER字段名2表示将“字段名1”移动到“字段名2”的后面。

知识学习

案例活动(1)将数据表baseinfo中lab_name字段的位置调整到第一个。ALTERTABLEbaseinfoMODIFYlab_nameCHAR(45)FIRST;DESCbaseinfo;【例4.12】修改字段的位置运行结果：

案例活动(1)将数据表baseinfo中lab_name字段的位置调整到lab_id字段之后。ALTERTABLEbaseinfoMODIFYlab_nameCHAR(45)AFTERlab_id;DESCbaseinfo;【例4.12】修改字段的位置运行结果：7.新增字段通过使用ALTERTABLE语句中的ADD选项新增字段。(1)语法格式语法格式1：ALTERTABLE数据表名ADD[COLUMN]新字段名字段类型[FIRST|AFTER字段名]语法格式2：ALTERTABLE数据表名ADD[COLUMN](新字段名1字段类型1,新字段名2字段类型2,…)(2)语句功能在指定的数据表中增加新的字段。

知识学习7.新增字段(3)语法说明①第一种格式的语句可以新增一个字段，并可以通过FIRST或AFTER字段名来指定新增字段的位置。②第二种格式的语句可以同时新增多个字段。③在不指定位置的情况下，新增的字段默认添加到表的最后。

知识学习

案例活动(1)SQL语句：ALTERTABLEbaseinfoADDbase_introTEXTCOMMENT'实训室介绍';DESCbaseinfo;【例4.13】在数据表baseinfo中新增一个TEXT类型的字段base_intro。运行结果：8.删除字段通过ALTERTABLE语句的DROP选项实现。(1)语法格式ALTERTABLE数据表名DROP[COLUMN]字段名;(2)语句功能删除指定的数据表中的指定字段。(3)语法说明语句中的COLUMN选项可以省略。

知识学习

案例活动(1)SQL语句：ALTERTABLEbaseinfoDROPbase_intro;DESCbaseinfo;【例4.14】删除数据表baseinfo中的base_intro字段。运行结果：原先的base_intro字段被删除4.2.3删除数据表4.2.3删除数据表删除数据表操作指的是删除指定数据库中已经存在的表。在删除数据表的同时，存储在数据表中的数据都将被删除，与该数据表相关的其他数据库也都会受到影响。

知识学习1.语法格式DROPTABLE[IFEXISTS]数据表列表;2.语句功能删除指定的一个或多个数据表。3.语法说明此语句可以删除一个或多个数据表。如果删除多个数据表，“数据表列表”中的各表之间使用英文逗号分隔。可选项IFEXISTS用于在检验被删除的数据表是否存在，防止产生操作错误。

知识学习

案例活动(1)SQL语句：DROPTABLEIFEXISTSbaseinfo;SHOWTABLES;【例4.15】删除数据表baseinfo并查看运行结果。运行结果：数据表baseinfo已经被删除。任务4.3使用图形化工具创建与管理数据表

4.3.1创建数据表

4.3.2修改数据表4.3.3查看数据表4.3.4删除数据表4.3.1创建数据表

知识学习1.在MySQLWorkbench中双击选中当前数据库，选择“建表命令按钮”或者在Tables菜单上右击，在弹出的快捷菜单中选择“CreateTable…”命令，就可以新建一个数据表。

知识学习2.输入表中各字段并设置字段名、字段类型和约束。

知识学习常用的字段约束设置说明如下：(1)主键约束：PK列的勾选情况，即可操作数据表的主键约束；(2)非空约束：NN列的勾选情况，即可操作数据表的非空约束。(3)唯一约束：UQ列的勾选情况，即可操作数据表的唯一约束；(4)默认值约束：Default/Expression列中编辑字段的默认值；

知识学习3.完成数据表的创建，并显示自动生成的SQL语句，确定并修改无误后，点击确定。

知识学习4.完成数据表的创建。4.3.2修改数据表

知识学习选中需要修改的表，在其右侧选择第二个按钮，或者在需要修改表结构的数据表上右击，在弹出的快捷菜单中选择“AlterTable…”命令，在弹出的界面中可以修改数据表的基本信息和数据表结构，完成相应的修改操作。4.3.3查看数据表

知识学习在需要查看表结构的数据表上右击，在弹出的快捷菜单中选择“TableInspector”命令，或者单击数据表名后的第一个按钮，即可查看数据表的结构。

知识学习Info标签显示了该数据表的表名、存储引擎、列数、表空间大小、创建时间、更新时间、字符集校对规则等信息。Columns标签显示了该表数据列的信息，包括列名、数据类型、默认值、非空标识、字符集、校对规则和使用权限等信息。4.3.4删除数据表

知识学习如果要删除数据表，只需要在待删除的数据表上右击，在弹出的快捷菜单中选择“DropTable…”命令，在弹出的对话框中单击“DropNow