信息记录:
逆序排列语句
ORDER BY [clause] desc;
ok
子查询
现在我们需要查询一个顾客列表,里面包含已订购的总金额(返回顾客ID,顾客订单总数,结果按照金额从大到小排序)
SELECT cust_id,
COUNT(*) AS total_order,
(select SUM(quantity*item_price)
FROM orderitems
WHERE orderitems.order_num = orders.order_num)
AS total_value
FROM orders
GROUP BY cust_id
ORDER BY (SELECT SUM(quantity*item_price)
FROM orderitems
WHERE orderitems.order_num = orders.order_num
GROUP BY orderitems.order_num) desc;
这些语句实现了好几件事情
首先是从orders表中返回cust_id和每个id一共下单次数,同时还把每个id的订单总金额显示在最后一列。
排序按照订单总金额从大到小降序排列。
联结
内联结
SELECT vend_id,
vend_name,
prod_name
FROM vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;
尝试多表查询
很遗憾程序出错
网上声称出现这种问题,是没有具体声明参数在哪个表单中。
Σ(っ °Д °;)っ
检查发现select子句中的vend_id,因为其同时存在于在vendors和products两个表中,所以我们需要在前面加上表头。
再贴一次语句
SELECT vendors.vend_id,
vend_name,
prod_name
FROM vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;
好耶!!!这次一切正常!!!
如果我们想一次性联结多个表单有应该怎么做呢?
方法一:用where······and······and·····
方法二:用inner join···on····inner·····join···on···
SELECT cust_name,
order_num,
(SELECT SUM(quantity*item_price)
FROM orderitems
WHERE orderitems.order_num = orders.order_num
)AS Ordertotal
FROM customers
inner JOIN orders ON orders.cust_id = customers.cust_id
ORDER BY cust_name,
order_num
;
上述语句在一次查询中同时使用了子查询和内联结
返回顾客名称、订单号、以及每个订单的总金额
创建高级联结
自然联结 排除多次出现的列,让每列值出现一次,而这个排除工作由你自己来完成。
外联结,左外联结和右外联结