--新增字段 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 ----------------------大小写转化----------------------
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!