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;