mysql-6 資料檢索（4）

標籤：custom   border   ges   having   郵件   end   需要   where   使用   

摘要資料

函數	說明
AVG()	返回某列的平均數
COUNT()	返回某列的行數
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

1、AVG函數

SELECT AVG(prod_price) AS avg_price FROM products;

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

2、count()函數

SELECT COUNT(*) AS num_cust FROM customers;

SELECT COUNT(cust_email) AS num_cust FROM customers;

這條select語句使用count(cust_email)對cust_email列中有值的進行計數，在此例子中，cust_email的計算式3次，表示5個客戶中只有三個客戶有電子郵件

3、max()函數

SELECT MAX(prod_price) AS max_price FROM products;

4、min()函數

SELECT MIN(prod_price) AS min_price FROM products;

5、sum()函數

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;

 

分組資料    group by 

SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;

order by 1、排序產生的輸出 2、任意列都可以使用 3、不一定需要

group by 1、分組行，但輸出可能不是分組的順序 2、只可能使用選擇列或運算式列，而且必須使用每個挑選清單達式 3、如果與聚集合函式一起使用列，則必須使用

 where過濾行，having過濾組

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

 

 使用子查詢

 

SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2‘;SELECT cust_id FROM orders WHERE order_num IN (20005,20007);SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2‘);

 

 

SELECT AVG(prod_price) *AS avg_price FROM products;SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;SELECT COUNT(*) AS num_cust FROM customers;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(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;SELECT cust_id FROM orders ;SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2‘;SELECT cust_id FROM orders WHERE order_num IN (20005,20007);SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ‘TNT2‘);

 

mysql-6 資料檢索（4）