OSS-DB Silver 暗記 C-1-4
Contents
開発/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;
ビュー
- 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文書：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;
複数列インデックス
- インデックスは、テーブルの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 テーブル名;
=> CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
ルール
- 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; --
