ポスグレ参考書 in win10

psql.md

https://dotinstall.com/lessons/basic_postgresql

ログイン

• cmd上で

psql -d postgres -U postgres

データベース作成/削除/リネーム

create database <database-name>;
drop database <database-name>;
alter database <before> rename to <after>;

• 予約語のある名前は使えない

データベース一覧

select * from pg_database;

• \l でも可

データベースを選択

\c <database-name>

現在時刻の表示(now関数)

select now();

テーブルの作成/削除/リネーム

create table <database-name> (<column-name> <column-type> [constraint-type], ...);
drop table <database-name>;
alter table <before> rename to <after>;

• 型(type)の例:
  • 数値: integer(int), real, serial
  • 文字: char(5), varchar(255), text
  • 真偽: boolean TRUE FALSE t f
  • 日付: date, time, timestamp
  • その他: https://www.postgresql.jp/document/9.4/html/datatype.html
• 制約(constraint)の例:
  • not null
    • 空の値はエラー
  • unique
    • 重複した値を許さない
  • check()
    • check(length(body)) > 5: 5文字以上でないとエラー
  • default
    • デフォルト値を決定
  • primary key
    • not null + unique
• 補足
  • (created timestamp default current_timestamp) とすると現在時刻が入る

テーブル一覧/テーブル型情報一覧

\dt
\t <table-name>

外部SQLスクリプトを直接実行

\i <filename>.sql

• ログイン時のパスが参照される

コメント

-- comment
/*
comments
*/

データの挿入

insert into <table-name> (<column-name>, ...) values (<insert-value>, ...), ...;
-- select * from hoge;

表示の縦長/横長を切り替える(拡張表示)

\x

• カラムが多くてターミナルをはみ出すようなときに使う

特定のカラムのみ取得(SELECT)

select (<column-name>, ...) from <table-name>;

特定の条件のデータのみ取得(WHERE)

... where <condition>;
... where <condition> AND <condition> OR <condition>;
-- select * from <table-name> where <column-name> > 5.0;

• 演算子の例
  • > < <= >= = !=, != === <>
    • AND OR [NOT] BETWEEN IS [NOT] [...]
      • DISTINCT FROM UNKNOWN TRUE FALSE DISTINCT FROM NULL
  • 文字列で使える: like % _ @
    • like '%i': 最後に i がある
    • like 'a_c': _ の部分以外一致

並べ替え(ORDER BY)/TOPをピックアップ(LIMIT)/N件目から表示(OFFSET)

... order by <column-name> [asc/desc], ...;
-- select * from <table-name> order by <column-name_1>, <column-name_2> desc;
... limit <top-length>;
-- select * from <table-name> order by <column-name> limit 10;
... offset <index>;
-- select * from <table-name> order by <column-name> limit 5 offset 20;

テーブルのデータ数を取得

select count(*) from <table-name>;

特定のカラムの重複を除くデータのみ取得

select distinct <column-name> from <table-name>;

特定のカラムの合計(sum)/最大値(max)/最小値(min)/平均(avg)

select sum(<column-name>) from <table-name>;
select max(<column-name>) from <table-name>;
select min(<column-name>) from <table-name>;
select avg(<column-name>) from <table-name>;

もう一つのカラムのユニーク値(チーム等)ごとに集計したい場合(GROUP BY)

select <column-name_1>, sum(<column-name_2>) from <table-name> group by <column-name_1>;

さらに条件を加えたい場合(having)

select <cn1>, sum(<cn2>) from <table-name> group by <cn1> having sum(<cn2>) > 20.5;

カラムの名前変更表示(as)

select <before> as <after> from <table-name>;

様々な関数

• selectにかかわらず様々な場所で使える

-- 文字数
select length(<column-name>) from <table-name>;
-- 文字列の連結表示
select concat(<column-name>, ...) from <table-name>;
-- select concat(<column-name_1>, '_', <column-name_2>, '!') from <table-name>;
-- 文字列抽出
select substring(<column-name>, <from>, <to>) from <table-name>;
-- ランダムな数
select random();

データを編集(UPDATE)/削除(DELETE)

update <table-name> set <column-name> = <value> where <condition>;
-- update <table-name> set <column-name_1> = 100 where <column-name_2> = "blue";
-- update <table-name> set A = A + 100 where B = "blue" or C = FALSE;
delete from <table-name> where <condition>;
-- delete <table-name> where <column-name> = "blue";

カラムの追加/削除/リネーム/型変更

alter table <table-name> add <column-name> <column-type>;
alter table <table-name> drop <column-name>;
alter table <table-name> rename <before> to <after>;
alter table <table-name> alter <column-name> type <column-type>;

検索を早くするためにインデックスを追加/削除

create index <index-name> on <table-name>(<column-name>);
drop index <index-name>;

• 検索は早くなるが、更新時などには遅くなる。

複数のテーブルをまたがって使用する

• ドットをつければいい

select <table-name_1>.<column-name_1>, <table-name_2>.<column-name_2> from <table-name_1>, <table-name_2> where ...;

-- whereでselectを短くできる
select <T1>.<column-name_1>, <T2>.<column-name_2> from <table-name_1> <T1>, <table-name_2> <T2> where ...;
-- select f.one, s.two from first f, second s where ...;

クエリー文を一発入力するためにビューを作成/使用/削除/リネーム

• 一覧表示: \dv

create view <view-name> as <...>;
-- create view rnd as select random();
select * from <view-name>;
drop view <view-name>;
alter view <before> rename to <after>;

複数の処理をまとめて行うためにトランザクションする

begin;
/*
ここにいろいろな処理を書く
*/
commit;

• 実行中に取り消す場合は rollback; する。