【PGSQL】数据类型和表操作

最新推荐文章于 2024-05-29 11:12:11 发布
最新推荐文章于 2024-05-29 11:12:11 发布
阅读量549 收藏
点赞数
文章标签： sql mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chaosssock/article/details/132289570
版权

PGSQL学习

PGSQL数据类型

数值类型

类型名称存储尺寸描述范围
smallint2字节小整形-32768至+32767
integer4字节整形-2147483648至+214783647
bigint8字节长整型-9223372036854775808至+9223372036854775807
decimal变长用户指定精度，精确小数点前131072位，到小数点后16383位
numeric变长用户指定精度，精确小数点前131072位，到小数点后16383位
real4字节可变精度，不精确6位十进制精度
double precision8字节可变精度，不精确15位十进制精度
smallserial2字节小范围自增整数1至32767
serial4字节自增整数1至2147483647
bigserial8字节大范围自增整数1至9223372036854775807

字符类型

类型描述
character varying(n), varchar(n)变长字符串，最大1GB
character(n), char(n)定长字符串，不足位空格补齐
text变长字符串，无长度限制

二进制类型

类型存储空间描述
bytea1或4字节加实际长度变成二进制值

时间类型

类型存储空间描述最小值最大值间隔
timestamp[§] [without time zone]8字节不带时区的日期时间4713 BC294276 AD1毫秒
timestamp[§] with time zone8字节带时区的日期时间4713 BC294276 AD1毫秒
date4字节不带时间的日期4713 BC5874897 AD1天
time[§] [without time zone]8字节不带时区的时间00:00:0024:00:001毫秒
time[§] with time zone12字节带时区的时间00:00:00+145924:00:00-14591毫秒
interval[fields] [§]16字节时间间隔-178000000 years178000000 years1毫秒

布尔值类型

类型存储空间描述
boolean1字节布尔值，真或假

可用值：

truefalse
TRUEFALSE
‘t’‘f’
‘true’‘false’
‘y’‘n’
‘yes’‘no’
‘on’‘off’
‘1’‘0’

金额类型

类型名称存储尺寸描述范围
money8 bytescurrency amount-92233720368547758.08至+92233720368547758.07

网络地址类型

类型存储空间描述
cidr7或19字节IPv4/IPv6网络地址
inet7或19字节IPv4/IPv6网络地址、支持位数表示的掩码
macaddr6字节MAC物理地址
macaddr88字节MAC物理地址（EUI-64格式）

几何类型

类型存储空间描述数值表示方法
point16 bytes点(x, y)
line32 bytes无限长的线(A, B, C)或[(x1, y1), (x2, y2)]
lseg32 bytes有限长线段((x1, y1), (x2, y2))
box32 bytes矩形((x1, y1), (x2, y2))
path16+16n bytes封闭路径((x1, y1), …)
path16+16n bytes开放路径((x1, y1), …)
polygon40+16n bytes多边形((x1, y1), …)
circle24 bytes圆<(x, y), r>（圆心位置和半径）

文档类型

类型描述
XMLXML文档
json原文本存储的JSON文档
jsonb经过分解的二进制方式存储的JSON文档

数组类型

• PostgreSQL允许在各种内置的或者用户自定义的数据类型基础上创建数组类型

• 数组类型的定义是通过在数组元素类型后面添加中括号“[ ]”来实现的

• 多维数组就是使用多对中括号“[ ]”，但实际上是否为多维是取决于数据而不取决于有多少对[]，所以[]与[][]的效果是一样的

范围类型

类型描述
int4range整形范围
int8range长整型范围
numrange数值范围
tsrange时间戳范围
tstzrange带时区时间戳范围
daterange日期范围

PGSQL基本语法

表操作

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL ,  
NAME TEXT NOT NULL,  
AGE INT NOT NULL,   
ADDRESS CHAR(50),   
SALARY REAL,   
LOCATION point
);

# 删除表
drop table if exists company;

drop table if exists weather;

CREATE TABLE cities (
    city_id          varchar(10),
    name            varchar(80),
    location        point
);

INSERT INTO cities VALUES ('xa','西安', '(-194.0, 53.0)');

UPDATE cities SET NAME='西安市', LOCATION='(-196.12,63.22)' WHERE CITY_ID ='xa';

DELTE FROM cities WHERE CITY_ID =’xa’;

模式(SCHEMA)

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

CREATE SCHEMA myschema.mytable (
...
);

# 创建一个模式,并模式下创建一个表格
create schema myschema;
create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

# 查看表格
select * from myschema.company;

# 删除一个为空的模式（其中的所有对象已经被删除）
drop schema myschema;

# 删除一个模式以及其中包含的所有对象（cascade 层叠/大量）
drop schema myschema cascade;

CRUD

# 插入语句 JOIN_DATE 字段使用 DEFAULT 子句来设置默认值
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

# 插入多行数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

# 查询
select * from company;
SELECT (17 + 6) AS ADDITION；
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
SELECT CURRENT_TIMESTAMP;

# 子查询
SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

# 更新
UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

# 删除
DELETE FROM COMPANY WHERE ID = 2;

聚合操作

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

# like  将整型数据类型转化为字符串数据类型,再模糊搜索
SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

# limit 从第三位开始提取3个记录
SELECT * FROM COMPANY LIMIT 4;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

# GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

# HAVING 子句可以让我们筛选分组后的各组数据。
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

# DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
SELECT DISTINCT name FROM COMPANY;

WITH子句

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用；

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表；

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它；WITH 子句在使用前必须先定义；

语法：name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

# 1导入数据
DROP TABLE COMPANY;
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

# 2使用 WITH 子句在上表中查询数据：
With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

# 3使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和
WITH RECURSIVE t(salary) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(salary) FROM t;

# 4我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 
#表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 
#表数据转移到 COMPANY1 表中

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);


WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      salary >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

union, except, intersect

并集： union会移除所有重复的行，要保留重复的行，需要使用 union all
     select name ,age, address,salary from COMPANY  where (salary > 10000)
union
      select name ,age, address,salary from COMPANY1  where (salary > 10000)

差集：except返回在第一张表出现，但在第二张表不存在的记录，两张表查询有先后顺序之别
     select name ,age, address,salary from COMPANY  where (salary > 10000)
except
      select name ,age, address,salary from COMPANY  where (age > 30)

交集：intersect返回既,又两种条件
     select name ,age, address,salary from COMPANY  where (salary > 10000)
intersect
      select name ,age, address,salary from COMPANY  where (age > 20)

级联查询

内连接：inner Join

select co1.* from company co1 inner join  company1 co2 on  co1.id =  co2.id;

select co1.* from  company co1, company1 co2 where  co1.id =  co2.id;


外连接： left join , right join 
   
左连接：
          select co1.* from company co1 left join  company1 co2 on  co1.id =  co2.id;
          
          select co1.* from  company co1, company1 co2 where  co1.id =  co2.id(+);

右连接：
          select co2.* from company co1 right join  company1 co2 on  co1.id =  co2.id;
          
          select co2.* from  company co1, company1 co2 where  co1.id(+) =  co2.id;

PGSQL查看执行计划

1.使用explain命令查看执行计划;

2.客户端点击查看执行计划；

执行计划是从下往上读的

explain报告查询的操作，开启的消耗，查询总的消耗，访问的行数 访问的平均宽度

开启时间消耗是输出开始前的时间例如排序的时间

消耗包括磁盘检索页，cpu时间

注意，每一步的cost包括上一步的，重要的是，explain 不一定是真正的执行一次查询 只是得到查询执行的计划和估计的花费

chaosssock
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
PostgreSql常用数据类型（数字、序列、字符、时间、布尔、枚举、网络地址、json）
zhangyongze_z的博客
03-04 1万+
1、数字类型 CREATE table tm_z( n1 SMALLINT , n2 INT , n3 BIGINT , n4 DECIMAL(8,2), n5 numeric(8,2), n6 real, n7 double PRECISION, n8 SMALLserial, n9 serial, n10 bigserial); 整数型与浮点型 类型numeric可以存储非常多位的数字。建议将它用于货币金额和其它要求计算准确的数量。numeric值的计算会得到准确的结果，
生成pgsql表结构程序
06-04
首先，我们要理解表结构是数据库设计的基础，它定义了数据库中的各个表、字段、数据类型以及它们之间的关系。在PGSql中，可以使用SQL命令如`\d`或`\d+`来查看表结构，但这并不方便在团队间共享或长期保存。因此，...
PostgreSQL教程：数值类型（整型、浮点型、序列、数值的常见操作）
学亮编程手记
09-16 1283
告诉缓存，插入的数据比较多，可以指定告诉缓存，一次性计算出20个后续的值，nextval时，就不可以不去计算，直接去高速缓存拿值，效率会有一内内的提升。数值操作也提供了一些函数，比如pi()，round(数值，位数)，floor()，ceil()PGSQL提供了序列的数据类型，可以在声明表结构时，直接指定序列的类型即可。如果是单独构建序列，再构建表，使用传统方式实现，序列和表就是相对独立的。在drop表之后，序列不会被删除，但是序列会变为不可用的状态。序列大多数的应用，是用作表的主键自增效果。
PostgreSQL 数据类型
最新发布
liu289747235的专栏
05-29 945
在对 inet 或 cidr 数据类型进行排序的时候， IPv4 地址总是排在 IPv6 地址前面，包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址， 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。注意最后的格式是 空，代表着一个空的范围（一个不含有值的范围）。
PostgreSQL数据类型
weixin_33897722的博客
08-31 373
名字别名描述bigintint8有符号 8 字节整数bigserialserial8自增 8 字节整数bit [ (n) ] 定长位串bit varying [ (n) ]varbit变长位串booleanbool逻辑布尔值(真/假)box 平面中的矩形bytea 二进制数据("字节数组")character varying [ (n) ]varchar [ (n) ]变长字...
PostgreSQL 常用数据类型介绍
ningyuanhuo的专栏
06-08 3226
一、数值类型：    下面是PostgreSQL所支持的数值类型的列表和简单说明：名字存储空间描述范围smallint2 字节小范围整数-32768 到 +32767integer4 字节常用的整数-2147483648 到 +2147483647bigint8 字节大范围的整数-9223372036854775808 到 9223372036854775807decimal变长用户声明精度，精确...
pgsql数据类型：数字类型
热门推荐
zhouzhenyong的专栏
08-18 1万+
smallint 2字节 小范围整数 -32768 to +32767 integer 4字节 整数的典型选择 -2147483648 to +2147483647 bigint 8字节 大范围整数 -9223372036854775808 to +9223372036854775807 decimal 可变 用户指定精度，精确 最高小数点前131072位，以及小数点后16383位 numeric 可变 用户指定精度，精确 最高小数点前131072位，以及小数点后16383位 ...
pgsql12和pgsql9.6（包含对应版本的pggis)
04-28
它支持SQL标准，并提供了一套全面的数据类型和高级查询功能。 “pggis”是PostGIS的缩写，它是一个空间数据库扩展，为PostgreSQL增加了对地理数据的支持。PostGIS使得PostgreSQL能够处理地理对象，如点、线、多边形...
MySQL与Oracle数据类型对应关系(表格形式)
09-09
在进行MySQL到Oracle或Oracle到MySQL的数据迁移时，了解这些数据类型的对应关系是非常重要的，它能帮助我们正确地转换数据，确保数据的一致性和完整性。在实际操作中，可能还需要考虑字符集、时间格式以及其他数据库...
介绍PostgreSQL中的jsonb数据类型
09-10
在PostgreSQL数据库系统中，`jsonb`是一种特殊的数据类型，自9.4版本开始引入，...此外，`jsonb`与`json`使用相同的操作符和函数，使得从`json`向`jsonb`的迁移更加平滑，开发者可以根据具体需求选择合适的数据类型。
pgdecimal:PostgreSQLDecimal64和decimal128
05-07
pg十进制 PostgreSQL用于decimal64和decimal128。 Decimal64将具有16个精度数字，而decimal128将具有34。34很大。 这项工作的灵感来自于Pavel Stehule的pgDecimal软件包。 有关原始作者的文档，请参见README.pavel。 它经历了重大变化。 我们实现了decimal64和decimal128。 后端是的decNumber库。 有关构建说明，请参见README.decnumber。 我们没有使用Decimal32 / 64作为原始的pgDecimal，因为我们要访问decDouble / Quad中的某些API。 此外，由于只有7个精度数字，因此十进制32未实现，因此太小而无用。 （真正的原因是decSingle没有实现算术运算:-) 像INT64一样存储decimal64，而像间隔类型那样存储decimal12
postgis-shp2pgsql
11-19
如果需要，可以自定义表的字段名和数据类型。 4. **数据导入**：工具会将Shapefile的数据转化为SQL INSERT语句，通过管道（|）传递给psql命令，直接将数据插入到PostgreSQL数据库中。 5. **验证与查询**：导入完成...
PgSQL修改数据类型
qq_48777186的博客
01-25 2686
PgSQL修改数据类型 alter table 表名 alter column 列名 type 类型 using 列名::类型 ALTER TABLE zw_lxfyhz ALTER COLUMN hzny type timestamp(6) USING hzny::timestamp(6) alter table dwr_cm.dwr_cm_shearer_info_history alter column event_time_utc type VARCHAR(255) using
PostgresSQL数据类型
weixin_44352020的博客
06-01 416
数据类型： 1.数值数据类型 2.字符串数据类型 3.日期/时间数据类型 数值数据类型 smallint 存储整数，小范围 2字节 -32768 至 +32767 integer 存储整数。使用这个类型可存储典型的整数 4字节 -2147483648 至 +2147483647 bigint 存储整数，大范围。 8字节 -9223372036854775808 至 9223372036854775807 decimal 用户指定的精度，精确 变量 小数点前最多为131072个数字; 小数点后最多为16383
postgresql 数据类型
snowyar的专栏
10-24 880
smallint、integer（或者int）、bigint。对应的扩展是int2、int4、int8 定点数 numeric类型，这个用法如下，该类型是用在对于精确描述的数字上面，比如货币金额说明： precision：精度，就是小数点的左右共有多少个数字 scale：刻度，就是小数点的右边有多少个数字 比如： number(3,2)：表示的就是2.12 number(3)：表示的就可以是整数：123 number：表示的就不限制了：1233，432， 2212876注意： 1.虽然该类型功能看着
postgresql自定义类型并返回数组
weixin_30311605的博客
11-08 329
转自https://blog.csdn.net/victor_ww/article/details/44415895 create type custom_data_type as ( id int, name varchar(50), score decimal(5,2), create_time timestamp ); create or rep...
【PostgreSQL】从零开始:（十六）数据类型-数值类型
圈小圈DBA的博客
12-20 1466
数值类型是一种用于存储数字的数据类型。在编程语言中，数值类型通常包括整数类型和浮点数类型。整数类型用于存储整数值，包括正整数、负整数和零。在不同的编程语言中，整数类型可能有不同的大小限制，例如8位、16位、32位或64位。浮点数类型用于存储带有小数部分的数字，也称为实数。浮点数类型可以表示很大或很小的数字，并具有一定的精度。浮点数类型通常分为单精度和双精度，分别表示32位和64位的浮点数。数值类型可以进行各种数学运算，如加法、减法、乘法和除法。
PostgreSQL内核开发过程介绍（三）-用户自定义基本类型
数据库耕耘者
06-12 899
postgresql
pgsql 修改字段数据类型
09-14
要修改PostgreSQL中的字段数据类型，可以使用ALTER TABLE语句。具体的语法如下： ALTER TABLE 表名 ALTER COLUMN 字段名 TYPE 新的数据类型; 其中，表名是要进行修改的表的名称，字段名是要修改数据类型的字段名称，新的数据类型是你要将该字段修改为的数据类型。 举个例子，如果你要将表public.tableone中的字段id_name的数据类型修改为character varying(20)，可以使用以下语句： ALTER TABLE public.tableone ALTER COLUMN id_name TYPE character varying(20); 这样就可以将id_name字段的数据类型修改为character varying(20)了。 你可以参考《PostgreSQL ALTER语句常用操作小结》了解更多关于ALTER语句的常见操作。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值