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 EmployeesWHERE 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 OrdersWHERE 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 CityHAVING 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 CityHAVING 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 OrderDetailsGROUP 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 OrderDetailsGROUP BY ProductIDHAVING AVG(UnitPrice) > 70ORDER 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) > 15ORDER BY Nums DESCLIMIT 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 F5FROM 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 M2FROM EmployeesORDER BY M2

SELECT FirstName, LastName,BirthDate,EXTRACT(YEAR FROM BirthDate)FROM EmployeesORDER BY EXTRACT(MONTH FROM BirthDate)

DATE_FORMAT:格式化
'%m-%d-%Y':月日年
EXTRACT:萃取

(25)子查询 (FROM需大写)

// Orders 中 OrderID = 10280 中的 CustomerID = ?

SELECT CustomerID FROM OrdersWHERE 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 (                    //多个用INSELECT CustomerID FROM OrdersWHERE OrderID = 10280 OR OrderID = 10270 OR OrderID = 10290)

//_v2错误

SELECT CompanyName FROM Customers     //查询最终目标WHERE CustomerID = (      //此处的=、is 都只能有一个，多个用INSELECT CustomerID FROM OrdersWHERE 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.OrderDateFROM Orders JOIN Employees ON (Employees.EmployeeID = Orders.EmployeeID)ORDER BY Orders.OrderDate

表别名的方式:

SELECT e.EmployeeID, e.FirstName, o.OrderID, o.OrderDateFROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID)ORDER BY o.OrderDate

JOIN ON:表合併

4.如果未来就业DBA 有需要甚么证照吗?类似MCTS或者ORACLE OCA证照吗?

重点是写考古题，证照顺便去考就好

5.查询语法的处理顺序，为何要强调这些语法的排序？文法、语法

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 sumPriceFROM customers c     JOIN orders o ON(c.CustomerID = o.CustomerID)     JOIN orderdetails od ON ( o.OrderID = od.OrderID)GROUP BY c.CustomerIDHAVING sumPrice > 10000ORDER 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.QuantityFROM 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 sumPriceFROM customers c     JOIN orders o ON(c.CustomerID = o.CustomerID)     JOIN orderdetails od ON ( o.OrderID = od.OrderID)GROUP BY c.CustomerID , o.OrderIDHAVING sumPrice > 10000ORDER BY sumPrice DESC

9.GROUP BY使用时机

SUM()、AVG()、COUNT()，计算都要+()