○○攻撃まとめ

セキュリティの攻撃はいろいろな種類と名前があります。試験対策用に代表的なものに加え、〇〇攻撃とつくものを列挙しました。覚えているかの確認用等にも使用していただけるとうれしいです。不定期更新予定。

 

SQLインジェクション

Web サイトの入力エリアに脆弱性があり、攻撃者がSQL文を含んだ文字を入力しそのままSQL が実行されること。データベース情報の窃取、コンテンツの改ざんなどを行う。※インジェクションとは英語で「注入」「挿入」を表す。

 

発生しうる脅威

・データベース情報の窃取、コンテンツの改ざんなど

 

対策

・シングルクオーテーションやダブルクオーテーションといった記号や文字をエスケープする

 

その他

・実例としてはこのサイトが一番わかりやすかった 

 

 

XSS(クロスサイトスクリプティング)

Webアプリケーションの脆弱性を利用して悪意のあるデータを埋め込みスクリプトを実行させる攻撃手法。サイトをまたいでスクリプトが移転・実行されることが「クロスサイト」の名称の由来となっているが、現在では攻撃者が用意したスクリプトを標的サイトのものとして実行させられてしまう攻撃全般をクロスサイトスクリプティングと呼んでいる。

(画像出典):IPA 安全なウェブサイトの作り方 - 1.5 クロスサイト・スクリプティング

 

発生しうる脅威

・本物サイト上に偽のページが表示され、フィッシング詐欺にあう

・ブラウザが保存しているCookieを取得される(セッションハイジャック)

・ウェブサイトの改ざん

 

対策

・入力値の制限

・サニタイジング(エスケープ)

より詳細な対策はIPAを参照(こちら

 

その他

・イメージをつかむだけならこのサイトがおすすめ

XSS攻撃体験サイト

 

 

クリックジャッキング

クリックジャッキングとは、iframe(インラインフレーム)で表示させた透明なページを通常のウェブページの上にかぶせ、視覚的に騙してクリックを行わせる攻撃手法のこと。

(画像引用):yamory Blog「悪意あるサイトにこっそり誘導 クリックジャッキング」

 

発生しうる脅威

・ログイン後の利用者のみが利用可能なサービスの悪用(利用者が意図しない情報発信、利用者が意図しない退会処理 等)

・ログイン後の利用者のみが編集可能な設定の変更(利用者情報の公開範囲の意図しない変更等)

 

対策

・HTTPレスポンスヘッダに、X-Frame-Optionsヘッダフィールドを出力し、他ドメインのサイトからのframe要素やiframe要素による読み込みを制限する。

・処理を実行する直前のページで再度パスワードの入力を求め、実行ページでは、再度入力されたパスワードが正しい場合のみ処理を実行する。

・重要な処理は、一連の操作をマウスのみで実行できないようにする。

 

 

CSRF

Cross Site Request Forgery(クロスサイトリクエストフォージェリ)。ユーザーがターゲットとなるウェブサイトへログインしている状態で、攻撃者が用意した罠サイトにアクセスを行う。その罠サイトでのリンククリック等により、ターゲットのウェブサイトに(偽のリクエストを送るなどして)ユーザーの予期しない処理を実行させること。例えば、ショッピングサイト○天にログインしている状態で、罠サイトのリンクをクリックしてしまうと、○天の最終決済や退会等の処理を実行する等。

(画像出典):安全なウェブサイトの作り方 - 1.6 CSRF(クロスサイト・リクエスト・フォージェリ)

 

発生しうる脅威

・Webサイトにログイン後可能な処理をされる

 不正な送金、利用者が意図しない商品購入、利用者が意図しない退会処理

  各種設定の不正な変更(管理者画面、パスワード等)、掲示板への不適切な書き込み

 

対策

・Webサーバー側で、ページのhiddenに乱数(ワンタイムパスワード)を埋めこみそれを検証する

・処理を実行する直前のページで再度パスワードの入力を求め、実行ページでは、再度入力されたパスワードが正しい場合のみ処理を実行する

・重要な操作を行った際に、その旨を登録済みのメールアドレスに自動送信する(保護的な対策)

 

 

セッションハイジャック

何らかの手段でセッションIDやcookie情報を窃取し、本人に成り代わって通信を行うこと。

 

セッションIDを取得する手段

・セッションIDの推測

セッションIDの生成規則を割り出しセッションIDを推測する

・セッションIDの盗用

罠を仕掛ける、ネットワークを盗聴する等でセッションIDを盗む

・セッションIDを取得する方法ではなく、セッションIDを指定させる方法もある(セッションID固定化)

攻撃者が予め準備したセッションIDをユーザに送り込み、ユーザがそのセッションIDでログインする

 

発生しうる脅威

・ログイン後の利用者のみが利用可能なサービスの悪用や閲覧・改ざん

不正な送金、利用者が意図しない商品購入、利用者が意図しない退会処理 等

各種設定の不正な変更(管理者画面、パスワード等)、掲示板への不適切な書き込み 等

非公開の個人情報を不正閲覧、ウェブメールを不正閲覧、コミュニティ会員専用の掲示板を不正閲覧 等

 

対策

・セッションIDを推測が困難なものにする

・セッションIDをURLパラメータに格納しない

・HTTPS通信で利用するCookieにはsecure属性を加える

・ログイン後に、新しくセッションを開始する

・ログイン後、既存のセッションIDとは別に秘密情報を発行し、ページ遷移ごとにその値を確認する

 

OSコマンドインジェクション

ソフトウェアなどのセキュリティホールが発見されてから、その情報公開や対策が講じられる前に、そのセキュリティホールを狙う攻撃のこと。

 

 

ゼロデイ攻撃

ソフトウェアなどのセキュリティホールが発見されてから、その情報公開や対策が講じられる前に、そのセキュリティホールを狙う攻撃のこと。

 

 

○○攻撃まとめ

分類はサイトによってまちまちのため保留にします。忘れていないかをチェックするようにサイバー攻撃を列挙。

・ 中間者攻撃

・ バッファオーバーフロー攻撃

・ セッションハイジャック

・ SQLインジェクション

・ ドライブバイダウンロード

・ DOS攻撃、DDOS攻撃

・ F5アタック

・ UDPフラッド攻撃(ランダム・ポート・フラッド攻撃)

・ Pingフラッド

・ SYNフラッド攻撃/FINフラッド攻撃

・ DNS Flood attacks(DNSフラッド攻撃)

・ DNSキャッシュポイズニング

・ DNS amp攻撃

・ ARPキャッシュポイズニング(ARPスプーフィング)

・ ブルートフォースアタック、リバースブルートフォース攻撃

・ パスワードリスト攻撃

・ クレデンシャルスタッフィング攻撃

・ 辞書攻撃

・ 水飲み場型攻撃

・ 標的型攻撃

・ APT攻撃

・ サプライチェーン攻撃

・ キーロガー

・ スミッシング

・ ジュースジャッキング攻撃

・ タイポスクワッティング(URLハイジャック)

・ スロー攻撃

・ POODLE攻撃

・ LDAPインジェクション攻撃

・ カミンスキー攻撃

・ ディレクトリトラバーサル

・ テンペスト攻撃

・ サイドチャネル攻撃

・ ガンブラー攻撃

・ フィッシング詐欺

・ ゼロクリック詐欺

・ ランサムウェア

設定ファイル(postgresql.conf)とは

・ postgresql.confはPostgreSQLについての様々な設定をするファイルです

・ パラメータは1行に1つだけ設定できます

・ #はコメントに使われる記号で、それより右側がコメントとなります(途中からコメントアウトでOK)。

    行頭に書けば、行全体がコメント行になります

・ 設定ファイルを変更した場合、ファイルの再読み込みにより有効になるパラメータと、データベースの再起動が必要になるパラメータがあります

 

 

設定ファイル(postgresql.conf)の場所

設定ファイルは、初期設定で『C:\Program Files\PostgreSQL\XX\data\postgresql.conf』にあります。

 

 

設定値の確認方法

設定値を確認するには「SHOW」を使用します。

--設定値を確認する
SHOW ALL;
SHOW 設定値名;

--設定値を確認する例
SHOW max_connections;       -- 100
SHOW shared_buffers;        -- 128MB

 

 

変更した設定値の反映させる

設定値を修正後、反映するには設定ファイルの再読み込みもしくは、PostgreSQLの再起動が必要になります。どちらが必要になるかは項目ごとに異なるため注意が必要です。

 

あわせて読みたい

https://postgresweb.com/post-5530

 

 

設定できる値について

・ 数値型の場合、kB(キロバイト)、MB(メガバイト)、GB(ギガバイト)の単位が指定可能

・ 時間型の場合、d(日)、h(時間)、min(分)、s(秒)、ms(ミリ秒)を指定可能。指定しない場合、項目ごとのデフォルトの単位が使用される。注意点として、msはミリ秒であり、マイクロ秒ではない。また、分の指定はmではなく、min。月単位の指定は不可。単位は大文字と小文字が区別されることも注意。

・ Boolean型(=論理値型、論理値データ型と表現される場合あり)は、デフォルトのonもしくはoffのほか、trueもしくはfalse、あるいは、1もしくは0をとることができる

・ コメントは「#」に続けて書く。「- -」や「/* */」は使用不可

 

 

設定項目について

それぞれの設定値について説明と補足です。

 

max_connections

説明

・ データベースサーバーへの同時最大接続数を指定する(接続数が最大値に達するとエラーが発生)

・ max_connectionはその値にあわせて起動時にマシンリソースを確保するため、ただ大きく設定すればよいというものではない

・ データベースクラスタ単位で設定するもので、データベース単位で設定で設定するものではない

・ superuser_reserved_connections(この下)はmax_connectionsに含まれるため、スーパーユーザーの数を含めて考慮が必要

・ 初期値は100

 

値の反映

・ PostgreSQLの再起動が必要(reloadでは不可)

 

 

superuser_reserved_connections

説明

・ スーパーユーザーのために予約されている接続数を指定する。接続数がmax_connectionのパラメータを超えたときにスーパーユーザーが接続できなくなることを防ぐ。

・ デフォルトは3。

 

値の反映

・ PostgreSQLの再起動が必要(reloadでは不可)

 

 

shared_buffers

説明

・ メモリサイズを指定する。デフォルトで128MB

・ 1GB以上のRAMを載せた専用データベースサーバの場合、妥当な初期値はシステムメモリの25%

 

値の反映

・ PostgreSQLの再起動が必要(reloadでは不可)

 

 

port

・ 接続するポートを指定する。デフォルトは5432

 

log_destination

説明

・ log_destinatonはログの出力先を指定するパラメータ

・ stderr、syslog、eventlog、csvlogの4種類の値が指定できる

・ eventlogはWindows上で動作するPostgreSQLのみ指定ができる

・ csvlogは出力先ではなく、ログの形式を表す値です。これは出力先を表す値とカンマ区切りで組み合わせて指定する

・ csvlogを指定するとログの項目がカンマ区切りの書式(CSV)で出力される

 

値の反映

・ PostgreSQLの再読み込みでOK(reloadでOK)

 

 

log_checkpoints

説明

・ チェックポイント処理が発生したことをログに出力。デフォルトはoff

 

値の反映

・ PostgreSQLの再読み込みでOK(reloadでOK)

 

 

log_connections

・ クライアントからサーバに接続されたこと(サーバへの接続試行)をログに出力。デフォルトはoff

 

log_directory

・ ログの出力先となるディレクトリをlog_directoryで設定します

・ ディレクトリは絶対パスまたは、データベースクラスタディレクトリからの相対パスで指定する

 

log_disconnections

・ クライアントのサーバ接続終了をログに出力

 

log_filename

・ ログのファイル名

 

log_line_prefix

・ ログメッセージの先頭行に追記する情報

 

log_rotation_age

・ 時間契機のローテーション有効・無効化

 

log_statement

・ 指定した種類のSQLが発行されたことをログに出力

 

log_truncate_on_rotation

・ ログファイルのローテーション時に同名ファイルがあった時に上書きするか追加するかの設定

 

temp_buffers

・ ソートなどセッションが一時的に使用するメモリを指定する

 

maintenance_work_mem

・ VACUUMやCREATE INDEXなどの保守作業に使用されるメモリサイズを指定する

 

wal_buffers

・ WAL(Write-Ahead Log)データ用に共有メモリで確保されるメモリサイズを指定する。PostgreSQLではバッファキャッシュの内容はトランザクションがコミットされるかバッファがあふれるごとに書き出される

 

COPYコマンドとは

COPY文はPostgreSQLによるSQLの独自拡張で、テーブルの内容をカンマ、タブ区切りのテキストファイルに出力したり、あるいはテキストファイルを読み込んでテーブルにコピーする時に使うコマンドです。

 

 

CSVからテーブルにデータをコピーする

CSVファイルの内容をテーブルにコピー(インポート)するコマンド例を紹介します。コマンドはpsqlで実行して下さい。※ CSVの各項目がダブルクォーテーションで囲まれている・いないどちらでも同じ動作をします。

# CSVの内容をテーブルにコピー(インポート)する構文
\copy テーブル名 from 'CSVファイルのフルパス'

# カンマ区切り、ヘッダ行なし
\copy test_table from 'CSVファイルのフルパス'

# カンマ区切り、ヘッダ行あり
\copy test_table from 'CSVファイルのフルパス' with csv header

# タブ区切り、ヘッダ行あり
\copy test_table from 'CSVファイルのフルパス' CSV DELIMITER E'\t' HEADER

# encoding
\copy test_table from 'CSVファイルのフルパス' WITH CSV HEADER encoding 'sjis'
\copy test_table from 'CSVファイルのフルパス' WITH CSV HEADER encoding 'UTF8'

補足:WITH CSV HEADER:CSVにヘッダ行があり、ヘッダーを読み込まないようにする

区切り文字はタブの時だけ指定(カンマ区切り用のオプションの「WITH CSV DELIMITER ‘,’」はなくてもOK)

 

 

テーブルからファイルにデータを出力する

テーブルの内容をファイル出力する(エクスポート)するコマンド例を紹介します。コマンドはpsqlで実行して下さい。

# CSVファイルを出力
\COPY テーブル名 TO 'CSVファイルパス' DELIMITER ',';
\COPY テーブル名 TO 'CSVファイルパス' WITH CSV;

# CSVファイルをヘッダを含めて出力
\COPY テーブル名 TO 'CSVファイルパス' WITH CSV HEADER;

# CSVファイルをダブルクォーテーションで囲む
\COPY テーブル名 TO 'CSVファイルパス' WITH CSV FORCE QUOTE *;

# 列を指定してCSV出力
\COPY テーブル名(列名1,列名2,…) TO 'CSVファイルパス' DELIMITER ',';

# SELECT文の結果を出力
\COPY (SELECT * FROM テーブル名 where 抽出条件 ) TO 'CSVファイルパス' DELIMITER ',';

# SELECT文の結果を出力しタブ区切りで出力
\COPY (SELECT * FROM テーブル名 where 抽出条件 ) TO 'CSVファイルパス' DELIMITER E'\t';

【頻出】数値操作のまとめ

数値操作のうち、特に頻出のものをまとめました。

 

1.数値の書式設定

select to_char(12345.67, 'FM999,999.999'); --"12,345.67"  少数3桁カンマ区切り
select to_char(12345.67, 'FM999,999.000'); --"12,345.670" 少数3桁でカンマ区切り

ここで詳しく説明しています → 数値を文字に変換する(to_char)

 

2.絶対値を取得

select * from abs(-5);  --5

ここで詳しく説明しています → 絶対値を取得する(ABS)

 

3.割り算の余りを取得

select * from mod(11,3);  --2(11÷3の余り)

ここで詳しく説明しています → 余りを計算する(mod)、偶数奇数によって処理を分ける

 

4.四捨五入

select * From round(123.45678,3);  --123.457(小数第3位四捨五入)
select * From trunc(123.45678,3);  --123.456(小数第3位切り捨て)
--切り上げを小数第1位までとする時:0.09を足して、小数第1位で切り捨てる

ここで詳しく説明しています → 四捨五入・切り上げ・切り捨て(Round)【自作関数】

 

5.ランダム値

select * from random(); --0.896430・・・
--範囲指定でのランダム値:最小値=1,最大値=10,000の時(値は適宜変更してください)
select round(( random() * (1 - 10000) )::numeric, 0) + 10000; --7000

ここで詳しく説明しています → ランダム値を生成する(random)

 

6.こちらもおすすめ

数値であるかのチェック(IsNumeric)

 

【頻出】日付操作のまとめ

日付操作のうち、特に頻出のものをまとめました。

 

1.現在日時の取得

select * from current_date;      --"2020-06-11"
select * from current_time;      --"22:00:00.000000+09"
select * from now();             --"2020-06-11 22:00:00.000000+09"
select * from current_timestamp; --"2020-06-11 22:00:00.000000+09"
--now()とcurrent_timestampの違いはありません

ここで詳しく説明しています → 現在日時、システム日時の取得

 

2.日付の書式設定

select * from to_char(now(),'YYYY/MM/DD');  --2020/04/01
select * From to_char(now(), 'HH:MI:SS');   --10:34:56(12時間表記)
select * from to_char(now(), 'HH24:MI:SS'); --22:34:56(24時間表記)
select * from to_char(now(),'YYYY/MM/DD HH24:MI:SS'); --2020/04/01 22:34:56

ここで詳しく説明しています → 日時、時刻の書式設定をする(yyyymmdd形式)

 

3.月、週、日、時刻を加算する

select current_date + cast('5 months' as INTERVAL);  --"2020-11-12 00:00:00"
select current_date + cast('5 weeks' as INTERVAL);   --"2020-07-17 00:00:00"
select current_date + cast('5 days' as INTERVAL);    --"2020-06-17 00:00:00"
select current_date + cast('5 hours' as INTERVAL);   --"2020-06-12 05:00:00"
select current_date + cast('5 minutes' as INTERVAL); --"2020-06-12 00:05:00"
select current_date + cast('5 seconds' as INTERVAL); --"2020-06-12 00:00:05"

ここで詳しく説明しています → 日付の加算、週の加算、月の加算

 

4.曜日を取得する

select date_part('dow',current_date); --0:日、1:月、2:火、3:水、4:木、5:金、6:土

ここで詳しく説明しています → 曜日を取得する

 

5.月末の日を取得する

select date(date_trunc('month',current_date) + '1 month' + '-1 day') --2020/6/30

ここで詳しく説明しています → 月末日を取得する(LAST_DAY)

 

6.こちらもおすすめ

日付であるかのチェック(IsDate)  西暦から和暦へ変換する  次の○曜日を取得

 

【頻出】型の変換操作のまとめ

型の変換操作のうち、特に頻出のものをまとめました。

 

1.文字 ⇒ 日付

select * from cast('20190401' as date);  --2019-04-01

ここで詳しく説明しています → 文字を日付に変換する、書式を設定する

 

2.文字 ⇒ timestamp

select * from cast('20190401 123456' as timestamp); --2019-04-01 12:34:56

ここで詳しく説明しています → 文字を日付に変換する、書式を設定する

 

3.文字 ⇒ 数値

select * From cast('0123' as integer);     -- 123
select * From cast('0123.45' as numeric);  -- 123.45

ここで詳しく説明しています → 文字から数値、数値から文字へ変換する

 

4.数値 ⇒ 文字

select * From cast(12345 as character varying);  -- '12345'
select * From cast(45678 as text);               -- '45678'

ここで詳しく説明しています → 文字から数値、数値から文字へ変換する

 

5.数値 ⇒ 日付

select * From cast(cast(20190401 as character varying(8)) as date); --2019-04-01

ここで詳しく説明しています → 文字を日付に変換する、書式を設定する(一番下に記載)

 

6.こちらもおすすめ

文字型の型について整理してみた

数値型の型について整理してみた

booleanについて整理してみた

textとvarcharの違い

timestampのwithout time zoneとwith time zoneの違い

textからvarcharへの変換、varcharからtextへの変換の方法

 

【頻出】テーブルの作成・列の変更操作のまとめ

テーブルの作成・列の変更操作のうち、特に頻出のものをまとめました。

 

1.CREATE TABLE(+DELETE CASCADE)

/*従業員テーブルを作成し、
部署マスタ(department)の部署コードに対し外部キー制約を付ける(DELETE CASCADE)*/
CREATE TABLE employee
(
    employee_code character varying(10) NOT NULL,
	nm character varying(100),
	department_code  character varying(10),
	--(略)
    CONSTRAINT 主キー名 PRIMARY KEY (employee_code),  --主キー
	CONSTRAINT 外部キー名 FOREIGN KEY (department_code)    --外部キー
	REFERENCES department (department_code) ON DELETE CASCADE on update no action --DELETE CASCADE
);

--DELETE CASCADEしない時は、最後の行を「CASCADE」→「no action」にする
--例↓
--REFERENCES busyo (busyo_code) ON DELETE no action on update no action

ここで詳しく説明しています

テーブルを作成する(CREATE TABLE文の構文説明)

 

関連投稿

テーブルを作成する方法(pgAdminで作成する)

テーブルを作成する方法(CREATE文で作成する)

 

 

2.テーブル作成(テンプレ文)

--table1
CREATE TABLE table1
(
	cd1 integer not null,
	nm1 text,
	CONSTRAINT pk_table1 PRIMARY KEY (cd1)
);

--table2
CREATE TABLE table2
(
	cd2 integer not null,
	cd1 integer,
	nm2 text,
	CONSTRAINT pk_table2 PRIMARY KEY (cd2),
	CONSTRAINT fk_tabel2_table1 FOREIGN KEY (cd1)
	REFERENCES table1 (cd1) 
	ON UPDATE NO ACTION
	ON DELETE CASCADE
);

--テストデータ用(使用する場合はコメント解除)
/*
insert into table1 values(1,'A');
insert into table1 values(2,'B');
insert into table1 values(3,'C');
insert into table2 values(1,1,'a');
insert into table2 values(2,1,'b');
insert into table2 values(3,2,'c');
insert into table2 values(4,2,'d');
insert into table2 values(5,3,'e');
insert into table2 values(6,3,'f');
*/

 

 

3.列の追加・変更・削除

--列の追加
ALTER TABLE テーブル名 add 列名 型;

--Not Null制約の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET NOT NULL;

--デフォルト値の追加
ALTER TABLE テーブル名 ALTER COLUMN 列名 SET DEFAULT デフォルト値;

--列の削除
ALTER TABLE テーブル名 DROP COLUMN 列名;

--外部キー制約の追加
ALTER TABLE テーブル名
ADD FOREIGN KEY (外部キーを付けるテーブルの列名) REFERENCES 参照先テーブル名 (参照先列名);

--インデックスの作成
CREATE INDEX インデックス名 ON テーブル名 (列名1 , 列名2 , ・・・ );

--インデックスの削除
DROP INDEX インデックス名;

ここで詳しく説明しています

テーブルに列を追加する、削除する方法、桁数を変更する方法

列にデフォルト値(初期値)を追加・削除する方法

列にNOT NULL制約を追加・削除する方法

外部キーを追加・削除する方法

プライマリーキーとユニークキーの違い

INDEXを追加、削除する

INDEXを再構築する(REINDEX)

 

 

4.データベース・テーブル・列にコメントを付加する

COMMENT ON DATABASE データベース名 IS 'コメントです';    --データベースへコメント
COMMENT ON TABLE テーブル名 IS 'コメントです';           --テーブルへコメント
COMMENT ON COLUMN テーブル名.列名 IS 'コメントです';      --列へコメント
COMMENT ON COLUMN テーブル名.列名 IS NULL;               --コメントの削除

ここで詳しく説明しています

データベース、テーブル、列へコメントをつける

 

 

5.生成列(自動で計算される列)

--生成列を作る(CREATE TABLEの列に書く)
列名 型 GENERATED ALWAYS AS 式 STORED

CREATE TABLE public.test
(   ・・・
    height numeric,
    weight numeric,
    --生成列 : BMI( 体重 ÷ 身長(m)の2乗)の作成
    bmi numeric(3,1) GENERATED ALWAYS AS (weight / ((height / 100) * (height / 100))) STORED,
    ・・・
)

ここで詳しく説明しています

生成列を定義する

 

 

6.こちらもおすすめ

文字型の型について整理してみた

数値型の型について整理してみた

booleanについて整理してみた

textとvarcharの違い

timestampのwithout time zoneとwith time zoneの違い

textからvarcharへの変換、varcharからtextへの変換の方法

シーケンス(serial)を作成する

列挙型(ENUM)の書き方

 

【頻出】ビュー操作のまとめ

ビュー操作のうち、特に頻出のものをまとめました。

 

1.ビューの作成

CREATE OR REPLACE VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;

ここで詳しく説明しています → ビュー(VIEW)を作成する、削除する

 

2.ビューの削除

DROP VIEW ビュー名;

ここで詳しく説明しています → ビュー(VIEW)を作成する、削除する

 

3.マテリアライズドビューの作成

--ビュー作成の構文にMATERIALIZEDがつく形
CREATE MATERIALIZED VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名;

ここで詳しく説明しています → マテリアライズドビュー(マテビュー)を作成する

 

4.マテリアライズドビューのINDEX作成

CREATE UNIQUE INDEX ON マテビュー名(列1 , 列2 , ・・・);

 

5.マテリアライズドビューの更新

REFRESH MATERIALIZED VIEW マテビュー名;              --通常のリフレッシュ文
REFRESH MATERIALIZED VIEW CONCURRENTLY マテビュー名; --←これにはINDEX作成が必要

 

6.ウィンドウ関数

SELECT 
rank() OVER(PARTITION BY 列名 order by 列名 desc) AS aaa
--avg(列名)、min(列名)、max(列名)、sum(列名)等も使える 
--filterで集計する対象を指定できる。FROMの後のWHEREとは意味が異なる点に注意。
, avg(列名) filter(where 条件文) OVER(PARTITION BY 列名 order by 列名 desc) AS bbb
FROM テーブル名
WHERE XXXX --ここの条件文はSELECT文そのものの抽出条件

 

【頻出】配列操作のまとめ

配列操作のうち、特に頻出のものをまとめました。

 

1.配列の要素数

select array_length(array['A','B','C'],1); --3 ※最後の",1"を忘れないように注意

ここで詳しく説明しています → 配列の要素数を取得する

 

2.配列の要素数分ループ(関数で使用)

for i in 1..array_length(arr,1) loop  --arrは配列の変数
raise info '%' , arr[i]; --ここに処理を書く(raise info:配列の中身を表示する) 
end loop;

ここで詳しく説明しています → 配列をループさせる

 

【頻出】関数・プロシージャ操作のまとめ

関数・プロシージャ操作のうち、特に頻出のものをまとめました。

 

1.変数の宣言

variable1   character(10);             --文字
variable2   character varying(10);     --文字
variable3   integer;                   --数値
variable4   numeric;                   --数値
variable5   boolean;                   --true/false
variable6   RECORD;                    --レコード
variable7   テーブル名%ROWTYPE;         --存在するテーブルの行
variable8   テーブル名.カラム名%TYPE;   --存在するテーブルの列
arr1        character varying[];       --配列
arr2        integer[];                 --配列
variable9   integer:=3;                --数値(初期値を設定)

ここで詳しく説明しています → 変数の宣言、変数宣言時に代入する、定数を宣言する方法

 

 

2.定数(constant)の宣言

variable1 constant character varying(10) := 'abcdefghij';
variable2 constant numeric := 1.08;  --定数にするには「constant」をつける

ここで詳しく説明しています → 変数の宣言、変数宣言時に代入する、定数を宣言する方法

 

 

3.1行コメント、複数行コメント

--1行コメント
/* 複数行のコメント1行目
   複数行のコメント2行目*/

ここで詳しく説明しています → コメントの書き方

 

 

4.比較演算子

if (a > b) and (a < c) then   --大なり小なり 
if (a >= b) or (a <= c) then --大なりイコール、小なりイコール
if (a = b) and (a != c) then  --イコール、ノットイコール

ここで詳しく説明しています → 比較演算子の紹介

 

 

5.IF文

if variable1 = 1 then
	--処理1;
elseif variable1 = 2 then
	--処理2;
else
	--処理3;
end if;
 
--否定の場合(!=をつける)
if variable1 != 1 then ・・・
 
--「select * into rec・・・」で取得した結果がない時の条件分岐例
if rec is null then ・・・

ここで詳しく説明しています → IF文の書き方(条件分岐)

 

 

6.SELECT結果を変数へ代入する①

select 列名 into 変数 from テーブル名; --「列名」の値を変数へ代入する
 
--例
select emp_name into nm from m_employee where ・・・;

ここで詳しく説明しています → SQLの結果を変数に格納する

 

 

7.SELECT結果を変数へ代入する②

select * into 変数 from テーブル名;  --SELECTで取得した行を変数へ入れる
 
--例
rec record;                         --変数「rec」をrecord型で宣言(declareへ書く)
select * into rec from m_employee;  --m_employeeをSELECTした結果をrecへ代入
if rec.code = '' then …             --recの列「code」の値で条件分岐

ここで詳しく説明しています → SQLの結果を変数に格納する

 

 

8.変数の中身を表示する(raise)

raise info '%' , 変数名;  --変数の中身を表示する
raise info '%' , var1;    --変数var1の中身を表示する例
--変数の中身は「データ出力」の2つ右にある「メッセージ」タブに表示されます

ここで詳しく説明しています → 変数の値を画面に出す(raise)

 

 

9.For文(単純なループ)

for i in 1..10 loop --1から10まで繰り返す
--処理内容
end loop;
 
for i in 1..10 loop
	IF i > 5 THEN
		EXIT;  --「EXIT」でループを抜け出す
	END IF;
end loop;

ここで詳しく説明しています → For文の書き方、ループから抜ける方法

 

 

10.For文(SELECT文の結果をループする方法)

FOR rec
	-- 列Aが'test'のレコードを取得しその結果をrecに入れる
	IN EXECUTE 'select * from XXX where 列A = ''test'' '
LOOP
	raise info '%', rec.code; --codeの値を表示
END LOOP;

ここで詳しく説明しています → SQL(SELECT文)の結果をループする方法

 

 

11.While文

while 条件文 loop
	--条件文がtrueの時にこの処理を繰り返す
	--処理
end loop;

ここで詳しく説明しています → While文の書き方、途中で抜ける方法

 

 

12.関数(FUNCTION)の作成・書き方 テンプレ用

CREATE OR REPLACE FUNCTION testfnc              --ファンクション名は適宜変更する
(arg1 character varying,arg2 numeric)           --引数をこの行で設定
RETURNS boolean                                 --返り値の型を定義
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$  --この行は変更しない
declare
begin
	--処理を記載
	return true;   -- 返り値はboolean型なのでここはbooleanでリターン(とりあえず)
end;
$BODY$;

ここで詳しく説明しています

関数(FUNCTION)の作り方・書き方を解説する

CREATE FUNCTIONのテンプレート

 

 

13.関数の実行の仕方

--上のテンプレ関数作成を実行する(上の関数を作成後、コピー&貼り付けで実行できます)
select * From testfnc('a',1);
select testfnc('a',1);       --どちらでも同じ(結果も変わらない)

ここで詳しく説明しています → 関数(FUNCTION)を実行する・呼び出す方法

 

 

14.関数を削除する

--関数「testfnc」を削除する。引数も含んで書かなければならない点に注意
--DROP FUNCTION 関数名(引数);
DROP FUNCTION testfnc(arg1 character varying,arg2 numeric);

ここで詳しく説明しています → 関数(FUNCTION)を削除する方法

 

 

15.プロシージャ(PROCEDURE)の作成・書き方 テンプレ用

CREATE OR REPLACE PROCEDURE testproc(                   --プロシージャ名は要変更
INOUT num1 integer,INOUT num2 integer,IN num3 integer)  --引数(下に補足あり)
--引数:IN=引数のみ、INOUT=引数+返り値としても使用される ※OUTのみは不可
LANGUAGE 'plpgsql' AS $BODY$
BEGIN
	--処理を記載
END;
$BODY$;

ここで詳しく説明しています → CREATE PROCEDUREのテンプレート

 

 

16.プロシージャの実行の仕方

call testproc(1,2,3);  --プロシージャはcallを使う(selectは不可)

ここで詳しく説明しています → プロシージャ(PROCEDURE)を実行する・呼び出す方法

 

 

17.EXCEPTION

BEGIN
	--(略)
	EXCEPTION  --BEGINとENDの間に書く
	WHEN OTHERS THEN
	--エラーになった時の処理をここに書く
END;

ここで詳しく説明しています → エラーを取得する(EXCEPTION)