MySQL周内训参照3、简单查询与多表联合复杂查询

07-02 1198阅读

编号

人员

题目

总分数

题干

提交内容

得分标准

DBA

基础查询

SQL要求：

1、查询用户信息，仅显示用户的姓名与手机号，用中文显示列名。

2、根据商品名称进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。

3、统计用户订单信息，查询所有用户的下单数量，并进行倒序排列。

提交3条sql与对应的结果截图

1、中文显示姓名列与手机号列（2分）

2、使用explain测试给出的查询语句，需要显示走了索引查询。(3分)

3、使用聚合函数查询处所有用户的订单数量(2分)，倒序排列结果(3分)，(共5分)。

DBA

复杂查询

SQL要求：

1、查询用户的基本信息，钱包信息。

2、查看订单中下单最多的产品对应的类别。

3、查询下单总金额最多的用户，并查询用户的全部信息与当前钱包余额。

提交3条sql与对应的结果截图

1、正确显示用户信息(1分)，正确显示用户钱包信息(1分)，正确进行多表联合查询(2分)（共4分）

2、正确使用聚合函数(2分)，正确使用子查询(2分)，正确显示结果(1分)，（共5分）

3、正确使用聚合函数(2分)，正确使用子查询(2分)，正确进行多表联合查询(2)（共6分）

基础查询

1、查询用户信息，仅显示用户的姓名与手机号，用中文显示列名。中文显示姓名列与手机号列

SELECT username AS '姓名', phone AS '手机号' FROM user;

2、根据商品名称进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。使用explain测试给出的查询语句，需要显示走了索引查询。

CREATE INDEX idx_product_name ON product(product_name);

不能用模糊查询的符号作为查询的开头，否则不走索引。

EXPLAIN SELECT * FROM product WHERE product_name LIKE '关键词%';

可以看到已经走了索引了。

3、统计用户订单信息，查询所有用户的下单数量，并进行倒序排列。使用聚合函数查询处所有用户的订单数量(2分)，倒序排列结果(3分)，(共5分)。

SELECT user_id, COUNT(order_id) AS '订单数量'
FROM `order`
GROUP BY user_id
ORDER BY `订单数量` DESC;

order表共计10条数据，结果正确。

复杂查询

1、查询用户的基本信息，钱包信息。正确显示用户信息(1分)，正确显示用户钱包信息(1分)，正确进行多表联合查询(2分)（共4分）

SELECT 
    u.user_id, -- 选择用户的用户ID
    u.username, -- 选择用户名
    u.email, -- 选择邮箱
    u.phone, -- 选择电话
    uw.wallet_id, -- 选择钱包ID
    uw.balance -- 选择钱包余额
FROM 
    user u -- 从用户表中选择数据
JOIN 
    user_wallet uw ON u.user_id = uw.user_id; -- 使用JOIN连接用户表和钱包表，连接条件是两个表中的user_id相同

2、查看订单中下单最多的产品对应的类别。正确使用聚合函数(2分)，正确使用子查询(2分)，正确显示结果(1分)，（共5分）

基础写法：

select type_name '下单最多的产品类别' 
from product p 
INNER join 
product_type pt 
on p.type_id=pt.type_id 
where 
product_id =
(select 
product_id 
from order_info 
GROUP BY product_id 
ORDER BY count(product_id) desc 
limit 1);

标准写法：

SELECT 
    pt.type_name -- 选择产品类型名称
FROM 
    product p -- 从产品表中选择数据
JOIN 
    product_type pt ON p.type_id = pt.type_id -- 使用JOIN连接产品表和产品类型表，连接条件是产品表中的type_id与产品类型表中的type_id相同
JOIN 
    (SELECT 
         product_id, -- 子查询中选择产品ID
         COUNT(order_id) AS order_count -- 子查询中对每个产品ID的订单ID进行计数，并命名为order_count
     FROM 
         order_info -- 子查询从订单详情表中选择数据
     GROUP BY 
         product_id -- 按产品ID进行分组
     ORDER BY 
         order_count DESC -- 按订单数量降序排列
     LIMIT 1) oi ON p.product_id = oi.product_id; -- 子查询的结果作为临时表oi，与产品表通过product_id进行连接

由于订单详情中能看到产品id2的数量是最多的，产品2的类型就是笔记本电脑，结果正确。

3、查询下单总金额最多的用户，并查询用户的全部信息与当前钱包余额。正确使用聚合函数(2分)，正确使用子查询(2分)，正确进行多表联合查询(2)（共6分）

基础写法：

select * 
from `user` 
join 
user_wallet wu 
on `user`.user_id=wu.user_id 
where 
wu.user_id=
(
select user_id 
from `order` 
group by user_id 
ORDER BY sum(total_price) desc 
limit 1
);

标准写法：

SELECT 
    u.*, -- 选择用户的所有信息
    uw.wallet_id, -- 选择钱包ID
    uw.balance -- 选择钱包余额
FROM 
    user u -- 从用户表中选择数据
JOIN 
    (SELECT 
         user_id, -- 子查询中选择用户ID
         SUM(total_price) AS total_spent -- 子查询中对每个用户的订单总金额进行求和，并命名为total_spent
     FROM 
         `order` -- 子查询从订单表中选择数据
     GROUP BY 
         user_id -- 按用户ID进行分组
     ORDER BY 
         total_spent DESC -- 按总消费金额降序排列
     LIMIT 1) o ON u.user_id = o.user_id -- 子查询的结果作为临时表o，与用户表通过user_id进行连接
JOIN 
    user_wallet uw ON u.user_id = uw.user_id; -- 使用JOIN连接用户表和钱包表，连接条件是两个表中的user_id相同