SQL Server 常用语句

查看数据库中的表信息

select 
    table_name=
    (
    case when t_c.column_id=1 
        then t_o.name 
        else '' 
    end
    ),
    column_id=t_c.column_id,
    column_name=t_c.name,
    type=t.name,
    max_length=t_c.max_length,
    precision=isnull(t_c.precision,0),
    scale=isnull(t_c.scale,0),
    is_identity=case when t_c.is_identity=1 then '√' else '' end,    
    is_primary=
    (
        case when exists
        (
            select 1 from sys.indexes i,sys.index_columns ic,sys.objects o
                where o.type='PK' and o.name=i.name and i.index_id=ic.index_id 
                    and i.object_id=ic.object_id and ic.column_id=t_c.column_id 
                    and o.parent_object_id=t_c.object_id
        )
        then '√'
        else ''
        end
    ),
    is_nullable=case when t_c.is_nullable=1 then '√' else '' end,
    default_value=isnull(c.definition,''),
    description=isnull(e.value,''),
    fk_column_name=isnull(f_c.name,''),
    fk_table_name=isnull(f_o.name,'')
from sys.columns t_c
    inner join sys.objects t_o on t_c.object_id=t_o.object_id        
    left join sys.types t on t.system_type_id=t_c.system_type_id 
        and t.user_type_id=t_c.user_type_id
    left join sys.default_constraints c on c.object_id=t_c.default_object_id 
        and c.parent_object_id=t_c.object_id and c.parent_column_id=t_c.column_id
    left join sys.extended_properties e on e.major_id=t_c.object_id 
        and e.minor_id=t_c.column_id    
    left join 
    (
        select parent_object_id,referenced_object_id,column_id=min(key_index_id) from sys.foreign_keys
            group by parent_object_id,referenced_object_id
    )f on f.parent_object_id=t_c.object_id and f.column_id=t_c.column_id  
    left join sys.columns f_c on f_c.object_id=f.referenced_object_id and f_c.column_id=f.column_id
    left join sys.objects f_o on f_o.object_id=f.referenced_object_id
where t_o.type='U' and t_o.name<>'sysdiagrams'
    order by t_o.name,t_c.column_id ;

创建/删除数据库

if object_id(N'mytest') is not null
drop DATABASE mytest;
GO
create DATABASE mytest ;
go

创建数据库，指定物理文件

Create Database dbtang1 on
(Name=tang, Filename='D:Microsoft SQL Server02MSSQL.2MSSQLData	ang.mdf')

查看数据库

use mytest;

select name, size, size*1.0/128 AS [Size in MBs]  from sys.master_files where name='mytest';

创建表

create table t_user(id int primary key, name varchar(100));

修改表

alter table t_user add state int;

注：此处不可以用表的别名。

插入数据

use mytest
go
declare @i int;
set @i = 1;
while @i < 100
begin
  insert into dbo.t_user (id,name) values(@i,'name_' + convert(varchar(20),@i));
  set @i = @i + 1;
end;

插入数据可以单个记录插入，也可以批量插入。

操作表(增、删、改)

select * from t_user ;

delete from t_user ;

update t_user  set state = 1 ;

从已存在表中复制数据

select * into t_user_bak from t_user a where a.id<51;

添加时间过滤条件

SELECT *
  FROM CardInfo a where a.cardtype=11
and CONVERT(varchar(100), a.begindate, 112) > '201211'
and CONVERT(varchar(100), a.begindate, 112) < '201304'  ;

修改表字段属性

alter table [dbo].[cardinfo] alter column remark [varchar] (1000)

查看表结构

select * from information_schema.columns where table_name='ControlRoomInfo' ;

exec sp_columns 'ControlRoomInfo' ;

exec sp_help 'ControlRoomInfo' ;

分组统计

select CONVERT(varchar(100), sendcardtime, 112)card_date, count(1) num from cardinfo a, GuestInfo b
where a.cardid = b.cardid 
group by CONVERT(varchar(100), sendcardtime, 112) ;

使用 LTRIM() 函数可以去除查询结果中左边的空格，RTRIM() 函数可以去除查询结果中右边的空格，那么嵌套这两个函数即可去除查询结果中两边的空格了。

SELECT LTRIM(RTRIM(字段名)) FROM 表名

获取英文月份

select DATENAME(mm,getdate())

select DATENAME(month,getdate())

获取年月数字

Select CONVERT(varchar(6), GETDATE(), 112)

获取字符或者字符串在另一个字符串中的起始位置

select charindex(' ', b.remark) from dbo.CardInfo b

查询每日的发卡数量（以房间、通道、门禁等为单位），汇总每日房卡报表

select  ROW_NUMBER() OVER (ORDER BY CONVERT(varchar(100), sendcardtime, 112) desc) id,CONVERT(varchar(100), e.sendcardtime, 112) mth, typename, count(1) num from (
select d.*, b.locktype, c.typename 
from (
select a.*, substring(a.remark, 1, (select charindex(' ', b.remark) from dbo.CardInfo b where b.cardid=a.cardid ))roomname from dbo.CardInfo a 
where a.remark<>''
) d ,
roominfo b, locktypeinfo c 
where d.roomname=b.roomname and b.locktype=c.locktype
and d.sendcardtime>='2014-10-25' and d.sendcardtime<='2014-12-01'
) e
group by CONVERT(varchar(100), sendcardtime, 112), locktype, typename  
order by CONVERT(varchar(100), sendcardtime, 112) desc ;

生成月总发卡数量报表

select CONVERT(varchar(6), e.sendcardtime, 112) mth, typename, count(1) num from (
select d.*, b.locktype, c.typename 
from (
select a.*, substring(a.remark, 1, (select charindex(' ', b.remark) from dbo.CardInfo b where b.cardid=a.cardid ))roomname from dbo.CardInfo a 
where a.remark<>''
) d ,
roominfo b, locktypeinfo c 
where d.roomname=b.roomname and b.locktype=c.locktype
) e
group by CONVERT(varchar(6), sendcardtime, 112), locktype, typename 
order by CONVERT(varchar(6), sendcardtime, 112) desc  ;

查询前 n 条记录

SELECT  top 3 * FROM SystemVariables

with as 语句

WITH 
RoomsWithCustomEvents AS 
(
    SELECT RoomID, case when COUNT('1') = 0 then 0 else 1 end AS Events FROM RecordConfig GROUP BY RoomID
), 
RoomsWithAlarms AS 
(
    SELECT RoomID, case when COUNT('1') = 0 then 0 else 1 end AS Alarms FROM RecordConfig R Inner Join RecordTypes RT on R.RecordTypeId = RT.RecordTypeId Where RT.Alarm = 1 GROUP BY R.RoomID
) 
SELECT  a.*, b.TypeName,RC.Events AS HasEvent, RA.Alarms AS HasAlarm FROM 
RoomInfo AS a 
inner join RoomTypeInfo b on a.RoomType = b.RoomType 
LEFT OUTER JOIN RoomsWithCustomEvents AS RC ON a.RoomID = RC.RoomID 
Left Outer join RoomsWithAlarms RA on a.RoomID = RA.RoomID 
WHERE a.roomstatus=0 And a.Building=1 And a.Floor=1 ;

查询 SQL SERVER 2005 的版本

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

或者

SELECT @@VERSION ;

事务

USE tempdb
GO
BEGIN TRAN --标记事务开始
INSERT INTO Table2 Values(1)
Waitfor delay '00:00:10' --表时等待10秒后再执行后面的代码
Select * from Table2
COMMIT TRAN --提交事务
Rollback TRAN --回滚事务

表dept

id　　name

表person

id name did score

统计各部门的员工分数<60, 60-80, >80的人数

select t.name,
(select case when low is null then 0 else low end from(
select a.did, COUNT(1) as low
from t_person a
where a.score < 60
group by did
)b where id=b.did)low,
(select case when mid is null then 0 else mid end from (
select a.did, COUNT(1) as mid
from t_person a
where a.score >= 60 and a.score <= 80
group by did
)c where id=c.did ) mid,
(select high from(
select a.did, COUNT(1) as high
from t_person a
where a.score > 80
group by did
) d where id=d.did)high
from t_dept t