从零开始学习MySQL--基础查询教程3--006

表连接

MySQL列的别名

使用 AS 关键词后跟别名，可省略，如果别名有空格用``，包起来

--查询全名
SELECT CONCAT_WS(', ', lastName, firstname) AS fullName FROM employees;
SELECT CONCAT_WS(', ', lastName, firstname) fullName FROM employees;
SELECT CONCAT_WS(', ', lastName, firstname) AS `Full Name` FROM employees;

MySQL表的别名

SELECT * FROM employees emp where emp.id=1;

内连接： INNER JOIN

--语法
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;

--连接具有外键关系的表， 如产品线( productlines )和产品( products )表。
--获取以下数据 -
--获取 products 表中的 productCode 和 productName 列的值。
--获取 productlines 表产品线的描述 - textDescription 列的值

SELECT productCode, productName, textDescription
FROM products t1
INNER JOINproductlines t2 ON t1.productline = t2.productline;

--由于两个表的连接列是使用相同一个列： productline
SELECT productCode, productName,textDescription
FROM products
INNER JOIN productlines USING (productline);

左外链接： LEFT JOIN

SELECT
t1.c1, t1.c2, t2.c1, t2.c2
FROM
t1
LEFT JOIN
t2 ON t1.c1 = t2.c1;

订单( orders )表中的每个订单必须属于客户( customers )表中的客户。

客户( customers )表中的每个客户在订单( orders )表中可以有零个或多个

SELECT c.customerNumber,c.customerName,orderNumber,o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;

GROUP BY子句

--使用 GROUP BY 子句并指定按 status 列来执行分组
SELECT status FROM orders GROUP BY status;

HAVING子句

--使用 GROUP BY 子句来获取订单号， 查看每个订单销售的商品数量和每个销售总额
SELECT ordernumber, SUM(quantityOrdered) AS itemsCount,SUM(priceeach*quantityOrdered) AS total
FROM orderdetails
GROUP BY ordernumber;

MySQL子查询

在WHERE子句中

--查询返回最大付款额的客户
SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount = (SELECT MAX(amount) FROM payments);
--查询大于子查询返回的平均付款的付款
SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount > (SELECT AVG(amount) FROM payments);