【牛客SQL学习（必知必会）】

检索数据 select [distinct] x,y

1、不重复的数据：distinct

select distinct prod_id from OrderItems

排序检索数据

1、单列排序 order by

select cust_name from Customers ORDER BY cust_name DESC;

2、多列排序 order by [条件1] [条件2]

select cust_id,order_num 
from Orders 
ORDER BY 
    cust_id,
    order_num DESC;

过滤数据 where

数据范围
where price > 9
where price >=9
where price between 3 and 9

高级数据过滤

where的多个条件 AND OR

select vend_name
from Vendors
WHERE vend_country = 'USA' 
AND vend_state = 'CA'

【字符串用“=”匹配是否相等】

IN 筛选条件

select order_num,prod_id,quantity
from OrderItems 
where prod_id IN ("BR01","BR02","BR03")
AND quantity>=100

纠错题

order by语句必须放在 where 之后
mysql中，其实单引号和双引号没啥区别，单引号和双引号都可以表示字符串

通配符过滤

Like / Not Like / %

函数处理数据

链接s1和s2两个字符串concat(s1,s2)

取s的前两个字符substring(s,1,2)

获取日期类型的年份 year(date)

获取日期类型的月份 month(date)

select * from Orders
where month(order_date)='01' and year(order_date)='2020'
order by order_date

分组数据

group by

过滤分组 having 【与group by连用，放在其后面】

注意这里不能用where, where后面不能加聚合函数！！！！！

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

select中的聚合函数

下面如果是item_price*quantity代替**sum(item_price*quantity)**则会报错

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

子查询

查询字段只在一个表中

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

【难】查询字段分别在两个表中出现

【解决方法】
用where限制主键相同，从而联结两个表
注意主键是不允许重复的

【简单点】方法一：直接在select里用聚合函数

select a.prod_name,SUM(b.quantity) quant_sold 
from Products a,OrderItems b
where a.prod_id = b.prod_id
group by prod_name

方法二：在子表里面用聚合函数

select a.cust_id cust_id,SUM(b.total_ordered)  total_ordered 
from Orders a,(
    select order_num,sum(quantity*item_price) total_ordered
    from OrderItems
    group by order_num
) b
where a.order_num = b.order_num
group by cust_id
order by total_ordered desc

联结表

用where简单联结

select a.cust_name,b.order_num
from Customers a,Orders b
where a.cust_id = b.cust_id
order by a.cust_name,b.order_num

用内联结 inner join

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

使用Using

1. 查询必须是等值连接。
2. 等值连接中的列必须具有相同的名称和数据类型。

select cust_name, order_num, SUM(quantity * item_price) OrderTotal
from Customers 
    inner join Orders 
    USING(cust_id)
    inner join OrderItems
    USING(order_num)
group by cust_name,order_num
order by cust_name,order_num
    

【如果是三个表联结用USING，用ON会报错】

select cust_name, order_num, SUM(quantity * item_price) 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,order_num
order by cust_name,order_num

    
    

报错一：select字段不属于group by

select a.cust_name,b.order_num, SUM(c.quantity * c.item_price) OrderTotal
from Customers a,Orders b,OrderItems c
where a.cust_id=b.cust_id AND b.order_num=c.order_num
# 注意这里的group by 只有b.order_num会报错
# SQL_ERROR_INFO: "Expression #1 of SELECT 
# list is not in GROUP BY clause and contains 
# nonaggregated column 'a.cust_name' which is 
# not functionally dependent on columns in GROUP BY clause;
group by a.cust_id,b.order_num
order by a.cust_name,b.order_num

创建高级联结

outer join

使用left join/ right join和 union / union all进行实现

union和union all求两个表的并集，union会去重，union all不去重

SQL103 错因：
1、join的ON和USING用法记混了。
a join b on a.id = b.id
a join b USING(id)

SQL104 错因：
1、union后的表无法作为select from的表
2、mysql不能使用outer join，具体观察用left join还是right join
3、用count() sum() max()等聚合函数时，后面一定要有group by
4、left join / right join 记得加ON和USING

SQL106错因：
1、LIKE通配符为%
2、理解“where中用AND ”和 “UNION”是不一样的两个操作
UNION = where xxx or xxx
3、UNION两个select，对select的列不要改名，会报错

SQL107
用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。