SQL
出典: Wikipedio
|
Template:プログラミング言語 SQL(エスキューエル,シークェル)は、リレーショナルデータベース管理システム (RDBMS) において、データの操作や定義を行うためのデータベース言語(問い合わせ言語)である。リレーショナルデータベースの関係モデル(リレーショナルモデル)における演算体系である、関係代数と関係論理(関係計算)にある程度基づいている。 データベース言語国際標準としてのSQLは何かの略語ではない<ref>SQLは、Structured Query Languageの略と思っている人やそう紹介しているサイトも少なくないが、多くの場合、これは正しくない。IBM社がかつて提供していたSQL/DSなどのRDBMSのSQLなら、これで正解である。一方、標準SQLは、IBM社のRDBMSのDB2の仕様を多く取り入れており、影響を受けていることは疑いようもないが、「SQLは、何かの略語ではない」としている。</ref>。
SQLは、シークェル Template:IPA と読まれることもある。これは、SQLの元となったデータベース言語が、IBM社が開発したRDBMSの実験実装である System R の操作言語「SEQUEL (Structured English Query Language)」であったことが由来である。
SQLに対しては、関係代数と関係論理に忠実に準拠していないとして批判する意見がある(The Third Manifesto - クリス・デイト、ヒュー・ダーウェン)。
目次 |
標準SQL規格
当初は特に統一標準規格が存在しない状況で、各リレーショナルデータベース管理システム (RDBMS) ベンダーごとにさまざまな拡張がなされてきた。 近年になってANSI、後にISOで言語仕様の標準化が行われており、制定された年ごとにSQL86, SQL89, SQL92, SQL:1999, SQL:2003, SQL:2008 などの規格があるが、対応の程度はベンダーごとにバラバラなのが実情である。これは標準SQL策定に時間がかかりすぎたことにより、ビジネスの現状から早期の機能拡張が迫られたベンダーの都合と、独自構文を頻繁に利用していたユーザに対し、互換性保持を保証する必要もあったためである。
SQL規格は非常に多くの改正が行われた。制定年度順に代表的な規格を以下に挙げる。
年 | 規格名称 | 別称 | 説明 |
---|---|---|---|
1986 | SQL86 | SQL87 | ANSIによって最初に発表された最初の規約。1987年にISOによって批准された。 |
1989 | SQL89 | マイナーバージョン。
| |
1992 | SQL92 | SQL2 | メジャーバージョン |
1995 | SQL/CLI | コールレベルインターフェース (Call Level Interface) 業界標準になった ODBC API のインタフェースに相当する機能を国際標準化した規格 | |
1996 | SQL/PSM | 永続格納モジュール (Persistent Storage Module) 一般的にストアドプロシージャと呼ばれる機能を国際標準化した規格 | |
1999 | SQL:1999 (SQL99) | SQL3 | RDBMSのための完全な言語になることを目指した仕様。<ref>非スカラー型とオブジェクト指向機能については、いくらか論議を呼ぶことになり、いまだ広く支持されていない。</ref>
|
2003 | SQL:2003 | ||
2008 | SQL:2008 |
|
SQLとオンライン処理
当初はリレーショナルデータベース管理システム (RDBMS) に端末から直接命令を発行することを想定していたため、制御文法の仕様が存在しなかった。このため、SQLは「宣言型プログラミング言語」と分類される。これに対し、COBOLやC言語などは「手続き型プログラミング言語」と分類される。
その後、手続き型プログラミング言語(母言語)からリレーショナルデータベースへのアクセスを行えるようにするため、母言語のソースコードにSQL文を記述し、プリプロセッサによってSQL部分を母言語のソースコードに変換してデータベースアプリケーションを開発する方式が普及した。これを「埋め込みSQL」(Embedded SQL/ESQL) と呼び、後にANSIにより仕様が標準化された。
暫くの間、データベースアプリケーションは、RDBMSベンダーが製品に同封したユーティリティや埋め込みSQLにより開発されてきたが、マイクロソフト社がC言語からAPIレベルで統一したソースコードを記述し、クライアント・サーバ型アプリケーションシステムの構築に有用である仕組み「Open Database Connectivity」(ODBC) を発表し、その有用性からANSIではODBC仕様を参考に「SQL/CLI」という仕様を標準化した。
SQLとバッチ処理
埋め込みSQLやODBCの普及により、オンライントランザクション処理向きのSQLアクセス方法は確立されたが、バッチ処理性能向上の必要性が求められるようになった。
ある表(テーブル)の内容を編集して別の表に格納する大量データの更新処理などをデータベースエンジン内部で処理プログラムを実行し、入出力 (I/O) のほとんどをデータベース内部で完結することにより、クライアント側とのデータ通信によるオーバヘッドを削減することでバッチ処理性能を向上させる「ストアドプロシージャ」が考え出された。
ストアドプロシージャは、同じくデータベース内部に定義し、データベースに発生したイベントの内容に応じて任意の処理を実行する機能である「データベーストリガ」とともに、標準SQL仕様に採用され、SQL:1999 (SQL99) 規格の永続格納モジュール (SQL/PSM) として標準化された。
しかし、標準化される以前から各リレーショナルデータベース管理システム (RDBMS) ベンダーがデータベースエンジン内部で制御文法を記述し実行できるように独自の拡張が行われていたため、ストアドプロシージャの処理ロジック記述文法はそれ以前に標準化されたSQL文法と比較して著しい非互換が認められるため、アプリケーションソフトウェアの移植性・開発生産性・保守性を損なう場合がある。
各RDBMSベンダーによる、制御構文を含む独自SQL文法には以下のようなものがある。これらの仕様には、文法単位の独自追加だけでなく、命令やデータ型の非互換も存在するため注意が必要である。
- PL/SQL (Oracle)
- Transact-SQL (Sybase、Microsoft SQL Server)
- PL/pgSQL (PostgreSQL)
- ストアドルーチン (MySQL)
Javaとデータベース
後に大規模システム開発において、Javaによるアプリケーションソフトウェア開発が一般的になるきっかけとなったのは、リレーショナルデータベースアクセスをJavaから行うAPI仕様である「JDBC」が発表されてからである。
さらにJavaで大規模エンタープライズシステムを開発するための仕様「Java EE (Java Platform, Enterprise Edition)」には、リレーショナルデータベースの表(テーブル)の行データを、Javaのオブジェクトに1対1に対応させ、オブジェクト内容の永続化=行データの保存というデータのリンクと、オブジェクトのメソッド呼び出し=データベースへのトランザクション処理を同期させる特殊なJava Beanを動作・管理する機構である「EJB (Enterprise JavaBeans)」 の「Entity Bean」が導入された。
EJB2.1までは、オブジェクトーリレーショナルの間にあるインピーダンスミスマッチによりリレーショナルデータベースの機能を十分に生かせないことや性能面の問題があったが、EJB3.0仕様により改善されてきている。
SQLの対話的実行
SQLを対話的に実行する場合、リレーショナルデータベース管理システム (RDBMS) に付属するコマンドラインタイプのアクセスユーティリティを利用するのが一般的である。SQL文を記述したテキストファイルをスクリプトとして実行し、バッチ的に実行することが可能なものもあり、広く利用されている。RDBMSごとに、そのユーティリティ固有の命令を備えているものもあるため、データベースを扱うアプリケーションソフトウェア開発の初心者はその命令もデータベースエンジンが解釈するSQL文法のひとつであると間違って覚えてしまい、ODBCやJDBCなどAPIからSQLを実行したときのエラーの原因が理解できずに混乱することもある。
ユーティリティ固有の文法で誤解しやすいものには、データベースでSQL文の文末に指定する文字である。全データベース共通では「;」、Oracle Database の ユーティリティであるSQL*Plusで、ストアドプロシージャの定義や無名PL/SQLブロックを発行するときに文末行に指定する「/」 や、Sybase/SQL Serverのisql/osqlではすべてのSQL文の文末行に指定する「GO」などがある。このなかでもっとも間違えやすいのが「;」である。これは、一般的なSQL教科書でも構文の終端文字として例が記載されているが、標準SQLの構文の終端文字ではない。
SQL文法
コマンド種別
データベース言語SQLの文法の種別は、以下の3つに大別される。
- データ定義言語 (DDL: Data Definition Language)
- データ操作言語 (DML: Data Manipulation Language)
- データ制御言語 (DCL: Data Control Language)
その他に、これらの命令の適用範囲を補完するための機能として、SQL文を実行時に解釈する「動的SQL」や、埋め込みSQLのための命令などが用意されている。 リレーショナルデータベース管理システム (RDBMS) 以前のデータベース管理システム (DBMS) では、これらは必ずしも同一の言語ではなかった。データ定義言語は存在せずにすべて専用のコマンドにパラメタを指定して実行する実装も存在した。
コマンド文法
データ定義言語
データ操作言語
- INSERT INTO (行データもしくは表データの挿入)
- UPDATE 〜 SET (表を更新)
- DELETE FROM (表から特定行の削除)
- SELECT 〜 FROM 〜 WHERE (表データの検索、結果集合の取り出し)
- 後述する「動的SQL」でのSELECT文には、一度の実行で1行の結果を取得する「単一行SELECT文」と、カーソルにより複数行の結果を取得する「カーソルSELECT文」がある。
列名と値を、対で指定 <source lang="sql"> INSERT INTO 表名(列名1,列名2) VALUES(値1,値2) </source>
表を構成するすべての列に値を格納する場合は、列名の記述を省略可能 <source lang="sql"> INSERT INTO 表名 VALUES (値1, 値2) </source>
他表のデータを検索して格納 <source lang="sql"> INSERT INTO 表名1 SELECT 列名1, 列名2 FROM 表名2 〜 </source>
更新 <source lang="sql"> UPDATE 表名
SET 列名2=値2, 列名3=値3 WHERE 列名1=値1
</source>
削除 <source lang="sql"> DELETE FROM 表名
WHERE 列名1=値1
</source>
1行以上の検索 <source lang="sql"> SELECT *
FROM 表名 WHERE 列名1 BETWEEN 値1 AND 値2 ORDER BY 列名1
</source>
1行だけの検索 <source lang="sql"> SELECT *
INTO 受け取り変数 FROM 表名 WHERE 列名1=値1
</source>
データ制御言語
- GRANT (特定のデータベース利用者に特定の作業を行う権限を与える)
- REVOKE (特定のデータベース利用者からすでに与えた権限を剥奪する)
- SET TRANSACTION (トランザクションモードの設定(並行トランザクションの分離レベル (ISOLATION MODE) など))
- BEGIN (トランザクションの開始)
- COMMIT (トランザクションの確定)
- ROLLBACK (トランザクションの取り消し)
- SAVEPOINT (任意にロールバック地点を設定する)
- LOCK (表などの資源を占有する)
カーソル定義・操作
「カーソル」とは、SELECT文などによるデータベース検索による検索実行の結果を1行ずつ取得して処理するために、データベースサーバ側にある結果集合と行取得位置を示す概念をいう。 カーソルの定義とその操作は、主にアプリケーションプログラムなどの手続き型言語からのSQL実行において利用する。
- DECLARE CURSOR (カーソル定義)
- OPEN (カーソルのオープン)
- FETCH (カーソルのポインタが指し示す位置の行データを取得し、ポインタを一行分進める。)
- UPDATE (カーソルのポインタが指し示す位置の行データを更新する)
- DELETE (カーソルのポインタが指し示す位置の行データを削除する)
- CLOSE (カーソルのクローズ)
カーソル宣言例
DECLARE CR1 CURSOR FOR SELECT CLMA, CLMB, CLMC FROM TBL1 WHERE CLMA BETWEEN :V開始値 AND :V終了値
※V開始値、V終了値は、埋め込み変数あるいはホスト変数と呼ばれ、埋め込みSQLの場合は、プログラム中のBEGIN DECLARE SECTION〜END DECLARE SECTIONの間で宣言する。
カーソルのオープン例
OPEN CR1
※カーソルのオープン前に、V開始値、V終了値には値を設定しておく。
行の取り出し例
FETCH CR1 INTO :V列A, :V列B, :V列C
検索条件に合致した行をすべて取り出すには、「データなし」になるまでFETCHを繰り返す。
※V列A, :V列B, :V列C は、埋め込み変数あるいはホスト変数と呼ばれ、埋め込みSQLの場合は、プログラム中のBEGIN DECLARE SECTION〜END DECLARE SECTIONの間で宣言する。
取り出した行の更新例
UPDATE TBL1 SET CLMB=CLMB+1, CLMC=:V列C更新値 WHERE CURRENT OF CR1
FETCHで位置付けた行を更新するには、UPDATE文でWHERE CURRENT OF カーソル名を指定する。
※V列C更新値は、埋め込み変数あるいはホスト変数と呼ばれ、埋め込みSQLの場合は、プログラム中のBEGIN DECLARE SECTION〜END DECLARE SECTIONの間で宣言する。
取り出した行の削除例
DELETE FROM TBL1 WHERE CURRENT OF CR1
FETCHで位置付けた行を削除するには、DELETE文でWHERE CURRENT OF カーソル名を指定する。
カーソルのクローズ例
CLOSE CR1
動的SQL
動的SQLは、通常SQL文をRDBMSに対して送信の度にデータベースエンジンで実行可能な内部中間コードに翻訳する作業を事前に行うことによって、翻訳済みSQLコードを再度利用してSQL解析のオーバーヘッドを削減することと、SQL文をソースコードで固定せずにデータベースへのアクセス毎に構文を書き換えたい場合に、有用である。データ操作言語 (DML) ももちろん実行できるが、データ定義言語 (DDL) のようにデータベース製品の機能アップによって新しい命令が追加されるものは、プリプロセッサの対応作業が重荷になるため、ほとんどのデータベース製品ではDDL文は動的SQLにて実行することが一般的となっている。
- PREPARE (文字列で与えたSQL文を解析・翻訳する)
- EXECUTE (PREPAREで翻訳したSQL文を実行する)
パラメタなし PREPARE PRESQL FROM 'DELETE FROM TBL1 WHERE CLMA=1' ↓ EXECUTE PRESQL パラメタあり(1回のPREPAREで、EXECUTEの繰り返し実行が可能) PREPARE PRESQL FROM 'DELETE FROM TBL1 WHERE CLMA=? AND CLMB=?' ↓ EXECUTE PRESQL USING :XCLMA,:XCLMB
埋め込みSQL
もともとカーソルは、埋め込みSQLでホスト言語(母言語)から結果集合を取得するために、都合のよい方法として考えられたものである。データベースと通信するためのリソースの割り当て確保や開放、1行ごとにホスト言語のループ処理で取得するための命令 (FETCH) などがある。
- ALLOCATE (DEALLOCATE) DESCRIPTOR (データベースとホスト言語(母言語)間での通信領域の確保と開放。)
- WHENEVER (エラー発生時の振る舞いを定義)
- SQLSTATE (SQL文実行後の状態が保存される領域)
<source lang="sql"> EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 77 XPARM PIC X(3). 01 XTBL1.
03 XCLMA PIC X(3). 03 XCLMB PIC X(10).
01 XTBL2.
03 XCLM1 PIC S9(5) COMP-3. 03 XCLM2 PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL DECLARE CR1 CURSOR FOR SELECT CLMA, CLMB FROM TBL1 WHERE CLMA>=:XPARM ORDER BY CLMA END-EXEC.
EXEC SQL WHENEVER SQLERROR GO TO ERR--PROC END-EXEC.
- SQLの静的実行(カーソル操作例)
MOVE 'ABC' TO XPARM. EXEC SQL OPEN CR1 END-EXEC. PERFORM TEST BEFORE UNTIL SQLCODE NOT = ZERO EXEC SQL FETCH CR1 INTO :XCLMA, :XCLMB END-EXEC IF SQLCODE = ZERO データ検索時の処理 END-IF END-PERFORM. IF SQLCODE = 100 EXEC SQL CLOSE CR1 END-EXEC END-EXEC.
- SQLの動的実行(?パラメタ使用)
EXEC SQL PREPARE PRESQL FROM 'INSERT INTO TBL2 (CLM1, CLM2) VALUES(?, ?)' END-EXEC. MOVE ZERO TO XCLM2. PERFORM TEST AFTER VARYING XCLM1 FROM 1 BY 1 UNTIL XCLM1 >= 10 EXEC SQL EXECUTE PRESQL USING :XCLM1, :XCLM2 END-EXEC END-PERFORM. GOBACK.
ERR--PROC.
例外処理
</source>
3値論理
SQLで用いられる論理値は、コンピュータの世界でもっとも広く利用されている2値論理 (TRUE, FALSE) ではなく、3値論理 (TRUE, FALSE, UNKNOWN) となっている。
主な SQL DBMS 実装
- Ingres (オープンソース、UNIX、Linux、Windows、Mac OS対応)
- Oracle Database (プロプライエタリ、UNIX、Linux、Windows対応)
- SQL/DS (VSE、VM/CMS)
- IBM DB2 (プロプライエタリ、AS/400、z/OS、UNIX、Linux、Windows対応)
- IBM Informix Dynamic Sever (プロプライエタリ、UNIX、Linux、Windows対応)
- Sybase Adaptive Server Enterprise (プロプライエタリ、UNIX、Linux、Windows対応)
- Microsoft SQL Server (プロプライエタリ、Windows対応)
- PostgreSQL (オープンソース、UNIX、Linux、Windows対応)
- MySQL (オープンソース、UNIX、Linux、Windows対応)
- InterBase (プロプライエタリ、Linux、Windows、Solaris、Mac OS X 対応)
- Firebird (オープンソース、Linux、Windows、Mac OS X 、Solaris、HP-UX対応)
関連項目
- 関係モデル (リレーショナルモデル)
- SQLインジェクション
脚注
Template:脚注ヘルプ <references/>
Template:Databasear:إس كيو إل be:SQL bg:SQL bn:এসকিউএল br:Areg SQL bs:SQL ca:Structured Query Language cdo:SQL cs:SQL da:Structured Query Language de:SQL el:SQL en:SQL eo:SQL es:SQL et:Struktuurpäringukeel eu:SQL fa:اسکیوال fi:SQL fr:Structured Query Language ga:Teanga Struchtúrtha Iarratas he:SQL hi:SQL hr:SQL hu:SQL ia:SQL id:SQL is:SQL it:SQL ko:SQL ku:SQL lt:SQL lv:SQL ml:എസ്.ക്യു.എൽ. ms:SQL nl:SQL no:Structured Query Language pl:SQL pt:SQL ro:SQL ru:SQL simple:Structured Query Language sk:SQL sl:SQL sq:SQL sr:SQL sv:SQL ta:சீக்வல் (க. வி. மொழி) tg:SQL th:ภาษาสอบถามเชิงโครงสร้าง tr:SQL uk:SQL vi:SQL yo:SQL zh:SQL