mysql数据库多表查询教程_MySQL数据库实操教程(16)——多表连接查询

版权声明

在本节教程中，我们来一起学习多表连接查询。

交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。

在讲解该知识点之前，我们先准备数据，代码如下：

DROP TABLE IF EXISTS department;

DROP TABLE IF EXISTS employee;

-- 创建部门表

CREATE TABLE department(

did int (4) NOT NULL PRIMARY KEY,

dname varchar(20)

);

-- 创建员工表

CREATE TABLE employee (

eid int (4) NOT NULL PRIMARY KEY,

ename varchar (20),

eage int (2),

departmentid int (4) NOT NULL

);

-- 向部门表插入数据

INSERT INTO department VALUES(1,'财务部');

INSERT INTO department VALUES(2,'技术部');

INSERT INTO department VALUES(3,'行政部');

-- 向员工表插入数据

INSERT INTO employee VALUES(1,'李思思',19,1);

INSERT INTO employee VALUES(2,'王甜甜',19,2);

INSERT INTO employee VALUES(3,'张丽丽',19,3);

示例如下：

-- 交叉查询

SELECT * FROM department CROSS JOIN employee;

结果如下：

从上图的结果可以看出：交叉连接的结果就是两个表中所有数据的组合。这种连接在实际开发中极少使用。

内连接

内连接(Inner Jom)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的 记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

SELECT 查询宇段1,查询宇段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

在该语法中：INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

在讲解该知识点之前，我们先准备数据，代码如下：

DROP TABLE IF EXISTS department;

DROP TABLE IF EXISTS employee;

-- 创建部门表

CREATE TABLE department(

did int (4) NOT NULL PRIMARY KEY,

dname varchar(20)

);

-- 创建员工表

CREATE TABLE employee (

eid int (4) NOT NULL PRIMARY KEY,

ename varchar (20),

eage int (2),

departmentid int (4) NOT NULL

);

-- 向部门表插入数据

INSERT INTO department VALUES(1,'财务部');

INSERT INTO department VALUES(2,'技术部');

INSERT INTO department VALUES(3,'行政部');

-- 向员工表插入数据

INSERT INTO employee VALUES(1,'李思思',19,1);

INSERT INTO employee VALUES(2,'王甜甜',19,2);

INSERT INTO employee VALUES(3,'张丽丽',19,3);

示例如下：

SELECT employee.ename, department.dname FROM department INNER JOIN employee ON department.did=employee.departmentid;

结果如下：

其实，刚才的内连接也可以使用WHERE子句实现，代码如下：

SELECT employee.ename, department.dname FROM department,employee WHERE department.did=employee.departmentid;

虽然两者的结果相同，但是，请注意：INNER JOIN是内连接语句，WHERE是条件判断语句。在WHERE语句后可以直接添加其它条件而 INNER JOIN语句不可以。

在使用连接查询的过程中我们也可以为其设置限制和过滤的条件。

代码如下：

-- 带限制条件的内连接

SELECT employee.ename, employee.eage,department.dname

FROM department INNER JOIN employee ON department.did=employee.departmentid

WHERE employee.eage>20 ORDER BY employee.eage DESC;

结果如下：

外连接

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。其语法格式如下：

SELECT 查询宇段1,查询宇段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

由此可见，外连接的语法格式和内连接非常相似，只不过使用的是LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN关键字。其中，关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。

在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下：

LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。

RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

在讲解该知识点之前，我们先准备数据，代码如下：

DROP TABLE IF EXISTS student;

DROP TABLE IF EXISTS class;

-- 创建班级表

CREATE TABLE class(

cid int (4) NOT NULL PRIMARY KEY,

cname varchar(20)

);

-- 创建学生表

CREATE TABLE student (

sid int (4) NOT NULL PRIMARY KEY,

sname varchar (20),

sage int (2),

classid int (4) NOT NULL

);

-- 向班级表插入数据

INSERT INTO class VALUES(1001,'Java');

INSERT INTO class VALUES(1002,'C++');

INSERT INTO class VALUES(1003,'Python');

INSERT INTO class VALUES(1004,'PHP');

-- 向学生表插入数据

INSERT INTO student VALUES(1,'刘婷婷',20,1001);

INSERT INTO student VALUES(2,'王琴琴',21,1002);

INSERT INTO student VALUES(3,'杨洋洋',24,1002);

INSERT INTO student VALUES(4,'李霞霞',23,1003);

INSERT INTO student VALUES(5,'王跃跃',22,1009);

请注意数据的特点：

班级编号为1004的PHP班级没有学生

学号为5的学生王跃跃班级编号为1009，该班级编号并不在班级表中

左(外)连接

左(外)连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接 条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。

示例如下：

-- 左(外)连接

SELECT class.cid, class.cname,student.sname FROM class LEFT OUTER JOIN student ON class.cid=student.classid;

结果如下：

结果分析：

分别找出Java班、C++班、Python班的学生

右表的王跃跃不满足查询条件故其没有出现在查询结果中

虽然左表的PHP班没有学生，但是任然显示了PHP的信息；但是，它对应的学生名字为NULL

右(外)连接

右(外)连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。

示例如下：

-- 右(外)连接

SELECT class.cid, class.cname,student.sname FROM class RIGHT OUTER JOIN student ON class.cid=student.classid;

结果如下：

结果分析：

分别找出Java班、C++班、Python班的学生

左表的PHP班不满足查询条件故其没有出现在查询结果中

虽然右表的王跃跃没有对应班级，但是任然显示王跃跃的信息；但是，它对应的班级以及班级编号均为NULL

多表连接查询示意图