### 9.1、SQL92语法
格式sleect 列 from 表名1[别名], 表名2[别名] where 条件 [group by [having]] [order by]
连接查询:也可以叫跨表查询,需要关联多个表进行查询
l 显示每个员工信息,并显示所属的部门名称
```
select ename, dname from emp, dept;
```
```
SQL> select ename, dname from emp, dept;
ENAME DNAME
\---------- --------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
SCOTT ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
ADAMS ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
SCOTT RESEARCH
KING RESEARCH
TURNER RESEARCH
ADAMS RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
BLAKE SALES
CLARK SALES
SCOTT SALES
KING SALES
TURNER SALES
ADAMS SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
SCOTT OPERATIONS
KING OPERATIONS
TURNER OPERATIONS
ADAMS OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
MILLER OPERATIONS
已选择56行。
```
以上输出,不复合预期结果,输出了56条数据,其实就是两个表记录的成绩,这种情况我们称为:“笛卡儿乘积”,出现错误的原因是:没有指定连接条件
>[danger]笛卡儿乘积:所有数据的乘积,这种情况是严重的浪费资源,怎么解决呢?
在语句后面添加where 条件数量至少是n-1而且必须使用多个表之间的关联列
指定连接条件
```
select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;
也可以使用别名
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
```
![](https://img.kancloud.cn/dc/54/dc545fc18567f91b487154faa171010a_642x355.png)
以上结果输出正确,因为加入了正确的连接条件
以上查询也称为 “内连接”,只查询相等的数据(连接条件相等的数据)
l 取得员工和所属的领导的姓名
```
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
```
```
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 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月\-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
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
已选择14行。
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 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月\-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
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
已选择14行。
SQL> select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
ENAME ENAME
\---------- ----------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
已选择13行。
```
以上称为“自连接”,只有一张表连接,具体的查询方法,把一张表看作两张表即可,如以上示例:第一个表emp e代表了员工表,emp m代表了领导表,相当于员工表和部门表一样