主講教師：姜云橋任務(wù)3.4CASE表達(dá)式和控制流函數(shù)子項(xiàng)目3數(shù)據(jù)庫的查詢開發(fā)任務(wù)目標(biāo)1.能熟練使用CASE表達(dá)式完成各種查詢2.能利用控制流函數(shù)完成各種查詢3.能使用CASE表達(dá)式完成數(shù)據(jù)的更改知識(shí)目標(biāo)1.掌握CASE表達(dá)式的兩種語法2.掌握CASE表達(dá)式在select、groupby、orderby、UPDATE等語句的使用。3.掌握IF、IFNULL控制流函數(shù)的使用能力目標(biāo)任務(wù)要求對(duì)數(shù)據(jù)庫jxgl中的student(學(xué)生信息表)、course（課程表）、score（成績(jī)表）、departments（院系單位表）、teacher（教師表）和teach（講授表）6張表進(jìn)行增刪改操作任務(wù)必備知識(shí)想一想CASE表達(dá)式是什么？任務(wù)必備知識(shí)3.4.1CASE表達(dá)式CASE表達(dá)式是MySQL中非常重要的技術(shù)，通過它可以巧妙的實(shí)現(xiàn)一些查詢，本節(jié)將通過幾個(gè)常見需求來學(xué)習(xí)該表達(dá)式的使用。CASE表達(dá)式有兩種語法形式，一種時(shí)簡(jiǎn)單CASE表達(dá)式，另外一種是搜索CASE表達(dá)式。任務(wù)必備知識(shí)一、簡(jiǎn)單case表達(dá)式簡(jiǎn)單case表達(dá)式類似Java中的switch結(jié)構(gòu)，語法格式如下：caseexpression whenvalue1thenresult1 whenvalue2thenresult2 ... elseresultend3.4.1CASE表達(dá)式任務(wù)必備知識(shí)【任務(wù)3.4.1】查詢學(xué)生表student“張”姓同學(xué)的性別，男性輸出1，女性輸出2mysql>selects_name,cases_sex->when'男'then1->when'女'then2->endas'性別'->fromstudent->wheres_namelike'張%’;3.4.1CASE表達(dá)式任務(wù)必備知識(shí)【任務(wù)3.4.2】假設(shè)文明路、人民路屬于片區(qū)1，北京路、東風(fēng)路屬于片區(qū)2，學(xué)院路屬于片區(qū)3，其他屬于片區(qū)4，查詢學(xué)生表，統(tǒng)計(jì)各片區(qū)學(xué)生人數(shù)mysql>selectcasesubstring(s_address,1,3)->when'文明路'then'片區(qū)1'->when'人民路'then'片區(qū)1'->when'北京路'then'片區(qū)2'->when'東風(fēng)路'then'片區(qū)2'->when'學(xué)院路'then'片區(qū)3'->else'片區(qū)4'->endassection,count(*)->fromstudent->groupbysection;3.4.1CASE表達(dá)式任務(wù)必備知識(shí)二、搜索CASE表達(dá)式case whencond1thenresult1 whencond2thenresult2... elseresultend3.4.1CASE表達(dá)式任務(wù)必備知識(shí)三、CASE表達(dá)式的試用3.4.1CASE表達(dá)式1.select語句中使用CASE表達(dá)式【任務(wù)3.4.3】通過搜索CASE表達(dá)式的方式顯示任務(wù)3.4.1mysql>selects_name,casewhens_sex='男'then1else2endas'性別'->fromstudent->wheres_namelike'張%';任務(wù)必備知識(shí)3.4.1CASE表達(dá)式2.利用CASE表達(dá)式完成行列轉(zhuǎn)換【任務(wù)3.4.4】行列轉(zhuǎn)換，查詢學(xué)生表student，統(tǒng)計(jì)各系男、女生人數(shù)mysql>selectd_no,->sum(casewhens_sex='男'then1else0end)asm_count,->sum(casewhens_sex='女'then1else0end)asf_count->fromstudent->groupbyd_no;任務(wù)必備知識(shí)3.4.1CASE表達(dá)式3.GROUPBY子句使用CASE表達(dá)式【任務(wù)3.4.5】查詢成績(jī)表score，統(tǒng)計(jì)優(yōu)秀數(shù)、良好數(shù)、及格數(shù)、不及格數(shù)，90~100為優(yōu)秀，80~89為良好，60~79為及格，0~59為不及格mysql>selectcasewhenmark>=90then'優(yōu)秀'->whenmark>=80then'良好'->whenmark>=60then'及格'->else'不及格'->endaslevel,count(*)->fromscore->groupbylevel;任務(wù)必備知識(shí)3.4.1CASE表達(dá)式4.orderby子句使用case表達(dá)式【任務(wù)3.4.6】查詢課程表，按照專業(yè)課、專業(yè)基礎(chǔ)課、必修課、選修課的順序顯示課程信息mysql>select*->fromcourse->orderbycasewhenc_type='專業(yè)課'then1->whenc_type='專業(yè)基礎(chǔ)課'then2->whenc_type='必修課'then3->else4->end;任務(wù)必備知識(shí)3.4.1CASE表達(dá)式5.update更新數(shù)據(jù)時(shí)使用case表達(dá)式【任務(wù)3.4.7】按如下規(guī)則更新課程的學(xué)分，專業(yè)課*4，必修課*3，選修課*2，其他類型的課程不變mysql>updatecoursesetc_credit=casewhenc_type='專業(yè)課'thenc_credit*4->whenc_type='必修課'thenc_credit*3->whenc_type='選修課'thenc_credit*2->elsec_credit->end;任務(wù)必備知識(shí)想一想控制流函數(shù)有哪些？任務(wù)必備知識(shí)3.4.2控制流函數(shù)一、if()函數(shù)語法格式如下：

if(expr1,expr2,expr3)這個(gè)函數(shù)有3個(gè)參數(shù)，第一個(gè)是要被判斷的表達(dá)式，如果表達(dá)式expr1成立，返回結(jié)果expr2；否則，返回結(jié)果expr3。expr1expr2是expr3否任務(wù)必備知識(shí)【任務(wù)3.4.8】查詢成績(jī)表score，獲取課程號(hào)’A002’的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)，成績(jī)顯示是否及格，若成績(jī)的值≥60,則顯示為“是”，<60顯示為“否”mysql>selects_no,c_no,if(mark>=60,'是','否')as'是否及格'->fromscore->wherec_no='A002’;3.4.2控制流函數(shù)任務(wù)必備知識(shí)【任務(wù)3.4.9】查詢學(xué)生表student，統(tǒng)計(jì)各系男、女生人數(shù)mysql>selectd_no,->sum(if(s_sex='男',1,0))asm_count,->sum(if(s_sex='女',1,0))asf_count->fromstudent->groupbyd_no;3.4.2控制流函數(shù)任務(wù)必備知識(shí)3.4.2控制流函數(shù)二、ifnull函數(shù)ifnull函數(shù)的語法格式如下：

ifnull(expr1,expr2)此函數(shù)的作用是：判斷參數(shù)expr1是否為null，如果expr1的不為空，就顯示expr1的值；否則就顯示expr2的值。任務(wù)必備知識(shí)【任務(wù)3.4.10】從student表中查詢學(xué)號(hào)(s_no)和地址(s_address)。如果s_address不為null，顯示地址，否則，顯示“地址未知”mysql>selects_no,ifnull(s_address,'地址未知')->fromstudent;3.4.1CASE表達(dá)式任務(wù)實(shí)施1.查詢B001部門員工的姓名、性別、職稱、學(xué)歷，并按學(xué)歷（博士、碩士、本科排序），結(jié)果如下:任務(wù)實(shí)施2.統(tǒng)計(jì)各部門黨員和非黨員人數(shù)，結(jié)果如下:任務(wù)實(shí)施3.統(tǒng)計(jì)每個(gè)員工的收入（基本收入+福利+補(bǔ)貼-公積金-稅-其他）及等級(jí)，>8000為高，6000~8000為中，<6000為低，結(jié)果如下:任務(wù)實(shí)施4.用if函數(shù)實(shí)現(xiàn)（2）中的查詢?nèi)蝿?wù)實(shí)施要求撰寫實(shí)訓(xùn)總結(jié)，把完成任務(wù)過程中主要操作、命令及關(guān)鍵步驟的截圖，遇到的問題與解決方法