SQL :配列

51085 ワード

database programming sql テキストリンク

このガイドはPostgreSQL用です.
PostgreSQLから始めましょう.
https://www.postgresql.org/
サンプルデータの読み込み
https://dev117uday.gitbook.io/databases/sql/getting-started
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data
SQLの配列

オリジナル文書:

here
構文

column_name DATATYPE[] {CONSTRAINT}

CREATE TABLE table_array
(
    id     SERIAL,
    name   varchar(100),
    grades text[]
);

INSERT INTO table_array (name, grades)
VALUES ('person 1', array ['100','45']);
INSERT INTO table_array (name, grades)
VALUES ('person 2', array ['100','90']);
INSERT INTO table_array (name, grades)
VALUES ('person 3', array ['100','97']);
INSERT INTO table_array (name, grades)
VALUES ('person 4', array ['100','94']);


SELECT name, grades[1]
FROM table_array;

   name   | grades 
----------+--------
 person 1 | 100
 person 2 | 100
 person 3 | 100
 person 4 | 100

表の配列

挿入
非テキストデータのための

、使用{value1,value2}またはarray ['value1','value2']

テキストデータのための

、使用{"value1","value2"}またはarray [value1,value2]

CREATE TABLE teachers
(
    id    serial primary key,
    class text[]
);

CREATE TABLE IF NOT EXISTS teachers
(
    id    serial primary key,
    class text array
);

INSERT INTO teachers (class)
VALUES (array ['english','maths']);

 id |      class      
----+-----------------
  1 | {english,maths}

クエリ

SELECT class[1]
FROM teachers;

  class  
---------
 english


SELECT *
FROM teachers
WHERE class[1] = 'english';

 id |      class      
----+-----------------
  1 | {english,maths}


SELECT *
FROM teachers
WHERE 'english' = any (class);

 id |      class      
----+-----------------
  1 | {english,maths}

更新

update teachers
set class[1] = 'dutch'
WHERE id = 1;

 id |     class     
----+---------------
  1 | {dutch,maths}

Update teachers
set class[3] = 'science'
WHERE id = 1;

 id |         class         
----+-----------------------
  1 | {dutch,maths,science}

無次元の

CREATE TABLE teacher2
(
    id    serial primary key,
    class text array[1]
);

INSERT INTO teacher2 (class)
VALUES (array ['english']);

 id |   class   
----+-----------
  1 | {english}

-- dimensions doesnt matter
INSERT INTO teacher2 (class)
VALUES (array ['english','hindi']);

 id |      class      
----+-----------------
  1 | {english}
  2 | {english,hindi}

巣のない

SELECT id, class, unnest(class)
FROM teacher2;

 id |      class      | unnest  
----+-----------------+---------
  1 | {english}       | english
  2 | {english,hindi} | english
  2 | {english,hindi} | hindi

多次元配列

CREATE TABLE students
(
    id    serial primary key,
    name  varchar(50) not null,
    grade integer[][]
);

INSERT INTO students (name, grade)
VALUES ('s1', '{90,2020}'),
       ('s1', '{70,2020}'),
       ('s1', '{60,2020}');

SELECT *
FROM students;

 id | name |   grade   
----+------+-----------
  1 | s1   | {90,2020}
  2 | s1   | {70,2020}
  3 | s1   | {60,2020}

SELECT *
FROM students
WHERE grade @> '{90}';

 id | name |   grade   
----+------+-----------
  1 | s1   | {90,2020}

SELECT *
FROM students
WHERE '2020' = any (grade);

 id | name |   grade   
----+------+-----------
  1 | s1   | {90,2020}
  2 | s1   | {70,2020}
  3 | s1   | {60,2020}

SELECT *
FROM students
WHERE grade[1] < 80;

 id | name |   grade   
----+------+-----------
  2 | s1   | {70,2020}
  3 | s1   | {60,2020}

アレイ対jsonb

配列の利点

は、セットアップにかなり簡単です

はJSONB

よりも少ない記憶装置を必要とする

は、多次元支持

を持ちます
GINを通して

索引をつけて、質問

を大いにスピードアップします

PostgreSQLのプランナーはPostgreSQLの配列でより良い決定をする可能性があります.

配列の欠点

の主な利点は、1つのデータ型

に制限されていることです

は、配列データ入力の厳しい順序に従わなければならない.

への利点

は

に照会するための追加演算子を提供する
のインデックスのための

のサポート

jsonbの欠点

はバイナリ形式

にJSONデータを解析しなければなりません

は書くのが遅いが、

を読むことでより速いです

は注文

を維持しません

範囲

SELECT INT4RANGE(1, 6)                                                   AS "DEFAULT [(",
       NUMRANGE(1.432, 6.238, '[]')                                      AS "[]",
       DATERANGE('20200101', '20201222', '()')                           AS "DATES ()",
       TSRANGE(LOCALTIMESTAMP, LOCALTIMESTAMP + INTERVAL '8 DAYS', '(]') AS "OPENED CLOSED";

 DEFAULT [( |      []       |        DATES ()         |                       OPENED CLOSED                       
------------+---------------+-------------------------+-----------------------------------------------------------
 [1,6)      | [1.432,6.238] | [2020-01-02,2020-12-22) | ("2021-08-24 05:22:13.03625","2021-09-01 05:22:13.03625"]


SELECT ARRAY [1,2,3]        AS "INT ARRAYS",
       ARRAY [2.123::FLOAT] AS "FLOATING NUMBERS",
       ARRAY [CURRENT_DATE, CURRENT_DATE + 5];

 INT ARRAYS | FLOATING NUMBERS |          array          
------------+------------------+-------------------------
 {1,2,3}    | {2.123}          | {2021-08-24,2021-08-29}


SELECT ARRAY [1,2,3,4] = ARRAY [1,2,3,4],
       ARRAY [1,2,3,4] = ARRAY [1,1,3,4],
       ARRAY [1,2,3,4] <> ARRAY [1,2,3,4],
       ARRAY [1,2,3,4] < ARRAY [1,5,3,4],
       ARRAY [1,2,3,4] <= ARRAY [1,3,3,4],
       ARRAY [1,2,3,4] > ARRAY [1,2,3,4];

 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------+----------+----------
 t        | f        | f        | t        | t        | f

包含演算子

SELECT ARRAY [1,2,3,4] @> ARRAY [2,3,4]       AS "CONTAINS",
       ARRAY ['A','B'] <@ ARRAY ['A','B','C'] AS "CONTAINED BY",
       ARRAY [1,2,3,4] && ARRAY [2,3,4]       AS "IS OVERLAP";

 CONTAINS | CONTAINED BY | IS OVERLAP 
----------+--------------+------------
 t        | t            | t

長さと寸法

SELECT ARRAY [1,2,3] || ARRAY [4,5,6] AS "COMBINED ARRAY";

 COMBINED ARRAY 
----------------
 {1,2,3,4,5,6}

SELECT ARRAY_CAT(ARRAY [1,2,3],
                 ARRAY [4,5,6]) AS "COMBINED ARRAY VIA CAT";

 COMBINED ARRAY VIA CAT 
------------------------
 {1,2,3,4,5,6}

SELECT 4 || ARRAY [1,2,3] AS "ADDING TO ARRAY";

 ADDING TO ARRAY 
-----------------
 {4,1,2,3}

SELECT ARRAY [1,2,3] || 4 AS "ADDING TO ARRAY";

 ADDING TO ARRAY 
-----------------
 {1,2,3,4}

SELECT ARRAY_APPEND(ARRAY [1,2,3], 4) AS "USING APPEND";

 USING APPEND 
--------------
 {1,2,3,4}

SELECT ARRAY_PREPEND(4, ARRAY [1,2,3]) AS "USING APPEND";

 USING APPEND 
--------------
 {4,1,2,3}

SELECT ARRAY_NDIMS(ARRAY [[1,2,3,4],[1,2,3,4],[1,2,3,4]]) AS "DIMENSIONS",
       ARRAY_DIMS(ARRAY [1,2,3,4,2,3,4])                  AS "DIMENSIONS";

 DIMENSIONS | DIMENSIONS 
------------+------------
          2 | [1:7]

SELECT ARRAY_LENGTH(ARRAY [-111,2,3,4], 1);

 array_length 
--------------
            4

SELECT ARRAY_UPPER(ARRAY [1,2,3,4000], 1),
       ARRAY_LOWER(ARRAY [-100,2,3,4], 1);

 array_upper | array_lower 
-------------+-------------
           4 |           1

位置

SELECT array_position(array ['jan','feb','mar'], 'feb');

 array_position 
----------------
              2

SELECT array_position(array [1,2,2,3,4], 2, 3);

 array_position 
----------------
              3

SELECT array_positions(array [1,2,2,3,4], 2);
 array_positions 
-----------------
 {2,3}

検索、置換、削除

SELECT array_cat(array [1,2], array [3,4]);

 array_cat 
-----------
 {1,2,3,4}

SELECT array_append(array [1,2,3], 4);

 array_append 
--------------
 {1,2,3,4}

SELECT array_remove(array [1,2,3,4,4,4], 4);

 array_remove 
--------------
 {1,2,3}

SELECT array_replace(array [1,2,3,4,4,4], 4, 5);

 array_replace 
---------------
 {1,2,3,5,5,5}

イン・イン・イン

SELECT 20 in (1, 2, 3, 20) as "result";
-- t
SELECT 25 in (1, 2, 3, 20) as "result";
-- f
SELECT 25 not in (1, 2, 3, 20) as "result";
-- t
SELECT 20 = all (Array [20,22]), 20 = all (array [20,20]);
-- f
SELECT 20 = any (Array [1,2,25]) as "result";
-- f

配列から文字列

SELECT string_to_array('1,2,3,4,5', ',');

 string_to_array 
-----------------
 {1,2,3,4,5}

SELECT string_to_array('1,2,3,4,5,ABC', ',', 'ABC');

 string_to_array  
------------------
 {1,2,3,4,5,NULL}

SELECT string_to_array('1,2,3,4,,6', ',', '');

 string_to_array  
------------------
 {1,2,3,4,NULL,6}

SELECT array_to_string(ARRAY [1,2,3,4], '|');

 array_to_string 
-----------------
 1|2|3|4

SELECT array_to_string(ARRAY [1,2,3,4,NULL], '|', 'EMPTY');

  array_to_string 
-----------------
 1|2|3|4|EMPTY

Reference

この問題について(SQL :配列), 我々は、より多くの情報をここで見つけました https://dev.to/dev117uday/sql-arrays-28o2

テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。

Collection and Share based on the CC Protocol

7-40～41 defaultメソッド、静的メソッド

CentOS 7(64ビット)下QT 5.5 MySQLデータベースへの接続(driver not loaded)