PostgreSQL - 基础入门

浅浅的花香味﹌ 2023-10-13 17:26 53阅读 0赞

1.1 什么是PostgreSQL

PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。 用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。

PostgreSQL(也称为Post-gress-Q-L)由PostgreSQL全球开发集团(全球志愿者团队)开发。 它不受任何公司或其他私人实体控制。 它是开源的，其源代码是免费提供的。

PostgreSQL是跨平台的，可以在许多操作系统上运行，如Linux，FreeBSD，OS X，Solaris和Microsoft Windows等。

1.2 PostgreSQL 的特点

*  PostgreSQL可在所有主要操作系统(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和Windows等)上运行。
 *  PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口。
 *  PostgreSQL支持SQL的许多功能，例如复杂SQL查询，SQL子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))。
 *  在PostgreSQL中，表可以设置为从“父”表继承其特征。
 *  可以安装多个扩展以向PostgreSQL添加附加功能。

2. PostgreSQL 工具

2.1 psql ：

它是一个命令行工具，也是管理PostgreSQL的主要工具。 pgAdmin是PostgreSQL的免费开源图形用户界面管理工具。

2.2 phpPgAdmin：

它是用PHP编写的PostgreSQL的基于Web的管理工具。 它基于phpMyAdmin工具管理MySQL功能来开发。它可以用作PostgreSQL的前端工具。

2.3 pgFouine

它是一个日志分析器，可以从PostgreSQL日志文件创建报告

3. PostgreSQL数据类型

PotgreSQL中主要有三种类型的数据类型。 此外，用户还可以使用CREATE TYPE SQL命令创建自己的自定义数据类型。

以下是PostgreSQL中主要有三种类型的数据类型：

*  数值数据类型
 *  字符串数据类型
 *  日期/时间数据类型

3.1 数值数据类型

用于指定表中的数组数据

<table> 
 <tbody> 
 <tr> 
 <td> 名称 </td> 
 <td> 描述 </td> 
 <td> 存储大小 </td> 
 <td> 范围 </td> 
 </tr> 
 <tr> 
 <td> smallint </td> 
 <td> 存储整数，小范围 </td> 
 <td> 2字节 </td> 
 <td> -32768 至 +32767 </td> 
 </tr> 
 <tr> 
 <td> integer </td> 
 <td> 存储整数。使用这个类型可存储典型的整数 </td> 
 <td> 4字节 </td> 
 <td> -2147483648 至 +2147483647 </td> 
 </tr> 
 <tr> 
 <td> bigint </td> 
 <td> 存储整数，大范围。 </td> 
 <td> 8字节 </td> 
 <td> -9223372036854775808 至 9223372036854775807 </td> 
 </tr> 
 <tr> 
 <td> decimal </td> 
 <td> 用户指定的精度，精确 </td> 
 <td> 变量 </td> 
 <td> 小数点前最多为131072个数字; 小数点后最多为16383个数字。 </td> 
 </tr> 
 <tr> 
 <td> numeric </td> 
 <td> 用户指定的精度，精确 </td> 
 <td> 变量 </td> 
 <td> 小数点前最多为131072个数字; 小数点后最多为16383个数字。 </td> 
 </tr> 
 <tr> 
 <td> real </td> 
 <td> 可变精度，不精确 </td> 
 <td> 4字节 </td> 
 <td> 6位数字精度 </td> 
 </tr> 
 <tr> 
 <td> double </td> 
 <td> 可变精度，不精确 </td> 
 <td> 8字节 </td> 
 <td> 15位数字精度 </td> 
 </tr> 
 <tr> 
 <td> serial </td> 
 <td> 自动递增整数 </td> 
 <td> 4字节 </td> 
 <td> 1 至 2147483647 </td> 
 </tr> 
 <tr> 
 <td> bigserial </td> 
 <td> 大的自动递增整数 </td> 
 <td> 8字节 </td> 
 <td> 1 至 9223372036854775807 </td> 
 </tr> 
 </tbody> 
</table>

3.2 字符串数据类型

String数据类型用于表示字符串类型值

<table> 
 <tbody> 
 <tr> 
 <td> 数据类型 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> char(size) </td> 
 <td> 这里size是要存储的字符数。固定长度字符串，右边的空格填充到相等大小的字符。 </td> 
 </tr> 
 <tr> 
 <td> character(size) </td> 
 <td> 这里size是要存储的字符数。 固定长度字符串。 右边的空格填充到相等大小的字符。 </td> 
 </tr> 
 <tr> 
 <td> varchar(size) </td> 
 <td> 这里size是要存储的字符数。 可变长度字符串。 </td> 
 </tr> 
 <tr> 
 <td> character varying(size) </td> 
 <td> 这里size是要存储的字符数。 可变长度字符串。 </td> 
 </tr> 
 <tr> 
 <td> text </td> 
 <td> 可变长度字符串。 </td> 
 </tr> 
 </tbody> 
</table>

3.3 日期/时间数据类型

日期/时间数据类型用于表示使用日期和时间值的列。

<table> 
 <tbody> 
 <tr> 
 <td> 名称 </td> 
 <td> 描述 </td> 
 <td> 存储大小 </td> 
 <td> 最小值 </td> 
 <td> 最大值 </td> 
 <td> 解析度 </td> 
 </tr> 
 <tr> 
 <td> timestamp [ (p) ] [不带时区 ] </td> 
 <td> 日期和时间(无时区) </td> 
 <td> 8字节 </td> 
 <td> 4713 bc </td> 
 <td> 294276 ad </td> 
 <td> 1微秒/14位数 </td> 
 </tr> 
 <tr> 
 <td> timestamp [ (p) ]带时区 </td> 
 <td> 包括日期和时间，带时区 </td> 
 <td> 8字节 </td> 
 <td> 4713 bc </td> 
 <td> 294276 ad </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> date </td> 
 <td> 日期(没有时间) </td> 
 <td> 4字节 </td> 
 <td> 4713 bc </td> 
 <td> 5874897 ad </td> 
 <td> 1微秒/14位数 </td> 
 </tr> 
 <tr> 
 <td> time [ (p) ] [ 不带时区 ] </td> 
 <td> 时间(无日期) </td> 
 <td> 8字节 </td> 
 <td> 00:00:00 </td> 
 <td> 24:00:00 </td> 
 <td> 1微秒/14位数 </td> 
 </tr> 
 <tr> 
 <td> time [ (p) ] 带时区 </td> 
 <td> 仅限时间，带时区 </td> 
 <td> 12字节 </td> 
 <td> 00:00:00+1459 </td> 
 <td> 24:00:00-1459 </td> 
 <td> 1微秒/14位数 </td> 
 </tr> 
 <tr> 
 <td> interval [ fields ] [ (p) ] </td> 
 <td> 时间间隔 </td> 
 <td> 12字节 </td> 
 <td> -178000000年 </td> 
 <td> 178000000年 </td> 
 <td> 1微秒/14位数 </td> 
 </tr> 
 </tbody> 
</table>

布尔类型：

<table> 
 <tbody> 
 <tr> 
 <td> 名称 </td> 
 <td> 描述 </td> 
 <td> 存储大小 </td> 
 </tr> 
 <tr> 
 <td> boolean </td> 
 <td> 它指定true或false的状态。 </td> 
 <td> 1字节 </td> 
 </tr> 
 </tbody> 
</table>

4. 基础sql语句

创建数据库：

CREATE DATABASE database\_name;

删除数据库：

drop database testdb;

创建表：

CREATE TABLE table\_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );

删除表：

drop table student2;

创建模式（架构）:

模式(也叫架构)是指定的表集合。 它还可以包含视图，索引，序列，数据类型，运算符和函数。

CREATE SCHEMA schema\_name;

插入数据：

INSERT INTO TABLE\_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

数据查询：

SELECT "column1", "column2".."column" FROM "table\_name";

更新数据：

UPDATE table\_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE \[condition\];

删除数据：

DELETE FROM table\_name WHERE \[condition\];

Order by语句：

SELECT column-list FROM table\_name \[WHERE condition\] \[ORDER BY column1, column2, .. columnN\] \[ASC | DESC\];

Group By分组：

SELECT column-list FROM table\_name WHERE \[conditions \] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN

Having语句：

SELECT column1, column2 FROM table1, table2 WHERE \[ conditions \] GROUP BY column1, column2 HAVING \[ conditions \] ORDER BY column1, column2

PostgreSQL条件用于从数据库获取更具体的结果。 它们通常与WHERE子句一起使用。 具有子句的条件就像双层过滤器。

以下是PostgreSQL条件的列表：

*  AND 条件
 *  OR 条件
 *  AND & OR 条件
 *  NOT 条件
 *  LIKE 条件
 *  IN 条件
 *  NOT IN 条件
 *  BETWEEN 条件

AND条件：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] AND \[search\_condition\];

OR条件：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] OR \[search\_condition\];

AND和OR合用：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] AND \[search\_condition\] OR \[search\_condition\];

NOT条件：

NOT条件与WHERE子句一起使用以否定查询中的条件

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] NOT \[condition\];

Like条件：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] LIKE \[condition\];

IN条件：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] IN \[condition\];

NOT IN条件：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] NOT IN \[condition\];

Between条件：

SELECT column1, column2, ..... columnN FROM table\_name WHERE \[search\_condition\] BETWEEN \[condition\];

在PostgreSQL中，有以下类型的连接：

*  内连接(INNER JOIN)
 *  左外连接(LEFT OUTER JOIN)
 *  右外连接(RIGHT OUTER JOIN)
 *  全连接(FULL OUTER JOIN)
 *  跨连接(CROSS JOIN)

内连接

PostgreSQL内部连接也被称为连接或简单连接。 这是最常见的连接类型。 此连接返回满足连接条件的多个表中的所有行。

如下图表示 -

![8cc83a202e8346f8b15226de196e4415.png][]

![0][]

SELECT table1.columns, table2.columns FROM table1 INNER JOIN table2 ON table1.common\_filed = table2.common\_field;

左外连接

左外连接返回从“ON”条件中指定的左侧表中的所有行，只返回满足条件的另一个表中的行。

如下图中所表示：

![fde9bc9ceae34f8c9f8dabb62797dbb0.png][]

![0][0 1]

SELECT table1.columns, table2.columns FROM table1 LEFT OUTER JOIN table2 ON table1.common\_filed = table2.common\_field;

右外连接

右外连接返回从“ON”条件中指定的右侧表中的所有行，只返回满足条件的另一个表中的行。

如下图中所表示：

如下图所示(蓝色部分) -

![45ece3c6a3554dce8d66a80908ba5ba8.png][]

![0][0 2]

SELECT table1.columns, table2.columns FROM table1 RIGHT OUTER JOIN table2 ON table1.common\_filed = table2.common\_field;

全外连接

全外连接从左表和左表中返回所有行。 它将NULL置于不满足连接条件的位置。

如下图所示(蓝色部分) -

![0019d068b36540da9702e0da0b9a5763.png][]

![0][0 3]

SELECT table1.columns, table2.columns FROM table1 FULL OUTER JOIN table2 ON table1.common\_filed = table2.common\_field;

跨连接

ostgreSQL跨连接(CROSS JOIN)将第一个表的每一行与第二个表的每一行相匹配。 它也被称为笛卡尔积。 如果table1具有“x”行，而table2具有“y”行，则所得到的表将具有(x \* y)行。

SELECT coloums FROM table1 CROSS JOIN table2

[8cc83a202e8346f8b15226de196e4415.png]: https://img-blog.csdnimg.cn/8cc83a202e8346f8b15226de196e4415.png
[0]: https://note.youdao.com/yws/res/4204/3254042D87DB4C1BB77DE7C011C9427B
[fde9bc9ceae34f8c9f8dabb62797dbb0.png]: https://img-blog.csdnimg.cn/fde9bc9ceae34f8c9f8dabb62797dbb0.png
[0 1]: https://note.youdao.com/yws/res/4201/E9375103CFE2413E994C886128EE46C7
[45ece3c6a3554dce8d66a80908ba5ba8.png]: https://img-blog.csdnimg.cn/45ece3c6a3554dce8d66a80908ba5ba8.png
[0 2]: https://note.youdao.com/yws/res/4202/D3C833A5E7884BC4A21D3EBA89928745
[0019d068b36540da9702e0da0b9a5763.png]: https://img-blog.csdnimg.cn/0019d068b36540da9702e0da0b9a5763.png
[0 3]: https://note.youdao.com/yws/res/4203/8A1928DB42594D8DA107FA5EA7BF0C05