Day 35 (MySQL)

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)计算(有先乘除後加减)

10-1

SELECT OrderID, Freight, Freight*1.1 FROM `orders`

Freight, Freightx1.1:运费、运费计算x10%(手续费)

10-2

SELECT OrderID, Freight, Freight*1.1 AS FreightTotal FROM `orders`;

FreightTotal:运费+运费计算x10%(手续费)
(AS可略)

10-3

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:自订

(22)

员工入职年龄_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及供应哪些商品

公司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

8.作业

(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