dokoQLOSS-DBプログラム

OSS-DB Silver 暗記 C-1-3

管理者

開発/SQL（32%）：：：SQL：データ定義【重要度：13】dokoQL可

PostgreSQL 11.5文書：第5章 データ定義



 

  • dokoQL で実習が可能です（スキーマ除く）

 

テーブル

テーブルの基本

作成と削除

PostgreSQL 11.5文書：5.1. テーブルの基本

  • CREATE TABLE  table_name  ( syntax );
    • syntax：column_name  data_type  [, …]
  • DROP TABLE  table_name;

 

=> CREATE TABLE table1 (
	c1 int,
	c2 timestamp
);
=> DROP TABLE table1;

 

デフォルト値

PostgreSQL 11.5文書：5.2. デフォルト値

  • CREATE TABLE  table_name  ( syntax );
    • syntax：column_name  data_type  DEFAULT  { default_value | default_expr }

 

=> CREATE TABLE table2 (
        c1 int DEFAULT 10,
        c2 timestamp DEFAULT now()
);
=> INSERT  INTO  table2(c1)  VALUES  (1);
----+----------------------------
 c1 |            c2
----+----------------------------
  1 | 2020-10-26 12:38:54.395432
----+----------------------------

 

制約

 

  暗記  

  • 制約の種類
    • 主キー制約：PRIMARY KEY
    • 一意性制約：UNIQUE
    • 非NULL制約：NOT NULL
    • 外部キー制約：REFERENCES / FOREIGN KEY
    • 検査制約：CHECK
      • ドメイン制約：CHECK

 

PostgreSQL 11.5文書：5.3. 制約

主キー

PostgreSQL 11.5文書：5.5.4. 主キー

  • CREATE TABLE  table_name  ( syntax );
    • syntax：column_name  data_type  PRIMARY KEY, […]
    • syntax：column_name  data_type, […,] PRIMARY KEY ( column_name )
  • ALTER TABLE  table_name  syntax;
    • syntax：ADD PRIMARY KEY  ( column_name )
    • syntax：ADD CONSTRAINT    primary_key_name PRIMARY KEY ( column_name )
    • syntax：DROP CONSTRAINT  primary_key_name;

 

=> create table table3 (
	id int primary key,
	name text);
=> INSERT  INTO  table3 VALUES  (1, 'aaa');
----+------
 id | name
----+------
  1 | aaa
----+------

=> create table table4 (
	id int,
	name text
        PRIMARY KEY (id)
);

 

一意性制約

  • 格納されるデータに重複がない制約
    • 例外：NULL は重複可

 

PostgreSQL 11.5文書：5.3.3. 一意性制約

  • CREATE TABLE  table_name  ( syntax );
    • syntax：column_name data_type  UNIQUE, […]
    • syntax：column_name data_type , […,]  UNIQUE KEY ( column_name )
  • ALTER  TABLE  table_name    syntax ;
    • syntax：ADD  UNIQUE           ( column_name )
    • syntax：ADD  CONSTRAINT   unique_key_name  UNIQUE  ( column_name )
    • syntax：DROP CONSTRAINT  unique_key_name

 

=> CREATE TABLE products (
       product_no integer UNIQUE,
       name text,
       price numeric
   );
=>
=> CREATE TABLE products1 (
       product_no integer,
       name text,
       price numeric,
       UNIQUE (product_no)
   );

 

 

非NULL制約

  • 列がNULL値を取らない制約

 

PostgreSQL 11.5文書：5.3.2. 非NULL制約

  • CREATE TABLE  table_name  ( syntax );
    • syntax：column_name data_type  NOT NULL
  • ALTER  TABLE  table_name  ALTER COLUMN column_name  syntax;
    • syntax：SET  NOT NULL
    • syntax：DROP NOT NULL

 

=> CREATE TABLE products (
       product_no integer NOT NULL,
       name text NOT NULL,
       price numeric
   );

 

 

外部キー制約

  • 外部キー制約は参照整合性制約とも呼ばれる
  • 参照整合性制約とは
    • 例えば、製品テーブルと注文テーブルがあった場合、
    • 「製品テーブルにない製品の注文は注文テーブルに格納できない」といった制約

 

PostgreSQL 11.5文書：5.3.5. 外部キー

  • CREATE TABLE  table_name  ( column_name data_type syntax );
    • syntax：REFERENCES  table_name1 ( column_name )
    • syntax：REFERENCES  table_name1
    • syntax：, FOREIGN KEY ( column_name ) REFERENCES table_name1 ( column_name )
  • ALTER TABLE  table_name  syntax;
    • syntax：ADD CONSTRAINT  foreign_key_name FOREIGN KEY ( column_name ) REFERENCES  table_name1 ( column_name );

 

=> CREATE TABLE products (         -- 製品テーブル
	product_no  int  PRIMARY KEY,  -- 外部キー制約に必須！
	name  text,
	price  numeric
);
=> INSERT INTO products VALUES (1,'aaa', 1000);
=> INSERT INTO products VALUES (2,'bbb', 2000);
=> INSERT INTO products VALUES (3,'ccc', 3000);
------------+------+-------
 product_no | name | price
------------+------+-------
          1 | aaa  |  1000
------------+------+-------
          2 | bbb  |  2000
------------+------+-------
          3 | ccc  |  3000
------------+------+-------

=> CREATE TABLE orders (             -- 注文テーブル
	order_id int PRIMARY KEY,
	product_no int REFERENCES products (product_no),
	quantity int
);
=> INSERT INTO orders VALUES (1, 3, 10);
----------+------------+----------
 order_id | product_no | quantity
----------+------------+----------
        1 |          3 |       10
----------+------------+----------
=> INSERT INTO oeders VALUES (2, 9, 10);
=> SQL実行エラー

 

検査制約

  • 特定の列の値が論理式を満たす（真値）ように指定する制約

 

PostgreSQL 11.5文書：5.3.1. 検査制約

  • CREATE TABLE table_name ( syntax );
    • syntax：column_name data_type CHECK ( expression )
  • ALTER TABLE table_name syntax;
    • syntax：ADD  CONSTRAINT  constraint_name CHECK ( expression )
    • syntax：DROP CONSTRAINT constraint_name

 

=> CREATE TABLE products (
	product_no int,
	name text,
	price numeric CHECK ( price>0 )
);

=> CREATE TABLE products1 (
	product_no int,
	name text,
	price numeric CONSTRAINT plus_price CHECK ( price>0 )
);

=> ALTER TABLE products1 DROP CONSTRAINT plus_price;

 

ドメイン制約

PostgreSQL 11.5文書：SQLコマンド：CREATE DOMAIN

  • CREATE DOMAIN domain_name [ AS ] data_type [ DEFAULT expression] [ constraint […] ];
    • constraint：[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) }
  • DROP DOMAIN domain_name;

 

=> CREATE DOMAIN postal_code AS TEXT
   CHECK (
       value ~ '^\d{3}-\d{4}$'
   );

=> CREATE TABLE pc_table (
       id SERIAL PRIMARY KEY,
       postal postal_code NOT NULL
   );

=> INSERT INTO pc_table ( postal ) VALUES ( '030-0801' );

=> SELECT * FROM pc_table;
-------------------
      pc_table
------+------------
  id  |   postal
------+------------
   1  |  170-0014
   2  |  160-0021
   3  |  530-0001
   4  |  812-0011
   5  |  030-0801
------+------------

 

テーブルの変更

 

  暗記  

  • ALTER TABLE
    • カラム　：追加・削除・リネーム
    • テーブル：リネーム
    • オーナー：変更

 

PostgreSQL 11.5文書：5.5. テーブルの変更

  • ALTER TABLE table_name syntax;
    • syntax：ADD   COLUMN column_name data_type
    • syntax：DROP COLUMN column_name
    • syntax：RENAME COLUMN column_name TO new_column_name
    • syntax：RENAME TO new_table_name
    • syntax：OWNER  TO new_owner

 

=> ALTER TABLE products RENAME TO items;

 

 

テーブルのパーティショニング

 

  • テーブルを分割（パーティショニング）する
    • 論理的に一つの大きなテーブルを、物理的に小さな部品に分割する
  • 利点が多々ある

 

PostgreSQL 11.5文書：5.10. テーブルのパーティショニング

  • CREATE TABLE table_name ( column_name data_type [, …] ) syntax;
    • syntax：PATITION BY RANGE ( column_name )
  • CREATE TABLE partition_table_name ( column_name data_type [,…] )syntax;
    • syntax：PATITION OF table_name FOR VALUES condition;
  • ALTER TABLE table_name syntax;
    • syntax：ATTATCH PARTITION partition_table_name FOR VALUES condirion
    • syntax：DETACH  PARTITION  partition_table_name
  • DROP TABLE partition_table_name;

例：注文テーブル

=> CREATE TABLE orders (
	logdate date not null,
	product_no int not null,
	quantity int 
) PARTITION BY RANGE ( logdate );

例：分割した各注文テーブル

=> CREATE TABLE orders_y2020m10 PARTITION OF orders
     FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
=> CREATE TABLE orders_y2020m11 PARTITION OF orders
     FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
=> CREATE TABLE orders_y2020m12 PARTITION OF orders
     FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');

=> INSERT INTO orders VALUES ('2020-11-10', 3, 20);
=> SELECT * FROM orders;
------------+------------+----------
  logdate   | product_no | quantity
------------+------------+----------
 2020-11-10 |          3 |      20
------------+------------+----------
=> SELECT * FROM order_y2020m11;
------------+------------+----------
  logdate   | product_no | quantity 
------------+------------+----------
 2020-11-10 |          3 |      20 
------------+------------+----------

 

 

スキーマ

PostgreSQL 11.5文書：5.8. スキーマ　　　　　　　　　　　　　　　　　　　dokoQL不可　　　

  • スキーマ（Schema）とは、DB内で定義される名前空間のこと
  • 名前空間：名前の集合を分割することで衝突の可能性を低減し、参照を容易にする概念

Wiki：名前空間

schemas postgreSQL
Fig.1 schemas

 

 

スキーマの作成

PostgreSQL 11.5文書：5.8.1 スキーマの作成

  • CREATE SCHEMA schema_name [ AUTHORIZATION database_user_name ];
  • CREATE SCHEMA AUTHORIZATION database_user_name;
  • DROP SCHEMA schema_name [ CASCADE ];

例：スキーマ作成と表示

postgres=# CREATE SCHEMA test;
CREATE SCHEMA
postgres=#
postgres=# \dn
---------------------
   List of schemas
----------+----------
    Name  |  Owner
----------+----------
 postgres | postgres
 public   | postgres
 test     | postgres
----------+----------

postgres=# ▯

例：スキーマ使用と削除

postgres=# CREATE TABLE test.table1 (c1 int, c2 text);
CREATE TABLE
postgres=#
postgres=# DROP SCHEMA test CASCADE;
NOTICE: drop cascades to table test:table1
DROP SCHEMA
postgres=# ▯

 

publicスキーマ

PostgreSQL 11.5文書：5.8.2. publicスキーマ

public schema postgreSQL
Fig.2 public schema

 

 

スキーマ検索パス

PostgreSQL 11.5文書：5.8.3. スキーマ検索パス

  • SET search_path TO myschema [ , “$user”, public ];

例：スキーマ検索パス表示

postgres=# show search_path;
-----------------
   search_path
-----------------
 “$user”, public
-----------------

postgres=# ▯

例：スキーマ検索パス設定

postgres=# set search_path to test, “$user”, public;
SET
postgres=#
postgres=# show search_path;
-----------------------
      search_path
-----------------------
 test, “$user”, public
-----------------------

postgres=# ▯

 

参考

 

こちらもおすすめ

OSS-DB Silver 暗記 A-1

管理者

１分タイマーの作り方

管理者

さくらのVPS：一覧ページ（Index of） の非表示

管理者

コメントを残す

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