有学有练才叫学习:学而不思则罔,思而不学则殆:学而不习,纸上谈兵,习而不进,画地为牢!

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

MySQL 炮渣日记 3周前 (11-17) 40次浏览 已收录 0个评论 扫描二维码

关联查询

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

作用:从2张或多张表中,取出有关联的数据.

关联查询一共有几种情况:

内连接:INNER JOIN 、CROSS JOIN

外连接:左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、全外连接(FULL OUTER JOIN)

自连接:当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义

说明:

(1)连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。

(2)当两个关联查询的表如果有字段名字相同,并且要查询中涉及该关联字段,那么需要使用表名前缀加以区分

(3)当如果表名比较长时,可以给表取别名,简化SQL语句

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

笛卡尔积

定义:将两(或多)个表的所有行进行组合,连接后的行数为两(或多)个表的乘积数.

在MySQL中如下情况会出现笛卡尔积,主要是因为缺少关联条件或者关联条件不准确

注:外连接必须写关联条件,否则报语法错误

#笛卡尔积
#查询员工姓名和所在部门名称
SELECT ename,dname FROM t_employee,t_department;
SELECT ename,dname FROM t_employee INNER JOIN t_department;
SELECT ename,dname FROM t_employee CROSS JOIN t_department;
SELECT ename,dname FROM t_employee JOIN t_department;

关联条件

表连接的约束条件可以有三种方式:WHERE, ON, USING

WHERE:适用于所有关联查询

ON:只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写,但分开写可读性更好。

USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字段值相等

#关联条件
#把关联条件写在where后面
SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did;
#把关联条件写在on后面,只能和JOIN一起使用
SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did;
#把关联字段写在using()中,只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同,而且只能表示=
#查询员工姓名与基本工资
SELECT ename,basic_salary FROM t_employee INNER JOIN t_salary USING(eid);
#n张表关联,需要n-1个关联条件
#查询员工姓名,基本工资,部门名称
SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary
WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;
SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary
ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

内连接(INNER JOIN)

有两种,显式的和隐式的,返回连接表中符合连接条件和查询条件的数据行

格式:

隐式:

SELECT [cols_list] from 表1,表2 where [condition]

显式:

SELECT [cols_list] from 表1 INNER JOIN 表2 ON [关联条件] where [其他筛选条件]

SELECT [cols_list] from 表1 CROSS JOIN 表2 ON [关联条件] where [其他筛选条件]

SELECT [cols_list] from 表1 JOIN 表2 ON [关联条件] where [其他筛选条件]

#内连接
#查询员工姓名和所在部门名称
SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did;
#查询员工姓名,基本工资,部门名称
SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary
WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;
SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary
ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

外连接(OUTER JOIN)

外连接分为:

左外连接(LEFT OUTER JOIN),简称左连接(LEFT JOIN)

右外连接(RIGHT OUTER JOIN),简称右连接(RIGHT JOIN)

全外连接(FULL OUTER JOIN),简称全连接(FULL JOIN)。

左连接(LEFT JOIN)

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询
返回左表中的所有行,如果左表中行在右表中没有匹配行,则结果中右表中的列返回空值。

返回左边中行在右表中没有匹配行的记录

#左连接

#查询所有部门信息以及该部门员工信息

SELECT did,dname,eid,ename

FROM t_department LEFT OUTER JOIN t_employee

ON t_department.did = t_employee.dept_id;

#查询部门信息,仅保留没有员工的部门信息

SELECT did,dname,eid,ename

FROM t_department LEFT OUTER JOIN t_employee

ON t_department.did = t_employee.dept_id

WHERE t_employee.dept_id IS NULL;

#“从表外键列”是NULL

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询
#查询所有员工信息,以及员工的部门信息

SELECT eid,ename,did,dname

FROM t_employee LEFT OUTER JOIN t_department

ON t_employee.dept_id = t_department.did ;

#查询员工信息,仅保留没有分配部门的员工

SELECT eid,ename,did,dname

FROM t_employee LEFT OUTER JOIN t_department

ON t_employee.dept_id = t_department.did

WHERE t_employee.dept_id IS NULL;

#“从表外键列”是NULL

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

右外连接(RIGHT JOIN)

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

恰与左连接相反,返回右表中的所有行,如果右表中行在左表中没有匹配行,则结果中左表中的列返回空值。

返回右表中在左表没有匹配行的记录

#查询所有部门信息以及该部门员工信息

SELECT did,dname,eid,ename

FROM t_employee RIGHT OUTER JOIN t_department

ON t_department.did = t_employee.dept_id;

#查询部门信息,仅保留没有员工的部门信息

SELECT did,dname,eid,ename

FROM t_employee RIGHT OUTER JOIN t_department

ON t_department.did = t_employee.dept_id

WHERE t_employee.dept_id IS NULL;

#“从表外键列”是NULL

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询
#查询所有员工信息,以及员工的部门信息

SELECT eid,ename,did,dname

FROM t_department RIGHT OUTER JOIN t_employee

ON t_employee.dept_id = t_department.did ;

#查询员工信息,仅保留没有分配部门的员工

SELECT eid,ename,did,dname

FROM t_department RIGHT OUTER JOIN t_employee

ON t_employee.dept_id = t_department.did

WHERE t_employee.dept_id IS NULL;

#“从表外键列”是NULL

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

外连接(FULL JOIN)

mysql不支持FULL JOIN,但是可以用 left join union right join代替

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询
#查询所有部门信息和员工信息

SELECT did,dname,eid,ename

FROM t_department LEFT OUTER JOIN t_employee

ON t_department.did = t_employee.dept_id

UNION

SELECT did,dname,eid,ename

FROM t_department RIGHT OUTER JOIN t_employee

ON t_department.did = t_employee.dept_id;

#查询所有没有员工的部门和没有分配部门的员工

SELECT did,dname,eid,ename

FROM t_department LEFT OUTER JOIN t_employee

ON t_department.did = t_employee.dept_id

WHERE t_employee.dept_id IS NULL

UNION

SELECT did,dname,eid,ename

FROM t_employee LEFT OUTER JOIN t_department

ON t_department.did = t_employee.dept_id

WHERE t_employee.dept_id IS NULL;

两个数据库的表如何关联查询(内连接、外连接、自连接)Mysql中的关联查询

自连接

当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接,外连接等查询

#自连接
#查询员工姓名以及领导姓名,仅显示有领导的员工
SELECT emp.ename,mgr.ename
FROM t_employee AS emp, t_employee AS mgr
WHERE emp.mid = mgr.eid;
#查询员工姓名以及领导姓名,仅显示有领导的员工
SELECT emp.ename,mgr.ename
FROM t_employee AS emp INNER JOIN t_employee AS mgr
ON emp.mid = mgr.eid;
#查询所有员工姓名及其领导姓名
SELECT emp.ename,mgr.ename
FROM t_employee AS emp LEFT JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

喜欢 (0)
炮渣日记
关于作者:
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址