SQLの種類を一覧でまとめておこう(例文つき)

覚えるべきキーワード
・データ操作言語(DML)
・SELECT
・INSERT
・UPDATE
・DELETE
・MERGE
・データ定義言語(DDL)
・CREATE
・ALETR
・DROP
・RENAME
・TRUNCATE
・COMMENT
・データ制御言語(DCL)
・GRANT
・REVOKE
・トランザクション制御
・COMMIT
・ROLLBACK
・SAVEPOINT

システムエンジニア(SE)になりたての方がSQLを習得するのはなかなか難しいですが、基礎と言われる構文だけは覚えておきましょう。大きく4つの種類に分類できます。4つとはデータ操作言語(DML)、データ定義言語(DDL)、データ制御言語(DCL)、トランザクション制御です。

データ操作言語(DML)

データを操作するための言語です。データベースからデータ取出し、更新、挿入、削除、マージをします。データに直接影響するSQLです。SELECT、UPDATE、INSERT、DELETE、MERGEがあります。最終的にcommitを実行しない限り、ディスクには書きこまれません。

SELECT
SELECTはデータを取得するSQLです。システムエンジニア(SE)になりたての新人はSELECTしか触れません。なぜなら、SELECT以外のDMLはテーブルのデータを変更するからです。でもSELECTは取得するだけなので、データを壊したり、消したりするリスクがありません。
deptテーブルからすべてのデータを取得するSQLです。

*(アスタリスク)なので、全データを取得します。

selectはサブクエリやWHERE句、結合などを使って、複雑なSQLが作成されます。また、性能悪化の原因にもなりえるDMLなので、SELECTと言えど、侮ってはいけません。

UPDATE
UPDATEはデータを更新するSQLです。更新とは、すでにテーブルに入っているデータの値を書き変えることを言います。dept表のid列値が10のデータがあります。そのage列値を20から30に更新するUPDATE文です。

UPDATEはWHERE句で対象条件を絞って、SETに変更する列名と値を書きます。もし、deptテーブルにid列値が10のデータが5個あれば、このSQLを実行すると、その5個のデータのage列はすべて30に更新されます。一括で変更するので慎重にSQLを組んでください。基本的に途中でストップできません。

INSERT
INSERTは新規でデータを挿入するSQLです。deptテーブルがid、age、salary、branchで構成されているとしましょう。
このdeptテーブルにid:30、age:28、salary:250000,branch:tokyoというデータをINSERTします。

列名は書いたり、書かなかったりできます。サブクエリをそのままINSERTしたりもできます。

DELETE
データを削除するSQLです。削除はDMLの中で一番気を使います。個人的にはあまり書きたくないです。誤ったDELETE文では必要なデータまで削除しかねませんので、僕は入念にレビューしてもらいます。dept表のid列値が10のデータを削除します。もし、deptテーブルにid列値が10のデータが5個あれば、このSQLを実行すると、その5個のデータのage列はすべて削除されます。一括で削除されてしまうので、慎重にSQLを組んでください。

MERGE
MERGE分は可読性が低いので、あまり使いません。MERGEは「条件に合致したデータがあったときはUPDATE文を実行し、なかったときはINSERT文を実行」するDMLです。
一旦、次のSQLを見てください。

ごちゃごちゃしていますね。このSQLは、dept表のデータをINSERTもしくは、UPDATEします。
意味は、dept表のdeptnoとdept_old表のdeptnoを比較して、同じ値があれば、dept_old表のnameをdept表のnameにUPDATE(更新)し、なければdept_old表のid、dept_name、branchデータをINSERT(挿入)します。

個人的にはMERGEを使わずに、IF文でSQLを分岐させた方が見やすい気がします。

データ定義言語(DDL)

データ定義言語はオブジェクト(表や索引)の作成、変更、削除を行います。データ操作言語(DML)が表の中にあるデータを直接操作するSQLだったのに対して、データ定義言語は表や索引が対象です。DDLは実行したらそれが即データベースに反映されます。DMLのようにcommitせずともデータベースに反映するので、commit忘れなどを心配する必要がない反面、実行後は取り消せないリスクがあります。

CREATE
オブジェクトを作成するDDLです。良く見るCREATEはCREATE TABLEですが、CREATE VIEWやCREATE SYNONYMなどあらゆるオブジェクトを作成します。
dept表を作成するCREATE TABLEです。

ALTER
システム開発の現場で意外に良く使うDDLがALTER文です。システムエンジニア(SE)になる前に独学していた時はALTERがあまり参考書に出てこないので軽視していましたが、実際はテーブルや制約の変更でよく使っています。
dept表に設定されていたpk_deptというプライマリー制約を追加するALTER TABLEです。

これでpk_dept制約を追加できました。ただし、dept_id列にnullや重複値が入っていると、プライマリーキー制約の性質上、作成できません。

DROP
オブジェクトを削除するDDLです。dept表を削除します。
DROP TABLEを実行すると対象オブジェクトはゴミ箱に入ります。フラッシュバックを使えば、実行後も復活させられますが、purge句を付けて、DROP TABLEを実行すると完全に削除され、再利用は不可能になります。

RENAME
オブジェクトの名前を変更するときに使うDDLです。基本、ALTERとセットで使います。

dept表名をdept_newに名前変更するRENAMEです。あんまりRENAMEを使うことはないかもしれないです。個人的には、RENAMEするならDROP、CREATEのセットで新しく作り直す方がリスク少ない気がします。

TRUNCATE
TRUNCATEは表の切り捨てを実行するDDLです。DROPとTRUNCATEは似ていますが、違いをはっきりとさせておいた方が良いです。DROPはオブジェクトごと消し去りますが、TRUNCATEはオブジェクトの構造を残したまま、すべてのデータを削除します。なのでTRUNCATEはDELETEにも近いSQLです。ただし、DELETEで全データ削除するよりもTRUNCATEで全データ削除した方がスピードは速いです。
dept表をTRUNCATEしたSQLです。

COMMENT
テーブルに対するコメントを入れるSQLです。「このテーブルはマスタテーブルです。」などのコメントを加えられます。
「deptはマスタテーブルです。」のコメントをdept表に入れるSQLです。

COMENT ON TABLE 表名 IS ‘コメント’;が構文です。

データ制御言語(DCL)

データ制御言語は、アクセス制御を行うSQLです。ユーザーに対して、テーブルごとに実行できるSQLなどを付与・または削除を行います。DCLはGRANTとREVOKEの2つだけです。

GRANT
権限を付与するDCLです。テーブルを作成したユーザーはそのテーブルに対して、SELECTやらDELETEやらを実行できるのですが、テーブルを作成していないユーザーはデフォルトではそのテーブルが操作不可です。権限を与えれば、テーブル非作成ユーザーでもテーブルを操作できるようになります。
auserユーザーが作成したdeptテーブルのselect権限をbuserユーザーに付与するSQLです。

ちなみに複数の権限を同時に付与することも可能です。上記SQLはSELECTだけでしたが、UPDATEも一緒に付与するSQLはこちらです。

GRANT SELECT,UPDATE ON auser.dept TO buser;

REVOKE
権限を削除するDCLです。
buserユーザーに付与されていたauser所有のdept表に対するSELECT権限を取り消します。

以後、buserはauser.dept表にはselect実行できません。もちろん、GRANTで再度、権限付与すればSELECTできるようになります。

トランザクション制御

DMLによるデータ変更をトランザクション単位で管理するSQLです。DMLを実行した時点ではまだデータ変更は未確定です。トランザクション制御のSQLを実行して、そのDMLの変更を制御します。COMMIT、ROLLBACK、SAVEPOINTがあります。

COMMIT
トランザクション制御を確定するSQLです。例えば、UPDATE文を実行してもそれだけではDBのディスクに変更が書き込まれませんので、実質的にデータは変わらないままです。COMMITして、初めて確定します。
COMMITの実行は、COMMITと打つだけです。

ROLLBACK
実行したトランザクションを確定しないためにはDB操作ツール(osqleditなど)の接続を切断するか、ROLLBACKの実行をするかです。ROLLBACKを実行すれば、トランザクションは取り消されます。

SAVEPOINT
SAVEPOINTは、トランザクションを分けるためのSQLです。(と僕は思っています。)ROLLBACKとセットで使います。
例えば以下の順にロールバックをしてみましょう。

このROLLBACKだと処理①も処理②も変更取り消しが行われます。処理①と処理②を分けてトランザクション制御したい時は処理①と処理②の間にセーブポイントを作成します。

このようにセーブポイントAを作成し、Aまでロールバックを実行すると処理②は取りされますが、処理①は取り消されません。セーブポイントを作成することでトランザクションを分割管理できます。

まとめ

よく使うSQLは、システムエンジニア(SE)のポジションによって様々です。新人レベルはSELECTしか実行させてもらえないでしょうが、少しレベルが上がればUPDATEなどのDML、さらに上がればCREATEやGRANTなどのDDLやDCLも上司の実行許可が出ると思います。ただし、成長が不十分なシステムエンジニアは一生、DML以外は使わせてもらえないです。ご注意を。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です