はなちるのマイノート

Unityをメインとした技術ブログ。自分らしくまったりやっていきたいと思いますー!

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

さいごに

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

今回はここまで。