常用sql语句记录

1、表

--建表
if OBJECT_ID('Student') is not null
create table Student(
  ID int identity(1,1) not null,
  Name nvarchar(50),
  Code nvarchar(50),
  flag int default(0)
)

--建表时添加约束，表字段定义外键、由函数计算获得
CREATE TABLE [dbo].[A](
    [Id] [int] IDENTITY(1,1) NOT NULL primary key,
    [ClassId] [int] NOT NULL foreign key references [dbo].[Class] ([Id]) ,
    [Name] nvarchar(50),
    [InvoiceYear] int,
    [InvoiceMonth] int,
    [Date] AS  (dbo.ToDateFromPeriod(InvoiceYear,InvoiceMonth))PERSISTED,
    Memo nvarchar(50)  NULL
) 

--增加列
if not exists(select * from syscolumns where id=OBJECT_ID('Student') and name='ID')
alter table student add id int identity(1,1) not null

--添加主键约束
if OBJECT_ID('PK_Student') is not null
ALTER TABLE Student DROP CONSTRAINT [PK_ApprovalMatrix]
GO
ALTER TABLE Student  WITH CHECK ADD  CONSTRAINT [PK_ApprovalMatrix] PRIMARY KEY CLUSTERED (ID asc)
GO

--删除表
DELETE FROM [Student]

--清空标识值
DBCC  CHECKIDENT  (Student, RESEED,0)

--插入数据
IF NOT EXISTS(SELECT * FROM [Student] WHERE NAME='Win')
INSERT [Student] ([Code],[Memo]) VALUES (N'CA',N'Simon Deschenes')

--更新数据
UPDATE [dbo].[Student] SET Code = '001'  WHERE NAME='Win'

 

--insert into要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量
Insert into Table2(field1,field2,...) select value1,value2,... from Table1

--select into,要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
SELECT vale1, value2 into Table2 from Table1

  insert into and select into ：http://www.cnblogs.com/freshman0216/archive/2008/08/15/1268316.html

 

2、函数

 2.1 --自定义函数，根据年月返回日期

        USE TEST
        GO
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        Create function [dbo].[ToDateFromPeriod](@Year int, @Month int)
        returns datetime
        with SCHEMABINDING
        as
        -- Returns the first of the month for the specified year and month.
        begin
        return dateadd(mm,((@Year-1900)*12)+@Month-1,0)
        end

        --调用自定义函数
        select dbo.[ToDateFromPeriod]('2015','1') as result

2.2、表值函数

       2.2.1 直接返回表

       create function getp(@id int)
       returns table as
       return select * from [Project] where ClientId=@id

      2.2.2 返回自定义表

      create function getp()
      returns @t table (id int ,name varchar(50))
      as 
        begin
           insert into @t select p.Id,p.Name from Project p
           return 
        end

3、判断对象是否存在

3.1 判断用户是否存在，并创建

if USER_ID('fas') is null

CREATE USER [fas] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

其他相关判断参考http://www.cnblogs.com/fumj/archive/2012/07/15/2592558.html

3.2 判断自定义类型是否存在

if type_id('userid') is null 

 create type [dbo].[userid] from [nvarchar](150) null

4、给角色db_owner添加用户dds

EXEC sp_addrolemember 'db_owner', 'dds'

角色功能https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/

5、SET NOCOUNT ON  

使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息

如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

http://blog.csdn.net/thomas_chen/article/details/1660562

 6、查询数据库中所有表的名称,及数量

    select name from sysobjects where xtype='u'

    select count(0) from sysobjects where xtype='u'

 7、删除数据库中的所有表

     select 'delete from [' + name + ']' from sysobjects where xtype='u'

     执行上面sql，然后复制结果再次执行。