SQLÖеÄIF ELSE£¨CASEÓï¾äµÄʹÓã©
----start
´ó¼Ò¶ÔIF ELSEÓï¾ä¿ÉÄܶ¼ºÜÊìϤ£¬ËüÊÇÓÃÀ´¶Ô¹ý³Ì½øÐпØÖƵġ£ÔÚSQLµÄÊÀ½çÖÐCASEÓï¾äÓï¾äÓÐÀàËƵÄЧ¹û¡£ÏÂÃæ¼òµ¥µÄ½éÉÜCASEÓï¾äµÄÓ÷¨¡£¿¼ÂÇÏÂÃæµÄÇé¿ö£¬¼ÙÉèÓиöuser±í£¬¶¨ÒåÈçÏ£º
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,---ÐÕÃû
SEX INTEGER,---ÐÔ±ð£¨1¡¢ÄÐ 2¡¢Å®£©
BIRTHDAY DATE---ÉúÈÕ
);
CASEʹÓó¡ºÏ1£º°Ñuser±íµ¼³öÉú³ÉÒ»¸öÎļþ£¬ÒªÇóÐÔ±ðΪÄлòÅ®£¬¶ø²»ÊÇ1ºÍ2£¬Ôõô°ì£¿ÎÒÃÇ¿ÉÒÔÓÃÈçϵÄÓï¾ä´¦Àí£º
SELECT
NAME,
CASE SEX
WHEN 1 THEN 'ÄÐ'
ELSE 'Å®'
END AS SEX,
BIRTHDAY
from USER;
CASEʹÓó¡ºÏ2£º¼ÙÉèuserĿǰûÓÐÖµ£¬È»ºóÄãÍùuserµ¼ÈëÁËÒ»ÅúÊý¾Ý£¬µ«ÊǺܲ»ÐÒ£¬´í°ÑÄÐÉèÖóÉΪ2£¬¶ø°ÑÅ®ÉèÖóÉΪ1£¬ÏÖÔÚÒªÇóÄã±ä»»¹ýÀ´£¬Ôõô°ì£¿
·½·¨1£ºÊ¹ÓÃÈýÌõÓï¾ä£¬ÏÈ°Ñ2¸üгÉ3£¬½Ó×Å°Ñ1¸üгÉ2£¬×îºó°Ñ3¸üгÉ1£¬ºÜÂé·³£¬²»ÊÇÂð£¿
UPDATE USER SET SEX=3 WHERE SEX=2;
UPDATE USER SET SEX=1 WHERE SEX=3;
UPDATE USER SET SEX=2 WHERE SEX=1;
·½·¨2£ºÊ¹ÓÃCASEÓï¾ä
UPDATE USER SET SEX=
(
CASE SEX
WHEN 1 THEN 2
WHEN 2 THEN 1
ELSE SEX
END
);
ϸÐĵÄÅóÓÑ¿ÉÄÜÒѾ·¢ÏÖÁË£¬ÉÏÃæµÄ·½·¨1µÄÈýÌõÓï¾äµÄÖ´ÐÐ˳ÐòÓÐÎÊÌ⣬û´í£¬ÊÇÎÒ¹ÊÒâÄÇЩдµÄ£¬½ö½öÊÇ°Ñ1±ä³É2£¬°Ñ2±ä³É1¾ÍÄÇôÂé·³£¬¶øÇÒºÜÈÝÒ׳ö´í£¬ÏëÏóһϣ¬Èç¹ûÓкܶàÕâÑùµÄÖµÐèÒª±ä»»£¬ÄÇÊÇÒ»ÖÖʲôÑùµÄÇé¿ö¡£»¹ºÃ£¬ÎÒÃÇÓÐCASEÓï¾ä£¬ÓкöàÕâÑùµÄÖµÐèÒª±ä»»£¬CASEÓï¾äÒ²²»»á´æÔÚÎÊÌâ¡£¿ÉÄÜÓÐЩÅóÓÑ»¹ÊÇÓÐÒÉÂÇ£¬ÕâÑù×ö»á²»»áËÀÑ»·°¡£¿¹þ¹þ£¬Ïë·¨ºÜºÃ£¬Èç¹ûÄã·¢ÏÖÕâÑù×ö»áËÀÑ»·£¬Ò»¶¨Òª¸æËßIBM£¬ÎÒ·´Õýû·¢ÏÖ¡£
---¸ü¶à²Î¼û£ºDB2 SQL ¾«Òª
----ÉùÃ÷£º×ªÔØÇë×¢Ã÷³ö´¦¡£
----last update at 2009.9.27
----write by wave at 2009.9.23
----end
Ïà¹ØÎĵµ£º
1.ÔÚ²éѯ·ÖÎöÆ÷ϲéѯExcelÎĵµ
Select * from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source = "c:\²âÊÔ.xls";User ID = Admin;Password=;Extended properties=Excel8.0)....Sheet1$
2.´ÓÊý¾Ý¿âÖе¼³öÊý¾Ý²¢´æµ½ÎļþÖÐ
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"."- ......
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËƺõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......
½ñÌì¸ø´ó¼Ò·ÖÏíÒ»ÏÂÈçºÎ±¸·ÝºÍ»Ø¸´sql server Êý¾Ý£¬
Ê×ÏÈÈ·±£Äã¾ßÓÐDBµÄ×î¸ßȨÏÞ
ÔÚJAVA´úÂëÖÐ Äã¿ÉÒÔÕâÑùдpublic class Opp extends TestCase{
public void test1()
{
try
{
Connection con = DBUtils.getConn();
String sql="backup database tdzl to disk='d:\\tdzl.bak'";
Statement st = con.c ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
Èç¹ûÎÒÃÇ°´ÕÕʾÀýÏëµÃµ½Ã¿¸ö²¿ÃÅнˮֵ×î¸ßµÄ¹ÍÔ±µÄ¼Í¼£¬¿ÉÒÔÓÐËÄÖÖ·½·¨ÊµÏÖ£ ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Ò»)--αÁÐROWNUMʹÓü¼ÇÉ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(¶þ)--±êÁ¿×Ó²éѯ
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Èý)--PackageµÄÓŵã
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(ËÄ)--ÅúÁ¿´¦Àí
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Îå)--µ÷Óô洢¹ý³Ì·µ»Ø½á¹û¼¯
[Oracle]¸ßЧµÄPL/SQL³ÌÐòÉè¼Æ(Áù)- ......