Joining Tables
SELECT/JOIN
JOIN 用于连接两个或多个表，基于这些表之间的相关列。主要有内连接、外连接等类型。 注意连接顺序会影响查询结果，难点在于如何正确合并多个表
-- JOIN/INNER JOIN (内连接) - 返回两个表中都有的匹配行
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
-- LEFT JOIN (左连接) - 返回左表的所有行及右表的匹配行
SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
-- RIGHT JOIN (右连接) - 返回右表的所有行及左表的匹配行
SELECT customers.name, orders.order_date
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;
---值得注意的是，交换表顺序，LEFT↔RIGHT JOIN功能互换（对称）
-- FULL OUTER JOIN (全外连接) - 返回两个表的所有行
SELECT customers.name, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;
-- 多表连接示例
SELECT c.name, o.order_date, p.product_name
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id;| JOIN类型 | 场景 | SQL | 结果 (关键行) |
|---|---|---|---|
| INNER | 只关心匹配（有订单客户） | SELECT * FROM C INNER JOIN O ON C.id=O.cust_id | Alice(101,102); 无Bob/103 |
| LEFT | 全客户+订单（查缺订单） | SELECT * FROM C LEFT JOIN O ON C.id=O.cust_id | Alice(101,102); Bob(NULL); 无103 |
| RIGHT | 全订单+客户（查匿名订单） | SELECT * FROM C RIGHT JOIN O ON C.id=O.cust_id | Alice(101,102); NULL(103); 无Bob |
| FULL | 全数据合并（审计/补全） | SELECT * FROM C FULL OUTER JOIN O ON C.id=O.cust_id | Alice(101,102); Bob(NULL); NULL(103) |
Cartesian Product
笛卡尔积是两个表的每一行都与另一个表的每一行进行组合，结果行数等于两表行数的乘积。通常需要避免，除非特殊需求。
-- 笛卡尔积示例 (没有连接条件的 JOIN)
SELECT *
FROM table1, table2;
-- 避免笛卡尔积的正确写法
SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id;STR TO DATE
STR TO DATE 是将字符串转换为日期格式的功能，不同数据库系统有不同的函数实现，此处介绍 MySQL 的 STR_TO_DATE()
-- MySQL 中的 STR_TO_DATE 函数
SELECT STR_TO_DATE('2023-12-25', '%Y-%m-%d') AS converted_date;
SELECT STR_TO_DATE('25/12/2023', '%d/%m/%Y') AS converted_date;
SELECT STR_TO_DATE('Dec 25 2023', '%b %d %Y') AS converted_date;Nested Queries
嵌套查询是指在一个查询内部包含另一个查询，也称为子查询。外部查询依赖于内部查询的结果。
-- 标量子查询 (返回单行单列)
SELECT name, salary,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;
-- 在 WHERE 子句中使用 IN 的子查询
SELECT * FROM employees
WHERE department_id IN (
SELECT id FROM departments
WHERE location = 'Beijing'
);
-- 使用比较运算符的子查询
SELECT * FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);
-- 相关子查询 (子查询引用外部查询的列)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
-- 多层嵌套查询
SELECT name
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE company_id IN (
SELECT id
FROM companies
WHERE industry = 'Technology'
)
);