SQL 述語、CASE文編
2019-08-26
述語は戻り値が真理値になる関数のこと
目次：
述語
・LIKE述語
・BETWEEN述語
・IS NULL, IS NOT NULL
CASE文
文字列の部分一致検索 LIKE述語
文字を検索する際、文の一部分だけを指定して、それが含まれる文字を探してくれる便利なLIKE述語。
・前方一致
下記の例では、「dddなんとか〜」という文字を探してきます。
SELECT *
FROM SampleLike
WHERE 'ddd%';
・中間一致
これは、どこかの位置にdddが含まれる文字を探します。
〜dddやddd~、また〜ddd〜などなど
SELECT *
FROM SampleLike
WHERE '%ddd%';
・後方一致
これは、「なんとか〜ddd」という文字を探します。
SELECT *
FROM SampleLike
WHERE '%ddd';
範囲検索 BETWEEN述語
対象 BETWEEN 下限 AND 上限
1下記、単価を取得する際、「100円から1000円までの」と範囲を絞れます。
SELECT shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka BETWEEN 100 AND 1000;
反対に含みたくないときは
SELECT shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > 100
AND hanbai＿tanka < 1000;
NULLかどうかの判定 IS NULL / IS NOT NULL
NULLは ＝ NULLということができません。
NULLを指定する場合はIS NULL
NULLを排除したいときは IS NOT NULLです。
SELECT shohin_mei, shiire_tanka
FROM Shohin
WHERE shiire_tanka IS NULL;
場合分けを記述する CASE文
単純CASE式
CASE ＜式＞
WHEN ＜式＞ THEN ＜式＞
WHEN ＜式＞ THEN ＜式＞
ELSE ＜式＞
END
CASE hanbai_tanka
WHEN 100 THEN '100円'
WHEN 200 THEN '200円'
ELSE 'その他'
END
例２
SELECT 費目, 出金額,
CASE 費目 WHEN '居住費' THEN '固定費'
WHEN '教育費' THEN '固定費'
ELSE '固定費'
END AS 出費の分類
FROM 家計簿 WHERE 出金額 > 0
検索CASE式
WHEN ＜評価式＞THEN＜式＞
WHEN ＜評価式＞THEN＜式＞
ELSE ＜評価式＞
SELECT SUM(CASE WHEN shohin_bunrui = '衣服'
THEN hanbai_tanka ELSE 0 END) AS sum_tanka_ihuku,
SUM(CASE WHEN shohin_bunrui = 'キッチン用品'
THEN hanbai_tanka ELSE 0 END) AS sum_tanka_kitchin
FROM Shohin;
例２
SELECT メールアドレス
CASE WHEN 年齢 >= 20 AND 年齢 < 30 THEN '20代'
WHEN 年齢 >= 30 AND 年齢 < 40 THEN '40代'
END
SQL サブクエリ編
2019-08-25
サブクエリは副問い合わせともいい、SELECT文をネストして欲しいデータを探します。
下記では、FROMの後に、さらにSELECT文を使っています。
順番としてはまず（）内のサブクエリが実行され、その後１行目のSELECT文が実行されます。FROMで参照したいテーブル内容をさらにSELECT文で探してきているということです。
例１
SELECT shohin_bunrui, cnt_shohin
FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin
FROM Shohin
GROUP BY shohin_bunrui) AS Shohinsum;
例２
サブクエリを使わない場合、「一度SELECT文で出金額を出して、それをもう一回SELECT文書いて使う」と二度手間になってしまいます。
SELECT MAX(出金額)
FROM 家計簿;
SELECT 費目, 出金額
FROM 家計簿
WHERE 出金額 = [メモしておいた金額]
↓
サブクエリ使うと、以下のように１つのSQL文で済みます。出金額については（）で
MAX額を計算して「出金額」を取得しています。
SELECT 費目, 出金額
FROM 出金額 = (SELECT MAX(出金額)
FROM 家計簿) AS 出金額
サブクエリの３つのパターン
１、単一の値の代わりとして、サブクエリの検索結果を用いる
２、複数の値の代わりとして、サブクエリの検索結果を用いる
３表のの値の代わりとして、サブクエリの検索結果を用いる
１、単一行サブクエリ
・検索結果が１行１列の値になる。
・SELECT文の選択リストやFROM句、UPDATEのSET句、また１つの値との判定を行うWHERE句の条件式などに使える
SET句
選択リスト
２、複数行サブクエリ
・検索結果がn行1列の複数の値となる（nは１以上）
・複数の値との判定を行うWHERE句の条件式や、SELECT文のFROM句に記述できる
INを利用
または以下のようにすることも可能
SELECT *
FROM 家計簿
WHERE 費目 IN (SELECT DISTINCT 費目 FROM 家計簿);
ANYを利用
NULLを除外したい
３、表形式
・検索結果がn行n列の表となる（n、mは１以上）
・SELECT文のFROM句やINSERT文などに記述できる
SELECT SUM(SUB, 出金額) AS 出金額合計
FROM (SELECT 日付, 費目, 出金額
FROM 家計簿
UNION
SELECT 日付, 費目, 出金額
FROM 家計簿アーカイブ
WHERE 日付 >= '2018-01-01'
AND 日付 <= '2018-01-31') AS SUB;
SQL トランザクション編
2019-08-25
「データベースに対する１つ以上の更新をまとめて呼ぶ時の名称」です。
トランザクションを使う目的は、処理の途中で予期せぬトラブルでコンピューターの処理が中断してしまった時に整合性を保たせるためです。
例えば、銀行の処理
Aさんの口座からBさんの口座に1万円を移す処理。
①Aさんの口座から１万円引く
↓
②Bさんの口座に１万円増やす
もし①が終わった段階で処理が滞ってしまったら、Aさんの口座は１万円減っているのにBさんの口座には１万円入っていないという不整合が起きてしまいます。
なので上記のような処理を「１セット」とし、途中で終わったらその処理が「なかったこと」にすればいいですね。そして、最後まで処理が終わって初めて「完了」になれば整合性は保たれます。
ACID
・Atomicity（原子性）
処理を一つの塊とし、「処理が全く行われない」OR「処理が完全に終わる」のいずれか。途中まではありえません。
・Consistency（一貫性）
データベースにあらかじめ設定された制約は最後まで守られる。
・Isolation（独立性）
割り込み禁止。トランザクション同士が互いに干渉を受けないことを保証。
・Durability
永続性。トランザクションが終了したら、その時点でのデータの状態が保存されることを保証する。
BEGIN TRANSACTION;
処理１
処理２
COMMIT;
(処理がなかったことにするには、ROLLBACK）
--トランザクション
BEGIN TRANSACTION;
--処理１
UPDATE Shohin
SET hanbai_tanka = hanbai_tanka - 1000
--処理２
UPDATE Shohin
SET hanbai_tanka + 1000
WHERE shohin_mei = 'Tシャツ';
COMMIT;
トランザクションの処理に慣れる
自分の練習方法：
AをBに変更する場合、トランザクションを使わない場合、やっぱり戻したいと思ったら反対にBをAに変更する処理が必要になります。
トランザクションを使えば、BEGIN TRANSACTIONを先に書き、
処理を書いていきます。あとはCOMMITさえしなければ、処理する前の状態に戻したい場合は、最後にROLLBACKを書けばOKです。
本番で失敗したくないので、練習用のデータベースで色々試してみるのはオススメです。
SQL 集約と並べ替え編
2019-08-25
集約と並べ替え
集約関数
行数を数える COUNT関数
SELECT COUNT(引数）
FROM テーブル名
全行数えるなら(＊）を利用。
SELECT COUNT(*)
FROM shohin;
NULLの行を除外したい場合、対象とする列を限定して引数に書く。
SELECT COUNT(shiire_tanka)
FROM Shohin;
合計を求める SUM関数
NULLがあった場合、無視されますので影響ありません。
SELECT SUM(hanbai_tanka), SUM(shiire_tanka)
FROM Shohin;
平均値を求める AVG関数
SUM関数と同様、NULLは含まれません。
300, 500, NULLの場合、（300 + 500) / 2 = 400となります。
FROM Shohin;
最大値・最小値を求める MAX関数・MIN関数
SELECT MAX(hanbai_tanka), MIN(shiire_tanka)
FROM Shohin;
グループ分けする GROUP BY句
SELECT 列名１, 列名２, 列名３
FROM テーブル名
GROU BY 列名１, 列名２, 列名３
グループ分けして、そのグループごとに計算して表示することができます。
下記、shohin_bunruiごとに行数を数えます。
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui;
例えば、shohin_bunruiに衣服、事務用品、キッチン用品があった場合、それぞれ何行あるかを計算して表示できます。
shohin_bunrui count
衣服 ２
事務用品 ４
キッチン用品 ５
WHERE句で条件を指定できます。
SELECT shiire_tanka, COUNT(*)
FROM Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka;
NULLがあった場合、NULLとして１つのグループに分類されます。
注意１：
GROUP BYでSELECT分に書けるのは、
・定数
・集約関数
・GROUP BY句で指定した列名（つまり集約キー）
注意２：
GROUP BY句に列の別名（AS)はつけれない
注意３：
GROUP BY句を使って結果を選択した時、表示される結果の順番はランダム
注意４：
GROUP BY句のGROUP BYリストに使用される式内では集計、またはサブクエリは使えない
集約した結果に条件を指定する HAVING句
GROUP BYを使って集約した際、更に条件を指定して絞り込みたい時はHAVING句を使います。
SELECT 列名１, 列名２, 列名３
FROM テーブル名
GROU BY 列名１, 列名２, 列名３
HAVING グループの値に対する条件
shohin_bunruiごとに集約をし、集約された値に対し更に2500（円）以上のもののみ取得するという条件をつけると
FROM Shohin
GROUP BY shohin_bunrui
WHERE と HAVINGについて
「集約キーに対する条件」は WHERE句、HAVING句それぞれに書くことはできますが、その違いは何なのか。取得できる結果は同じですが、集約キーに対する条件はソートに掛かる時間の関係上WHERE句に書く方がいいそうです。
WHERE句
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shohin_bunrui;
HAVING句
--△ ソートに時間がかかり処理が遅くなる
SELECT shohin_bunrui, COUNT(*)
FROM Shohin
GROUP BY shohin_bunrui
HAVING shohin_bunrui = '衣服';
注意：
集計がHAVING句または選択リスト内にある場合、及び集計する列が外部参照の場合にだけWHERE句に集計を含めることができる。
検索結果を並べ替える ORDER BY句
ASC: 昇順（小さい順）
DESC: 降順（大きい順）
※指定しなければ自動的にASCになる
ORDER BY句は最後に書きます。
SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
FROM shohin
ORDER BY hanbai_tanka;
--何も指定しなければASC(昇順：小さい順）
・NULLを含む列は先頭または末尾にまとめられて表示されます。
・GROUP BYと違い列の別名が使える
（処理の順番的にGROUP BYの時はまだ別名が認識されていないがORDER BY句は最後なので認識される）
SQL 検索の基礎編
2019-08-25
SQL 検索編
・基本の形
SELECT
選択列
FROM テーブル名
SELECTの後に選択したい列（カラム名）を書きます。AS 〜 とすることで別名にすることができます。ASを付けた場合、AS以下の名前が列名として表示されます。
FROMのあとは、データを取ってきたいテーブル名を書きます。
重複行を一つにまとめる DISTINCT
SELECT DISTINCT 列名
FROM テーブル名
同じ値が入っている行については２つ表示するのではなく、１行でまとめます。
SELECT DISTINCT shohin_bunrui
FROM Shohin;
行を指定する WHERE
SELECT 列名
FROM テーブル名
WHERE 取得したい条件
下記では、Shohinテーブルからshohin_meiとshohin_bunruiを取得する。条件としてshohin_bunruiが"衣服"のもの。つまりWHERE抜きだと単純にshohin_meishohin_bunruiの列を全行取得しますが、WHEREに続けて条件を書けば、それに合致した行のみ取得できます。
SELECT shohin_mei, shohin_bunrui
FROM Shohin
WHERE shohin_bunrui = "衣服";
算術演算子
SQL文の中では四則演算（＋ ー ＊ / )も使えます。
SELECT shouhin_mei, hanbai_tanka,
hanbai_tanka * 2 AS '販売単価の２倍'
FROM Shohin;
注意１：
NULLが入っている式は答えが全てNULLになります。
5 + NULL = NULL
10 - NULL = NULL
2 * NULL = NULL
注意２：
データ型が違うものは+ で連結できません。
どちらかデータ型を変換して合わせましょう。
文字型 → 数字型 へ変換 （できない）
数字型 → 文字型 へ変換（できる）
注意：
WHERE句の後ろに四則演算は使えません（条件式は書ける）。
WHERE句に書けるもの、書けないものは別途ブログに書きます。
比較演算子
= 等しい
<> 等しくない
>= 以上
> より大きい
<= 以下
< より小さい
hanbai_tankaが1000(円)以上のものを条件にする
日付に使うと、以降、以前など指定できます。
もしCHAR型(文字型）の'3'に指定すると辞書式順序になります。
論理演算子
〜でないもの AND演算子
hanbai_tankaが1000(円)以下のもの
または OR演算子
shohin_bunruiが事務用品または衣服
SELECT shohin_mei, shohin_bunrui, torokubi
FROM Shohin
WHERE shohin_bunrui = '事務用品'
OR shohin_bunrui = '衣服';
ANDとORの組み合わせに注意：
( )で分けないと意図しないデータが取得されます。
( )の中から処理されるので取得したい条件を整理して書きましょう。
以下 、「shohin_bunruiが事務用品」と「登録日が9/11または9/20」となります。
事務用品は必ず取得できます。が（）の入れ子を誤ると取得できなくなる可能性も。
SELECT shohin_mei, shohin_bunrui, torokubi
FROM Shohin
WHERE shohin_bunrui = '事務用品'
AND ( torokubi = '2009-09-11'
OR torokubi = '2009-09-20');
論理演算子の優先度は
１、NOT
２、AND
３、OR
（）をつければ優先順位を引き上げる事ができます。
取得したい情報は何か整理してうまく（）を使おう！
SQL DB・テーブルの作成、編集編
2019-08-24
データベース、テーブルの作成、編集
SQLはデータベースに指令を出して得たい情報などを取得できる言語なので、
まずはデータベースありきです。
データベース内では様々なテーブルがあり、それら各々のテーブルから、またテーブル同士を結合させるなどして情報を取得します。
データベース、テーブルを作ることもSQLではできるので、まずはそこからまとめていきます。
なお、コードの内容については今読んでる参考書のものを引用させて頂きます。
使用するRDBMSはSQL serverを想定しています。
・データベースを作る
CREATE DATABASE データベース名;
下記ではshopというデータベースを作りました。
CREATE DATABASE shop;
・テーブルを作る
CREATE TABLE テーブル名
(カラム名 データ型 制約の設定);
下記、shohinというテーブルを作りました。
その下は、例えばshohin_idカラムで、データ型はCHAR型、さらにNOT NULL制約をつけています。制約については付けなくてもOK。付ければデータの整合性は高められます。反面、制約をかけるのでデータの使い勝手が悪くなるということも。
一番下のPRIMARY KEYは主キーです。今回はshohin_idを主キーにしました。
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)
);
・テーブルの削除
DROP TABLE テーブル名;
・テーブルの更新
カラムの追加
ALTER TABLE テーブル名 ADD カラム名 データ型
ALTER TABLE Shohin ADD shihin_mei_kana VARCHAR(100);
カラムの削除
ALTER TABLE テーブル名 DROP COLUM カラム名
※追加の時は新規なのでデータ型も指定しますが、削除する時はすでにデータ型はわかっているので指定は不要。
・テーブルにデータを登録
先ほどまではカラムでしたが、１つのデータ、つまりレコードを追加します。
INSERT INTO テーブル名 VALUES(カラムに対応する値、カラムに対応する値・・・);
下記の（）の中の値は、上記「テーブルを作る」で作ったshohinテーブルのカラム（shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, tourokubi)にそれぞれ対応します。順番はカラム通りに追加されるので、このように全てのカラムに値を入れる際、特に順番やカラム名を指定する必要はありません。
INSERT INTO Shohin VALUES('0001', 'Tシャツ', '衣服',
1000, 500, '2009-09-20');
もし、一部のみデータを登録したい場合は、下記のように、どのカラムにどういう値を入れたいという指定をすることができます。逆に、これが基本です。もちろん、全部登録したい時にもカラム名も全部書いても同じですが、上記コードのように省略もできます。一部だけの時はちゃんと指定しないとどのカラムに入れたらいいのか分からないので登録できません。
INSERT INTO Shohin
(shohin_id, shohin_mei)
VALUES('0001', 'Tシャツ)
次回は「検索の基本編」です
SQL 基本編
2019-08-24
SQLを勉強中なので、学んだ内容を何回かに分けてまとめていきます。
第一回 基本編
１、SQLを書くにあたって、自分なりに重要だと思ったこと
２、SQLの流れ（順番）
３、型
１、SQLを書くにあたって、自分なりに重要だと思ったこと
・読みやすいように改行や、インデントをつける（所属する組織、チームのコーディング規約に従う）
・テーブル名に別名をつける
AS T1やAS T2など別名をつければ見た目もスッキリし見やすくなる。また書く手間も省ける
・関数を全て覚える必要はない
関数は多く、またRDBMSによって使える、使えないがあり統一されていないので、今やりたい処理は何か整理し、リファレンスなどで都度調べればOK。もちろんよく使う関数は覚えておけば便利。
・文が長くなる場合、処理ごとにパーツに切り出して考えると整理しやすい
例えば、サブクエリなど、別途その部分だけSQL文を書き取得したい情報がちゃんと取れるか確認してみるなど。
２、SQLの流れ（順番）
処理実行の順番
FROM
JOIN
WHERE
GROUP BY
SUM, AVGなど
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
記述の順番
FROM
WHERE
GROU BY
HAVING
ORDER BY
３、型
INTEGER 整数
DECIMAL 固定小数点数
FLOAT 浮動小数点数
CHAR 固定長文字列（文字数が最大長になるまで空きを半角スペースで埋める）
VARCHAR 可変長文字列
DATE 日付
DATETIME 日付・時間
