OSS-DB Silver 暗記 C-1-4
Contents
開発/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文の結果を仮想的なテーブルにしたもの
- 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文書：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;
複数列インデックス
- インデックスは、テーブルの2つ以上の列に対して定義できる
例えば、/devディレクトリの内容をDBに保持している場合、
デバイスファイルのメジャー番号とマイナー番号の2つをインデックにする
=> CREATE TABLE test (
major int,
minor int,
name varchar);
=> CREATE INDEX test_mm_idx ON test (major, minor);
式に対するインデックス
- インデックスは、テーブルの1つ以上の列から計算される関数やスカラ式として定義できる
=> CREATE INDEX people_name ON ((first_name || ' ' || last_name)); => SELECT * FROM people WHERE (first_name || ' ' || last_name)='John Smith';
部分インデックス
- 部分インデックスとは、テーブルの部分集合に構築されるインデックスです
- 部分集合は条件式で定義する
=> 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に書き換える仕組み
- 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; --
参考
- IT資格といえばLPI-Japan | LinuC/OSS-DB/HTML5/ACCEL/OPCEL
- PostgreSQL 11.5文書
- OSS教科書 OSS-DB Silver Ver2.0対応
- 徹底攻略OSS-DB Silver問題集［Ver.2.0］対応
- dokoQL 学習用オンラインSQL実行環境
- スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)
