MySql ´æ´¢¹ý³Ì
MySQL ´æ´¢¹ý³ÌÊÇ´Ó MySQL 5.0 ¿ªÊ¼Ôö¼ÓµÄй¦ÄÜ¡£´æ´¢¹ý³ÌµÄÓŵãÓÐÒ»Âá¿ð¡£²»¹ý×îÖ÷ÒªµÄ»¹ÊÇÖ´ÐÐЧÂʺÍSQL ´úÂë·â×°¡£ÌرðÊÇ SQL ´úÂë·â×°¹¦ÄÜ£¬Èç¹ûûÓд洢¹ý³Ì£¬ÔÚÍⲿ³ÌÐò·ÃÎÊÊý¾Ý¿âʱ£¨ÀýÈç PHP£©£¬Òª×éÖ¯ºÜ¶à SQL Óï¾ä¡£ÌرðÊÇÒµÎñÂß¼¸´ÔÓµÄʱºò£¬Ò»´ó¶ÑµÄ SQL ºÍÌõ¼þ¼ÐÔÓÔÚ PHP ´úÂëÖУ¬ÈÃÈ˲»º®¶øÀõ¡£ÏÖÔÚÓÐÁË MySQL ´æ´¢¹ý³Ì£¬ÒµÎñÂß¼¿ÉÒÔ·â×°´æ´¢¹ý³ÌÖУ¬ÕâÑù²»½öÈÝÒ×ά»¤£¬¶øÇÒÖ´ÐÐЧÂÊÒ²¸ß¡£
Ò»¡¢MySQL ´´½¨´æ´¢¹ý³Ì
“pr_add” ÊǸö¼òµ¥µÄ MySQL ´æ´¢¹ý³Ì£¬Õâ¸ö´æ´¢¹ý³ÌÓÐÁ½¸ö int ÀàÐ͵ÄÊäÈë²ÎÊý “a”¡¢“b”£¬·µ»ØÕâÁ½¸ö²ÎÊýµÄºÍ¡£
drop procedure if exists pr_add;
-- ¼ÆËãÁ½¸öÊýÖ®ºÍ
create procedure pr_add
(
a int,
b int
)
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c as sum;
/*
return c;- ²»ÄÜÔÚ MySQL ´æ´¢¹ý³ÌÖÐʹÓá£return Ö»ÄܳöÏÖÔÚº¯ÊýÖС£
/
end;
¶þ¡¢µ÷Óà MySQL ´æ´¢¹ý³Ì
call pr_add(10, 20);
Ö´ÐÐ MySQL ´æ´¢¹ý³Ì£¬´æ´¢¹ý³Ì²ÎÊýΪ MySQL Óû§±äÁ¿¡£
set @a = 10;
set @b = 20;
call pr_add(@a, @b);
Èý¡¢MySQL ´æ´¢¹ý³ÌÌØµã
´´½¨ MySQL ´æ´¢¹ý³ÌµÄ¼òµ¥Ó﷨Ϊ£º
create procedure ´æ´¢¹ý³ÌÃû×Ö()
(
[in|out|inout] ²ÎÊý datatype
)
begin
MySQL Óï¾ä;
end;
MySQL ´æ´¢¹ý³Ì²ÎÊýÈç¹û²»ÏÔʽָ¶¨“in”¡¢“out”¡¢“inout”£¬ÔòĬÈÏΪ“in”¡£Ï°¹ßÉÏ£¬¶ÔÓÚÊÇ“in” µÄ²ÎÊý£¬ÎÒÃǶ¼²»»áÏÔʽָ¶¨¡£
1. MySQL ´æ´¢¹ý³ÌÃû×ÖºóÃæµÄ“()”ÊDZØÐëµÄ£¬¼´Ê¹Ã»ÓÐÒ»¸ö²ÎÊý£¬Ò²ÐèÒª“()”
2. MySQL ´æ´¢¹ý³Ì²ÎÊý£¬²»ÄÜÔÚ²ÎÊýÃû³ÆÇ°¼Ó“@”£¬È磺“@a int”¡£ÏÂÃæµÄ´´½¨´æ´¢¹ý³ÌÓï·¨ÔÚ MySQL ÖÐÊÇ´íÎóµÄ£¨ÔÚ SQL Server ÖÐÊÇÕýÈ·µÄ£©¡£ MySQL ´æ´¢¹ý³ÌÖеıäÁ¿£¬²»ÐèÒªÔÚ±äÁ¿Ãû×Öǰ¼Ó“@”£¬ËäÈ» MySQL ¿Í»§¶ËÓû§±äÁ¿Òª¼Ó¸ö“@”¡£
create procedure pr_add
(
@a int,
Ïà¹ØÎĵµ£º
×î½üÓöµ½Ò»¸öÐèÇóÒªÔÚlinuxÏÂÓÃjava µ÷ÓÃmysql¿Í»§¶ËÔ¶³ÌµÇ½mysql·þÎñÆ÷£¬´Ó¿Í»§¶Ë»úÆ÷µ¼Èëmysql½Å±¾£¬´Ómysql·þÎñÆ÷¶Ëµ¼³ö±íÖеÄÊý¾Ý¡£ÒÔÏÂÊÇÓõ½µÄÖ÷Òª·½·¨£º
Java ´úÂë
/**
* µ¼ÈëÊý¾Ý
* @param ½Å±¾µÄµØÖ·ºÍÃû³Æ
* @return ÊÇ·ñ³ ......
×î½ü¹¤×÷Éϳ£Óõ½MySQL£¬·¢ÏÖMySQLµÄ´íÎóÌáʾÒô±È½Ï·³ÈË£¬ÉÏÍøËÑË÷ÁËһϣ¬ÓÐÒÔÏÂÈýÖֹرոÃÌáʾÒôµÄ·½·¨£º
·½·¨1. ʹÓÃÖÕ¶ËÃüÁî
ʹÓÃMySQLµÄÃüÁîÖÕ¶Ëʱ£¬Ê¹ÓÃmysql --no-beep¼´¿É¡£
·½·¨2. ÐÞ¸ÄMySQLÅäÖÃÎļþmy.ini
ÔÚ[mysql] ϼÓÈëÒ»ÐÐ
no-beep
&nb ......
½¨Á¢Íâ¼üµÄǰÌ᣺ ±¾±íµÄÁбØÐëÓëÍâ¼üÀàÐÍÏàͬ(Íâ¼ü±ØÐëÊÇÍâ±íÖ÷¼ü)¡£
Íâ¼ü×÷Ó㺠ʹÁ½ÕűíÐγɹØÁª£¬Íâ¼üÖ»ÄÜÒýÓÃÍâ±íÖеÄÁеÄÖµ£¡
Ö¸¶¨Ö÷¼ü¹Ø¼ü×Ö£º foreign key(ÁÐÃû)
ÒýÓÃÍâ¼ü¹Ø¼ü×Ö£º references <Íâ¼ü±íÃû>(Íâ¼üÁÐÃû)
ʼþ´¥·¢ÏÞÖÆ: on deleteºÍon update , ¿ÉÉè²ÎÊýcascade(¸úËæÍâ¼ü¸Ä¶¯ ......
ÕûÀíÁËÒÔÏÂËÄÖÖÔÚMySQLÖÐÐÞ¸ÄrootÃÜÂëµÄ·½·¨,¿ÉÄܶԴó¼ÒÓÐËù°ïÖú
·½·¨1£º ÓÃSET PASSWORDÃüÁî
¡¡¡¡mysql -u root
¡¡¡¡mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
·½·¨2£ºÓÃmysqladmin
¡¡¡¡mysqladmin -u root password "newpass"
¡¡¡¡Èç¹ûrootÒѾÉèÖùýÃÜÂ룬²ÉÓÃÈçÏ·½·¨
¡¡¡¡mysql ......
¶ÔÓÚÿ¸öÀàÐÍÓµÓеÄÖµ·¶Î§ÒÔ¼°²¢ÇÒÖ¸¶¨ÈÕÆÚºÎʱ¼äÖµµÄÓÐЧ¸ñʽµÄÃèÊö¼û7.3.6 ÈÕÆÚºÍʱ¼äÀàÐÍ¡£
ÕâÀïÊÇÒ»¸öʹÓÃÈÕÆÚº¯ÊýµÄÀý×Ó¡£ÏÂÃæµÄ²éѯѡÔñÁËËùÓмǼ£¬Æädate_colµÄÖµÊÇÔÚ×îºó30ÌìÒÔÄÚ£º
mysql> SELECT something from table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
·µ»ØÈÕÆ ......