【勉強用】PostgreSQLの基本操作

環境

Ubuntu ver18.04.3

SQLとは

SQL（エスキューエル）はデータベースを操作する言語のことで、「データベース問い合わせ言語」とも呼ばれます。
基本的には、RDBMS（関係データベース管理システム）を操作するために使われます。
関係データベースとは、関係モデルという表に似た構造でデータを管理するデータベースのことで、この関係データベースを管理するシステムがRDBMSというわけです。
有名なRDBMSには、MySQLやPostgreSQL、Oracle Databaseなどがあります。
参考：Wikipedia 関係モデル

SQLの中での役割について

SQLの中でも役割分けされており、以下の分け方をされています。

・テーブルの操作であるDDL(Data Define Language)

CREATE - 新しいテーブルを作成する。
DROP - 既に存在するテーブルを削除する。

・データの操作であるDML(Data Manipulation Language)

SELECT - データを取得・検索する
INSERT - データを挿入・登録する)
UPDATE - データを更新する
DELETE - データを削除する

・データベースの権限を扱うDCL(Data Control Language)

GRANT - 特定のデータベース利用者に特定の作業を行う権限を与える
REVOKE - 特定のデータベース利用者から既に与えた権限を剥奪する

RDBMSのインストール

まずは、RDBMSのインストールから始める。今回はPostgreSQLを利用する。

console

$ sudo apt install -y postgresql-10

APTは、Debian用に開発されたパッケージ管理システムである。
UbuntuがDebianを元にしたOSであるためにAPTを利用する。
また、APTはroot権限を必要とするので、sudoを利用する。
今回はPostgreSQLのバージョン10をインストールした。

SQL準備

インストールが終わったら、SQLの準備を行う。

操作画面へのログイン

コンソールに以下のコマンドを打ち込むことで、PostgreSQLにログインできる。

console

$ sudo su - postgres

PostgreSQLをインストールした際にpostgresというユーザが自動で作成されるので、最初はpostgresユーザでログインすることになる。

console

$ psql

psqlと入力することで、PostgreSQLの操作画面を起動できる。

準備の最終段階として、postgresユーザーでログインする際のパスワードを設定しておく、パスワードは簡単なもので構わない。

postgres

postgres=# alter role postgres with password 'パスワードはここに入力';

postgres=#という表示は、psqlと入力したことで、PostgreSQLの操作画面にあることを意味する。

データベースの作成

postgres

postgres=# create database db_example;
> CREATE DATABASE

create database データベース名;でデータベースを作成できる。
ここで、;を忘れないように気をつける。
CREATE DATABASEとコマンドに表示されたら、データベースの作成は成功である。

データベースに接続

データベースを操作するためには、データベースに接続する必要がある。

postgres

postgres=# \c db_example
> You are now connected to database "db_example" as user "postgres".

\c データベース名と入力することで、データベースへ接続できる。
You are now connected to database "db_example" as user "postgres".は"db_example"というデータベースに"postgres"というユーザで接続することに成功したことを意味する。

SQL実践

いよいよSQLを行える。

テーブルの作成

データベースに直接データベースを書き込むことはできない。
データベースのテーブル（表）の列（カラム）と行（レコード）にデータを書き込んでいく。
そのために、テーブルを作成する。

	カラム1	カラム2
レコード1	フィールド1	フィールド2
レコード2	フィールド3	フィールド4
レコード3	フィールド5	フィールド5

今回は、以下のような簡単な名簿（namelist）を作成する。

name	age
たろう	15
じろう	12
さぶろう	10

db_example

db_example=# CREATE TABLE namelist (name TEXT PRIMARY KEY, age INTEGER);
> CREATE TABLE

CREATE TABLE namelistでnamelistという名前のテーブルを作成。
(name TEXT PRIMARY KEY, age INTEGER)は、列名、データ型、主キーを設定している。
以上の設定は(列名 データ型 主キー, 列名　データ型)となっている。

列名はその名の通り、どんな列にするか名前を自由に決めることができる。
データ型はPostgreSQL指定のデータ型を列名に合わせて記述する。
今回は、nameは名前なのでTEXTという文字のデータ型、ageは年齢なのでINTEGERという数字のデータ型を指定した。
主キーは、その名の通り、そのテーブルでもっとも主要なデータを指定する。
主キーは重複が許されず、データを空にすることもできない。

テーブルへデータを追加

テーブルの作成ができたら、いよいよデータを追加していく。

db_example

db_example=# INSERT INTO namelist (name, age) VALUES ('たろう', '15');
> INSERT 0 1

INSERT INTO namelistはnamelistというテーブルの1行に新しくデータを追加していくことを意味する。
(name, age) VALUES ('たろう', '15')は、nameという列にたろうを追加し、ageという列に15を追加することを意味する。

(列1, 列2) VALUES ('列1に入れたい値', '列2に入れたい値')

これでデータの追加が完了しました。

テーブルに追加したデータの取得

続いて、追加データを取得してみる。

db_example

db_example=# SELECT name FROM namelist;
> 　name  
  --------
   たろう
  (1 row)

SELECT name FROM namelistでnamelistというテーブルのnameという行を取得することを意味している。

SELECT 取得したい行名 FROM 行のテーブル名

SELECT 取得したい行1, 取得したい行2 FROM 行のテーブル名とすると、複数の行を取得できる。

テーブルからデータを削除

続いて、テーブルからデータを削除するSQLを紹介する。
テーブルには1行しか存在しないので、まず適当にデータを追加してから削除を行う。

db_example

db_example=# INSERT INTO namelist (name, age) VALUES ('じろう', '12');
db_example=# INSERT INTO namelist (name, age) VALUES ('さぶろう', '10');
db_example=# INSERT INTO namelist (name, age) VALUES ('よしこ', '5');

db_example

db_example=# SELECT * FROM namelist;
>    name   | age 
  ----------+-----
    たろう   |  15
    じろう   |  12
　　 さぶろう |  10
    よしこ   |   5
  (4 rows)

SELECT *で全ての行・列のデータを取得することができる。

続いて、データの削除をしてみる。

db_example

db_example=# DELETE FROM namelist WHERE name='よしこ';
> DELETE 1

DELETE FROM namelist WHERE name='よしこ';は、namelistというテーブルから、列nameの値がよしこであるデータを削除することを意味する。

DELETE FROM テーブル名 WHERE 列名='消したい値';

db_example

db_example=# SELECT * FROM namelist;
>    name   | age 
  ----------+-----
    たろう   |  15
    じろう   |  12
　　 さぶろう |  10
  (3 rows)

はなこが入った行データが消えた。

WHERE句の使い方

WHERE句を使うことで便利な検索ができる。
例えば、データの中から'ろう'とついたものを探し出したい時は以下のようにする。

db_example

db_example=# SELECT * FROM namelist WHERE name LIKE '%ろう%';
>    name   | age 
  ----------+-----
    たろう   |  15
    じろう   |  12
　　 さぶろう |  10
  (3 rows)

WHERE name LIKE '%ろう%'はnameの中に'ろう'という文字が入っているものを選択している。

テーブル自体の削除

テーブル自体の削除は以下のように行う。

db_example

db_example=# DROP TABLE テーブル名;

主キー

テーブルを作成した時にnameに主キーというものを設定した。
ここで主キーがどういうものか確認してみる。

INSERT INTO namelist (name, age) VALUES ('たろう', '17');
> ERROR:  duplicate key value violates unique constraint "namelist_pkey"
DETAIL:  Key (name)=(たろう) already exists.

列nameにたろうというデータを再び入れようとするとエラーが出る。
これは、主キーは値の重複が許されないからだ。
しかし、この主キーのお陰でデータの検索も早くなるなどのメリットがある。

データベースのテーブル一覧の表示

以下のコマンドで、データベース内にあるテーブルの一覧を表示することができる。

db_example

db_example=# \dt
>         List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | namelist | table | postgres
(1 row)

操作画面からログアウト

db_example

db_example=# \q

以上のコマンドで操作画面からログアウトすることができる。

ただ、まだpostgresユーザなので以下のコマンドを入力し、
元のユーザに戻る。

console

$ exit