创建表
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' )BeginPrint '存在'End
判断唯一约束是否存在
If Exists(Select tab.name AS [表名],idx.name AS [约束名称],col.name AS [约束列名]From sys.indexes idxJoin 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')BeginPrint '存在'End
删除约束
Alter Table UserInfo Drop Constraint UserInfoName
删除列
Alter Table UserInfo Drop Column Name
判断表是否存在
If Exists (Select * From sysobjects Where name = 'UserInfo' And type = 'U')BeginPrint '存在'End
删除表
Drop Table UserInfo
删除数据
Delete from UserInfo Where Id = 1
设置查询结果全部大写或小写
select Lower(name) from UserInfoselect 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'))BEGINDrop Proc dbo.USP_SIS30_DataWarehouse_JobApplication_FullProfile_ConvertEND
