提示：
- 此文章下的code示例皆在cmd下运行，并未借助可视化工具；
- code示例较多，若只想关注具体语句，可借助目录快速跳转
一、Note
1、多表查询概念
-
在一个SELECT语句中查询了两张或两张以上的表就是多表查询，也称作 多表联查 或 连接查询
-
笛卡尔积
-
m元关系R 和 m元关系S的笛卡尔积记为 R*S，结果是一个(n+m)列元组的集合，元组的前n列是关系R的一个元组，后m列是关系S的一个元组；计算时从R的第一个元组开始，依次与S的每一个元组组合
-
笛卡尔积对两个关系R和S进行操作，产生的关系中元组个数为两个关系中元组个数之积
-
笛卡尔积也叫连接查询，没有 on连接条件
select * from emp,temp ;
-
-
等值连接
- 是在笛卡尔积的结果集上通过on连接条件，对结果集再进行筛选操作
select * from a join b on a.a1=b.b1 ;
-
自然连接
- 是在等值连接(以公共属性值相等为条件)的基础上再行投影操作，去掉关系S中的公共属性列（去重），当两个关系没有公共属性时，自然连接就转化成笛卡尔积
- 是两个关系中所有公共属性列进行等值连接的结果
- 在等值连接后的结果集中去除重复的列，即只保留一个an，而不是保留an和bn
select * from a natural join b ;
-
区别
- 自然连接一定是等值连接，但等值连接不一定是自然连接
- 等值连接中，on要求的等值连接的列，不一定是公共属性；而自然连接中，on要求的等值连接的列必须是公共属性；
- 等值连接不把重复的属性除去；而自然连接要把重复的属性除去
-
表的别名
2、连接条件
- 所谓连接条件就是用来避免产生笛卡尔积的条件
- 通常是采用两张表中有关联的两个或多个列来确定
3、内连接
-
定义
-
当查询结果中的所有数据都 满足连接条件时，就称该查询为 内连接（INNER JOIN）
-
SELECT 列名 ,... FROM 表名 [INNER] JOIN 表名 ON 连接条件 [WHERE 筛选条件] [GROUP BY 分组条件 [HAVING 筛选条件]] [ORDER BY 排序条件] ;- 按on后的条件对两个表进行连接，再按where后的条件对连接后的结果进行筛选
-
两种写法
- 非标准SQL：
select * from c,p where c.course_id = p.course_id ; - 标准SQL：
select * from c inner join p on c.course_id = p.course_id ;
- 非标准SQL：
-
-
分类
-
等值连接
-
SELECT e.empno,e.ename,e.deptno, d.deptno,d.dname,d.loc FROM emp e INNER JOIN dept d ON e.deptno = d.deptno ;
-
-
非等值连接
-
SELECT e.empno,e.ename,e.sal, g.grade FROM emp e INNER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal ;
-
-
4、外连接
-
定义
-
当查询结果中除了 满足连接条件 数据外，也包含不满足连接条件的数据时，就称该查询为 外连接（OUTER JOIN）
-
SELECT 列名 ,... FROM 表名 {LEFT|RIGHT|FULL} [OUTER] JOIN 表名 ON 连接条件 [WHERE 筛选条件] [GROUP BY 分组条件 [HAVING 筛选条件]] [ORDER BY 排序条件] ;
-
-
分类
-
左外连接
-
定义
- 查询结果中既包含满足连接条件的数据
- 也包含了【JOIN之前】的表中不满足条件的数据
-
语句
SELECT ... FROM 表名 LEFT [OUTER] JOIN 表名 ON 连接条件 ;
-
-
右外连接
-
定义
- 查询结果中既包含满足连接条件的数据
- 也包含了【JOIN之后】的表中不满足条件的数据
-
语句
SELECT ... FROM 表名 RIGHT [OUTER] JOIN 表名 ON 连接条件 ;
-
-
全外连接
-
定义
- 查询结果中既包含满足连接条件的数据
- 也包含了【JOIN之前】的表中不满足条件的数据
- 也包含了【JOIN之后】的表中不满足条件的数据
- 先列出左边的表中不满足条件的数据，再列出右边的表中不满足条件的数据
-
语句
SELECT ... FROM 表名 FULL [OUTER] JOIN 表名 ON 连接条件 ;
-
-
5、自连接
-
自关联
- 某一张表中存在某个列或某些列引用了本表的主键
- 建表时就完成了自关联
- 如：emp表、s_emp表、t_categories表
-
自连接
- 实际上就是将一张表当多张表来使用（通过为表取不同别名来区分）
SELECT e.empno , e.ename , m.empno , m.ename FROM emp e JOIN emp m ON e.mgr = m.empno ;
二、Code
1、多表查询/笛卡尔乘积/表的列名
-
purge 表示彻底删除该表而不是将该表删除到回收站（recyclebin）
DROP TABLE 表名 [ CASCADE CONSTRAINTS ] [PURGE] ;- 注意删表前首先注意表的主键/唯一键是否被外键引用
-
查找两个表，此查询会先形成笛卡尔积
SELECT * FROM t_majors , t_courses ;
-
从以上的笛卡尔乘积中查找 专业 id 为 1001 的信息
SELECT * FROM t_majors , t_courses WHERE t_majors.id = 1001 ;
-
可以在 FROM 之后的 表名 之后 使用 空格 隔开，指定表的别名
ELECT * FROM t_majors m , t_courses c WHERE m.id = 1001 ;
-
仅列出 专业名称 和 课程名称
SELECT m.name , c.name FROM t_majors m , t_courses c WHERE m.id = 1001 ;
SQL>
SQL> -- 创建两张简单的表 (每张表两个列)
SQL>
SQL>
SQL> -- DROP TABLE 表名 [ CASCADE CONSTRAINTS ] [PURGE] ;
SQL> DROP TABLE t_majors PURGE ;
DROP TABLE t_majors PURGE
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
SQL> DROP TABLE t_majors PURGE CASCADE;
DROP TABLE t_majors PURGE CASCADE
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> -- 需要先删除t_students表
SQL> DROP TABLE t_students PURGE ;
表已删除。
SQL> DROP TABLE t_majors PURGE ;
表已删除。
SQL> CREATE TABLE t_majors ( id NUMBER(5) , name VARCHAR2(20) ) ;
表已创建。
SQL> INSERT INTO t_majors VALUES ( 1001 , '软件工程' );
已创建 1 行。
执行计划
----------------------------------------------------------
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T_MAJORS | | | | |
-------------------------------------------------------------------------------------
SQL> SET AUTOTRACE OFF
SQL> INSERT INTO t_majors VALUES ( 2002 , '土木工程' );
已创建 1 行。
SQL> INSERT INTO t_majors VALUES ( 3003 , '环境工程' );
已创建 1 行。
SQL> commit ;
提交完成。
SQL> SELECT * FROM t_majors ;
ID NAME
---------- --------------------
1001 软件工程
2002 土木工程
3003 环境工程
SQL>
SQL> CREATE TABLE t_courses ( id NUMBER(5) , name VARCHAR2(20) ) ;
表已创建。
SQL> INSERT INTO t_courses VALUES ( 9001 , 'Java' );
已创建 1 行。
SQL> INSERT INTO t_courses VALUES ( 9002 , 'Database' );
已创建 1 行。
SQL> INSERT INTO t_courses VALUES ( 2002 , 'HTML/CSS' );
已创建 1 行。
SQL> INSERT INTO t_courses VALUES ( 9003 , 'JavaScript' );
已创建 1 行。
SQL> commit ;
提交完成。
SQL> SELECT * FROM t_courses ;
ID NAME
---------- --------------------
9001 Java
9002 Database
2002 HTML/CSS
9003 JavaScript
SQL> SELECT * FROM t_majors ;
ID NAME
---------- --------------------
1001 软件工程
2002 土木工程
3003 环境工程
SQL> SELECT * FROM t_majors , t_courses ;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1001 软件工程 9001 Java
1001 软件工程 9002 Database
1001 软件工程 2002 HTML/CSS
1001 软件工程 9003 JavaScript
2002 土木工程 9001 Java
2002 土木工程 9002 Database
2002 土木工程 2002 HTML/CSS
2002 土木工程 9003 JavaScript
3003 环境工程 9001 Java
3003 环境工程 9002 Database
3003 环境工程 2002 HTML/CSS
ID NAME ID NAME
---------- -------------------- ---------- --------------------
3003 环境工程 9003 JavaScript
已选择12行。
SQL> -- 从以上的笛卡尔乘积中查找 专业 id 为 1001 的信息
SQL> SELECT * FROM t_majors , t_courses WHERE id = 1001 ;
SELECT * FROM t_majors , t_courses WHERE id = 1001
*
第 1 行出现错误:
ORA-00918: 未明确定义列
SQL> SELECT * FROM t_majors , t_courses WHERE t_majors.id = 1001 ;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1001 软件工程 9001 Java
1001 软件工程 9002 Database
1001 软件工程 2002 HTML/CSS
1001 软件工程 9003 JavaScript
SQL> -- 以上查询会先形成笛卡尔乘积，再从笛卡尔乘积中挑选 专业id 为 1001 的数据
SQL>
SQL> -- 可以在 FROM 之后的 表名 之后 使用 空格 隔开，指定表的别名
SQL>
SQL> SELECT * FROM t_majors m , t_courses c WHERE m.id = 1001 ;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1001 软件工程 9001 Java
1001 软件工程 9002 Database
1001 软件工程 2002 HTML/CSS
1001 软件工程 9003 JavaScript
SQL> -- 仅列出 专业名称 和 课程名称
SQL> SELECT m.name , c.name FROM t_majors m , t_courses c WHERE m.id = 1001 ;
NAME NAME
-------------------- --------------------
软件工程 Java
软件工程 Database
软件工程 HTML/CSS
软件工程 JavaScript
SQL> spool off
2、多表查询：内连接(等值/非等值)/外连接(左/右/全)
-
查询 emp 表 和 dept 表 ( 因为屏幕大小有限，所以仅仅显示部分列即可 )
SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e , dept d ;
-
先产生笛卡尔乘积，然后从笛卡尔乘积中筛选数据
-
SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e , dept d WHERE e.deptno = d.deptno ; -
以上查询的含义是: 查询每个员工的个人信息 及 其部门信息 ( 不统计没有部门的员工 和 没有员工的部门 )
-
由以上查询可知，如果从两张表中仅挑选满足
emp.deptno = dept.deptno条件的数据，就可以不产生笛卡尔乘积 -
此时就可以将
emp.deptno = dept.deptno当做 【连接条件】 来对待 -
实际上 deptno 可以是 dept 表的主键，emp 表中的 deptno 可以是个外键(它参照dept表的主键)
-
-
使用 标准 SQL 语句 实现 内连接查询
-
SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e INNER JOIN dept d ON e.deptno = d.deptno ; -
此时已经不再产生笛卡尔乘积
-
其中的 INNER 关键字可以省略
-
-
查询 emp 表中 部门号 为 20 的雇员的个人信息 和 部门信息
-
SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.deptno = 20 ;
-
-
查询 emp 表中 部门号 为 20 的雇员的个人信息 和 部门信息 ，并按照 工号进行降序排列
-
SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.deptno = 20 ORDER BY empno DESC ; -- 以下写法与之前的写法有什么区别 (注意不单是写法上的区别) -- 将 e.deptno = 20 写在 ON e.deptno = d.deptno 之后 与 写在单独的 WHERE 子句之后有什么区别? SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno AND e.deptno = 20 ORDER BY empno DESC ; -- 解答：第一种写在where后，是将两个表进行内连接之后的结果集先按e.deptno=20的条件进行筛选，之后在select选择要列出的列并按empno降序排序 -- 第二种写在where前，是在两个表进行内连接时就先按e.deptno=20的条件连接两个表，得到结果集，之后在select选择要列出的列并按empno降序排序
-
-
查询emp表中所有雇员的个人信息和部门信息，如果某些雇员没有部门，也将其列在查询结果中
-
-- 左外连接：在 查询结果中 包含 join 关键字之前的表中不满足连接条件的数据 -- 即两个表进行左外连接后的结果集，包含左侧表中不满足 on连接条件的数，此时对应右侧表的数据为空 SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno ;
-
-
查询emp表中所有雇员的个人信息和部门信息，如果某些部门没有员工，也将其列在查询结果中
-
-- 右外连接：在 查询结果中 包含 join 关键字之后的表中不满足连接条件的数据 -- 即两个表进行右外连接后的结果集，包含右侧表中不满足 on连接条件的数，此时对应左侧表的数据为空 SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno ;
-
-
全连接 ( Oracle 支持 ，MySQL 不支持 )
-
-- 全连接：先列出join左边表不满足结果条件，再列出join右边表不满足条件的结果 SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno ;
-
-
内连接-非等值连接：查 emp 表中每个雇员的工号、姓名、薪水、薪水级别
-
SELECT e.empno , e.ename , e.sal , g.grade FROM emp e JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal ;
-
SQL> -- 查看 emp 表结构
SQL> DESC emp ;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> -- 查看 dept 表结构
SQL> DESC dept ;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> -- 查看 emp 表中的数据
SQL> SET linesize 120 ;
SQL> SELECT * FROM emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 09-12月-82 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 12-1月 -83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
9257 HUA'AN CLERK 7839 23-1月 -85 1300
8526 QIU'XIANG ANALYST 7839 23-1月 -85 1400
已选择16行。
SQL> -- 查看 dept 表中的数据
SQL> SELECT * FROM dept ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL> -- 查询 emp 表 和 dept 表 ( 因为屏幕大小有限，所以仅仅显示部分列即可 )
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e , dept d ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 10 ACCOUNTING NEW YORK
7499 ALLEN 30 10 ACCOUNTING NEW YORK
7521 WARD 30 10 ACCOUNTING NEW YORK
7566 JONES 20 10 ACCOUNTING NEW YORK
7654 MARTIN 30 10 ACCOUNTING NEW YORK
7698 BLAKE 30 10 ACCOUNTING NEW YORK
7782 CLARK 10 10 ACCOUNTING NEW YORK
7788 SCOTT 20 10 ACCOUNTING NEW YORK
7839 KING 10 10 ACCOUNTING NEW YORK
7844 TURNER 30 10 ACCOUNTING NEW YORK
7876 ADAMS 20 10 ACCOUNTING NEW YORK
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7900 JAMES 30 10 ACCOUNTING NEW YORK
7902 FORD 20 10 ACCOUNTING NEW YORK
7934 MILLER 10 10 ACCOUNTING NEW YORK
9257 HUA'AN 10 ACCOUNTING NEW YORK
8526 QIU'XIANG 10 ACCOUNTING NEW YORK
7369 SMITH 20 20 RESEARCH DALLAS
7499 ALLEN 30 20 RESEARCH DALLAS
7521 WARD 30 20 RESEARCH DALLAS
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 20 RESEARCH DALLAS
7698 BLAKE 30 20 RESEARCH DALLAS
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7782 CLARK 10 20 RESEARCH DALLAS
7788 SCOTT 20 20 RESEARCH DALLAS
7839 KING 10 20 RESEARCH DALLAS
7844 TURNER 30 20 RESEARCH DALLAS
7876 ADAMS 20 20 RESEARCH DALLAS
7900 JAMES 30 20 RESEARCH DALLAS
7902 FORD 20 20 RESEARCH DALLAS
7934 MILLER 10 20 RESEARCH DALLAS
9257 HUA'AN 20 RESEARCH DALLAS
8526 QIU'XIANG 20 RESEARCH DALLAS
7369 SMITH 20 30 SALES CHICAGO
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7499 ALLEN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7566 JONES 20 30 SALES CHICAGO
7654 MARTIN 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 30 SALES CHICAGO
7788 SCOTT 20 30 SALES CHICAGO
7839 KING 10 30 SALES CHICAGO
7844 TURNER 30 30 SALES CHICAGO
7876 ADAMS 20 30 SALES CHICAGO
7900 JAMES 30 30 SALES CHICAGO
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7902 FORD 20 30 SALES CHICAGO
7934 MILLER 10 30 SALES CHICAGO
9257 HUA'AN 30 SALES CHICAGO
8526 QIU'XIANG 30 SALES CHICAGO
7369 SMITH 20 40 OPERATIONS BOSTON
7499 ALLEN 30 40 OPERATIONS BOSTON
7521 WARD 30 40 OPERATIONS BOSTON
7566 JONES 20 40 OPERATIONS BOSTON
7654 MARTIN 30 40 OPERATIONS BOSTON
7698 BLAKE 30 40 OPERATIONS BOSTON
7782 CLARK 10 40 OPERATIONS BOSTON
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7788 SCOTT 20 40 OPERATIONS BOSTON
7839 KING 10 40 OPERATIONS BOSTON
7844 TURNER 30 40 OPERATIONS BOSTON
7876 ADAMS 20 40 OPERATIONS BOSTON
7900 JAMES 30 40 OPERATIONS BOSTON
7902 FORD 20 40 OPERATIONS BOSTON
7934 MILLER 10 40 OPERATIONS BOSTON
9257 HUA'AN 40 OPERATIONS BOSTON
8526 QIU'XIANG 40 OPERATIONS BOSTON
已选择64行。
SQL> -- 先产生笛卡尔乘积，然后从笛卡尔乘积中筛选数据
SQL>
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e , dept d
3 WHERE e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 20 RESEARCH DALLAS
7499 ALLEN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7788 SCOTT 20 20 RESEARCH DALLAS
7839 KING 10 10 ACCOUNTING NEW YORK
7844 TURNER 30 30 SALES CHICAGO
7876 ADAMS 20 20 RESEARCH DALLAS
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7900 JAMES 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7934 MILLER 10 10 ACCOUNTING NEW YORK
已选择14行。
SQL> -- 以上查询的含义是: 查询每个员工的个人信息 及 其部门信息 ( 不统计没有部门的员工 和 没有员工的部门 )
SQL>
SQL> -- 由以上查询可知，如果从两张表中仅挑选满足 emp.deptno = dept.deptno 条件的数据，就可以不产生笛卡尔乘积
SQL>
SQL> -- 此时就可以将 emp.deptno = dept.deptno 当做 【连接条件】 来对待
SQL>
SQL> -- 实际上 deptno 可以是 dept 表的主键，emp 表中的 deptno 可以是个外键(它参照dept表的主键)
SQL>
SQL> -- 使用 标准 SQL 语句 实现 内连接查询
SQL>
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e
3 INNER JOIN dept d
4 ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 20 RESEARCH DALLAS
7499 ALLEN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7788 SCOTT 20 20 RESEARCH DALLAS
7839 KING 10 10 ACCOUNTING NEW YORK
7844 TURNER 30 30 SALES CHICAGO
7876 ADAMS 20 20 RESEARCH DALLAS
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7900 JAMES 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7934 MILLER 10 10 ACCOUNTING NEW YORK
已选择14行。
SQL> -- 此时已经不再产生笛卡尔乘积
SQL>
SQL> -- 其中的 INNER 关键字可以省略
SQL>
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e
3 JOIN dept d
4 ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 20 RESEARCH DALLAS
7499 ALLEN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7788 SCOTT 20 20 RESEARCH DALLAS
7839 KING 10 10 ACCOUNTING NEW YORK
7844 TURNER 30 30 SALES CHICAGO
7876 ADAMS 20 20 RESEARCH DALLAS
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7900 JAMES 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7934 MILLER 10 10 ACCOUNTING NEW YORK
已选择14行。
SQL> -- 查询 emp 表中 部门号 为 20 的雇员的个人信息 和 部门信息
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e
3 JOIN dept d
4 ON e.deptno = d.deptno
5 WHERE e.deptno = 20 ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 20 RESEARCH DALLAS
7566 JONES 20 20 RESEARCH DALLAS
7788 SCOTT 20 20 RESEARCH DALLAS
7876 ADAMS 20 20 RESEARCH DALLAS
7902 FORD 20 20 RESEARCH DALLAS
SQL> -- 查询 emp 表中 部门号 为 20 的雇员的个人信息 和 部门信息 ，并按照 工号进行降序排列
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e
3 JOIN dept d
4 ON e.deptno = d.deptno
5 WHERE e.deptno = 20
6 ORDER BY empno DESC ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7902 FORD 20 20 RESEARCH DALLAS
7876 ADAMS 20 20 RESEARCH DALLAS
7788 SCOTT 20 20 RESEARCH DALLAS
7566 JONES 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
SQL>
SQL>
SQL> -- 以下写法与之前的写法有什么区别 (注意不单是写法上的区别)
SQL>
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 F
3 ;
*
第 3 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
SQL> ed
已写入 file afiedt.buf
1 SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e
3 JOIN dept d
4 ON e.deptno = d.deptno AND e.deptno = 20
5* ORDER BY empno DESC
SQL> /
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7902 FORD 20 20 RESEARCH DALLAS
7876 ADAMS 20 20 RESEARCH DALLAS
7788 SCOTT 20 20 RESEARCH DALLAS
7566 JONES 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
SQL> -- 将 e.deptno = 20 写在 ON e.deptno = d.deptno 之后 与 写在单独的 WHERE 子句之后有什么区别?
SQL>
SQL>
SQL> -- 在 查询结果中 包含 join 关键字之前的表中不满足连接条件的数据
SQL>
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e LEFT OUTER JOIN dept d
3 ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7934 MILLER 10 10 ACCOUNTING NEW YORK
7839 KING 10 10 ACCOUNTING NEW YORK
7782 CLARK 10 10 ACCOUNTING NEW YORK
7902 FORD 20 20 RESEARCH DALLAS
7876 ADAMS 20 20 RESEARCH DALLAS
7788 SCOTT 20 20 RESEARCH DALLAS
7566 JONES 20 20 RESEARCH DALLAS
7369 SMITH 20 20 RESEARCH DALLAS
7900 JAMES 30 30 SALES CHICAGO
7844 TURNER 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7654 MARTIN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7499 ALLEN 30 30 SALES CHICAGO
8526 QIU'XIANG
9257 HUA'AN
已选择16行。
SQL> -- 以上查询就是左外连接的结果
SQL> -- 以上查询的含义是:
SQL> -- 查询emp表中所有雇员的个人信息和部门信息，如果某些雇员没有部门，也将其列在查询结果中
SQL>
SQL>
SQL> -- 在 查询结果中 包含 join 关键字之后的表中不满足连接条件的数据
SQL>
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e RIGHT OUTER JOIN dept d
3 ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 20 RESEARCH DALLAS
7499 ALLEN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7788 SCOTT 20 20 RESEARCH DALLAS
7839 KING 10 10 ACCOUNTING NEW YORK
7844 TURNER 30 30 SALES CHICAGO
7876 ADAMS 20 20 RESEARCH DALLAS
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7900 JAMES 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7934 MILLER 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
已选择15行。
SQL>
SQL>
SQL>
SQL> -- 全连接 ( Oracle 支持 ，MySQL 不支持 )
SQL> -- 先列出左边不满足的结果，再列出右边不满足条件的结果
SQL> SELECT e.empno , e.ename , e.deptno , d.deptno , d.dname , d.loc
2 FROM emp e FULL OUTER JOIN dept d
3 ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH 20 20 RESEARCH DALLAS
7499 ALLEN 30 30 SALES CHICAGO
7521 WARD 30 30 SALES CHICAGO
7566 JONES 20 20 RESEARCH DALLAS
7654 MARTIN 30 30 SALES CHICAGO
7698 BLAKE 30 30 SALES CHICAGO
7782 CLARK 10 10 ACCOUNTING NEW YORK
7788 SCOTT 20 20 RESEARCH DALLAS
7839 KING 10 10 ACCOUNTING NEW YORK
7844 TURNER 30 30 SALES CHICAGO
7876 ADAMS 20 20 RESEARCH DALLAS
EMPNO ENAME DEPTNO DEPTNO DNAME LOC
---------- ---------- ---------- ---------- -------------- -------------
7900 JAMES 30 30 SALES CHICAGO
7902 FORD 20 20 RESEARCH DALLAS
7934 MILLER 10 10 ACCOUNTING NEW YORK
9257 HUA'AN
8526 QIU'XIANG
40 OPERATIONS BOSTON
已选择17行。
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- 最后，尝试查询 emp 表中每个雇员的工号、姓名、薪水、薪水级别
SQL>
SQL> DESE salgrade ;
SP2-0734: 未知的命令开头 "DESE salgr..." - 忽略了剩余的行。
SQL> DESC salgrade ;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
SQL> SELECT * FROM salgrade ;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> SELECT e.empno , e.ename , e.sal , g.grade
2 FROM emp e
3 JOIN salgrade g
4 ON e.sal BETWEEN g.losal AND g.hisal ;
EMPNO ENAME SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 1
7876 ADAMS 1100 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
9257 HUA'AN 1300 2
8526 QIU'XIANG 1400 2
7844 TURNER 1500 3
7499 ALLEN 1600 3
7782 CLARK 2450 4
EMPNO ENAME SAL GRADE
---------- ---------- ---------- ----------
7698 BLAKE 2850 4
7566 JONES 2975 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
已选择16行。
SQL> spool off
3、修改format.sal文件设置数字格式
- format 00代表两位数，不足时补0；format 99 代表两位数，不足时不补0
-- linesize
SET linesize 150 ;
-- user_objects
COLUMN object_name FORMAT a20 ;
-- user_cons_columns / user_constraints
COLUMN table_name FORMAT a20 ;
COLUMN column_name FORMAT a20 ;
COLUMN constraint_name FORMAT a30 ;
COLUMN owner FORMAT a10 ;
-- s_emp : first_name / last_name / title / comments
COLUMN first_name FORMAT a15 ;
COLUMN last_name FORMAT a15 ;
COLUMN title FORMAT a15 ;
COLUMN comments FORMAT a10 ;
COLUMN id FORMAT 99 ;
COLUMN manager_id FORMAT 99 ; -- id列为数值类型，设置格式用数字
COLUMN dept_id FORMAT 99 ; -- format 00代表两位数，不足时补0；format 99 代表两位数，不足时不补0
4、多表查询练习：内连接/外连接/查询三张表
-
统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区，不统计部门号为空的雇员,也不统计没有员工的部门
-
SELECT e.empno , e.ename , e.job ,e.sal , d.dname , d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno ;
-
-
统计薪水级别不低于3的雇员信息 ( 列出它们的 工号、姓名、岗位、月薪 、薪水级别 )
-
SELECT e.empno , e.ename , e.job , e.sal , g.grade FROM emp e INNER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal WHERE g.grade >=3 ; -- 或者写为 SELECT e.empno , e.ename , e.job , e.sal , g.grade FROM emp e INNER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal AND g.grade >=3 ;
-
-
统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区，如果某位雇员部门号为空，也将该雇员的信息显示在查询结果中
-
SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno ;
-
-
统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区，如果某个部门没有员工，也将该部门的信息显示在查询结果中
-
SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno ;
-
-
统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区
如果某位雇员部门号为空，也将该雇员的信息显示在查询结果中
如果某个部门没有员工，也将该部门的信息显示在查询结果中-
SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno ;
-
-
统计每个员工的姓( last_name ) 、名 ( first_name ) 、部门名称、地区名称
-
SELECT e.last_name , e.first_name , d.name , r.name FROM s_emp e JOIN s_dept d ON e.dept_id = d.id JOIN s_region r ON d.region_id = r.id ;
-
-
以上SQL建议每天手敲/手抄三遍( 早、上午或下午、晚上 )
SQL> -- 内连接
SQL>
SQL> -- 统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区
SQL> -- 不统计部门号为空的雇员,也不统计没有员工的部门
SQL>
SQL> DESC emp ;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> DESC dept ;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc
2 FROM emp e INNER JOIN dept d ON e.deptno = d.deptno ;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7369 SMITH CLERK 800 RESEARCH DALLAS
7499 ALLEN SALESMAN 1600 SALES CHICAGO
7521 WARD SALESMAN 1250 SALES CHICAGO
7566 JONES MANAGER 2975 RESEARCH DALLAS
7654 MARTIN SALESMAN 1250 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 1500 SALES CHICAGO
7876 ADAMS CLERK 1100 RESEARCH DALLAS
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7900 JAMES CLERK 950 SALES CHICAGO
7902 FORD ANALYST 3000 RESEARCH DALLAS
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
已选择14行。
SQL>
SQL> -- 统计薪水级别不低于3的雇员信息 ( 列出它们的 工号、姓名、岗位、月薪 、薪水级别 )
SQL>
SQL> DESC salgrade ;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
SQL> SELECT e.empno , e.ename , e.job , e.sal , g.grade
2 FROM emp e INNER JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal
3 WHERE g.grade >= 3 ;
EMPNO ENAME JOB SAL GRADE
---------- ---------- --------- ---------- ----------
7844 TURNER SALESMAN 1500 3
7499 ALLEN SALESMAN 1600 3
7782 CLARK MANAGER 2450 4
7698 BLAKE MANAGER 2850 4
7566 JONES MANAGER 2975 4
7788 SCOTT ANALYST 3000 4
7902 FORD ANALYST 3000 4
7839 KING PRESIDENT 5000 5
已选择8行。
SQL> SELECT e.empno , e.ename , e.job , e.sal , g.grade
2 FROM emp e INNER JOIN salgrade g
3 ON e.sal BETWEEN g.losal AND g.hisal AND g.grade > 3 ;
EMPNO ENAME JOB SAL GRADE
---------- ---------- --------- ---------- ----------
7782 CLARK MANAGER 2450 4
7698 BLAKE MANAGER 2850 4
7566 JONES MANAGER 2975 4
7788 SCOTT ANALYST 3000 4
7902 FORD ANALYST 3000 4
7839 KING PRESIDENT 5000 5
已选择6行。
SQL>
SQL> -- 外连接
SQL>
SQL> -- 统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区
SQL> -- 如果某位雇员部门号为空，也将该雇员的信息显示在查询结果中
SQL> SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc
2 FROM emp e
3 LEFT OUTER JOIN dept d
4 ON e.deptno = d.deptno ;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7902 FORD ANALYST 3000 RESEARCH DALLAS
7876 ADAMS CLERK 1100 RESEARCH DALLAS
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7566 JONES MANAGER 2975 RESEARCH DALLAS
7369 SMITH CLERK 800 RESEARCH DALLAS
7900 JAMES CLERK 950 SALES CHICAGO
7844 TURNER SALESMAN 1500 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7654 MARTIN SALESMAN 1250 SALES CHICAGO
7521 WARD SALESMAN 1250 SALES CHICAGO
7499 ALLEN SALESMAN 1600 SALES CHICAGO
8526 QIU'XIANG ANALYST 1400
9257 HUA'AN CLERK 1300
已选择16行。
SQL>
SQL> -- 统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区
SQL> -- 如果某个部门没有员工，也将该部门的信息显示在查询结果中
SQL> SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc
2 FROM emp e
3 RIGHT OUTER JOIN dept d
4 ON e.deptno = d.deptno ;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7369 SMITH CLERK 800 RESEARCH DALLAS
7499 ALLEN SALESMAN 1600 SALES CHICAGO
7521 WARD SALESMAN 1250 SALES CHICAGO
7566 JONES MANAGER 2975 RESEARCH DALLAS
7654 MARTIN SALESMAN 1250 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 1500 SALES CHICAGO
7876 ADAMS CLERK 1100 RESEARCH DALLAS
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7900 JAMES CLERK 950 SALES CHICAGO
7902 FORD ANALYST 3000 RESEARCH DALLAS
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
OPERATIONS BOSTON
已选择15行。
SQL>
SQL>
SQL> -- 统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区
SQL> -- 如果某位雇员部门号为空，也将该雇员的信息显示在查询结果中
SQL> -- 如果某个部门没有员工，也将该部门的信息显示在查询结果中
SQL>
SQL> SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc
2 FROM emp e
3 FULL OUTER JOIN dept d
4 ON e.deptno = d.deptno ;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7369 SMITH CLERK 800 RESEARCH DALLAS
7499 ALLEN SALESMAN 1600 SALES CHICAGO
7521 WARD SALESMAN 1250 SALES CHICAGO
7566 JONES MANAGER 2975 RESEARCH DALLAS
7654 MARTIN SALESMAN 1250 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 1500 SALES CHICAGO
7876 ADAMS CLERK 1100 RESEARCH DALLAS
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7900 JAMES CLERK 950 SALES CHICAGO
7902 FORD ANALYST 3000 RESEARCH DALLAS
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
9257 HUA'AN CLERK 1300
8526 QIU'XIANG ANALYST 1400
OPERATIONS BOSTON
已选择17行。
SQL>
SQL>
SQL> -- 以上SQL建议每天手敲/手抄三遍( 早、上午或下午、晚上 )
SQL> -- 要去想，去思考
SQL>
SQL>
SQL>
SQL> -- 统计每位雇员的 工号、姓名、岗位、月薪、部门名称、部门所在地区
SQL> -- 如果某位雇员部门号为空，也将该雇员的信息显示在查询结果中
SQL> -- 使用右外连接完成
SQL>
SQL> SELECT e.empno , e.ename , e.job , e.sal , d.dname , d.loc
2 FROM dept d RIGHT OUTER JOIN emp e ON e.deptno = d.deptno ;
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7902 FORD ANALYST 3000 RESEARCH DALLAS
7876 ADAMS CLERK 1100 RESEARCH DALLAS
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7566 JONES MANAGER 2975 RESEARCH DALLAS
7369 SMITH CLERK 800 RESEARCH DALLAS
7900 JAMES CLERK 950 SALES CHICAGO
7844 TURNER SALESMAN 1500 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
EMPNO ENAME JOB SAL DNAME LOC
---------- ---------- --------- ---------- -------------- -------------
7654 MARTIN SALESMAN 1250 SALES CHICAGO
7521 WARD SALESMAN 1250 SALES CHICAGO
7499 ALLEN SALESMAN 1600 SALES CHICAGO
8526 QIU'XIANG ANALYST 1400
9257 HUA'AN CLERK 1300
已选择16行。
SQL>
SQL>
SQL>
SQL>
SQL> DESC s_emp ;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(7)
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
USERID VARCHAR2(8)
START_DATE DATE
COMMENTS VARCHAR2(255)
MANAGER_ID NUMBER(7)
TITLE VARCHAR2(25)
DEPT_ID NUMBER(7)
SALARY NUMBER(11,2)
COMMISSION_PCT NUMBER(4,2)
SQL> DESC s_dept ;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
REGION_ID NUMBER(7)
SQL> DESC s_region
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(50)
SQL> -- 统计每个员工的姓( last_name ) 、名 ( first_name ) 、部门名称、地区名称
SQL>
SQL> SELECT e.last_name , e.first_name , d.name , r.name
2 FROM s_emp e JOIN s_dept d ON e.dept_id = d.id
3 JOIN s_region r ON d.region_id = r.id ;
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Velasquez Carmen Administration
North America
Ngao LaDoris Operations
North America
Nagayama Midori Sales
North America
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Quick-To-See Mark Finance
North America
Ropeburn Audry Administration
North America
Urguhart Molly Operations
North America
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Menchu Roberta Operations
South America
Biri Ben Operations
Africa / Middle East
Catchpole Antoinette Operations
Asia
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Havel Marta Operations
Europe
Magee Colin Sales
North America
Giljum Henry Sales
South America
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Sedeghi Yasmin Sales
Africa / Middle East
Nguyen Mai Sales
Asia
Dumas Andre Sales
Europe
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Maduro Elena Operations
North America
Smith George Operations
North America
Nozaki Akira Operations
South America
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Patel Vikram Operations
South America
Newman Chad Operations
Africa / Middle East
3karian Alexander Operations
Africa / Middle East
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Chang Eddie Operations
Asia
Patel Radha Sales
Asia
Dancs Bela Operations
Europe
LAST_NAME FIRST_NAME NAME
------------------------- ------------------------- -------------------------
NAME
--------------------------------------------------
Schwartz Sylvie Operations
Europe
已选择25行。
SQL> column first_name format a20 ;
SQL> column last_name format a20 ;
SQL> column name format a20 ;
SQL> set linesize 120 ;
SQL> SELECT e.last_name , e.first_name , d.name , r.name
2 FROM s_emp e
3 JOIN s_dept d ON e.dept_id = d.id
4 JOIN s_region r ON d.region_id = r.id ;
LAST_NAME FIRST_NAME NAME NAME
-------------------- -------------------- -------------------- --------------------
Velasquez Carmen Administration North America
Ngao LaDoris Operations North America
Nagayama Midori Sales North America
Quick-To-See Mark Finance North America
Ropeburn Audry Administration North America
Urguhart Molly Operations North America
Menchu Roberta Operations South America
Biri Ben Operations Africa / Middle East
Catchpole Antoinette Operations Asia
Havel Marta Operations Europe
Magee Colin Sales North America
LAST_NAME FIRST_NAME NAME NAME
-------------------- -------------------- -------------------- --------------------
Giljum Henry Sales South America
Sedeghi Yasmin Sales Africa / Middle East
Nguyen Mai Sales Asia
Dumas Andre Sales Europe
Maduro Elena Operations North America
Smith George Operations North America
Nozaki Akira Operations South America
Patel Vikram Operations South America
Newman Chad Operations Africa / Middle East
3karian Alexander Operations Africa / Middle East
Chang Eddie Operations Asia
LAST_NAME FIRST_NAME NAME NAME
-------------------- -------------------- -------------------- --------------------
Patel Radha Sales Asia
Dancs Bela Operations Europe
Schwartz Sylvie Operations Europe
已选择25行。
SQL>
5、自关联/自连接
-
笛卡尔积
SELECT id , first_name , last_name , manager_id FROM s_emp ;
-
创建一个无限极分类表( 某个分类下可以有子分类，子分类可以继续有子分类)
-
CREATE TABLE t_categories ( id NUMBER(10) , name VARCHAR2(20) , pid NUMBER(10) , CONSTRAINT t_categories_id_pk PRIMARY KEY ( id ) , CONSTRAINT t_categories_pid_fk FOREIGN KEY ( pid ) REFERENCES t_categories ( id ) )
-
-
查看索引
SELECT index_name , index_type FROM user_indexes WHERE lower(table_name) = 't_categories' ;
-
查看约束
SELECT constraint_name , constraint_type FROM user_constraints WHERE lower(table_name) = 't_categories' ;
-
向 t_categories 表中批量插入数据
-
INSERT ALL INTO t_categories ( id , name ) VALUES ( 1, '家用电器') INTO t_categories ( id , name , pid ) VALUES ( 2 , '手机/运营商/数码' ， null ) INTO t_categories ( id , name , pid ) VALUES ( 3 , '电视' , 1 ) INTO t_categories ( id , name , pid ) VALUES ( 4 , '空调' , 1 ) SELECT * FROM dual ; commit ;
-
-
连接查询 的一个特殊应用: 自连接
-
1、自连接（内连接）：查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名
-
SELECT e.empno , e.ename , m.empno , m.ename FROM emp e INNER JOIN emp m ON e.mgr = m.empno ; -
-- emp表是张自关联的表，我们先将emp表当员工表对待，取别名e； -- 再将emp表当上司表对待，取别名m，用内连接查询数据 SELECT e.empno 员工工号 , e.ename 员工姓名 , m.empno 上司工号, m.ename 上司姓名 FROM emp eINNER JOIN emp m ON e.mgr = m.empno ;
-
-
2、左外连接：查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名，如果某个雇员没有上司，也将其显示在查询结果中
-
SELECT e.empno , e.ename , m.empno , m.ename FROM emp e LEFT OUTER JOIN emp m ON e.mgr = m.empno ;
-
-
3、右外连接：查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名，如果某个上司没有雇员，也将其显示在查询结果中
-
SELECT e.empno 员工工号, e.ename 员工姓名, m.empno 上司工号, m.ename 上司姓名 FROM emp e RIGHT OUTER JOIN emp m ON e.mgr = m.empno ;
-
-
4、全外连接：查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名；如果 某个员工 没有上司也将其列到查询结果中； 如果 某个员工 不是其他任何员工上司(也就是不是领导)也将其列到查询结果中
-
SELECT e.empno 员工工号 , e.ename 员工姓名 , m.empno 上司工号, m.ename 上司姓名 FROM emp e FULL OUTER JOIN emp m ON e.mgr = m.empno ;
-
SQL> -- 查看 emp 表的表结构
SQL>
SQL> DESC emp;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> START format.sql
SQL> -- 查看 emp 表中的所有数据
SQL> SELECT * FROM emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 09-12月-82 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 12-1月 -83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
9257 HUA'AN CLERK 7839 23-1月 -85 1300
8526 QIU'XIANG ANALYST 7839 23-1月 -85 1400
已选择16行。
SQL>
SQL> -- 查看 s_emp 表的表结构
SQL> DESC s_emp ;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
ID NOT NULL NUMBER(7)
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
USERID VARCHAR2(8)
START_DATE DATE
COMMENTS VARCHAR2(255)
MANAGER_ID NUMBER(7)
TITLE VARCHAR2(25)
DEPT_ID NUMBER(7)
SALARY NUMBER(11,2)
COMMISSION_PCT NUMBER(4,2)
SQL> START format.sql
SQL> SELECT * FROM s_emp ;
ID LAST_NAME FIRST_NAME USERID START_DATE COMMENTS MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
--- --------------- --------------- -------- -------------- ---------- ---------- --------------- ------- ---------- --------------
01 Velasquez Carmen cvelasqu 03-3月 -90 President 50 2500
02 Ngao LaDoris lngao 08-3月 -90 01 VP, Operations 41 1450
03 Nagayama Midori mnagayam 17-6月 -91 01 VP, Sales 31 1400
04 Quick-To-See Mark mquickto 07-4月 -90 01 VP, Finance 10 1450
05 Ropeburn Audry aropebur 04-3月 -90 01 VP, Administrat 50 1550
ion
06 Urguhart Molly murguhar 18-6月 -91 02 Warehouse Manag 41 1200
er
07 Menchu Roberta rmenchu 14-5月 -90 02 Warehouse Manag 42 1250
er
08 Biri Ben bbiri 07-4月 -90 02 Warehouse Manag 43 1100
er
09 Catchpole Antoinette acatchpo 09-2月 -92 02 Warehouse Manag 44 1300
er
10 Havel Marta mhavel 27-2月 -91 02 Warehouse Manag 45 1307
er
ID LAST_NAME FIRST_NAME USERID START_DATE COMMENTS MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
--- --------------- --------------- -------- -------------- ---------- ---------- --------------- ------- ---------- --------------
11 Magee Colin cmagee 14-5月 -90 03 Sales Represent 31 1400 10
ative
12 Giljum Henry hgiljum 18-1月 -92 03 Sales Represent 32 1490 12.5
ative
13 Sedeghi Yasmin ysedeghi 18-2月 -91 03 Sales Represent 33 1515 10
ative
14 Nguyen Mai mnguyen 22-1月 -92 03 Sales Represent 34 1525 15
ative
15 Dumas Andre adumas 09-10月-91 03 Sales Represent 35 1450 17.5
ative
16 Maduro Elena emaduro 07-2月 -92 06 Stock Clerk 41 1400
17 Smith George gsmith 08-3月 -90 06 Stock Clerk 41 940
18 Nozaki Akira anozaki 09-2月 -91 07 Stock Clerk 42 1200
19 Patel Vikram vpatel 06-8月 -91 07 Stock Clerk 42 795
20 Newman Chad cnewman 21-7月 -91 08 Stock Clerk 43 750
21 3karian Alexander a3kari 26-5月 -91 08 Stock Clerk 43 850
22 Chang Eddie echang 30-11月-90 09 Stock Clerk 44 800
ID LAST_NAME FIRST_NAME USERID START_DATE COMMENTS MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
--- --------------- --------------- -------- -------------- ---------- ---------- --------------- ------- ---------- --------------
23 Patel Radha rpatel 17-10月-90 09 Stock Clerk 34 795
24 Dancs Bela bdancs 17-3月 -91 10 Stock Clerk 45 860
25 Schwartz Sylvie sschwart 09-5月 -91 10 Stock Clerk 45 1100
已选择25行。
SQL> SELECT empno , ename , mgr FROM emp ;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
EMPNO ENAME MGR
---------- ---------- ----------
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
9257 HUA'AN 7839
8526 QIU'XIANG 7839
已选择16行。
SQL> -- 笛卡尔积
SQL> SELECT id , first_name , last_name , manager_id FROM s_emp ;
ID FIRST_NAME LAST_NAME MANAGER_ID
--- --------------- --------------- ----------
01 Carmen Velasquez
02 LaDoris Ngao 01
03 Midori Nagayama 01
04 Mark Quick-To-See 01
05 Audry Ropeburn 01
06 Molly Urguhart 02
07 Roberta Menchu 02
08 Ben Biri 02
09 Antoinette Catchpole 02
10 Marta Havel 02
11 Colin Magee 03
ID FIRST_NAME LAST_NAME MANAGER_ID
--- --------------- --------------- ----------
12 Henry Giljum 03
13 Yasmin Sedeghi 03
14 Mai Nguyen 03
15 Andre Dumas 03
16 Elena Maduro 06
17 George Smith 06
18 Akira Nozaki 07
19 Vikram Patel 07
20 Chad Newman 08
21 Alexander 3karian 08
22 Eddie Chang 09
ID FIRST_NAME LAST_NAME MANAGER_ID
--- --------------- --------------- ----------
23 Radha Patel 09
24 Bela Dancs 10
25 Sylvie Schwartz 10
已选择25行。
SQL>
SQL>
SQL> -- 创建一个无限极分类表( 某个分类下可以有子分类，子分类可以继续有子分类)
SQL> CREATE TABLE t_categories (
2
SQL> DESC t_categories ;
ERROR:
ORA-04043: 对象 t_categories 不存在
SQL> CREATE TABLE t_categories (
2 id NUMBER(10) ,
3 name VARCHAR2(20) ,
4 pid NUMBER(10) ,
5 PRIM
6 ;
*
第 6 行出现错误:
ORA-00907: 缺失右括号
SQL> ed
已写入 file afiedt.buf
1 CREATE TABLE t_categories (
2 id NUMBER(10) ,
3 name VARCHAR2(20) ,
4 pid NUMBER(10) ,
5 CONSTRAINT t_categories_id_pk PRIMARY KEY ( id ) ,
6 CONSTRAINT t_categories_pid_fk FOREIGN KEY ( pid ) REFERENCES t_categories ( id )
7* )
SQL> /
表已创建。
SQL> DESC t_categories ;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
ID NOT NULL NUMBER(10)
NAME VARCHAR2(20)
PID NUMBER(10)
SQL>
SQL> -- 查看索引
SQL> SELECT index_name , index_type FROM user_indexes WHERE lower(table_name) = 't_categories' ;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
T_CATEGORIES_ID_PK NORMAL
SQL>
SQL> -- 查看约束
SQL> SELECT constraint_name , constraint_type FROM user_constraints WHERE lower(table_name) = 't_categories' ;
CONSTRAINT_NAME C
------------------------------ -
T_CATEGORIES_PID_FK R
T_CATEGORIES_ID_PK P
SQL>
SQL>
SQL> -- 向 t_categories 表中插入数据
SQL>
SQL> INSERT ALL
2 INTO t_categories ( id , name ) VALUES ( 1 , '家用电器' )
3 INTO t_categories ( id , name , pid ) VALUES ( 2 , '手机/运营商/数码' ， null )
4 INTO t_categories ( id , name , pid ) VALUES ( 3 , '电视' , 1 )
5 INTO t_categories ( id , name , pid ) VALUES ( 4 , '空调' , 1 )
6 SELECT * FROM dual ;
已创建4行。
SQL> commit ;
提交完成。
SQL> SELECT * FROM t_categories ;
ID NAME PID
--- -------------------- ----------
01 家用电器
02 手机/运营商/数码
03 电视 1
04 空调 1
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- 查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名
SQL>
SQL> SELECT e.empno , e.ename , m.empno , m.ename
2 FROM emp e
3 INNER JOIN emp m
4 ON e.mgr = m.empno ;
EMPNO ENAME EMPNO ENAME
---------- ---------- ---------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
8526 QIU'XIANG 7839 KING
9257 HUA'AN 7839 KING
EMPNO ENAME EMPNO ENAME
---------- ---------- ---------- ----------
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
已选择15行。
SQL> -- 自连接
SQL> -- emp表是张自关联的表，我们先将emp表当员工表对待，取别名e；再将emp表当上司表对待，取别名m，用内连接查询数据
SQL> set pagesize 25
SQL> SELECT e.empno 员工工号 , e.ename 员工姓名 , m.empno 上司工号, m.ename 上司姓名
2 FROM emp e
3 INNER JOIN emp m
4 ON e.mgr = m.empno ;
员工工号 员工姓名 上司工号 上司姓名
---------- ---------- ---------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
8526 QIU'XIANG 7839 KING
9257 HUA'AN 7839 KING
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
已选择15行。
SQL> -- 以上查询结果中不包含 King ，因为它没有直接上司
SQL>
SQL>
SQL> -- 查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名
SQL> -- 如果某个雇员没有上司，也将其显示在查询结果中
SQL>
SQL> -- 左外连接
SQL> SELECT e.empno 员工工号 , e.ename 员工姓名 , m.empno 上司工号, m.ename 上司姓名
2 FROM emp e LEFT OUTER JOIN emp m ON e.mgr = m.empno ;
员工工号 员工姓名 上司工号 上司姓名
---------- ---------- ---------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
8526 QIU'XIANG 7839 KING
9257 HUA'AN 7839 KING
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
7839 KING
已选择16行。
SQL> -- 右外连接
SQL> SELECT e.empno 员工工号 , e.ename 员工姓名 , m.empno 上司工号, m.ename 上司姓名
2 FROM emp m RIGHT OUTER JOIN emp e ON e.mgr = m.empno ;
员工工号 员工姓名 上司工号 上司姓名
---------- ---------- ---------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
8526 QIU'XIANG 7839 KING
9257 HUA'AN 7839 KING
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
7839 KING
已选择16行。
SQL>
SQL>
SQL>
SQL> -- 有同学会尝试全外连接
SQL> SELECT e.empno 员工工号 , e.ename 员工姓名 , m.empno 上司工号, m.ename 上司姓名
2 FROM emp e FULL OUTER JOIN emp m ON e.mgr = m.empno ;
员工工号 员工姓名 上司工号 上司姓名
---------- ---------- ---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7654 MARTIN
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
8526 QIU'XIANG 7839 KING
9257 HUA'AN 7839 KING
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7369 SMITH 7902 FORD
员工工号 员工姓名 上司工号 上司姓名
---------- ---------- ---------- ----------
7934 MILLER
9257 HUA'AN
8526 QIU'XIANG
7839 KING
已选择26行。
SQL> -- 以上查询的含义是:
SQL> -- 查询 emp 表中 所有雇员的 工号、姓名 及其直接上司的 工号、姓名
SQL> -- 如果 某个员工 没有上司也将其列到查询结果中
SQL> -- 如果 某个员工 不是其他任何员工上司(也就是不是领导)也将其列到查询结果中
SQL>
SQL> -- 以上就是 连接查询 的一个特殊应用: 自连接
SQL>
SQL> spool off
6、多表查询(连接查询)的标准写法&非标准写法
-
MySQL也支持这种非标准写法
-
查询 emp 表中 所有雇员的工号、姓名、部门号、部门名称
-
建议通过 执行计划 来对比以下两个SQL的执行过程
-
-- 内连接（SQL标准写法） SELECT e.empno , e.ename , d.deptno , d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno ; -
-- 内连接（非SQL标准写法） SELECT e.empno , e.ename , d.deptno , d.dname FROM emp e , dept d WHERE e.deptno = d.deptno ;
-
-
查询 emp 表中 所有雇员的工号、姓名、部门号、部门名称，将没有部门的雇员信息也统计在查询结果中
-
-- 左外连接( SQL标准写法 ) SELECT e.empno , e.ename , d.deptno , d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno ; -
-- 左外连接( 非SQL标准写法 ) -- d.deptno(+)的(+)说明,当e.deptno(逗号左侧)包含不满足连接条件的数据时,在部门表d的dept列处加空行 -- 非标准写法里的 左、右 按【逗号】区分，虽然where后的(+)在哪也可区分左右，但=靠不住，因为=的两边是可调换的 SELECT e.empno , e.ename , d.deptno , d.dname FROM emp e , dept d WHERE e.deptno = d.deptno(+) ;
-
-
查询 emp 表中 所有雇员的工号、姓名、部门号、部门名称，将没有雇员的部门信息也统计在查询结果中
-
-- 右外连接( SQL标准写法 ) SELECT e.empno , e.ename , d.deptno , d.dname FROM dept d RIGHT JOIN emp e ON e.deptno = d.deptno ; -
-- 右外连接( 非SQL标准写法 ) -- d.deptno(+)的(+)说明,当e.deptno(逗号右侧)包含不满足连接条件的数据时,在部门表d的dept列处加空行 -- 写法一： SELECT e.empno , e.ename , d.deptno , d.dname FROM dept d , emp e WHERE e.deptno = d.deptno(+); -- 写法二： SELECT e.empno , e.ename , d.deptno , d.dname FROM dept d , emp e WHERE d.deptno(+) = e.deptno ;
-
-
所以就有了以下写法(可能、也许、大概)，这是错误的！！
-
SELECT e.empno , e.ename , d.deptno , d.dname FROM dept d , emp e WHERE d.deptno(+) = e.deptno(+) ; -
记住，不能在 WHERE 子句的 = 前后同时使用 (+)
-
因为，一个谓词只能引用一个外部联接的表
-
什么是 “谓词” ? ? ?
-
SQL>
SQL> -- 关于连接查询的非标准写法
SQL>
SQL> -- 查询 emp 表中 所有雇员的工号、姓名、部门号、部门名称
SQL>
SQL> -- 内连接( SQL标准写法 )
SQL>
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM emp e JOIN dept d ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
已选择14行。
SQL>
SQL> -- 内连接( 非SQL标准写法)
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM emp e , dept d
3 WHERE e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
已选择14行。
SQL>
SQL> -- 建议通过 执行计划 来对比以上两个SQL的执行过程
SQL>
SQL>
SQL>
SQL> -- 查询 emp 表中 所有雇员的工号、姓名、部门号、部门名称，将没有部门的雇员信息也统计在查询结果中
SQL>
SQL> -- 左外连接( SQL标准写法 )
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
8526 QIU'XIANG
9257 HUA'AN
已选择16行。
SQL> -- 左外连接( 非SQL标准写法 )
SQL> -- d.deptno(+)的(+)说明在部门表dept处加空行，当e.deptno包含不满足连接条件的数据时
SQL> -- 非标准写法里的 左、右 按【逗号】区分，虽然where后的(+)在哪也可区分左右，但=靠不住，=的两边可调换
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM emp e , dept d
3 WHERE e.deptno = d.deptno(+) ;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
8526 QIU'XIANG
9257 HUA'AN
已选择16行。
SQL>
SQL> -- 右外连接( SQL标准写法 )
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM dept d RIGHT JOIN emp e ON e.deptno = d.deptno ;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
8526 QIU'XIANG
9257 HUA'AN
已选择16行。
SQL> -- 右外连接( 非SQL标准写法 )
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM dept d , emp e
3 WHERE e.deptno = d.deptno(+);
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
8526 QIU'XIANG
9257 HUA'AN
已选择16行。
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM dept d , emp e
3 WHERE d.deptno(+) = e.deptno ;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7782 CLARK 10 ACCOUNTING
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7788 SCOTT 20 RESEARCH
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES
8526 QIU'XIANG
9257 HUA'AN
已选择16行。
SQL>
SQL>
SQL> -- 所以就有了以下写法(可能、也许、大概)
SQL> SELECT e.empno , e.ename , d.deptno , d.dname
2 FROM dept d , emp e
3 WHERE d.deptno(+) = e.deptno(+) ;
WHERE d.deptno(+) = e.deptno(+)
*
第 3 行出现错误:
ORA-01468: 一个谓词只能引用一个外部联接的表
SQL> -- 记住，不能在 WHERE 子句的 = 前后同时使用 (+)
SQL> -- 为啥子不可以嘞:
SQL> -- 一个谓词只能引用一个外部联接的表
SQL>
SQL> -- 有一个任务等着你: 什么是 "谓词" ? ? ?
SQL>
SQL> spool off
580
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
