当前位置： 首页 > article >正文

【PGSQL】数据类型和表操作

article 2025/9/11 21:43:16

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
(SelectID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;# 3使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和
WITH RECURSIVE t(salary) AS (VALUES (0)UNION ALLSELECT 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 COMPANYWHEREsalary >= 30000RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

union, except, intersect

并集： union会移除所有重复的行，要保留重复的行，需要使用 union allselect name ,age, address,salary from COMPANY  where (salary > 10000)
unionselect name ,age, address,salary from COMPANY1  where (salary > 10000)差集：except返回在第一张表出现，但在第二张表不存在的记录，两张表查询有先后顺序之别select name ,age, address,salary from COMPANY  where (salary > 10000)
exceptselect name ,age, address,salary from COMPANY  where (age > 30)交集：intersect返回既,又两种条件select name ,age, address,salary from COMPANY  where (salary > 10000)
intersectselect name ,age, address,salary from COMPANY  where (age > 20)

级联查询

内连接：inner Joinselect 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 不一定是真正的执行一次查询 只是得到查询执行的计划和估计的花费

http://www.lryc.cn/news/2415468.html

相关文章：

  • 给你8个接私活的网站，保证你月薪轻松上W
  • c# MessageBox 用法大全
  • 【技术分享】数据之大，云动未来 - 弹性云数据库技术解析
  • Linux 中主机名、域名以及IP地址的关系
  • 虚拟机软件（VMware Workstation）工作模式及网络功能介绍
  • MBR、主分区、扩展分区、逻辑分区、活动分区、系统分区、启动分区讲解
  • 2023 QQ自定义在线源码无需SVIP
  • 前端的UI设计与交互之布局篇
  • ERP项目的二次开发
  • 保姆级Java入门练习教程，附代码讲解，小白零基础入门必备（建议收藏）_java教程
  • MySQL数据库引擎有哪些
  • 【视频教程】MAME0.238配置分享
  • 性能测试-测试方法总结（压力/负载）超详细
  • 音视频之解析flv文件实战
  • 【计算机毕业设计】008房屋租赁系统
  • 中国基准、基本气象站列表2012版
  • 安装Quartus_II_9.0
  • C++ static_cast基本用法
  • 北斗形变监测系统_北斗高精度在桥梁监测上有怎样的实际应用？
  • 如何修改默认浏览器_iPhone 自定义默认浏览器，支持随意修改
  • 视觉里程计 第二部分：匹配、鲁棒、优化和应用
  • 迅雷极速版任务出错的解决办法（亲测可用）
  • 英语词根单词记忆. 包含大量自己对于词根的理解. 方便大家记忆使用.
  • ECShop二次开发指南
  • 博客园如何设置自定义主题？
  • 不再担心DDoS攻击!高防直连VPS的终极保护!
  • 用Python爬虫帮助出版社的实习生批量爬取古籍图片
  • 解析Windows7下的BCD管理工具
  • Git同时push到多个远程仓库
  • Mother‘s Milk