第四周 基於MYSQL的SQL數據分析6章

來自專欄數據分析之旅

第四周 基於MYSQL的SQL數據分析6章

本教程主要借鑒劉增傑老師《Mysql從入門到精通一書》，全文框架如下

第一章 SQL與資料庫介紹

第二章 資料庫工具

第三章 資料庫的基本操作

第四章 數據表的基本操作

第五章 數據類型和運算符

第六章 Mysql函數

第七章 查詢語句

第八章 插入、更新與刪除數據

第九章 SQL數據分析實戰案例

第6章 MySQL函數大全手冊

學習目標

函數很多， 不用都記下來，有個大概印象，作為函數大全手冊使用

6.1 數學函數

（1）ABS(x)

返回x的絕對值

（2）PI()

返回圓周率π，默認顯示6位小數

（3）SQRT(x)

返回非負數的x的二次方根

（4）MOD(x,y)

返回x被y除後的餘數

（5）CEIL(x)、CEILING(x)

返回不小於x的最小整數

（6）FLOOR(x)

返回不大於x的最大整數

（7）ROUND(x)、ROUND(x,y)

前者返回最接近於x的整數，即對x進行四捨五入；後者返回最接近x的數，其值保留到小數點後面y位，若y為負值，則將保留到x到小數點左邊y位

（8）SIGN(x)

返回參數x的符號，-1表示負數，0表示0，1表示正數

（9）POW(x,y)和、POWER(x,y)

返回x的y次乘方的值

（10）EXP(x)

返回e的x乘方後的值

（11）LOG(x)

返回x的自然對數，x相對於基數e的對數

（12）LOG10(x)

返回x的基數為10的對數

（13）RADIANS(x)

返回x由角度轉化為弧度的值

（14）DEGREES(x)

返回x由弧度轉化為角度的值

（15）SIN(x)、ASIN(x)

前者返回x的正弦，其中x為給定的弧度值；後者返回x的反正弦值，x為正弦

（16）COS(x)、ACOS(x)

前者返回x的餘弦，其中x為給定的弧度值；後者返回x的反餘弦值，x為餘弦

（17）TAN(x)、ATAN(x)

前者返回x的正切，其中x為給定的弧度值；後者返回x的反正切值，x為正切

（18）COT(x)

返回給定弧度值x的餘切

（19）RAND()、RAND(x)

返回0->1的隨機數，後者當x相同時返回一樣的隨機數。

6.2 字元串函數

1）CHAR_LENGTH(str)

計算字元串字元個數

（2）CONCAT(s1,s2，...)

返回連接參數產生的字元串，一個或多個待拼接的內容，任意一個為NULL則返回值為NULL

（3）CONCAT_WS(x,s1,s2,...)

返回多個字元串拼接之後的字元串，每個字元串之間有一個x

（4）INSERT(s1,x,len,s2)

返回字元串s1，其子字元串起始於位置x，被字元串s2取代len個字元

（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

前兩者將str中的字母全部轉換成小寫，後兩者將字元串中的字母全部轉換成大寫

（6）LEFT(s,n)、RIGHT(s,n)

前者返回字元串s從最左邊開始的n個字元，後者返回字元串s從最右邊開始的n個字元

（7）LPAD(s1,len,s2)、RPAD(s1,len,s2)

前者返回s1，其左邊由字元串s2填補到len字元長度，假如s1的長度大於len，則返回值被縮短至len字元；後者返回s1，其右邊由字元串s2填補到len字元長度，假如s1的長度大於len，則返回值被縮短至len字元

（8）LTRIM(s)、RTRIM(s)

前者返回字元串s，其左邊所有空格被刪除；後者返回字元串s，其右邊所有空格被刪除

（9）TRIM(s)

返回字元串s刪除了兩邊空格之後的字元串

（10）TRIM(s1 FROM s)

刪除字元串s兩端所有子字元串s1，未指定s1的情況下則默認刪除空格

（11）REPEAT(s,n)

返回一個由重複字元串s組成的字元串，字元串s的數目等於n

（12）SPACE(n)

返回一個由n個空格組成的字元串

（13）REPLACE(s,s1,s2)

返回一個字元串，用字元串s2替代字元串s中所有的字元串s1

（14）STRCMP(s1,s2)

若s1和s2中所有的字元串都相同，則返回0；根據當前分類次序，第一個參數小於第二個則返回-1，其他情況返回1

（15）SUBSTRING(s,n,len)、MID(s,n,len)

兩個函數作用相同，從字元串s中返回一個第n個字元開始、長度為len的字元串

（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

三個函數作用相同，返回子字元串str1在字元串str中的開始位置（從第幾個字元開始）

（17）REVERSE(s)

將字元串s反轉

（18）ELT(N,str1,str2,str3,str4,...)

返回第N個字元串

6.3 日期和時間函數

（1）CURDATE()、CURRENT_DATE()

將當前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具體格式根據函數用在字元串或是數字語境中而定

（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

這四個函數作用相同，返回當前日期和時間值，格式為"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具體格式根據函數用在字元串或數字語境中而定

（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

前者返回一個格林尼治標準時間1970-01-01 00:00:00到現在的秒數，後者返回一個格林尼治標準時間1970-01-01 00:00:00到指定時間的秒數

（4）FROM_UNIXTIME(date)

和UNIX_TIMESTAMP互為反函數，把UNIX時間戳轉換為普通格式的時間

（5）UTC_DATE()和UTC_TIME()

前者返回當前UTC（世界標準時間）日期值，其格式為"YYYY-MM-DD"或"YYYYMMDD"，後者返回當前UTC時間值，其格式為"YYYY-MM-DD"或"YYYYMMDD"。具體使用哪種取決於函數用在字元串還是數字語境中

（6）MONTH(date)和MONTHNAME(date)

前者返回指定日期中的月份，後者返回指定日期中的月份的名稱

（7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

DAYNAME(d)返回d對應的工作日的英文名稱，如Sunday、Monday等；DAYOFWEEK(d)返回的對應一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d對應的工作日索引，0表示周一，1表示周二

（8）WEEK(d)、WEEKOFYEAD(d)

前者計算日期d是一年中的第幾周，後者計算某一天位於一年中的第幾周

（9）DAYOFYEAR(d)、DAYOFMONTH(d)

前者返回d是一年中的第幾天，後者返回d是一月中的第幾天

（10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

YEAR(date)返回指定日期對應的年份，範圍是1970~2069；QUARTER(date)返回date對應一年中的季度，範圍是1~4；MINUTE(time)返回time對應的分鐘數，範圍是0~59；SECOND(time)返回制定時間的秒值

（11）EXTRACE(type FROM date)

從日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

（12）TIME_TO_SEC(time)

返回以轉換為秒的time參數，轉換公式為"3600*小時 + 60*分鐘 + 秒"

（13）SEC_TO_TIME()

和TIME_TO_SEC(time)互為反函數，將秒值轉換為時間格式

（14）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

返回將起始時間加上expr type之後的時間，比如DATE_ADD(2010-12-31 23:59:59, INTERVAL 1 SECOND)表示的就是把第一個時間加1秒

（15）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

返回將起始時間減去expr type之後的時間

（16）ADDTIME(date,expr)、SUBTIME(date,expr)

前者進行date的時間加操作，後者進行date的時間減操作

（17）DATE_FORMAT(date,』%Y %M %W %D)

指定格式輸出日期

6.4 條件判斷函數

（1）IF(expr,v1,v2)

如果expr是TRUE則返回v1，否則返回v2

（2）IFNULL(v1,v2)

如果v1不為NULL，則返回v1，否則返回v2

（3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

如果expr等於某個vn，則返回對應位置THEN後面的結果，如果與所有值都不想等，則返回ELSE後面的rn。

6.5 系統信息函數

（1）VERSION()

查看MySQL版本號

（2）CONNECTION_ID()

查看當前用戶的連接數

（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

查看當前被MySQL伺服器驗證的用戶名和主機的組合，一般這幾個函數的返回值是相同的

（4）CHARSET(str)

查看字元串str使用的字符集

（5）COLLATION()

查看字元串排列方式

6.6 加/解密函數

（1）PASSWORD(str)

從原明文密碼str計算並返回加密後的字元串密碼，注意這個函數的加密是單向的（不可逆），因此不應將它應用在個人的應用程序中而應該只在MySQL伺服器的鑒定系統中使用

（2）MD5(str)

為字元串算出一個MD5 128比特校驗和，改值以32位十六進位數字的二進位字元串形式返回

（3）ENCODE(str, pswd_str)

使用pswd_str作為密碼，加密str

（4）DECODE(crypt_str,pswd_str)

使用pswd_str作為密碼，解密加密字元串crypt_str，crypt_str是由ENCODE函數返回的字元串

6.7 其他函數

（1）FORMAT(x,n)

將數字x格式化，並以四捨五入的方式保留小數點後n位，結果以字元串形式返回

（2）CONV(N,from_base,to_base)

不同進位數之間的轉換，返回值為數值N的字元串表示，由from_base進位轉換為to_base進位

（3）INET_ATON(expr)

給出一個作為字元串的網路地址的點地址表示，返回一個代表該地址數值的整數，地址可以使4或8比特

（4）INET_NTOA(expr)

給定一個數字網路地址（4或8比特），返回作為字元串的該地址的點地址表示

（5）BENCHMARK(count,expr)

重複執行count次表達式expr，它可以用於計算MySQL處理表達式的速度，結果值通常是0（0隻是表示很快，並不是沒有速度）。另一個作用是用它在MySQL客戶端內部報告語句執行的時間

（6）CONVERT(str USING charset)

使用字符集charset表示字元串str

6.8 案例實戰

1、案例目的

使用各種函數操作數據，掌握各種函數的作用和使用方法。

2、操作過程

6.9 常見疑問