tyoshikawa1106のブログ

- Force.com Developer Blog -

DataBase:Postgresの環境構築を試してみました

データベース環境の構築でPostgresについて勉強しました。環境はMacを使っています。

PostgreasSQLのダウンロード

ダウンロードページはこちらです。

PostgreSQL: Downloads


今回はMacを利用するのでMac OS Xのリンクをクリックします。
f:id:tyoshikawa1106:20160907130835p:plain


DownloadInstallerのリンクをクリックします。
f:id:tyoshikawa1106:20160907130957p:plain


安定版で最新バージョンのインストーラをダウンロードします。(RC1とついているのは多分次公開予定のプレビュー版バージョン)
f:id:tyoshikawa1106:20160907131244p:plain


dmgファイルをダウンロードできます。
f:id:tyoshikawa1106:20160907131401p:plain


appファイルを実行後、Setupガイドに従って進めていけば設定できます。
f:id:tyoshikawa1106:20160907131455p:plain:w300


途中でパスワードを設定する必要があります。
f:id:tyoshikawa1106:20160907131656p:plain:w300


ポート番号はデフォルトの5432をそのまま使用します。


地域はja_JP.UTF-8を指定しました。
f:id:tyoshikawa1106:20160907131859p:plain:w300


Stack Builderのチェックを外してFinishボタンをクリックします。チェックをつけていると便利ツールのインストール作業が始まりますが、これは後からでも実行できるとのことです。
f:id:tyoshikawa1106:20160907132137p:plain:w300

インストール後は・・

アプリケーションフォルダにPostgresSQLが追加されています。
f:id:tyoshikawa1106:20160907132340p:plain


pg adminを起動するとこんな感じでした。ここでDBの作成などいろいろやれると思います。
f:id:tyoshikawa1106:20160907132602p:plain

PSQLの動かし方

PSQLはコマンドラインからSQLコマンドを実行できるツールです。起動後は以下の質問を聞かれます。

  • Server [localhost]:
  • Database [postgres]:
  • Port [5432]:
  • Username [postgres]:
  • Password for user postgres:

基本的にはデフォルトのままで大丈夫です。(未入力でEnter)
ただし、 Password for user postgresにはインストール時に指定したパスワードを入力します。


postgres=# と表示されたらそこでコマンドを入力していけます。


例えば次のコマンドが実行できるはずです。

postgres=# SELECT version();

f:id:tyoshikawa1106:20160907133716p:plain

PSQLの切断方法

次のコマンドを実行すると切断されます。

postgres=# \q

f:id:tyoshikawa1106:20160907133857p:plain

データベースの作成

次のコマンドでデータベースを作成できます。

CREATE DATABASE <データベース名>;


sampleという名前の場合はこんな感じ

CREATE DATABASE sample;

f:id:tyoshikawa1106:20160907142516p:plain


作成したデータベースは次のコマンド(DB一覧表示コマンド)で確認できます。

\l

f:id:tyoshikawa1106:20160907142707p:plain

データベースの削除

DROP DATABASE <データベース名>;

f:id:tyoshikawa1106:20160907143017p:plain

テーブルの作成

データベースの作成ができたので、次はテーブルを作成してみます。

CREATE TABLE <テーブル名> (
  <列名 データ型>,
  <列名 データ型>
);


例えばこんな感じ

CREATE TABLE sample (
  column1 varchar(10),
  column2 integer
);


制約をつけたい場合はこんな感じ

CREATE TABLE sample (
  column1 varchar(10) PRIMARY KEY,
  column2 integer
);

作成後は次のコマンドで一覧表示して確認できます。

\d

f:id:tyoshikawa1106:20160907145148p:plain


テーブルの定義を確認したい場合は次のコマンドです。

\d <テーブル名>;

f:id:tyoshikawa1106:20160907145303p:plain

テーブルの変更

一度作成したテーブル定義の変更方法です。

ALTER Table <テーブル名>
  [Add | DROP | ALTER | RENAME] <変更文>;
列の追加
ALTER TABLE sample
  ADD COLUMN column varchar(10);
列の削除
ALTER TABLE sample
  DROP COLUMN column;
列のデータを変更
ALTER TABLE sample
  ALTER COLUMN column TYPE varchar(10);

テーブルの削除

DROP TABLE <テーブル名>

インデックスの作成

CREATE INDEX <インデックス名> ON テーブル名 (カラム名)


インデックス一覧を表示する方法

\di

f:id:tyoshikawa1106:20160907171518p:plain


インデックスの定義内容を確認する方法

\d <インデックス名>

f:id:tyoshikawa1106:20160907171622p:plain

インデックスの削除

DROP INDEX <インデックス名>

ロールの作成

ロールは権限をまとめて管理するための仕組みがあります。PostgreSQLでは、ロール単位でユーザのデータベースに対する権限を管理します。

CREATE ROLE <ロール名> WITH <オプション>

例えばこんな感じ

CREATE ROLE sample_user WITH LOGIN PASSWORD 'pass';


ロール一覧の表示はSELECTクエリで行います。

SELECT usename FROM pg_user

f:id:tyoshikawa1106:20160907172117p:plain

権限の付与

GRANT <権限> ON <権限を付与する対象> TO <権限を付与するロール>


例えばこんな感じ。

GRANT SELECT, INSERT ON sample TO sample_user;

f:id:tyoshikawa1106:20160907172910p:plain

権限の剥奪

REVOKE <権限> ON <権限を剥奪する対象> FROM <権限を剥奪するロール>


例えばこんな感じ。

REVOKE SELECT ON sample FROM sample_user;

SELECTクエリの例

基本構文
SELECT <列名> FROM <テーブル名>;
全列取得
SELECT * FROM sample;
並べ替え
SELECT <列名> FROM <テーブル名> ORDER BY <カラム名> [ASC | DESC];
SELECT * FROM sample ORDER BY column2 DESC;
集合(GROUP BY)と集合関数
SELECT <集計関数> FROM <テーブル名> GROUP BY <カラム名>;
  • avg
  • max
  • min
  • sum
  • count
SELECT score, count(*) FROM score GROUP BY score;
別名を付ける (AS キーワード)
SELECT score, count(*) AS number FROM score GROUP BY score;
||< 


*** 検索条件を指定
基本構文
>||
SELECT <カラム名> FROM <テーブル名> WHERE <カラム名> <比較演算子> <条件>;

値指定

SELECT * FROM item WHERE category = 'お菓子';

NULL指定

SELECT * FROM item WHERE category IS NULL;

ANDやOR演算子

SELECT * FROM item WHERE category='お菓子' AND price=300;
SELECT * FROM item WHERE category='お菓子' OR price=300;

IN演算子

SELECT * FROM item WHERE name IN('みかん', 'クッキー', 'キャンディー');

範囲での抽出

SELECT * FROM item WHERE price BETWEEN 100 AND 200;

集計した結果を更に絞り込む(HAVING句)

SELECT category, count(*) FROM item GROUP BY category HAVING count(*) >= 3;

挿入(INSERT)と更新(UPDATE)

INSERTの基本構文
INSERT INTO <テーブル名> (<カラム名>,<カラム名>, ...) VALUES (値1, 値2, ...);
INSERTの実行例
INSERT INTO item VALUES('医薬品', '風邪薬', 1000);
UPDATEの実行例
UPDATE item SET price = 1500 WHERE name = '風邪薬';

削除 (DELETE / TRUNCATE)

DELETE 文の基本構文
DELETE FROM <テーブル名>  WHERE 条件;
DELETEの実行例
DELETE FROM item WHERE name = '風邪薬';
データの全削除
TRUNCATE <テーブル名1>, <テーブル名2>, ...

結合

INNER JOINを使った内部結合
SELECT <テーブル名.カラム名>, <テーブル名.カラム名>, ...
FROM <テーブル名1> INNER JOIN <テーブル名2>
ON 結合条件
SELECT category.cotegory_name, price.item_name, price.price
FROM category INNER JOIN price
ON category.category_cd = price.category_cd;
WHERE句を使った内部結合
SELECT <テーブル名.カラム名>, <テーブル名.カラム名>, ...
FROM <テーブル名1>, <テーブル名2>
WHERE <結合条件>
SELECT category.category_name, price.item_name, price.price
FROM category, price
WHERE category.category_cd = price.cateroy_cd;
外部結合
SELECT <テーブル名.カラム名>, <テーブル名.カラム名>
FROM <テーブル名1> LEFT OUTER JOIN <テーブル名2>
SELECT <テーブル名.カラム名>, <テーブル名.カラム名>
FROM <テーブル名1> RIGHT OUTER JOIN <テーブル名2>
SELECT category.category_name, price.item_name, price.price
FROM category LEFT OUTER JOIN price
ON category.category_cd = price.category_cd;
SELECT category.category_name, price.item_name, price.price
FROM category RIGHT OUTER JOIN price
ON category.category_cd = price.category_cd;
副問い合わせ(サブクエリ)を使った条件指定
SELECT <カラム名> FROM <テーブル名>
WHERE <カラム名> IN(SELECT <カラム名> FROM <テーブル名> WHERE <条件>);
||< 

>||
SELECT * FROM price
WHERE category_cd IN(
  SELECT category_cd FROM category
);