PostgreSQLとは
- RDBMS(リレーショナルデータベース管理システム)
※RDBとは、テーブル(エクセルみたいな)という形でデータを返します。
複数のテーブルを関連づけることができる
ローカルからPostgreSQLに接続する方法
$ psql -h localhost -p 5432 -U postgres -d postgres
ローカルに戻るコマンド
postgres=# \q
途中で終了する場合はセミコロンで終了できる。
postgres=# k
postgres-# ;
ERROR: syntax error at or near "k"
LINE 1: k
^
postgres=#
テーブル作成
$ create table posts (title varchar(255), body text);
postgres=# create table posts (title varchar(255), body text);
CREATE TABLE
$ ¥dt
※テーブル一覧
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | posts | table | postgres
(1 row)
$ ¥d posts
※テーブルのカラムを表示する
postgres=# \d posts
Table "public.posts"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
title | character varying(255) | | |
body | text | | |
$ alter table posts rename to myposts;
※テーブル名前変更
postgres=# alter table posts rename to myposts;
ALTER TABLE
$ drop table myposts;
※テーブル削除
postgres=# drop table myposts;
DROP TABLE
外部ファイルを使用する方法
postgres=# \i commands.sql
※ターミナル開いてcommands.sqlファイルを作成して、ファイルの中身に
create table posts (title varchar(255), body text);
を記載して、上記コマンドを実行するとテーブルが作成される
データの型
- 数値: integer(int),real(実数),serial(連番)
- 文字: char(5)-固定文字の場合, varchar(255)-上限付きの可変長,
※読み方、char = キャラ - 真偽: boolean TRUE FALSE t f
- 日付: date(日付), time(時間),timestamp(両方)
フィールドに制約をつけてみよう
- 制約
- not null(入力必須のものにつける)
- unique(重複した値を許さない、同じEメールアドレスを許さない)
- check(内容をチェックする)
- default
- primary key,(not null, unique)(一意に決めるための主キー)
※テーブルにつき1つだけしか設定できない
create table posts(
id serial primary key,(一意に決まるもの)
title varchar(255) not null,(タイトルが体と困る)
body text check(length(body) > 5),(五文字以上)
is_draft boolean default TRUE,
created timestamp default 'now',(現在時刻に設定)
);
insert文を使ってみよう
insert into posts (title, body) values ('title', 'body1111');
postgres=# insert into posts (title, body) values ('title', 'body1111');
INSERT 0 1
select * from posts;
※全てのレコードを見ることができる
postgres=# select * from posts;
title | body
-------+----------
title | body1111
(1 row)
select文を使ってみよう
値を追加
postgres=# create table users (
id serial primary key,
name varchar(255),
score real,
team varchar(255)
);
insert into users (name, score, team) values
('aa', 5.5, 'red'),
('ss', 8.3, 'blue'),
('dd', 2.2, 'blue'),
('ff', 5.0, 'green'),
('gg', 4.6, 'red'),
('hh', 4.7, 'green');
CREATE TABLE
postgres-# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | posts | table | postgres
public | users | table | postgres
(2 rows)
$ \d users
フィールドが設定されていることを確認
※$ \x、拡張表示はonです。表示がいつもと違う形に なる、\x2回入力すると戻る
postgres-# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ select * from users;
※全てのフィールドを表示する
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
$ select name, score from users;
※全てのフィールドを表示する
postgres=# select name, score from users;
name | score
------+-------
aa | 5.5
ss | 8.3
dd | 2.2
ff | 5
gg | 4.6
hh | 4.7
(6 rows)
where句を使ってみよう
$ select * from users where score > 4.0;
※ scoreが4以上のものを抽出する
postgres=# select * from users where score > 4.0;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(5 rows)
$ select * from users where score = 5.0;
※scoreが5のものを抽出する
$ select * from users where score ！= 5.0;
※scoreが5以外のものを抽出する
など、
$ select * from users where name = 'ff';
※ 文字列に関して抽出する
postgres=# select * from users where name = 'ff'
postgres-# ;
id | name | score | team
----+------+-------+-------
4 | ff | 5 | green
(1 row)
$ select * from users where name like '%a';
※ %i = 最後にaがつくものを抽出する
postgres=# select * from users where name like '%a'
postgres-# ;
id | name | score | team
----+------+-------+------
1 | aa | 5.5 | red
(1 row)
$ select * from users where name like 'h_h';
※ h_h = 最初と最後の文字を指定して抽出が可能
order by、limit、offsetを使おう
$ select * from users order by score;
※ フィールドごとの並び替え(小さい順)
postgres=# select * from users order by score;
id | name | score | team
----+------+-------+-------
3 | dd | 2.2 | blue
5 | gg | 4.6 | red
6 | hh | 4.7 | green
4 | ff | 5 | green
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
(6 rows)
$ select * from users order by score desc;
※ scoreごとの並び替え(大きい順)
postgres=# select * from users order by score desc;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
1 | aa | 5.5 | red
4 | ff | 5 | green
6 | hh | 4.7 | green
5 | gg | 4.6 | red
3 | dd | 2.2 | blue
(6 rows)
$ select * from users order by team;
※ チームごと並び替え(アルファベット順)
postgres=# select * from users order by team
postgres-# ;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
6 | hh | 4.7 | green
1 | aa | 5.5 | red
5 | gg | 4.6 | red
(6 rows)
$ select * from users order by team, score desc;
※ チーム内でscoreが 大きい順に並べたい
postgres=# select * from users order by team, score desc;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
6 | hh | 4.7 | green
1 | aa | 5.5 | red
5 | gg | 4.6 | red
(6 rows)
件数を制限したい場合,limit
$ select * from users limit 3;
※ 最初の三件だけを表示してくれる
postgres=# select * from users limit 3;
id | name | score | team
----+------+-------+------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
(3 rows)
$ select * from users limit 3 offset 3;
※ 項目の4から表示させる場合、
postgres=# select * from users limit 3 offset 3;
id | name | score | team
----+------+-------+-------
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(3 rows)
$ select * from users order by score desc limit 3;
※ ハイスコアランキングで3人だけ表示させたい
postgres=# select * from users order by score desc limit 3;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
1 | aa | 5.5 | red
4 | ff | 5 | green
(3 rows)
レコードを集計してみよう
$ select * from users;
※全てのフィールドを表示する
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
$ select count(*) from users;
※ レコード件数が何件あるか知りたい場合は
postgres=# select count(*) from users;
count
-------
6
(1 row)
$ select distinct team from users;
※ team内で何色があるか知りたい場合は
postgres=# select distinct team from users;
team
-------
blue
red
green
(3 rows)
$ select sum(score) from users;
※ 全体の合計を引っ張ってくる
postgres=# select sum(score) from users;
sum
------
30.3
(1 row)
$ select max(score) from users;
※ 最大値を引っ張ってくる
postgres=# select max(score) from users;
max
-----
8.3
(1 row)
$ select avg(score) from users;
※ 平均値を引っ張ってくる
postgres=# select avg(score) from users;
avg
-------------------
5.049999992052714
(1 row)
$ select team, sum(score) from users group by team;
※ スコアの合計値を出したいし、チームごとに出したい場合は、
postgres=# select team, sum(score) from users group by team;
team | sum
-------+------
blue | 10.5
red | 10.1
green | 9.7
(3 rows)
$ select team, sum(score) from users group by team having sum(score) > 10.0;
※ 集計した後のもに対してさらに条件をつけることができる、(having)
チームスコアの合計値が10より多いものだけ知りたい場合、
postgres=# select team, sum(score) from users group by team having sum(score) > 10.0;
team | sum
------+------
blue | 10.5
red | 10.1
(2 rows)
便利な関数を使ってみよう
$ select * from users;
※全てのフィールドを表示する
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
$ select name, length(name) from users;
※ 名前の文字数を調べたい場合、
postgres=# select name, length(name) from users;
name | length
------+--------
aa | 2
ss | 2
dd | 2
ff | 2
gg | 2
hh | 2
(6 rows)
$ select concat(name, ' (', team, ')') from users;
※ 文字列の連結、
名前の後に、チームの名前を入れたいという場合、
postgres=# select concat(name, ' (', team, ')') from users;
aa (red)
ss (blue)
dd (blue)
ff (green)
gg (red)
hh (green)
$ select concat(name, ' (', team, ')') as namelabel from users;
※ ラベルを変える方法、
postgres=# select concat(name, ' (', team, ')') as namelabel from users;
aa (red)
ss (blue)
dd (blue)
ff (green)
gg (red)
hh (green)
$ select substring(team, 1, 1) from users;
※ チームの1文字目しか必要なかった場合、
postgres=# select substring(team, 1, 1) from users;
r
b
b
g
r
g
$ select *from users order by random() limit 1;
※ ランダムに1人抽出してくれる
postgres=# select *from users order by random() limit 1;
4 | ff | 5 | green
postgres=# select *from users order by random() limit 1;
5 | gg | 4.6 | red
postgres=# select *from users order by random() limit 1;
4 | ff | 5 | green
updete、deleteを使ってみよう
$ select * from users;
※全てのフィールドを表示する
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
$ update users set score = 5.8 where name = 'aa';
※ scoreを変えたい場合、
テーブル名を指定した後に、[set]として、変更したいフィールドを書いて、その後にに値を = で結びつける
postgres=# update users set score = 5.8 where name = 'aa';
UPDATE 1
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
1 | aa | 5.8 | red
(6 rows)
$ update users set score = score + 1 where team = 'red';
※ スコアを１増やして、ただしチームがredのもに限る場合、
postgres=# update users set score = score + 1 where team = 'red';
UPDATE 2
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
6 | hh | 4.7 | green
5 | gg | 5.6 | red
1 | aa | 6.8 | red
(6 rows)
$ update users set score = score + 1 where team = 'red' or team = 'green';
※ where句はandなどで条件をつなげることもできるので、
チームがredもしくはgreenの場合はスコアが1ずつあげる
postgres=# update users set score = score + 1 where team = 'red' or team = 'green';
UPDATE 4
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 6 | green
6 | hh | 5.7 | green
5 | gg | 6.6 | red
1 | aa | 7.8 | red
(6 rows)
$ delete from users where score < 3.0;
※ スコアが3より低い人を削除したい場合、
postgres=# delete from users where score < 3.0;
DELETE 1
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
4 | ff | 6 | green
6 | hh | 5.7 | green
5 | gg | 6.6 | red
1 | aa | 7.8 | red
(5 rows)
テーブルの構造を変更してみよう
- まずはこういった型で用意されていることを確認
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ alter table users add fullname varchar(255);
※ フィールドを追加する場合、
postgres=# alter table users add fullname varchar(255);
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
fullname | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ alter table users drop fullname;
※ 逆にフィールドを削除する場合、
postgres=# alter table users drop fullname;
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ alter table users rename name to myname;
※ 名前をnameからmynameに変更した場合、
postgres=# alter table users rename name to myname;
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ alter table users alter myname type varchar(32);
※ 型す、文字数を変更したい場合、
postgres=# alter table users alter myname type varchar(32);
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(32) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ create index team_index on users(team);
-
teamにindexをつける方法、
- インデックスという、テーブルについている索引なのですがこれの追加、削除方法、
- primary keyには最初からついているのですが、これがついていると検索が早くなります。
- 運用していったらteamで検索されることが多かったので、teamにインデックスをつけてみよう
postgres=# create index team_index on users(team);
CREATE INDEX
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(32) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"team_index" btree (team)
$ alter table users alter myname type varchar(32);
※ indexを削除する場合、
postgres=# drop index team_index;
DROP INDEX
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(32) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
複数のテーブルを扱ってみよう
※ 複数テーブル作成、
postgres=# create table users (
id serial primary key,
name varchar(255),
score real,
team varchar(255)
);
insert into users (name, score, team) values
('aa', 5.5, 'red'),
('ss', 8.3, 'blue'),
('dd', 2.2, 'blue'),
('ff', 5.0, 'green'),
('gg', 4.6, 'red'),
('hh', 4.7, 'green');
create table posts (
id serial primary key,
user_id int not null,
title varchar(255) not null,
body text not null
);
insert into posts (user_id, title, body) values
('1', 'title1', 'body1'),
('2', 'title2', 'body2'),
('3', 'title3', 'body3'),
('4', 'title4', 'body4'),
('5', 'title5', 'body5'),
('6', 'title6', 'body6');
$ \d
※ 作成されたことを確認、
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+----------
public | posts | table | postgres
public | posts_id_seq | sequence | postgres
public | users | table | postgres
public | users_id_seq | sequence | postgres
(4 rows)
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
postgres=# select * from posts;
id | user_id | title | body
----+---------+--------+-------
1 | 1 | title1 | body1
2 | 2 | title2 | body2
3 | 3 | title3 | body3
4 | 4 | title4 | body4
5 | 5 | title5 | body5
6 | 6 | title6 | body6
(6 rows)
$ select users.name, posts.title from users, posts where users.id = posts.user_id;
-
誰がどの記事を書いたか引っ張ってくる、
- 複数のテーブルから引っ張ってくるには、テーブル名とフィールド名を .(ドット)で繋いであげればOKです。
- 結び付きを指定してあげないといけないので,whereをつけてuser.idとposts.user_idが一緒
postgres=# select users.name, posts.title from users, posts where users.id = posts.user_id;
name | title
------+--------
aa | title1
ss | title2
dd | title3
ff | title4
gg | title5
hh | title6
(6 rows)
$ select u.name, p.title from users u, posts p where u.id = p.user_id;
※ user = u, posts = p,省力する書き方、
postgres=# select u.name, p.title from users u, posts p where u.id = p.user_id;
name | title
------+--------
aa | title1
ss | title2
dd | title3
ff | title4
gg | title5
hh | title6
(6 rows)
$ select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1
-
ユーザーごとの記事一覧ページ作成、
- andでuserのidが1,つまり{aa}君のものしか知りたくないという場合は、このような条件句を与えること
postgres=# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;
name | title
------+--------
aa | title1
(1 row)
viewを使ってみよう
$ create view aa_posts as (さっきのの長い文)
- 長文をviewとして、簡単に出せるようにする、
postgres=# create view aa_posts as
postgres-# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;
CREATE VIEW
postgres=# \dv
List of relations
Schema | Name | Type | Owner
--------+----------+------+----------
public | aa_posts | view | postgres
(1 row)
$ select * from aa_posts;
- viewで作成したものを出す方法、
postgres=# select * from aa_posts;
name | title
------+--------
aa | title1
(1 row)
$ drop view aa_post;
- viewを削除する場合、
postgres=# drop view aa_posts;
DROP VIEW
postgres=# \dv
Did not find any relations.
トランザクジョンを使ってみよう
- 複数の処理を必ずまとめて行う処理のこと
$ begin;
-
途中に変な処理がいってこない、不整合が起きないための仕組み、
- トランザクショの開始には[begin]として、その後に処理をいくつか書いていく
postgres=# begin;
BEGIN
postgres=*# update users set score = score -1.0 where name = 'aa';
UPDATE 1
postgres=*# update users set score = score +1.0 where name = 'ss';
UPDATE 1
postgres=*# commit;
COMMIT
$ rollback;
- 今までの変更をまとめて取り消される、
postgres=# begin;
BEGIN
postgres=*# update users set score = score -1.0 where name = 'aa';
UPDATE 1
postgres=*# update users set score = score +1.0 where name = 'ss';
UPDATE 1
postgres=*# rollback;
ROLLBACK
1からnpmで立ち上げ
・npm init -y
パッケージ.jsを初期化して使用していく
・npm i express nodemon pg
・npm start
server.jsファイル作成
const express = require("express");
const app = express();
const PORT = 5000;
app.listen(PORT, () => {
console.log("server is runing on PORT" + PORT);
});
API作成
const express = require("express");
const app = express();
const PORT = 5000;
app.get("/", (req, res) => {
res.send("Hello Express");
});
app.listen(PORT, () => {
console.log("server is runing on PORT" + PORT);
});
※reg=リクエスト
※res=レスポンス
http://localhost:5000/
に接続できか確認
PostgreSQLインストール
データベース作成
postgres=# CREATE DATABASE users;
※作成したら /lでデータベース一覧表示、2個目の記事参照
## テーブルクリア
postgres=# ¥l cls
## テーブル作成
postgres=# ¥c users
### usersテーブル作成
users=# CREATE TABLE users (
## フィールド作成
users(# ID serial primary key,
users(# name varchar(255),
users(# email varchar(255),
users(# age int);
※serial=連番
※primary key=主キー(ユーザーを代表する番号)
※varchar(255)=255文字以内(文字列)
※int=整数値
# テーブル確認
users(# ¥dt
Mysqlを使用してデータ挿入(SQL文)
### usersテーブル中身作成
users=# insert into users (name, email, age)
※insert=挿入
users-# values ('shincode', 'shinncode@gmail.com', 25), ('testUser', 'test@gmail.com', 34);
## 作成した情報を出力する方法
users=# select * from users;
※ *=全て
VScodeから作成したデータを取り出す、サーバと連携してどのように出力するのか
db.js作成
const Pool = require("pg").Pool;
const pool = new Pool({
user: "postgres",
host: "localhost",
database: "users",
password: "password",
port: 5432,
});
module.exports = pool;
server.js追加
const express = require("express");
const app = express();
const PORT = 5000;
//jsonを使用する場合は、ミドルウェアの設定を行う必要がある
app.use(express.json());
app.get("/", (req, res) => {
res.send("Hello Express");
});
//ユーザー情報を全て取得する
app.get("/users", (req, res) => {
pool.query("SELECT * FROM users", (error, results) => {
if (error) throw error;
return res.status(200).json(results.rows);
});
});
app.listen(PORT, () => {
console.log("server is runing on PORT" + PORT);
});