1.抓資料 > 匯入

CREATE DATABASE 後匯入
https://github.com/bradchao/MySQL2021/blob/master/MySQLTutor/northwind_brad.sql

2.匯入錯誤 wrpracti_northwind

建資料庫後才能放TABLE

3.語法 (抓資料)

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: employees

(1)查看職稱, FirstName, LastName 依照職稱排序, 再依照 FirstName 反向排序

SELECT Title,FirstName ,LastName FROM `employees`
ORDER BY Title ,FirstName DESC
ORDER:排序
DESC:反向
ESC:正向(不常用，預設)
(2)哪些員工不是業務代表 'Sales Representative'
SELECT Title, FirstName, LastName FROM Employees
WHERE Title <> 'Sales Representative'
WHERE:塞選(條件)
(3)哪些員工其 Region 資料為IS(或不為 IS NOT ) NULL
SELECT Title,FirstName ,LastName ,Region FROM `employees` 
WHERE Region IS NULL
SELECT Title,FirstName ,LastName ,Region FROM `employees` 
WHERE Region IS NOT NULL
WHERE:塞選(條件)
(4)英文資料適用，塞選N之後的資料
SELECT LastName FROM `employees` 
WHERE LastName >= 'n'
ORDER BY LastName
ORDER:排序
WHERE:塞選(條件)
LastName >= 'N' 是什麼意思嗎? => 78
NA, P...
(5)顯示商品名稱, 庫存量(UnitsInStock), 訂單量(UnitsOnOrder), 安全庫存量(ReorderLevel)
SELECT ProductName, UnitsInStock ,UnitsOnOrder ,ReorderLevel FROM `products`
(6)顯示商品名稱, 庫存量(UnitsInStock)少於安全庫存量(ReorderLevel)
(該進貨了)
SELECT ProductName, UnitsInStock ,UnitsOnOrder ,ReorderLevel FROM `products`
WHERE UnitsInStock <= ReorderLevel
WHERE:塞選(條件)
(7)顯示商品名稱, 庫存量(UnitsInStock)－訂單量(UnitsOnOrder)少於安全庫存量(ReorderLevel)
(該進貨了)
SELECT ProductName, UnitsInStock ,UnitsOnOrder ,ReorderLevel FROM `products`
WHERE UnitsInStock - UnitsOnOrder <= ReorderLevel
SELECT Discount FROM `orderdetails` 
WHERE Discount > 0
WHERE:塞選(條件)
伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orders
(8)訂單中顯示出：超過需求日期才寄出的訂單 RequiredDate(需求日)ShippedDate(寄出日)
(誰晚寄)
SELECT EmployeeID, OrderID, CustomerID, RequiredDate, ShippedDate FROM `Orders`
WHERE ShippedDate > RequiredDate
日期比對似字串比對
原始資料結構:datetime:只會有日期，不會有時分秒
WHERE:塞選(條件)
(9)兩個以上欄位資料相加形成一個欄位資料(字串相加，中文比較常用到)
SELECT concat(FirstName,'',LastName) FROM `employees`
concat:字串相加
伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orders
(10)計算(有先乘除後加減)
SELECT OrderID, Freight, Freight*1.1 FROM `orders`
Freight, Freightx1.1:運費、運費計算x10%(手續費)
SELECT OrderID, Freight, Freight*1.1 AS FreightTotal FROM `orders`;
FreightTotal:運費+運費計算x10%(手續費)
(AS可略)
SELECT OrderID, Freight, Freight*1.1 AS FreightTotal FROM Orders
WHERE Freight*1.1 >= 500
WHERE:塞選(條件)
(11)算出優惠價格
SELECT UnitPrice, Quantity ,UnitPrice*Quantity*(100-Discount)/100 AS Price FROM `orderdetails`
(100-Discount)/100:折扣
伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orderdetails
(12)SUM:加總
SELECT OrderID ,ProductID ,Quantity FROM `orderdetails`
WHERE ProductID = 3 ;
SELECT SUM(Quantity) totalQty FROM `orderdetails`
WHERE ProductID = 3 ;
SUM:加總
伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: employees
(13)統計各城市的員工數量(群組後統計)
SELECT City, COUNT(EmployeeID) FROM `employees` GROUP BY City
SELECT City, COUNT(EmployeeID) AS NumsEmployee FROM `employees` GROUP BY City
GROUP:群組(與ORDER不同，ORDER只是排序)
COUNT:各自有幾個
(14)只列出>1的城市
SELECT City, COUNT(EmployeeID) AS NumsEmployee FROM `employees` 
GROUP BY City
HAVING COUNT(EmployeeID) > 1
HAVING:用在GROUP BY後的篩選
COUNT:各自有幾個
(15)業務代表 'Sales Representative' 中各個城市員工數量小於 3
SELECT City, COUNT(EmployeeID) AS NumsOfEmployee FROM `employees` 
WHERE Title = 'Sales Representative'
GROUP BY City
HAVING NumsOfEmployee < 3
WHERE:條件式
COUNT:各自有幾個
HAVING:用在GROUP BY後的篩選
(16)列出員工表中的城市, 不重複
SELECT DISTINCT City FROM Employees ORDER BY City
顯示出員工表中有幾個City
SELECT COUNT(DISTINCT City) FROM Employees
COUNT:各自有幾個
DISTINCT:不同的
(17)OrderDetail 各個商品的總銷售量
SELECT ProductID, SUM(Quantity) AS TotalQty FROM OrderDetails
GROUP BY ProductID
SUM:加總
GROUP:群組(與ORDER不同，ORDER只是排序)
TotalQty:自訂
(18)OrderDetail 各個商品的總銷售量 小於 200 單位
SELECT ProductID, SUM(Quantity) AS TotalQty FROM OrderDetails GROUP BY ProductID HAVING SUM(Quantity) < 200
HAVING SUM(Quantity) < 200:群組化後的篩選 後相加 < 200
(19)統計各個商品的平均單價
SELECT ProductID, AVG(UnitPrice) AS AvgPrice FROM OrderDetails
GROUP BY ProductID
HAVING AVG(UnitPrice) > 70
ORDER BY AvgPrice
AVG:平均
AvgPrice:自訂
HAVING:用在GROUP BY後的篩選
伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orders
(20)統計各個客戶的訂單數
SELECT CustomerID, COUNT(OrderID) AS Nums FROM Orders 
GROUP BY CustomerID HAVING COUNT(OrderID) > 15
ORDER BY Nums DESC
LIMIT 5
COUNT:各自有幾個
LIMIT:只顯示幾個
Nums:自訂
DESC:反向
(21)運費取小數點一位並四捨五入
SELECT Freight, 
ROUND(Freight, 1) AS F2, 
ROUND(Freight, 2) AS F3 
FROM Orders
SELECT Freight, ROUND(Freight, 1) AS F2, 
ROUND(Freight, 2) AS F3,  
ROUND(Freight, 0) AS F4,
ROUND(Freight, -1) AS F5
FROM Orders
原始資料結構:decimal(10,4)小數點後第四位
ROUND(Freight, 1):小數點第一位
ROUND(Freight, -1):取十位數(個位數0)
F2、F3:自訂
員工入職年齡_V1
SELECT LastName, BirthDate ,BirthDate,
YEAR(HireDate)-YEAR(BirthDate) AS HireDate FROM `employees`
YEAR:年
員工入職年齡_V2
SELECT LastName, BirthDate, HireDate,
YEAR(HireDate) - YEAR(BirthDate) AS HireAge1,
ROUND(DATEDIFF(HireDate,BirthDate)/365,0) AS HireAge2 
FROM Employees
DATEDIFF:幾日
ROUBND四捨五入(,0)第幾位
(23)算幾天
SELECT DATEDIFF ('2021-03-01','2021-02-01')
DATEDIFF:幾日
(24)員工幾月生日
SELECT MONTH(BirthDate), DATE_FORMAT(BirthDate,'%m-%d-%Y') FROM Employees
SELECT FirstName, LastName,
MONTH(BirthDate) AS M1, 
DATE_FORMAT(BirthDate,'%m') AS M2
FROM Employees
ORDER BY M2
SELECT FirstName, LastName,
BirthDate,
EXTRACT(YEAR FROM BirthDate)
FROM Employees
ORDER BY EXTRACT(MONTH FROM BirthDate)
DATE_FORMAT:格式化
'%m-%d-%Y':月日年
EXTRACT:萃取
(25)子查詢 (FROM需大寫)
// Orders 中 OrderID = 10280 中的 CustomerID = ?
SELECT CustomerID FROM Orders
WHERE OrderID = 10280
// COMMI
// Customers 中 CustomerID = 'COMMI' 的公司名稱為?
// 合成以下
SELECT CompanyName FROM Customers       //查詢最終目標
WHERE CustomerID = (
	SELECT CustomerID FROM Orders   //子查詢WHERE被塞進去條件
	WHERE OrderID = 10280
Customers內的CustomerID 與
Orders CustomerID 有關 並從裡面抓 OrderID =10280
// 合成以下_v2
SELECT CompanyName FROM Customers        //查詢最終目標
WHERE CustomerID IN (                    //多個用IN
	SELECT CustomerID FROM Orders
	WHERE OrderID = 10280 OR OrderID = 10270 OR OrderID = 10290
//_v2錯誤
SELECT CompanyName FROM Customers     //查詢最終目標
WHERE CustomerID = (      //此處的=、is 都只能有一個，多個用IN
	SELECT CustomerID FROM Orders
	WHERE OrderID = 10280 OR OrderID = 10270 OR OrderID = 10290
(26)子查詢:1997 年中有下訂單的客戶公司名稱列出來
思考:1997 有訂單的客戶 => Group By
SELECT CompanyName FROM Customers 
WHERE CustomerID IN (
    SELECT CustomerID FROM Orders
    WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
    GROUP BY CustomerID   
BETWEEN...AND:介於
(27)子查詢:供應商Tokyo Traders, Mayumi's, Svensk Sjfda的公司ID及供應哪些商品
SELECT SupplierID FROM `suppliers` 
WHERE `CompanyName` IN ('Tokyo Traders',"Mayumi's",'	
Svensk Sjfda AB')
''=""
SupplierID(供應哪些商品)
SELECT productName, SupplierID 
FROM products 
WHERE `SupplierID` IN (
    SELECT SupplierID FROM `suppliers` 
    WHERE CompanyName IN 
    ('Tokyo Traders',"Mayumi's",'Svensk Sjfda AB')
(28)子查詢:Seafood => ?
SELECT Suppliers.CompanyName FROM Suppliers 
WHERE SupplierID IN (
    SELECT SupplierID FROM Products
    WHERE CategoryID IN (
        SELECT CategoryID FROM Categories
        WHERE Categories.CategoryName = 'Seafood'
CompanyName在Suppliers抓
1.連接
SupplierID Suppliers
SupplierID Products
2.再連接
CategoryID Categories
(29)Join => 所有訂單的員工名字（表單內有同樣抬頭才能這樣抓）
SELECT Employees.EmployeeID, Employees.FirstName, Orders.OrderID, Orders.OrderDate
FROM Orders JOIN Employees 
ON (Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Orders.OrderDate
表別名的方式:
SELECT e.EmployeeID, e.FirstName, o.OrderID, o.OrderDate
FROM Orders o JOIN Employees e 
ON (e.EmployeeID = o.EmployeeID)
ORDER BY o.OrderDate
JOIN ON:表合併
4.如果未來就業DBA 有需要甚麼證照嗎?類似MCTS或者ORACLE OCA證照嗎?
重點是寫考古題，證照順便去考就好
5.查詢語法的處理順序，為何要強調這些語法的排序？文法、語法
SELECT ... FROM
WHERE
GROUP BY ... HAVING(群組化後的篩選)
ORDER BY
LIMIT
6.ＳＵＭ跟ＣＯＵＮＴ差異
Sum => 數值加總
Count => 筆數加總
1 => 38
2 => 44
SELECT ProductID, COUNT(OrderID), SUM(UnitPrice), AVG(UnitPrice) FROM OrderDetails
GROUP BY ProductID
HAVING ProductID <= 2
7.w3school上面navbar還有一個SQL 跟現在在看的這邊的MySQL有差別嗎？
https://www.w3schools.com/sql/
還是有差異，SQL是統稱
https://www.w3schools.com/sql/sql_autoincrement.asp
(1)哪些公司的總銷售額是>10000=>
SELECT c.CompanyName, SUM(od.UnitPrice * od.Quantity) AS sumPrice
FROM customers c
     JOIN orders o ON(c.CustomerID = o.CustomerID)
     JOIN orderdetails od ON ( o.OrderID = od.OrderID)
GROUP BY c.CustomerID
HAVING sumPrice > 10000
ORDER BY sumPrice DESC
customers = c
orderdetails = od
orders = o
JOIN ON一樣才能抓再一起
c.CustomerID = o.CustomerID抓在一起
o.OrderID = od.OrderID 抓在一起
(2)哪些公司的單一商品銷售額是>10000=>
SELECT c.CompanyName,o.OrderID,od.UnitPrice * od.Quantity
FROM customers c
     JOIN orders o ON(c.CustomerID = o.CustomerID)
     JOIN orderdetails od ON ( o.OrderID = od.OrderID)
WHERE od.UnitPrice * od.Quantity >10000
(3)哪些公司的單一訂單銷售額是>10000=
SELECT c.CompanyName, o.OrderID , SUM(od.UnitPrice * od.Quantity) AS sumPrice
FROM customers c
     JOIN orders o ON(c.CustomerID = o.CustomerID)
     JOIN orderdetails od ON ( o.OrderID = od.OrderID)
GROUP BY c.CustomerID , o.OrderID
HAVING sumPrice > 10000
ORDER BY sumPrice DESC
9.GROUP BY使用時機
SUM()、AVG()、COUNT()，計算都要+()