MySQL之多表连查(精简笔记)

发表于 2020-01-14 更新于 2023-05-25 分类于《MySQL 数据库从入门到精通》

MySQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(RelationalDatabaseManagementSystem，关系数据库管理系统)应用软件之一，该笔记用于生产环境快速查阅.

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一，该笔记用于生产环境快速查阅.

MariaDB 连接查询

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

◆内连接查询◆

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

为了演示效果的需要,首先穿件一个suppliers表,SQL语句如下:

MariaDB [lyshark]> 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.09 sec)

需要插入演示的数据,SQL语句如下:

INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(101,'FastFruit.','TianJin','30000','4521');
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(102,'LT Supplies','ShangHai','554780','3268');
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(103,'ACME LyShark','Chongqing','447581','9985');
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(104,'FNK INCS','XiZang','3526','4475');
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(105,'GOOD SET','ZhongSHan','4478511','6666');
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(106,'JUST Eat Ours','TaiYuang','33325469','5555');
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) values(107,'docker INC','ZhengZhou','1124574','4851');

内连接实例: 在lyshark表和suppliers表之间使用内连接查询.

1.在查询之前先来看一下表结构吧.

MariaDB [lyshark]> desc lyshark;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Uid   | char(10)     | NO   | PRI | NULL    |       |
| Gid   | int(11)      | NO   |     | NULL    |       |
| Name  | char(255)    | NO   |     | NULL    |       |
| Price | decimal(8,2) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [lyshark]> 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)

由上面的查询结果可看到,lyshark表和suppliers表中都有相同数据类型的字段ID,两个表通过Gid与s_id字段建立联系.

2.接下来从lyshark表查询Name,Price字段,从suppliers表查询s_id,s_name字段,SQL语句如下:

MariaDB [lyshark]> select suppliers.s_id,s_name,Name,Price
    -> from lyshark,suppliers
    -> where lyshark.Gid = suppliers.s_id;

+------+---------------+------------+-------+
| s_id | s_name        | Name       | Price |
+------+---------------+------------+-------+
|  101 | FastFruit.    | apple      |  5.20 |
|  103 | ACME LyShark  | apricot    |  2.20 |
|  101 | FastFruit.    | blackberry | 10.20 |
|  104 | FNK INCS      | berry      |  7.60 |
|  107 | docker INC    | xxxx       |  3.60 |
|  102 | LT Supplies   | orange     | 11.20 |
|  105 | GOOD SET      | melon      |  8.20 |
|  101 | FastFruit.    | cherry     |  3.20 |
|  104 | FNK INCS      | lemon      |  6.40 |
|  106 | JUST Eat Ours | mango      | 15.70 |
|  105 | GOOD SET      | xbabay     |  2.60 |
|  105 | GOOD SET      | xxtt       | 11.60 |
|  103 | ACME LyShark  | coconut    |  9.20 |
|  102 | LT Supplies   | bannana    | 10.30 |
|  102 | LT Supplies   | grape      |  5.30 |
|  107 | docker INC    | xbababa    |  3.60 |
+------+---------------+------------+-------+
16 rows in set (0.01 sec)

在这里,SELECT语句与前面所介绍的一个最大的差别是,SELECT后面指定的列分别属于两个不同的表,(Name,Price)在表 lyshark中,而另外两个字段在表 suppliers中,同时FROM子句列出了两个表lyshark 和 suppliers.WHERE 子句在这里作为过滤条件,指明只有两个表中的s_id字段值相等的时候才符合连接查询的条件.从返回的结果可以看到,显示的记录是由两个表中不同列值组成的新记录.

内连接(INNER JOIN)实例: 在lyshark表和suppliers表之间,使用INNER JOIN语法进行内连接查询,SQL语句如下:

MariaDB [lyshark]> select suppliers.s_id,s_name,Name,Price
    -> from lyshark INNER JOIN suppliers
    -> ON lyshark.Gid=suppliers.s_id;
+------+---------------+------------+-------+
| s_id | s_name        | Name       | Price |
+------+---------------+------------+-------+
|  101 | FastFruit.    | apple      |  5.20 |
|  103 | ACME LyShark  | apricot    |  2.20 |
|  101 | FastFruit.    | blackberry | 10.20 |
|  104 | FNK INCS      | berry      |  7.60 |
|  107 | docker INC    | xxxx       |  3.60 |
|  102 | LT Supplies   | orange     | 11.20 |
|  105 | GOOD SET      | melon      |  8.20 |
|  101 | FastFruit.    | cherry     |  3.20 |
|  104 | FNK INCS      | lemon      |  6.40 |
|  106 | JUST Eat Ours | mango      | 15.70 |
|  105 | GOOD SET      | xbabay     |  2.60 |
|  105 | GOOD SET      | xxtt       | 11.60 |
|  103 | ACME LyShark  | coconut    |  9.20 |
|  102 | LT Supplies   | bannana    | 10.30 |
|  102 | LT Supplies   | grape      |  5.30 |
|  107 | docker INC    | xbababa    |  3.60 |
+------+---------------+------------+-------+
16 rows in set (0.01 sec)

本案例和上面的查询结果是一样的,在这里两表之间的关系通过INNER JOIN指定,使用这种语法的时候连接的条件使用ON子句,ON和WHERE后面指定的条件相同.

自连接实例: 查询供应商Uid='a1'的水果的种类,SQL语句如下:

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

MariaDB [lyshark]> select f1.Uid,f1.Name
    -> from lyshark AS f1,lyshark AS f2 
    -> where f1.Gid = f2.Gid and f2.Uid='a1';
+-----+------------+
| Uid | Name       |
+-----+------------+
| a1  | apple      |
| b1  | blackberry |
| c0  | cherry     |
+-----+------------+
3 rows in set (0.01 sec)

此处查询的两个表是相同的表,为了防止产生二义性,对表使用了别名,lyshark表第1次出现的别名为fl,第2次出现的别名为f2,使用SELECT语句返回列时明确指出返回以n为前缀的列的全名,WHERE连接两个表,并按照第2个表的Gid对数据进行过滤,返回所需数据.

◆外连接查询◆

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

● LEFT JOIN(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录.
● RIGHT JOIN(右连接):返回包括右表中的所有记录和左表中连接字段相等的记录.

接着下面先来创建两个测试表orders和customers,SQL语句如下:

MariaDB [lyshark]> 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.02 sec)

MariaDB [lyshark]> create table customers (c_id int not null );
Query OK, 0 rows affected (0.01 sec)

接着插入需要演示的数据,SQL语句如下:

INSERT INTO orders(o_num,o_date,c_id) VALUES(30001,'2018-09-01',10001);
INSERT INTO orders(o_num,o_date,c_id) VALUES(30002,'2018-09-02',10003);
INSERT INTO orders(o_num,o_date,c_id) VALUES(30003,'2018-09-03',10004);
INSERT INTO orders(o_num,o_date,c_id) VALUES(30004,'2018-09-04',10005);
INSERT INTO orders(o_num,o_date,c_id) VALUES(30005,'2018-09-05',10001);

INSERT INTO customers(c_id) VALUES(10001);
INSERT INTO customers(c_id) VALUES(10002);
INSERT INTO customers(c_id) VALUES(10003);
INSERT INTO customers(c_id) VALUES(10004);
INSERT INTO customers(c_id) VALUES(10005);

左连接(lEFT JOIN)

左连接的结果包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行,如果左表的某行在右表中没有匹配行,则在相关联的结果中,右表的所有选择列表列均为空值.

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

MariaDB [lyshark]> 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 |
| 10005 | 30004 |
| 10001 | 30005 |
| 10002 |  NULL |
+-------+-------+
6 rows in set (0.00 sec)

结果显示了6条记录,编号10002的用户并没有达成交易,所有该条记录只取出了ordes表中相应的值,而从customers表中取出的值为空值NULL.

右连接(RIGHT JOIN)

右连接是左连接的反向连接,将返回右表的所有行,如果右表的某行在作表中没有匹配行,作表将返回空值.
实例: 在customers表和orders表中,查询所有订单,包括没有客户的订单,SQL语句如下:

MariaDB [lyshark]> 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 |
| 10001 | 30005 |
| 10003 | 30002 |
| 10004 | 30003 |
| NULL  | 30004 |
+-------+-------+
5 rows in set (0.00 sec)

结果显示6条记录,30004订单客户取消了订单,对应的customers表中并没有该客户的信息,所以该条记录只取出了orders表中相应的值,而从customers表中取出的值为空值NULL.

◆复合连接查询◆

复合条件连接查询是在连接查询的过程中,通过添加过滤条件,限制查询的结果,使查询结果更精确.
实例: 在lyshark表和suppliers表之间,使用INNER JOIN语法进行内连接查询,并对查询结果排序,SQL语句如下:

MariaDB [lyshark]> select suppliers.s_id,s_name,Name,Price
    -> from lyshark INNER JOIN suppliers
    -> ON lyshark.Gid = suppliers.s_id
    -> ORDER BY lyshark.Gid;
+------+---------------+------------+-------+
| s_id | s_name        | Name       | Price |
+------+---------------+------------+-------+
|  101 | FastFruit.    | apple      |  5.20 |
|  101 | FastFruit.    | blackberry | 10.20 |
|  101 | FastFruit.    | cherry     |  3.20 |
|  102 | LT Supplies   | grape      |  5.30 |
|  102 | LT Supplies   | bannana    | 10.30 |
|  102 | LT Supplies   | orange     | 11.20 |
|  103 | ACME LyShark  | apricot    |  2.20 |
|  103 | ACME LyShark  | coconut    |  9.20 |
|  104 | FNK INCS      | lemon      |  6.40 |
|  104 | FNK INCS      | berry      |  7.60 |
|  105 | GOOD SET      | xbabay     |  2.60 |
|  105 | GOOD SET      | xxtt       | 11.60 |
|  105 | GOOD SET      | melon      |  8.20 |
|  106 | JUST Eat Ours | mango      | 15.70 |
|  107 | docker INC    | xxxx       |  3.60 |
|  107 | docker INC    | xbababa    |  3.60 |
+------+---------------+------------+-------+
16 rows in set (0.00 sec)

MariaDB 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询,在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表.
一般的子查询中常用的操作符有ANY(SOME),ALL,IN,EXISTS.子查询可以添加到SELECT、UPDATE和DELETE 语句中,而且可以进行多层嵌套.子查询中也可以使用比较运算符,如<,<=,>,>=,!=等.

◆IN 子查询◆

IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作.

IN查询1: 查询lyshark表中的Uid='a1'的GId号,并以Gid作为查询条件查询suppliers表中s_id字段的s_city字段的数值.

MariaDB [lyshark]> select Gid from lyshark where Uid='a1';
+-----+
| Gid |
+-----+
| 101 |
+-----+
1 row in set (0.00 sec)

MariaDB [lyshark]> select * from suppliers;
+------+---------------+-----------+----------+--------+
| s_id | s_name        | s_city    | s_zip    | s_call |
+------+---------------+-----------+----------+--------+
|  101 | FastFruit.    | TianJin   | 30000    | 4521   |
|  102 | LT Supplies   | ShangHai  | 554780   | 3268   |
|  103 | ACME LyShark  | Chongqing | 447581   | 9985   |
|  104 | FNK INCS      | XiZang    | 3526     | 4475   |
|  105 | GOOD SET      | ZhongSHan | 4478511  | 6666   |
|  106 | JUST Eat Ours | TaiYuang  | 33325469 | 5555   |
|  107 | docker INC    | ZhengZhou | 1124574  | 4851   |
+------+---------------+-----------+----------+--------+
7 rows in set (0.00 sec)

MariaDB [lyshark]> select s_city from suppliers where s_id IN
    -> (select Gid from lyshark where Uid='a1');
+---------+
| s_city  |
+---------+
| TianJin |
+---------+
1 row in set (0.02 sec)

IN查询2: 查询lyshark表中,Gid字段是(101和105)编号的内容.

MariaDB [lyshark]> select Gid,Name from lyshark where Gid IN(101,105);
+-----+------------+
| Gid | Name       |
+-----+------------+
| 101 | apple      |
| 101 | blackberry |
| 105 | melon      |
| 101 | cherry     |
| 105 | xbabay     |
| 105 | xxtt       |
+-----+------------+
6 rows in set (0.00 sec)

NOT IN查询: 这个查询结果正好和上面的例子相反.

MariaDB [lyshark]> select Gid,Name from lyshark where Gid NOT IN(101,105,102);
+-----+----------+
| Gid | Name     |
+-----+----------+
| 103 | apricot  |
| 104 | berry    |
| 107 | xxxx     |
| 104 | lemon    |
| 999 | lysharks |
| 106 | mango    |
| 103 | coconut  |
| 107 | xbababa  |
+-----+----------+
8 rows in set (0.00 sec)

◆ANY 子查询◆

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

为了进行下面的实验,先来创建一下SQL语句表格:

MariaDB [lyshark]> create table tab1(num1 int not null);
Query OK, 0 rows affected (0.02 sec)

MariaDB [lyshark]> create table tab2(num2 int not null);
Query OK, 0 rows affected (0.00 sec)

INSERT INTO tab1 VALUES(1),(5),(13),(27);
INSERT INTO tab2 VALUES(6),(14),(11),(20);

ANY 实例: 返回tab2表的所有num2列,然后将tab1中的num1的值与之进行比较,只要大于num2的任何1个值,即为符合查询结果.

MariaDB [lyshark]> select num1 from tab1 where num1 > ANY(select num2 from tab2);
+------+
| num1 |
+------+
|   13 |
|   27 |
+------+
2 rows in set (0.01 sec)

在子查询中,返回的是tab2的所有num2列结果(6,14,11,20),然后将tab1中的num1列的值与之进行比较,只要大于num2列的任意一个数即为符合条件的结果.

◆ALL 子查询◆

ALL关键字与ANY不同,使用ALL时需要同时满足所有内层查询的条件,ALL关键字接在一个比较操作符的后面,表示与子查询返回的所有值比较为TRUE,则返回TRUE.

ALL实例: 返回tab2表中比tab2表num2列所有值都打的值,SQL语句如下:

MariaDB [lyshark]> select num1 from tab1 where num1 > ALL(select num2 from tab2);
+------+
| num1 |
+------+
|   27 |
+------+
1 row in set (0.02 sec)

以上,大于所有num2列的num1值只有27,则返回27.

◆EXISTS 子查询◆

EXISTS关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层查询语句将进行查询.如果子查询没有返回任何行,那么EXISTS返回的结果是false,此时外层语句将不进行查询.

EXISTS 实例1: 查询lyshark表中所有记录,查询suppliers表中是否存在s_id=107的记录,如果存在,则查询lyshark表中的记录,否则不查询,SQL语句如下:

MariaDB [lyshark]> select * from lyshark
    -> where EXISTS
    -> (select s_name from suppliers where s_id=107);
+---------+-----+------------+--------+
| Uid     | Gid | Name       | Price  |
+---------+-----+------------+--------+
| a1      | 101 | apple      |   5.20 |
| a2      | 103 | apricot    |   2.20 |
| b1      | 101 | blackberry |  10.20 |
| b2      | 104 | berry      |   7.60 |
| b5      | 107 | xxxx       |   3.60 |
| bs1     | 102 | orange     |  11.20 |
| bs2     | 105 | melon      |   8.20 |
| c0      | 101 | cherry     |   3.20 |
| l2      | 104 | lemon      |   6.40 |
| lyshark | 999 | lysharks   | 999.00 |
| m1      | 106 | mango      |  15.70 |
| m2      | 105 | xbabay     |   2.60 |
| m3      | 105 | xxtt       |  11.60 |
| o2      | 103 | coconut    |   9.20 |
| t1      | 102 | bannana    |  10.30 |
| t2      | 102 | grape      |   5.30 |
| t4      | 107 | xbababa    |   3.60 |
+---------+-----+------------+--------+
17 rows in set (0.01 sec)

由结果可以看到,内层查询结果表明suppliers表中存在s_id =107表达式返回true,外层查询语句接收true 之后对表lyshark进行查询,并返回所有的记录.

EXISTS 实例2: 查询suppliers表中是否存在s_id=107的字段,如果存在,则查询lyshark表中的Price大于10.02的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark
    -> where Price >10.02 AND EXISTS
    -> (select s_name from suppliers where s_id=107);
+---------+-----+------------+--------+
| Uid     | Gid | Name       | Price  |
+---------+-----+------------+--------+
| b1      | 101 | blackberry |  10.20 |
| bs1     | 102 | orange     |  11.20 |
| lyshark | 999 | lysharks   | 999.00 |
| m1      | 106 | mango      |  15.70 |
| m3      | 105 | xxtt       |  11.60 |
| t1      | 102 | bannana    |  10.30 |
+---------+-----+------------+--------+
6 rows in set (0.00 sec)

NOT EXISTS 实例: 查询suppliers表中是否存在s_id=107的供应商,如果不存在则查询lyshark表中的记录,SQL语句如下:

MariaDB [lyshark]> select * from lyshark
    -> where NOT EXISTS
    -> (select s_name from suppliers where s_id=107);
Empty set (0.00 sec)

如上:查询语句select s_name from suppliers where s_id=107返回false,则不再执行外层的查询工作.

◆比较运算符子查询◆

在前面介绍的带ANY,ALL关键字的子查询时使用了>比较运算符,子查询时还可以使用其他的比较运算符,如<,<=,>,>=,=,!=等.

实例1: 使用>=号,过滤出lyshark表中,Gid>=101的字段并显示其Gid,Name字段内容.

MariaDB [lyshark]> select Gid,Name from lyshark
    -> where Gid >=101;
+-----+------------+
| Gid | Name       |
+-----+------------+
| 101 | apple      |
| 103 | apricot    |
| 101 | blackberry |
| 104 | berry      |
| 107 | xxxx       |
| 102 | orange     |
| 105 | melon      |
| 101 | cherry     |
| 104 | lemon      |
| 999 | lysharks   |
| 106 | mango      |
| 105 | xbabay     |
| 105 | xxtt       |
| 103 | coconut    |
| 102 | bannana    |
| 102 | grape      |
| 107 | xbababa    |
+-----+------------+
17 rows in set (0.00 sec)

实例2: 使用<>不等于,查询指定字段,通过别名查询,其中嵌套子查询语句.

MariaDB [lyshark]> select Gid,Name from lyshark
    -> where Gid <>
    -> (select Gid from lyshark AS ly where ly.Name='apple');
+-----+----------+
| Gid | Name     |
+-----+----------+
| 103 | apricot  |
| 104 | berry    |
| 107 | xxxx     |
| 102 | orange   |
| 105 | melon    |
| 104 | lemon    |
| 999 | lysharks |
| 106 | mango    |
| 105 | xbabay   |
| 105 | xxtt     |
| 103 | coconut  |
| 102 | bannana  |
| 102 | grape    |
| 107 | xbababa  |
+-----+----------+
14 rows in set (0.01 sec)

MariaDB 其他查询

◆合并查询结果◆

利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集.合并时,两个表对应的列数和数据类型必须相同.各个SELECT语句之间使用UNION或UNION ALL关键字分隔.UNION不使用关键字ALL,执行的时候删除重复的记录,所有返回的行都是唯一的.使用关键字ALL的作用是不删除重复行也不对结果进行自动排序.基本语法格式如下:

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

合并并删除重复行: 查询所有价格小于9.0的水果的信息,且查询Gid=101和103所有水果信息,使用UNION连接自动删除重复的行,查询结果,SQL语句如下:

MariaDB [lyshark]> select Gid,Name,Price from lyshark
    -> where Price < 9.0
    -> UNION
    -> select Gid,Name,Price from lyshark
    -> where Gid IN(101.103);
+-----+---------+-------+
| Gid | Name    | Price |
+-----+---------+-------+
| 101 | apple   |  5.20 |
| 103 | apricot |  2.20 |
| 104 | berry   |  7.60 |
| 107 | xxxx    |  3.60 |
| 105 | melon   |  8.20 |
| 101 | cherry  |  3.20 |
| 104 | lemon   |  6.40 |
| 105 | xbabay  |  2.60 |
| 102 | grape   |  5.30 |
| 107 | xbababa |  3.60 |
+-----+---------+-------+
10 rows in set (0.00 sec)

如上所示:使用UNION将两条SELECT语句分开,执行完毕后把输出结果组合成单个的结果集,并删除重复的记录.

合并但不删除重复行: 查询所有价格小于9.0的水果的信息,且查询Gid=101和103所有水果信息,使用UNION ALL连接,不要删除重复的行,查询结果,SQL语句如下:

MariaDB [lyshark]> select Gid,Name,Price from lyshark
    -> where Price < 9.0
    -> UNION ALL
    -> select Gid,Name,Price from lyshark
    -> where Gid IN(101,103);
+-----+------------+-------+
| Gid | Name       | Price |
+-----+------------+-------+
| 101 | apple      |  5.20 |
| 103 | apricot    |  2.20 |
| 104 | berry      |  7.60 |
| 107 | xxxx       |  3.60 |
| 105 | melon      |  8.20 |
| 101 | cherry     |  3.20 |
| 104 | lemon      |  6.40 |
| 105 | xbabay     |  2.60 |
| 102 | grape      |  5.30 |
| 107 | xbababa    |  3.60 |
| 101 | apple      |  5.20 |
| 103 | apricot    |  2.20 |
| 101 | blackberry | 10.20 |
| 101 | cherry     |  3.20 |
| 103 | coconut    |  9.20 |
+-----+------------+-------+
15 rows in set (0.00 sec)

上面的查询结果可以看出UNION ALL只会合并数据,但不会去除重复行.

◆为表和字段取别名◆

在前面介绍分组查询、聚合函数查询和嵌套子查询,你可以看到有的地方使用了AS关键字为查询结果中的某一列指定一个特定的名字,在内连接查询时,则对相同的表lyshark分别指定两个不同的名字,这里可以为字段或者表取一个别名,在查询时,使用别名替代其指定内容,下面即将主要了解一下如何给字段和表创建别名以及如何使用别名吧.

为表取别名:

当表名称很长或者执行一些特殊查询时,为了方便操作或者需要多次使用相同的表时,可以为表指定别名,用这个别名替代表原来的名称,为表取别名SQL基本语法如下:

表名称 [AS] 表别名

1.给orders表取别名o,查询30001订单的下单日期,SQL语句如下:

MariaDB [lyshark]> select * from orders AS o where o.o_num=30001;

+-------+---------------------+-------+
| o_num | o_date              | c_id  |
+-------+---------------------+-------+
| 30001 | 2018-09-01 00:00:00 | 10001 |
+-------+---------------------+-------+
1 row in set (0.35 sec)

2.为customers和orders表分别取别名,并进行连接查询,SQL语句如下:

MariaDB [lyshark]> select c.c_id,o.o_num
    -> from customers AS c LEFT OUTER JOIN orders AS o
    -> ON c.c_id=o.c_id;
+-------+-------+
| c_id  | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| 10005 | 30004 |
| 10001 | 30005 |
| 10002 |  NULL |
+-------+-------+
6 rows in set (0.01 sec)

由上面的查询结果可看到,MariaDB可同时为多个表取别名,且表名可以放在不同的位置,如where子句,select列表,on子句,以及order by 子句.

在前面还介绍了自连接查询,在连接两个表都是同一个表时,也可以使用别名机制,SQL语句如下:

MariaDB [lyshark]> select f1.Gid,f1.Name
    -> from lyshark AS f1,lyshark AS f2
    -> where f1.Gid=f2.Gid AND f2.Gid=101;
+-----+------------+
| Gid | Name       |
+-----+------------+
| 101 | apple      |
| 101 | blackberry |
| 101 | cherry     |
| 101 | apple      |
| 101 | blackberry |
| 101 | cherry     |
| 101 | apple      |
| 101 | blackberry |
| 101 | cherry     |
+-----+------------+
9 rows in set (0.00 sec)

为字段取别名:

当我们使用SELECT语句显示查询结果时,MySQL会显示每个SELECT后面指定的输出列,在有些情况下,显示的列的名称会很长或者名称不够改观,MySQL可以指定列别名,替换字段或表达式.为字段取别名的基本语法格式为:

列名称 [AS] 列别名

1.查询lyshark表,为Name字段取别名file_Name,Price取别名file_Price,为lyshark表取别名为f1,并查询表中Price < 8的水果名称,SQL语句如下:

MariaDB [lyshark]> select f1.Name AS file_name,f1.Price AS file_price
    -> from lyshark AS f1
    -> where f1.Price < 8;
+-----------+------------+
| file_name | file_price |
+-----------+------------+
| apple     |       5.20 |
| apricot   |       2.20 |
| berry     |       7.60 |
| xxxx      |       3.60 |
| cherry    |       3.20 |
| lemon     |       6.40 |
| xbabay    |       2.60 |
| grape     |       5.30 |
| xbababa   |       3.60 |
+-----------+------------+
9 rows in set (0.00 sec)

连接查询练习

建表:

create table tbl_dept(
    id int(11) not null auto_increment,
    deptName varchar(30) default null,
    locAdd varchar(40) default null,
    primary key(id)
)ENGINE=innodb auto_increment=1 default charset=utf8;

create table tbl_emp(
    id int(11) not null auto_increment,
    name varchar(20) default null,
    deptld int(11) default null,
    primary key(id),
    key fk_dept_id(deptld)
)engine=innodb auto_increment=1 default charset=utf8;


insert into tbl_dept(deptName,locAdd) values('研发部',11);
insert into tbl_dept(deptName,locAdd) values('人力部',12);
insert into tbl_dept(deptName,locAdd) values('市场部',13);
insert into tbl_dept(deptName,locAdd) values('后勤部',14);
insert into tbl_dept(deptName,locAdd) values('财务部',15);

insert into tbl_emp(name,deptld) values('张三',1);
insert into tbl_emp(name,deptld) values('张四',1);
insert into tbl_emp(name,deptld) values('张五',1);
insert into tbl_emp(name,deptld) values('王五',2);
insert into tbl_emp(name,deptld) values('王六',2);
insert into tbl_emp(name,deptld) values('李七',3);
insert into tbl_emp(name,deptld) values('孙八',4);
insert into tbl_emp(name,deptld) values('临时',51);

查询两表的中间公有部分,两个表中共有的数据

左表中存在,查询两表共有部分,和左表的全部.

右表中存在的部分

去掉中间部分,只要a表的独有部分

MariaDB [lyshark]> select * from tbl_emp a right join tbl_dept b on a.deptld = b.id where a.deptld is null;

– 查询全部记录,a与b全有的记录

MariaDB [lyshark]> select * from tbl_emp a left join tbl_dept b on a.deptld = b.id
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptld = b.id;

查询a与b各自的独有部分
MariaDB [lyshark]> select * from tbl_emp a left join tbl_dept b on a.deptld=b.id where b.id is null
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptld = b.id where a.deptld is null;

参考文献

《MySQL5.7从入门到精通》 - 刘增杰