sqlÓï¾ä
SqlÓï¾ä
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb) SQL:select * into bfrom awhere 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb) SQL:insert into b(a, b, c)select d, e, ffrom b;
3. ˵Ã÷£ºÏÔʾÎÄÕ¡¢Ìá½»È˺Í×îºó»Ø¸´Ê±¼ä SQL:select a.title, a.username, b.adddatefrom table a,(select max(adddate) adddatefrom table where table.title=a.title) b ¡¡
4. ˵Ã÷£ºÍâÁ¬½Ó²éѯ(±íÃû1£ºa£¬±íÃû2£ºb) SQL:select a.a, a.b, a.c, b.c, b.d, b.ffrom aLEFT OUTJOIN bON a.a= b.c; ¡¡
5. ˵Ã÷£ºÈճ̰²ÅÅÌáÇ°Îå·ÖÖÓÌáÐÑ SQL:select * from Èճ̰²ÅÅwhere datediff(’’minute’’, f¿ª ʼʱ¼ä,getdate())>5 ¡¡¡¡¡¡¡¡
6. ˵Ã÷£ºÁ½ÕŹØÁª±í£¬É¾³ýÖ÷±íÖÐÒѾÔÚ¸±±íÖÐûÓеÄÐÅÏ¢ SQL:delete from infowhere not exists(select * from infobzwhere info.infid=infobz.infid );
¡¾SQL SERVER Êý¾Ý¿âʵÓÃSQLÓï¾ä¡¿
1.°´ÐÕÊϱʻÅÅÐò:Select * from TableNameOrder By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.·ÖÒ³SQLÓï¾äselect * from(select (row_number()OVER (ORDER BY tab.IDDesc))as rownum,tab.* from ±íÃûAs tab)As twhere rownumbetween ÆðʼλÖÃAnd ½áÊøλÖÃ
3.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§±íselect * from sysobjectswhere xtype='U' and category=0
4.»ñȡijһ¸ö±íµÄËùÓÐ×Ö¶Îselect namefrom syscolumnswhere id=object_id('±íÃû')
5.²é¿´Óëijһ¸ö±íÏà¹ØµÄÊÓͼ¡¢´æ´¢¹ý³Ì¡¢º¯Êýselect a.* from sysobjects a, syscomments bwhere a.id= b.idand b.text like '%±íÃû%'
6.²é¿´µ±Ç°Êý¾Ý¿âÖÐËùÓд洢¹ý³Ìselect nameas ´æ´¢¹ý³ÌÃû³Æfrom sysobjectswhere xtype='P'
7.²éѯÓû§´´½¨µÄËùÓÐÊý¾Ý¿âselect * from master..sysdatabases Dwhere sidnot in(select sidfrom master..sysloginswhere name='sa') »òÕßselect dbid, nameAS DB_NAME from master..sysdatabaseswhere sid<> 0x01
8.²éѯijһ¸ö±íµÄ×ֶκÍÊý¾ÝÀàÐÍselect column_name,data_typefrom information_schema.columnswhere table_name= '±íÃû'
9.ʹÓ
Ïà¹ØÎĵµ£º
ÔÚµ÷Ó÷þÎñÆ÷ºóÒª¼ÌÐøÔÚ·þÎñÆ÷Öб£ÁôÊý¾Ýͨ³£ºÜÄÑ£¬Äã¿ÉÒÔÓÃSQL ServerÀ´½øÐлỰ״̬¹ÜÀí(Óû§»á»°Êý¾Ý)¡£ÒÔϽÌÄãÔõÑùÓÃËüÀ´½øÐйÜÀí¡£
¡¡¡¡ÔÚÒ³Ã濪·¢ÖУ¬ÔÚ·þÎñÆ÷µ÷ÓÃÀï±£ÁôÊý¾ÝÊÇÒ»¸ö³£¼ûµÄ²»ºÃ½â¾öµÄÄÑÌâ¡£ÄãÒ²ÐíÐèҪΪÄãµÄÓ¦ÓóÌÐò»òÌØÊâÓû§µÄ»á»°±£´æÐÅÏ¢¡£´æ´¢ÕâÑùµÄÊý¾Ý±»³ÆΪ״̬¹ÜÀí£¬ASP.NETΪÄãÌṩ ......
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb)
SQL: select * into b from a where 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb)
SQL: insert into b(a, b, c) select d, e, f from b;
3. ......
ÓÐЩʱºòÎÒÃÇÐèÒª²éѯÊý¾Ý¿âÖеÄʱ¼ä×ֶΣ¬ÀýÈç2009-11-11 11:11:11:111 ÕâÑùµÄʱ¼ä¸ñʽ¡£
¶øÎÒÃÇÓÐЩʱºò²»ÓðÑÕû¸öµÄ×ֶβéѯ³öÀ´£¬ÐèÒª°ÑÇ°ÃæµÄÈÕÆÚ½ØÈ¡³öÀ´£¬»òÕ߰ѺóÃæµÄʱ¼ä½ØÈ¡³öÀ´¡£
Õâ¸öʱºò¾ÍÒªÓõ½SQLÀïÃæµÄʱ¼äº¯ÊýÁË£º
select convert(char(10),×Ö¶ÎÃû,108) from ±íÃû
ÉÏÊöÓï¾äÊǽ«ºóÃæµÄʱ¼ä²éѯ³öÀ´£¬¸ñ ......
sp_databases --Áгö·þÎñÆ÷ÉϵÄËùÓÐÊý¾Ý¿â
sp_server_info --Áгö·þÎñÆ÷ÐÅÏ¢£¬Èç×Ö·û¼¯£¬°æ±¾ºÍÅÅÁÐ˳Ðò
sp_stored_procedures--Áгöµ±Ç°»·¾³ÖеÄËùÓд洢¹ý³Ì
sp_tables --Áгöµ±Ç°»·¾³ÖÐËùÓпÉÒÔ²éѯµÄ¶ÔÏó
sp_start_job --Á¢¼´Æô¶¯×Ô¶¯»¯ÈÎÎñ
sp_stop_job --Í£Ö¹ÕýÔÚÖ´ÐеÄ×Ô¶¯»¯ÈÎÎñ
sp_password --Ì ......
ÎÊÌâÃèÊö:
¡¡¡¡Îª¹ÜÀí¸ÚλҵÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í:
¡¡¡¡S (S#,SN,SD,SA) S#,SN,SD,SA ·Ö±ð´ú±íѧºÅ¡¢Ñ§Ô±ÐÕÃû¡¢ËùÊôµ¥Î»¡¢Ñ§Ô±ÄêÁä
¡¡¡¡C (C#,CN ) C#,CN ·Ö±ð´ú±í¿Î³Ì±àºÅ¡¢¿Î³ÌÃû³Æ
¡¡¡¡SC ( S#,C#,G ) S#,C#,G ·Ö±ð´ú±íѧºÅ¡¢ËùÑ¡Ð޵Ŀγ̱àºÅ¡¢Ñ§Ï°³É¼¨
¡¡¡¡1. ʹÓñê×¼SQLǶÌ×Óï¾ä²éѯѡÐ޿γÌÃû³ÆΪ&rsquo ......