備忘録的プログラミングリファレンス

SQL リファレンス

　SQLは、リレーショナルデータベース（データベース）のデータ操作や定義を行うための問い合わせ言語です。データベース言語とも呼ばれます。
　リレーショナルデータベースは関係データベースとも呼ばれ、関連している複数のテーブルデータから構成されます。

　代表的なRDMSには、Oracle、MySQL、Microsoft SQL Server、PostgreSQL などがありますが、ここではオーブンソースである MySQL、PostgreSQL を取り上げます。

リレーショナルデータベースとは

　リレーショナルデータベース、 Relational Database Management System（RDMS）とは関連している複数のテーブルデータで構成されるデータベースの一種です。

テーブル（表）のリレーション
関連付けられたテーブル

　データベースはリレーションという技術で複数のテーブル（表）を関連付けることができ汎用性をもちます。さらに、データベースには web アプリケーションや他のプログラムと連携するといったことができ、大規模なシステムに向いています。

　リレーショナルデータベースの詳しくはリレーショナルデータベースについてを参照してください。

SQL

　SQL は、リレーショナルデータベース（データベース）のデータ操作や定義を行うための問い合わせ言語です。データベース言語とも呼ばれます。

SQL ( SELECT 文 )
SELECT * FROM goods;

　データベースは、データを収めるための複数のテーブル、ビュー、トランザクションやトリガといったスクリプトを統括し管理します。

　SQL はデータベースのための言語で、データベースのテーブルやビューの定義、トランザクションやトリガといったスクリプトの定義、データの作成や削除といったことを行うことができます。
　データベースを管理するための言語が必ずしも SQL であるとは限りませんが、その利便性のよさから長い間利用されてきています。

　SQL で定義されたコードは SQL 文と呼ばれます。
　SQL 文はデータベースやテーブル、インデックスを作成するための SQL スキーマ文、データの作成、再編集、削除するための SQL 文に分類されることがあります。データの操作をする SQL 文は SQL データ文と呼ばれたりもします。

　以下は売上を記録するためのテーブルを作成する例です。

SQL スキーマ文( テーブルの作成 )
CREATE TABLE goods;

　テーブルを作成する前にデータベースを作成しておく必要があります。

　以下は SQL データ文の例で、goods テーブルに新規でデータを作成します。

SQL データ文( データの新規作成 )
INSERT INTO goods ( name, unit_cost ) VALUES ( 'candy', 3 );

　さらにトランザクションやトリガといったスクリプトを定義している SQL 文は SQL トランザクション文と呼ばれることがあります。

　SQL は非手続き型言語と呼ばれ、C、Java、Basic のようにプログラムの実行を制御するような記述方法ではありません。
　SQL 文はオプティマイザー（ optimizer ）と呼ばれる機能によっての SQL の実行が最適化されます。最適化における条件はオプティマイザーヒント（ optimizer hint ）と呼ばれ、最適化の条件を指定することができます。
　詳しくは各データベースのオプティマイザーの仕様を参照してください。

SQL スキーマ文

　SQL スキーマ文にはデータベースやテーブル、インデックスを作成するための SQL 文が含まれます。

データベースの作成

　データベースは、実際にデータを収める複数のテーブルやテーブルの集合体であるスキーマ、ビュー,トリガといったスクリプトを統括し管理します。 まずはその統括し管理するためのデータベースを作成することから始めます。
　データベースを作成するには CREATE　DATABASE 文を使用します。しかし、この SQL スキーマ文は標準SQLにはありません。データベースの実装に依存しますのでお使いのデータベースを参照してください。

　簡単にデータベースを作成するには以下の構文を使います。データベースオーナーはログインしているユーザーに成ります。

データベース作成例
> CREATE DATABASE example;

　データベースを作成したら、そのデータベース内でテーブルを作成していきます。

　詳しくはデータベースの作成ページを参照してください。

テーブルの作成

　テーブルを作成、変更、削除する方法がSQL スキーマ文に含まれます。

　例えばテーブルを作成するには CREATE TABLE 文を使用します。

TABLE の作成
CREATE TABLE example (
	id	SERIAL,				/* 番号 */
	name	VARCHAR(30)			/* 商品名 */
);

　詳しくはテーブルの作成　CREATE TABLEページを参照してください。

　SQL スキーマ文によって、データベースやテーブルなどを作成するとデータベースを構成するに必要なデータが作成または更新されます。
　データベースを構成するのに必要な要素はテーブル群としてデータディクショナリ（ data dictionary ）に格納されます。そのようなデータベースに必要なデータをメタデータ（metadata）とも呼ばれます。
　メタデータは SELECT 文で参照することができます。詳しいことは各データベースの仕様を参照してください。

SQL データ文

　SQL データ文は主にテーブルのデータの閲覧、新規作成、更新、削除を行う SQL 文を指します。

　データの閲覧は SELECT 文、新規作成は INSERT　文、更新は UPDATE 文、削除は DELETE 文を使用します。これらの SQL 文はクエリ（問い合わせ）文などとも呼ばれます。

データの閲覧

　データの閲覧は SELECT 文を使用します。

SELECT 文
SELECT * FROM example;

　上記の例は example テーブルの全てのデータを返してきます。WHERE 句を追加することでデータのフィルタリング（抽出）を行うことができます。

SELECT 文
SELECT * FROM example WHERE id=0;

　上記の例は id=0 である行のデータのみを返します。

　詳しくはSELECTページを参照してください。

データの新規作成

　データを新規作成するには INSERT 文を使用します。

INSERT 文
INSERT INTO example ( name ) VALUES ( 'candy' );

　上記の例は example テーブルに　name = 'candy' である行を追加しています。id は SERIAL 型であるため上記の例が最初の行なら i d= 0 になります。

　詳しくはINSERTページを参照してください。

データの更新

　データの更新には UPADTE 文を使用します。

UPDATE 文
UPDATE example SET name = 'cookie' WHERE id=0;

　上記の例は id = 0 である行の name を cookie に更新します。

　UPDATE は WHERE 句で行を指定しないと全ての行を更新してしまいますので注意してください。

　詳しくはUPDATEページを参照してください。

データの削除

　データの削除には DELETE 文を使用します。

DELETE 文
DELETE FROM example WHERE id=0;

　上記の例は id = 0 である行を削除します。

　DELETE は WHERE 句で行を指定しないと全ての行を削除してしまいますので注意してください。

　詳しくはDELETEページを参照してください。

　UPDATE や DELETE には WHERE 句を付けてフィルタリングをしないと全てのデータを更新または削除するので注意が必要です。

　SQL データ文はクエリ文とも呼ばれます。

クエリの実行

　API などの外部からデータベースに接続する場合には、ユーザー認証の後にクエリのパーミッションによる操作許可やクエリが正しいかがチェックされます。このことが一連の動作として実行されます。
　クエリが正しいかがチェックされたら次にクエリオプティマイザ (query optimizer) に渡され、クエリ最適化が行われます。その後にクエリが実行されます。

　以上のことはデータベースのチューニングや API などの開発時には気に留めておく必要があります。

結果セット

　データベースはクエリの実行が終わると結果セット（result set）を返してきます。
　結果セットとはクエリの実行結果で、一覧やエラーメッセージのことです。

mysql.user テーブルの閲覧の結果セット
mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
エラー表示
mysql> show tables;
ERROR 1046 (3D000): No database selected

　それぞれのクエリは対象となった行を返します。API によっては特殊な ID を返す場合もあります。

MySQL

　MySQL ではデータベースの管理を SQL 文で行います。mysql といったコマンドラインなどのインターフェイスからデータベースを操作します。

　インストール直後の初期状態ではrootユーザーでデータベースの管理を行います。

$ sudo mysql

　ユーザー名はユーザー名とユーザーの接続元の組み合わせ 'username'@'host' です。ユーザー名と接続元との組み合わせでデータベース接続の認証をします。
　ユーザーごとにデータベースの操作権限を設定します。操作権限はロールを利用することもできます。

　詳しくは「MySQL」ページを参照して下さい。

PostgreSQL

　PostgreSQL の特徴的なのは postmaster と psql です。
　PostgreSQL データベースの入出力は postmaster が担います。 PostgreSQL のインターフェイスとして psql コマンドラインを使用します。psql は PostgreSQL のためのコマンドラインインターフェイスです。

$ psql

　PostgreSQL ではユーザーはロールとして扱います。
　MySQL はロールはユーザーにデータベース操作権限を設定するために利用するものですが、PostgreSQL ではロールがユーザーの代わりをし、ユーザーとロールを分けていません。

　詳しくは「PostgreSQL」ページを参照して下さい。