SQL学习下

我现在完全按你的要求把所有 SQL 代码直接在行内加注释一行代码 一行注释不分开、不省略直接复制就能用、就能看懂。MySQL 学习笔记代码行内逐行注释版-- ---------------------------- 一、空值处理函数-- ---------------------------- IFNULL 函数如果字段为NULL替换为指定值SELECTorder_id,-- 查询订单编号IFNULL(shipper_id,未分配)ASshipper-- shipper_id为空则显示“未分配”FROMorders;-- 来自orders表-- COALESCE 函数返回第一个非NULL的值SELECTorder_id,-- 查询订单编号COALESCE(shipper_id,comments,未分配)ASshipper-- 依次判断取第一个非空FROMorders;-- 来自orders表-- 拼接字符串 空值替换SELECTCONCAT(first_name, ,last_name)AScustomer,-- 拼接姓和名IFNULL(phone,Unknown)-- 电话为空显示UnknownFROMsql_store.customers;-- 来自sql_store库的customers表-- ---------------------------- 二、条件判断函数-- ---------------------------- IF 函数简单二分支SELECTp.product_id,-- 查询商品IDp.name,-- 查询商品名称count(*)ASorders,-- 统计订单数量IF(count(*)1,Once,Many times)ASnumber-- 数量1显示Once否则Many timesFROMproducts p-- 商品表别名pLEFTJOINorder_items oiONp.product_idoi.product_id-- 左连接订单明细表GROUPBYp.product_id,p.name;-- 按商品分组-- CASE 函数日期分类SELECTorder_id,-- 订单IDcustomer_id,-- 客户IDorder_date,-- 订单日期CASE-- 开始多条件判断WHENYEAR(order_date)2019THEN今年-- 2019年→今年WHENYEAR(order_date)2018THEN去年-- 2018年→去年WHENYEAR(order_date)2017THEN前年-- 2017年→前年ELSE其他年份-- 其余情况ENDASnumber-- 结束CASE别名numberFROMorders;-- 来自orders表-- CASE 函数会员等级分类SELECTCONCAT(first_name, ,last_name)AScustomer,-- 姓名拼接points,-- 积分CASE-- 开始分级WHENpoints3000THENGold-- 3000→黄金WHENpointsBETWEEN2000AND3000THENSilver-- 2000~3000→白银WHENpoints2000THENBronze-- 2000→青铜ELSE没有了-- 其他ENDAScategory-- 别名categoryFROMcustomers-- 来自customers表ORDERBYpointsDESC;-- 按积分降序-- ---------------------------- 三、视图 VIEW-- ---------------------------- 创建视图客户余额视图CREATEVIEWsales_by_balanceAS-- 创建视图SELECTi.client_id,-- 客户IDc.name,-- 客户姓名SUM(invoice_total-payment_total)ASbalance-- 计算未付余额FROMinvoices i-- 发票表别名iJOINclients cUSING(client_id)-- 连接客户表GROUPBYi.client_id,c.name;-- 按客户分组-- 创建/替换视图CREATEORREPLACEVIEWinvoices_with_balanceAS-- 有则覆盖无则新建SELECTinvoice_id,-- 发票IDnumber,-- 发票编号client_id,-- 客户IDinvoice_total,-- 发票总金额payment_total,-- 已付金额(invoice_total-payment_total)ASbalance,-- 未付余额invoice_date,-- 开票日期due_date,-- 到期日payment_date-- 付款日期FROMinvoices-- 来自发票表WHEREinvoice_total-payment_total0;-- 只查未付完的-- 通过视图修改数据UPDATEinvoices_with_balance-- 更新视图SETdue_dateDATE_ADD(due_date,INTERVAL2DAY)-- 到期日2天WHEREinvoice_id2;-- 条件invoice_id2-- 通过视图删除数据DELETEFROMinvoices_with_balance-- 删除WHEREinvoice_id1;-- 条件invoice_id1-- ---------------------------- 四、存储过程 PROCEDURE-- ---------------------------- 无参存储过程DELIMITER$-- 临时修改结束符为$CREATEPROCEDUREget_clients()-- 创建存储过程BEGIN-- 开始代码块SELECT*FROMclients;-- 查询所有客户END$-- 结束存储过程DELIMITER;-- 恢复结束符为;-- 调用存储过程CALLsql_invoicing.get_clients();-- 带参数存储过程DELIMITER$-- 修改结束符CREATEPROCEDUREget_invoices_by_client(client_idINT)-- 入参client_idBEGINSELECT*FROMinvoices i-- 查询发票WHEREi.client_idclient_id;-- 按客户ID过滤END$DELIMITER;-- 调用CALLget_invoices_by_client(5);-- 空参数返回全部DELIMITER$CREATEPROCEDUREget_clients_by_state(stateCHAR(2))BEGINSELECT*FROMclients cWHEREc.stateIFNULL(state,c.state);-- 参数为空则返回全部END$DELIMITER;CALLget_clients_by_state(NULL);-- 参数验证付款存储过程CREATEPROCEDUREmake_payment(invoice_idINT,-- 发票IDpayment_amountDECIMAL(9,2),-- 付款金额payment_dateDate)-- 付款日期BEGINIFpayment_amount0THEN-- 判断金额是否合法SIGNAL SQLSTATE22003-- 抛出错误SETMESSAGE_TEXT数据错误;-- 错误提示ENDIF;UPDATEinvoices i-- 更新发票SETi.payment_totalpayment_amount,-- 已付金额i.payment_datepayment_date-- 付款日期WHEREi.invoice_idinvoice_id;-- 条件END;-- 带输出参数CREATEPROCEDUREget_unpaid_invoices_for_client(client_idINT,-- 入参客户IDOUTinvoices_countINT,-- 出参发票数量OUTpayment_totalDECIMAL(9,2))-- 出参总金额BEGINSELECTCOUNT(*),SUM(invoice_total)-- 统计数量与总金额INTOinvoices_count,payment_total-- 存入输出变量FROMinvoices iWHEREi.client_idclient_idANDi.payment_total0;-- 未付款END;-- 本地变量示例CREATEPROCEDUREget_risk_factor()BEGINDECLARErisk_factordecimal(9,2)DEFAULT0;-- 声明变量DECLAREinvoices_totalDECIMAL(9,2);DECLAREinvoices_countINT;SELECTcount(*),SUM(invoice_total)-- 统计INTOinvoices_count,invoices_total;-- 存入变量SETrisk_factorinvoices_total/invoices_count*5;-- 计算SELECTrisk_factor;-- 返回结果END;-- ---------------------------- 五、自定义函数 FUNCTION-- --------------------------CREATEFUNCTIONget_risk_factor_for_client(client_idINT)RETURNSint-- 返回int类型READSSQLDATA-- 只读数据BEGINDECLARErisk_factordecimal(9,2)DEFAULT0;DECLAREinvoices_totalDECIMAL(9,2);DECLAREinvoices_countINT;SELECTcount(*),SUM(invoice_total)INTOinvoices_count,invoices_totalFROMinvoices iWHEREi.client_idclient_id;SETrisk_factorinvoices_total/invoices_count*5;RETURNIFNULL(risk_factor,0);-- 返回结果END;-- 调用函数SELECTclient_id,name,get_risk_factor_for_client(client_id)ASrisk_factorFROMclients;-- 删除函数DROPFUNCTIONIFEXISTSget_risk_factor_for_client;-- ---------------------------- 六、触发器 TRIGGER-- ---------------------------- 插入后触发DELIMITER$CREATETRIGGERpayment_after_insert-- 触发器名AFTERINSERTONpayments-- 插入payments表后触发FOR EACH ROW-- 每行触发BEGINUPDATEinvoices-- 更新发票SETpayment_totalpayment_totalnew.amount-- 新增金额WHEREinvoice_idnew.invoice_id;-- 对应发票END$DELIMITER;-- 测试插入INSERTINTOpaymentsVALUES(DEFAULT,5,3,2019-01-01,20,1);-- 删除后触发DELIMITER$CREATETRIGGERpayment_before_deleteAFTERDELETEONpaymentsFOR EACH ROWBEGINUPDATEinvoicesSETpayment_totalpayment_total-OLD.amount-- 减去已删除金额WHEREinvoice_idOLD.invoice_id;END$DELIMITER;-- 测试删除DELETEFROMpaymentsWHEREpayment_id10;-- 触发器管理SHOWTRIGGERS;-- 查看触发器SHOWTRIGGERSLIKEpayments%;-- 模糊查询DROPTRIGGERIFEXISTSpayment_before_delete;-- 删除-- ---------------------------- 七、事件 EVENT定时任务-- --------------------------SHOWVARIABLESLIKEevent%;-- 查看事件调度器状态SETGLOBALevent_schedulerON;-- 开启事件调度器-- 创建年度清理事件DELIMITER$CREATEEVENT yearly_delete_stale_audit_rowsONSCHEDULE EVERY1YEARSTARTS2026-04-01ENDS2029-04-01-- 每年执行DOBEGINDELETEFROMpayments_auditWHEREaction_dateNOW()-INTERVAL1YEAR;-- 删除1年前数据END$DELIMITER;-- 事件管理SHOWEVENTSLIKEyearly%;DROPEVENTIFEXISTSyearly_delete_stale_audit_rows;ALTEREVENT yearly_delete_stale_audit_rowsDISABLE;-- 禁用ALTEREVENT yearly_delete_stale_audit_rowsENABLE;-- 启用-- ---------------------------- 八、事务 TRANSACTION-- --------------------------USEsql_store;STARTTRANSACTION;-- 开启事务INSERTINTOorders(customer_id,order_date,status)-- 新增订单VALUES(1,2019-01-01,1);INSERTINTOorder_items-- 新增订单明细VALUES(LAST_INSERT_ID(),1,1,1);COMMIT;-- 提交-- ROLLBACK; -- 回滚-- 事务隔离级别SHOWVARIABLESLIKEtransaction_isolation;SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- ---------------------------- 九、JSON 操作-- ---------------------------- 更新JSON字段UPDATEproductsSETpropertiesJSON_OBJECT(weight,20,dimensions,JSON_ARRAY(4,5,6),manufacturer,JSON_OBJECT(name,sony))WHEREproduct_id2;-- 查询JSONSELECTproduct_id,properties-$.weightASweight,properties-$.dimensions[0]ASdimensions,properties-$.manufacturer.nameASmanufacturerFROMproductsWHEREproduct_id2;-- 修改JSONUPDATEproductsSETpropertiesJSON_SET(properties,$.weight,30)WHEREproduct_id1;-- 删除JSON字段UPDATEproductsSETpropertiesJSON_REMOVE(properties,$.age)WHEREproduct_id1;