--T-SQL字符串函数
select name,LEFT(name,12) as biketype,
       SUBSTRING(name,9,4) as model,
       RIGHT(name,2) as size,
       RTRIM(style) as style
from Production.Product
where 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 ProductLine
from Production.Product
where ProductID='771';
 
select name+CHAR(9)+str(makeflag) as Product,
       ASCII(makeflag) as asciiMakeFlag,
       RTRIM(style) as style,
       UNICODE(rtrim(style)) as unicodeStyle
from Production.Product
where ProductID='771';
 
select LEN(Description) as LenghthDescription,
       CHARINDEX('tech',description) as CharIndexDescription,
       PATINDEX('%bike%',description) as PatindexDescription
from Production.ProductDescription
where 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 lastname
   from Person.Contact
   where 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.SalesOrderHeader
group by TerritoryID
order by TerritoryID
 
 
select min(name) as firstproduct,
      MAX(name) as lastproduct
from Production.Product
 
 
select COUNT(*)as productcount,
       COUNT(productmodelid) as modelcount,
       COUNT(distinct ProductModelID) as distinctcount
from Production.Product
 
select Color,
      AVG(listprice) as avgprice,
      COUNT(*) as totalAmount,
      GROUPING(color) as aggGroup
from Production.Product
group by Color
with rollup
 
select CHECKSUM_agg(CAST(Quantity as int)) as checksum_agg,
      CHECKSUM_agg(distinct CAST(quantity as int)) as checksumdistinct
from Production.ProductInventory
 
select * from Production.ProductInventory
 
declare @agenle float
set @agenle=3.1415926
declare @sine float
set @sine=SIN(@agenle)
select @sine as sine,degrees(ASIN(@sine)) as asine
 
declare @degrees float
set @degrees=30.0
declare @radians float
set @radians=RADIANS(@degrees)
select @radians as radians,DEGREES(@radians) as degrees
 
declare @pi float
set @pi=PI()
declare @degree float
set @degree=DEGREES(@pi)
select @pi as pi,@degree as degree, RADIANS(@degree) as radians
 
declare @root1 float
set @root1=4
declare @root2 float
set @root2=SQUARE(@root1)
select @root1 as root1,@root2 as root2,SQRT(@root1) as sqrtroot
 
select FirstName+'  '+LastName as fullname,
      ROUND(SalesYTD,2) AS salesYTD,
      (select ROUND(AVG(salesYTD),2)
      from Sales.vSalesPerson
      where JobTitle='Sales Representative') as AVGsales
from Sales.vSalesPerson
where SalesPersonID='275'
 
select p.ProductID as productID,
      p.name 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
      --ps.name as subcategoryname
from Production.Product p
--,Production.ProductSubcategory ps
where p.ProductSubcategoryID is not null
and p.Name like '%seat%'
 
select ProductID,Name,ProductSubcategoryID
from Production.Product
where ProductSubcategoryID<>(
     select ProductSubcategoryID
     from Production.ProductSubcategory
     where Name='mountain bikes')
 
select FirstName+' '+LastName,round(salesYTD,0),JobTitle
from Sales.vSalesPerson
where ROUND(salesYTD,0)>= any(
     select round(avg(salesYTD),0)
     from Sales.vSalesPerson
     group by JobTitle)
    
select SalesPersonID,TerritoryID,SalesQuota,SalesYTD
from Sales.SalesPerson
where TerritoryID not in (
     select TerritoryID
     from Sales.SalesTerritory
     where Name='northeast' or Name='northwest')
order by TerritoryID desc,SalesPersonID desc
 
select * from Production.Illustration
 
set identity_insert adventureworks.Production.Illustration off
 
insert Production.Illustration(IllustrationID,Diagram,ModifiedDate)
values(9,
'<!-- Generator: Adobe Illustrator 10.0, SVG Export Plug-In . SVG Version: 3.0.0 Build 76)  -->
','2012-08-22 16:47:11.788')
 
update Production.Illustration
set Diagram='<!-- -->'
where ModifiedDate in (
select ModifiedDate
from Production.Illustration
where ModifiedDate like '%2012-08%')
 
select ProductID,Name,ProductModelID
from Production.Product
where exists (
     select *
     from Production.ProductModel t1,Production.Product t2
     where t1.ProductModelID = t2.ProductModelID
     and t1.Name like '%brakes%')
    
--T-SQL 系统函数
use AdventureWorks
if OBJECT_ID('TableA','u') is not null
drop table dbo.TableA
create 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 TableA
set colB='orange'
where colA='701'
 
select * from TableA
 
select 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 salesquota
from Sales.vSalesPerson
where CountryRegionName='united states'
 
select 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 isonlyread,
      FILEPROPERTY('adventureworks_data','isprimaryfile') as isprimaryfile,
      FILEPROPERTY('adventureworks_data','spaceused') as spaceused;
 
select FILEGROUPPROPERTY('primary','isreadonly') as isreadonly,
      filegroupproperty('primary','isuserdefinedfg') as isuserdefinefg,
      FILEGROUPPROPERTY('primary','isdefault') as isdefault;
     
--T-SQL系统配置函数
select @@DATEFIRST as datefirst;
 
select case @@DATEFIRST when 1 then 'monday'
                   when 2 then 'tuesday'
                   when 3 then 'wednesday'
                   when 4 then 'thursday'
                   when 5 then 'friday'
                   when 6 then 'saturday'
                   when 7 then 'sunday'
                   end as datefirst
 
select @@DBTS as dbtimestap
 
select @@LANGID AS LANGID,@@LANGUAGE AS LANGUAGE;
 
select @@MAX_CONNECTIONS as max_connection,
      @@MAX_PRECISION as max_precision,
      @@OPTIONS as options;
 
set nocount on;
select @@OPTIONS AS SETOPTIONS;
 
SET NOCOUNT OFF;
SELECT @@OPTIONS AS SETOPTIONS;
 
Select @@SERVERNAME as srvname,@@SERVICENAME as svicname,
      @@SPID as spid,@@TEXTSIZE as Tsize;
     
select @@VERSION as version;