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

mysql sql °ÙÍò¼¶Êý¾Ý¿âÓÅ»¯·½°¸

1.¶Ô²éѯ½øÐÐÓÅ»¯£¬Ó¦¾¡Á¿±ÜÃâÈ«±íɨÃ裬Ê×ÏÈÓ¦¿¼ÂÇÔÚ where ¼° order by Éæ¼°µÄÁÐÉϽ¨Á¢Ë÷Òý¡£
2.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐÐ null ÖµÅжϣ¬·ñÔò½«µ¼ÖÂÒýÇæ
·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num is null
¿ÉÒÔÔÚnumÉÏÉèÖÃ
ĬÈÏÖµ0£¬È·±£±íÖÐnumÁÐûÓÐnullÖµ£¬È»ºóÕâ
Ñù²éѯ£º
select id from t where num=0
3.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓÃ!=»ò<>²Ù×÷·û£¬·ñÔò½«ÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£
4.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓà or À´Á¬½ÓÌõ¼þ£¬·ñÔò½«µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num=10 or num=20
¿ÉÒÔÕâÑù²éѯ£º
select id from t where num=10
union all
select id from t where num=20
5.in ºÍ not in Ò²ÒªÉ÷Ó㬷ñÔò»áµ¼ÖÂÈ«±íɨÃ裬È磺
select id from t where num in(1,2,3)
¶ÔÓÚÁ¬ÐøµÄÊýÖµ£¬ÄÜÓà between ¾Í²»ÒªÓà in ÁË£º
select id from t where num between 1 and 3
6.ÏÂÃæµÄ²éѯҲ½«µ¼ÖÂÈ«±íɨÃ裺
select id from t where name like ‘%abc%’
ÈôÒªÌá¸ßЧÂÊ£¬¿ÉÒÔ¿¼ÂÇÈ«ÎļìË÷¡£
7.Èç¹ûÔÚ where ×Ó¾äÖÐʹÓòÎÊý£¬Ò²»áµ¼ÖÂÈ«±íɨÃè¡£ÒòΪSQL
Ö»ÓÐÔÚÔËÐÐʱ²Å»á½âÎö
¾Ö²¿±äÁ¿£¬µ«ÓÅ»¯³ÌÐò
²»Äܽ«·ÃÎÊ
¼Æ»®µÄÑ¡ÔñÍÆ³Ùµ½ÔËÐÐʱ£»Ëü±ØÐëÔÚ±àÒë
ʱ½øÐÐÑ¡Ôñ¡£È»
¶ø£¬Èç¹ûÔÚ±àÒëʱ½¨Á¢·ÃÎʼƻ®£¬±äÁ¿µÄÖµ»¹ÊÇδ֪µÄ£¬Òò¶øÎÞ·¨×÷ΪË÷ÒýÑ¡ÔñµÄÊäÈëÏî¡£ÈçÏÂÃæÓï¾ä½«½øÐÐÈ«±íɨÃ裺
select id from t where [email=num=@num]num=@num[/email]
<[email=num=@num]mailto:num=@num[/email]>
¿ÉÒÔ¸ÄÎªÇ¿ÖÆ²éѯʹÓÃË÷Òý£º
select id from t with(index(Ë÷ÒýÃû)) where [email=num=@num]num=@num[/email]
<[email=num=@num]mailto:num=@num[/email]>
8.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐбí´ïʽ²Ù×÷£¬Õ⽫µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£È磺
select id from t where num/2=100
Ó¦¸ÄΪ:
select id from t where num=100*2
9.Ó¦¾¡Á¿±ÜÃâÔÚwhere×Ó¾äÖжÔ×ֶνøÐк¯Êý
²Ù×÷£¬Õ⽫µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£
È磺
select id from t where substring(name,1,3)=’abc’–nameÒÔabc¿ªÍ·µÄid
select id from t where
datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’Éú³ÉµÄid
Ó¦¸ÄΪ:
select id from t where name like &lsquo


Ïà¹ØÎĵµ£º

mysql´æ´¢¹ý³Ì Àý×Ó Óαêcursor Ñ­»·Ç¶Ì× ÊÂÎï

×òÌìÒªmysqlÖÐд´æ´¢¹ý³Ì·¢ÏÖÍøÉÏÓйØÓÚcursorÑ­»·ÇÒÌ×µÄ×ÊÁϺÜÉÙ,ÏÖ·¢³ö±¾ÈËдµÄÀý×Ó,Ï£ÍûÄܶԴó¼ÒÓаïÖú.
¿´ÕÐ:
DELIMITER $$
USE `ad_db`$$
DROP PROCEDURE IF EXISTS `test`$$
CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
     /** CPAЧ¹ûÊý */
DECLARE _tmpId1 INT; 
DECLARE ......

mysqlÖÐunionºÍunion allµÄÇø±ðºÍ×¢Òâµã


×òÌìÓиö¹¦ÄÜҪʵÏÖÅÅÐÐЧ¹û£¬¶øÇÒÊÇËæ»ú°´ÕÕijЩ×ֶεÄ×ÜÖµ£¬µ«ÊǵÚÒ»¸öÓÖÒª²»Ò»Ñù£¬ÒòΪµÚÒ»¸öÈ˸øÁËÇ®£¬ËùÒÔÒªÅŵÚÒ»¡£
´ò¸ö±È·½°É£¬±í(userinfo)ÖÐÓÐÕ⼸¸ö×ֶΣºusername,isvalid,givedmoney,sumip,dayip,monthip,visitcount,regdate
ÏÖÔÚҪʵÏÖÏÔʾÅÅÐÐǰ10λÓû§£¬
     ³öÏÖÔÚÅÅÐеÚһλµ ......

mysqlµ÷Óúó

ÓÃmysql_query£¬mysql_fetch_array()ºóÓ¦¸ÃÒª
free_result().function count_admin($where = '')
{
if($where) $where = " WHERE $where";
$result = $this->db->get_one("SELECT count(*) as num from $this->table_admin_role $where");
return $result['num'];
} ......

Ò»¸ö¸´ÔÓµÄmysql²éѯÓï¾ä(case,when,then,left join )

select `a`.`id` AS `id`,`a`.`UserName` AS `UserName`,
(case when (`a`.`sRegDate` = _utf8'1990-01-01 00:00:00.0') then _utf8'' else cast(date_format(`a`.`sRegDate`,_utf8'%Y-%m-%d %H:%i:%S') as char charset utf8) end) AS `sRegDate`,(case when (`a`.`feeendtime` = _utf8'1990-01-01 00:00:00.0') then _ut ......

mysqlµ¼Èëµ¼³öÊý¾Ý¿â

¼¸¸ö³£ÓÃÓÃÀý£º
1.µ¼³öÕû¸öÊý¾Ý¿â mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû > µ¼³öµÄÎļþÃû     mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql2.µ¼³öÒ»¸ö±í mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû ±íÃû> µ¼³öµÄÎļþÃû mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql3.µ¼³öÒ»¸öÊý¾Ý¿â½ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ