⑧【MySQL】数据库查询：内连接、外连接、自连接、子查询、多表查询

内连接（INNER JOIN）:

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field;

外连接（LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN）:

• 左外连接（LEFT JOIN）:

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field;

• 右外连接（RIGHT JOIN）:

SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;

• 全外连接（不常用，MySQL不直接支持，需要结合LEFT JOIN和UNION来实现）:

SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_field = b.common_field
UNION
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_field = b.common_field;

自连接（同一个表内的不同行之间的关系）:

SELECT a.column1, b.column1 AS alias_column1
FROM table a
JOIN table b ON a.common_field = b.common_field AND a.id <> b.id;

子查询（Subquery）:

SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

多表查询（多个表同时进行内连接或外连接）:

SELECT a.column1, b.column2, c.column3
FROM table1 a
INNER JOIN table2 b ON a.common_field = b.common_field
LEFT JOIN table3 c ON b.common_field = c.common_field;

以上代码示例展示了如何在MySQL中使用不同类型的连接以及子查询进行数据库查询操作。