SQL Server - 小鸟系列之T-SQL基础篇

创建表

Create Table dbo.UserInfo
(
    Id int identity(1,1) primary key ,
    Name varchar(100) Constraint UQ_Name unique,--创建唯一约束
    CreatedTime DateTime,
    CreatedUser varchar(50)
)

修改表名

EXEC sp_rename 'UserInfo','UserInfo2'

修改列名

EXEC sp_rename 'UserInfo.Name','UserName','column'

修改列类型

Alter Table UserInfo Alter Column Name Char(100) 

新增列

Alter Table UserInfo Add EditTime varchar(100)

增加约束条件 (唯一值约束，约束名:CK_UserInfoName)

Alter Table UserInfo Add Constraint CK_UserInfoName Unique(Name)

判断列是否存在

If Exists(Select * From syscolumns Where id=object_id('UserInfo') and name='Name' )
Begin
  Print '存在'
End

判断唯一约束是否存在

If Exists(Select tab.name AS [表名],idx.name AS [约束名称],col.name AS [约束列名]
    From sys.indexes idx
	Join sys.index_columns idxCol On(idx.object_id = idxCol.object_id And idx.index_id = idxCol.index_id And idx.is_unique_constraint = 1)
	Join sys.tables tab On (idx.object_id = tab.object_id)
	Join sys.columns col On (idx.object_id = col.object_id And idxCol.column_id = col.column_id)
    Where tab.name = 'UserInfo' And idx.name = 'CK_UserInfoName')
Begin
  Print '存在'
End

删除约束

Alter Table UserInfo Drop Constraint UserInfoName 

删除列

Alter Table UserInfo Drop Column Name

判断表是否存在

If Exists (Select * From sysobjects Where name = 'UserInfo' And type = 'U')
Begin
  Print '存在'
End

删除表

Drop Table UserInfo

删除数据

Delete from UserInfo Where Id = 1

设置查询结果全部大写或小写

select Lower(name) from UserInfo
select Upper(name) from UserInfo

通配符"_"的使用 

_(下划线)：代表任意单个字符，例a_b代表以a开头且b结尾的长度为3的字符串

例：查询姓"欧阳" 且全名3个汉子的用户的姓名

select * from UserInfo where Name like '欧阳_'

存储过程使用

判断存储过程是否存在

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_SIS30_DataWarehouse_JobApplication_FullProfile_Convert]') AND type in (N'P', N'PC'))
BEGIN
  Drop Proc dbo.USP_SIS30_DataWarehouse_JobApplication_FullProfile_Convert
END