[PostgerSQL] データベースの基本操作とSQL

SQLをちゃんと学んだことがなかったため以下の書籍を元に勉強しました。

SQL　ゼロからはじめるデータベース操作　著者：ミック

初心者でも内容が分かりやすく書かれているのでおすすめです！

PostgerSQLコマンド

サーバースタート
pg_ctl -D /usr/local/var/postgres start

サーバー接続
psql postgres

サーバーストップ
pg_ctl -D /usr/local/var/postgres stop

SQLの文とその種類

DDL（DataDefinitionLanguage）

DDL（データ定義言語）は、データを格納する入れ物であるデータベースやテーブルなどを作成したり削除したりします。DDLに分類される命令は次のとおりです。

• CREATE：データベースやテーブルなどを作成する
• DROP：データベースやテーブルなどを削除する
• ALTER：データベースやテーブルなどの構成を変更する

DML（DataManipulationLanguage）

DML（データ操作言語）は、テーブルの行を検索したり変更したりします。DMLに分類される命令は次のとおりです。

• SELECT：テーブルから行を検索する
• INSERT：テーブルに新規行を登録する
• UPDATE：テーブルの行を更新する
• DELETE：テーブルの行を削除する

ポイント

• SQL文はセミコロン（;）で終わる。
• キーワードの大文字・小文字は区別されない。
• 文字列と日付の定数はシングルクォーテーション（'）で囲む。
• 数値の定数は囲まない（数値だけを書く）。
• データベースやテーブル、列に使っていい文字
• 半角のアルファベット
• 半角の数字
• アンダーバー(_)
• 名前の最初は半角アルファベットを使用すること

データ型の種類

• INTEGER型
• 整数を入れる列に指定するデータ型（数値型）です。小数は入れられません。
• CHAR型
• CHARはCHARACTER（文字）の略で、文字列を入れる列に指定するデータ型（文字列型）です。CHAR(10)やCHAR(200)のように、列の中に入れることのできる文字列の長さ（最大長）をカッコ()で指定します。
• VARCHAR型
• CHAR型と同じく文字列を入れる列に指定するデータ型（文字列型）で、やはり、列の中に入れることのできる文字列の長さ（最大長）をカッコで指定します。ただし、こちらは可変長文字列という形式で、列の中に文字列が入ります。
• DATA型
• 日付（年月日）を入れる列に指定するデータ型（日付型）です。

制約の設定

制約とは、データ型のほかに、列に入れるデータに制限や条件を追加する機能です。
NOTNULL制約の設定です。NULLとは「無記入」状態であることを表わすキーワードです。この場合、必ずデータが入るようにしなければいけません。
プライマリキーは、一つの行を特定できる列のことです。

テーブルの削除と変更について

テーブルの削除
DROP TABLE <テーブル名>;削除したテーブルとデータは元に戻らないので注意。

テーブル定義の変更(ALTER TABLE文)

テーブル作成後に、後から列を追加する場合に使用するALTER TABLE文がある。
ALTER TABLE <テーブル文> ADD COLUMN <列名>;
テーブル定義を変更したら元には戻せない。

検索の基本

列を出力する

• SELECT文　テーブルからデータを取り出す。SELECT文でデータを検索し取り出すことを「クエリ」とも呼ぶ。
  SELECT <列名>,・・・・・ 　FROM <テーブル名>;
• 全ての列を出力
  SELECT * FROM Shohin;　（*）アスタリスクは全列を意味する。

列に別名をつける

ASキーワードを使用して別名をつけることができる
SELECT shohin_id AS id, shohin_mei AS name FROM Shohin;
日本語を別名につけるときは・・・AS "商品名"にする。

• 定数の出力
  SELECT句には、定数も使用できる

SELECT '商品' AS mojiretsu, 38 AS kazu, '2019-08-06' AS hizuke,shohin_id, shohin_mei FROM Shohin;

• 重複行を省く
  SELECT句にDISTINCTを使用する。
  `SELECT DISTINCT <列名> FROM <テーブル名>;
  NULLがある場合は,NULLも一つのデータとして数えられる。

WHERE句による行の選択

条件に合う行だけを選択したい時に使用する。
SELECT <列名>,・・・・ FROM <テーブル名>,・・・・ WHERE <条件式>;

• WHERE句はFROM句の直後に書くようにする

SQL文でのコメントを使い方

• 1行コメント -- コメントをかく
• 複数行のコメント
  「/* ここの間に改行してコメントをかける */」
• SQL文の途中でコメントを挟むこともできる

算術演算子

hanbai_tankaの２倍の数をhanbai_tanka_x2として出力する
SELECT shohin_mei, hanbai_tanka, hanbai_tanka * 2 AS "hanbai_tanka_x2" FROM Shohin;
計算式にNULLが含まれる場合は、計算結果は全部NULLになってしまう。

比較演算子

演算子	意味
=	~と等しくない
<>	~と等しくない
>=	~以上
>	~より大きい
<=	~以下
<	~より小さい

• 比較演算子を使用してNULLの行は演算子に使用できない。

IS 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;

WHERE句での複数条件の検索をする時

• AND演算子 [両辺の検索条件が両方とも成り立つ時に、全体の検索条件として成り立つ]
• OR演算子 [両辺の検索条件のうち、どちらか一方あるいは両方が成り立つ時に全体の検索条件として成り立つ]
• OR演算子よりAND演算子が優先される。OR演算子を優先したいときは,両辺を半角文字の()で囲む。

集約関数

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

全行を数える(NULLを含む)
SELECT COUNT(*) FROM Shohin<テーブル名>;

列にあるNULLの行を数える
SELECT COUNT(shiire_tanka) FROM Shohin;

合計値を求める(SUM関数)　
販売単価の合計を求める
SELECT SUM(hanbai_tanka) FROM Shohin;

販売単価と仕入れ単価の合計を求める
SELECT SUM(shiire_tanka), SUM(hanbai_tanka) FROM Shohin;

全ての集約関数は、列名を集約関数にとった場合、計算前にNULLを除外して計算する。ただし、「COUNT(*)」は例外的にNULLを除外しない

平均値を求める（AVG関数）
販売単価の平均値を求める
SELECT AVG(hanbai_tanka) FROM Shohin;

最大値・最小値を求める
販売単価の最大値、仕入れ単価の最小値を求める
SELECT MAX(hanbai_tanka), MIN(shiire_tanka) FROM Shohin;

MAX/MIN関数はほとんどずべてのデータ型に使用することができる。SUM/AVG関数は数値のみにしか使用できない

値の重複を除いて,行数を数える
SELECT COUNT(DISTINCT shohin_bunrui) FROM Shohin;

GROUP BY句による集約

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

GROUP BY句に指定する列のことを集約キーやグループ化列と呼ぶ

句の記述の順序
1.SELECT → 2.FROM → 3.WHERE → 4.GROUP BY

集約キーにNULLが含まれる場合、結果にも空行として現れる

WHERE句とGROUP BY句による集約

SELECT <列名1>, <列名2>・・・・ FROM <テーブル名> WHERE <条件式> GROUP BY <列名1>, <列名2>・・・・

GROUP BY句を使うときには、SELECT句に集約キー以外の列名を使えない
GROUPBY句にSELECT句でつけた別名は使えない

集約した結果に条件を指定する

HAVING句

集合に対する条件を指定する時に使用する
SELECT <列名1>, <列名2>・・・・ FROM <テーブル名> GROUP BY <列名1>, <列名2>・・・・ HAVING　＜グループの値に対する条件>

HAVING句を使用する時のSELECT句の記述の順序
SELECT → FROM → WHERE → GROUP BY → HAVING

集約キーに対する条件は、HAVING句ではなくWHERE句にかく

検索結果を並び替える

ORDER BY句(行の順序を指定する)

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

販売単価の低い順に並べる
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin ORDER BY hanbai_tanka;

記述順序
1.SELECT句→2.FROM句→3.WHERE句→4.GROUPBY句→5.HAVING句→6.ORDERBY句

DESC(降順に並び替えたい時)
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin ORDER BY hanbai_tanka DESC;

ORDER BY句ではSELECT句に含まれない列や集約関数も使用できる

INSERT文の基本構文

INSERT INTO <テーブル名> (列１, 列2, 列３, ・・・) VALUES(値1, 値2, 値3 ・・・);

列リスト　→　(列名, 列名, ・・・・）
値リスト → (値, 値,　値, ・・・・）

原則として、INSERT文は一回の実行で１行を挿入すること

列リストを省略することもできる
INSERT INTO　<テーブル名> VALUES (値1, 値2,　値3, ・・・・);
ある列にNULLを割り当てない場合はそのまま「NULL」を挿入する。割り当てることができるのはNOT NULL制約がついていない列に限るので注意する。

デフォルト値を挿入する
CREATE TABLEの時にデフォルト制約を設定した列にはデフォルト値が挿入できる
INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES '007', 'おろしがね', 'キッチン用品', DEFAULT, 790, '2009-04-28');

INSERT~SELECT文 (他のテーブルからデータをコピーする)
ShohinCopyにShohinのデータをコピーする
INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) ELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi FROM Shohin;

INSERT文内のSELECT文では、WHERE句やGROUP BY句など、どんなSQL構文も使うことができる

データの削除

• DROP TABLE文は、テーブルそのものを削除する。
• DELETE文は、テーブルは残したまま、テーブル内の全ての行を削除する。

DELETE分の基本構文

DELETE FROM <テーブル名>;
DELETE文の削除対象は、テーブルや列でななく 「行（レコード）」である。

削除対象を指定したDELETE

一部の行だけを削除する探索型DELETE
DELETE FROM <テーブル名> WHERE <条件>;

DELETE文で、条件に使用できるのはWHERE句だけ。

データの更新

テーブルのデータを更新するUPDATE文

UPDATE <テーブル名> SET <列名> = <式>;

登録日を全て2009-10-10に更新する
UPDATE Shohin SET torokugi = '2009-10-10'

更新した場合は、元のデータがNULLだった場合も更新される

条件を指定したUPDATE文(探索型UPDATE)

一部の行だけを更新するUPDATE文
UPDATE <テーブル名> SET <列名> = <式> WHERE <条件式>;

商品分類がキッチン用品の行のみ販売単価を１０倍にする
UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10 WHERE shohin_bunrui = 'キッチン用品';

UPDATEで値をNULLで更新する（NULLクリア)

UPDATE Shohin SET torokubi = NULL WHERE shohin_id = '0008';

NULLクリアが使用できるのは,NOT NULL制約や主キー制約のついていない行に限る。

複数列の更新

販売単価を1.1倍、仕入れ単価を1/2する
UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 1.1, shiire_tanka = shiire_tanka / 2 WHERE shohin_bunrui = 'キッチン用品';

トランザクション

データベースに対する1つ以上(セット)の更新をまとめて呼ぶ時の名称

トランザクションの構文(SQL Server PostgereSQLの場合)

BEGIN TRANSACTION DML構文１； DML構文２；・・・・, COMMIT;

COMMITとは、トランザクションに含まれていた処理による変更をすべて反映して、トランザクションを終了するコマンドです。ファイルでいうところの、「上書き保存」に相当します。一度コミットしたら、もうトランザクションの開始前の状態に戻すことはできないので、コミットする前には、本当に変更を確定して良いか自問自答しましょう。

ROLLBACK 処理の取り消し

トランザクションに含まれていた処理による変更をすべて破棄して、トランザクションを終了するコマンドです。
ファイルでいうところの、「保存せずに終了」に相当します。ロールバックしたら、データベースの状態はトランザクションを開始する前の状態に戻ります。一般的に、コミットと違ってロールバックが大きなデータ損失につながることはありません。