½²½âMSSQLÊý¾Ý¿âÖÐSQLËø»úÖƺÍÊÂÎñ¸ôÀ뼶±ð
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²ÏíËøÒÔ×èÖ¹ÆäËüÊÂÎñ¶ÔÊý¾Ý±íÊý¾ÝµÄÐ޸ġ£
SELECT * from Customer
ÕâÌõÓï¾ä½«Ò»Ö±ËÀËø£¬Ö±µ½ÅÅËûËø½â³ý»òÕßËø³¬Ê±ÎªÖ¹¡£(×¢:ÉèÖÃËø³¬Ê±SET LOCK_TIMEOUT 1800)
SELECT * from Customer WITH (READPAST)
ÕâÌõÓï¾ä½«ÏÔʾaδÌύǰµÄ״̬£¬µ«²»Ëø¶¨Õû¸ö±í¡£Õâ¸öÌáʾָÃ÷Êý¾Ý¿âÒýÇæ·µ»Ø½á¹ûʱºöÂÔ¼ÓËøµÄÐлòÊý¾ÝÒ³¡£
3. Ö´ÐÐÒ»Ìõ²åÈëÓï¾ä¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'b','b'
COMMIT TRAN t
Õâ¸öʱºò£¬¼´Ê¹²½Öè1µÄÊÂÎñ»Ø¹ö£¬ÄÇôaÕâÌõÊý¾Ý½«¶ªÊ§£¬¶øb¼ÌÐø²åÈëÊý¾Ý¿âÖС£
NOLOCK
1£® Ö´ÐÐÈçÏÂÓï¾ä¡£
BEGIN TRAN ttt
SELECT * from Customer WITH (NOLOCK)
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
×¢£ºNOLOCK²»¼ÓÈκÎËø£¬¿ÉÒÔÔöɾ²é¸Ä¶ø²»Ëø¶¨¡£
INSERT INTO Customer SELECT 'a','b' –²»Ëø¶¨
DELETE Customer where ID=1 –²»Ëø¶¨
SELECT * from Customer –²»Ëø¶¨
UPDATE Customer SET Title='aa' WHERE ID=1 –²»Ëø¶¨
ROWLOCK
1. Ö´ÐÐÒ»Ìõ´øÐÐËøµÄ²éѯÓï¾ä¡£
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (±ØÐë)
BEGIN TRAN ttt
SELECT * from Customer WITH (ROWLOCK) WHERE ID=17
WAITFOR delay '00:00:20'
COMMIT TRAN ttt
×¢£ºÔÚɾ³ýºÍ¸üÐÂÕýÔÚ²éѯµÄÊý¾Ýʱ£¬»áËø¶¨Êý¾Ý¡£¶ÔÆäËûδ²éѯµÄÐкÍÔö¼Ó£¬²éѯÊý¾ÝÎÞÓ°Ïì¡£
INSERT INTO Customer SELECT 'a','b' –²»µÈ´ý
DELETE Customer where ID=17 –µÈ´ý
DELETE Customer where ID<>17 –²»µÈ´ý
SELECT * from Customer –²»µÈ´ý
UPDATE Customer SET Title='aa' WHERE ID=17–µÈ´ý
UPDATE Customer SET Title='aa' WHERE ID<>17–²»µÈ´ý
HOLDLOCK£¬TABLOCKºÍTABLOCKX
1. Ö´ÐÐHOLDLOCK
BEGIN TRAN ttt
SELECT * from Customer WITH (HOLDLOCK)
WAITFOR delay '00:00:10'
COMMIT TRAN ttt
×¢£ºÆäËûÊÂÎñ¿ÉÒÔ¶ÁÈ¡±í£¬µ«²»ÄܸüÐÂɾ³ý
update Customer set Title='aa' —ÒªµÈ´ý10ÃëÖС£
SEL
Ïà¹ØÎĵµ£º
ÊÓͼ
SET¡¡NOCOUNT ON;
SET Northwind;
GO
IF OBJECT_ID('dbo.ViewName') IS NOT NULL
DROP VIEW dbo.ViewName;
GO
CREATE VIEW dbo.Viewname
AS
SELECT * from customer AS C
WHERE EXISTS
(SELECT * from dbo.Orders AS O
WHERE O.CustomerI ......
SQL³£Óú¯Êý£º
1. ASCII()£º·µ»Ø×Ö·û´®×î×ó¶Ë×Ö·ûµÄASCIIÂë¡£
2. CHAR()£º½«ASCIIת»»³É×Ö·û¡£(0~255)
3. LOWER()ºÍUPPER()¡£
4. STR()£º°ÑÊýÖµÐÍÊý¾Ýת»»Îª×Ö·ûÐÍÊý¾Ý
SRT(<float_expression>[,length[,<decimal>]])
LTRIM()°Ñ×Ö·û´®Í·²¿¿Õ¸ñÈ¥µô£¬RTRIM()°Ñ×Ö·û´®Î²¿Õ¸ñÈ¥µô¡£
È¡× ......
SQL Server ϵͳȫ¾Ö±äÁ¿
@@CONNECTIONS
·µ»Ø×ÔÉÏ´ÎÆô¶¯ÒÔÀ´Á¬½Ó»òÊÔͼÁ¬½ÓµÄ´ÎÊý¡£
@@CURSOR_ROWS
·µ»ØÁ¬½ÓÉÏ×îºó´ò¿ªµÄÓαêÖе±Ç°´æÔڵĺϸñÐеÄÊýÁ¿(·µ»Ø±»´ò¿ªµÄÓαêÖл¹Î´±»¶ÁÈ¡µÄÓÐЧÊý¾ÝÐеÄÐÐÊý)
@@DATEFIRST
·µ»ØÿÖܵÚÒ»ÌìµÄÊý×Ö
@@ERROR
·µ»Ø×îºóÖ´ÐеÄSQL Óï¾äµÄ´íÎó´úÂë¡£
@@FETCH_STATUS
·µ»Ø ......
xtype ´ú±íÀàÐÍ
C = CHECK Ô¼Êø
D = ĬÈÏÖµ»ò DEFAULT Ô¼Êø
F = FOREIGN KEY Ô¼Êø
L = ÈÕÖ¾
FN = ±êÁ¿º¯Êý
IF = ÄÚǶ±íº¯Êý
P = ´æ´¢¹ý³Ì
PK = PRIMARY KEY Ô¼Êø£¨ÀàÐÍÊÇ K£©
RF = ¸´ÖÆɸѡ´æ´¢¹ý³Ì
S = ϵͳ±í
TF = ±íº¯Êý
TR = ´¥·¢Æ÷
U = Óû§±í
UQ = UNIQUE Ô¼Êø£¨ÀàÐÍÊÇ K£©
V = ÊÓͼ ......
ʹÓà Microsoft SQL Server °²×°Ïòµ¼µÄ"ʵÀýÃû"Ò³£¬¿ÉÖ¸¶¨´´½¨Ä¬ÈÏʵÀý»¹ÊÇ´´½¨ SQL Server Express ÃüÃûʵÀý¡£³ý·ÇÄúÑ¡ÔñĬÈÏʵÀý£¬·ñÔò SQL Server Express ½«Ê¼ÖÕ°²×°ÃüÃûʵÀý (SQLExpress)¡£´ËÐÐΪÓë SQL Server 2005 ²»Í¬£¬ºóÕßÔÚδѡÔñÃüÃûʵÀýµÄÇé¿öÏ£¬½«Ê¼ÖÕ°²×°Ä¬ÈÏʵÀý¡£
Ñ¡Ïî
Ñ¡Ïî
˵Ã÷
ĬÈÏʵÀý ......