目录
- 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}与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.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使用学习笔记(命令行及图形化界面)