數(shù)據(jù)庫教學(xué)第6天課件

6PLSQL.DEV1plSqlDevcommit2commit動(dòng)作3122查詢?nèi)〉玫臄?shù)這些的數(shù)據(jù)的數(shù)據(jù)類型及字段先后順三、關(guān)于結(jié)果集的1SQL2從from數(shù)據(jù)表開始，包括 ,groupby，having,orderby查詢的selectselect學(xué)號(hào) 2表達(dá)式21select學(xué)號(hào)，，，from學(xué)生表where班級(jí)號(hào)=’QH10001’ and性1=1,1＝0的問題1sqljava恒等號(hào),21=1(表示)→1==1(java),true34sql語句沒有true1=1,0＝0true2=2,3=3等來表示1=0falseselect*fromselect*fromstudentwhere1=1--這一行與下一行代碼結(jié)果集相select*fromselect*fromstudentwhere1=0--沒有查詢數(shù)據(jù)，只有數(shù)據(jù)1select12n,1,2，統(tǒng)計(jì)nfrom12nwheregroupby having分組條件orderby--關(guān)于分組字段，group--關(guān)于分組字段，groupbyselect'QH10001班名稱,count(*select'QH10002班名稱,count(*select'QH10003班名稱,count(*fromstudentfromstudentfromstudent--采用分組方法 求上面SQL語selectclass_ID班名稱,count(*)fromstudentgroupbyclass_idorderbyclass_idascselect*from--updatestudentsetclass_id='QH10004'where--下面是錯(cuò)誤的，因?yàn)閚ameselectclass_ID班名稱,name,count(*)fromstudentgroupbyclass_idorderbyclass_idasc--2selectclass_ID班名稱, ,count(*)班人數(shù)fromstudentgroupby orderbyclass_id21groupby中的列表字段項(xiàng)212where條件whereselect*select*fromstudentwhereage>=18selectclass_ID班名稱,count(*)班人數(shù)fromwhereage>=18groupbyclass_idhavingcount(*)<6orderbyclass_idascselectclass_ID班名稱,count(*)班人數(shù),avg(age)fromwhereage>=18groupbyclass_idhavingcount(*)<6andorderbyclass_id3范式，3NF12233范式createtable(IDcreatetable(IDvarchar(20)primarykey,varchar(20)check(in('學(xué)生','講師','咨詢師',')createtable(intidentity(10000,1)createtable(intidentity(10000,1)primarychar(7)notnullforeignkeyreferences班級(jí)表(班級(jí)號(hào)),nvarchar(10)notnull,nchar(1)nchar(1)check(tinyintcheck(住址char(8)>=0like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-datetimenotchar(1)notnullcheck(學(xué)期intnotnullforeignkeyreferences員工表(員工號(hào))createtable(intidentity(10000,1)createtable(intidentity(10000,1)primary班級(jí)號(hào)char(7)not 間datetime,--這個(gè)字段與非主鍵字段班級(jí)號(hào)有依賴關(guān)系，就不是第3范 datetime,nvarchar(10)notnchar(1)check(tinyintcheck(住址>=0)312select主表.*,子表.*fromleftouterjoinon主表.主鍵=子表.3、leftouterouter:外面join:連4leftouter主表在左邊，稱為左外連5,rightouter主表在右邊，稱為右外連selectt2.*,t1.*fromselectt2.*,t1.*fromstudentt1leftouterjoinstudyt2selectt2.*,t1.*selectt2.*,t1.*fromstudentt1leftouterjoinstudyt2t1.stud_id=t2.stud_idwheret2.stud_idisnullorderby--select--selectt1.*,t2.*fromclass_regt1leftouterjoinstudentt2onselectt1.*,t2.*fromclass_regt1leftouterjoinstudentt2onwheret2.stud_idis--selectt1.*,t2.*--selectt1.*,t2.*fromstudentt2rightouterjoinclass_regt1onwheret2.stud_idisselectt1.*,t2.*fromstudentt2rightouterjoinclass_regt1onwheret2.stud_idisselectt2.*,t1.*fromstudentt1leftouterjoinstudyt2ont1.stud_id=t2.stud_idwheret2.stud_idselectt2.*,t1.*fromstudentt1leftouterjoinstudyt2ont1.stud_id=t2.stud_idwheret2.stud_idisnullorderbyt1.stud_idselectt2.*,t1.*fromstudyt2rightouterjoinstudentt1ont1.stud_id=t2.stud_idwheret2.stud_idisnullorderbyt1.stud_id1where23十一 子查詢的概念與使1sql2sqlsqlselectclass_idselectclass_idfromselect*fromstudentwhereclass_idin(selectclass_idfromupdateclass_regsetstart_time=to_Date('2008-1-27','yyyy-mm-dd')whereclass_id='QH10001'selectclass_idfromclass_regwhereselectclass_idfromclass_regwherestart_time=to_Date('2008-1-select*fromstudentwhereclass_idin(selectclass_idfromclass_regwhereclass_id='QH10001')select*fromstudentwhereclass_idin('QH10001')select*fromstudentwhereclass_idin(selectclass_idfromclass_regwhere)selectavg(age)fromselect*fromstudentwhereage>=(selectavg(age)fromstudent)updatestudentsetage=(selectavg(age)fromstudent)where十二 關(guān)于數(shù)據(jù)視圖設(shè)3createview數(shù)據(jù)視圖名稱4SQL--select--select*fromstudentwhereclass_idin('QH10001','QH10002')selectstud_id,name,class_id,,agefromstudentwhereclass_idin('QH10001','QH10002')and='女'createviewVSTUDENT_1selectstud_id,name,class_id, ,agefromstudentwhereclass_idin('QH10001','QH10002')and='女'select*fromVSTUDENT_1createviewVSTUDENT_2asselect*fromstudentwhereclass_idin('QH10001','QH10002')select*from--此時(shí)VTUDENT_3與studentcreateviewVSTUDENT_3asselect*fromstudentselect*fromselectt1.*,namefromstudyt1,studentt2wherecreateviewV_STUDY1selectt1.*,namefromstudyt1,studentt2whereselect*from十三 關(guān)于數(shù)據(jù)視圖的解2345十四 關(guān)于索引的概1index,2的34 1createuniqueindex索引名稱on數(shù)據(jù)表名稱(122 index索引名稱on數(shù)據(jù)表名稱(123--createuniqueindex--createuniqueindexuser_reg_index1onupdateuser_regsetname=' 'whereuser_id='lixin'--有錯(cuò)，因?yàn)?updateuser_regsetname=' 'whereuser_id='lixin'dropindexuser_reg_index1--createindexuser_reg_index2onupdateuser_regsetname=' 'whereuser_id='lixin'--沒有錯(cuò)，因?yàn)閐ropindex十六 索引的優(yōu)缺1wherewhere2十七 用一個(gè)己有數(shù)據(jù)表，向一個(gè)空的數(shù)據(jù)表插入數(shù)據(jù)，或新建一個(gè)數(shù)據(jù)--create--createtableuser_regAasselect*from--create--createtableuser_regBasselect*fromuser_Regwhere1=0select*fromuser_