Day 36 (MySQL+PHP)

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作为变数
     ?>