Sqlº¯Êý´óÈ«
--¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price) --ËãÆ½¾ùÊý
from titles
where type='business'
go
use pubs
go
select max(ytd_sales) --×î´óÊý
from titles
go
use pubs
go
select min(ytd_sales) --×îСÊý
from titles
go
use pubs
go
select type,sum(price),sum(advance) --ÇóºÍ
from titles
group by type
order by type
go
use pubs
go
select count(distinct city) --Çó¸öÊý
from authors
go
use pubs
go
select stdev(royalty) --·µ»Ø¸ø¶¨±í´ïʽÖÐËùÓÐÖµµÄͳ¼Æ±ê׼ƫ²î
from titles
go
use pubs
go
select stdevp(royalty) --·µ»Ø±í´ïʽÖÐËùÓÐÖÆµÄÌî³äͳ¼Æ±ê׼ƫ²î
from titles
go
use pubs
go
select var(royalty) --·µ»ØËùÓÐÖµµÄͳ¼Æ·½²î
from titles
go
use pubs
go
select varp(royalty) --·µ»ØËùÓÐÖµµÄÌî³äµÄͳ¼Æ·½²î
from titles
go
--Êýѧº¯Êý
select sin(23.45),atan(1.234),rand(),PI(),sign(-2.34) --ÆäÖÐrandÊÇ»ñµÃÒ»¸öËæ»úÊý
--ÅäÖú¯Êý
SELECT @@VERSION --»ñÈ¡µ±Ç°Êý¾Ý¿â°æ±¾
SELECT @@LANGUAGE --µ±Ç°ÓïÑÔ
--ʱ¼äº¯Êý
select getdate() as 'wawa_getdate' --µ±Ç°Ê±¼ä
select getutcdate() as 'wawa_getutcdate' --»ñÈ¡utcʱ¼ä
select day(getdate()) as 'wawa_day' --È¡³öÌì
select month(getdate()) as 'wawa_month' --È¡³öÔÂ
select year(getdate()) as 'wawa_year' --È¡³öÄê
select dateadd(d,3,getdate()) as wawa_dateadd --¼ÓÈýÌì,×¢Òâ'd'±íʾÌì,'m'±íʾÔÂ,'yy'±íʾÄê,ÏÂÃæÒ»Ñù
select datediff(d,'2004-07-01','2004-07-15') as wawa_datediff --¼ÆËãÁ½¸öʱ¼äµÄ²î
select datename(d,'2004-07-15') as wawa_datename --È¡³öʱ¼äµÄijһ²¿·Ö
select datepart(d,getdate()) as wawa_datepart --È¡³öʱ¼äµÄijһ²¿·Ö,ºÍÉÏÃæµÄÄǸö²î²»¶à
--×Ö·û´®º¯Êý
select ascii(123) as '123',ascii('123') as '"123"',ascii('abc') as '"abc"' --ת»»³ÉasciiÂë
select char(123),char(321),char(-123) --¸ù¾Ýasciiת»»³É×Ö·û
select lower('ABC'),lower('Abc'),upper('Abc'),upper('abc') --ת»»´óСд
select str(123.45,6,1), str(123.45,2,2) --°ÑÊýֵת»»³É×Ö·û´®
select ltrim(' "×ó±ßûÓпոñ"') --È¥¿Õ¸ñ
select rtrim('"ÓÒ±ßûÓпոñ" ') --È¥¿
Ïà¹ØÎĵµ£º
exists £¨sql ·µ»Ø½á¹û¼¯ÎªÕ棩
not exists (sql ²»·µ»Ø½á¹û¼¯ÎªÕ棩
ÈçÏ£º
±íA
ID NAME
1 A1
2 A2
3 A3
±íB
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
±íAºÍ±íBÊÇ£±¶Ô¶àµÄ¹ØÏµ A.ID => B.AID
......
How To Use Dynamic Sql in Sql Server ?
¶¯Ì¬SQL ÔÚsql server ProcedureÖеÄÓ¦ÓÃ
Create PROCEDURE [dbo].[Proc_Get_Serial_No]
(
@Table_Name varchar(20),
......
¶àÓàµÄ»°¾Í²»¶à˵ÁË£¬ÕýÊÔlinq to sqlµÄѧϰ¡£
ÏÈÀ´¿´¿´linq to sql µÄÌØÐÔ:
×Ô¶¯ÊôÐÔ(Auto-Implemented Properties)
Òþº¬ÀàÐ;ֲ¿±äÁ¿(Local Variable Type Inference)
ÄäÃûÀàÐÍ(Anonymous Types)
¶ÔÏóÓ뼯ºÏ³õʼ»¯Æ÷(Object and Collection Initializers)
À©Õ¹·½·¨(Extension Methods)
Lambda±í´ïʽºÍLambda±í´ïʽ ......
ÖÚËùÖÜÖª£¬SQL ServerÖ´ÐÐSQLÓï¾äµÄÐÔÄÜÅж¨±ê×¼Ö÷ÒªÊÇIO¶ÁÈ¡Êý´óС¡£±¾ÎÄÔÚ²»Î¥·´ÕâÒ»ÔÔòÇé¿öÏ£¬Í¬Ê±À´·ÖÎöһϲ¿·ÖSQLÓï¾äÖ´ÐÐʱ£¬SQL ServerÄÚ´æµÄ±ä»¯Çé¿ö¡£
Ê×ÏȼòÊöÒ»ÏÂSQL ServerÄÚ´æÕ¼ÓõÄÌØµã¡£SQL ServerËùÕ¼ÓõÄÄÚ´æ³ý³ÌÐò£¨¼´SQL
ServerÒýÇæ£©Í⣬Ö÷Òª°üÀ¨»º´æµÄÊý¾Ý£¨Buffer£©ºÍÖ´Ðмƻ®£¨Cache£©¡£SQL ......
ÎÒÃÇÕâÀï¶Ô SQL Server ×Ö·û´®º¯Êý½øÐзÖÃűðÀàµØÁгö£¬±ãÓÚ²éÔĺͼÇÒ䣬ÏàÐÅ´ó¼Ò¶¼ÔÚÆäËü·½ÃæÓиßÉîµÄ±à³Ì»ù´¡£¬´Ó×ÖÃæÉÏÀ´Ëµ´ó¼Ò¶¼ÖªµÀÕâЩº¯ÊýµÄÒâÒ壬¾Í²»¶ÔÕâЩº¯Êý×÷¹ý¶àµÄ½âÊÍÁË£¬Ö÷Ҫ̸Щ¾Ñ飬¾ßÌåÇë²Î¼ûÁª»ú´ÔÊé¡£
ASCII(character_expression) ·µ»Ø×î×ó¶Ë×Ö·ûµÄ ASCII ´úÂëÖµ
CHAR(integer_expression)
UNICO ......