MySQL48--表连接

2022-07-16 09:22:37

连接

连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的重要标志。当检索数据时，通过连接操作可以管理存放在多张数据表中的记录信息；
MySQL支持多表查询、多表删除及多表更新；
MySQL连接可以分为：
内连接；
外连接；
自连接；

使用连接

连接的语法结构是：

TABLE_reference
 { [ INNER | CROSS ] JOIN | { LEFT | RIGHT } [ OUTER ] JOIN }
 TABLE_reference
 ON cONditiONal_expr

TABLE_reference语法结构是：

tbl_name  [ [ AS ] aliAS ] | TABLE_subquery [ AS ] aliAS

cONditiONal_expr语法结构是：
使用ON关键字来设定连接条件，也可以使用WHERE来代替；
通常使用ON关键字来设定连接条件；使用WHERE关键字进行结果集记录的过滤；

连接类型

INNER JOIN

INNER JOIN称为内连接，返回数据表中符合连接条件的记录；

-- 查询emp表中,员工编号(empno),姓名(ename),职位(job)以及dept表中,部门名称(dname),部门地址(loc)
SELECT e.empno,e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
-- 说明:多表连接查询的关联条件就是查看2张表中是否有相同的列

-- 查询emp表中,员工姓名不包含字母M的,员工编号(empno),姓名(ename),职位(job)以及dept表中,部门名称(dname),部门地址(loc),根据员工编号降序排列
SELECT e.empno,e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno AND NOT e.ename LIKE '%M%'
ORDER BY e.empno DESC;

-- 查询部门地址(loc)是CHENGDU的,员工的编号(empno),姓名(ename),职位(job),工资(sal)
SELECT e.empno,e.ename,e.job,e.sal
FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.loc='CHENGDU';

-- 查询emp表中,员工姓名不包含字母K并且部门编号是10号或者20号部门的,员工的编号(empno),姓名(ename),职位(job),部门编号(deptno),以及dept表中部门名称(dname),部门地址(loc),根据员工姓名升序排列
SELECT e.empno,e.ename,e.job,e.deptno,d.dname,d.loc
FROM dept d,emp e
WHERE e.deptno=d.deptno AND NOT e.ename LIKE '%K%' AND e.deptno in(10,20)
ORDER BY e.ename ASC;

-- 查询emp表中,员工编号(empno),姓名(ename),工资(sal),以及salgrade表中工资等级(grade),该等级下的最低工资(losal),该等级下的最高工资(hISal) ----特殊
SELECT e.empno,e.ename,e.sal,s.grade,s.losal,s.hISal
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hISal;

-- 查询emp表中,员工编号(empno),员工姓名(ename),职位(job),工资(sal),上级领导编号(mgr),上级领导姓名(ename) ----特殊
SELECT e.empno,e.ename,e.job,e.sal,e.mgr,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno;

LEFT [OUTER] JOIN

LEFT [OUTER] JOIN称为左外连接；
左外连接的结果集包括 LEFT OUTER 子句中指定的左表中所有记录（而不仅仅是符合连接的记录）和右表中符合连接条件的记录；
如果左表的某条记录在右表中没有与之相匹配的记录，则右表的相关字段显示空值(NULL)；

-- 查询emp表中全部员工的编号,姓名,职位,工资以及该员工的部门名称(dname),部门地址(loc)
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e LEFT JOIN dept d
ON e.deptno=d.deptno;

-- 查询dept表中所有部门的全部信息,以及该部门下,员工的编号(empno),姓名(ename),职位(job),根据员工编号降序排列
SELECT d.*,e.empno,e.ename,e.job
FROM dept d LEFT JOIN emp e
ON e.deptno=d.deptno
ORDER BY e.empno DESC;

-- 查询emp表中,员工姓名不包含字母K的,所有员工的编号,姓名,职位,以及该员工的部门名称(dname),部门地址(loc),根据员工编号升序排列
SELECT e.empno,e.ename,e.job,d.dname,d.loc
FROM emp e LEFT JOIN dept d
ON e.deptno=d.deptno
WHERE NOT e.ename LIKE '%K%'
ORDER BY e.empno ASC;

RIGHT [OUTER] JOIN

RIGHT [OUTER] JOIN称为右外连接，右外连接是左外连接的反向联接；
右外连接的结果集包括 RIGHT OUTER 子句中指定的右表中所有记录（而不仅仅是符合连接的记录）和左表中符合连接条件的记录；
如果右表的某条记录在左表中没有与之相匹配的记录，则左表的相关字段显示空值(NULL)；

-- 查询emp表中全部员工的编号,姓名,职位,工资以及该员工的部门名称(dname),部门地址(loc)
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM dept d RIGHT JOIN emp e
ON e.deptno=d.deptno;

连接时可能产生的错误

ERROR 1052 (23000): Column 'type_id' in ON clause IS ambiguous 
错误原因：连接时因多张数据表存在相同名称的字段而导致的错误；

码农公寓

连接