数据库架构更新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] (