SQL Server 查询语句

1，比较NULL值

SELECT OrderDate,SalesOrderNumber,CustomerID,Subtotal FROM Sales.SalesOrderHeader WHERE CurrencyRateID is null

or       .....from Sales.SalesOrderHeader WHERE CurrencyRateID=null

选择不是NULL值的行： select OrderDate,SalesOrderNumber,CustomerID,Subtotal,TaxAmt from Sales.SalesORderHeader

　　WHERE NOT  CurrencyRateID is null

2，使用case语句

　　select a,b,c,d,

　　　　case when e<25 then 'soft'

　　　　　　 when e between 25 and 700 then 'normal'

　　　　　　 else 'hard'

　　　　end WW

from salse.salesorderheader   where shipdate='20010708'

3，使用搜索参数

select a,b,c

from table1 where YEAR(shipdate)=2001 and MONTH(shipdate)=7

or    ....  from table1 where shipdate between '20010701' and '20010731'

二、从多个表中选择数据

1，使用别名：

　　select A.id,A.name,B.info  from table1 A,table2 B  WHERE A.id=B.id AND A.sid=43234

2，使用INNER JOIN语法

　　select A.id,A.name,B.info from table1 A INNER JOIN table2 B ON A.id=B.id where A.sid=432432

3，使用2个以上的表  （INNER JOIN  inner join）

　　select A.id,A.Name,B.[Primary],B.ModifiedDate,C.LargePhotoFileName

　　from table1 A INNER JOIN table2 B INNER JOIN table3 C

　　　　　ON B.ProductPhotoID=C.ProductPhotoID        --on 代表怎样把不同的表按照顺序连接起来。

　　　　   ON A.ProductID=B.ProductID

　　where B.ModifiedDate<='20000101'

4，使用LEFT JOIN

　　厂商表与特定的产品表关联，但是新收购的公司没有与任何产品关联，但是查询时也要显示没有关联的厂商，使用INNER JOIN的话没有关联任何产品的厂商就不会显示，可以使用LEFT JOIN ，left join 会显示左表中的所有记录，另外加上右表中您需要的数据。针对一个特定的行，假如不存在一个关系，那么右表中的所有列回返回一个NULL值。

　　select a.VenderID,a.AccountNumber,a.[Name],

　　　　　c.ProductID,c.[Name]

　　from table1 a left join table2 b inner join table3 c

　　　　  on b.ProductID=c.ProductID

　　　　  on a.VendorID=b.VendorID

     where c.ProductID is null

5，使用RIGHT JOIN

　　保留右边的所有列

6，full join  保留2个表所有行

三、读取单一值

1，聚合函数

　　聚合函数(aggregate function)帮助我们获得值得汇总，其中包括COUNT，SUM，AVG，MAX和MIN函数。聚合函数要与group by 子句配合起来使用。

　　select count(*) numofelements  from  table1.

　　select count(*) numofelements,max(orderdate) LastSaleDate from table1.

　yuselect A.班级编号,A.班级名称,COUNT(*) 学生人数,(MAX(B.学号)) 学号
             from 班级信息 A inner join 学生信息 B on A.班级编号=B.班级编号
             group by A.班级编号,A.班级名称
             order by A.班级名称

2，配置函数

@@Datefirst  　　　　　　　　　　返回代表一周的第一天的一个整数

@@DBTS　　　　　　　　　　　　当前数据库的最后一个时间戳

@@LANGID,@@LANGUAGE　　　　实际连接的默认语言

@@LOCKTIMEOUT　　　　　　　　锁定超时值

@@NESTLEVEL　　　　　　　　嵌套级别

@@ SPID　　　　　　　　　　连接标识符，会话ID

3，游标函数

　　游标函数用于管理游标(cursor)，常用的游标函数：@@FETCH_STATUS。返回0标识上一个FETCH语句成功。-1表示失败。-2表示值不存在。

4，日期和时间函数

　　SELECT YEAR('2006/02/03'),MONTH('20000101'),DAY('20100901')

　　GETDATE() 和GETUTCDATE()　　可以返回日期和时间。

　　datepart()和 datename()  分别获取时间的不同部分(小时或分钟)，以及日或者月的名称。

　　执行日期运算符，dateadd()和datediff()。。

5，数学函数

　　abs()  绝对值 , square() 平方根  , power(expression, y) 返回给定表达式乘指定次方的值 ,  exp()：返回指数值 , floor() 大于数的最小整数 ,  cos()返回指定表达式中以弧度表示的指定角的三角余弦。

6，元数据函数

　　元数据(metadate)函数用于获得有关数据库和数据库对象的信息。

　　databaseproperty('master' ,'IsAnisiNullDefault')     datebasepropertyex('master', 'collation').

7，安全函数

　　current_user(),   is_member()    ,    is_srvrolemember()

　　is_srvrolemember('sysadmin') 判断一个组或者角色的成员。

8，字符串函数

　　select left('a123456789bcd',2),right('a123456789bcd',4),substring('a123456789bcd',4,4),STUFF('abcdef123456789', 3, 3, 'ijklmn'),REPLICATE('0a', 4)

　　lower('FDGHERH'),reverse('ABCDEFGHIJK'),upper('abcdefghijk') , replicate()

9，系统函数

　　select cast('1234' AS int),convert(datetime,'20060201',112)   110 120 121

　　declare @i int
set @i=0
begin try
 select 10/@i

end try
begin catch
select line=ERROR_LINE(),
 [Message]=ERROR_MESSAGE(),
 errnumber=ERROR_NUMBER(),
 [procedure]=ERROR_PROCEDURE(),
 serverity=ERROR_SEVERITY(),
 [state]=ERROR_STATE()
end catch

系统函数：

@@identity　　　　　　　　当前连接上插入的最后一个标识符，包含触发器

ident_current('table1')      选择表的最后一个标识符

scope_identity()　　　　　 当前范围内最后一个标识符(不包含标识符)

10，系统统计函数

@@CPU_BUSY返回从SQL Server 上次启动以来花费的工作时间。

11，排名函数

三、设计和使用标量UDF

1，设计不访问表或者视图的UDF

create function States(@idState tinyint)

returns nvarchar(15)

as

begin

　　return ( case @idState when 0 then N'Stock Pending'

　　　　　　　　   when 1 then N'Prepared'

　　　　　　　　  when 2 then N'On truck'

　　　　　　　　  when 3 then N'In warehouse'

　　　　　　　　  when 4 then N'Finished'

　　　　　　  else N'unknown'

　　　　　　end)

　　end

go

select dbo.states(0),dbo.States(1),dbo.States(100)

这个函数States返回状态描述。

2，设计要访问表或者视图的UDF

还可以用UDF来访问表

create function MostRecentSaleByCustomer(@CustomerID int)

RETURNS INT

AS

begin

　　declare @SalesOrderId int

　　select top 1 @SalesOrderId=SalesOrderId

　　from Sales.SalesOrderHeader

　　where Customer=@CustomerID

　　order by OrderDate desc

return @SalesOrderId

end

go

select * from Sales.SalesOrderHeader where SalesOrderId=dbo.MostRecentSaleByCustomer(676)

UDF 无法被‘查询优化器’优化。所以不要用标量函数选取太多行。

四、设计和使用存储过程

1，使用输出参数

create proc ReturnAVowel (@WhatVowel tinyint,@Vowel char(1) output)

as

begin

　　select @Vowel= case @WhatVowel  when 1 then 'A'

　　　　　　　　　　　　　　　　　　　　when 2 then 'E'

　　　　　　　　　　　　　　　　　　　　when 3 then 'I'

　　　　　　　　　　　　　　　　　　　　when 4 then 'O'

　　　　　　　　　　　　　　　　　　　　when 5 then 'U'

　　　　　　　　　　　　　　　　　　　　else null

　　　　　　　　　　end

end

使用该存储过程：

declare @Vowel char(1)

EXEC ReturnAWowel 1,@Vowel output

select 'Vowel'=@Vowel

五、通过一个视图来封装查询

1，创建一个视图：

create view Sales.ProductSales

as

select ******

go

2，通过视图来更新数据

create view view1

as

select id,name from tabel1

go

update view1

set name='Jack' where id=34

go

3，分区视图

将不同的表放在不同的数据库实例中，可以创建一个视图来合并左右这些表，并通过INSTEAD of 触发器来管理对数据库的修改。为了查询一个远程实例的数据库，您应当在访问远程实例之前创建一个链接服务器。

下面将操作一个名为ISAN的链接服务器，它链接到另一个SQL Server 实例，后者存储了一个TestPartitionedViews数据库。

create database TestPartitionViews

go

use TestPartitionedViews

go

create table USA_Customers(CustomerID int,CustomerName varchar(200),Region varchar(20))

go

create view Customers

as

Select * from USA_Customers

union

select * from ISAN.TestPartiationedViews.dbo.EMEA_Customers

go

union 用于合并两个查询结果集得基本规则：

　　1，所有查询的列数和列的顺序必须相同。

　　2，数据类型必须兼容。

4，混合来自表和视图中的数据

select table1.a,table1.b,view1.c,view1.d from table1 inner join

5，在客户端应用程序中操作视图

访问视图，像表一样访问。

chapter 9 使用可编程对象来检索数据

用户自定义函数、存储过程、

1，从一个简单的问题开始

select ISNULL(AVG(TotalDue),0) as Amount

from Sales.SalesOrderHeader

where(CustomerID=23)

2，理解标量UDF

UDF必须是确定性的(deteministic).

create function GetAvgGust(@CustomerID int)

return money

as

begin

declare @ Amount Money

set @Amount=0

select @Amount=AVG(TotalDue)

from Sales.SalesOrderHeader

where (CustomerID=@CustomerID)

return ISNULL(@Amount ,0)

end

调用：PRINT dbo.GetAvgCust(23)

3，使用内联UDF 内联UDF没有begin 和end

内联UDF视为一个参数化的SELECT查询或者一个参数化的视图。

create function fun1

return table

as

select * from table1

where(table1.id=1)

4，使用表值UDF

表值(table-valued)UDF 是能返回一个表的UDF。由于在函数本身中定义了表的架构，所以返回的表不需要基于其他数据库表。

create function dbo.GetInterest(@NumPeriod int,@PercentInterest money)

returns @InterestTable Table

(

Num int,

I money

)

as

begin

　　declare @n int

　　set @N=0

　　declare @ITot=money

　　set @ITot=1

　　while @n<@NumPeriods

　　Begin

　　　　set @N=@N+1

　　　　set @ITot=@ITot*(1+(@PercentInterest/100))

　　　　insert into @InterestTable Values(@N,@ITot)

　　end

　　return

end

表值UDF的构建是在return子句之后定义。表值UDF是视图的一个很好的替代物，因为它接受参数，而且可以包含几个复杂的语句。

5，存储过程

create procedure DeleteCity(@City_ID int)

as

begin

　　delete from cities where city_ID=@City_ID

end

调用存储过程：

EXEC DeleteCity 1

9.3 CLR UDF 和过程

create function calcFact(@N int)

　　returns float

as

begin

　　declare @r float

　　set @R=1

　　declare @I int

　　set @I=1

　　while @I<=@N

　　begin

　　　　set @R=@R*@I

　　　　set @I=@I+1

　　end

　　return @R

end

chapter 10 修改数据

insert into table1 （dsf,dfe,fef,ew,gef) values('werwe','ewrew','werw')

如果所有的列都定义了默认值，可以用

Insert into table1 default values.