MySQL表的操作_知识文档

MySQL 表操作知识文档一、学习目标掌握 MySQL 中表的完整生命周期操作查看数据库中的表创建普通表和临时表查看表结构添加、修改、重命名和删除字段重命名表删除一个或多个表理解 InnoDB 与 MyISAM 表在磁盘上的文件形式二、核心命令速查操作SQL查看当前数据库中的所有表SHOW TABLES;创建表CREATE TABLE 表名 (...);表不存在时才创建CREATE TABLE IF NOT EXISTS 表名 (...);创建临时表CREATE TEMPORARY TABLE 表名 (...);查看表结构DESC 表名;添加字段ALTER TABLE 表名 ADD 字段定义;修改字段定义ALTER TABLE 表名 MODIFY 字段定义;重命名字段ALTER TABLE 表名 RENAME COLUMN 旧字段 TO 新字段;删除字段ALTER TABLE 表名 DROP COLUMN 字段名;重命名表ALTER TABLE 旧表名 RENAME TO 新表名;删除表DROP TABLE 表名;表存在时才删除DROP TABLE IF EXISTS 表名;三、查看所有表SHOWTABLES;该命令只显示当前已选数据库中的表。若尚未选择数据库应先执行USE数据库名;四、创建表4.1 基本语法CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名(字段名 数据类型[约束][COMMENT字段说明],字段名 数据类型[约束][COMMENT字段说明])[ENGINE存储引擎][DEFAULTCHARACTERSET字符集][COLLATE排序规则];4.2 关键选项选项作用TEMPORARY创建临时表通常仅在当前会话中有效IF NOT EXISTS表已存在时不报错通常会产生警告COMMENT为字段添加说明ENGINE指定存储引擎CHARACTER SET指定字符集COLLATE指定排序规则4.3 创建用户表CREATETABLEusers(idBIGINT,nameVARCHAR(20)COMMENT用户名,passwordCHAR(32)COMMENT32位MD5密码值,birthdayDATECOMMENT生日)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ci;字段设计说明BIGINT适合保存范围较大的整数编号。VARCHAR(20)保存长度可变的用户名。CHAR(32)适合保存固定长度为 32 的字符串。DATE只保存年月日不保存具体时间。4.4 指定 MyISAM 存储引擎CREATETABLEt_myisam(idBIGINT,nameVARCHAR(20)COMMENT用户名)ENGINEMyISAM;4.5 防止重复创建CREATETABLEIFNOTEXISTSusers(idINT,nameVARCHAR(20));查看产生的警告SHOWWARNINGS;五、存储引擎与磁盘文件5.1 InnoDB创建 InnoDB 表后通常会生成表名.ibd.ibd文件用于保存该表的数据和索引信息。5.2 MyISAMMyISAM 表通常对应多个文件文件作用表名.MYD数据文件表名.MYI索引文件表名.sdi表结构描述文件通常为 JSON 格式MySQL 8.0 以前表结构信息常保存在.frm文件中。六、查看表结构DESCusers;DESC输出字段说明列含义Field字段名Type字段的数据类型和长度Null是否允许保存NULLKey索引类型Default默认值Extra自动增长等扩展信息也可以写成DESCRIBEusers;七、修改表结构7.1 基本语法ALTERTABLE表名 修改选项;常用修改选项ADD[COLUMN]字段名 字段定义[FIRST|AFTER已有字段名]MODIFY[COLUMN]字段名 新字段定义[FIRST|AFTER已有字段名]DROP[COLUMN]字段名RENAMECOLUMN旧字段名TO新字段名RENAMETO新表名7.2 添加字段在birthday字段后添加头像地址ALTERTABLEusersADDCOLUMNassetsVARCHAR(100)COMMENT头像地址AFTERbirthday;字段位置控制-- 添加到第一列ALTERTABLEusersADDCOLUMNcodeVARCHAR(20)FIRST;-- 添加到指定字段之后ALTERTABLEusersADDCOLUMNassetsVARCHAR(100)AFTERbirthday;不写FIRST或AFTER时新字段默认添加到表的末尾。7.3 修改字段类型或长度将assets的最大长度由 100 改为 255ALTERTABLEusersMODIFYCOLUMNassetsVARCHAR(255);使用MODIFY时应完整写出新的字段定义。原有的注释、默认值或约束不会自动补充遗漏后可能发生改变。例如保留字段注释ALTERTABLEusersMODIFYCOLUMNassetsVARCHAR(255)COMMENT头像地址;7.4 重命名字段ALTERTABLEusersRENAMECOLUMNassetsTOavatar;此操作只修改字段名不主动修改其数据类型和已有数据。7.5 删除字段ALTERTABLEusersDROPCOLUMNavatar;也可以省略COLUMNALTERTABLEusersDROPavatar;删除字段会同时删除该字段中的全部数据执行前应确认数据是否仍有保留价值。7.6 重命名表方式一ALTERTABLEusersRENAMETOemployee;方式二RENAMETABLEusersTOemployee;重命名后表结构和表中数据仍然保留但原表名不再可用。八、删除表8.1 基本语法DROP[TEMPORARY]TABLE[IFEXISTS]表名[,表名...];8.2 删除单个表DROPTABLEemployee;8.3 删除多个表DROPTABLEemployee,t_myisam;8.4 安全删除DROPTABLEIFEXISTSemployee;IF EXISTS可以避免目标表不存在时直接报错。8.5 注意事项删除表会删除表结构和表中的全部数据。表对应的磁盘数据文件也会被删除。DROP TABLE属于高风险操作执行前应核对数据库名和表名。重要数据应先备份。不要把“删除字段”和“删除表”混淆ALTER TABLE ... DROP COLUMN ...删除字段。DROP TABLE ...删除整张表。九、常见易错点9.1 忘记选择数据库USE数据库名;否则可能在错误的数据库中创建或删除表。9.2 字符串长度设计不合理nameVARCHAR(20)括号中的数字表示最大字符长度。长度过小会造成数据无法完整保存长度过大则可能增加设计和索引成本。9.3CHAR与VARCHAR使用混淆CHAR(n)固定长度适合长度稳定的数据。VARCHAR(n)可变长度适合长度不固定的数据。例如passwordCHAR(32);nameVARCHAR(20);9.4 修改字段时遗漏原属性下面的语句可能使原有注释、默认值或约束发生变化ALTERTABLEusersMODIFYassetsVARCHAR(255);更稳妥的方式是写出完整字段定义ALTERTABLEusersMODIFYassetsVARCHAR(255)COMMENT头像地址;9.5 误用删除命令ALTERTABLEusersDROPCOLUMNavatar;-- 删除一个字段DROPTABLEusers;-- 删除整张表执行删除类语句前应先运行SHOWTABLES;DESC表名;确认目标对象。十、练习商店数据库10.1 创建并选择数据库CREATEDATABASEIFNOTEXISTSstoreDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ci;USEstore;10.2 创建商品表CREATETABLEIFNOTEXISTSgoods(idBIGINTCOMMENT商品编号,nameVARCHAR(50)COMMENT商品名,unitpriceDECIMAL(12,2)COMMENT零售单价,costpriceDECIMAL(12,2)COMMENT成本价,categoryVARCHAR(20)COMMENT商品类型,providerVARCHAR(50)COMMENT供应商)ENGINEInnoDBDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ci;10.3 创建客户表CREATETABLEIFNOTEXISTScustomer(idBIGINTCOMMENT客户编号,nameVARCHAR(20)COMMENT客户姓名,genderBOOLCOMMENT性别,phone_numVARCHAR(20)COMMENT电话号码,emailVARCHAR(50)COMMENT电子邮件,addressVARCHAR(255)COMMENT地址,create_timeDATETIMECOMMENT注册时间)ENGINEInnoDBDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ci;10.4 创建购买记录表CREATETABLEIFNOTEXISTSpurchase(order_idVARCHAR(32)COMMENT订单编号,customer_idBIGINTCOMMENT客户编号,goods_idBIGINTCOMMENT商品编号,numsINTCOMMENT购买商品数量,amountDECIMAL(12,2)COMMENT总金额,create_timeDATETIMECOMMENT订单生成时间)ENGINEInnoDBDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ci;10.5 检查创建结果SHOWTABLES;DESCgoods;DESCcustomer;DESCpurchase;十一、操作流程模板创建表后的检查流程CREATETABLEIFNOTEXISTS表名(字段定义);SHOWTABLES;DESC表名;SHOWWARNINGS;修改表后的检查流程ALTERTABLE表名 修改选项;DESC表名;删除表前的确认流程SHOWTABLES;DESC表名;DROPTABLEIFEXISTS表名;SHOWTABLES;十二、重点记忆查看表SHOW TABLES 创建表CREATE TABLE 查看结构DESC 修改表ALTER TABLE 删除表DROP TABLEADD 添加字段 MODIFY 修改字段定义 RENAME COLUMN 重命名字段 DROP COLUMN 删除字段 RENAME TO 重命名表删除操作不可只看 SQL 是否能执行还必须确认目标数据库、目标表和目标字段是否正确。