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
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;
跨连接
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函数
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
运算符。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 [ 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模型策略。
例如,咨询锁的常见用途是模拟所谓的“平面文件”数据管理系统的典型的悲观锁定策略。 虽然存储在表中的标志可以用于相同的目的,但是建议锁更快,避免了表的膨胀,并且在会话结束时被服务器自动清除。