PostgreSQL基础（六）：PostgreSQL基本操作（二）

举报
Lansonli 发表于 2024/10/27 11:31:51 2024/10/27
【摘要】 ​PostgreSQL基本操作（二）一、字符串类型字符串类型用的是最多的一种，在PGSQL里，主要支持三种：character（就是MySQL的char类型），定长字符串。（最大可以存储1G）character varying（varchar），可变长度的字符串。（最大可以存储1G）text（跟MySQL异常）长度特别长的字符串。操作没什么说的，但是字符串常见的函数特别多。字符串的拼接一要要...

​PostgreSQL基本操作（二）

一、字符串类型

字符串类型用的是最多的一种，在PGSQL里，主要支持三种：

  • character（就是MySQL的char类型），定长字符串。（最大可以存储1G）

  • character varying（varchar），可变长度的字符串。（最大可以存储1G）

  • text（跟MySQL异常）长度特别长的字符串。

操作没什么说的，但是字符串常见的函数特别多。

字符串的拼接一要要使用||来拼接。

完整的函数介绍可查看下面链接：

 http://www.postgres.cn/docs/12/functions-string.html


二、日期类型

在PGSQL中，核心的时间类型，就三个。

  • timestamp（时间戳，覆盖 年月日时分秒）

  • date（年月日）

  • time（时分秒）

在PGSQL中，声明时间的方式。

只需要使用字符串正常的编写 yyyy-MM-dd HH:mm:ss 就可以转换为时间类型。

直接在字符串位置使用之前讲到的数据类型转换就可以了。

当前系统时间 ：

  • 可以使用now作为当前系统时间（没有时区的概念）

select timestamp 'now';
-- 直接查询now，没有时区的概念
select time with time zone 'now' at time zone '08:00:00'

  • 也可以使用current_timestamp的方式获取（推荐，默认东八区）

日期类型的运算

  • 正常对date类型做+，-操作，默认单位就是天~

  • date + time = timestamp

select date '2011-11-11' + time '12:12:12' ;
  • 可以针对timestamp使用interval的方式进行 +，-操作，在查询以时间范围为条件的内容时，可以使用
select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';


三、枚举类型

枚举类型MySQL也支持，只是没怎么用，PGSQL同样支持这种数据类型

可以声明枚举类型作为表中的字段类型，这样可以无形的给表字段追加诡异的规范。

-- 声明一个星期的枚举，值自然只有周一~周日。
create type week as enum ('Mon','Tues','Sun');
-- 声明一张表，表中的某个字段的类型是上面声明的枚举。
drop table test;
create table test(
    id bigserial ,
    weekday week
);
insert into test (weekday) values ('Mon');
insert into test (weekday) values ('Fri');

 


四、IP类型

PGSQL支持IP类型的存储，支持IPv4，IPv6这种，甚至Mac内种诡异类型也支持

这种IP类型，可以在存储IP时，帮助做校验，其次也可以针对IP做范围查找。

IP校验的效果

IP也支持范围查找。


五、JSON&JSONB类型

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引。

PGSQL支持JSON类型以及JSONB类型。

JSON和JSONB的使用基本没区别。

撇去JSON类型，本质上JSON格式就是一个字符串，比如MySQL5.7不支持JSON的情况的下，使用text也可以，但是字符串类型无法校验JSON的格式，其次单独的字符串没有办法只获取JSON中某个key对应的value。

JSON和JSONB的区别：

  • JSON类型无法构建索引，JSONB类型可以创建索引。

  • JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。

  • JSON类型甚至可以存储重复的key，以最后一个为准。JSONB不会保留多余的重复key（保留最后一个）。

  • JSON会保留存储时key的顺序，JSONB不会保留原有顺序。

JSON中key对应的value的数据类型

JSON PGSQL
String text
number numeric
boolean boolean
null (none) 
[
  {"name": "张三"},
  {"name": {
      "info": "xxx"
    }}

]

操作JSON：

  • 上述的四种JSON存储的类型：

select '9'::JSON,'null'::JSON,'"laozheng"'::JSON,'true'::json;
select '9'::JSONB,'null'::JSONB,'"laozheng"'::JSONB,'true'::JSONB;
  • JSON数组
select '[9,true,null,"我是字符串"]'::JSON;
  • JSON对象

select '{"name": "张三","age": 23,"birthday": "2011-11-11","gender": null}'::json;
select '{"name": "张三","age": 23,"birthday": "2011-11-11","gender": null}'::jsonb;
  • 构建表存储JSON
create table test(
    id bigserial,
    info json,
    infob jsonb
);
insert into
  test
(info,infob)   
  values 
('{"name":            "张三"              ,"age": 23,"birthday": "2011-11-11","gender": null}',
'{"name":               "张三"             ,"age": 23,"birthday": "2011-11-11","gender": null}')
select * from test;
  • 构建索引的效果

create index json_index on test(info);
create index jsonb_index on test(infob);

 JSON还支持很多函数，可以直接查看以下文档地址：

http://www.postgres.cn/docs/12/functions-json.html


六、复合类型

复合类型就好像Java中的一个对象，Java中有一个User，User和表做了一个映射，User中有个人信息对象。可以基于符合类型对映射上个人信息。

public class User{
   private Integer id;
   private Info info;
}

class Info{
   private String name;
   private Integer age;
}

按照上面的情况，将Info构建成一个复合类型

-- 构建复合类型，映射上Info
create type info_type as (name varchar(32),age int);
-- 构建表，映射User
create table tb_user(
    id serial,
    info info_type
);
-- 添加数据
insert into tb_user (info) values (('张三',23));
insert into tb_user (info) values (('露丝',233));
insert into tb_user (info) values (('jack',33));
insert into tb_user (info) values (('李四',24));
select * from tb_user;


七、数组类型

数组还是要依赖其他类型，比如在设置住址，住址可能有多个住址，可以采用数组类型去修饰字符串。

PGSQL中，指定数组的方式就是[]，可以指定一维数组，也支持二维甚至更多维数组。

构建数组的方式：

drop table test;
create table test(
    id serial,
    col1 int[],
    col2 int[2],
    col3 int[][]
);
-- 构建表指定数组长度后，并不是说数组内容只有2的长度，可以插入更多数据
-- 甚至在你插入数据，如果将二维数组结构的数组扔到一维数组上，也可以存储。
-- 数组编写方式
select '{{how,are},{are,you}}'::varchar[];
select array[[1,2],[3,4]];
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{7,8,9}');
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}',array[[1,2],[3,4]]);
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{{1,2},{3,4}}');
select * from test;

如果现在要存储字符串数组，如果存储的数组中有双引号怎么办，有大括号怎么办。

-- 如果存储的数组中的值，有单引号怎么办？
-- 使用两个单引号，作为一个单引号使用
select '{''how''}'::varchar[];
-- 如果存储的数组中的值，有逗号怎么办？(PGSQL中的数组索引从1开始算，写0也是从1开始算。)
-- 用双引号将数组的数据包起来~
select ('{"how,are"}'::varchar[])[2];
-- 如果存储的数组中的值，有双引号怎么办？
-- 如果要添加双引号，记得转义。
select ('{"\"how\",are"}'::varchar[])[1];

数组的比较方式

-- 包含
select array[1,2] @> array[1];
-- 被包含
select array[1,2] <@ array[1,2,4];
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];

​

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
JSON MySQL PostgreSQL 数据结构
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入