Mysql基础查询语句练习题——Northwind 34题参考答案

# 这里省去导入数据的过程,数据源在网上下载即可
# 以下代码在datagrip中编辑运行
show databases ;
use north_wind;
select database();
show tables;

-- 需求1: 选中employees 表的所有数据
select * from employees;

-- 需求2: 查询每个客户的 ID, company name, contact name, contact title, city, 和 country.并按照国家名字排序
# 默认升序排列(缺省,asc)
# 降序排列,desc
select customer_id, company_name, contact_name, contact_title, city, country
    from customers
    order by country;

-- 替换快捷键: ctrl + 字母R
-- 需求3: 查询每一个商品的product_name, category_name, quantity_per_unit, unit_price, units_in_stock 并且通过 unit_price 字段排序
# 连接products表和categories表
-- 方式1: 显式内连接
select product_name, category_name, quantity_per_unit, unit_price, units_in_stock
    from products p
    join categories c on p.category_id = c.category_id
    order by unit_price;

-- 方式2: 隐式内连接.
# 不能单独使用 select * from A,B 后面没有where条件时会出错
select product_name, category_name, quantity_per_unit, unit_price, units_in_stock
    from products p, categories c
    where p.category_id = c.category_id
    order by unit_price;

-- 需求4: 列出所有提供了4种以上不同商品的供应商;列表所需字段:supplier_id, company_name, and products_count (提供的商品种类数量).
# 4.1 需要连接products表和suppliers表
# 4.2 聚合查询count(*)
# 4.3 group by...having...
select s.supplier_id, company_name, count(*) products_count
    from suppliers s
    join products p on s.supplier_id = p.supplier_id
    group by supplier_id
    having products_count > 4;
# 语句中 count(*)也可写作count(product_id),效果相同

在写需求4的代码时,这里测试了一下count(字段)和count(*)的区别,发现二者结果相同。查了一下,MySQL使用 InnoDB引擎,不加where条件时,count(主键)(这道题中supplier_id为主键)和count(*)的处理方式是一样的。其他的情况,在下面的文章,博主也写的很清楚,供参考:

深度剖析:MySQL count() 函数,这下彻底明白了! - 知乎 (zhihu.com)icon-default.png?t=N7T8https://zhuanlan.zhihu.com/p/572387666

-- 需求5: 提取订单编号为10250的订单详情, 显示如下信息:
-- product_name, quantity, unit_price ( order_items 表), discount , order_date 按商品名字排序
# 需要连接orders,order_items,products三张表
select product_name, quantity, oi.unit_price, discount, order_date
    from products p
    join order_items oi on p.product_id = oi.product_id
    join orders o on oi.order_id = o.order_id
    where o.order_id = 10250;

-- 需求6: 收集运输到法国的订单的相关信息,包括订单涉及的顾客和员工信息,下单和发货日期等.
# 需要连接orders和customers,employees三张表
select *
    from orders o
    join customers c on o.customer_id = c.customer_id
    join employees e on o.employee_id = e.employee_id
    where ship_country = 'France';

-- 需求7: 提供订单编号为10248的相关信息,包括product name, unit price (在 order_items 表中), quantity(数量),company_name(供应商公司名字 ,起别名 supplier_name).
# 需要连接suppliers,order_items,products三张表
select product_name, quantity, oi.unit_price, company_name supplier_name
    from products p
    join order_items oi on p.product_id = oi.product_id
    join suppliers s on p.supplier_id = s.supplier_id
    where order_id  = 10248;

-- 需求8:  提取每件商品的详细信息,包括 商品名称(product_name), 供应商的公司名称 (company_name,在 suppliers 表中),
-- 类别名称 category_name, 商品单价unit_price, 和每单位商品数量quantity per unit
# 需要连接suppliers,categories,products三张表
select product_name, company_name, category_name, unit_price, quantity_per_unit
    from products p
    join suppliers s on p.supplier_id = s.supplier_id
    join categories c on p.category_id = c.category_id;

-- 需求9: 另一种常见的报表需求是查询某段时间内的业务指标, 我们统计2016年7月的订单数量,
select count(*)
    from orders
    where order_date >= '2016-07-01 00:00:00' and order_date <= '2016-07-31 00:00:00';
# 或者使用between...and...函数
select count(*)
    from orders
    where order_date between '2016-07-01 00:00:00' and '2016-07-31 00:00:00';

-- 需求11: 统计每个供应商供应的商品种类数量, 结果返回供应商ID:supplier_id,
-- 公司名字company_name ,商品种类数量(起别名products_count )
# 使用 products 和 suppliers 表
select s.supplier_id, company_name, count(*) products_count
    from products p
    join suppliers s on p.supplier_id = s.supplier_id
    group by s.supplier_id;

-- 需求12: 我们要查找ID为10250的订单的总价(折扣前),SUM(unit_price * quantity)
select sum(unit_price * quantity)
    from order_items
    where order_id = 10250;

-- 需求13:  统计每个员工处理的订单总数, 结果包含员工ID:employee_id,姓名first_name 和 last_name,处理的订单总数(别名 orders_count)
select e.employee_id, first_name, last_name, count(*) orders_count
    from employees e
    join orders o on e.employee_id = o.employee_id
    group by employee_id;

-- 需求14: 统计每个类别中的库存产品值多少钱?显示三列:category_id, category_name, 和 category_total_value,
# 如何计算库存商品总价:SUM(unit_price * units_in_stock)
select c.category_id, category_name, sum(unit_price * units_in_stock) category_total_value
    from products p
    join categories c on p.category_id = c.category_id
    group by c.category_id;

-- 需求15: 计算每个员工的订单数量
select e.employee_id, count(*) orders_count
    from employees e
    join orders o on e.employee_id = o.employee_id
    group by employee_id;

-- 需求16: 计算每个客户的下订单数 结果包含:用户id、用户公司名称、订单数量(customer_id, company_name, orders_count )
select c.customer_id, company_name, count(*) orders_count
    from customers c
    join orders o on c.customer_id = o.customer_id
    group by c.customer_id;

-- 需求17: 统计2016年6月到2016年7月用户的总下单金额并按金额从高到低排序
-- 结果包含:顾客公司名称company_name 和总下单金额(折后实付金额)total_paid
-- 提示:
-- 计算实际总付款金额: SUM(unit_price * quantity * (1 - discount))
-- 日期过滤 WHERE order_date >= '2016-06-01' AND order_date < '2016-08-01'
select company_name, sum(unit_price * quantity * (1 - discount)) total_paid
    from customers c
    join orders o on c.customer_id = o.customer_id
    join order_items oi on o.order_id = oi.order_id
    where order_date >= '2016-06-01' and order_date < '2016-08-01'
    group by company_name;

-- 需求18: 统计客户总数和带有传真号码的客户数量
-- 需要字段:all_customers_count 和 customers_with_fax_count
select count(customer_id) all_customers_count, count(fax) customers_with_fax_count
    from customers;

-- 需求19: 我们要在报表中显示每种产品的库存量,但我们不想简单地将“ units_in_stock”列放在报表中。报表中只需要一个总体级别,例如低,高:
-- 库存大于100 的可用性为高(high)
-- 50到100的可用性为中等(moderate)
-- 小于50的为低(low)
-- 零库存 为 (none)
select product_id, product_name,
    case
        when units_in_stock > 100 then 'high'
        when units_in_stock between 50 and 100 then 'moderate'
        when units_in_stock < 50 and units_in_stock > 0 then 'low'
        else 'none'
    end as level
from products;

这里用到的作用类似分段函数的MySQL中函数有2种,case()函数和if()函数,具体使用哪个需要视情况而定。这里如果要用if()函数的话,涉及到if函数嵌套,比较麻烦,所以使用case()函数。

  • MySQL函数中case()函数的使用方法,下面这篇知乎的文章讲得很清楚,供参考:

https://www.zhihu.com/question/559593987/answer/2871089245icon-default.png?t=N7T8https://www.zhihu.com/question/559593987/answer/2871089245

  • MySQL函数中if函数的使用方法,与excel中该函数使用方法类似:

-- 需求20: 创建一个报表,统计员工的经验水平
-- 显示字段:first_name, last_name, hire_date, 和 experience
-- 经验字段(experience ):
-- 'junior' 2014年1月1日以后雇用的员工
-- 'middle' 在2013年1月1日之后至2014年1月1日之前雇用的员工
-- 'senior' 2013年1月1日或之前雇用的员工
select first_name, last_name, hire_date,
    case
        when hire_date > '2014-01-01' then 'junior'
        when hire_date > '2013-01-01' and hire_date <= '2014-01-01' then 'middle'
        else 'senior'
    end as experience
from employees;

-- 需求21: 我们的商店要针对北美地区的用户做促销活动:任何运送到北美地区(美国,加拿大) 的包裹免运费。
# 创建报表,查询订单编号为10720~10730 活动后的运费价格
select order_id, ship_country,
       if(ship_country in ('USA','Canada'), 0, freight) as new_freight
from orders
where order_id between 10720 and 10730;

-- 需求22: 需求:创建客户基本信息报表, 包含字段:客户id customer_id, 公司名字 company_name,
-- 所在国家 country, 使用语言language, 使用语言language 的取值按如下规则
-- Germany, Switzerland, and Austria 语言为德语 'German', 	UK, Canada, the USA, and Ireland -- 语言为英语 'English',
-- 其他所有国家 'Other'
select customer_id, company_name, country,
    case
        when country in ('Germany', 'Switzerland', 'Austria') then 'German'
        when country in ('UK', 'Canada', 'USA', 'Ireland') then 'English'
        else 'Other'
    end as language
from customers;

-- 需求23: 需求:创建报表将所有产品划分为素食和非素食两类
-- 报表中包含如下字段:产品名字 product_name, 类别名称 category_name
-- 膳食类型 diet_type:
-- 	非素食 'Non-vegetarian' 商品类别字段的值为 'Meat/Poultry' 和 'Seafood'.
-- 	素食
select product_name, category_name,
       if(category_name in ('Meat/Poultry' , 'Seafood'), 'Non-vegetarian', 'vegetarian') as diet_type
from products p
join categories c on p.category_id = c.category_id;

-- 需求24: 在引入北美地区免运费的促销策略时,我们也想知道运送到北美地区和其它国家地区的订单数量
-- 促销策略, 参见需求21的代码.
select
       count(case when ship_country in ('USA','Canada') then 0 end) as North_America,
       count(case when ship_country not in ('USA','Canada') then 1 end) as Other
    from orders;

-- 需求25: 创建报表统计供应商来自哪个大洲, 报表中包含两个字段:供应商来自哪个大洲(supplier_continent )和 供应产品种类数量(product_count)
-- 供应商来自哪个大洲(supplier_continent )包含如下取值:
-- 'North America' (供应商来自 'USA' 和 'Canada'.)
-- 'Asia' (供应商来自 'Japan' 和 'Singapore')
-- 'Other' (其它国家)
# 需要连接supplier表和products表
# 在group by后面跟case when,只有end结尾,不能用as; if同理
select
    case
        when country in ('USA', 'Canada') then 'North America'
        when country in ('Japan', 'Singapore') then 'Asia'
        else 'Other'
    end as supplier_continent,
    count(category_id) supplier_continent
from suppliers s
join products p on s.supplier_id = p.supplier_id
group by
    case
        when country in ('USA', 'Canada') then 'North America'
        when country in ('Japan', 'Singapore') then 'Asia'
        else 'Other'
    end;

-- 需求26: 需求:创建一个简单的报表来统计员工的年龄情况
-- 报表中包含如下字段
-- 年龄( age ):生日大于1980年1月1日 'young' ,其余'old'
--  员工数量 ( employee_count)
# 依旧分段函数的具体情况灵活选用case()函数和if函数
select
    if(birth_date > '1980-01-01', 'old', 'young') as age,
    count(*) employee_count
from employees
group by
    if(birth_date > '1980-01-01', 'old', 'young');

-- 需求27: 统计客户的contact_title 字段值为 'Owner' 的客户数量
-- 查询结果有两个字段:represented_by_owner 和 not_represented_by_owner
select
       count(case when contact_title = 'Owner' then 1 end) as represented_by_owner,
       count(case when contact_title != 'Owner' then 0 end) as not_represented_by_owner
from customers;

-- 需求28: Washington (WA) 是 Northwind的主要运营地区,统计有多少订单是由华盛顿地区的员工处理的,
-- 多少订单是有其它地区的员工处理的
-- 结果字段: orders_wa_employees 和 orders_not_wa_employees
select
    count(case when region = 'WA' then 1 end) as orders_wa_employees,
    count(case when region != 'WA' then 0 end) as orders_not_wa_employees
from orders o
join employees e on o.employee_id = e.employee_id;

-- 需求29: 创建报表,统计不同类别产品的库存量,将库存量分成两类 >30 和 <=30 两档分别统计数量
-- 报表包含三个字段, 类别名称 category_name, 库存充足 high_availability, 库存紧张 low_availability
-- 简化需求: 统计不同类别产品的库存量
select
    category_name,
    count(case when units_in_stock > 30 then 1 end) as high_availability,
    count(case when units_in_stock <= 30 then 0 end) as low_availability
from products p
join categories c on p.category_id = c.category_id
group by category_name;

-- 需求30: 创建报表统计运输到法国的的订单中,打折和未打折订单的总数量
-- 结果包含两个字段:full_price (原价)和 discounted_price(打折)
# 子查询:先筛选出运输到法国的订单打折情况
select
    count(case when discount = 0 then  true end) discounted_price,
    count(case when discount != 0 then  false end) full_price
from
    (select discount
    from orders o, order_items oi
    where ship_country = 'France' and o.order_id = oi.order_id) a;

-- 需求31: 输出报表,统计不同供应商供应商品的总库存量,以及高价值商品的库存量(单价超过40定义为高价值)
-- 结果显示四列:
-- 供应商ID supplier_id
-- 供应商公司名 company_name
-- 由该供应商提供的总库存 all_units
-- 由该供应商提供的高价值商品库存 expensive_units
select s.supplier_id, company_name,
       count(*) all_units,
       count(case when unit_price > 40 then 1 end) expensive_units
from suppliers s
join products p on s.supplier_id = p.supplier_id
group by s.supplier_id;

-- 需求32: 创建报表来为每种商品添加价格标签,贵、中等、便宜
-- 结果包含如下字段:product_id, product_name, unit_price, 和 price_level
-- 价格等级price_level的取值说明:
-- 'expensive' 单价高于100的产品
-- 'average' 单价高于40但不超过100的产品
-- 'cheap' 其他产品
select product_id, product_name, unit_price,
    case
        when unit_price > 100 then 'expensive'
        when unit_price >40 and unit_price <= 100 then 'average'
        else 'cheap'
    end as price_level
from products;

-- 需求33: 制作报表统计所有订单的总价(不计任何折扣)对它们进行分类。
-- 包含以下字段:
-- 	order_id
-- 	total_price(折扣前)
-- 	price_group
-- 字段 price_group 取值说明:
-- 	'high' 总价超过2000美元
-- 	'average',总价在$ 600到$ 2,000之间,包括两端
-- 	'low' 总价低于$ 600
select order_id, sum(unit_price * quantity) total_price,
    case
        when sum(unit_price * quantity) > 2000 then 'high'
        when sum(unit_price * quantity) between 600 and 2000 then 'average'
        else 'low'
    end as price_group
from order_items
group by order_id;

-- 需求34: 统计所有订单的运费,将运费高低分为三档
-- 报表中包含三个字段
-- low_freight freight值小于“ 40.0”的订单数
-- avg_freight freight值大于或等于“ 40.0”但小于“ 80.0”的订单数
-- high_freight freight值大于或等于“ 80.0”的订单数
select
    count(case when freight < 40.0 then 0 end) as low_freight,
    count(case when freight >= 40.0 and freight < 80.0 then 1 end) as avg_freight,
    count(case when freight >= 80.0 then 2 end) as high_freight
from orders;

  • 5
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值