SQL ゼロからはじめるデータベース操作のアウトプット1

前回、データベース設計を学びましたがデータベースを操作できなければ意味がないと思いSQLを学びました。 今回は自分用の備忘録としてアウトプットしていきます。

おさらいですが「データベースとは何を指すでしょうか？」 ここでは「大量の情報を保存し、コンピュータから効率よくアクセスできるように加工したデータの集まりのこと」としています。 又、データベースを管理するコンピュータシステムのことを、「データベース管理システム（DBMS）」と呼びます。 DBMSにはいろんな種類がありますが今回は現在最も広く利用されているリレーショナルデータベース（関係データベース）を学びます。 代表的なリレーショナルデータベースとしては以下の5つがあります。
Oracle Database：Oracle社のRDBMS
SQL Server：Microsoft社のRDBMS
DB2：IBM社のRDBMS
PostgreSQL（ポストグレスキューエル）：オープンソースのRDBMS
MySQL：オープンソースのRDBMS（2010年からOracle社が開発元）

SQLを学ぶ上で「標準SQL」も頭に入れておいた方が良いかもしれません。
ISO（国際標準化機構）に準拠したSQLのことです。以前はRDBMSごとの「方言」でSQL分を書いていました。実務においても方言で書かれたSQL文に遭遇することもあるので読めるようにはしておいた方が良いみたいです。

SQL文の種類

DDL（データ定義言語）
CREATE：データベースやテーブルなどを作成する
DROP：データベースやテーブルなどを削除する
ALTER：データベースやテーブルなどの構成を変更する

DML（データ操作言語）
SELECT：テーブルから行を検索する
INSERT：テーブルに新規行を登録する
UPDATE：テーブルの行を更新する
DELETE：テーブルの行を削除する

DCL（データ制御言語）
COMMIT：データベースに対して行った変更を確定する
ROLLBACK：データベースに対して行った変更を取り消す
GRANT：ユーザに操作の権限を与える
REVOKE：ユーザから操作の権限を奪う

SQLの基本的な記述ルール

・文末に「;」（セミコロン）をつける ・大文字、小文字は区別されない SELECTもslectも同じように解釈されます。書内では以下のルールで記述しています。
・キーワードは大文字
・テーブル名は頭文字のみ大文字
・その他（列名など）は小文字

その他留意点

SQL文の中にじかに書く文字列や日付、数値を定数と呼びます。
文字列や日付は'abc'のように「'」（シングルクォーテーション）で囲む
一方、数値は記号で囲む必要は無い。
単語は半角スペースか改行で区切る。

実践

以後は構文と例を用いて実際のSQL文を記述していきます。

データベースの作成

CREATE DATABASE <テーブル名>;

例

CREATE DATABASE shop;

テーブルの作成

構文

CREATE TABLE <テーブル名>
(<列名1> <データ型> <この列の制約>,  
 <列名2> <データ型> <この列の制約>,  
                          ・ 
                          ・
 <このテーブルの制約1>, <このテーブルの制約2>,......);  

例

CREATE TABLE Shohin
(shohin_id           CHAR(4)        NOT NULL,
 shohin_mei        VARCHAR(100)      NOT NULL,
 shohin_bunrui    VARCHAR(32)       NOT NULL,
 hanbai_tanka     INTEGER                ,
 shiire_tanka       INTEGER              ,
 torokubi              DATE               ,
 PRIMARY  KEY (shohin_id));

尚、命名のルールとして
データベースやテーブル、列などの名前に使って良い文字は次の3種類
・半角のアルファベット
・半角の数字
・アンダーバー（_）
又、名前の最初の文字は「半角のアルファベット」にすること。
1shohinなど数字で始めることは出来ません。

テーブルの削除

DROP TABLE <テーブル名>;

例

DROP TABLE Shohin;

※削除したテーブルとそのデータは復活できませんので注意が必要です。

テーブル定義の変更

ALTER TABLE <テーブル名> ADD COLUMN <列の定義>;

例

ALTER TABLE Shohin ADD COLUMN shohin_mei_kana VARCHAR(100);

これはShohinテーブルにshohin_mei_kana（商品名（カナ））という100桁の可変長文字列を入れる列を追加することを指します。
又、テーブルの列を削除する際には以下となります。

ALTER TABLE <テーブル名> DROP COLUMN <列名>;

テーブルへのデータ登録

-- DML :データ登録
BEGIN TRANSACTION;

INSERT INTO Shohin VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Shohin VALUES ('0002', '穴あけパンチ', '事務用品', 500, 320, '2009-09-11');
                       ・
                       ・
                       ・
INSERT INTO Shohin VALUES ('0008', 'ボールペン', '事務用品', 100, NULL, '2009-11-11');

COMMIT;

テーブルの訂正

ALTER TABLE Sohin RENAME TO Shohin;

ALTER TABLE <変更前の名前> TO <変更後の名前>とする。

SELECT文の基本

SELECT <列名>, ・・・・
  FROM <テーブル名>;

例

SELECT shohin_id, shohin_mei, shiire_tanka
  FROM Shohin;

全ての列を出力する場合は

SELECT *（アスタリスク）
  FROM <テーブル名>;

列に別名をつける（後から頻出する）

ASキーワードを使って、列に別名をつけることができる。

SELECT shohin_id AS id,
    shohin_mei AS namae,
    shiire_tanka AS tanka
  FROM Shohin;

尚、別名には日本語を使うこともできる。その場合は別名をダブルクォーテーション（")で囲む。

SELECT shohin_id  AS "商品ID",
     shohin_mei AS "商品名",
      shiire_tanka AS "仕入単価"
  FROM Shohin;

結果から重複行を省く

SELECT DISTINCT shohin_bunrui
  FROM Shohin;

SELECT句でDISTINCTを使う。
重複行を省く際、複数の行にNULLがある場合は1つのNULLとして扱われる。
又、

SELECT DISTINCT shohin_bunrui, torokubi
  FROM Shohin;

とすれば2つの行の組み合わせで全く同じものを省く。

WHERE句による行の選択

SELECT <列名>, ・・・
  FROM <テーブル名>
WHERE <条件式>;

とするとWHERE句に指定した条件に合う行だけが選択される。
例

SELECT shohin_mei, shohin_bunrui
  FROM Shohin
WHERE shohin_bunrui = '衣服';

処理の順番はWHERE句で指定した条件に合う行をまず選択し、その後にSELECT句で指定された列を返す。句の順番を入れ替えるとエラーになるので注意。

コメントの書き方

1行コメント
「--」の後に記述する。
複数行のコメント
「/」と「/」で囲む。
例

-- このSELECT文は、結果から重複を無くします。
SELECT DISTINCT shohin_id, shiire_tanka
  FROM Shohin;

例2

/* このSELECT文は、
  結果から重複を無くします。*/
 SELECT DISTINCT shohin_id, shiire_tanka
   FROM Shohin;

※例では冒頭にコメントを入れていますがSQL文の途中に差し込むこともできます。

算術演算子と比較演算子

まずは算術演算子から
SQL文の中には+,-,*,/を用いて計算ができる。
しかし、NULLには注意が必要でNULLが含まれる計算は、全てNULLになる。
5 + NULL = NULLなど。
次に比較演算子
・ = : 〜と等しい
・ <> : 〜と等しくない
・ >= : 〜以上
・ > : 〜より大きい
・ <= : 〜以下
・ < : 〜より小さい
これらの比較演算子は文字、数値、日付など、ほぼ全てのデータ型の列、値を比較することができる。
例

SELECT shohin_mei, shohin_bunrui, hanbai_tanka
  FROM Shohin
 WHERE hanbai_tanka >= 1000;

尚、以下のように計算した結果を比較演算子で比較することも可能。

SELECT shohin_mei, hanbai_tanka, shiire_tanka
  FROM Shohin
 WHERE hanbai_tanka - shiire_tanka >= 500;

※NULLは比較演算子の対象になりません。
NULLである行を選択する際には

SELECT shohin_mei, shiire_tanka
  FROM Shohin
 WHERE shiire_tanka IS NULL;

NULLでない行を選択する際には

SELECT shohin_mei, shiire_tanka
  FROM Shohin
 WHERE shiire_tanka IS NOT NULL;

論理演算子について

NOT演算子
例

SELECT shohin_mei, shohin_bunrui, hanbai_tanka
  FROM Shohin
WHERE NOT hanab_tanka >= 1000;

これは

SELECT shohin_mei, shohin_bunrui
  FROM Shohin
WHERE hanbai_tanka < 1000;

と同じ。NOT演算子を使わなくても良い場面も多く、あまり使われません。

AND演算子

SELECT shohin_mei, shiire_tanka
  FROM Shohin
WHERE shohin_bunrui = 'キッチン用品'
  AND hanbai_tanka >= 3000;

のように使います。両辺の条件が成り立つものを結果として返します。
対してOR演算子はANDと同じように使いますが、
両辺の条件のうち、どちらか一方、あるいは両方が成り立つものを結果として返します。
※ANDはORより強いので、ORを優先させるときは()で囲い込みます。
例

SELECT shohin_mei, shohin_bunrui, torokubi
  FROM Shohin
WHERE shohin_bunrui = '事務用品'
  AND ( torokubi = '2009-09-11'
    OR torokubi = '2009-09-20');

これで「登録日が2009-09-11または2009-09-20」かつ「商品分類が事務用品」のものを検索します。

テーブルを集約して検索する

集約関数
COUNT：テーブルのレコード数（行数）を数える
SUM：テーブルの数値列のデータを合計する
AVG：テーブルの数値列のデータを平均する
MAX：テーブルの任意の列のデータの最大値を求める
MIN：テーブルの任意の列のデータの最小値を求める
例

SELECT COUNT(*)
  FROM Shohin;

と用いる。 例（販売単価の合計を求める）

SELECT SUM(hanbai_tanka)
  FROM Shohin;

例2（販売単価と仕入単価の合計を求める）

SELECT SUM(hanbai_tanka), SUM(shiire_tanka)
  FROM Shohin;

２つあるときは「,」で区切る。 ※集約関数はNULLを除外する。ただし「COUNT(*)」は例外的にNULLを除外しない。 他の集約関数も使い方は同様。

重複値を除外する場合

SELECT COUNT(DISTINCT shohin_bunrui)
  FROM Shohin;

というように引数内にDISTINCTを用いる。

テーブルをグループに切り分ける

GROUP BY句による集約

SELECT <列名1>,<列名2>,<列名3>,・・・
  FROM <テーブル名>
 GROUP BY <列名1>,<列名2>,<列名3>,・・・・;

例えば商品分類ごとの行数を数える際

SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui;

となる。 ※集約キー(GROUP BY句の引数）にNULLが含まれている場合一括してNULLというグループに分類される。
SELECT文の記述順
1.SELECT→2.FROM→3.WHERE→4.GROUP BY

実行順序は記述順と異なり
FROM→WHERE→GROUP BY→SELECT
なので、

SELECT shiire_tanka, COUNT(*)
  FROM Shohin
 WHERE shohin_bunrui = '衣服'
 GROUP BY shiire_tanka;

ではまず商品分類=衣服の行に絞り込まれてから仕入れ単価ごとにグループ分けされる。

間違えやすい点

①SELECT句に余計な列を書いてしまう。
COUNTのような集約関数を使った場合、SELECT句に書くことができる要素が非常に限定され、以下の3つしかSELECT句に書けない
・定数（123という数値や'テスト'という文字列）
・集約関数
・GROUP BY句で指定した列名（つまり集約キー）
「GROUP BY句を使うときは、SELECT句に集約キー以外の列名を書けない」と覚えておく
例えば 　

SELECT shohin_mei, shiire_tanka, COUNT(*)
  FROM Shohin
 GROUP BY shiire_tanka;

はエラーになる。 shohin_meiという列名は、GROUP BY句にないので。

②GROUP BY句に列の別名を書いてしまう。 ASで別名をつけることができるが、それをGROUP BY句で使うとエラーになる。

SELECT shohin_bunrui AS sb, COUNT(*)
  FROM Shohin
 GROUP BY sb;

のようにしても処理順序がGROUP BY→SELECTなので、SELECT句で別名をつけてるsbはGROUP BY句の時点では存在しないのでエラーになる。

③WHERE句に集約関数を書いてしまう。
集約関数を書ける場所はSELECT句とHAVING句（とORDER BY句）だけ

集約した結果に条件を指定する(HAVING句)

集約した結果に対して条件指定をしたい。
条件指定といえばWHERE句が出てくるかもしれないがWHERE句はレコード（行）に対してしか条件を指定できない。
グループに対する条件指定（例「含まれる行数が2行」「平均値が500」など）はHAVING句を用いる。
構文

SELECT <列名1>,<列名2>,<列名3>,・・・
  FROM <テーブル名>
 GROUP BY <列名1>,<列名2>,<列名3>,・・・
HAVING <グループの値に対する条件>

例（商品分類で集約したグループに対して、「含まれる行数が2行」という条件を指定する

SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui
HAVING COUNT(*) = 2;

HAVING句に書ける要素

・定数
・集約関数
・GROUP BY句で指定した列名（つまり集約キー）

検索結果を並び替える（ORDER BY句）p107~

ORDER BY句を用いる
構文

SELECT <列名1>,<列名2>,<列名3>,・・・
  FROM <テーブル名>;
 ORDER BY <並べ替えの基準となる列1>,<並び替えの基準となる列2>,・・・

例（販売単価の低い順、つまり昇順に並べる場合）

SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka
  FROM Shohin
ORDER BY hanbai_tanka;

※ORDER BY句に書く列名は「ソートキー」と呼びます。

句の記述順序
SELECT句→FROM句→WHERE句→GROUP BY句→HAVING句→ORDER BY句

昇順と降順の指定

昇順に対して降順に並べる場合は列名の後ろにDESCキーワードを用います。

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
  FROM Shohin
 ORDER BY hanbai_tanka DESC;

この場合販売単価の高い順に並べます。
実は昇順に並べる際にもASCというキーワードがあるのですが、省略した場合は暗黙に昇順に並べるという約束になってます。
※NULLを含む列をソートキーにした場合、NULLは先頭又は末尾にまとめて表示されます。
※ORDER BY句では、SELECT句に含まれていない列や集約関数も使える。