注：数据来源oracle默认用户Scott中的表
#测试数据
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
一、表连接
表达式：
SELECTtable1.column,table2.column
FROMtable1,table2
WHEREtable1.column1=table2.column2
- where中写连接条件
- 如果不同表中存在相同列名，需要在列名前加上表名作前缀
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK 10 10 ACCOUNTING
KING 10 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
JONES 20 20 RESEARCH
FORD 20 20 RESEARCH
使用别名
上述等连接的SQL1999表达式等价于
*select emp.ename,emp.deptno,dept.deptno,dept.dname from emp inner join dept *
on ( emp.deptno=dept.deptno);
还等价于:
select emp.ename,deptno,dept.dname from emp inner join dept using (deptno);
使用using时，前面列名相同的不再进行区分
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK 10 10 ACCOUNTING
KING 10 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
JONES 20 20 RESEARCH
FORD 20 20 RESEARCH
ADAMS 20 20 RESEARCH
SMITH 20 20 RESEARCH
二、笛卡尔积（交叉连接）
笛卡尔积的出现条件
- 连接条件遗漏、不正确
- 表中所有行互相连接
- 避免笛卡尔积使用where条件
#emp共14行数据，dept共4行数据，笛卡尔积出现14*4=56行数据
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH 20 10 ACCOUNTING
ALLEN 30 10 ACCOUNTING
WARD 30 10 ACCOUNTING
...........
56 rows selected.
三、自然连接NATURAL JOIN
自然连接会以两表中具有相同名字的列为条件创建等值连接
- 列名相同而类型不同会报错（可以隐式转换也可以执行成功）
- 多个列名符合都会作为条件
select emp.ename,deptno,dept.dname from emp natural join dept;
ENAME DEPTNO DNAME
---------- ---------- --------------
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 10 ACCOUNTING
JONES 20 RESEARCH
FORD 20 RESEARCH
......
四、非等连接
***等连接=*，如emp.deptno = dept.deptno
非等连接：**>,>=,<,<=,!>,!<,<>**
#查询员工的工资以及对应的工资等级
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.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
7844 TURNER 1500 3
7499 ALLEN 1600 3
7782 CLARK 2450 4
7698 BLAKE 2850 4
7566 JONES 2975 4
7788 SCOTT 3000 4
7902 FORD 3000 4
7839 KING 5000 5
14 rows selected.
上述查询语句等价于：
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
五、外连接
INNER与OUTER外连接
- 在SQL1999中，两表连接只返回匹配的行，成为内连接；
- 两表连接，除了返回匹配的行，还返回左/右表中不满足条件的行，称为左/右外连接；
- 两表连接，除了返回匹配的行，还返回两表中所有不满足的行，称为全外连接。
外连接的符号为+
右外连接：
SELECTtable1.column,table2.column
FROMtable1,table2
WHEREtable1.column1（+）=table2.column2
左外连接：
SELECTtable1.column,table2.column
FROMtable1,table2
WHEREtable1.column1=table2.column2（+）
左外连接
#在emp中准备一条数据
insert into emp(empno,ename) values(8000,'DEBUG');
#左外连接
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d where e.deptno = d.deptno(+);
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK 10 10 ACCOUNTING
KING 10 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
SMITH 20 20 RESEARCH
JONES 20 20 RESEARCH
SCOTT 20 20 RESEARCH
ADAMS 20 20 RESEARCH
FORD 20 20 RESEARCH
ALLEN 30 30 SALES
WARD 30 30 SALES
MARTIN 30 30 SALES
BLAKE 30 30 SALES
TURNER 30 30 SALES
JAMES 30 30 SALES
DEBUG
#注意最后一行没有部门号
15 rows selected.
其SQL1999语法等价于：
select e.ename,e.deptno,d.deptno,d.dname from emp e left outer join dept d on (e.deptno = d.deptno);
右外连接
#右外连接
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d where e.deptno(+) = d.deptno;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK 10 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
KING 10 10 ACCOUNTING
JONES 20 20 RESEARCH
SMITH 20 20 RESEARCH
SCOTT 20 20 RESEARCH
FORD 20 20 RESEARCH
ADAMS 20 20 RESEARCH
WARD 30 30 SALES
TURNER 30 30 SALES
ALLEN 30 30 SALES
JAMES 30 30 SALES
MARTIN 30 30 SALES
BLAKE 30 30 SALES
40 OPERATIONS
#注意最后一行该部门没有员工
上述右外连接SQL1999等价于：
select e.ename,e.deptno,d.deptno,d.dname from emp e right outer join dept d on (e.deptno = d.deptno);
全外连接
#全外连接SQL1999语法
select e.ename,e.deptno,d.deptno,d.dname from emp e full outer join dept d on (e.deptno = d.deptno);
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH 20 20 RESEARCH
ALLEN 30 30 SALES
WARD 30 30 SALES
JONES 20 20 RESEARCH
MARTIN 30 30 SALES
BLAKE 30 30 SALES
CLARK 10 10 ACCOUNTING
SCOTT 20 20 RESEARCH
KING 10 10 ACCOUNTING
TURNER 30 30 SALES
ADAMS 20 20 RESEARCH
JAMES 30 30 SALES
FORD 20 20 RESEARCH
MILLER 10 10 ACCOUNTING
DEBUG
40 OPERATIONS
16 rows selected.
六、自连接
在emp表中，mgr为员工的领导工号，所以可以对emp自连接来查询每个员工的领导及其工号
自连接中必须使用别名，其select查询必须要前缀
自连接可看做两张表的等值连接，只不过这两张表实质为一张表。
select e.empno,e.ename,e.mgr,m.empno,m.ename from emp e ,emp m where e.mgr=m.empno;
EMPNO ENAME MGR EMPNO ENAME
---------- ---------- ---------- ---------- ----------
7902 FORD 7566 7566 JONES
7788 SCOTT 7566 7566 JONES
7844 TURNER 7698 7698 BLAKE
7499 ALLEN 7698 7698 BLAKE
7521 WARD 7698 7698 BLAKE
7900 JAMES 7698 7698 BLAKE
7654 MARTIN 7698 7698 BLAKE
7934 MILLER 7782 7782 CLARK
7876 ADAMS 7788 7788 SCOTT
7698 BLAKE 7839 7839 KING
7566 JONES 7839 7839 KING
7782 CLARK 7839 7839 KING
7369 SMITH 7902 7902 FORD
13 rows selected.
七、多表关联
多表关联实质通两张表做连接一样，注意区分条件而已
例：
select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.sal between s.losal and s.hisal and e.deptno=d.deptno;
ENAME DNAME GRADE
---------- -------------- ----------
KING ACCOUNTING 5
FORD RESEARCH 4
SCOTT RESEARCH 4
JONES RESEARCH 4
BLAKE SALES 4
CLARK ACCOUNTING 4
ALLEN SALES 3
TURNER SALES 3
MILLER ACCOUNTING 2
MARTIN SALES 2
WARD SALES 2
ADAMS RESEARCH 1
JAMES SALES 1
SMITH RESEARCH 1
14 rows selected.
select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where (e.sal between s.losal and s.hisal) and (e.deptno=d.deptno);
本文转载自: https://blog.csdn.net/qq_41896822/article/details/127908243
版权归原作者 十三妹_ 所有， 如有侵权，请联系我们删除。
版权归原作者 十三妹_ 所有， 如有侵权，请联系我们删除。