数据库-PostgreSQL学习笔记

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 (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

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;

分页查询

$与MYSQL的LIMITm,n语法不同，postgresql使用的是LIMIT与OFFSET$
，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.location
    FROM 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 AS
    SELECT name, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

外键

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

CREATE TABLE cities2 (
        name     varchar(80) primary key,
        location point
);

CREATE TABLE weather2 (
        city      varchar(80) references cities2(name),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

插件

函数

聚合函数

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使用学习笔记（命令行及图形化界面）