表的復雜查詢(xún) --多表的查詢(xún)
還是用之前的兩張表 emp,dept
--顯示雇員的名字，雇員的工資及雇員所在的部門(mén)
--如果兩張表都有相同名字的字段，則需要用表名來(lái)區分（或者別名）
select ename,sal,loc,emp.deptno from emp,dept where emp.dept no=dept.deptno
注：因為兩張表中都含有deptno，所以需要表名來(lái)區分,同時(shí)相同的名字可以找到相同的聯(lián)系，如emp.deptno=dept.deptno可以找到兩個(gè)表的聯(lián)系
--使用兩張表的別名
select ename,sal,e.deptno from emp e,dept d, where e.deptno=d.deptno
--如何顯示部門(mén)號為10的部門(mén)名，員工名和工資
select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=10 and e.deptno=d.deptno
--顯示雇員的名字，雇員的工資及所在部門(mén)的名字，并按部門(mén)排序
select d.dname，e.sal，e.ename from emp e,dept d where e.deptno=d.deptno order by d.dname
---自連接
--自連接是指在同一張表內的鏈接
--顯示某個(gè)員工的上級領(lǐng)導的姓名，比如顯示ford的上級
select ename from emp where empno=(select mgr from emp where ename='ford')
--顯示公司每個(gè)員工的名字和他的上級的名字
--把emp表看做兩張表，分別是worker和boss
select worker.ename 雇員,boss.ename 老板from emp worker, emp boss where worker.mgr = boss.empno
--子查詢(xún)
子查詢(xún)是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢(xún)
--單行子查詢(xún)
單行子查詢(xún)指只返回一行數據的子查詢(xún)語(yǔ)句
--如何顯示與smith同一部門(mén)的所有員工
select *from emp where deptno=(select deptno from emp where ename='smith')
--多行子查詢(xún)
多行子查詢(xún)指返回多行數據的子查詢(xún)
--查詢(xún)和部門(mén)10的工作相同的雇員的名字，崗位，工資，部門(mén)號,多行子查詢(xún)一般用in
select *from emp where job in (select distinct job from emp where deptno=10)
--在from字句中使用子查詢(xún)
--顯示高于部門(mén)平均工資的員工的名字，薪水，和他們的平均工資,部門(mén)
select * from emp where sal>(select avg(sal),deptno from emp group by deptno)
上面是錯誤的
--正確分析思路
1.首先要知道各個(gè)部門(mén)的平均工資
select avg(sal),deptno from emp group by deptno
注：在這里返回的并不是一行數據
2.將上面的查詢(xún)結果看做是一張新的表，當做一個(gè)臨時(shí)表對待，在和原來(lái)的表做表間鏈接查詢(xún)
select emp.ename,emp.sal,tem.myavg emp.deptno from emp,(select avg(sal),deptno from emp group by deptno) tmp
where emp.deptno=tem.deptno and emp.sal>tem.myavg
注：一定要給臨時(shí)的表格去一個(gè)別名，在這里為tem,只有通過(guò)這個(gè)別名才可以訪(fǎng)問(wèn)到這張表
當我們想要訪(fǎng)問(wèn)表tem中的某個(gè)字段時(shí)，應該為這個(gè)字段家一個(gè)列名，這里為myavg,只有這樣才可以訪(fǎng)問(wèn)到臨時(shí)表中的字段信息
--分頁(yè)查詢(xún)（重要）
--1.顯示第1個(gè)到第4個(gè)入職的雇員
select top 4 *from emp order by hirdate
--top 后面的數字表示要取出幾條記錄
--選出第5到10個(gè)入職的人
select top 6 *from emp where empno not in (select top 4 *from emp order by hirdate) order by hirdate
--選出第11個(gè)到第13個(gè)入職的人
select top 3 *from emp where empno not in (select top 10 *from emp order by hirdate) order by hirdate
--顯示第5到第9個(gè)人的信息（按照薪水的高低）
select top 5 *from emp where empno not in (select top 4 *from emp order by sal desc) order by sal desc
思考:如何刪除一張表中的重復數據
例如
create table cat （
catId int,
catName varchar(40)
）
insert into cat values(1,'bb');
insert into cat values(1,'bb');
insert into cat values(1,'bb');
insert into cat values(1,'bb');
insert into cat values(1,'bb');
insert into cat values(1,'bb');
insert into cat values(2,'aa');
insert into cat values(2,'aa');
insert into cat values(2,'aa');
insert into cat values(2,'aa');
--1.把cat表的記錄distinct后的結果，放入到臨時(shí)表temp3
select distinct * into temp3 from cat
--把cat表的記錄清空
delete from cat
--把temp3表的數據（沒(méi)有重復的記錄），插入到cat表
insert into cat select *from temp3
--刪除 temp3
drop table temp3
--左外連接和右外連接
--思考題：顯示公司每個(gè)員工和他上級的名字
--內連接
select w.ename, b.ename from emp w, emp b where w.mgr=b.empno
思考題：顯示公司每個(gè)員工和他上級的名字,要求沒(méi)有上級的人，名字也要顯示
--左外連接：指左邊的表的記錄全部顯示，如果沒(méi)有匹配的記錄就用null填
--右外連接：指右邊的表的記錄全部顯示，如果沒(méi)有匹配的記錄就用null填。
select w.ename,b.ename from emp w left join emp b on w.mgr=b.empno
--連接的例子
--表stu
id name
1, Jack
2, Tom
3, Kity
4, nono
--表exam
id grade
1, 56
2, 76
11,89
--內連接
select s.id,s.name from stu s exam e where s.is=e.id
或者
select stu.id,exam.id,stu.name,exam.grade from stu inner join exam on stu.id=exam.id
--左連接（顯示join左邊的所有數據，exam只有兩條記錄，所以stu.grade都用null表示）
select stu.id,exam.id,stu.name,exam.grade from stu left join exam on stu.id=exam.id
1 1 Jack 56
2 2 Tom 76
3 null Kity null
4 null nono null
--右連接（與左連接相反，顯示join右邊表的所有數據）
select stu.id,exam.id,stu.name,exam.grade from stu right join exam on stu.id=exam.id
1 1 Jack 56
2 2 Tom 76
null 11 null 89
