用群晖做网站,wordpress 快速安装,响应式网站 app,本地佛山顺德网站建设为什么选择PostgreSQL
功能更全面#xff1a;PGSQL的功能更加全面#xff0c;支持开窗函数、物化视图、分区表、json等类型#xff0c;MySQL8以上支持开窗函数、分区表、json等#xff0c;但物化视图仍不支持。高可用#xff1a;PG更适合分布式环境#xff0c;如流复制、…为什么选择PostgreSQL
功能更全面PGSQL的功能更加全面支持开窗函数、物化视图、分区表、json等类型MySQL8以上支持开窗函数、分区表、json等但物化视图仍不支持。高可用PG更适合分布式环境如流复制、逻辑复制和基于 WAL 日志的复制机制而MySQL则基于BingLog。数据类型更丰富支持多种索引类型如数组、jsonip类型text类型。
PostgreSQL和MySQL对比
MySQLPgSQL功能8以下版本功能弱8以上版本支持JSON、开窗函数、分区表目前不支持物化视图支持的功能更多例如开窗函数、物化视图、JSON、分区表等性能对于读操作性能更好在处理复杂查询和写密集操作时更有优势可扩展性可扩展性不强可扩展性强支持自定义列类型、插件等
PostgreSQL和MySQL如何选择
PgSQL更适合写操作频繁以及复杂查询的情况因为PgSQL内置了性能更好的MVCC来解决并发写MySQL则是通过锁解决并发锁。如果项目中有一些特殊场景例如json、gis等则可以选择PgSQL。
MySQL更适合读操作多的情况读取性能更好。MySQL运维成本更低上手便捷社区支持、技术方案更多。
PostgreSQL基础
--数据类型
-- 整数 smallint、int
-- 小数 numeric(5,3)
-- 字符串 char 定长、varchar变长、text无限长
-- 日期 date yyyy-MM-dd
-- 时间 time HH:mm:ss
-- 时间戳 timestamp yyyy-MM-dd HH:mm:ss.0000数据类型
数字
类型描述int整数类型4字节大小范围为 -2147483648 到 2147483647int2整数类型2字节大小其范围为 -32768 到 32767int4整数类型4字节大小范围为 -2147483648 到 2147483647int8范围为 -9223372036854775808 到 9223372036854775807serial、serial2、serial4、serial8等于int会自动创建序列实现主键自增。numeric(m,n)小数类型小数超长会四舍五入。numeric(5,2)最大为999,99
字符串
类型描述char定长字符串不足补空格varchar变长字符串text无限长度字符串
日期
类型描述date日期例如‘2023-11-10’time时间例如‘20:00:00.200’timestampdatetime
基本语法
-- 创建数据库
create database test1 encoding utf-8;
-- 修改数据库名称
alter database test1 rename to test;
-- 删除数据库
drop database if exists test1;-- 查看所有表
select * from pg_tables where schemaname public;-- 创建表 双引号表示对象单引号表示字符串。表名为特殊字符需要加“”
create table user2 (id serial8 primary key, -- serial表示主键自增需要注意的时不能手动指定id了不然会冲突name varchar(255) not null default zs,birthday date, -- 日期数据类型 date(日期) time(时间) timestamp(日期加时间)age int,money numeric(5,2) -- 小数类型整数位为3小数位为2。
);
-- 表注释
comment on table user2 is 用户表;
comment on column user2.id is 用户Id;
comment on column user2.name is 名称;
comment on column user2.birthday is 生日;
comment on column user2.age is 年龄;
comment on column user2.money is 存款;-- 修改表名
alter table user rename to user2;
-- 修改字段类型
alter table user2 alter column age type numeric(3,0);
-- 新增字段
alter table user add column test numeric(3,0);
-- 修改字段名称
alter table user rename column test to test2;
-- 删除字段
alter table user drop column test;-- 插入数据
-- 小数超过会四舍五入numeric52表示最大不能超过999.99
-- id自增使用default
insert into user values (DEFAULT,zs,2023-11-09 01:00:00,20.1,999.99);
-- 不要指定id因为他会和自增id冲突
insert into user(id,name,birthday,age,money) values (4,zs,2023-11-09 01:00:00,20.1,999.994);-- upsert 不存在新增存在修改 EXCLUDED是一个临时表用于存放当前要插入的数据。
INSERT INTO user(id,name)
VALUES (default,ls),(id,zs2)
ON CONFLICT(id) -- 当id冲突时
DO UPDATE SET -- 进行修改操作
name user.name || EXCLUDED.name, -- name原name新name ||表示字符串拼接
birthdaynow();-- 分页查询(刚好和mySQL反过来了)
-- 第一页 limit 3 offset 0
-- 第二页 limit 3 offset 3
-- limit pageSize offset pageNum*pageSize -1
select * from employee limit 3 offset 0;特殊函数
-- string_agg 类似于mysql的 group_concat
SELECT country,string_agg(city,,) FROM city GROUP BY country;
-- array_agg 功能和string_agg类似不同的是返回的是数组。
SELECT country,array_agg(city) FROM city GROUP BY country;PostgreSQL扩展
物化视图
物化视图是一张真实存在的表能够随着原表的改变而改变需要手动刷新主要用于提高一些更新不频繁的大表。
-- 创建物化视图
create materialized view user_view as select u.name,count(a.address) from user as u inner join user_address as a on u.id a.user_id group by u.id,u.name;
-- 刷新物化视图
refresh materialized view user_view;
-- 并发刷新(需要创建唯一索引)
create unique index idx_id on user_view(id); -- 为物化视图创建索引
refresh materialized view concurrently user_view; -- 并发刷新-- 删除物化视图
drop materialized view user_view;开窗函数 如果想要获取聚合数据但又不想让数据聚合那么就可以使用聚合函数。 开窗函数的作用对查询出来的数据再次进行分析如聚合、排名、累计等。
例如获取每个部门薪资排名前三的员工。
select dept_name,emp_name,salary,
rank() over(patition by dept_name order by salary desc) -- 开窗函数根据部门分组然后根据薪资排序
from salary_log;开窗函数语法
想要使用的函数() -- 例如 rank()获取排名、sum()、ave()、count()
over(partition by 列 -- 要分组的列(类似于group by)order by 列 -- 排序列rows between xxx and xxx -- 要选取的行
) -- over表示开窗配合函数表示开窗函数一定要加
-- 执行顺序 先执行over然后数据传给前面的函数执行。-- rows between xxx and xxx 详解
-- 使用示例
-- rows between unbounded proceding and current row 从第一行数据开始到当前行。-- unbounded preceding 表示从当前行的前无限行开始(第一行)
-- 1 preceding 表示从当前行的前1行开始
-- current row 表示读取至当前行。
-- 1 following 表示读取至当前行的后一行
-- unbounded following 表示读取至最后一行-- rows between 可以省略不写默认为 rows between unbounded preceding and current row常用开窗函数
row_number() -- 排名分数相同排名不同。
rank() -- 排名分数相同排名相同但末位数不变例如查询前三名 1,1,3
dense_rank() -- 排名分数相同排名相同但末位数减少例如查询前三名 1,1,2sum( column ) -- 求和
count( column ) -- 总数
avg( column ) -- 求平均分lag( column,1,0) -- 求当前行的上1行的值如果没有上1行则返回0 (一般用于求环比)
leag( column,1,0)-- 求当前行的下1行的值如果没有下1行则返回0 (一般用于求环比)row_number dense_rank
PostgreSQL与 SpringBoot、MybatisPlus 整合
引入依赖
dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.5.4.1/version/dependencydependencygroupIdorg.postgresql/groupIdartifactIdpostgresql/artifactIdscoperuntime/scope/dependency
/dependencies配置
spring.datasource.driver-class-nameorg.postgresql.Driver
spring.datasource.usernamepostgres
spring.datasource.passwordroot
spring.datasource.urljdbc:postgresql://localhost:5432/test1?currentSchemapublicmybatis-plus.configuration.map-underscore-to-camel-casetrue
mybatis-plus.mapper-locationsclasspath*:/mapper/**/*.xml
mybatis-plus.configuration.log-implorg.apache.ibatis.logging.stdout.StdOutImpl配置分页插件
Configuration
public class MybatisPlusConfigure {//必须配置分页插件不然分页无效Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor new MybatisPlusInterceptor();//设置分页插件PaginationInnerInterceptor paginationInnerInterceptor new PaginationInnerInterceptor();paginationInnerInterceptor.setOptimizeJoin(true);//指定数据库方言paginationInnerInterceptor.setDbType(DbType.POSTGRE_SQL);paginationInnerInterceptor.setOverflow(true);interceptor.addInnerInterceptor(paginationInnerInterceptor);return interceptor;}
}id自增
Data
NoArgsConstructor
AllArgsConstructor
TableName(employee)
public class Employee {TableId(type IdType.AUTO)private Long id;private String name;private Long deptId;private String month;private BigDecimal salary;private Date sendDate;
}
