SQL ServerÐÔÄÜÓÅ»¯µÄһЩ¼¼ÇÉ
Êý¾Ý¿âÐÔÄÜÓÅ»¯Éæ¼°µ½ºÜ¶à·½Ã棬ÔÚÊý¾Ý¿â¿ª·¢Ê±¿ÉÒÔͨ¹ýһЩ»ù±¾µÄÓÅ»¯¼¼ÇÉÌá¸ßÊý¾Ý¿âµÄÐÔÄÜ£º
1£®ÔÔòÉÏΪ´´½¨µÄÿ¸ö±í¶¼½¨Á¢Ò»¸öÖ÷¼ü,Ö÷¼üΨһ±êʶijһÐмǼ£¬ÓÃÓÚÇ¿ÖÆ±íµÄʵÌåÍêÕûÐÔ¡£SQL Server 2005 Database Engine ½«Í¨¹ýΪÖ÷¼üÁд´½¨Î¨Ò»Ë÷ÒýÀ´Ç¿ÖÆÊý¾ÝµÄΨһÐÔ¡£²éѯÖÐʹÓÃÖ÷¼üʱ£¬´ËË÷Òý»¹¿ÉÓÃÀ´¶ÔÊý¾Ý½øÐпìËÙ·ÃÎÊ¡££¨×¢Ò⣺Èç¹ûÄ㽨Á¢ÁËÖ÷¼ü£¬Ä¬ÈÏÇé¿öÏÂËü¾ÍÊǾۼ¯Ë÷Òý£©
2£®ÎªÃ¿Ò»¸öÍâ¼üÁн¨Á¢Ò»¸öË÷Òý£¬Èç¹ûÈ·ÈÏËüÊÇΨһµÄ£¬¾Í½¨Á¢Î¨Ò»Ë÷Òý¡£µ±ÔÚ²éѯÖÐ×éºÏÏà¹Ø±íÖеÄÊý¾Ýʱ£¬¾³£ÔÚÁª½ÓÌõ¼þÖÐʹÓÃÍâ¼üÁУ¬Ë÷Òýʹ SQL Server 2005 Êý¾Ý¿âÒýÇæ ¿ÉÒÔÔÚÍâ¼ü±íÖпìËÙ²éÕÒÏà¹ØÊý¾Ý¡£
3£®ÔÝʱ²»ÒªÎªÆäËûÁн¨Á¢Ë÷Òý
4£®µ±ÔÚTSQLÖÐÒýÓöÔÏóʱ£¬½¨ÒéʹÓöÔÏóµÄ¼Ü¹¹Ãû³ÆÏÞ¶¨¡££¨Ê¹ÓÃdbo.sysdatabases´úÌæsysdatabases£©Î´Ö¸¶¨¼Ü¹¹¿ÉÄܻᵼÖ»ìÏýºÍÒâÒå²»Ã÷È·£¬»¹ÓÐÒ»¸öÖØÒªÔÒò£¬µ±ºÜ¶àÁ¬½ÓͬʱÔËÐÐͬһ¸ö´æ´¢¹ý³Ìʱ£¬Èç¹ûδָ¶¨¼Ü¹¹Ãû³Æ£¬ÕâЩÁ¬½Ó¿ÉÄÜ»áÒòΪҪ»ñÈ¡±àÒëËø£¨compile lock£©¶ø»¥Ïà×èÈû¡£
5£®Ê¹ÓÃSET NOCOUNT ONÔÚÿ¸ö´æ´¢¹ý³ÌµÄ¿ªÍ·SET NOCOUNT OFFÔÚ½áβ¡£µ± SET NOCOUNT Ϊ ON ʱ£¬½«²»¸ø¿Í»§¶Ë·¢ËÍ´æ´¢¹ý³ÌÖеÄÿ¸öÓï¾äµÄ DONE_IN_PROC ÐÅÏ¢¡£µ±Ê¹Óà Microsoft SQL Server ÌṩµÄʵÓù¤¾ßÖ´Ðвéѯʱ£¬ÔÚ Transact-SQL Óï¾ä£¨Èç SELECT¡¢INSERT¡¢UPDATE ºÍ DELETE£©½áÊøÊ±½«²»»áÔÚ²éѯ½á¹ûÖÐÏÔʾ"n rows affected"¡£Èç¹û´æ´¢¹ý³ÌÖаüº¬µÄһЩÓï¾ä²¢²»·µ»ØÐí¶àʵ¼ÊµÄÊý¾Ý£¬Ôò¸ÃÉèÖÃÓÉÓÚ´óÁ¿¼õÉÙÁËÍøÂçÁ÷Á¿£¬Òò´Ë¿ÉÏÔÖøÌá¸ßÐÔÄÜ¡£
²¹³ä£º
1.µ± SET NOCOUNT Ϊ ON ʱ£¬Ò²¸üР@@ROWCOUNT º¯Êý¡£
2. @@ROWCOUNTÊÇ·µ»ØÊÜÉÏÒ»Óï¾äÓ°ÏìµÄÐÐÊý£¬°üÀ¨ÕÒµ½¼Ç¼µÄÊýÄ¿¡¢É¾³ýµÄÐÐÊý¡¢¸üеļǼÊýµÈ£¬²»ÒªÈÏΪÊÇ·µ»Ø²éÕҵļǼÊýÄ¿£¬¶øÇÒ@@ROWCOUNTÒª½ô¸úÐèÒªÅжÏÓï¾ä£¬·ñÔò@@ROWCOUNT½«·µ»Ø0¡£
3. ʹÓôíÎó´¦Àí³ÌÐò£¬ÓÃÀ´¼ì²é @@ERROR ϵͳº¯ÊýµÄ T-SQL Óï¾ä (IF) ʵ¼ÊÉÏÔÚ½ø³ÌÖÐÇå³ýÁË @@ERROR Öµ£¬ÎÞ·¨ÔÙ²¶»ñ³ýÁãÖ®ÍâµÄÈκÎÖµ,±ØÐëʹÓà SET »ò SELECT Á¢¼´²¶»ñ´íÎó´úÂë¡£
6£®É÷ÓÃËø£¬¿ÉÒÔʹÓÃNOLOCKÌáʾ£¬ËüÓëREADUNCOMMITTEDÊǵȼ۵ġ£¸ü¼òµ¥µÄ×ö·¨ÊÇÔÚ´æ´¢¹ý³ÌµÄ¿ªÍ·SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED£¬½áβREAD COMMITTED¡£
7£®²éѯ½ö½ö·µ»ØÐèÒªµÄÐкÍÁÐ
8£®ÔÚÊʵ±µÄʱºòʹÓÃÊÂÎñ£¬¾¡Á¿½«ÊÂÎñ·ÅÔÚÒ»¸ö´æ´¢¹ý³ÌÖС£
9£®¾¡Á¿ÉÙµÄʹÓÃÁÙʱ±í£¬ÒòΪ´óÁ¿Ê¹ÓÃÁÙʱ±í¿ÉÄÜʹtempdb³ÉΪƿ¾±¡£¿ÉÒÔʹÓñí±í´ïʽ£¬
Ïà¹ØÎĵµ£º
µ±ÔÚÄÚÁ¬½Ó²éѯÖмÓÈëÌõ¼þʱ£¬ÎÞÂÛÊǽ«Ëü¼ÓÈëµ½join×Ӿ䣬»¹ÊǼÓÈëµ½where×Ӿ䣬ÆäЧ¹ûÊÇÍêȫһÑùµÄ£¬µ«¶ÔÓÚÍâÁ¬½ÓÇé¿ö¾Í²»Í¬ÁË¡£µ±°ÑÌõ¼þ¼ÓÈëµ½ join×Ó¾äʱ£¬»á·µ»ØÍâÁ¬½Ó±íµÄÈ«²¿ÐУ¬È»ºóʹÓÃÖ¸¶¨µÄÌõ¼þ·µ»ØµÚ¶þ¸ö±íµÄÐС£Èç¹û½«Ìõ¼þ·Åµ½where×Ó¾äÖУ¬½«»áÊ×ÏȽøÐÐÁ¬½Ó²Ù×÷£¬È»ºóʹÓÃwhere×Ó¾ä¶ÔÁ¬½ÓºóµÄÐнøÐÐɸѡ¡ ......
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
fr ......
@@IDENTITY£º·µ»Ø×îºó²åÈëµÄ±êʶֵµÄϵͳº¯Êý¡£
SCOPE_IDENTITY£º·µ»Ø²åÈ뵽ͬһ×÷ÓÃÓòÖеıêʶÁÐÄÚµÄ×îºóÒ»¸ö±êʶֵ¡£Ò»¸ö·¶Î§ÊÇÒ»¸öÄ£¿é£º´æ´¢¹ý³Ì¡¢´¥·¢Æ÷¡¢º¯Êý»òÅú´¦Àí¡£Òò´Ë£¬Èç¹ûÁ½¸öÓï¾ä´¦ÓÚͬһ¸ö´æ´¢¹ý³Ì¡¢º¯Êý»òÅú´¦ÀíÖУ¬ÔòËüÃÇλÓÚÏàͬµÄ×÷ÓÃÓòÖС£
IDENT_CURRENT:·µ»ØÎªÖ¸¶¨µÄ±í»òÊÓͼÉú³ÉµÄ×îºóÒ»¸ö±êʶֵ ......
Õª×Ô£ºhttp://www.cnblogs.com/dicky/archive/2007/01/12/618453.html
¸ñʽ:
CONVERT(data_type,expression[,style])
˵Ã÷:
´ËÑùʽһ°ãÔÚʱ¼äÀàÐÍ(datetime,smalldatetime)Óë×Ö·û´®ÀàÐÍ(nchar,nvarchar,char,varchar)
Ï໥ת»»µÄʱºò²ÅÓõ½.
Àý×Ó:
SELECT CONVERT(varchar(30),getdate(),101) now
½á¹ûΪ ......