【牛客刷题笔记】SQL必知必会

SQL22 顾客登录名

// 拼接concat函数,upper转大写，lower转小写,
// substring(字符串,起始位,起始位置后几位)
select cust_id, cust_name,
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from Customers

SQL30 计算总和

1、where后面不能直接加聚合函数（分组函数），我们也可以这样记忆：where后面只能跟表中存在的字段。
2、having 不能单独使用，必须跟 group by 联合使用。

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

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

select cust_id, 
(select sum(item_price * quantity) 
from OrderItems t1 where t1.order_num = t2.order_num) as total_ordered
from Orders t2
order by total_ordered desc

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

//使用where查询
select cust_name, order_num
from Customers t1, Orders t2
where t1.cust_id = t2.cust_id
order by cust_name, order_num

//使用内连接，join默认内连接
select cust_name, order_num
from Customers t1
join Orders t2
on t1.cust_id = t2.cust_id
order by cust_name, order_num

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

关键词：union
用法：
join—连接表，对列操作
union–连接表，对行操作。
union–将两个表做行拼接，同时自动删除重复的行。
union all—将两个表做行拼接，保留重复的行。
思路：
筛选条件：like用法。where quantity=100，where prod_id like ‘BNBG%’
排序：放在最后进行排序，不能先排序在拼接。order by prod_id

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;

感谢观看，如有问题，请批评指正！