PostgreSQL基礎

lwx_R發表於2024-08-27
SQL

1.資料型別

1.4 布林型別

bool

1.5 網路地址型別

  • cidr：對ip和子網掩碼合法性做校驗，輸出時會帶子網掩碼
  • inet：對ip做校驗，輸出時有可能帶子網掩碼
  • macaddr和macaddr8：MAC地址

1.5.1 運算子

1.5.2 函式

  • host: 取ip地址 SELECT host(cidr '192.168.2.0/24')
  • text: 取ip和子網掩碼 SELECT text(cidr '192.168.2.0/24')
  • netmask：取子網掩碼 SELECT netmask(cidr '192.168.2.0/24')

1.6 陣列

插入方式：

  • {val1,val2}：
  • array函式： array[1,2,3]
    查詢： 下標1-n SELECT d_array[1] FROM test

1.6.1 函式

  • 追加： SELECT array_append(ARRAY[1,2,3], 4) / SELECT ARRAY[1,2,3] || 4
  • 刪除: SELECT array_remove(ARRAY[1,2,3], 2)
  • 更新： update test set d_array[2] = 3 / update test set d_array = array[1,2]
  • 陣列緯度: SELECT array_ndims(ARRAY[1,2,3])
  • 陣列長度： SELECT array_length(ARRAY[1,2,3], 1) // 第二個引數為緯度，二維陣列填2
  • 元素位置： SELECT array_position(ARRAY[1,2,3], 1)
  • 元素替換： SELECT array_replace(ARRAY[1,2,3], 1,3)
  • 以字串輸出： SELECT array_to_string(ARRAY[1,2,null], ',', '0') // 第二引數為分隔符，第三個為替換null的數

1.6.2 運算子

1.7 範圍型別

2.SQL高階特性

2.1 WITH查詢

CTE:Common Table Expression
相當於中間表

WITH r as (
	SELECT generate_series(3)
)
SELECT * from r

遞迴使用：

// 1-5 之和
WITH recursive t (x) as (
    SELECT 1
    UNION
    SELECT x + 1
    FROM t
    WHERE x < 5
)
SELECT sum(x) FROM t;

2.2 批次插入

  • INSERT INTO table_name SELECT...FROM source_table
  • INSERT INTO tbl_batch3(id, info) VALUES (1, 'a'), (2, 'b'), (3, 'c');
  • COPY: COPY pguser.tbl_batch4 FROM '/home/pg10/tbl_batch4.txt';

2.3 RETURNING

  • 插入：INSERT INTO test(a_boolean) VALUES ('f') RETURNING *;
  • 更新：UPDATE test SET a_boolean='f' RETURNING *;
  • 刪除：DELETE FROM test RETURNING *;

2.4 UPSERT

資料插入過程中資料衝突的情況，比如違反使用者自定義約束

INSERT INTO user_logins(user_name, login_cnt)
VALUES ('matiler',1), ('francs',1)
ON CONFLICT(user_name)
DO UPDATE SET
login_cnt=user_logins.login_cnt+EXCLUDED.login_cnt, last_login_time=now();
  • 衝突但是不做
INSERT INTO user_logins(user_name, login_cnt)
VALUES ('tutu',1), ('francs',1)
ON CONFLICT(user_name) DO NOTHING;

2.5 資料抽樣

order by random 效率低

  • SYSTEM抽樣方式
    SYSTEM抽樣方式為隨機抽取表上資料塊上的資料，理論上被抽樣表的每個資料塊被檢索的機率是一樣的
查詢總數的0.01條
SELECT ＊ FROM test_sample TABLESAMPLE SYSTEM(0.01);
  • BERNOULLI抽樣方式
    BERNOULLI抽樣方式隨機抽取表的資料行，並返回指定百分比資料，BERNOULLI抽樣方式基於資料行級別，理論上被抽樣表的每行記錄被檢索的機率是一樣的，因此BERNOULLI抽樣方式抽取的資料相比SYSTEM抽樣方式具有更好的隨機性，但效能上相比SYSTEM抽樣方式低很多
 SELECT ＊ FROM test_sample TABLESAMPLE BERNOULLI (0.01);

2.6 聚合函式

  • string_add: 將輸出的結果集連線成字串 SELECT country, string_agg(city, ', ') FROM city GROUP BY country;
  • array_agg: 返回的型別為陣列 SELECT country, array_agg(city) FROM city GROUP BY country;

相關文章