本文介绍了数据库查询的基础，如简单查询和多表连接。重点讲解了等值连接、笛卡尔积、左外连接、右外连接和全外连接的概念及SQL实现。还提到了非等值连接和using子句的使用，并通过实例展示了如何根据业务需求选择合适的表作为左表。此外，讨论了自连接的应用场景。
多表连接应用：单一表中无法得到我想查询的数据结果，必须用到多个表进行关联
简单查询解析方法：
例如我执行 select empno,deptno,ename ,sal from emp 这个语句
映射到我系统中解析方式就是，先横向选取column值：empno,deptno,ename,sal
再通过这些列进行全表扫描也就是纵向的投影
多表连接:
优势：减少笛卡尔积，节省空间，灵活查询结果
劣势：SQL结构复杂可读性差，消耗更多的内存和CPU资源
笛卡尔积和等值连接举个例子：
等值连接，典型的内连接
SQL99写法：
SCOTT@ prod>select e.ename,d.loc from emp e inner join dept d on e.deptno=d.deptno;
Oracle写法：
SCOTT@ prod>select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno;
笛卡尔积
TEST@ prod>select * from emp, dept where dept.deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ---------- ---------- ------------------------------------------ ---------------------------------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 30 SALES CHICAGO
已选择14行。
TEST@ prod>select * from emp, dept where where dept.deptno=emp.deptno and dept.deptno=30;
select * from emp, dept where where dept.deptno=emp.deptno and dept.deptno=30
*
第 1 行出现错误:
ORA-00936: 缺失表达式
进行了ORACLE表连接
TEST@ prod>select * from emp, dept where dept.deptno=emp.deptno and dept.deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ---------- ---------- ------------------------------------------ ---------------------------------------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
已选择6行。
TEST@ prod>非等值连接
非等值连接：（连接条件没有“=”号）
SQL99写法：
SCOTT@ prod>select empno,ename,sal,grade,losal,hisal from emp join salgrade on sal between losal and hisal;
Oracle写法：
SCOTT@ prod>select empno,ename,sal,grade,losal,hisal from emp,salgrade where sal between losal and hisal;
如果连接字段相同，可以使用using字句简化书写,using里也可以多列
使用using关键字注意事项
1、如果select的结果列表项中包含了using关键字所指明的那个关键字，那么，不要指明该关键字属于哪个表。
2、using中可以指定多个列名。
3、on和using关键字是互斥的，也就是说不能同时出现。
TEST@ prod>select * from emp e join dept d using(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ------------------------------------------ ---------------------------------------
20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 RESEARCH DALLAS
20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 SALES CHICAGO
30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 SALES CHICAGO
20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 SALES CHICAGO
10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 RESEARCH DALLAS
10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 SALES CHICAGO
20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 SALES CHICAGO
20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 ACCOUNTING NEW YORK
已选择19行。
TEST@ prod>select * from emp e,dept d where d.deptno=e.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ---------- ---------- ------------------------------------------ ---------------------------------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
已选择19行。
外连接（包括左外连接，右外连接，全外连接）
1）左外连接语法
SQL99语法:
SCOTT@ prod>select * from emp e left outer join dept d on e.deptno=d.deptno;
Oracle语法:
SCOTT@ prod>select * from emp e,dept d where e.deptno=d.deptno(+);
如何确定左表和右表
SQL99写法：通过from后面表的先后顺序确定，第一个表为左表
SCOTT@ prod>select e.ename,d.loc from emp e left join dept d on e.deptno=d.deptno;
from后第一个表是emp表，为左表，“=”左右位置无所谓
Oracle写法：通过where 后面的“=”的位置确定，“=”号左边的为左表
SCOTT@ prod>select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno(+);
“=”左边是emp表，为左表，from后面表位置无所谓
2、左连是左表为主
①左连是以左表为驱动，每行都参与匹配右表的行，匹配上就连成一行，如果匹配不上，左表行也不缺失该连接行，这时右表内容填空就是了。
②左连后，左表的行是不缺失的，即左连后的结果集的行数>=左表行数，存在>的可能是因为左表的一行可能匹配了右表的多行。
③也可以左表、右表都是同一个表，即“自左连”。
- 到底哪个表当左表好
无一定之规，根据业务需求来决定。
两表之间一般以主外键确定一对多关系，外键表是明细表，比如emp和dept的关系，以deptno确定父子关系，emp是外键表
你要查每个员工的工作地点，这时以外键表（emp明细表）做左表理所当然
SCOTT@ prod>select e.ename,d.loc from emp e left outer join dept d on e.deptno=d.deptno;
你要查每个部门有多少员工，要求根据每个部门号做统计（40号部门没有员工也统计），这时以主键表(dept)做左表更合理
SCOTT@ prod>select d.deptno,count(e.ename) from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno;
右外连接
SQL99语法:
SCOTT@ prod>select * from emp e right join dept d on e.deptno=d.deptno;
Oracle语法:
SCOTT@ prod> select * from emp e,dept d where e.deptno(+)=d.deptno;
3）全外连接
SQL99语法:
SCOTT@ prod> select * from emp e full join dept d on e.deptno=d.deptno;
Oracle语法:（无，等同于union连接）
SCOTT@ prod>
select * from emp e,dept d where e.deptno=d.deptno(+)
Union
select * from emp e,dept d where e.deptno(+)=d.deptno;
自连接
SQL99语法：
SCOTT@ prod>select e1.empno,e2.mgr from emp e1 cross join emp e2;
Oracle语法:
SCOTT@ prod> select e1.empno,e2.mgr from emp e1,emp e2;
必须使用别名区别不同的表
Jrojyun
2021.03.23
扫一扫
3175
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
