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

-T-SQL字符串函数select name,LEFT(name,12) as biketype, SUBSTRING(name,9,4) as model, RIGHT(name,2) as size, RTRIM(style) as stylefrom Production.Productwhere ProductNumber like bk-t%;select REPLACE(name,mountain,Mtn) as ReplaceName, STUFF(name,11,2,x01) as StuffName, REVERSE(productnumber)as ReverseNum, STUFF(productnumber,1,2,REPLICATE(0,3) as ReplicateNum, productnumber+SPACE(4)+LOWER(productline) as ProductLinefrom Production.Productwhere ProductID=771;select name+CHAR(9)+str(makeflag) as Product, ASCII(makeflag) as asciiMakeFlag, RTRIM(style) as style, UNICODE(rtrim(style) as unicodeStylefrom Production.Productwhere ProductID=771;select LEN(Description) as LenghthDescription, CHARINDEX(tech,description) as CharIndexDescription, PATINDEX(%bike%,description) as PatindexDescriptionfrom Production.ProductDescriptionwhere ProductDescriptionID=321;select * from Production.ProductDescription where ProductDescriptionID=321;declare Name1 varchar(30)set Name1=(select lastname from Person.Contact where ContactID=1829)declare Name2 varchar(30)set Name2=(select lastnamefrom Person.Contactwhere ContactID=2580)select Name1 as Name1,Name2 as Name2,Soundex(Name1) as soundexname1,soundex(Name2) as soundexname2,difference(Name1,Name2) as soundexdiff-T-SQL数学函数select TerritoryID, AVG(SubTotal) as avgsales, SUM(subtotal) as sumsales, MAX(subtotal) as maxsales, MIN(subtotal) as minsales from Sales.SalesOrderHeadergroup by TerritoryIDorder by TerritoryIDselect min(name) as firstproduct, MAX(name) as lastproductfrom Production.Productselect COUNT(*)as productcount, COUNT(productmodelid) as modelcount, COUNT(distinct ProductModelID) as distinctcountfrom Production.Productselect Color, AVG(listprice) as avgprice, COUNT(*) as totalAmount, GROUPING(color) as aggGroupfrom Production.Productgroup by Color with rollupselect CHECKSUM_agg(CAST(Quantity as int) as checksum_agg, CHECKSUM_agg(distinct CAST(quantity as int) as checksumdistinctfrom Production.ProductInventoryselect * from Production.ProductInventory declare agenle floatset agenle=3.1415926declare sine floatset sine=SIN(agenle)select sine as sine,degrees(ASIN(sine) as asinedeclare degrees floatset degrees=30.0declare radians floatset radians=RADIANS(degrees)select radians as radians,DEGREES(radians) as degreesdeclare pi floatset pi=PI()declare degree floatset degree=DEGREES(pi)select pi as pi,degree as degree, RADIANS(degree) as radians declare root1 floatset root1=4declare root2 floatset root2=SQUARE(root1)select root1 as root1,root2 as root2,SQRT(root1) as sqrtrootselect FirstName+ +LastName as fullname, ROUND(SalesYTD,2) AS salesYTD, (select ROUND(AVG(salesYTD),2) from Sales.vSalesPerson where JobTitle=Sales Representative) as AVGsalesfrom Sales.vSalesPersonwhere SalesPersonID=275select p.ProductID as productID, as productName, -P.ProductSubcategoryID AS productsubcategoryID, (select ps.Name from Production.ProductSubcategory ps where ps.ProductSubcategoryID=ps.ProductSubcategoryID and ps.Name like %saddles%) as subcategoryname - as subcategorynamefrom Production.Product p-,Production.ProductSubcategory pswhere p.ProductSubcategoryID is not nulland p.Name like %seat%select ProductID,Name,ProductSubcategoryID from Production.Productwhere ProductSubcategoryID( select ProductSubcategoryID from Production.ProductSubcategory where Name=mountain bikes)select FirstName+ +LastName,round(salesYTD,0),JobTitle from Sales.vSalesPersonwhere ROUND(salesYTD,0)= any( select round(avg(salesYTD),0) from Sales.vSalesPerson group by JobTitle) select SalesPersonID,TerritoryID,SalesQuota,SalesYTD from Sales.SalesPersonwhere TerritoryID not in ( select TerritoryID from Sales.SalesTerritory where Name=northeast or Name=northwest)order by TerritoryID desc,SalesPersonID descselect * from Production.Illustrationset identity_insert adventureworks.Production.Illustration offinsert Production.Illustration(IllustrationID,Diagram,ModifiedDate)values(9,2012-08-22 16:47:11.788)update Production.Illustrationset Diagram=where ModifiedDate in (select ModifiedDate from Production.Illustration where ModifiedDate like %2012-08%)select ProductID,Name,ProductModelIDfrom Production.Productwhere exists ( select * from Production.ProductModel t1,Production.Product t2 where t1.ProductModelID = t2.ProductModelID and t1.Name like %brakes%) -T-SQL 系统函数use AdventureWorksif OBJECT_ID(TableA,u) is not nulldrop table dbo.TableAcreate table dbo.TableA(colA int identity(101,100) not null,colB varchar(20) not null)insert into TableA(colB) values(red);insert into TableA(colB) values(blue);insert into TableA(colB) values(yellow);insert into TableA(colB) values(green);insert into TableA(colB) values(black);insert into TableA(colB) values(white); insert into TableA(colB) values(orange);insert into TableA(colB) values(青色);update TableAset colB=orangewhere colA=701select * from TableAselect IDENT_CURRENT(TableA) as LastValue, IDENT_SEED(TableA) as SeedValue, IDENT_INCR(TableA) as incrValue, rowcount as RowAffected, Rowcount_big() as BigrowAffected, IDENTITY as identitya, SCOPE_IDENTITY() as scope;select SERVERPROPERTY(edition) as svrEdition, SERVERPROPERTY(instancename) as svrInstancename, SERVERPROPERTY(collation) as svrcollation, COLLATIONPROPERTY(chinese_prc_ci_as,version) as collversion, COLLATIONPROPERTY(chinese_prc_ci_as,codepage) as collcodepage; select USER_NAME() as username, USER_ID() as userid, SUSER_SID() as usersid, SYSTEM_USER as sysuser; select SESSION_USER as sessionuser, app_name() as appname, SESSIONPROPERTY(ansi_nulls) as AnsiNulls, SERVERPROPERTY(quoted_identifier) as quotedID; select CURRENT_TIMESTAMP as Currenttimestamp, GETANSINULL() as asinull, HOST_NAME() as hostname, HOST_ID() as hostID; select name as name fro m fn_helpcollations() where name like greek_ci_ai%select newid() as newguide;select PARSENAME(winsrv.adventureworks2008.person.address,4) as servername, PARSENAME(winsrv.adventureworks2008.person.address,3) as dbname, PARSENAME(winsrv.adventureworks2008.person.address,2) as schamename, PARSENAME(winsrv.adventureworks2008.person.address,1) as objectname; select ISDATE(2012-08-23 15:29:34.345) as datetime, isnumeric(123) as isnumber; select FirstName + + LastName as Fname, SalesQuota as quota -ISNULL(SalesQuota,0) as salesquotafrom Sales.vSalesPersonwhere CountryRegionName=united statesselect DB_ID() as defaultdb, DB_ID(adventureworksLT2008R2) as specificdb, DB_NAME() as defaultdbnanme, DB_NAME(10) as specificdbname; select SCHEMA_ID() as defaultID, SCHEMA_ID(sales) as specificID, SCHEMA_NAME() as defaultname, SCHEMA_NAME(8) as specificname; select OBJECT_ID(Sales.SalesPerson) as Id_defaultDb, OBJECT_ID(AdventureWorks.Production.ProductCategory) as ID_specificDB, OBJECT_NAME(1298103665) as Name_defaultdb, OBJECT_NAME(309576141,10)as name_specificdb; select DATABASEPROPERTYEX(AdventureWorks,COLLATIONNAME) as collation, DATABASEPROPERTYEX(adventureworks,isautoshrink) as ISautoshrink, DATABASEPROPERTYEX(adventureworks,isfulltextEnabled) as isfulltextEnabled; select OBJECTPROPERTYEX(1042102753,basetype) as basetype, OBJECTPROPERTYEX(1042102753,isindexed) as indexed, OBJECTPROPERTYEX(1042102753,isusertable) as isusertable; select INDEXPROPERTY(1042102753,PK_salesperson_bussinessentityID,isculstered) as isclustered, INDEXPROPERTY(1042102753,PK_salesperson_businessentityID,isfulltextkey) as isfulltextkey, INDEXPROPERTY(1042102753,PK_salesperson_businessentityID,isunique) as uniqueind; select COLUMNPROPERTY(1042102753,salesquota,allowsnull) as allowsnull, COLUMNPROPERTY(1042102753,salesquota,columnid) as columnid, COLUMNPROPERTY(1042102753,salesquota,iscomputed) as iscomputed; select TYPEPROPERTY(money,allowsnull) as allowsnull, TYPEPROPERTY(money,precision) as precision, TYPEPROPERTY(money,scale) as scale; select FILE_IDEX(adventureworks_data) as fileid, FILE_name(1) as filename, FILEGROUP_ID(primary) as filegroupid, FILEGROUP_NAME(1) as filegroupname; select FILEPROPERTY(adventureworks_data,isreadonly) as i