¹¦ÄÜ£ºpl plusÖ´Ðб¾µØµÄsqlÎļþÖеÄsqlÓï¾ä
˵Ã÷£º±ÈÈ磺e:\zhaozhenlongÏÂÓÐcreate_table.sqlÎļþ£¬Ôò°´ÈçÏ·½·¨Ö´ÐУº
²½Ö裺
1¡¢ÔÚpl plusµÄ windowÏ£¬
2¡¢Ö´ÐУº
@e:\zhaozhenlong\drop_table.sql
@e:\zhaozhenlong\create_table.sql
......
select *
from (select row_number() over(partition by t.type order by date desc) rn,
t.*
from ±íÃû t)
where rn <= 2;
typeÒª·ÖµÄÀà
date ÅÅÐò
rn ÿÀàÒªÏÔʾ¼¸ÌõÊý¾Ý ......
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä±ß½çÊý¡£
select datediff(day,'2004-09-01','2004-09-18') --·µ»Ø£º17
select datediff(day,'2004-09-18','2004-09-01') --·µ»Ø£º-17
4. datepart ·µ»Ø´ú±íÖ¸¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄÕûÊý¡£
SELECT DATEPART(month, '2004-10-15') --·µ»Ø 10
5. datename ·µ»Ø´ú±íÖ¸¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄ×Ö·û´®
SELECT datename(weekday, ' ......
1. ËÀËøÔÀí
¸ù¾Ý²Ù×÷ϵͳÖеĶ¨Ò壺ËÀËøÊÇÖ¸ÔÚÒ»×é½ø³ÌÖеĸ÷¸ö½ø³Ì¾ùÕ¼Óв»»áÊͷŵÄ×ÊÔ´£¬µ«Òò»¥ÏàÉêÇë±»ÆäËû½ø³ÌËùÕ¾Óò»»áÊͷŵÄ×ÊÔ´¶ø´¦ÓÚµÄÒ»ÖÖÓÀ¾ÃµÈ´ý״̬¡£
ËÀËøµÄËĸö±ØÒªÌõ¼þ£º
»¥³âÌõ¼þ(Mutual exclusion)£º×ÊÔ´²»Äܱ»¹²Ïí£¬Ö»ÄÜÓÉÒ»¸ö½ø³ÌʹÓá£
ÇëÇóÓë±£³ÖÌõ¼þ(Hold and wait)£ºÒѾµÃµ½×ÊÔ´µÄ½ø³Ì¿ÉÒÔÔÙ´ÎÉêÇëеÄ×ÊÔ´¡£
·Ç°þ¶áÌõ¼þ(No pre-emption)£ºÒѾ·ÖÅäµÄ×ÊÔ´²»ÄÜ´ÓÏàÓ¦µÄ½ø³ÌÖб»Ç¿ÖƵذþ¶á¡£
Ñ»·µÈ´ýÌõ¼þ(Circular wait)£ºÏµÍ³ÖÐÈô¸É½ø³Ì×é³É»·Â·£¬¸Ã»·Â·ÖÐÿ¸ö½ø³Ì¶¼ÔڵȴýÏàÁÚ½ø³ÌÕýÕ¼ÓõÄ×ÊÔ´¡£
¶ÔÓ¦µ½SQL ServerÖУ¬µ±ÔÚÁ½¸ö»ò¶à¸öÈÎÎñÖУ¬Èç¹ûÿ¸öÈÎÎñËø¶¨ÁËÆäËûÈÎÎñÊÔͼËø¶¨µÄ×ÊÔ´£¬´Ëʱ»áÔì³ÉÕâЩÈÎÎñÓÀ¾Ã×èÈû£¬´Ó¶ø³öÏÖËÀËø£»ÕâЩ×ÊÔ´¿ÉÄÜÊÇ£ºµ¥ÐÐ(RID£¬¶ÑÖеĵ¥ÐÐ)¡¢Ë÷ÒýÖеļü(KEY£¬ÐÐËø)¡¢Ò³(PAG£¬8KB)¡¢Çø½á¹¹(EXT£¬Á¬ÐøµÄ8Ò³)¡¢¶Ñ»òBÊ÷(HOBT) ¡¢±í(TAB£¬°üÀ¨Êý¾ÝºÍË÷Òý)¡¢Îļþ(File£¬Êý¾Ý¿âÎļþ)¡¢Ó¦ÓóÌÐòרÓÃ×ÊÔ´(APP)¡¢ÔªÊý¾Ý(METADATA)¡¢·ÖÅäµ¥Ôª(Allocation_Unit)¡¢Õû¸öÊý¾Ý¿â(DB)¡£Ò»¸öËÀËøʾÀýÈçÏÂͼËùʾ£º
˵Ã÷£ºT1¡¢T2±íʾÁ½¸öÈÎÎñ£»R1ºÍR2±í ......
SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò
2009-06-02 15:57
SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò£º1¡¢from×Ó¾ä×é×°À´×Ô²»Í¬Êý¾ÝÔ´µÄÊý¾Ý£»2¡¢where×Ó¾ä»ùÓÚÖ¸¶¨µÄÌõ¼þ¶Ô¼Ç¼ÐнøÐÐɸѡ£»3¡¢group by×Ӿ佫Êý¾Ý»®·ÖΪ¶à¸ö·Ö×飻4¡¢Ê¹Óþۼ¯º¯Êý½øÐмÆË㣻5¡¢Ê¹ÓÃhaving×Ó¾äɸѡ·Ö×飻
ÒÔϽáÂÛÊÇͨ¹ýÔÚSql Server2000ÉϵÄʵÑéµÃ³öµÄ½áÂÛ£¬Ò²¿Éͨ¹ý²é¿´¹À¼ÆµÄÖ´Ðмƻ®£¨Ctrl+L£©À´²é¿´Ö´ÐÐ˳Ðò¡£ 1.ÏÈÖ´ÐеȺţ¨=£©×ó±ßÊDZäÁ¿µÄ±í´ïʽ£¨AÀࣩ£¬ÔÙÖ´ÐеȺţ¨=£©×ó±ßÊÇÁÐÃûµÄ±í´ïʽ£¨BÀࣩ¡£Àý£ºupdate tablename set columnName=@variable,@variable=@variable+1 ÏÈÖ´ÐÐ@variable=@variable+1£¬ÔÙÖ´ÐÐcolumnName=@variable 2.Èç¹ûÓжà¸öAÀࣨ»òBÀࣩ±í´ïʽ£¬°´´Ó×óµ½ÓÒ˳ÐòÖ´ÐÐAÀࣨ»òBÀࣩ±í´ïʽ¡£Àý£ºupdate tablename set columnName=@variable,@variable=@variable+1,@variable=2*@variable ÏÈÖ´ÐÐ@variable=@variable+1£¬ÔÙÖ´ÐÐ@variable=2*@variable£¬×îºóÖ´ÐÐcolumnName=@variable 3.ÁÐÃûËù´ú±íµÄÖµÓÀÔ¶ÊÇÔÖµ¡£Àý£ºupdate tablename set columnName=colunName+1,columnNameBa
group by
ÔÚselect Óï¾äÖпÉÒÔʹÓÃgroup by ×Ӿ佫Ðл®·Ö³É½ÏСµÄ×飬Ȼºó£¬Ê¹ÓþÛ×éº ......
жÔØSQL Server 2005µÄ·½·¨Ö®Ò»£¬·ÅÔÚÕâ¶ùÖ»ÊÇΪÁ˼Çס£º
1£º°ÑSQL Server2005µÄ°²×°ÅÌ£¨°²×°Îļþ£©·ÅÈëµ½¹âÇý¡£
2£º´ò¿ªÈçÏ·¾¶£º¿ªÊ¼/ÔËÐУ¬ÊäÈ룺cmd
3£ºÊäÈëÏÂÁÐÃüÁ
Start /wait \setup.exe /qb REMOVE=ALL INSTANCENAME=£º¹ÜÇøÅÌ·û£¬ÀýÈ磺G£º¡¢H£ºµÈ£»£º°²×°µÄSQLServerʵÀýÃû³Æ£¬Ä¬ÈÏֵΪ£º¹¦ÄÜ£ºÐ¶ÔØSQL Server 2005 ²¿¼þ¡£
4£ºÊäÈëÏÂÁÐÃüÁStart /wait msiexec /qb /X \Setup\sqlncli.msiµÄ½âÊÍÈçÉÏ¡£¹¦ÄÜ£ºÐ¶ÔØMicrosoft SQL Native Client¡£
5£ºÊäÈëÏÂÁÐÃüÁStart /wait \redist\2.0\dotnetfx.exe /q:a /c:"install /qu"ÈçÉÏ¡£¹¦ÄÜ£ºÐ¶ÔØMicrosoft .NET Framework¡£
´ÓSQL Server 2005 ExpressÉý¼¶¶øÀ´µÄʵÀý£¬ÓÃÆðÀ´×ܸоõ¹Ö¹ÖµÄ£¬ºÜ¶à¹¦ÄܺÃÏñ¶¼Í£ÁôÔÚExpress°æ±¾ÉÏ¡£Òò´ËÏë°ÑSQL Server 2005ÍêȫжÔغóÔÙÖØ×°¡£
²Î¿¼ÎÄÕ£ºÈçºÎÊÖ¶¯Ð¶ÔØ SQL Server 2005 ʵÀý£¬ ´íÎó´úÂë-2147024893
²½Öè 1£ºÈ·±£ Microsoft SQL Server °²×°Ö§³ÖÎļþ×é¼þÒÑ°²×°
ÔÚ“Ìí¼Ó»òɾ³ý³ÌÐò”ÖУ¬È·±£“Microsoft SQL Server °²×°Ö§³ÖÎļþ”³öÏÖÔÚÒÑ°²×°µÄ³ÌÐòÁбíÖС£Èç¹ ......