Sql server表操作基础

1、创建表 1.1概述 表可以通过以下两种方法创建：企业管理器；DDL建表。 建表时应该考虑的问题： 表名 列的特征（列数，每列的列名、类型、宽度及是否允许为空等） 主键、索引、约束等 权限。CREATE TABLE 权限默认授予 db_owner 和 db_ddladmin 固定数据库角色成员。db_owner 固定数据库角色成员和 sysadmin 固定服务器角色成员可以将 CREATE TABLE 权限转让给其他用户。 1.2完整语法 CREATE TABLE [ database_name.[ owner ] . | owner. ] table_name ( { < column_definition > | column_name AS computed_column_expression | < table_constraint > } [ ,...n ] ) [ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ] < column_definition > ::= column_name data_type [ COLLATE < collation_name > ] [ [ DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL] [ < column_constraint > ] [ ...n ] < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ON {filegroup | DEFAULT} ] ] ] | [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } < table_constraint > ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] { ( column [ ASC | DESC ] [ ,...n ] ) } [ WITH FILLFACTOR = fillfactor ] [ ON { filegroup | DEFAULT } ] ] | FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( search_conditions ) } 1.3部分语法 CREATE TABLE table_name ( column_name datatype [table_constraint] [,...n] ) 1.3.1说明 本地临时表表名前加#，全局临时表表名前加## PRIMARY KEY约束 一个表只能包含一个 PRIMARY KEY 约束 存在PRIMARY KEY 约束的表中的非聚集索引不能超过 249 个，聚集索引不能超过 1 个 在主键约束上自动创建CLUSTERED索引 在 PRIMARY KEY 约束中定义的所有列都必须定义为 NOT NULL UNIQUE约束 如果 UNIQUE 约束中没有指定 CLUSTERED 或 NONCLUSTERED，则默认为 NONCLUSTERED 每个 UNIQUE 约束都生成一个索引，表中的非聚集索引不能超过 249 个，聚集索引不能超过 1 个 允许出现NULL值，但只能有一个，其它特性与主键约束相同（值的唯一性） FOREIGN KEY 约束 如果在 FOREIGN KEY 约束的列中输入非 NULL 值，则此值必须在被引用的列中存在，否则将返回违反外键约束的错误信息 FOREIGN KEY 约束仅能引用位于同一服务器上的同一数据库中的表。数据库间的引用完整性必须通过触发器实现 FOREIGN KEY 可以引用同一表中的其它列（自引用） 一个表最多可包含 253 个 FOREIGN KEY 约束 对于临时表不强制 FOREIGN KEY 约束 每个表在其 FOREIGN KEY 约束中最多可以引用 253 个不同的表 FOREIGN KEY 约束只能引用被引用表的 PRIMARY KEY 或 UNIQUE 约束中的列或被引用表上 UNIQUE INDEX 中的列 DEFAULT 定义 每列只能有一个 DEFAULT 定义 DEFAULT 定义可以包含常量值、函数、SQL-92 niladic 函数(提供当前系统日期时间或当前插入操作的用户名等)或 NULL DEFAULT 定义中的 constant_expression 不能引用表中的其它列，也不能引用其它表、视图或存储过程 不能在数据类型为 timestamp 的列或具有 IDENTITY 属性的列上创建 DEFAULT 定义 CHECK 约束 列可以有任意多个 CHECK 约束，并且约束条件中可以包含用 AND 和 OR 组合起来的多个逻辑表达式。列上的多个 CHECK 约束按创建顺序进行验证 搜索条件必须取值为布尔表达式，并且不能引用其它表 其它约束信息 为约束创建的索引不能用 DROP INDEX 语句除去；必须用 ALTER TABLE 语句除去约束 约束的名称必须符合标识符规则，但其名称的首字符不能为 #。如果没有提供 constraint_name，则使用系统生成的名称。约束名将出现在所有与违反约束有关的错误信息中 当 INSERT、UPDATE 或 DELETE 语句违反约束时，将终止执行该语句。但将继续处理事务（如果此语句为显式事务的组成部分）。可以通过检查系统函数 @@ERROR，在事务定义中使用 ROLLBACK TRANSACTION 语句 1.3.2分项示例 例1：使用 PRIMARY KEY 约束 * job_id smallint PRIMARY KEY CLUSTERED * emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED 例2：使用 FOREIGN KEY 约束 * job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id) * 也可以显式使用 FOREIGN KEY 子句并复述列特性。注意在这两个表中列名不必相同。 FOREIGN KEY (job_id) REFERENCES jobs(job_id) * 下例显示如何从其它表中引用多列键。CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id) REFERENCES sales (stor_id, ord_num, title_id) 例3：使用 UNIQUE 约束(UNIQUE 约束用于强制非主键列的唯一性) * pseudonym varchar(30) NULL UNIQUE NONCLUSTERED * stores 表中，stor_name 列和 city 列上创建的 UNIQUE 约束，实现同一个城市中的商店不应同名约束。CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city) 例4：使用 DEFAULT 定义（使用 INSERT 和 UPDATE 语句时，如果没有提供值，则默认会提供值。） * job_descvarchar(50) NULL DEFAULT 'New Position - title not formalized yet' * 获取输入项的当前日期： DEFAULT (getdate()) 例5：使用 CHECK 约束 * min_lvl tinyint NOT NULL CHECK (min_lvl >= 10) * emp_id char(9) CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]') * CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]') 1.4例 1.4.1完整表定义 /* ************************** jobs table ************************** */ 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) ) /* ************************* employee table ************************* */ CREATE TABLE employee ( emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'), fname varchar(20) NOT NULL, minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id), job_lvl tinyint DEFAULT 10, pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id), hire_date datetime NOT NULL DEFAULT (getdate()) ) /* ***************** publishers table ******************** */ CREATE TABLE publishers ( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT('USA') ) 2、修改表 2.1概述 实现表定义的更改。 权限。ALTER TABLE 权限默认授予表的所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让。 2.2完整语法 ALTER TABLE table { [ ALTER COLUMN column_name { new_data_type [ ( precision [ , scale ] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL } ] | ADD { [ < column_definition > ] | column_name AS computed_column_expression } [ ,...n ] | [ WITH CHECK | WITH NOCHECK ] ADD { < table_constraint > } [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] | { [ WITH CHECK | WITH NOCHECK ] CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } } < column_definition > ::= { column_name data_type } [ [ DEFAULT constant_expression ] [ WITH VALUES ] | [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL ] [ COLLATE < collation_name > ] [ < column_constraint > ] [ ...n ] < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor ] [ ON { filegroup | DEFAULT } ] ] | [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } < table_constraint > ::= [ CONSTRAINT constraint_name ] { [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] { ( column [ ,...n ] ) } [ WITH FILLFACTOR = fillfactor ] [ ON {filegroup | DEFAULT } ] ] | FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] | DEFAULT constant_expression [ FOR column ] [ WITH VALUES ] | CHECK [ NOT FOR REPLICATION ] ( search_conditions ) } 2.3部分语法 2.3.1添加列 ADD TABLE table_name ADD column_name列定义 [，...n] 例：更改表以添加新列 if exists (select * from tempdb.dbo.sysobjects where name like '#doc_exa%') drop table [dbo].[#doc_exa] CREATE TABLE #doc_exa ( column_a INT) GO ALTER TABLE #doc_exa ADD column_b VARCHAR(20) NULL GO SELECT * FROM #doc_exa GO 2.3.2撤消列 ALTER TABLE table_name DROP COLUMN column_name 例：更改表以除去列 if exists (select * from tempdb.dbo.sysobjects where name like '#doc_exb%') drop table [dbo].[#doc_exb] CREATE TABLE #doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GO ALTER TABLE #doc_exb DROP COLUMN column_b GO SELECT * FROM #doc_exb 2.3.3修改列 ALTER TABLE table_name ALTER COLUMN column_name data_type [NULL | NOT NULL] 例：更改表中的列 if exists (select * from tempdb.dbo.sysobjects where name like '#doc_exc%') drop table [dbo].[#doc_exc] CREATE TABLE #doc_exc ( column_a INT, column_b VARCHAR(20) NULL) GO ALTER TABLE #doc_exc ALTER COLUMN column_b CHAR(10) NOT NULL GO SELECT * FROM #doc_exc 3、删除表 3.1概述 将删除表定义和所有数据，以及该表的权限指定。 默认情况下，将 DROP TABLE 权限授予表所有者，该权限不可转让。然而，sysadmin 固定服务器角色成员或 db_owner 和 db_ddladmin 固定数据库角色成员可以通过在 DROP TABLE 内指定所有者除去任何对象。 3.2语法 DROP TABLE table_name