mysql?洢???????????

?????洢?????????????????????????????????????????????????????????????????????
????--------------------??????--------------------
?????.?????洢????
????create procedure sp_name()
????begin
????.........
????end
??????.????洢????
????1.????????call sp_name()
????????洢?????????????????????????洢??????в???????
??????.????洢????
????1.????????
????drop procedure sp_name//
????2.???????
????(1)??????????洢???????????????洢?????????????????洢????
??????.????????????
????1.show procedure status
?????????????????д洢??洢??????????????????????????洢?????????????????
????2.show create procedure sp_name
???????????MySQL?洢???????????
????--------------------??????????????--------------------
????????????????????
??????
??????????????
??????????????DECLARE a INT ; SET a=100; ?????????????棺DECLARE a INT DEFAULT 100;
??????????????????????????????????????????????????
???????????????????????????@???????????????????3??????????????
????1?? ??mysql???????????????
????mysql> SELECT 'Hello World' into @x;
????mysql> SELECT @x;
????mysql> SET @y='Goodbye Cruel World';
????mysql> select @y;
????mysql> SET @z=1+2+3;
????mysql> select @z;
????2?? ??洢????????????????
????mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting??' World');
????mysql> SET @greeting='Hello';
????mysql> CALL GreetWorld( );
????3?? ??洢???????????Χ?????????
????mysql> CREATE PROCEDURE p1( ) SET @last_procedure='p1';
????mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was '??@last_procedure);
????mysql> CALL p1( );
????mysql> CALL p2( );
???????????????
????1.?????????
????+ ?? SET var1=2+2; 4
????- ?? SET var2=3-2; 1
????* ?? SET var3=3*2; 6
????/ ?? SET var4=10/3; 3.3333
????DIV ???? SET var5=10 DIV 3; 3
????% ?? SET var6=10%3 ; 1
????2.????????
????> ???? 1>2 False
????< С?? 2<1 False
????<= С????? 2<=2 True
????>= ??????? 3>=2 True
????BETWEEN ???????? 5 BETWEEN 1 AND 10 True
????NOT BETWEEN ?????????? 5 NOT BETWEEN 1 AND 10 False
????IN ??????? 5 IN (1??2??3??4) False
????NOT IN ????????? 5 NOT IN (1??2??3??4) True
????= ???? 2=3 False
????<>?? != ?????? 2<>3 False
????<=> ?????????NULL??????? NULL<=>NULL True
????LIKE ??????? "Guy Harrison" LIKE "Guy%" True
????REGEXP ???????? "Guy Harrison" REGEXP "[Gg]reg" False
????IS NULL ??? 0 IS NULL False
????IS NOT NULL ????? 0 IS NOT NULL True
????3.????????
????4.λ?????
????| ??
????& ??
????<< ????λ
????>> ????λ
????~ ??(?????????λ???)
????????
????mysql?洢??????????????????
?????????--
?????÷???????????????
????c???/* ??????? */ ?????????????
????--------------------???????--------------------
???????????
?????????????
????if
????case
?????????????
????for???
????while???
????loop???
????repeat until???
???????
???????鶨?壬????
????begin
????......
????end;
??????????????????????磺
????lable:begin
????...........
????end lable;
??????????leave lable;???????飬??????????????
????begin??end???C?????е?{ ?? }??
????--------------------????????--------------------
????mysql?洢????????????洢???????壬????????????????IN??OUT??INOUT
????Create procedure|function([[IN |OUT |INOUT ] ?????? ????????...])
????IN ???????
????????ò???????????????洢????????????洢?????????ò?????????????????????
????OUT ???????
???????????洢???????????????????
????INOUT ???????????
???????????????????????????
????IN?????????
????CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
????BEGIN
????SELECT p_in; --??????????
????SET p_in=2; --???
????select p_in;--????????
????END;
??????н??:
????mysql> set @p_in=1
????mysql> call sp_demo_in_parameter(@p_in)
??????
????mysql> select @p_in;
??????
????????????????p_in?????洢?????б??????????????@p_id???
????OUT????????
????????:
????mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
????BEGIN
????SELECT p_out;/*?????????*/
????SET p_out=2;/*???????*/
????SELECT p_out;/*?????з?仯*/
????END;
??????н??:
????mysql> SET @p_out=1
????mysql> CALL sp_demo_out_parameter(@p_out)
??????
????mysql> SELECT @p_out;
??????
????INOUT?????????
????mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
????BEGIN
????SELECT p_inout;
????SET p_inout=2;
????SELECT p_inout;
????END;
??????н????
????set @p_inout=1
????call sp_demo_inout_parameter(@p_inout) //
??????
????select @p_inout;
??????
??????????????
????mysql?洢???????????????????????????????????????????
??????????????
????CHARSET(str) //????????????
????CONCAT (string2 [??… ]) //???????
????INSTR (string ??substring ) //????substring?????string?г????λ????????????0
????LCASE (string2 ) //?????Сд
????LEFT (string2 ??length ) //??string2?е???????length?????
????LENGTH (string ) //string????
????LOAD_FILE (file_name ) //????????????
????LOCATE (substring ?? string [??start_position ] ) ?INSTR????????????λ??
????LPAD (string2 ??length ??pad ) //?????pad????string????????????????length
????LTRIM (string2 ) //????????
????REPEAT (string2 ??count ) //???count??
????REPLACE (str ??search_str ??replace_str ) //??str????replace_str?滻search_str
????RPAD (string2 ??length ??pad) //??str????pad????????????length
????RTRIM (string2 ) //????????
????STRCMP (string1 ??string2 ) //???????????????С??
????SUBSTRING (str ?? position [??length ]) //??str??position??????length???????
???????mysql?д??????????????????????±??1????????position??????????1
????mysql> select substring(’abcd’??0??2);
????+———————–+
????| substring(’abcd’??0??2) |
????+———————–+
????| |
????+———————–+
????1 row in set (0.00 sec)
????mysql> select substring(’abcd’??1??2);
????+———————–+
????| substring(’abcd’??1??2) |
????+———————–+
????| ab |
????+———————–+
????1 row in set (0.02 sec)
????TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //??????λ?????????
????UCASE (string2 ) //??????д
????RIGHT(string2??length) //?string2??length?????
????SPACE(count) //????count?????