dokoQLOSS-DBプログラム

OSS-DB Silver 暗記 C-1-4

管理者 0 Comments

開発/SQL（32%）：：：SQL：その他のデータベースオブジェクト【重要度：13】dokoQL可

PostgreSQL 11.5文書：その他のデータベースオブジェクト



 

  • dokoQL で実習が可能です（関数・プロシージャ・テーブルスペース除く）

 

シーケンス

  • 連番を自動生成するオブジェクト

 

PostgreSQL 11.5文書：CREATE SEQUENCE

  • CREATE SEQUENCE — 新しいシーケンスジェネレータを定義する

 

CREATE SEQUENCE  シーケンス名
    [START 開始値] [INCREMENT 増加値];

DROP SEQUENCE シーケンス名;

例：シーケンスの作成と削除

=> CREATE SEQUENCE seq START 100 INCREMENT 10;
=> SELECT setval('seq', 1000);
=> SELECT currval('seq');
---------
 currval
---------
    1000
---------
=> SELECT nextval('seq');
---------
 nextval
---------
    1010
---------
=> DROP SEQUENCE seq;

 

 

ビュー

  • SELECT文の結果を仮想的なテーブルにしたもの

 

PostgreSQL 11.5文書：CREATE VIEW

  • CREATE VIEW — 新しいビューを定義する

 

CREATE VIEW ビュー名
    [(列名 [,...])] AS SELECT文

DROP VIEW ビュー名;

例：フィルムテーブル作成

=> CREATE TABLE films (
        code text,
        title text,
        did int,
        date_prod date,
        kind text
);
=> INSERT INTO films VALUES
       ('AB100', 'AAA AAAAAA AAAA', 110, '2000-01-01', 'Action'),
       ('CD120', 'BBB BBBBBB BBBB', 140, '2001-02-02', 'Action'),
       ('EE502', 'ZZZ ZZZZZZ ZZZZ', 105, '2003-03-03', 'Comedy'),
       ('LL601', 'SSSSSSS', 106, '2004-04-04', 'Action'),
       ('QS502', 'PPPPPPP', 107, '2005-05-05', 'Comedy'),
       ('SO321', 'A OOOO', 210, '2006-06-06', 'SF'),
       ('SL111', 'A RRRR', 211, '2007-07-07', 'SF'),
       ('LX388', 'A XXXX', 212, '2008-08-08', 'Comedy');

例：ビューの作成と削除

=> CREATE VIEW comedies AS
       SELECT * FROM films WHERE kind='Comedy';
=> SELECT * FROM comedies;
-------+-----------------+-----+------------+--------
 code  |      title      | did | date_prod  |  kind
-------+-----------------+-----+------------+--------
 EE502 | ZZZ ZZZZZZ ZZZZ | 105 | 2003-03-03 | Comedy
 QS502 | PPPPPPP         | 107 | 2005-05-05 | Comedy
 LX388 | A XXXX          | 212 | 2008-08-08 | Comedy
-------+-----------------+-----+------------+--------
=> DROP VIEW comedies;

 

 

マテリアライズドビュー

  • 実体のあるビュー
    • SELECT文の結果を実体のあるテーブルにしたもの

 

PostgreSQL 11.5文書：CREATE MATERIALIZED VIEW

  • CREATE MATERIALIZED VIEW — 新しいマテリアライズドビューを定義する

 

CREATE MATERIALIZED VIEW マテリアライズドビュー名
    [(列名 [,...])] AS SELECT文

DROP VIEW マテリアライズドビュー名;

=> CREATE MATERIALIZED VIEW sf AS
       SELECT * FROM films WHERE kind='SF';
=> INSERT INTO films VALUES
       ('SP755', 'Z QQQQ', 213, '2009-09-09', 'SF');
=> REFRESH MATERIALIZED VIEW sf;      -- リフレッシュ
=> SELECT * FROM sf;
-------+--------+-----+------------+------
 code  | title  | did | date_prod  | kind
-------+--------+-----+------------+------
 S0321 | A OOOO | 210 | 2006-06-06 | SF
 SL111 | A RRRR | 211 | 2007-07-07 | SF
 SP755 | Z QQQQ | 213 | 2009-09-09 | SF
-------+--------+-----+------------+------
=> DROP VIEW comedies;

 

 

インデックス

  • 索引のこと

 

PostgreSQL 11.5文書：インデックス

 

インデックスの作成

PostgreSQL 11.5文書：CREATE INDEX

  • CREATE INDEX — 新しいインデックスを定義する

 

CREATE INDEX インデックス名 ON テーブル名
    [USING インデックス種別]
    (列名 [,...])];
    インデックス種別：{ B-Tree | GiST | GIN | ハッシュ }

DROP INDEX インデックス名;

例：インデックスの作成と削除

=> CREATE MATERIALIZED VIEW sf AS
       SELECT * FROM films WHERE kind='SF';
=> CREATE TABLE test (
       id integer,
       content varchar
);
=> CREATE INDEX test_id_index ON test (id);
=>
=> DROP INDEX test_id_index;

複数列インデックス

PostgreSQL 11.5文書：複数列インデックス

  • インデックスは、テーブルの2つ以上の列に対して定義できる

 

例えば、/devディレクトリの内容をDBに保持している場合、

デバイスファイルのメジャー番号とマイナー番号の2つをインデックにする

=> CREATE TABLE test (
       major int,
       minor int,
       name varchar);
=> CREATE INDEX test_mm_idx ON test (major, minor);

 

式に対するインデックス

PostgreSQL 11.5文書：式に対するインデックス

  • インデックスは、テーブルの1つ以上の列から計算される関数やスカラ式として定義できる

 

=> CREATE INDEX people_name ON ((first_name || ' ' || last_name));
=> SELECT * FROM people WHERE (first_name || ' ' || last_name)='John Smith';

 

部分インデックス

PostgreSQL 11.5文書：部分インデックス

  • 部分インデックスとは、テーブルの部分集合に構築されるインデックスです
    • 部分集合は条件式で定義する

 

=> CREATE TABLE access_log (
       url varchar,
       client_ip inet
);
=> CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
       WHERE NOT (client_ip>inet '192.168.100.0' AND
                  client_ip<inet '192.168.100.255');
=> SELECT * FROM access_log
       WHERE url='/index.html' AND client_ip=inet '212.78.10.32';
=> SELECT * FROM access_log
       WHERE client_ip=inet '192.168.100.23';

 

 

トリガ

  • 銃の引き金のこと
  • テーブルの行に 挿入/更新/削除 が行われたとき指定した関数を呼び出す機能

 

PostgreSQL 11.5文書：CREATE TRIGGER

  • CREATE TRIGGER — 新しいトリガを定義する

 

CREATE TRIGGER トリガ名 { BEFORE | AFTER }
    { UPDATE | INSERT | DELETE | TRUNCATE } ON テーブル名
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE { FUNCTION | PROCEDURE } 関数名(引数);

DROP TRIGGER トリガ名 ON テーブル名;
  • 発動タイミング
    • 更新前：BEFORE
    • 更新後：AFTER
  • キーワード
    • 更新前の値参照：NEW
    • 更新後の値参照：OLD
  • NEW / OLD
    • 両方使用可：UPDATE
    • NEW のみ：INSERT
    • OLD のみ：DELETE

 

=> CREATE TRIGGER check_update
       BEFORE UPDATE ON accounts
       FOR EACH ROW
       EXECUTE FUNCTION check_account_update();

 

 

ルール

  • SQLを別のSQLに書き換える仕組み

 

PostgreSQL 11.5文書：CREATE RULE

  • CREATE RULE — 新しい書き換えルールを定義する

 

CREATE RULE ルール名
    AS ON [SELECT | INSERT | UPDATE | DELETE]
    TO テーブル名 [WHERE 条件]
    DO [ALSO | INSTEAD] SQL文;

DROP RULE ルール名 ON テーブル名;

例：ルール作成

=> CREATE TABLE table1 (a int);
=> CREATE TABLE table2 (b int);
=>
=> CREATE RULE table_insert AS ON INSERT
       TO table1
       DO ALSO INSERT INTO table2 VALUES (NEW.a*2);

例：ルールの利用と削除

=> INSERT INTO table1 VALUES(100);
=> SELECT * FROM table1;
-----------
     a
-----------
    100
-----------
=> SELECT * FROM table2;
-----------
     b
-----------
    200
-----------
=>
=> DROP RULE table_insert ON table1;

 

 

関数

  • 関数とプロシージャはほぼ同じもの
  • 関数とプロシージャの違い
    • 関数は戻り値を使用できるが、プロシージャはできない
    • 関数はSELECT で呼び出し、プロシージャはCALL で呼び出す
    • 関数はトランザクション処理を記述できないが、プロシージャはできる

 

dokoQL不可

PostgreSQL 11.5文書：CREATE FUNCTION

  • CREATE FUNCTION — 新しい関数を定義する

 

CREATE FUNCTION 関数名（引数）
    RETURNS 戻り値
    AS '関数の記述'
    LANGUAGE 使用言語
    {IMMUTABLE | STABLE | VOLATILE}
    {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT};

DROP FUNCTION 関数名（引数） [CASCADE | RESTRICT];

例：関数作成

postgres=# CREATE FUNCTION add (int, int)
               RETURNS INT
               AS 'SELECT $1+$2;'
               LANGUAGE SQL
               IMMUTABLE
               RETURNS NULL ON NULL INPUT;
postgres=#
postgres=# \df
-----------------------------------------------------------------
                        List of functions
----------+------+------------------+---------------------+------
  Schema  | Name | Result data type | Argument data types | Type
----------+------+------------------+---------------------+------
 postgres | add  | integer          | integer, integer    | func
----------+------+------------------+---------------------+------

postgres=# ▯

例：関数使用・削除

postgres=# SELECT add (10, 20);
---------
   add 
---------
    30
---------

postgres=#
postgres=# DROP FUNCTION add (int, int);
DROP FUNCTION
postgres=# SELECT add (10, 20);
ERROR: function add(integer,integer) does not exist
postgres=# ▯

 

 

プロシージャ

PostgreSQL 11.5文書：CREATE PROCEDURE

  • CREATE PROCEDURE — 新しいプロシージャを定義する

 

dokoQL不可

CREATE PROCEDURE プロシージャ名（引数）
    AS '関数の記述'
    LANGUAGE 使用言語;

DROP PROCEDURE プロシージャ名（引数） [CASCADE | RESTRICT];

例：プロシージャ作成

postgres=# CREATE TABLE tbl (a int);
CREATE TABLE
postgres=# CREATE PROCEDURE insert_data (a int, b int)
               LANGUAGE SQL
               AS $$
                   INSERT INTO tbl VALUES (a);
                   INSERT INTO tbl VALUES (b);
               $$;
CREATE PROCEDURE
postgres=# ▯

例：プロシージャ使用・削除

postgres=# CALL insert_data (10,20);
CALL
postgres=# SELECT * FROM tbl;
----------
    a 
----------
    10
----------
    20
----------
postgres=# DROP PROCEDURE insert_data (int, int);
DROP PROCEDURE
postgres=# ▯

 

 

テーブルスペース

  • データベースオブジェクトを格納する場所（ディレクトリ）

 

PostgreSQL 11.5文書：CREATE TABLESPACE

  • CREATE TABLESPACE — 新しいテーブル空間を定義する

 

dokoQL不可

CREATE TABLESPACE テーブルスペース名
    [OWNER {new_owner | CURRENT_USER | SESSION_USER}]
    LOCATION 'パス名';

DROP TABLESPACE テーブルスペース名;

 

例：/home/postgres/dbs にテーブル空間 dbspace を作成する

postgres=# CREATE TABLESPACE dbspace LOCATION '/home/postgres/dbs';
postgres=# \db
-----------------------------------------
           List of tablespaces
------------+----------+--------------------
    Name    |   Owner  |      Location 
------------+----------+--------------------
 dbspace    | postgres | /home/postgres/dbs
 pg_default | postgres |
 pg_global  | postgres | 
------------+----------+--------------------

 

例：テーブル空間 dbspace にテーブル tbl を作成する

postgres=# CREATE TABLE tbl (a int) TABLESPACE dbspace;

例：テーブル空間 dbspace を削除する

postgres=# DROP TABLE tbl;            -- テーブル空間を空にする
postgres=# DROP TABLESPACE dbspace;  --

 

参考

 

こちらもおすすめ

さくらのVPS：Laravel デプロイ

管理者 0

Web Storage とJSON の試作プログラム

管理者 0

１分タイマーの作り方

管理者 0

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です