7、SQL语言（5）

DQL

多表查询

多表查询是指在查询时涉及到多个表的操作。常用的多表查询方式包括联结（Join）、子查询（Subquery）、联合查询（Union）等。

联结（Join）是最常用的多表查询方式之一，它通过连接多个表的列，从而实现多表查询的目的。Join有多种类型，包括内连接（Inner Join）、左连接（Left Join）、右连接（Right Join）和全连接（Full Join）等。

子查询（Subquery）是将一个查询语句嵌入到另一个查询语句中作为条件的方式。在子查询中，通常使用了关键字IN、EXISTS或者比较运算符（如=、>、<等）来连接两个查询语句，从而实现多表查询的目的。

联合查询（Union）是将两个或多个查询语句的结果集合并为一个结果集的方式。联合查询要求每个查询语句的列数和列类型相同，否则需要使用转换函数将结果转换为相同类型的数据。

子查询

常用在WHERE子句中的子查询

1. 用于比较表达式中的子查询；子查询仅能返回单个值

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);

2. 用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

3. 用于EXISTS 和 Not EXISTS

SELECT column_name(s) FROM table_name1 WHERE EXISTS(SELECT column_name FROM table_name2 WHERE condition);
SELECT * FROM customers c WHERE EXISTS(SELECT * FROM orders o WHERE o.customer_id = c.customer_id AND o.price > 100);
这个查询中，customers 表是主查询表，orders 表是子查询表。子查询的条件是 o.customer_id = c.customer_id AND o.price > 100，表示只要 orders 表中存在客户的订单价格大于 100，主查询就返回该客户的信息

4、用于FROM子句中的子查询

FROM子查询指的是将一个SELECT语句作为另一个SELECT语句的FROM子句中的一部分，用于产生虚拟表，以便与另一个表联接。它可以用于复杂的查询，例如从多个表中选择数据和使用聚合函数。常见的用法包括生成汇总报告、分析和比较数据

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;

SELECT *
FROM (
  SELECT *
  FROM customers
  WHERE country = 'USA'
) AS us_customers
WHERE us_customers.state = 'NY';
在此查询中，外部SELECT语句从从子查询中返回填满了符合固定USA国家的数据客户列表结果集。子查询过滤条件过滤掉非USA国家的客户。然后，外部SELECT语句进一步筛选出NY州的客户记录。

联合查询：UNION

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers; 纵向联合

交叉连接

​交叉连接 (Cross join) 是一种MySQL中的查询操作，也被称为笛卡尔积（Cartesian Product）。它会返回两个表中所有行的组合，即没有任何条件限制，返回两个表中每一行的所有组合结果。交叉连接可以使用 CROSS JOIN 或者省略 JOIN 关键字，直接使用逗号 , 连接两个表来实现；

SELECT *FROM table1 CROSS JOIN table2;
SELECT *FROM table1,table2;

SQL JOIN

inner join

left join和right join

在 SQL 中，LEFT OUTER JOIN 和 LEFT JOIN 是等价的，两者的作用是一样的，都是返回左表中所有的记录，再匹配右表中符合条件的记录，没有匹配的记录设置为NULL。

只不过LEFT OUTER JOIN 是标准 ANSI SQL 的语法，而LEFT JOIN 是 MySQL 数据库特有的语法，不过在大多数情况下，它们的效果是相同的，可以通用。

full join

MariaDB [hellodb]> select * from students s full join teachers t on s.teacherid=t.tid;  

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'full join teachers t on s.teacherid=t.tid' at line 1

MYSQL 不支持full join，可使用left join和right join 和union 变通处理

self join

是一种特殊的关系型数据库表（或视图）连接，用于连接同一表中的不同行。在自链接中，同一表被视为两个不同的表，并且需要至少两个表别名或引用原表两次或以上，每个引用都应使用不同的别名。

以下是一个自链接的示例，假设有一个名为employee的表，包含以下字段：id，name，manager_id。其中 id 是员工的唯一标识符，name 是员工的姓名，manager_id 是员工的经理的标识符。

我们可以使用自链接查询来查找每个员工的经理的姓名。查询如下：

SELECT e.name AS employee_name, m.name AS manager_name,IFNULL(manager_name,'无上级')
FROM employee e
left JOIN employee m ON e.manager_id = m.id;
在上面的查询中，我们使用了两个表别名（e 和m），其中 e 用于引用原表的当前行，而m 用于引用原表的 manager_id 所对应的行，从而找到对应经理的姓名。通过自链接，我们得到了一个包含每个员工姓名及其经理姓名的结果集。

SELECT语句处理的顺序

SELECT语句的执行流程：

查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎