MySQL周内训参照3、简单查询与多表联合复杂查询
编号 | 人员 | 题目 | 总分数 | 题干 | 提交内容 | 得分标准 |
5 | DBA | 基础查询 | 10 | SQL要求: 1、查询用户信息,仅显示用户的姓名与手机号,用中文显示列名。 2、根据商品名称进行模糊查询,模糊查询需要可以走索引,需要给出explain语句。 3、统计用户订单信息,查询所有用户的下单数量,并进行倒序排列。 | 提交3条sql与对应的结果截图 | 1、中文显示姓名列与手机号列(2分) 2、使用explain测试给出的查询语句,需要显示走了索引查询。(3分) 3、使用聚合函数查询处所有用户的订单数量(2分),倒序排列结果(3分),(共5分)。 |
6 | DBA | 复杂查询 | 15 | 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相同
对应7号是郭靖,结果正确,没有问题。
原文地址:https://blog.csdn.net/feng8403000/article/details/139567319
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!