1.表
1.1 建表
--创建组织结构信息表 CREATE TABLE [dbo].[tblOrg]( [PID] [int] IDENTITY(1,1) NOT NULL, [CreateTime] [datetime] NOT NULL, [OfficerID] [varchar](50) NOT NULL, [ModityTime] [datetime] NOT NULL, [OrgID] [varchar](30) NOT NULL, [OrgLevel] [int] NOT NULL, [OrgName] [varchar](50) NOT NULL, [AliasName] [varchar](50) NOT NULL, [AccountsID] [varchar](30) NOT NULL, [OrderNum] [int] NULL, [Isforbidden] [int] NULL, CONSTRAINT [PK_tblOrg] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_CreateTime] DEFAULT (getdate()) FOR [CreateTime] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_OfficerID] DEFAULT ('') FOR [OfficerID] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_ModityTime] DEFAULT ('') FOR [ModityTime] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_OrgID] DEFAULT ('') FOR [OrgID] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_OrgLevel] DEFAULT ((1)) FOR [OrgLevel] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_OrgName] DEFAULT ('') FOR [OrgName] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_AliasName] DEFAULT ('') FOR [AliasName] GO ALTER TABLE [dbo].[tblOrg] ADD CONSTRAINT [DF_tblOrg_AccountsID] DEFAULT ('') FOR [AccountsID] GO
1.2 删除表
DROP TABLE tblOrg;
1.3 添加表说明
EXECUTE sp_addextendedproperty N'MS_Description','创建组织结构信息表',N'user',N'dbo',N'table',N'tblOrg',NULL,NULL;
1.4 删除表说明
EXEC sp_dropextendedproperty N'MS_Description','user','dbo','table', 'tblOrg', NULL,NULL;
2.字段
2.1.增加字段
alter table tblFMInfo add CmmType varchar(20) null; --通讯方式 EXECUTE sp_addextendedproperty N'MS_Description','通讯方式',N'user',N'dbo',N'table',N'tblFMInfo',N'column',N'CmmType';
(
alter table tblFMInfo add CmmType varchar(50);
alter table tblFMInfo add CmmType varchar(20) null;
alter table tblFMInfo add CmmType int default 1 NOT NULL;
alter table tblFMInfo add CmmType int IDENTITY(1,1) NOT NULL;
)
2.2.删除字段
alter table tblFMInfo drop CmmType; /* 单独使用(一般不用) EXEC sp_dropextendedproperty N'MS_Description', 'user','dbo', 'table', 'tblFMInfo', 'column','CmmType'; */
3.常用数据类型
4.CONVERT() 函数
(1)把日期转换为新数据类型
(2)用不同的格式显示日期/时间数据
语法: CONVERT(data_type(length),data_to_be_converted,style)
data_type(length) 规定目标数据类型(带有可选的长度)
data_to_be_converted 含有需要转换的值
style 规定日期/时间的输出格式
20,21,23,25,120,121,126 用于提取前10位YYYY—MM-DD ,常用121 使用方式:convert(varchar(10),getdate(),121)
日期数值范围:0-25,100-114,120-121,126,130,131
style取值:
举个栗子:
select convert(varchar(16),getdate()) 05 15 2017 10:02 select convert(varchar(16),getdate(),121) 2017-05-15 10:02 select convert(varchar(16),getdate(),110) 05-15-2017
select convert(varchar(8),getdate(),112) 20170515
参考:Sql日期时间格式转换
http://www.cnblogs.com/Gavinzhao/archive/2009/11/10/1599690.html