MySQL连接查询、联合查询、子查询

时间:2022-01-02 16:52:02

参考地址: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使用限定的最大数即可.

MySQL连接查询、联合查询、子查询

子查询:

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

子查询分类:

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

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

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

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

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

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

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

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

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

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

标量子查询:

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

MySQL连接查询、联合查询、子查询

MySQL连接查询、联合查询、子查询

MySQL连接查询、联合查询、子查询

列子查询:

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

MySQL连接查询、联合查询、子查询

MySQL连接查询、联合查询、子查询

行子查询:

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

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

MySQL连接查询、联合查询、子查询

方案一:

MySQL连接查询、联合查询、子查询

方案二:

MySQL连接查询、联合查询、子查询

表子查询:

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

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

MySQL连接查询、联合查询、子查询

MySQL连接查询、联合查询、子查询

Exists子查询:

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

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

MySQL连接查询、联合查询、子查询

MySQL连接查询、联合查询、子查询