MySQL数据库高级查询（联合查询、内连接、外连接、子查询、连接查询、全连接查询）

多表查询分类

以下是常见的多表查询分类：

多表查询分类	描述	示例SQL语句
联合查询	使用UNION或UNION ALL操作符将两个或更多的表的结果直接合并在一起。	SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;
内连接查询	使用INNER JOIN操作符根据连接条件将两个或多个表进行内连接，返回满足条件的记录。	SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
外连接查询	使用OUTER JOIN操作符将两个或多个表进行外连接，返回包括满足条件记录以及不满足条件记录的结果。	SELECT table1.column1, table2.column2 FROM table1 LEFT OUTER JOIN table2 ON table1.common_column = table2.common_column;
自连接查询	将一个表自身连接起来，通常用于处理具有对称关系的数据。	SELECT column1, column2 FROM table1 WHERE column3 = 'value';
子查询	在一个查询语句的内部嵌套了另一个完整的查询语句。	SELECT column1, column2 FROM table1 WHERE column3 IN (SELECT column4 FROM table2);
连接查询	使用JOIN操作符将多个表连接起来，通过指定连接条件获取相关联的数据。包括INNER JOIN、LEFT JOIN、RIGHT JOIN等。	SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;

联合查询

基本概念：UNION、UNION ALL可以把两个或更多的查询结果合并在一起

注意：

UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。删除重复的数据

UNION ALL操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。保留重复的数据

SELECT name FROM employees_china UNION SELECT name FROM employees_usa;

SELECT name FROM employees_china UNION ALL SELECT name FROM employees_usa;

连接查询

连接查询（JOIN）是用于将两个或多个表中的数据按照某个列进行关联的查询操作。通过连接查询，我们可以从多个表中获取相关联的数据，并进行合并、比较等操作。

内连接

语法

SELECT 列名  
FROM 表1  
INNER JOIN 表2  
ON 表1.列 = 表2.列;

表1和表2是要进行内连接的两个表，列是用于匹配的列。ON关键字后面的条件指定了匹配规则，它可以是任意的逻辑表达式，通常用于比较两个表的列值。

内连接只返回匹配成功的行。如果两个表的某一对匹配的行在连接条件上不符合要求，那么这一对行就不会出现在结果集中。

SELECT * FROM student INNER JOIN score ON student.stu_no = score.stu_no;

注意：关键字可以省略，MySQL默认的连接方式就是内连接，不需要使用WHERE关键字，而是使用ON关键字

SELECT * FROM student , score WHERE student.stu_no = score.stu_no;

SELECT sc.stu_no,st.stu_name,sc.stu_score
FROM score sc
INNER JOIN student st
ON st.stu_no = sc.stu_no;

左连接（LEFT JOIN）

左连接（LEFT JOIN）是一种数据库查询语言中的连接操作，用于将两个表中的数据按照指定的条件进行关联。左连接返回左表中的所有记录以及与右表匹配的记录，如果没有匹配的记录则返回NULL值。

SELECT sc.stu_no,st.stu_name,sc.stu_score
FROM student st
LEFT JOIN score sc
ON st.stu_no = sc.stu_no;

右连接（RIGHT JOIN）

右连接（RIGHT JOIN）是一种数据库查询语言中的连接操作，用于将两个表中的数据按照指定的条件进行关联。右连接返回右表中的所有记录以及与左表匹配的记录，如果没有匹配的记录则返回NULL值。

SELECT sc.stu_no,st.stu_name,sc.stu_score
FROM  student st
RIGHT JOIN score sc
ON st.stu_no = sc.stu_no;

注意：右连接在处理大数据量时的性能可能不如内连接或左连接。因此，应该根据实际情况选择合适的连接方式和优化策略，以提高查询效率。

外连接（左连接、右连接）

外连接（Outer Join）是另一种数据库查询操作，用于返回连接表中未匹配的行。外连接分为左外连接（Left Outer Join）和右外连接（Right Outer Join）。

左外连接

左外连接返回左表中的所有行，以及与右表中匹配成功的行。如果左表中的某行在右表中没有匹配的行，那么结果集中来自右表的列将显示为NULL。

语法：

SELECT 列名  
FROM 表1  
LEFT OUTER JOIN 表2  
ON 表1.列 = 表2.列;

LEFT OUTER JOIN关键字后面的条件指定了连接条件，它用于匹配两个表的列值。

SELECT * FROM student LEFT JOIN score ON student.stu_no = score.stu_no;

右外连接

右外连接返回右表中的所有行，以及与左表中匹配成功的行。如果右表中的某行在左表中没有匹配的行，那么结果集中来自左表的列将显示为NULL。

语法：

SELECT 列名  
FROM 表1  
RIGHT OUTER JOIN 表2  
ON 表1.列 = 表2.列;

RIGHT OUTER JOIN关键字后面的条件指定了连接条件，它用于匹配两个表的列值。

SELECT * FROM student RIGHT JOIN score ON student.stu_no = score.stu_no;

需要注意的是，不是所有的数据库系统都支持全外连接（Full Outer Join），一些数据库系统可能只支持左外连接或右外连接。

子查询

子查询:当一个查询是另一个查询的条件时，称之为子查询。

子查询就是嵌套查询，即 SELECT 中包含SELECT，如果一条语句中存在两个或两个以上 SELECT，就是子查询

子查询可以出现在主查询之前，也可以出现在主查询之后。子查询返回的数据将作为主查询进一步匹配和筛选的条件。

在SQL中，子查询通常嵌套在SELECT、FROM或WHERE等关键字后面，作为查询条件的一部分。子查询可以根据主查询提供的数据，动态生成查询条件，从而实现更加灵活的数据筛选和操作。

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

单行单列(用于条件)

查询大于学生‘李四’年龄的学生信息

SELECT * FROM student stu1 WHERE stu1.age >(SELECT stu2.age FROM student stu2 WHERE stu2.stu_name = '李四');

注意：在实际工作中单行单列的子查询使用比较多

单行多列(用于条件)

查询与学号stu_01相同分数的学生

SELECT * FROM score sc WHERE(sc.course_no , sc.stu_score) IN (SELECT sc2.course_no , sc2.stu_score FROM score sc2 WHERE sc2.stu_no = 'stu_01');

注意：在实际工作中单行多列的子查询使用比较少，但是我们需要了解基本用法

多行单列(用于条件)

查询学生‘王五’的成绩，显示课程编号、学生学号、学生成绩

SELECT sc.course_no , sc.stu_no , sc.stu_score FROM score sc,(SELECT st.stu_no FROM student st WHERE st.stu_name = '王五')t2 WHERE sc.stu_no = t2.stu_no;

注意：在实际工作中多行多列的子查询使用比较少，但是我们需要了解基本用法

多行多列(用于表)

查询学生‘王五’的成绩，显示课程编号、学生学号、学生姓名、学生成绩

SELECT sc.course_no , sc.stu_no ,t2.stu_name, sc.stu_score FROM score sc,(SELECT * FROM student st WHERE st.stu_name = '王五')t2 WHERE sc.stu_no = t2.stu_no;