mysql关联查询语句

前言

MySQL中当查询数据来自多张表时需要用到关联查询，其中关联查询又分为内连接、外连接、交叉连接。
创建两张表，分别是学生表、课程表。建表语句如下：

create table employee(
	empNo INT PRIMARY KEY,
	eName varchar(10) not NULL,
	job varchar(10),
	salary VARCHAR(10),
	dempNo VARCHAR(10) not NULL
);

create table demp(
	dempNo VARCHAR(10) PRIMARY KEY,
	dempName VARCHAR(10) not NULL
);
insert into demp VALUES('1','采购部'),('2','运营部'),('3','开发部'),('4','运维部');
insert INTO employee VALUES(1,'张三','采购','2000','1'),(2,'李四','采购','2000','1'),(3,'王五','采购','3000','1'),(4,'赵六','运营','3000','2'),(5,'田七','开发','6000','3');
alter TABLE employee ADD INDEX(dempNo);
alter table demp add INDEX(dempNo);

内连接

内连接又分为等值内连接与非等值内连接。其中等值内连接涉及where子句、on子句、using子句以及自然连接。

等值内连接

where子句

查询每个员工的编号、姓名、薪资、部门名称。

select employee.empNo,employee.eName,employee.salary,demp.dempName from employee,demp WHERE employee.dempNo=demp.dempNo;

结果：

on子句

查询员工姓名、薪水、部门名称

select e.eName,e.salary,d.dempName FROM demp d inner JOIN employee e ON e.dempNo=d.dempNo;

using子句

查询员工姓名、薪水、部门名称

select e.eName,e.salary,d.dempName from demp d INNER JOIN employee e USING(dempNo);

自然连接

以左右两表相同字段作为关联条件，会自动去掉重复字段。

select e.eName,e.salary,d.dempName FROM demp d NATURAL JOIN employee e;

非等值内连接

> < != between and都属于非等值条件。

select e.empNo,e.eName,e.dempNo,d.dempName from employee e,demp d WHERE e.dempNo=d.dempNo AND e.empNo BETWEEN 1 AND 3;

外连接

外连接主要分为左外连接、右外连接、全外连接。

左外连接

在内连接的基础上保证左表的数据都有，右表的字段用null补充，同样的字段会重复出现。

select * FROM demp d LEFT JOIN employee e on d.dempNo=e.dempNo;

右外连接

在内连接的基础上保证右表的数据都有，左表的字段用null补充，同样的字段会重复出现。

select * from employee d RIGHT JOIN demp e on d.dempNo=e.dempNo;

全外连接

在内连接基础上保证左右表的数据都有，是左连接和有连接的并集

select * from employee e full JOIN demp d on e.dempNo=d.dempNo;

需要关注的是，mysql中并不支持全外连接，想要实现全外连接可以通过union。

交叉连接

左表每条记录与右表每条记录首尾相连，创建数据库demp表中有4条数据，employee表中有5条数据，即查询结果共有4*5=20条记录。

select * from demp d CROSS JOIN employee e;

over;