目录
操作多个表
注:数据集和表结构见 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 |