欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL数据查询之连接查询

程序员文章站 2023-12-21 18:26:16
...
MySQL数据查询之连接查询
  • 连接关系是关系数据库模型的主要特点。也是最主要的查询,主要包括内连接、外连接等。
  • 通过连接运算符可以实现多个表查询。
  • 在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中。当查询数据时,通过连接操作查询出存放在多个表中不同实体的信息。当两个或多个表中存在相同意义的字段时,便可以通过这些字段对不同的表进行连接查询。

下面介绍多表之间的三种连接方式:

(1)内连接查询
(2)外连接查询
(3)复合条件连接查询

(免费学习推荐:mysql视频教程


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

下面创建数据表suppliers进行演示:

mysql> create table suppliers    -> (
    -> s_id int not null auto_increment,
    -> s_name char(50) not null,
    -> s_city char(50) null,
    -> s_zip char(10) null,
    -> s_call char(50) not null,
    -> primary key(s_id)
    -> );Query OK, 0 rows affected (0.17 sec)mysql> insert into suppliers(s_id,s_name,s_city,s_zip,s_call)
    -> values(101,'FastFruit Inc.','Tianjin','300000','48075'),
    -> (102,'LT Supplies','Chongqing','400000','44333'),
    -> (103,'ACME','Shanghai','200000','90046'),
    -> (104,'FNK Inc.','Zhongshan','528437','11111'),
    -> (105,'Good Set','Taiyuan','030000','22222'),
    -> (106,'Just Eat Ours','Beijing','010','45678'),
    -> (107,'DK Inc','Zhengzhou','450000','33332');Query OK, 7 rows affected (0.07 sec)Records: 7  Duplicates: 0  Warnings: 0

【例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 (0.06 sec)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 (0.00 sec)

可以看到fruits和suppliers表中都有相同数据类型的字段s_id,两个表通过s_id字段建立联系。

接下来从fruits表中查询f_name、f_price字段,从suppliers表中查询s_id、s_name,SQL语句如下:

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 |+------+----------------+------------+---------+|  104 | FNK Inc.       | lemon      |    6.40 ||  101 | FastFruit Inc. | apple      |    5.20 ||  103 | ACME           | apricot    |    2.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  104 | FNK Inc.       | berry      |    7.60 ||  107 | DK Inc         | xxxx       |    3.60 ||  102 | LT Supplies    | orange     |   11.20 ||  105 | Good Set       | melon      |    8.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  106 | Just Eat Ours  | mango      |   15.70 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  103 | ACME           | coconut    |    9.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
  • 注意:因为fruits表和suppliers表中有相同的字段s_id,因此在比较的时候,需要完全限定表名,格式为"表名.列名",如果只给出s_id,MySQL将不知道指的是哪一个,并返回错误信息。

【例2】在fruits表和suppliers表之间,使用inner join语法进行内连接查询,SQL语句如下:

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 |+------+----------------+------------+---------+|  104 | FNK Inc.       | lemon      |    6.40 ||  101 | FastFruit Inc. | apple      |    5.20 ||  103 | ACME           | apricot    |    2.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  104 | FNK Inc.       | berry      |    7.60 ||  107 | DK Inc         | xxxx       |    3.60 ||  102 | LT Supplies    | orange     |   11.20 ||  105 | Good Set       | melon      |    8.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  106 | Just Eat Ours  | mango      |   15.70 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  103 | ACME           | coconut    |    9.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)
  • 使用where子句定义连接条件比较简单明了,而inner join语法是ANSI SQL 的标准规范,使用inner join连接语法能够确保不会忘记连接条件,而且,where子句在某些时候回影响查询的性能。

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

【例3】查询供应f_id='a1'的水果供应商提供的水果种类,SQL语句如下:

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 (0.00 sec)

(2)、外连接查询
  • 外连接查询将查询多个表中相关联的行。
  • 内连接时,返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据,即返回查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接:

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

1.左连接left join

首先创建表orders,SQL语句如下;

mysql> create table orders    -> (
    -> o_num int not null auto_increment,
    -> o_date datetime not null,
    -> c_id int not null,
    -> primary key (o_num)
    -> );Query OK, 0 rows affected (0.11 sec)mysql> insert into orders(o_num,o_date,c_id)
    -> values(30001,'2008-09-01',10001),
    -> (30002,'2008-09-12',10003),
    -> (30003,'2008-09-30',10004),
    -> (30004,'2008-10-03',10005),
    -> (30005,'2008-10-08',10001);Query OK, 5 rows affected (0.06 sec)Records: 5  Duplicates: 0  Warnings: 0

【例】在customers表和orders表中,查询所有客户,包括没有订单的客户,SQL语句如下:

mysql> select * from customers;+-------+----------+---------------------+---------+--------+-----------+-------------------+| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           |+-------+----------+---------------------+---------+--------+-----------+-------------------+| 10001 | redhool  | 200 Street          | Tianjin | 300000 | LiMing    | LMing@163.com     || 10002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhangbo   | Jerry@hotmail.com || 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              || 10004 | JOTO     | 829 Riverside Drive | Haikou  | 570000 | YangShan  | sam@hotmail.com   |+-------+----------+---------------------+---------+--------+-----------+-------------------+4 rows in set (0.00 sec)mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date              | c_id  |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)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 (0.00 sec)

2.右连接right join

  • 右连接是左连接的反向连接,将返回右表的所有行。如果右表的某行在左表中没有匹配行,左表将返回空值。

【例】在customers表和orders表中,查询所有订单,包括没有客户的订单,SQL语句如下:

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 (0.00 sec)

(3)、复合条件连接查询
  • 复合条件连接查询是在连接查询的过程中,通过添加过滤条件,限制查询结果,使查询的结果更加准确。

【例1】在customers表和orders表中,使用inner join语法查询customers表汇总ID为10001的客户的订单信息,SQL语句如下:

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 (0.00 sec)

【例2】在fruits表和suppliers表之间,使用inner join语法进行内连接查询,并对查询结果排序,SQL语句如下:

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits inner join suppliers    -> on fruits.s_id = suppliers.s_id    -> order by fruits.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  101 | FastFruit Inc. | apple      |    5.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  102 | LT Supplies    | orange     |   11.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  103 | ACME           | apricot    |    2.20 ||  103 | ACME           | coconut    |    9.20 ||  104 | FNK Inc.       | lemon      |    6.40 ||  104 | FNK Inc.       | berry      |    7.60 ||  105 | Good Set       | melon      |    8.20 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  106 | Just Eat Ours  | mango      |   15.70 ||  107 | DK Inc         | xxxx       |    3.60 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

更多相关免费学习推荐:mysql教程(视频)

以上就是MySQL数据查询之连接查询的详细内容,更多请关注其它相关文章!

相关标签: MySQL 连接查询

上一篇:

下一篇: