Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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º¯Êý


ÓÃÔÚSELECTºÍWHERE×Ó¾äµÄº¯Êý
--------------------------------------------------------------
1 ·Ö×麯Êý
( ... ) À¨ºÅ¡£Ê¹ÓÃËüÃÇÀ´Ç¿ÖÆÔÚÒ»¸ö±í´ïʽµÄ¼ÆËã˳Ðò¡£
--------------------------------------------------------------
2 ³£ÓõÄËãÊõÔËËã
£¨+ - * /£©
Ò»°ãµÄË ......

MYSQL my.cnfÅäÖÃÖÐÎÄÏê½â

MYSQL--my.cnfÅäÖÃÖÐÎÄÏê½â ZZ2007Äê08ÔÂ16ÈÕ ÐÇÆÚËÄ 15:53mysqld³ÌÐò--Ŀ¼ºÍÎļþ
ÒýÓÃ
basedir = path   ʹÓøø¶¨Ä¿Â¼×÷Ϊ¸ùĿ¼(°²×°Ä¿Â¼)¡£
character-sets-dir = path   ¸ø³ö´æ·Å×Å×Ö·û¼¯µÄĿ¼¡£
datadir = path   ´Ó¸ø¶¨Ä¿Â¼¶ÁÈ¡Êý¾Ý¿âÎļþ¡£
pid-file = filename   Î ......

MYSQL×Ô´ø¹¤¾ßQuery ProfilerʹÓÃ

Query ProfilerÊÇMYSQL×Ô´øµÄÒ»ÖÖqueryÕï¶Ï·ÖÎö¹¤¾ß£¬Í¨¹ýËü¿ÉÒÔ·ÖÎö³öÒ»ÌõSQLÓï¾äµÄÐÔÄÜÆ¿¾±ÔÚʲôµØ·½¡£Í¨³£ÎÒÃÇÊÇʹÓõÄexplain,ÒÔ¼°slow query log¶¼ÎÞ·¨×öµ½¾«È··ÖÎö£¬µ«ÊÇQuery ProfilerÈ´¿ÉÒÔ¶¨Î»³öÒ»ÌõSQLÓï¾äÖ´Ðеĸ÷ÖÖ×ÊÔ´ÏûºÄÇé¿ö£¬±ÈÈçCPU£¬IOµÈ£¬ÒÔ¼°¸ÃSQLÖ´ÐÐËùºÄ·ÑµÄʱ¼äµÈ¡£²»¹ý¸Ã¹¤¾ßÖ»ÓÐÔÚMYSQL 5.0.37ÒÔ ......

MySQLѧϰ(3) ÊÓͼ£¬Óα꣬Óï¾ä¿é¶ù

1.MySQL´´½¨ÊÓͼµÄ¼òµ¥Óï¾äΪ£ºCREATE VIEW view_name  AS SELECT * from TABLE_NAME;
²»ÄܶԴ¥·¢Æ÷´´½¨ÊÓͼ(VIEW)¡£µ«ÊÇÔÚ5.1.4ÖÐÊÇÕâÑùÃèÊöµÄ£¬A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries¡£´óÖÂÒâ˼ÊÇÊÓÍ ......

mysql errno: 150 ´íÎó Íâ¼ü

ERROR 1005 (HY000): Can't create table '  ****.frm' (errno: 150)
ÎÒÊÇ´ÓÒÔϼ¸¸ö·½Ãæ½â¾öÁË´ËÎÊÌ⣺
1¡¢È·±£²ÎÕյıíºÍ×Ö¶ÎÊÇ´æÔڵģ»
2¡¢×é³ÉÍâ¼üµÄ×Ö¶ÎÒªÇó±»Ë÷Òý(Ö÷ÒªÊÇÍâ¼üÄǸö×Ö¶ÎÒªÇóÔÚÆäËû±íÖÐÊÇÖ÷¼ü)£»
3¡¢Íâ¼ü¹ØÁªµÄÁ½±í»ò¶à±íÒªÇó¶¼ÊÇINNODBÀàÐÍµÄ±í£»
4¡¢×Ö¶ÎÀàÐÍ£¨ËµÃ÷£©ÒªÒ»Ñù`itemId` varchar( ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ