SQL Server

オブジェクトの作成・変更・削除

当ページではデータベースオブジェクト(以後、オブジェクトと呼称します)の作成・変更・削除などのクエリをまとめています。

データベース

データベースはテーブルやプロシージャを格納する最も大きな単位です。正直、オブジェクトにデータベースと名付けてしまうと、概念としてのデータベースと混同してしまうので別の名前(例えば、パッケージとかグループとか)にしたほうが良かったのではないかと思います。

作成

データベースを作成するにはCREATE DATABASE文を使います。

--文法 CREATE DATABASE データベース名 ; --例文 CREATE DATABASE database_1 ; --同時に複数作成はできません CREATE DATABASE database_1, database_2, database_3 ;

削除

データベースを作成するにはDROP DATABASE文を使います。

--文法 DROP DATABASE データベース名 ; --例文 DROP DATABASE database_1 ; --同時に複数削除はできません DROP DATABASE database_1, database_2, database_3 ;

テーブル

テーブルはカラムとレコードを収容するオブジェクトです。最も触れる機会の多いオブジェクトでしょう。

作成

テーブルを作成するにはCREATE TABLE文を使います。

--文法 CREATE TABLE テーブル名 ( カラム1名 データ型 オプション, カラム2名 データ型 オプション, カラムn名 データ型 オプション ) --例文 CREATE TABLE table_1 ( id int PRIMARY KEY, name varchar(50) NOT NULL, price int )

また、SELECT INTO文を使えばSELECT文の出力結果を元にテーブルを作成することもできます。CREATE TABLE文とINSERT INTO文を兼ねるとても便利なクエリですがオプションは引き継がれないことに注意してください。

--文法 SELECT データ1 INTO 作成するテーブル名 FROM テーブル名 ; --例文(table_1の内容を丸々table_2にコピーするクエリです) SELECT * INTO table_2 FROM table_1 ;

変更

この項ではテーブル名の変更・カラムの追加・カラムの変更・カラムの削除についてまとめています。

テーブル名の変更にはsp_renameというプロシージャを使います。

--文法 EXECUTE sp_rename 変更したいテーブル名, 変更後のテーブル名 ; --例文 EXECUTE sp_rename before_table, after_table ;

カラムを追加するにはALTER TABLE文とADD句を使います。ALTER TABLE文はカラムの変更や削除にも用います。

--文法 ALTER TABLE テーブル名 ADD 追加するカラム1名 データ型 オプション, 追加するカラム2名 データ型 オプション, 追加するカラムn名 データ型 オプション ; --例文 ALTER TABLE table_1 ADD release_date date, comment varchar(50) ;

カラムを変更するにはALTER COLUMN句を使います。なお、1度に複数のカラムを変更することはできません。

--文法 ALTER TABLE テーブル名 ALTER COLUMN 変更するカラム名 データ型やオプションを再度指定 ; --例文 ALTER TABLE table_1 ALTER COLUMN name char(16) PRIMARY KEY ;

カラムを削除するにはDROP COLUMN句を使います。

--文法 ALTER TABLE テーブル名 DROP COLUMN 削除するカラム1, 削除するカラム2, 削除するカラムn ; --例文 ALTER TABLE テーブル名 DROP COLUMN category, cost ;

削除

テーブルを削除するにはDROP TABLE文を使います。

--文法 DROP TABLE テーブル名 ; --例文 DROP TABLE table_1 ;

レコード

レコードは挿入・更新・削除の3つの処理が行えます。

挿入

レコードの挿入にはINSERT INTO文を使います。挿入先となるテーブル、およびカラムは予め作成しておく必要があります。

テーブル名直後の括弧のなかに挿入するカラム名を指定し、VALUES句で指定したカラムの順番通りに挿入するデータを指定します。テーブルの全てのカラムにデータを挿入するときはカラム名の指定を省略することができます。

--文法 INSERT INTO テーブル名 ( 挿入対象となる1つ目のカラム, 挿入対象となる2つ目のカラム, 挿入対象となるnつ目のカラム ) VALUES ( 1つ目のカラムに挿入するデータ, 2つ目のカラムに挿入するデータ, nつ目のカラムに挿入するデータ ), ( 1つ目のカラムに挿入するデータ, 2つ目のカラムに挿入するデータ, nつ目のカラムに挿入するデータ ) --例文 INSERT INTO table_1 ( id, name, price ) VALUES ( 4, 'Apple', 85 ), ( 5, 'Beans', 210 ) --省略することもできます INSERT INTO table_1 VALUES ( 4, 'Apple', 85 ), ( 5, 'Beans', 210 )

また、SELECT文の出力結果を挿入することもできます。ただし、出力結果の各カラムのデータ型と挿入先のカラムのデータ型に互換性がある必要があります。

--文法 INSERT INTO テーブル名 ( 挿入対象となる1つ目のカラム, 挿入対象となる2つ目のカラム, 挿入対象となるnつ目のカラム ) ※以下SELECT文 --例文 INSERT INTO table_1 ( name, price SELECT name, price FROM table_2 ;

更新

レコードを更新するときはUPDATE文を使います。SET句で更新対象となるカラムと上書きする値を指定し、WHERE句でどのレコードを更新対象とするのかを指定します。

--文法 UPDATE テーブル名 SET カラム名 = 値, カラム名 = 値, カラム名 = 値 WHERE 条件式 ; --例文 UPDATE table_1 SET price = 270, update_date = GETDATE() WHERE id = 24 ;

削除

レコードを削除するときはDELETE文を使います。削除対象のテーブルを指定し、WHERE句で削除するレコードを条件式で指定します。条件式を指定しないと対象のテーブルに格納された全てのレコードを削除します。

--文法 DELETE FROM テーブル名 WHERE 条件式 ; --例文 DELETE FROM table_1 WHERE update_date < GETDATE() ;

レコードを全て消す場合、TRUNCATE TABLE文がおススメです。DELETE文とは違い、削除するレコードを指定できませんが削除にかかる時間が短く済みます。

--文法 TRUNCATE TABLE テーブル名 ; --文法 TRUNCATE TABLE table_1 ;

ストアドプロシージャ

ストアドプロシージャは複数のクエリをまとめて1つにしたものです。こうすることで何度も同じクエリを書く手間を省き、また、記述ミスも防ぎます。似たようなものにストアドファンクション(ユーザー定義型関数)がありますが、あちらは関数として機能するのに対して、こちらはあくまでクエリをまとめたものです。似ていると言えば似ていますが本質的には異なるものです。

作成

ストアドプロシージャを作成するにはCREATE PROCEDURE文を使います。変数宣言部を囲む括弧は省略することができます。変数を利用しないときは省略しないとエラーになります。処理部を囲むBEGIN・ENDは省略することができます。

--文法 CREATE PROCEDURE ストアドプロシージャ名 ( ※変数宣言 ) AS BEGIN ※処理 END ; --文法(省略版) CREATE PROCEDURE ストアドプロシージャ名 AS ※処理 ;

変更

ストアドプロシージャの処理内容を変更するにはALTER PROCEDURE文を使います。変数宣言部を囲む括弧は省略することができます。変数を利用しないときは省略しないとエラーになります。処理部を囲むBEGIN・ENDは省略することができます。

--文法 CREATE PROCEDURE ストアドプロシージャ名 ( ※変数宣言 ) AS BEGIN ※処理 END ; --文法(省略版) CREATE PROCEDURE ストアドプロシージャ名 AS ※処理 ;

削除

ストアドプロシージャを削除するにはDROP PROCEDURE文を使用します。

DROP PROCEDURE 削除するプロシージャ1, 削除するプロシージャ2, 削除するプロシージャn ;

実行

ストアドプロシージャを実行するにはEXECUTE文を使用します。EXECUTEはEXECと略することができます。

--文法 EXECUTE ストアドプロシージャ名 ; --例文 EXECUTE usp_1 ; --省略表記版 EXEC usp_1 ;