01-28-记录下练习时长三天半的MySQL小白练习过程-sum-group by

先简要贴个表，记录select子句及其顺序

子句	说明
select	要返回的列或者表达式
from	从中检索数据的表
where	行级过滤
group by	分组说明
having	组级过滤
order by	输出排序顺序

在数据库里面已经安排好了orderitems表单

--------------------------------------------------------------------------------

确定已售出产品的总数

SELECT SUM(quantity)
FROM orderitems; 

输出得到

我们还可以对上面的列名重新命名

SELECT SUM(quantity) AS sum_quantity
FROM orderitems;

在select句后面加上as

很简单的可以实现列名的修改

对刚刚创建的语句进行一个小学二年级程度的修改，可以实现确定已售出产品项BR01的总数

SELECT SUM(quantity) AS sum_quantity
FROM orderitems
WHERE prod_id = 'BR01';

and we get

------------------------------------------------------------------------------------

下面进阶一小点，加上group by

确定每个订单号有多少行数，并且按照行数对订单号排序

SELECT order_num,COUNT(*) AS order_lines
FROM orderitems
GROUP BY order_num
ORDER BY order_lines;

------------------------------------------------------------------

再次进阶一点，子查询
返回购买prod_id为BR01的所有顾客的电子邮件
先附上语句

SELECT cust_name,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')
						ORDER BY order_date);