五、连接表
- mysql 列的别名
-
有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。
以下语句说明了如何使用列别名：
-
SELECT [column_1 | expression] AS descriptive_name FROM table_name;
要给列添加别名，可以使用
AS关键词后跟别名。 如果别名包含空格，则必须引用以下内容： -
SELECT [column_1 | expression] AS `descriptive name` FROM table_name;
因为
AS关键字是可选的，可以在语句中省略它。 请注意，还可以在表达式上使用别名。 - 示例：
-
mysql> use jialedb; Database changed mysql>
select concat(lastname,",",firstname) full_name from employees limit 5;
+-------------------+
| full_name |
+-------------------+
| Murphy,Diane |
| Patterson,Mary |
| Firrelli,Jeff |
| Patterson,William |
| Bondur,Gerard |
+-------------------+ - 请注意，不能在WHERE子句中使用列别名。原因是当MySQL评估求值
WHERE子句时，SELECT子句中指定的列的值可能尚未确定。 -
该表的别名称为表别名。像列别名一样，
AS关键字是可选的，所以完全可以省略它。一般在包含INNER JOIN，LEFT JOIN，self join子句和子查询的语句中使用表别名。
下面来看看客户(
customers)和订单(orders)表，它们的ER图如下所示 -
-
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; +------------------------------+-------+ | customerName | total | +------------------------------+-------+ | Euro+ Shopping Channel | 26 | | Mini Gifts Distributors Ltd. | 17 | | Reims Collectables | 5 | | Down Under Souveniers, Inc | 5 |
inner join 子句介绍
-
在使用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;
示例：
-
在上面图中，
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;
SELECT productCode, productName, textDescription FROM products INNER JOIN productlines USING (productline);
以下查询使用少于(
<)连接来查找低于代码为S10_1678的产品的销售价格的制造商建议零售价(MSRP)的所有产品。 -
SELECT orderNumber, productName, msrp, priceEach FROM products p INNER JOIN orderdetails o ON p.productcode = o.productcode and p.msrp > o.priceEace WHERE p.productcode = 'S10_1678';
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;
left join 示例：
-
在上面的数据库图中： 订单(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;
我们使用
LEFT JOIN子句来查询orders表和orderDetails表中的数据。 该查询返回订单号为10123的订单及其购买产品明细信息(如果有的话)。 -
SELECT o.orderNumber, customerNumber, productCode FROM orders o LEFT JOIN orderDetails USING (orderNumber) WHERE orderNumber = 10123; +-------------+----------------+-------------+ | orderNumber | customerNumber | productCode | +-------------+----------------+-------------+ | 10123 | 103 | S18_1589 | | 10123 | 103 | S18_2870 | | 10123 | 103 | S18_3685 | | 10123 | 103 | S24_1628 | +-------------+----------------+-------------+ 4 rows in set
但是，如果将条件从
WHERE子句移动到ON子句： -
mysql> SELECT o.orderNumber, customerNumber, productCode FROM orders o LEFT JOIN orderDetails d ON o.orderNumber = d.orderNumber AND o.orderNumber = 10123; +-------------+----------------+-------------+ | orderNumber | customerNumber | productCode | +-------------+----------------+-------------+ | 10123 | 103 | S18_1589 | | 10123 | 103 | S18_2870 | | 10123 | 103 | S18_3685 | | 10123 | 103 | S24_1628 | | 10298 | 103 | NULL | | 10345 | 103 | NULL | | 10124 | 112 | NULL | .... .... | 10179 | 496 | NULL | | 10360 | 496 | NULL | | 10399 | 496 | NULL | +-------------+----------------+-------------+ 329 rows in set
请注意，对于INNER JOIN子句，
ON子句中的条件等同于WHERE子句中的条件。 CROSS JOIN子句-
CROSS JOIN子句从连接的表返回行的笛卡儿乘积。假设使用
CROSS JOIN连接两个表。 结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。要特别注意的是，如果每个表有
1000行，那么结果集中就有1000 x 1000 = 1,000,000行，那么数据量是非常巨大的。下面说明连接两个表：
T1和T2的CROSS JOIN子句的语法： -
SELECT * FROM T1 CROSS JOIN T2;
请注意，与INNER JOIN或LEFT JOIN子句不同，
CROSS JOIN子句不具有连接条件。如果添加了
WHERE子句，如果T1和T2有关系，则CROSS JOIN的工作方式与INNER JOIN子句类似，如以下查询所示： -
SELECT * FROM T1 CROSS JOIN T2 WHERE T1.id = T2.id;
- 六、分组数据
- mysql group by 子句简介
-
GROUP BY子句通过列或表达式的值将一组行分组为一个小分组的汇总行记录。GROUP BY子句为每个分组返回一行。换句话说，它减少了结果集中的行数。经常使用
GROUP BY子句与聚合函数一起使用，如SUM，AVG，MAX，MIN和COUNT。SELECT子句中使用聚合函数来计算有关每个分组的信息。GROUP BY子句是SELECT语句的可选子句。 下面是GROUP BY子句语法： -
SELECT c1, c2,..., cn, aggregate_function(ci) FROM table WHERE where_conditions GROUP BY c1 , c2,...,cn;
示例：
- 要按状态获取所有订单的总金额，可以使用
orderdetails表连接orders表，并使用SUM函数计算总金额。请参阅以下查询： - 如果想知道每个状态中的订单数，可以使用
COUNT函数与GROUP BY子句查询语句，如下所示： -
SELECT status, SUM(quantityOrdered * priceEach) AS amount FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY status;
mysql> select year(orderdate) year,sum(quantityOrdered * priceEach) total
from orders inner join orderdetails using(ordernumber)
where status="shipped" group by year(orderdate); +------+------------+ | year | total | +------+------------+ | 2013 | 3223095.80 | | 2014 | 4300602.99 | | 2015 | 1341395.85 | +------+------------+ 3 rows in set (0.10 sec)having 子句简介
HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。- 请注意，
HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。 - 示例：
- 现在，可以通过使用
HAVING子句查询(过滤)哪些订单的总销售额大于55000，如下所示： -
SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 55000;
可以使用逻辑运算符(如
OR和AND)在HAVING子句中构造复杂过滤条件。 假设您想查找哪些订单的总销售额大于50000，并且包含超过600个项目，则可以使用以下查询： -
SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(priceeach*quantityOrdered) AS total FROM orderdetails GROUP BY ordernumber HAVING total > 50000 AND itemsCount > 600;