数据库架构更新Sql语句编写规范

 

l         创建数据库表

下例显示 pubs 数据库中所创建的三个表（jobs、employee 和 publishers）的完整表定义，其中包含所有的约束定义。

CREATE TABLE 表名

(

字段名1 数据类型 是否允许为null,

字段名2 数据类型 是否允许为null

)

例如:

CREATE TABLE jobs

(

   job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   job_desc varchar(50) NOT NULL  DEFAULT 'New Position - title not formalized yet',

   min_lvl tinyint NOT NULL  CHECK (min_lvl >= 10),

   max_lvl tinyint NOT NULL  CHECK (max_lvl <= 250)

)

SqlServer2000
SqlAnyWhere

 

l         修改数据库表

例如:

ALTER TABLE jobs

(

   job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   job_desc varchar(50) NOT NULL  DEFAULT 'New Position - title not formalized yet',

   min_lvl tinyint NOT NULL  CHECK (min_lvl >= 10),

   max_lvl tinyint NOT NULL  CHECK (max_lvl <= 250)

)

SqlServer2000
SqlAnyWhere

 

l         为表增加主键

ALTER TABLE 表名

ADD CONSTRAINT [主键名(默认加”PK_”的前缀)] PRIMARY KEY CLUSTERED(是否是簇索引)

(

       [字段名1],[字段名2],[字段名…]

)

GO

例如:

ALTER TABLE [dbo].[m_p_CheckPrepotency] WITH NOCHECK

ADD CONSTRAINT [PK_m_p_CheckPrepotency] PRIMARY KEY CLUSTERED

       (

              [GUID],

              [SequenceNum]

       )

GO

SqlServer2000
SqlAnyWhere

 

l         新加字段

ALTER TABLE 表名

ADD 字段名 数据类型 属性

GO

例如:

SqlServer2000
SqlAnyWhere	ALTER TABLE skill ADD skill_description CHAR(254)

 

l         删除字段

SqlServer2000
SqlAnyWhere	ALTER TABLE 表名 DROP 字段名

 

l         创建索引

CREATE INDEX 索引名 ON 表名(字段名1,字段名2,字段名3…)

例如:

CREATE INDEX [IX_m_p_CheckPrepotency] ON [dbo].[m_p_CheckPrepotency]([UnitCoding], [FamilyId], [PersonnelCoding]) ON [PRIMARY]

GO

SqlServer2000
SqlAnyWhere

 

l         删除索引

IF EXISTS (SELECT name FROM sysindexes WHERE name = '索引名')

DROP INDEX 表名.索引名

GO

例如:

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'au_id_ind')

   DROP INDEX authors.au_id_ind

GO

SqlServer2000
SqlAnyWhere

 

l         删除视图

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

         WHERE TABLE_NAME = '视图名')

   DROP VIEW 视图名

GO

例如:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

         WHERE TABLE_NAME = 'titles_view')

   DROP VIEW titles_view

GO

 

SqlServer2000
SqlAnyWhere

 

 

 

 

 

 

 

 

 

l         为表的字段添加外键和级联

ALTER TABLE 表名1 ADD

       CONSTRAINT [FK_外键名] FOREIGN KEY

       (

              [字段名]

       ) REFERENCES 表名2

(

              [字段名]

       )

ON DELETE CASCADE –级联删除

ON UPDATE CASCADE –级联更新

GO

       CONSTRAINT [FK_m_p_CheckPrepotency_o_PrepotencyResult] FOREIGN KEY

       (

              [CheckPrepotencyResult]

       ) REFERENCES [dbo].[o_PrepotencyResult] (

              [CheckResultId]

       ) ON UPDATE CASCADE

GO

例如:

ALTER TABLE [dbo].[m_p_CheckPrepotency] ADD

       CONSTRAINT [FK_m_p_CheckPrepotency_m_PersonnelInfo] FOREIGN KEY

       (

              [GUID]

       ) REFERENCES [dbo].[m_PersonnelInfo] (

              [GUID]

       ) ON DELETE CASCADE  ON UPDATE CASCADE ,

       CONSTRAINT [FK_m_p_CheckPrepotency_o_PrepotencyResult] FOREIGN KEY

       (

              [CheckPrepotencyResult]

       ) REFERENCES [dbo].[o_PrepotencyResult] (

              [CheckResultId]

       ) ON UPDATE CASCADE

GO

SqlServer2000
SqlAnyWhere

 

l         修改列的数据类型/大小/是否允许为NULL

S2K:

SqlAnyWhere:

SqlServer2000	ALTER TABLE s_Anchor ALTER COLUMN AnchorName varchar(100) NOT NULL
SqlAnyWhere	ALTER TABLE s_Anchor MODIFY  AnchorName varchar(100) NOT NULL

 

l         修改列名

SqlServer2000
SqlAnyWhere	ALTER TABLE skill RENAME skill_type TO classification

 

l         修改表名

SqlServer2000
SqlAnyWhere	ALTER TABLE skill RENAME qualification

 

l         删除列

SqlServer2000
SqlAnyWhere	ALTER TABLE skill DROP classification

 

l         判断某个表的主键是否存在

SqlServer2000
SqlAnyWhere	if (select primary_root from systable where table_name = 's_Anchor' and table_type = ‘BASE’)<>0 如果primary_root不为0说明表中已有主键

 

l         删除表的所有主键

SqlServer2000
SqlAnyWhere	ALTER TABLE skill DELETE PRIMARY KEY

 

l         增加表主键

SqlServer2000
SqlAnyWhere	ALTER TABLE skill ADD PRIMARY KEY ( "skill_id", "skill_type" )

 

l         判断某个表中某个字段是否存在

SqlServer2000
SqlAnyWhere	if not exists (select * from syscolumn sc join systable st on sc.table_id = st.table_id where st.table_name = 'm_p_PregnancyInfo' and sc.column_name = 'PunishSequenceNum')

 

l         判断某个表是否存在

SqlServer2000
SqlAnyWhere	if not exists(select * from systable where table_name = 's_Anchor' and table_type = ‘BASE’)

 

l         判断某个视图是否存在

SqlServer2000	if not exists (select * from dbo.sysobjects where xtype = 'V' and name = 'v_s_Marriage')
SqlAnyWhere	if not exists(select * from systable where table_name = 's_Anchor' and table_type = ‘VIEW’)

 

l         判断某个全局临时表是否存在

SqlServer2000
SqlAnyWhere	if not exists(select * from systable where table_name = 's_Anchor' and table_type = ‘GBL TEMP’)

 

l         判断某个触发器是否存在

SqlServer2000
SqlAnyWhere	if not exists(select * from systable where table_name = 's_Anchor' and table_type = ‘TR’)

 

l         判断某个函数/存储过程是否存在

SqlServer2000
SqlAnyWhere	if exists (select * from sysprocedure where proc_name = 'sp_seeaboutServiceCar')

 

l         判断某个表的某个字段是否存在

SqlServer2000
SqlAnyWhere	if exists (select * from syscolumn where column_name = 'DogID' and table_id = (select table_id from systable where table_name = 's_Users' and table_type = 'BASE'))

 

l         为某个表增加一个字段

SqlServer2000
SqlAnyWhere	ALTER TABLE s_Users ADD DogID char(32) NULL