mysql 交叉查询,mysql基础(25)_连接查询【一】(交叉查询、内连接)

连接查询

连接查询：将多张表连到一起进行查询(会导致记录数行和字段数列发生改变)

连接查询的意义

在关系型数据库设计过程中，实体(表)与实体之间是存在很多联系的。在关系型数据库表的设计过程中，遵循着关系来设计：一对一，一对多和多对多，通常在实际操作的过程中，需要利用这层关系来保证数据的完整性。

连接查询分类:

连接查询一共有以下几类：

交叉连接

内连接

外连接：左外连接(左连接)和右外连接(右连接)

自然连接

交叉连接

交叉连接：将两张表的数据与另外一张表彼此交叉

原理：

1、从第一张表依次取出每一条记录

2、取出每一条记录之后，与另外一张表的全部记录挨个匹配

3、没有任何匹配条件，所有的结果都会进行保留

4、记录数 = 第一张表记录数 * 第二张表记录数；字段数 = 第一张表字段数 + 第二张表字段数(笛卡尔积)

基本语法：

select from cross join [where子句]

或

select from , [where子句]

mysql> select * from student;

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

| id | name |

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

| 1 | 小明 |

| 2 | 小可 |

| 3 | 小艾 |

| 4 | 小新 |

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

4 rows in set (1.63 sec)

mysql> select * from teacher;

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

| name | age |

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

| 小明 | 26 |

| 小可 | 28 |

| 小云 | 22 |

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

3 rows in set (0.00 sec)

mysql> select * from student,teacher;

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

| id | name | name | age |

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

| 1 | 小明 | 小明 | 26 |

| 1 | 小明 | 小可 | 28 |

| 1 | 小明 | 小云 | 22 |

| 2 | 小可 | 小明 | 26 |

| 2 | 小可 | 小可 | 28 |

| 2 | 小可 | 小云 | 22 |

| 3 | 小艾 | 小明 | 26 |

| 3 | 小艾 | 小可 | 28 |

| 3 | 小艾 | 小云 | 22 |

| 4 | 小新 | 小明 | 26 |

| 4 | 小新 | 小可 | 28 |

| 4 | 小新 | 小云 | 22 |

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

12 rows in set (0.01 sec)

-- 交叉查询和上表一样，产生笛卡尔积，因此需要避免。加入条件后效率不如内/外连接，在此仅作了解。

mysql> select * from student cross join teacher;

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

| id | name | name | age |

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

| 1 | 小明 | 小明 | 26 |

| 1 | 小明 | 小可 | 28 |

| 1 | 小明 | 小云 | 22 |

| 2 | 小可 | 小明 | 26 |

| 2 | 小可 | 小可 | 28 |

| 2 | 小可 | 小云 | 22 |

| 3 | 小艾 | 小明 | 26 |

| 3 | 小艾 | 小可 | 28 |

| 3 | 小艾 | 小云 | 22 |

| 4 | 小新 | 小明 | 26 |

| 4 | 小新 | 小可 | 28 |

| 4 | 小新 | 小云 | 22 |

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

12 rows in set (0.00 sec)

应用

交叉连接产生的结果是笛卡尔积，没有实际应用。在MySQL中，多表查询一般使用内连接和外连接，它们的效率要高于交叉连接。

内连接

内连接：inner join，从一张表中取出所有的记录去另外一张表中匹配：利用匹配条件进行匹配，成功了则保留，失败了则放弃。

原理

1、从第一张表中取出一条记录，然后去另外一张表中进行匹配；

2、利用匹配条件进行匹配；

2.1匹配到：保留，继续向下匹配

2.2匹配失败：向下继续，如果全表匹配失败，结束。

语法

基本语法：表1 [inner] join 表2 on 匹配条件;

1、如果内连接没有条件(允许)，那么其实就是交叉连接(避免出现笛卡尔积)

2、使用匹配条件进行匹配

3、因为表的设计通常容易产生同名字段，尤其是ID，所以为了避免重名出现错误，通常使用表名.字段名，来确保唯一性。

4、通常，如果条件中使用到对应的表名，而表名通常比较长，所以可以通过表别名来简化。

5、内连接匹配的时候，必须保证匹配到才会保存。

6、内连接因为不强制必须使用匹配条件(on),因此可以在数据匹配完成之后，使用where条件来限制，效果和on一样(建议使用on)

-- 和交叉查询一样，也会产生笛卡尔积(前提条件是不带条件下查询)

mysql> select * from student inner join teacher;

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

| id | name | name | age |

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

| 1 | 小明 | 小明 | 26 |

| 1 | 小明 | 小可 | 28 |

| 1 | 小明 | 小云 | 22 |

| 2 | 小可 | 小明 | 26 |

| 2 | 小可 | 小可 | 28 |

| 2 | 小可 | 小云 | 22 |

| 3 | 小艾 | 小明 | 26 |

| 3 | 小艾 | 小可 | 28 |

| 3 | 小艾 | 小云 | 22 |

| 4 | 小新 | 小明 | 26 |

| 4 | 小新 | 小可 | 28 |

| 4 | 小新 | 小云 | 22 |

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

12 rows in set (0.00 sec)

-- 学生表别名s、教师表别名t，查询字段name同名信息。

mysql> select * from student as s inner join teacher t on s.name=t.name;

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

| id | name | name | age |

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

| 1 | 小明 | 小明 | 26 |

| 2 | 小可 | 小可 | 28 |

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

2 rows in set (0.00 sec)

-- 顺序反一下表的位置也变了

mysql> select * from teacher as t inner join student s on s.name=t.name;

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

| name | age | id | name |

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

| 小明 | 26 | 1 | 小明 |

| 小可 | 28 | 2 | 小可 |

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

2 rows in set (0.00 sec)

应用

内连接通常是在对数据有精确要求的地方使用，必须保证两种表中都能进行数据匹配。