2025年计算机二级MySQL数据库应用实例试题及答案

2025年计算机二级MySQL数据库应用实例试题及答案一、基本操作题（共3小题，每小题10分，共计30分）现有电商平台用户订单系统数据库`shop_order`，包含4张核心业务表，表结构定义要求如下：1.用户表`users`：`user_id`INTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID'，`user_name`VARCHAR(20)NOTNULLCOMMENT'用户名'，`phone`CHAR(11)UNIQUENOTNULLCOMMENT'手机号'，`register_time`DATETIMENOTNULLCOMMENT'注册时间'，`is_vip`TINYINTDEFAULT0COMMENT'是否为会员，0否1是'2.商品表`goods`：`goods_id`INTPRIMARYKEYAUTO_INCREMENTCOMMENT'商品ID'，`goods_name`VARCHAR(50)NOTNULLCOMMENT'商品名称'，`price`DECIMAL(10,2)NOTNULLCOMMENT'单价'，`stock`INTDEFAULT0COMMENT'库存'，`category_id`INTCOMMENT'分类ID'3.订单表`orders`：`order_id`CHAR(18)PRIMARYKEYCOMMENT'订单编号'，`user_id`INTNOTNULLCOMMENT'用户ID'，`order_time`DATETIMENOTNULLCOMMENT'下单时间'，`pay_amount`DECIMAL(10,2)NOTNULLCOMMENT'实付金额'，`order_status`TINYINTDEFAULT0COMMENT'订单状态：0待支付1已支付2已发货3已完成4已取消'，外键关联`users.user_id`4.订单明细表`order_detail`：`detail_id`INTPRIMARYKEYAUTO_INCREMENTCOMMENT'明细ID'，`order_id`CHAR(18)NOTNULLCOMMENT'订单编号'，`goods_id`INTNOTNULLCOMMENT'商品ID'，`buy_num`INTNOTNULLCOMMENT'购买数量'，`subtotal`DECIMAL(10,2)NOTNULLCOMMENT'小计金额'，外键分别关联`orders.order_id`、`goods.goods_id`完成以下操作：1.1创建`shop_order`数据库及上述4张表，要求全局字符集设置为utf8mb4，存储引擎统一为InnoDB，所有外键约束开启级联删除。1.2向4张表插入测试数据：`users`表插入3条记录：(1,'张三',,'2023-05-1012:30:00',1)、(2,'李四',,'2024-01-1509:20:00',0)、(3,'王五',,'2024-06-2016:45:00',1)；`goods`表插入2条记录：(1,'无线蓝牙耳机',199.00,100,1)、(2,'机械键盘',299.00,50,1)；`orders`表插入2条记录：('202409010001',1,'2024-09-0110:15:00',199.00,3)、('202409010002',3,'2024-09-0114:20:00',598.00,1)；`order_detail`表插入2条记录：(1,'202409010001',1,1,199.00)、(2,'202409010002',2,2,598.00)。1.3迭代表结构：在`goods`表新增`is_on_sale`TINYINTDEFAULT1COMMENT'是否上架，0下架1上架'字段；为`goods.goods_name`创建普通索引；为`orders`表的`order_time`、`user_id`字段创建联合索引，索引字段顺序为`order_time`在前，`user_id`在后。二、简单查询与数据操作题（共2小题，每小题15分，共计30分）2.1编写SQL语句完成以下查询需求：（1）查询2024年注册的会员用户的用户名、手机号、注册时间，结果按注册时间降序排列。（2）查询实付金额≥300元，且订单状态为已支付（1）或已完成（3）的订单编号、用户ID、实付金额、下单时间，实付金额保留2位小数。（3）统计每个分类ID下的商品总库存、平均单价，仅返回平均单价>100元的分类数据。2.2编写SQL语句完成以下数据更新操作：（1）将用户ID为2的用户会员状态修改为1，手机号更新为。（2）将订单编号为`202409010002`的订单状态修改为已完成（3），同时扣减对应商品ID为2的库存2件，需确保库存扣减后不小于0。（3）删除2023年及之前注册的非会员用户的所有信息，包含其关联的订单、订单明细数据。三、复杂多表查询题（共1小题，共计20分）基于`shop_order`数据库的4张表，完成以下查询需求：3.1查询所有已完成订单的全量明细信息，返回字段包括：订单编号、用户名、用户手机号、下单时间、实付金额、商品名称、购买数量、商品单价、小计金额，结果按下单时间降序排列，相同下单时间按订单编号升序排列。（7分）3.2统计2024年第三季度（7月1日-9月30日）所有会员用户的消费总金额、下单次数、购买商品总数量，仅返回消费总金额≥500元的用户数据，结果按消费总金额降序排列。（8分）3.3查询从未产生过订单的用户的用户名、手机号、注册时间。（5分）四、高级数据库对象应用题（共1小题，共计20分）4.1创建存储过程`proc_get_user_order_stat`，接收输入参数`p_user_idINT`，输出参数`p_total_amountDECIMAL(10,2)`、`p_order_countINT`，功能为统计指定用户的累计消费总金额、累计下单次数，若用户不存在则两个输出参数均返回NULL。（8分）4.2创建触发器`trg_order_detail_stock_check`，功能为在`order_detail`表插入新记录前，自动校验对应商品的库存是否足够，若库存<购买数量则抛出错误终止插入，若库存足够则自动扣减对应商品的库存。（7分）4.3创建视图`view_vip_order_info`，仅展示会员用户的已完成订单数据，返回字段包括订单编号、用户名、下单时间、实付金额，要求视图为只读不可更新，查询时不继承底层表的索引提示。（5分）五、综合设计与应用题（共1小题，共计30分）现有企业员工考勤系统的业务需求，完成以下数据库设计与功能实现：5.1设计3张核心表：（12分）（1）员工表`emp`：字段包括员工ID（主键自增）、员工姓名（非空）、部门ID、手机号（唯一非空）、入职时间（非空）、员工状态（0离职1在职，默认1）（2）部门表`dept`：字段包括部门ID（主键自增）、部门名称（唯一非空）、部门负责人ID（关联员工表的员工ID）、创建时间（非空）（3）考勤记录表`attendance`：字段包括记录ID（主键自增）、员工ID（关联员工表）、考勤日期（DATE类型，非空）、打卡时间（DATETIME类型）、考勤状态（0正常1迟到2早退3旷工，默认0），要求同一员工同一考勤日期仅能存在一条记录。5.2编写SQL语句查询2025年8月所有迟到次数≥3次的在职员工的姓名、所属部门名称、迟到次数，结果按迟到次数降序排列。（10分）5.3编写SQL语句统计2025年8月每个部门的员工平均出勤率，出勤率计算公式为：部门所有在职员工的正常考勤次数总和/（部门在职员工人数*当月工作日天数）*100%，2025年8月工作日为22天，离职员工不计入统计，结果保留2位小数，按出勤率降序排列。（8分）参考答案一、基本操作题参考答案1.1实现代码：```sqlCREATEDATABASEIFNOTEXISTSshop_orderDEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci;USEshop_order;SETFOREIGN_KEY_CHECKS=1;-创建用户表CREATETABLEIFNOTEXISTSusers(user_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',user_nameVARCHAR(20)NOTNULLCOMMENT'用户名',phoneCHAR(11)UNIQUENOTNULLCOMMENT'手机号',register_timeDATETIMENOTNULLCOMMENT'注册时间',is_vipTINYINTDEFAULT0COMMENT'是否为会员，0否1是')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'用户表';-创建商品表CREATETABLEIFNOTEXISTSgoods(goods_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'商品ID',goods_nameVARCHAR(50)NOTNULLCOMMENT'商品名称',priceDECIMAL(10,2)NOTNULLCOMMENT'单价',stockINTDEFAULT0COMMENT'库存',category_idINTCOMMENT'分类ID')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'商品表';-创建订单表CREATETABLEIFNOTEXISTSorders(order_idCHAR(18)PRIMARYKEYCOMMENT'订单编号',user_idINTNOTNULLCOMMENT'用户ID',order_timeDATETIMENOTNULLCOMMENT'下单时间',pay_amountDECIMAL(10,2)NOTNULLCOMMENT'实付金额',order_statusTINYINTDEFAULT0COMMENT'订单状态：0待支付1已支付2已发货3已完成4已取消',FOREIGNKEY(user_id)REFERENCESusers(user_id)ONDELETECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'订单表';-创建订单明细表CREATETABLEIFNOTEXISTSorder_detail(detail_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'明细ID',order_idCHAR(18)NOTNULLCOMMENT'订单编号',goods_idINTNOTNULLCOMMENT'商品ID',buy_numINTNOTNULLCOMMENT'购买数量',subtotalDECIMAL(10,2)NOTNULLCOMMENT'小计金额',FOREIGNKEY(order_id)REFERENCESorders(order_id)ONDELETECASCADE,FOREIGNKEY(goods_id)REFERENCESgoods(goods_id)ONDELETECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'订单明细表';```评分要点：数据库字符集、存储引擎设置正确得3分，4张表字段约束、外键级联删除设置正确各得1.75分，总计10分。1.2实现代码：```sql-插入用户数据INSERTINTOusers(user_id,user_name,phone,register_time,is_vip)VALUES(1,'张三',,'2023-05-1012:30:00',1),(2,'李四',,'2024-01-1509:20:00',0),(3,'王五',,'2024-06-2016:45:00',1);-插入商品数据INSERTINTOgoods(goods_id,goods_name,price,stock,category_id)VALUES(1,'无线蓝牙耳机',199.00,100,1),(2,'机械键盘',299.00,50,1);-插入订单数据INSERTINTOorders(order_id,user_id,order_time,pay_amount,order_status)VALUES('202409010001',1,'2024-09-0110:15:00',199.00,3),('202409010002',3,'2024-09-0114:20:00',598.00,1);-插入订单明细数据INSERTINTOorder_detail(detail_id,order_id,goods_id,buy_num,subtotal)VALUES(1,'202409010001',1,1,199.00),(2,'202409010002',2,2,598.00);```评分要点：4张表插入数据字段对应、语法正确各得2.5分，总计10分。1.3实现代码：```sql-新增上架状态字段ALTERTABLEgoodsADDCOLUMNis_on_saleTINYINTDEFAULT1COMMENT'是否上架，0下架1上架';-创建商品名称普通索引CREATEINDEXidx_goods_nameONgoods(goods_name);-创建订单时间+用户ID联合索引CREATEINDEXidx_order_time_useridONorders(order_time,user_id);```评分要点：新增字段语法正确得3分，两个索引创建字段顺序、语法正确各得3.5分，总计10分。二、简单查询与数据操作题参考答案2.1各小题实现代码：（1）```sqlSELECTuser_name,phone,register_timeFROMusersWHEREis_vip=1ANDYEAR(register_time)=2024ORDERBYregister_timeDESC;```评分要点：筛选条件正确得3分，排序规则正确得2分，共5分。（2）```sqlSELECTorder_id,user_id,ROUND(pay_amount,2)ASpay_amount,order_timeFROMordersWHEREpay_amount>=300ANDorder_statusIN(1,3);```评分要点：筛选条件正确得3分，小数精度处理正确得2分，共5分。（3）```sqlSELECTcategory_id,SUM(stock)AStotal_stock,AVG(price)ASavg_priceFROMgoodsGROUPBYcategory_idHAVINGavg_price>100;```评分要点：分组聚合逻辑正确得3分，HAVING筛选正确得2分，共5分。2.2各小题实现代码：（1）```sqlUPDATEusersSETis_vip=1,phone=WHEREuser_id=2;```评分要点：更新字段正确得3分，筛选条件正确得2分，共5分。（2）```sqlSTARTTRANSACTION;-更新订单状态UPDATEordersSETorder_status=3WHEREorder_id='202409010002';-扣减库存（自带非负校验）UPDATEgoodsSETstock=stock-2WHEREgoods_id=2ANDstock>=2;-判断更新结果，若库存不足则回滚，否则提交IFROW_COUNT()=1THENCOMMIT;ELSEROLLBACK;SIGNALSQLSTATE'45000'SETMESSAGE_TEXT='库存不足，更新失败';ENDIF;```评分要点：订单状态更新正确得2分，库存扣减校验逻辑正确得3分，共5分。（3）```sqlDELETEFROMusersWHEREYEAR(register_time)<=2023ANDis_vip=0;```评分要点：筛选条件正确得3分，利用外键级联删除关联数据得2分，共5分。三、复杂多表查询题参考答案3.1实现代码：```sqlSELECTo.order_id,u.user_name,u.phone,o.order_time,o.pay_amount,g.goods_name,od.buy_num,g.price,od.subtotalFROMordersoINNERJOINusersuONo.user_id=u.user_idINNERJOINorder_detailodONo.order_id=od.order_idINNERJOINgoodsgONod.goods_id=g.goods_idWHEREo.order_status=3ORDERBYo.order_timeDESC,o.order_idASC;```评分要点：多表关联逻辑正确得4分，筛选条件正确得2分，排序规则正确得1分，共7分。3.2实现代码：```sqlSELECTu.user_id,u.user_name,SUM(o.pay_amount)AStotal_amount,COUNT(DISTINCTo.order_id)ASorder_count,SUM(od.buy_num)AStotal_goods_numFROMusersuLEFTJOINordersoONu.user_id=o.user_idANDo.order_timeBETWEEN'2024-07-0100:00:00'AND'2024-09-3023:59:59'LEFTJOINorder_detailodONo.order_id=od.order_idWHEREu.is_vip=1GROUPBYu.user_idHAVINGtotal_amount>=500ORDERBYtotal_amountDESC;```评分要点：时间范围筛选正确得2分，聚合函数（订单去重计数）逻辑正确得3分，分组与筛选正确得2分，排序正确得1分，共8分。3.3实现代码（两种方法均可）：```sql-方法1：左连接空值筛选SELECTu.user_name,u.phone,u.register_timeFROMusersuLEFTJOINordersoONu.user_id=o.user_idWHEREo.order_idISNULL;-方法2：NOTEXISTS子查询SELECTuser_name,phone,register_timeFROMusersuWHERENOTEXISTS(SELECT1FROMordersoWHEREo.user_id=u.user_id);```评分要点：逻辑正确即可得5分，两种方法均正确额外加1分，本题满分5分。四、高级数据库对象应用题参考答案4.1存储过程实现代码：```sqlDELIMITER//CREATEPROCEDUREproc_get_user_order_stat(INp_user_idINT,OUTp_total_amountDECIMAL(10,2),OUTp_order_countINT)BEGINDECLAREuser_existINTDEFAULT0;-校验用户是否存在SELECTCOUNT(1)INTOuser_existFROMusersWHEREuser_id=p_user_id;IFuser_exist=0THENSETp_total_amount=NULL;SETp_order_count=NULL;ELSE-统计用户订单数据SELECTIFNULL(SUM(pay_amount),0),IFNULL(COUNT(order_id),0)INTOp_total_amount,p_order_countFROMordersWHEREuser_id=p_user_id;ENDIF;END//DELIMITER;```评分要点：参数定义正确得2分，用户存在性校验逻辑正确得3分，统计逻辑与返回值处理正确得3分，共8分。4.2触发器实现代码：```sqlDELIMITER//CREATETRIGGERtrg_order_detail_stock_checkBEFOREINSERTONorder_detailFOREACHROWBEGINDECLAREcurrent_stockINTDEFAULT0;-查询当前商品库存SELECTstockINTOcurrent_stockFROMgoodsWHEREgoods_id=NEW.goods_id;-库存校验IFcurrent_stock<NEW.buy_numTHENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='商品库存不足，无法创建订单明细';ELSE-扣减库存UPDATEgoodsSETstock=stock-NEW.buy_numWHEREgoods_id=NEW.goods_id;ENDIF;END//DELIMITER;```评分要点：触发器触发时机（BEFOREINSERT）正确得2分，库存校验逻辑正确得3分，错误抛出语法正确得2分，共7分。4.3视图实现代码：```sql-ALGORITHM=TEMPTABLE使用临时表存储视图结果，天然不可更新，WITHREADONLY显式设置只读CREATEALGORITHM=TEMPTABLEVIEWview_vip_order_infoASSELECTo.order_id,u.user_name,o.order_time,o.pay_amountFROMordersoINNERJOINusersuONo.user_id=u.user_idWHEREu.is_vip=1ANDo.order_status=3WITHREADONLY;```评分要点：视图筛选条件正确得2分，只读不可更新设置正确得2分，属性配置符合要求得1分，共5分。五、综合设计与应用题参考答案5.1建表实现代码：```sqlCREATEDATABASEIFNOTEXISTSemp_attendanceDEFAULTCHARSETutf8mb4;USEemp_attendance;-创建员工表CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'员工ID',emp_nameVARCHAR(20)NOTNULLCOMMENT'员工姓名',dept_idINTCOMMENT'部门ID',phoneCHAR(11)UNIQUENOTNULLCOMMENT'手机号',hire_dateDATENOTNULLCOMMENT'入职时间',statusTINYINTDEFAULT1COMMENT'状态：0离职1在职')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'员工表';-创建部门表CREATETABLEdept(dept_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'部门ID',dept_nameVARCHAR(30)UNIQUENOTNULLCOMMENT'部门名称',manager_idINTCOMMENT'部门负责人ID',create_timeDATETIMENOTNULLCOMMENT'创建时间',FOREIGNKEY(manager_id)REFERENCESemp(emp_id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'部门表';-创建考勤记录表CREATETABLEattendance(record_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'记录ID',emp_idINTNOTNULLCOMMENT'员工ID',attendance_dateDATENOTNULLCOMMENT'考勤日期',clock_timeDATE