SQL從入門到進階跑 課件 第5、6章 玩轉(zhuǎn)SQL函數(shù)與語法-數(shù)據(jù)的高級查詢；封裝SQL語句的表-視圖的增刪及查詢

第5章玩轉(zhuǎn)SQL函數(shù)與語法——數(shù)據(jù)的高級查詢/05Contents1SQL常用函數(shù)23去重查詢嵌套查詢45關(guān)聯(lián)查詢合并查詢67分組排序查詢轉(zhuǎn)置查詢5.1SQL常用函數(shù)3MySQL內(nèi)置了很多函數(shù)，包括數(shù)值函數(shù)、字符串函數(shù)、日期和時間函數(shù)、格式類型轉(zhuǎn)換函數(shù)、條件判斷函數(shù)、非空處理函數(shù)等。數(shù)值函數(shù)字符串函數(shù)日期函數(shù)其他函數(shù)ABSLENGTHCURRENT_DATE、CURDATECASTMODUPPER、LOWER、UCASE、LCASECURRENT_TIME、CURTIMECONVERTPOWER、POWCONCAT、CONCAT_WSNOW、CURRENT_TIMESTAMP、SYSDATEIFCEIL、CEILINGLEFT、RIGHTYEAR、QUARTER、MONTH、DAYCASEFLOORSUBSTRINGHOUR、MINUTE、SECONDIFNULL、COALESCERANDREPLACEDAYOFWEEK、DAYOFMONTH、DAYOFYEARNULLIFROUNDINSERTWEEK、WEEKOFYEARISNULLTRUNCATEINSTR、POSITION、LOCATEWEEKDAYTRIMMONTHNAME、DAYNAMEREVERSEDATE_ADD、ADDDATEDATE_SUB、SUBDATETIMESTAMPADDADDTIME、SUBTIMEDATEDIFF、TIMEDIFFTIMESTAMPDIFFDATE、TIME、TIMESTAMPDATE_FORMAT、TIME_FORMAT

UNIX_TIMESTAMP、FROM_UNIXTIME

表1MySQL常用函數(shù)MySQL常用函數(shù)如下所示：5.2去重查詢4當(dāng)從數(shù)據(jù)表中查詢出來的行數(shù)據(jù)（單列或多列）出現(xiàn)重復(fù)時，可以使用DISTINCT關(guān)鍵字進行去重查詢，針對重復(fù)的行數(shù)據(jù)僅保留其中一條。DISTINCT語法格式如下所示：示例：查詢門店電器用戶信息表store_appliance_userinfo中所有客戶的不同省份。5.3嵌套查詢5在一段SQL查詢腳本中嵌套了一個或多個查詢，嵌套的查詢語句通常叫作子查詢語句。子查詢語句可以分為兩種類型，一種類型是相關(guān)子查詢，另外一種類型是不相關(guān)子查詢。示例：查詢門店電器用戶信息表store_appliance_userinfo中年齡高于所有人平均年齡的客戶信息。5.3.1常量值的子查詢下面主要針對不相關(guān)子查詢進行詳細講解。示例：查詢門店電器用戶信息表store_appliance_userinfo中人數(shù)大于等于2人對應(yīng)的省份個數(shù)。5.3.2表的子查詢5.4關(guān)聯(lián)查詢6關(guān)聯(lián)查詢指的是將兩個或兩個以上的表連接起來進行查詢，多表通過連接進行列合并生成一張總表，連接后的總表可以作為一張全新的表來查詢。MySQL中的關(guān)聯(lián)查詢主要分為三種類型，分別是：內(nèi)關(guān)聯(lián)（[INNER]JOIN）、左關(guān)聯(lián)（LEFTJOIN）、右關(guān)聯(lián)（RIGHTJOIN）。三種關(guān)聯(lián)的語法基本一致，語法格式如下所示：除了上面三種關(guān)聯(lián)類型之外，還可以衍生出四種關(guān)聯(lián)查詢。5.5合并查詢7合并查詢指的是數(shù)據(jù)的行合并查詢。在SQL語法中通常使用UNION或UNIONALL關(guān)鍵字來實現(xiàn)數(shù)據(jù)的行合并操作，SQL合并查詢語法如下所示：示例：查詢門店電器用戶信息表store_appliance_userinfo中男性與女性的所屬省份，并將所屬省份數(shù)據(jù)行合并到一起（去重）。5.5.1去重合并查詢?nèi)ブ睾喜⒉樵兪褂玫氖荱NION關(guān)鍵字，通過UNION將兩個或者多個SELECT查詢語句返回的結(jié)果集在垂直方向合并到一起，并實現(xiàn)去重操作。5.5合并查詢8示例：查詢門店電器用戶信息表store_appliance_userinfo中男性與女性的所屬省份，并將所屬省份數(shù)據(jù)行合并到一起（不去重）。5.5.2不去重合并查詢不去重合并查詢使用的是UNIONALL關(guān)鍵字，通過UNIONALL將兩個或者多個SELECT查詢語句返回的結(jié)果集在垂直方向合并到一起，行合并之后的數(shù)據(jù)不會進行去重操作。5.6分組排序查詢9MySQL在8.0版本之后新增了幾個窗口函數(shù)，可以幫助解決分組后不同組內(nèi)的排序問題。這里涉及排序的窗口函數(shù)主要有RANK、DENSE_RANK、ROW_NUMBER。它們的相同點與不同點如下所示：5.6分組排序查詢10SQL分組排序查詢函數(shù)語法如下所示：示例：查詢門店電器用戶信息表store_appliance_userinfo中的客戶信息以及年齡從高到低的排序（三種排序方式）。5.7轉(zhuǎn)置查詢11查詢有時需要將表進行轉(zhuǎn)置，將原數(shù)據(jù)中的某一列作為維度轉(zhuǎn)換為列標題。MySQL中借助于CASE…WHEN、IF、聚合函數(shù)（MAX、MIN、SUM、AVG）來實現(xiàn)轉(zhuǎn)置查詢。示例：將左邊表的數(shù)據(jù)轉(zhuǎn)置成右邊表的數(shù)據(jù)，這里就用到了轉(zhuǎn)置操作，需要將prodName字段作為維度轉(zhuǎn)換為列標題。具體實現(xiàn)腳本如下：第6章封裝SQL語句的表——視圖的增刪及查詢/06Contents1視圖的創(chuàng)建23視圖的刪除視圖的修改4視圖的查詢第6章

封裝SQL語句的表——視圖的增刪及查詢14視圖（View）是一種虛擬存在的表，但視圖并不實際存在于數(shù)據(jù)庫中。視圖也是由列和行構(gòu)成，行和列的數(shù)據(jù)來自于定義視圖的查詢中所使用的表，并且還是在使用視圖時動態(tài)生成的。視圖相對于普通表的優(yōu)勢在于以下幾點：6.1視圖的創(chuàng)建15基于單張表的數(shù)據(jù)查詢而創(chuàng)建的視圖稱為單表視圖的創(chuàng)建。6.1.1單表視圖的創(chuàng)建示例：創(chuàng)建成功一個名為view_loan_userinfo的視圖。A部門只看到的客戶字段信息為姓名、性別和年齡，B部門只看到的客戶字段信息為姓名、學(xué)歷和收入，C部門只看到的是地區(qū)為Florida且客戶字段信息為姓名、是否有房產(chǎn)和是否有車。根據(jù)ABC部門不同的需求來創(chuàng)建不同的視圖。6.1視圖的創(chuàng)建16基于多張表的數(shù)據(jù)查詢而創(chuàng)建的視圖稱為多表視圖的創(chuàng)建。6.1.2多表視圖的創(chuàng)建示例：將貸款用戶信息表loan_userinfo與貸款業(yè)務(wù)明細表loan_details兩張表作為源表來創(chuàng)建一張視圖，創(chuàng)建完成后的視圖字段包括custID、custName、income、orderDate、prodName、amount。創(chuàng)建語法如下所示：執(zhí)行完上段SQL腳本，數(shù)據(jù)庫中就會創(chuàng)建成功一張多表視圖，通過關(guān)鍵字DESC可以查看視圖結(jié)構(gòu)。6.2視圖的刪除17視圖的刪除是通過關(guān)鍵字DROPVIEW來實現(xiàn)的，既可以一次刪除一張視圖，也可以一次刪除多張視圖。刪除視圖的語法如下所示：示例：一次刪除一張視圖view_loan_userinfo和一次刪除三張視圖view_loan_userinfo_a、view_loan_userinfo_b、view_loan_userinfo_c。6.3視圖的修改18視圖的修改可以分為兩大類，一類是針對創(chuàng)建視圖SQL的修改，通過修改可以改變整個視圖的查詢內(nèi)容，一類是針對視圖內(nèi)容的增刪改，通過對視圖的增刪改實現(xiàn)對源表數(shù)據(jù)的修改。語法如下所示：示例：將之前創(chuàng)建成功的視圖view_loan_userinfo的查詢SQL進行修改，修改后的視圖可以查詢的字段信息為姓名、性別、年齡、學(xué)歷和年收入。6.4視圖的查詢19視圖的查詢方式和表的查詢方式是一致的，針對視圖進行數(shù)據(jù)查詢時，其實是執(zhí)行的存儲在視圖中的SQL腳本。視圖的查詢可以分為：單視圖