02-SQL语法精讲：DDL/DML/DQL/DCL 企业级常用语句全攻略

SQL语法精讲DDL/DML/DQL/DCL 企业级常用语句全攻略作者黒漂技术佬适用读者刚接触SQL、想系统掌握的同学关联场景无人售货柜、智慧农业监控系统一、SQL四大分类先搞清楚你在写哪类语句SQL语句按功能分四大类很多新手混在一起叫不清这里一张表说清楚分类全称作用常见语句DDLData Definition Language定义结构建表、改表结构CREATE / ALTER / DROPDMLData Manipulation Language操作数据增删改INSERT / UPDATE / DELETEDQLData Query Language查询数据SELECTDCLData Control Language控制权限GRANT / REVOKE记忆技巧DDL管房子结构DML管搬家数据DQL管找东西查询DCL管给钥匙权限。二、DDL数据定义语言2.1 库的操作-- 创建数据库指定字符集中文必选utf8mb4CREATEDATABASEIFNOTEXISTSvending_machineDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_general_ci;-- 查看所有数据库SHOWDATABASES;-- 使用数据库USEvending_machine;-- 删除数据库谨慎DROPDATABASEIFEXISTSvending_machine;为什么用utf8mb4而不是utf8MySQL的utf8最多只支持3字节存不了emoji表情和部分生僻字。utf8mb4才是真正的UTF-8。无人售货柜商品名里如果有特殊字符用utf8可能存进去就乱码了。2.2 表的操作创建一张无人售货柜的订单表CREATETABLEorders(order_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT订单ID,cabinet_idINTNOTNULLCOMMENT售货柜ID,product_idINTNOTNULLCOMMENT商品ID,quantityINTNOTNULLDEFAULT1COMMENT购买数量,unit_priceDECIMAL(8,2)NOTNULLCOMMENT单价,total_amountDECIMAL(10,2)NOTNULLCOMMENT订单总金额,pay_statusTINYINTNOTNULLDEFAULT0COMMENT0未支付 1已支付 2已退款,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间,paid_atDATETIMENULLCOMMENT支付时间,INDEXidx_cabinet(cabinet_id),INDEXidx_product(product_id),INDEXidx_created(created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT无人售货柜订单表;修改表结构-- 新增列ALTERTABLEordersADDCOLUMNrefund_reasonVARCHAR(200)NULLAFTERpay_status;-- 修改列类型ALTERTABLEordersMODIFYCOLUMNrefund_reasonVARCHAR(500)NULL;-- 修改列名ALTERTABLEorders CHANGE refund_reason refund_descVARCHAR(500);-- 删除列ALTERTABLEordersDROPCOLUMNrefund_desc;-- 重命名表ALTERTABLEordersRENAMETOorder_info;-- 删除表危险操作数据全没DROPTABLEIFEXISTSorder_info;企业开发中ALTER TABLE要谨慎——在大表上加索引或改列类型可能导致长时间锁表。生产环境一般用工具如pt-online-schema-change来在线改表。三、DML数据操作语言3.1 INSERT 插入数据-- 插入单条INSERTINTOproduct(product_name,price,category)VALUES(可口可乐,3.50,饮料);-- 插入多条批量插入效率远高于循环单条插入INSERTINTOproduct(product_name,price,category)VALUES(可口可乐,3.50,饮料),(乐事薯片,7.90,零食),(红牛,6.00,饮料),(农夫山泉,2.00,饮料);-- 插入时如果主键冲突则更新UPSERTINSERTINTOproduct(product_id,product_name,price,category)VALUES(1001,可口可乐,3.80,饮料)ONDUPLICATEKEYUPDATEpriceVALUES(price);批量插入比循环单条插入快得多因为每次INSERT都有网络往返和事务开销。1000条数据批量插入可能0.1秒循环单条插入可能要10秒。3.2 UPDATE 更新数据-- 基础更新UPDATEproductSETprice3.80WHEREproduct_id1001;-- 多字段更新UPDATEproductSETprice3.80,category碳酸饮料WHEREproduct_id1001;-- 条件更新所有饮料涨价5%UPDATEproductSETpriceprice*1.05WHEREcategory饮料;永远不要忘了WHERE不带WHERE的UPDATE会更新整张表UPDATE product SET price0;会让所有商品免费——这是新手最容易犯的灾难级错误。建议先SELECT确认范围再UPDATE。3.3 DELETE 删除数据-- 删除指定记录DELETEFROMordersWHEREorder_id9999;-- 条件删除清理30天前的未支付订单DELETEFROMordersWHEREpay_status0ANDcreated_atDATE_SUB(NOW(),INTERVAL30DAY);DELETE vs TRUNCATEDELETE可以带WHERE逐行删除记录日志可回滚TRUNCATE清空整张表不可回滚但速度极快会重置自增ID生产环境清理大量数据时DELETE大批量数据可能锁表过久常配合LIMIT分批删除四、DQL数据查询语言SELECT是SQL中使用频率最高的语句也是花样最多的。4.1 基础查询-- 查询所有字段SELECT*FROMproduct;-- 查询指定字段企业规范禁止SELECT *只查需要的列SELECTproduct_name,priceFROMproduct;-- 去重查询SELECTDISTINCTcategoryFROMproduct;企业规范中禁止SELECT *的原因一是浪费带宽有些字段是TEXT类型很大二是*的列顺序依赖表结构加列后可能导致ORM映射出错。4.2 条件查询 WHERE-- 多条件SELECT*FROMproductWHEREcategory饮料ANDprice5.00;-- IN查询SELECT*FROMproductWHEREcategoryIN(饮料,零食);-- 范围查询SELECT*FROMordersWHEREcreated_atBETWEEN2024-01-01AND2024-01-31;-- 模糊查询SELECT*FROMproductWHEREproduct_nameLIKE%可乐%;4.3 聚合与分组-- 聚合函数SELECTCOUNT(*)AStotal_orders,SUM(total_amount)AStotal_revenue,AVG(total_amount)ASavg_order_amount,MAX(total_amount)ASmax_order,MIN(total_amount)ASmin_orderFROMordersWHEREpay_status1;-- 分组统计每个售货柜的销售额SELECTcabinet_id,COUNT(*)ASorder_count,SUM(total_amount)ASrevenueFROMordersWHEREpay_status1GROUPBYcabinet_id;-- HAVING过滤分组结果WHERE过滤行HAVING过滤组SELECTcabinet_id,COUNT(*)ASorder_countFROMordersWHEREpay_status1GROUPBYcabinet_idHAVINGCOUNT(*)100;-- 只看订单数超过100的柜子WHERE vs HAVINGWHERE在分组前过滤行HAVING在分组后过滤组。记住顺序WHERE → GROUP BY → HAVING。4.4 排序与分页-- 排序SELECT*FROMordersORDERBYcreated_atDESC;-- 降序最新订单在前-- 分页LIMIT偏移量, 数量SELECT*FROMordersORDERBYcreated_atDESCLIMIT0,20;-- 第1页每页20条-- 第2页SELECT*FROMordersORDERBYcreated_atDESCLIMIT20,20;4.5 连接查询 JOINJOIN是关系型数据库的核心能力——把多张表的数据关联起来。-- 查询订单关联的商品信息SELECTo.order_id,o.created_at,p.product_name,p.price,o.quantity,o.total_amountFROMorders oINNERJOINproduct pONo.product_idp.product_idWHEREo.pay_status1ORDERBYo.created_atDESCLIMIT20;-- LEFT JOIN即使没有匹配也保留左表记录SELECTc.cabinet_id,c.location,COUNT(o.order_id)ASorder_countFROMcabinet cLEFTJOINorders oONc.cabinet_ido.cabinet_idGROUPBYc.cabinet_id,c.location;JOIN类型INNER JOIN取交集LEFT JOIN保留左表全部RIGHT JOIN保留右表全部。实际开发中LEFT JOIN用得最多。4.6 子查询-- 查询比平均价格高的商品SELECT*FROMproductWHEREprice(SELECTAVG(price)FROMproduct);-- EXISTS查询有订单的商品SELECT*FROMproduct pWHEREEXISTS(SELECT1FROMorders oWHEREo.product_idp.product_id);4.7 SQL执行顺序重要理解SQL执行顺序对写复杂查询至关重要FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT注意SELECT在GROUP BY之后才执行所以WHERE里不能用SELECT起的别名。这就是为什么下面这条SQL报错-- 错误WHERE中不能使用SELECT的别名SELECTproduct_name,price*1.05ASnew_priceFROMproductWHEREnew_price5;-- 报错Unknown column new_price-- 正确写法SELECTproduct_name,price*1.05ASnew_priceFROMproductWHEREprice*1.055;五、DCL数据控制语言5.1 创建用户-- 创建用户并指定密码CREATEUSERreport_user%IDENTIFIEDBYSecurePass123!;-- %表示可以从任意IP连接192.168.1.%限制网段更安全CREATEUSERops_user192.168.1.%IDENTIFIEDBYOpsPass456!;5.2 授权 GRANT-- 授予查询权限只读账号给报表系统GRANTSELECTONvending_machine.*TOreport_user%;-- 授予增删改查权限给应用后端账号GRANTSELECT,INSERT,UPDATE,DELETEONvending_machine.*TOops_user192.168.1.%;-- 授予所有权限仅限DBA使用GRANTALLPRIVILEGESON*.*TOadminlocalhost;权限最小化原则给应用账号只授予SELECT/INSERT/UPDATE/DELETE不给DROP/ALTER权限。这样即使应用被注入攻击攻击者也删不了表结构。5.3 撤销权限 REVOKE-- 撤销删除权限REVOKEDELETEONvending_machine.*FROMops_user192.168.1.%;-- 查看用户权限SHOWGRANTSFORops_user192.168.1.%;5.4 刷新与删除用户-- 权限修改后刷新FLUSHPRIVILEGES;-- 删除用户DROPUSERreport_user%;六、企业级SQL编写规范关键词大写SELECT而不是select代码可读性更好禁止SELECT *只查需要的列减少网络传输和内存占用表名用复数或下划线风格orders、cabinet_info全小写下划线必须有注释建表语句的COMMENT字段、复杂SQL的行内注释金额用DECIMAL绝不用FLOAT/DOUBLE存金额INSERT写明字段列表不用INSERT INTO t VALUES (...)省略字段名UPDATE/DELETE必带WHERE最好先SELECT验证范围大结果集必分页LIMIT防止一次性返回过多数据避免SELECT子查询替代JOINJOIN通常比子查询效率更高线上禁止DROP/TRUNCATE删表操作必须走DBA审批流程总结分类核心语句记忆口诀DDLCREATE/ALTER/DROP盖房子、改房子、拆房子DMLINSERT/UPDATE/DELETE搬进、换掉、搬走DQLSELECT找东西DCLGRANT/REVOKE发钥匙、收钥匙四类SQL是数据库操作的基础中的基础。写SQL不难写出规范、高效的SQL需要长期积累。后面几篇会深入索引原理和查询优化帮你从能写SQL进化到写好SQL。