PostgreSQL基础入门

为什么选择PostgreSQL

1. 功能更全面：PGSQL的功能更加全面，支持开窗函数、物化视图、分区表、json等类型，MySQL8以上支持开窗函数、分区表、json等，但物化视图仍不支持。
2. 高可用：PG更适合分布式环境，如流复制、逻辑复制和基于 WAL 日志的复制机制，而MySQL则基于BingLog。
3. 数据类型更丰富：支持多种索引类型，如：数组、json，ip类型，text类型。

PostgreSQL和MySQL对比

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
1
2
3
4
5
6
7

数据类型

数字

字符串

日期

基本语法

-- 创建数据库
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;

-- 插入数据
-- 小数超过会四舍五入，numeric（5，2）表示最大不能超过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 （||表示字符串拼接）
birthday=now();



-- 分页查询(刚好和mySQL反过来了)
-- 第一页= limit 3 offset 0 
-- 第二页  limit 3 offset 3
-- limit pageSize offset pageNum*pageSize -1
select * from employee limit 3 offset 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

特殊函数

-- 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;
1
2
3
4

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;
1
2
3
4
5
6
7
8
9
10

开窗函数

如果想要获取聚合数据，但又不想让数据聚合，那么就可以使用聚合函数。

开窗函数的作用：对查询出来的数据再次进行分析，如聚合、排名、累计等。

例如：获取每个部门薪资排名前三的员工。

select dept_name,emp_name,salary, 
rank() over(patition by dept_name order by salary desc) -- 开窗函数，根据部门分组，然后根据薪资排序
from salary_log;
1
2
3

开窗函数语法

想要使用的函数() -- 例如 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

常用开窗函数

row_number() -- 排名，分数相同，排名不同。
rank() -- 排名，分数相同，排名相同，但末位数不变，例如查询前三名： 1,1,3
dense_rank() -- 排名，分数相同，排名相同，但末位数减少，例如查询前三名 1,1,2

sum( "column" ) -- 求和
count( "column" ) -- 总数
avg( "column" ) -- 求平均分

lag( "column",1,0) -- 求当前行的上1行的值，如果没有上1行则返回0 (一般用于求环比)
leag( "column",1,0)-- 求当前行的下1行的值，如果没有下1行则返回0 (一般用于求环比)
1
2
3
4
5
6
7
8
9
10

row_number dense_rank

PostgreSQL与 SpringBoot、MybatisPlus 整合

1. 引入依赖

<dependencies>
    
    <dependency>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-webartifactId>
    dependency>

    <dependency>
        <groupId>org.projectlombokgroupId>
        <artifactId>lombokartifactId>
    dependency>

    <dependency>
        <groupId>com.baomidougroupId>
        <artifactId>mybatis-plus-boot-starterartifactId>
        <version>3.5.4.1version>
    dependency>

    <dependency>
        <groupId>org.postgresqlgroupId>
        <artifactId>postgresqlartifactId>
        <scope>runtimescope>
    dependency>
dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2. 配置

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=root
spring.datasource.url=jdbc:postgresql://localhost:5432/test1?currentSchema=public

mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.mapper-locations=classpath*:/mapper/**/*.xml
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
1
2
3
4
5
6
7
8

3. 配置分页插件

@Configuration
public class MybatisPlusConfigure {
    //必须配置分页插件，不然分页无效
    @Bean
    public 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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4. 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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13