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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 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号是郭靖,结果正确,没有问题。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
关系型数据库 MySQL
mysql关联查询
mysql关联查询
18 0
|
26天前
|
存储 关系型数据库 MySQL
MySQL数据库的数据类型、语法和高级查询
MySQL数据库的数据类型、语法和高级查询
30 0
|
13天前
|
SQL 关系型数据库 MySQL
MySQL数据库—DQL查询语句(一篇教会你快速找到想要的数据)
MySQL数据库—DQL查询语句(一篇教会你快速找到想要的数据)
|
18天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之mysql迁移后查询不走索引了,该如何解决
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
20天前
|
关系型数据库 MySQL 数据库
关系型数据库MySQL开发要点之多表设计案例详解代码实现
关系型数据库MySQL开发要点之多表设计案例详解代码实现
30 2
|
26天前
|
存储 缓存 监控
MySQL 8.0中查询缓存的废弃与原因分析
MySQL 8.0中查询缓存的废弃与原因分析
39 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL周内训参照5、存储过程创建
MySQL周内训参照5、存储过程创建
31 1
|
1月前
|
存储 SQL 关系型数据库
MySQL周内训参照4、触发器-插入-修改-删除
MySQL周内训参照4、触发器-插入-修改-删除
18 1
|
10天前
|
SQL 关系型数据库 MySQL
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
Navicate,数据库,Mysql,改表,4月29日Finished - Unsuccessfully,导出数据不妨,右键,备份一下Mysql数据库的内容,你想导入和导出数据不如,用查询的方式去做
|
12天前
|
存储 关系型数据库 MySQL
【Elasticsearch】在es中实现mysql中的FIND_IN_SET查询条件
【Elasticsearch】在es中实现mysql中的FIND_IN_SET查询条件
18 0