目录

  • PostgreSQL介绍与安装
    • docker安装
    • 腾讯云免费领用1个月
  • 数据库操作
    • 连接数据库实例
    • 创建数据库
    • 查看数据库列表
    • 使用数据库
    • 删除数据库
    • 修改数据库属性
  • 表操作
    • 字段类型
      • 整数
      • 浮点数
      • 日期与时间类型
      • 字符串类型
    • DDL
      • CREATE
      • DROP
      • ALTER
    • DML
      • INSERT
      • UPDATE
      • DELETE
  • 查询
    • 查询所有数据
    • 查询部分列
    • 指定条件查询
    • 查询结果排序
    • 分页查询
    • 多表查询
    • 子查询
      • exists
      • in
      • UNION查询
  • 高级特性
    • 视图
    • 外键
    • 插件
    • 函数
      • 聚合函数
  • 参考

PostgreSQL介绍与安装

docker安装

下载镜像:

docker pull postgre

启动容器:

docker run -d --name postgres --restart always -e POSTGRES_PASSWORD='admin' -e POSTGRES_USER='admin' -e ALLOW_IP_RANGE=0.0.0.0/0 -v /data/postgres/data:/var/lib/postgresql -p 5432:5432 -d postgres

腾讯云免费领用1个月

学习中心-免费领取一个月

博主这里选择了白嫖~~~

数据库操作

连接数据库实例

windows下使用pgAdmin
linux下使用psql

psql -U 用户名 -h 访问地址 -p 端口 -d postgres

腾讯云提供了网页版的连接方式,博主直接使用这个,免得安装其他的了。

创建数据库

语法:

create database database_name;

示例:

create database lady_killer9;

查看数据库列表

使用数据库

使用数据库语法

psql 数据库名

例如:

psql lady_killer9 

腾讯云上,点一下就可以了。

退出当前数据库

\q

删除数据库

语法:

drop database database_name;

示例:

drop database lady_killer9;

修改数据库属性

语法:

alter database database_name xxx;

示例:

表操作

字段类型

整数

  • SMALLINT
  • INT(INTEGER)

浮点数

  • REAL: 6位10进制数字精度
  • NUMERIC(m,n):任意精度类型,m代表长度(含精度部分),n代表精度,四舍五入
    例如,NUMERIC(3,2) 可以保存 3.11,但是100.00就会报错,3.126可以,但会保存位3.13

日期与时间类型

  • TIME: 时间,例如:16:24:05
  • DATE:日期,例如:2023-12-02
  • TIMESTAMP:日期和时间,例如:2023-12-02 16:24:05

字符串类型

  • CHAR(n):固定长度字符串,不足补空白
  • VARCHAAR:变长字符串,有长度限制
  • TEXT:可以认为无长度限制

DDL

CREATE

CREATE TABLE weather (cityvarchar(80),temp_lo int, -- low temperaturetemp_hi int, -- high temperatureprcpreal,-- precipitationdatedate);

DROP

语法:

DROP TABLE tablename;

示例:

DROP TABLE weather;

ALTER

ALTER TABLE weather RENAME to weather2;

DML

INSERT

插入数据

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');


通过逗号分隔values后面的数据元组,可以批量插入。除此之外,可以从已有表进行

INSERT INTO weather2 SELECT * from weather;


也可以使用COPY命令进行本地文件读取,然后写入表格。

COPY weather FROM '/home/user/weather.txt';

当然,这里也引入了安全问题。

UPDATE

UPDATE weather SET temp_lo = 10 WHERE temp_hi = 50;

DELETE

DELETE FROM weather WHERE city = 'Hayward';

查询

查询所有数据

SELECT * FROM weather;

查询部分列

SELECT temp_lo,temp_hi FROM weather;

指定条件查询

SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;

查询结果排序

SELECT * FROM weather order by temp_lo;

分页查询

与 M Y S Q L 的 L I M I T m , n 语法不同, p o s t g r e s q l 使用的是 L I M I T 与 O F F S E T \color{red}{与MYSQL 的 LIMIT m,n语法不同,postgresql使用的是LIMIT与OFFSET}MYSQLLIMITm,n语法不同,postgresql使用的是LIMITOFFSET
,limit后面的数字是返回的数量,offset后面的是下标。

SELECT * FROM weather order by temp_lo LIMIT 1 OFFSET 1;


每页显示数量count,和页码(从1开始)的关系如下:

limit count offset ((page_num-1)*count)

多表查询

SELECT * FROM weather JOIN cities ON city = name;


注意,列名有重复时,可以使用表的别名来区分。

SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.locationFROM weather JOIN cities ON weather.city = cities.name;

同样,postgresql也有外连接,左外连接

SELECT * FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

子查询

exists

子查询结果为真,返回主查询结果,否则不返回。

SELECT * FROM weather WHERE EXISTS (SELECT city FROM cities WHERE NAME = 'lady_killer9');

SELECT * FROM weather WHERE EXISTS (SELECT city FROM cities WHERE NAME = 'San Francisco');

SELECT * FROM weather WHERE EXISTS (SELECT city FROM cities WHERE NAME = 'San Francisco' and city = name);

in

子查询结果作为结果集,主查询条件的字段从里面查找。

SELECT * FROM weather WHERE city in (SELECT NAME FROM cities WHERE NAME = 'San Francisco');

UNION查询

union查询用于合并查询结果,会去除重复的。

SELECT * FROM weather UNION SELECT * FROM weather WHERE city = 'San Francisco';


不想去除时,在UNION后添加ALL即可。

SELECT * FROM weather UNION ALL SELECT * FROM weather WHERE city = 'San Francisco';

高级特性

视图

如果你对一些数据感兴趣,又不想每次都写查询语句,可以建立一个视图,这个视图让你像普通表一样使用。

CREATE VIEW myview ASSELECT name, temp_lo, temp_hi, prcp, date, locationFROM weather, citiesWHERE city = name;SELECT * FROM myview;

外键

用于保护数据完整性,防止插入脏数据。

CREATE TABLE cities2 (name varchar(80) primary key,location point);CREATE TABLE weather2 (cityvarchar(80) references cities2(name),temp_lo int,temp_hi int,prcpreal,datedate);

插件

函数

聚合函数

max
查询最大值,min是最小值,不再展示。

SELECT max(temp_lo) FROM weather;


注意:max不能直接放到where中。
count
用于统计数量。

SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city;


sum
用于求和
avg
用于求平均值

SELECT sum(temp_lo),avg(temp_lo) FROM weather;


有聚合函数时,可以通过HAVING进行过滤

SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

参考

postgresql官网
postgresql教程
腾讯云-postgresql
数据库-Mysql使用学习笔记(命令行及图形化界面)