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;