学习Sql-part1 for婷姐

学习sql前，需要先建表

建表先建库

以下是三张表，用户表、订单表、商品表

CREATETABLE`users`(`user_id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'用户主键',`username`varchar(50)NOTNULLCOMMENT'用户名',`email`varchar(100)NOTNULLCOMMENT'邮箱',`phone`varchar(20)DEFAULTNULLCOMMENT'手机号',`status`enum('ACTIVE','INACTIVE')NOTNULLDEFAULT'ACTIVE'COMMENT'用户状态',`created_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'注册时间',`updated_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`user_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用户表';CREATETABLE`products`(`product_id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'商品主键',`product_name`varchar(100)NOTNULLCOMMENT'商品名称',`category`varchar(50)NOTNULLCOMMENT'商品分类',`price`bigint(20)NOTNULLCOMMENT'当前标价 单位 分',`stock`int(10)unsignedNOTNULLDEFAULT'0'COMMENT'库存数量',`status`int(11)NOTNULLDEFAULT'0'COMMENT'0-下架 3-上架',`created_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',`updated_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`product_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='商品表';CREATETABLE`orders`(`order_id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'订单主键',`order_no`varchar(32)NOTNULLCOMMENT'业务订单号',`user_id`bigint(20)unsignedNOTNULLCOMMENT'下单用户',`product_id`bigint(20)unsignedNOTNULLCOMMENT'所购商品',`quantity`int(10)unsignedNOTNULLCOMMENT'购买数量',`unit_price`bigint(20)NOTNULLCOMMENT'下单时成交单价',`discount_amount`bigint(20)NOTNULLDEFAULT'0'COMMENT'整单优惠金额',`total_amount`bigint(20)NOTNULLDEFAULT'0'COMMENT'订单实付金额',`status`int(11)NOTNULLDEFAULT'0'COMMENT'订单状态 0-待支付 1-已支付 2-已收货 3-取消',`created_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'下单时间',`updated_at`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`order_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='订单表';

在query下运行sql，即可创建三张表

然后进行数据初始化，在query中运行以下sql

INSERTINTOusers(user_id,username,email,phone,status,created_at,updated_at)VALUES(1,'婷A','alice@example.com','13800000001','ACTIVE','2026-09-01 09:00:00','2026-09-01 09:00:00'),(2,'婷B','bob@example.com','13800000002','ACTIVE','2026-09-03 10:30:00','2026-09-03 10:30:00'),(3,'婷C','carol@example.com','13800000003','INACTIVE','2026-09-05 14:20:00','2026-09-05 14:20:00'),(4,'婷D','david@example.com','13800000004','ACTIVE','2026-09-08 18:10:00','2026-09-08 18:10:00'),(5,'婷E','eve@example.com',NULL,'ACTIVE','2026-09-10 08:40:00','2026-09-10 08:40:00'),(6,'婷F','frank@example.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 使用字段别名 AS

SELECTuser_idAS用户ID,usernameAS用户名,emailAS邮箱FROMusers;

1.4 统计总数 count

SELECTcount(1)FROMusers;

二 条件查询

2.1 等值查询 where

查询所有上架商品：SELECTproduct_id,product_name,price,stockFROMproductsWHEREstatus=3;查询已支付订单：SELECTorder_no,total_amount,created_atFROMordersWHEREstatus=1;

2.2 多条件查询 AND OR IN

查询上架且有库存的数码商品：SELECTproduct_id,product_name,stockFROMproductsWHEREcategory='数码'ANDstatus=3ANDstock>0;查询家居类或办公类商品：SELECTproduct_id,product_name,categoryFROMproductsWHEREcategory='家居'ORcategory='办公';查询有效订单，即已支付或已收货订单：IN(1,2)比status=1ORstatus=2更简洁。SELECTorder_no,status,total_amountFROMordersWHEREstatusIN(1,2);

2.3 范围查询

查询2026年10月创建的订单：SELECTorder_no,created_atFROMordersWHEREcreated_at>='2026-10-01 00:00:00'ANDcreated_at<'2026-11-01 00:00:00';查询金额在300-500元的订单SELECT*FROMordersWHEREtotal_amount>=30000ANDtotal_amount<=50000;

2.4 模糊查询 LIKE

%：匹配任意长度字符，也可以是零个字符。

查询名称中包含“本”的商品：SELECTproduct_id,product_name,categoryFROMproductsWHEREproduct_nameLIKE'%本%';查询名称中以“本”开头的商品：SELECTproduct_id,product_name,categoryFROMproductsWHEREproduct_nameLIKE'本%';

2.5 排序 order by

ASC：升序，默认值。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,FROMproductsWHEREstatus=3;

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;