MySQL知识（十）——连接查询与子查询
来源：互联网 发布：小米移动4g网络 编辑：程序博客网 时间：2024/05/16 02:32
1 连接查询
连接查询是关系数据库中最主要的查询，主要包括连接、外连接等。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。
1.1 内连接查询
内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的纪录。在内连接查询中，只有满足条件的记录才能出现在结果关系中。
（1）fruits表和suppliers表的结构：
mysql> DESC fruits;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| f_id | char(10) | NO | PRI | NULL | || s_id | int(11) | NO | | NULL | || f_name | char(255) | NO | | NULL | || f_price | decimal(8,2) | NO | | NULL | |+---------+--------------+------+-----+---------+-------+4 rows in setmysql> DESC suppliers;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| s_id | int(11) | NO | PRI | NULL | auto_increment || s_name | char(50) | NO | | NULL | || s_city | char(50) | YES | | NULL | || s_zip | char(10) | YES | | NULL | || s_call | char(50) | NO | | NULL | |+--------+----------+------+-----+---------+----------------+5 rows in set（2）SELECT、FROM、WHERE查询
mysql> SELECT suppliers.s_id,s_name,f_name,f_price -> FROM fruits,suppliers -> WHERE fruits.s_id=suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 101 | FastFruit Inc. | apple | 5.2 || 103 | ACME | apricot | 2.2 || 101 | FastFruit Inc. | blackberry | 10.2 || 104 | FNK Inc. | berry | 7.6 || 107 | DK Inc. | xxxx | 3.6 || 102 | LT Supplies | orange | 11.2 || 105 | Good Set | melon | 8.2 || 101 | FastFruit Inc. | cherry | 3.2 || 104 | FNK Inc. | lemon | 6.4 || 106 | Just Eat Ours | mango | 15.6 || 105 | Good Set | xbabay | 2.6 || 105 | Good Set | xxtt | 11.6 || 103 | ACME | coconut | 9.2 || 102 | LT Supplies | banana | 10.3 || 102 | LT Supplies | grape | 5.3 || 107 | DK Inc. | xbababa | 3.6 |+------+----------------+------------+---------+16 rows in set（3）使用INNER JOIN语法进行查询
mysql> SELECT suppliers.s_id,s_name,f_name,f_price -> FROM fruits INNER JOIN suppliers -> ON fruits.s_id=suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 101 | FastFruit Inc. | apple | 5.2 || 103 | ACME | apricot | 2.2 || 101 | FastFruit Inc. | blackberry | 10.2 || 104 | FNK Inc. | berry | 7.6 || 107 | DK Inc. | xxxx | 3.6 || 102 | LT Supplies | orange | 11.2 || 105 | Good Set | melon | 8.2 || 101 | FastFruit Inc. | cherry | 3.2 || 104 | FNK Inc. | lemon | 6.4 || 106 | Just Eat Ours | mango | 15.6 || 105 | Good Set | xbabay | 2.6 || 105 | Good Set | xxtt | 11.6 || 103 | ACME | coconut | 9.2 || 102 | LT Supplies | banana | 10.3 || 102 | LT Supplies | grape | 5.3 || 107 | DK Inc. | xbababa | 3.6 |+------+----------------+------------+---------+16 rows in set （4）自连接
在一个连接查询中，涉及到的连个表都是同一个表。自连接是一个特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
例如，查询f_id=’a1’的水果供应商提供的其他水果种类：
mysql> 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';+------+------------+| f_id | f_name |+------+------------+| a1 | apple || b1 | blackberry || c0 | cherry |+------+------------+3 rows in set1.2 外连接查询
连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接或左连接）、右表（右外连接或右连接）或两个边接表（全外连接）中的所有数据行。
外连接分为左外连接和右外连接
①LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
②RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
（1）左连接
左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。
mysql> SELECT customers.c_id,orders.o_num -> FROM customers LEFT OUTER JOIN orders -> ON customers.c_id=orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 | NULL |+-------+-------+5 rows in set （2）右连接
右连接时左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。
mysql> SELECT customers.c_id,orders.o_num -> FROM customers RIGHT OUTER JOIN orders -> ON customers.c_id=orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || NULL | 30004 || 10001 | 30005 |+-------+-------+5 rows in set1.3 复合条件连接查询
复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。
mysql> SELECT customers.c_id,orders.o_num -> FROM customers INNER JOIN orders -> ON customers.c_id=orders.c_id AND customers.c_id=10001;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10001 | 30005 |+-------+-------+2 rows in set2 子查询
子查询是一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。
2.1 带ANY、SOME关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
mysql> CREATE TABLE tbl1(num1 INT NOT NULL);Query OK, 0 rows affectedmysql> CREATE TABLE tbl2(num1 INT NOT NULL);Query OK, 0 rows affectedmysql> INSERT INTO tbl1 VALUES(1),(5),(13),(27);Query OK, 4 rows affectedRecords: 4 Duplicates: 0 Warnings: 0mysql> INSERT INTO tbl2 VALUES(6),(14),(11),(20);Query OK, 4 rows affectedRecords: 4 Duplicates: 0 Warnings: 0mysql> SELECT num1 -> FROM tbl1 -> WHERE num1>ANY(SELECT num1 FROM tbl2);+------+| num1 |+------+| 13 || 27 |+------+2 rows in set只要大于tbl2表中任意一个数即为符合条件的结果。
2.2 带ALL关键字的子查询
使用ALL需要满足所有内层查询的条件。
使用前面的tbl1表和tbl2表：
mysql> SELECT num1 -> FROM tbl1 -> WHERE num1>ALL(SELECT num1 FROM tbl2);+------+| num1 |+------+| 27 |+------+1 row in set2.3 带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
使用前面的fruits表和suppliers表：
（1）查询suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的记录：
mysql> SELECT * FROM fruits -> WHERE EXISTS -> (SELECT s_name FROM suppliers WHERE s_id=107);+------+------+------------+---------+| f_id | s_id | f_name | f_price |+------+------+------------+---------+| a1 | 101 | apple | 5.2 || a2 | 103 | apricot | 2.2 || b1 | 101 | blackberry | 10.2 || b2 | 104 | berry | 7.6 || b5 | 107 | xxxx | 3.6 || bs1 | 102 | orange | 11.2 || bs2 | 105 | melon | 8.2 || c0 | 101 | cherry | 3.2 || l2 | 104 | lemon | 6.4 || m1 | 106 | mango | 15.6 || m2 | 105 | xbabay | 2.6 || m3 | 105 | xxtt | 11.6 || o2 | 103 | coconut | 9.2 || t1 | 102 | banana | 10.3 || t2 | 102 | grape | 5.3 || t4 | 107 | xbababa | 3.6 |+------+------+------------+---------+16 rows in set EXISTS关键字可以和条件表达式一起使用：
（2）查询suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的f_price大于10.20的记录：
mysql> SELECT * FROM fruits -> WHERE f_price>10.20 AND EXISTS -> (SELECT s_name FROM suppliers WHERE s_id=107);+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| bs1 | 102 | orange | 11.2 || m1 | 106 | mango | 15.6 || m3 | 105 | xxtt | 11.6 || t1 | 102 | banana | 10.3 |+------+------+--------+---------+4 rows in setNOT EXISTS与EXISTS使用方法相同，返回的结果相反。
2.4 带IN关键字的子查询
IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
例：在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id：
mysql> SELECT c_id -> FROM orders -> WHERE o_num -> IN -> (SELECT o_num FROM orderitems WHERE f_id='c0');+-------+| c_id |+-------+| 10004 || 10001 |+-------+2 rows in setNOT IN关键字与IN正好相反
2.5 带比较运算符的子查询
子查询还可以使用其他的比较运算符，如”<”,”<=”,”=”,”>=”,”!=”等
例，在suppliers表中查询s_city等于’Tianjin’的供应商s_id，然后再fruits表中查询所有该供应商提供的水果的种类：
mysql> SELECT s_id,f_name FROM fruits -> WHERE s_id = -> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city='Tianjin');+------+------------+| s_id | f_name |+------+------------+| 101 | apple || 101 | blackberry || 101 | cherry |+------+------------+3 rows in set3 合并查询结果
使用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。
例：查询所有价格小于9的水果信息，查询s_id等于101和103所有水果的信息，
（1）使用UNION连接查询结果：
mysql> SELECT s_id,f_name,f_price -> FROM fruits -> WHERE f_price<9.0 -> UNION -> SELECT s_id,f_name,f_price -> FROM fruits -> WHERE s_id IN(101,103);+------+------------+---------+| s_id | f_name | f_price |+------+------------+---------+| 101 | apple | 5.2 || 103 | apricot | 2.2 || 104 | berry | 7.6 || 107 | xxxx | 3.6 || 105 | melon | 8.2 || 101 | cherry | 3.2 || 104 | lemon | 6.4 || 105 | xbabay | 2.6 || 102 | grape | 5.3 || 107 | xbababa | 3.6 || 101 | blackberry | 10.2 || 103 | coconut | 9.2 |+------+------------+---------+12 rows in set（2）使用UNION ALL连接查询结果：
mysql> SELECT s_id,f_name,f_price -> FROM fruits -> WHERE f_price<9.0 -> UNION ALL -> SELECT s_id,f_name,f_price -> FROM fruits -> WHERE s_id IN(101,103);+------+------------+---------+| s_id | f_name | f_price |+------+------------+---------+| 101 | apple | 5.2 || 103 | apricot | 2.2 || 104 | berry | 7.6 || 107 | xxxx | 3.6 || 105 | melon | 8.2 || 101 | cherry | 3.2 || 104 | lemon | 6.4 || 105 | xbabay | 2.6 || 102 | grape | 5.3 || 107 | xbababa | 3.6 || 101 | apple | 5.2 || 103 | apricot | 2.2 || 101 | blackberry | 10.2 || 101 | cherry | 3.2 || 103 | coconut | 9.2 |+------+------------+---------+15 rows in set4 为表和字段取别名
（1）为表取别名
表名 [AS] 表别名（2）为字段取别名
列名 [AS] 列别名5 说明
阅读《MySQL5.5 从零开始学》笔记记录。
- MySQL知识（十）——连接查询与子查询
- mysql 子查询与连接
- MySQL子查询与连接
- MYSQL子查询与连接
- MySQL子查询与连接
- MySQL的子查询与连接查询
- MySQL的子查询和连接知识
- MySQL数据库学习——子查询与连接
- Mysql学习总结（8）——MySql基本查询、连接查询、子查询、正则表达查询讲解
- Mysql学习总结（8）——MySql基本查询、连接查询、子查询、正则表达查询讲解
- mysql数据库子查询、连接查询——2014.5.27
- mysql 子查询，连接查询
- Mysql数据库查询语句DQL（Data Query Language）—— 连接查询、子查询、联合查询
- MySQL——查询sql总结：简单查询、连接查询、子查询
- MySQL——查询sql总结：简单查询、连接查询、子查询
- (5)Mysql子查询与连接
- MySQL day4---子查询与连接
- 连接查询与子查询
- UVA 1422 - Processor（二分+优先队列+贪心）
- string 与char* char[]之间的转换
- 今后怎么做才能赚到钱？读完此文你就懂了！
- fuse用户空间文件系统
- div布局
- MySQL知识（十）——连接查询与子查询
- libgdx [Audio]
- div-2
- arm-linux-gcc/ncurses的安装和使用,arm-linux-gcc: 没有那个文件或目录
- 鸟哥的Linux私房菜(服务器)- 第二章、基础网络概念
- 计算器-4
- 鸟哥的Linux私房菜(服务器)- 第三章、局域网络架构简介
- CocoaPods安装和使用教程
- html5的input标签的pattern正则匹配问题
