SQL ÖÐ where 1=1 ºÍ 1=0µÄ ×÷ÓÃ
where 1=1ÓÐʲôÓã¿ÔÚSQLÓïÑÔÖУ¬Ð´Õâôһ¾ä»°¾Í¸úûдһÑù¡£
select * from table1 where 1=1Óëselect * from table1ÍêȫûÓÐÇø±ð£¬ÉõÖÁ»¹ÓÐÆäËûÐí¶àд·¨£¬1<>2£¬'a'='a','a'<>'b'£¬ÆäÄ¿µÄ¾ÍÖ»ÓÐÒ»¸ö£¬whereµÄÌõ¼þΪÓÀÕæ£¬µÃµ½µÄ½á¹û¾ÍÊÇδ¼ÓÔ¼ÊøÌõ¼þµÄ¡£
ÔÚSQL×¢Èëʱ»áÓõ½Õâ¸ö£¬ÀýÈçselect * from table1 where name='lala'¸øÇ¿ÐмÓÉÏselect * from table1 where name='lala' or 1=1Õâ¾ÍÓÖ±ä³ÉÁËÎÞÔ¼ÊøµÄ²éѯÁË¡£
×î½ü·¢ÏÖµÄÃîÓÃÔÚÓÚ£¬ÔÚ²»¶¨ÊýÁ¿²éѯÌõ¼þÇé¿öÏ£¬1=1¿ÉÒԺܷ½±ãµÄ¹æ·¶Óï¾ä¡£ÀýÈçÒ»¸ö²éѯ¿ÉÄÜÓÐname,age,height,weightÔ¼Êø£¬Ò²¿ÉÄÜûÓУ¬ÄǸÃÈçºÎ´¦ÀíÄØ£¿
String sql=select * from table1 where 1=1
ΪʲôҪд¶àÓàµÄ1=1£¿ÂíÉϾÍÖªµÀÁË¡£
if(!name.equals("")){
sql=sql+"name='"+name+"'";
}
if(!age.equals("")){
sql=sql+"age'"+age+"'";
}
if(!height.equals("")){
sql=sql+"height='"+height+"'";
}
if(!weight.equals("")){
sql=sql+"weight='"+weight+"'";
}
Èç¹û²»Ð´1=1ÄØ£¬ÄÇôÔÚÿһ¸ö²»Îª¿ÕµÄ²éѯÌõ¼þÃæÇ°£¬¶¼±ØÐëÅжÏÓÐûÓÐwhere×־䣬·ñÔòÒªÔÚµÚÒ»¸ö³öÏֵĵط½¼Ówhere
ÓÐÒ»´ÎÔÚÂÛ̳Àï¿´µ½£º"SELECT * from strName WHERE 1 = 0";
²»Àí½âΪʲôÓÐ1=0£¿
¸ÃselectÓï¾äÖ÷ÒªÓÃÓÚ¶ÁÈ¡±íµÄ½á¹¹¶ø²»¿¼ÂDZíÖеÄÊý¾Ý£¬ÕâÑù½ÚÊ¡ÁËÄڴ棬ÒòΪ¿ÉÒÔ²»Óñ£´æ½á¹û¼¯¡£
ÁíÍ⣬Õâ¸öÓÃÔÚʲôµØ·½ÄØ£¿Ö÷ÒªÓÃÓÚ´´½¨Ò»¸öÐÂ±í£¬¶øÐ±íµÄ½á¹¹Óë²éѯµÄ±íµÄ½á¹¹ÊÇÒ»ÑùµÄ¡£ÈçÏÂSQLÓï¾ä£º
create table newtable as select * from oldtable where 1=0;
Ïà¹ØÎĵµ£º
select case when b.name is null and c.name is null then 'ºÏ¼Æ' when b.name is null and c.name is not null then 'С¼Æ' else b.name end as mtrname,
sum(a.number),c.name as cname from x_sell a join x_material b on a.mtr=b.fid join p_organi c on c.fid=a.customer
where a.stime>'2009-10-01'
GROUP B ......
SQLÖеÄʱ¼äº¯Êý·Ç³£ÓÐÓã¬ÌرðÊÇÔÚÎÒÃǽøÐгõʼ¸³Öµ¡¢¸´ÔÓ²éѯµÄʱºò£¬¾ÍÏÔµÃÌØ±ð·½±ã¡£
1¡¢»ñµÃϵͳµ±Ç°Ê±¼ä
select getdate()
2¡¢DateName ( datepart , date )·µ»Ø±íʾָ¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄ×Ö·û´®¡£
--½ñÌìÊÇ2009-2-24--ÐÇÆÚ¶þ
SELECT DATENAME(year, getdate()) AS 'Year&nbs ......
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®ºÍ±í´ïʽִÐв»Í¬µÄÔËËã¡£´ËÀຯÊý×÷ÓÃÓÚCHAR¡¢VARCHAR¡¢ BINARY¡¢ ºÍVARBINARY Êý¾ÝÀàÐÍÒÔ¼°¿ÉÒÔÒþʽת»»ÎªCHAR »òVARCHARµÄÊý¾ÝÀàÐÍ¡£¿ÉÒÔÔÚSELECT Óï¾äµÄSELECT ºÍWHERE ×Ó¾äÒÔ¼°±í´ïʽÖÐʹÓÃ×Ö·û´®º¯Êý¡£
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ ......
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éÑ ......
1:Ó¦ÓóÌÐò²»ÔÙÐèҪʹÓà Class.forName() ÏÔʽµØ¼ÓÔØ JDBC Çý¶¯³ÌÐò¡£µ±Ç°Ê¹Óà Class.forName() ¼ÓÔØ JDBC Çý¶¯³ÌÐòµÄÏÖÓгÌÐò½«ÔÚ²»×÷Ð޸ĵÄÇé¿öϼÌÐø¹¤×÷¡£
2:ÐèҪעÒâÒÔÏÂÃüÁ
executeUpdate£ºÊÇ×î»ù´¡µÄÊý¾Ý¿âµÄ¸üС¢²åÈëºÍɾ³ý²Ù×÷¡£Ð§ÂʵÍÏ¡£
executeQuery£ºÊÇ×î»ù´¡µÄÖ´ÐвéѯÓï¾ä£¬Í¬Ñù ......