学习sql前需要先建表建表先建库以下是三张表用户表、订单表、商品表CREATETABLEusers(user_idbigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT用户主键,usernamevarchar(50)NOTNULLCOMMENT用户名,emailvarchar(100)NOTNULLCOMMENT邮箱,phonevarchar(20)DEFAULTNULLCOMMENT手机号,statusenum(ACTIVE,INACTIVE)NOTNULLDEFAULTACTIVECOMMENT用户状态,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT注册时间,updated_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(user_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT用户表;CREATETABLEproducts(product_idbigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT商品主键,product_namevarchar(100)NOTNULLCOMMENT商品名称,categoryvarchar(50)NOTNULLCOMMENT商品分类,pricebigint(20)NOTNULLCOMMENT当前标价 单位 分,stockint(10)unsignedNOTNULLDEFAULT0COMMENT库存数量,statusint(11)NOTNULLDEFAULT0COMMENT0-下架 3-上架,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,updated_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(product_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT商品表;CREATETABLEorders(order_idbigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT订单主键,order_novarchar(32)NOTNULLCOMMENT业务订单号,user_idbigint(20)unsignedNOTNULLCOMMENT下单用户,product_idbigint(20)unsignedNOTNULLCOMMENT所购商品,quantityint(10)unsignedNOTNULLCOMMENT购买数量,unit_pricebigint(20)NOTNULLCOMMENT下单时成交单价,discount_amountbigint(20)NOTNULLDEFAULT0COMMENT整单优惠金额,total_amountbigint(20)NOTNULLDEFAULT0COMMENT订单实付金额,statusint(11)NOTNULLDEFAULT0COMMENT订单状态 0-待支付 1-已支付 2-已收货 3-取消,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间,updated_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(order_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT订单表;在query下运行sql即可创建三张表然后进行数据初始化在query中运行以下sqlINSERTINTOusers(user_id,username,email,phone,status,created_at,updated_at)VALUES(1,婷A,aliceexample.com,13800000001,ACTIVE,2026-09-01 09:00:00,2026-09-01 09:00:00),(2,婷B,bobexample.com,13800000002,ACTIVE,2026-09-03 10:30:00,2026-09-03 10:30:00),(3,婷C,carolexample.com,13800000003,INACTIVE,2026-09-05 14:20:00,2026-09-05 14:20:00),(4,婷D,davidexample.com,13800000004,ACTIVE,2026-09-08 18:10:00,2026-09-08 18:10:00),(5,婷E,eveexample.com,NULL,ACTIVE,2026-09-10 08:40:00,2026-09-10 08:40:00),(6,婷F,frankexample.com,13800000006,ACTIVE,2026-10-01 13:00:00,2026-10-01 13:00:00);INSERTINTOproducts(product_id,product_name,category,price,stock,status,created_at,updated_at)VALUES(1,轻薄笔记本电脑,数码,599900,20,3,2026-09-01 09:00:00,2026-09-01 09:00:00),(2,机械键盘,数码,39900,100,3,2026-09-01 09:10:00,2026-09-01 09:10:00),(3,陶瓷咖啡杯,家居,5990,50,3,2026-09-01 09:20:00,2026-09-01 09:20:00),(4,27英寸显示器,数码,129900,35,3,2026-09-01 09:30:00,2026-09-01 09:30:00),(5,硬壳笔记本,办公,1990,200,3,2026-09-01 09:40:00,2026-09-01 09:40:00),(6,无线鼠标,数码,12900,0,0,2026-09-01 09:50:00,2026-09-01 09:50:00),(7,电动升降桌,办公,89900,12,3,2026-09-02 10:00:00,2026-09-02 10:00:00);INSERTINTOorders(order_id,order_no,user_id,product_id,quantity,unit_price,discount_amount,total_amount,status,created_at,updated_at)VALUES(1,ORD202609100001,1,1,1,579900,20000,559900,1,2026-09-10 10:00:00,2026-09-10 10:00:00),(2,ORD202609120001,1,2,2,39900,0,79800,2,2026-09-12 11:20:00,2026-09-12 11:20:00),(3,ORD202609150001,2,3,3,5990,0,17970,1,2026-09-15 15:30:00,2026-09-15 15:30:00),(4,ORD202609180001,2,4,1,119900,10000,109900,3,2026-09-18 16:40:00,2026-09-18 16:40:00),(5,ORD202609200001,3,5,10,1990,1000,18900,2,2026-09-20 09:15:00,2026-09-20 09:15:00),(6,ORD202610010001,4,2,1,35900,4000,31900,0,2026-10-01 12:00:00,2026-10-01 12:00:00),(7,ORD202610030001,1,3,2,5990,0,11980,2,2026-10-03 19:20:00,2026-10-03 19:20:00),(8,ORD202610050001,4,4,2,129900,10000,249800,1,2026-10-05 20:10:00,2026-10-05 20:10:00),(9,ORD202610080001,2,2,1,39900,0,39900,2,2026-10-08 14:30:00,2026-10-08 14:30:00),(10,ORD202610090001,6,5,5,1990,0,9950,1,2026-10-09 09:45:00,2026-10-09 09:45:00);感觉有某个字段值不喜欢可以双击单元格修改正经的Sql开始一 基础查询1.1 查询整张表SELECT*FROMusers;*表示查询所有字段。1.2 查询指定字段SELECTuser_id,username,emailFROMusers;1.3 使用字段别名 ASSELECTuser_idAS用户ID,usernameAS用户名,emailAS邮箱FROMusers;1.4 统计总数 countSELECTcount(1)FROMusers;二 条件查询2.1 等值查询 where查询所有上架商品SELECTproduct_id,product_name,price,stockFROMproductsWHEREstatus3;查询已支付订单SELECTorder_no,total_amount,created_atFROMordersWHEREstatus1;2.2 多条件查询 AND OR IN查询上架且有库存的数码商品SELECTproduct_id,product_name,stockFROMproductsWHEREcategory数码ANDstatus3ANDstock0;查询家居类或办公类商品SELECTproduct_id,product_name,categoryFROMproductsWHEREcategory家居ORcategory办公;查询有效订单即已支付或已收货订单IN(1,2)比status1ORstatus2更简洁。SELECTorder_no,status,total_amountFROMordersWHEREstatusIN(1,2);2.3 范围查询查询2026年10月创建的订单SELECTorder_no,created_atFROMordersWHEREcreated_at2026-10-01 00:00:00ANDcreated_at2026-11-01 00:00:00;查询金额在300-500元的订单SELECT*FROMordersWHEREtotal_amount30000ANDtotal_amount50000;2.4 模糊查询 LIKE%匹配任意长度字符也可以是零个字符。查询名称中包含“本”的商品SELECTproduct_id,product_name,categoryFROMproductsWHEREproduct_nameLIKE%本%;查询名称中以“本”开头的商品SELECTproduct_id,product_name,categoryFROMproductsWHEREproduct_nameLIKE本%;2.5 排序 order byASC升序默认值。DESC降序。按商品价格从高到低排序SELECT*FROMproductsORDERBYpriceDESC;查询金额最高的3笔有效订单LIMIT可以限制查询条数SELECT*FROMordersWHEREstatusIN(1,2)ORDERBYtotal_amountDESC,order_idDESCLIMIT3;2.6 case重命名翻译商品状态SELECTproduct_id,product_name,CASEstatusWHEN0THEN下架WHEN3THEN上架ELSE未知状态ENDASstatus_nameFROMproducts;翻译订单状态SELECTorder_no,CASEstatusWHEN0THEN待支付WHEN1THEN已支付WHEN2THEN已收货WHEN3THEN已取消ELSE未知状态ENDASstatus_nameFROMordersORDERBYcreated_at;三 数据聚合3.1 取最值 MIN MAX获取商品的最大、最小价格SELECTMIN(price)/100.0ASmin_price_yuan,MAX(price)/100.0ASmax_price_yuan,FROMproductsWHEREstatus3;3.2 求和 多行数据累加 SUM有效订单统计SELECTCOUNT(*)ASvalid_order_count,SUM(quantity)ASsold_quantity,SUM(total_amount)/100.0ASsales_amount_yuanFROMordersWHEREstatusIN(1,2);3.3 分组求和按商品分类统计获取每个商品分类的总数、最大价格、求和价格SELECTcategory,COUNT(*)ASproduct_count,SUM(price)astotal_price,MAX(price)ASmax_priceFROMproductsGROUPBYcategoryORDERBYtotal_priceDESC; 
长河君 · 2026/10/10 8:37:59 · 阅读 1.2k 学习Sql-part1 for婷姐
学习sql前需要先建表建表先建库以下是三张表用户表、订单表、商品表CREATETABLEusers(user_idbigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT用户主键,usernamevarchar(50)NOTNULLCOMMENT用户名,emailvarchar(100)NOTNULLCOMMENT邮箱,phonevarchar(20)DEFAULTNULLCOMMENT手机号,statusenum(ACTIVE,INACTIVE)NOTNULLDEFAULTACTIVECOMMENT用户状态,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT注册时间,updated_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(user_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT用户表;CREATETABLEproducts(product_idbigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT商品主键,product_namevarchar(100)NOTNULLCOMMENT商品名称,categoryvarchar(50)NOTNULLCOMMENT商品分类,pricebigint(20)NOTNULLCOMMENT当前标价 单位 分,stockint(10)unsignedNOTNULLDEFAULT0COMMENT库存数量,statusint(11)NOTNULLDEFAULT0COMMENT0-下架 3-上架,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,updated_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(product_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT商品表;CREATETABLEorders(order_idbigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT订单主键,order_novarchar(32)NOTNULLCOMMENT业务订单号,user_idbigint(20)unsignedNOTNULLCOMMENT下单用户,product_idbigint(20)unsignedNOTNULLCOMMENT所购商品,quantityint(10)unsignedNOTNULLCOMMENT购买数量,unit_pricebigint(20)NOTNULLCOMMENT下单时成交单价,discount_amountbigint(20)NOTNULLDEFAULT0COMMENT整单优惠金额,total_amountbigint(20)NOTNULLDEFAULT0COMMENT订单实付金额,statusint(11)NOTNULLDEFAULT0COMMENT订单状态 0-待支付 1-已支付 2-已收货 3-取消,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间,updated_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(order_id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT订单表;在query下运行sql即可创建三张表然后进行数据初始化在query中运行以下sqlINSERTINTOusers(user_id,username,email,phone,status,created_at,updated_at)VALUES(1,婷A,aliceexample.com,13800000001,ACTIVE,2026-09-01 09:00:00,2026-09-01 09:00:00),(2,婷B,bobexample.com,13800000002,ACTIVE,2026-09-03 10:30:00,2026-09-03 10:30:00),(3,婷C,carolexample.com,13800000003,INACTIVE,2026-09-05 14:20:00,2026-09-05 14:20:00),(4,婷D,davidexample.com,13800000004,ACTIVE,2026-09-08 18:10:00,2026-09-08 18:10:00),(5,婷E,eveexample.com,NULL,ACTIVE,2026-09-10 08:40:00,2026-09-10 08:40:00),(6,婷F,frankexample.com,13800000006,ACTIVE,2026-10-01 13:00:00,2026-10-01 13:00:00);INSERTINTOproducts(product_id,product_name,category,price,stock,status,created_at,updated_at)VALUES(1,轻薄笔记本电脑,数码,599900,20,3,2026-09-01 09:00:00,2026-09-01 09:00:00),(2,机械键盘,数码,39900,100,3,2026-09-01 09:10:00,2026-09-01 09:10:00),(3,陶瓷咖啡杯,家居,5990,50,3,2026-09-01 09:20:00,2026-09-01 09:20:00),(4,27英寸显示器,数码,129900,35,3,2026-09-01 09:30:00,2026-09-01 09:30:00),(5,硬壳笔记本,办公,1990,200,3,2026-09-01 09:40:00,2026-09-01 09:40:00),(6,无线鼠标,数码,12900,0,0,2026-09-01 09:50:00,2026-09-01 09:50:00),(7,电动升降桌,办公,89900,12,3,2026-09-02 10:00:00,2026-09-02 10:00:00);INSERTINTOorders(order_id,order_no,user_id,product_id,quantity,unit_price,discount_amount,total_amount,status,created_at,updated_at)VALUES(1,ORD202609100001,1,1,1,579900,20000,559900,1,2026-09-10 10:00:00,2026-09-10 10:00:00),(2,ORD202609120001,1,2,2,39900,0,79800,2,2026-09-12 11:20:00,2026-09-12 11:20:00),(3,ORD202609150001,2,3,3,5990,0,17970,1,2026-09-15 15:30:00,2026-09-15 15:30:00),(4,ORD202609180001,2,4,1,119900,10000,109900,3,2026-09-18 16:40:00,2026-09-18 16:40:00),(5,ORD202609200001,3,5,10,1990,1000,18900,2,2026-09-20 09:15:00,2026-09-20 09:15:00),(6,ORD202610010001,4,2,1,35900,4000,31900,0,2026-10-01 12:00:00,2026-10-01 12:00:00),(7,ORD202610030001,1,3,2,5990,0,11980,2,2026-10-03 19:20:00,2026-10-03 19:20:00),(8,ORD202610050001,4,4,2,129900,10000,249800,1,2026-10-05 20:10:00,2026-10-05 20:10:00),(9,ORD202610080001,2,2,1,39900,0,39900,2,2026-10-08 14:30:00,2026-10-08 14:30:00),(10,ORD202610090001,6,5,5,1990,0,9950,1,2026-10-09 09:45:00,2026-10-09 09:45:00);感觉有某个字段值不喜欢可以双击单元格修改正经的Sql开始一 基础查询1.1 查询整张表SELECT*FROMusers;*表示查询所有字段。1.2 查询指定字段SELECTuser_id,username,emailFROMusers;1.3 使用字段别名 ASSELECTuser_idAS用户ID,usernameAS用户名,emailAS邮箱FROMusers;1.4 统计总数 countSELECTcount(1)FROMusers;二 条件查询2.1 等值查询 where查询所有上架商品SELECTproduct_id,product_name,price,stockFROMproductsWHEREstatus3;查询已支付订单SELECTorder_no,total_amount,created_atFROMordersWHEREstatus1;2.2 多条件查询 AND OR IN查询上架且有库存的数码商品SELECTproduct_id,product_name,stockFROMproductsWHEREcategory数码ANDstatus3ANDstock0;查询家居类或办公类商品SELECTproduct_id,product_name,categoryFROMproductsWHEREcategory家居ORcategory办公;查询有效订单即已支付或已收货订单IN(1,2)比status1ORstatus2更简洁。SELECTorder_no,status,total_amountFROMordersWHEREstatusIN(1,2);2.3 范围查询查询2026年10月创建的订单SELECTorder_no,created_atFROMordersWHEREcreated_at2026-10-01 00:00:00ANDcreated_at2026-11-01 00:00:00;查询金额在300-500元的订单SELECT*FROMordersWHEREtotal_amount30000ANDtotal_amount50000;2.4 模糊查询 LIKE%匹配任意长度字符也可以是零个字符。查询名称中包含“本”的商品SELECTproduct_id,product_name,categoryFROMproductsWHEREproduct_nameLIKE%本%;查询名称中以“本”开头的商品SELECTproduct_id,product_name,categoryFROMproductsWHEREproduct_nameLIKE本%;2.5 排序 order byASC升序默认值。DESC降序。按商品价格从高到低排序SELECT*FROMproductsORDERBYpriceDESC;查询金额最高的3笔有效订单LIMIT可以限制查询条数SELECT*FROMordersWHEREstatusIN(1,2)ORDERBYtotal_amountDESC,order_idDESCLIMIT3;2.6 case重命名翻译商品状态SELECTproduct_id,product_name,CASEstatusWHEN0THEN下架WHEN3THEN上架ELSE未知状态ENDASstatus_nameFROMproducts;翻译订单状态SELECTorder_no,CASEstatusWHEN0THEN待支付WHEN1THEN已支付WHEN2THEN已收货WHEN3THEN已取消ELSE未知状态ENDASstatus_nameFROMordersORDERBYcreated_at;三 数据聚合3.1 取最值 MIN MAX获取商品的最大、最小价格SELECTMIN(price)/100.0ASmin_price_yuan,MAX(price)/100.0ASmax_price_yuan,FROMproductsWHEREstatus3;3.2 求和 多行数据累加 SUM有效订单统计SELECTCOUNT(*)ASvalid_order_count,SUM(quantity)ASsold_quantity,SUM(total_amount)/100.0ASsales_amount_yuanFROMordersWHEREstatusIN(1,2);3.3 分组求和按商品分类统计获取每个商品分类的总数、最大价格、求和价格SELECTcategory,COUNT(*)ASproduct_count,SUM(price)astotal_price,MAX(price)ASmax_priceFROMproductsGROUPBYcategoryORDERBYtotal_priceDESC;