sql server 常用语句

--新增字段
use [MEASDatabase]
go
if not exists(select * from syscolumns where id=object_id('DictCheckItems') and name='OfDepartName') begin
ALTER TABLE DictCheckItems ADD OfDepartName VARCHAR(50) default '';
end
go

--修改字段长度
alter table DictCheckItems alter column OfDepartName varchar(60)

 --新增表结构
IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = object_id('TableInfo')
AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
CREATE TABLE [dbo].TableInfo(
[ID] [varchar](50) NOT NULL,
[PARAMTYPE] [varchar](50) NOT NULL,
[PARAMNAME] [varchar](100) NOT NULL,
PARAMITEMS [varchar](500) not NULL
CONSTRAINT [PK_DICT_PARAM] PRIMARY KEY CLUSTERED
([ID] ASC
)ON [PRIMARY]
)

GO

--删除字段
alter table 表名 drop column 字段名;

--修改字段名称
exec sp_rename 'UserMEAS.PermissionID' , 'RoleCode', 'column'
eg:

if exists(select * from syscolumns where id=object_id('UserMEAS') and name='PermissionID') begin
exec sp_rename 'UserMEAS.PermissionID' , 'RoleCode', 'column'
end
go

if exists(select * from syscolumns where id=object_id('UserMEAS') and name='RoleId') begin
alter table UserMEAS drop column [RoleId];
end
go

--判断是否存在某条数据
if not exists (select * from [DictPublic] where Type='默认密码')
insert into DictPublic
(Id, Type, TypeCode, TypeName, TypeLevel, IsEnable, Remark, PTypeCode)
values(NEWID(),'默认密码','PassWord','666666','1','1','用户默认密码','')
go

--新增视图
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id(N'[VIEW_USERINFO]') AND OBJECTPROPERTY(id, N'IsView') = 1)
   DROP View [VIEW_USERINFO]
GO
CREATE VIEW [dbo].[VIEW_USERINFO]
AS 
SELECT U.ID, LoginName, TrueName, PassWord, OrganizationCode, 
Organization, DepartMent, SystemClass, UserType, StuffID, 
UserParam, U.RoleCode
RoleName, RoleRemark, PermissionCodes
FROM [dbo].[UserMEAS] U,[dbo].[UserRole]
WHERE U.RoleCode=UserRole.RoleCode  
GO

--left join
ALTER VIEW [dbo].[VIEW_USERINFO]
AS 
SELECT U.ID, LoginName, TrueName, PassWord, OrganizationCode, 
Organization, DepartMent, SystemClass, UserType, StuffID, 
UserParam, U.RoleCode,
RoleName, RoleRemark, PermissionCodes
FROM [dbo].[UserMEAS] U
--,[dbo].[UserRole]
--WHERE U.RoleCode=UserRole.RoleCode  
LEFT JOIN [dbo].[UserRole] 
ON U.RoleCode=UserRole.RoleCode  

GO

--字符串截取
select  substring(createdate,1,10) ,createdate,* from [HISAPPLICATIONFORM]

select count(hisexamno) as ApplyCount,t.createdate from [dbo].[HISAPPLICATIONFORM] t
 where t.createdate >='2022-03-19 00:00:00' 
 group by t.createdate
--分组，按日期
 select count(hisexamno) as ApplyCount,substring(convert(char(10) ,t.createdate , 21),1,10) from [dbo].[HISAPPLICATIONFORM] t
 where t.createdate >='2022-03-19 00:00:00' 
 group by substring(convert(char(10) ,t.createdate , 21),1,10)
--分组，按月份
  select count(hisexamno) as ApplyCount,substring(convert(char(7) ,t.createdate , 21),1,7) from [dbo].[HISAPPLICATIONFORM] t
 where t.createdate >='2022-03-19 00:00:00' 
 group by substring(convert(char(7) ,t.createdate , 21),1,7)

1.convert(float,endtimepart)——conver(数据类型，字段名称)

2.cast(endtimepart as float)——cast(字段名称 as 数据类型)

--数据类型转化
cast(SUBSTRING(d.TIMEPART,0,CHARINDEX('~',d.TIMEPART)) as float)as BEGINTIME
--修改字段类型
alter table DocumentManagement  alter column filecode [varchar](8000) null

 --数字转日期 

 select convert(char(20) ,convert(datetime,'20220310') , 21) as dd
 select convert(char(20) ,convert(datetime,'20220310') , 22) as dd
 select convert(char(20) ,convert(datetime,'20220310') , 23) as dd
 select convert(char(20) ,GETDATE() , 21) as dd
 select convert(datetime,'20220310')  as dd

 --实现Image类型转Text

------------------实现Image类型转Text------------------
--1.新增一字段
ALTER TABLE [InformedConsentInfo] ADD SignatureImg1 text
GO
--2.将数据更新至新增列
UPDATE [InformedConsentInfo]
SET    SignatureImg1 = CAST(CAST(SignatureImg AS text) AS text)
GO
--3.删除错误插入的字段
ALTER TABLE [InformedConsentInfo] DROP COLUMN SignatureImg 
GO
--4.对新增的字段进行重命名
sp_rename 'InformedConsentInfo.SignatureImg1','SignatureImg','column'

大小写转化

--大写
upper('aaaaa')
--小写
lower('BBBB')
--截取左边第1位字符串
left(‘abCdsE’,1)
--获取字符串长度
len(‘abCdsE’)
--截取从第2位到最后一位字符串
SUBSTRING(‘abCdsE’,2,len(‘abCdsE’))

----------------------大小写转化----------------------
select upper('helloWpf') as ToUpper,lower('helloWpf') as ToLower
go
--示例
declare @TableNameL varchar(200), @TableName sysname = 'DictPublic'
set @TableNameL = (select (lower(left('helloWpf',1))+SUBSTRING('helloWpf',2,len('helloWpf'))))
set @TableName = (select (lower(left(@TableName,1))+SUBSTRING(@TableName,2,len(@TableName))))
select @TableNameL as test1,@TableName as test2
print @TableNameL
print @TableName
go
----------------------大小写转化----------------------