PostgreSQL教程（一）：データ表の詳細

11583 ワード

PostgreSQL 教程データテーブル

一、表の定義：
どのような関係データベースにとっても、テーブルはデータ格納の最も核心的かつ基礎的なオブジェクトユニットである。ここから始めましょう。
1.テーブルを作成する:



    CREATE TABLE products (

        product_no integer,

        name text,

        price numeric

    );

2.テーブルの削除:



    DROP TABLE products;

3.デフォルトの値が付いたテーブルを作成します。



    CREATE TABLE products (

        product_no integer,

        name text,

        price numeric DEFAULT 9.99 --DEFAULT    ，     9.99   price    。

    );

    

    CREATE TABLE products (

        product_no SERIAL,            --SERIAL               ，     Oracle  Sequence。

        name text,

        price numeric DEFAULT 9.99

    );

出力:



    NOTICE:  CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"

4.制約：
チェックの制約は表の中で最も一般的な制約の種類で、あるフィールドの数値がブール式を満たす必要があると宣言することができます。それだけではなく、我々はまた、テーブルレベルのチェックの制約を宣言することができます。



    CREATE TABLE products (

        product_no integer,

        name text,

        --price        0，             ，       。       ，     

        --     ，               ，  PostgreSQL         、        ，

        --        ， ：products_price_check。

        price numeric CHECK (price > 0) 

    );

  

    CREATE TABLE products (

        product_no integer,

        name text,

        --              positive_price。               ，            。

        price numeric CONSTRAINT positive_price CHECK (price > 0) 

    );

以下の制約は、非空の制約であり、つまり、制約されたフィールドは空の値を挿入できないか、既存のデータを空の値に更新することができます。


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

一つのフィールドに複数の制約がある場合、制約を考慮しないで定義することができる。


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

一意制約とは、指定されたフィールドが重複値を挿入できないか、またはあるレコードの値を現在のテーブルの既存の値に更新することです。


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

テーブル内の複数のフィールドの連結一意性を定義します。



    CREATE TABLE example (

        a integer,

        b integer,

        c integer,

        UNIQUE (a, c)

    );

一意制約の名前を付けます。



    CREATE TABLE products (

        product_no integer CONSTRAINT must_be_different UNIQUE,

        name text,

        price numeric

    );

    データを挿入すると、空の値（NULL）の間は等しくないデータとみなされますので、ある一意フィールドに対しては、空の値を複数回挿入することができます。しかし、この規則はすべてのデータベースに守られているわけではないので、データベース移植を行う際には、ある程度のトラブルが発生する可能性があるということに注意したい。

    5.メインキーと外部キー：
    技術的には，主結合制約は，唯一の制約と非空制約の組み合わせにすぎない。



    CREATE TABLE products (

        product_no integer PRIMARY KEY,  --  product_no           。

        name text,

        price numeric

    );

一意の制約と同様に、プライマリキーは複数のフィールドに同時に作用し、結合されたプライマリキーを形成することができる。



    CREATE TABLE example (

        a integer,

        b integer,

        c integer,

        PRIMARY KEY (b, c)

    );

外挿結合制約は、フィールド（またはフィールドのセット）の値が他のテーブルの一部の行の値に一致していることを宣言します。私たちはこの挙動を二つの相関表間の参照完全性と呼びます。



    CREATE TABLE orders (

        order_id integer PRIMARY KEY, --           。

        --   product_no     products   (product_no)   。

        product_no integer REFERENCES products(product_no), 

        quantity integer

    );

    

    CREATE TABLE t1 (

        a integer PRIMARY KEY,

        b integer,

        c integer,

        --                         。

        FOREIGN KEY (b, c) REFERENCES example (b, c)

    );

複数のテーブルの間に主キーの参照制約がある場合、アプリケーションテーブル（主キーテーブル）の中のある行のレコードを削除したい場合、行レコードの主キーフィールド値は、その参照テーブル（外キーテーブル）の中のレコードに関連付けられている可能性があるので、削除操作は失敗します。この動作を完了するためには、参照表とその記録に関連する行を削除した後、参照テーブルの行レコードを削除することが明白な方法である。しかし、PostgreSQLは、このような動作をより便利に行うために提供してくれます。



    CREATE TABLE products (

        product_no integer PRIMARY KEY,

        name text,

        price numeric

    );

    

    CREATE TABLE orders (

        order_id integer PRIMARY KEY,

        shipping_address text

    );

    

    CREATE TABLE order_items (

        product_no integer REFERENCES products ON DELETE RESTRICT, --    

        order_id integer REFERENCES orders ON DELETE CASCADE, --      

        quantity integer,

        PRIMARY KEY (product_no, order_id)

    );

    制限とカスケード削除は最も一般的なオプションです。RESTRICTは参照された行を削除することを禁止します。NO ACTIONは、制約をチェックする時に、まだ何か引用行があれば、エラーを出すという意味です。何も声明しないなら、それはデフォルトです。この二つの選択の実際の違いは、NO ACTIONが拘束検査を事務の遅れに延ばすことを許可します。RESTRICTはだめです。）CASCADEは引用された行を削除すると、その行を引用しても自動的に削除されます。外キーフィールドでの動作には、SET NULLとSET DEFAULTの2つのオプションがあります。このようにすると、参照行が削除されると、参照フィールドはそれぞれ空またはデフォルトに設定されます。これらのオプションは観察と制約から逃れられないように注意してください。例えば、SET DEFAULTの動作宣言があったが、デフォルト値が外キーを満たしていないと動作が失敗します。ON DELETEのように、ON UPDATEのオプションがあります。参照フィールドによって修正（更新）された時に呼び出します。利用可能な動作は同じです。
二、システムフィールド：
    PostgreSQLの各データテーブルには、暗黙的に定義されたいくつかのシステムフィールドが含まれています。したがって、これらの名前はユーザー定義のフィールド名には使用できません。これらのシステムフィールドの機能は、Oracleのrownumやrowidなどに似ています。
    oid:行のオブジェクト識別子(オブジェクトID)このフィールドはテーブルを作成する時のみWITH OIDSを使用しています。または設定パラメータdefault_を設定しています。with_oDSが現れます。このフィールドのタイプはoidです。
    tablebloid：本行の表のOIDを含みます。このフィールドは継承レベルから選択したクエリーに特に役立ちます。それがないと、どの独立したテーブルからの行なのか説明するのが難しいからです。テーブルアロイスはpg_と一緒にできますclassのoidフィールドを接続して表の名前を取得します。
    xmin：当該行のバージョンの事務の識別（事務ID）を挿入します。
    cmin：事務内部に挿入されたコマンドの表示（ゼロから開始）。
    xmax:削除されたトランザクションのID(トランザクションID)は、削除されたラインバージョンではない場合、ゼロです。
    cmax:トランザクション内のコマンド識別子を削除するか、または0です。
    ctid：1つの行のバージョンは、そのテーブル内の物理的な位置にあります。ctidは非常に速いロケーションのために使用されますが、VCUUM FLLのたびに、1ラインのctidが更新されたり、移動されたりします。したがって、ctidは、長い行識別子として使用することができない。
    OIDは32ビットの量で、同じクラスタ内で通用するカウンタに値を割り当てます。大型または長時間使用するデータベースの場合、このカウンタは重複する可能性があります。したがって、OIDが唯一であると仮定すると、それらが唯一であることを保証するために自分で対策を講じる以外は、非常に間違っています。テーブルの行を識別する必要があるなら、シーケンス番号生成器を使用することを強く推奨します。

三、表の修正：
    1.フィールドを追加:



    ALTER TABLE products ADD COLUMN description text;

新規フィールドは、表に既に存在している行に対して、最初に与えられたデフォルト値を塗りつぶします。
フィールドを追加すると、このフィールドに制約を同時に指定することができます。



    ALTER TABLE products ADD COLUMN description text CHECK(description <> '');

2.フィールドの削除:



    ALTER TABLE products DROP COLUMN description;

この表が参照表である場合、フィールドは参照フィールドであるため、上記の削除操作は失敗します。参照フィールドを削除しながら連結したすべての参照フィールドを削除するには、次の構文が適用されます。



    ALTER TABLE products DROP COLUMN description CASCADE;

3.制約を追加:



    ALTER TABLE products ADD CHECK(name <> '');  --        

    ALTER TABLE products ADD CONSTRAINT some_name UNIQUE(product_no);--          。

    ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id) REFERENCES pdt_grps; --      。

    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --        。

4.制約の削除:



    ALTER TABLE products DROP CONSTRAINT some_name;

ネーミングの制約を表示するには、その名前に従って直接削除することができ、陰的に自動的に名付けられた制約は、psqlの「d tableaname」によってその制約の名前を取得することができる。削除フィールドと同様に、依存関係の制約がある場合は、CASCADEを使用する必要があります。1つの例は、参照フィールド上の唯一の制約または主結合制約に依存する外殻結合制約である。例えば:



    MyTest=# \d products

         Table "public.products"

       Column     |  Type   | Modifiers

     ------------+---------+-----------

     product_no | integer |

     name          | text    |

     price           | numeric |

     Check constraints:

        "positive_price" CHECK (price > 0::numeric)

他の制約とは違って、名前がないので、以下の方法でしか削除できません。



    ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

5.フィールドのデフォルト値を変更する:
既存のフィールドにデフォルト値を追加すると、テーブル内の既存のデータ行には影響しません。将来のINSERTコマンドのためにデフォルト値を変更します。



    ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

以下はデフォルトを削除します。



    ALTER TABLE products ALTER COLUMN price DROP DEFAULT

    6.フィールドのデータタイプを変更します。
    フィールド内の既存の項目が一つの暗黙的なタイプで新しいタイプに変換できる場合にのみ成功します。例えば、現在のデータはすべて整体型で、変換の目標タイプはnumericあるいはvarrharで、このような転換は普通はすべて成功することができます。同時に、PostgreSQLはまた、フィールドのデフォルト値（存在する場合）を新しいタイプに変換しようと試み、また、フィールドに関する任意の制約がある。しかし、これらの変換は失敗するかもしれない。フィールドのタイプを変更する前に、制約を削除してから自分の手で修正したものを追加したほうがいいです。



    ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

7.フィールド名を変更する:



    ALTER TABLE products RENAME COLUMN product_no TO product_number;

8.表の名前を変更する:



    ALTER TABLE products RENAME TO items;

四、権限：
テーブルの所有者だけがテーブルの権限を変更または削除できます。権限を付与するには、GRANTコマンドを使用して、REVOKEコマンドを使用して権限を取り消す必要があります。
PUBLICは特殊な「ユーザ」であり、システム中の各ユーザに権限を付与するために使用されてもよいことを指摘したい。宣言権限の位置にALLを書くと、そのオブジェクトのタイプに関するすべての権限が与えられます。



    GRANT UPDATE ON table_name TO user;  --            user。

    GRANT SELECT ON table_name TO GROUP group; --   select        。

    REVOKE ALL ON table_name FROM PUBLIC; --        Public  。

最初は、オブジェクト所有者（またはスーパーユーザ）のみが対象の権限を付与または取り消すことができる。しかし、私たちは「with grant option」の権限を与えられます。そうすると、権限を受け取った人に権限を与えます。与えられたオプションが後で取り消されると、この受け入れ者から権限を受け取ったすべてのユーザ（直接またはクラス連を通した承認）はその権限を失うことになります。
ここで特に説明したいのは、このブログのほとんどのケースと段落はPostgreSQL中国語の文書から取っています。

Ruby文字列処理

ルビー正規表現教程