OSS-DB Silver 暗記 C-1-3

開発/SQL(32%):::SQL:データ定義【重要度:13】

PostgreSQL 11.5文書:データ定義

 

 

 

1.  テーブル

1.1 テーブルの基本

1.1.1 作成と削除

PostgreSQL 11.5文書

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

 

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

 

1.1.2 デフォルト値

PostgreSQL 11.5文書

  • CREATE TABLE  table_name  ( syntax );
    • syntaxcolumn_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 02:38:54.395432
----+----------------------------

 

1.2 制約

PostgreSQL 11.5文書

1.2.1 主キー

PostgreSQL 11.5文書

  • CREATE TABLE  table_name  ( syntax );
    • syntaxcolumn_name  data_type  PRIMARY KEY, […]
    • syntaxcolumn_name  data_type, […,] PRIMARY KEY ( column_name )
  • ALTER TABLE  table_name  syntax;
    • syntaxADD PRIMARY KEY  ( column_name )
    • syntaxADD CONSTRAINT    primary_key_name PRIMARY KEY ( column_name )
    • syntaxDROP 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)
);

 

1.2.2 一意性制約

PostgreSQL 11.5文書

  • CREATE TABLE  table_name  ( syntax );
    • syntaxcolumn_name data_type  UNIQUE, […]
    • syntaxcolumn_name data_type , […,]  UNIQUE KEY ( column_name )
  • ALTER  TABLE  table_name    syntax ;
    • syntaxADD  UNIQUE           ( column_name )
    • syntaxADD  CONSTRAINT   unique_key_name  UNIQUE  ( column_name )
    • syntaxDROP CONSTRAINT  unique_key_name

 

1.2.3 非NULL制約

PostgreSQL 11.5文書

  • CREATE TABLE  table_name  ( syntax );
    • syntaxcolumn_name data_type  NOT NULL
  • ALTER  TABLE  table_name  ALTER COLUMN column_name  syntax;
    • syntaxSET  NOT NULL
    • syntaxDROP NOT NULL

 

1.2.4 外部キー

PostgreSQL 11.5文書

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

 

  • CREATE TABLE  table_name  ( column_name data_type syntax );
    • syntaxREFERENCES  table_name1 ( column_name )
    • syntaxREFERENCES  table_name1
    • syntaxFOREIGN KEY ( column_name ) REFERENCES table_name1 ( column_name )
  • ALTER TABLE  table_name  syntax;
    • syntaxADD 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                           -- REFERENCES products:短縮形
);
=> INSERT INTO orders VALUES (1, 3, 10);
----------+------------+----------
 order_id | product_no | quantity
----------+------------+----------
        1 |          3 |       10
----------+------------+----------

 

1.2.5 検査制約

PostgreSQL 11.5文書

  • CREATE TABLE table_name ( syntax );
    • syntaxcolumn_name data_type CHECK ( expression )
  • ALTER TABLE table_name syntax;
    • syntaxADD  CONSTRAINT  constraint_name CHECK ( expression )
    • syntaxDROP CONSTRAINT constraint_name

 

=> CREATE TABLE products (                       -- 製品テーブル
	product_no int,
	name text,
	price numeric CHECK ( price>0 )          -- expression: price>0
);
=> CREATE TABLE products (
	product_no int,
	name text,                               -- constraint_name: plus_price
	price numeric CONSTRAINT plus_price CHECK ( price>0 )
);
=> ALTER TABLE products DROP CONSTRAINT plus_price;

 

1.2.6 ドメイン制約

PostgreSQL 11.5文書

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

 

 

1.3 テーブルの変更

PostgreSQL 11.5文書

  • ALTER TABLE table_name syntax;
    • syntaxADD   COLUMN column_name data_type
    • syntaxDROP COLUMN column_name
    • syntaxRENAME COLUMN column_name TO new_column_name
    • syntaxRENAME TO new_table_name
    • syntaxOWNER  TO new_owner

 

 

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

PostgreSQL 11.5文書

  • CREATE TABLE table_name ( column_name data_type [, …] ) syntax;
    • syntaxPATITION BY RANGE ( column_name )
  • CREATE TABLE partition_table_name ( column_name data_type [,…] )syntax;
    • syntaxPATITION OF table_name FOR VALUES condition;
  • ALTER TABLE table_name syntax;
    • syntaxATTATCH PARTITION partition_table_name FOR VALUES condirion
    • syntaxDETACH  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);    -- 2020-11-10
------------+------------+----------
  logdate   | product_no | quantity
------------+------------+----------
 2020-11-10 |          3 |      20                     ← orders, orders_y2020m11
------------+------------+----------

 

 

3. スキーマ

PostgreSQL 11.5文書

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

Wiki:名前空間

schemas postgreSQL
Fig.1 schemas

 

 

3.1 スキーマの作成

PostgreSQL 11.5文書

  • CREATE SCHEMA schema_name [ AUTHORIZATION database_user_name ];
  • CREATE SCHEMA AUTHORIZATION database_user_name;
  • DROP SCHEMA schema_name [ CASCADE ];
=> create schema test;
=> create table test.table1 (c1 int, c2 text);

 

3.2 publicスキーマ

PostgreSQL 11.5文書

public schema postgreSQL
Fig.2 public schema

 

 

3.3 スキーマ検索パス

PostgreSQL 11.5文書

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

 

=> show search_path;                            -- search_path 表示
-----------------
   search_path
-----------------
 “$user”, public
-----------------
=> set search_path to test, “$user”, public;    -- search_path 設定
=> show search_path;                            -- search_path 表示
-----------------------
      search_path
-----------------------
 test, “$user”, public
-----------------------

 

スポンサーリンク

コメントを残す

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