综合练习与面试题

今天不学新关键字，而是把 00–16 串成一个能写在简历里的小项目，并整理几道入门/校招向的高频问答。

系列：MySQL 入门到查询 · 第 17 / 17 篇（第 17 天 · 5 月 7 日 · 收官）
上一篇：备份与实用命令
总目录：系列索引

环境说明

• 库：shop（也可继续用 train，全文保持一致即可）
• check 约束需 MySQL 8.0.16+ 才能强制；系列按 8.0 编写
• 请按顺序执行下文 A～E 全部 seed
• 练习数据以本篇为准

---

第一部分 · 综合项目：迷你商城库

A. 建库与部门表

create database if not exists shop charset utf8mb4;
use shop;

drop table if exists order_items;
drop table if exists orders;
drop table if exists goods;
drop table if exists users;
drop table if exists dept;

create table dept(
    deptno int primary key,
    dname varchar(20) not null,
    loc varchar(20)
);
insert into dept values
    (10,'运营部','北京'),
    (20,'技术部','上海'),
    (30,'客服部','广州'),
    (40,'仓储部','深圳');

B. 商品与用户

create table goods(
    gid int primary key auto_increment,
    gname varchar(30) not null,
    price double check(price>0),
    stock int not null default 0
);

insert into goods(gname,price,stock) values
    ('键盘',199,50),
    ('鼠标',99,80),
    ('显示器',1299,20),
    ('主机',4999,10),
    ('耳机',299,0);

create table users(
    uid int primary key auto_increment,
    uname varchar(20) not null,
    phone varchar(11) unique,
    regtime date
);

insert into users(uname,phone,regtime) values
    ('张三','13800000001','2024-01-10'),
    ('李四','13800000002','2024-03-15'),
    ('王五','13800000003','2025-06-01'),
    ('赵六','13800000004','2025-09-20');

C. 订单与明细（多表 + 约束）

create table orders(
    oid int primary key auto_increment,
    uid int,
    otime datetime,
    constraint orders_uid_fk foreign key(uid) references users(uid)
);

insert into orders(uid,otime) values
    (1,'2025-01-05 10:00:00'),
    (2,'2025-02-14 20:30:00'),
    (1,'2025-03-01 09:15:00'),
    (3,'2025-11-11 11:11:00');

create table order_items(
    iid int primary key auto_increment,
    oid int,
    gid int,
    num int check(num>0),
    constraint items_oid_fk foreign key(oid) references orders(oid),
    constraint items_gid_fk foreign key(gid) references goods(gid)
);

insert into order_items(oid,gid,num) values
    (1,1,2),
    (1,2,1),
    (2,3,1),
    (3,4,1),
    (3,5,2),
    (4,3,2),
    (4,2,3);

建表顺序：先主表 users/goods，再 orders，最后 order_items（外键）。

D. 你可以自己完成的练习题（建议先做再看答案）

D1 基础查询

1. 列出所有商品名称与价格
2. 查找价格大于 200 的商品
3. 名字里含「机」的商品
4. 库存为 0 的商品

D2 修改与删除

5. 将「耳机」价格改为 259
6. 思考：能否直接 delete from goods where gname='耳机'？（本库有外键引用，见下方答案区提示）

D3 聚合与分组

7. 商品总数、库存总和、平均价格
8. 有多少个不同价格的商品（distinct price）
9. 按注册年份看每年有多少用户（year(regtime) + count(*)）

D4 排序与分页

10. 按价格降序，显示前 3 件商品
11. 按价格升序，跳过 1 件后再取 2 件

D5 多表关联

12. 每个订单的：订单号、用户姓名、下单时间
13. 每个订单明细：订单号、商品名、数量、单价
14. 每个订单的总金额（数量×单价，再 sum）
15. 没有任何订单的用户（提示：users left join orders）

D6 子查询 / 综合

16. 找出「订单总金额」最高的那个订单号（可用 order by ... limit 1 或子查询）
17. 买过「显示器」的用户姓名
18. 每个用户下了几单、累计消费金额

---

E. 参考答案（可折叠阅读，建议先自己写）

use shop;

-- D1
select gname, price from goods;
select gname, price from goods where price>200;
select gname from goods where gname like '%机%';
select gname, stock from goods where stock=0;

-- D2
update goods set price=259 where gname='耳机';
select * from goods;
-- 勿直接：delete from goods where stock=0 and gname='耳机';
-- 外键示例：先查明细再删（或本练习只做 update）
-- select gid from goods where gname='耳机';
-- delete from order_items where gid=5;
-- delete from goods where gid=5;

-- D3
select count(*) as 商品数, sum(stock) as 库存合计, avg(price) as 均价 from goods;
select count(distinct price) as 不同价格数 from goods;
select year(regtime) as 注册年, count(*) as 人数
from users group by year(regtime) order by 注册年;

-- D4
select gid,gname,price from goods order by price desc limit 3;
select gid,gname,price from goods order by price limit 2 offset 1;

-- D5
select o.oid, u.uname, o.otime
from orders o join users u on o.uid=u.uid
order by o.otime;

select o.oid, g.gname, i.num, g.price
from order_items i
join orders o on i.oid=o.oid
join goods g on i.gid=g.gid
order by o.oid, g.gname;

select o.oid, sum(i.num*g.price) as 订单金额
from order_items i
join orders o on i.oid=o.oid
join goods g on i.gid=g.gid
group by o.oid
order by 订单金额 desc;

select u.uid, u.uname
from users u left join orders o on u.uid=o.uid
where o.oid is null;

-- D6（示例一种写法）
select o.oid, sum(i.num*g.price) as total
from orders o
join order_items i on o.oid=i.oid
join goods g on i.gid=g.gid
group by o.oid
order by total desc
limit 1;

select distinct u.uname
from users u
join orders o on u.uid=o.uid
join order_items i on o.oid=i.oid
join goods g on i.gid=g.gid
where g.gname='显示器';

-- （可选）订单表上的部门/员工若在其他业务库，会用类似 JOIN；本篇 dept 表供后续扩展
-- select d.dname, count(*) from dept d group by d.dname;

select u.uid, u.uname,
       count(distinct o.oid) as 订单数,
       ifnull(sum(i.num*g.price),0) as 累计金额
from users u
left join orders o on u.uid=o.uid
left join order_items i on o.oid=i.oid
left join goods g on i.gid=g.gid
group by u.uid, u.uname
order by 累计金额 desc;

累计金额：若某用户无订单，sum 可能为 NULL，可用 ifnull(sum(...),0)。

---

第二部分 · 高频面试问答（入门向）

Q1 char 和 varchar 有什么区别？

	char(n)	varchar(n)
长度	定长	变长
空间	不足 n 也按 n 约预留	按实际使用（上限 n）
适合	长度几乎固定（如性别 1 位）	长度变化大（姓名、标题）

例：存「张三」，char(10) 仍按 10 字符位置考虑；varchar(10) 更省空间。

Q2 MySQL 里 char 和 varchar 的 n 是什么？

一般是字符个数（不是字节数）。中文能否存多个，还与列编码（如 utf8mb4）和实际版本有关；入门按「最多 n 个字符」理解即可。

Q3 NULL 和空字符串 '' 一样吗？

不一样。
'' 是有值、内容为空；NULL 是没有值。
判断要用 is null / is not null，不能写 = null 找空值。

Q4 delete、truncate、drop 有什么区别？

语句	数据	表结构
delete from t where ...	可按条件删行	保留
truncate table t	清空所有行	保留
drop table t	没了	删除表

Q5 where 和 having 有什么区别？

• where：分组之前过滤原始行，不能放聚合条件
• having：分组之后过滤，可以写 count(*)>3 这类聚合条件

Q6 内连接和左连接？

• join / inner join：只保留两边都匹配的行
• left join：左表全保留，右表匹配不上补 NULL

Q7 order by 和 limit？

• order by 决定顺序（asc/desc）
• limit 决定取结果集中哪几行（分页/TOP-N）
• 稳定分页要 先排序再 limit

Q8 主键和唯一约束？

• 主键：非空 + 唯一，一张表通常一个
• unique：不能重复；多个 NULL 通常仍允许

Q9 为什么要子查询？

条件里的值不确定、要现算（如最低工资、公司平均工资）时，先内层查询，再当外层条件。

Q10 为什么要有外键？增删要注意什么？

保证多表引用关系一致。
顺序口诀：先主后从建表与插入；删除则先从后主。

Q11 文字乱码怎么办？

新项目优先 utf8mb4；程序与库编码要一致；客户端/终端编码也要能显示中文。

Q12 什么是 SQL 执行的大致顺序？

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

（本系列未展开优化器细节，入门记这个骨架即可。）

---

系列技能自检清单

能力	对应篇目	自测
装好 MySQL 并登录	02–03	☐
建库建表、改结构	04–05	☐
INSERT/UPDATE/DELETE	06	☐
SELECT / WHERE / LIKE	07–08	☐
字符串与日期函数	09–10	☐
聚合 / GROUP BY / HAVING	11	☐
ORDER BY / LIMIT	12	☐
子查询	13	☐
JOIN / 外连接	14	☐
约束与外键顺序	15	☐
mysqldump 备份还原	16	☐

全部能独立写出来，入门阶段就比较扎实了。

速查表（项目常用）

场景	常用写法
主从表数据	A join B on A.id=B.xxx
保留无订单用户	users left join orders ... where oid is null
订单金额	sum(num*price) + group by oid
TOP-N	order by ... desc limit N
先备份再改数据	mysqldump 导出 .sql

今天的练习清单

• 完整执行 A～C 建库
• 不看答案做完 D1–D5
• 对照 E，标出自己漏掉的条件/连接
• 口头回答 Q1、Q4、Q5、Q6（不看手机）
• 用 mysqldump 把 shop 库导出一份 .sql 作为收官备份

常见错误（项目里）

易错：订单明细 JOIN 时漏 goods 或 orders，金额算错。
易错：group by 后 select 了未分组的明细列。
易错：left join 后又在 where 里对右表列写死等值，把 NULL 行滤掉。
易错：外键插入顺序颠倒。
易错：修改/删除前不先 select 确认。

小结与结语

1. 用 小项目 把建表、约束、增删改查、函数、分组、JOIN、备份串起来。
2. 面试入门题重在概念清楚 + 能写最小示例，不必死背长篇理论。
3. 本系列未深入：视图、事务、索引、权限、复杂存储过程等——工作前按需要再补。
4. 建议收藏 总目录，隔两周回来重做 D5、D6 与口答题。

感谢阅读《MySQL 入门到查询》全系列。练习时仍以你本机 shop/train 库为准，重要数据记得备份。

---

系列导航：总目录 · 上一篇：备份与实用命令