oracle操作03多表連接子查詢

Oracle表操作講師:王虎

創(chuàng)建表createtablestudent(stu_idnumberprimarykey,stu_namevarchar2(20),stu_agenumbernotnull)備注：primarykey為主鍵notnull為非約束createtablestudent2(stu_idnumberprimarykey,stu_namevarchar2(20)notnull,stu_agenumbernotnull)備注：primarykey為主鍵notnull為非約束--向student列中插入數(shù)據(jù)insertintostudentvalues(1,'oop',23);insertintostudentvalues(2,'oralce',13);insertintostudentvalues(3,'java',10);insertintostudentvalues(4,'c',10);insertintostudentvalues(5,'C++',3);向student2列中插入數(shù)據(jù)insertintostudent2values(31,'王二',11);insertintostudent2values(24,'李四',22);insertintostudent2values(25,'張桑拿',33);在列值使用單引號和雙引號'ge''jing','"gejing"'insertintostudentvalues(13,'ge''jing',14);insertintostudentvalues(14,'ge''''jing',21);insertintostudentvalues(15,'"gejing"',22);刪除表delete以下二者是等價的delete不帶條件和truncatetable表名deletefromstudenttruncatetablestudent備注:delete只能刪除表數(shù)據(jù)，但是刪除不了表結(jié)構(gòu)，如果要刪除表結(jié)構(gòu)，則利用drop刪除表結(jié)構(gòu)droptable表名droptablestudent從一個表向另一個表復(fù)制行insertinto要插入的表1(select字段名1，字段名2...from表2where條件)insertintostudent(select*fromstudent2)多表查詢語法結(jié)構(gòu):select字段1，字段2.。from表1，表2where條件selects1.stu_name,s2.stu_agefromstudents1,student2s2wheres1.stu_id=s2.stu_id給一張表添加一個字段語法altertable表名字add(字段名字1，字段名字.......)為學(xué)生表添加字段班級編號-創(chuàng)建學(xué)生表studentcreatetablestudent(stu_idnumber,stu_namevarchar2(10),stu_sexchar(2),gradenumber(4,1))

--創(chuàng)建班級表createtablebanji(bj_idnumber,bj_namevarchar2(20)

)-向?qū)W生表中插入數(shù)據(jù)insertintostudentvalues(1001,'張三','男',87.5);insertintostudentvalues(1002,'王琴','女',98);insertintostudentvalues(1003,'李四','男',90.5);insertintostudentvalues(1004,'趙敏','女',88);insertintostudentvalues(1005,'張華','女',68);insertintostudentvalues(1005,'張小華','女',68);--向班級表中插入數(shù)據(jù)insertintobanjivalues(0212001,'02計應(yīng)');insertintobanjivalues(0212002,'02信管');insertintobanjivalues(0212003,'02通信');insertintobanjivalues(0212004,'02數(shù)學(xué)');給一張表添加一個字段語法altertable表名字add(字段名字1，字段名字......)altertablestudentaddban_jnumber修改表中字段的類型altertable表名字modify(字段名字1，字段名字.....)altertablestudentmodifyban_jnumberaltertablestudentmodifystu_idnumbernotnull刪除表中字段語法altertable表名字dropcolumn(字段名字1，字段名字.....)altertablestudentdropcolumnban_j添加主鍵約束語法altertable表名addconstraint主鍵約束名字primarykey(主鍵字段1，主鍵字段2)altertablestudentaddconstraintpk_sprimarykey(stu_id)外鍵語法altertable主表名字addconstraint外鍵約束名字foreignkey(外鍵字段名)references外鍵表名(外鍵字段)altertablestudentaddconstraintfk_sforeignkey(ban_j)referencesbanji(bj_id)刪除約束刪除約束,先刪除從表，再刪除主表altertable主表名字dropconstraintaltertablestudentdropconstraintpk_sselect*fromemp,dept；

--笛卡爾積(交叉連接，顯示兩表相乘后所得的行數(shù))select*fromemp,deptwhereemp.deptno=dept.deptno;

--內(nèi)連接（兩表按照指定條件匹配后得出的表）內(nèi)連接實際上就是利用where子句對兩張表形成的笛卡爾積進(jìn)行篩選Select*fromempinnerjoindeptonemp.deptno=dept.deptno;內(nèi)連接——等連接外連接我們會注意到兩個事情，tab1和tab2兩個標(biāo)做內(nèi)連接的時候有些記錄沒有辦法顯示出來，原因是另一個表沒有匹配的條件：我們發(fā)出：select*fromempe,deptdwheree.deptno=d.deptno;的時候40部門不會被顯示，原因是40部門沒有員工，所以在員工表中沒有記錄，因此不滿足連接的篩選條件所以顯示不出來。Oracle的外連接語法外連接：a和ｂ進(jìn)行連接，如果要完全顯示a，就在ｂ條件上加一個（＋），也就是說不帶＋的表完全顯示select*fromempe,deptdwheree.deptno(+)=d.deptno;因為我們給e.deptno帶上一個(+),所以d表將會被完全顯示也就是dept表會被完全顯示如果左側(cè)的表完全顯示我們就說是左外連接。比如：Select*fromtab1t,tab2rwheret.aa=r.bb(+);如果右側(cè)的表完全顯示我們就說是右外連接。比如：Select*fromtab1t,tab2rwheret.aa(+)=r.bb;左外連接左外連接（左側(cè)的表完全顯示）select*fromempleftouterjoindeptonemp.deptno=dept.deptno;右外連接右外連接（右側(cè)的表完全顯示）select*fromemprightouterjoindeptonemp.deptno=dept.deptno;

完全外連接完全外連接（表完全顯示）select*fromempfullouterjoindeptonemp.deptno=dept.deptno；

查詢職員SMITH的所有信息

select*fromempfullouterjoindeptonemp.deptno=dept.deptnowhereename='SMITH';

--完全外連接

自連接自連接(一個特殊的內(nèi)連接，自己連接自己的一個副本)比如：SQL>select*fromtab1a,tab1b;就是自己聯(lián)接自己的笛卡爾積我們分析emp表，該表中有一個字段叫作mgr，它的具體含義是該員工領(lǐng)導(dǎo)的empno，如果我們要查找一個員工領(lǐng)導(dǎo)的姓名怎么辦呢？我們想到把emp表形成2個副本，連接后我們使用如下條件，a.mgr=b.empno就可以了，查詢?nèi)缦拢翰樵儐T工信息以及該員工上級信息selecte.empno,e.ename,e.mgr,b.empno,b.enamefromempe,empbwheree.mgr=b.empno;子查詢嵌套子查詢一個查詢?nèi)缦拢翰檎宜泄べY高于公司平均工資的人：select*fromempwheresal>(selectavg(sal)fromemp);查詢?nèi)绾芜M(jìn)行呢？系統(tǒng)找到emp的所有的紀(jì)錄，從中分別拿出每一行來求解where子句，也就是說我們的emp表有14行，自查詢也會執(zhí)行14次。嵌套子查詢SMITH所在部門名稱selectdnamefromdeptwheredeptno=(selectdeptnofromempwhereename='SMITH');//子查詢可以出現(xiàn)在條件工資最低的兩個雇員信息select*from(select*fromemporderbysal)whererownum<3;//子查詢可以出現(xiàn)在表顯示當(dāng)前雇員總?cè)藬?shù)以及雇員表信息。select(selectcount(*)fromemp),emp.*fromemp;//子查詢可以出現(xiàn)在列相關(guān)子查詢我們來看一個新的查詢要求：1、查找所有工資高于自己本部門平均工資的人。既然查詢是逐行來求解where子句，我們是否可以讓where子句求解當(dāng)前行員工所在部門的平均工資呢，也就是說我們要在自查詢中獲得主查詢當(dāng)前行的值：具體如法如下：

用主表的別名.列名代表主查詢當(dāng)前行得值我們發(fā)出查詢?nèi)缦拢?/p>

select*fromempewheresal>(selectavg(sal)fromempwheredeptno=e.deptno);注意：e.deptno代表的就是主查詢當(dāng)前行的deptno的值相關(guān)子查詢查找每個部門工資最高的人的詳細(xì)資料思路：得到所有的員工，進(jìn)行篩選，每拿到一個員工，判斷該員工的工資是否是他們部門的最高工資select*fromempewheresal=(selectmax(sal)fromempwheredeptno=e.deptno);selectmax(sal)fromempwheredeptno=e.deptno就是求解當(dāng)前員工所在部門的最高工資相關(guān)子查詢查找每個部門工資最高的人的詳細(xì)資料Selectmax(sal)fromempgroupbydeptno;--分組select*fromempwheresalin(Selectmax(sal)fromempgroupbydeptno);相關(guān)聯(lián)子查詢43、顯示每個部門的信息和人員數(shù)量思路：查詢獲得所有的部門信息，沒獲得一個部門，我們就查詢該部門的人數(shù)，保存為一個新的列selectd.*,(selectcount(*)fromempwheredeptno=d.deptno)totfromdeptd;相關(guān)聯(lián)子查詢5----了解顯示員工信息以及該員工工資