資料庫設計 - 有效的使用系統資料 - 第六章 - MySQL 函式庫與運算子

單元 1 數學運算子

運算

• 加 +
• 減 -
• 乘 *
• 除 /
• 除法取餘數 mod
• 除法取商 div

練習

算出 products table 中所有產品價格的平均值

select sum(price) / count(productid) average_price
from products;

單元 2 比較運算子

比較運算子

• >
• <
• =
• !=

select * 
from Table1
where column1 > 50;

練習

找出 productid 不等於 1 的所有資料

select *
from products
where productid != 1;

單元 3 邏輯運算子

比較運算子

• and
• or
• not

select *
from TABLE1
where (name = "value1" or name = "value2");

select *
from TABLE1
where not name = "value1";

練習

找出 supplier table 中 supplierid = 1 或 2 的資料

select *
from products
where supplierid = 1 or SupplierID = 2;

作業

找出 supplier table 中 category = 1 或 2 ，且 price 介於 10 與 20 之間的資料

• 用 and

select *
from products
where (CategoryID = 1 or categoryid = 2) and (price >= 10 and price <= 20);

• 用 between

select *
from products
where (price between 10 and 20) and (CategoryID = 1 or categoryid = 2);

單元 4 計算資料的數量

count

計算資料的數量

select count(column1)
from TABLE1;

distinct

呈現不重複的資料

select distinct column1
from TABLE1;

練習

1. 計算 product Table 中 productid 的數量

SELECT count(productid)
FROM orders.orderdetails;

2. 去除 roduct Table 中 productid 的重複值

select distinct productid from orderdetails;

3. productid 不重複的資料有幾筆

select count(distinct productid) from orderdetails;

作業

請在 orderdetails table 中，找出 quantity > 40 的資料，並計算出不重複的 productid 有幾個

select count(distinct productid)
from orderdetails
where quantity > 40;

單元 5 數學運算函式庫

數學函式庫

• sum(col): 欄位 col 的總和
• abs(num): num 的絕對值
• round(num, n): 四捨五入 num 到小數點第 n 位
• truncate(num, n): 無條件捨去 num 後面的小數到第 n 位
• pow(x, y): x 的 y 次方
• sqrt(num): num 的平方根

作業

在 orderdetails 表格中，找出每一筆訂單中產品的平均價格，最後顯示出 orderid 及計算過後的平均價格結果。

產品平均價格四捨五入到小數點第一位。

• 方法一

  join product table 與 orderdetails table 後計算（老師的方法）

  SELECT od.OrderID, round(sum(p.price * od.Quantity) / sum(od.Quantity), 1)
  FROM orderdetails od, products p
  where p.productid = od.ProductID
  group by od.orderid;
  

• 方法二

  取出 product table 中的 price 欄位，直接使用

  SELECT orderid , round(sum((select p.price from products p where p.productid = od.ProductID) * quantity) / sum(Quantity), 1)
  FROM orderdetails od
  group by orderid;
  

單元 6 處理文字的函式庫

字串函式庫

• SUBSTRING(str, pos): 擷取字串，從 pos 開始

• SUBSTRING(str, pos, len): 擷取字串，從 pos 開始，擷取 len 長度

• CONCAT(A, B): 將 A 欄位/字串與 B 欄位/字串結合

• REPLACE(A, "cde", "fgh"): 將 A 欄位/字串中的 "cde" 替換成 "fgh")