SQLº¯Êý´óÈ«
¾ÛºÏº¯Êý
MAX(×Ö¶Î)
Çóij×Ö¶ÎÖеÄ×î´óÖµ
MIN(×Ö¶Î)
Çóij×Ö¶ÎÖеÄ×îСֵ
AVG(×Ö¶Î)
Çóij×Ö¶ÎÖÐµÄÆ½¾ùÖµ
SUM(×Ö¶Î)
Çóij×Ö¶ÎÖеÄ×ܺÍ
COUNT(×Ö¶Î)
ͳ¼ÆÄ³×ֶηǿռͼÊý
COUNT
(*)
ͳ¼Æ¼Í¼Êý
¾ÛºÏº¯ÊýÁ·Ï°
²éѯProducts±í£¬ËùÓÐUnitPriceÖ®ºÍ
²éѯProducts±í£¬ËùÓиßÓÚ50µÄUnitPriceÖ®ºÍ
²éѯProducts±í£¬ËùÓÐUnitPrice֮ƽ¾ùÖµ
²éѯCustomers±í£¬¹²ÓжàÉÙCustomer
²éѯCustomers±í£¬¹²ÓжàÉÙCountry
²éѯProducts±í£¬UnitPriceµÄ×î´óÖµ
ÈÕÆÚº¯Êý
GETDATE()
×÷Ó㺻ñÈ¡µ±Ç°ÈÕÆÚʱ¼ä
DATEPART(datepart ,
date)
×÷Ó㺻ñÈ¡ÈÕÆÚ»òʱ¼äµÄÒ»²¿·Ö¡£ÓÐÁ½¸ö²ÎÊý£¬date´ú±íÒª±»²Ù×÷µÄÈÕÆÚ¡£datepart´ú±íÒª»ñ
È¡µÄ²¿·Ö¡£
DATEDIFF(datepart , startdate ,
enddate)
×÷Ó㺱ȽÏÁ½¸öÈÕÆÚijһ²¿·ÖµÄ²îÖµ£¬
ͨ³£startdate´ú±í½ÏÔçµÄÈÕÆÚ , enddate´ú±í½ÏÍíµÄ
DATEADD (datepart , number,
date)
×÷ÓãºÔÚdate»ù´¡ÉϼÓÉÏÒ»¶Îʱ¼ä
YEAR(date)
µÈ¼ÛÓÚDATEPART(year , date)
MONTH(date)
µÈ¼ÛÓÚDATEPART(month , date)
DAY(date)
µÈ¼ÛÓÚDATEPART(day , date)
Datepart Abbr.
year yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
weekday dw
hour hh
minute
mi, n
second
ss, s
millisecond
ms
ËÄÉáÎåÈë±£ÁôÁ½Î»Ð¡Êý
print
Round(5.5678,2)
ÏòÏÂÈ¡Õû
print Floor(1.9)
ÏòÉÏÈ¡Õû
print Ceiling(2.1)
create
table
score
(stuId int,
ps int,
jm int
)
insert into score values(1,99,54)
insert into score values(1,85,43)
insert into score values(1,92,71)
select
stuId,ceiling(ps*0.35+jm*0.65)
from Score
print
rand()*100
CHARINDEX
·µ»Ø×Ö·û´®ÖÐÖ¸¶¨±í´ïʽµÄÆðʼλÖá£
Óï·¨
CHARINDEX ( expression_r1 , expression_r2 [ , start_location ]
)
print charindex('hello','gsfjhellokjhellosa',7)
REPLACE
ÓÃ
Ïà¹ØÎĵµ£º
(1)¶þ½øÖÆÊý¾ÝÀàÐÍ
¡¡¡¡¶þ½øÖÆÊý¾Ý°üÀ¨ Binary¡¢Varbinary ºÍ Image
¡¡¡¡Binary
Êý¾ÝÀàÐͼȿÉÒÔÊǹ̶¨³¤¶ÈµÄ(Binary),Ò²¿ÉÒÔÊDZ䳤¶ÈµÄ¡£
¡¡¡¡Binary[(n)] ÊÇ n λ¹Ì¶¨µÄ¶þ½øÖÆÊý¾Ý¡£ÆäÖУ¬n
µÄȡֵ·¶Î§ÊÇ´Ó 1 µ½ 8000¡£Æä´æ´¢ñ¿µÄ´óСÊÇ n + 4 ¸ö×Ö½Ú¡£
¡¡¡¡Varbinary[(n)] ÊÇ n
λ±ä³¤¶ÈµÄ¶þ½øÖÆÊý¾Ý¡£ÆäÖУ ......
µ±×°ÉÏÁËMSSQL2005ºó£¬ÄÚ´æµÄÕ¼Óûá±äµÃºÜ´ó¡£ËùÒÔÈç¹ûÓÃÒ»¸öÅúÁ¿´¦ÀíÀ´¿ªÆô»ò¹Ø±ÕMSSQL2005ËùÓеķþÎñ£¬Äǽ«»áÈÃÎÒÃǵĵçÄÔ¸üºÃʹÓ᣸ù¾Ý×Ô¼ºµÄ¾Ñ飬×ö³öÁËÏÂÃæÁ½¸öÅú´¦Àí£º
1¡¢¿ªÆô·þÎñ£º£¨¸´ÖƺáÏßµÄÄÚÈÝ£¬×¢Ò⣬·þÎñÆ÷ÕæÕýµÄÃû³ÆÄã¿ÉÒÔͨ¹ý“¿ªÊ¼--¡·¿Ø¼þÃæ°æ--¡·¹ÜÀí¹¤¾ ......
ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£
¡¡¡¡ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£¼ÇµÃÔÚ¸øÒ»¼Ò ......
ÓÃEXPLAIN PLAN ·ÖÎöSQLÓï¾ä
http://blog.csdn.net/kj021320/archive/2006/08/19/1096021.aspx
ÈçºÎÉú³Éexplain plan?
¡¡¡¡½â´ð:ÔËÐÐutlxplan.sql. ½¨Á¢plan ±í
¡¡¡¡Õë¶ÔÌØ¶¨SQLÓï¾ä£¬Ê¹Óà explain plan set statement_id = 'tst1' into plan_table
¡¡¡¡ÔËÐÐutlxplp.sql »ò utlxpls.sql²ì¿´explain plan
EXPLAIN PLA ......