項(xiàng)目“學(xué)生管理系統(tǒng)”中查詢(xún)的應(yīng)用

網(wǎng)絡(luò)數(shù)據(jù)庫(kù)案例與應(yīng)用授課教師：聯(lián)系電話(huà)：E–Mail：任務(wù)1單表查詢(xún)和多表查詢(xún)?nèi)蝿?wù)2子查詢(xún)和分組查詢(xún)?nèi)蝿?wù)3使用視圖任務(wù)4設(shè)計(jì)并實(shí)現(xiàn)“修改學(xué)生基本信息頁(yè)面”任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)項(xiàng)目三“學(xué)生管理系統(tǒng)”中查詢(xún)的應(yīng)用教師2能夠使用語(yǔ)句完成單表、多表數(shù)據(jù)的查詢(xún)；對(duì)于各種查詢(xún)?nèi)蝿?wù)，能夠用分組、集合函數(shù)等查詢(xún)選項(xiàng)來(lái)實(shí)現(xiàn)；能夠結(jié)合查詢(xún)技術(shù)完成修改信息頁(yè)面的代碼書(shū)寫(xiě)；能夠根據(jù)登錄流程，結(jié)合查詢(xún)完成登錄頁(yè)面代碼的書(shū)寫(xiě)；能夠設(shè)計(jì)必要的存儲(chǔ)過(guò)程、觸發(fā)器和函數(shù)。教學(xué)目標(biāo)目錄教師3教學(xué)重難點(diǎn)單表查詢(xún)和多表查詢(xún)；子查詢(xún)和分組查詢(xún)；查詢(xún)?cè)谛薷男畔㈨?yè)面和登錄頁(yè)面中的應(yīng)用。自主學(xué)習(xí)設(shè)計(jì)郵箱系統(tǒng)的“用戶(hù)登錄”頁(yè)面，編寫(xiě)代碼實(shí)現(xiàn)登錄功能，登錄后頁(yè)面轉(zhuǎn)向主頁(yè)面。教學(xué)重難點(diǎn)及自主學(xué)習(xí)目錄教師4任務(wù)1單表查詢(xún)和多表查詢(xún)student表結(jié)構(gòu)及數(shù)據(jù)course表結(jié)構(gòu)及數(shù)據(jù)stu_course表結(jié)構(gòu)及數(shù)據(jù)teacher表結(jié)構(gòu)及數(shù)據(jù)1.1查詢(xún)數(shù)據(jù)介紹目錄教師5任務(wù)1單表查詢(xún)和多表查詢(xún)1.1查詢(xún)數(shù)據(jù)介紹student與stu_course表通過(guò)snocourse與stu_course表通過(guò)cnocourse與teacher表通過(guò)tno目錄教師6任務(wù)1單表查詢(xún)和多表查詢(xún)1.2、查詢(xún)語(yǔ)句的基本格式SELECT<字段列表>FROM<表名/視圖名列表> [WHERE條件表達(dá)式]1.3單表查詢(xún)示例及查詢(xún)其他關(guān)鍵字的使用1)查詢(xún)部分列：selectsno,snamefromstu2)查詢(xún)?nèi)苛校?）：select*fromcourse3)設(shè)置字段別名（as別名）selectsnoas學(xué)號(hào),姓名=snamefromstu4)查詢(xún)經(jīng)過(guò)計(jì)算的值Selectsno,sname,age+1fromstu目錄教師71.3單表查詢(xún)示例及查詢(xún)其他關(guān)鍵字的使用5)去除重復(fù)記錄（DISTINCT）Selectdistinctsnofromstu_courseSelectsnofromstu_course2條語(yǔ)句執(zhí)行后有何不同6)返回前面若干條記錄(TOPn︱TOPnPERCENT)Selecttop2*fromstuSelecttoppercent2*fromstu7)保存查詢(xún)結(jié)果到數(shù)據(jù)表（into表名）Select*intostu_newfromstuwheresex=‘男’8)對(duì)查詢(xún)結(jié)果進(jìn)行排序（orderby）Select*fromstuorderbyageSelect*fromstuorderbybirthdaydesc任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師81.3單表查詢(xún)示例及查詢(xún)其他關(guān)鍵字的使用9)模糊查詢(xún)（like）％：表示若干個(gè)任意字符。_：表示單個(gè)任意字符。[]：表示方括號(hào)里列出的任意一個(gè)字符。[^]：任意一個(gè)沒(méi)有在方括號(hào)里列出的字符。selectsno,snameFROMstuwheresnamelike'張%'selectsno,snameFROMstuwheresnamelike‘%張%'10)in查詢(xún)select*fromstudentwheresnoin(‘2012001’,’2012002’)等價(jià)于select*fromstudentwheresno=‘2012001’orsno=‘2012002’任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師91.3單表查詢(xún)示例及查詢(xún)其他關(guān)鍵字的使用

11)集合函數(shù)的統(tǒng)計(jì)功能sum():求和，avg():平均，max():最大,min():最小,count()：計(jì)數(shù)selectsum(score),avg(score),max(score),min(score),count(*)fromstu_course查詢(xún)stu_course表中score的和、平均、最高分、最低分以及記錄條數(shù)selectcount(*),max(birthday)fromstudentwheresex=‘男’統(tǒng)計(jì)出student表中男生的人數(shù)和birthday的最大值任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師101.4多表查詢(xún)當(dāng)from后表名多于1個(gè)時(shí)稱(chēng)為多表查詢(xún)，是數(shù)據(jù)庫(kù)中最主要的查詢(xún)方式為了保證數(shù)據(jù)的準(zhǔn)確性，多表查詢(xún)需要帶上連接條件，連接條件是使用表之間的關(guān)聯(lián)字段將表邏輯上串聯(lián)成一串連接條件有內(nèi)連接，左連接，右連接，完全連接、交叉連接等形式任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師111.4多表查詢(xún)1、內(nèi)連接（innerjoin）最簡(jiǎn)單的內(nèi)連接條件表示where表1.連接字段=表2.連接字段正規(guī)內(nèi)連接條件表示表1innerjoin表2on表1.連接字段=表2.連接字段示例：查詢(xún)“彭佳樂(lè)”同學(xué)選修課程的分?jǐn)?shù)（數(shù)據(jù)來(lái)自2張表）selectscorefromstudent,stu_coursewheresname=‘彭佳樂(lè)’and

student.sno=stu_course.sno

等價(jià)于selectscorefromstudentinnerjoinstu_courseonstudent.sno=stu_course.snowheresname='彭佳樂(lè)'任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師121.4多表查詢(xún)1、內(nèi)連接（innerjoin）在多表查詢(xún)時(shí)，表數(shù)量越多，連接條件也越多，總之需要將表在邏輯上串成一串查詢(xún)學(xué)生的姓名及所選課程課程名和分?jǐn)?shù)（數(shù)據(jù)來(lái)自3張表）selectsname,cname,scorefromstudent,stu_course,coursewherestudent.sno=stu_o=stu_o等價(jià)于selectsname,cname,scorefromstudentinnerjoinstu_courseonstudent.sno=stu_course.snoinnerjoincourseonstu_o=o

任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師131.4多表查詢(xún)1、內(nèi)連接（innerjoin）查詢(xún)學(xué)生的姓名及所選課程課程名、分?jǐn)?shù)和教師姓名（數(shù)據(jù)來(lái)自4張表）selectsname,cname,score,tnamefromstudent,stu_course,course,teacherwherestudent.sno=stu_o=stu_oandteacher.tno=course.tno等價(jià)于selectsname,cname,score,tnamefromstudentinnerjoinstu_courseonstudent.sno=stu_course.snoinnerjoincourseonstu_o=oinnerjointeacheronteacher.tno=course.tno

任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師141.4多表查詢(xún)2、左連接（leftjoin）將左表數(shù)據(jù)全部顯示，右表在左表中有對(duì)應(yīng)值則顯示，沒(méi)有顯示NULLselect*fromstudentleftjoinstu_courseonstudent.sno=stu_course.sno任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師151.4多表查詢(xún)3、右連接（rightjoin）是左連接的逆運(yùn)算4、完全連接（fulljoin）是左連接與右連接的綜合，會(huì)將左表、右表的數(shù)據(jù)全部顯示，有對(duì)應(yīng)的就顯示，無(wú)對(duì)應(yīng)的以NULL填充5、交叉連接（crossjoin）是表中數(shù)據(jù)的所有組合可能。select*fromteachercrossjoincourse等價(jià)于select*fromteacher,course任務(wù)1單表查詢(xún)和多表查詢(xún)目錄教師16任務(wù)2子查詢(xún)和分組查詢(xún)2.1子查詢(xún)找出沒(méi)有選課的學(xué)生的姓名、性別snosnamesex001張三男002李四男003王五男snocnoscore001001960020026900300185cnocnametea001SQL96001C69002ASP.NET85studentstu_coursecourse目錄教師17任務(wù)2子查詢(xún)和分組查詢(xún)2.1子查詢(xún)1、基本語(yǔ)法格式select字段名列表from表名列表where字段名

in|notin|關(guān)系表達(dá)式any|關(guān)系表達(dá)式all(select

字段名

from表名列表where條件表達(dá)式)當(dāng)一個(gè)查詢(xún)作為另一個(gè)查詢(xún)的條件時(shí)，稱(chēng)為子查詢(xún)2、解決問(wèn)題：selectsname,sexfromstudentwheresnonotin(selectsnofromstu_course)同類(lèi)問(wèn)題：查詢(xún)沒(méi)有學(xué)生選修的課程信息目錄教師18任務(wù)2子查詢(xún)和分組查詢(xún)2.2分組查詢(xún)（GROUPBY）1、GROUPBY字段或計(jì)算字段[HAVING條件]按照字段分割表中數(shù)據(jù)，將值相同的分成一組selectcount(sno)fromstu_coursegroupbysno統(tǒng)計(jì)各學(xué)號(hào)在stu_course表中出現(xiàn)的次數(shù)selectsno,count(sno)as選修門(mén)數(shù)fromstu_coursegroupbysnohavingCOUNT(sno)>=2分組后條件限制，不可用where實(shí)現(xiàn)2、課堂案例查詢(xún)選課最多的學(xué)生的選課門(mén)數(shù)查詢(xún)每門(mén)課程的選修量目錄教師19任務(wù)3使用視圖3.1視圖概念視圖是基于一個(gè)或多個(gè)基本數(shù)據(jù)表而生成的一個(gè)虛擬表，僅存儲(chǔ)了視圖的定義，沒(méi)有存儲(chǔ)實(shí)際數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)在基本表中。3.2創(chuàng)建視圖1、圖形化方式目錄教師20任務(wù)3使用視圖3.2創(chuàng)建視圖1、圖形化方式目錄教師21任務(wù)3使用視圖3.2創(chuàng)建視圖2、命令方式CREATEVIEW視圖名[(視圖字段名1,視圖字段名2,…)][WITHENCRYPTION]ASSELECT查詢(xún)語(yǔ)句[WITHCHECKOPTION]說(shuō)明：WITHENCRYPTION關(guān)鍵字表示對(duì)視圖加密示例createviewVIEW_stu1asselectstudent.*,scorefromstudentinnerjoinstu_courseonstu_course.sno=student.snowherescore<60目錄教師22任務(wù)4設(shè)計(jì)并實(shí)現(xiàn)“修改學(xué)生基本信息頁(yè)面”4.1頁(yè)面設(shè)計(jì)（UpdateStu.aspx）目錄教師23任務(wù)4設(shè)計(jì)并實(shí)現(xiàn)“修改學(xué)生基本信息頁(yè)面”4.1頁(yè)面設(shè)計(jì)（UpdateStu.aspx）控件ID屬性值說(shuō)明TextBox1MaxLength7輸入學(xué)號(hào)TextBox2MaxLength20輸入姓名TextBox3MaxLength10輸入出生日期RadioButton1Text男GroupNamesex組名相同可保證只有一個(gè)被選中CheckedFalse是否被選中RadioButton2Text女GroupNamesexCheckedFalse是否被選中Button1Text查詢(xún)根據(jù)學(xué)號(hào)框的輸入查詢(xún)Button2Text修改確認(rèn)修改Button3Text取消清空輸入目錄教師24任務(wù)4設(shè)計(jì)并實(shí)現(xiàn)“修改學(xué)生基本信息頁(yè)面”4.2代碼設(shè)計(jì)1、為ConnSql類(lèi)添加方法①添加引用usingSystem.Data;//為了可直接使用DataTable對(duì)象②添加RunSqlReturnTable()方法publicDataTable

RunSqlReturnTable(string

sqltext){#regionOpen();

SqlDataAdapter

sda=newSqlDataAdapter(sqltext,con);//定義對(duì)象實(shí)例sda

DataTabletable=newDataTable();

sda.Fill(table);//將sda中的數(shù)據(jù)裝載進(jìn)table中

Close();returntable;//返回table#endregion}目錄教師25任務(wù)4設(shè)計(jì)并實(shí)現(xiàn)“修改學(xué)生基本信息頁(yè)面”4.2代碼設(shè)計(jì)1、為ConnSql類(lèi)添加方法①添加引用usingSystem.Data;//為了可直接使用DataTable對(duì)象②添加RunSqlReturnTable()方法publicDataTable

RunSqlReturnTable(string

sqltext){#regionOpen();

SqlDataAdapter

sda=newSqlDataAdapter(sqltext,con);//定義對(duì)象實(shí)例sda

DataTabletable=newDataTable();

sda.Fill(table);//將sda中的數(shù)據(jù)裝載進(jìn)table中

Close();returntable;//返回table#endregion}目錄教師26任務(wù)4設(shè)計(jì)并實(shí)現(xiàn)“修改學(xué)生基本信息頁(yè)面”4.2代碼設(shè)計(jì)2、編寫(xiě)查詢(xún)按鈕代碼3、編寫(xiě)修改按鈕代碼4、編寫(xiě)取消按鈕代碼參考教材目錄教師27任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”5.1界面設(shè)計(jì)1、添加web窗體Login.aspx2、頁(yè)面布局目錄教師28任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”5.1界面設(shè)計(jì)1、添加web窗體Login.aspx2、頁(yè)面布局控件ID屬性值說(shuō)明TextBox1MaxLength16輸入用戶(hù)名TextBox2MaxLength16輸入密碼TextModePassword密碼演示掩碼TextBox3MaxLength0輸入驗(yàn)證碼Label1Text用于顯示產(chǎn)生的隨機(jī)驗(yàn)證碼Button1Text登錄實(shí)現(xiàn)登錄Button2Text取消清空輸入內(nèi)容目錄教師29任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”5.2代碼設(shè)計(jì)1、驗(yàn)證碼設(shè)計(jì)①在App_Code文件夾中添加類(lèi)，名為“Yzm.cs”②添加靜態(tài)方法CreateYzm(intn)靜態(tài)方法可以直接使用類(lèi)名調(diào)用，不用定義實(shí)例名。代碼參考教材目錄教師30任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”5.2代碼設(shè)計(jì)2、編寫(xiě)窗體的Page_Load事件，用于顯示產(chǎn)生的隨機(jī)驗(yàn)證碼protectedvoidPage_Load(objectsender,EventArgse){if(IsPostBack==false)Label1.Text=Yzm.CreateYzm(4);//產(chǎn)生長(zhǎng)度為4的驗(yàn)證碼

}目錄教師31任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”5.2代碼設(shè)計(jì)3、編寫(xiě)“登錄”按鈕的Click事件代碼①添加引用

usingSystem.Data;②編寫(xiě)Click代碼

stringsqltext="select*fromadminwhereusername='"+TextBox1.Text+"'";

DataTabletable=newDataTable();

ConnSql

cn=newConnSql();table=cn.RunSqlReturnTable(sqltext);if(TextBox1.Text=="")

WebMessage.Show("請(qǐng)輸入用戶(hù)名");elseif(TextBox2.Text=="")

WebMessage.Show("請(qǐng)輸入密碼");elseif(TextBox3.Text=="")

WebMessage.Show("請(qǐng)輸入驗(yàn)證碼");else目錄教師32任務(wù)5設(shè)計(jì)并實(shí)現(xiàn)“管理員用戶(hù)登錄頁(yè)面”5.2代碼設(shè)計(jì)3、編寫(xiě)“登錄”按鈕的Click事件代碼②編寫(xiě)Click代碼

{if(table.Rows.Count<=0)

WebMessage.Show("用戶(hù)名錯(cuò)誤！");elseif(table.Rows[0][1].ToString().Trim()!=TextBox2.Text)

WebMessage.Show("密碼錯(cuò)誤！");elseif(TextBox3.Text.Trim().ToUpper()!=Label1.Text.ToUpper())

WebMessage.Show("驗(yàn)證碼錯(cuò)誤！");else{//保存登錄的用戶(hù)的用戶(hù)名和密碼到Session對(duì)象

Session["username"]=table.Rows[0][0].ToString();

Session["password"]=table.Rows[0][1].ToString();//頁(yè)面轉(zhuǎn)向stu文件夾中的UpdateStu.aspx頁(yè)面

Response.Redirect("stu/UpdateStu.aspx");}}目錄教師33任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)6.1局部變量由用戶(hù)定義用于存放臨時(shí)數(shù)據(jù)的變量。局部變量由@開(kāi)頭，后跟變量名，如@a1、@s、@sum等1、聲明局部變量DECLARE變量名數(shù)據(jù)類(lèi)型[,……]例如：DECLARE@aint,@sflaot,@ddatetime目錄教師342、變量的賦值SELECT變量名1=表達(dá)式/字段名[from子句][where

子句]……,變量名2=……SET變量名=表達(dá)式3、舉例declare@aint,@bchar(10),@cchar(3),@nfloatselect@a=scorefromstu_courseselect@b='VFP6.0',@n=123.456set@c='1.2'select@a,@b,@c,@n任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師356.2流程控制語(yǔ)句1、BEGIN…END語(yǔ)句塊

作用：將多條T-SQL語(yǔ)句組合在一起，組成一個(gè)邏輯塊，當(dāng)控制流語(yǔ)句必須執(zhí)行一個(gè)包含一條或兩條以上的T-SQL語(yǔ)句的語(yǔ)句塊時(shí)使用。注意：相當(dāng)與很多語(yǔ)言中的{}2、IF…ELSE語(yǔ)句格式：IF(布爾表達(dá)式){SQL語(yǔ)句塊1}ELSE{SQL語(yǔ)句塊2}任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師36Eg：如果student表中有名叫張三的學(xué)生，請(qǐng)輸出信息，否則提示用戶(hù)沒(méi)有這個(gè)學(xué)生。IFexists(selectsnofromstudent

wheresname='張三')

BEGINPRINT'下列學(xué)生的姓名是張三'

select*fromstudent

wheresname='張三'

ENDELSEBEGINPRINT'沒(méi)有張三這個(gè)的學(xué)生'

END任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師37任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)3、CASE…END格式一：CASE表達(dá)式WHEN值1THEN結(jié)果表達(dá)式1WHEN值2THEN結(jié)果表達(dá)式2…[ELSE結(jié)果表達(dá)式N]END格式二：CASEWHEN邏輯表達(dá)式1THEN結(jié)果表達(dá)式1WHEN邏輯表達(dá)式2THEN結(jié)果表達(dá)式2…[ELSE結(jié)果表達(dá)式N]END目錄教師38任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)Eg：selectsno,sname,casesexwhen0then'男'when1then'女'else'不確定'endas性別fromstudent目錄教師39任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)select姓名=casesnowhen'2012001'then'薛若涵'when'2012002'then'彭佳樂(lè)'when'2012003'then'范一帥'when'2012004'then'楊一凡'when'2012005'then'薛若涵'when'2012006'then'胡梓昕'when'2012007'then'賀欣雅'else'請(qǐng)查詢(xún)'end,cno,casewhenscore>=85then'A'whenscore>=75then'B'whenscore>=60then'C'else'D'endscorefromstu_course

目錄教師404、WHILE語(yǔ)句

格式：

WHILE布爾表達(dá)式

BEGIN

命令程序塊

END作用：當(dāng)WHILE后面的布爾表達(dá)式為真，會(huì)重復(fù)執(zhí)行命令程序塊，直到條件不成立時(shí)為止任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師41Eg2：判斷xk表中是否有學(xué)生的成績(jī)少于30，如果有，則將所有學(xué)生的成績(jī)?cè)黾?，直到所有學(xué)生的成績(jī)都多于30WHILEEXISTS(SELECT*FROMxkwherecj<30)

BEGINUPDATExksetcj=cj+1

END任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師426.3存儲(chǔ)過(guò)程設(shè)計(jì)1、簡(jiǎn)介是數(shù)據(jù)庫(kù)對(duì)象之一，是一段存儲(chǔ)起來(lái)備用的SQL程序，用于完成某項(xiàng)具體的操作。使用存儲(chǔ)過(guò)程的原因有：方便、執(zhí)行速度快、安全。2、存儲(chǔ)過(guò)程的類(lèi)型①系統(tǒng)存儲(chǔ)過(guò)程（systemprocedure）②用戶(hù)自定義存儲(chǔ)過(guò)程任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師436.3存儲(chǔ)過(guò)程設(shè)計(jì)3、創(chuàng)建用戶(hù)自定義存儲(chǔ)過(guò)程CREATEPROC[EDURE]存儲(chǔ)過(guò)程名[參數(shù)名類(lèi)型[=默認(rèn)值]，]ASSQL語(yǔ)句[SQL語(yǔ)句]任務(wù)6存儲(chǔ)過(guò)程設(shè)計(jì)目錄教師446.3存儲(chǔ)過(guò)程設(shè)計(jì)3、創(chuàng)建用戶(hù)自定義存儲(chǔ)過(guò)程

例：建立proc_stu1存儲(chǔ)過(guò)程，查詢(xún)cj在80-85之間且是女學(xué)生的信息

CREATEPROCEDUREproc_stu1

AS

SELECT*FROMstudent,stu_course

WHEREstu_course.sno=student.snoand

(score

between80and18