1.Windows 針對資料庫表名稱小寫

C:\MAMP\conf\mysql\my.ini(cnf) => 處理之前先備份 用記事本改
...
[mysqld]
....
lower_case_table_names=2

2.語法

伺服器: localhost:3306 »資料庫: class

(1)

SELECT cSex,COUNT(cID) 
FROM `students` 
GROUP BY cSex

(2)逾時訂單的需求日期;1998-01-01後的

2-1 逾時訂單的需求日期

SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
        JOIN Customers c ON (c.CustomerID = o.CustomerID)

a.需要抓哪些
b.在不同的表單用JOIN ON把有一樣抬頭的連在一起(拉關係)
c.表單各取一個代號

2-2 1998-01-01後的逾時訂單

SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
        JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate >= '1998-01-01'
ORDER BY c.CompanyName 

(3)逾時訂單的需求日期;1998-01-01後的，不使用JOIN ON

SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o, Employees e, Customers c
WHERE (e.EmployeeID = o.EmployeeID) 
AND (c.CustomerID = o.CustomerID)
AND o.ShippedDate > o.RequiredDate 
AND o.OrderDate >= '1998-01-01'
ORDER BY c.CompanyName

o.c.e.:別名
JOIN ON:在(這樣的條件)下才合併

(4)各產品的最高單價

SELECT DISTINCT od1.ProductID, od1.UnitPrice
FROM OrderDetails od1
WHERE od1.UnitPrice = (
	SELECT MAX(UnitPrice)
        FROM OrderDetails od2
        WHERE od1.ProductID = od2.ProductID
        )
ORDER BY od1.ProductID

DISTINCT:過濾重複的
d1.ProductID = od2.ProductID:自表子查詢
(白話:外面(OrderDetails od1)1234..
裡面最高(OrderDetails od2)的1出去、裡面最高的2出去..)

(5)哪些公司的訂單數量在10張以上的, 列出公司名稱, 以及單量,在1997-01-01年以後

=> Orders
=> WHERE OrderDate >= '1997--01-01'
=> GROUP BY CustomerID(群組公司名稱) => COUNT(OrderID)(看單量)
=> JOIN Customers ON o.CustomerID = c.CustomerID (拉關係)

SELECT c.CompanyName, COUNT(o.OrderID) AS nums
FROM Customers c
	JOIN Orders o ON (c.CustomerID = o.CustomerID)
WHERE o.OrderDate >= '1997-01-01'
GROUP BY c.CustomerID
HAVING nums >= 10
ORDER BY nums DESC

HAVING nums >= 10 群組化之後的條件

(6)銷售排行榜

Employees, Orders, OrderDetails
=> Orders => OrderDetails
=> Orders => Employees
=> Group by Orders.EmployeeID

SELECT e.EmployeeID, e.FirstName, e.LastName, SUM(od.UnitPrice * od.Quantity) AS total
FROM Orders o
	JOIN OrderDetails od ON (o.OrderID = od.OrderID)
        JOIN Employees e ON (o.EmployeeID = e.EmployeeID)
GROUP BY o.EmployeeID 
ORDER BY total DESC

--- 驗算 ---

SELECT SUM(UnitPrice * Quantity)
FROM OrderDetails
WHERE OrderID IN  (
	    SELECT OrderID FROM Orders
        WHERE Orders.EmployeeID = 6
)

(7)產品是哪個供應商(含名稱)提供?

(7-1)JOIN ON寫法:

SELECT p.ProductName, s.CompanyName, s.ContactName
FROM Products p
	JOIN Suppliers s ON (p.SupplierID = s.SupplierID)
LIMIT 10

LIMIT 10:看前10筆

(7-2)USING寫法:(串在一起)

SELECT p.ProductName, s.CompanyName, s.ContactName
FROM Products p
	JOIN Suppliers s USING (SupplierID)
LIMIT 10

USING串在一起

(7-3)WHERE寫法:

SELECT p.ProductName, s.CompanyName, s.ContactName
FROM Products p,Suppliers s
WHERE p.SupplierID = s.SupplierID
LIMIT 10

(8)增加資料

Use class

ALTER TABLE students ADD COLUMN 
ch INT DEFAULT 0, 
ADD COLUMN eng INT DEFAULT 0,
ADD COLUMN math INT DEFAULT 0

ALTER TABLE:轉換表
ADD COLUMN:直行增加
DEFAULT 0:預設 0

結果:

(9)更新資料 UPDATE SET

UPDATE students SET ch = 99, eng = 97, math= 95  //全部人 
UPDATE students SET ch = 99, eng = 97, math= 95 WHERE cID = 3;  //only 3
UPDATE students SET ch = 89, eng = 79, math= 59 WHERE cName LIKE '林%' //LIKE:像 %、_
UPDATE students SET ch = RAND()*101, eng = RAND()*101, math = RAND()*101  //RAND亂數 0~1

分數會變成101

解決方法1.

UPDATE students SET ch = 
FLOOR(RAND()*101), eng = FLOOR(RAND()*101), math = FLOOR(RAND()*101) //FLOOR無條件捨去

解決方法2.

// 0 - 100
// 這個有辦法用ROUND()來寫嗎
// 0 <= RAND() < 1
// 0 <= RAND() *101 < 101
// round(rand()*100)+1 => 1

(10)計算

SELECT cID,cName,ch,eng,math, 
(ch+eng+math) as score, 
(ch+eng+math)/3 as avg 
FROM students

(11)視圖:CREATE VIEW as

(11-1)建立視圖

CREATE VIEW scoreView as
SELECT cID,cName,ch,eng,math, (ch+eng+math) as score, (ch+eng+math)/3 as avg FROM students

(11-2)變更資料

UPDATE students SET math= 95 WHERE cID = 1;

(11-3)視圖(scoreView)也會更新

(11-4)另建立視圖

CREATE VIEW girlScore as
SELECT cID,cName,ch,eng,math, (ch+eng+math) as score FROM students WHERE cSex = 'F'

(12)分類 CASE END (WHEN THEN)

SELECT cID,cName,ch,eng,math,(ch+eng+math)/3 AS avg,
CASE
	WHEN ((ch+eng+math))/3 >= 90 THEN 'A'
	WHEN ((ch+eng+math))/3 >= 80 THEN 'B'
	WHEN ((ch+eng+math))/3 >= 70 THEN 'C'
	WHEN ((ch+eng+math))/3 >= 60 THEN 'D'
	ELSE 'E'
END as level
FROM students

• 視圖JION ON 及 子查詢亦可使用

3. class.sql內容

CREATE TABLE IF NOT EXISTS `students` (
  `cID` tinyint(2) unsigned zerofill NOT NULL auto_increment,
  `cName` varchar(20) collate utf8_unicode_ci NOT NULL,
  `cSex` enum('F','M') collate utf8_unicode_ci NOT NULL default 'F',
  `cBirthday` date NOT NULL,
  `cEmail` varchar(100) collate utf8_unicode_ci default NULL,
  `cPhone` varchar(50) collate utf8_unicode_ci default NULL,
  `cAddr` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`cID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

zerofill:自動補0
AUTO_INCREMENT=11:之後從11開始建資料

剛提到的auto_increment=11 不是因為定義要雙位數,
是因為已經有10筆資料(而且佔用了1~10), 所以下一筆從11開始,
如果資料繼續新增, 這個數值也會一直增加, 表示下一筆自動產生的值

Attributes:UNSIGNED ZEROFILL自己補0

4.解釋JOIN ON

還沒使用JOIN ON

SELECT o.OrderID, o.CustomerID, c.CompanyName, c.ContactName, o.EmployeeID, e.FirstName, e.LastName
FROM Orders o, Employees e, Customers c
WHERE o.EmployeeID=e.EmployeeID AND o.CustomerID = c.CustomerID
AND o.ShippedDate > o.RequiredDate 
AND o.OrderDate >= '1998-01-01'
ORDER BY o.CustomerID

SELECT 看啥
FROM 從這裡
WHERE 拉關係

使用JOIN ON

SELECT o.OrderID, o.CustomerID, c.CompanyName, c.ContactName, o.EmployeeID, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (o.EmployeeID = e.EmployeeID)
        JOIN Customers c ON (o.CustomerID = c.CustomerID)
WHERE o.ShippedDate > o.RequiredDate 
AND o.OrderDate >= '1998-01-01'
ORDER BY o.CustomerID

JOIN ON條件式拉關係

5. Q:JOIN跟子查詢之間有差別嗎

JOIN

SELECT o.OrderID, o.CustomerID, c.CompanyName, c.ContactName, o.EmployeeID, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (o.EmployeeID = e.EmployeeID)
        JOIN Customers c ON (o.CustomerID = c.CustomerID)
WHERE o.ShippedDate > o.RequiredDate 
AND o.OrderDate >= '1998-01-01'
ORDER BY o.CustomerID

子查詢

SELECT CompanyName FROM Customers
WHERE CustomerID IN (
	SELECT CustomerID FROM Orders
	WHERE OrderID = 10280 OR OrderID = 10270 OR OrderID = 10290
)

子查詢只能看一張表
JOIN ON 可以看多張表

相似處:拉關係

6.副表、主表?

JOIN ON 條件 用 副表=主表
07271014的時候 用 主表=副表 (Orders Employees、Customers)
雖然兩表交換不影響查詢結果

SELECT o.OrderID, o.CustomerID, c.CompanyName, c.ContactName, o.EmployeeID, e.FirstName, e.LastName
FROM Orders o
	    JOIN Employees e ON (o.EmployeeID = e.EmployeeID)
        JOIN Customers c ON (o.CustomerID = c.CustomerID)
WHERE o.ShippedDate > o.RequiredDate 
AND o.OrderDate >= '1998-01-01'
ORDER BY o.CustomerID

師:沒有主、副表的概念，這只是主觀想法

7.隨意筆記

JOIN ON 及 WHERE都是條件式
DISTINCT:過濾重複的
MAX:最大

8.資料庫表正規化

https://ithelp.ithome.com.tw/articles/10229472

9.PHP執行開始(MySQL04+PHP01影片4:30開始)

(1)找開啟資料夾的路徑，打開資料夾
http://localhost/
http://localhost/MAMP/

Windows:白色大象=>MAMP=>Preferences=>Web Server
Document Root

路徑:對外營業處

(2)外掛
PHP Intelphense
PHP Debug

(3)建立資料夾，建立:brad01.php

(4)localhost/Myphp/brad01.php
讀取PHP路徑

(5)格式

     <?php
        phpinfo();
     ?>  //結尾不一定要寫

執行這個檔案很重要，有版本、執行環境、核心模組等等重點

(6)執行

     <?php
     echo '<h2>Hello, World</h2>';
     ?>

檢視原始碼剩下html,js...沒有PHP
因為PHP語法送去PHP了，所以原始碼不會顯示

     <h2>Hello, World</h2><hr />

(7)$變數

     <?php
     $var1 = 123;   
     $var2 = 'Brad';
     $var3 = 12.3;
     $var4 = true;
     $true = 'Brad';  //js不可以拿keyword作為變數
     $for = 123;      //js不可以拿keyword作為變數
     $if = 321;       //js不可以拿keyword作為變數
     ?>