sql常用语法
--在表中添加字段--数据库同步更新
if col_length('JX_DomesticStudy','XL') is null begin alter table JX_DomesticStudy add PXlevel int default 0 end go
--创建表
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JX_PXlevel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin CREATE TABLE [dbo].[JX_PXlevel]( [ID] [int] IDENTITY(1,1) NOT NULL, [PXJBName] [nvarchar](20) NOT NULL, CONSTRAINT [PK_JX_PXlevel] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --添加[dbo].[JX_PXlevel]表和JX_DomesticStudy的主外键约束 ALTER TABLE [dbo].[JX_DomesticStudy] WITH CHECK ADD CONSTRAINT [FK_JX_DomesticStudy_JX_PXlevel] FOREIGN KEY([PXlevel]) REFERENCES [dbo].[JX_PXlevel] ([ID]) ALTER TABLE [dbo].[JX_DomesticStudy] CHECK CONSTRAINT [FK_JX_DomesticStudy_JX_PXlevel] end go --向表中插入数据
if not exists (select * from JX_PXlevel where PXJBName='国外') begin insert into JX_PXlevel values('国外'); end go
--向表中添加字段
if not exists(select 1 from syscolumns where name='ZS01' and id=object_id('JX_DomesticStudy')) begin alter table JX_DomesticStudy add ZS01 nvarchar(30) end go
--教务管理中添加学生综合查询模块 add by 吴翰哲 if not exists (select * from dbo.XT_Cell where CellID='XS1301' and CellName='学生综合查询' and ModuleID='XS12' ) BEGIN INSERT INTO dbo.XT_Cell VALUES('XS1301','学生综合查询','XS12',NULL,'/XS/BZR/BZRSearchStu/BZRSearchStuView.aspx','学生综合查询.png',1,20,1) END GO --清空教研组成员所需要的约束删除 add by 吴翰哲 IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_XZ_Employee_JX_TeachingGroup]') AND parent_object_id = OBJECT_ID(N'[dbo].[XZ_Employee]')) begin ALTER TABLE [dbo].[XZ_Employee] DROP CONSTRAINT [FK_XZ_Employee_JX_TeachingGroup] end GO --向表中添加字段
if not exists(select * from sysColumns where name='JX_AttendClassInfo' and id=object_ID('XQMC')) begin alter table JX_AttendClassInfo add XQMC VARCHAR(50) null end GO
update yiyao.dbo.xt_querycolumn set FIELDALIAS = b.FIELDALIAS
from yiyao.dbo.xt_querycolumn a, panto_school.dbo.xt_querycolumn b
where a.queryobject = b.queryobject and a.attributesname = b.attributesname and a.fieldname = b.fieldname
select * from panto_school.dbo.xt_querycolumn
from yiyao.dbo.xt_querycolumn a, panto_school.dbo.xt_querycolumn b
where a.queryobject = b.queryobject and a.attributesname = b.attributesname and a.fieldname = b.fieldname
select * from panto_school.dbo.xt_querycolumn