mysql 高級(jí)語句
一堤器、存儲(chǔ)過程
1.什么是存儲(chǔ)過程:
就是一組SQL語句集，功能強(qiáng)大劲腿，可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能肴楷。
ps:存儲(chǔ)過程跟觸發(fā)器有點(diǎn)類似，都是一組SQL集扬绪，但是存儲(chǔ)過程是主動(dòng)調(diào)用的竖独，且功能比觸發(fā)器更加強(qiáng)大，觸發(fā)器是某件事觸發(fā)后自動(dòng)調(diào)用挤牛；
2.優(yōu)勢
有輸入輸出參數(shù)莹痢，可以聲明變量，有if/else, case,while等控制語句，通過編寫存儲(chǔ)過程竞膳，可以實(shí)現(xiàn)復(fù)雜的邏輯功能航瞭；
函數(shù)的普遍特性：模塊化，封裝顶猜，代碼復(fù)用沧奴；
速度快，只有首次執(zhí)行需經(jīng)過編譯和優(yōu)化步驟长窄，后續(xù)被調(diào)用可以直接執(zhí)行，省去以上步驟纲菌；
3.樣式
MySQL存儲(chǔ)過程創(chuàng)建的格式：CREATE PROCEDURE過程名([過程參數(shù)[,...]])
[特性...]過程體
IN輸入?yún)?shù):表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過程時(shí)指定挠日，在存儲(chǔ)過程中修改該參數(shù)的值不能被返回，為默認(rèn)值
OUT輸出參數(shù):該值可在存儲(chǔ)過程內(nèi)部被改變翰舌，并可返回
INOUT輸入輸出參數(shù):調(diào)用時(shí)指定嚣潜，并且可被改變和返回
4.創(chuàng)建存儲(chǔ)過程
3.1簡單案例：
#創(chuàng)建簡單的存儲(chǔ)過程
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER //? ? #定界符
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int) #傳參數(shù)a,b輸出sum
BEGIN
#Routine body goes here...
DECLARE c int;? #定義常量
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum? = a + b;
END
//
DELIMITER ;
set @b=5;
call proc_adder(2,@b,@s); #調(diào)用函數(shù)
select @s as sum;
if 語句
DROP PROCEDURE IF EXISTS `proc_if`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_if`(IN type int)
BEGIN
#Routine body goes here...
DECLARE c varchar(500);
IF type = 0 THEN
set c = 'param is 0';
ELSEIF type = 1 THEN
set c = 'param is 1';
ELSE
set c = 'param is others, not 0 or 1';
END IF;
select c;
END
//
DELIMITER ;
set @type=1;
call proc_if(@type);
2.case 語句
set @type=1;
call proc_if(@type);
DROP PROCEDURE IF EXISTS `proc_case`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_case`(IN type int)
BEGIN
#Routine body goes here...
DECLARE c varchar(500);
CASE type
WHEN 0 THEN
set c = 'param is 0';
WHEN 1 THEN
set c = 'param is 1';
ELSE
set c = 'param is others, not 0 or 1';
END CASE;
select c;
END
//
DELIMITER ;
set @type=1;
call proc_if(@type);
3.while 語句
DROP PROCEDURE IF EXISTS `proc_while`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_while`(IN n int)
BEGIN
#Routine body goes here...
DECLARE i int;
DECLARE s int;
SET i = 0;
SET s = 0;
WHILE i <= n DO
set s = s + i;
set i = i + 1;
END WHILE;
SELECT s;
END
//
DELIMITER ;
set @type=1;
call proc_if(@type);
二，存儲(chǔ)過程操作
-- 查看所有的存儲(chǔ)過程
show PROCEDURE status;
-- 查看特定數(shù)據(jù)庫存儲(chǔ)過程
show PROCEDURE status where db='test';
-- 用指定的登錄名查看該用戶創(chuàng)建的存儲(chǔ)過程
show PROCEDURE status where definer='root@localhost';? -- @localhost為用戶登錄位置(本地登錄)
-- 查看指定時(shí)間段創(chuàng)建存儲(chǔ)過程
show PROCEDURE status where created between '2017-02-17 00:00:00'
and '2017-02-17 23:59:59';
用系統(tǒng)表mysql.proc來查看：
-- 查看所有的存儲(chǔ)過程信息
select * from mysql.proc;
-- 查看特定數(shù)據(jù)庫里的存儲(chǔ)過程
select * from mysql.proc where db='test';
-- 查看某個(gè)用戶定義的存儲(chǔ)過程
select * from mysql.proc where definer='root@localhost';
-- 查看某時(shí)間段創(chuàng)建的存儲(chǔ)過程
select * from mysql.proc where created between '2017-02-17 00:00:00'
and '2017-02-17 23:59:59';
5.存儲(chǔ)過程刪除
刪除一個(gè)存儲(chǔ)過程比較簡單椅贱，和刪除表一樣：
DROP PROCEDURE
三, MySQL存儲(chǔ)過程的基本函數(shù)
(1).字符串類
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //連接字串
INSTR (string ,substring ) //返回substring首次在string中出現(xiàn)的位置,不存在返回0
LCASE (string2 ) //轉(zhuǎn)換成小寫
LEFT (string2 ,length ) //從string2中的左邊起取length個(gè)字符
LENGTH (string ) //string長度
LOAD_FILE (file_name ) //從文件讀取內(nèi)容
LOCATE (substring , string [,start_position ] )同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重復(fù)用pad加在string開頭,直到字串長度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重復(fù)count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
RPAD (string2 ,length ,pad) //在str后用pad補(bǔ)充,直到長度為length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比較兩字串大小,
SUBSTRING (str , position [,length ]) //從str的position開始,取length個(gè)字符
,注：mysql中處理字符串時(shí)懂算，默認(rèn)第一個(gè)字符下標(biāo)為1，即參數(shù)position必須大于等于1
UCASE (string2 ) //轉(zhuǎn)換成大寫
RIGHT(string2,length) //取string2最后length個(gè)字符
SPACE(count) //生成count個(gè)空格
(2).數(shù)學(xué)類
ABS (number2 ) //絕對值
BIN (decimal_number ) //十進(jìn)制轉(zhuǎn)二進(jìn)制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //進(jìn)制轉(zhuǎn)換
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小數(shù)位數(shù)
HEX (DecimalNumber ) //轉(zhuǎn)十六進(jìn)制
注：HEX()中可傳入字符串庇麦，則返回其ASC-11碼计技，如HEX('DEF')返回4142143也可以傳入十進(jìn)制整數(shù)，返回其十六進(jìn)制編碼山橄，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指數(shù)
RAND([seed]) //隨機(jī)數(shù)
ROUND (number [,decimals ]) //四舍五入,decimals為小數(shù)位數(shù)]
(3).日期時(shí)間類
ADDTIME (date2 ,time_interval ) //將time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉(zhuǎn)換時(shí)區(qū)
CURRENT_DATE ( ) //當(dāng)前日期
CURRENT_TIME ( ) //當(dāng)前時(shí)間
CURRENT_TIMESTAMP ( ) //當(dāng)前時(shí)間戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時(shí)間
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式顯示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上減去一個(gè)時(shí)間
DATEDIFF (date1 ,date2 ) //兩個(gè)日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1為星期天
DAYOFYEAR (date ) //一年中的第幾天
EXTRACT (interval_name FROM date ) //從date中提取日期的指定部分
MAKEDATE (year ,day ) //給出年及年中的第幾天,生成日期串
MAKETIME (hour ,minute ,second ) //生成時(shí)間串
MONTHNAME (date ) //英文月份名
NOW ( ) //當(dāng)前時(shí)間
SEC_TO_TIME (seconds ) //秒數(shù)轉(zhuǎn)成時(shí)間
STR_TO_DATE (string ,format ) //字串轉(zhuǎn)成時(shí)間,以format格式顯示
TIMEDIFF (datetime1 ,datetime2 ) //兩個(gè)時(shí)間差
TIME_TO_SEC (time ) //時(shí)間轉(zhuǎn)秒數(shù)]
WEEK (date_time [,start_of_week ]) //第幾周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第幾天
HOUR(datetime) //小時(shí)
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符號(hào),正負(fù)或0
SQRT(number2) //開平方
四垮媒，PHP對存儲(chǔ)過程的調(diào)用
實(shí)例一：無參的存儲(chǔ)過程
$conn = mysql_connect('localhost','root','root') or die ("數(shù)據(jù)連接錯(cuò)誤!!!");
mysql_select_db('test',$conn);
$sql = "
create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end;
";
mysql_query($sql);//創(chuàng)建一個(gè)myproce的存儲(chǔ)過程
$sql = "call test.myproce();";
mysql_query($sql);//調(diào)用myproce的存儲(chǔ)過程，則數(shù)據(jù)庫中將增加一條新記錄航棱。
實(shí)例二：使用變量的存儲(chǔ)過程
$sql = "
create procedure myproce5(in a int,in b int)
begin
declare s int default 0;
set s=a+b;
select s;
end;
";
mysql_query($sql);//創(chuàng)建一個(gè)myproce5的存儲(chǔ)過程
$sql = "call test.myproce5(4,6);";
mysql_query($sql);//調(diào)用myproce5的存儲(chǔ)過程,在cmd下面看效果
轉(zhuǎn)自：http://blog.csdn.net/u011871037/article/details/51546605的博客睡雇。
一，mysql光標(biāo) 儲(chǔ)存
1.declare ? 聲明光標(biāo)
2.open ?打開光標(biāo)
3.fetch 獲取光標(biāo)記錄饮醇，并賦值
4.close 關(guān)閉光標(biāo)
