先简要贴个表,记录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);