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; --