MySQL知识(十)——连接查询与子查询

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 set

mysql> 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 set

1.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 set

1.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 set

2 子查询

  子查询是一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或多个表。子查询可以添加到SELECT、UPDATE和DELETE语句中,而且可以进行多层嵌套。

2.1 带ANY、SOME关键字的子查询

  ANY和SOME关键字是同义词,表示满足其中任一条件,它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件。

mysql> CREATE TABLE tbl1(num1 INT NOT NULL);
Query OK, 0 rows affected

mysql> CREATE TABLE tbl2(num1 INT NOT NULL);
Query OK, 0 rows affected

mysql> INSERT INTO tbl1 VALUES(1),(5),(13),(27);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tbl2 VALUES(6),(14),(11),(20);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> 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 set

2.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 set

  NOT 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 set

  NOT 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 set

3 合并查询结果

  使用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 set

4 为表和字段取别名

  (1)为表取别名

表名 [AS] 表别名

  (2)为字段取别名

列名 [AS] 列别名

5 说明

阅读《MySQL5.5 从零开始学》笔记记录。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值