4.1 別名
有時，列的名稱是一些表達(dá)式，使查詢的輸出很難理解。要給列一個描述性名稱，可以使用列別名。
以下語句說明了如何使用列別名：
SELECT [column_1 | expression] AS descriptive_name
FROM table_name;SQL
要給列添加別名，可以使用AS關(guān)鍵詞后跟別名。 如果別名包含空格，則必須引用以下內(nèi)容：
SELECT [column_1 | expression] AS `descriptive name`
FROM table_name;SQL
因?yàn)?code>AS關(guān)鍵字是可選的，可以在語句中省略它。 請注意，還可以在表達(dá)式上使用別名。
我們來看看示例數(shù)據(jù)庫(yiibaidb)中的employees表，其表結(jié)構(gòu)如下所示 -
以下查詢選擇員工的名字和姓氏，并將其組合起來生成全名。 CONCAT_WS函數(shù)用于連接名字和姓氏。
SELECT CONCAT_WS(', ', lastName, firstname)
FROM employees;SQL
執(zhí)行上面代碼，得到以下結(jié)果 -
mysql> SELECT CONCAT_WS(', ', lastName, firstname)FROM employees; -------------------------------------- | CONCAT_WS(', ', lastName, firstname) | -------------------------------------- | Murphy, Diane || Patterson, Mary || Firrelli, Jeff || Patterson, William || Bondur, Gerard || Bow, Anthony || Jennings, Leslie || Thompson, Leslie || Firrelli, Julie || Patterson, Steve || Tseng, Foon Yue || Vanauf, George || Bondur, Loui || Hernandez, Gerard || Castillo, Pamela || Bott, Larry || Jones, Barry || Fixter, Andy || Marsh, Peter || King, Tom || Nishi, Mami || Kato, Yoshimi || Gerard, Martin | -------------------------------------- 23 rows in setShell
在上面示例中，列標(biāo)題很難閱讀理解。可以為輸出的標(biāo)題分配一個有意義的列別名，以使其更可讀，如以下查詢：
SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM employees;SQL
執(zhí)行上面代碼，得到以下結(jié)果 -
mysql> SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name`FROM employees; -------------------- | Full name | -------------------- | Murphy, Diane || Patterson, Mary || Firrelli, Jeff |... ...| King, Tom || Nishi, Mami || Kato, Yoshimi || Gerard, Martin | -------------------- 23 rows in setShell
在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列別名來引用該列。
以下查詢使用ORDER BY子句中的列別名按字母順序排列員工的全名：
SELECT CONCAT_WS(' ', lastName, firstname) `Full name`
FROM employees
RDER BY `Full name`;SQL
執(zhí)行上面代碼，得到以下結(jié)果 -
mysql> SELECT CONCAT_WS(' ', lastName, firstname) `Full name`FROM employeesORDER BY `Full name`; ------------------- | Full name | ------------------- | Bondur Gerard || Bondur Loui || Bott Larry || Bow Anthony || Castillo Pamela || Firrelli Jeff || Firrelli Julie || Fixter Andy || Gerard Martin || Hernandez Gerard || Jennings Leslie || Jones Barry || Kato Yoshimi || King Tom || Marsh Peter || Murphy Diane || Nishi Mami || Patterson Mary || Patterson Steve || Patterson William || Thompson Leslie || Tseng Foon Yue || Vanauf George | ------------------- 23 rows in setShell
以下語句查詢總金額大于60000的訂單。它在GROUP BY和HAVING子句中使用列別名。
SELECT orderNumber `Order no.`, SUM(priceEach * quantityOrdered) total
FROM orderdetails
GROUP BY `Order no.`
HAVING total > 60000;SQL
執(zhí)行上面查詢語句，得到以下結(jié)果 -
請注意，不能在WHERE子句中使用列別名。原因是當(dāng)MySQL評估求值
WHERE子句時，SELECT子句中指定的列的值可能尚未確定。
可以使用別名為表添加不同的名稱。使用AS關(guān)鍵字在表名稱分配別名，如下查詢語句語法：
table_name AS table_aliasSQL
該表的別名稱為表別名。像列別名一樣，AS關(guān)鍵字是可選的，所以完全可以省略它。
一般在包含INNER JOIN，LEFT JOIN，self join子句和子查詢的語句中使用表別名。
下面來看看客戶(customers)和訂單(orders)表，它們的ER圖如下所示 -
兩個表都具有相同的列名稱：customerNumber。如果不使用表別名來指定是哪個表中的customerNumber列，則執(zhí)行查詢時將收到類似以下錯誤消息：
Error Code: 1052. Column 'customerNumber' in on clause is ambiguousShell
為避免此錯誤，應(yīng)該使用表別名來限定customerNumber列：
SELECT customerName, COUNT(o.orderNumber) total
FROM customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY customerName
HAVING total >=5
ORDER BY total DESC;SQL
執(zhí)行上面查詢語句，得到以下結(jié)果 -
上面的查詢從客戶(customers)和訂單(orders)表中選擇客戶名稱和訂單數(shù)量。 它使用c作為customers表的表別名，o作為orders表的表別名。customers和orders表中的列通過表別名(c和o)引用。
如果您不在上述查詢中使用別名，則必須使用表名稱來引用其列，這樣的會使得查詢?nèi)唛L且可讀性較低，如下 -
SELECT customers.customerName, COUNT(orders.orderNumber) total
FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
GROUP BY customerName
ORDER BY total DESC4.2 INNER JOIN
MySQL INNER JOIN子句將一個表中的行與其他表中的行進(jìn)行匹配，并允許從兩個表中查詢包含列的行記錄。
INNER JOIN子句是SELECT語句的可選部分，它出現(xiàn)在FROM子句之后。
在使用INNER JOIN子句之前，必須指定以下條件：
首先，在FROM子句中指定主表。
其次，表中要連接的主表應(yīng)該出現(xiàn)在INNER JOIN子句中。理論上說，可以連接多個其他表。 但是，為了獲得更好的性能，應(yīng)該限制要連接的表的數(shù)量(最好不要超過三個表)。
第三，連接條件或連接謂詞。連接條件出現(xiàn)在INNER JOIN子句的ON關(guān)鍵字之后。連接條件是將主表中的行與其他表中的行進(jìn)行匹配的規(guī)則。
INNER JOIN子句的語法如下：
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;SQL
假設(shè)使用INNER JOIN子句連接兩個表：t1和t2，我們來簡化上面的語法。
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;SQL
對于t1表中的每一行，INNER JOIN子句將它與t2表的每一行進(jìn)行比較，以檢查它們是否都滿足連接條件。當(dāng)滿足連接條件時，INNER JOIN將返回由t1和t2表中的列組成的新行。
請注意，t1和t2表中的行必須根據(jù)連接條件進(jìn)行匹配。如果找不到匹配項(xiàng)，查詢將返回一個空結(jié)果集。當(dāng)連接超過2個表時，也應(yīng)用此邏輯。
以下維恩圖說明了INNER JOIN子句的工作原理。結(jié)果集中的行必須出現(xiàn)在兩個表中：t1和t2，如兩個圓的交叉部分所示 -
如果連接具有相同列名稱的多個表，則必須使用表限定符引用SELECT和ON子句的列，以避免列錯誤。
例如，如果t1和t2表都具有名為c的一個相同列名，則必須在SELECT和ON子句中使用表限定符，如使用t1.c或t2.c指定引用是那個表中的c列。
為了節(jié)省書寫表限定符的時間，可以在查詢中使用表別名。 例如，可以長名稱verylonglonglong_tablename表使用表別名，并使用t.column引用其列，而不是使用verylonglonglong_tablename.column，但是如果喜歡書寫或使用這么長的表名稱，那么也應(yīng)該允許你的開發(fā)伙伴罵你幾句類似：傻逼~等這樣的話！
下面來看看示例數(shù)據(jù)庫(yiibaidb)中的產(chǎn)品(products)和產(chǎn)品線(productlines)表。它們的 ER 圖如下所示 -
在上面圖中，products表中的productLine列參考引用productlines表的productline列。 products表中的productLine列稱為外鍵列。
通常，連接具有外鍵關(guān)系的表，如產(chǎn)品線(productlines)和產(chǎn)品(products)表?，F(xiàn)在，如果想獲取以下數(shù)據(jù) -
獲取products表中的productCode和productName列的值。
獲取productlines表產(chǎn)品線的描述 - textDescription列的值。
為此，需要通過使用INNER JOIN子句根據(jù)productline列匹配行來從兩個表中查詢選擇數(shù)據(jù)，如下所示：
SELECT productCode, productName, textDescription
FROM products t1
INNER JOIN productlines t2 ON t1.productline = t2.productline;SQL
執(zhí)行上面查詢，得到下面的結(jié)果(部分)-
由于兩個表的連接列是使用相同一個列：productline，因此可以使用以下語法：
SELECT productCode, productName, textDescription
FROM products
INNER JOIN productlines USING (productline);SQL
上面語句返回相同的結(jié)果集，但是使用此語法，不必使用表的別名。
MySQL INNER JOIN GROUP BY子句
請參閱以下訂單和訂單詳細(xì)表，orders表和orderdetails表的結(jié)構(gòu)如下所示 -
可以使用具有GROUP BY子句的INNER JOIN子句從orders和orderdetails表中獲取訂單號，訂單狀態(tài)和總銷售額，如下所示：
SELECT T1.orderNumber, status, SUM(quantityOrdered * priceEach) total
FROM orders AS T1
INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber;SQL
執(zhí)行上面查詢，結(jié)果如下所示(部分) -
類似地，以下語句查詢與上述得到結(jié)果相同：
SELECT orderNumber, status, SUM(quantityOrdered * priceEach) total
FROM orders
INNER JOIN orderdetails USING (orderNumber)
GROUP BY orderNumber;SQL
MySQL INNER JOIN使用等于以外的運(yùn)算符
到目前為止，您已經(jīng)看到連接謂詞使用相等的運(yùn)算符(=)來匹配行。但是也可以使用大于(>)，小于(<)和不等于(<>)運(yùn)算符的其他運(yùn)算符來形成連接謂詞。
以下查詢使用少于(<)連接來查找低于代碼為S10_1678的產(chǎn)品的銷售價格的制造商建議零售價(MSRP)的所有產(chǎn)品。
SELECT orderNumber, productName, msrp, priceEach
FROM products p
INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach
WHERE p.productcode = 'S10_1678';SQL
執(zhí)行上面查詢語句，得到以下輸出結(jié)果 -
mysql> SELECT orderNumber, productName, msrp, priceEachFROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEachWHERE p.productcode = 'S10_1678'; ------------- --------------------------------------- ------ ----------- | orderNumber | productName | msrp | priceEach | ------------- --------------------------------------- ------ ----------- | 10107 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35 || 10121 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 86.13 || 10134 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 90.92 || 10145 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 76.56 || 10159 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35 || 10168 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 94.74 || 10399 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 77.52 || 10403 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 85.17 |... ...| 10417 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 79.43 | ------------- --------------------------------------- ------ ----------- 26 rows in set4.3 LEFT JOIN
MySQL LEFT JOIN子句允許您從兩個或多個數(shù)據(jù)庫表查詢數(shù)據(jù)。LEFT JOIN子句是SELECT語句的可選部分，出現(xiàn)在FROM子句之后。
我們假設(shè)要從兩個表t1和t2查詢數(shù)據(jù)。以下語句說明了連接兩個表的LEFT JOIN子句的語法：
SELECT t1.c1, t1.c2, t2.c1, t2.c2
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1;SQL
當(dāng)使用LEFT JOIN子句將t1表加入t2表時，如果來自左表t1的行與基于連接條件(t1.c1 = t2.c1)的右表t2匹配，則該行將被包含在結(jié)果集中。
如果左表中的行與右表中的行不匹配，則還將選擇左表中的行并與右表中的“假”行組合。“假”行對于SELECT子句中的所有相應(yīng)列都包含NULL值。
換句話說，LEFT JOIN子句允許您從匹配的左右表中查詢選擇行記錄，連接左表(t1)中的所有行，即使在右表(t2)中找不到匹配的行也顯示出來，但使用NULL值代替。
下圖可幫助您可視化LEFT JOIN子句的工作原理。 兩個圓圈之間的交點(diǎn)是兩個表中匹配的行，左圓的剩余部分(白色部分)是t1表中不存在t2表中任何匹配行的行。 因此，左表中的所有行都包含在結(jié)果集中。
請注意，如果這些子句在查詢中可用，返回的行也必須與WHERE和HAVING子句中的條件相匹配。
2.1 使用MySQL LEFT JOIN子句來連接兩個表
我們來看看在示例數(shù)據(jù)庫(yiibaidb)中的兩個表：訂單表和客戶表，兩個表的 ER 圖如下所示 -
在上面的數(shù)據(jù)庫圖中：
訂單(orders)表中的每個訂單必須屬于客戶(customers)表中的客戶。
客戶(customers)表中的每個客戶在訂單(orders)表中可以有零個或多個訂單。
要查詢每個客戶的所有訂單，可以使用LEFT JOIN子句，如下所示：
SELECT c.customerNumber, c.customerName, orderNumber, o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;SQL
執(zhí)行上面查詢語句，得到以下結(jié)果(部分) -
mysql> SELECT c.customerNumber, c.customerName, orderNumber, o.statusFROM customers cLEFT JOIN orders o ON c.customerNumber = o.customerNumber; ---------------- ------------------------------------ ------------- ------------ | customerNumber | customerName | orderNumber | status | ---------------- ------------------------------------ ------------- ------------ | 103 | Atelier graphique | 10123 | Shipped || 103 | Atelier graphique | 10298 | Shipped |... 省略部分 ...| 477 | Mit Vergngen & Co. | NULL | NULL || 480 | Kremlin Collectables, Co. | NULL | NULL || 481 | Raanan Stores, Inc | NULL | NULL || 484 | Iberia Gift Imports, Corp. | 10184 | Shipped || 484 | Iberia Gift Imports, Corp. | 10303 | Shipped || 486 | Motor Mint Distributors Inc. | 10109 | Shipped || 486 | Motor Mint Distributors Inc. | 10236 | Shipped | ---------------- ------------------------------------ ------------- ------------ 350 rows in setShell
左表是customers表，因此，所有客戶都包含在結(jié)果集中。 但是，結(jié)果集中有一些行具有客戶數(shù)據(jù)，但沒有訂單數(shù)據(jù)。如：customerNumber列值為：477，480等。這些行中的訂單數(shù)據(jù)為NULL。也就是說這些客戶在orders表中沒有任何訂單(未購買過任何產(chǎn)品)。
因?yàn)槲覀兪褂孟嗤牧忻?orderNumber)來連接兩個表，所以可以使用以下語法使查詢更短：
SELECT c.customerNumber, customerName, orderNumber, status
FROM customers c
LEFT JOIN orders USING (customerNumber);SQL
在上面查詢語句中，下面的子句 -
USING (customerNumber)SQL
相當(dāng)于 -
ON c.customerNumber = o.customerNumberSQL
如果使用INNER JOIN子句替換LEFT JOIN子句，則只能獲得至少有下過一個訂單的客戶。
2.2 使用MySQL LEFT JOIN子句來查找不匹配的行
當(dāng)您想要找到右表中與不匹配的左表中的行時，LEFT JOIN子句非常有用。要查詢兩個表之間的不匹配行，可以向SELECT語句添加一個WHERE子句，以僅查詢右表中的列值包含NULL值的行。
例如，要查找沒有下過訂單的所有客戶，請使用以下查詢：
SELECT c.customerNumber, c.customerName, orderNumber, o.status
FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE orderNumber IS NULL;SQL
執(zhí)行上面查詢語句，得到以下結(jié)果 -
mysql> SELECT c.customerNumber, c.customerName, orderNumber, o.status
FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE orderNumber IS NULL;
請參見以下示例。
SELECT o.orderNumber, customerNumber, productCode
FROM orders o
LEFT JOIN orderDetails USING (orderNumber)
WHERE orderNumber = 10123;SQL
在本示例中，我們使用LEFT JOIN子句來查詢orders表和orderDetails表中的數(shù)據(jù)。 該查詢返回訂單號為10123的訂單及其購買產(chǎn)品明細(xì)信息(如果有的話)。
但是，如果將條件從WHERE子句移動到ON子句：
SELECT o.orderNumber, customerNumber, productCodeFROM orders o LEFT JOIN orderDetails d ON o.orderNumber = d.orderNumber
AND o.orderNumber = 10123;SQL
想想上面代碼將會輸出什么結(jié)果 -
4.4 CROSS JOIN （笛卡爾積）
CROSS JOIN子句從連接的表返回行的笛卡兒乘積。
假設(shè)使用CROSS JOIN連接兩個表。 結(jié)果集將包括兩個表中的所有行，其中結(jié)果集中的每一行都是第一個表中的行與第二個表中的行的組合。 當(dāng)連接的表之間沒有關(guān)系時，會使用這種情況。
要特別注意的是，如果每個表有1000行，那么結(jié)果集中就有1000 x 1000 = 1,000,000行，那么數(shù)據(jù)量是非常巨大的。
下面說明連接兩個表：T1和T2的CROSS JOIN子句的語法：
SELECT *
FROM T1
CROSS JOIN T2;SQL
請注意，與INNER JOIN或LEFT JOIN子句不同，CROSS JOIN子句不具有連接條件。
如果添加了WHERE子句，如果T1和T2有關(guān)系，則CROSS JOIN的工作方式與INNER JOIN子句類似，如以下查詢所示：
SELECT *
FROM T1
CROSS JOIN T2
WHERE T1.id = T2.id;SQL
下面我們將使用以下幾個表來演示CROSS JOIN的工作原理。
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS products;
CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100), price DECIMAL(13 , 2 ));
DROP TABLE IF EXISTS sales;
CREATE TABLE stores ( id INT PRIMARY KEY AUTO_INCREMENT, store_name VARCHAR(100));
DROP TABLE IF EXISTS sales;
CREATE TABLE sales ( product_id INT, store_id INT, quantity DECIMAL(13 , 2 ) NOT NULL, sales_date DATE NOT NULL, PRIMARY KEY (product_id , store_id), FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (store_id) REFERENCES stores (id) ON DELETE CASCADE ON UPDATE CASCADE);SQL
上面語句中，創(chuàng)建了三個表：
產(chǎn)品(products)表包含產(chǎn)品編號，產(chǎn)品名稱和銷售價格等產(chǎn)品主要數(shù)據(jù)。
商店(stores)表包含銷售產(chǎn)品的商店信息。
銷售(sales)表包含在特定商店按數(shù)量和日期銷售的產(chǎn)品。
假設(shè)有三個產(chǎn)品：iPhone，iPad和Macbook Pro，在北部(North)和南部(South)的這兩個商店中出售。
INSERT INTO products(product_name, price)
VALUES('iPhone', 699), ('iPad',599), ('Macbook Pro',1299);
INSERT INTO stores(store_name)
VALUES('North'), ('South');
INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'), (1,2,15,'2017-01-05'), (1,3,25,'2017-01-05'), (2,1,30,'2017-01-02'), (2,2,35,'2017-01-05');SQL
要獲得每個商店和每個產(chǎn)品的總銷售額，您可以計算銷售額，并按商店和產(chǎn)品分組如下：
SELECT store_name, product_name, SUM(quantity * price) AS revenue
FROM sales
INNER JOIN products ON products.id = sales.product_id
INNER JOIN stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;SQL
執(zhí)行上面查詢，得到以下結(jié)果 -
mysql> SELECT store_name, product_name, SUM(quantity * price) AS revenueFROM sales INNER JOIN products ON products.id = sales.product_id INNER JOIN stores ON stores.id = sales.store_idGROUP BY store_name , product_name; ------------ -------------- ------------ | store_name | product_name | revenue | ------------ -------------- ------------ | North | iPad | 8985.0000 || North | iPhone | 13980.0000 || North | Macbook Pro | 32475.0000 || South | iPad | 20965.0000 || South | iPhone | 20970.0000 | ------------ -------------- ------------ 5 rows in setShell
現(xiàn)在，如果你想知道哪個商店中的哪些產(chǎn)品的沒有銷售怎么辦？ 上面的查詢無法回答這個問題。
要解決這個問題，可以使用CROSS JOIN子句。
首先，使用CROSS JOIN子句來獲取所有商店和產(chǎn)品的組合：
SELECT store_name, product_name
FROM stores AS a CROSS JOIN products AS b;SQL
執(zhí)行上面查詢語句，得到以下結(jié)果 -
mysql> SELECT store_name, product_nameFROM stores AS a CROSS JOIN products AS b; ------------ -------------- | store_name | product_name | ------------ -------------- | North | iPhone || South | iPhone || North | iPad || South | iPad || North | Macbook Pro || South | Macbook Pro | ------------ -------------- 6 rows in setShell
接下來，將上述查詢的結(jié)果與按商店和產(chǎn)品返回總銷售額的查詢相結(jié)合。以下查詢說明了這個想法：
SELECT b.store_name, a.product_name, IFNULL(c.revenue, 0) AS revenue
FROM products AS a CROSS JOIN stores AS b LEFT JOIN (SELECT stores.id AS store_id, products.id AS product_id, store_name, product_name, ROUND(SUM(quantity * price), 0) AS revenue
FROM sales
INNER JOIN products ON products.id = sales.product_id
INNER JOIN stores ON stores.id = sales.store_id
GROUP BY store_name , product_name) AS c ON c.store_id = b.id
AND c.product_id= a.idORDER BY b.store_name;SQL
請注意，如果收入為NULL(表示商店沒有銷售的產(chǎn)品)，則查詢使用IFNULL函數(shù)返回0。
通過這樣使用CROSS JOIN子句，可以解決類似這樣的問題，例如銷售人員按月查找銷售收入，即使推銷員在特定月份沒有銷售產(chǎn)品。
