連接查詢和子查詢

SQL92的多表連接查詢

一、本節要點
1.笛卡爾集
2.等值連接
3.非等值連接
4.外連接
5.自連接
說明。SQL 92是1992年提出的查詢語法，向上兼容
二、語法，類型及連接類型區分
語法規則

SELECT table1.column, table2 .column
FROM
table1, table2
WHERE table1.column1 = table2 .column2;

特點
1.在WHERE子句中寫入連接條件
2.當多個表中有重名列時，必須在列的名字前加上表名作為前綴
連接的類型:
等值連接- Equijoin
非等值連接- Non- equijoin
外連接- Outer join
1.左外連接
2.右外連接
自連接- Self join
三、笛卡爾集

select * from dept;//4條記錄
select * from emp; ;/14條記錄
select * from dept,emp; ;//4* 14=56條記錄

總結
檢索出的行的數目將是第一個表中的行數乘以第二 個表中的
行數
檢索出的列的數目將是第-個表中的列數加上第二個表中的
列數
應該保證所有聯結都有where子句，不然數據庫返回比想要
的數據多得多的數據
四、等值查詢

注意點
當被連接的多個表中存在同名字段時，須在該字段前加
上"表名"前綴可使用AND操作符增加查詢條件;
使用表別名可以簡化查詢；
使用表名(表別名)前綴可提高查詢效率;
五、非等值查詢
要求:查詢員工的工資等級

select empno,ename,job,sal,grade
from emp e,salgrade s
where e.sal<s.hisal and e sal>s.losal;

select empno,ename,job,sal,grade
from emp e,salgrade s
where e.sal<s.hisal and e.sal>s.losal and e.job='MANAGER';

六、外連接
使用外連接可以看到參與連接的某一方不滿足連接條件的記
錄，而不僅僅是滿足連接條件的數據。
外連接運算符為(+)
外連接分為左外連接和右外連接兩種
左外連接顯示左邊表的全部行

SELECT table.column, table column
FROM
table1, table2
WHERE table1.column = table2.column(+);

右外連接顯示右邊表的全部行

SELECT table.column, table column
FROM
table1, table2
WHERE table1.column(+) = table2.column;

SQL舉例

select d.deptno,e.deptno, dname,ename from dept d,empe
where d.deptno(+ )=e.deptno;
select d.deptno,e.deptno, dname,ename from dept d,empe
where d.deptno(+ )=e.deptno
order by d.deptno desc;

六、自連接
特點:將-個表當兩個表使用
使用舉例:查詢每個員工的工號、姓名、經理姓名

select e1.empno,e1.ename,e1.job,e2.ename
from empe1 ,emp e2
where e1.mgr=e2.empno
order by e1.empno;

SQL99的多表連接查詢

一、前言
SQL 92的語法規則的缺點:
1.語句過濾條件和表連接的條件都放到了where子句中
2.當條件過多時，聯結條件多,過濾條件多時，就容易
造成混淆
SQL 99修正了整個缺點，把聯結條件,過濾條件分開來,
包括以下新的TABLE JOIN的句法結構:
1.交叉連接(Cross join)
2.自然連接(Natural join)
3.使用Using子句建立連接
4.使用On子句建立連接
5.外連接( Outer join )
①左外連接
②右外連接
③全外連接
二、SQL1999規范中規定的連接查詢語法

三、交叉連接
Cross join產生了一個笛卡爾集，其效果等同于在兩個表進行連接時未使用WHERE子句限定連接條件;可以使用where條件從笛卡爾集中選出滿足條件的記錄。
用法舉例

select dept.deptno,dname, ename,job
from dept cross join emp;

等價于

select dept.deptno,dname,ename,job
from dept,emp;

四、自然連接
Natural join基于兩個表中的全部同名列建立連接
1.從兩個表中選出同名列的值均對應相等的所有行
2.如果兩個表中同名列的數據類型不同，則出錯
3.不允許在參照列上使用表名或者別名作為前綴
4.自然連接的結果不保留重復的屬性
舉例:

select empno, ename, sal, deptno, dname
from emp natural join dept
where deptno= 10;

五、Using子句
如果不希望參照被連接表的所有同名列進行等值連接，自然連接將無法滿足要求，可以在連接時使用USING子句來設置用于等值連接的列(參照列)名。
using子句引|用的列在sq任何地方不能使用表名或者別名做前綴
舉例:

select e.ename,e.ename,e.sal,deptno,d. dname
from emp e join dept d
using(deptno)
where deptno=20

六、On子句
自然連接的條件是基于表中所有同名列的等值連接
1.為了設置任意的連接條件或者指定連接的列，需要使
用ON子句
2.連接條件與其它的查詢條件分開書寫
3.使用ON子句使查詢語句更容易理解
舉例:

1----select ename ,dname
from emp join dept on emp.deptno=dept.deptno
where emp.deptno=30;
2--- select empno, ename, sal, emp.deptno, dname
from emp join dept
on (emp.deptno = dept.deptno and job='SAL ESMAN);
3---select *
from dept, emp
where dept.deptno = emp.deptno and job='SAL ESMAN';

七、外連接
左外聯接
兩個表在連接過程中除返回滿足連接條件的行以外，還返回左表中不滿足條件的行，這種連接稱為左外聯接。
右外聯接
兩個表在連接過程中除返回滿足連接條件的行以外，還返回右表中不滿足條件的行，這種連接稱為右外聯接。
滿外聯接
兩個表在連接過程中除返回滿足連接條件的行以外，還返回兩個表中不滿足條件的所有行，這種連接稱為滿外聯接。
內連接:在SQL .99規范中，內連接只返回滿足連接條件的數據。
舉例：

左外連接
select deptno, dname,empno,ename,job
from dept left join emp
using(deptno);
右外連接
select deptno, dname,empno,ename,job
from dept right join emp
using(deptno);
滿外連接
select deptno, dname,empno,ename,job
from dept full join emp
using(deptno);

子查詢

問題引入
如何查得所有比“CL ARK"工資高的員工的信息

select * from emp
where sal>(select sal from emp where
ename='CLARK');

思考:查詢工資高于平均工資的雇員名字和工資。
思考:查詢和SCOTT同一部門且比他工資低的雇員名字和
工資。
語法格式:
select字段列表from table
where表達式operator (select 字段列表from table);
特點
1.子查詢在主查詢前執行- -次
2.主查詢使用子查詢的結果
使用子查詢注意事項
1.在查詢是基于未知值時應考慮使用子查詢
2.子查詢必須包含在括號內
3.建議將子查詢放在比較運算符的右側，以增強可讀性。
4.除非進行Top-N分析，否則不要在子查詢中使用ORDER BY子句。
5.如果子查詢返回單行結果，則為單行子查詢,可以在主查詢中對其使用相應的單行記錄比較運算符.
6.如果子查詢返回多行結果，則為多行子查詢，此時不允許對其使用單行記錄比較運算符.
單行子查詢
單行子查詢只返回一行記錄,對單行子查詢可使用單行記錄比較運算符
<、>、=、>=、<=;、<>
舉例:

select * from emp
where sal > (select sal from emp where empno = 7566);

思考:查詢工資最高的雇員名字和工資。
查詢職務和SCOTT相同，比SCOTT雇傭時間早的雇員信息

SELECT empno, ename, job FROM emp
WHERE job =(SEL ECT job FROM emp WHERE empno=7 788)
AND hiredate < (SEL .ECT hiredate FROM emp WHERE
empno=7788); 

查詢工資比SCOTT高或者雇傭時間比SCOTT早的雇員的編號和名字

select empno,ename, sal,hiredate
from emp
where sal>(select sal from emp where ename='SCOT T') or
hiredate<(select hiredate from emp where
ename='SCOT T')

多行子查詢
多行子查詢返回多行記錄
1.對多行子查詢只能使用多行記錄比較運算符
2. ALL和子查詢返回的所有值比較
3. ANY和子查詢返回的任意一個值比較
4. IN 等于列表中的任何一個
案例
1.查詢工資低于任何一個“CLERK"的工資的雇員信息。
2.查詢工資比所有的“SAL ESMAN”都高的雇員的編號、
名字和工資。
3.查詢部門]20中職務同部門10的雇員-樣的雇員信息。
案例答案

SELECT empno, ename, job,sal
FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')
AND job <> 'CLERK';

SELECT empno, ename,sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job= 'SALESMAN');

SELECT empno, ename, job FROM emp
WHERE job IN (SELECT job FROM emp WHERE deptno=10)
AND deptno =20;

rowid和rownum

1、rowid
① rowid在記錄創建時生成，而且是不變，直接指向硬件上的存儲位置
②用rowid直接訪問是最快的，但也是人力所無法做到的
③只要記錄沒被搬動過, rowid是不變的
2、rownum
①rownum是個偽列,查詢的時候除非特別指定，否則不會顯示。
②其主要的用處是控制查詢返回的行數
③只能使用: <,<=
④當rownum和order by-起使用時，會首先選出符合rownum條件的記錄，然后再進行排序,這會給我們的查詢帶來難度。
3、示例

① Select rowid,rownum,e.* from emp
②Select rowid,rownum,e.* from emp order by sal
③Select rowid,rownum,e.* from emp where rownum<=5

分頁查詢

一、案例-----顯示EMP表中薪水最高的前五個雇員信息

二、案例----分頁按照薪水高低顯示數據，每頁5條數據，顯示第2頁數據

三、模板1

四、模板2