SQLÓï¾äµÄÓÅ»¯ÒÔ¼°Ë÷ÒýµÄÓ¦Ó÷¶Î§
SQLÓÅ»¯µÄÔÔòÊÇ£º½«Ò»´Î²Ù×÷ÐèÒª¶ÁÈ¡µÄBLOCKÊý¼õµ½×îµÍ¡£
µ÷Õû²»Á¼SQLͨ³£¿ÉÒÔ´ÓÒÔϼ¸µãÇÐÈ룺
¼ì²é²»Á¼µÄSQL£¬¿¼ÂÇÆäд·¨ÊÇ·ñ»¹ÓпÉÓÅ»¯ÄÚÈÝ£»
¼ì²é×Ó²éѯ¿¼ÂÇSQL×Ó²éѯÊÇ·ñ¿ÉÒÔÓüòµ¥Á¬½ÓµÄ·½Ê½½øÐÐÖØÐÂÊéд£»
¼ì²éÓÅ»¯Ë÷ÒýµÄʹÓã»
¿¼ÂÇÊý¾Ý¿âµÄÓÅ»¯Æ÷;
²éѯµÄÒ»°ã¹æÔò
Ø Ã÷È·Ö¸³ö¼ìË÷µÄ×ֶΣ¬¾¡Á¿¼õÉÙ¶Ô¶àÓàµÄÁÐÓë¶àÓàµÄÐжÁÈ¡¡£
½ûֹʹÓà select * from table ……µÄ·½Ê½·ÃÎÊ±í¡£
Ø ÔÚÒ»¸öSQLÓï¾äÖУ¬Èç¹ûÒ»¸öwhereÌõ¼þ¹ýÂ˵ÄÊý¾Ý¿â¼Ç¼Խ¶à£¬¶¨Î»Ô½×¼È·£¬Ôò¸ÃwhereÌõ¼þÔ½Ó¦¸ÃÇ°ÒÆ¡£
Ø ²éѯʱ¾¡¿ÉÄÜʹÓÃË÷Òý¸²¸Ç¡£¼´¶ÔSELECTµÄ×ֶν¨Á¢¸´ºÏË÷Òý£¬ÕâÑù²éѯʱֻ½øÐÐË÷ÒýɨÃ裬²»¶ÁÈ¡Êý¾Ý¿é¡£
Ø ÔÚÅжÏÓÐÎÞ·ûºÏÌõ¼þµÄ¼Ç¼ʱ²»ÒªÓÃSELECT COUNT £¨*£© Óï¾ä¡£ È磺
BF: select count(*) from table where condition
ÔÚMSSQLºÍSybaseÖУ¬Ê¹Óà if exists (select 1 from table_name where condition)ÐÔÄܽϺã¬ÔÚOracleÖУ¬Ê¹ÓÃselect 1 from table_name where condition½ÏºÃ¡£
Ø ÄÚ²ãÏÞ¶¨ÔÔò
ÔÚÆ´Ð´SQLÓï¾äʱ£¬½«²éѯÌõ¼þ·Ö½â¡¢·ÖÀ࣬²¢¾¡Á¿ÔÚSQLÓï¾äµÄ×îÀï²ã½øÐÐÏÞ¶¨£¬ÒÔ¼õÉÙÊý¾ÝµÄ´¦ÀíÁ¿¡£
Ó¦¾ø¶Ô±ÜÃâÔÚorder by×Ó¾äÖÐʹÓñí´ïʽ¡£
¡¡¡¡
ÕýȷʹÓÃINDEXµÄSQL
Èç¹ûºÍ²éѯÌõ¼þÏà¹ØµÄcolumnÉÏÓн¨index,ÒÔϼ¸µãÄܰïÖúSQLÕýÈ·µÄʹÓÃindex ¡£
Ø ±ÜÃâÏÔʽ»òÒþº¬µÄÀàÐÍת»»¡£
where×Ó¾äÖдæÔÚÊý¾ÝÀàÐÍÒþÐÎת»»µÄ,ÈçÓÃNumeric ÐÍºÍ IntÐ͵ÄÁеıȽÏʱ£¬²»ÄÜʹÓÃindex¡£
Ø WHERE×Ó¾äÖÐÈκζÔÁеIJÙ×÷¶¼½«ÎÞ·¨Ê¹ÓÃindex£¬Ëü°üÀ¨Êý¾Ý¿âº¯Êý¡¢¼ÆËã±í´ïʽµÈµÈ£¬ËùÒÔÒª¾¡Á¿¼õÉÙÔÚ£½×ó±ßµÄÁеÄÔËËã¡£È磺
BF£ºselect staff_no, staff_name from staff_member where salary*2 <= 10000;
RP£ºselect staff_no, staff_name from staff_member where salary <= 5000;
Ø WHERE×Ó¾äÖÐʹÓò»µÈÓÚ(<>)ÔËËãµÄ£¬½«ÎÞ·¨Ê¹ÓÃindex¡£¿ÉÒÔÓÃunion all¸Äд¡£È磺
BF£ºselect staff_no, staff_name from staff_member where dept_no<>2001;
RP£ºsele
Ïà¹ØÎĵµ£º
ÒÔÏÂ˵Ã÷¾ùÕë¶ÔÆóÒµ°æ»òÕß¿ª·¢°æ£»
1.¿ªÆôsql server ʵÀýµÄÔ¶³ÌÁ¬½Ó£»
“¿ªÊ¼”-->“³ÌÐò”-->“Microsoft SQL Server 2005”-->“ÅäÖù¤¾ß”-->“SQL Server ÍâΧӦÓÃÅäÖÃÆ÷”-->“·þÎñºÍÁ¬½ÓµÄÍâΧӦÓÃÅäÖÃÆ÷”--> ......
SQL Server 2005±¨ÁËÕâô¸ö´í£ºCannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "Chinese_PRC_90_CI_AS" in the like operation.
ºó²éÖ¤£¬ÕâÊÇÓÉÓÚÁ½¸ö×ö±È½ÏµÄÁеÄÅÅÐò·½Ê½²»Í¬£¬ÖÐÎÄÓкܶàÖÖÅÅÐò·½Ê½£¬¶ÔÓ¦ÈçÏ£º
ÖÐÎÄ£¨Ïã¸ÛÌØ±ðÐÐÕþÇø£©
Chinese_Hong_Kong_Stroke_90_CI_AS
ÖÐÎÄ£¨Ïã¸ÛÌØ±ðÐ ......
C#ÖвÙ×÷OracleʱµÄSQLÓï¾ä²ÎÊýµÄÓ÷¨
OracleTransaction myTrans ;
conn.Open();
myTrans =conn.BeginTransaction(IsolationLevel.ReadCommitted) ......
sql serverÖÐÈçºÎÅжϱí»òÕßÊý¾Ý¿âµÄ´æÔÚ£¬µ«ÔÚʵ¼ÊʹÓÃÖУ¬ÐèÅжÏStatus״̬λ£º
ÆäÖÐijЩ״̬λ¿ÉÓÉÓû§Ê¹Óà sp_dboption£¨read only¡¢dbo use only¡¢single user µÈ£©½øÐÐÉèÖãº
1 = autoclose£»Ê¹Óà sp_dboption ÉèÖᣠÊý¾Ý¿âÍêÈ«¹Ø±Õ£¬Æä×ÊÔ´ÔÚ×îºóÒ»¸öÓû§×¢ÏúºóÊÍ·Å¡£
4 = select into/bulkcopy£»Ê¹Óà sp_dbopti ......
ALTER procedure [dbo].[sp_lock_check]
@spid1 int = NULL,
@spid2 int = NULL
as
set nocount on
if @spid1 is not NULL
begin
select ......