PostgreSQLのチートシート

はじめに

学校でPostgreSQLを使わなければならなくなったので、使い方を一覧にしてまとめようと思います。ただどんなことを載せるかは独断と偏見で選んでいるのでご注意を。

またwindows10への導入の仕方が分からない場合は以下の記事を是非参考にしてみてください。

www.hanachiru-blog.com

接続

psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名

ローカルホストの場合はホスト名は省略可，ポート番号も省略可。ロール名と同じデーベースに接続する場合はデータベース名を省略可。

psql -U postgres

切断

¥q

テーブル、ビュー、シーケンスの一覧を表示

¥d

慣習

PostgreSQLでは慣習的にSQLコマンドを大文字で書くそうです。ただ後ほど紹介する集約関数は小文字で表記するみたい。でもどっちでも動作はします。

また改行は好きなところでして(しなくても)大丈夫です。

SELECT count(*)
FROM 一覧;

テーブルの作成

CREATE TABLE 履修 (
  科目番号 CHAR(3) NOT NULL,
  学籍番号 CHAR(6) NOT NULL,
  成績 INT,
  PRIMARY KEY (科目番号) REFERENCES 科目(科目番号),
  PRIMARY KEY (学籍番号) REFERENCES 学生(学籍番号),
  CHECK (0 <= 成績 AND 成績 <= 100)
);

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER

テーブルの削除

DROP TABLE 履修;

データの挿入

INSERT INTO 科目 VALUES('002', '勇者学', 1);

〇科目

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER
001	モンスター学	2

↓

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER
001	モンスター学	2
002	勇者学	1

データの参照

SELECT * FROM 科目;

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER
001	モンスター学	2
002	勇者学	1

SELECT 科目名 FROM 科目 WHERE 科目番号='002';

科目名
勇者学

データの削除

DELETE FROM 履修 WHERE 科目番号='002' AND 学籍番号='A0001';

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
002	A0001	70
002	A0004	90

↓

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
002	A0004	90

データの更新

UPDATE 履修 SET 成績=100 WHERE 科目番号='002' AND 学籍番号='A0001';

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
002	A0001	70

↓

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
002	A0001	100

重複行の削除

SELECT DISTINCT 科目番号 FROM 履修;

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
001	A0001	90
001	A0002	80
001	A0003	60
002	A0001	70
002	A0004	90

↓

科目番号
001
002

並び替え

SELECT * FROM 履修 ORDER BY 成績 ASC;

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
001	A0003	60
002	A0001	70
002	A0004	90

SELECT * FROM 履修 ORDER BY 成績 DESC;

直積(テーブルの結合)

SELECT * FROM 科目,履修;

θ結合

SELECT * FROM 科目,履修 WHERE 科目.科目番号=履修.科目番号;

自然結合

SELECT * FROM 科目 NATURAL JOIN 履修;

もしくは

SELECT * FROM 科目 JOIN 履修 USING (科目番号)

〇科目

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER
001	モンスター学	2
002	勇者学	1
003	魔王学	2

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
001	A0001	90
001	A0002	80
002	A0004	90

↓

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER	学籍番号 CHAR(6)	成績 INTEGER
001	モンスター学	2	A0001	90
001	モンスター学	2	A0002	80
002	勇者学	1	A0004	90

外部結合

SELECT * FROM 科目 LEFT OUTER JOIN 履修 USING (科目番号);

〇科目

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER
001	モンスター学	2
002	勇者学	1
003	魔王学	2

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
001	A0001	90
001	A0002	80
002	A0001	70
002	A0003	90

↓

科目番号 CHAR(3)	科目名 VARCHAR(30)	単位数 INTEGER	学籍番号 CHAR(6)	成績 INTEGER
001	モンスター学	2	A0001	90
001	モンスター学	2	A0002	80
002	勇者学	1	A0001	70
002	勇者学	1	A0003	90
003	魔王学	2	NULL	NULL

集約関数

何個カウントするcount

SELECT count(*) AS 履修者 FROM 履修 WHERE 科目番号='001';

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
001	A0001	90
001	A0002	80
002	A0001	70
002	A0003	90

↓

履修者総数
2

これと同じような使い方で，最大値(max)，最小値(min)，平均(avg)，合計(sum)なんかがあります。

GROUP BY，HAVING

SELECT 学籍番号, count(*) AS 履修科目数 FROM 履修 GROUP BY 学籍番号 HAVING count(*) >= 2;

〇履修

科目番号 CHAR(3)	学籍番号 CHAR(6)	成績 INTEGER
001	A0001	90
001	A0002	80
002	A0001	70
002	A0003	90

↓

学籍番号	履修科目数
A0001	2

さいごに

正直見返すとかなり分かりにくくて失敗したなと思いましたが、いまから書き直すのもあれだと思ったのでそのままあげちゃいます。

今回はここまで。