DML
DML(Data Manipulation Language)はデータ操作言語で、データベースにデータを登録・変更・削除またはデータを参照するなどの操作を行います。
データベースからデータを取得するにはSELECTを使用します。
・書式：SELECT 表示項目,[Expression] FROM テーブル名 [Option]
・説明：指定したレコードからデータを取得します。表示項目には、列名を指定すると指定した列を表示して「*」を指定すると全ての列名を表示します。プログラムとして実装する場合は、「*」は列を追加すると変わりますので、明示的に列名を指定した方がいいです。関数(COALESCE・DISTINCTなど)・Window関数・集合関数(SUM、MAX、MIN、AVG、COUNT)・四則演算・文字列結合(||)・データ型変換(cast)など利用できます。
-
[Option]※後述
- (1) 制限：WHERE
-
(2) 結合：別のテーブルと結合
・CROSS JOIN 結合テーブル名 ON 結合条件：交差結合
・INNER JOIN 結合テーブル名 ON 結合条件：内部結合
・LEFT OUTER JOIN 結合テーブル名 ON 結合条件：左外部結合
・RIGHT OUTER JOIN 結合テーブル名 ON 結合条件：右外部結合
・FULL OUTER JOIN 結合テーブル名 ON 結合条件：完全外部結合
・UNION(ALL)：テーブル結合
・LATERAL：ラテラル結合
※テーブル結合は列名が1つなら特に指定は不要で重複すれば装飾が必要です。
※テーブル名の後に「AS 別名」としてテーブル名を別名にできます。 - (3) INTERSECT：テーブル重複抽出
- (4) EXCEPT：テーブル差分抽出
- (5) GROUP BY(HAVING)：列をグループ化
- (6) ORDER BY：並び替え
- (7) LIMIT：レコード数制限とOFFSET：取得する位置指定
- (8) FETCH：カーソルを使用
- (9) FOR SHARE or UPDATE：行ロック
SQLのオプションは記述する順番があります。正しく記述しないと文法エラーとなります。
実際に実行される順番は記述順序とことなりますので、そちらも理解しておく必要があります。
- (1) SELECT
- (2) FROM
- (3) JOIN
- (4) WHERE
- (5) GROUP BY
- (6) HAVING
- (7) ORDER BY
- (8) LIMIT
[SQL記述順序]
- (1) FROM
- (2) JOIN
- (3) WHERE
- (4) GROUP BY
- (5) SELECT
- (6) HAVING
- (7) ORDER BY
- (8) LIMIT
[SQL実行順序]
(1) 制限「WHERE」
制限では、比較演算子または比較述語などの述語を利用して表示する内容を制限します。
[比較演算子]
比較演算子は以下になります。
- =：一致したもの
- >：超える(=で以上)
- <：未満(=で以下)
- ><：一致していない(!=でも代用可能)
[比較述語]
比較演算子は以下になります。
- BETWEEN：範囲(NOTで範囲外)
- IS NULL：NULLのデータ(IS NOT NULLでNULL以外)
- IS TRUE：真(NOTで偽または不明)
- IS FALSE：偽(NOTで真または不明)
- IS UNKNOWN：不明(NOTで真または偽)
相関副問い合わせ（相関サブクエリ）
相関副問い合わせ（相関サブクエリ）は、SQLのクエリの中でサブクエリ（副問い合わせ）が外側のクエリ（主問い合わせ）と依存関係を持ちながら実行されるクエリのことです。
具体的には、サブクエリが外側のクエリの行ごとに繰り返し実行され、その都度、外側のクエリの列を参照します。
[その他述語]
比較演算子は以下になります。
- LIKE：%文字%が含まれているか
- SIMILAR TO：%文字%のパターンマッチ
- ~：^文字^のPOSIXパターンマッチ
- IN：含まれたもの
- EXISTS：テーブルの存在判定(NOTで存在していない)
※INとEXISTSは結果は変わりませんが、EXISTSはテーブルを参照しないので高速です。
・IS NULL or ：NULLのみまたはNULLでないもののみ
(2) 結合「別のテーブルと結合」
関係代数の結合で記載していますが、もう少し詳しく記載します。
【CROSS JOIN】
全ての組合せを対象に結合していきます。
以下テーブルの行がある過程として進めます。
| id | name |
|---|---|
| 1 | abc |
| 2 | def |
| 3 | hij |
| id | name |
|---|---|
| 3 | hij |
| 4 | xyz |
| 5 | uvw |
テーブル「T1」の行にはIDが「1」と「2」と「3」が存在
テーブル「T2」の行にはIDが「3」と「4」と「5」が存在
クエリ：SELECT * FROM T1 CROSS JOIN T2;
※「SELECT * FROM T1,T2;」としても同じ結果が表示します。
CROSSは特にON句などでカラムを指定する必要はありません。
テーブル「T1」とテーブル「T2」を結合したときはすべての行のすべての組み合わせ(直積)が作成されます。
テーブル「T1」とテーブル「T2」の全組み合わせを表示したい場合に利用します。
以下ののように表示されます。
| id | name | id | name |
|---|---|---|---|
| 1 | abc | 4 | xyz |
| 1 | abc | 3 | hij |
| 1 | abc | 2 | uvw |
| 2 | def | 4 | xyz |
| 2 | def | 3 | hij |
| 2 | def | 2 | uvw |
| 3 | hij | 4 | xyz |
| 3 | hij | 3 | hij |
| 3 | hij | 2 | uvw |
【INNER JOIN】
結合したテーブルで条件に一致したものだけが表示されます。
以下テーブルの行がある過程として進めます。
| id | quiz | quiz_type |
|---|---|---|
| 1 | 以下のSQLで表示する件数は？ | 10 |
| 2 | ネットワークの帯域幅は？ | 11 |
| quiz_type_id | type_name |
|---|---|
| 10 | データベース |
| 11 | ネットワーク |
| 12 | セキュリティ |
クエリ：SELECT * FROM T1 INNER JOIN T2 ON T1.quiz_type =
T2.quiz_type_id;
ONで条件を指定します。今回はT1のquiz_typeとT2のquiz_type_idが一致していることになります。
INNERは省略可能です。
結果は以下になります。
| id | quiz | quiz_type | quiz_type_id | type_name |
|---|---|---|---|---|
| 1 | 以下のSQLで表示する件数は？ | 10 | 10 | データベース |
| 2 | ネットワークの帯域幅は？ | 11 | 11 | ネットワーク |
T1のquiz_typeとT2のquiz_type_idが一致している行だけが表示されます。
【NATURAL JOIN】
結合同士のテーブルで同じカラム名がある場合、【INNER
JOIN】と同じ結果を表示しますが、同じカラム名は複数表示されません。
同じカラム名がない場合は、「CROSS JOIN」と同じ動作をします。
クエリ：SELECT * FROM T1 NATURAL JOIN T2;
※「SELECT * FROM T1,T2 WHERE T1.id =
T2.id」のようにカンマで別テーブルを区切り、WHEREで結合テーブル間のキーを一致させても同じ結果になります。
【LEFT OUTER JOIN】
左側のテーブルに右側の内容を結合します。条件は指定するのですが、左側はすべての行を表示します。
以下テーブルの行がある過程として進めます。
| id | quiz | quiz_type |
|---|---|---|
| 1 | 以下のSQLで表示する件数は？ | 10 |
| 2 | ネットワークの帯域幅は？ | 11 |
| quiz_type_id | type_name |
|---|---|
| 10 | データベース |
クエリ：SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.quiz_type =
T2.quiz_type_id;
ONで条件を指定します。今回はT1のquiz_typeとT2のquiz_type_idが一致していることになります。
LEFTまたはOUTERは省略可能です。
結果は以下になります。
| id | quiz | quiz_type | quiz_type_id | type_name |
|---|---|---|---|---|
| 1 | 以下のSQLで表示する件数は？ | 10 | 10 | データベース |
| 2 | ネットワークの帯域幅は？ | 11 | NULL | NULL |
INNER JOINなら1行しか表示されないのですが、LEFT JOINではT1の行はすべて表示するため、条件に一致しない場合はNULLになります。
【RIGHT OUTER JOIN】
LEFT JOINの右版です。右側にある行をすべて表示します。OUTERは省略可能です。
【FULL OUTER JOIN】
左右の行をすべて表示します。
以下テーブルの行がある過程として進めます。
| id | quiz | quiz_type |
|---|---|---|
| 1 | 以下のSQLで表示する件数は？ | 10 |
| 2 | ネットワークの帯域幅は？ | 11 |
| 3 | Pythonのクラスの書き方は？ | 14 |
| quiz_type_id | type_name |
|---|---|
| 10 | データベース |
| 11 | ネットワーク |
| 12 | セキュリティ |
クエリ：SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.quiz_type =
T2.quiz_type_id;
ONで条件を指定します。今回はT1のquiz_typeとT2のquiz_type_idが一致していることになります。
OUTERは省略可能です。
結果は以下になります。
| id | quiz | quiz_type | quiz_type_id | type_name |
|---|---|---|---|---|
| 1 | 以下のSQLで表示する件数は？ | 10 | 10 | データベース |
| 2 | ネットワークの帯域幅は？ | 11 | 11 | ネットワーク |
| 3 | Pythonのクラスの書き方は？ | 14 | NULL | NULL |
| NULL | NULL | NULL | 12 | セキュリティ |
下の2行では、T1とT2のテーブルで条件に一致するものがないため、カラムがNULLで表示されますがすべての行が表示されていることが分かります。
条件指定では、ONで指定していましたが、「USING」を利用することも可能です。
USINGで利用する場合、同じ列名である必要があります。
T1とT2の共通のカラム名「id」があった場合は、以下のように記載します。
クエリ：SELECT * FROM T1 LEFT OUTER JOIN T2 USING (id);
【UNION】
テーブル同士を結合します。
注意点として、JOINとは異なり、結合するテーブル同士のカラム数とドメインなどが同じでないといけません。
以下テーブルの行がある過程として次のクエリで進めます。
クエリ：SELECT * FROM T1 UNION SELECT * FROM T1;
| id | name |
|---|---|
| 1 | abc |
| 2 | def |
| 3 | hij |
| id | name |
|---|---|
| 3 | hij |
| 4 | xyz |
| 5 | uvw |
結果は、両方のテーブルに存在している行が全て返されます。
しかし、重複している行は返されません。
UNIONの前にALLを付けると(SELECT * FROM T1 ALL UNION SELECT * FROM T1;)重複しているid「3」が2行表示されて2つのテーブルの総計は6行として表示されます。
ALLを付けない場合は、id「3」は1行になり、テーブルの総計は5行になりますが、ALLではなくDISTINCTを付けても5行でALLをつけないのと同じ結果を表示できます。
| id | name |
|---|---|
| 1 | abc |
| 2 | def |
| 3 | hij |
| 3 | hij |
| 4 | xyz |
| 5 | uvw |
(3) INTERSECT「テーブル重複抽出」
テーブルの重複抽出を行うには、INTERSECTを利用します。
2つのテーブル「T1」と「T2」が以下のようにあった場合に次のクエリで差分を抽出します。
注意点として、重複しているかを比較するテーブル同士のカラム数とドメインなどが同じでないといけません。
クエリ：SELECT * FROM T1 INTERSECT SELECT * FROM T1;
| id | name |
|---|---|
| 1 | abc |
| 2 | def |
| 3 | hij |
| id | name |
|---|---|
| 3 | hij |
| 4 | xyz |
| 5 | uvw |
結果は、左側で指定したテーブル(T1)に存在していて、右側のテーブル(T2)にも存在している行だけが返されます。
id「3」は、T1,T2のどちらにも存在していますので、その1行が結果として表示されます。
| id | name |
|---|---|
| 3 | hij |
(4) EXCEPT「テーブル差分抽出」
テーブルの差分抽出を行うには、EXCEPTを利用します。
2つのテーブル「T1」と「T2」が以下のようにあった場合に次のクエリで差分を抽出します。
注意点として、差分があるかを比較するテーブル同士のカラム数とドメインなどが同じでないといけません。
クエリ：SELECT * FROM T1 EXCEPT SELECT * FROM T1;
| id | name |
|---|---|
| 1 | abc |
| 2 | def |
| 3 | hij |
| id | name |
|---|---|
| 3 | hij |
| 4 | xyz |
| 5 | uvw |
結果は、左側で指定したテーブル(T1)で、右側のテーブル(T2)に存在している行以外を返します。
id「3」は、T1,T2のどちらにも存在していますのでT2に存在していなく、T1だけに存在しているid「1」と「2」が結果として表示されます。
| id | name |
|---|---|
| 1 | abc |
| 2 | def |
(5) GROUP BY(HAVING)「列をグループ化」
GROUP BYは、カラムを一つまたは複数列でグループ化し、集約関数などを利用してグループ化した値を表示します。
カラムを表示できる列はグループ化したカラムと集約関数を利用したものに限られます。
HAVINGはグループ化した後に制限し、WHEREはグループ化する前に制限します。
クエリ：SELECT quiz_type,COUNT(*) FROM quiz GROUP BY quiz_type HAVING 10 < COUNT(*);
(6) ORDER BY「並び替え」
ORDER BYはカラムでソートします。
ASC(デフォルト)が昇順でDESCが降順で表示をソートします。
クエリ：SELECT * FROM quiz ORDER BY id DESC;
(7) LIMIT「レコード数制限」とOFFSET「取得する位置指定」
LIMITは表示するレコード数を制限します。
10件だけ表示したいなど表示するレコード数を制限する場合に使用します。
OFFSETはLIMITと一緒に使用して、開始位置を指定します。
例えば、「id」を昇順に指定して、最初の1番目から10レコード表示ではなく20番目から取得したい場合にOFFSETで指定します。
クエリ：SELECT * FROM quiz ORDER BY id ASC LIMIT 10 OFFSET 20;
(8) FETCH「カーソルを使用」
カーソルは、埋込み型SQLで使用するもので、会話型SQLでは使用できませんので、pgadmin4の画面では動作しません。
カーソルを定義して、「DECLARE カーソル名 SCROLL CURSOR FOR SELECT * FROM quiz;」でそのカーソルから「FETCH FORWARD 1 FROM カーソル名;」データを取得していきます。
(9) FOR SHARE or UPDATE「行ロック」
明示的にロックを掛ける場合に使用します。
参照時にデータが更新されたくない場合は、「FOR SHARE」を使います。
更新するときにデータを変更したくない場合は、「FOR UPDATE」を使います。
クエリ：SELECT * FROM quiz FOR SHARE;
関数
[関数] 、算術関数、演算子 / 時間関数
集約関数は以下になります。
- ・COALESCE：初にNULLでない値を返します。
- ・DISTINCT：重複行を返しません。
- ・COUNT(値)：値の件数を表示します。
- ・SUM(値)：値の合計値を表示します。
- ・AVG(値)：値の平均値を表示します。
- ・MAX(値)：値の最大値を表示します。
- ・MIN(値)：値の最小値を表示します。
- ・STDDEV(値)：値の標本標準偏差を表示します。
- ・VARIANCE(値)：値の分散標本を表示します。
文字列関数、文字列演算子は以下になります。
- ・CHAR_LENGTH(値)：文字数を表示します。
- ・LOWER(値)：値を小文字にして表示します。
- ・UPPER(値)：値を大文字にして表示します。
- ・SUBSTRING(値,開始位置,切取文字数)：値を開始位置から切取文字数までを表示します。
- ・REPLACE(値,検索文字,置換文字)：値に含まれる文字を検索文字で検索して置換文字に置換して表示します。
- ・TRIM(値,刈取文字)：値に含まれる文字から刈取文字を刈り取って表示します。
- ・値1 || 値2：値1と値2を結合して表示します。
- ・AGE(データ型 日にち)：今日 - 日にちを表示します。
- ・NOW()：現在の日付と時刻を表示します。
- ・CURRENT_DATE：現在の日付を表示します。
- ・CURRENT_TIME：現在の時刻を表示します。
- ・CURRENT_TIMESTAMP：現在の日付と時刻を表示します。
- ・STATEMENT_TIMESTAMP：現在の日付と時刻を表示します。
- ・CLOCK_TIMESTAMP
- ・EXTRACT(取得フィールド FROM データ型 日時)：日時から取得フィールドのみを表示します。
- ・TO_CHAR(日時,フォーマット)：文字列に変換
パラレルクエリ
検索結果を素早く表示するために複数のCPUを活用します。
Window関数
・関数 OVER (PARTITION BY 列名)：対象の列で範囲を区切
row_number() 行番号
rank() ランキング (同率で番号を飛ばす)
dense_rank() ランキング (同率で番号を飛ばさない)
percent_rank() ランキング(%で表示) : (rank - 1) / (全行数 - 1)
cume_dist() percent_rank に類似 : (現在の行の位置) / (全行数)
ntile(N) ランキング (1..Nに分割)
lag(value, offset, default) ソート状態での前の行の値
lead(value, offset, default) ソート状態での後の行の値
first_value(value) 最初の値
last_value(value) 最後の値
nth_value(value, N) N番目の値(1から数える)
・例1：SELECT DISTINCT SEX limit 4
・例2：SELECT Barcode,Name,Xtime FROM Members WHERE Barcode = '12345678901' ORDER BY Barcode WHERE Barcode BETWEEN 1000 AND 2000 GROUP BY JOB HAVING AVG(SAL) >= 2500
ーーー文字操作 repeat ーーー自己結合 --self join ーーーー照合順序：COLLATE -- select case aa WHEN `aa` THEN `abc` WHEN `bb` THEN `def` ELSE `other`END ーーーシーケンス nextval setval curval lastval
共通テーブル式について
共通テーブル式(別名：CTE[Common Table
Expression])は事前に一時テーブルを定義してその一時テーブルを問合せに利用できます。
2つのテーブル「T1」と「T2」が以下のようにあった場合にwith句で最初にT1を一時テーブルとして定義してそれを利用します。
WITH 一時テーブル名 AS (一時テーブルExpression)
Expressionのように記載します。
WITHの後に一時テーブル名を指定して、(Expression)に一時テーブルとする問合せ内容を記載します。
,で複数記載することも可能です。{WITH 一時テーブル名 AS
(一時テーブルExpression),一時テーブル名2 AS (一時テーブル2Expression)
Expression}
その後にExpression内に一時テーブル名を指定することが可能です。
T1テーブルを一時テーブルとして、T2とクロス結合場合の例を次に記載します。
クエリ：WITH CTE1 AS (SELECT * FROM T1) SELECT * FROM T2 CROSS JOIN
CTE1;
今まで一時テーブルを作成していたケースやクエリを簡潔に書きたい場合に効果的です。
-
・書式：INSERT INTO テーブル名 (列名,…) VALUES
(挿入するデータ,…)
RETURNING：戻りのIDを取得
- ・説明：指定したレコードをテーブルに挿入します。
- ・例：INSERT INTO public.user VALUES(1,'太郎')
実際のコマンドを次に記載します。
テーブル「user」にレコードを挿入
postgres=# INSERT INTO public.user (id,name) VALUES(1,'太郎')
- ・書式：UPDATE テーブル名 SET 列名 = 変更する値 WHERE 条件式
- ・説明：指定したレコードの値を変更します。条件を指定しない場合はそのテーブルの全レコードが更新されます。
実際のコマンドを次に記載します。
テーブル「user」の属性「id」が1の場合に属性「name」を太郎に変更
postgres=# UPDATE public.user SET name='太郎' WHERE id = 1;
- ・書式：DELETE FROM テーブル名 WHERE 条件式
- ・説明：指定したレコードを削除します。条件を指定しない場合はそのテーブルの全レコードが削除されます。
実際のコマンドを次に記載します。
テーブル「quiz」のレコードを全て削除
postgres=# DELETE FROM quiz;