PostgreSQL基础

PostgreSQL基础

1.什么是PostgreSQL？

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

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

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

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添加附加功能。   

3.PostgreSQL工具

1）psql：

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

2）phpPgAdmin

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

3）pgFouine

它是一个日志分析器，可以从PostgreSQL日志文件创建报告。 专有工具有 -
Lightning Admin for PostgreSQL, Borland Kylix, DBOne, DBTools Manager PgManager, Rekall, Data Architect, SyBase Power Designer, Microsoft Access, eRWin, DeZign for Databases, PGExplorer, Case Studio 2, pgEdit, RazorSQL, MicroOLAP Database Designer, Aqua Data Studio, Tuples, EMS Database Management Tools for PostgreSQL, Navicat, SQL Maestro Group products for PostgreSQL, Datanamic DataDiff for PostgreSQL, Datanamic SchemaDiff for PostgreSQL, DB MultiRun PostgreSQL Edition, SQLPro, SQL Image Viewer, SQL Data Sets 等等//原文出自【易百教程】，商业转载请联系作者获得授权，非商业转载请保留原文链接：https://www.yiibai.com/postgresql/postgresql-features.html
 
 

4.PostgreSQL数据类型

数据类型指定要在表字段中存储哪种类型的数据。 在创建表时，对于每列必须使用数据类型。
PotgreSQL中主要有三种类型的数据类型。 此外，用户还可以使用CREATE TYPE SQL命令创建自己的自定义数据类型。

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

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

数字数据类型用于指定表中的数字数据。

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

字符串数据类型

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

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

日期/时间数据类型

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

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

一些其他数据类型

布尔类型：

名称描述存储大小
boolean 它指定true或false的状态。 1字节

货币类型：

名称描述存储大小范围
money 货币金额 8字节 -92233720368547758.08 至 +92233720368547758.07

几何类型：

几何数据类型表示二维空间对象。最根本的类型：点 - 形成所有其他类型的基础。

名称存储大小表示描述
point 16字节 在一个平面上的点 (x,y)
line 32字节 无限线(未完全实现) ((x1,y1),(x2,y2))
lseg 32字节 有限线段 ((x1,y1),(x2,y2))
box 32字节 矩形框 ((x1,y1),(x2,y2))
path 16+16n字节 封闭路径(类似于多边形) ((x1,y1),…)
polygon 40+16n字节 多边形(类似于封闭路径) ((x1,y1),…)
circle 24字节 圆 <(x，y)，r>(中心点和半径)

基础部分

创建数据库：
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内部连接也被称为连接或简单连接。 这是最常见的连接类型。 此连接返回满足连接条件的多个表中的所有行。

如下图表示 -

SELECT table1.columns, table2.columns  
FROM table1  
INNER JOIN table2  
ON table1.common_filed = table2.common_field;

左外连接

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

SELECT table1.columns, table2.columns  
FROM table1  
LEFT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

右外连接

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

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

SELECT table1.columns, table2.columns  
FROM table1  
RIGHT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

全外连接

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

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

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

高级部分

PostgreSQL创建视图

可以使用CREATE VIEW语句来在PostgreSQL中创建视图。 您可以从单个表，多个表以及另一个视图创建它
CREATE [TEMP | TEMPORARY] VIEW view_name AS  
SELECT column1, column2.....  
FROM table_name  
WHERE [condition];

PostgreSQL函数

 
PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。 它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

参数说明

  • function_name：指定函数的名称。
  • [OR REPLACE]：是可选的，它允许您修改/替换现有函数。
  • RETURN：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。
  • function_body：function_body包含可执行部分。
  • plpgsql：它指定实现该函数的语言的名称。

PostgreSQL创建触发器

CREATE TRIGGER语句用于在PostgreSQL表中创建一个新的触发器。 当表发生特定事件(即INSERT，UPDATE和DELETE)时，它被激活。
CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

在这里，event_name可以是INSERT，UPDATE，DELETE和TRUNCATE数据库操作上提到的表table_name。 您可以选择在表名后指定FOR EACH ROW。

下面来看看看如何在INSERT操作中创建触发器的语法。

CREATE  TRIGGER trigger_name AFTER INSERT ON column_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

触发器例子

下面举个例子来演示PostgreSQL在INSERT语句之后创建触发器。在以下示例中，我们对每个记录插入到COMPANY表中进行审核(审计)。

使用以下查询创建一个名为COMPANY的表：

CREATE TABLE COMPANY(  
   ID INT PRIMARY KEY     NOT NULL,  
   NAME           TEXT    NOT NULL,  
   AGE            INT     NOT NULL,  
   ADDRESS        CHAR(50),  
   SALARY         REAL  
);

为了保存审计/审核，我们将创建一个名为AUDIT的新表，只要在COMPANY表中有一个新记录的条目，就会插入日志消息。

使用以下查询语句创建另一个表Audit：

CREATE TABLE AUDIT(  
    EMP_ID INT NOT NULL,  
    ENTRY_DATE TEXT NOT NULL  
);

在COMPANY表上创建触发器之前，首先创建一个名为auditlogfunc()的函数/过程。

执行以下查询语句来创建函数/过程：

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$  
    BEGIN  
        INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);  
        RETURN NEW;   
    END;  
$example_table$ LANGUAGE plpgsql;

PostgreSQL列别名

SELECT column_name AS alias_name  
FROM table_name  
conditions...  ;

参数说明

  • column_name: 它指定要进行别名的列的原始名称。
  • alias_name: 它指定分配给列的临时名称。
  • table_name：它指定表的名称。
  • AS：这是可选的。大多数程序员将在对列名进行别名时指定AS关键字，但在别名表名时不指定。

注意：

  • 如果alias_name包含空格，则必须将alias_name包含在引号中。
  • 在别名列名时，可以使用空格。 但是使用表名时，使用空格通常不是一个好习惯。
  • alias_name仅在SQL语句的范围内有效。

PostgreSQL创建索引

CREATE INDEX index_name ON table_name;

PostgreSQL日期和时间函数

所有重要的日期和时间相关函数如下列表所示：

函数描述
AGE() 减去参数 （获取两个时间点之间的时长）
CURRENT DATE/TIME() 它指定当前日期和时间。
DATE_PART() 获取子字段(相当于提取)
EXTRACT() 获得子字段
ISFINITE() 测试有限的日期，时间和间隔(非+/-无穷大)
JUSTIFY 调整间隔

AGE(timestamp，timestamp)＆AGE(timestamp)：

函数描述
age(timestamp, timestamp) 当使用第二个参数的时间戳形式调用时，age()减去参数，产生使用年数和月份的类型为“interval”的“符号”结果。
age(timestamp) 当仅使用时间戳作为参数调用时，age()从current_date(午夜)减去。

AGE()函数示例：
SELECT AGE(timestamp '2017-01-26', timestamp '1951-08-15');

函数AGE(timestamp)的示例：

（生成当前年龄）

SELECT AGE(timestamp '1990-08-15');

当前DATE/TIME()

以下是返回与当前日期和时间相关的值的函数的列表。

函数描述
CURRENT_DATE 提供当前日期
CURRENT_TIME 提供带时区的值
CURRENT_TIMESTAMP 提供带时区的值
CURRENT_TIME(precision) 可以选择使用precision参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
CURRENT_TIMESTAMP(precision) 可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
LOCALTIME 提供没有时区的值。
LOCALTIMESTAMP 提供没有时区的值。
LOCALTIME(precision) 可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。
LOCALTIMESTAMP(precision) 可以选择使用精度参数，这将使结果在四分之一秒的范围内四舍五入到数位数。

现在，您可以来看看以下命令：

 

#获取当前时间：
SELECT CURRENT_TIME;
#获取当前日期：
SELECT CURRENT_DATE;
#获取当前时间戳(两者的日期和时间)：
SELECT CURRENT_TIMESTAMP;
#当前时间戳更精确：
SELECT CURRENT_TIMESTAMP(2);
#本地时间戳：
SELECT LOCALTIMESTAMP;

 

PostgreSQL UNIONS子句

ostgreSQL UNION子句/运算符用于组合两个或多个SELECT语句的结果，而不返回任何重复的行。

要使用UNION，每个SELECT必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同。

UNION的基本语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

示例表如下：

 SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;
#获取当前时间：
SELECT CURRENT_TIME;
#获取当前日期：
SELECT CURRENT_DATE;
#获取当前时间戳(两者的日期和时间)：
SELECT CURRENT_TIMESTAMP;
#当前时间戳更精确：
SELECT CURRENT_TIMESTAMP(2);
#本地时间戳：
SELECT LOCALTIMESTAMP;

PostgreSQL UNION ALL子句

UNION ALL运算符用于组合两个SELECT语句(包括重复行)的结果。 适用于UNION的相同规则也适用于UNION ALL运算符。
UNION ALL的基本语法如下：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

示例表如下：

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
     SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

PostgreSQL修改表

#新增字段：
ALTER TABLE table_name ADD column_name datatype;
#删除字段
ALTER TABLE table_name DROP COLUMN column_name;
#修改字段类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
#添加NOT NULL约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
#添加唯一约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
#检查约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
SQL
#ALTER TABLE添加主键
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
SQL
#删除约束(DROP CONSTRAINT)的基本语法如下：
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;
SQL
#删除主键约束(DROP PRIMARY KEY)约束的基本语法如下：
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;

PostgreSQL截断表：

PostgreSQL TRUNCATE TABLE命令用于从现有表中删除完整的数据。您也可以使用DROP TABLE命令删除完整的表，但会从数据库中删除完整的表结构，如果希望存储某些数据，则需要重新创建此表。

它和在每个表上使用DELETE语句具有相同的效果，但由于实际上并不扫描表，所以它的速度更快。 此外，它会立即回收磁盘空间，而不需要后续的VACUUM操作。 这在大表上是最有用的。

TRUNCATE TABLE  table_name;

PostgreSQL事务

事务性质

事务具有以下四个标准属性，一般是由首字母缩写词ACID简称：

  • 原子性(Atomicity)：确保工作单位内的所有操作成功完成; 否则事务将在故障点中止，以前的操作回滚到其以前的状态。

  • 一致性(Consistency)：确保数据库在成功提交的事务时正确更改状态。

  • 隔离性(Isolation)：使事务能够独立运作并相互透明。

  • 持久性(Durability)：确保在系统发生故障的情况下，提交的事务的结果或效果仍然存在。

事务控制

以下命令用于控制事务：

  • BEGIN TRANSACTION：开始事务。
  • COMMIT：保存更改，或者您可以使用END TRANSACTION命令。
  • ROLLBACK：回滚更改。

事务控制命令仅用于DML命令INSERT，UPDATE和DELETE。 创建表或删除它们时不能使用它们，因为这些操作会在数据库中自动提交。

BEGIN TRANSACTION命令：

可以使用BEGIN TRANSACTION或简单的BEGIN命令来开始事务。 这样的事务通常会持续下去，直到遇到下一个COMMIT或ROLLBACK命令。 但如果数据库关闭或发生错误，则事务也将ROLLBACK。

以下是启动/开始事务的简单语法：

BEGIN;

or

BEGIN TRANSACTION;

COMMIT命令

COMMIT命令是用于将事务调用的更改保存到数据库的事务命令。
COMMIT命令自上次的COMMIT或ROLLBACK命令后将所有事务保存到数据库。
COMMIT命令的语法如下：

COMMIT;

or

END TRANSACTION;

ROLLBACK命令

ROLLBACK命令是用于还原尚未保存到数据库的事务的事务命令。自上次发出COMMIT或ROLLBACK命令以来，ROLLBACK命令只能用于撤销事务。
ROLLBACK命令的语法如下：

ROLLBACK;

PostgreSQL锁

锁或独占锁或写锁阻止用户修改行或整个表。 在UPDATE和DELETE修改的行在事务的持续时间内被自动独占锁定。 这将阻止其他用户更改行，直到事务被提交或回退。

用户必须等待其他用户当他们都尝试修改同一行时。 如果他们修改不同的行，不需要等待。 SELECT查询不必等待。

数据库自动执行锁定。 然而，在某些情况下，必须手动控制锁定。 手动锁定可以通过使用LOCK命令完成。 它允许指定事务的锁类型和范围。

LOCK命令的语法：
LOCK [ TABLE ]
name
 IN
lock_mode
  • name：要锁定的现有表的锁名称(可选模式限定)。 如果在表名之前指定了ONLY，则仅该表被锁定 如果未指定ONLY，则表及其所有后代表(如果有)被锁定。
  • lock_mode：锁模式指定此锁与之冲突的锁。 如果未指定锁定模式，则使用最严格的访问模式ACCESS EXCLUSIVE。 可能的值是：ACCESS SHARE，ROW SHARE，ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。

死锁

当两个事务正在等待彼此完成操作时，可能会发生死锁。 虽然PostgreSQL可以检测到它们并使用ROLLBACK结束，但死锁仍然可能不方便。 为了防止您的应用程序遇到此问题，请确保以这样的方式进行设计，以使其以相同的顺序锁定对象。

咨询锁

PostgreSQL提供了创建具有应用程序定义含义的锁的方法。这些称为咨询锁(劝告锁,英文为：advisory locks)。 由于系统不强制使用它，因此应用程序正确使用它们。 咨询锁可用于锁定针对MVCC模型策略。

例如，咨询锁的常见用途是模拟所谓的“平面文件”数据管理系统的典型的悲观锁定策略。 虽然存储在表中的标志可以用于相同的目的，但是建议锁更快，避免了表的膨胀，并且在会话结束时被服务器自动清除。

posted @ 2020-11-22 17:50  漠漠颜  阅读(487)  评论(0)  编辑  收藏  举报