MySQL 笔试题及参考答案.docxVIP

MySQL笔试题及参考答案

一、基础语法题（每题10分，共30分）

1.表结构设计与数据操作

现有需求：设计一张「用户订单表（order_info）」，需包含以下字段：订单ID、用户ID、订单金额、下单时间、订单状态（待支付/已支付/已取消）、支付方式（微信/支付宝/银行卡），要求：

订单ID为主键，自增

订单状态和支付方式需限制可选值

下单时间默认取当前时间

订单金额非负

（1）写出建表SQL语句；

（2）插入一条「用户ID=1001，金额99.9元，已支付，微信支付」的订单数据；

（3）查询2023年1月1日后下单的所有已支付订单，按金额降序排列。

2.关联查询

现有两张表：

商品表（product）：id（主键）、name（商品名）、price（单价）、stock（库存）

订单明细表（order_item）：id（主键）、order_id（订单ID）、product_id（商品ID）、quantity（购买数量）

查询所有订单中，购买了「iPhone14」且数量≥2的订单ID、商品名、购买数量及订单总金额（单价×数量）。

3.聚合与分组

基于上述order_info表，统计每个用户的以下信息：用户ID、订单总数、已支付订单数、总消费金额，仅显示总消费金额≥1000元的用户。

二、索引与性能优化题（每题15分，共30分）

1.索引设计

针对order_info表的以下查询场景，设计合理的索引（说明索引类型和字段），并解释原因：

（1）高频查询：根据用户ID查询该用户的所有订单；

（2）统计查询：按订单状态分组统计订单数；

（3）范围查询：查询某个时间段内的已支付订单。

2.SQL优化

以下SQL语句存在性能问题，请指出问题并优化：

SELECTo.id,o.user_id,p.name

FROMorder_infoo

LEFTJOINproductpONo.product_id=p.id

WHEREo.create_time2023-01-01

ANDo.status=已支付

ORDERBYo.amountDESC;

（注：order_info表数据量100万+，product表数据量1万+）

三、事务与锁机制题（每题15分，共30分）

1.事务隔离级别

（1）MySQL默认的事务隔离级别是什么？该级别会解决哪些并发问题，存在什么问题？

（2）如果需要避免「不可重复读」，应选择哪个隔离级别？该级别如何实现？

2.锁问题分析

现有两个事务同时执行：

--事务A

BEGIN;

UPDATEorder_infoSETstatus=已取消WHEREid=100;

--未提交

--事务B

BEGIN;

UPDATEorder_infoSETstatus=已支付WHEREid=100;

（1）事务B会处于什么状态？为什么？

（2）如果事务A执行后立即提交，事务B的执行结果是什么？

（3）如果将WHERE条件改为「user_id=100」（假设该用户有10条订单），事务B是否会阻塞？为什么？

四、综合应用题（10分）

现有一个电商场景：用户下单后，需要执行以下操作：

插入订单记录到order_info表；

插入订单明细到order_item表；

扣减对应商品的库存（product表stock字段）。

请写出完整的SQL逻辑（包含事务处理），要求：

库存不足时无法下单；

三个操作要么同时成功，要么同时失败；

避免超卖问题。

参考答案

一、基础语法题

1.表结构设计与数据操作

（1）建表SQL：

CREATETABLEorder_info(

idINTPRIMARYKEYAUTO_INCREMENTCOMMENT订单ID,

user_idINTNOTNULLCOMMENT用户ID,

amountDECIMAL(10,2)NOTNULLCHECK(amount=0)COMMENT订单金额,

create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT下单时间,

statusENUM(待支付,已支付,已取消)NOTNULLCOMMENT订单状态,

pay_typeENUM(微信,支付宝,银行卡)COMMENT支付方式

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4C