MySQLѧϰ(1)Procedure,TRIGGER,FUNTCION
MySQLµÄÒ²Ö§³Ö´æ´¢¹ý³Ì£¬º¯Êý£¬´¥·¢Æ÷¡£
ÒÔϸ÷¸ö¹¦ÄܵÄÏêÊö£¬Ö÷ÒªÊÇʾÀý¡£
/**1.¶¨Òå·Ö¸î·û*/
DELIMITER //
/**2.´´½¨º¯Êý-¼ÆËãÁ½ÊýÖ®ºÍ*/
CREATE FUNCTION f_sum(num1 INT,num2 INT) RETURNS INT
BEGIN
RETURN num1+num2;
END;
//
/**
*3.´´½¨´æ´¢¹ý³Ì
*˼·£º3.1.Óиô洢¹ý³ÌÔòɾ³ý£¬3.2ÓÐʹÓõÄÖмä±íҲɾ³ý£¬²¢´´½¨¸Ã±í²åÈëÊý¾Ý£¬3.3ͳ¼Æ±íÖй²ÓжàÉÙÊý¾Ý²¢Í¨¹ýÊä³ö²ÎÊý·µ»Ø¸øµ÷ÓÃÕß 3.4ɾ³ýÖмäʹÓõ½µÄ±í
*/
DROP PROCEDURE IF EXISTS p_count ;//
DROP TABLE IF EXISTS T;//
CREATE TABLE T(i_num INT);//
INSERT INTO T VALUES(1),(2),(3);//
CREATE PROCEDURE p_count(OUT param INT)
BEGIN
SELECT COUNT(*) INTO param from T;
DROP TABLE T;
END;//
CALL p_count(@a);//
/**
*4.´´½¨´¥·¢Æ÷
*/
DROP TABLE IF EXISTS t;//
DROP TABLE IF EXISTS tx;//
CREATE TABLE tx(a INT);//
CREATE TABLE t(a INT);//
INSERT INTO t VALUES(2),(3),(4);//
CREATE TRIGGER tr_insert BEFORE INSERT ON t FOR EACH ROW
BEGIN
temp INT;
SELECT COUNT(*) INTO temp from t;
INSERT INTO tx VALUES(temp);
INSERT INTO tx SET a = NEW.a+1;
INSERT INTO tx VALUES(NEW.A+2);
END;
//
INSERT INTO t VALUES(1);
/**
*5.ʱ¼äÀàÐÍDATE,DATTIME,TIME,TIMESTAMP,YEAR
*²åÈëÊýֵʱ£º¿ÉÒÔÊÇ6£¬8£¬12£¬14λ£¬Î»Êý²»Í¬£¬MySQL²ÉÓõĸñʽ»¯·½Ê½Ò²²»Í¬¡£
*6-YYMMDD,8-YYYYMMDD,12-YYMMDDHHMMSS,14-YYYYMMDDHHMMSS
*²åÈë×Ö·û´®Ê±¸ù¾ÝÒÔÉÏ¿ÉÒÔÀàÍÆ,¾ßÌå¿ÉÒԲο¼MySQLµÄʹÓÃÊÖ²á
*/
DROP TABLE IF EXISTS t;//
CREATE TABLE t(ts TIMESTAMP);//
INSERT INTO t VALUES('09:02:01');//
INSERT INTO t VALUES(090101);
DROP TABLE IF EXISTS t£»//
CREATE TABLE 1P(a INT);
ÒÔÉÏËùÓж¼Í¨¹ý²âÊÔ¡£
MySQLµÄÁ½¸öº¯ÊýSELECT VERSION();--µÃµ½·þÎñÆ÷°æ±¾ÐÅÏ¢
SELECT CURRENT_DATE();--µÃµ½ÏµÍ³µ±Ç°ÈÕÆÚ£¬ÁíÍ⻹ÓÐCURRENT_TIMEµÃµ½µ±Ç°Ê±¼ä¡£
DELIMITER--·Ö¸ô·û£¬¶¨Ò幦ÄÜÓï¾ä½áÊøµÄ±êʶ,ĬÈÏΪ";"¡£
ÔÚд´æ´¢¹ý³ÌµÈ»áÉæ¼°µ½¶à¸ö";"£¬ÓÃËü×÷·Ö¸ô·ûÊDz»»á±àÒëͨ¹ýµÄ¡£
DELIMITER //£¬¶¨Òå"//"Ϊ·Ö¸ô·û£¬×÷ΪÕâ¸öÓï¾ä¿é¶ùµÄ½áÊø±êÖ¾¡£
CALL p£ßcount(@a);@aÔÚ¸ÃÁ¬½ÓÄÚÓÐЧ¡£
ÍØÕ¹£º½«£Í£ù£Ó£Ñ£Ì¿Í»§¶ËµÄËùÓÐÏÔʾÊý¾ÝÊä³öµ½Öƶ¨Îļþ£¬µÇ¼ºó mysql>tee D:/log/log.log
×¢ÒâÎļþµÄĿ¼һ¶¨Ò
Ïà¹ØÎĵµ£º
MySQLÎå¸ö²éѯÓÅ»¯·½·¨
ÊìϤSQLµÄÈ˶¼ÖªµÀ£¬Íê³Éͬһ¸öÈÎÎñ£¬SQL¿ÉÄÜÓжàÖÖд·¨£¬µ«²»Í¬Ð´·¨µÄ²éѯ
ÐÔÄÜ¿ÉÄÜ»áÓÐÌìÈÀÖ®±ð£¬±¾ÎÄÁоٳöÎå¸ö²éѯÓÅ»¯µÄ·½·¨
£¬µ±È»£¬ÓÅ»¯µÄ·½·¨»¹Óкܶࡣ¡¡¡¡
1¡¢ÓÅ»¯Êý¾ÝÀàÐÍ
MySQL
ÖÐÊý¾ÝÀàÐÍÓжàÖÖ£¬Èç¹ûÄãÊÇÒ»ÃûDBA£¬ÕýÔÚ°´ÕÕÓÅ»¯µÄÔÔò¶ÔÊý¾ÝÀàÐͽøÐÐÑϸñµÄ¼ì²é£¬µ«¿ª·¢
ÈËÔ ......
MySQL
ÊÇÒ»¸ö·Ç³£Á÷ÐеÄСÐ͹ØÏµÐÍÊý¾Ý¿â¹ÜÀíϵͳ£¬2008Äê1ÔÂ16ºÅ±»Sun¹«Ë¾ÊÕ¹º¡£Ä¿Ç°MySQL±»¹ã·ºµØÓ¦ÓÃÔÚInternetÉϵÄÖÐСÐÍÍøÕ¾ÖС£ÓÉ
ÓÚÆäÌå»ýС¡¢Ëٶȿ졢×ÜÌåÓµÓгɱ¾µÍ£¬ÓÈÆäÊÇ¿ª·ÅÔ´ÂëÕâÒ»ÌØµã£¬Ðí¶àÖÐСÐÍÍøÕ¾ÎªÁ˽µµÍÍøÕ¾×ÜÌåÓµÓгɱ¾¶øÑ¡ÔñÁËMySQL×÷ÎªÍøÕ¾Êý¾Ý¿â¡£
1¡¢phpMyAdmin£¨http://www.phpmyadmin.ne ......
ERROR 1005 (HY000): Can't create table ' ****.frm' (errno: 150)
ÎÒÊÇ´ÓÒÔϼ¸¸ö·½Ãæ½â¾öÁË´ËÎÊÌ⣺
1¡¢È·±£²ÎÕյıíºÍ×Ö¶ÎÊÇ´æÔڵģ»
2¡¢×é³ÉÍâ¼üµÄ×Ö¶ÎÒªÇó±»Ë÷Òý(Ö÷ÒªÊÇÍâ¼üÄǸö×Ö¶ÎÒªÇóÔÚÆäËû±íÖÐÊÇÖ÷¼ü)£»
3¡¢Íâ¼ü¹ØÁªµÄÁ½±í»ò¶à±íÒªÇó¶¼ÊÇINNODBÀàÐÍµÄ±í£»
4¡¢×Ö¶ÎÀàÐÍ£¨ËµÃ÷£©ÒªÒ»Ñù`itemId` varchar( ......
µ¼³öÊÇ,ûÓÐÖ¸¶¨±àÂë
·½·¨Ò» phpmyadmin´¦Àí·½Ê½
¼ì²éphp-mbstringÊÇ·ñÓпÉ×°,ûÓа²×°ÉÏ yum -y install php-mbstring
´ò¿ªphpmyadmin Öе¼³ö,ÔÙ´Óphpmyadmin µ¼ÈëÊÇѡȡ¶þÖÆ½ø(binary)
Èç¹û»¹²»ÐÐ,ÔÚmy.cnf mysqldÖмÓÈë default-character=ÄãµÄ±àÂë, ÖØÆômysql ÔÙÊÔ ......
create procedure getInfor2()
BEGIN
declare _abc varchar(200);
declare stopFlag int;
DECLARE cursor_name CURSOR
FOR
select zhuanyuan from server;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
OPEN cursor_name;
REPEAT
FETCH cursor_name INTO _abc;
begin
if _abc='root'
the ......