当前位置：首页 > news >正文

SQL学习下

news 2026/4/21 5:10:04

我现在完全按你的要求：把所有 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_id=oi.product_id-- 左连接订单明细表GROUPBYp.product_id,p.name;-- 按商品分组-- CASE 函数：日期分类SELECTorder_id,-- 订单IDcustomer_id,-- 客户IDorder_date,-- 订单日期CASE-- 开始多条件判断WHENYEAR(order_date)='2019'THEN'今年'-- 2019年→今年WHENYEAR(order_date)='2018'THEN'去年'-- 2018年→去年WHENYEAR(order_date)='2017'THEN'前年'-- 2017年→前年ELSE'其他年份'-- 其余情况ENDASnumber-- 结束CASE，别名numberFROMorders;-- 来自orders表-- CASE 函数：会员等级分类SELECTCONCAT(first_name,' ',last_name)AScustomer,-- 姓名拼接points,-- 积分CASE-- 开始分级WHENpoints>3000THEN'Gold'-- >3000→黄金WHENpointsBETWEEN2000AND3000THEN'Silver'-- 2000~3000→白银WHENpoints<2000THEN'Bronze'-- <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_total>0;-- 只查未付完的-- 通过视图修改数据UPDATEinvoices_with_balance-- 更新视图SETdue_date=DATE_ADD(due_date,INTERVAL2DAY)-- 到期日+2天WHEREinvoice_id=2;-- 条件：invoice_id=2-- 通过视图删除数据DELETEFROMinvoices_with_balance-- 删除WHEREinvoice_id=1;-- 条件：invoice_id=1-- ---------------------------- 四、存储过程 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_id=client_id;-- 按客户ID过滤END$DELIMITER;-- 调用CALLget_invoices_by_client(5);-- 空参数返回全部DELIMITER$CREATEPROCEDUREget_clients_by_state(stateCHAR(2))BEGINSELECT*FROMclients cWHEREc.state=IFNULL(state,c.state);-- 参数为空则返回全部END$DELIMITER;CALLget_clients_by_state(NULL);-- 参数验证：付款存储过程CREATEPROCEDUREmake_payment(invoice_idINT,-- 发票IDpayment_amountDECIMAL(9,2),-- 付款金额payment_dateDate)-- 付款日期BEGINIFpayment_amount<=0THEN-- 判断金额是否合法SIGNAL SQLSTATE'22003'-- 抛出错误SETMESSAGE_TEXT='数据错误';-- 错误提示ENDIF;UPDATEinvoices i-- 更新发票SETi.payment_total=payment_amount,-- 已付金额i.payment_date=payment_date-- 付款日期WHEREi.invoice_id=invoice_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_id=client_idANDi.payment_total=0;-- 未付款END;-- 本地变量示例CREATEPROCEDUREget_risk_factor()BEGINDECLARErisk_factordecimal(9,2)DEFAULT0;-- 声明变量DECLAREinvoices_totalDECIMAL(9,2);DECLAREinvoices_countINT;SELECTcount(*),SUM(invoice_total)-- 统计INTOinvoices_count,invoices_total;-- 存入变量SETrisk_factor=invoices_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_id=client_id;SETrisk_factor=invoices_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_total=payment_total+new.amount-- 新增金额WHEREinvoice_id=new.invoice_id;-- 对应发票END$DELIMITER;-- 测试插入INSERTINTOpaymentsVALUES(DEFAULT,5,3,'2019-01-01',20,1);-- 删除后触发DELIMITER$CREATETRIGGERpayment_before_deleteAFTERDELETEONpaymentsFOR EACH ROWBEGINUPDATEinvoicesSETpayment_total=payment_total-OLD.amount-- 减去已删除金额WHEREinvoice_id=OLD.invoice_id;END$DELIMITER;-- 测试删除DELETEFROMpaymentsWHEREpayment_id=10;-- 触发器管理SHOWTRIGGERS;-- 查看触发器SHOWTRIGGERSLIKE'payments%';-- 模糊查询DROPTRIGGERIFEXISTSpayment_before_delete;-- 删除-- ---------------------------- 七、事件 EVENT（定时任务）-- --------------------------SHOWVARIABLESLIKE'event%';-- 查看事件调度器状态SETGLOBALevent_scheduler=ON;-- 开启事件调度器-- 创建年度清理事件DELIMITER$CREATEEVENT yearly_delete_stale_audit_rowsONSCHEDULE EVERY1YEARSTARTS'2026-04-01'ENDS'2029-04-01'-- 每年执行DOBEGINDELETEFROMpayments_auditWHEREaction_date<NOW()-INTERVAL1YEAR;-- 删除1年前数据END$DELIMITER;-- 事件管理SHOWEVENTSLIKE'yearly%';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; -- 回滚-- 事务隔离级别SHOWVARIABLESLIKE'transaction_isolation';SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;-- ---------------------------- 九、JSON 操作-- ---------------------------- 更新JSON字段UPDATEproductsSETproperties=JSON_OBJECT('weight',20,'dimensions',JSON_ARRAY(4,5,6),'manufacturer',JSON_OBJECT('name','sony'))WHEREproduct_id=2;-- 查询JSONSELECTproduct_id,properties->'$.weight'ASweight,properties->'$.dimensions[0]'ASdimensions,properties->>'$.manufacturer.name'ASmanufacturerFROMproductsWHEREproduct_id=2;-- 修改JSONUPDATEproductsSETproperties=JSON_SET(properties,'$.weight',30)WHEREproduct_id=1;-- 删除JSON字段UPDATEproductsSETproperties=JSON_REMOVE(properties,'$.age')WHEREproduct_id=1;