{"id":210,"date":"2011-12-04T16:15:00","date_gmt":"2011-12-04T16:15:00","guid":{"rendered":"6f3ef77ac0e3619e98159e9b6febf557"},"modified":"2011-12-04T16:15:00","modified_gmt":"2011-12-04T16:15:00","slug":"","status":"publish","type":"post","link":"https:\/\/www.xiaobo.li\/notes\/archives\/210","title":{"rendered":"mysql 5.0\u5b58\u50a8\u8fc7\u7a0b\u5b66\u4e60\u603b\u7ed3"},"content":{"rendered":"

\u4e00.\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b<\/span><\/p>\n

1.\u57fa\u672c\u8bed\u6cd5\uff1a<\/p>\n
create procedure sp_name()
\nbegin
\n.........
\nend\n<\/div>\n

\n2.\u53c2\u6570\u4f20\u9012\n<\/div>\n

\n\u4e8c.\u8c03\u7528\u5b58\u50a8\u8fc7\u7a0b<\/span><\/p>\n

1.\u57fa\u672c\u8bed\u6cd5\uff1acall sp_name()
\n\u6ce8\u610f\uff1a\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0\u540e\u9762\u5fc5\u987b\u52a0\u62ec\u53f7\uff0c\u54ea\u6015\u8be5\u5b58\u50a8\u8fc7\u7a0b\u6ca1\u6709\u53c2\u6570\u4f20\u9012\n<\/div>\n

\n\u4e09.\u5220\u9664\u5b58\u50a8\u8fc7\u7a0b<\/span><\/p>\n

1.\u57fa\u672c\u8bed\u6cd5\uff1a
\ndrop procedure sp_name\/\/
\n2.\u6ce8\u610f\u4e8b\u9879
\n(1)\u4e0d\u80fd\u5728\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u5220\u9664\u53e6\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\uff0c\u53ea\u80fd\u8c03\u7528\u53e6\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\n<\/div>\n

\n\u56db.\u533a\u5757\uff0c\u6761\u4ef6\uff0c\u5faa\u73af
\n<\/span><\/p>\n

1.\u533a\u5757\u5b9a\u4e49\uff0c\u5e38\u7528
\nbegin
\n......
\nend;
\n\u4e5f\u53ef\u4ee5\u7ed9\u533a\u5757\u8d77\u522b\u540d\uff0c\u5982\uff1a
\nlable:begin
\n...........
\nend lable;
\n\u53ef\u4ee5\u7528leave lable;\u8df3\u51fa\u533a\u5757\uff0c\u6267\u884c\u533a\u5757\u4ee5\u540e\u7684\u4ee3\u7801
\n2.\u6761\u4ef6\u8bed\u53e5<\/p>\n
\n
\"\"if<\/span> \u6761\u4ef6 <\/span>then<\/span>
\n\"\"statement
\n\"\"<\/span>else<\/span>
\n\"\"statement
\n\"\"<\/span>end<\/span> <\/span>if<\/span>;<\/span><\/div>\n<\/div>\n

\n3.\u5faa\u73af\u8bed\u53e5
\n(1).while\u5faa\u73af<\/p>\n

\n
\"\"[<\/span>label:<\/span>]<\/span> <\/span>WHILE<\/span> expression DO
\n\"\"
\n\"\"statements
\n\"\"
\n\"\"<\/span>END<\/span> <\/span>WHILE<\/span> <\/span>[<\/span>label<\/span>]<\/span> ;
\n\"\"<\/span><\/div>\n<\/div>\n

\n<\/div>\n

<\/p>\n

(2).loop\u5faa\u73af<\/p>\n
\n
\"\"[<\/span>label:<\/span>]<\/span> LOOP
\n\"\"
\n\"\"statements
\n\"\"
\n\"\"<\/span>END<\/span> LOOP <\/span>[<\/span>label<\/span>]<\/span>;<\/span><\/div>\n<\/div>\n

\n<\/div>\n

<\/p>\n

(3).repeat until\u5faa\u73af<\/p>\n
\n
\"\"[<\/span>label:<\/span>]<\/span> REPEAT
\n\"\"
\n\"\"statements
\n\"\"
\n\"\"UNTIL expression
\n\"\"
\n\"\"<\/span>END<\/span> REPEAT <\/span>[<\/span>label<\/span>]<\/span> ;<\/span><\/div>\n<\/div>\n

\n<\/div>\n

\n\u4e94.\u5176\u4ed6\u5e38\u7528\u547d\u4ee4<\/span><\/p>\n

1.show procedure status
\n\u663e\u793a\u6570\u636e\u5e93\u4e2d\u6240\u6709\u5b58\u50a8\u7684\u5b58\u50a8\u8fc7\u7a0b\u57fa\u672c\u4fe1\u606f\uff0c\u5305\u62ec\u6240\u5c5e\u6570\u636e\u5e93\uff0c\u5b58\u50a8\u8fc7\u7a0b\u540d\u79f0\uff0c\u521b\u5efa\u65f6\u95f4\u7b49
\n2.show create procedure sp_name
\n\u663e\u793a\u67d0\u4e00\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u7684\u8be6\u7ec6\u4fe1\u606f\n<\/div>\n<\/p>\n

<\/p>\n

\n

mysql\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u8981\u7528\u5230\u7684\u8fd0\u7b97\u7b26<\/p>\n<\/div>\n

\n
\n

mysql\u5b58\u50a8\u8fc7\u7a0b\u5b66\u4e60\u603b\u7ed3\uff0d\u64cd\u4f5c\u7b26<\/h2>\n<\/div>\n

\u7b97\u672f\u8fd0\u7b97\u7b26<\/strong><\/p>\n

+     \u52a0   SET var1=2+2;       4
\n-     \u51cf   SET var2=3-2;       1
\n*     \u4e58   SET var3=3*2;       6
\n\/     \u9664   SET var4=10\/3;      3.3333
\nDIV   \u6574\u9664 SET var5=10 DIV 3;  3
\n%     \u53d6\u6a21 SET var6=10%3 ;     1<\/p>\n

\u6bd4\u8f83\u8fd0\u7b97\u7b26<\/strong><\/p>\n

<\/p>\n

>            \u5927\u4e8e 1>2 False
\n<            \u5c0f\u4e8e 2<1 False
\n<=           \u5c0f\u4e8e\u7b49\u4e8e 2<=2 True
\n>=           \u5927\u4e8e\u7b49\u4e8e 3>=2 True
\nBETWEEN      \u5728\u4e24\u503c\u4e4b\u95f4 5 BETWEEN 1 AND 10 True
\nNOT BETWEEN  \u4e0d\u5728\u4e24\u503c\u4e4b\u95f4 5 NOT BETWEEN 1 AND 10 False
\nIN           \u5728\u96c6\u5408\u4e2d 5 IN (1,2,3,4) False
\nNOT IN       \u4e0d\u5728\u96c6\u5408\u4e2d 5 NOT IN (1,2,3,4) True
\n=            \u7b49\u4e8e 2=3 False
\n<>, !=       \u4e0d\u7b49\u4e8e 2<>3 False
\n<=>          \u4e25\u683c\u6bd4\u8f83\u4e24\u4e2aNULL\u503c\u662f\u5426\u76f8\u7b49 NULL<=>NULL True
\nLIKE         \u7b80\u5355\u6a21\u5f0f\u5339\u914d \"Guy Harrison\" LIKE \"Guy%\" True
\nREGEXP       \u6b63\u5219\u5f0f\u5339\u914d \"Guy Harrison\" REGEXP \"[Gg]reg\" False
\nIS NULL      \u4e3a\u7a7a 0 IS NULL False
\nIS NOT NULL  \u4e0d\u4e3a\u7a7a 0 IS NOT NULL True<\/div>\n

<\/p>\n

\n\u903b\u8f91\u8fd0\u7b97\u7b26<\/strong><\/p>\n

\u4e0e<\/strong>(AND)<\/p>\n

\n


\n
\n <\/p>\n