はじめに
学校でPostgreSQL
を使わなければならなくなったので、使い方を一覧にしてまとめようと思います。ただどんなことを載せるかは独断と偏見で選んでいるのでご注意を。
またwindows10への導入の仕方が分からない場合は以下の記事を是非参考にしてみてください。
接続
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 |
さいごに
正直見返すとかなり分かりにくくて失敗したなと思いましたが、いまから書き直すのもあれだと思ったのでそのままあげちゃいます。
今回はここまで。