SqlServer总结

分享

一下都是自己写的，总结的，不是照搬网上的，只是分享，其中也有些小案例分享

创建数据库

create database schoolA
on primary
(
 name='MyschoolA',
 filename='D:\database\MyschoolA.mdf',
 size=3mb,
 maxsize=500mb,
 filegrowth=10%
),
(
  name='MyschoolA_2',
  filename='D:\database\MyschoolA_2.ndf',
  size=3mb,
  maxsize=500mb,
  filegrowth=10%
)
log on
(
   name ='MyschoolA_log',
   filename='D:\database\MyschoolA_log.ldf',
   size=3mb,
   maxsize=500mb,
   filegrowth=10%
)

约束：
 alter table Users add constraint uk_LoginID unique(loginID)唯一约束

分页

--pageSize:一页有多少条
--pageIndex:第几页
--totalCount:总共有多少条


CREATE PROC GetPageData
    @pageSize int,
    @pageIndex int,
    @totalCount int output
as
select * into #temp from 
(
    select * from T_Customer
) as T

declare @str nvarchar(500)
set @str=('select top('+cast(@pageSize as nvarchar(32))+') * from #Temp where Id not in (select top('+cast((@pageIndex-1)*@pageSize as nvarchar(32))+') Id FROM #Temp order by Id)order by Id')

exec (@str)
select @totalCount=COUNT(1) from #Temp;
drop table #Temp

declare @count int
exec GetPageData 4,2,@count


////////////////////////////////////
ALTER proc [dbo].[usp_UserInfo_LoadPageData]
@pageSize int,
@pageIndex int,
@totalCount int output
as

--select * from (
--    select ROW_NUMBER() over(order by userinfoid) num,* from UserInfo
--)as temp where temp.num>(@pageIndex-1)*@pageSize and temp.num<=@pageIndex*@pageSize


select top(@pageSize) * from UserInfo where UserInfoID not in
(select top(@pageSize*(@pageIndex-1))UserInfoID from UserInfo)

select @totalCount=COUNT(1) from UserInfo


------------------
正序和到序
SELECT * FROM (
select top 10 * from ztrqzc where 
 num>all(select top 20 num from ztrqzc  order by num )  
 order by num 
 ) temp ORDER BY temp.createdate desc

事务扩展

 begin transaction
declare @errorSum int 
set @errorSum=0
--查询tbResults表中所有Y2学生的考试成绩，保存到新表HistoreResult中
select tbResults.* into HistoreResult from tbResults join dbo.students on tbResults.stuID=students.stuID
inner join dbo.GradeInfo on dbo.GradeInfo.gID=students.stuGID
where GradeInfo.gName='Y2'
set @errorSum=@errorSum+@@ERROR
--删除tbResults表所有Y2学生的考试成绩
delete dbo.tbResults from  dbo.tbResults inner join students on  dbo.tbResults.stuID=students.stuID
inner join dbo.GradeInfo on  dbo.GradeInfo.gID=students.stuGID
where gName='Y2'
set @errorSum=@errorSum+@@ERROR
--删除dbo.students表所有Y2学生的记录，保存到新表HistoreResult中
--删除dbo.students表所有Y2学生的记录
--根据是否有错误，确认事务是提交还是撤销

bank案例 触发器事务等

Create Database Bank
on primary
(
name='bank',
filename='D:\database\bank.mdf',
size=3mb,
maxsize=500mb,
filegrowth=10%
)
log on
(
 name='bank_log',
 filename='D:\database\bank_log.ldf',
 size=3mb,
 maxsize=500mb,
 filegrowth=10%
)
use Bank
go
create table bank
(bankID  int identity(1,1) not null, 
 customerName char(10) not null,--顾客姓名
 currentMoney money not null --当前
)
alter table bank drop column  currentMoeny --删字段
alter table bank add currentMoney money not null--加字段
alter table bank add constraint CK_currentMoney check(currentMoney>=1) --检查约束
select * from bank
insert into bank(customerName,currentMoney)values('小小',1000)
insert into bank(customerName,currentMoney)values('dada',1)
---事务
begin transaction
declare @errorNum int
set @errorNum=0
update bank set currentMoney=currentMoney-200 where customerName='小小'
set @errorNum =@errorNum+@@ERROR
update bank set currentMoney=currentMoney+800 where customerName='dada'
set @errorNum =@errorNum+@@ERROR
if @errorNum<>0
begin
print '转账失败'
rollback --回滚
end
else
begin
print '转账成功'
commit --提交
print'转账之后的信息'
select * from bank
end
use master
select * from sysdatabases
exec sp_databases
--存储过程
use Bank
go
create proc getBank
@cateId int 
as 
begin
select * from bank where bankID=@cateId
end

go
alter proc getBank--修改
as 
begin
select * from dbo.bank
end
exec getBank 
use Bank
--存储过程分页
go
alter proc getBank
@pageSize int =1,
@pageindex int =5,
@rowCount int  output ,
@pageCount int output
as 
begin
select * from
(select ROW_NUMBER()over(order by bankID) as R, * from bank) as b
 where b.R>(@pageSize-1)*@pageindex and b.R <=@pageSize*@pageindex
select @rowCount=COUNT(*) from bank
set @pageCount=ceiling(convert(float,(@rowCount/@pageindex)))
end
declare @pi int, @ps int, @rc int, @pc int
set @pi=2  --保存页码
set @ps=5  -- 保存页容量(每页显示多少行)
set @rc=0  --总行数 
set @pc=0  --总页数
exec getBank @pi,@ps,@rc output,@pc output
select @pi 页码,@ps 页容量, @rc 总共行数,@pc 总页数

--触发器
insert into bank(customerName,currentMoney)values('小周',2600)
go
create Trigger chubank on bank
for insert
as 
begin
select * from bank
end
drop Trigger chubank  --alter
--视图 不同权限的人看到的不同的视图不一样，其实都是一个表中,当成虚拟表，就是查询语句
use Bank
go
alter  view vw_customer_currentMoney
as 
select 姓名=customerName,余额=currentMoney 
from bank where bankID=1

go
--查询视图
select * from vw_customer_currentMoney

---索引 index create [unique]唯一， 主键 ，[clustered]聚集 [nonclustered]非聚集
--drop index 表名.索引名 放在sysindexes,索引是建在没个表中的
create index 
index_bank on bank(bankID)
with fillfactor=30
select name from sysindexes where name ='index_bank'
exec sp_helpindex bank--存储过程中的一个系统存储过程

go 
exec sp_columns bank
exec sp_help bank
exec sp_helpconstraint bank

汽车案例创建信息表

 --汽车租赁系统
create database dbTraffics
on
(
 name='dbTraffics',
 filename='E:\sql\dbTraffics.mdf',
 size=3mb,
 maxsize=1024mb,
 filegrowth=10%
),
(
 name='dbTraffics_ndf',
 filename='E:\sql\dbTraffics_ndf.ndf',
 size=3mb,
 maxsize=1024mb,
 filegrowth=10%
)
log on
(
 name='dbTraffics_log',
 filename='E:\sql\dbTraffics.ldf',
 size=3mb,
 maxsize=1024mb,
 filegrowth=10%
)
go
use dbTraffics
go
--类型
create table tbType
(
  tID int primary key not null,
  tName nvarchar(50),
  Remarks nvarchar(200) null
)
--创建tbTraffics表保存汽车信息：
create table tbTraffics
(
 RID bigint,
 Number  nvarchar(50) not null primary key,
 Type int foreign key references tbType(tID),
 Name   nvarchar(20) not null,
 color nvarchar(20) not null,
 DailyRent float default(0.0),--日租金
 YearsOfservice int default(12),--使用年限
 Load nvarchar(20)
)
go

项目里存储过程分享

 USE [GSSMS]
GO
/****** Object:  StoredProcedure [dbo].[getprdouceStudents]    Script Date: 03/17/2013 16:48:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[getprdouceStudents]
@pageSize int =5,--每页行数,页的容量
@pageindex int =1, --页的索引
@rowCount int output, --总行数（传出参数）
@pageCount int output --总页数（传出参数）
as
begin
select * from
(select ROW_NUMBER()over(order by SID) as 序号,* from dbo.Students)as b where
b.序号>(@pageindex-1)*@pageSize and b.序号<=@pageindex*@pageSize
select @rowCount=COUNT(*) from dbo.Students
set @pageCount=CEILING(Convert(float,@rowCount)/Convert(float,@pageSize))
end
declare @ps int,@pi int,@rc int,@pc int
set @ps=5 --保存页面行数
set @pi=1 --保存页面索引
set @rc=0 --总行数
set @pc=0 --总页面
exec getprdouceStudents @ps,@pi,@rc output,@pc output
select @ps 页面数,@pi 页面行数,@rc 总共行数,@pc 总页面

金额自增

 select * from Books 
update Books set UnitPrice=88
go
declare @position int,@price int;
    set @position=17
    set @price=90
while(1=1)
begin
    update Books set UnitPrice=@price where Id=@position
    select @position=@position+1
    select @price=@price+2
    if(@position>35)
    begin
    break; 
    end

end

代码分享myTestBase

 use myTestBase
go
select * from dbo.GradeInfo
select * from dbo.students
select * from dbo.tbCourses
select * from dbo.tbResults
----case .when
select tbResults.stuID, students.stuName, tRscore 分数,成绩=case 
                 when tRscore between 90 and 100 then 'A'
                 when  tRscore between 80 and 90 then 'B'
                 when  tRscore between 70 and 80 then 'C'
                 when  tRscore between 60 and 70 then 'D'
                 else 'E'
                 end
 from tbResults,students where tbResults.stuID=students.stuID
 go
 ---if -else print
--select *  from tbResults
 declare @score int 
 select @score=avg(tRscore) from tbResults
 if(@score>70)
 begin
 print  '优秀' +convert(varchar(max),@score)
 select top 3 * from tbResults order by tRscore asc
 end
 else
 begin
 print'一般'
 select top 3 * from tbResults order by tRscore desc
 end

 go
---while break  方法一
Declare @n int 
while(1=1)
begin
select @n=COUNT(*) from tbResults r where r.tRscore <81
if (@n>0)
update tbResults set tRscore=tRscore+2 where tRscore<98
else 
break
end
print '加分后成绩如下'
select * from tbResults
--- 方法二
while(exists(select * from tbResults r where r.tRscore <83))
begin
update tbResults set tRscore=tRscore+2 where tRscore<98
end
select * from tbResults
---内查询 join
select s.stuName 姓名,TR.tRscore 成绩,TC.tCName from students as s 
join tbResults as TR on (s.stuID=TR.stuID) 
join dbo.tbCourses as TC on(TR.tCID=TC.tCID)
--where综合查询
select s.stuName 姓名,TR.tRscore 成绩,TC.tCName from students as s,tbResults as TR,tbCourses as TC where s.stuID=TR.stuID and TR.tCID=TC.tCID
--左连接,左边全部要显示,右边没有的为null
select * from tbCourses as c left join tbResults as r on c.tCID=r.tCID 
--右连接,右边全部要显示
select * from tbCourses as c right join tbResults as r on c.tCID=r.tCID 
--in，not in
select * from tbCourses where tCName in('程序逻辑','C#基础')
--link
select * from tbCourses where tCName like '[^C#]%'--不包含
select * from tbCourses where tCName like 'C#%'--包含
print '服务器'+@@SERVERNAME
print 'SQL的版本:'+ @@VERSION
select s.stuName,c.tCID,r.tRtime,r.tRscore,c.tCName from students as s,tbCourses as c,tbResults as r where s.stuID=r.stuID and c.tCID=r.tCID and r.tRtime='2012-12-24'
--子查询

select * from students
select * from tbCourses
select * from tbResults
go
insert into students(stuName,stuSex,stuCardID,stuGID,BornDate) values('小肖','女','420108989099999999',1,1992-06-09)
declare @birthday datetime
select @birthday=BornDate from students  where stuName='张三'
select * from students where BornDate>@birthday
select * from students where BornDate>(select BornDate from students  where stuName='张三')
--索引
create index index_students on students(stuID)
with fillfactor=30
select name from sysindexes where name='index_students_students_stuID'
drop index students.index_students

create unique index index_students_students_stuID on students(stuID)
with fillfactor=30

select * from  students with (index=index_students_students_stuID)
where stuID=3
exec sp_helpindex students --存储过程中的一个系统存储过程
--存储过程 编程基础没有达到90分名单
go
alter proc my_getResult
@ucnt int output
as 
begin
select R.tRscore,R.tCID,R.stuID,s.stuName,c.tCName from dbo.tbResults as R,dbo.students as s,tbCourses as c where R.stuID=s.stuID  and R.tRscore<90 and c.tCID=R.tCID
select @ucnt=count(R.tRscore) from dbo.tbResults as R,dbo.students as s,tbCourses as c where R.stuID=s.stuID  and R.tRscore<90 and c.tCID=R.tCID
end
declare @ucn int
set  @ucn=0
exec my_getResult @ucn output
select @ucn 没到90分人数
/////////////
use master
go
--创建数据库
if exists
(select * from sysdatabases where name='myTestBase')--所有数据库
begin
drop database myTestBase
end
create database myTestBase
on
(
  name='myTestBase',
  filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\myTestBase.mdf',
  size=5mb,
  maxsize=100mb,
  filegrowth=10%
)
log on
(
  name='myTestBase_log',
  filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\myTestBase_log.ldf',
  size=5mb,
  maxsize=100mb,
  filegrowth=10%
)
use myTestBase
if exists(select * from sysobjects where name='students' )
begin
drop table students --删除
end 
go--创建表
create table students 
(
  stuID int identity(1,1) not null primary key, --标识列，设置主键
  stuName nvarchar(50) not null,
  stuSex char(2) default('男'),
  stuCardID char(18) null check(len(stuCardID)=18),--约束
  stuGID int null
)

go
 create table GradeInfo
 (
   gID int  identity(1,1) not null primary key, --标识列，设置主键
   gName nvarchar(50) not null
 )
 go
 --加外键约束
 alter table students 
 add constraint FK_students_stuGID_GradeInfo
 foreign key(stuID) references GradeInfo(gID)
 go 
 --删约束 alter table 表名 drop constraint 约束名                            
select * from students,GradeInfo
---插入数据
insert into GradeInfo(gName) values('一年级')
insert into GradeInfo(gName) values('二年级')
insert into GradeInfo(gName) values('三年级')
insert into students(stuName,stuCardID,stuGID) values('张三','430111199909092075',1)
create table tbCourses --课程信息表
(
  tCID int identity(1,1), tCName nvarchar(30), tCClass int, gID int, tCRmark nvarchar(max)  
)
create table tbResults --考试成绩表
(
  tRID int ,tCID int, stuID int ,tRtime date, tRscore int ,tRRmark nvarchar(max)
)

select * from students --学生表
select * from GradeInfo --年纪表
select * from tbCourses
select * from tbResults
truncate table tbCourses --清除数据、还原标识列
insert into tbCourses(tCName,tCClass,gID,tCRmark) values('程序逻辑',30,1,'课程')
insert into tbCourses(tCName,tCClass,gID,tCRmark) values('编程基础',30,1,'课程')
insert into tbCourses(tCName,tCClass,gID,tCRmark) values('面向对象设计',30,1,'课程')
insert into tbCourses(tCName,tCClass,gID,tCRmark) values('C#基础',123,1,'课程')
insert into tbCourses(tCName,tCClass,gID,tCRmark) values('C#基础',235,1,'课程')
insert into tbResults(tCID,stuID,tRtime,tRscore,tRRmark) values(1,1,'2012-12-24',88,'语文')
insert into students(stuName,stuSex,stuCardID,stuGID) values('小小','女','4201023001230120',2)
select * from tbCourses where tCClass Like'3%'
select * from tbCourses where tCClass Like'_3%' 
delete  from tbCourses where tCID=7--删 
select * from tbCourses order by tCID ASC

use master
go 
select * from sysdatabases  --查询所有数据库
use myTestBase
go
select * from sys.objects where name='students'
create database schoolA --创建数据库
on primary
(
 name='MyschoolA',
 filename='D:\database\MyschoolA.mdf',
 size=3mb,
 maxsize=500mb,
 filegrowth=10%
),
(
  name='MyschoolA_2',
  filename='D:\database\MyschoolA_2.ndf',
  size=3mb,
  maxsize=500mb,
  filegrowth=10%
)
log on
(
   name ='MyschoolA_log',
   filename='D:\database\MyschoolA_log.ldf',
   size=3mb,
   maxsize=500mb,
   filegrowth=10%
)
--alter table tastduents 删除表示列
--drop colum ssid
--go
---在加进去
-- alter table tastduents 
-- ADD ssid int identity(1,1) not null  primary key
-- go
---内连接
---- select Book.* from Book inner jion Catefory on Book.id= Catefory.id
---联合查询
---select Book.* from Book,Catrfory where Book.id=catefory.id
-- case-when
select tCClass ,
case tCClass
when 30 then '正常课时'
else '正常+课时'
end
from tbCourses

自定义排序

  SELECT InforSource FROM dbo.viewShopAllInfo 
  ORDER BY CHARINDEX(InforSource,'1,0,2,3');

Split函数

 CREATE FUNCTION dbo.SplitString
(
          @Expression NVARCHAR(4000), --要拆分的字符串
          @Delimiter NVARCHAR(100), --拆分符号
          @n INT --要得到已拆分的返回字符串位置
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @p INT
SET @p = CharIndex(@Delimiter,@Expression)  --获得第一个拆分符号所在的首位置
IF @p > 0
          BEGIN
          SET @p = @p + Len(@Delimiter) - 1
          END
DECLARE @i INT
SET @i = 1
WHILE @i < @n
          BEGIN
          SET @i = @i + 1
          SET @Expression = SubString(@Expression, @p + 1,Len(@Expression) - @p )
          SET @p = CharIndex(@Delimiter,@Expression)
          IF @p > 0
                    BEGIN
                    SET @p = @p + Len(@Delimiter) - 1
                    END
          ELSE
                    BEGIN
                    BREAK
                    END
END
DECLARE @s NVARCHAR(1000)
IF @p = 0 AND @i = @n
          BEGIN
          SET @s = @Expression
          END
ELSE
          IF @i = @n
          BEGIN
          SET @s = SubString(@Expression, 1,@p - Len(@Delimiter))
          END
RETURN @s
END

substring用法

 SELECT keywords,
substring(
substring(keywords, CHARINDEX('$',keywords,CONVERT(INT,CHARINDEX ('$',keywords))+1)+1,100), 
 CHARINDEX('$',keywords,CONVERT(INT,CHARINDEX ('$',keywords))),100
)
FROM dbo.house_searchs

增加字段

 --修改二手房查询的表结构
ALTER TABLE house_searchs ADD sortdate datetime DEFAULT getdate()


update house_searchs 
set sortdate = convert(datetime,left(cast(publicdate as varchar),4) + '-' + substring(cast(publicdate as varchar),5,2) +'-' + right(cast(publicdate as varchar),2) + ' 00:00:00') 

分组按照时间汇总

 ---以部门,时间汇总 时间字段OIS_Date
--这是取到所有的总和,我想要时间之间的总和
select DeptID,
    in_price =sum(CASE OIS_Type when '4'  then OIS_Price*OIS_Count END),
    out_price = sum(CASE OIS_Type when '7'  then OIS_Price*OIS_Count END)
from Oper_OutInStockStream
WHERE  (OIS_Date>='2015-01-27 00:00:01'
 AND OIS_Date<='2015-05-01 23:59:59')
group by  DeptID

更新加行号

 UPDATE His_Cart SET IsShopping='0',DETAIL_ID=temp.rid
FROM His_Cart,
(
SELECT ROW_NUMBER() OVER (ORDER BY id) rid,id FROM dbo.His_Cart
WHERE CreatedBy='jzt') temp WHERE His_Cart.ID=temp.id

根据时间查询

select * from info where DateDiff(dd,datetime,getdate())=0 

--查询24小时内的: 
select * from info where DateDiff(hh,datetime,getDate())<=24 

--info为表名,datetime为数据库中的字段值

--查询当天：
select * from info where DateDiff(dd,datetime,getdate())=0

--查询24小时内的:
select * from info where DateDiff(hh,datetime,getDate())<=24

--info为表名,datetime为数据库中的字段值

Sql代码
--查询当天记录另类的方法 
SELECT * 
FROM j_GradeShop 
WHERE (GAddTime BETWEEN CONVERT(datetime, LEFT(GETDATE(), 10) + ’ 00:00:00.000’) 
AND CONVERT(datetime, LEFT(GETDATE(), 10) + ’ 00:00:00.000’) + 1) 
ORDER BY GAddTime DESC

--查询当天记录另类的方法
SELECT *
FROM j_GradeShop
WHERE (GAddTime BETWEEN CONVERT(datetime, LEFT(GETDATE(), 10) + ’ 00:00:00.000’)
AND CONVERT(datetime, LEFT(GETDATE(), 10) + ’ 00:00:00.000’) + 1)
ORDER BY GAddTime DESC

DATEDIFF 函数:

语法:


DATEDIFF ( datepart , startdate , enddate )

备注:enddate 减去 startdate。如果 startdate 晚于 enddate，则返回负值。

如果结果超出整数值范围，则 DATEDIFF 将产生错误。对于毫秒，最大数是 24 天 20 小时 31 分钟零 23.647 秒。对于秒，最大数是 68 年。

跨分钟、秒和毫秒等边界计算的方法使得 DATEDIFF 指定的结果在所有数据类型中均一致。结果是带正负号的整数值，它等于跨第一个和第二个日期间的 datepart 边界数。例如，在 1 月 4 日（星期日）和 1 月 11 日（星期日）之间的星期数是 1。


可以再MSSQL中测试:

Sql代码
--两个时间差刚好是24 
--打印的方式 
print dateDiff(hh,’2009-1-1 0:0:0’,’2009-1-2 0:0:0’) 

--查询的方式 
print dateDiff(hh,’2009-1-1 0:0:0’,’2009-1-2 0:0:0’)

--两个时间差刚好是24
--打印的方式
print dateDiff(hh,’2009-1-1 0:0:0’,’2009-1-2 0:0:0’)

--查询的方式
print dateDiff(hh,’2009-1-1 0:0:0’,’2009-1-2 0:0:0’)

Sql代码
--本月记录 
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0 

--本周记录 
SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0 

--包括本年这些查询方式是一样的

--本月记录
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0

--本周记录
SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0

sql server中的时间函数


1. 当前系统日期、时间
select getdate()


2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd(day,2,’2004-10-15’) --返回：2004-10-17 00:00:00.000


3. datediff 返回跨两个指定日期的日期和时间边界数。
select datediff(day,’2004-09-01’,’2004-09-18’) --返回：17


4. datepart 返回代表指定日期的指定日期部分的整数。
SELECT DATEPART(month, ’2004-10-15’) --返回 10


5. datename 返回代表指定日期的指定日期部分的字符串
SELECT datename(weekday, ’2004-10-15’) --返回：星期五


6. day(), month(),year() --可以与datepart对照一下

select 当前日期=convert(varchar(10),getdate(),120)
,当前时间=convert(varchar(8),getdate(),114)

select datename(dw,’2004-10-15’)

select 本年第多少周=datename(week,’2004-10-15’)
,今天是周几=datename(weekday,’2004-10-15’)


函数 参数/功能
GetDate( ) 返回系统目前的日期与时间
DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期
DatePart (interval,date) 返回日期date中，interval指定部分所对应的整数值
DateName (interval,date) 返回日期date中，interval指定部分所对应的字符串名称

参数 interval的设定值如下：


值 缩 写（Sql Server） Access 和 ASP 说明
Year Yy yyyy 年 1753 ~ 9999
Quarter Qq q 季 1 ~ 4
Month Mm m 月1 ~ 12
Day of year Dy y 一年的日数,一年中的第几日 1-366
Day Dd d 日，1-31
Weekday Dw w 一周的日数，一周中的第几日 1-7
Week Wk ww 周，一年中的第几周 0 ~ 51
Hour Hh h 时0 ~ 23
Minute Mi n 分钟0 ~ 59
Second Ss s 秒 0 ~ 59
Millisecond Ms - 毫秒 0 ~ 999



access 和 asp 中用date()和now()取得系统日期时间；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，这些函数的用法也类似


举例：
1.GetDate() 用于sql server :select GetDate()

2.DateDiff(’s’,’2005-07-20’,’2005-7-25 22:56:32’)返回值为 514592 秒
DateDiff(’d’,’2005-07-20’,’2005-7-25 22:56:32’)返回值为 5 天

3.DatePart(’w’,’2005-7-25 22:56:32’)返回值为 2 即星期一(周日为1，周六为7)
DatePart(’d’,’2005-7-25 22:56:32’)返回值为 25即25号
DatePart(’y’,’2005-7-25 22:56:32’)返回值为 206即这一年中第206天
DatePart(’yyyy’,’2005-7-25 22:56:32’)返回值为 2005即2005年

表横向展示

 SELECT PDNAME as 设备类别,
  max(case ftName when '临床功能' then score else 0 end) 临床功能,
  max(case ftName when '有形风险' then score else 0 end) 有形风险,
  max(case ftName when '问题避免概率' then score else 0 end) 问题避免概率, 
  max(case ftName when '事故历史' then score else 0 end) 事故历史,
  max(case ftName when '管理部门的特殊要求' then score else 0 end) 管理部门的特殊要求
  FROM
(
SELECT  fs.PDCode ,
        fdt.PDNAME PDNAME ,
        ft.Name ftName ,
        SUM(CONVERT(INT, fps.score)) score
FROM    FixeaAssetsPMConS fs
        LEFT JOIN FixeaAssetsPMScore fps ON fps.id = fs.PMScoreID
        LEFT JOIN dbo.FixeaAssetsPMScoreType ft ON ft.ID = fps.NameTypeID
        LEFT JOIN FixeaPreventiveDeviceType fdt ON fdt.PDCode = fs.PDCode
GROUP BY ft.Name ,
        fs.PDCode ,
        fdt.PDNAME
HAVING fs.PDCode !=''
) tb
group by PDNAME     

无联系2个表拼接

 SELECT  BuyDate,BaseID,sum(IsBuycount) IsBuycount,sum(IsNollBuycount) IsNollBuycount,sum(HousePartner) HousePartner FROM (
SELECT * FROM 
--购买者
(select
 convert(varchar(10),UpdateDate,120) BuyDate,BaseID, count(*) IsBuycount,'' AS IsNollBuycount,'' AS HousePartner
from 
 dbo.RecCustomer WHERE IsBuy=1  
group by 
 convert(varchar(10),UpdateDate,120),BaseID) a 
 UNION 
 SELECT * FROM 
 --没购买
 (select
 convert(varchar(10),UpdateDate,120) BuyDate,BaseID,'' AS IsBuycount , count(*) IsNollBuycount,'' AS HousePartner
from 
 dbo.RecCustomer WHERE IsBuy=0
group by 
 convert(varchar(10),UpdateDate,120),BaseID) b 
  UNION 
  --合伙人
 SELECT * FROM 
( select
 convert(varchar(10),CreateDate,120) BuyDate,BaseID, '' AS IsBuycount , '' AS IsNollBuycount,count(*) HousePartner
from 
 dbo.Partner_CommsionHouse WHERE IsDeleted=0
group by 
 convert(varchar(10),CreateDate,120),BaseID) c
 ) temp WHERE temp.BaseID='A6F4C7A7-D308-4DA6-BD0F-B1F50E27783D' AND temp.BuyDate BETWEEN '2014-10-01' and '2014-10-21'
 GROUP BY temp.BuyDate,temp.BaseID

有联系2个表拼接

SELECT * FROM (
 SELECT a.BaseID aBaseID,b.BaseID bBaseID,c.BaseID cBaseID, ISNULL(BuyDate,ISNULL(NullBuyDate,Date)) BuyDate,ISNULL(Date,ISNULL(BuyDate,NullBuyDate)) Date,ISNULL(NullBuyDate,ISNULL(BuyDate,Date)) NullBuyDate,
 ISNULL(IsBuycount,0) IsBuycount, ISNULL(IsNollBuycount,0) IsNollBuycount,
 ISNULL(HousePartner,0) HousePartner FROM 
( select
 convert(varchar(10),UpdateDate,120) BuyDate,BaseID, count(*) IsBuycount
from 
 dbo.RecCustomer WHERE IsBuy=1  
group by 
 convert(varchar(10),UpdateDate,120),BaseID) a
 RIGHT join
 (select
 convert(varchar(10),UpdateDate,120) NullBuyDate,BaseID, count(*) IsNollBuycount
from 
 dbo.RecCustomer WHERE IsBuy=0
group by 
 convert(varchar(10),UpdateDate,120),BaseID) b
  ON  a.BuyDate=b.NullBuyDate
 LEFT JOIN
 (select
 convert(varchar(10),CreateDate,120) Date,BaseID, count(*) HousePartner
from 
 dbo.Partner_CommsionHouse WHERE IsDeleted=0
group by 
 convert(varchar(10),CreateDate,120),BaseID) c
ON b.NullBuyDate=c.Date
) temp 
WHERE 
temp.aBaseID=temp.bBaseID
 AND  temp.aBaseID='A6F4C7A7-D308-4DA6-BD0F-B1F50E27783D'
AND temp.BuyDate BETWEEN   '2014-10-16'    AND  '2014-10-22'

2个数据库表互相导数据

服务器数据库之间互相导入数据
insert into [HPD2.0].dbo.SysMenu
 select * from [10.3.1.27].[HPD2.0].dbo.SysMenu

要是找不到服务器
--添加服务器
EXEC sp_addlinkedserver
@server='10.3.1.27',--被访问的服务器别名（习惯上直接使用目标服务器IP，或取个别名如：JOY）
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='10.3.1.27' --要访问的服务器
--设置账号和密码

EXEC sp_addlinkedsrvlogin
'10.3.1.27', 
'false',
NULL,
'sa', --帐号
'JZTeya0!' --密码


insert into [shenzhengHPD2.0].dbo.BaseSupplierInfo
 select * from [qingdaoHPD2.0].dbo.BaseSupplierInfo

/////////////////////////
列名不一定要相同，只要你在HH中列出要插入列的列表跟select from mm表中的选择的列的列表一一对应就可以了，当然两边的数据类型应该是兼容的。 
insert into hh (fielda,fieldb,fieldc) select fieldx,fieldy,fieldz from mm 
复制代码 代码如下:

---更新计量点中不存在的数据,将台帐中的信息转移到计量点中 
insert into MetricPoints (MeterID,MetricPointName,[Description],DepartmentID,MediumID) 
(SELECT m.MeterID,m.MetricItems+m.InstallPlace as m_MetricPointName,m.MetricItems,m.DepartmentID,m.MediumID 
FROM Meters m WHERE NOT EXISTS (SELECT 1 FROM MetricPoints WHERE MetricPoints.MeterID = m.MeterID ) and m.MediumID =2) 

声名：a,b ,都是表 
复制代码 代码如下:

--b表存在（两表结构一样） 
insert into b select * from a 

若两表只是有部分（字段）相同，则 
复制代码 代码如下:

insert into b(col1,col2,col3,col4,...) select col1,col2,col3,col4,... from a where... 

把表a插入到表b中去。 
复制代码 代码如下:

--b表不存在 
select * into b from a 
// 
select (字段1，字段2，...) into b from a 

页总数

 SELECT  CEILING((COUNT(*) *2+0.0)/10) TotalPageCount,COUNT(*) TotalCount
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY customer DESC ) AS rowIndex ,
                    SUM(OutStockCount * Price) totalPrice ,
                    SUM(OutStockCount) totalStock ,
                    COUNT(GoodsName) GoodsNumber ,
                    COUNT(VoucherCode) VoucherNumber ,
                    customer,OrderWay,
                    Owner OwnerID
          FROM      dbo.V_getOutStockVoucher
          WHERE     Status = '已复核' 
          GROUP BY  customer ,
                    Owner,OrderWay
        ) temp

查询重复的数据

select id, name, memo from A where id in (select id from A group by id having count(1) >= 2)

去除重复的数据

Distinct 或select * from table1 as a  where not exists(select 1 from table1 where logID=a.LogID and ID>a.ID)   
select Distinct 列名 from 表名

检查重复数据情况

SELECT Userid, Res_ID FROM ahuser_app_rec GROUP BY Userid, Res_ID HAVING (COUNT(*) > 1)