Skip to content

Instantly share code, notes, and snippets.

@itokami1123dev

itokami1123dev/CAM-study-PostgreSQL-20150925.png

Last active September 25, 2015 01:17
Show Gist options
  • Save itokami1123dev/94a4bf4633e084e9e054 to your computer and use it in GitHub Desktop.
Save itokami1123dev/94a4bf4633e084e9e054 to your computer and use it in GitHub Desktop.
Download ZIP
データベース初心者勉強会(2015/09/25)PostgreSQL
Raw
fukuoka_cam_study_postgres_20150925.md

データベース初心者勉強会

  • コマンド入力で基礎から学ぼう 2015/09/25(金)

  • http://www.zusaar.com/event/12077003

  • PostgreSQLを利用してデータベースのコマンド操作を学びます。

  • おもにSQLコマンドの学習となる予定です。

内容

データベースを作ろう

# データベース一覧表示
psql -l

# 自分専用のデータベースを作成する
createdb "your name"
## example
## createdb cam

# 出来ているかデータベース一覧で確認
psql -l

# 作成したDBに接続
psql -d "your name"
## example
## psql -d cam

# 終了 (円マーク + q)
\q

テーブルを作ろう

# 作成したDBに接続
psql -d "your name"

# テーブル一覧を確認
# (円マーク + d)
\d

CREATE TABLE テーブル作成SQL

-- fruits 果物
CREATE TABLE fruits
     ( id    INTEGER
     , name  TEXT
     , price INTEGER
     ) ;
  • INTEGER : 整数 (-2147483648から+2147483647)
  • TEXT : 文字列 (制限なし可変長)
id name price
1 りんご 200
2 みかん 100
3 ばなな 300 
# テーブル一覧を確認
\d
-- テーブル削除
DROP TABLE fruits ;

-- もう一回作成して CREATE TABLEして
\d fruits
--  テーブル "public.fruits"
--  カラム  |   型    | 修飾語
-- --------+---------+--------
--  id     | integer |
--  name   | text    |
--  price  | integer |

データの登録

-- テーブルを作ったときは空っぽです
-- SEL.00
SELECT * FROM fruits ;
-- id | name | price
-- ----+------+-------
-- (0 rows)
-- INS.10
INSERT INTO fruits
       (id,    name, price)
VALUES ( 1, 'りんご',   200)
     , ( 2, 'みかん',   100)
     , ( 3, 'ばなな',   300)
;

-- SEL.10
SELECT * FROM fruits ;
-- id |  name  | price
-- ---+--------+-------
--  1 |  りんご |   200
--  2 |  みかん |   100
--  3 |  ばなな |   300

200円以上の果物の名前と価格を表示

-- SEL.20
SELECT name
     , price
  FROM fruits
 WHERE price >= 200
;

-- name  | price
-- ------+-------
-- りんご |   200
-- ばなな |   300

果物を顧客が注文することを表現

-- customer 顧客
CREATE TABLE customer
     ( id    INTEGER
     , name  TEXT
     ) ;

-- INS.20
INSERT INTO customer
       (id,   name)
VALUES ( 1, '太郎')
     , ( 2, 'まいく')
     , ( 3, '次郎')
     ;

-- SEL.30
SELECT * FROM customer;
-- id |  name
-- ---+--------
--  1 | 太郎
--  2 | まいく
--  3 | 次郎

-- orders 注文履歴
CREATE TABLE orders
     ( id          INTEGER
     , order_date  DATE
     , customer_id INTEGER
     , fruits_id   INTEGER
     , quantity    INTEGER
     ) ;
  • DATE : 日付(時刻はない)

9/5 太郎 が ばなな を 10つ買う

-- INS.30
INSERT INTO orders
     ( id,   order_date, customer_id, fruits_id, quantity)
VALUES
     (  1, '2015-09-05',           1,         3,       10)
;

-- SEL.40
SELECT * FROM orders ;
--  id |     order_date      | customer_id | fruits_id | quantity
-- ----+---------------------+-------------+-----------+----------
--   1 | 2015-09-05 00:00:00 |           1 |         3 |       10

顧客名と果物名で表示する

-- SEL.50
SELECT
       o.order_date
     , c.name
     , f.name
     , o.quantity
  FROM orders o
       INNER JOIN customer c
          ON c.id = o.customer_id
       INNER JOIN fruits f
          ON f.id = o.fruits_id
;
-- order_date          | name |  name  | quantity
-- --------------------+------+--------+----------
-- 2015-09-05 00:00:00 | 太郎  | ばなな  |       10

まだ登録されていない果物があるとき

-- INS.40
INSERT INTO orders
     ( id,   order_date, customer_id, fruits_id, quantity)
VALUES
     (  2, '2015-09-10',           1,         4,        6)
;

-- さっきのSQL(SEL.50)を試すと ... 表示されない...

-- SEL.60
-- INNER JOIN を LEFT JOINに変更
SELECT
       o.order_date
     , c.name
     , f.name
     , o.quantity
  FROM orders AS o
       LEFT JOIN customer AS c
         ON c.id = o.customer_id
       LEFT JOIN fruits   AS f
         ON f.id = o.fruits_id
;
--    order_date       | name |  name  | quantity
-- --------------------+------+--------+----------
-- 2015-09-05 00:00:00 | 太郎 |  ばなな |       10
-- 2015-09-10 00:00:00 | 太郎 |        |        6

ちょっと寄り道 INNER, FULL, LEFT, FULL, CROSS

A Visual Explanation of SQL Joins http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

CREATE TABLE tableA (id INTEGER, name TEXT);
INSERT INTO tableA VALUES (1, 'Pirate'),(2, 'Monkey'),(3, 'Ninja'), (4, 'Spaghetti');
CREATE TABLE tableB (id INTEGER, name TEXT);
INSERT INTO tableB VALUES (1, 'Rutabaga'),(2, 'Pirate'),(3, 'Darth Vader'), (4, 'Ninja');

IDの重複

-- customer 間違って三郎を太郎と同じIDで登録
-- INS.50
INSERT INTO customer (id, name) VALUES ( 1, '三郎') ;

-- SEL.60 のLEFT JOINのSELECTを試すと...
--     order_date      | name |  name  | quantity
-- --------------------+------+--------+----------
-- 2015-09-05 00:00:00 |  太郎 | ばなな  |       10
-- 2015-09-10 00:00:00 |  太郎 |        |        6
-- 2015-09-05 00:00:00 |  三郎 | ばなな  |       10
-- 2015-09-10 00:00:00 |  三郎 |        |        6

間違ったデータを削除

DELETE FROM customer WHERE name = '三郎';

IDを重複させない

プライマリキー制約は単純に一意性制約と非NULL制約を組み合わ http://www.postgresql.jp/document/9.4/html/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS

-- プライマリキー制約
ALTER TABLE customer ADD PRIMARY KEY (id);

-- customer 間違って三郎を太郎と同じIDで登録すると
-- INS.60
INSERT INTO customer (id, name) VALUES ( 1, '三郎') ;
-- ERROR:  duplicate key value violates unique constraint "customer_pkey"
-- DETAIL:  Key (id)=(1) already exists.

-- これで間違いがなくなりました

自動連番機能でIDをつける

-- シーケンスの作成
CREATE SEQUENCE customer_id_seq;

-- 連番を1から始める
SELECT setval('customer_id_seq', 1) ;

-- 二つ進める
SELECT nextval('customer_id_seq') ;
SELECT nextval('customer_id_seq') ;

-- 現在の連番
SELECT currval('customer_id_seq') ;

INSERT時に勝手に連番を振るようにする

-- id属性に シーケンスを設定
ALTER TABLE customer
      ALTER COLUMN id SET DEFAULT nextval('customer_id_seq');

-- テーブルの定義を確認
\d customer

-- 現在どこまで進んでいるか確認
SELECT MAX(id) FROM customer;
--  max
-- -----
--    3

-- 現在の最大値を設定
SELECT setval('customer_id_seq', 3) ;
SELECT setval('customer_id_seq', (SELECT MAX(id) FROM customer)) ;

-- customer 三郎を追加
-- INS.70
INSERT INTO customer (name) VALUES ('三郎') ;

-- 自動的に id:4 で 三郎が登録されている
SELECT * FROM customer;
--  id |  name
-- ----+--------
--   1 | 太郎
--   2 | まいく
--   3 | 次郎
--   4 | 三郎

誤った注文を更新して修正

-- SEL.60 登録していない果物が名前が表示されない

-- INS.40 で登録したfrutsのIDを更新
UPDATE orders SET fruits_id = 2 WHERE id = 2;

-- SEL.60 確認
--     order_date      | name |  name  | quantity
-- --------------------+------+--------+----------
-- 2015-09-10 00:00:00 |  太郎 |  みかん |        6
-- 2015-09-05 00:00:00 |  太郎 |  ばなな |       10

指定した表示順で並べる

-- りんご を 太郎 が 3つ 注文
INSERT INTO orders
     ( id,   order_date, customer_id, fruits_id, quantity)
VALUES
     (  3, '2015-09-15',           1,         1,        3)
;

-- ばなな を 太郎 が 8房 注文
INSERT INTO orders
     ( id,   order_date, customer_id, fruits_id, quantity)
VALUES
     (  4, '2015-09-20',           1,         3,        8)
;

-- 購入日付順に表示 ORDER BY
SELECT
       o.order_date
     , c.name
     , f.name
     , o.quantity
  FROM orders AS o
       LEFT JOIN customer AS c
         ON c.id = o.customer_id
       LEFT JOIN fruits   AS f
         ON f.id = o.fruits_id
 ORDER BY o.order_date
;
--     order_date      | name |  name  | quantity
-- --------------------+------+--------+----------
-- 2015-09-05 00:00:00 | 太郎  | ばなな  |       10
-- 2015-09-10 00:00:00 | 太郎  | みかん  |        6
-- 2015-09-15 00:00:00 | 太郎  | りんご  |        3
-- 2015-09-20 00:00:00 | 太郎  | ばなな  |        8
-- 果物の注文合計数を集計する
SELECT
       f.name
     , SUM(o.quantity) AS total_qty
  FROM orders AS o
       LEFT JOIN customer AS c
         ON c.id = o.customer_id
       LEFT JOIN fruits   AS f
         ON f.id = o.fruits_id
 GROUP BY f.name
;
-- name  | total_qty
-- ------+-----------
-- みかん |         6
-- りんご |         3
-- ばなな |        18

-- 集計した結果 10を超えるもの
SELECT
       f.name
     , SUM(o.quantity) AS total_qty
  FROM orders AS o
       LEFT JOIN customer AS c
         ON c.id = o.customer_id
       LEFT JOIN fruits   AS f
         ON f.id = o.fruits_id
 GROUP BY f.name
HAVING SUM(o.quantity)>10
;
--  name  | total_qty
-- -------+-----------
--  ばなな |        18
-- 日付の累計
SELECT
       o.order_date
     , c.name
     , f.name
     , o.quantity
     , SUM(o.quantity) OVER (ORDER BY o.order_date)
  FROM orders AS o
       LEFT JOIN customer AS c
         ON c.id = o.customer_id
       LEFT JOIN fruits   AS f
         ON f.id = o.fruits_id
 ORDER BY o.order_date
;
--     order_date      | name |  name  | quantity | sum
-- --------------------+------+--------+----------+-----
-- 2015-09-05 00:00:00 | 太郎  | ばなな |       10 |  10
-- 2015-09-10 00:00:00 | 太郎  | みかん |        6 |  16
-- 2015-09-15 00:00:00 | 太郎  | りんご |        3 |  19
-- 2015-09-20 00:00:00 | 太郎  | ばなな |        8 |  27

-- 注文の数量のランキングを横に付与する
SELECT
       o.order_date
     , c.name
     , f.name
     , o.quantity
     , RANK() OVER (ORDER BY o.quantity DESC)
  FROM orders AS o
       LEFT JOIN customer AS c
         ON c.id = o.customer_id
       LEFT JOIN fruits   AS f
         ON f.id = o.fruits_id
 ORDER BY o.order_date
;

PostgreSQL Advent Calendar 2012 Window関数 - 導入編 http://chopl.in/blog/2012/12/01/window-function-tutorial.html

Let's Postgres http://lets.postgresql.jp/documents/technical/window_functions



復習 ２つのテーブルを作ってデータを入れてください


-- 従業員マスタ
CREATE TABLE employee ;

-- 部署マスタ
CREATE TABLE dept ;


アイディア


    

  • 

    各人 CREATE DATABASE してもらって自分のDBを持ってもらう
    

    • 

  • 

    ターミナルで使えるようにして 各人のPCに PostgreSQLは入れてなくても良いようにする
    

    • 

  • 

    テストデータの入っているDBを用意して COPYコマンドや dumpコマンドでデータを入れてもらう
    

    • 

  • 

    統計情報
    

    • 



EXPLAIN ANALYZE
SELECT name, price FROM fruits WHERE price >= 200 ;




COPY foo FROM '/path/to/file' (FORMAT csv, DELIMITER '    ', HEADER true);
PostgreSQLのCOPYコマンドでヘッダ付きtsvファイルを読み込む
http://qiita.com/eiryu/items/a0cfd23334156cbc959f




自分用メモ


    

  • 

    initdb /usr/local/var/postgres/tablespace -E utf8
    

    • 

  • 

    pg_ctl -D /usr/local/var/postgres/tablespace -l  /usr/local/var/postgres/server.log start
    

    • 




  


  





      
      

        

          

            









          


          

            

  
    Sign up for free
    to join this conversation on GitHub.
    Already have an account?
    Sign in to comment