SQL note3

SELECT AVG(prod_price) AS avg_price
-> FROM Products;

SELECT AVG(prod_price) AS avg_price
-> FROM Products
-> WHERE vend_id = ‘DLL01’;

SELECT COUNT(cust_email) AS num_cust
-> FROM Customers;

SELECT MAX(prod_price) AS max_price
-> FROM Products;

SELECT MIN(prod_price) AS min_price
-> FROM Products;

SELECT SUM(item_price*quantity) AS total_price
-> FROM OrderItems
-> WHERE order_num = 20005;

SELECT AVG(DISTINCT prod_price) AS avg_price
-> FROM Products
-> WHERE vend_id=’DLL01’;

DISTINCT参数必须使用列名，不能用于计算或表达式

SELECT COUNT(*) AS num_items,
-> MIN(prod_price) AS price_min,
-> MAX(prod_price) AS price_max,
-> AVG(prod_price) AS price_avg
-> FROM Products;

==========================================
语句，子句，字段，函数，参数，操作符

==========================================
使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

SELECT vend_id,COUNT(*) AS num_prods
-> FROM Products
-> GROUP BY vend_id;

WHERE 过滤指定的是行而不是分组，它没有分组的概念。
WHERE过滤行，HAVING过滤分组。
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT vend_id,COUNT(*)AS num_prods
-> FROM Products
-> WHERE prod_price>=4
-> GROUP BY vend_id
-> HAVING COUNT(*)>=2;

SELECT order_num,COUNT(*)AS items
-> FROM OrderItems
-> GROUP BY order_num
-> HAVING COUNT(*)>=3
-> ORDER BY items,order_num;