SQL基础操作_2_操作多个表

目录

操作多个表

7.3.1 记录集的叠加

7.3.2 组合相关的行

7.3.3 查询两个表共同的行

7.3.4 从一个表中查询另外一个表里没有的值

7.3.5 在一个表中查询与其它表不匹配的记录

7.3.6 笛卡尔积生成数据

7.3.7 基于截取子串的连接

7.3.8 不等值连接

---

操作多个表

注:数据集和表结构见 SQL基础操作_1_检索数据

7.3.1 记录集的叠加

需求：

查询雇员表里部门编号是10的员工名和部门编号以及部门表里的部门名称和部门编号。

解决方法:

这里需要用到unionall关键词，因为是两个结果集的叠加。首先查询出emp表的empno，deptno再union all dept表里的deptname，deptno。

Mysql、Sql server、Oracle:

SELECT ename as ename_dname,deptno
FROM emp
WHERE deptno=10
UNION
SELECT dname as ename_dname,deptno
FROM dept

执行结果：

ename_dname	deptno
ACCOUNTING	10
CLARK	10
KING	10
MILLER	10
OPERATIONS	40
RESEARCH	20
SALES	30

注：这里记录集叠加时主要对应字段类型要一致。比如这里的ename和dname是一致的都是字符串类型，如果类型不一致需要做转换。

7.3.2 组合相关的行

需求：

查询雇员表里部门编号是10的员工名和部门名称。

解决方法:

这里需要用到inner join或则等值连接，因为是对两张表进行关联，按照deptno去emp和dept表里关联得到ename和dname。

Mysql、Sql server、Oracle:

SELECT ename,dname
FROM emp,dept
WHERE emp.deptno= dept.deptno
AND emp.deptno= 10;

或：

SELECT ename,dname
FROM emp
INNER JOIN dept
ON emp.deptno= dept.deptno
WHERE emp.deptno= 10

执行结果:

 

ename	dname
CLARK	ACCOUNTING
KING	ACCOUNTING
MILLER	ACCOUNTING

延展阅读:

这里是emp表里相同的deptno和dept里相同的deptno关联，见下：

SELECT ename,dname,emp.deptno AS emp_deptno,dept.deptno AS dept_deptno
FROM emp
INNER JOIN dept
ON emp.deptno= dept.deptno
WHERE emp.deptno= 10;

执行结果：

ename	dname	emp_deptno	dept_deptno
CLARK	ACCOUNTING	10	10
KING	ACCOUNTING	10	10
MILLER	ACCOUNTING	10	10

7.3.3 查询两个表共同的行

需求：

查询雇员表里部门编号是10的员工编号、员工名称、职位和雇员表的公共行。

解决方法:

这里需要用到intersect关键字，注意这里为了演示，显然返回的还是部门编号为10的员工编号、员工名称、职位。当然部分数据库不支持，我们可以通过关联的方式达到同样的效果。

Sql server、Oracle:

SELECT empno,ename,job FROM emp WHERE deptno=10
INTERSECT
SELECT empno,ename,job FROM emp

Mysql、Sql server、Oracle:

SELECT A.empno,A.ename,A.job FROM
(SELECT empno,ename,job FROM emp WHERE deptno=10)A
JOIN emp B
ON A.empno = B.empno
     AND A.ename = B.ename
     AND A.job = B.job

执行结果：

empno	ename	job
7782	CLARK	MANAGER
7839	KING	PRESIDENT
7934	MILLER	CLERK

7.3.4 从一个表中查询另外一个表里没有的值

需求：

查询部门表雇员表里的部门编号，即未分配员工的部门。

解决方法:

这里需要用到minus关键字，当然部分数据库不支持，我们可以通过not exists关键词解决。

Oracle:

SELECT deptno FROM dept
minus
SELECT deptno FROM emp

Sql server:

SELECT deptno FROM dept
EXCEPT
SELECT deptno FROM emp

Mysql、Sql server、Oracle:

SELECT deptno FROM dept
WHERE deptno NOT IN
(SELECT deptnoFROM emp)

或者：

SELECT dept.deptno FROM dept
LEFT JOIN emp
ON emp.deptno= dept.deptno
WHERE emp.deptno IS NULL

执行结果:

deptno

40

延展阅读：

SELECT dept.deptno,emp.deptno FROM dept
LEFT JOIN emp
ON emp.deptno= dept.deptno
-- WHERE emp.deptno IS NULL

 执行结果:

dept_deptno	emp_deptno
10	10
10	10
10	10
20	20
20	20
20	20
20	20
20	20
30	30
30	30
30	30
30	30
30	30
30	30
40	NULL

注：这里dept表里的deptno40并没有在emp表里关联到，所以更加left join的特性，emp_deptno这列为null，所以我们加个WHERE emp.deptno IS NULL即可取出该条记录。

7.3.5 在一个表中查询与其它表不匹配的记录

需求：

查询没有部门员工的部门的信息。

解决方法:

这里需要用到left join解决思路类似上一章，也可以通过not exists、notin这些关键词解决。

Mysql、Sql server、Oracle:

SELECT dept.* FROM dept
LEFT JOIN emp
ON emp.deptno= dept.deptno
WHERE emp.deptno IS NULL

SELECT dept.* FROM dept
WHERE not exists
(SELECT deptno FROM emp WHERE emp.deptno= dept.deptno)

SELECT dept.* FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp)

执行结果:

deptno	dname	loc
40	OPERATIONS	BOSTON

7.3.6 笛卡尔积生成数据

需求：

查询雇员表里部门编号是10的员工可能会待的所有部门的组合，这里要取得字段有员工编号、部门编号、部门名称。

解决方法:

这里需要用到cross join解决。生成一个A×B的结果集，即如果A表里有3条，B条里有4条则会有3*4=12条记录。

Mysql、Sql server、Oracle:

SELECT empno,deptno,dname FROM
 (SELECT empno FROM emp WHERE deptno=10)A
 CROSS JOIN dept

执行结果：

empno	deptno	dname
7782	10	ACCOUNTING
7782	20	RESEARCH
7782	30	SALES
7782	40	OPERATIONS
7839	10	ACCOUNTING
7839	20	RESEARCH
7839	30	SALES
7839	40	OPERATIONS
7934	10	ACCOUNTING
7934	20	RESEARCH
7934	30	SALES
7934	40	OPERATIONS

7.3.7 基于截取子串的连接

需求：

查询雇员表里部门编号是10的部门编号、部门名称、员工名,注这里需要emp表和tmp_v关联。

解决方法:

这里需要用到对tmp_v里的字段data进行字符串截取后再跟dept表的关联。

这里tmp_v的数据见下：

SELECT * FROM tmp_v;

data

SMITH 20

ALLEN 30

WARD 30

JONES 20

MARTIN 30

BLAKE 30

CLARK 10

SCOTT 20

KING 10

TURNER 30

ADAMS 20

JAMES 30

FORD 20

MILLER 10

 

Mysql、Sql server、Oracle:

SELECT B.deptno,B.dname,A.ename FROM
(SELECTSUBSTRING(data,1,CHARINDEX(' ',data,1)-1) as ename,
SUBSTRING(data,CHARINDEX(' ',data,1)+1,LEN(data)) as deptno
FROM tmp_v
)A
INNER JOIN dept B
ON A.deptno= B.deptno
WHERE A.deptno=10

-- 或:(这里oralce,sql server,mysql里相关字符串函数不尽相同)

SELECT B.deptno,B.dname,SUBSTRING(data,1,CHARINDEX(' ',data,1)-1) as ename
FROM tmp_v A
INNER JOIN dept B
ON SUBSTRING(A.data,CHARINDEX(' ',A.data,1)+1,LEN(A.data))= B.deptno
WHERE B.deptno=10

执行结果：

deptno	dname	ename
10	ACCOUNTING	CLARK
10	ACCOUNTING	KING
10	ACCOUNTING	MILLER

7.3.8 不等值连接

需求：

查询雇员表里部门编号大于20的部门编号、部门名称、员工名,注这里需要emp表和tmp_v关联。

解决方法:

这里需要用到对tmp_v里的字段data进行字符串截取后再跟dept表的关联。

SELECT B.deptno,B.dname,SUBSTRING(data,1,CHARINDEX(' ',data,1)-1) as ename
FROM tmp_v A
INNER JOIN dept B
ON SUBSTRING(A.data,CHARINDEX(' ',A.data,1)+1,LEN(A.data))= B.deptno ANDB.deptno>20

执行结果：

deptno	dname	ename
30	SALES	ALLEN
30	SALES	WARD
30	SALES	MARTIN
30	SALES	BLAKE
30	SALES	TURNER
30	SALES	JAMES