實訓案例名稱：MySQL常用函數(shù)一、任務介紹MySQL數(shù)據(jù)庫內(nèi)置了一些常用函數(shù)，包括數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)和格式化函數(shù)。這些常用函數(shù)方便了我們?nèi)粘?shù)據(jù)庫、對表以及查詢等的操作。二、實現(xiàn)步驟1.使用數(shù)學函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#將12.6向上取整mysql>SELECTCEILING(12.6);+---------------+|CEILING(12.6)|+---------------+|13|+---------------+1rowinset(0.01sec)#將27.4向下取整mysql>SELECTFLOOR(27.4);+-------------+|FLOOR(27.4)|+-------------+|27|+-------------+1rowinset(0.00sec)#將54362.8792四舍五入、保留2位小數(shù)mysql>SELECTROUND(54362.8792,2);+---------------------+|ROUND(54362.8792,2)|+---------------------+|54362.88|+---------------------+1rowinset(0.00sec)#將678.3478不四舍五入、保留2位小數(shù)mysql>SELECTTRUNCATE(678.3478,2);+----------------------+|TRUNCATE(678.3478,2)|+----------------------+|678.34|+----------------------+1rowinset(0.00sec)#將3除以5取余數(shù)mysql>SELECTMOD(3,5);+----------+|MOD(3,5)|+----------+|3|+----------+1rowinset(0.00sec)#將3除以5取余數(shù)mysql>SELECT3%5;+------+|3%5|+------+#3除以5mysql>SELECT3/5;+--------+|3/5|+--------+|0.6000|+--------+1rowinset(0.00sec)2.使用字符串函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#獲取字符串"hello"的字符數(shù)mysql>SELECTLENGTH('hello');+-----------------+|LENGTH('hello')|+-----------------+|5|+----------+1rowinset(0.00sec)#將“hello”的所有字母轉(zhuǎn)換為大寫mysql>SELECTUPPER('hello');+----------------+|UPPER('hello')|+----------------+|HELLO|+----------------+1rowinset(0.00sec)#將“WORLD”的所有字母轉(zhuǎn)換為小寫mysql>SELECTLOWER('WORLD');+----------------+|LOWER('WORLD')|+----------------+|world|+----------------+1rowinset(0.00sec)#比較“hello”和“world”的大小mysql>SELECTSTRCMP('hello','world');+--------------------------+|STRCMP('hello','world')|+--------------------------+|-1|+--------------------------+1rowinset(0.00sec)#將字符串“hello”中的字母“e”替換為“abc”mysql>SELECTREPLACE('hello','e','abc');+----------------------------+|REPLACE('hello','e','abc')|+----------------------------+|habcllo|+----------------------------+1rowinset(0.00sec)#將“hello”和“world”拼接成一個字符串mysql>SELECTCONCAT('hello','world');+-------------------------+|CONCAT('hello','world')|+-------------------------+|helloworld|+-------------------------+1rowinset(0.00sec)#將“hello”和“world”使用分隔符“-”拼接成一個字符串mysql>SELECTCONCAT_WS('-','hello','world');+--------------------------------+|CONCAT_WS('-','hello','world')|+--------------------------------+|hello-world|+-----------------------------+1rowinset(0.00sec)#截取“helloworld”字符串，從第2個位置開始截取長度為5的字符串mysql>SELECTSUBSTRING('helloworld',2,5);+-----------------------------+|SUBSTRING('helloworld',2,5)|+-----------------------------+|ellow|+-----------------------------+1rowinset(0.00sec)mysql>3.使用日期和時間函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#獲取當前日期和時間mysql>SELECTNOW();+---------------------+|NOW()

|+---------------------+|2023-11-2923:38:58|+---------------------+1rowinset(0.00sec)#獲取當前日期mysql>SELECTCURDATE();+------------+|CURDATE()|+------------+|2023-11-29|+------------+1rowinset(0.00sec)#獲取當前時間mysql>SELECTCURTIME();+-----------+|CURTIME()|+-----------+|23:39:24|+-----------+1rowinset(0.00sec)#獲取日期中的年份mysql>SELECTYEAR('2023-11-30');+--------------------+|YEAR('2023-11-30')|+--------------------+| 2023 |+--------------------+1rowinset(0.00sec)#獲取日期中的月份mysql>SELECTMONTH('2023-11-30');+---------------------+|MONTH('2023-11-30')|+---------------------+| 11 |+---------------------+1rowinset(0.00sec)#獲取指定日期為一年中的第幾天mysql>SELECTDAYOFYEAR('2023-11-30');+-------------------------+|DAYOFYEAR('2023-11-30')|+-------------------------+| 334 |+-------------------------+1rowinset(0.00sec)#獲取指定日期為一周中的第幾天mysql>SELECTDAYOFWEEK('2023-11-30');+--------------------------+|DAYOFWEEK('2023-11-30')|+--------------------------+| 5 |+--------------------------+1rowinset(0.00sec)#獲取時間中的小時值mysql>SELECTHOUR('11:20:59');+------------------+|HOUR('11:20:59')|+------------------+| 11|+------------------+1rowinset(0.00sec)#獲取時間中的分鐘值mysql>SELECTMINUTE('11:30:59');+---------------------+|MINUTE('11:30:59')|+---------------------+| 30 |+---------------------+1rowinset(0.00sec)#獲取時間中的秒值mysql>SELECTSECOND('11:30:59');+---------------------+|SECOND('11:30:59')|+---------------------+| 59 |+---------------------+1rowinset(0.00sec)#將時間往后加3年mysql>SELECTDATE_ADD(NOW(),INTERVAL3YEAR);+---------------------------------+|DATE_ADD(NOW(),INTERVAL3YEAR)|+---------------------------------+|2026-11-2923:41:45 |+---------------------------------+1rowinset(0.00sec)#將時間往后加3個月mysql>SELECTDATE_ADD(NOW(),INTERVAL3MONTH);+----------------------------------+|DATE_ADD(NOW(),INTERVAL3MONTH)|+----------------------------------+|2024-02-2823:42:31 |+----------------------------------+1rowinset(0.00sec)#將時間往后加3天mysql>SELECTDATE_ADD(NOW(),INTERVAL3DAY);+--------------------------------+|DATE_ADD(NOW(),INTERVAL3DAY)|+--------------------------------+|2023-12-0223:42:35 |+--------------------------------+1rowinset(0.00sec)#將時間往前減3天mysql>SELECTDATE_SUB(NOW(),INTERVAL3DAY);+--------------------------------+|DATE_SUB(NOW(),INTERVAL3DAY)|+--------------------------------+|2023-11-2623:43:03 |+--------------------------------+1rowinset(0.00sec)#將時間往前減3個月mysql>SELECTDATE_SUB(NOW(),INTERVAL3MONTH);+----------------------------------+|DATE_SUB(NOW(),INTERVAL3MONTH)|+----------------------------------+|2023-08-2623:43:03|+----------------------------------+1rowinset(0.01sec)mysql>4.使用系統(tǒng)信息函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#獲取當前數(shù)據(jù)庫的版本號mysql>SELECTVERSION();+-----------+|VERSION()|+-----------+|8.0.33|+-----------+1rowinset(0.00sec)#獲取服務器的連接數(shù)mysql>SELECTCONNECTION_ID();+-----------------+|CONNECTION_ID()|+-----------------+|37|+-----------------+1rowinset(0.00sec)#獲取當前數(shù)據(jù)庫名mysql>SELECTSCHEMA();+----------+|SCHEMA()|+----------+|NULL|+----------+1rowinset(0.00sec)#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#獲取當前數(shù)據(jù)庫名mysql>SELECTSCHEMA();+----------+|SCHEMA()|+----------+|shop|+----------+1rowinset(0.00sec)#獲取當前用戶mysql>SELECTUSER();+----------------+|USER()|+----------------+|root@localhost|+----------------+1rowinset(0.00sec)#獲取當前用戶mysql>SELECTCURRENT_USER();+----------------+|CURRENT_USER()|+----------------+|root@localhost|+----------------+1rowinset(0.00sec)#獲取字符串的編碼集mysql>SELECTCHARSET('hello');+------------------+|CHARSET('hello')|+------------------+|utf8|+------------------+1rowinset(0.00sec)#獲取最近生成的自增字段的值mysql>SELECTLAST_INSERT_ID();+------------------+|LAST_INSERT_ID()|+------------------+|0|+------------------+1rowinset(0.00sec)mysql>5.加密函數(shù)（1）PASSWORD(str)加密函數(shù)：可以對字符串str進行加密，采用MySQL的SHA1加密方式，生成的是41位字符串，其中*不加入實際的密碼運算。PASSWORD(str)加密函數(shù)常用于對用戶的密碼進行加密。mysql>SELECTPASSWORD('123456');+-------------------------------------------+|PASSWORD('123456')|+-------------------------------------------+|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|+-------------------------------------------+（2）MD5(str)加密函數(shù)：可以對字符串str進行散列加密，計算字符串str的MD5校驗和，常用于一些不需要解密的數(shù)據(jù)。mysql>SELECTMD5('123456');+----------------------------------+|MD5('123456')|+----------------------------------+|e10adc3949ba59abbe56e057f20f883e|+----------------------------------+（3）加密函數(shù)進行加密，加密后生成解密字符串密鑰；再使用DECODE(crypt_str,pswd_str)解密函數(shù)和密鑰進行解密。mysql>SELECTENCODE('xiaogang','key');+--------------------------+|ENCODE('xiaogang','key')|+--------------------------+|?1'?&?4|+--------------------------+1rowinset,1warning(0.00sec)mysql>SELECTDECODE(ENCODE('xiaogang','key'),'key');+----------------------------------------+|DECODE(ENCODE('xiaogang','key'),'key')|+----------------------------------------+|xiaogang|+----------------------------------------+（4）AES_ENCRYPT(str,key)與AES_DECRYPT(str,key)加、解密函數(shù)：一對加、解密函數(shù)，AES_ENCRYPT(str,key)用密鑰key對字符串利用高級加密標準算法進行加密，加密結(jié)果是一個二進制字符串，以BLOB類型的字段進行存儲；AES_DECRYPT(str,key)用密鑰key對字符串str利用高級加密標準算法進行解密。mysql>SELECTAES_ENCRYPT('xiaogang','key');+-------------------------------+|AES_ENCRYPT('xiaogang','key')|+-------------------------------+|y??3??t?3?0|+-------------------------------+1rowinset(0.00sec)mysql>SELECTAES_DECRYPT(AES_ENCRYPT('xiaogang','key'),'key');+--------------------------------------------------+|AES_DECRYPT(AES_ENCRYPT('xiaogang','key'),'key')|+--------------------------------------------------+|xiaogang|+--------------------------------------------------+1rowinset(0.00sec)（5）校驗和。mysql>SELECTSHA('123456');+------------------------------------------+|SHA('123456')|+------------------------------------------+|7c4a8d09ca3762af61e59520943dc26494f8941b|+------------------------------------------+1rowinset(0.00sec)6.格式化函數(shù)（1）mysql>SELECTDATE_FORMAT(NOW(),'%W,%D%M%Y%r');+-----------------------------------------+|DATE_FORMAT(NOW(),'%W,%D%M%Y%r')|+-----------------------------------------+|Thursday,30thNovember202308:44:29PM|+-----------------------------------------+1rowinset(0.13sec)mysql>SELECTDATE_FORMAT(NOW(),'%Y-%m-%d');+-------------------------------+|DATE_FORMAT(NOW(),'%Y-%m-%d')|+-------------------------------+|2023-11-30|+-------------------------------+1rowinset(0.00sec)mysql>SELECTDATE_FORMAT(19990330,'%Y-%m-%d');+----------------------------------+|DATE_FORMAT(19990330,'%Y-%m-%d')|+----------------------------------+|1999-03-30|+----------------------------------+1rowinset(0.11sec)mysql>SELECTDATE_FORMAT(NOW(),'%h:%i%p');+-------------------------------+|DATE_FORMAT(NOW(),'%h:%i%p')|+-------------------------------+|08:44PM|+-------------------------------+（2）mysql>SELECTTIME_FORMAT(NOW(),'%h%i');+----------------------------+|TIME_FORMAT(NOW(),'%h%i')|+----------------------------+|0933|+----------------------------+1rowinset(0.00sec)mysql>SELECTTIME_FORMAT('21:34:45','%h%i');+---------------------------------+|TIME_FORMAT('21:34:45','%h%i')|+---------------------------------+|0934|