【頻出】データベースやテーブル情報操作のまとめ

データベースやテーブル情報操作のうち、特に頻出のものをまとめました。

 

1.テーブル一覧の取得

--データベースに存在するテーブルの取得(そのまま実行してみてください)
select tablename from pg_tables
where schemaname != 'pg_catalog' order by tablename;

ここで詳しく説明しています → テーブル一覧とデータ件数を取得するSQL

 

 

2.テーブルの列一覧の取得

--'テーブル名'に存在する列一覧を取得
select * from information_schema.columns
where table_name='テーブル名' order by ordinal_position;

ここで詳しく説明しています → テーブルの列一覧を取得する

 

 

3.ビュー一覧の取得

--ビュー一覧の取得(そのまま実行してみてください)
select * From pg_views
where schemaname = 'public';

ここで詳しく説明しています → データベース情報を取得する(ビュー、トリガー、インデックス)

 

 

4.データベース一覧の取得

--pgAdminでそのまま実行してみてください。2カラム目はDBのエンコーディングを表示
select datname, pg_encoding_to_char(encoding) from pg_database;

ここで詳しく説明しています → データベース情報を取得する(ビュー、トリガー、インデックス)

 

 

5.シーケンス一覧の取得

--シーケンス一覧の取得(そのまま実行してみてください)
SELECT relname FROM pg_class 
JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE relkind = 'S';

 

 

6.シーケンスの初期化

select SETVAL('seq_name', 1 , false); --シーケンス「seq_name」を1に設定

ここで詳しく説明しています → シーケンス(serial)を作成する

 

 

7.トリガー一覧の取得

--トリガー一覧の取得
SELECT * FROM information_schema.triggers;

 

 

8.テーブルデータ数の一覧を表示する

--テーブルごとのデータ数を表示する(そのまま実行してみてください)
SELECT pg_class.relname , pg_class.reltuples
FROM pg_stat_user_tables
INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
order by cast(pg_class.reltuples as numeric) desc;

ここで詳しく説明しています → テーブル一覧とデータ件数を取得するSQL

 

 

9.インデックス情報の取得

--idx_scan列は、インデックススキャンを使用した回数
select * From pg_stat_user_indexes; 

 

 

10.ディスク容量を調べる

-- 1.データベースの物理容量を取得する
SELECT datname, pg_size_pretty(pg_database_size(datname)) 
FROM pg_database; 
 
-- 2.テーブルの物理容量を取得する
SELECT relname, reltuples as rows, (relpages * 8192) as bytes
,((relpages * 8192) / (1024 * 1024)) AS mbytes 
FROM pg_class order by mbytes desc;

ここで詳しく説明しています → テーブルサイズを確認するSQL

 

【頻出】メンテナンス操作のまとめ

メンテナンス操作のうち、特に頻出のものをまとめました。

 

1.手動VACUUM

VACUUM テーブル名;
VACUUM;  --テーブル名を省略すると、すべてのテーブルを対象とする

--次のselect文でvacuumの実行時間を確認できる
select relname , last_vacuum , last_autovacuum from pg_stat_all_tables;

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

VACUUMとは、VACUUM FULLの実行

AUTOVACUUMとは

 

 

2.手動ANALYZE

ANALYZE テーブル名;
ANALYZE;  --テーブル名を省略すると、すべてのテーブルを対象とする

--次のselect文でanalyzeの実行時間を確認できる
--autovacuum時にanalyzeが実行されるが、その時間がautoanalyzeとなる
select relname , last_analyze , last_autoanalyze from pg_stat_all_tables;

ここで詳しく説明しています → ANALYZEとは

 

 

3.CLUSTER

CLUSTER テーブル名 USING インデックス名;

ここで詳しく説明しています → CLUSTER(データをインデックス順に並び替える)

 

 

4.EXPLAIN , EXPLAIN ANALYZE

EXPLAIN SELECT文;
EXPLAIN ANALYZE SELECT文;

ここで詳しく説明しています → Explainの見方(analyze , cost , scan , sort)についてのまとめ

 

 

5.DBに接続しているセッションの確認

select * from pg_stat_activity where datname = 'データベース名';

ここで詳しく説明しています → 接続中のセッションを確認・切断する

 

 

6.接続中セッションの切断

--pidはこの上の『DBに接続しているセッションの確認』で確認する
SELECT pg_terminate_backend(pidを設定); 

ここで詳しく説明しています → 接続中のセッションを確認・切断する

 

【頻出】コマンド操作のまとめ

コマンド操作のうち、特に頻出のものをまとめました。

 

1.PostgreSQLへのログイン(データベースへの接続)

-- 接続コマンド
psql -h 接続先 -p ポート番号 -U ユーザー名 -d データベース名   -- Uは大文字のため注意

-- コマンド例
psql -h localhost -p 5432 -U postgres -d dbname
psql -h testserver -p 5432 -U postgres -d dbname   -- testserverはサーバー名
psql -h x.x.x.x -p 5432 -U postgres -d dbname      -- x.x.x.xはIPアドレス

-- オプションを指定せず接続も可能。ただしユーザー名とデータベースの順番が上と逆になる点に注意
psql データベース名 ユーザー名
psql dbname postgres

ここで詳しく説明しています → Windowsでpsqlの起動の仕方

 

 

2.ユーザー(=ロール)の作成

CREATE ROLE ロール名 WITH LOGIN PASSWORD 'password'; --PostgreSQLではユーザーをロールという

ここで詳しく説明しています → ユーザー(ロール)の作成、削除の方法

 

 

3.ユーザーの削除

DROP ROLE ロール名; 
--権限が割り当てによりエラーが発生する時、次(↓)の特権削除をする

ここで詳しく説明しています → ユーザー(ロール)の作成、削除の方法

 

 

4.ユーザーに関連付けられている特権を削除する

DROP OWNED BY ロール名;

 

 

5.ロールにテーブル、ビューの権限を付与

GRANT SELECT ON テーブル名 TO ロール名;          --SELECT権限を付与する
GRANT SELECT ON ビュー名 TO ロール名;            --SELECT権限を付与する
GRANT ALL PRIVILEGES ON テーブル名 To ロール名;  --すべての権限を付与する

ここで詳しく説明しています → ユーザー(ロール)にテーブル、ビューの権限を付与する、削除する

 

 

6.ロールにテーブル、ビューの権限を削除

REVOKE ALL PRIVILEGES ON テーブル名 FROM ロール名;
REVOKE ALL PRIVILEGES ON ビュー名 FROM ロール名;

ここで詳しく説明しています → ユーザー(ロール)にテーブル、ビューの権限を付与する、削除する

 

 

7.PostgreSQLサービスの開始・停止・再起動

--サービスの開始
net start postgresql-XXXXX
net start postgresql-x64-12  --PostgreSQL12の場合
--サービスの停止
net stop postgresql-XXXXX
net stop postgresql-x64-12   --PostgreSQL12の場合
--サービスの再起動(停止 → 起動の順番に実行する)
net stop postgresql-x64-12   --PostgreSQL12の場合
net start postgresql-x64-12

ここで詳しく説明しています → PostgreSQLサービスの開始、停止、再起動コマンド

 

 

8.バックアップ

--pg_dump.exeのありかまで、cdで移動(パスが通っている場合は不要)
cd C:\Program Files\PostgreSQL\bin
pg_dump -U ユーザー名 -f バックアップファイルのフルパス(拡張子は.backup) -Fc データベース名

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

pg_dumpコマンドを初心者向けに解説します

pg_dumpでパスワードを省略する(簡単)

pgAdminでデータベースのバックアップを取る方法

データベースをバックアップする(コマンド、bat)

 

 

9.リストア

--1.あらかじめ作っておいた空のデータベースにリストアする
pg_restore -U postgres -d データベース名 "バックアップファイルパス" 
--2.リストアと同時にデータベースを作成する(作られるDB名はバックアップと同じになる)
pg_restore -U ユーザー名 -C -d postgres "バックアップファイルパス"

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

Windowsでpg_dumpとpg_restoreの実行方法

データベース(DB)を復元(リストア)する

 

 

10.CSVからデータを登録する

--postgreSQLにログイン後(psqlで)実行します
¥copy テーブル名 from 'CSVファイルのフルパス' with csv         --CSVにヘッダ行がない場合
¥copy テーブル名 from 'CSVファイルのフルパス' with csv header  --CSVにヘッダ行がある場合

 

【頻出】文字列操作のまとめ

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

 

1.文字列の結合

select 'abc' || 'EFG';  --'abcEFG'
select  null || 'abc';  --null(nullと結合するとnullになるため注意)

ここで詳しく説明しています → 文字列の結合、nullの結合

 

 

2.文字数の取得

select length('abcdefgあいう');  --10(文字)

ここで詳しく説明しています → 改行コードで結合、置換する

 

 

3.文字列のバイト数を取得

select octet_length('abcde');   --5 (半角=1、全角=3バイト)
select octet_length('アイウえお');  --15(半角カタカナも1文字3バイトで計算)

ここで詳しく説明しています → 文字数のカウント(length)、バイト数を取得する

 

 

4.文字列の一部分を取得

select substring('abcdefghijk',2,3); -- 'bcd'  (2文字目から3文字取得する)
select left('abcdefghijk',5);        -- 'abcde'(左から5文字取得する)
select right('abcdefghijk',5);       -- 'ghijk'(右から5文字取得する)

ここで詳しく説明しています → 文字の切り取り(Left、Right、Substring)

 

 

5.検索文字位置の取得

select strpos('abcdefg','ef');                --5(=5文字目にある)
select strpos(upper('abcdefg'),upper('Cde')); --3(大文字小文字の区別なし)
select strpos('abcdefg','xyz');               --0(見つからない場合0となる)

ここで詳しく説明しています → 検索文字位置の取得(strpos)

 

 

6.スペース除去

select * from trim('   abc   ');    --'abc'  (前後のスペースを除去)
select * from trim('   a b c   ');  --'a b c'(文字間のスペースは除去されない)

ここで詳しく説明しています → 全角半角スペースを除去する(trim)

 

 

7.大文字・小文字の変換

select upper('abc');  --ABC(大文字へ変換)
select lower('EFG');  --efg(小文字へ変換)

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

lower(大文字を小文字に変換する)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

 

 

8.置換

select replace('everywhere','e','E');  --'EvErywhErE'(e ⇒ Eへ置換)

ここで詳しく説明しています → 文字を置換する、複数置換する(replace、translate)

 

 

9.前0埋め

select lpad('12345',10,'0');  --'0000012345'(10桁前0埋め)
select lpad(cast(12345 as character varying), 8 , '0'); --"00012345"(数値をcast)

ここで詳しく説明しています → 前0埋め、後ろ0埋め(lpad、rpad)

 

 

10.後ろスペース埋め

select rpad('12345',10,' ');  --'12345     '(後ろスペース埋め10桁)

ここで詳しく説明しています → 前0埋め、後ろ0埋め(lpad、rpad)

 

 

11.Nullの場合の処理

select * From COALESCE(null , 'abc');  --'abc'(一つ目の引数がnullなら二つ目の引数を返す)

ここで詳しく説明しています → Nullの時に別の値に置き換える

こちらも → nullif関数

 

 

12.ひらがな⇔カタカナ、全角⇔半角の変換(自作関数)

ひらがな→カタカナ  カタカナ→ひらがな  半角→全角  全角→半角

 

SQL例文の実行方法

SQL例文の実行方法がわからない場合、こちらで紹介しています。

※ 見づらい場合は再生ボタン後、右下の「Yotube」ボタンでyoutubeから視聴して下さい。