MYSQL数据库-（十三）- join连接讲解、多表删除

目录

一、连接

二、多表删除

---

一、join连接

• 连接条件

三种连接类型示意图

• 1、内连接：inner join
• mysql> select a.id,a.name from ceshi AS a inner join ceshi_two AS b on a.name = b.name;

• 2、左外连接
• mysql> select a.id,a.name from ceshi AS a left join ceshi_two AS b on a.name = b.name;

• 3、右外连接
• mysql> select a.id,a.name from ceshi AS a right join ceshi_two AS b on a.name = b.name;

• 案例代码

第一步：创建数据表

mysql> create table ceshi(
    ->      id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->      name  VARCHAR(150) NOT NULL,
    ->      sex  varchar(20) not null);
Query OK, 0 rows affected (0.13 sec)


第二步：我们添加多条数据、如下：


//INSERT ceshi(name,sex) VALUES('老王','男');

//INSERT ceshi(name,sex) VALUES('老耿','帅哥');

//INSERT ceshi(name,sex) VALUES('老李','男');

//INSERT ceshi(name,sex) VALUES('老王','男');

//INSERT ceshi(name,sex) VALUES('老王','男');

//INSERT ceshi(name,sex) VALUES('小花','女');

//INSERT ceshi(name,sex) VALUES('小翠','妇女');

//INSERT ceshi(name,sex) VALUES('小三','女');

//INSERT ceshi(name,sex) VALUES('小名','儿童');



//第三步：我们对测试数据表进行分组

mysql> select * from ceshi;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 老王     | 男     |
|  2 | 老耿    | 帅哥    |
|  3 | 老李     | 男     |
|  4 | 老王     | 男     |
|  5 | 老王     | 男     |
|  6 | 小花    | 女    |
|  7 | 小翠    | 妇女    |
|  8 | 小三    | 女    |
|  9 | 小名    | 儿童    |
+----+------+------+
9 rows in set (0.00 sec)



//第四部步：写进我们新的数据表中，使用create...select，创建时写入数据。


//写入成功

mysql>   create table ceshi_two(
    ->      id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->      name  VARCHAR(150) NOT NULL)
    ->      select name from ceshi group by name;
Query OK, 6 rows affected (0.17 sec)
Records: 6  Duplicates: 0  Warnings: 0


//第五步，查看我们写的数据表数据


mysql> select * from ceshi_two;
+----+------+
| id | name |
+----+------+
|  1 | 小三    |
|  2 | 小名    |
|  3 | 小翠    |
|  4 | 小花    |
|  5 | 老李     |
|  6 | 老王     |
|  7 | 老耿    |
+----+------+
7 rows in set (0.00 sec)



//在给两个数据表，插入几条不通的数据

//INSERT ceshi(name,sex) VALUES('小狼','动物'),('小龙','动物');
//INSERT ceshi_two(name) VALUES('小小猪'),('小小鸡');


//插入成功
mysql> INSERT ceshi(name,sex) VALUES('小狼','动物'),('小龙','动物');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT ceshi_two(name) VALUES('小小猪'),('小小鸡');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

//第六步：查看插入后，两张数据表不通的数据。

mysql> select * from ceshi;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 老王     | 男     |
|  2 | 老耿    | 帅哥    |
|  3 | 老李     | 男     |
|  4 | 老王     | 男     |
|  5 | 老王     | 男     |
|  6 | 小花    | 女    |
|  7 | 小翠    | 妇女    |
|  8 | 小三    | 女    |
|  9 | 小名    | 儿童    |
| 10 | 小狼    | 动物     |
| 11 | 小龙    | 动物     |
+----+------+------+
11 rows in set (0.00 sec)


mysql> select * from ceshi_two;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小三      |
|  2 | 小名      |
|  3 | 小翠      |
|  4 | 小花      |
|  5 | 老李       |
|  6 | 老王       |
|  7 | 老耿      |
|  8 | 小小猪     |
|  9 | 小小鸡     |
+----+--------+
9 rows in set (0.00 sec)



//第七步：内连接

//所谓的内连接，就是即两者数据表都符合，所以，两张数据表存在相同的数据都会被打印出来，而一张数据表存在的name,但是另一张不存在，就不会被打印出来。例如小小猪。

mysql> select a.id,a.name from ceshi AS a
    -> inner join ceshi_two AS b on a.name = b.name;
+----+------+
| id | name |
+----+------+
|  1 | 老王     |
|  2 | 老耿    |
|  3 | 老李     |
|  4 | 老王     |
|  5 | 老王     |
|  6 | 小花    |
|  7 | 小翠    |
|  8 | 小三    |
|  9 | 小名    |
+----+------+
9 rows in set (0.00 sec)



//第八步：左外连接



mysql> select a.id,a.name from ceshi AS a
    -> left join ceshi_two AS b on a.name = b.name;
+----+------+
| id | name |
+----+------+
|  1 | 老王     |
|  2 | 老耿    |
|  3 | 老李     |
|  4 | 老王     |
|  5 | 老王     |
|  6 | 小花    |
|  7 | 小翠    |
|  8 | 小三    |
|  9 | 小名    |
| 10 | 小狼    |
| 11 | 小龙    |
+----+------+
11 rows in set (0.00 sec)



//第九步：右外连接

mysql> select a.id,a.name from ceshi AS a
    -> right join ceshi_two AS b on a.name = b.name;
+------+------+
| id   | name |
+------+------+
|    8 | 小三    |
|    9 | 小名    |
|    7 | 小翠    |
|    6 | 小花    |
|    3 | 老李     |
|    1 | 老王     |
|    4 | 老王     |
|    5 | 老王     |
|    2 | 老耿    |
| NULL | NULL |
| NULL | NULL |
+------+------+
11 rows in set (0.00 sec)

多表删除

//第一步，我们首先需要知道那些数据是多余的。

//创建数据表：
create table ceshi(
     id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     name  VARCHAR(150) NOT NULL,
     sex  varchar(20) not null);
Query OK, 0 rows affected (0.11 sec)



//添加很多条数据，其中老王是故意重复的

INSERT ceshi(name,sex) VALUES('老王','男');

INSERT ceshi(name,sex) VALUES('老耿','男');

INSERT ceshi(name,sex) VALUES('老李','男');

INSERT ceshi(name,sex) VALUES('老王','男');

INSERT ceshi(name,sex) VALUES('老王','男');

INSERT ceshi(name,sex) VALUES('小花','女');

INSERT ceshi(name,sex) VALUES('小猫','动物');

INSERT ceshi(name,sex) VALUES('小狗','动物');

//这里面会发现，添加三条相同的数据


//查看插入的数据：

mysql> select * from ceshi;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | 老王     | 男    |
|  2 | 老耿    | 男    |
|  3 | 老李     | 男    |
|  4 | 老王     | 男    |
|  5 | 老王     | 男    |
|  6 | 小花    | 女   |
+----+------+-----+
6 rows in set (0.00 sec)

//首先我们需要知道我们重复的都有哪些数据，

//第一步：进行对数据表进行分组，group by.
//第二步：进行后通过having进行限制筛选，条数大于等于2的
//第三部：进行多表删除。



//案例：
//第一步，进行对数据分组，并通过having进行限制筛选，获得存在重复数据老王，如下：

mysql> select id,name from ceshi group by name having count(name)>=2;
+----+------+
| id | name |
+----+------+
|  1 | 老王     |
+----+------+
1 row in set (0.00 sec)


//第二步，进行删除，请仔细看删除语句，我们把筛选出来的 AS t2 当做子表。来进行筛选。例如id和id的下一位进行比较，如果name相同，就删除。


//请仔细看下面这句代码，删除的关键


mysql> delete t1 from ceshi AS t1 left join (select id,name from ceshi group by
name having count(name)>=2) as t2 on t1.name = t2.name where t1.id>t2.id;
Query OK, 2 rows affected (0.10 sec)//删除成功



//语句讲解：
//1、首先cheshi数据表起一个别名为 t1  
//2、通过子连接来筛选出ceshi数据表中相同的数据，我们用他当做一张新的数据吧，别名为t2
//3、添加左外连接、
//4、然后根据id的编号来进行对比，例如id=1的name和id=2的name相同就进行删除。

//删除后，我们查看数据表：

mysql> select * from ceshi;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | 老王     | 男    |
|  2 | 老耿    | 男    |
|  3 | 老李     | 男    |
|  6 | 小花    | 女   |
+----+------+-----+
4 rows in set (0.00 sec)