Sql Server系列：数据表操作

　　表是用来存储数据和操作数据的逻辑结构，用来组织和存储数据，关系数据库中的所有数据都表现为表的形式，数据表由行和列组成。SQL Server中的数据表分为临时表和永久表，临时表存储在tempdb系统数据库中，当不再使用或退出SQL Server时，临时表会自动删除；永久表一旦创建之后，除非用户删除，否则将一直存在数据库文件中。

　　创建数据表的两种方法：(1) 通过对象资源管理器创建；(2) 通过Transact-SQL语句进行创建。

1 Transact-SQL创建表

1.1 语法

CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
        ( { <column_definition> | <computed_column_definition> 
                | <column_set_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
    [ FILESTREAM_ON { partition_scheme_name | filegroup 
        | "default" } ]
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

<column_definition> ::=
column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 
    [ SPARSE ] 

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max | 
        [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] 

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor  
          | WITH ( < index_option > [ , ...n ] ) 
        ] 
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | "default" } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<computed_column_definition> ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL ] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH ( <index_option> [ , ...n ] )
        ]
    | [ FOREIGN KEY ] 
        REFERENCES referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
    [ ON { partition_scheme_name ( partition_column_name ) 
        | filegroup | "default" } ]
] 

<column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
                (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
            WITH FILLFACTOR = fillfactor 
           |WITH ( <index_option> [ , ...n ] ) 
        ]
        [ ON { partition_scheme_name (partition_column_name)
            | filegroup | "default" } ] 
    | FOREIGN KEY 
                ( column [ ,...n ] ) 
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 
<table_option> ::=
{
    DATA_COMPRESSION = { NONE | ROW | PAGE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
            [ , ...n ] ) ]
}

<index_option> ::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF} 
  | ALLOW_PAGE_LOCKS ={ ON | OFF} 
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
       [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
       [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

2 Transact-SQL修改表

2.1 语法

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name 
            [ ( 
                { 
                   precision [ , scale ] 
                 | max 
                 | xml_schema_collection 
                } 
            ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]
      | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
     {
         [ CONSTRAINT ] 
         { 
              constraint_name 
              [ WITH 
               ( <drop_clustered_constraint_option> [ ,...n ] ) 
              ] 
          } [ ,...n ]
          | COLUMN 
          {
              column_name 
          } [ ,...n ]
     } [ ,...n ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 

    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_lock_priority_wait> ) ]
    | SET ( FILESTREAM_ON = 
            { partition_scheme_name | filegroup | "default" | "NULL" } 
          )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>

    | <filetable_option>

}
[ ; ]

2.2 示例

　　修改表添加新列

ALTER TABLE [dbo].[Product] ADD [UnitPrice] DECIMAL(18,2) NULL

ALTER TABLE [dbo].[Product] ADD [UnitsInStock] INT NULL, [UnitsOnSale] INT NULL

　　修改列

ALTER TABLE [dbo].[Product] ALTER COLUMN [ProductName] VARCHAR(100) NOT NULL

　　删除列

ALTER TABLE [dbo].[Product] DROP COLUMN [UnitPrice]

3 Transact-SQL删除表

3.1 语法

DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
table_name [ ,...n ]
 [ ; ]

4 表操作示例

　　查看数据库中全部的表

USE [Portal]
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

　　INFORMATION_SCHEMA是一个特定的访问路径，用于显示系统数据库及其内容的相关元数据。如INFORMATION_SCHEMA.VIEWS、INFORMATION_SCHEMA.SCHEMATA。

　　查看表的属性

　　存储过程sp_help的功能是查看任意数据库对象、用户自定义数据类型或SQL Server数据类型的信息。

　　执行存储过程sp_help的语法结构：

EXEC sp_help <name>

　　查看Product表的属性：

EXEC sp_help Product