SQL Server 2008 示例数据库教程sql语句.doc

--T-SQL字符串函数
selectname,LEFT(name,12)asbiketype,
SUBSTRING(name,9,4)asmodel,
RIGHT(name,2)assize,
RTRIM(style)asstyle
fromProduction.Product
whereProductNumberlike'bk-t%';

selectREPLACE(name,'mountain','Mtn')asReplaceName,
STUFF(name,11,2,'x01')asStuffName,
REVERSE(productnumber)asReverseNum,
STUFF(productnumber,1,2,REPLICATE(0,3))asReplicateNum,
productnumber+SPACE(4)+LOWER(productline)asProductLine
fromProduction.Product
whereProductID='771';

selectname+CHAR(9)+str(makeflag)asProduct,
ASCII(makeflag)asasciiMakeFlag,
RTRIM(style)asstyle,
UNICODE(rtrim(style))asunicodeStyle
fromProduction.Product
whereProductID='771';

selectLEN(Description)asLenghthDescription,
CHARINDEX('tech',description)asCharIndexDescription,
PATINDEX('%bike%',description)asPatindexDescription
fromProduction.ProductDescription
whereProductDescriptionID='321';

select*fromProduction.ProductDescriptionwhereProductDescriptionID='321';

declare@Name1varchar(30)
set@Name1=(
selectlastname
fromPerson.Contact
whereContactID='1829')
declare@Name2varchar(30)
set@Name2=(
selectlastname
fromPerson.Contact
whereContactID='2580')
select@Name1asName1,@Name2asName2,
Soundex(@Name1)assoundexname1,
soundex(@Name2)assoundexname2,
difference(@Name1,@Name2)assoundexdiff

--T-SQL数学函数
selectTerritoryID,
AVG(SubTotal)asavgsales,
SUM(subtotal)assumsales,
MAX(subtotal)asmaxsales,
MIN(subtotal)asminsales
fromSales.SalesOrderHeader
groupbyTerritoryID
orderbyTerritoryID

selectmin(name)asfirstproduct,
MAX(name)aslastproduct
fromProduction.Product

selectCOUNT(*)asproductcount,
COUNT(productmodelid)asmodelcount,
COUNT(distinctProductModelID)asdistinctcount
fromProduction.Product

selectColor,
AVG(listprice)asavgprice,
COUNT(*)astotalAmount,
GROUPING(color)asaggGroup
fromProduction.Product
groupbyColor
withrollup

selectCHECKSUM_agg(CAST(Quantityasint))aschecksum_agg,
CHECKSUM_agg(distinctCAST(quantityasint))aschecksumdistinct
fromProduction.ProductInventory

select*fromProduction.ProductInventory

decla