MYSQL数据查询 - 连接查询（内连接、外连接）、复合条件查询、合并查询结果

站长

2023年11月22日 13:56 · 阅读数 31

连接查询

连接查询是关系型数据库的主要特点，通过连接运算符可以实现多个表连接查询。

当两个或者多个表中存在相同字段时，便可以通过这些字段对不同的表进行连接查询。

INNNER JOIN

内连接使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组成新的记录。也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

SELECT 后面的字段是两个表中的字段，FROM后面跟两个表，WHERE子句的查询条件表明只有在fruits的s_id和suppliers的s_id相同的时候，才是符合条件的查询结果。

SELECT suppliers.s_id, s_name, f_name, f_price
FROM fruits, suppliers
WHERE fruits.s_id = suppliers.s_id;

INNER JOIN，与上面WHERE子句的查询结果一样：

SELECT suppliers.s_id, s_name, f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;

使用INNER JOIN ... ON ...的语法确保连接条件不会被忘记。而WHERE子句在某些时候会影响查询性能。

自连接：如果在一个查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接查询。相互连接的表在物理上是同一张表，在逻辑上分为两张表。

SELECT f1.f_id, f1.f_name
FROM fruits AS f1, fruits AS f2
WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

内连接的查询结果集合中仅包含符合查询条件和连接条件的行。

外连接有时候需要包含没有关联的行中数据，即返回结果集合中不仅包含符合连接条件的行，还包含左表、右表、或两个连接表中的所有行数据。外连接分为左连接、右连接。

返回包括左表中的所有记录和右表中连接字段相等的记录。

LEFT JOIN

返回包括右表中的所有记录和左表中连接字段相等的记录。

RIGHT JOIN

其实就是在连接查询的基础上添加一些其他的查询条件，比如WHERE子句的条件表达式、GROUP BY分组、ORDER BY排序等；

子查询指一个查询语句嵌套在另一个查询语句内的查询。

在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表；

num1的值大于任意一个num2的值，则符合查询条件

SELECT num1 FROM tab1 WHERE num1 > ANY(SELECT num2 FROM tab2);

num1的值大于所有num2的值，则符合查询条件

SELECT num1 FROM tab1 WHERE num1 > ALL(SELECT num2 FROM tab2);

EXISTS后面是一个任意的子查询，系统对自查询进行运算，判断它是否返回。

这个示例，子查询的条件是是否存在107这个人，如果存在，才会执行SELECT * FROM fruits语句

SELECT * FROM fruits
WHERE EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT * FROM fruits
WHERE f_price > 10.20 AND EXISTS
(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT c_id
FROM orders
WHERE o_num
IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

子查询的功能也可以通过连接查询实现，但是子查询代码使MYSQL代码更容易阅读和编写。

SELECT s_id, f_name
FROM fruits
WHERE s_id = 
(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

这个示例，先通过子查询(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin')查到符合条件的s_id，再通过外层嵌套的查询通过WHERE子句比较，的到最终的查询结果；

利用UNION，可以给出多条SELECT语句，并将他们的结果组合成单个结果集。

合并时，两个表对应的列数和数据类型必须相同。

各个SELECT语句之间使用UNION或者UNION ALL关键字分割。

UNION 执行的时候删除重复的记录，所有返回的行都是唯一的；UNION ALL 执行的时候删除重复的记录。

SELECT column, ... FROM table1
UNION [ALL]
SELECT column, ... FROM table2

UNION ALL的查询效率比UNION的高，如果没必要删除重复行，使用UNION ALL来查询。