牛客SQL-必知必会

01 检索数据

SQL60 从 Customers 表中检索所有的 ID

select cust_id from Customers  

SQL61 检索并列出已订购产品的清单

两种去重方法：

-- 方法一 去重
select distinct prod_id 
from OrderItems

-- 方法二 分组
select prod_id 
from OrderItems 
group by prod_id

SQL62 检索所有列

select * 
from Customers  

02 排序检索数据

SQL63 检索顾客名称并且排序

select cust_name 
from Customers 
order by cust_name desc

SQL64 对顾客ID和日期排序

select cust_id,order_num
from Orders
order by cust_id,order_date desc

SQL65 按照数量和价格排序

select quantity,item_price 
from OrderItems 
order by quantity desc,item_price desc

SQL66 检查SQL语句

SELECT vend_name
FROM Vendors 
ORDER BY vend_name DESC;

03 过滤数据

SQL67 返回固定价格的产品

select prod_id,prod_name 
from Products 
where prod_price = 9.49 

SQL68 返回更高价格的产品

select prod_id,prod_name
from Products 
where prod_price>=9

SQL69 返回产品并且按照价格排序

select prod_name,prod_price
from Products
where prod_price >= 3 and prod_price <= 6
order by prod_price 

SQL70 返回更多的产品

select order_num 
from OrderItems 
group by order_num 
having sum(quantity) >= 100

04 高级数据过滤

SQL71 检索供应商名称

select vend_name 
from Vendors 
where vend_country = 'USA' and vend_state = 'CA'

SQL72 检索并列出已订购产品的清单

select order_num,prod_id,quantity
from OrderItems
where prod_id in ('BR01','BR02','BR03') and quantity >= 100

SQL73 返回所有价格在 3美元到 6美元之间的产品的名称和价格

select prod_name, prod_price
from Products
where prod_price >= 3 and prod_price <= 6
order by prod_price

SQL74 纠错2

where的用法在order之前

SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name 

05 用通配符进行过滤

SQL75 检索产品名称和描述（一）

select prod_name, prod_desc 
from Products 
where prod_desc like '%toy%'

SQL76 检索产品名称和描述（二）

SELECT prod_name, prod_desc 
FROM Products 
WHERE prod_desc NOT LIKE '%toy%' 
ORDER BY prod_name;

SQL77 检索产品名称和描述（三）

select prod_name,prod_desc
from Products
where prod_desc like '%toy%'
and prod_desc like '%carrots%'

SQL78 检索产品名称和描述（四）

select prod_name, prod_desc
from Products
where prod_desc like '%toy%carrots%'

06 创建计算字段

SQL79 别名

select vend_id,vend_name as vname,vend_address as vaddress,vend_city as vcity
from Vendors 
order by vend_name

SQL80 打折

select prod_id, prod_price, prod_price * 0.9 as sale_price 
from Products 

07 使用函数处理数据

SQL81 顾客登录名

• upper():小写转大写
• concat(s1,s2):连接两个字符串
• left(column_name,int size):取字符串左边 size个字符

select cust_id,cust_name,
upper(concat(left(cust_contact,2),left(cust_city,3)))
as user_login
from Customers;

SQL82 返回 2020 年 1 月的所有订单的订单号和订单日期

select order_num, order_date
from Orders
where year(order_date) = '2020' and month(order_date) = '1'
order by order_date

08 汇总数据

SQL83 确定已售出产品的总数

select sum(quantity) as items_ordered 
from OrderItem

SQL84 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered
from OrderItems
where prod_id = 'BR01'

SQL85 确定 Products 表中价格不超过 10 美元的最贵产品的价格

select max(prod_price) as max_price
from Products 
where prod_price <= 10

09 分组数据

SQL86 返回每个订单号各有多少行数

select order_num,count(order_num) as order_lines 
from OrderItems 
group by order_num 
order by order_lines 

SQL87 每个供应商成本最低的产品

select vend_id,min(prod_price) as cheapest_item 
from Products 
group by vend_id 
order by cheapest_item

SQL88 返回订单数量总和不小于100的所有订单的订单号

select order_num 
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num

SQL89 计算总和

select order_num, sum(quantity*item_price) as total_price
from OrderItems
group by order_num
having total_price >= 1000
order by order_num

SQL90 纠错3

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

10 使用子查询

SQL91 返回购买价格为 10 美元或以上产品的顾客列表

select cust_id 
from Orders
where order_num in(
    select order_num 
    from OrderItems 
    where item_price >= 10
)

SQL92 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

select cust_id, order_date 
from Orders
where order_num in (
    select order_num 
    from OrderItems
    where prod_id = 'BR01'
)
order by order_date;

SQL93 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

select cust_email 
from Customers
where cust_id in(
    select cust_id 
    from Orders
    where order_num in (
        select order_num 
        from OrderItems
        where prod_id='BR01'
     )
 )

SQL94 返回每个顾客不同订单的总金额

题目给的不严谨，准确的说是测试设计的不严谨，题目里面有两个order_num a0002和a0003对应着cust_id 为cust1的数据,但是测试用例设计的时候没体现这一条重复数据。所以前面好多解答都只做一次分组就可以通过测试的方法不严谨。

select
    cust_id,
    sum(q_total_ordered) as total_ordered
from
    (
        -- 聚集订单信息
        -- 订单号 订单总金额
        select
            order_num,
            sum(item_price * quantity) as q_total_ordered
        from
            OrderItems
        group by
            order_num
    ) as temp
    inner join Orders on temp.order_num = Orders.order_num
group by
    cust_id
order by
    total_ordered desc

SQL95 从 Products 表中检索所有的产品名称以及对应的销售总数

select p.prod_name,ot.quantity
from(
    select prod_id,SUM(quantity) quantity
    from OrderItems
    group by prod_id
    ) as ot,
    Products p
WHERE ot.prod_id = p.prod_id;

11 联结表

inner join on 和 where = 的区别！

WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。（其他JOIN参数也是显性连接）WHERE和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

SQL96 返回顾客名称和相关订单号

select cust_name,order_num
from Customers
INNER JOIN Orders ON Orders.cust_id=Customers.cust_id
order by cust_name,order_num;

SQL97 返回顾客名称和相关订单号以及每个订单的总价

• 因为order_num在多个表出现，所以在引用列的时候，需要注明该列来自哪个表。
• 连接过程中，完成了分组

select
    cust_name,
    Orders.order_num,
    (OrderItems.quantity * OrderItems.item_price) as OrderTotal
from
    Customers INNER JOIN Orders ON Orders.cust_id=Customers.cust_id
    INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
order by
    cust_name,
    Orders.order_num

如果使用了聚合函数：select的字段有一个聚合函数sum，而其他两个字段没有使用聚合函数，此时必须将这两个字段放在group by 语句中

select
    cust_name,
    Orders.order_num,
    sum(OrderItems.quantity * OrderItems.item_price) as OrderTotal
from
    Customers
    INNER JOIN Orders ON Orders.cust_id = Customers.cust_id
    INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
group by
    cust_name,
    Orders.order_num
order by
    cust_name,
    Orders.order_num

cust_name和order_num为非聚类而在不使用group by的聚类函数中，有一个聚类sum，其他的必须也使用聚类，所以添加了这两列的group by 。

而在selct句子中不使用sum，即没有聚类函数，则可以在后来不用将这两列进行group by。

总结：要么全为聚类，要么全不为聚类

SQL98 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

select
    cust_id,
    order_date
from
    Orders t
    inner join OrderItems t1 on t.order_num = t1.order_num
where
    prod_id = "BR01"
order by
    order_date

SQL99 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

select cust_email
from
  Customers
  inner join Orders on Customers.cust_id = Orders.cust_id
  inner join OrderItems on OrderItems.order_num = Orders.order_num
where prod_id = 'BR01'

SQL100 确定最佳顾客的另一种方式（二）

select
    cust_name,
    sum(item_price * quantity) as total_price
from
    Customers
    inner join Orders on Customers.cust_id = Orders.cust_id
    inner join OrderItems on OrderItems.order_num = Orders.order_num
group by
    cust_name
having
    total_price > 1000
order by
    total_price

12 创建高级联结

SQL101 检索每个顾客的名称和所有的订单号（一）

select cust_name,order_num
from
  Customers
  inner join Orders on Customers.cust_id = Orders.cust_id
order by cust_name

SQL102 检索每个顾客的名称和所有的订单号（二）

• 内联结：inner join。取两列的交集。
• 外联结：
• • left join。左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取null。
• • right join。右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取null。

select cust_name,order_num
from
  Customers
  left join Orders on Customers.cust_id = Orders.cust_id
order by cust_name

SQL103 返回产品名称和与之相关的订单号

select prod_name,order_num
from
  Products
  left join OrderItems on Products.prod_id = OrderItems.prod_id
order by prod_name

SQL104 返回产品名称和每一项产品的总订单数

select prod_name,count(order_num)
from
  Products
  left join OrderItems on Products.prod_id = OrderItems.prod_id
group by prod_name
order by prod_name

SQL105 列出供应商及其可供产品的数量

select Vendors.vend_id,count(prod_id) as prod_id
from
  Vendors
  left join Products on Vendors.vend_id = Products.vend_id
group by Vendors.vend_id
order by Vendors.vend_id

13 组合查询

SQL106 将两个 SELECT 语句结合起来（一）

select prod_id,quantity
from OrderItems
where quantity=100

union

select prod_id,quantity
from OrderItems
where prod_id like 'BNBG%'
order by prod_id;

SQL107 将两个 SELECT 语句结合起来（二）

select prod_id,quantity
from OrderItems
where quantity=100 or prod_id like 'BNBG%'
order by prod_id;

SQL108 组合 Products 表中的产品名称和 Customers 表中的顾客名称

注意：这个地方用的是union all 而不是 union

union与union all 都是行合并，前者去重，后者不去重，会全部罗列出来。他们合并后列数不变，行数变多

# 不需要改字段名
select prod_name
from Products
union all
# 需要改字段名
select cust_name as prod_name
from Customers

order by prod_name;

SQL109 纠错4

使用union组合查询时，只能使用一条order by字句，他必须位于最后一条select语句之后，因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
-- 总的排序
ORDER BY cust_name;