案例一后台骑手管理——条件 分页 排序需求做一个骑手管理后台的列表页页面上有搜索条件、分页条、排序列。用户输入查询条件后点搜索后端查出符合条件的第 1 页数据每页 8 条按评分降序排列。搜索表单如下┌─────────────────────────────────────────────────┐ │ 姓名[ 王 ] 城市[ 北京 ] │ │ 入职时间[ 2020-01-01 ] 至 [ 2024-12-31 ] │ │ 评分不低于[ 4.5 ] │ │ [ 搜索 ] [ 重置 ] │ │ ───────────────────────────────────────────── │ │ ID │ 姓名 │ 城市 │ 等级 │ 评分 │ 入职日期 │ │ ────┼──────┼──────┼──────┼──────┼──────────── │ │ ... │ ... │ ... │ ... │ ... │ ... │ │ ────────────────────────────────────────────── │ │ 1 2 3 ... 12 │ └─────────────────────────────────────────────────┘需求分析把需求拆成三块条件查询姓名模糊LIKE 王% 城市等值 入职日期范围 评分大于等于排序按评分降序分页第 1 页每页 8 条 → LIMIT 0, 8SQL 实现-- 骑手管理后台条件分页查询 SELECT id, name, city, level, score, join_date FROM rider WHERE name LIKE 王% AND city 北京 AND join_date BETWEEN 2020-01-01 AND 2024-12-31 AND score 4.5 ORDER BY score DESC LIMIT 0, 8;翻页计算用户点第 2 页前端传来 page2, pageSize8后端算出起始索引 (page - 1) × pageSize (2 - 1) × 8 8SQL 变成SELECT id, name, city, level, score, join_date FROM rider WHERE name LIKE 王% AND city 北京 AND join_date BETWEEN 2020-01-01 AND 2024-12-31 AND score 4.5 ORDER BY score DESC LIMIT 8, 8; -- 从第 8 条开始取 8 条第 3 页同理LIMIT 16, 8。总记录数与总页数前端的分页条需要展示总页数需要先查询总记录数SELECT COUNT(*) AS total FROM rider WHERE name LIKE 王% AND city 北京 AND join_date BETWEEN 2020-01-01 AND 2024-12-31 AND score 4.5;总页数 CEIL(total / pageSize)这两条 SQL 是后台列表页的标配——一条查数据一条查总数。后端实现伪代码// Controller RequestMapping(/admin/rider/list) public Result list(RequestParam(defaultValue 1) Integer page, RequestParam(defaultValue 8) Integer pageSize, String name, String city, DateTimeFormat(pattern yyyy-MM-dd) LocalDate startDate, DateTimeFormat(pattern yyyy-MM-dd) LocalDate endDate, Double minScore) { // 动态拼 SQL空值不参与 WHERE PageResult result riderService.queryPage(page, pageSize, name, city, startDate, endDate, minScore); return Result.success(result); }在 MyBatis 里用if标签动态拼接 WHERE 条件——姓名不填就不查 LIKE城市不选就不加 AND city ?。这个后面写项目时会细讲。案例二运营数据看板——分组统计 图形报表需求运营人员想看两个报表性别分布饼图男骑手多少人、女骑手多少人等级分布柱状图青铜多少人、白银多少人、黄金多少人、钻石多少人后端只需要返回统计好的数字前端拿到后用 ECharts 渲染。1. 性别统计SELECT CASE WHEN gender 1 THEN 男骑手 WHEN gender 2 THEN 女骑手 ELSE 未知 END AS 性别, COUNT(*) AS 人数 FROM rider GROUP BY gender;也可以用 IF 函数只有两个分支时更简洁SELECT IF(gender 1, 男骑手, 女骑手) AS 性别, COUNT(*) AS 人数 FROM rider GROUP BY gender;返回结果性别人数男骑手13女骑手7前端拿到这两行数据饼图就出来了。2. 等级分布统计SELECT CASE level WHEN 1 THEN 青铜骑手 WHEN 2 THEN 白银骑手 WHEN 3 THEN 黄金骑手 WHEN 4 THEN 钻石骑手 ELSE 未评级 END AS 等级, COUNT(*) AS 人数 FROM rider GROUP BY level ORDER BY level;返回结果等级人数青铜骑手3白银骑手5黄金骑手4钻石骑手4未评级1CASE WHEN 是 SQL 里的 if-else做字段值转换非常方便。比如数据库里性别存的是 1/2返回给前端时转成男/女——一行 SQL 搞定不用在 Java 代码里遍历。3. 各城市评分排名扩展SELECT city, COUNT(*) AS 骑手数, ROUND(AVG(score), 2) AS 平均分 FROM rider WHERE city IS NOT NULL GROUP BY city HAVING COUNT(*) 2 ORDER BY 平均分 DESC;ROUND(AVG(score), 2)平均分保留两位小数。HAVING COUNT(*) 2只看有 2 人以上的城市单人城市的平均分没参考价值。案例三外卖点餐系统——从头设计四张表需求做一个外卖点餐系统包含三个核心模块菜品分类管理后台可以维护菜品分类如热菜凉菜主食饮品每个分类有名称、排序号、启用/停用状态。菜品管理每个菜品属于一个分类有菜品名、图片、售价、描述、售卖状态。套餐管理套餐是多个菜品的组合打折卖。比如一人食套餐包含 1 份宫保鸡丁 1 份米饭 1 瓶可乐原价 45 元套餐价 35 元。表关系分析先想清楚各模块之间是什么关系分类 → 菜品一个分类下有多个菜品一个菜品只属于一个分类 →一对多菜品 ↔ 套餐一个套餐包含多个菜品一个菜品可被多个套餐引用 →多对多多对多需要一张中间表来拆。设计四张表1. 分类表 categoryCREATE TABLE category ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 分类ID, name VARCHAR(20) NOT NULL UNIQUE COMMENT 分类名称, type TINYINT UNSIGNED NOT NULL COMMENT 类型: 1菜品分类 2套餐分类, sort TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 排序号, status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 状态: 0禁用 1启用, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间 ) COMMENT 分类表;2. 菜品表 dishCREATE TABLE dish ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 菜品ID, name VARCHAR(50) NOT NULL COMMENT 菜品名称, category_id INT UNSIGNED NOT NULL COMMENT 分类ID, price DECIMAL(10, 2) NOT NULL COMMENT 售价, image VARCHAR(255) COMMENT 图片URL, description TEXT COMMENT 描述, status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 状态: 0停售 1启售, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间, FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE RESTRICT ) COMMENT 菜品表;3. 套餐表 setmealCREATE TABLE setmeal ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 套餐ID, name VARCHAR(50) NOT NULL COMMENT 套餐名称, category_id INT UNSIGNED NOT NULL COMMENT 分类ID, price DECIMAL(10, 2) NOT NULL COMMENT 套餐价, image VARCHAR(255) COMMENT 图片URL, description TEXT COMMENT 描述, status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 状态: 0停售 1启售, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间, FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE RESTRICT ) COMMENT 套餐表;4. 套餐菜品关系表 setmeal_dish中间表CREATE TABLE setmeal_dish ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 主键, setmeal_id INT UNSIGNED NOT NULL COMMENT 套餐ID, dish_id INT UNSIGNED NOT NULL COMMENT 菜品ID, copies INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 份数, sort TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 排序, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间, FOREIGN KEY (setmeal_id) REFERENCES setmeal(id) ON DELETE CASCADE, FOREIGN KEY (dish_id) REFERENCES dish(id) ON DELETE CASCADE, UNIQUE KEY uk_setmeal_dish (setmeal_id, dish_id) ) COMMENT 套餐菜品关系表;表关系总结category → dish一对多通过 dish.category_id 关联category → setmeal一对多通过 setmeal.category_id 关联setmeal ↔ dish多对多通过中间表 setmeal_dish 关联这样设计后就可以用 SQL 实现各种业务查询-- 查询某个分类下的所有菜品 SELECT d.* FROM dish d WHERE d.category_id 1 AND d.status 1 ORDER BY d.create_time DESC; -- 查询某个套餐包含的所有菜品及份数 SELECT d.name, d.price, sd.copies FROM setmeal_dish sd JOIN dish d ON sd.dish_id d.id WHERE sd.setmeal_id 1; -- 统计每个分类下的菜品数量 SELECT c.name, COUNT(d.id) AS dish_count FROM category c LEFT JOIN dish d ON c.id d.category_id WHERE c.type 1 GROUP BY c.id ORDER BY c.sort; web前端基础到入门——15day
案例一后台骑手管理——条件 分页 排序需求做一个骑手管理后台的列表页页面上有搜索条件、分页条、排序列。用户输入查询条件后点搜索后端查出符合条件的第 1 页数据每页 8 条按评分降序排列。搜索表单如下┌─────────────────────────────────────────────────┐ │ 姓名[ 王 ] 城市[ 北京 ] │ │ 入职时间[ 2020-01-01 ] 至 [ 2024-12-31 ] │ │ 评分不低于[ 4.5 ] │ │ [ 搜索 ] [ 重置 ] │ │ ───────────────────────────────────────────── │ │ ID │ 姓名 │ 城市 │ 等级 │ 评分 │ 入职日期 │ │ ────┼──────┼──────┼──────┼──────┼──────────── │ │ ... │ ... │ ... │ ... │ ... │ ... │ │ ────────────────────────────────────────────── │ │ 1 2 3 ... 12 │ └─────────────────────────────────────────────────┘需求分析把需求拆成三块条件查询姓名模糊LIKE 王% 城市等值 入职日期范围 评分大于等于排序按评分降序分页第 1 页每页 8 条 → LIMIT 0, 8SQL 实现-- 骑手管理后台条件分页查询 SELECT id, name, city, level, score, join_date FROM rider WHERE name LIKE 王% AND city 北京 AND join_date BETWEEN 2020-01-01 AND 2024-12-31 AND score 4.5 ORDER BY score DESC LIMIT 0, 8;翻页计算用户点第 2 页前端传来 page2, pageSize8后端算出起始索引 (page - 1) × pageSize (2 - 1) × 8 8SQL 变成SELECT id, name, city, level, score, join_date FROM rider WHERE name LIKE 王% AND city 北京 AND join_date BETWEEN 2020-01-01 AND 2024-12-31 AND score 4.5 ORDER BY score DESC LIMIT 8, 8; -- 从第 8 条开始取 8 条第 3 页同理LIMIT 16, 8。总记录数与总页数前端的分页条需要展示总页数需要先查询总记录数SELECT COUNT(*) AS total FROM rider WHERE name LIKE 王% AND city 北京 AND join_date BETWEEN 2020-01-01 AND 2024-12-31 AND score 4.5;总页数 CEIL(total / pageSize)这两条 SQL 是后台列表页的标配——一条查数据一条查总数。后端实现伪代码// Controller RequestMapping(/admin/rider/list) public Result list(RequestParam(defaultValue 1) Integer page, RequestParam(defaultValue 8) Integer pageSize, String name, String city, DateTimeFormat(pattern yyyy-MM-dd) LocalDate startDate, DateTimeFormat(pattern yyyy-MM-dd) LocalDate endDate, Double minScore) { // 动态拼 SQL空值不参与 WHERE PageResult result riderService.queryPage(page, pageSize, name, city, startDate, endDate, minScore); return Result.success(result); }在 MyBatis 里用if标签动态拼接 WHERE 条件——姓名不填就不查 LIKE城市不选就不加 AND city ?。这个后面写项目时会细讲。案例二运营数据看板——分组统计 图形报表需求运营人员想看两个报表性别分布饼图男骑手多少人、女骑手多少人等级分布柱状图青铜多少人、白银多少人、黄金多少人、钻石多少人后端只需要返回统计好的数字前端拿到后用 ECharts 渲染。1. 性别统计SELECT CASE WHEN gender 1 THEN 男骑手 WHEN gender 2 THEN 女骑手 ELSE 未知 END AS 性别, COUNT(*) AS 人数 FROM rider GROUP BY gender;也可以用 IF 函数只有两个分支时更简洁SELECT IF(gender 1, 男骑手, 女骑手) AS 性别, COUNT(*) AS 人数 FROM rider GROUP BY gender;返回结果性别人数男骑手13女骑手7前端拿到这两行数据饼图就出来了。2. 等级分布统计SELECT CASE level WHEN 1 THEN 青铜骑手 WHEN 2 THEN 白银骑手 WHEN 3 THEN 黄金骑手 WHEN 4 THEN 钻石骑手 ELSE 未评级 END AS 等级, COUNT(*) AS 人数 FROM rider GROUP BY level ORDER BY level;返回结果等级人数青铜骑手3白银骑手5黄金骑手4钻石骑手4未评级1CASE WHEN 是 SQL 里的 if-else做字段值转换非常方便。比如数据库里性别存的是 1/2返回给前端时转成男/女——一行 SQL 搞定不用在 Java 代码里遍历。3. 各城市评分排名扩展SELECT city, COUNT(*) AS 骑手数, ROUND(AVG(score), 2) AS 平均分 FROM rider WHERE city IS NOT NULL GROUP BY city HAVING COUNT(*) 2 ORDER BY 平均分 DESC;ROUND(AVG(score), 2)平均分保留两位小数。HAVING COUNT(*) 2只看有 2 人以上的城市单人城市的平均分没参考价值。案例三外卖点餐系统——从头设计四张表需求做一个外卖点餐系统包含三个核心模块菜品分类管理后台可以维护菜品分类如热菜凉菜主食饮品每个分类有名称、排序号、启用/停用状态。菜品管理每个菜品属于一个分类有菜品名、图片、售价、描述、售卖状态。套餐管理套餐是多个菜品的组合打折卖。比如一人食套餐包含 1 份宫保鸡丁 1 份米饭 1 瓶可乐原价 45 元套餐价 35 元。表关系分析先想清楚各模块之间是什么关系分类 → 菜品一个分类下有多个菜品一个菜品只属于一个分类 →一对多菜品 ↔ 套餐一个套餐包含多个菜品一个菜品可被多个套餐引用 →多对多多对多需要一张中间表来拆。设计四张表1. 分类表 categoryCREATE TABLE category ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 分类ID, name VARCHAR(20) NOT NULL UNIQUE COMMENT 分类名称, type TINYINT UNSIGNED NOT NULL COMMENT 类型: 1菜品分类 2套餐分类, sort TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 排序号, status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 状态: 0禁用 1启用, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间 ) COMMENT 分类表;2. 菜品表 dishCREATE TABLE dish ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 菜品ID, name VARCHAR(50) NOT NULL COMMENT 菜品名称, category_id INT UNSIGNED NOT NULL COMMENT 分类ID, price DECIMAL(10, 2) NOT NULL COMMENT 售价, image VARCHAR(255) COMMENT 图片URL, description TEXT COMMENT 描述, status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 状态: 0停售 1启售, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间, FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE RESTRICT ) COMMENT 菜品表;3. 套餐表 setmealCREATE TABLE setmeal ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 套餐ID, name VARCHAR(50) NOT NULL COMMENT 套餐名称, category_id INT UNSIGNED NOT NULL COMMENT 分类ID, price DECIMAL(10, 2) NOT NULL COMMENT 套餐价, image VARCHAR(255) COMMENT 图片URL, description TEXT COMMENT 描述, status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 状态: 0停售 1启售, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间, FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE RESTRICT ) COMMENT 套餐表;4. 套餐菜品关系表 setmeal_dish中间表CREATE TABLE setmeal_dish ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 主键, setmeal_id INT UNSIGNED NOT NULL COMMENT 套餐ID, dish_id INT UNSIGNED NOT NULL COMMENT 菜品ID, copies INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 份数, sort TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 排序, create_time DATETIME NOT NULL COMMENT 创建时间, update_time DATETIME NOT NULL COMMENT 更新时间, FOREIGN KEY (setmeal_id) REFERENCES setmeal(id) ON DELETE CASCADE, FOREIGN KEY (dish_id) REFERENCES dish(id) ON DELETE CASCADE, UNIQUE KEY uk_setmeal_dish (setmeal_id, dish_id) ) COMMENT 套餐菜品关系表;表关系总结category → dish一对多通过 dish.category_id 关联category → setmeal一对多通过 setmeal.category_id 关联setmeal ↔ dish多对多通过中间表 setmeal_dish 关联这样设计后就可以用 SQL 实现各种业务查询-- 查询某个分类下的所有菜品 SELECT d.* FROM dish d WHERE d.category_id 1 AND d.status 1 ORDER BY d.create_time DESC; -- 查询某个套餐包含的所有菜品及份数 SELECT d.name, d.price, sd.copies FROM setmeal_dish sd JOIN dish d ON sd.dish_id d.id WHERE sd.setmeal_id 1; -- 统计每个分类下的菜品数量 SELECT c.name, COUNT(d.id) AS dish_count FROM category c LEFT JOIN dish d ON c.id d.category_id WHERE c.type 1 GROUP BY c.id ORDER BY c.sort;