mysql联合查询查询语句_MySQL连接查询、联合查询、子查询

参考地址：http://blog.csdn.net/u011277123/article/details/54863371

1.MySQL连接查询

连接查询：将多张表(>=2)进行记录的连接(按照某个指定的条件进行数据拼接)。

连接查询的意义: 在用户查看数据的时候,需要显示的数据来自多张表.

连接查询分类：SQL中将连接查询分成四类: 内连接,外连接,自然连接和交叉连接

1.1、交叉连接：

交叉连接: cross join, 从一张表中循环取出每一条记录, 每条记录都去另外一张表进行匹配: 匹配一定保留(没有条件匹配), 而连接本身字段就会增加(保留),最终形成的结果叫做: 笛卡尔积。基本用到的很少。

mysql> select * from tb_goods;

+----+--------------+-----------+

| id | name | parent_id |

+----+--------------+-----------+

| 1 | 家用电器 | 0 |

| 2 | 大家电 | 1 |

| 3 | 电脑 | 2 |

| 4 | 铅笔 | 0 |

| 5 | 耳机 | 4 |

| 6 | 可乐 | 3 |

+----+--------------+-----------+

6 rows in set (0.00 sec)

mysql> select * from ying;

+------------+-----+

| id | age |

+------------+-----+

| 0000000032 | 0 |

| 0000000444 | 0 |

| 0000333333 | 0 |

| 0000000033 | 3 |

+------------+-----+

4 rows in set (0.00 sec)

mysql> select * from tb_goods cross join ying;(在MySQL中cross join和inner join其实是一样的，所以select * from tb_goods inner join ying也是同样的效果)

+----+--------------+-----------+------------+-----+

| id | name | parent_id | id | age |

+----+--------------+-----------+------------+-----+

| 1 | 家用电器 | 0 | 0000000032 | 0 |

| 1 | 家用电器 | 0 | 0000000444 | 0 |

| 1 | 家用电器 | 0 | 0000333333 | 0 |

| 1 | 家用电器 | 0 | 0000000033 | 3 |

| 2 | 大家电 | 1 | 0000000032 | 0 |

| 2 | 大家电 | 1 | 0000000444 | 0 |

| 2 | 大家电 | 1 | 0000333333 | 0 |

| 2 | 大家电 | 1 | 0000000033 | 3 |

| 3 | 电脑 | 2 | 0000000032 | 0 |

| 3 | 电脑 | 2 | 0000000444 | 0 |

| 3 | 电脑 | 2 | 0000333333 | 0 |

| 3 | 电脑 | 2 | 0000000033 | 3 |

| 4 | 铅笔 | 0 | 0000000032 | 0 |

| 4 | 铅笔 | 0 | 0000000444 | 0 |

| 4 | 铅笔 | 0 | 0000333333 | 0 |

| 4 | 铅笔 | 0 | 0000000033 | 3 |

| 5 | 耳机 | 4 | 0000000032 | 0 |

| 5 | 耳机 | 4 | 0000000444 | 0 |

| 5 | 耳机 | 4 | 0000333333 | 0 |

| 5 | 耳机 | 4 | 0000000033 | 3 |

| 6 | 可乐 | 3 | 0000000032 | 0 |

| 6 | 可乐 | 3 | 0000000444 | 0 |

| 6 | 可乐 | 3 | 0000333333 | 0 |

| 6 | 可乐 | 3 | 0000000033 | 3 |

+----+--------------+-----------+------------+-----+

24 rows in set (0.00 sec)

1.2、内连接

内连接: [inner] join, 从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

基本语法：select * from tb_name1 inner join tb_name2 on tb_name.id = tb_name2.id； on表示连接条件: 条件字段就是代表相同的业务含义,如果没有on条件就是笛卡尔乘积，on可以使用where来代替，但是这样效率会比较低

mysql> select * from tt1;

+----+-------+--------+

| id | name | gender |

+----+-------+--------+

| 1 | xiong | 0 |

| 2 | ying | 0 |

| 3 | cai | 0 |

| 4 | zhang | 0 |

| 5 | li | 1 |

| 6 | wang | 1 |

+----+-------+--------+

6 rows in set (0.00 sec)

mysql> select * from tt2;

+----+------+------+--------+

| id | age | name | gender |

+----+------+------+--------+

| 1 | 23 | a | 1 |

| 2 | 24 | b | 1 |

| 3 | 33 | c | 2 |

| 4 | 43 | d | 2 |

| 5 | 11 | d | 2 |

+----+------+------+--------+

5 rows in set (0.00 sec)

mysql> select * from tt2 inner join tt1 on tt1.gender = tt2.gender;

+----+------+------+--------+----+------+--------+

| id | age | name | gender | id | name | gender |

+----+------+------+--------+----+------+--------+

| 1 | 23 | a | 1 | 5 | li | 1 |

| 2 | 24 | b | 1 | 5 | li | 1 |

| 1 | 23 | a | 1 | 6 | wang | 1 |

| 2 | 24 | b | 1 | 6 | wang | 1 |

+----+------+------+--------+----+------+--------+

4 rows in set (0.00 sec)

1.3、外连接:

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL

1.3.1、左外连接 left join:以左表为主

mysql> select * from tt1;

+----+-------+--------+

| id | name | gender |

+----+-------+--------+

| 1 | xiong | 0 |

| 2 | ying | 0 |

| 3 | cai | 0 |

| 4 | zhang | 0 |

| 5 | li | 1 |

| 6 | wang | 1 |

+----+-------+--------+

6 rows in set (0.00 sec)

mysql> select * from tt2;

+----+------+------+--------+

| id | age | name | gender |

+----+------+------+--------+

| 1 | 23 | a | 1 |

| 2 | 24 | b | 1 |

| 3 | 33 | c | 2 |

| 4 | 43 | d | 2 |

| 5 | 11 | d | 2 |

+----+------+------+--------+

5 rows in set (0.00 sec)

mysql> select * from tt2 left join tt1 on tt1.gender = tt2.gender;

+----+------+------+--------+------+------+--------+

| id | age | name | gender | id | name | gender |

+----+------+------+--------+------+------+--------+

| 1 | 23 | a | 1 | 5 | li | 1 |

| 2 | 24 | b | 1 | 5 | li | 1 |

| 1 | 23 | a | 1 | 6 | wang | 1 |

| 2 | 24 | b | 1 | 6 | wang | 1 |

| 3 | 33 | c | 2 | NULL | NULL | NULL |

| 4 | 43 | d | 2 | NULL | NULL | NULL |

| 5 | 11 | d | 2 | NULL | NULL | NULL |

+----+------+------+--------+------+------+--------+

7 rows in set (0.00 sec)

1.3.2、右外连接 left join:以右表为主

mysql> select * from tt1;

+----+-------+--------+

| id | name | gender |

+----+-------+--------+

| 1 | xiong | 0 |

| 2 | ying | 0 |

| 3 | cai | 0 |

| 4 | zhang | 0 |

| 5 | li | 1 |

| 6 | wang | 1 |

+----+-------+--------+

6 rows in set (0.00 sec)

mysql> select * from tt2;

+----+------+------+--------+

| id | age | name | gender |

+----+------+------+--------+

| 1 | 23 | a | 1 |

| 2 | 24 | b | 1 |

| 3 | 33 | c | 2 |

| 4 | 43 | d | 2 |

| 5 | 11 | d | 2 |

+----+------+------+--------+

5 rows in set (0.00 sec)

mysql> select * from tt2 right join tt1 on tt1.gender = tt2.gender;

+------+------+------+--------+----+-------+--------+

| id | age | name | gender | id | name | gender |

+------+------+------+--------+----+-------+--------+

| 1 | 23 | a | 1 | 5 | li | 1 |

| 1 | 23 | a | 1 | 6 | wang | 1 |

| 2 | 24 | b | 1 | 5 | li | 1 |

| 2 | 24 | b | 1 | 6 | wang | 1 |

| NULL | NULL | NULL | NULL | 1 | xiong | 0 |

| NULL | NULL | NULL | NULL | 2 | ying | 0 |

| NULL | NULL | NULL | NULL | 3 | cai | 0 |

| NULL | NULL | NULL | NULL | 4 | zhang | 0 |

+------+------+------+--------+----+-------+--------+

8 rows in set (0.00 sec)

联合查询：

联合查询：将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)

基本语法：多条select语句构成，每一条select语句获取的字段数必须严格一致(但是字段类型无关)【联合查询只要求字段一样, 跟数据类型无关，即使名字不同也可以】

另外联合查询默认是去重(distinct)的，如果需要显示全部则使用union all

联合查询的意义：

1. 查询同一张表,但是需求不同: 如查询学生信息, 男生身高升序, 女生身高降序.

2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

mysql> select * from tt1;

+----+-------+--------+

| id | name | gender |

+----+-------+--------+

| 1 | xiong | 0 |

| 2 | ying | 0 |

| 3 | cai | 0 |

| 4 | zhang | 0 |

| 5 | li | 1 |

| 6 | wang | 1 |

+----+-------+--------+

6 rows in set (0.00 sec)

mysql> select * from tt2;

+----+------+------+--------+

| id | age | name | gender |

+----+------+------+--------+

| 1 | 23 | a | 1 |

| 2 | 24 | b | 1 |

| 3 | 33 | c | 2 |

| 4 | 43 | d | 2 |

| 5 | 11 | d | 2 |

+----+------+------+--------+

5 rows in set (0.00 sec)

mysql> select id,name from tt1

-> union

-> select name,age from tt2;

+----+-------+

| id | name |

+----+-------+

| 1 | xiong |

| 2 | ying |

| 3 | cai |

| 4 | zhang |

| 5 | li |

| 6 | wang |

| a | 23 |

| b | 24 |

| c | 33 |

| d | 43 |

| d | 11 |

+----+-------+

11 rows in set (0.00 sec)

第二个表查询的内容直接连在第一个表查询结果的后面

order by的使用：

在联合查询中: order by不能直接使用,需要对查询语句使用括号才行；另外，要orderby生效: 必须搭配limit: limit使用限定的最大数即可.

子查询：

子查询：子查询是在某个查询结果之上进行的.(一条select语句内部包含了另外一条select语句).

子查询分类：

子查询有两种分类方式: 按位置分类;和按结果分类

按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

From子查询: 子查询跟在from之后

Where子查询: 子查询出现where条件中

Exists子查询: 子查询出现在exists里面

按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

标量子查询: 子查询得到的结果是一行一列

列子查询: 子查询得到的结果是一列多行

行子查询: 子查询得到的结果是多列一行(多行多列) (1,2,3出现的位置都是在where之后)

表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

标量子查询：

需求: 找到分类为科技的所有文章标题

列子查询：

找出语文和数学都及格人的名字，表是这样的：

行子查询：

行子查询: 返回的结果可以是多行多列(一行多列)

需求：找出年龄最大且身高最高的人

方案一：

方案二：

表子查询：

表子查询: 子查询返回的结果是多行多列的二维表: 子查询返回的结果是当做二维表来使用

需求：找出每个人最好的成绩

Exists子查询：

Exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表), exists是接在where之后: exists返回的结果只有0和1.

需求：如果存在的话身高大于2米的人，列出表中所有数据