欢迎来到MySQL系列教程的第3天今天我们将学习SQL语句的基础操作包括DDL数据定义语言、DML数据操作语言和DQL数据查询语言。这些将是日后最常用的SQL命令务必熟练掌握。文章目录一、写在前面二、DDL语句数据库和表的操作2.1 数据库操作2.2 表操作CREATE TABLE2.3 表操作ALTER TABLE2.4 删除表三、DML语句数据的增删改3.1 INSERT语句的多种写法3.2 UPDATE的安全写法3.3 DELETE和TRUNCATE的区别四、DQL基础SELECT查询4.1 SELECT * 的坑4.2 别名和去重五、实战创建电商订单系统相关表5.1 完整的电商表结构设计5.2 插入测试数据六、踩坑提醒6.1 UPDATE忘记WHERE的悲剧6.2 DELETE忘记WHERE的悲剧七、面试高频考点考点1DELETE和TRUNCATE的区别考点2DROP和DELETE的区别考点3INSERT的几种写法考点4如何防止UPDATE/DELETE误操作八、总结九、下一步预告十、参考资料互动话题一、写在前面SQL语句分为以下几类分类全称主要语句用途DDLData Definition LanguageCREATE, DROP, ALTER定义数据库结构DMLData Manipulation LanguageINSERT, UPDATE, DELETE操作数据DQLData Query LanguageSELECT查询数据DCLData Control LanguageGRANT, REVOKE权限控制TCLTransaction Control LanguageCOMMIT, ROLLBACK事务控制今天我们重点学习DDL、DML和DQL的基础用法。二、DDL语句数据库和表的操作2.1 数据库操作-- 创建数据库CREATEDATABASEIFNOTEXISTSecommerceDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 使用数据库USEecommerce;-- 查看所有数据库SHOWDATABASES;-- 查看当前数据库SELECTDATABASE();-- 删除数据库危险操作DROPDATABASEIFEXISTSecommerce;-- 修改数据库字符集ALTERDATABASEecommerceCHARACTERSETutf8mb4;经验之谈创建数据库时务必指定utf8mb4字符集支持完整的Unicode包括emoji。2.2 表操作CREATE TABLE-- 创建用户表CREATETABLEIFNOTEXISTSusers(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEYCOMMENT用户ID,usernameVARCHAR(50)NOTNULLUNIQUECOMMENT用户名,emailVARCHAR(100)NOTNULLUNIQUECOMMENT邮箱,phoneCHAR(11)COMMENT手机号,passwordVARCHAR(255)NOTNULLCOMMENT密码加密存储,statusTINYINTUNSIGNEDDEFAULT1COMMENT状态0-禁用 1-启用,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,INDEXidx_email(email),INDEXidx_phone(phone))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT用户表;CREATE TABLE要点每个表建议有id主键常用查询字段加索引必须设置字符集为utf8mb4添加COMMENT注释方便维护2.3 表操作ALTER TABLE-- 添加列ALTERTABLEusersADDCOLUMNnicknameVARCHAR(50)COMMENT昵称AFTERusername;-- 修改列类型ALTERTABLEusersMODIFYCOLUMNphoneVARCHAR(20)COMMENT手机号支持国际号码;-- 修改列名MySQL 8.0ALTERTABLEusersRENAMECOLUMNnicknameTOdisplay_name;-- 删除列ALTERTABLEusersDROPCOLUMNdisplay_name;-- 添加索引ALTERTABLEusersADDINDEXidx_created_at(created_at);-- 删除索引ALTERTABLEusersDROPINDEXidx_created_at;-- 修改表名ALTERTABLEusersRENAMETOsys_users;RENAMETABLEsys_usersTOusers;2.4 删除表-- 删除表会删除数据和结构DROPTABLEIFEXISTStemp_table;-- 清空表数据保留结构TRUNCATETABLEtemp_table;三、DML语句数据的增删改3.1 INSERT语句的多种写法-- 方式1标准插入推荐明确指定列INSERTINTOusers(username,email,phone,password)VALUES(zhangsan,zhangsanexample.com,13800138000,encrypted_pwd);-- 方式2插入多行性能更好INSERTINTOusers(username,email,password)VALUES(lisi,lisiexample.com,pwd1),(wangwu,wangwuexample.com,pwd2),(zhaoliu,zhaoliuexample.com,pwd3);-- 方式3使用SET语法INSERTINTOusersSETusernamesunqi,emailsunqiexample.com,passwordpwd4;-- 方式4从其他表插入数据迁移常用INSERTINTOusers_backup(username,email)SELECTusername,emailFROMusersWHEREstatus0;-- 方式5存在则更新不存在则插入UPSERTINSERTINTOusers(id,username,email)VALUES(1,newname,newexample.com)ONDUPLICATEKEYUPDATEusernameVALUES(username),emailVALUES(email);-- MySQL 8.0.19 新语法INSERTINTOusers(id,username,email)VALUES(1,newname,newexample.com)ASnewONDUPLICATEKEYUPDATEusernamenew.username,emailnew.email;经验之谈生产环境务必指定列名不要依赖列顺序批量插入比单条插入性能高很多使用INSERT IGNORE忽略重复键错误3.2 UPDATE的安全写法-- 危险忘记WHERE会更新所有行UPDATEusersSETstatus0;-- 所有用户都被禁用了-- 安全的UPDATE写法先查询确认-- 步骤1先SELECT确认要更新的数据SELECT*FROMusersWHEREid1;-- 步骤2执行UPDATE必须带WHEREUPDATEusersSETstatus0,updated_atNOW()WHEREid1;-- 使用LIMIT限制更新数量保险措施UPDATEusersSETstatus0WHEREstatus1LIMIT10;-- 多表UPDATEUPDATEusers uJOINorders oONu.ido.user_idSETu.order_countu.order_count1WHEREo.statuscompleted;3.3 DELETE和TRUNCATE的区别-- DELETE删除指定行可以回滚记录日志DELETEFROMusersWHEREid1;-- DELETE忘记WHERE的补救使用LIMITDELETEFROMusersLIMIT1;-- 只删1条-- TRUNCATE清空整个表速度快不可回滚TRUNCATETABLEtemp_logs;特性DELETETRUNCATE删除范围可指定条件全部数据执行速度较慢逐行删除很快删除表重建事务回滚支持不支持触发器会触发不会触发自增计数保留重置日志记录记录每行最小日志四、DQL基础SELECT查询4.1 SELECT * 的坑-- 不推荐SELECT * 会返回所有列SELECT*FROMusers;-- 推荐明确指定需要的列SELECTid,username,email,created_atFROMusers;-- 原因-- 1. 网络传输更多数据-- 2. 可能包含敏感字段password-- 3. 表结构变化会影响应用程序-- 4. 无法利用覆盖索引优化4.2 别名和去重-- 列别名AS可省略SELECTidASuser_id,usernameASname,created_atASregister_timeFROMusers;-- 表别名多表查询常用SELECTu.id,u.usernameFROMusers u;-- 去重DISTINCTSELECTDISTINCTstatusFROMusers;-- 查看有哪些状态值-- 多列去重组合唯一SELECTDISTINCTstatus,genderFROMusers;五、实战创建电商订单系统相关表5.1 完整的电商表结构设计-- 用户表CREATETABLEusers(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(100)NOTNULLUNIQUE,phoneCHAR(11),passwordVARCHAR(255)NOTNULL,statusTINYINTUNSIGNEDDEFAULT1,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_phone(phone))ENGINEInnoDBCOMMENT用户表;-- 商品表CREATETABLEproducts(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(200)NOTNULLCOMMENT商品名称,descriptionTEXTCOMMENT商品描述,priceDECIMAL(10,2)NOTNULLCOMMENT售价,stockINTUNSIGNEDDEFAULT0COMMENT库存,statusTINYINTUNSIGNEDDEFAULT1COMMENT0-下架 1-上架,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,INDEXidx_status_price(status,price))ENGINEInnoDBCOMMENT商品表;-- 订单表CREATETABLEorders(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,order_noVARCHAR(32)NOTNULLUNIQUECOMMENT订单编号,user_idINTUNSIGNEDNOTNULLCOMMENT用户ID,total_amountDECIMAL(10,2)NOTNULLCOMMENT订单总金额,statusTINYINTUNSIGNEDDEFAULT0COMMENT0-待支付 1-已支付 2-已发货 3-已完成 4-已取消,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_user_id(user_id),INDEXidx_order_no(order_no),INDEXidx_created_at(created_at),FOREIGNKEY(user_id)REFERENCESusers(id))ENGINEInnoDBCOMMENT订单表;-- 订单商品表订单明细CREATETABLEorder_items(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,order_idINTUNSIGNEDNOTNULLCOMMENT订单ID,product_idINTUNSIGNEDNOTNULLCOMMENT商品ID,product_nameVARCHAR(200)NOTNULLCOMMENT商品名称快照,priceDECIMAL(10,2)NOTNULLCOMMENT下单时价格,quantityINTUNSIGNEDNOTNULLCOMMENT数量,subtotalDECIMAL(10,2)NOTNULLCOMMENT小计金额,INDEXidx_order_id(order_id),FOREIGNKEY(order_id)REFERENCESorders(id),FOREIGNKEY(product_id)REFERENCESproducts(id))ENGINEInnoDBCOMMENT订单商品表;5.2 插入测试数据-- 插入用户INSERTINTOusers(username,email,phone,password)VALUES(user001,user001test.com,13800138001,pwd001),(user002,user002test.com,13800138002,pwd002),(user003,user003test.com,13800138003,pwd003);-- 插入商品INSERTINTOproducts(name,description,price,stock)VALUES(iPhone 15,苹果最新手机,5999.00,100),(MacBook Pro,苹果笔记本电脑,14999.00,50),(AirPods Pro,无线耳机,1999.00,200);-- 插入订单INSERTINTOorders(order_no,user_id,total_amount,status)VALUES(ORDER202401010001,1,5999.00,1),(ORDER202401010002,1,1999.00,0),(ORDER202401010003,2,14999.00,2);-- 插入订单明细INSERTINTOorder_items(order_id,product_id,product_name,price,quantity,subtotal)VALUES(1,1,iPhone 15,5999.00,1,5999.00),(2,3,AirPods Pro,1999.00,1,1999.00),(3,2,MacBook Pro,14999.00,1,14999.00);六、踩坑提醒6.1 UPDATE忘记WHERE的悲剧真实案例某运营人员执行了以下SQL-- 本意只禁用某个违规用户UPDATEusersSETstatus0;-- 结果所有用户都被禁用了网站瘫痪防范措施执行UPDATE前先用SELECT确认使用SET sql_safe_updates 1;开启安全模式UPDATE必须带WHERE条件-- 开启安全更新模式SETsql_safe_updates1;-- 此时执行无WHERE的UPDATE会报错UPDATEusersSETstatus0;-- ERROR 1175 (HY000): You are using safe update mode...6.2 DELETE忘记WHERE的悲剧-- 本意删除测试数据DELETEFROMorders;-- 结果所有订单都没了防范措施先用SELECT确认要删除的数据使用事务删除前可以回滚重要数据先备份-- 安全的删除流程STARTTRANSACTION;SELECT*FROMordersWHEREstatus4;-- 确认要删除的已取消订单DELETEFROMordersWHEREstatus4;-- 确认无误后提交COMMIT;-- 如果有问题回滚-- ROLLBACK;七、面试高频考点考点1DELETE和TRUNCATE的区别答案DELETE是DML语句TRUNCATE是DDL语句DELETE可以带WHERE条件删除部分数据TRUNCATE只能清空全部DELETE逐行删除记录日志可以回滚TRUNCATE直接删除表重建速度快不可回滚DELETE会触发表上的触发器TRUNCATE不会DELETE保留自增计数TRUNCATE重置自增计数考点2DROP和DELETE的区别答案DROP是DDL语句删除整个表结构和数据DELETE是DML语句只删除数据保留表结构DROP不可回滚DELETE在事务中可以回滚考点3INSERT的几种写法答案INSERT INTO table (cols) VALUES (...)- 标准插入INSERT INTO table VALUES (...)- 省略列名不推荐INSERT INTO table SET col1val1, col2val2- SET语法INSERT INTO table SELECT ...- 从其他表插入INSERT ... ON DUPLICATE KEY UPDATE- 存在更新不存在插入考点4如何防止UPDATE/DELETE误操作答案开启sql_safe_updates安全模式执行前先SELECT确认使用事务先执行后确认再提交重要操作先在测试环境验证定期备份数据八、总结今天我们学习了DDL语句CREATE/DROP/ALTER操作数据库和表结构DML语句INSERT/UPDATE/DELETE操作数据DQL语句SELECT基础查询实战创建了电商系统的核心表核心要点创建表要设置utf8mb4字符集UPDATE/DELETE必须带WHERE条件SELECT不要直接用*指定需要的列重要操作前先备份使用事务保护九、下一步预告Day4MySQL条件查询与排序我们将学习WHERE子句的各种条件写法NULL值的处理ORDER BY排序技巧LIMIT分页实现电商系统的各种查询场景十、参考资料MySQL 8.0官方文档 - SQL语句SQL必知必会第5版MySQL是怎样运行的从根儿上理解MySQL互动话题你在工作中有没有遇到过UPDATE/DELETE忘记WHERE的惊险时刻后来怎么解决的你们公司的数据库表设计规范有哪些欢迎在评论区分享对于电商系统的表设计你有什么优化建议如果觉得有帮助请点赞收藏关注我们Day4见