SQL リファレンス
SQLは、リレーショナルデータベース(データベース)のデータ操作や定義を行うための問い合わせ言語です。データベース言語とも呼ばれます。
リレーショナルデータベースは関係データベースとも呼ばれ、関連している複数のテーブルデータから構成されます。
代表的なRDMSには、Oracle、MySQL、Microsoft SQL Server、PostgreSQL などがありますが、ここではオーブンソースである MySQL、PostgreSQL を取り上げます。
リレーショナルデータベースとは
リレーショナルデータベース、 Relational Database Management System(RDMS)とは関連している複数のテーブルデータで構成されるデータベースの一種です。
データベースはリレーションという技術で複数のテーブル(表)を関連付けることができ汎用性をもちます。さらに、データベースには web アプリケーションや他のプログラムと連携するといったことができ、大規模なシステムに向いています。
リレーショナルデータベースの詳しくはリレーショナルデータベースについて
を参照してください。
SQL
SQL は、リレーショナルデータベース(データベース)のデータ操作や定義を行うための問い合わせ言語です。データベース言語とも呼ばれます。
データベースは、データを収めるための複数のテーブル、ビュー、トランザクションやトリガといったスクリプトを統括し管理します。
SQL はデータベースのための言語で、データベースのテーブルやビューの定義、トランザクションやトリガといったスクリプトの定義、データの作成や削除といったことを行うことができます。
データベースを管理するための言語が必ずしも SQL であるとは限りませんが、その利便性のよさから長い間利用されてきています。
SQL で定義されたコードは SQL 文と呼ばれます。
SQL 文はデータベースやテーブル、インデックスを作成するための SQL スキーマ文、データの作成、再編集、削除するための SQL 文に分類されることがあります。データの操作をする SQL 文は SQL データ文と呼ばれたりもします。
以下は売上を記録するためのテーブルを作成する例です。
テーブルを作成する前にデータベースを作成しておく必要があります。
以下は SQL データ文の例で、goods テーブルに新規でデータを作成します。
さらにトランザクションやトリガといったスクリプトを定義している SQL 文は SQL トランザクション文と呼ばれることがあります。
SQL は非手続き型言語と呼ばれ、C、Java、Basic のようにプログラムの実行を制御するような記述方法ではありません。
SQL 文はオプティマイザー( optimizer )と呼ばれる機能によっての SQL の実行が最適化されます。最適化における条件はオプティマイザーヒント( optimizer hint )と呼ばれ、最適化の条件を指定することができます。
詳しくは各データベースのオプティマイザーの仕様を参照してください。
SQL スキーマ文
SQL スキーマ文にはデータベースやテーブル、インデックスを作成するための SQL 文が含まれます。
データベースの作成
データベースは、実際にデータを収める複数のテーブル、ビュー,トリガといったスクリプトを統括し管理します。 データベースサーバーはそのようなデータベースを複数管理します。開発者は用途ごとにデータベースを作成していきます。
まずはテーブル、ビュー、トリガーといったスクリプトを統括し管理するためのデータベースを作成することから解説します。
データベースを作成するには CREATE DATABASE 文を使用します。しかし、この SQL スキーマ文は標準SQLにはありません。データベースの実装に依存しますのでお使いのデータベースを参照してください。
簡単にデータベースを作成するには以下の構文を使います。データベースオーナーはログインしているユーザーに成ります。
上記の例では在庫管理のためのデータベースを作成しています。
データベースを作成したら、そのデータベース内でテーブルを作成していきます。
PostgreSQL にようにデータベースによっては、データベースをさらに分割したスキーマもデータベースで管理する場合もあります。
詳しくはデータベースの作成
ページを参照してください。
テーブルの作成
テーブルを作成、変更、削除する方法がSQL スキーマ文に含まれます。
例えばテーブルを作成するには CREATE TABLE
文を使用します。
上記の例では商品名とその単価を記録できるようなテーブルを作成しています。
詳しくはテーブル
ページを参照してください。
データタイプ
テーブルを定義する上で特に気にしなければならないものにデータタイプがあります。テーブル定義においてカラム名と伴にそのデータタイプを決めておかなればなりません。
データタイプには文字列、数値、日付・時刻などがあります。通貨や幾何データ型、画像や音といったバイナリデータを扱う型などもありますがデータベースによって違いがありますので詳しくは調べてみてください。
タイプ名 | 概要 |
---|---|
文字列型 | |
CHAR() | 固定長。255 文字まで。引数に文字数を指定。指定数の容量を消費する |
VARCHAR() | 可変長。255 文字まで。引数に文字数を指定。入るデータ分のみ容量を消費 |
TEXT | 65,535 文字まで |
数値型 | |
INT | SIGNED、-2,147,483,648 〜 2,147,483,647。UNSIGNED、0 〜 4,294,967,295 |
BIGINT | SIGNED、-9,223,372,036,854,775,808 〜 9,223,372,036,854,775,807。UNSIGNED、0 〜 18,446,744,073,709,551,615 |
DECIMAL | 通貨に用いる。ユーザ指定精度、正確。小数点前までは131072桁、小数点以降は16383桁。MySQL では 65 桁 |
REAL | 可変精度。不正確。6桁精度 |
DOUBLE PRECISION | 可変精度。不正確。15桁精度 |
SERIAL | 連番型。自動増分。1 ~ 4,294,000,000 |
日付・時間型 | |
DATE | YYYY-MM-DD。1000-01-01 〜 9999-12-31 |
TIMESTAMP | YYYY-MM-DD HH:MI:SS。1970-01-01 00:00:00 〜 2037-12-31 23:59:59 |
TIME | HHH:MI:SS。-838:59:59 〜 838:59:59 |
データタイプに関して詳しくはテーブル
ページのデータ型
を参照してください。
SQL スキーマ文によって、データベースやテーブルなどを作成するとデータベースを構成するに必要なデータが作成または更新されます。
データベースを構成するのに必要な要素はテーブル群としてデータディクショナリ( data dictionary )に格納されます。そのようなデータベースに必要なデータをメタデータ(metadata)とも呼ばれます。
メタデータは SELECT 文で参照することができます。詳しいことは各データベースの仕様を参照してください。
テーブルの変更
テーブル内容の変更には ALTER TABLE コマンドを使います。テーブルに属するカラムの変更も ALTER TABLE コマンドを使います。
上記の例では、ALTER TABLE
はテーブルの内容を変更することを示しています。次のALTER COLUMN .. TYPE ..
はテーブルに属するカラムのデータタイプを変更することを示しています。
ALTER TABLE に関して詳しくはテーブル内容の変更
ページを参照してください。
SQL データ文
SQL データ文は主にテーブルのデータの閲覧、新規作成、更新、削除を行う SQL 文を指します。
データの閲覧は SELECT 文、新規作成は INSERT 文、更新は UPDATE 文、削除は DELETE 文を使用します。これらの SQL 文はクエリ(問い合わせ)文などとも呼ばれます。
データの閲覧 SELECT 文
データの閲覧は SELECT 文を使用します。
例えば以下のような SQL 文を作成します。
上記の例は goods テーブルの全てのデータを参照することができます。
さらに、WHERE 句を追加することでデータのフィルタリング(抽出)を行うことができます。
上記の例は id = 0 である行のデータのみを返します。
詳しくはSELECT
ページを参照してください。
データの新規作成 INSERT 文
データを新規作成するには INSERT 文を使用します。
上記の例は example テーブルに name = 'candy' である行を追加しています。id は SERIAL 型であるため上記の例が最初の行なら i d= 0 になります。
詳しくはINSERT
ページを参照してください。
データの更新 UPDATE 文
データの更新には UPADTE 文を使用します。
上記の例は id = 0 である行の name を cookie に更新します。
UPDATE は WHERE 句で行を指定しないと全ての行を更新してしまいますので注意してください。
詳しくはUPDATE
ページを参照してください。
データの削除 DELETE 文
データの削除には DELETE 文を使用します。
上記の例は id = 0 である行を削除します。
DELETE は WHERE 句で行を指定しないと全ての行を削除してしまいますので注意してください。
詳しくはDELETE
ページを参照してください。
UPDATE や DELETE には WHERE 句を付けてフィルタリングをしないと全てのデータを更新または削除するので注意が必要です。
SQL データ文はクエリ文とも呼ばれます。
クエリの実行
API などの外部からデータベースに接続する場合には、ユーザー認証の後にクエリのパーミッションによる操作許可やクエリが正しいかがチェックされます。このことが一連の動作として実行されます。
クエリが正しいかがチェックされたら次にクエリオプティマイザ (query optimizer) に渡され、クエリ最適化が行われます。その後にクエリが実行されます。
以上のことはデータベースのチューニングや API などの開発時には気に留めておく必要があります。
結果セット
データベースはクエリの実行が終わると結果セット(result set)を返してきます。
結果セットとはクエリの実行結果で、一覧やエラーメッセージのことです。
それぞれのクエリは対象となった行を返します。API によっては特殊な ID を返す場合もあります。
スキーマの作成
PostgreSQL といったデータベースではスキーマといって、データベースをさらに分割することができます。スキーマにはデータベースと同じように複数のテーブル、ビューやトリガといったスクリプトを統括することができます。
スキーマはデータベースの用途をさらに分割する役割を果たします。ただし、スキーマにスキーマを含めることができません。
PostgreSQLではデータベース間のデータの参照はできません。そのような場合はスキーマで分割した方がよい場合もあります。
例えば在庫管理や商品管理、顧客管理や売上管理といったデータベースを作成したい場合に PostgreSQL のようなデータベースではそれぞれでデータベースを作成していまうとお互いのデータを直接参照できません。
在庫管理と商品管理、顧客管理と売上管理はお互いにデータを内部参照する可能性が高く互いにデータを参照できないと不都合があります。そこで在庫管理スキーマ、商品管理スキーマ、顧客管理スキーマ、売上管理スキーマとすると直接データを参照することができます。
スキーマの作成には以下のように CREATE SCHEMA 文を使用します。
スキーマ名を明示しない場合は public スキーマ以下に作成されます。
スキーマはデータベースにおいて構造的にデータベースを設計するという概念です。MySQL にはスキーマは概念でありスキーマというオブジェクトは存在していません。
MySQL ではデータベース同士のデータを直接参照することができます。データベース名を指定するにはスキーマのように参照します。
データベース名をスキーマ部分に指定するとそのデータベースのテーブルとして作成されます。上記の例では、db_name データベース以下に example テーブルを作成します。
MySQL ではすべてデータベース同士を直接参照することができます。スキーマという管理方法がありません。
MySQL ではひとつのサーバーでひとつのデータベースサーバーしか扱えません。PostgreSQL ではひとつのサーバーで複数のデータベースを扱うことができます。
例えば、MySQL ではひとつのサーバーで1つの会社のデータベースしか管理できませんが、PostgreSQL ではひとつのサーバーで複数の会社のデータベースを扱うことができます。
別の例えでは、MySQL サーバーではコミュケーション用データベースと在庫管理といった事業用データベースを混在させないほうがよいです。PostgreSQL ではデータベースごとに作成すればよく、混在することができます。
サブクエリ(副問合せ)
サブクエリとは、以下のように SELECT、INSERT、UPDATE、DELETE といったクエリの中で SELECT といったクエリを実行するものです。
サブクエリは必ずカッコ(
と)
で囲み先に実行させます。ここでは、サブクエリを含むクエリをメインクエリと呼ぶことにします。
SELECT によってデータを抽出する際に、保存されたデータを検索条件にしたいときや取得したいデータに平均値や最大値を含めたいときにサブクエリを利用します。
詳しくはサブクエリ ( 副問合せ )
ページを参照してください。
テーブルの結合
テーブルの結合は JOIN 句を使用して複数のテーブルを組み合わせることができます。
多くの場合は、組み合わせに条件を設定する ON を付加します。
テーブルの結合には外部結合と内部結合があります。JOIN 句はデフォルトの設定では内部結合です。
JOIN 句の代わりに INNER JOIN 句によって明確に内部結合であることを指定することもできます。
詳しくはテーブルの結合 JOIN
ページを参照してください。
MySQL
MySQL ではデータベースの管理を SQL 文で行います。mysql といったコマンドラインなどのインターフェイスからデータベースを操作します。
インストール直後の初期状態ではroot
ユーザーでデータベースの管理を行います。
ユーザー名はユーザー名とユーザーの接続元の組み合わせ 'username'@'host' です。ユーザー名と接続元との組み合わせでデータベース接続の認証をします。
ユーザーごとにデータベースの操作権限を設定します。操作権限はロールを利用することもできます。
詳しくは「MySQL」ページを参照して下さい。
PostgreSQL
PostgreSQL の特徴的なのは postmaster と psql です。
PostgreSQL データベースの入出力は postmaster が担います。
PostgreSQL のインターフェイスとして psql コマンドラインを使用します。psql は PostgreSQL のためのコマンドラインインターフェイスです。
PostgreSQL ではユーザーはロールとして扱います。
MySQL はロールはユーザーにデータベース操作権限を設定するために利用するものですが、PostgreSQL ではロールがユーザーの代わりをし、ユーザーとロールを分けていません。
詳しくは「PostgreSQL」ページを参照して下さい。