sql server 常用语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

--新增字段 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

--新增视图 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--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 数据类型)

?

1 2 3 4

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

 --数字转日期 

?

1 2 3 4 5

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

------------------实现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'

大小写转化

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

--大写 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 ----------------------大小写转化----------------------