MySQL數據庫應用與維護項目式教程（微課版） 課件 項目7 MySQL與SQL

MySQL與SQL項目7【能力目標】

掌握MySQL的基礎用?法。掌握MySQL中的數據類?型。熟悉MySQL中的運算符與表達?式。理解MySQL中的常見內置函數及其功?能?！舅仞B(yǎng)目標】培養(yǎng)精益求精的工匠精神，建立規(guī)范編寫程序代碼的意?識。目標學習導航圖7-1項目7所講內容在數據庫系統(tǒng)開發(fā)中的位置認識SQL7.17.1.1SQL簡介

SQL是一種由美國國家標準協(xié)會（AmericanNationalStandardInstitute，ANSI）規(guī)范的標準計算機語言，用于訪問和處理數據庫系統(tǒng)。

SQL具有面向數據庫執(zhí)行查詢、從數據庫取回數據、在數據庫中插入新的記錄、更新及刪除數據庫中的記錄、創(chuàng)建庫和表、在數據庫中創(chuàng)建存儲過程及視圖、設定表及視圖對象的權限等功能。不同類型的數據庫使用的SQL語句會略有不同，但都會遵循基本的標準SQL。7.1.2SQL的組成

SQL分為四大部分：數據定義語言、數據操縱語言、數據查詢語言和數據控制語?言。

1．數據定義語言

數據定義語言（DataDefinitionLanguage，DDL）的主要操作對象為數據庫、表、視圖、索引和觸發(fā)器等。最常用的語句如CREATE、ALTER、DROP等?！纠?-1】創(chuàng)建數據庫userTest，并在數據庫中創(chuàng)建數據表user。

執(zhí)行結果如圖7-2所?示。CREATEDATABASEuserTest;USEuserTest;CREATETABLEuser(uidINTNOTNULLPRIMARYKEY,usernameVARCHAR(10)NOTNULL);圖7-2在數據庫userTest中創(chuàng)建表user圖7-3修改表結構（增加一列）【例7-2】修改表user的表結構（增加一列）。ALTERTABLEuserADDpasswordCHAR(8)NOTNULL;DESCuser;執(zhí)行結果如圖7-3所?示。7.1.2SQL的組成2．數據操縱語言

數據操縱語言（DataManipulationLanguage，DML）的主要操作對象為數據（行），常見關鍵字有INSERT（插入數據）、UPDATE（更新數據）、DELETE（刪除數據）。【例7-3】在表user中插入一條數?據。INSERTINTOuserVALUES(1101,'JIM','020311');SELECT*FROMuser;執(zhí)行結果如圖7-4所?示。圖7-4使用DML的INSERT語句插入數據7.1.2SQL的組成2．數據操縱語言

數據操縱語言（DataManipulationLanguage，DML）的主要操作對象為數據（行），常見關鍵字有INSERT（插入數據）、UPDATE（更新數據）、DELETE（刪除數據）?！纠?-3】在表user中插入一條數?據。INSERTINTOuserVALUES(1101,'JIM','020311');SELECT*FROMuser;執(zhí)行結果如圖7-4所?示。圖7-4使用DML的INSERT語句插入數據7.1.2SQL的組成圖7-5使用DML的UPDATE語句更新數據【例7-4】將表user中uid為1101的用戶名改為WangMing。UPDATEuserSETusername='WangMing'WHEREuid=1101;SELECT*FROMuser;執(zhí)行結果如圖7-5所?示。7.1.2SQL的組成

3．數據查詢語言

數據查詢語言（DataQueryLanguage，DQL）一般指數據檢索語句，用來從表中獲取數據，確定數據在應用程序中的顯示方式。通常使用SELECT語句來查詢數據記錄，SELECT語句能夠進行單表查詢、連接查詢、嵌套查詢，以及集合查詢等各種不同復雜程度的數據庫查詢。4．數據控制語言

數據控制語言（DataControlLanguage，DCL）主要用于控制用戶的訪問權限，常用的有GRANT、REVOKE、COMMIT、ROLLBACK等語句。7.1.2SQL的組成認識常量和變量7.21．字符串常量字符串常量通常使用英文的單引號或雙引號引起來。如果字符串常量中含有換行符號、單引號、雙引號、“%”或“\”，就需要在符號前面加上轉義字符“\”。SELECT'\"hello\"\nworld\n\\Hi!';執(zhí)行結果如圖7-6所?示。7.2.1認識常量【例7-5】字符串常量的用法舉?例。圖7-6字符串常量的用法舉例【例7-6】數值常量用法舉?例。SELECT12+20+23.5-8.7+3;圖7-7數值常量用法舉例7.2.1認識常量2．數值常量

數值常量分為整數常量和浮點數常量，它通常與算術運算符結合使?用。執(zhí)行結果如圖7-7所?示?！纠?-7】布爾常量用法舉?例。SELECTTRUE,FALSE,120<80;圖7-8布爾常量用法舉例7.2.1認識常量3．布爾常量布爾常量的取值有TRUE和FALSE，在SQL中使用數值1和0表示。通常布爾常量與比較運算符或邏輯運算符配合使?用。執(zhí)行結果如圖7-8所?示。SET@變量名=值;7.2.2認識變量

1．用戶自定義變量

用戶自定義變量是由用戶自己定義的變量，可以暫存值，并傳遞給同一連接中的下一條SQL語句使用的變量，它的作用域為當前會話（客戶端連接）。

用戶自定義變量的聲明通常以@開始，用SET關鍵字給變量賦值。變量的聲明和初始化格式如?下。MySQL中的變量類似于動態(tài)語言，在賦值時，會根據具體的值來確定變量的數據類型。也就是說，int、string類型的值都可以賦給同一個變量。MySQL將未分配值的變量默認設為NULL，類型為字符?串。SET@name='Kate';SELECT@name;7.2.2認識變量1．用戶自定義變量

對用戶變量賦值的方式有兩種，一種是直接用“=”，另一種是用“:=”。使用SET對用戶變量進行賦值時，兩種方式都可以使用；使用SELECT語句對用戶變量進行賦值時，只能使用“:=”，因為在SELECT語句中，“=”會被看作比較運算?符?！纠?-8】創(chuàng)建一個用戶變量并查詢其?值。執(zhí)行結果如圖7-9所?示。圖7-9布爾常量用法舉例【例7-9】將【例7-8】中定義的用戶變量插入表user中，并進行查?詢。

INSERTINTOuserVALUES(1102,@name,'223311');SELECT*FROMuser;

執(zhí)行結果如圖7-10所示。

7.2.2認識變量圖7-10使用定義的用戶變量7.2.2認識變量2．系統(tǒng)變量MySQL服務器維護兩種變量，分別是全局系統(tǒng)變量和會話系統(tǒng)變量。（1）全局系統(tǒng)變量

全局系統(tǒng)變量針對所有默認設置。全局系統(tǒng)變量在MySQL啟動時，由服務器自動將它們初始化為默認值，這些默認值可以通過my.ini文件更?改。

（2）會話系統(tǒng)變量

會話系統(tǒng)變量針對當前用戶。用戶登錄MySQL會使用全局系統(tǒng)變量，如果會話中更改了變量值，會使用更改后的值，不過只對當前用戶有效。

【例7-11】修改系統(tǒng)變量character_set_client為gbk字符?集。SET@@session.character_set_client='gbk';SELECT@@character_set_client;

執(zhí)行結果如圖7-12所示。圖7-12修改系統(tǒng)變量character_set_client為gbk字符集在MySQL中，有一部分系統(tǒng)變量的值是不可以修改的，例如VERSION和系統(tǒng)日期?？梢孕薷牡南到y(tǒng)變量可通過SET語句進行修改，語法格式如?下。

SET@@[GLOBAL.|SESSION.]系統(tǒng)變量名=EXPR7.2.2認識變量在MySQL中查看系統(tǒng)變量最常用的方法是使用SHOWvariables語句，使用SHOWglobalvariables語句可以顯示所有全局系統(tǒng)變量，使用SHOWsessionvariables語句可以顯示會話系統(tǒng)變量?？梢赃\用通配符%實現系統(tǒng)變量的模糊查?詢?！纠?-12】運用通配符查看所有以char開頭的系統(tǒng)變?量。SHOWvariablesLIKE'char%';7.2.2認識變量

執(zhí)行結果如圖7-13所示。

圖7-13查看所有以char開頭的系統(tǒng)變量認識MySQL的數據類型7.3MySQL支持的5種整數類型按存儲空間由小到大分別為：TINYINT、SAMLLINT、MEDIUMINT、INT（INTEGER）和BIGINT，它們表示的數據范圍如表7-1所?示。7.3.1認識整數類型整數類型字節(jié)數有符號數無符號數TINYINT1(-128,127)(0,255)SMALLINT2(-32768,32767)(0,65535)MEDIUMINT3(-8388608,8388607)(0,16777215)INT（INTEGER）4(-231,231-1)(0,232-1)BIGINT8(-263,263-1)(0,264-1)表7-1整數類型表示的數據范圍【例7-13】創(chuàng)建一個表test_int，包括int_i、int_u兩個字段。創(chuàng)建完成后插入兩條記錄并查看插入效?果。CREATETABLEtest_int(int_iINT(8),int_uINTUNSIGNED);INSERTINTOtest_intVALUES(1000,1322);INSERTINTOtest_intVALUES(-1000,-1322);執(zhí)行結果如圖7-14所?示。圖7-14整數類型的應用7.3.1認識整數類型

根據表7-1可知，插入第二條記錄出錯是因為數據?1322超出了無符號整數類型數據表示的數據范?圍。

7.3.2認識浮點數類型

浮點數主要用于表示實數（帶有小數點的數值），通常采用M（尾數）×B（基數）的E（指數）次方形式表示。MySQL中的浮點數類型主要包括FLOAT（單精度浮點數）、DOUBLE（雙精度浮點數）。在數據的正負問題上，浮點數與整數類型數據類似，也分為有符號數及無符號數，并使用UNSIGNED修飾符標識無符號浮點數（無負數）。MySQL中浮點數類型表示的數據范圍如表7-2所?示。表7-2浮點數類型表示的數據范圍浮點數類型字節(jié)數有符號數無符號數表示數據FLOAT4(-3.402823466E+38,-1.175494351E-38)、0、(1.175494351E-38,3.402823466351E+38)0、(1.175494351E-38,3.402823466E+38)單精度浮點數DOUBLE8（-1.7976931348623157E+308,-2.2250738585072014E-308）、0、(2.2250738585072014E-308,1.7976931348623157E+308)0、(2.2250738585072014E-308,1.7976931348623157E+308)雙精度浮點數

在MySQL中，單精度與雙精度浮點數的定義形式分別表示為：FLOAT(M,D)、DOUBLEPRECISION(M，D)。其中“(M,D)”的M表示該值的總共位數，D表示小數點后的位數。【例7-14】創(chuàng)建表test_float，包括ff1和ff2兩個字段，然后向表中插入一條記?錄。CREATETABLEtest_float(ff1FLOAT,ff2FLOAT);INSERTINTOtest_floatVALUES(1.1111,11111159);SELECT*FROMtest_float;

7.3.2認識浮點數類型執(zhí)行結果如圖7-15所示。

通過【例7-14】可以看出，浮點數類型的數據一旦超過規(guī)定的位數，如FLOAT的7位，則其表示的數據就是不確定的了，所以應用時需要特別注?意。圖7-15浮點數類型應用MySQL在處理日期/時間類型的數據時提供了3種數據類型：日期類型、時間類型、混合日期。根據要求的日期/時間精度不同，數據類型可設置為DATE、TIME、DATETIME、TIMESTAMP和YEAR中的某一種，不同日期/時間類型能夠表示的數據及字節(jié)數、日期格式、取值范圍不同，如表7-3所?示。

7.3.3認識日期/時間類型表7-3日期/時間類型的數據范圍類型字節(jié)數日期格式最小值最大值表示數據DATE3YYYY-MM-DD1000-01-019999-12-31日期TIME3HH:MM:SS-838:59:59838:59:59時間DATETIME8YYYY-MM-DDHH:MM:SS1000-01-0100:00:009999-12-3123:59:59混合日期時間TIMESTAMP8YYYY-MM-DDHH:MM:SS1970-01-0100:00:002037-12-3123:59:59混合日期時間YEAR1YYYY19012155年份值【例7-15】創(chuàng)建表test_Date，包括d、t、y、dt、ts共5個字段，然后插入兩條記?錄。CREATETABLEtest_Date(dDATE,tTIME,yYEAR,dtDATETIME,tsTIMESTAMP);INSERTINTOtest_DateVALUES('2020-02-05','15:48:34','2020','2020-02-0515:48:34','2020-02-0515:48:34');INSERTINTOtest_DateVALUES('202002-05','15:78:34','20','2020-02-051548

34','2020-02-0515:48:34');7.3.3認識日期/時間類型執(zhí)行結果如圖7-16所示。

通過【例7-15】可以看出，第一條插入語句采用標準日期/時間格式，可以成功實現操作。第二條記錄中的日期格式不符合要求，所以出現了語法錯?誤。圖7-16日期/時間類型應用7.3.3認識日期/時間類型MySQL提供多種不同的字符串數據類型，如CHAR和VARCHAR類型、BLOB和TEXT類型等。運用它們可以使存儲的數據范圍從簡單的一個字符擴大到巨大的文本塊或二進制字符串，具體的數據表示情況如表7-4所示。

7.3.4認識字符串和二進制類型表7-4字符串和二進制數據表示情況類型字節(jié)數表示數據CHAR0~255字節(jié)定長字符串VARCHAR0~65535字節(jié)變長字符串BLOB0~65535字節(jié)二進制形式的長文本數據TEXT0~65535字節(jié)長文本數據TINYBLOB0~255字符不超過255個字符的二進制字符串TINYTEXT0~255字節(jié)短文本字符串MEDIUMBLOB0~16777215字節(jié)二進制形式的中等長度文本數據MEDIUMTEXT0~16777215字節(jié)中等長度文本數據LONGBLOB0~4294967295字節(jié)二進制形式的極大文本數據LONGTEXT0~4294967295字節(jié)極大文本數據CREATETABLEtest_char(cCHAR(4),

vcVARCHAR(4));INSERTINTOtest_charVALUES('ab','AA');SELECTLENGTH(c),LENGTH(vc)FROMtest_char;1．CHAR和VARCHAR類型

CHAR類型用于定義定長字符串，使用時必須在其后加上圓括號，且括號內用一個大小修飾符來指定字符串長度，其中大小修飾符的取值范圍是0~255。

VARCHAR類型是CHAR類型的一個變體，被稱為可變長度的字符串類型。VARCHAR類型的用法與CHAR相同，但表示的數據長度為0~65535字節(jié)。【例7-16】創(chuàng)建表test_char，包含c和vc兩個字段，這兩個字段均定義長度為4個字符，在該表中插入一條記錄并查看兩個字段值的長?度。7.3.4認識字符串和二進制類型執(zhí)行結果如圖7-17所?示。圖7-17CHAR與VARCHAR類型應用

可見，CHAR和VARCHAR類型最大的不同之處在于MySQL數據庫處理長度指示器的方法：CHAR類型把長度指示器的值直接視為字符串的長度大小，在長度不足的情況下用空格補足；而VARCHAR類型則將長度指示器的值看作可表示字符串的最大長度值，并且只使用存儲字符串實際需要的長度來存儲對應值。7.3.4認識字符串和二進制類型2．BLOB和TEXT類型

為了保存大文本數據塊或二進制的大對象，MySQL提供了TEXT和BLOB兩種類型。根據存儲數據內容的大小不同，二者都設置有不同的子類型。大型的數據用于存儲文本塊、圖像和聲音文件等二進制數據類?型。

BLOB是一個二進制大對象，可以容納可變數量的數據。TEXT被視為字符字符串，類似于CHAR和VARCHAR。BLOB類型區(qū)分大小寫，而TEXT類型不區(qū)分大小寫。7.3.4認識字符串和二進制類型認識運算符與表達式7.4

在MySQL中，算術運算符是最常見、最簡單易用的一種運算符。它主要面向數值類型數據的計算。算術運算符包括加（+）、減（-）、乘（*）、除（/）、取模（%）。1．加法運算符（+）用于求兩個或多個數值之和。如果整數數據與浮點數數據進行運算，則結果將自動轉換為浮點數數?據。SELECT24+3.14+2,18+22;執(zhí)行結果如圖7-18所?示。7.4.1認識算術運算符【例7-17】計算24+3.14+2和18+22的結?果。圖7-18加法運算的結果

2．減法運算符（-）用于求一個值與另一個值的差，運算后可能會改變操作數的符號。減法運算符作為一元運算符時，用于更改操作數的符號（即取負值）。SELECT4.7-5.2,7.15-3.14,26-18;7.4.1認識算術運算符【例7-18】計算4.7-5.2、7.15-3.14和26-18的結?果?！纠?-19】運用【例7-13】表test_int中的無符號數int_u參與減法運?算。SELECTint_u-1000FROMtest_int;SELECTint_u-1350FROMtest_int;對于加、減法運算，如果執(zhí)行的是無符號數據的運算，則結果默認為無符號數，此時若運算結果出現負值，就會報錯。注意3.乘法運算符（*）用于獲得兩個數值的乘積。運算結果的符號與參與運算的所有操作數符號相?關?！纠?-20】計算3*10、0.6*15和-12.5*2的結?果。SELECT3*10,0.6*15,-12.5*2;7.4.1認識算術運算符圖7-21乘法運算的結果4.除法運算符（/）用于計算一個值除以另一個值的商。除法運算的結果使用浮點數表?示。針對除數為0的情況，MySQL會返回結果NULL?！纠?-20】計算18/3、21.0/7和5/0的結?果。SELECT18/3,21.0/7,5/0;圖7-22除法運算的結果5．模運算符（%）用于計算一個值除以另一個值的余數。模運算符的功能與MOD()函數相?同?！纠?-22】計算17%5、10%3、5%-3和-2%4的結?果。SELECT17%5,10%3,5%-3,-2%4;7.4.1認識算術運算符圖7-18加法運算的結果

在算術運算中，如果出現字符串參與運算的情況，則字符串表示的數字會自動地轉換為數字。如果字符串的第一個字符是數字，那么它就轉換為這個數字的值，否則字符串將被轉換為?零。【例7-23】計算'91BQ'+'5'、'BQ91'+2和'8y'×5×'wxz'的結?果。SELECT'91BQ'+'5','BQ91'+2,'8y'*5*'wxz';圖7-24字符串參與算術運算的結果

MySQL中，運用SELECT語句進行查詢時，允許用戶對表達式的左邊操作數與右邊操作數進行比較運算，如果比較運算結果為真，則返回1，否則返回0。比較運算結果不確定時，返回NULL。MySQL中對NULL值的條件判斷，使用ISNULL（為空）與ISNOTNULL（不為空）專用運算符表?示。

比較運算符主要包括等于（=）、全等于（<=>）、不等于（!=或<>）、小于（<）、小于等于（<=）、大于（>）、大于等于（>=）以及BETWEEN…AND、LIKE等。7.4.2認識比較運算符

1．“=”和“<=>”運算符“=”運算符用于比較運算符左、右兩側的操作數是否相等。如果運算符兩側的操作數相等，則返回1，否則返回0。需要注意的是，NULL不能用“=”運算符進行比較?！?lt;=>”和“=”運算符類似，當運算符兩側的操作數相同時返回1，“<=>”運算符可以用來比較NULL?！纠?-24】求'AA'='aa'、5.12=4和'5q'=5的比較結?果。SELECT'AA'='aa',5.12=4,'5q'=5;執(zhí)行結果如圖7-25所?示。7.4.2

認識比較運算符圖7-25“=”比較運算的結果通過【例7-24】可以看到，在MySQL中如果無特別說明，則字符串的比較是不區(qū)分大小寫的，所以“AA”與“aa”是相等的。同時在比較運算中，與數值進行比較的字符串會自動轉換為數?字。3.“<>”與“!=”運算符都表示不等于，即如果運算符兩側的操作數不等，則返回1，否則返回0。NULL也不能使用“<>”運算符來比?較?！纠?-25】求't'<>'L'、15<>20和1<>1.0的結?果。SELECT't'<>'L',15<>20,1<>1.0;圖7-26“<>”比較運算的結果4．當運算符“<”左側的操作數小于右側的操作數時返回1，否則返回0。當運算符“<=”左側的操作數小于等于右側的操作數時返回1，否則返回0。【例7-26】計算'APPLE'<'PEAR'、15<10、-1<1.6和14<=14.0的結果。運算符按照ASCⅡ碼值比較字符?串。SELECT'APPLE'<'PEAR',15<10,-1<1.6,14<=14.0;圖7-27“<”與“<=”比較運算的結果7.4.2

認識比較運算符

4．“>”與“>=”運算符

當運算符“>”左側的操作數大于右側的操作數時返回1，否則返回0。當運算符“>=”左側的操作數大于等于右側的操作數時返回1，否則返回0。【例7-27】計算'TEA'>'PEAR'、18>10、-1>1.6和3.14>=1.40的結果。運算符按照ASCⅡ碼值比較字符?串。SELECT'TEA'>'PEAR',18>10,-1>1.6,3.14>=1.40;

執(zhí)行結果如圖7-28所?示。7.4.2

認識比較運算符圖7-28“>”與“>=”比較運算的結果5．“BETWEEN…AND”運算符“BETWEEN…AND”運算符用于范圍查詢，需要設置“條件1”和“條件2”兩個參數，即范圍的起始值和終止值。其基本語法格式如?下。BETWEEN條件1AND條件2

【例7-28】在數據庫ssms中查詢表elective中成績在60~70分的學生的學?號。7.4.2

認識比較運算符

需要注意，條件1對應的值要小于等于條件2的值。如果表示數據不在某個范圍內，則可以使用NOTBETWEEN…AND。USEssms;SELECTS_IDFROMelectiveWHEREGradeBETWEEN60AND70;6．“LIKE”運算符模糊匹配

根據SQL標準，“LIKE”運算符在每個字符的基礎上執(zhí)行匹配，一般會與通配符“%”（匹配0個或多個字符）和“_”（匹配任意單個字符）搭配使用。還可以使用NOTLIKE運算符來表示不匹?配?！纠?-29】在數據庫ssms中查詢表student中姓王的學生的信?息。SELECTS_ID,Name,Major,SexFROMstudentWHERENameLIKE'王%';

執(zhí)行結果如圖7-30所?示。7.4.2

認識比較運算符圖7-30“LIKE”運算符的應用1

【例7-30】在數據庫ssms中查詢表student中學號倒數第二位為“4”的學生的信?息。SELECTS_ID,Name,Major,SexFROMstudentWHERES_IDLIKE'%4_';

執(zhí)行結果如圖7-31所?示。7.4.2

認識比較運算符圖7-31“LIKE”運算符的應用2注意

運用“LIKE”運算符時，如果要查詢的內容中包含通配符“%”或“_”，則需要使用轉義字符進行匹配查詢，“\%”表示匹配一個“%”，“\_”表示匹配一個“_”。

邏輯運算符也稱為布爾運算符，用來確認條件表達式邏輯結果的真（TRUE）或假（FALSE）。MySQL支持4種類型的邏輯運算符，包括邏輯與運算符（AND或&&）、邏輯或運算符（OR或||）、邏輯非運算符（NOT或!）、邏輯異或運算符（XOR或^）。1．邏輯與運算

邏輯與運算的語法規(guī)則為當所有操作數均為非零值并且不為NULL時，計算所得結果為1；當一個或多個操作數為0時，所得結果為0；操作數中有一個操作數為NULL，則返回結果為NULL。【例7-31】查詢表student中姓王且性別為男的學生的信?息。SELECTS_ID,Name,Major,SexFROMstudentWHERENameLIKE'王%'ANDSex=1;圖7-32“AND”運算符的應用7.4.3認識邏輯運算符2．邏輯或運算

邏輯或運算的語法規(guī)則為當兩個操作數均為NOTNULL時，如果有任意一個操作數為非零值，則結果為1，否則為0；當操作數有一個為NULL時，如果另一個操作數為非0值，則結果為1，否則為NULL；如果兩個操作數均為NULL，那么所得結果也為NULL?！纠?-32】查詢表student中姓王的學生或總學分在50分以上的學生的信?息。SELECTS_ID,Name,Major,Sex,Total_CreditFROMstudentWHERENameLIKE'王%'ORTotal_Credit>50;圖7-33“OR”運算符的應用7.4.3認識邏輯運算符3．邏輯非運算

邏輯非運算的語法規(guī)則為返回與操作數相反的結果。當操作數為0時，結果為1，否則結果為0。注意，進行非運算的操作數如果為NOTNULL，則返回值為NULL?！纠?-33】計算!(35<20)and(15>10)的結?果。SELECT!(35<20)and(15>10);7.4.3認識邏輯運算符4.邏輯異或運算

邏輯異或運算的語法規(guī)則為當任意一個操作數為NULL時，返回值為NULL。對于為NOTNULL的操作數，如果兩者的邏輯值相異，即其中一個邏輯值為真且不為NULL，另一個邏輯值為假，則返回結果為1，否則返回結果為0?！纠?-34】計算(3<5)XOR(2>6)的結?果。SELECT(3<5)XOR(2>6);

位運算符主要是指對二進制位的邏輯運算。位運算通常將給定參與運算的操作數轉化為二進制數后，對各個操作數的每一位（bit）進行指定的邏輯運算，最后將所得的二進制結果轉換為十進制數作為最終結果。位運算符主要包括按位與（&）、按位或（|）、按位異或（^）、按位取反（~）、按位移位（“>>”按位右移和“<<”按位左移）運算1．按位與運算（&）按位與運算表示對多個操作數的二進制數進行按位邏輯與操?作?！纠?-35】計算5&6的結?果。SELECT5&6;7.4.4認識位運算符2．按位或運算（|）按位或運算表示對多個操作數的二進制數進行按位邏輯或操?作。【例7-36】計算25|32的結?果。SELECT25|32;3．按位異或運算（^）

按位異或運算表示對多個操作數的二進制數按位進行邏輯異或操?作?！纠?-37】計算7^9的結?果。SELECT7^9;圖7-38“^”運算符的應用4．按位取反運算（~）

按位取反運算表示對操作數對應的二進制數進行按位取反操作。按位取反原來存放0的位，取反后取值均變?yōu)??！纠?-38】計算~16的結?果。SELECT~16;圖7-39“~”運算符的應用7.4.4認識位運算符5．移位運算

移位運算符“>>”表示按位右移，即將左操作數向右移動右操作數指定的位?數?！?lt;<”表示按位左移，即將左操作數向左移動右操作數指定的位?數。【例7-39】計算4>>2的結?果。SELECT4>>2;7.4.4認識位運算符【例7-40】計算7<<2的結?果。SELECT7<<2;右移位中將7轉換為二進制數，得到000…0111（共64位），然后將數據左移兩位，得到000…11100（共64位），其對應的十進制數為28。注意7.4.5認識運算符優(yōu)先級

如果表達式中包含了多個運算符，則優(yōu)先級高的運算符先運算，如果運算符的優(yōu)先級相同，則MySQL通常會按照表達式中運算符出現的順序，從左到右依次進行運算（賦值運算符除外）。運算符的優(yōu)先級由低到高的順序如表7-9所?示。可以使用圓括號“()”，括號內的運算符具有最高的運算優(yōu)先級。表7-9運算符的優(yōu)先級（由低到高）優(yōu)先級(由低到高)運算符1=（賦值）、:=2||、OR、XOR3&&、AND4NOT5BETWEEN、CASE、WHEN、THEN和ELSE6=、<=>、>=、>、<=、<、<>、!=、IS、LIKE、REGEXP和IN7|8&9<<和>>10?和+11*、/、%12^（按位異或）13～（一元，取負）和~（按位取反）14!15()

7.4.6認識表達式

表達式通常是由常量、變量、列名、運算符和函數組合而成的。通過運算，一個表達式最終都可以得到一個確定的運算結果值。表達式的結果值取決于參與運算的相關數據的數據類?型。

根據表達式的組成與表達式的運算結果可以對表達式進行分類。根據表達式值的不同數據類型，可以將表達式分類為字符表達式、數值表達式或者日期表達式。根據表達式運算結果的不同，可以將表達式分為標量表達式（運算結果為單個值）、行表達式（運算結果為表的一行）、表表達式（結果為一個或多個行表達式）。

認識系統(tǒng)內置函數7.5

數學函數用于完成復雜的算術操作及專門的數學運算，數學函數應用中一旦出現錯誤操作，其返回值通常為NULL。表7-10所示是一些常見的數學函?數。表7-10常見的數學函數7.5.1使用數學函數函數名稱描述ABS()返回絕對值ROUND()返回整數部分或保留部分小數位TRUNCATE()截斷為指定的小數位數RAND()返回一個隨機浮點值SIGN()返回參數的符號SQRT()返回參數的平方根PI()返回圓周率POW()將參數提高到指定的冪1．ABS(x)

ABS(x)的作用是返回某數的絕對值，可用于二者間差距的計?算。【例7-41】在數據庫ssms的表elective中查詢與學號為201101的學生的課程101成績相差不超過5分的學生的學?號。SELECTS_IDFROMelectiveWHEREABS(Grade-(SELECTGradeFROMelectiveWHERES_ID='201101'AND

C_ID='101'))<=5;7.5.1使用數學函數2．ROUND(x,d)

ROUND(x,d)的返回值為離x最近的整數（四舍五入）并保留d位小數，如果不指定d的值，則d默認為0，即ROUND(x)返回的是一個整?數?！纠?-42】計算ROUND(150.157,2)、ROUND(150,2)和ROUND(3.52)的結?果。SELECTROUND(150.157,2),ROUND(150,2),ROUND(3.52);3.TRUNCATE(x,d)

TRUNCATE(x,d)的返回值為x并保留d位小數?！纠?-43】計算TRUNCATE(76.28,1)、TRUNCATE(26.79,-2)和TRUNCATE(3.14,0)的結?果。SELECTTRUNCATE(76.28,1),TRUNCATE(26.79,?2),TRUNCATE(3.14,0);7.5.1使用數學函數4．RAND()

RAND()函數返回的結果為0~1的隨機浮點數。要取得某個范圍內的隨機數可以使用表達式實?現?！纠?-44】運用RAND()函數產生一個0~100的隨機?數。SELECTRAND()*100;5．SIGN(x)SIGN(x)的作用是返回參數x的符號標識。當x為負數時，返回值為-1；當x為正數時，返回值為1；當x取值為0時，返回值為0?！纠?-45】求SIGN(13)、SIGN(-212)和SIGN(0)的返回結?果。SELECTSIGN(13),SIGN(-212),SIGN(0);7.5.1使用數學函數6．SQRT(x)的作用是返回x的平方根，用于數學中開平方的求值運?算?！纠?-46】計算SQRT(144)的結?果。SELECTSQRT(144);7．PI()函數用于返回圓周率，返回結果默認顯示的小數位數為6位，但實際在MySQL內部使用的是完整的雙精度?值?！纠?-47】計算半徑為10的圓的面?積。SELECTPI()*10*10;7.5.1使用數學函數8．POW(x,y)用于返回x的y次方，其功能與POWER(x,y)相?同?！纠?-48】計算2的10次?方。SELECTPOW(2,10);

MySQL為便于完成字符串的相關操作，提供了多種字符串函數，包括獲取字符串長度的函數、大小寫轉換的函數、連接字符串的函數、刪除空格的函數等。本任務介紹幾個常見的字符串函數，如表7-11所?示。表7-11常見的字符串函數7.5.2使用字符串函數函數名稱描述CHAR_LENGTH()返回參數中的字符數CONCAT()返回串聯(lián)的字符串UPPER()轉換為大寫LOWER()轉換為小寫LPAD()、RPAD()返回字符串參數，用指定的字符串左填充或右填充LEFT()、RIGHT()返回指定的最左邊的或最右邊的字符LTRIM()、RTRIM()、TRIM()刪除前導、尾部、首尾空格REPLACE()替換出現的指定字符串STRCMP()比較兩個字符串SUBSTRING()、MID()返回指定的子字符串1．CHAR_LENGTH(str)的作用為返回字符串str包含的字符數，注意與LENGTH()函數區(qū)分。LENGTH()的作用為返回字符串str占用的空間大小，多字節(jié)字符算作單個字符?！纠?-49】查看字符串“ILOVECHINA!”包含的字符?數。SELECTCHAR_LENGTH('ILOVECHINA!');7.5.2

使用字符串函數CHAR_LENGTH()函數的應用2．CONCAT()函數返回參數列表中的字符串連接產生的新字符串，該函數中的參數可能有一個或多個。如果所有參數的值都為非二進制字符串，則結果為非二進制字符串。如果參數包含任何二進制字符串，則結果為二進制字符串。數字參數會被轉化為與之相等的二進制字符串格式。如果CONCAT()函數的返回值為NULL，則表示參數列表包含NULL?！纠?-50】在數據庫ssms的表student中查詢學號為201101的學生的學號、姓名、性別，并將這些信息連接成一個字符串顯?示。USEssms;SELECTCONCAT('學號：',S_ID,',姓名:',Name,',性別:',Sex)FROMstudentWHERE

S_ID='201101';7.5.2

使用字符串函數CONCAT()函數的應用3．UPPER(str)UPPER(str)的作用是返回str根據當前字符集映射將所有字符更改為大寫的字符串，在MySQL8.0中函數默認的字符集為utf8mb4?！纠?-51】將字符串“character_set”中的字母變?yōu)榇?寫。SELECTUPPER('character_set');4．LOWER(str)LOWER(str)的作用是返回str根據當前字符集映射將所有字符更改為小寫的字符串，同UPPER()一樣，其默認字符集也為utf8mb4?！纠?-52】將字符串“Let’sLearnMySQL8.0”中的字母轉換為小?寫。SELECTLOWER('Let\'sLearnMySQL8.0');7.5.2

使用字符串函數5．LPAD(str,len,padstr)與RPAD(str,len,padstr)LPAD(str,len,padstr)返回的字符串為在原字符串str的左邊填充padstr，直到字符長度達到len。與LPAD(str,len,padstr)相對應的是，RPAD(str,len,padstr)返回的字符串為在原字符串str的右邊填充字符padstr直到字符串長度為len。但無論是LPAD()還是RPAD()函數，如果參數列表中的len取值小于str對應的長度，則返回值輸出的結果為str的前l(fā)en個字?符?！纠?-53】應用LPAD()與RPAD()函數進行字符串的左填充和右填?充。SELECTLPAD('version',9,'@'),RPAD('1',4,'0');7.5.2

使用字符串函數LPAD()與RPAD()函數的應用6．LEFT(str,len)與RIGHT(str,len)LEFT(str,len)函數的返回值為字符串str最左邊的長度為len的子字符串，RIGHT(str，len)函數的返回值為str字符串最右邊的長度為len的子字符?串?！纠?-54】在數據庫ssms中運用LEFT()與RIGHT()函數將表student中王姓學生的姓氏與名字分開顯?示。SELECTLEFT(Name,1)AS姓氏,RIGHT(Name,CHAR_LENGTH(Name)-1)AS名字FROMstudentWHERENamelike'王%';7.5.2

使用字符串函數

LEFT()與RIGHT()函數的應用7．LTRIM(str)、RTRIM(str)與TRIM(str)LTRIM(str)可以刪除字符串str開始處的空格字符。RTRIM(str)刪除的是str結尾處的空格。TRIM(str)既可以刪除字符串str開始處的空格字符，也可以刪除結尾處的空格字符。但是需要注意，以上3個函數都無法刪除字符串str中間的空?格。【例7-55】運用LTRIM()、TRIM()、RTRIM()函數刪除字符串的空?格。SELECTLTRIM('Susan,seeyou!')ASL,RTRIM('Susan,seeyou!')ASR,TRIM('Susan,seeyou!')ASLR;7.5.2

使用字符串函數

LTRIM()、TRIM()、RTRIM()函數的應用8．REPLACE(str,from_str,to_str)REPLACE（str,from_str,to_str）的作用是將字符串str中所有出現的from_str字符串替換為字符串to_str。需要注意，搜索匹配from_str時會區(qū)分大小?寫?！纠?-56】運用REPLACE()函數將字符串中的空格替換為“_”。SELECTREPLACE('HANMeiMei','','_');7.5.2

使用字符串函數9．STRCMP(str1,str2)STRCMP(str1,str2)的返回結果為整數值。其中，如果兩個字符串相同，則函數返回結果為整數0；如果參數str1小于str2，則返回結果為-1；如果str1大于str2，則返回結果為1。參數列表中str1與str2的比較按照先字母后數字的順序進?行?！纠?-57】運用STRCMP()函數比較字符串“aPPLE”與“BANANA”。SELECTSTRCMP('aPPLE','BANANA');10．SUBSTRING(str,pos)、SUBSTRING(str,pos,len)與MID(str,pos,len)SUBSTRING(str,pos)返回一個從字符串str的pos位置開始的子字符串。帶len參數的SUBSTRING(str,pos,len)表示從字符串str的pos位置開始，返回一個長度為len的子字符串。在參數列表中，如果pos的值為0，則表示返回空字符串；若pos取負值，則表示從str的尾部開始取字?符。MID(str,pos,len)的功能與用法和SUBSTRING(str,pos,len)完全相?同?！纠?-58】運用SUBSTRING()函數將給定字符串包含的郵箱服務器地址截取出?來。SELECTSUBSTRING('wangxiaom***@',13,7);7.5.2

使用字符串函數

SUBSTRING()函數的應用

在MySQL中為了更好地實現日期與時間的表示、處理以及相關的轉換操作，系統(tǒng)提供了豐富的日期/時間函數。常見的日期/時間函數如表7-12所?示。表7-12常見的日期/時間函數7.5.3使用日期/時間函數名稱描述NOW()、CURRENT_TIMESTAMP()返回當前系統(tǒng)日期和時間CURDATE()、CURRENT_DATE()返回當前系統(tǒng)日期CURTIME()、CURRENT_TIME()返回當前系統(tǒng)時間DATE()提取日期或日期/時間表達式的日期部分TIME()提取傳遞的表達式的時間部分YEAR()返回年份MONTH()、MONTHNAME()返回日期的月份，返回月份名稱DAY()、DAYOFMONTH()返回月份中的一天（0~31）DAYNAME()返回工作日的名稱HOUR()返回小時MINUTE()返回分鐘SECOND()返回秒（0~59）WEEKDAY()返回工作日索引DAYOFYEAR()返回一年中的某天（1~366）WEEKOFYEAR()返回日期的日歷周（1~53）YEARWEEK()返回年和周DATE_ADD() 將時間值（間隔）添加到日期值DATE_SUB() 從日期中減去時間值（間隔）1．用于獲取當前系統(tǒng)日期/時間的函數用于獲取當前系統(tǒng)日期/時間的函數有NOW()、CURDATE()、CURRENT_DATE()、CURTIME()、CURRENT_TIME()?！纠?-59】運用NOW()和CURTIME()函數分別獲取當前的日期/時間及時?間。SELECTNOW(),CURTIME();7.5.3使用日期/時間函數NOW()與CURTIME()函數的應用2．用于獲得對應日期/時間中的年、月、日及時間的函數用于獲得對應日期/時間中的年、月、日及時間的函數有DATE()、TIME()、YEAR()、MONTH()、MONTHNAME()、DAY()、DAYOFMONTH()、DAYNAME()、HOUR()、MINUTE()、SECOND()?！纠?-60】在數據庫ssms中查詢與學號為201101的學生同年同月出生的學生的信?息。USEssms;SELECTS_ID,Name,Sex,BirthdayFROMstudentWHERE(YEAR(Birthday),MONTH(Birthday))=(SELECTYEAR(Birthday),MONTH(Birthday)FROMstudentWHERES_ID='201101');YEAR()與MONTH()函數的應用7.5.3使用日期/時間函數3．獲得給定日期年份與周、年份與天等信息的函數用于獲得給定日期年份與周、年份與天等信息的函數有WEEKDAY()、DAYOFYEAR()、WEEKOFYEAR()、YEARWEEK()。【例7-61】運用DAYOFYEAR()與WEEKOFYEAR()函數返回表student中學號為201101的學生的出生日期及對應的日歷?周。SELECTDAYOFYEAR(Birthday),WEEKOFYEAR(Birthday)FROMstudentWHERES_ID='201101';YEAR()與MONTH()函數的應用7.5.3使用日期/時間函數4．日期的計算函數

DATE_ADD(date,intervalexprunit)與DATE_SUB(date,intervalexprunit)函數用于執(zhí)行日期算術。參數列表中，date指定開始日期或日期/時間值，expr是一個表達式，用于指定要從開始日期添加或減去的間隔值。unit是一個關鍵字，指定表達式的單?位。函數的返回值類型取決于參數date的值，如果date參數是一個DATE類型的值，函數計算中只涉及YEAR、MONTH或DAY部分（即沒有時間部分），那么結果仍為DATE類型。如果第一個參數是一個DATETIME（或TIMESTAMP）類型的值，或者第一個參數date為DATE類型的值，而unit關鍵字的單位為HOURS、MINUTES或SECONDS，則返回值的類型為DATETIME型?！纠?-62】運用DATE_SUB()函數與DATE_ADD()函數計算2020年2月10日前45天和2023年2月10日后45天的日?期。SELECTDATE_SUB('2020-02-10',INTERVAL45day)ASbefore_45,DATE_ADD('2023-02-10',INTERVAL45day)ASafter_45;DATE_SUB()與DATE_ADD()函數的應用7.5.3使用日期/時間函數7.5.4使用聚合函數

MySQL中，聚合函數也稱為聚集函數，是一種對一組值進行操作的組（匯總）函數，返回結果通常為一個值。表7-13所示為常用的聚合函數。表7-13常用的聚合函數

函數名稱描述COUNT()、COUNT(DISTINCT)返回計數返回的行數SUM()返回總和AVG()返回參數的平均值MAX()返回最大值MIN()返回最小值GROUP_CONCAT()返回串聯(lián)的字符串BIT_AND()返回按位與BIT_OR()返回按位或BIT_XOR()返回按位異或【小結】

本項目對MySQL的組成、數據常量和變量、常見數據類型、運算符表達式及系統(tǒng)內置函數進行了系統(tǒng)闡述，同時以案例形式介紹了相關操作及用法。其中，MySQL中的數據類型、運算符及內置函數是本項目的重點內容，這些內容在查詢及數據庫編程中會有所應?用?！救蝿沼柧?】編寫MySQL語句1．實驗目的掌握MySQL中常量及變量的應?用。掌握并靈活運用運算符與表達?式。運用系統(tǒng)內置函數完成數據庫中的運算及查詢操?作。2．實驗內容運用