MySQL-查询语句（三）-阿里云开发者社区

外连接查询

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

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

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

1. mysql> create table orders
2.  -> (
3.  -> o_num int not null auto_increment,
4.  -> o_date datetime not null,
5.  -> c_id int not null,
6.  -> primary key(o_num)
7.  -> ) ;
8. Query OK, 0 rows affected (0.01 sec)
9. mysql> insert into orders(o_num,o_date,c_id)
10.  -> values(30001,'2008-09-01',10001),
11.  -> (30002,'2008-09-12',10003),
12.  -> (30003,'2008-09-30',10004),
13.  -> (30004,'2008-10-03',10005),
14.  -> (30005,'2008-10-08',10001);
15. Query OK, 5 rows affected (0.01 sec)
16. Records: 5 Duplicates: 0 Warnings: 0

1、左连接

1. mysql> select customers.c_id,orders.o_num
2.     -> from customers left outer join orders
3.     -> on
4.     -> customers.c_id = orders.c_id;
5. +-------+-------+
6. | c_id  | o_num |
7. +-------+-------+
8. | 10001 | 30001 |
9. | 10003 | 30002 |
10. | 10004 | 30003 |
11. | 10001 | 30005 |
12. | 10002 |  NULL |
13. +-------+-------+
14. 5 rows in set (0.00 sec)

2、右连接

1. mysql> select customers.c_id,orders.o_num
2.     -> from customers right outer join orders
3.     -> on
4.     -> customers.c_id = orders.c_id;
5. +-------+-------+
6. | c_id  | o_num |
7. +-------+-------+
8. | 10001 | 30001 |
9. | 10003 | 30002 |
10. | 10004 | 30003 |
11. |  NULL | 30004 |
12. | 10001 | 30005 |
13. +-------+-------+
14. 5 rows in set (0.00 sec)

复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。

1. mysql> select customers.c_id,orders.o_num
2.     -> from customers inner join orders
3.     -> on
4.     -> customers.c_id = orders.c_id and customers.c_id = 10001;
5. +-------+-------+
6. | c_id  | o_num |
7. +-------+-------+
8. | 10001 | 30001 |
9. | 10001 | 30005 |
10. +-------+-------+
11. 2 rows in set (0.00 sec)
12. 
13. mysql> select suppliers.s_id,s_name,f_name,f_price
14.     -> from fruits inner join suppliers
15.     -> on
16.     -> fruits.s_id = suppliers.s_id
17.     -> order by fruits.s_id;
18. +------+----------------+------------+---------+
19. | s_id | s_name         | f_name     | f_price |
20. +------+----------------+------------+---------+
21. |  101 | FastFruit Inc. | apple      |    5.20 |
22. |  101 | FastFruit Inc. | blackberry |   10.20 |
23. |  101 | FastFruit Inc. | cherry     |    3.20 |
24. |  102 | LT Supplies    | orange     |   11.20 |
25. |  102 | LT Supplies    | banana     |   10.30 |
26. |  102 | LT Supplies    | grape      |    5.30 |
27. |  103 | acme           | apricot    |    2.20 |
28. |  103 | acme           | coconut    |    9.20 |
29. |  104 | fnk inc.       | berry      |    7.60 |
30. |  104 | fnk inc.       | lemon      |    6.40 |
31. |  105 | good set       | melon      |    8.20 |
32. |  105 | good set       | xbabay     |    2.60 |
33. |  105 | good set       | xxtt       |   11.60 |
34. |  106 | just eat ours  | mango      |   15.70 |
35. |  107 | dk inc.        | xxxx       |    3.60 |
36. |  107 | dk inc.        | xbababa    |    3.60 |
37. +------+----------------+------------+---------+
38. 16 rows in set (0.01 sec)

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从mysql4.1开始引入。在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有any（some）、all、in、exists。子查询可以添加到select、update和delete语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”,“<=”,“>”,“>=”和“!=”等。

带any、some关键字的子查询

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

创建两个表并插入数据

1. mysql> create table tb1(num1 int not null);
2. Query OK, 0 rows affected (0.01 sec)
3. 
4. mysql> create table tb2(num2 int not null);
5. Query OK, 0 rows affected (0.01 sec)
6. 
7. mysql> insert into tb1 values(1),(5),(13),(27);
8. Query OK, 4 rows affected (0.00 sec)
9. Records: 4  Duplicates: 0  Warnings: 0
10. 
11. mysql> insert into tb2 values(6),(14),(11),(20);
12. Query OK, 4 rows affected (0.01 sec)
13. Records: 4  Duplicates: 0  Warnings: 0

查看满足条件的行

1. mysql> select num1 from tb1 where num1 > any (select num2 from tb2);
2. +------+
3. | num1 |
4. +------+
5. |   13 |
6. |   27 |
7. +------+
8. 2 rows in set (0.00 sec)

带all关键字的子查询

all关键字与any和some不同，使用all时需要同时满足所有内层查询的条件。

1. mysql> select num1 from tb1 where num1 > all (select num2 from tb2);
2. +------+
3. | num1 |
4. +------+
5. |   27 |
6. +------+
7. 1 row in set (0.00 sec)

带exists关键字的子查询

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

1. mysql> select * from fruits where exists (select s_name from suppliers where s_id  = 107);
2. +------+------+------------+---------+
3. | f_id | s_id | f_name     | f_price |
4. +------+------+------------+---------+
5. | a1   |  101 | apple      |    5.20 |
6. | a2   |  103 | apricot    |    2.20 |
7. | b1   |  101 | blackberry |   10.20 |
8. | b2   |  104 | berry      |    7.60 |
9. | b5   |  107 | xxxx       |    3.60 |
10. | bs1  |  102 | orange     |   11.20 |
11. | bs2  |  105 | melon      |    8.20 |
12. | c0   |  101 | cherry     |    3.20 |
13. | l2   |  104 | lemon      |    6.40 |
14. | m1   |  106 | mango      |   15.70 |
15. | m2   |  105 | xbabay     |    2.60 |
16. | m3   |  105 | xxtt       |   11.60 |
17. | o2   |  103 | coconut    |    9.20 |
18. | t1   |  102 | banana     |   10.30 |
19. | t2   |  102 | grape      |    5.30 |
20. | t4   |  107 | xbababa    |    3.60 |
21. +------+------+------------+---------+
22. 16 rows in set (0.00 sec)

带in关键字的子查询

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

1. mysql> select c_id from orders where o_num in
2.     -> (select o_num from orderitems where f_id = 'c0');
3. +-------+
4. | c_id  |
5. +-------+
6. | 10004 |
7. | 10001 |
8. +-------+
9. 2 rows in set (0.00 sec)

带比较运算符的子查询

1. mysql> select s_id,f_name from fruits
2.  -> where s_id = 
3.  -> (select s1.s_id from suppliers as s1 where s1.s_city='tianjin');
4. +------+------------+
5. | s_id | f_name     |
6. +------+------------+
7. | 101 | apple       |
8. | 101 | blackberry  |
9. | 101 | cherry      |
10. +------+------------+
11. 3 rows in set (0.00 sec)

合并查询结果

利用union关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个select语句之间使用union或union all关键字分隔。union不使用关键字all，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字all的作用是不删除重复行也不对结果进行自动排序。

1. mysql> select s_id,f_name,f_price
2.     -> from fruits
3.     -> where f_price < 9.0
4.     -> union all
5.     -> select s_id,f_name,f_price
6.     -> from fruits
7.     -> where s_id in(101,103);
8. +------+------------+---------+
9. | s_id | f_name     | f_price |
10. +------+------------+---------+
11. |  101 | apple      |    5.20 |
12. |  103 | apricot    |    2.20 |
13. |  104 | berry      |    7.60 |
14. |  107 | xxxx       |    3.60 |
15. |  105 | melon      |    8.20 |
16. |  101 | cherry     |    3.20 |
17. |  104 | lemon      |    6.40 |
18. |  105 | xbabay     |    2.60 |
19. |  102 | grape      |    5.30 |
20. |  107 | xbababa    |    3.60 |
21. |  101 | apple      |    5.20 |
22. |  103 | apricot    |    2.20 |
23. |  101 | blackberry |   10.20 |
24. |  101 | cherry     |    3.20 |
25. |  103 | coconut    |    9.20 |
26. +------+------------+---------+
27. 15 rows in set (0.00 sec)

union和union all的区别：使用union all的功能是不删除重复行，加上all关键字语句执行时所需要的资源少，所以尽可能地使用它，因此知道有重复行但是想保留这些行，确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用union all以提高查询效率。

1. mysql> select s_id,f_name,f_price
2.     -> from fruits
3.     -> where f_price < 9.0
4.     -> union
5.     -> select s_id,f_name,f_price
6.     -> from fruits
7.     -> where s_id in(101,103);
8. +------+------------+---------+
9. | s_id | f_name     | f_price |
10. +------+------------+---------+
11. |  101 | apple      |    5.20 |
12. |  103 | apricot    |    2.20 |
13. |  104 | berry      |    7.60 |
14. |  107 | xxxx       |    3.60 |
15. |  105 | melon      |    8.20 |
16. |  101 | cherry     |    3.20 |
17. |  104 | lemon      |    6.40 |
18. |  105 | xbabay     |    2.60 |
19. |  102 | grape      |    5.30 |
20. |  107 | xbababa    |    3.60 |
21. |  101 | blackberry |   10.20 |
22. |  103 | coconut    |    9.20 |
23. +------+------------+---------+
24. 12 rows in set (0.01 sec)

为表和字段取别名

前面介绍了分组查询、聚合函数查询和嵌套子查询，取别名使用关键字as为查询结果中的某一列指定一个特别的名字。可以为字段或者表分别取别名，在查询时，使用别名替代指定的内容。

为表取别名

1. mysql> select * from orders as abc where abc.o_num = 30001;
2. +-------+---------------------+-------+
3. | o_num | o_date              | c_id  |
4. +-------+---------------------+-------+
5. | 30001 | 2008-09-01 00:00:00 | 10001 |
6. +-------+---------------------+-------+
7. 1 row in set (0.00 sec)

为字段取别名

先给fruits表指定别名为fa，f_name列名指定为fruits_name，f_price列名指定为fruits_price，并且价格小于8

1. mysql> select fa.f_name as fruits_name,fa.f_price as fruits_price
2.     -> from fruits as fa
3.     -> where fa.f_price < 8;
4. +-------------+--------------+
5. | fruits_name | fruits_price |
6. +-------------+--------------+
7. | apple       |         5.20 |
8. | apricot     |         2.20 |
9. | berry       |         7.60 |
10. | xxxx        |         3.60 |
11. | cherry      |         3.20 |
12. | lemon       |         6.40 |
13. | xbabay      |         2.60 |
14. | grape       |         5.30 |
15. | xbababa     |         3.60 |
16. +-------------+--------------+
17. 9 rows in set (0.00 sec)

使用正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等等，这些地方都可以使用正则表达式。正则表达式强大且灵活，可以应用于非常复杂的查询。mysql中使用regexp关键字指定正则表达式的字符匹配模式。

选项	说明	例子	匹配值实例
^	匹配文本的开始字符	^b'匹配以字母b开头的字符串	book，big，bike
$	匹配文本的结束字符	st$'匹配以st结尾的字符串	test，resist
.	匹配任何单个字符	b.t'匹配任何b和t之间有一个字符	bit，bat，but
*	匹配零个或者多个在它前面的字符	f*n'匹配字符n前面有任意个字符	fn，fan，faaan
+	匹配前面的字符1次或多次	ba+'匹配以b开头后面紧跟至少有一个a	ba，bay，bare，battle
<字符串>	匹配包含指定的字符串的文本	fa' fan,afa,faad
[字符集合]	匹配字符集合中的任何一个字符	[xz]'匹配x或者z	dizzy，zabra，extra
[^]	匹配不在括号中的任何字符	[^abc]'匹配任何不包括a、b或c的字符串	desk，fox，f8ke
字符串{n,}	匹配前面的字符串至少n次	b{2}匹配2个或者更多的b	bbb,bbbbb
字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，则省略	b{2,4}匹配最少2个，最多4个	bbb,bbb,bbbb

查询以特定字符或字符串开头的记录

1. mysql> select * from fruits where f_name regexp '^b';
2. +------+------+------------+---------+
3. | f_id | s_id | f_name     | f_price |
4. +------+------+------------+---------+
5. | b1   |  101 | blackberry |   10.20 |
6. | b2   |  104 | berry      |    7.60 |
7. | t1   |  102 | banana     |   10.30 |
8. +------+------+------------+---------+
9. 3 rows in set (0.00 sec)

查询以特定字符或字符串结尾的记录

1. mysql> select * from fruits where f_name regexp 'y$';
2. +------+------+------------+---------+
3. | f_id | s_id | f_name     | f_price |
4. +------+------+------------+---------+
5. | b1   |  101 | blackberry |   10.20 |
6. | b2   |  104 | berry      |    7.60 |
7. | c0   |  101 | cherry     |    3.20 |
8. | m2   |  105 | xbabay     |    2.60 |
9. +------+------+------------+---------+
10. 4 rows in set (0.05 sec)

用符合‘.’来代替字符串中的任意一个字符

1. mysql> select * from fruits where f_name regexp 'a.g';
2. +------+------+--------+---------+
3. | f_id | s_id | f_name | f_price |
4. +------+------+--------+---------+
5. | bs1  |  102 | orange |   11.20 |
6. | m1   |  106 | mango  |   15.70 |
7. +------+------+--------+---------+
8. 2 rows in set (0.00 sec)

**使用“*”和“+”来匹配多个字符**

1. mysql>  select * from fruits where f_name regexp '^ba*';
2. +------+------+------------+---------+
3. | f_id | s_id | f_name     | f_price |
4. +------+------+------------+---------+
5. | b1   |  101 | blackberry |   10.20 |
6. | b2   |  104 | berry      |    7.60 |
7. | t1   |  102 | banana     |   10.30 |
8. +------+------+------------+---------+
9. 3 rows in set (0.00 sec)
10. mysql> select * from fruits where f_name regexp '^ba+';
11. +------+------+--------+---------+
12. | f_id | s_id | f_name | f_price |
13. +------+------+--------+---------+
14. | t1   |  102 | banana |   10.30 |
15. +------+------+--------+---------+
16. 1 row in set (0.00 sec)

匹配指定字符串

1. mysql> select * from fruits where f_name regexp 'on|ap';
2. +------+------+---------+---------+
3. | f_id | s_id | f_name  | f_price |
4. +------+------+---------+---------+
5. | a1   |  101 | apple   |    5.20 |
6. | a2   |  103 | apricot |    2.20 |
7. | bs2  |  105 | melon   |    8.20 |
8. | l2   |  104 | lemon   |    6.40 |
9. | o2   |  103 | coconut |    9.20 |
10. | t2   |  102 | grape   |    5.30 |
11. +------+------+---------+---------+
12. 6 rows in set (0.00 sec)
13. mysql> select * from fruits where f_name like 'on';
14. Empty set (0.00 sec)

匹配指定字符中的任意一个

1. mysql> select * from fruits where f_name regexp '[ot]';
2. +------+------+---------+---------+
3. | f_id | s_id | f_name  | f_price |
4. +------+------+---------+---------+
5. | a2   |  103 | apricot |    2.20 |
6. | bs1  |  102 | orange  |   11.20 |
7. | bs2  |  105 | melon   |    8.20 |
8. | l2   |  104 | lemon   |    6.40 |
9. | m1   |  106 | mango   |   15.70 |
10. | m3   |  105 | xxtt    |   11.60 |
11. | o2   |  103 | coconut |    9.20 |
12. +------+------+---------+---------+
13. 7 rows in set (0.00 sec)
14. 
15. mysql> select * from fruits where s_id regexp '[456]';
16. +------+------+--------+---------+
17. | f_id | s_id | f_name | f_price |
18. +------+------+--------+---------+
19. | b2   |  104 | berry  |    7.60 |
20. | bs2  |  105 | melon  |    8.20 |
21. | l2   |  104 | lemon  |    6.40 |
22. | m1   |  106 | mango  |   15.70 |
23. | m2   |  105 | xbabay |    2.60 |
24. | m3   |  105 | xxtt   |   11.60 |
25. +------+------+--------+---------+
26. 6 rows in set (0.00 sec)

匹配指定字符以外的字符

1. mysql> select * from fruits where f_id regexp '[^a-e1-2]';
2. +------+------+---------+---------+
3. | f_id | s_id | f_name  | f_price |
4. +------+------+---------+---------+
5. | b5   |  107 | xxxx    |    3.60 |
6. | bs1  |  102 | orange  |   11.20 |
7. | bs2  |  105 | melon   |    8.20 |
8. | c0   |  101 | cherry  |    3.20 |
9. | l2   |  104 | lemon   |    6.40 |
10. | m1   |  106 | mango   |   15.70 |
11. | m2   |  105 | xbabay  |    2.60 |
12. | m3   |  105 | xxtt    |   11.60 |
13. | o2   |  103 | coconut |    9.20 |
14. | t1   |  102 | banana  |   10.30 |
15. | t2   |  102 | grape   |    5.30 |
16. | t4   |  107 | xbababa |    3.60 |
17. +------+------+---------+---------+
18. 12 rows in set (0.00 sec)

使用{n,}或者{n,m}来指定字符串连续出现的次数

1. mysql> select * from fruits where f_name regexp 'x{2,}';
2. +------+------+--------+---------+
3. | f_id | s_id | f_name | f_price |
4. +------+------+--------+---------+
5. | b5   |  107 | xxxx   |    3.60 |
6. | m3   |  105 | xxtt   |   11.60 |
7. +------+------+--------+---------+
8. 2 rows in set (0.00 sec)
9. mysql> select * from fruits where f_name regexp 'ba{1,3}';
10. +------+------+---------+---------+
11. | f_id | s_id | f_name  | f_price |
12. +------+------+---------+---------+
13. | m2   |  105 | xbabay  |    2.60 |
14. | t1   |  102 | banana  |   10.30 |
15. | t4   |  107 | xbababa |    3.60 |
16. +------+------+---------+---------+
17. 3 rows in set (0.00 sec)

MySQL-查询语句（三）

外连接查询

复合条件连接查询

子查询

带any、some关键字的子查询

带all关键字的子查询

带exists关键字的子查询

带in关键字的子查询

带比较运算符的子查询

合并查询结果

为表和字段取别名

为表取别名

为字段取别名

使用正则表达式查询

查询以特定字符或字符串开头的记录

查询以特定字符或字符串结尾的记录

用符合‘.’来代替字符串中的任意一个字符

**使用“*”和“+”来匹配多个字符**

匹配指定字符串

匹配指定字符中的任意一个

匹配指定字符以外的字符

使用{n,}或者{n,m}来指定字符串连续出现的次数

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL-查询语句（三）

外连接查询

复合条件连接查询

子查询

带any、some关键字的子查询

带all关键字的子查询

带exists关键字的子查询

带in关键字的子查询

带比较运算符的子查询

合并查询结果

为表和字段取别名

为表取别名

为字段取别名

使用正则表达式查询

查询以特定字符或字符串开头的记录

查询以特定字符或字符串结尾的记录

用符合‘.’来代替字符串中的任意一个字符

使用“*”和“+”来匹配多个字符

匹配指定字符串

匹配指定字符中的任意一个

匹配指定字符以外的字符

使用{n,}或者{n,m}来指定字符串连续出现的次数

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

**使用“*”和“+”来匹配多个字符**