Sql Serverシリーズ:データテーブル操作

30266 ワード

テーブルは、データを格納し、操作するための論理構造であり、データを組織および格納するために使用され、リレーショナル・データベース内のすべてのデータがテーブルの形式で表現され、データ・テーブルは行と列から構成されます.SQL Serverのデータテーブルはテンポラリ・テーブルと永続テーブルに分けられ、テンポラリ・テーブルはtempdbシステム・データベースに格納され、SQL Serverを使用しないか終了すると、テンポラリ・テーブルは自動的に削除されます.永続テーブルは、ユーザーが削除しない限り、作成後もデータベース・ファイルに存在します.
データテーブルを作成する2つの方法:(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

【白俊】10816番:デジタルカード2

SQL Serverテーブルの重複行を削除する方法