黑马程序员---内连接和左右连接

布满荆棘的人生 2022-11-27 12:26 134阅读 0赞

一：内连接查询：  
1：查询 有能够对应班级的学生以及班级信息。

mysql> select * from students inner join classes on students.cls_id = classes.id;
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    | id | name         | age  | height | gender | cls_id | is_delete | id | name         |
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |  2 | python_02期  |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |  1 | python_01期  |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |  2 | python_02期  |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |  1 | python_01期  |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期  |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |  1 | python_01期  |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |  1 | python_01期  |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |  2 | python_02期  |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |  2 | python_02期  |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |  1 | python_01期  |
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    12 rows in set (0.00 sec)

取别名：

mysql> select * from students as s inner join classes as c on s.cls_id = c.id;
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    | id | name         | age  | height | gender | cls_id | is_delete | id | name         |
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |  2 | python_02期  |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |  1 | python_01期  |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |  2 | python_02期  |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |  1 | python_01期  |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期  |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |  1 | python_01期  |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |  1 | python_01期  |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |  2 | python_02期  |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |  2 | python_02期  |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |  1 | python_01期  |
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    12 rows in set (0.00 sec)

2：显示两个表的姓名？问题俩名字相同怎么办？

mysql> mysql> select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
    +--------------+--------------+
    | name         | name         |
    +--------------+--------------+
    | 小明         | python_01期  |
    | 小月月       | python_02期  |
    | 彭于晏       | python_01期  |
    | 刘德华       | python_02期  |
    | 黄蓉         | python_01期  |
    | 凤姐         | python_02期  |
    | 王祖贤       | python_01期  |
    | 周杰伦       | python_01期  |
    | 程坤         | python_02期  |
    | 刘亦菲       | python_02期  |
    | 凌小小       | python_01期  |
    | 司马二狗     | python_01期  |
    +--------------+--------------+
    12 rows in set (0.00 sec)

4：两个都是name不好看，取别名：

mysql> select s.name 姓名,c.namee 班级 from students as s inner join classes as c on s.cls_id = c.id;
    +--------------+--------------+
    | 姓名         | 班级         |
    +--------------+--------------+
    | 小明         | python_01期  |
    | 小月月       | python_02期  |
    | 彭于晏       | python_01期  |
    | 刘德华       | python_02期  |
    | 黄蓉         | python_01期  |
    | 凤姐         | python_02期  |
    | 王祖贤       | python_01期  |
    | 周杰伦       | python_01期  |
    | 程坤         | python_02期  |
    | 刘亦菲       | python_02期  |
    | 凌小小       | python_01期  |
    | 司马二狗     | python_01期  |
    +--------------+--------------+
    12 rows in set (0.00 sec)

5：内连接的另一种写法：

mysql> select *  from students , classes where  students.cls_id = classes.id;
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    | id | name         | age  | height | gender | cls_id | is_delete | id | name         |
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |  2 | python_02期  |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |  1 | python_01期  |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |  2 | python_02期  |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |  1 | python_01期  |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期  |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |  1 | python_01期  |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |  1 | python_01期  |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |  2 | python_02期  |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |  2 | python_02期  |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |  1 | python_01期  |
    +----+--------------+------+--------+--------+--------+-----------+----+--------------+
    12 rows in set (0.00 sec)

6：查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.\*，只显示班级名称 classes.name.

mysql> select students.* , classes.name  from students , classes where  students.cls_id = classes.id;
    +----+--------------+------+--------+--------+--------+-----------+--------------+
    | id | name         | age  | height | gender | cls_id | is_delete | name         |
    +----+--------------+------+--------+--------+--------+-----------+--------------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           | python_01期  |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          | python_02期  |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           | python_01期  |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          | python_02期  |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           | python_01期  |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          | python_02期  |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          | python_01期  |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           | python_01期  |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           | python_02期  |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           | python_02期  |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           | python_01期  |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           | python_01期  |
    +----+--------------+------+--------+--------+--------+-----------+--------------+
    12 rows in set (0.00 sec)

或者：

select s.*,c.name from students s inner join classes c on s.cls_id=c.id;

7：在以上的查询中，将班级名称显示在第1列

mysql> select classes.name , students.*   from students , classes where  students.cls_id = classes.id;
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    | name         | id | name         | age  | height | gender | cls_id | is_delete |
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    | python_01期  |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    | python_02期  |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    | python_01期  |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    | python_02期  |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    | python_01期  |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    | python_02期  |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    | python_01期  |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    | python_01期  |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    | python_02期  |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | python_02期  | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | python_01期  | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | python_01期  | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    12 rows in set (0.00 sec)

或者：

select c.name,s.* from students s inner join classes c on s.cls_id=c.id;

8：查询 有能够对应班级的学生以及班级信息, 按照班级编号进行排序

mysql> select classes.name , students.*   from students , classes where  students.cls_id = classes.id order by classes.id;
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    | name         | id | name         | age  | height | gender | cls_id | is_delete |
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    | python_01期  |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    | python_01期  |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    | python_01期  | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | python_01期  |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    | python_01期  |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    | python_01期  |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    | python_01期  | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    | python_02期  |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    | python_02期  |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    | python_02期  | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    | python_02期  |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    | python_02期  |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    12 rows in set (0.00 sec)

或者：

select c.name,s.* from students s inner join classes c on s.cls_id=c.id order by c.id;

9：当是同一个班级的时候，按照学生的id进行从小到大排序。

mysql> select classes.name , students.*   from students , classes where  students.cls_id = classes.id order by classes.id, students.id;
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    | name         | id | name         | age  | height | gender | cls_id | is_delete |
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    | python_01期  |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |
    | python_01期  |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |
    | python_01期  |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |
    | python_01期  |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |
    | python_01期  |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |
    | python_01期  | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |
    | python_01期  | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |
    | python_02期  |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |
    | python_02期  |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |
    | python_02期  |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |
    | python_02期  |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |
    | python_02期  | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |
    +--------------+----+--------------+------+--------+--------+--------+-----------+
    12 rows in set (0.00 sec)

或者：

select c.name,s.* from students s inner join classes c on s.cls_id=c.id order by c.id, s.id;

二：左连接和右连接  
左表内容加中间部分，左表存在，右表不存的补null。  
右连接则相反。  
1：查询每位学生对应的班级信息。（左连接）：找出那个人是没有班级的。

mysql> select * from students left join classes on students.cls_id = classes.id ;
    +----+--------------+------+--------+--------+--------+-----------+------+--------------+
    | id | name         | age  | height | gender | cls_id | is_delete | id   | name         |
    +----+--------------+------+--------+--------+--------+-----------+------+--------------+
    |  1 | 小明         |   18 | 180.00 | 女     |      1 |           |    1 | python_01期  |
    |  3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |    1 | python_01期  |
    |  5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |    1 | python_01期  |
    |  7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |    1 | python_01期  |
    |  8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |    1 | python_01期  |
    | 15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |    1 | python_01期  |
    | 16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |    1 | python_01期  |
    |  2 | 小月月       |   18 | 180.00 | 女     |      2 |          |    2 | python_02期  |
    |  4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |    2 | python_02期  |
    |  6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |    2 | python_02期  |
    |  9 | 程坤         |   27 | 181.00 | 男     |      2 |           |    2 | python_02期  |
    | 10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |    2 | python_02期  |
    | 11 | 金星         |   33 | 162.00 | 中性   |      3 |          | NULL | NULL         |
    | 12 | 静香         |   12 | 180.00 | 女     |      4 |           | NULL | NULL         |
    | 13 | 郭靖         |   12 | 170.00 | 男     |      4 |           | NULL | NULL         |
    | 14 | 周杰         |   34 | 176.00 | 女     |      5 |           | NULL | NULL         |
    +----+--------------+------+--------+--------+--------+-----------+------+--------------+
    16 rows in set (0.00 sec)

过滤出那个人是没有班级的：

mysql> select * from students left join classes on students.cls_id = classes.id where classes.id is null;
    +----+--------+------+--------+--------+--------+-----------+------+------+
    | id | name   | age  | height | gender | cls_id | is_delete | id   | name |
    +----+--------+------+--------+--------+--------+-----------+------+------+
    | 11 | 金星   |   33 | 162.00 | 中性   |      3 |          | NULL | NULL |
    | 12 | 静香   |   12 | 180.00 | 女     |      4 |           | NULL | NULL |
    | 13 | 郭靖   |   12 | 170.00 | 男     |      4 |           | NULL | NULL |
    | 14 | 周杰   |   34 | 176.00 | 女     |      5 |           | NULL | NULL |
    +----+--------+------+--------+--------+--------+-----------+------+------+
    4 rows in set (0.00 sec)

2：查询每位学生对应的班级信息。（右连接）：找出有哪个班是没有人报的。

mysql> select * from students right join classes on students.cls_id = classes.id ;
    +------+--------------+------+--------+--------+--------+-----------+----+--------------+
    | id   | name         | age  | height | gender | cls_id | is_delete | id | name         |
    +------+--------------+------+--------+--------+--------+-----------+----+--------------+
    |    1 | 小明         |   18 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    |    2 | 小月月       |   18 | 180.00 | 女     |      2 |          |  2 | python_02期  |
    |    3 | 彭于晏       |   29 | 185.00 | 男     |      1 |           |  1 | python_01期  |
    |    4 | 刘德华       |   59 | 175.00 | 男     |      2 |          |  2 | python_02期  |
    |    5 | 黄蓉         |   38 | 160.00 | 女     |      1 |           |  1 | python_01期  |
    |    6 | 凤姐         |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期  |
    |    7 | 王祖贤       |   18 | 172.00 | 女     |      1 |          |  1 | python_01期  |
    |    8 | 周杰伦       |   36 |   NULL | 男     |      1 |           |  1 | python_01期  |
    |    9 | 程坤         |   27 | 181.00 | 男     |      2 |           |  2 | python_02期  |
    |   10 | 刘亦菲       |   25 | 166.00 | 女     |      2 |           |  2 | python_02期  |
    |   15 | 凌小小       |   28 | 180.00 | 女     |      1 |           |  1 | python_01期  |
    |   16 | 司马二狗     |   28 | 120.00 | 男     |      1 |           |  1 | python_01期  |
    | NULL | NULL         | NULL |   NULL | NULL   |   NULL | NULL      |  8 | Python_03期  |
    +------+--------------+------+--------+--------+--------+-----------+----+--------------+
    13 rows in set (0.00 sec)

3：过滤出那个班级是没有人报的。

注意：classes 与 students 互换 ，那么左连接结果就变成右连接了。右连接就变成左连接了。