OSS-DB Silver 暗記 C-1-4

開発/SQL(32%):::SQL:その他のデータベースオブジェクト【重要度:13】

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

 

 

 

シーケンス

PostgreSQL 11.5文書:CREATE SEQUENCE

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

 

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

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;

 

 

ビュー

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;

 

 

マテリアライズドビュー

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 インデックス種別]
    (列名 [,...])];

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 テーブル名;

 

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

 

 

ルール

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);
=> DROP RULE table_insert ON table1;

 

 

関数

PostgreSQL 11.5文書:CREATE FUNCTION

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

 

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

DROP FUNCTION 関数名(引数) [CASCADE | RESTRICT];

 

=> CREATE FUNCTION add (integer, integer)
       RETURN INTEGER
       AS 'SELECT $1+$2;'
       LANGUAGE SQL
       IMMUTABLE
       RETURN NULL ON NULL INPUT;
=> SELECT add (10, 20);
---------
   add 
---------
    30
---------
=> DROP FUNCTION add (integer, integer);
=> SELECT add (10, 20);
ERROR: function add(integer,integer) does not exist

 

 

プロシージャ

PostgreSQL 11.5文書:CREATE PROCEDURE

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

 

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

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

 

=> CREATE PROCEDURE insert_data (a integer, b integer)
       LANGUAGE SQL
       AS $$
           INSERT INTO tbl VALUES (a);
           INSERT INTO tbl VALUES (b);
       $$; 

=> CREATE TABLE tbl (a integer);
=> CALL insert_data (10,20);
=> SELECT * FROM tbl;
----------
    a 
----------
    10
----------
    20
----------
=> DROP PROCEDURE insert_data (integer, integer);

 

 

テーブルスペース

PostgreSQL 11.5文書:CREATE TABLESPACE

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

 

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

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

 

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

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

 

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

=> CREATE TABLE tbl (a int) TABLESPACE dbspace;

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

=> DROP TABLE tbl;                      -- テーブル空間を空にする
=> DROP TABLESPACES dbspace;            --

 

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。