4.1 别名
有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。
以下语句说明了如何使用列别名：
SELECT [column_1 | expression] AS descriptive_name
FROM table_name;SQL
要给列添加别名，可以使用AS关键词后跟别名。 如果别名包含空格，则必须引用以下内容：
SELECT [column_1 | expression] AS `descriptive name`
FROM table_name;SQL
因为AS关键字是可选的，可以在语句中省略它。 请注意，还可以在表达式上使用别名。
我们来看看示例数据库(yiibaidb)中的employees表，其表结构如下所示 -
以下查询选择员工的名字和姓氏，并将其组合起来生成全名。 CONCAT_WS函数用于连接名字和姓氏。
SELECT CONCAT_WS(', ', lastName, firstname)
FROM employees;SQL
执行上面代码，得到以下结果 -
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
在上面示例中，列标题很难阅读理解。可以为输出的标题分配一个有意义的列别名，以使其更可读，如以下查询：
SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM employees;SQL
执行上面代码，得到以下结果 -
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
执行上面代码，得到以下结果 -
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
执行上面查询语句，得到以下结果 -
请注意，不能在WHERE子句中使用列别名。原因是当MySQL评估求值
WHERE子句时，SELECT子句中指定的列的值可能尚未确定。
可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名，如下查询语句语法：
table_name AS table_aliasSQL
该表的别名称为表别名。像列别名一样，AS关键字是可选的，所以完全可以省略它。
一般在包含INNER JOIN，LEFT JOIN，self join子句和子查询的语句中使用表别名。
下面来看看客户(customers)和订单(orders)表，它们的ER图如下所示 -
两个表都具有相同的列名称：customerNumber。如果不使用表别名来指定是哪个表中的customerNumber列，则执行查询时将收到类似以下错误消息：
Error Code: 1052. Column 'customerNumber' in on clause is ambiguousShell
为避免此错误，应该使用表别名来限定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
执行上面查询语句，得到以下结果 -
上面的查询从客户(customers)和订单(orders)表中选择客户名称和订单数量。 它使用c作为customers表的表别名，o作为orders表的表别名。customers和orders表中的列通过表别名(c和o)引用。
如果您不在上述查询中使用别名，则必须使用表名称来引用其列，这样的会使得查询冗长且可读性较低，如下 -
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子句将一个表中的行与其他表中的行进行匹配，并允许从两个表中查询包含列的行记录。
INNER JOIN子句是SELECT语句的可选部分，它出现在FROM子句之后。
在使用INNER JOIN子句之前，必须指定以下条件：
首先，在FROM子句中指定主表。
其次，表中要连接的主表应该出现在INNER JOIN子句中。理论上说，可以连接多个其他表。 但是，为了获得更好的性能，应该限制要连接的表的数量(最好不要超过三个表)。
第三，连接条件或连接谓词。连接条件出现在INNER JOIN子句的ON关键字之后。连接条件是将主表中的行与其他表中的行进行匹配的规则。
INNER JOIN子句的语法如下：
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;SQL
假设使用INNER JOIN子句连接两个表：t1和t2，我们来简化上面的语法。
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;SQL
对于t1表中的每一行，INNER JOIN子句将它与t2表的每一行进行比较，以检查它们是否都满足连接条件。当满足连接条件时，INNER JOIN将返回由t1和t2表中的列组成的新行。
请注意，t1和t2表中的行必须根据连接条件进行匹配。如果找不到匹配项，查询将返回一个空结果集。当连接超过2个表时，也应用此逻辑。
以下维恩图说明了INNER JOIN子句的工作原理。结果集中的行必须出现在两个表中：t1和t2，如两个圆的交叉部分所示 -
如果连接具有相同列名称的多个表，则必须使用表限定符引用SELECT和ON子句的列，以避免列错误。
例如，如果t1和t2表都具有名为c的一个相同列名，则必须在SELECT和ON子句中使用表限定符，如使用t1.c或t2.c指定引用是那个表中的c列。
为了节省书写表限定符的时间，可以在查询中使用表别名。 例如，可以长名称verylonglonglong_tablename表使用表别名，并使用t.column引用其列，而不是使用verylonglonglong_tablename.column，但是如果喜欢书写或使用这么长的表名称，那么也应该允许你的开发伙伴骂你几句类似：傻逼~等这样的话！
下面来看看示例数据库(yiibaidb)中的产品(products)和产品线(productlines)表。它们的 ER 图如下所示 -
在上面图中，products表中的productLine列参考引用productlines表的productline列。 products表中的productLine列称为外键列。
通常，连接具有外键关系的表，如产品线(productlines)和产品(products)表。现在，如果想获取以下数据 -
获取products表中的productCode和productName列的值。
获取productlines表产品线的描述 - textDescription列的值。
为此，需要通过使用INNER JOIN子句根据productline列匹配行来从两个表中查询选择数据，如下所示：
SELECT productCode, productName, textDescription
FROM products t1
INNER JOIN productlines t2 ON t1.productline = t2.productline;SQL
执行上面查询，得到下面的结果(部分)-
由于两个表的连接列是使用相同一个列：productline，因此可以使用以下语法：
SELECT productCode, productName, textDescription
FROM products
INNER JOIN productlines USING (productline);SQL
上面语句返回相同的结果集，但是使用此语法，不必使用表的别名。
MySQL INNER JOIN GROUP BY子句
请参阅以下订单和订单详细表，orders表和orderdetails表的结构如下所示 -
可以使用具有GROUP BY子句的INNER JOIN子句从orders和orderdetails表中获取订单号，订单状态和总销售额，如下所示：
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
执行上面查询，结果如下所示(部分) -
类似地，以下语句查询与上述得到结果相同：
SELECT orderNumber, status, SUM(quantityOrdered * priceEach) total
FROM orders
INNER JOIN orderdetails USING (orderNumber)
GROUP BY orderNumber;SQL
MySQL INNER JOIN使用等于以外的运算符
到目前为止，您已经看到连接谓词使用相等的运算符(=)来匹配行。但是也可以使用大于(>)，小于(<)和不等于(<>)运算符的其他运算符来形成连接谓词。
以下查询使用少于(<)连接来查找低于代码为S10_1678的产品的销售价格的制造商建议零售价(MSRP)的所有产品。
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
执行上面查询语句，得到以下输出结果 -
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子句允许您从两个或多个数据库表查询数据。LEFT JOIN子句是SELECT语句的可选部分，出现在FROM子句之后。
我们假设要从两个表t1和t2查询数据。以下语句说明了连接两个表的LEFT JOIN子句的语法：
SELECT t1.c1, t1.c2, t2.c1, t2.c2
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1;SQL
当使用LEFT JOIN子句将t1表加入t2表时，如果来自左表t1的行与基于连接条件(t1.c1 = t2.c1)的右表t2匹配，则该行将被包含在结果集中。
如果左表中的行与右表中的行不匹配，则还将选择左表中的行并与右表中的“假”行组合。“假”行对于SELECT子句中的所有相应列都包含NULL值。
换句话说，LEFT JOIN子句允许您从匹配的左右表中查询选择行记录，连接左表(t1)中的所有行，即使在右表(t2)中找不到匹配的行也显示出来，但使用NULL值代替。
下图可帮助您可视化LEFT JOIN子句的工作原理。 两个圆圈之间的交点是两个表中匹配的行，左圆的剩余部分(白色部分)是t1表中不存在t2表中任何匹配行的行。 因此，左表中的所有行都包含在结果集中。
请注意，如果这些子句在查询中可用，返回的行也必须与WHERE和HAVING子句中的条件相匹配。
2.1 使用MySQL LEFT JOIN子句来连接两个表
我们来看看在示例数据库(yiibaidb)中的两个表：订单表和客户表，两个表的 ER 图如下所示 -
在上面的数据库图中：
订单(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
执行上面查询语句，得到以下结果(部分) -
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表，因此，所有客户都包含在结果集中。 但是，结果集中有一些行具有客户数据，但没有订单数据。如：customerNumber列值为：477，480等。这些行中的订单数据为NULL。也就是说这些客户在orders表中没有任何订单(未购买过任何产品)。
因为我们使用相同的列名(orderNumber)来连接两个表，所以可以使用以下语法使查询更短：
SELECT c.customerNumber, customerName, orderNumber, status
FROM customers c
LEFT JOIN orders USING (customerNumber);SQL
在上面查询语句中，下面的子句 -
USING (customerNumber)SQL
相当于 -
ON c.customerNumber = o.customerNumberSQL
如果使用INNER JOIN子句替换LEFT JOIN子句，则只能获得至少有下过一个订单的客户。
2.2 使用MySQL LEFT JOIN子句来查找不匹配的行
当您想要找到右表中与不匹配的左表中的行时，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
执行上面查询语句，得到以下结果 -
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表中的数据。 该查询返回订单号为10123的订单及其购买产品明细信息(如果有的话)。
但是，如果将条件从WHERE子句移动到ON子句：
SELECT o.orderNumber, customerNumber, productCodeFROM orders o LEFT JOIN orderDetails d ON o.orderNumber = d.orderNumber
AND o.orderNumber = 10123;SQL
想想上面代码将会输出什么结果 -
4.4 CROSS JOIN （笛卡尔积）
CROSS JOIN子句从连接的表返回行的笛卡儿乘积。
假设使用CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。
要特别注意的是，如果每个表有1000行，那么结果集中就有1000 x 1000 = 1,000,000行，那么数据量是非常巨大的。
下面说明连接两个表：T1和T2的CROSS JOIN子句的语法：
SELECT *
FROM T1
CROSS JOIN T2;SQL
请注意，与INNER JOIN或LEFT JOIN子句不同，CROSS JOIN子句不具有连接条件。
如果添加了WHERE子句，如果T1和T2有关系，则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
上面语句中，创建了三个表：
产品(products)表包含产品编号，产品名称和销售价格等产品主要数据。
商店(stores)表包含销售产品的商店信息。
销售(sales)表包含在特定商店按数量和日期销售的产品。
假设有三个产品：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
要获得每个商店和每个产品的总销售额，您可以计算销售额，并按商店和产品分组如下：
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
执行上面查询，得到以下结果 -
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
现在，如果你想知道哪个商店中的哪些产品的没有销售怎么办？ 上面的查询无法回答这个问题。
要解决这个问题，可以使用CROSS JOIN子句。
首先，使用CROSS JOIN子句来获取所有商店和产品的组合：
SELECT store_name, product_name
FROM stores AS a CROSS JOIN products AS b;SQL
执行上面查询语句，得到以下结果 -
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
接下来，将上述查询的结果与按商店和产品返回总销售额的查询相结合。以下查询说明了这个想法：
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(表示商店没有销售的产品)，则查询使用IFNULL函数返回0。
通过这样使用CROSS JOIN子句，可以解决类似这样的问题，例如销售人员按月查找销售收入，即使推销员在特定月份没有销售产品。
联系客服
