SQL 数据定义（DDL）全解

SQL 数据定义DDL全解本文完全承接之前的 SQL 学习路径聚焦 SQL 体系的基石 ——数据定义语言DDLData Definition Language。所有 SQL 的增删改查操作都必须建立在「先定义数据结构」的基础上。什么是 SQL 数据定义DDLSQL 数据定义核心是通过 DDL 语句对关系型数据库的结构化对象进行创建、修改、删除、重命名等操作定义数据的存储结构、约束规则、关联关系是整个数据库的「骨架设计」。SQL 分类核心作用操作对象开发高频度DDL数据定义语言定义数据库结构对象库、表、索引、约束、视图等★★★★☆DQL数据查询语言查询数据表中的行和列★★★★★DML数据操纵语言增删改数据表中的行数据★★★★★TCL事务控制语言事务提交 / 回滚事务内的 DML 操作★★★☆☆DCL数据控制语言权限管理用户、角色、权限★☆☆☆☆自动提交不可回滚绝大多数数据库中DDL 语句执行后会自动提交事务无法通过ROLLBACK回滚一旦执行错误只能通过备份恢复生产环境操作必须极度谨慎。锁表风险对大表执行 DDL 操作如新增字段、修改字段类型可能会触发表锁导致业务读写阻塞高并发场景极易引发线上故障。权限要求高DDL 操作需要数据库的高阶权限生产环境通常仅 DBA 拥有开发人员需在测试环境验证后提交 DBA 执行。数据库级别 DDL 操作数据库是表、索引等所有对象的容器库级别操作是 DDL 的第一步核心是对数据库本身进行创建、修改、删除、切换。创建数据库CREATE DATABASECREATEDATABASE[IFNOTEXISTS]数据库名[DEFAULTCHARACTERSET字符集名][DEFAULTCOLLATE排序规则名][COMMENT数据库注释];IF NOT EXISTS避免数据库已存在时报错生产环境必加CHARACTER SET指定数据库默认字符集所有表默认继承该字符集COLLATE指定排序规则影响字符串的比较、排序逻辑。-- 创建电商业务数据库完全符合生产规范CREATEDATABASEIFNOTEXISTSecommerceDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_general_ciCOMMENT电商业务核心数据库;必须使用utf8mb4字符集MySQL 的utf8最多支持 3 字节无法存储 emoji、生僻字utf8mb4是完整的 4 字节 UTF-8 编码生产环境强制使用。排序规则选择通用场景用utf8mb4_general_ci兼容性好性能优异不区分大小写MySQL 8.0 推荐utf8mb4_0900_ai_ci新一代排序规则精度更高性能更优。数据库命名规范全小写字母 下划线分隔禁止驼峰、拼音、特殊字符名称体现业务含义如ecommerce、user_center。修改数据库ALTER DATABASE仅用于修改数据库的全局属性无法修改数据库名常用场景为修改字符集、排序规则。-- 修改数据库的字符集和排序规则ALTERDATABASEecommerceDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;删除数据库DROP DATABASE极度危险操作会删除数据库内所有表、数据、索引等对象且无法回滚生产环境严禁随意执行。-- 安全写法仅当数据库存在时才删除DROPDATABASEIFEXISTSecommerce;查看与切换数据库-- 查看所有数据库SHOWDATABASES;-- 查看当前数据库的创建信息SHOWCREATEDATABASEecommerce;-- 切换到指定数据库后续表操作默认在该库下执行USEecommerce;表级别 DDL 操作开发核心表是关系型数据库中数据存储的核心载体表级别 DDL 是开发人员日常接触最多的操作核心包括表的创建、修改、删除、清空四大类。创建表CREATE TABLECREATETABLE[IFNOTEXISTS]表名(字段1字段类型[字段约束][COMMENT字段注释],字段2字段类型[字段约束][COMMENT字段注释],-- 更多字段定义...[表级约束定义主键、唯一键、外键等])[ENGINE存储引擎][DEFAULTCHARACTERSET字符集][COLLATE排序规则][COMMENT表注释];属性生产推荐配置核心说明存储引擎InnoDBMySQL 默认引擎支持事务、行级锁、外键、崩溃恢复生产环境强制使用禁止使用 MyISAM 等其他引擎字符集utf8mb4继承数据库默认值可单独为表指定保证字符集统一排序规则与库一致保证字符串比较、排序逻辑统一表注释强制必填清晰说明表的业务含义禁止无注释建表整数类型TINYINT占用 1 字节有符号取值范围 - 128 ~ 127无符号 0 ~ 255适用于状态、性别、是否删除、枚举值等场景建议优先使用 UNSIGNED 无符号类型避免出现负数。INT占用 4 字节无符号取值 0~4294967295是常规数值的常用类型多用于主键、用户 ID、订单 ID、数量等场景为绝大多数场景的主键首选类型。BIGINT占用 8 字节无符号取值 0~18446744073709551615适用于超大数量主键、雪花 ID、大数据量表主键需避免滥用INT 可满足的场景无需使用。高精度数值类型DECIMAL(M,D) M 代表总位数D 为小数位数存储无精度丢失专门用于金额、价格、积分等高精度数值场景严禁使用 FLOAT/DOUBLE 存储金额会产生精度丢失问题。字符串类型CHAR(N) 固定长度 N最大支持 255 字符适合存储手机号、MD5 密码、身份证号等固定长度内容固定长度场景下性能优于 VARCHAR。VARCHAR(N) 变长字符串最大 65535 字节用于用户名、邮箱、地址、商品名称等变长内容长度 N 需按需设置不要无意义设置为 VARCHAR (255)。TEXT 长文本类型最大支持 64KB适用于商品详情、备注、富文本等长内容大表场景谨慎使用会影响查询性能且不建议在该字段建立索引。日期时间类型DATETIME取值范围 1000-01-01~9999-12-31精度可达秒 / 毫秒与时区无关是创建时间、更新时间、支付时间等所有业务时间的生产首选。DATE仅存储日期不包含时间适用于生日、统计日期、下单日期等仅需日期的场景占用存储空间更小。TIMESTAMP取值范围 1970-01-01~2038-01-19受时区影响且存在 2038 年问题核心业务时间字段禁止使用实际生产极少应用。注意事项必须有主键每张表必须定义主键优先使用无符号 INT/BIGINT 自增主键禁止无主键建表。必须有核心通用字段create_time创建时间默认当前时间、update_time更新时间自动更新、is_delete逻辑删除标记默认 0。所有字段必须加注释表、字段必须有清晰的业务注释枚举值必须说明含义。优先设置非空约束尽量给字段设置NOT NULL和默认值避免 NULL 值带来的索引失效、查询异常问题。禁止使用外键物理约束互联网高并发场景仅保留逻辑外键如 user_id不创建物理外键避免锁和性能问题。修改表结构ALTER TABLE用于修改已存在表的结构包括新增 / 修改 / 删除字段、约束、表属性等是开发中最常用的表修改操作。生产警告大表执行 ALTER TABLE 前必须评估数据量在业务低峰期执行避免锁表导致业务阻塞。字段相关操作-- 1. 新增字段给users表新增gender字段指定位置在age之后ALTERTABLEusersADDCOLUMNgenderTINYINTNOTNULLDEFAULT0COMMENT性别0-未知1-男2-女AFTERage;-- 2. 修改字段类型/属性修改username字段的长度为60保持非空ALTERTABLEusersMODIFYCOLUMNusernameVARCHAR(60)NOTNULLCOMMENT用户名;-- 3. 修改字段名将user_age字段重命名为ageALTERTABLEusers CHANGECOLUMNuser_age ageTINYINTUNSIGNEDDEFAULT0COMMENT年龄;-- 4. 删除字段删除users表的avatar字段ALTERTABLEusersDROPCOLUMNavatar;约束与索引相关操作-- 1. 新增主键约束极少用建表时已定义ALTERTABLEusersADDPRIMARYKEY(id);-- 2. 新增唯一约束ALTERTABLEusersADDUNIQUEKEYuk_phone(phone);-- 3. 新增普通索引ALTERTABLEusersADDINDEXidx_create_time(create_time);-- 4. 删除索引ALTERTABLEusersDROPINDEXidx_create_time;-- 5. 删除主键约束ALTERTABLEusersDROPPRIMARYKEY;表属性修改-- 修改表的字符集ALTERTABLEusersDEFAULTCHARACTERSETutf8mb4;-- 修改表注释ALTERTABLEusersCOMMENT用户信息主表;-- 表重命名谨慎操作会影响业务代码ALTERTABLEusersRENAMETOuser_info;删除表DROP TABLE极度危险操作删除表的结构、所有数据、索引、约束且无法回滚生产环境严禁随意执行。-- 安全写法仅当表存在时才删除DROPTABLEIFEXISTSusers;-- 批量删除多张表DROPTABLEIFEXISTSorder_goods,orders;清空表TRUNCATE TABLE用于清空表内所有数据保留表的结构、索引、约束本质是 DDL 操作和 DML 的 DELETE 有本质区别。-- 清空orders表所有数据TRUNCATETABLEorders;特性TRUNCATEDDLDELETEDML条件过滤不支持 WHERE只能全表清空支持 WHERE可精准删除指定行自增主键重置为初始值不重置保持原有自增序列事务回滚不支持执行后无法回滚支持在事务内可回滚执行效率大表极快不记录单行日志大表极慢逐行记录操作日志触发器不会触发 DELETE 触发器会触发 DELETE 触发器约束定义数据完整性的核心保障约束是定义在字段 / 表上的规则用于限制数据的格式、范围、关联关系从数据库层面保证数据的准确性、一致性杜绝脏数据写入。列级约束定义在字段后面仅作用于单个字段表级约束定义在所有字段之后可作用于多个字段如联合主键、联合唯一键。主键约束PRIMARY KEY-- 列级定义单字段主键CREATETABLEusers(idINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEYCOMMENT主键);-- 表级定义支持联合主键CREATETABLEuser_role(user_idINTUNSIGNEDNOTNULLCOMMENT用户ID,role_idINTUNSIGNEDNOTNULLCOMMENT角色ID,PRIMARYKEY(user_id,role_id)-- 联合主键用户角色唯一);核心作用唯一标识表中的每一行数据非空、唯一一张表只能有一个主键特性主键字段默认创建聚簇索引查询性能最高非空约束NOT NULLCREATETABLEusers(usernameVARCHAR(50)NOTNULLCOMMENT用户名非空);核心作用限制字段不能为 NULL 值必须填写具体值生产规范所有业务字段尽量设置 NOT NULL 默认值避免 NULL 值导致的索引失效、查询异常唯一约束UNIQUE-- 列级定义CREATETABLEusers(emailVARCHAR(100)UNIQUECOMMENT邮箱唯一);-- 表级定义支持联合唯一键CREATETABLEusers(idINTPRIMARYKEY,phoneCHAR(11),UNIQUEKEYuk_phone(phone)-- 表级定义可自定义约束名);核心作用限制字段的值在表中唯一不能重复允许有 NULL 值多个 NULL 值不算重复特性创建唯一约束时会自动创建同名的唯一索引查询性能极高默认值约束DEFAULT核心作用字段未赋值时自动使用默认值填充常用场景状态字段、逻辑删除标记、创建时间等CREATETABLEusers(genderTINYINTNOTNULLDEFAULT0COMMENT性别默认0-未知,is_deleteTINYINTNOTNULLDEFAULT0COMMENT逻辑删除默认0-未删除,create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间默认当前时间);检查约束CHECKCREATETABLEusers(ageTINYINTUNSIGNEDCHECK(age0ANDage120)COMMENT年龄必须在0-120之间,total_amountDECIMAL(10,2)CHECK(total_amount0)COMMENT金额不能为负数);核心作用自定义字段值的校验规则只有满足条件的数据才能写入注意MySQL 8.0.16 才正式支持 CHECK 约束之前的版本仅语法兼容不生效外键约束FOREIGN KEYCREATETABLEorders(idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,user_idINTUNSIGNEDNOTNULLCOMMENT用户ID外键关联users.id,-- 表级定义外键约束CONSTRAINTfk_orders_user_idFOREIGNKEY(user_id)REFERENCESusers(id)ONDELETERESTRICT);核心作用建立两张表的关联关系保证从表的外键值必须存在于主表的主键中杜绝无主数据级联规则支持ON DELETE RESTRICT/CASCADE/SET NULL等级联操作生产提示互联网高并发场景不推荐使用物理外键改用代码层面保证数据一致性索引定义查询性能的核心基石索引是提升 SQL 查询性能的核心手段本质是通过排序的数据结构避免全表扫描大幅提升查询效率。索引属于数据库对象通过 DDL 语句进行创建、查看、删除。索引的创建语法-- 1. 创建普通索引CREATEINDEXidx_cityONusers(city);-- 2. 创建唯一索引CREATEUNIQUEINDEXuk_emailONusers(email);-- 3. 创建联合索引最左前缀原则高区分度字段放前面CREATEINDEXidx_city_age_genderONusers(city,age,gender);-- 4. 创建前缀索引长字符串字段仅对前N个字符建索引节省空间CREATEINDEXidx_email_prefixONusers(email(20));-- 5. ALTER TABLE方式创建索引ALTERTABLEusersADDINDEXidx_create_time(create_time);索引的查看与删除-- 查看表中所有索引SHOWINDEXFROMusers;-- 删除索引DROPINDEXidx_cityONusers;-- 或ALTER TABLE方式删除ALTERTABLEusersDROPINDEXidx_city;索引定义生产最佳实践优先为 WHERE、ORDER BY、GROUP BY 后的字段建索引SELECT 后的字段无需单独建索引联合索引遵循最左前缀原则高区分度字段放在最左侧单表索引数量控制在 5 个以内小表、低区分度字段无需建索引如 gender、is_delete 等仅 2-3 个值的字段避免冗余索引已有联合索引idx_a_b(a,b)无需再为 a 单独建索引长字符串字段使用前缀索引避免索引占用空间过大。开发常用其他数据库对象定义视图VIEW视图是基于 SQL 查询结果创建的虚拟表本身不存储数据数据来自底层的基础表仅保存查询逻辑。简化复杂查询将多表关联、复杂聚合的查询封装为视图直接查询视图即可权限控制限制用户只能访问视图中的字段屏蔽底层表的敏感字段数据隔离固定查询逻辑避免业务代码重复写复杂 SQL。-- 创建视图用户订单统计视图CREATEVIEWv_user_order_statsASSELECTu.idASuser_id,u.username,COUNT(o.id)AStotal_order_num,SUM(o.total_amount)AStotal_consume_amountFROMusers uLEFTJOINorders oONu.ido.user_idANDo.is_pay1WHEREu.is_delete0GROUPBYu.id,u.username;-- 查询视图和查询普通表语法一致SELECT*FROMv_user_order_statsWHEREuser_id1;-- 修改视图ALTERVIEWv_user_order_statsAS-- 新的查询逻辑...-- 删除视图DROPVIEWIFEXISTSv_user_order_stats;禁止在视图中嵌套多层视图、多表复杂 JOIN会严重影响查询性能禁止通过视图更新底层表数据仅用于查询场景视图命名统一加v_前缀和普通表区分。临时表TEMPORARY TABLE临时表是仅在当前数据库会话中有效的表会话关闭后自动删除不会占用持久化存储空间常用于复杂查询的中间结果存储、批量数据处理。-- 创建临时表存储用户订单临时统计数据CREATETEMPORARYTABLEtemp_user_order(user_idINTUNSIGNEDPRIMARYKEY,total_order_numINTNOTNULLDEFAULT0,total_amountDECIMAL(12,2)NOTNULLDEFAULT0)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 向临时表插入数据INSERTINTOtemp_user_order(user_id,total_order_num,total_amount)SELECTuser_id,COUNT(*),SUM(total_amount)FROMordersWHEREis_pay1GROUPBYuser_id;-- 查询临时表SELECT*FROMtemp_user_order;-- 手动删除临时表会话关闭自动删除也可手动删除DROPTEMPORARYTABLEIFEXISTStemp_user_order;临时表仅当前会话可见不同会话可创建同名临时表互不影响临时表支持索引、约束和普通表用法一致不支持跨库事务、主从同步仅用于当前会话的临时数据处理。