實訓案例名稱：MySQL流程控制函數(shù)一、任務介紹MySQL流程控制函數(shù)用于控制SQL語句中實現(xiàn)條件的選擇。二、實現(xiàn)步驟1.使用流程控制函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.06sec)#使用IF()函數(shù)，判斷年齡是否大于18，是則輸出“成年”，否則輸出“未成年”mysql>SELECTname,IF(age>18,'成年','未成年')FROMuser;+-------+----------------------------+|name|IF(age>18,'成年','未成年') |+-------+----------------------------+|david|成年 ||小紅|成年 ||小明|未成年 ||小剛|未成年 ||小王|未成年 ||小綠|成年 ||曉峰|未成年 ||小影|成年 ||大梅|成年 |+-------+----------------------------+9rowsinset(0.01sec)#使用IFNULL()函數(shù)，判斷年齡是否為NULL，是則輸出默認值“0”，不是則返回age值mysql>SELECTname,IFNULL(age,0)FROMuser;+-------+---------------+|name|IFNULL(age,0)|+-------+---------------+|david|28||小紅|27||小明|10||小剛|12||小王|14||小綠|34||曉峰|15||小影|26||大梅|27|+-------+---------------+9rowsinset(0.01sec)#使用NULLIF()函數(shù)，判斷用戶名和密碼是否相等，相等則返回NULL，否則返回用戶名mysql>SELECTname,NULLIF(name,password)FROMuser;+-------+-----------------------+|name|NULLIF(name,password)|+-------+-----------------------+|david|david||小紅|小紅||小明|小明||小剛|小剛||小王|小王||小綠|小綠||曉峰|曉峰||小影|小影||大梅|大梅|+-------+-----------------------+9rowsinset(0.01sec)#使用CASE語句，判斷性別為女時，輸出“女生”；判斷性別為男時，輸出“男生”mysql>SELECTname,CASEsexWHEN'女'THEN'女生'WHEN'男'THEN'男生'ELSE

'未知'ENDasresultFROMuser;+-------+--------+|name|result|+-------+--------+|david|女生||小紅|女生||小明|男生||小剛|男生||小王|男生||小綠|女生||曉峰|男生||小影|女生||大梅|女生|+-------+--------+9rowsinset(0.02sec)mysql>實訓案例名稱：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|+---------------------------------+1rowinset(0.00sec)mysql>SELECTTIME_FORMAT('21:34:45','%h-%i-%s');+------------------------------------+|TIME_FORMAT('21:34:45','%h-%i-%s')|+------------------------------------+|09-34-45|+------------------------------------+1rowinset(0.00sec)（3）格式化IP地址函數(shù)INET_ATON(ip)和INET_NTOA(num)：INET_ATON(ip)將IP地址轉(zhuǎn)換為數(shù)字，INET_NTOA(num)將數(shù)字轉(zhuǎn)換為IP地址。mysql>SELECTINET_ATON('192.168.1.100');+----------------------------+|INET_ATON('192.168.1.100')|+----------------------------+|3232235876|+----------------------------+1rowinset(0.07sec)mysql>SELECTINET_NTOA(3232235876);+-----------------------+|INET_NTOA(3232235876)|+-----------------------+|192.168.1.100|+-----------------------+1rowinset(0.09sec)（4）格式化浮點數(shù)函數(shù)FORMAT(x,y)：把x格式化為以逗號分隔的數(shù)字序列，y是結(jié)果的小數(shù)位數(shù)。mysql>SELECTFORMAT(2367.6537,2);+---------------------+|FORMAT(2367.6537,2)|+---------------------+|2,367.65|+---------------------+1rowinset(0.13sec)mysql>SELECTFORMAT('36828.67628',3);+-------------------------+|FORMAT('36828.67628',3)|+-------------------------+|36,828.676|+-------------------------+1rowinset(0.06sec)實訓案例名稱：自定義函數(shù)一、任務介紹MySQL數(shù)據(jù)庫提供了一些內(nèi)置的函數(shù)，如數(shù)學函數(shù)、字符串函數(shù)等。但是往往在開發(fā)過程中，有些業(yè)務場景僅靠現(xiàn)有的內(nèi)置函數(shù)并不能滿足，這時就可以自定義函數(shù)，來滿足自己的業(yè)務需求。MySQL數(shù)據(jù)庫設計的擴展性允許通過自定義函數(shù)的功能來解決這個問題。二、實現(xiàn)步驟1.函數(shù)的基本語法（1）創(chuàng)建自定義函數(shù)CREATEFUCTION函數(shù)名(參數(shù)列表)RETURNS返回值類型函數(shù)體示例如下。mysql>DELIMITER$$mysql>CREATEFUNCTIONfun(a1varchar(255))RETURNSVARCHAR(255)BEGINDECLARExVARCHAR(255)DEFAULT'';SETx=CONCAT(a1,'word');RETURNx;END$$（2）查看自定義函數(shù)SHOWFUNCTIONSTATUSlike'function_name';SHOWCREATEFUNCTIONfunction_name;示例如下。SHOWFUNCTIONSTATUSlike'fun';SHOWCREATEFUNCTIONfun;（3）調(diào)用自定義函數(shù)SELECTfunction_name(parameter_value,)示例如下。mysql>SELECTFUN();（4）修改自定義函數(shù)ALTERFUNCTIONfunction_name[characteristic]characteristic:{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'示例如下。mysql>ALTERFUNCTIONfunREADSSQLDATACOMMENT'字符串連接';（5）刪除自定義函數(shù)DROPFUNCTIONfunction_nameDROPFUNCTIONIFEXISTSfunction_name示例如下。mysql>DROPFUNCTIONfun;2.創(chuàng)建不帶參數(shù)的自定義函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>useshop;Databasechanged#修改默認的結(jié)束符“;”為“$$”，以后的SQL語句都要以“$$”結(jié)尾mysql>DELIMITER$$#創(chuàng)建日期和時間函數(shù)getFormatDatetime()mysql>CREATEFUNCTIONgetFormatDatetime()RETURNSVARCHAR(255)RETURNDATE_FORMAT(NOW(),'%Y年%m月%d日%h時%i分%s秒');$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符為分號(;)mysql>DELIMITER;#調(diào)用getFormatDatetime()函數(shù)mysql>SELECTgetFormatDatetime();+-------------------------------+|getFormatDatetime()|+-------------------------------+|2023年12月03日10時56分28秒|+-------------------------------+1rowinset(0.02sec)mysql>3.創(chuàng)建帶參數(shù)的自定義函數(shù)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#修改結(jié)束符mysql>DELIMITER$$#創(chuàng)建自定義函數(shù)addFun()mysql>CREATEFUNCTIONaddFun(xintUNSIGNED,yintUNSIGNED)RETURNSintBEGINDECLAREsumintUNSIGNEDDEFAULT0;SETsum=x+y;RETURNsum;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用addFun()函數(shù)mysql>SELECTaddFun(10,26);+---------------+|addFun(10,26)|+---------------+|36|+---------------+1rowinset(0.00sec)#修改addFun()函數(shù)mysql>ALTERFUNCTIONaddFunREADSSQLDATACOMMENT'加法自定義函數(shù)';QueryOK,0rowsaffected(0.00sec)#刪除addFun()函數(shù)mysql>DROPFUNCTIONaddFun;QueryOK,0rowsaffected(0.00sec)#先判斷addFun()函數(shù)是否存在，再刪除addFun()函數(shù)mysql>DROPFUNCTIONIFEXISTSaddFun;QueryOK,0rowsaffected,1warning(0.00sec)#如果addFun()函數(shù)不存在，則當沒有判斷直接刪除時，會報錯mysql>DROPFUNCTIONaddFun;ERROR1305(42000):FUNCTIONshop.addFundoesnotexistmysql>實訓案例名稱：存儲過程一、任務介紹存儲過程是完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中，在使用的時候直接調(diào)用存儲過程來執(zhí)行就可以，省略了語法校驗、編譯的過程，大大提高了SQL語句的執(zhí)行效率。二、實現(xiàn)步驟1.存儲過程的基本語法（1）創(chuàng)建存儲過程CREATEPROCEDURE存儲過程名([[IN|OUT|INOUT]參數(shù)名參數(shù)類型[,[IN|OUT|INOUT]參數(shù)名參數(shù)類型]])[特性]BEGIN存儲過程體END示例如下。mysql>DELIMITER$$mysql>CREATEPROCEDUREgetUserCountBySex(INp_sexvarchar(255),OUTp_countint)BEGINSELECTCOUNT(*)INTOp_countFROMuserWHEREsex=p_sex;END$$mysql>DELIMITER;mysql>SET@p_sex='男';mysql>SET@p_count=0;mysql>CALLgetUserCountBySex(@p_sex,@p_count);mysql>SELECT@p_sex,@p_count;+--------+----------+|@p_sex|@p_count|+--------+----------+|男|4|+--------+----------+（2）查看存儲過程SHOWPROCEDURESTATUSlike'procedure_name';SHOWCREATEPROCEDUREprocedure_name;示例如下。SHOWPROCEDURESTATUSlike'getUserCountBySex';SHOWCREATEPROCEDUREgetUserCountBySex;（3）調(diào)用存儲過程CALLprocedure_name(parameter_value,)mysql>DELIMITER$$mysql>CREATEPROCEDUREgetUserCountBySex(INp_sexvarchar(255),OUTp_countint)BEGINDECLAREnameVARCHAR(5)DEFAULT'局部變量';SELECTCOUNT(*)INTOp_countFROMuserWHEREsex=p_sex;END$$示例如下。mysql>DELIMITER;mysql>SET@p_sex='男';mysql>SET@p_count=0;mysql>CALLgetUserCountBySex(@p_sex,@p_count);（4）修改存儲過程ALTERPROCEDUREprocedure_name[characteristic]characteristic:{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'示例如下。mysql>ALTERPROCEDUREgetUserCountBySexREADSSQLDATACOMMENT'字符串連接';（5）刪除存儲過程DROPPROCEDUREprocedure_nameDROPPROCEDUREIFEXISTSprocedure_name示例如下。mysql>DROPPROCEDUREgetUserCountBySex;2.創(chuàng)建不帶參數(shù)的存儲過程#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#修改結(jié)束符mysql>DELIMITER$$#創(chuàng)建查詢用戶表user的存儲過程mysql>CREATEPROCEDUREgetUserInfo()BEGINSELECT*FROMuser;END$$QueryOK,0rowsaffected(0.06sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程mysql>CALLgetUserInfo();+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.01sec)#不帶參數(shù)的存儲過程，在調(diào)用的時候可以省略括號mysql>CALLgetUserInfo;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)QueryOK,0rowsaffected(0.03sec)mysql>3.創(chuàng)建帶有IN類型參數(shù)的存儲過程#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#修改結(jié)束符mysql>DELIMITER$$#創(chuàng)建帶有IN類型參數(shù)的存儲過程，根據(jù)性別查詢用戶記錄mysql>CREATEPROCEDUREgetUserBySex(INp_sexvarchar(255))BEGINSELECTp_sex;SELECT*FROMuserWHEREsex=p_sex;SETp_sex='未知';SELECTp_sex;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程，輸入?yún)?shù)可以直接在括號中賦值mysql>CALLgetUserBySex('女');+-------+|p_sex|+-------+|女|+-------+1rowinset(0.01sec)+----+-------+------+------+----------+|id |name|sex|age|password

|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||8 |小綠|女|34|222222||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+5rowsinset(0.02sec)+-------+|p_sex|+-------+|未知|+-------+1rowinset(0.05sec)QueryOK,0rowsaffected(0.06sec)#IN類型參數(shù)也可以通過定義用戶變量@p_sex來傳入mysql>SET@p_sex='男';QueryOK,0rowsaffected(0.00sec)#傳入用戶變量，調(diào)用存儲過程mysql>CALLgetUserBySex(@p_sex);+-------+|p_sex|+-------+|男|+-------+1rowinset(0.00sec)+----+------+------+------+----------+|id|name|sex|age|password|+----+------+------+------+----------+|5|小明|男|10|123456||6|小剛|男|12|123456||7|小王|男|14|111111||9|曉峰|男|15|333333|+----+------+------+------+----------+4rowsinset(0.01sec)+-------+|p_sex|+-------+|未知|+-------+1rowinset(0.08sec)QueryOK,0rowsaffected(0.10sec)#在存儲過程中修改IN類型參數(shù)，但是并不能返回，所以@p_sex還是修改前的值mysql>SELECT@p_sex;+--------+|@p_sex|+--------+|男|+--------+1rowinset(0.00sec)mysql>4.創(chuàng)建帶有IN和OUT類型參數(shù)的存儲過程#使用root用戶名和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#修改結(jié)束符mysql>DELIMITER$$#創(chuàng)建帶有IN和OUT類型參數(shù)的存儲過程，根據(jù)性別查詢用戶記錄mysql>CREATEPROCEDUREgetCountBySex(INp_sexvarchar(255),OUTp_countint)BEGINSELECTCOUNT(*)INTOp_countFROMuserWHEREsex=p_sex;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#傳入輸入?yún)?shù)，定義輸出參數(shù)@p_count，調(diào)用存儲過程mysql>CALLgetCountBySex('女',@p_count);QueryOK,1rowaffected(0.07sec)#查詢輸出參數(shù)@p_sexmysql>SELECT@p_count;+--------+|@p_sex|+--------+|5|+--------+1rowinset(0.00sec)#定義輸入?yún)?shù)@p_sexmysql>SET@p_sex='男';QueryOK,0rowsaffected(0.00sec)#定義輸出參數(shù)@p_countmysql>SET@p_count=0;QueryOK,0rowsaffected(0.00sec)#調(diào)用存儲過程mysql>CALLgetCountBySex(@p_sex,@p_count);QueryOK,1rowaffected(0.00sec)#查詢輸出結(jié)果mysql>SELECT@p_count;+----------+|@p_count|+----------+|4|+----------+1rowinset(0.00sec)mysql>5.創(chuàng)建帶有多個OUT類型參數(shù)的存儲過程#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#修改結(jié)束符mysql>DELIMITER$$#創(chuàng)建存儲過程，輸入一個參數(shù)，輸出兩個參數(shù)mysql>CREATEPROCEDUREgetSexCountById(INp_idint,OUTp_m_countint,OUTp_f_countint)BE