基本的なPostgreSQLの操作方法

## はじめに

PostgreSQLの勉強をしている際、インストールした後、どうやって動かせばいいか分からず時間がかかってしまったので、備忘録として手順をまとめました。
参考記事にとてもお世話になりました。

## 環境

macOS: Big Sur version 11.4
PostgreSQL: 14.4

## PostgreSQLの起動・確認・終了

### 起動

```:zsh
brew services start postgresql
```

### 確認

```:zsh
brew services list
```

### 終了

```:zsh
brew services stop postgresql
```

## 環境変数の設定

```bash
# PostgreSQLで使用するデータを格納
export PGDATA=/usr/local/var/postgres
```

zshを使っているのであれば、~/.zshrcにbashであれば~/.bash_profileに上記の内容を記入する。

## データベース操作

### データベースに接続

```:zsh
# スーパーユーザーとしてログイン
psql postgres

# ログインコマンドのオプション
# ホスト名はデフォルトで-h localhost, ポート番号はデフォルトで -p 5432で変更がない場合は省略可能
psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名
```

### psqlを終了

```:sql
\q
```

コマンドラインへ戻るが、PostgreSQLサーバーを停止したわけではないので注意。

### データベース一覧の確認

```:sql
\l
```

### データベース作成

```:sql
create database データベース名;
```

dbnameというデータベースが作成されます。

### データベース名の変更

```:sql
alter database データベース名1 rename to データベース名2;
```

カレントデータベースは変更できないので注意。

### 使用するデータベースの選択

```:sql
\c データベース名;
```

### データベース削除

```:sql
drop database データベース名;
```

## ロール作成

### 作成

```:sql
CREATE ROLE ロール名;
# または
CREATE ROLE ロール名 LOGIN; #LOGIN属性をつける
```

似たものに、CREATE USERがあるのですが、違いはCREATE ROLEではLOGIN属性がつかず、CREATE USERではLOGIN属性が付くようです。CREATE ROLEを使うのが標準みたいです。
CREATE ROLE ロール名 LOGIN;とCREATE USER ロール名;は同じ結果となる。

参考記事：[【PostgreSQL】CREATE USERとCREATE ROLEの違い](https://postgresweb.com/difference-between-create-user-and-create-role)

### 属性変更

ロールをLOGIN属性なしで作成して、後からLOGIN属性を付けたい場合は、以下のようにします。

```:sql
ALTER ROLE ロール名 LOGIN;
```

その他の属性については以下PostgreSQL公式ドキュメントが参考になるかと思います。

[22.2. Role Attributes](https://www.postgresql.org/docs/14/role-attributes.html)

## テーブル操作

### テーブル一覧の確認

```:sql
\dt
```

### テーブルの作成

```:sql
create table テーブル名(カラム名1 データ型1, カラム名2 データ型2, ...);

# 例
create table users(id integer, name string);
```

### テーブル情報の確認

```:sql
\d テーブル名
```

### テーブル名の変更

```:sql
alter table テーブル名1 rename to テーブル名2;
```

### テーブルの削除

```:sql
drop table テーブル名;
```

## ファイルの実行

```:sql
\i ファイル名.sql
```

## データ型

```:sql
# 数値
int # 整数型
real # 実数型
serial # 連番 MYSQLでいうAUTO_INCREMENT属性

# 文字
char(数値) #文字数が決まっている場合
varchar(数値) #文字数が決まっていない場合。数値は上限を表す

# 真偽値
boolean

# 日付
date # 日付
time # 時間
timestamp # 日付と時間の両方
```

詳しいデータ型については以下の公式マニュアルに記載されています。

[Chapter 8. Data Types](https://www.postgresql.org/docs/14/datatype.html)

## データ制約

```sql
not null # 空の値が入った場合エラーにする
unique # 重複をエラーとする
check # 
default # デフォルト値を指定
primary key # 主キー、値を一意に決める。テーブルにつき1つのみ設定可能 (not null + unique)
```

詳しいデータ制約については以下の公式マニュアルに記載されています。
[5.4. Constraints](https://www.postgresql.org/docs/14/ddl-constraints.html)

### データ作成例

.sqlファイルを作ってそこに書き込みます。実行したい場合は、データベース内で\i ファイル名;で実行します。

```sql
drop table if exists users;

create table users (
	id serial, primary key,
	title varchar(255) not null,
	body text check(length(body) > 5),
	is_draft boolean default TRUE, 
	created timestamp default 'now'
);
```

## データ操作 (CRUD)

### レコードの挿入 (INSERT)

```sql
insert into テーブル名 (カラム名1, カラム名2, ...) values ('値1', '値2', ...);
```

値は文字列の場合はシングルクォーテーションで囲む。

### レコードの読み (Read)


```
select * from テーブル名;
```

全てのレコードを読み込む


```
select カラム名1, カラム名2 from テーブル名;
```

レコードのカラム名1, カラム名2を読み込む

#### 条件をつける

■where

```
select * from テーブル名 where カラム名1 > 5.5;
```
全レコードの内カラム名1が5.5より大きいレコードを読み込む。演算子として=, !=, <>, >, >=, <, <=が使える


■並び替え order by　数値は昇降順、文字列はアルファベット順に並び替え
昇順

```
select * from テーブル名 order by カラム名;
```

降順

```
select * from テーブル名 order by カラム名 desc;
```

■読み込み数の制限

```
select * from テーブル名 limit 数値;
```

数値分だけ読み込む。並び替えの後にも使える


■集計

```
select count(*) from テーブル名;
```

レコード数をカウントする

```
select distinct カラム名 from テーブル名;
```

カラムの中のユニークな値だけを読み込む

### レコードの更新 (UPDATE)

```sql
update テーブル名 set カラム名1 = 値1 where カラム名2 = 値2;
```

カラム名2が値2の全レコードに対してカラム名1を値1に更新する

■whereの条件はor, andでつなげることができる

```
update テーブル名 set カラム名1 = 値1 where カラム名2 = 値2 OR カラム名3 = カラム名3;
update テーブル名 set カラム名1 = 値1 where カラム名2 = 値2 and カラム名3 = カラム名3;
```

### レコードの削除 (Delete)

■全レコードの削除

```sql
delete from テーブル名;
```

■特定のレコードの削除

```
delete from テーブル where カラム名 = 値;
# 演算子は=, !=, <>, <, <=, >, >=が使える
```

## テーブル構造の変更

■カラムの追加

```
alter table テーブル名 add カラム名 型 (データ制約);
```

■カラムの削除

```
alter table テーブル名 drop カラム名;
```

■カラムの変更
```
alter table テーブル名 rename カラム名1 to カラム名2;
```

■型の変更
```
alter table テーブル名 alter カラム名 type (変更先の)型;
```

■インデックスの追加
```
create index インデックス名（任意で付ける） on テーブル名(カラム名);
```

"インデックス名"は以下のようになる。名命例:"カラム名_index"

```
\d 

Table "public.テーブル名"
 Column        |          Type          | Collation | Nullable |              Default              
---------------+------------------------+-----------+----------+---------------------------------
 id            | integer                |           | not null | nextval('users_id_seq'::regclass)
 カラム名_index | 型                      |           |          |
Indexes:
    "テーブル名_pkey" PRIMARY KEY, btree (id)
    "インデックス名" btree (カラム名)

# インデックスの削除
drop index インデックス名(任意でつけた名前);
```

インデックスを付けると、検索は早くなるがレコード挿入の速度は遅くなるので注意。

## 参考記事

- [PostgreSQL入門](https://dotinstall.com/lessons/basic_postgresql)
- [【PostgreSQLをMacで使う方法】インストールから基本的な使い方まで紹介します](https://amateur-engineer.com/postgresql-mac-install)
- [PostgreSQLへの接続と切断](https://www.javadrive.jp/postgresql/connect/index2.html)
- [[Mac]PostgreSQLをhomebrewでインストールする方法](https://awesomecatsis.com/postgresql-mac-install/)
- [【PostgreSQL】ユーザーを作成する（CREATE USER文、CREATEUSER）](https://postgresweb.com/createuser)
- [【PostgreSQL】CREATE USERとCREATE ROLEの違い](https://postgresweb.com/difference-between-create-user-and-create-role)
- [MySQLとPostgreSQLコマンド比較表](https://qiita.com/aosho235/items/c657e2fcd15fa0647471)
- [PostgreSQL 14.4 Documentation](https://www.postgresql.org/docs/14/index.html)
- [PostgreSQLの環境をmacのローカルに作成＆SQL操作（＆ついでにDataGripへの接続）](https://qiita.com/ysdyt/items/64ed98b420ea5c4e52ec)