第6章 MySQL函數(shù)

6.1 MySQL函數(shù)簡介

6.2 數(shù)學函數(shù)

6.2.1 絕對值函數(shù)ABS(x)和返回圓周率的函數(shù)PI()

6.2.2 平方根函數(shù)SQRT(x)和求余函數(shù)MOD(x,y)

6.2.3 獲取整數(shù)的函數(shù) CEIL(x)、CEILING(x)和 FLOOR(x)

6.2.4 獲取隨機數(shù)的函數(shù)RAND()和RAND(x)

6.2.5 函數(shù) ROUND(x)、ROUND(x，y)和 TRUNCATE(x,y)

6.2.6 符號函數(shù)SIGN(x)

6.2.7 冪運算函數(shù) POW(x,y)、POWER(x,y)和 EXP(x)

6.2.8 對數(shù)運算函數(shù) LOG(x)和 LOGlO(x)

6.2.9 角度與弧度相互轉(zhuǎn)換的函數(shù)RADIANS(x)和DEGREES(x)

6.2.10 正弦函數(shù)SIN(x)和反正弦函數(shù)ASIN(x)

6.2.11 余弦函數(shù)COS(x)和反余弦函數(shù)ACOS(x)

6.2.12 正切函數(shù)、反正切函數(shù)和余切函數(shù)

6.3 字符串函數(shù)

6.3.1 計算字符串字符數(shù)的函數(shù)和字符串長度的函數(shù)

6.3.2 合并字符串函數(shù) CONCAT(sl，s2，...)、CONCAT_WS(x，sl，s2，...)

6.3.3 替換字符串的函數(shù) INSERT(sl，x，len，s2)

6.3.4 字母大小寫轉(zhuǎn)換函數(shù)

6.3.5 獲取指定長度的字符串的函數(shù)LEFT(s,n)和RIGHT(s,n)

6.3.6 填充字符串的函數(shù) LPAD(sl,len,s2)和 RPAD(sl,len,s2)

6.3.7 刪除空格的函數(shù)LTRIM(s)、RTRJM(s)和TRIM(s)

6.3.8 刪除指定字符串的函數(shù)TRIM(sl FROM s)

6.3.9 重復生成字符串的函數(shù)REPEAT(s,n)

6.3.10 空格函數(shù) SPACE(n)和替換函數(shù) REPLACE(s,sl,s2)

6.3.11 比較字符串大小的函數(shù)STRCMP(sl,s2)

6.3.12 獲取子串的函數(shù) SUBSTRING(s,n,len)和 MID(s,n,len)

6.3.13 匹配子串開始位置的函數(shù)

6.3.14 字符串逆序的函數(shù)REVERSE(s)

6.3.15 返回指定位置的字符串的函數(shù)

6.3.16 返回指定字符串位置的函數(shù)FIELD(s,sl,s2,...)

6.3.17 返回子串位置的函數(shù)FIND_IN_SET(sl,s2)

6.3.18 選取字符串的函數(shù)MAKE_SET(x,sl,s2,...)

6.4 日期和時間函數(shù)

6.4.1 獲取當前日期的函數(shù)和獲取當前時間的函數(shù)

6.4.2 獲取當前日期和時間的函數(shù)

6.4.3 UNIX時間戳函數(shù)

6.4.4 返回UTC日期的函數(shù)和返回UTC時間的函數(shù)

6.4.5 獲取月份的函數(shù) MONTH(date)和 MONTHNAME(date)

6.4.6 獲取星期的函數(shù) DAYNAME(d). DAYOFWEEK(d)和 WEEKDAY(d)

6.4.7 獲取星期數(shù)的函數(shù) WEEK(d)和 WEEKOFYEAR(d)

6.4.8 獲取天數(shù)的函數(shù) DAYOFYEAR(d)和 DAYOFMONTH(d)

6.4.9 獲取年份、季度、小時、分鐘和秒鐘的函數(shù)

6.4.10 獲取日期的指定值的函數(shù)EXTRACT(type FROM date)

6.4.11 時間和秒鐘轉(zhuǎn)換的函數(shù)

6.4.12 計算日期和時間的函數(shù)

6.4.13 將日期和時間格式化的函數(shù)

6.5 條件判斷函數(shù)

6.5.1 IF(expr，vl，v2)函數(shù)

6.5.2 IFNULL(vl,v2)函數(shù)

6.5.3 CASE 函數(shù)

6.6 系統(tǒng)信息函數(shù)

6.6.1 獲取MySQL版本號、連接數(shù)和數(shù)據(jù)庫名的函數(shù)

6.6.2 獲取用戶名的函數(shù)

6.6.3 獲取字符串的字符集和排序方式的函數(shù)

6.6.4 獲取最后一個自動生成的ID值的函數(shù)

6.7 加/解密函數(shù).

6.7.1 加密函數(shù) PASSWORD(str)

6.7.2 加密函數(shù) MD5(str)

6.7.3 加密函數(shù)ENCODE(str,pswd_str)

6.7.4 解密函數(shù) DECODE(crypt_str,pswd一str)

6.8 其他函數(shù)

6.8.1 格式化函數(shù)FORMAT(x,n)

6.8.2 不同進制的數(shù)字進行轉(zhuǎn)換的函數(shù)

6.8.3 IP地址與數(shù)字相互轉(zhuǎn)換的函數(shù)

6.8.4 加鎖函數(shù)和解鎖函數(shù)

6.8.5 重復執(zhí)行指定操作的函數(shù)

6.8.6 改變字符集的函數(shù)

6.8.7 改變數(shù)據(jù)類型的函數(shù)

6.9 綜合案例——MySQL函數(shù)的使用

6.10 專家解惑

6.11 經(jīng)典習題

第7章 查詢數(shù)據(jù)

7.1 基本査詢語句

7.2 單表查詢

7.2.1 查詢所有字段

7.2.2 査詢指定字段

7.2.3 査詢指定記錄

7.2.4 帶IN關(guān)鍵字的査詢

7.2.5 帶 BETWEEN AND 的范圍查詢

7.2.6 帶LIKE的字符匹配査詢

7.2.7 查詢空值

7.2.8 帶AND的多條件査詢

7.2.9 帶OR的多條件查詢

7.2.10 查詢結(jié)果不重復

7.2.11 對查詢結(jié)果排序

7.2.12 分組査詢

7.2.13 使用LIMIT限制査詢結(jié)果的數(shù)量

7.3 使用聚合函數(shù)查詢

7.3.1 COUNT()函數(shù)

7.3.2 SUM()函數(shù)

7.3.3 AVG()函數(shù)

7.3.4 MAX()函數(shù)

7.3.5 MIN()函數(shù)

7.4 連接査詢

7.4.1 內(nèi)連接查詢

7.4.2 外連接査詢

7.4.3 復合條件連接查詢

7.5 子査詢

7.5.1 帶ANY、SOME關(guān)鍵字的子查詢

7.5.2 帶ALL關(guān)鍵字的子查詢

7.5.3 帶EXISTS關(guān)鍵字的子查詢

7.5.4 帶IN關(guān)鍵字的子查詢

7.5.5 帶比較運算符的子查詢

7.6 合并查詢結(jié)果

7.7 為表和字段取別名

7.7.1 為表取別名

7.7.2 為字段取別名

7.8 使用正則表達式查詢

7.8.1 查詢以特定字符或字符串開頭的記錄

7.8.2 查詢以特定字符或字符串結(jié)尾的記錄

7.8.3 用符號來替代字符串中的任意一個字符

7.8.4 使用■"和"+"來匹配多個字符

7.8.5 匹配指定字符串

7.8.6 匹配指定字符中的任意一個

7.8.7 匹配指定字符以外的字符

7.8.8 使用{nJ或者{n，ni}來指定字符串連續(xù)出現(xiàn)的次數(shù)

7.9 綜合案例——數(shù)據(jù)表查詢操作

7.10 專家解惑

7.11 經(jīng)典習題

第8章 插入、更新與刪除數(shù)據(jù)

8.1 插入數(shù)據(jù)

8.1.1 為表的所有字段插入數(shù)據(jù)

8.1.2 為表的指定字段插入數(shù)據(jù)

8.1.3 同時插入多條記錄

8.1.4 將查詢結(jié)果插入到表中

8.2 更新數(shù)據(jù)

8.3 刪除數(shù)據(jù)

8.4 綜合案例——記錄的插入、更新和刪除

8.5 專家解惑

8.6 經(jīng)典習題

第6章 MySQL函數(shù)

6.1 MySQL函數(shù)簡介

函數(shù)表示對輸入?yún)?shù)值返回一個具有特定關(guān)系的值,MySQL提供了大量豐富的函數(shù),在進行數(shù)據(jù)庫管理以及數(shù)據(jù)的查詢和操作時將會經(jīng)常用到各種函數(shù).通過對數(shù)據(jù)的處理,數(shù)據(jù)庫功能可以變得更加強大,更加靈活地滿足不同用戶的需求.各類函數(shù)從功能方面主要分為以下幾類:數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)和加密函數(shù)等. 本章將分類介紹不同函數(shù)的使用方法.

6.2數(shù)學函數(shù)

數(shù)學函數(shù)主要用來處理數(shù)值數(shù)據(jù),主要的數(shù)學函數(shù)有:絕對值函數(shù)、三角函數(shù)(包括正弦 函數(shù)、余弦函數(shù)、正切函數(shù)、余切函數(shù)等)、對數(shù)函數(shù)、隨機數(shù)函數(shù)等.在有錯誤產(chǎn)生時,數(shù) 學函數(shù)將會返回空值NULL.本節(jié)將介紹各種數(shù)學函數(shù)的功能和用法.

6.2.1絕對值函數(shù)ABS(x)和返回圓周率的函數(shù)Pl()

ABS(X)返回X的絕對值.
PI()返回圓周率n的值.默認的顯示小數(shù)位數(shù)是6位.

6.2.2平方根函數(shù)SQRT(x)和求余函數(shù)MOD(x,y)

SQRT(x)返回非負數(shù)x的二次方根.
負數(shù)沒有平方根,因此-49返回的結(jié)果為NULL.
MOD(x,y)返回x被y除后的余數(shù),MOD()對于帶有小數(shù)部分的數(shù)值也起作用,它返回除法運算后的精確余數(shù).

6.2.3 獲取整數(shù)的函數(shù) CEIL(x)、CEILING(x)和 FLOOR(x)

CEIL(x)和CEILING(x)意義相同,返回不小于x的最小整數(shù)值,返回值轉(zhuǎn)化為一個BIGINT.
FLOOR(x)返回不大于x的最大整數(shù)值,返回值轉(zhuǎn)化為一個BIGINT.

6.2.4獲取隨機數(shù)的函數(shù)RANDOM RAND(x)

RAND(x)返回一個隨機浮點值v,范圍在0到1之間(即0 <= v <= 1.0).若已指定一個整數(shù)參數(shù)X,則它被用作種子值,用來產(chǎn)生重復序列.
注意點:

• 不帶參數(shù)的RAND()每次產(chǎn)生的隨機數(shù)值是不同的.
• 當RAND(x)的參數(shù)相同時,將產(chǎn)生相同的隨機數(shù),不同的x產(chǎn)生的隨機數(shù)值 不同.

6.2.5 函數(shù) ROUND(x)、ROUND(x,y)和 TRUNCATE(x.y)

ROUND(x)返回最接近于參數(shù)x的整數(shù),對x值進行四舍五入.四舍五入處理之后,只保留了各個值的整數(shù)部分.
ROUND(x,y)返回最接近于參數(shù)x的數(shù),其值保留到小數(shù)點后面y位,若y為負值,則將保留x值到小數(shù)點左邊y位.
y值為負數(shù)時,保留的小數(shù)點左邊的相應(yīng)位數(shù)直接保存為0,不進行四舍五入.
TRUNCATE(x,y)返回被舍去至小數(shù)點后y位的數(shù)字x.若y的值為0,則結(jié)果不帶有小數(shù)點或不帶有小數(shù)部分.若y設(shè)為負數(shù),則截去(歸零)x小數(shù)點左起第y位開始后面所有低位 的值.

6.2.6符號函數(shù)SIGN(x)

SIGN(x)返回參數(shù)的符號,x的值為負、零或正時返回結(jié)果依次為-1、0或1.

6.2.7 冪運算函數(shù) POW(x,y)、POWER(x,y)和 EXP(x)

POW(x,y)或者POWER(x,y)函數(shù)返回x的y次乘方的結(jié)果值.
EXP(x)返回e的x乘方后的值.

6.2.8對數(shù)運算函數(shù)LOG(x)和LOGIO(x)

LOG(x)返回x的自然對數(shù),x相對于基數(shù)e的對數(shù).
對數(shù)定義域不能為負數(shù),因此LOG(-3)返回結(jié)果為NULL.
LOG 10(x)返回x的基數(shù)為10的對數(shù).

6.2.9角度與弧度相互轉(zhuǎn)換的函數(shù)RADIANS(x)和DEGREES(x)

RADIANS(x)將參數(shù)x由角度轉(zhuǎn)化為弧度.
DEGREES(x)將參數(shù)x由弧度轉(zhuǎn)化為角度.

6.2.10 正弦函數(shù)SIN(x)和反正弦函數(shù)ASIN(x)

SIN(x)返回x正弦,其中x為弧度值.ASIN(x)返回x的反正弦,即正弦為x的值.若x不在-1到1的范圍之內(nèi),則返回NULLo
函數(shù)ASIN和SIN互為反函數(shù);ASIN(3)中的參數(shù)3超出了正弦值的范圍,因此返回NULL.

6.2.11余弦函數(shù)COS(x)和反余弦函數(shù)ACOS(x)

COS(x)返回X的余弦,其中X為弧度值.
可以看到,COS(0)值為 1; COS(PI())值為-1:COS(1)值為 0.5403023058681398.
ACOS(x)返回x的反余弦,即余弦是x的值.若x不在-1~1的范圍之內(nèi),則返回NULL.
函數(shù)ACOS和COS互為反函數(shù).

6.2.12正切函數(shù)、反正切函數(shù)和余切函數(shù)

TAN(x)返回x的正切,其中x為給定的弧度值.
ATAN(x)返回x的反正切,即正切為x的值.
函數(shù)ATAN和TAN互為反函數(shù).
COT(x)返回x的余切.
函數(shù)COT和TAN互為倒函數(shù).

6.3字符串函數(shù)

字符串函數(shù)主要用來處理數(shù)據(jù)庫中的字符串數(shù)據(jù),MySQL中字符串函數(shù)有:計算字符串 長度函數(shù)、字符串合并函數(shù)、字符串替換函數(shù)、 字符串比較函數(shù)、查找指定字符串位置函數(shù)等. 本節(jié)將介紹各種字符串函數(shù)的功能和用法.

6.3.1計算字符串字符數(shù)的函數(shù)和字符串長度的函數(shù)

CHAR_LENGTH(str)返回值為字符串str所包含的字符個數(shù).一個多字節(jié)字符算作一個單字符.
LENGTH(str)返回值為字符串的字節(jié)長度,使用utf8(UNICODE的一種變長字符編碼,又稱萬國碼)編碼字符集時,一個漢字是3個字節(jié),一個數(shù)字或字母算一個字節(jié).
可以看到,計算的結(jié)果與CHAR_LENGTH相同,因為英文字符的個數(shù)和所占的字節(jié)相同,一個字符占一個字節(jié).

6.3.2 合并字符串函數(shù)CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)

CONCAT(sl,s2,...)返回結(jié)果為連接參數(shù)產(chǎn)生的字符串,或許有一個或多個參數(shù).如有任何一個參數(shù)為NULL,則返回值為NULL.如果所有參數(shù)均為非二進制字符串,則結(jié)果為非二進制字符串.如果自變量中含有任一二進制字符串,則結(jié)果為一個二進制字符串.

CONCAT('My SQL', ’5.7’)返回兩個字符串連接后的字符串;CONCAT(’My’,NULL,'SQL')中有一個參數(shù)為NULL,因此返回結(jié)果為NULL.
CONCAT_WS(x,s1,s2,...). CONCAT_WS 代表 CONCAT With Separator,是CONCAT()的特殊形式 .第一個參數(shù)x是其他參數(shù) 的分隔符,分 隔符的位置放在要連接的兩個字符串之間. 分隔符可以是一個字符串,也可以是其他參數(shù).如果分隔符為NULL,則結(jié)果為NULL.函數(shù)會忽略任何分隔符參數(shù)后的NULL值.

6.3.3替換字符串的函數(shù)INSERT(s1,x,len,s2)

INSERT(sl,x,len,s2)返回字符串si,其子字符串起始于x位置和被字符串s2取代的len字符.如果x超過字符串長度,則返回值為原始 字符串.假如 len 的長度大于其他字符串的長度,則從位置x開始替換.若任何一個參數(shù)為NULL,則返回值為NULL.

第一個函數(shù)INSERT('Quesf, 2, 4, ’What')將“Quest”第2個字符開始長度為4的字符串替換為What,結(jié)果為“QWhat” :第二個函數(shù)INSERT('Quest', -1, 4, ’What’)中起始位置-1超出了字符串長度,直接返回原字符;第三個函數(shù)INSERT('Quest', 3, 原字符串長度,則從第3個字符開始,截取后面所有的字符,并替換為指定字符What,結(jié)果 為 “QuWhat”.

6.3.4字母大小寫轉(zhuǎn)換函數(shù)

LOWER(str)或者LCASE(str)可以將字符串str中的字母字符全部轉(zhuǎn)換成小寫字母.
UPPER(str)或者UCASE(str)可以將字符串str屮的字母字符全部轉(zhuǎn)換成大寫字母.

6.3.5 獲取指定長度的字符串的函數(shù)LEFT(s,n)和RIGHT(s,n)

LEFT(s.n)返回字符串s開始的最左邊n個字符.
RIGHT(s,n)返回字符串中最右邊n個字符.

6.3.6 填充字符串的函數(shù) LPAD(s1,len,s2)和 RPAD(s1,len,s2)

LPAD(sl,len,s2)返回字符串si,其左邊由字符串s2填補到len字符長度.假如si的長度大于len,則返回值被縮短至len字符.
RPAD(sl,len,s2)返回字符串si,其右邊被字符串s2填補至len字符長度.假如字符串si的長度大于len,則返回值被縮短到len字符長度.

6.3.7 刪除空格的函數(shù) LTRIM(s)、RTRIM(s)和 TRIM(s)

LTRIM(s)返回字符串s.字符串左側(cè)空格字符被刪除.
RTRIM(s)返回字符串s,字符串右側(cè)空格字符被刪除.
TRIM(s)刪除字符串s兩側(cè)的空格.

6.3.8 刪除指定字符串的函數(shù)TRIM(s1 FROM s)

TRIM(s1 FROM s)刪除字符串s中兩端所有的子字符串sksl為可選項,在未指定情況下,刪除空格.

6.3.9重復生成字符串的函數(shù)REPEAT(s.n)

REPEAT(s,n)返回一個由重復的字符串s組成的字符串,字符串s的數(shù)目等于n.若n<=0,則返回一個空字符串.若s或n為NULL,則返回NULL.

6.3.10 空格函數(shù) SPACE(n)和替換函數(shù) REPLACE(s,s1,s2)

SPACE(n)返回一個由n個空格組成的字符串.
REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1.

6.3.11比較字符串大小的函數(shù)STRCMP(s1 ,s2)

STRCMP(s1,s2)若所有的字符串均相同,則返回0;若根據(jù)當前分類次序,第一個參數(shù)小于第二個,則返回-1,其他情況返回1.

6.3.12 獲取子串的函數(shù) SUBSTRING(s,n,len)和 MID(s,n,len)

SUBSTRING(s,n,len)帶有l(wèi)en參數(shù)的格式,從字符串s返回一個長度同len字符相同的子 字符串,起始于位置n.也可能對n使用一個負值.假若這樣,則子字符串的位置起始于字符 串結(jié)尾的n字符,即倒數(shù)第n個字符,而不是字符串的開頭位置.

MID(s,n,len)與 SUBSTRING(s,n,len)的作用相同.

如果對len使用的是一個小于1的值,則結(jié)果始終為空字符串.

6.3.13匹配子串開始位置的函數(shù)

LOCATE(strl,str)、POSITION(str1 IN str)和 INSTR(str,str1)3 個函數(shù)作用相同,返回子字符串strl在字符串str中的開始位置.

6.3.14 字符串逆序的函數(shù)REVERSE(s)

REVERSE(s)將字符串s反轉(zhuǎn),返回的字符串的順序和s字符串順序相反.

6.3.15返回指定位置的字符串的函數(shù)

ELT(N,字符串1,字符串2,字符串3,...,字符串N)若N= 1,則返回值為字符串1,若N=2,則返回值為字符串2,以此類推.若N小于1或大于參數(shù)的數(shù)目,則返回值為NULL.

6.3.16返回指定字符串位置的函數(shù)FIELD(s,s1,s2,...)

FIELD(s,sl,s2,...)返回字符串s在列表sl,s2,...中第一次出現(xiàn)的位置,在找不到s的情況下,返回值為9.如果s為NULL,則返回值為0,原因是NULL不能同任何值進行同等比較.

6.3.17返回子串位置的函數(shù)FIND_IN_SET(s1,s2)

FIND_IN_SET(sl,s2)返回字符串si在字符串列表s2中出現(xiàn)的位置,字符串列表是一個由多個逗號‘,'分開的字符串組成的列表.如果si不在s2或s2為空字符串,則返回值為0. 如果任意一個參數(shù)為NULL,則返回值為NULLo這個函數(shù)在第一個參數(shù)包含一個逗號',’ 時將無法正常運行.

雖然FIND_IN_SET()和FIELD()兩個函數(shù)格式不同,但作用類似,都可以返回指定字符串 在字符串列表中的位置.

6.3.18 選取字符串的函數(shù)MAKE_SET(x,s1,s2,...)

MAKE_SET(x,sl,s2,...)返回由x的二進制數(shù)指定的相應(yīng)位的字符串組成的字符串,s1對應(yīng)比特1, s2對應(yīng)比特01,以此類推.s1, s2...中的NULL值不會被添加到結(jié)果中.

6.4 曰期和時間函數(shù)

日期和吋間函數(shù)主要用來處理日期和時間值,一般的日期函數(shù)除了使用DATE類型的參數(shù)外,也可以使用DATETIME或者TIMESTAMP類型的參數(shù),但會忽略這些值的時間部分.相同的,以TIME類型值為參數(shù)的函數(shù),可以接受TIMESTAMP類型的參數(shù),但會忽略日期部分,許多日期函數(shù)可以同時接受數(shù)和字符串類型的兩種參數(shù),本節(jié)將介紹各種日期和時間函數(shù)的功能和用法.

6.4.1獲取當前日期的函數(shù)和獲取當前時間的函數(shù)

CURDATE()和CURRENT_DATE()函數(shù)作用相同,將當前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回,具體格式根據(jù)函數(shù)在字符串或是數(shù)字語境中而定.兩個函數(shù)作用相同,都返回了相同的系統(tǒng)當前日期,“CURDATE()+ 0”將當 前日期值轉(zhuǎn)換為數(shù)值型.
CURTIME()和CURRENT_TIME()函數(shù)作用相同,將當前時間以*HH:MM:SS ’或HHMMSS 的格式返回,具體格式根據(jù)函數(shù)在字符串或是數(shù)字語境中而定.兩個函數(shù)作用相同,都返回了相同的系統(tǒng)當前時間,“CURTIME()+ 0”將當 前時間值轉(zhuǎn)換為數(shù)值型.

6.4.2獲取當前日期和時間的函數(shù)

CURRENT_TIMESTAMP(). LOCALTIME()、NOW()和 SYSDATE()4 個函數(shù)的作用相同,均返回當前日期和時間值,格式為‘YYYY-MM-DDHH:MM:SS’或YYYYMMDDHHMMSS,具體格式根據(jù)函數(shù)在字符串或數(shù)字語境中而定.可以看到,4個函數(shù)返回的結(jié)果是相同的.

6.4.3 UNIX時間戳函數(shù)

UNIX TIMESTAMP(date)若無參數(shù)調(diào)用,則返回一個 Unix 時間戳(‘1970-01-01 00:00:00' GMT之后的秒數(shù))作為無符號整數(shù).其中,GMT(Greenwich mean time)'為格林尼治標準時間).若用 date 來調(diào)用 UNIX_TIMESTAMP(),它會將參數(shù)值以 41970-01-01 00:00:00* GMT 后的秒數(shù)的形式返回.date可以是一個DATE字符串、DATETIME字符串、TIMESTAMP或
一個當?shù)貐奸g的YYMMDD或YYYYMMDD格式的數(shù)字.
FROM_UNIXTIME(date)函數(shù)把 UNIX 時間戳轉(zhuǎn)換為普通格式的時間,與 UNIX_TIMESTAMP(date)函數(shù)互為反函數(shù).

6.4.4 返回UTC日期的函數(shù)和返回UTC時間的函數(shù)

UTC_DATE()函數(shù)返回當前UTC(世界標準時間)日期值,其格式為‘YYYY-MM-DD’或YYYYMMDD,具體格式取決于函數(shù)是否用在字符串或數(shù)字語境中.
UTC_DATE()函數(shù)返回值為當前時區(qū)的日期值.
UTC_TIME()返冋當前UTC時間值,其格式為‘HH:MM:SS’或HHMMSS,具體格式取 決于函數(shù)是否用在字符串或數(shù)字語境中.

6.4.5 獲取月份的函數(shù) MONTH(date)和MONTHNAME(date)

MONTH(date)函數(shù)返回date對應(yīng)的月份,范圍值從1-12.
MONTHNAME(date)數(shù)返回日期date對應(yīng)月份的英文全名.

6.4.6 獲取星期的函數(shù) DAYNAME(d),DAYOFWEEK(d)和WEEKDAY(d)

DAYNAME(d)函數(shù)返回d對應(yīng)的工作日的英文名稱,例如Sunday, Monday等.
DAYOFWEEK(d)函數(shù)返回d對應(yīng)的一周中的索引(位置).1表示周日,2表示周一, 7表不周六).
WEEKDAY(d)返回d對應(yīng)的工作日索引.0表示周一,1表示周二,...,6表示周日.
WEEKDAY()和DAYOFWEEK()函數(shù)都是返回指定日期在某一周內(nèi)的位置,只 是索引編號不同.

6.4.7 獲取星期數(shù)的函數(shù)WEEK(d)和WEEKOFYEAR(d)

WEEK(d)計算日期d是一年中的第幾周.WEEK()的雙參數(shù)形式允許指定該星期是否起始于周日或周一,以及返回值的范圍是否為從0?53或從1?53.若Mode參數(shù)被省略,則使用 default week format系統(tǒng)自變量的值.

WEEK函數(shù)中Mode參數(shù)取值

WEEKOFYEAR(d)計算某天位于一年中的第幾周,范圍是從1~53.相當于WEEK(d,3).

可以看到,兩個函數(shù)返回結(jié)果相同.

6.4.8 獲取天數(shù)的函數(shù) DAYOFYEAR(d)和 DAYOFMONTH(d)

DAYOFYEAR(d)函數(shù)返回d是一年中的第幾天,范圍是從1~366.
DAYOFMONTH(d)函數(shù)返回d是一個月中的第幾天,范圍是從1~31,

6.4.9獲取年份、季度、小時、分鐘和秒鐘的函數(shù)

YEAR(date)返冋date對應(yīng)的年份,范圍是1970-2069.
QUARTER(date)返回date對應(yīng)的一年中的季度值,范圍是從1~4.
MINUTE(time)返回time對應(yīng)的分鐘數(shù),范圍是從0~59.
SECOND(time)返回time對應(yīng)的秒數(shù),范圍是從0?59.

6.4.10 獲取日期的指定值的函數(shù)EXTRACT(type FROM date)

EXTRACT(type FROM date)函數(shù)所使用的時間間隔類型說明符同DATE_ADD()或DATE_SUB()的相同,但它從日期中提取一部分,而不是執(zhí)行日期運算.

6.4.11時間和秒鐘轉(zhuǎn)換的函數(shù)

TIME_TO_SEC(time)返回已轉(zhuǎn)化為秒的time參數(shù).轉(zhuǎn)換公式為:小時3600+分鐘60+秒.
SEC_TO_TIME(seconds)返回被轉(zhuǎn)化為小時、分鐘和秒數(shù)的seconds參數(shù)值,其格式為 ‘HH:MM:SS’或HHMMSS,具體格式根據(jù)該函數(shù)是否用在字符串或數(shù)字語境中而定.

可以看到,SEC_TO_TIME函數(shù)返回值加上0值之后變成了小數(shù)值;TIME_TO_SEC正好和SEC_TO_TIME互為反函數(shù).

6.4.12計算日期和時間的函數(shù)

計算曰期和時間的函數(shù)有:DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、 ADDTIME(), SUBTIME()和DATE_DIFF().
DATE_ADD(date,INTERVAL expr type)和 DATE_SUB(date,INTERVAL expr type),其中,date是一個DATETIME或DATE值,用來指定起始時間. expr是一個表達式,用來指定從起始日期添加或減去的時間間隔值.expr是一個字符串;對于負值的時間間隔,它可以以一個負號開頭.type為關(guān)鍵詞,它指示了表達式被解釋的方式.

MySQL中計算日期和時間的格式

若date參數(shù)是一個DATE值,計算只會包括YEAR、MONTH和DAY部分(即沒有時間 部分),其結(jié)果是一個DATE值.否則,結(jié)果將是一個DATETIME值.
DATE_ADD(date,INTERVAL expr type)和 ADDDATE(date,INTERVAL expr type)兩個函數(shù) 作用相同,執(zhí)行日期的加運算.
DATE_SUB(date,INTERVAL expr type)或者 SUBDATE(date,INTERVAL expr type)兩個函 數(shù)作用相同,執(zhí)行日期的減運算.
DATE_ADD和DATE_SUB在指定修改的時間段時,也可以指定負值,負值代表相減, 即返回以前的日期和時間.

ADDTIME(date,expr)函數(shù)將expr值添加到date,并返回修改后的值,date是一個日期或 者日期時間表達式,而expr是一個時間表達式.
SUBTIME(date,expr)函數(shù)中date減去expr值,并返回修改后的值,date是一個日期或者 日期時間表達式,而expr是一個時間表達式.
DATEDIFF(date1 ,date2)返回起始時間date1和結(jié)束時間date2之間的天數(shù).date1和date2 為日期或date-and-time表達式.計算中只用到這些值的日期部分.

6.4.13將日期和時間格式化的函數(shù)

DATE_FORMAT(date, format)根據(jù)format指定的格式顯示date值.

DATE_FORMAT時間日期格式

GET_FORMAT(val_type, fdrmat_type)返回日期時間字符串的顯示格式,val type表示日期數(shù)據(jù)類型,包括DATE、DATETIME和TIME; format_type表示格式化顯示類型,包括EUR、 INTERV AL. ISO、JIS、USA.

GET_FORMAT返回的格式字符串
值類型 格式化類型 顯示格式字符串

6.5條件判斷函數(shù)

條件判斷函數(shù)也稱為控制流程函數(shù),根據(jù)滿足的條件的不同,執(zhí)行相應(yīng)的流程.MySQL中進行條件判斷的函數(shù)有IF、IFNULL和CASE.本節(jié)將分別介紹各個函數(shù)的用法.

6.5.1 IF(expr,v1,v2)函數(shù)

IF(expr, v1, v2),如果表達式 expr 是 TRUE(expr <> 0 and expr <> NULL),則 IF()的返回值為v1;否則返回值為v2.IF()的返回值為數(shù)字值或字符串值,具體情況視其所在語境而定.
如果v1或v2中只有一個明確是NULL,則1F0函數(shù)的結(jié)果類型為非NULL表達式的結(jié)果類型.

6.5.2 IFNULL(v1,v2)函數(shù)

IFNULL(v1,v2)假如v1不為NULL,則IFNULL()的返回值為v1;否則其返回值為v2.
IFNULL()的返回值是數(shù)字或是字符串,具體情況取決于其所在的語境.

6.5.3 CASE 函數(shù)

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE m] END
該函數(shù)表示,如果expr值等于某個vn,則返回對應(yīng)位置THEN后面的結(jié)果.如果與所有值都不相等,則返回ELSE后面的rn.

一個CASE表達式的默認返回值類型是任何返回值的相容集合類型,但具體情況視其所在語境而定.如果用在字符串語境中,則返回結(jié)果為字符串.如果用在數(shù)字語境中,則返回結(jié)果為十進制值、實數(shù)值或整數(shù)值.

6.6 系統(tǒng)信息函數(shù)

本節(jié)將介紹常用的系統(tǒng)信息函數(shù),MySQL中的系統(tǒng)信息有,數(shù)據(jù)庫的版本號、當前用戶名和連接數(shù)、系統(tǒng)字符集、最后一個自動生成的ID值等.本章將介紹各個函數(shù)的使用方法.

6.6.1獲取MySQL版本號、連接數(shù)和數(shù)據(jù)庫名的函數(shù)

SHOW VERSION()
SHOW CONNECTION_ID()
SHOW PROCESSLIST
SHOW DATABASE
SHOW SCHEMA
VERSION()返回指示MySQL服務(wù)器版本的字符串.這個字符串使用utf8字符集.
CONNECTION_ID()返回MySQL服務(wù)器當前連接的次數(shù),每個連接都有各自唯一的ID.

processlist命令的輸出結(jié)果顯示了有哪些線程在運行,不僅可以查看當前所有的連接數(shù), 還可以查看當前的連接狀態(tài),幫助識別出有問題的查詢語句等.
如果是 root 賬號,能看到所有用戶的當前連接.如果是其他普通賬號,則只能看到自己占用的連接.show processlist;只列出前100條,如果想全部列出可使用show full processlist命令.
使用SHOW PROCESSLIST命令輸出當前用戶的連接信息,各個列的含義和用途:
(1)Id列,用戶登錄MySQL時,系統(tǒng)分配的“connection id”.
(2)User列,顯示當前用戶.如果不是root,這個命令就只顯示用戶權(quán)限范圍內(nèi)的SQL 語句.
(3)Host列,顯示這個語句是從哪個IP的哪個端口上發(fā)出的,可以用來追蹤出現(xiàn)問題 語句的用戶.
(4)db列,顯示這個進程目前連接的是哪個數(shù)據(jù)庫.
(5)Command列,顯示當前連接執(zhí)行的命令,一般取值為休眠(Sleep),查詢(Query), 連接(Connect).
(6)Time列,顯示這個狀態(tài)持續(xù)的時間,單位是秒.
(7)State列,顯示使用當前連接的SQL語句的狀態(tài),很重要的列,后續(xù)會有所有狀態(tài) 的描述,State只是語句執(zhí)行中的某一個狀態(tài).一個SQL語句,以查詢?yōu)槔?可能需要經(jīng)過 Copying to tmp table,Sorting result,Sending data 等狀態(tài)才可以完成.
(8)Info列,顯示這個SQL語句,是判斷問題語句的一個重要依據(jù).

DATABASE()和SCHEMA()函數(shù)返回使用utf8字符集的默認(當前)數(shù)據(jù)庫名.兩個函數(shù)的作用相同.

6.6.2獲取用戶名的函數(shù)

select user(), current_user(), system_user();
USER(), CURRENTJJSER、SYSTEM USER()和 SESSION_USER()這兒個函數(shù)返回當前被MySQL服務(wù)器驗證的用戶名和主機名組合.這個值符合確定當前登錄用戶存取權(quán)限的MySQL賬戶.一般情況下,這幾個函數(shù)的返回值是相同的.

6.6.3獲取字符串的字符集和排序方式的函數(shù)

select charset('abc');
select charset(version());
select charset(convert('abc' using latin1));
CHARSET(str)返回字符串str自變量的字符集.
CHARSET('abc')返回系統(tǒng)默認的字符集 utfB; CHARSET(CONVERT('abc' USING latinl))返回的字符集為latinl ;前面介紹過,VERSION0返回的字符串使用utf8字符集,因此 CHARSET(VERSION())返回結(jié)果為 utfB.

select collation('abc');
select collation(convert('abc' using utf8));
COLLATION(str)返回字符串str的字符排列方式.

可以看到,使用不同字符集時字符串的排列方式不同.

6.6.4獲取最后一個自動生成的ID值的函數(shù)

LAST_INSERT_ID()自動返回最后一個INSERT或UPDATE為AUTO_INCREMENT列設(shè)置的第一個發(fā)生的值.

一次插入一條記錄時,返回值為最后一條插入記錄的ID值.
一次插入多條記錄時,LAST_INSERT_ID()只返回插入的第一行數(shù)據(jù)時產(chǎn)生的ID值.

LAST_INSERT)ID是與table無關(guān)的,如果向表a插入數(shù)據(jù)后,再向表b插入數(shù)據(jù), LAST_INSERT_ID返回表b中的ID值.

6.7 加/解密函數(shù)

加密函數(shù)主要用來對數(shù)據(jù)進行加密和界面處理,以保證某些重要數(shù)據(jù)不被別人獲取.這些函數(shù)在保證數(shù)據(jù)庫安全時非常有用.本節(jié)將介紹各種加密和解密函數(shù)的作用和使用方法.

6.7.1 加密函數(shù) PASSWORD(str)

PASSWORD(str)從原明文密碼str計算并返回加密后的密碼字符串,當參數(shù)為NULL時, 返回NULL.
MySQL將PASSWORD函數(shù)加密后的密碼保存到用戶權(quán)限表中.

PASSWORD()函數(shù)在MySQL服務(wù)器的鑒定系統(tǒng)中使用;不應(yīng)將它用在個人的應(yīng)用程序 中.PASSWORD0加密是單向的(不可逆).PASSWORD()執(zhí)行密碼加密與UNIX中密碼被加密的方式不同.

6.7.2 加密函數(shù) MD5(str)

MD5(str)為字符串算出一個MD5 128比特校驗和.該值以32位十六進制數(shù)字的二進制字符串形式返回,若參數(shù)為NULL,則會返回NULL.

6.7.3 加密函數(shù) ENCODE(str,pswd_str)

ENCODE(str,pswd_str)使用pswd_str作為密碼,加密str.使用DECODE()解密結(jié)果,是一 個和str長度相同的二進制字符串.

6.7.4 解密函數(shù) DECODE(crypt_str,pswd_str)

DECODE(crypt_str,pswd_str)使用 pswd str 作為密碼,解密加密字符串 crypt str, crypt_str是由ENCODE()返回的字符串.
可以看到,使用相同解密字符串進行解密之后的結(jié)果,正好為ENCODE函數(shù)中被加密的字符串.DECODE函數(shù)和ENCODE函數(shù)互為反函數(shù).

6.8其他函數(shù)

本節(jié)將要介紹的函數(shù)不能籠統(tǒng)地分為哪一類,但是這些函數(shù)也非常有用,例如重復指定操作函數(shù)、改變字符集函數(shù)、ip地址與數(shù)字轉(zhuǎn)換函數(shù)等.本節(jié)將介紹這些函數(shù)的作用和使用方法.

6.8.1 格式化函數(shù) FORMAT(x,n)

FORMAT(x,n)將數(shù)字x格式化,并以四舍五入的方式保留小數(shù)點后n位,結(jié)果以字符串的形式返回.若n為0,則返回結(jié)果函數(shù)不含小數(shù)部分.

6.8.2不同進制的數(shù)字進行轉(zhuǎn)換的函數(shù)

CONV(N, from_base, to_base)S數(shù)進行不同進制數(shù)間的轉(zhuǎn)換.返回值為數(shù)值N的字符串表示,由from_base進制轉(zhuǎn)化為to_base進制.如有任意一個參數(shù)為NULL,則返回值為NULL. 自變量N被理解為一個整數(shù),但是可以被指定為一個整數(shù)或字符串.最小基數(shù)為2,而最大基數(shù)則為36.

進制說明:

• 二進制,采用0和1兩個數(shù)字來表示的數(shù).它以2為基數(shù),逢二進一.
• 八進制,采用0,1, 2, 3, 4, 5, 6, 7八個數(shù)字,逢八進一,以數(shù)字0開頭.
• 十進制,采用0-9,共十個數(shù)字表示,逢十進一.
• 十六進制,它由0-9, A-F組成,與十進制的對應(yīng)關(guān)系是;G-9對應(yīng)0-9 A-F對應(yīng)10-15.以數(shù)字Ox開頭.

6.8.3 IP地址與數(shù)字相互轉(zhuǎn)換的函數(shù)

INET_ATON(expr)給出一個作為字符串的網(wǎng)絡(luò)地址的點地址表示,返回一個代表該地址數(shù)值的整數(shù).地址可以是4或8bit地址.
INET_NTOA(expr)給定一個數(shù)字網(wǎng)絡(luò)地址(4或8bit),返回作為字符串的該地址的點地址表示.
可以看到,INET_NTOA和INET_ATON互為反函數(shù).

6.8.4加鎖函數(shù)和解鎖函數(shù)

GET_LOCK(str,timeout)設(shè)法使用字符串str給定的名字得到一個鎖,持續(xù)時間timeout秒.
若成功得到鎖,則返回1;若操作超時,則返回0;若發(fā)生錯誤,則返回NULL.假如有一個 用GET_LOCK()得到的鎖,當執(zhí)行RELEASE_LOCK()或連接斷開(正?；蚍钦?時,這個鎖就會解除.

RELEASE_LOCK(str)解開被GET_LOCK()獲取的,用字符串str所命名的鎖.若鎖被解開,則返回1;若該線程尚未創(chuàng)建鎖,則返回0(此時鎖沒有被解開);若命名的鎖不存在,則返回NULL.若該鎖從未被GET_LOCK()的調(diào)用獲取,或鎖已經(jīng)被提前解開,則該鎖不存在.

IS_FREE_LOCK(str)檢查名為str的鎖是否可以使用(換言之,沒有被封鎖).若鎖可以使用,則返回1(沒有人在用這個鎖);若這個鎖正在被使用,則返冋0:出現(xiàn)錯誤,則返冋 NULL(諸如不正確的參數(shù)).

lS_USED_LOCK(str)檢查名為str的鎖是否正在被使用(換言之,被封鎖).若被封鎖,則返回使用該鎖的客戶端的連接標識符(connections):否則,返回NULL.

6.8.5重復執(zhí)行指定操作的函數(shù)

BENCHMARK(count,expr)函數(shù)重復count次執(zhí)行表達式expr.它可以用于計算MySQL處理表達式的速度.結(jié)果值通常為0(0只是表示處理過程很快,并不是沒有花費時間).另一個作用是它可以在MySQL客戶端內(nèi)部報告語句執(zhí)行的時間.

BENCHMARK報告的時間是客戶端經(jīng)過的時間,而不是在服務(wù)器端的CPU時間,每次執(zhí)行后報告的時間并不一定是相同的.

6.8.6改變字符集的函數(shù)

CONVERT(... USING ...)帶有USING的CONVERT()函數(shù)被用來在不同的字符集之間轉(zhuǎn)化數(shù)據(jù).

6.8.7改變數(shù)據(jù)類型的函數(shù)

CAST(x,AS type)和CONVERT(x, type)函數(shù)將一個類型的值轉(zhuǎn)換為另一個類型的值,可轉(zhuǎn)換的 type 有:BINARY. CHAR(n)、DATE, TIME、DATETIME> DECIMAL> SIGNED, UNSIGNED.

6.9 綜合案例——MySQL函數(shù)的使用

6.10 6.10專家解惑

疑問1:如何從日期時間值中獲取年、月、曰等部分曰期或時間值?
MySQL中,日期時間值以字符串形式存儲在數(shù)據(jù)表中,因此可以使用字符串函數(shù)分別截 取日期時間值的不同部分,例如某個名稱為dt的字段有值“2010-10-01 12:00:30”,如果只需 要獲得年值,可以輸入LEFT(dt, 4),這樣就獲得了字符串左邊開始長度為4的子字符串,即 YEAR部分的值;如果要獲取月份值,可以輸入MID(dt,6,2),字符串第6個字符開始,長度 為2的子字符串正好為dt中的月份值.同理,讀者可以根據(jù)其他日期和時間的位置,計算并 獲取相應(yīng)的值.

疑問2:如何改變默認的字符集?
CONVERT0函數(shù)改變指定字符串的默認字符集,在開始的章節(jié)中,向讀者介紹使用GUI 圖形化安裝配置工具進行MySQL的安裝和配置,其中的一個步驟是可以選擇MySQL的默認 字符集 .但是, 如果只改 變字符集 ,沒有必 要把配置 過程重新 執(zhí)行一遍 ,在這里 ,一個簡單的方式是修改配置文件.在Windows中,MySQL配置文件名稱為my.ini,該文件在MySQL的 安裝目錄下面.修改配置文件中的default-character-set和character-set-server參數(shù)值,將其改 為想要的字符集名稱,如gbk、gb2312、latinl等,修改完之后重新啟動MySQL服務(wù),即可 生效.讀者可以在修改字符集時使用SHOW V ARIABLES LIKE ’character_set_%’;命令查看當 前字符集,以進行對比.

第7章 查詢數(shù)據(jù)

7.1 基本查詢語句

MySQL從數(shù)據(jù)表中查詢數(shù)據(jù)的基本語句為SELECT語句.SELECT語句的基本格式是:

select
    {* | <字段列表>}
    [
        from <表1>, <表2>, ...
        [ where <表達式>
        [GROUP BY <group by definition>]
        [HAVING <expression> [ {〈operator〉<expression>}...]] 
        [ORDER BY <order by definition>]
        [LIMIT [<offset>, ] <row count>]
    ]

SELECT [字段1,字段2,...,字段n] FROM [表或視圖] WHERE [査詢條件I;

其中,各條子句的含義如下:

• {*|<字段列表>}包含星號通配符選擇字段列表,表示查詢的字段,其中字段列至少包含一個字段名稱,如果要查詢多個字段,多個字段之間用逗號隔開,最后一個字段后 不要加逗號.
• FROM <表1>,<表2>...,表1和表2表示查詢數(shù)據(jù)的來源,可以是單個或者多個.
• WHERE子句是可選項,如果選擇該項,將限定查詢行必須滿足的查詢條件.
• GROUP BY <字段>,該子句告訴MySQL如何顯示查詢出來的數(shù)據(jù),并按照指定的字段分組.
• [ORDER BY <字段>],該子句告訴MySQL按什么樣的順序顯示查詢出來的數(shù)據(jù),可以進行的排序有:升序(ASC)、降序(DESC).
• [LIMIT [<offset>,] <row count>],該子句告訴MySQL每次顯示查詢出來的數(shù)據(jù)條數(shù).

7.2 單表查詢

單表查詢是指從一張表數(shù)據(jù)中查詢所需的數(shù)據(jù).本節(jié)將介紹單表查詢中的各種基本的查詢方式,主要有:查詢所有字段 、查詢指定字段、 查詢指定記錄、查詢空值、多條件的查詢、對查詢結(jié)果進行排序等.

7.2.1查詢所有字段

1.在SELECT語句中使用星號()通配符查詢所有字段
SELECT 查詢記錄最簡單的形式是從一個表中檢索所有記錄,實現(xiàn)的方法是使用星號()通配符指定查找所有列的名稱.語法格式如下:
SELECT * FROM 表名;
可以看到,使用星號(*)通配符時,將返回所有列,列按照定義表時候的順序顯示.

2.在SELECT語句中指定所有字段

一般情況下,除非需要使用表中所有的字段數(shù)據(jù),最好不要使用通配符.'*'使用通配符雖然可以節(jié)省輸入查詢語句的時間,但是獲取不需要的列數(shù)據(jù)通常會降低查詢和所使用 的應(yīng)用程序的效率.通配符的優(yōu)勢是,當不知道所需要的列的名稱時,可以通過它獲取它們.

7.2.2查詢指定字段

1.查詢單個字段
查詢表中的某一個字段,語法格式為:·SELECT 列名 FROM 表名;·

2.查詢多個字段
使用SELECT聲明,可以獲取多個字段下的數(shù)據(jù),只需要在關(guān)鍵字SELECT后面指定要 查找的字段的名稱,不同字段名稱之間用逗號(,)分隔開,最后一個字段后面不需要加逗號, 語法格式如下:
SELECT 字段名1, 字段名字段名n FROM 表名;

7.2.3查詢指定記錄

數(shù)據(jù)庫中包含大量的數(shù)據(jù),根據(jù)特殊要求,可能只需要查詢表中的指定數(shù)據(jù),即對數(shù)據(jù)進行過濾.在SELECT語句中,通過WHERE子句可以對數(shù)據(jù)進行過濾,語法格式為:
SELECT 字段名1,字段名字段名n FROM表名 WHERE 査詢條件

WHERE條件判斷符

7.2.4 帶IN關(guān)鍵字的查詢

IN操作符用來查詢滿足指定范圍內(nèi)的條件的記錄,使用IN操作符,將所有檢索條件用括號括起來,檢索條件之間用逗號分隔開,只要滿足條件范圍內(nèi)的一個值即為匹配項.

7.2.5 帶BETWEEN AND的范圍查詢

BETWEEN AND用來查詢某個范圍內(nèi)的值,該操作符需要兩個參數(shù),即范圍的開始值和結(jié)束值,如果字段值滿足指定的范圍查詢條件,則這些記錄被返回.
BETWEEN AND操作符前可以加關(guān)鍵字NOT,表示指定范圍之外的值,如果字段值不滿足指定的范圍內(nèi)的值,則這些記錄被返回.

7.2.6 帶LIKE的字符匹配查詢

通配符是一種在SQL的WHERE條件子句中擁有特殊意思的字符,SQL語句中支持多種通配符,可以和LIKE一起使用的通配符有‘%'和'_'.
1.百分號通配符'%',匹配任意長度的字符,甚至包括零字符
2.下劃線通配符,一次只能匹配任意一個字符

7.2.7查詢空值

數(shù)據(jù)表創(chuàng)建的時候,設(shè)計者可以指定某列中是否可以包含空值(NULL).空值不同于0,也不同于空字符串.空值-般表示數(shù)據(jù)未知、不適用或?qū)⒃谝院筇砑訑?shù)據(jù).在SELECT語句中使用用IS NULL子句,可以查詢某字段內(nèi)容為空的記錄.
與IS NULL相反的是NOT IS NULL,該關(guān)鍵字查找字段不為空的記錄.

7.2.8 帶AND的多條件查詢

使用SELECT查詢時,可以增加查詢的限制條件,這樣可以使查詢的結(jié)果更加精確. MySQL在WHERE子句中使用AND操作符限定只有滿足所有查詢條件的記錄才會被返回. 可以使用AND連接兩個甚至多個查詢條件,多個條件表達式之間用AND分開.

7.2.9 帶OR的多條件查詢

與AND相反,在WHERE聲明中使用OR操作符,表示只需要滿足其中一個條件的記錄即可返回.OR也可以連接兩個甚至多個查詢條件,多個條件表達式之間用OR分開.

OR操作符和IN操作符使用后的結(jié)果是-樣的,它們可以實現(xiàn)相同的功能.但是使用IN操作符使得檢索語句更加簡潔明了,并且IN執(zhí)行的速度要快于OR.更重要的是,使用IN操作符,可以執(zhí)行更加復雜的嵌套查詢

OR可以和AND-起使用,但是在使用時要注意兩者的優(yōu)先級,由于AND的優(yōu)先級高于 OR,因此先對AND兩邊的操作數(shù)進行操作,再與OR中的操作數(shù)結(jié)合.

7.2.10查詢結(jié)果不重復

在SELECT語句中,可以使 用DISTINCT關(guān)鍵字指示MySQL消除重復的記錄值.語法格式為:
SELECT DISTINCT 字段名 FROM 表名;

7.2.11對查詢結(jié)果排序

從前面的查詢結(jié)果,讀者會發(fā)現(xiàn)有些字段的值是沒有任何順序的,MySQL可以通過在SELECT語句中使用ORDER BY子句,對查詢的結(jié)果進行排序.

1.單列排序
2.多列排序
3.指定排序方向
默認情況下,查詢數(shù)據(jù)按字母升序進行排序(從A-Z),但數(shù)據(jù)的排序并不僅限于此, 還可以使用ORDER BY對查詢結(jié)果進行降序排序(從Z?A),這可以通過關(guān)鍵字DESC實現(xiàn).
與 DESC 相反的是ASC(升序排序),將字段列中的數(shù)據(jù),按字母表順序升序排序.實際上,在 排序的時候ASC是作為默認的排序方式,所以加不加都可以.

DESC關(guān)鍵字只對其前面的列進行降序排列,在這里只對Cprice排序,而并沒有對f_name 進行排序,因此,fjrice按降序排序,而fLname列仍按升序排序.如果要對多列都進行 降序排序,必須要在每一列的列名后面加DESC關(guān)鍵字.

7.2.12分組查詢

分組查詢是對數(shù)據(jù)按照某個或多個字段進行分組,MySQL中使用GROUP BY關(guān)鍵字對 數(shù)據(jù)進行分組,基本語法形式為:
[GROUP BY 字段][HAVING <條件表達式〉]
字段值為進行分組時所依據(jù)的列名稱;“HAVING <條件表達式>”指定滿足表達式限定條件的結(jié)果將被顯示.

1.創(chuàng)建分組
GROUP BY關(guān)鍵字通常和集合函數(shù)一起使用,例如:MAX()、MIN()、COUNT()、SUM()、 AVG().

MySQL中可以在GROUP BY字節(jié)中使用GROUP_CONCAT()函數(shù),將每個分組中各個字段的值顯示出來.

以看到,GROUP_CONCAT()函數(shù)將每個分組中的名稱顯示出來了,其名稱的個數(shù)與COUNT()數(shù)計算出來的相同.

2.使用HAVING過濾分組
GROUP BY可以和HAVING 一起限定顯示記錄所需滿足的條件,只有滿足條件的分組才會被顯示.

HAVING關(guān)鍵字與WHERE關(guān)鍵字都是用來過濾數(shù)據(jù)什么區(qū)別呢?其中重要的一點是,HAVING在數(shù)據(jù)分組之后進行過濾來選擇分組,而WHERE在分組之前用來選擇記錄.另外WHERE排除的記錄不再包括在分組中.

3.在 GROUP BY 子句中使用 WITH ROLLUP
使用WITH ROLLUP關(guān)鍵字之后,在所有查詢出的分組記錄之后增加一條記錄,該記錄計算查詢出的所有記錄的總和,即統(tǒng)計記錄數(shù)量.

4.多字段分組
使用GROUP BY可以對多個字段進行分組,GROUP BY關(guān)鍵字后面跟需要分組的字段,MySQL根據(jù)多字段的值來進行層次分組,分組層次從左到右,即先按第1個字段分組,然后在第1個字段值相同的記錄中,再根據(jù)第2個字段的值進行分組...依次類推.

5. GROUP BY和ORDER BY —起使用
   某些情況下需要對分組進行排序,在前面的介紹中,ORDER BY用來對查詢的記錄排序,如果和GROUP BY一起使用可以完成對分組的排序.

當使用ROLLUP時,不能同時使用ORDER BY子句進行結(jié)果排序.即ROLLUP和ORDER BY是互相排斥的.

7.2.13使用LIMIT限制查詢結(jié)果的數(shù)量

SELECT返回所有匹配的行,有可能是表中所有的行,如僅僅需要返回第一行或者前兒行,使用LIMIT關(guān)鍵字,基本語法格式如下:
LIMIT [位置偏移量,] 行數(shù)
第一個“位置偏移量”參數(shù)指示MySQL從哪一行開始顯示,是一個可選參數(shù),如果不指定“位置偏移量”,將會從表中的第一條記錄開始(第一條記錄的位置偏移量是0,第二條記錄的位置偏移量是1...依次類推):第二個參數(shù)“行數(shù)”指示返回的記錄條數(shù).

如果指定返回記錄的開始位置,則返回結(jié)果為從“位置偏移量”參數(shù)開始的指定行數(shù),"行 數(shù)”參數(shù)指定返回的記錄條數(shù).

MySQL 5.7中可以使用“LIMIT 4 OFFSET 3”,意思是獲取從第5條記錄開始后面的3條記錄,和“LIMIT4,3;”返回的結(jié)果相同.

7.3使用聚合函數(shù)查詢

有時候并不需要返回實際表中的數(shù)據(jù),而只是對數(shù)據(jù)進行總結(jié).MySQL提供一些查詢功能,可以對獲取的數(shù)據(jù)進行分析和報告.這些函數(shù)的功能有:計算數(shù)據(jù)表中記錄行數(shù)的總數(shù)、計算某個字段列下數(shù)據(jù)的總和,以及計算表中某個字段下的最大值、最小值或者平均值.

MySQL聚合函數(shù)

7.3.1 COUNT()函數(shù)

COUNT()函數(shù)統(tǒng)計數(shù)據(jù)表中包含的記錄行的總數(shù),或者根據(jù)查詢結(jié)果返回列中包含的數(shù)據(jù)行數(shù).其使用方法有兩種:

• COUNT(*)計算表中總的行數(shù),不管某列有數(shù)值或者為空值.
• COUNT(字段名)計算指定列下總的行數(shù),計算時將忽略空值的行.
  兩種方式在計算總數(shù)的時候?qū)Υ齆ULL值的方式不同.即指定列的值為空的行被COUNT()函數(shù)忽略,但是如果不指定列,而在COUNT()函數(shù)中使用星號,則所有記錄都不忽略.

7.3.2 SUM()函數(shù)

SUMO是一個求總和的函數(shù),返冋指定列值的總和.
SUM0可以與GROUP BY 一起使用,來計算每個分組的總和.
SUM0函數(shù)在計算時,忽略列值為NULL的行.

7.3.3 AVG()函數(shù)

AVG()函數(shù)通過計算返回的行數(shù)和每一行數(shù)據(jù)的和,求得指定列數(shù)據(jù)的平均值.
AVG()可以與GROUP BY 一起使用,來計算每個分組的平均值.
AVG()函數(shù)使用時,其參數(shù)為要計算的列名稱,如果要得到多個列的多個平均值,則需要在每一列上使用AVG()函數(shù).

7.3.4 MAX()函數(shù)

MAX()返回指定列中的最大值.
MAX()函數(shù)不僅適用于查找數(shù)值類型,也可應(yīng)用于字符類型.
MAX()函數(shù)可以對字母進行大小判斷,并返冋最大的字符或者字符串值.
MAX()函數(shù)除了用來找出最大的列值或日期值之外,還可以返回任意列中的最大值,包括返回字符類型的最大值.在對字符類型數(shù)據(jù)進行比較時,按照字符的ASCII碼值大小進 行比較,從a~z,a的ASCII碼最小,z的最大.在比較時,先比較第一個字母,如果相等,繼續(xù)比較下一個字符,一直到兩個字符不相等或者字符結(jié)束為止.

7.3.5 MIN()函數(shù)

MIN()返回查詢列中的最小值.
MIN()也可以和GROUP BY關(guān)鍵字一起使用,求出每個分組中的最小值.
MIN()函數(shù)與MAX()函數(shù)類似,不僅適用于查找數(shù)值類型,也可應(yīng)用于字符類型.

7.4 連接查詢

連接是關(guān)系數(shù)據(jù)庫模型的主要特點.連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢,主要包括內(nèi)連接、外連接等.通過連接運算符可以實現(xiàn)多個表查詢.在關(guān)系數(shù)據(jù)庫管理系統(tǒng)中,表建立時各數(shù)據(jù)之間的關(guān)系不必確定,常把一個實體的所有信息存放在一個表中.當查詢數(shù)據(jù)時,通過連接操作查詢出存放在多個表中的不同實體的信息.當兩個或多個表中存在相同意義的字段時,便可以通過這些字段對不同的表進行連接査詢.本節(jié)將介紹多表之間的內(nèi)連接查詢、外連接查詢以及復合條件連接查詢.

7.4.1內(nèi)連接查詢

內(nèi)連接(INNER JOIN)使用比較運算符進行表間某(些)列數(shù)據(jù)的比較操作,并列出這些表中與連接條件相匹配的數(shù)據(jù)行,組合成新記錄,也就是說,在內(nèi)連接查詢中,只有滿足條件的記錄才能出現(xiàn)在結(jié)果關(guān)系中.

使用WHERE子句定義連接條件比較簡單明了,而INNER JOIN語法是ANSI SQL的標準規(guī)范,使用INNER JOIN連接語法能夠確保不會忘記連接條件,而且,WHERE子句在某些時候會影響查詢的性能.

如果在一個連接查詢中,涉及的兩個表都是同一個表,這種查詢稱為自連接查詢.自連接是一種特殊的內(nèi)連接,它是指相互連接的表在物理上為同一張表,但可以在邏輯上分為兩張表.

7.4.2外連接查詢

外連接查詢將查詢多個表中相關(guān)聯(lián)的行,內(nèi)連接時,返冋查詢結(jié)果集合中的僅是符合查詢條件和連接條件的行.但有時候需要包含沒有關(guān)聯(lián)的行中數(shù)據(jù),即返回查詢結(jié)果集合中的不僅包含符合連接條件的行,而且還包括左表(左外連接或左連接)、右表(右外連接或右連接)或兩個連接表(全外連接)中的所有數(shù)據(jù)行.外連接分為左外連接或左連接和右外連接或右連接:

• LEFT JOIN(左連接):返回包括左表中的所有記錄和右表中連接字段相等的記錄.
• RIGHT JOIN(右連接):返回包括右表中的所有記錄和左表中連接字段相等的記錄.

1.LEFT JOIN(左連接)
左連接的結(jié)果包括LEFT OUTER子句中指定的左表的所有行,而不僅僅是連接列所匹配的行.如果左表的某行在右表中沒有匹配行,則在相關(guān)聯(lián)的結(jié)果行中,右表的所有選擇列表列均為空值.

2. RIGHT JOIN(右連接)
   右連接是左連接的反向連接,將返回右表的所有行.如果右表的某行在左表中沒有匹配行,左表將返回空值.

7.4.3復合條件連接查詢

復合條件連接查詢是在連接查詢的過程中,通過添加過濾條件,限制查詢的結(jié)果,使查詢的結(jié)果更加準確.

7.5 子查詢

子查詢指一個查詢語句嵌套在另一個查詢語句內(nèi)部的查詢,這個特性從MySQL 4.1開始引入.在SELECT子句中先計算子查詢,子查詢結(jié)果作為外層另一個查詢的過濾條件,查詢可以基于一個表或者多個表.子查詢中常用的操作符有ANY(SOME)、ALL、IN、EXISTS.子查詢可以添加到SELECT, UPDATE和DELETE語句中,而且可以進行多層嵌套.子查詢 中也可以使用比較運算符,如“<”、“<=”、“>”、“>=”和“!=”等.本節(jié)將介紹如何 在SELECT語句中嵌套子查詢.

7.5.1 帶ANY、SOME關(guān)鍵字的子查詢

ANY和SOME關(guān)鍵字是同義詞,表示滿足其中任一條件,它們允許創(chuàng)建一個表達式對子查詢的返回值列表進行比較,只要滿足內(nèi)層子查詢中的任何一個比較條件,就返回一個結(jié)果作為外層查詢的條件.

ANY關(guān)鍵字接在一個比較操作符的后面,表示若與子查詢返回的任何值比較為TRUE, 則返回TRUE.

7.5.2 帶ALL關(guān)鍵字的子查詢

ALL關(guān)鍵字與ANY和SOME不同,使用ALL時需要同時滿足所有內(nèi)層查詢的條件.
ALL關(guān)鍵字接在一個比較操作符的后面,表示與子査詢返回的所有值比較為TRUE,則返回TRUE.

7.5.3 帶EXISTS關(guān)鍵字的子查詢

EXISTS關(guān)鍵字后面的參數(shù)是一個任意的子查詢,系統(tǒng)對子查詢進行運算以判斷它是否返回行,如果至少返回一行,那么EXISTS的結(jié)果為true,此吋外層查詢語句將進行查詢;如果子查詢沒有返冋任何行,那么EXISTS返回的結(jié)果是false,此時外層語句將不進行查詢.
EXISTS關(guān)鍵字可以和條件表達式一起使用.
NOT EXISTS與EXISTS使用方法相同,返回的結(jié)果相反.子查詢?nèi)绻辽俜祷匾恍?那么NOT EXISTS的結(jié)果為false,此時外層查詢語句將不進行查詢;如果子查詢沒有返回任何行,那么NOT EXISTS返回的結(jié)果是true,此時外層語句將進行查詢.

EXISTS和NOT EXISTS的結(jié)果只取決于是否會返回行,而不取決于這些行的內(nèi)容,所以這個子查詢輸入列表通常是無關(guān)緊要的.

7.5.4 帶IN關(guān)鍵字的子查詢

IN關(guān)鍵字進行子查詢時,內(nèi)層查詢語句僅僅返回一個數(shù)據(jù)列,這個數(shù)據(jù)列里的值將提供給外層查詢語句進行比較操作.

SELECT語句中可以使用NOT IN關(guān)鍵字,其作用與IN正好相反.

7.5.5帶比較運算符的子查詢

在前面介紹的帶ANY、ALL關(guān)鍵字的子查詢時使用了 “>”比較運算符,子査詢時還可 以使用其他的比較運算符,如、“<=”、“=”、“>=”和“!=”等.

7.6合并查詢結(jié)果

利用UNION關(guān)鍵字,可以給出多條SELECT語句,并將它們的結(jié)果組合成單個結(jié)果集.合并時,兩個表對應(yīng)的列數(shù)和數(shù)據(jù)類型必須相同.各個SELECT語句之間使用UNION或 UNION ALL關(guān)鍵字分隔.UNION不使用關(guān)鍵字ALL,執(zhí)行的時候刪除重復的記錄,所有返回的行都是唯一的;使用關(guān)鍵字ALL的作用是不刪除重復行也不對結(jié)果進行自動排序.
基本語法格式如下:
SELECT column,.. ..FROM tablel UNION [ALL] SELECT column,.‘ ..FROM table2
UNION從查詢結(jié)果集中自動去除了重復的行,如果要返回所有匹配行,而不進行刪 除,可以使用UNION ALL.

UNION和UNION ALL的區(qū)別:使用UNION ALL的功能是不刪除重復行,加上ALL關(guān)鍵字語句執(zhí)行時所需要的資源少,所以盡可能地使用它,因此知道有重復行但是想保留這些行,確定查詢結(jié)果中不會有重復數(shù)據(jù)或者不需要去掉重復數(shù)據(jù)的時候,應(yīng)當使用UNION ALL以提高查詢效率.

7.7 為表和字段取別名

7.7.1為表取別名

當表名字很長或者執(zhí)行一些特殊查詢時,為了方便操作或者需要多次使用相同的表時,可以為表指定別名,用這個別名替代表原來的名稱.為表取別名的基本語法格式為:
表名 [AS] 表別名
“表名”為數(shù)據(jù)庫中存儲的數(shù)據(jù)表的名稱,“表別名”為查詢時指定的表的新名稱,AS 關(guān)鍵字為可選參數(shù).

在為表取別名時,要保證不能與數(shù)據(jù)庫中的其他表的名稱沖突.

7.7.2為字段取別名

在使用SELECT語句顯示查詢結(jié)果時,MySQL會顯示毎個SELECT后面指定的輸出列,在有些情況下,顯示的列的名稱會很長或者名稱不 夠直觀,MySQL可以指定列別名,替換字段或表達式.為字段取別名的基本語法格式為:
列名 [AS] 列別名
“列名”為表中字段定義的名稱,‘‘列別名”為字段新的名稱,AS關(guān)鍵字為可選參數(shù).

表別名只在執(zhí)行查詢的時候使用,并不在返回結(jié)果中顯示,而列別名定義之后,將返回給客戶端顯示,顯示的結(jié)果字段為字段列的別名.

7.8 使用正則表達式查詢

正則表達式通常被用來檢索或替換那些符合某個模式的文本內(nèi)容,根據(jù)指定的匹配模式匹配文本中符合要求的特殊字符串.
正則表達式強大而且靈活,可以應(yīng)用于非常復雜的查詢.
MySQL中使用REGEXP關(guān)鍵字指定正則表達式的字符匹配模式.

正則表達式常用字符匹配列表
選項|說明|例子|匹配值示例
^{|匹配文本的開始字符|’}b’匹配以字母b開頭的|book, big, banana, bike 字符串
'匹配以st結(jié)尾的字符串|test, resist, persist
.|匹配任何單個字符|'b.t’匹配任何b和t之間有一個字符|bit, bat, but,bite
|匹配零個或多個在它前面的字符|'fn’匹配字符n前面有任意個字符f|fn, fan,faan, fabcn
+|匹配前面的字符1次或多次|'ba+ ’匹配以b開頭后面緊跟至少有一個a|ba, bay, bare, battle
<字符串>|匹配包含指定的字符串的文本|'fa'|fan,afa,faad
[字符集合]|匹配字符集合中的任何一個字符|'[xz]'匹配X或者z|dizzy, zebra, x-ray, extra
[^{]|匹配不在括號中的任何字符|'[}abc]'匹配任何不包含 a、b或c的字符串|desk, fox, fBke
字符串{n,}|匹配前面的字符串至少n次|b{2}匹配2個或更多的b|bbb,bbbb,bbbbbbb
字符串{n,m}|匹配前面的字符串至少n次,至多m 次.如果n為0,此參數(shù)為可選參數(shù)|b{2,4}匹配最少2個,最多4個b|bb,bbb,bbbb

7.8.1查詢以特定字符或字符串開頭的記錄

字符匹配以特定字符或者字符串開頭的文本.

7.8.2查詢以特定字符或字符串結(jié)尾的記錄

字符匹配以特定字符或者字符串結(jié)尾的文本.

7.8.3用符號來替代字符串中的任意一個字符

字符匹配任意一個字符.

7.8.4使用"*"和"+"來匹配多個字符

星號匹配前面的字符任意多次,包括0次.加號‘+’匹配前面的字符至少一次.

7.8.5匹配指定字符串

正則表達式可以匹配指定字符串,只要這個字符串在查詢文本中即可,如要匹配多個字符串,多個字符串之間使用分隔符隔開.

7.8.6匹配指定字符中的任意一個

方括號“[]”指定一個字符集合,只匹配其中任何一個字符,即為所查找的文本.
方括號“[]”還可以指定數(shù)值集合.
匹配集合“[456]”也可以寫成“[4-6]”即指定集合區(qū)間.例如“[a-z]”表示集合區(qū)間為 從a~z的字母,“[0-9]”表示集合區(qū)間為所有數(shù)字.

7.8.7匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符.

7.8.8使用{n,}或者{n,m}來指定字符串連續(xù)出現(xiàn)的次數(shù)

“字符串{n,}”表示至少匹配n次前面的字符;“字符串{n,m}w表示匹配前面的字符串 不少于n次,不多于m次.

7.9 綜合案例一據(jù)表查詢操作

7.10 專家解惑

疑問1 :DISTINCT可以應(yīng)用于所有的列嗎?
查詢結(jié)果中,如果需要對列進行降序排序,可以使用DESC,這個關(guān)鍵字只能對;其前面的列進行降序排列.例如,要對多列都進行降序排序,必須要在每一列的列名后面加DESC關(guān)鍵字.而DISTINCT不同,DISTINCT不能部分使用.換句話說,DISTINCT關(guān)鍵字應(yīng)用于所有列而不僅是它后面的第一個指定列.例如,查詢3個字段s_id,f^name. f_price,如果不同 記錄的這3個字段的組合值都不同,則所有記錄都會被查詢出來.

疑問2 :ORDER BY可以和LIMIT混合使用嗎?
在使用ORDER BY子句時,應(yīng)保證其位于FROM子句之后,如果使用LIMIT,則必須位于ORDER BY之后,如果子句順序不正確,MySQL將產(chǎn)生錯誤消息.

疑問3 :什么時候使用引號?
在査詢的時候,會看到在WHERE子句中使用條件,有的值加上了單引號,而有的值未加.單引號用來限定字符串,如果將值與字符串類型列進行比較,則需要限定引號;而用來與數(shù)值進行比較則不需要用引號.

疑問4 :在WHERE子句中必須使用圓括號嗎?
任何時候使用具有AND和OR操作符的WHERE子句,都應(yīng)該使用圓括號明確操作順序.如果條件較多,即使能確定計算次序,默認的計算次序也可能會使SQL語句不易理解,因此 使用括號明確操作符的次序,是一個良好的習慣.

疑問5 :為什么使用通配符格式正確,卻沒有查找出符合條件的記錄?
MySQL中存儲字符串數(shù)據(jù)時,可能會不小心把兩端帶有空格的字符串保存到記錄中,而在查看表中記錄時,MySQL不能明確地顯示空格,數(shù)據(jù)庫操作者不能直觀地確定字符串兩端是否有空格.例如,使用LIKE‘%e’匹配以字母e結(jié)尾的水果的名稱,如果字母e后面多了一個空格,則LIKE語句不能將該記錄查找出來.解決的方法是使用TRIM函數(shù),將字符串兩端的空格刪除之后再進行匹配.

第8章 插入、更新與刪除數(shù)據(jù)

8.1 插入數(shù)據(jù)

在使用數(shù)據(jù)庫之前,數(shù)據(jù)庫中必須要有數(shù)據(jù),MySQL中使用INSERT語句向數(shù)據(jù)庫表中 插入新的數(shù)據(jù)記錄.可以插入的方式有:插入 完整的記錄、插入記錄的一部分、插入多條記錄、 插入另一個查詢的結(jié)果,下面將分別介紹這些內(nèi)容.

8.1.1為表的所有字段插入數(shù)據(jù)

使用基本的INSERT語句插入數(shù)據(jù),要求指定表名稱和插入到新記錄中的值.基本語法格式為:
INSERT INTO table_name(column_list)VALUES(value_list);
table name指定要插入數(shù)據(jù)的表名,column list指定要插入數(shù)據(jù)的那些列,value list指定每個列應(yīng)對應(yīng)插入的數(shù)據(jù).注意,使用該語句時字段列和數(shù)據(jù)值的數(shù)量必須相同.

向表中所有字段插入值的方法有兩種:一種是指定所有字段名,另一種是完全不指定字段名.
插入數(shù)據(jù)時,不需要 按照表定義的順序插入,只要保證值的順序與列字段的順序相同就可以.
使用INSERT插入數(shù)據(jù)時,允許列名稱列表為空,此時,值列表中需要為表的每一個字段指定值,并且值的順序必須和數(shù)據(jù)表中字段定義時的順序相同.

雖然使用INSERT插入數(shù)據(jù)時可以忽略插入數(shù)據(jù)的列名稱,但是值如果不包含列名稱,那VALUES關(guān)鍵字后面的值不僅要求完整而且順序必須和表定義時列的順序相同.如果表的結(jié)構(gòu)被修改,對列進行增加、刪除或者位置改變操作,這些操作將使得用這種方式插入數(shù)據(jù)時的順序也同時改變.如果指定列名稱,則不會受到表結(jié)構(gòu)改變的影響.

8.1.2為表的指定字段插入數(shù)據(jù)

為表的指定字段插入數(shù)據(jù),就是在INSERT語句中只向部分字段中插入值,而其他字段的值為表定義時的默認值.

要保證每個插入值的類型和對應(yīng)列的數(shù)據(jù)類型匹配,如果類型不同,將無法插入,并且MySQL會產(chǎn)生錯誤.

8.1.3同時插入多條記錄

INSERT語句可以同時向數(shù)據(jù)表中插入多條記錄,插入時指定多個值列表,每個值列表之間用逗號分隔開
基本語法格式如下:
INSERT INTO table_name(column_list)
VALUES(value_list1),(value_list2),.. .,(value_listn);
value list1,value_list2,...,value_listn;表示第1,2,...,n個插入記錄的字段的值列表.

一個同時插入多行記錄的INSERT語句等同于多個單行插入的INSERT語句,但是多行INSERT語句在處理過程中,效率更高.因為MySQL 執(zhí)行單條INSERT語句插入多行數(shù)據(jù),比使用多條INSERT語句快,所以在插入多條記錄時,最好選擇使用單條INSERT語句的方式插入.

8.1.4 將查詢結(jié)果插入到表中

INSERT語句用來給數(shù)據(jù)表插入記錄時,指定插入記錄的列值.
INSERT還可以將SELECT語句查詢的結(jié)果插入到表中,如果想要從另外一個表中合并個人信息到person表,不需要把每一條記錄的值一個一個輸入,只需要使用一條INSERT語句和一條SELECT語句組成的組合語句,即可快速地從一個或多個表中向一個表中插入多個行.
基本語法格式如下:
INSERT INTO table_name1(column_list1)
SELECT(column_list2)FROM table_name2 WHERE(condition)
table name1指定待插入數(shù)據(jù)的表;column list1指定待插入表中要插入數(shù)據(jù)的哪些列; table_name2指定插入數(shù)據(jù)是從哪個表中查詢出來的;column_list2指定數(shù)據(jù)來源表的查詢列,該列表必須和column listl列表中的字段個數(shù)相同,數(shù)據(jù)類型相同;condition指定SELECT語句的查詢條件.

8.2更新數(shù)據(jù)

表中有數(shù)據(jù)之后,接下來可以對數(shù)據(jù)進行更新操作,MySQL中使用UPDA TE語句更新表 中的記錄,可以更新特定的行或者同時更新所有的行.基本語法結(jié)構(gòu)如下:
UPDATE table_name
SET column_name1=value1,column_name2=value2,...“.,column_namen=valuen
WHERE(condition);
column name 1 ,column_name2, ,column_namen 為指定更新的字段的名稱;value1, value2,.....valuen為相對應(yīng)的指定字段的更新值;condition指定更新的記錄需要滿足的條件.

保證UPDATE以WHERE子句結(jié)束,通過WHERE子句指定被更新的記錄所需要滿足的 條件,如果忽略WHERE子句,MySQL將更新表中所有的行.

8.3刪除數(shù)據(jù)

從數(shù)據(jù)表中刪除數(shù)據(jù)使用DELETE語句DELETE語句允許WHERE子句指定刪除條件.
DELETE語句基本語法格式如下:
DELETE FROM table_name [WHERE <condition>];
table_name指定要執(zhí)行刪除操作的表;“[WHERE <condition>]”為可選參數(shù),指定刪除條件,如果沒有WHERE子句,DELETE語句將刪除表中的所有記錄.

如果想刪除表中的所有記錄,還可以使用TRUNCATE TABLE語句,TRUNCATE將直接 刪除原來的表,并重新創(chuàng)建一個表,其語法結(jié)構(gòu)為TRUNCATE TABLE table_name. TRUNCATE直接刪除表而不是刪除記錄,因此執(zhí)行速度比DELETE快.

8.4 綜合案例——記錄的插入、更新和刪除

8.5 專家解惑

疑問1:插入記錄時可以不指定字段名稱嗎?
不管使用哪種INSERT語法,都必須給出VALUES的正確數(shù)目.如果不提供字段名,則必須給每個字段提供一個值,否則將產(chǎn)生一條錯誤消息.如果要在INSERT操作中省略某些字段,這些字段需要滿足一定條件:該列定義為允許空值;或者表定義時給出默認值,如果不給出值,將使用默認值.

疑問2 :更新或者刪除表時必須指定WHERE子句嗎?
在前面章節(jié)中可以看到,所有的UPDATE和DELETE語句全都在WHERE子句中指定了條件.如果省略WHERE子句,則UPDATE或DELETE將被應(yīng)用到表中所有的行.因此,除非確實打算更新或者刪除所有記錄,否則要注意使用不帶WHERE子句的UPDATE或DELETE語句.建議在對表進行更新和刪除操作之前,使用SELECT語句確認需要刪除的記錄,以免造成無法挽回的結(jié)果.