fujjima’s blog

主に備忘録

SQL一時調べ

目次

  • SQLとは
  • 用語
  • 基本的な構文
  • Select構文
  • 句・式・文について
  • 内部結合・外部結合
  • 集計関数
  • case式
  • サブクエリ
  • Active Record
  • Active Recordとは
  • Active Recordの設計
  • Active RecordからSQLを直接発行

SQLとは

SQLは、関係データベース管理システム (RDBMS) において、データの操作や定義を行うためのデータベース言語(問い合わせ言語)、ドメイン固有言語である。プログラミングにおいてデータベースへのアクセスのために、プログラミング言語と併用されるが、SQLそのものはプログラミング言語ではない。

(引用:wikipedia https://ja.wikipedia.org/wiki/SQL


基本的な用語

  • クエリ(問合せ) データベースへ要求を出すこと

  • DML(データ操作言語) ★今日のメイン データベースにに対する操作を行う言語。SELECT(検索)、INSERT(挿入)、UPDATE(更新)、DELETE(削除)など

  • DDL(データ定義言語) データ構造を定義する言語。代表的な言語はCREATE(表領域作成)、DROP(表領域の削除)、ALTER(変更),TRUNCATE(表領域内のレコード削除)など

  • DCL(データ制御言語) トランザクションの制御を行うための言語。BEGIN、COMMIT、ROLLBACKなど


SQLの構文について

SQL構文逆引き辞典 http://www.sql-reference.com/


Select構文

テーブルからレコードを取得するための命令文。

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT| DISTINCTROW | ALL]
フィールド名 / 式 [ AS 別名 ], ...
[INTO {OUTFILE | DUMPFILE} 'ファイル名' オプション]
[FROM テーブル名
[WHERE 条件式]
[GROUP BY {整数 | フィールド名| 式} [ASC | DESC], ... [WITH ROLLUP]]
[HAVING 条件式]
[ORDER BY {整数 | フィールド名| 式} [ASC | DESC] ,...]
[LIMIT [オフセット,] 行指定]
[PROCEDURE 手続き名(属性一覧)]
[FOR UPDATE | LOCK IN SHARE MODE]
]

(引用:https://rfs.jp/sb/sql/s03/03-15.html


Select句の例

/* budgetsテーブルの全レコードの取得・表示 */
select * from budgets;
/* 重複の排除(distinct) */
SELECT DISTINCT id, management_item_id, value from budgets
/* 実績の金額順でソート */
SELECT id, management_item_id, value AS "金額" from budgets WHERE value > 30000 ORDER BY value;

句・式・文の区別について

image.png

句(clause)

節とも呼ぶ。文、またはクエリの構成要素。 省略可な句もある。

(例) UPDATE句、FROM句、WHERE句、EXISTS句

式(experssion)

スカラー値などを表す '1', 'katsui', trueなど

文(statement)

命令文ともいう。 一つの実行の単位となるSQL。 文の末尾には';'を付けることができる。 (例) SELECT文 UPDATE文

参考: https://qiita.com/gooddoog/items/a12d500b331f2ecf718f https://en.wikipedia.org/wiki/SQL


内部結合・外部結合

内部結合(INNER JOIN): 複数のテーブルを結合し、条件が一致したもののみを取得する。 結合条件に合わないものは弾かれる。

左外部結合(LEFT OUTER JOIN) 右外部結合(RIGHT OUTER JOIN) 完全外部結合(FULL OUTER JOIN): それぞれ、まず内部結合が行われる。その後、対象とするテーブルに対して結合できるデータをくっつける。完全外部結合は「内部結合 + 左外部結合 + 右外部結合」を行う。

/* 内部結合 */
SELECT * from base_achievements INNER JOIN management_items on base_achievements.management_item_id = management_items.id;

/* 左外部結合(実績に対して、結合条件に合う予算細目を結合し、なければNULLを入れておく) */
SELECT * from base_achievements LEFT OUTER JOIN management_items on base_achievements.management_item_id = management_items.id;

/* 右外部結合(予算細目に対して、結合条件に合う実績を結合し、なければNULLを入れておく) */
SELECT * from base_achievements RIGHT OUTER JOIN management_items on base_achievements.management_item_id = management_items.id;

集計関数について

GroupBy: GROUP BY句に記述された列のリスト(集約キー)を基準に、同じ値を持つ行を同じグループに振り分ける Having: グルーピングした結果から、Having句で指定した抽出条件を実行する

/* 実績のmanagement_idに対して単純にグループ化 */
SELECT management_item_id from base_achievements GROUP BY management_item_id ORDER BY management_item_id;

/* 実績を紐づく細目ごとにグループ化→実績値の合計を出力 */
SELECT management_item_id, sum(value) from base_achievements GROUP BY management_item_id ORDER BY management_item_id;

/* 実績を紐づく細目ごとにグループ化→実績値の合計を出力→合計金額6000000以下は弾く */
SELECT management_item_id, sum(value) AS "合計金額" from base_achievements GROUP BY management_item_id HAVING sum(value) > 6000000 ORDER BY management_item_id;

集計関数を実行する上でのメモ

  • whereとHavingとの違いについて Where句はselect句の結果からwhere句で指定した抽出条件を実行する Having句はGroupBy句でグルーピングした結果からHaving句で指定した抽出条件を実行する

  • GroupByでは、GroupByで指定したカラムしかSelect句に指定できない


case式

SQL内で条件分岐を行うための構文。 値と式による分岐があるが、値の方はあまり使わないのではないか。

/* (条件式による分岐)実績のvalueに応じて、判定結果をそれぞれ「継続」「保留」「棄却」とする) 
空文字の時には"/"を外す */
SELECT id, summary, value AS "金額",
CASE
WHEN value < 500000 THEN CASE summary WHEN '' THEN '棄却' ELSE summary || '/棄却' END
WHEN value > 1000000 THEN CASE summary WHEN '' THEN '継続' ELSE summary || '/継続' END
ELSE CASE summary WHEN '' THEN '保留' ELSE summary || '/保留' END
END "判定結果"
FROM base_achievements
ORDER BY value DESC

副問合せ(サブクエリ)

SQL文の中に入れ子SQL文を指定すること。Select文の結果を別のSQL文で利用する。 from句配下でも、where句配下でも、select句のすぐ後に使用することもできる。 from句配下で使用されるケースはイメージがつかない。

image.png (引用:https://www.atmarkit.co.jp/ait/articles/1208/06/news118.html

 


副問合せの例

/* サブクエリ:勘定科目に紐づく細目に紐づく実績のみを取得する */
SELECT id, summary, management_item_id, value AS "金額" from base_achievements WHERE management_item_id IN (
SELECT management_items.id from management_items INNER JOIN accounts on management_items.account_id = accounts.id WHERE accounts.type = 'FinancialAccount') ORDER BY base_achievements.id;

便利ではあるが、ネストしまくると途端に可読性が悪くなるやつでもあると思う。


Active Recordとは

「Active Recordパターン」というデザインパターンに由来した、SQLを意識せずにデータベースアクセスを行えるライブラリ。

→ Active RecordパターンをRubyで実装したライブラリが、Railsで使われているActive Recordライブラリ

(参考:https://www.techscore.com/tech/Ruby/Rails/other/designpattern/1/


Active Recordの設計

Active Recordパターンについて → 「データベーステーブルまたはビューの行をラップし、データベースアクセスをカプセル化してデータにドメインロジックを追加するオブジェクト」

これをRubyで実装すると下記のようなクラス設計になる。


# application_record.rb
class ApplicationRecord < ActiveRecord::Base
…
end

# ApplicationRecordを継承したbase_achievement.rb(ActiveRecord::Baseを継承したモデルクラス)
class BaseAchievement < ApplicationRecord
…
end

Active RecordでSQLを直接発行したい時

ActiveRecord::Base.connection」を使用する

con = ActiveRecord::Base.connection
con.execute("SELECT DISTINCT id, management_item_id, value from budgets WHERE value > 30000;")

// 返り値(DBアダプタ依存のオブジェクト)
#<PG::Result:0x00005627b5dd0080 status=PGRES_TUPLES_OK ntuples=11 nfields=3 cmd_tuples=11>

直接検索とかしたい時とか

  • select_all(検索結果)

これ自体の返り値はActiveRecord::Resultオブジェクトで、to_hashとかでハッシュの配列が取得できる。

con = ActiveRecord::Base.connection
con.select_all("SELECT DISTINCT id, management_item_id, value from budgets WHERE value > 30000;").to_hash

// 返り値
[{"id"=>1166, "management_item_id"=>471, "value"=>42433.0},
{"id"=>1165, "management_item_id"=>471, "value"=>34243.0},
{"id"=>1155, "management_item_id"=>474, "value"=>42342.0},
{"id"=>1173, "management_item_id"=>472, "value"=>909090.0},
{"id"=>1161, "management_item_id"=>476, "value"=>625656.0},
{"id"=>1171, "management_item_id"=>481, "value"=>34242.0},
{"id"=>1156, "management_item_id"=>474, "value"=>35252.0},
{"id"=>1174, "management_item_id"=>472, "value"=>454545.0},
{"id"=>1206, "management_item_id"=>492, "value"=>245345.0},
{"id"=>1159, "management_item_id"=>476, "value"=>55245.0},
{"id"=>1210, "management_item_id"=>480, "value"=>345345.0}]

select_oneとかselect_rowsなどいくつかメソッドがある。

参考: https://qiita.com/yut_h1979/items/4cb3d9a3b3fc87ca0435