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] (