2024年数据库-PostgreSQL学习笔记

\q

### 删除数据库


语法：

drop database database_name;

示例：

drop database lady_killer9;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/55c2bc679f7c4d09a4b493026b5828e9.png)


### 修改数据库属性


语法：

alter database database_name xxx;

示例：  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/f0fefee077a1418b850f385d141f66f5.png)


## 表操作


### 字段类型


#### 整数


* 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
);

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/d0733dc2c6b5470dbe7707d34bd195de.png)


#### DROP


语法：

DROP TABLE tablename;

示例：

DROP TABLE weather;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/13e90434193743a4b55bf1868072f97b.png)


#### ALTER

ALTER TABLE weather RENAME to weather2;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/f733665eaf454331b69bd963b226a5b9.png)


### DML


#### INSERT


插入数据

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

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/76dacd03e810417898bab2cbb21b7132.png)  
 通过逗号分隔values后面的数据元组，可以批量插入。除此之外，可以从已有表进行

INSERT INTO weather2 SELECT * from weather;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/36aca785811444b095febeaea91052dd.png)  
 也可以使用COPY命令进行本地文件读取，然后写入表格。

COPY weather FROM ‘/home/user/weather.txt’;

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


#### UPDATE

UPDATE weather SET temp_lo = 10 WHERE temp_hi = 50;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/661ddb18f658483e9be74963bc437708.png)


#### DELETE

DELETE FROM weather WHERE city = ‘Hayward’;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/336c406db4df44d7a0821a23373e74a1.png)


## 查询


### 查询所有数据

SELECT * FROM weather;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/06cfb7dd8fe64aa59b361f0cc1ecdcd2.png)


### 查询部分列

SELECT temp_lo,temp_hi FROM weather;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/9039b6aa6fd04dbcb9cbf0683820ebb5.png)


### 指定条件查询

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

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/c51a1ebe777648be9e9ab84d421deb96.png)


### 查询结果排序

SELECT * FROM weather order by temp_lo;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/01b8b0bcc38d4184b77f772ed3c813a8.png)


### 分页查询


 
 
 
 
 
 
 
 与 
 
 
 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;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/32b6f3baf2f84250a848c3e41ce0c82f.png)  
 每页显示数量count，和页码（从1开始）的关系如下：

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

### 多表查询

SELECT * FROM weather JOIN cities ON city = name;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/8086f3f2ae664415874f29628026cdd2.png)  
 注意，列名有重复时，可以使用表的别名来区分。

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;

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/bb0abb8c7b194fccbfd5071f7cf39968.png)


### 子查询


#### exists


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

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

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/13d2560641224f2e919756ed71b9bd7d.png)

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

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/c8480aea6cea46b5af03b7587d49236c.png)

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

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/4daf2347cc294d04b463e520920efd45.png)


#### in


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

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以点击这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！