PostgreSQL – 快速指南
PostgreSQL – 快速指南
PostgreSQL – 概述
PostgreSQL 是一个强大的开源对象关系数据库系统。它拥有超过 15 年的活跃开发阶段和经过验证的架构，在可靠性、数据完整性和正确性方面赢得了良好的声誉。
本教程将为您提供 PostgreSQL 的快速入门，并使您熟悉 PostgreSQL 编程。
什么是PostgreSQL？
PostgreSQL（发音为post-gress-QL）是一个由全球志愿者团队开发的开源关系数据库管理系统 (DBMS)。PostgreSQL 不受任何公司或其他私人实体的控制，并且源代码是免费提供的。
PostgreSQL 简史
PostgreSQL，最初称为 Postgres，是由一位名叫 Michael Stonebraker 的计算机科学教授在 UCB 创建的。Stonebraker 于 1986 年启动了 Postgres，作为其前身 Ingres 的后续项目，Ingres 现在归 Computer Associates 所有。
-
1977-1985 – 开发了一个名为 INGRES 的项目。
-
关系数据库的概念验证
-
1980年成立Ingres公司
-
1994 年被 Computer Associates 收购
-
-
1986-1994 – POSTGRES
-
INGRES 概念的发展，重点是面向对象和查询语言 – Quel
-
INGRES 的代码库没有用作 POSTGRES 的基础
-
商业化为 Illustra（被 Informix 收购，被 IBM 收购）
-
-
1994-1995 – Postgres95
-
1994 年添加了对 SQL 的支持
-
1995 年作为 Postgres95 发布
-
1996 年重新发布为 PostgreSQL 6.0
-
PostgreSQL全球开发团队成立
-
PostgreSQL 的主要特性
PostgreSQL 可在所有主要操作系统上运行，包括 Linux、UNIX（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris、Tru64）和 Windows。它支持文本、图像、声音和视频，并包括 C/C++、Java、Perl、Python、Ruby、Tcl 和开放数据库连接 (ODBC) 的编程接口。
PostgreSQL 支持大部分 SQL 标准并提供许多现代功能，包括以下内容 –
- 复杂的 SQL 查询
- SQL 子选择
- 外键
- 扳机
- 观看次数
- 交易
- 多版本并发控制 (MVCC)
- 流复制（从 9.0 开始）
- 热备（从 9.0 开始）
您可以查看 PostgreSQL 的官方文档以了解上述特性。用户可以通过多种方式扩展 PostgreSQL。例如通过添加新的 –
- 数据类型
- 职能
- 运营商
- 聚合函数
- 索引方法
程序语言支持
PostgreSQL 支持四种标准的过程语言，允许用户用任何一种语言编写自己的代码，并且可以由 PostgreSQL 数据库服务器执行。这些过程语言是 – PL/pgSQL、PL/Tcl、PL/Perl 和 PL/Python。此外，还支持其他非标准过程语言，如 PL/PHP、PL/V8、PL/Ruby、PL/Java 等。
PostgreSQL – 环境设置
要开始了解 PostgreSQL 基础知识，首先让我们安装 PostgreSQL。本章解释了如何在 Linux、Windows 和 Mac OS 平台上安装 PostgreSQL。
在 Linux/Unix 上安装 PostgreSQL
按照给定的步骤在您的 Linux 机器上安装 PostgreSQL。在继续安装之前，请确保您以root身份登录。
-
从EnterpriseDB 中选择您想要的 PostgreSQL 版本号，并尽可能准确地选择您想要的平台
-
我为我的 64 位 CentOS-6 机器下载了postgresql-9.2.4-1-linux-x64.run。现在，让我们按如下方式执行它 –
[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run [root@host]# ./postgresql-9.2.4-1-linux-x64.run ------------------------------------------------------------------------ Welcome to the PostgreSQL Setup Wizard. ------------------------------------------------------------------------ Please specify the directory where PostgreSQL will be installed. Installation Directory [/opt/PostgreSQL/9.2]:
-
启动安装程序后，它会询问您一些基本问题，例如安装位置、将使用数据库的用户的密码、端口号等。因此，请保留所有这些问题的默认值，密码除外，您可以提供密码根据您的选择。它将在您的 Linux 机器上安装 PostgreSQL 并显示以下消息 –
Please wait while Setup installs PostgreSQL on your computer. Installing 0% ______________ 50% ______________ 100% ######################################### ----------------------------------------------------------------------- Setup has finished installing PostgreSQL on your computer.
-
按照以下安装后步骤创建数据库 –
[root@host]# su - postgres Password: bash-4.1$ createdb testdb bash-4.1$ psql testdb psql (8.4.13, server 9.2.4) test=#
-
您可以使用以下命令启动/重启 postgres 服务器，以防它没有运行 –
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
-
如果您的安装正确，您将看到 PotsgreSQL 提示test=#，如上所示。
在 Windows 上安装 PostgreSQL
按照给定的步骤在您的 Windows 机器上安装 PostgreSQL。确保在安装时关闭了第三方防病毒软件。
-
从EnterpriseDB 中选择您想要的 PostgreSQL 版本号，并尽可能准确地选择您想要的平台
-
我为运行在 32 位模式下的 Windows PC 下载了 postgresql-9.2.4-1-windows.exe，所以让我们以管理员身份运行postgresql-9.2.4-1-windows.exe来安装 PostgreSQL。选择要安装它的位置。默认情况下，它安装在 Program Files 文件夹中。
-
安装过程的下一步是选择存储数据的目录。默认情况下，它存储在“数据”目录下。
-
接下来，设置会要求输入密码，因此您可以使用自己喜欢的密码。
-
下一步; 保持端口为默认值。
-
在下一步中，当询问“区域设置”时，我选择了“英语，美国”。
-
在您的系统上安装 PostgreSQL 需要一段时间。安装过程完成后，您将看到以下屏幕。取消选中复选框并单击完成按钮。
安装过程完成后，您可以从 PostgreSQL 9.2 下的程序菜单访问 pgAdmin III、StackBuilder 和 PostgreSQL shell。
在 Mac 上安装 PostgreSQL
按照给定的步骤在 Mac 机器上安装 PostgreSQL。在继续安装之前，请确保您以管理员身份登录。
-
选择EnterpriseDB提供的适用于 Mac OS 的 PostgreSQL 的最新版本号
-
我为运行 OS X 版本 10.8.3 的 Mac OS下载了postgresql-9.2.4-1-osx.dmg。现在，让我们在 finder 中打开 dmg 图像并双击它，这将在以下窗口中为您提供 PostgreSQL 安装程序 –
-
接下来，单击postgres-9.2.4-1-osx图标，它将给出警告消息。接受警告并继续进行进一步安装。它将要求输入管理员密码，如下面的窗口所示 –
输入密码，继续安装，这一步之后，重启你的Mac机器。如果您没有看到以下窗口，请再次开始安装。
-
启动安装程序后，它会询问一些基本问题，例如安装位置、将使用数据库的用户密码、端口号等。根据您的选择。它将在您的 Mac 机器上的 Application 文件夹中安装 PostgreSQL，您可以检查 –
-
现在，您可以启动任何程序开始。让我们从 SQL Shell 开始。当您启动 SQL Shell 时，只需使用它显示的所有默认值，除了输入您在安装时选择的密码。如果一切顺利，那么您将进入 postgres 数据库并显示postgress#提示，如下所示 –
恭喜！！！现在您已准备好开始 PostgreSQL 数据库编程的环境。
PostgreSQL – 语法
本章提供了 PostgreSQL SQL 命令的列表，以及每个命令的精确语法规则。这组命令取自 psql 命令行工具。现在你已经安装了 Postgres，打开 psql 为 –
程序文件 → PostgreSQL 9.2 → SQL Shell(psql)。
使用 psql，您可以使用 \help 命令生成完整的命令列表。对于特定命令的语法，请使用以下命令 –
postgres-# \help <command_name>
SQL 语句
SQL 语句由标记组成，其中每个标记可以表示关键字、标识符、带引号的标识符、常量或特殊字符符号。下表使用一个简单的 SELECT 语句来说明一个基本但完整的 SQL 语句及其组件。
| 选择 | 身份证、姓名 | 从 | 状态 | |
|---|---|---|---|---|
| Token Type | 关键词 | 身份标识 | 关键词 | 标识符 |
| Description | 命令 | Id 和 name 列 | 条款 | 表名 |
PostgreSQL SQL 命令
中止
中止当前事务。
ABORT [ WORK | TRANSACTION ]
更改聚合
更改聚合函数的定义。
ALTER AGGREGATE name ( type ) RENAME TO new_name ALTER AGGREGATE name ( type ) OWNER TO new_owner
改变转换
更改转换的定义。
ALTER CONVERSION name RENAME TO new_name ALTER CONVERSION name OWNER TO new_owner
更改数据库
更改数据库特定参数。
ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner
更改域
更改域特定参数的定义。
ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner
更改功能
更改函数的定义。
ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner
更改组
更改用户组。
ALTER GROUP groupname ADD USER username [, ... ] ALTER GROUP groupname DROP USER username [, ... ] ALTER GROUP groupname RENAME TO new_name
更改索引
更改索引的定义。
ALTER INDEX name OWNER TO new_owner ALTER INDEX name SET TABLESPACE indexspace_name ALTER INDEX name RENAME TO new_name
改变语言
更改过程语言的定义。
ALTER LANGUAGE name RENAME TO new_name
更改运算符
更改运算符的定义。
ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner
更改运算符类
更改运算符类的定义。
ALTER OPERATOR CLASS name USING index_method RENAME TO new_name ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner
更改架构
更改架构的定义。
ALTER SCHEMA name RENAME TO new_name ALTER SCHEMA name OWNER TO new_owner
改变序列
更改序列生成器的定义。
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
更改表
更改表的定义。
ALTER TABLE [ ONLY ] name [ * ] action [, ... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name
其中动作是以下几行之一 –
ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name
改变表空间
更改表空间的定义。
ALTER TABLESPACE name RENAME TO new_name ALTER TABLESPACE name OWNER TO new_owner
改变触发器
更改触发器的定义。
ALTER TRIGGER name ON table RENAME TO new_name
更改类型
更改类型的定义。
ALTER TYPE name OWNER TO new_owner
更改用户
更改数据库用户帐户。
ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter
选项可以在哪里–
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | VALID UNTIL 'abstime'
分析
收集有关数据库的统计信息。
ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]
开始
启动一个事务块。
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
其中transaction_mode是其中之一 –
ISOLATION LEVEL {
SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED
}
READ WRITE | READ ONLY
检查点
强制事务日志检查点。
CHECKPOINT
关闭
关闭游标。
CLOSE name
簇
根据索引对表进行聚类。
CLUSTER index_name ON table_name CLUSTER table_name CLUSTER
评论
定义或更改对象的注释。
COMMENT ON {
TABLE object_name |
COLUMN table_name.column_name |
AGGREGATE agg_name (agg_type) |
CAST (source_type AS target_type) |
CONSTRAINT constraint_name ON table_name |
CONVERSION object_name |
DATABASE object_name |
DOMAIN object_name |
FUNCTION func_name (arg1_type, arg2_type, ...) |
INDEX object_name |
LARGE OBJECT large_object_oid |
OPERATOR op (left_operand_type, right_operand_type) |
OPERATOR CLASS object_name USING index_method |
[ PROCEDURAL ] LANGUAGE object_name |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
}
IS 'text'
犯罪
提交当前事务。
COMMIT [ WORK | TRANSACTION ]
复制
在文件和表之间复制数据。
COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]
创建聚合
定义一个新的聚合函数。
CREATE AGGREGATE name ( BASETYPE = input_data_type, SFUNC = sfunc, STYPE = state_data_type [, FINALFUNC = ffunc ] [, INITCOND = initial_condition ] )
创建演员表
定义新演员表。
CREATE CAST (source_type AS target_type) WITH FUNCTION func_name (arg_types) [ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (source_type AS target_type) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ]
创建约束触发器
定义一个新的约束触发器。
CREATE CONSTRAINT TRIGGER name AFTER events ON table_name constraint attributes FOR EACH ROW EXECUTE PROCEDURE func_name ( args )
创造转化
定义一个新的转换。
CREATE [DEFAULT] CONVERSION name FOR source_encoding TO dest_encoding FROM func_name
创建数据库
创建一个新的数据库。
CREATE DATABASE name [ [ WITH ] [ OWNER [=] db_owner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] ]
创建域
定义一个新域。
CREATE DOMAIN name [AS] data_type [ DEFAULT expression ] [ constraint [ ... ] ]
其中约束是 –
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }
创建函数
定义一个新函数。
CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]
创建组
定义一个新的用户组。
CREATE GROUP name [ [ WITH ] option [ ... ] ] Where option can be: SYSID gid | USER username [, ...]
创建索引
定义新索引。
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]
创建语言
定义一种新的程序语言。
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name HANDLER call_handler [ VALIDATOR val_function ]
创建运算符
定义一个新的运算符。
CREATE OPERATOR name ( PROCEDURE = func_name [, LEFTARG = left_type ] [, RIGHTARG = right_type ] [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] [, RESTRICT = res_proc ] [, JOIN = join_proc ] [, HASHES ] [, MERGES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ] )
创建操作员类
定义一个新的运算符类。
CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS
{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
| FUNCTION support_number func_name ( argument_type [, ...] )
| STORAGE storage_type
} [, ... ]
创建规则
定义新的重写规则。
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
创建架构
定义新模式。
CREATE SCHEMA schema_name [ AUTHORIZATION username ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]
创建序列
定义一个新的序列生成器。
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
创建表
定义一个新表。
CREATE [ [ GLOBAL | LOCAL ] {
TEMPORARY | TEMP } ] TABLE table_name ( {
column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ]
} [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
column_constraint在哪里–
[ CONSTRAINT constraint_name ] {
NOT NULL |
NULL |
UNIQUE [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
CHECK (expression) |
REFERENCES ref_table [ ( ref_column ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ]
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
而table_constraint是 –
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
创建表为
根据查询结果定义一个新表。
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query
创建表空间
定义一个新的表空间。
CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'
创建触发器
定义一个新的触发器。
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )
创建类型
定义新的数据类型。
CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)
创建用户
定义一个新的数据库用户帐户。
CREATE USER name [ [ WITH ] option [ ... ] ]
选项可以在哪里–
SYSID uid | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP group_name [, ...] | VALID UNTIL 'abs_time'
创建视图
定义新视图。
CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query
解除分配
释放准备好的语句。
DEALLOCATE [ PREPARE ] plan_name
宣布
定义游标。
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
删除
删除表的行。
DELETE FROM [ ONLY ] table [ WHERE condition ]
下降聚合
删除聚合函数。
DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]
空投
删除演员表。
DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]
丢弃转换
删除转换。
DROP CONVERSION name [ CASCADE | RESTRICT ]
删除数据库
删除数据库。
DROP DATABASE name
删除域
删除域。
DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]
下降功能
删除一个函数。
DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]
删除组
删除用户组。
DROP GROUP name
下降指数
删除索引。
DROP INDEX name [, ...] [ CASCADE | RESTRICT ]
删除语言
删除程序语言。
DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]
下拉运算符
删除操作员。
DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]
下降操作员类
删除运算符类。
DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]
丢弃规则
删除重写规则。
DROP RULE name ON relation [ CASCADE | RESTRICT ]
删除架构
删除架构。
DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]
丢弃序列
删除一个序列。
DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]
掉落表
删除一个表。
DROP TABLE name [, ...] [ CASCADE | RESTRICT ]
删除表空间
删除表空间。
DROP TABLESPACE tablespace_name
下降触发器
删除触发器。
DROP TRIGGER name ON table [ CASCADE | RESTRICT ]
掉落类型
删除数据类型。
DROP TYPE name [, ...] [ CASCADE | RESTRICT ]
删除用户
删除数据库用户帐户。
DROP USER name
下拉视图
删除视图。
DROP VIEW name [, ...] [ CASCADE | RESTRICT ]
结尾
提交当前事务。
END [ WORK | TRANSACTION ]
执行
执行准备好的语句。
EXECUTE plan_name [ (parameter [, ...] ) ]
解释
显示语句的执行计划。
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
拿来
使用游标从查询中检索行。
FETCH [ direction { FROM | IN } ] cursor_name
凡方向可以为空或一个-
NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count ALL FORWARD FORWARD count FORWARD ALL BACKWARD BACKWARD count BACKWARD ALL
授予
定义访问权限。
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
插
在表中创建新行。
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
听
收听通知。
LISTEN name
加载
加载或重新加载共享库文件。
LOAD 'filename'
锁
锁定一张桌子。
LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]
其中lock_mode是其中之一 –
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
移动
定位光标。
MOVE [ direction { FROM | IN } ] cursor_name
通知
生成通知。
NOTIFY name
准备
准备执行语句。
PREPARE plan_name [ (data_type [, ...] ) ] AS statement
重新索引
重建索引。
REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]
释放保存点
销毁先前定义的保存点。
RELEASE [ SAVEPOINT ] savepoint_name
重启
将运行时参数的值恢复为默认值。
RESET name RESET ALL
撤销
删除访问权限。
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
回滚
中止当前事务。
ROLLBACK [ WORK | TRANSACTION ]
回滚到保存点
回滚到保存点。
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
保存点
在当前事务中定义一个新的保存点。
SAVEPOINT savepoint_name
选择
从表或视图中检索行。
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
其中from_item可以是以下之一：
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
选择进入
根据查询结果定义一个新表。
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
放
更改运行时参数。
SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }
设置约束
为当前事务设置约束检查模式。
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
设置会话授权
设置会话用户标识和当前会话的当前用户标识。
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT RESET SESSION AUTHORIZATION
设置交易
设置当前事务的特征。
SET TRANSACTION transaction_mode [, ...] SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
其中transaction_mode是其中之一 –
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY
表演
显示运行时参数的值。
SHOW name SHOW ALL
开始交易
启动一个事务块。
START TRANSACTION [ transaction_mode [, ...] ]
其中transaction_mode是其中之一 –
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY
截短
清空一张桌子。
TRUNCATE [ TABLE ] name
不听
停止收听通知。
UNLISTEN { name | * }
更新
更新表的行。
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]
真空
垃圾收集和可选地分析数据库。
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
PostgreSQL – 数据类型
在本章中，我们将讨论 PostgreSQL 中使用的数据类型。创建表时，为每一列指定一种数据类型，即您希望在表字段中存储什么样的数据。
这有几个好处 –
-
一致性– 针对相同数据类型的列的操作给出一致的结果并且通常是最快的。
-
验证– 正确使用数据类型意味着对数据进行格式验证并拒绝数据类型范围之外的数据。
-
紧凑性– 由于列可以存储单一类型的值，因此它以紧凑的方式存储。
-
性能– 正确使用数据类型可以最有效地存储数据。可以快速处理存储的值，从而提高性能。
PostgreSQL 支持多种数据类型。此外，用户可以使用CREATE TYPE SQL 命令创建自己的自定义数据类型。PostgreSQL 中有不同类别的数据类型。它们将在下面讨论。
数字类型
数字类型由两字节、四字节和八字节整数、四字节和八字节浮点数以及可选精度的小数组成。下表列出了可用的类型。
| Name | 存储大小 | 描述 | 范围 |
|---|---|---|---|
| smallint | 2 字节 | 小范围整数 | -32768 至 +32767 |
| integer | 4字节 | 整数的典型选择 | -2147483648 至 +2147483647 |
| bigint | 8 字节 | 大范围整数 | -9223372036854775808 至 9223372036854775807 |
| decimal | 多变的 | 用户指定精度，精确 | 小数点前最多 131072 位；小数点后最多 16383 位 |
| numeric | 多变的 | 用户指定精度，精确 | 小数点前最多 131072 位；小数点后最多 16383 位 |
| real | 4字节 | 可变精度，不精确 | 6 位小数精度 |
| double precision | 8 字节 | 可变精度，不精确 | 15位十进制数字精度 |
| smallserial | 2 字节 | 自增小整数 | 1 至 32767 |
| serial | 4字节 | 自增整数 | 1 至 2147483647 |
| bigserial | 8 字节 | 大自增整数 | 1 至 9223372036854775807 |
货币类型
的钱类型存储与一个固定的分数精确度的货币金额。该值的数字，int和BIGINT数据类型可以转换为金钱。由于存在舍入错误的可能性，不建议使用浮点数来处理货币。
| Name | 存储大小 | 描述 | 范围 |
|---|---|---|---|
| money | 8 字节 | 货币金额 | -92233720368547758.08 至 +92233720368547758.07 |
字符类型
下表列出了 PostgreSQL 中可用的通用字符类型。
| S. No. | 名称和描述 |
|---|---|
| 1 |
character varying(n), varchar(n) 有限制的可变长度 |
| 2 |
character(n), char(n) 固定长度，空白填充 |
| 3 |
text 可变无限长度 |
二进制数据类型
所述BYTEA数据类型允许的二进制串的存储如下面给出的表中。
| Name | 存储大小 | 描述 |
|---|---|---|
| bytea | 1 或 4 个字节加上实际的二进制字符串 | 变长二进制串 |
日期/时间类型
PostgreSQL 支持全套 SQL 日期和时间类型，如下表所示。日期是根据公历计算的。这里，所有类型的分辨率都是1 微秒/14 位，但日期类型除外，其分辨率是day。
| Name | 存储大小 | 描述 | 低价值 | 高价值 |
|---|---|---|---|---|
| timestamp [(p)] [without time zone ] | 8 字节 | 日期和时间（无时区） | 公元前 4713 年 | 公元 294276 年 |
| TIMESTAMPTZ | 8 字节 | 日期和时间，带时区 | 公元前 4713 年 | 公元 294276 年 |
| date | 4字节 | 日期（没有时间） | 公元前 4713 年 | 公元5874897 |
| time [ (p)] [ without time zone ] | 8 字节 | 时间（无日期） | 00:00:00 | 24:00:00 |
| time [ (p)] with time zone | 12 字节 | 仅限一天中的时间，带时区 | 00:00:00+1459 | 24:00:00-1459 |
| interval [fields ] [(p) ] | 12 字节 | 时间间隔 | -17.8亿年 | 17.8亿年 |
布尔类型
PostgreSQL 提供了标准的 SQL 类型 Boolean。布尔数据类型可以具有true、false状态和第三个状态unknown，它由 SQL 空值表示。
| Name | 存储大小 | 描述 |
|---|---|---|
| boolean | 1 字节 | 真假状态 |
枚举类型
枚举 (enum) 类型是包含一组静态有序值的数据类型。它们等同于许多编程语言支持的枚举类型。
与其他类型不同，枚举类型需要使用 CREATE TYPE 命令创建。此类型用于存储一组静态、有序的值。例如指南针方向，即北、南、东和西或一周中的几天，如下所示 –
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
枚举一旦创建，就可以像任何其他类型一样使用。
几何类型
几何数据类型表示二维空间对象。最基本的类型，即点，构成了所有其他类型的基础。
| Name | 存储大小 | 表示 | 描述 |
|---|---|---|---|
| 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),…) |
| path | 16+16n 字节 | 打开路径 | [(x1,y1),…] |
| polygon | 40+16n | 多边形（类似于封闭路径） | ((x1,y1),…) |
| circle | 24 字节 | 圆圈 | <(x,y),r>（中心点和半径） |
网络地址类型
PostgreSQL 提供数据类型来存储 IPv4、IPv6 和 MAC 地址。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供输入错误检查和专门的运算符和函数。
| Name | 存储大小 | 描述 |
|---|---|---|
| cidr | 7 或 19 字节 | IPv4 和 IPv6 网络 |
| inet | 7 或 19 字节 | IPv4 和 IPv6 主机和网络 |
| macaddr | 6 字节 | MAC地址 |
位串类型
位串类型用于存储位掩码。它们是 0 或 1。有两种 SQL 位类型：bit(n)和bit changed(n)，其中 n 是一个正整数。
文本搜索类型
这种类型支持全文搜索，这是搜索自然语言文档集合以找到最匹配查询的那些的活动。这有两种数据类型 –
| S. No. | 名称和描述 |
|---|---|
| 1 |
tsvector 这是一个不同单词的排序列表，这些单词已被规范化以合并同一单词的不同变体，称为“词素”。 |
| 2 |
tsquery 这存储了要搜索的词素，并按照布尔运算符 & (AND), | 将它们组合起来。（或），和！（不是）。括号可用于强制对运算符进行分组。 |
UUID 类型
UUID（通用唯一标识符）被写成一系列小写十六进制数字，由连字符分隔的几组，特别是一组八位数字，然后是三组四位数字，然后是一组 12 位数字，例如总共 32 位数字代表 128 位。
UUID 的一个例子是 – 550e8400-e29b-41d4-a716-446655440000
XML 类型
XML 数据类型可用于存储 XML 数据。为了存储 XML 数据，首先必须使用函数 xmlparse 创建 XML 值，如下所示 –
XMLPARSE (DOCUMENT '<?xml version="1.0"?> <tutorial> <title>PostgreSQL Tutorial </title> <topics>...</topics> </tutorial>') XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')
JSON 类型
的JSON数据类型可以被用来存储JSON（JavaScript对象符号）的数据。此类数据也可以存储为text，但json数据类型的优点是可以检查每个存储的值是否是有效的 JSON 值。还有相关的支持函数可用，可以直接用于处理JSON数据类型如下。
| Example | 示例结果 |
|---|---|
| array_to_json(‘{{1,5},{99,100}}’::int[]) | [[1,5],[99,100]] |
| row_to_json(row(1,’foo’)) | {“f1″:1,”f2″:”foo”} |
数组类型
PostgreSQL 提供了将表的列定义为可变长度多维数组的机会。可以创建任何内置或用户定义的基类型、枚举类型或复合类型的数组。
数组声明
数组类型可以声明为
CREATE TABLE monthly_savings ( name text, saving_per_quarter integer[], scheme text[][] );
或使用关键字“ARRAY”作为
CREATE TABLE monthly_savings ( name text, saving_per_quarter integer ARRAY[4], scheme text[][] );
插入值
数组值可以作为文字常量插入，将元素值括在花括号内并用逗号分隔它们。一个例子如下所示 –
INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);
访问数组
访问数组的示例如下所示。下面给出的命令将选择第二季度比第四季度储蓄更多的人。
SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];
修改数组
修改数组的示例如下所示。
UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';
或使用 ARRAY 表达式语法 –
UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Manisha';
搜索数组
搜索数组的示例如下所示。
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR saving_per_quarter[2] = 10000 OR saving_per_quarter[3] = 10000 OR saving_per_quarter[4] = 10000;
如果数组的大小已知，则可以使用上面给出的搜索方法。否则，以下示例显示了在大小未知时如何进行搜索。
SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);
复合类型
该类型表示字段名称及其数据类型的列表，即表的行或记录的结构。
复合类型的声明
以下示例显示了如何声明复合类型
CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric );
此数据类型可用于创建表，如下所示 –
CREATE TABLE on_hand ( item inventory_item, count integer );
复合值输入
复合值可以作为文字常量插入，将字段值括在括号内并用逗号分隔它们。一个例子如下所示 –
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
这对上面定义的inventory_item有效。只要表达式中有多个字段，ROW 关键字实际上是可选的。
访问复合类型
要访问复合列的字段，请使用点后跟字段名称，就像从表名称中选择字段一样。例如，要从我们的 on_hand 示例表中选择一些子字段，查询将如下所示 –
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
您甚至可以使用表名（例如在多表查询中），如下所示 –
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
范围类型
范围类型表示使用数据范围的数据类型。范围类型可以是离散范围（例如，所有整数值 1 到 10）或连续范围（例如，上午 10:00 到上午 11:00 之间的任何时间点）。
可用的内置范围类型包括以下范围 –
-
int4range – 整数范围
-
int8range – bigint 的范围
-
numrange – 数字范围
-
tsrange – 没有时区的时间戳范围
-
tstzrange – 带时区的时间戳范围
-
daterange – 日期范围
可以创建自定义范围类型以使新的范围类型可用，例如使用 inet 类型作为基础的 IP 地址范围，或使用浮点数据类型作为基础的浮点范围。
范围类型分别支持使用 [ ] 和 ( ) 字符的包含和排除范围边界。例如 ‘[4,9)’ 表示从 4 开始并包括 4 到但不包括 9 的所有整数。
对象标识符类型
对象标识符 (OID) 在 PostgreSQL 内部用作各种系统表的主键。如果指定了WITH OIDS或启用了default_with_oids配置变量，则只有在这种情况下，才会将 OID 添加到用户创建的表中。下表列出了几种别名类型。除了专门的输入和输出例程外，OID 别名类型没有自己的操作。
| Name | 参考 | 描述 | 值示例 |
|---|---|---|---|
| oid | 任何 | 数字对象标识符 | 564182 |
| regproc | pg_proc | 函数名 | 和 |
| regprocedure | pg_proc | 带参数类型的函数 | 总和（整数4） |
| regoper | pg_operator | 操作员姓名 | + |
| regoperator | pg_operator | 带参数类型的运算符 | *(integer,integer) 或 -(NONE,integer) |
| regclass | pg_class | 关系名称 | pg_type |
| regtype | pg_type | 数据类型名称 | 整数 |
| regconfig | pg_ts_config | 文本搜索配置 | 英语 |
| regdictionary | pg_ts_dict | 文本搜索词典 | 简单的 |
伪类型
PostgreSQL 类型系统包含许多特殊用途的条目，统称为伪类型。伪类型不能用作列数据类型，但可用于声明函数的参数或结果类型。
下表列出了现有的伪类型。
| S. No. | 名称和描述 |
|---|---|
| 1 |
any 表示函数接受任何输入数据类型。 |
| 2 |
anyelement 表示函数接受任何数据类型。 |
| 3 |
anyarray 表示函数接受任何数组数据类型。 |
| 4 |
anynonarray 表示函数接受任何非数组数据类型。 |
| 5 |
anyenum 表示函数接受任何枚举数据类型。 |
| 6 |
anyrange 表示函数接受任何范围数据类型。 |
| 7 |
cstring 指示函数接受或返回以空字符结尾的 C 字符串。 |
| 8 |
internal 指示函数接受或返回服务器内部数据类型。 |
| 9 |
language_handler 程序语言调用处理程序被声明为返回 language_handler。 |
| 10 |
fdw_handler 外部数据包装处理程序被声明为返回 fdw_handler。 |
| 11 |
record 标识返回未指定行类型的函数。 |
| 12 |
trigger 一个触发器函数被声明为返回触发器。 |
| 13 |
void 表示函数不返回任何值。 |
PostgreSQL – 创建数据库
本章讨论如何在 PostgreSQL 中创建新数据库。PostgreSQL 提供了两种创建新数据库的方法 –
- 使用 CREATE DATABASE，一个 SQL 命令。
- 使用createdb命令行可执行文件。
使用创建数据库
此命令将从 PostgreSQL shell 提示符创建一个数据库，但您应该具有创建数据库的适当权限。默认情况下，将通过克隆标准系统数据库template1来创建新数据库。
句法
CREATE DATABASE 语句的基本语法如下 –
CREATE DATABASE dbname;
其中dbname是要创建的数据库的名称。
例子
下面是一个简单的例子，它将在你的 PostgreSQL 模式中创建testdb
postgres=# CREATE DATABASE testdb; postgres-#
使用 createdb 命令
PostgreSQL 命令行可执行文件createdb是 SQL 命令CREATE DATABASE的包装器。此命令与 SQL 命令CREATE DATABASE之间的唯一区别是前者可以直接从命令行运行，并且允许将注释添加到数据库中，所有这些都在一个命令中。
句法
createdb的语法如下所示 –
createdb [option...] [dbname [description]]
参数
下表列出了参数及其说明。
| S. No. | 参数及说明 |
|---|---|
| 1 |
dbname 要创建的数据库的名称。 |
| 2 |
description 指定要与新创建的数据库相关联的注释。 |
| 3 |
options 命令行参数， createdb 接受。 |
选项
下表列出了 createdb 接受的命令行参数 –
| S. No. | 选项和说明 |
|---|---|
| 1 |
-D tablespace 指定数据库的默认表空间。 |
| 2 |
-e 回显 createdb 生成并发送到服务器的命令。 |
| 3 |
-E encoding 指定要在此数据库中使用的字符编码方案。 |
| 4 |
-l locale 指定要在此数据库中使用的语言环境。 |
| 5 |
-T template 指定用于构建此数据库的模板数据库。 |
| 6 |
–help 显示有关 createdb 命令行参数的帮助，然后退出。 |
| 7 |
-h host 指定运行服务器的机器的主机名。 |
| 8 |
-p port 指定服务器正在侦听连接的 TCP 端口或本地 Unix 域套接字文件扩展名。 |
| 9 |
-U username 要连接的用户名。 |
| 10 |
-w 永远不要发出密码提示。 |
| 11 |
-W 强制 createdb 在连接到数据库之前提示输入密码。 |
打开命令提示符并转到安装 PostgreSQL 的目录。进入bin目录，执行以下命令创建数据库。
createdb -h localhost -p 5432 -U postgres testdb password ******
上面给出的命令将提示您输入 PostgreSQL 管理员用户的密码，默认为postgres。因此，提供密码并继续创建新数据库
使用上述任一方法创建数据库后，您可以使用\l在数据库列表中检查它，即反斜杠 el 命令，如下所示 –
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C | C |
(4 rows)
postgres-#
PostgreSQL – 选择数据库
本章解释了访问数据库的各种方法。假设我们已经在上一章中创建了一个数据库。您可以使用以下任一方法选择数据库 –
- 数据库 SQL 提示
- 操作系统命令提示符
数据库 SQL 提示
假设你已经启动了你的 PostgreSQL 客户端并且你已经登陆了以下 SQL 提示符 –
postgres=#
您可以使用\l检查可用的数据库列表，即反斜杠 el 命令如下 –
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C | C |
(4 rows)
postgres-#
现在，键入以下命令以连接/选择所需的数据库；在这里，我们将连接到testdb数据库。
postgres=# \c testdb; psql (9.2.4) Type "help" for help. You are now connected to database "testdb" as user "postgres". testdb=#
操作系统命令提示符
您可以在登录数据库时从命令提示符中选择您的数据库。以下是一个简单的例子 –
psql -h localhost -p 5432 -U postgress testdb Password for user postgress: **** psql (9.2.4) Type "help" for help. You are now connected to database "testdb" as user "postgres". testdb=#
您现在已登录 PostgreSQL testdb 并准备好在 testdb 中执行您的命令。要退出数据库，可以使用命令 \q。
PostgreSQL – 删除数据库
在本章中，我们将讨论如何在 PostgreSQL 中删除数据库。删除数据库有两个选项 –
- 使用 DROP DATABASE，一个 SQL 命令。
- 使用dropdb命令行可执行文件。
使用此操作前要小心，因为删除现有数据库会导致数据库中存储的完整信息丢失。
使用 DROP 数据库
此命令删除数据库。它删除数据库的目录条目并删除包含数据的目录。它只能由数据库所有者执行。当您或其他任何人连接到目标数据库（连接到 postgres 或任何其他数据库以发出此命令）时，无法执行此命令。
句法
DROP DATABASE 的语法如下 –
DROP DATABASE [ IF EXISTS ] name
参数
该表列出了参数及其说明。
| S. No. | 参数及说明 |
|---|---|
| 1 |
IF EXISTS 如果数据库不存在，不要抛出错误。在这种情况下会发出通知。 |
| 2 |
name 要删除的数据库的名称。 |
我们不能删除具有任何打开连接的数据库，包括我们自己来自psql或pgAdmin III的连接。如果我们想删除我们当前连接的数据库，我们必须切换到另一个数据库或模板1。因此，使用程序dropdb可能更方便，它是此命令的包装器。
例子
以下是一个简单的例子，它将从你的 PostgreSQL 模式中删除testdb –
postgres=# DROP DATABASE testdb; postgres-#
使用 dropdb 命令
PostgresSQL 命令行可执行文件dropdb是 SQL 命令DROP DATABASE的命令行包装器。通过此实用程序删除数据库与通过其他访问服务器的方法删除数据库之间没有有效区别。dropdb 会破坏现有的 PostgreSQL 数据库。执行此命令的用户必须是数据库超级用户或数据库所有者。
句法
dropdb的语法如下所示 –
dropdb [option...] dbname
参数
下表列出了参数及其说明
| S. No. | 参数及说明 |
|---|---|
| 1 |
dbname 要删除的数据库的名称。 |
| 2 |
option dropdb 接受的命令行参数。 |
选项
下表列出了 dropdb 接受的命令行参数 –
| S. No. | 选项和说明 |
|---|---|
| 1 |
-e 显示发送到服务器的命令。 |
| 2 |
-i 在执行任何破坏性操作之前发出验证提示。 |
| 3 |
-V 打印 dropdb 版本并退出。 |
| 4 |
–if-exists 如果数据库不存在，不要抛出错误。在这种情况下会发出通知。 |
| 5 |
–help 显示有关 dropdb 命令行参数的帮助，然后退出。 |
| 6 |
-h host 指定运行服务器的机器的主机名。 |
| 7 |
-p port 指定服务器正在侦听连接的 TCP 端口或本地 UNIX 域套接字文件扩展名。 |
| 8 |
-U username 要连接的用户名。 |
| 9 |
-w 永远不要发出密码提示。 |
| 10 |
-W 强制 dropdb 在连接到数据库之前提示输入密码。 |
| 11 |
–maintenance-db=dbname 指定要连接到的数据库的名称以删除目标数据库。 |
例子
以下示例演示从操作系统命令提示符删除数据库 –
dropdb -h localhost -p 5432 -U postgress testdb Password for user postgress: ****
上面的命令删除数据库testdb。在这里，我使用了postgres（在 template1 的 pg_roles 下找到）用户名来删除数据库。
PostgreSQL – 创建表
PostgreSQL CREATE TABLE 语句用于在任何给定数据库中创建新表。
句法
CREATE TABLE 语句的基本语法如下 –
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
CREATE TABLE 是一个关键字，告诉数据库系统创建一个新表。表的唯一名称或标识符遵循 CREATE TABLE 语句。最初，当前数据库中的空表由发出命令的用户拥有。
然后，在括号中，是列表，定义表中的每一列以及它是什么类型的数据类型。通过下面给出的示例，语法将变得清晰。
例子
以下是一个示例，它创建了一个以 ID 作为主键的 COMPANY 表，NOT NULL 是显示在此表中创建记录时这些字段不能为 NULL 的约束 –
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
让我们再创建一张表，我们将在后续章节的练习中使用它 –
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
您可以使用\d命令验证您的表是否已成功创建，该命令将用于列出附加数据库中的所有表。
testdb-# \d
上面给出的 PostgreSQL 语句将产生以下结果 –
List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | company | table | postgres public | department | table | postgres (2 rows)
使用\d tablename描述每个表，如下所示 –
testdb-# \d company
上面给出的 PostgreSQL 语句将产生以下结果 –
Table "public.company"
Column | Type | Modifiers
-----------+---------------+-----------
id | integer | not null
name | text | not null
age | integer | not null
address | character(50) |
salary | real |
join_date | date |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
PostgreSQL – 删除表
PostgreSQL DROP TABLE 语句用于删除表定义以及该表的所有关联数据、索引、规则、触发器和约束。
使用此命令时必须小心，因为一旦表被删除，表中的所有可用信息也将永远丢失。
句法
DROP TABLE 语句的基本语法如下 –
DROP TABLE table_name;
例子
我们在上一章中创建了表 DEPARTMENT 和 COMPANY。首先，验证这些表（使用\d列出表） –
testdb-# \d
这将产生以下结果 –
List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | company | table | postgres public | department | table | postgres (2 rows)
这意味着存在 DEPARTMENT 和 COMPANY 表。所以让我们按如下方式删除它们 –
testdb=# drop table department, company;
这将产生以下结果 –
DROP TABLE testdb=# \d relations found. testdb=#
返回 DROP TABLE 消息表示 drop 命令执行成功。
PostgreSQL – 架构
一个方案是表的命名集合。模式还可以包含视图、索引、序列、数据类型、运算符和函数。模式类似于操作系统级别的目录，只是模式不能嵌套。PostgreSQL 语句 CREATE SCHEMA 创建一个模式。
句法
CREATE SCHEMA 的基本语法如下 –
CREATE SCHEMA name;
其中name是架构的名称。
在模式中创建表的语法
在模式中创建表的基本语法如下 –
CREATE TABLE myschema.mytable ( ... );
例子
让我们看一个创建模式的例子。连接到数据库testdb并创建一个模式myschema，如下所示 –
testdb=# create schema myschema; CREATE SCHEMA
消息“CREATE SCHEMA”表示模式已成功创建。
现在，让我们在上述模式中创建一个表，如下所示 –
testdb=# create table myschema.company( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
这将创建一个空表。您可以验证使用下面给出的命令创建的表 –
testdb=# select * from myschema.company;
这将产生以下结果 –
id | name | age | address | salary ----+------+-----+---------+-------- (0 rows)
删除架构的语法
要删除空模式（其中的所有对象都已删除），请使用命令 –
DROP SCHEMA myschema;
要删除包含所有包含对象的模式，请使用命令 –
DROP SCHEMA myschema CASCADE;
使用模式的优点
-
它允许许多用户使用一个数据库而不会相互干扰。
-
它将数据库对象组织成逻辑组，使它们更易于管理。
-
第三方应用程序可以放在单独的模式中，这样它们就不会与其他对象的名称发生冲突。
PostgreSQL – 插入查询
PostgreSQL INSERT INTO语句允许向表中插入新行。作为查询的结果，可以一次插入一行或多行。
句法
INSERT INTO 语句的基本语法如下 –
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);
-
此处，column1、column2、…columnN 是表中要插入数据的列的名称。
-
目标列名称可以按任何顺序列出。VALUES 子句或查询提供的值与从左到右的显式或隐式列列表相关联。
如果要为表的所有列添加值，则可能不需要在 SQL 查询中指定列名称。但是，请确保值的顺序与表中的列顺序相同。SQL INSERT INTO 语法如下：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
输出
下表总结了输出消息及其含义 –
| S. No. | 输出消息和描述 |
|---|---|
| 1 |
INSERT oid 1 如果仅插入一行，则返回消息。oid 是插入行的数字 OID。 |
| 2 |
INSERT 0 # 如果插入了多行，则返回消息。# 是插入的行数。 |
例子
让我们在testdb 中创建 COMPANY 表如下 –
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL, JOIN_DATE DATE );
以下示例在 COMPANY 表中插入一行 –
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
下面的例子是插入一行；这里省略了薪水列，因此它将具有默认值 –
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
以下示例对 JOIN_DATE 列使用 DEFAULT 子句而不是指定值 –
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
以下示例使用 multirow VALUES 语法插入多行 –
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
以上所有语句都会在 COMPANY 表中创建以下记录。下一章将教你如何从一个表中显示所有这些记录。
ID NAME AGE ADDRESS SALARY JOIN_DATE ---- ---------- ----- ---------- ------- -------- 1 Paul 32 California 20000.0 2001-07-13 2 Allen 25 Texas 2007-12-13 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 2007-12-13 5 David 27 Texas 85000.0 2007-12-13
PostgreSQL – SELECT 查询
PostgreSQL SELECT语句用于从数据库表中获取数据，以结果表的形式返回数据。这些结果表称为结果集。
句法
SELECT 语句的基本语法如下 –
SELECT column1, column2, columnN FROM table_name;
此处，column1、column2…是表的字段，您要获取其值。如果要获取该字段中可用的所有字段，则可以使用以下语法 –
SELECT * FROM table_name;
例子
考虑表COMPANY 的记录如下 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它将获取 CUSTOMERS 表中可用客户的 ID、姓名和薪水字段 –
testdb=# SELECT ID, NAME, SALARY FROM COMPANY ;
这将产生以下结果 –
id | name | salary ----+-------+-------- 1 | Paul | 20000 2 | Allen | 15000 3 | Teddy | 20000 4 | Mark | 65000 5 | David | 85000 6 | Kim | 45000 7 | James | 10000 (7 rows)
如果要获取 CUSTOMERS 表的所有字段，请使用以下查询 –
testdb=# SELECT * FROM COMPANY;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
PostgreSQL – 运算符
什么是 PostgreSQL 中的运算符？
运算符是保留字或主要用于 PostgreSQL 语句的 WHERE 子句中的字符，以执行操作，例如比较和算术运算。
运算符用于在 PostgreSQL 语句中指定条件，并用作语句中多个条件的连接词。
- 算术运算符
- 比较运算符
- 逻辑运算符
- 按位运算符
PostgreSQL 算术运算符
假设变量a 为2，变量b 为3，然后 –
| Operator | 描述 | 例子 |
|---|---|---|
| + | 加法 – 在运算符的任一侧添加值 | a + b 将给 5 |
| – | 减法 – 从左手操作数中减去右手操作数 | a – b 会给 -1 |
| * | 乘法 – 将运算符两边的值相乘 | a * b 将给出 6 |
| / | 除法 – 将左手操作数除以右手操作数 | b / a 会给 1 |
| % | 模数 – 将左手操作数除以右手操作数并返回余数 | b % a 会给 1 |
| ^ | 指数 – 这给出了右手操作数的指数值 | a ^ b 将给 8 |
| |/ | 平方根 | // 25.0 会给 5 |
| ||/ | 立方根 | ||/ 27.0 将给出 3 |
| ! | 阶乘 | 5 ！会给 120 |
| !! | 阶乘（前缀运算符） | !! 5 会给 120 |
PostgreSQL 比较运算符
假设变量 a 为 10，变量 b 为 20，然后 –
| Operator | 描述 | 例子 |
|---|---|---|
| = | 检查两个操作数的值是否相等，如果是，则条件为真。 | (a = b) 不正确。 |
| != | 检查两个操作数的值是否相等，如果值不相等则条件为真。 | (a != b) 是真的。 |
| <> | 检查两个操作数的值是否相等，如果值不相等则条件为真。 | (a <> b) 是真的。 |
| > | 检查左操作数的值是否大于右操作数的值，如果是，则条件为真。 | (a > b) 不正确。 |
| < | 检查左操作数的值是否小于右操作数的值，如果是，则条件为真。 | (a < b) 是真的。 |
| >= | 检查左操作数的值是否大于或等于右操作数的值，如果是则条件成立。 | (a >= b) 不是真的。 |
| <= | 检查左操作数的值是否小于或等于右操作数的值，如果是则条件成立。 | (a <= b) 是真的。 |
PostgreSQL 逻辑运算符
这是 PostgresSQL 中可用的所有逻辑运算符的列表。
| S. No. | 运算符和描述 |
|---|---|
| 1 |
AND AND 运算符允许在 PostgresSQL 语句的 WHERE 子句中存在多个条件。 |
| 2 |
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator. |
| 3 |
OR OR 运算符用于在 PostgresSQL 语句的 WHERE 子句中组合多个条件。 |
PostgreSQL 位串运算符
按位运算符作用于位并执行逐位运算。& 和 | 的真值表 如下 –
| p | q | p&q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
假设如果 A = 60; B = 13；现在以二进制格式，它们将如下 –
A = 0011 1100
乙 = 0000 1101
—————–
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
PostgreSQL 支持的按位运算符列于下表中 –
| Operator | 描述 | 例子 |
|---|---|---|
| & | 如果两个操作数中都存在二进制 AND 运算符，则将其复制到结果中。 | (A & B) 将给出 12，即 0000 1100 |
| | | 如果任一操作数中存在二进制 OR 运算符，则复制一点。 | (A | B) 将给出 61，即 0011 1101 |
| ~ | Binary Ones Complement Operator 是一元的，具有“翻转”位的效果。 | (~A ) 将给出 -61，它是 1100 0011 由于带符号的二进制数，以 2 的补码形式表示。 |
| << | 二元左移运算符。左操作数值向左移动右操作数指定的位数。 | A << 2 将给出 240，即 1111 0000 |
| >> | 二元右移运算符。左操作数的值向右移动右操作数指定的位数。 | A >> 2 将给出 15，即 0000 1111 |
| # | 按位异或。 | A # B 将给出 49，即 0100 1001 |
PostgreSQL – 表达式
表达式是一个或多个值、运算符和计算结果的 PostgresSQL 函数的组合。
PostgreSQL EXPRESSIONS 就像公式一样，它们是用查询语言编写的。您还可以使用查询数据库中的特定数据集。
句法
考虑 SELECT 语句的基本语法如下 –
SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];
有不同类型的 PostgreSQL 表达式，如下所述 –
PostgreSQL – 布尔表达式
PostgreSQL 布尔表达式根据匹配的单个值获取数据。以下是语法 –
SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
这是一个简单的例子，展示了 PostgreSQL 布尔表达式的用法 –
testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+----------+-------- 7 | James | 24 | Houston | 10000 (1 row)
PostgreSQL – 数值表达式
这些表达式用于在任何查询中执行任何数学运算。以下是语法 –
SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;
这里 numeric_expression 用于数学表达式或任何公式。以下是一个简单的例子，展示了 SQL 数字表达式的用法 –
testdb=# SELECT (15 + 6) AS ADDITION ;
上面给出的 PostgreSQL 语句将产生以下结果 –
addition
----------
21
(1 row)
有几个内置函数，例如 avg()、sum()、count()，可以对表或特定表列执行所谓的聚合数据计算。
testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
上面给出的 PostgreSQL 语句将产生以下结果 –
RECORDS
---------
7
(1 row)
PostgreSQL – 日期表达式
日期表达式返回当前系统日期和时间值，这些表达式用于各种数据操作。
testdb=# SELECT CURRENT_TIMESTAMP;
上面给出的 PostgreSQL 语句将产生以下结果 –
now ------------------------------- 2013-05-06 14:38:28.078+05:30 (1 row)
PostgreSQL – WHERE 子句
PostgreSQL WHERE 子句用于在从单个表中获取数据或连接多个表时指定条件。
如果满足给定条件，则仅从表中返回特定值。您可以使用 WHERE 子句过滤掉不想包含在结果集中的行。
WHERE 子句不仅用在 SELECT 语句中，还用在 UPDATE、DELETE 语句等中，我们将在后续章节中进行研究。
句法
带有 WHERE 子句的 SELECT 语句的基本语法如下 –
SELECT column1, column2, columnN FROM table_name WHERE [search_condition]
您可以使用比较或逻辑运算符指定search_condition 。像>、<、=、LIKE、NOT 等。下面的例子将使这个概念变得清晰。
例子
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是显示 PostgreSQL 逻辑运算符用法的简单示例。以下SELECT语句会列出所有的记录，其中年龄大于或等于25和薪水大于或等于65000.00 –
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows)
以下 SELECT 语句列出 AGE 大于或等于 25或工资大于或等于 65000.00 的所有记录 –
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (4 rows)
以下 SELECT 语句列出了所有 AGE 不为 NULL 的记录，这意味着所有记录，因为没有一条记录的 AGE 等于 NULL –
testdb=# SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下 SELECT 语句列出了 NAME 以 ‘Pa’ 开头的所有记录，与 ‘Pa’ 之后的内容无关。
testdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age |address | salary ----+------+-----+-----------+-------- 1 | Paul | 32 | California| 20000
以下 SELECT 语句列出了 AGE 值为 25 或 27 的所有记录 –
testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows)
以下 SELECT 语句列出了 AGE 值既不是 25 也不是 27 的所有记录 –
testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (4 rows)
以下 SELECT 语句列出了 AGE 值在 BETWEEN 25 AND 27 之间的所有记录 –
testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows)
以下 SELECT 语句使用 SQL 子查询，其中子查询查找 AGE 字段具有 SALARY > 65000 的所有记录，随后将 WHERE 子句与 EXISTS 运算符一起使用，以列出返回结果中存在来自外部查询的 AGE 的所有记录通过子查询 –
testdb=# SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
上面给出的 PostgreSQL 语句将产生以下结果 –
age ----- 32 25 23 25 27 22 24 (7 rows)
以下 SELECT 语句使用 SQL 子查询，其中子查询查找 AGE 字段具有 SALARY > 65000 的所有记录，随后使用 WHERE 子句与 > 运算符一起列出所有来自外部查询的 AGE 大于中的年龄的记录子查询返回的结果 –
testdb=# SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+------+-----+------------+-------- 1 | Paul | 32 | California | 20000
AND 和 OR 连接运算符
PostgreSQL AND和OR运算符用于组合多个条件以缩小 PostgreSQL 语句中的选定数据的范围。这两个运算符称为连接运算符。
这些运算符提供了一种在同一 PostgreSQL 语句中与不同运算符进行多次比较的方法。
AND 运算符
在与运营商允许多个条件在PostgreSQL的声明中存在的WHERE子句。使用 AND 运算符时，当所有条件都为真时，将假定完整条件为真。例如，只有当条件 1 和条件 2 都为真时，[条件 1] 和 [条件 2] 才会为真。
句法
带有 WHERE 子句的 AND 运算符的基本语法如下 –
SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];
您可以使用 AND 运算符组合 N 个条件。对于 PostgreSQL 语句要执行的操作，无论是事务还是查询，由 AND 分隔的所有条件都必须为 TRUE。
例子
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
下面的SELECT语句列出了所有在那里的年龄大于或等于25的记录和工资大于或等于65000.00 –
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows)
OR 运算符
OR 运算符还用于在 PostgreSQL 语句的 WHERE 子句中组合多个条件。使用 OR 运算符时，当至少有任何条件为真时，将假定完整条件为真。例如，如果条件 1 或条件 2 为真，则 [条件 1] 或 [条件 2] 将为真。
句法
带有 WHERE 子句的 OR 运算符的基本语法如下 –
SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]
您可以使用 OR 运算符组合 N 个条件。对于 PostgreSQL 语句要执行的操作，无论是事务还是查询，只有任何一个由 OR 分隔的条件必须为 TRUE。
例子
考虑具有以下记录的COMPANY表 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下 SELECT 语句列出 AGE 大于或等于 25或工资大于或等于 65000.00 的所有记录 –
testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (4 rows)
PostgreSQL – 更新查询
PostgreSQL UPDATE Query 用于修改表中的现有记录。您可以使用 WHERE 子句和 UPDATE 查询来更新选定的行。否则，所有行都将被更新。
句法
带有 WHERE 子句的 UPDATE 查询的基本语法如下：
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
您可以使用 AND 或 OR 运算符组合 N 个条件。
例子
考虑表COMPANY，其记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它将为 ID 为 6 的客户更新 ADDRESS –
testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
现在，COMPANY 表将有以下记录 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 3 | Teddy | 23 | Norway | 15000 (7 rows)
如果要修改 COMPANY 表中的所有 ADDRESS 和 SALARY 列值，则不需要使用 WHERE 子句，UPDATE 查询如下 –
testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;
现在，COMPANY 表将包含以下记录 –
id | name | age | address | salary ----+-------+-----+---------+-------- 1 | Paul | 32 | Texas | 20000 2 | Allen | 25 | Texas | 20000 4 | Mark | 25 | Texas | 20000 5 | David | 27 | Texas | 20000 6 | Kim | 22 | Texas | 20000 7 | James | 24 | Texas | 20000 3 | Teddy | 23 | Texas | 20000 (7 rows)
PostgreSQL – 删除查询
PostgreSQL DELETE查询用于从表中删除现有记录。您可以使用 WHERE 子句和 DELETE 查询来删除选定的行。否则，所有记录都将被删除。
句法
带有 WHERE 子句的 DELETE 查询的基本语法如下 –
DELETE FROM table_name WHERE [condition];
您可以使用 AND 或 OR 运算符组合 N 个条件。
例子
考虑表COMPANY，其记录如下 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它将删除 ID 为 7 的客户 –
testdb=# DELETE FROM COMPANY WHERE ID = 2;
现在，COMPANY 表将包含以下记录 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (6 rows)
如果要删除 COMPANY 表中的所有记录，则不需要在 DELETE 查询中使用 WHERE 子句，如下所示 –
testdb=# DELETE FROM COMPANY;
现在，COMPANY 表没有任何记录，因为所有记录都已被 DELETE 语句删除。
PostgreSQL – LIKE 子句
PostgreSQL LIKE运算符用于使用通配符将文本值与模式匹配。如果搜索表达式可以与模式表达式匹配，则 LIKE 运算符将返回 true，即1。
有两个通配符与 LIKE 运算符结合使用 –
- 百分号 (%)
- 下划线 (_)
百分号代表零、一个或多个数字或字符。下划线代表单个数字或字符。这些符号可以组合使用。
如果这两个符号中的任何一个未与 LIKE 子句结合使用，则 LIKE 的作用类似于等号运算符。
句法
% 和 _ 的基本语法如下 –
SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX_' or SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE '_XXXX_'
您可以使用 AND 或 OR 运算符组合 N 个条件。这里 XXXX 可以是任何数字或字符串值。
例子
以下是一些示例，显示 WHERE 部分具有不同的 LIKE 子句，带有“%”和“_”运算符 –
| S. No. | 声明和说明 |
|---|---|
| 1 |
WHERE SALARY::text LIKE ‘200%’ 查找任何以 200 开头的值 |
| 2 |
WHERE SALARY::text LIKE ‘%200%’ 查找在任何位置具有 200 的任何值 |
| 3 |
WHERE SALARY::text LIKE ‘_00%’ 查找第二个和第三个位置为 00 的任何值 |
| 4 |
WHERE SALARY::text LIKE ‘2_%_%’ 查找任何以 2 开头且长度至少为 3 个字符的值 |
| 5 |
WHERE SALARY::text LIKE ‘%2’ 查找任何以 2 结尾的值 |
| 6 |
WHERE SALARY::text LIKE ‘_2%3’ 查找第二个位置为 2 并以 3 结尾的任何值 |
| 7 |
WHERE SALARY::text LIKE ‘2___3’ 查找以 2 开头并以 3 结尾的五位数字中的任何值 |
Postgres LIKE 只是字符串比较。因此，我们需要像上面的例子一样显式地将整数列转换为字符串。
让我们举一个真实的例子，考虑表COMPANY，其记录如下 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它将显示 COMPANY 表中 AGE 以 2 开头的所有记录 –
testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 (7 rows)
以下是一个示例，它将显示 COMPANY 表中的所有记录，其中 ADDRESS 将在文本中包含一个连字符 (-) –
testdb=# SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';
这将产生以下结果 –
id | name | age | address | salary ----+------+-----+-------------------------------------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 (2 rows)
PostgreSQL – 限制条款
PostgreSQL LIMIT子句用于限制SELECT 语句返回的数据量。
句法
带有 LIMIT 子句的 SELECT 语句的基本语法如下 –
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
以下是 LIMIT 子句与 OFFSET 子句一起使用时的语法 –
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
LIMIT 和 OFFSET 允许您仅检索由查询的其余部分生成的行的一部分。
例子
考虑表COMPANY 的记录如下 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它根据要从表中提取的行数限制表中的行 –
testdb=# SELECT * FROM COMPANY LIMIT 4;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 (4 rows)
但是，在某些情况下，您可能需要从特定偏移量中获取一组记录。这是一个示例，它从第三个位置开始获取三个记录 –
testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (3 rows)
PostgreSQL – ORDER BY 子句
PostgreSQL ORDER BY子句用于根据一列或多列按升序或降序对数据进行排序。
句法
ORDER BY 子句的基本语法如下 –
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
您可以在 ORDER BY 子句中使用多个列。确保您使用的任何列进行排序，该列应该在列列表中可用。
例子
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它将按 SALARY 按升序对结果进行排序 –
testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 6 | Kim | 22 | South-Hall | 45000 3 | Teddy | 23 | Norway | 20000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 4 | Mark | 25 | Rich-Mond | 65000 2 | Allen | 25 | Texas | 15000 5 | David | 27 | Texas | 85000 1 | Paul | 32 | California | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
以下是一个示例，它将按 NAME 和 SALARY 按升序对结果进行排序 –
testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+--------------+-------- 2 | Allen | 25 | Texas | 15000 5 | David | 27 | Texas | 85000 10 | James | 45 | Texas | 5000 9 | James | 44 | Norway | 5000 7 | James | 24 | Houston | 10000 6 | Kim | 22 | South-Hall | 45000 4 | Mark | 25 | Rich-Mond | 65000 1 | Paul | 32 | California | 20000 8 | Paul | 24 | Houston | 20000 3 | Teddy | 23 | Norway | 20000 (10 rows)
以下是一个示例，它将按 NAME 按降序对结果进行排序 –
testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 3 | Teddy | 23 | Norway | 20000 1 | Paul | 32 | California | 20000 8 | Paul | 24 | Houston | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 5 | David | 27 | Texas | 85000 2 | Allen | 25 | Texas | 15000 (10 rows)
PostgreSQL – GROUP BY
PostgreSQL GROUP BY子句与 SELECT 语句配合使用，将表中具有相同数据的行组合在一起。这样做是为了消除适用于这些组的输出和/或计算聚合中的冗余。
GROUP BY 子句在 SELECT 语句中的 WHERE 子句之后，并在 ORDER BY 子句之前。
句法
下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须跟在 WHERE 子句中的条件之后，并且必须在 ORDER BY 子句之前（如果使用）。
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
您可以在 GROUP BY 子句中使用多个列。确保您使用的任何列进行分组，该列应该在列列表中可用。
例子
考虑表COMPANY 的记录如下 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
如果您想知道每个客户的工资总额，那么 GROUP BY 查询将如下 –
testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
这将产生以下结果 –
name | sum -------+------- Teddy | 20000 Paul | 20000 Mark | 65000 David | 85000 Allen | 15000 Kim | 45000 James | 10000 (7 rows)
现在，让我们使用以下 INSERT 语句在 COMPANY 表中再创建三个记录 –
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
现在，我们的表有以下重复名称的记录 –
id | name | age | address | salary ----+-------+-----+--------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
同样，让我们使用相同的语句对使用 NAME 列的所有记录进行分组，如下所示 –
testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
这将产生以下结果 –
name | sum -------+------- Allen | 15000 David | 85000 James | 20000 Kim | 45000 Mark | 65000 Paul | 40000 Teddy | 20000 (7 rows)
让我们使用 ORDER BY 子句和 GROUP BY 子句如下 –
testdb=# SELECT NAME, SUM(SALARY)
FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
这将产生以下结果 –
name | sum -------+------- Teddy | 20000 Paul | 40000 Mark | 65000 Kim | 45000 James | 20000 David | 85000 Allen | 15000 (7 rows)
PostgreSQL – WITH 子句
在 PostgreSQL 中，WITH 查询提供了一种编写辅助语句以用于更大查询的方法。它有助于将复杂的大型查询分解为更易于阅读的简单形式。这些语句通常被称为公共表表达式或 CTE，可以被认为是定义了只为一个查询而存在的临时表。
WITH 查询是 CTE 查询，在多次执行子查询时特别有用。它同样有助于代替临时表。它计算一次聚合，并允许我们在查询中通过其名称（可能多次）引用它。
WITH 子句必须在查询中使用之前定义。
句法
WITH查询的基本语法如下 –
WITH
name_for_summary_data AS (
SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
SELECT column
FROM name_for_summary_data)
[ORDER BY columns]
其中name_for_summary_data是给 WITH 子句的名称。name_for_summary_data 可以与现有表名相同，并优先。
您可以在 WITH 中使用数据修改语句（INSERT、UPDATE 或 DELETE）。这允许您在同一个查询中执行多个不同的操作。
递归 WITH
递归 WITH 或分层查询是 CTE 的一种形式，其中 CTE 可以引用自身，即 WITH 查询可以引用其自己的输出，因此名称递归。
例子
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
现在，让我们使用 WITH 子句编写一个查询来从上表中选择记录，如下所示 –
With CTE AS (Select ID , NAME , AGE , ADDRESS , SALARY FROM COMPANY ) Select * From CTE;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
现在，让我们使用 RECURSIVE 关键字和 WITH 子句编写一个查询，以查找小于 20000 的工资总和，如下所示 –
WITH RECURSIVE t(n) AS ( VALUES (0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY < 20000 ) SELECT sum(n) FROM t;
上面给出的 PostgreSQL 语句将产生以下结果 –
sum ------- 25000 (1 row)
让我们使用数据修改语句和 WITH 子句编写一个查询，如下所示。
首先，创建一个类似于表 COMPANY 的表 COMPANY1。示例中的查询有效地将行从 COMPANY 移动到 COMPANY1。WITH 中的 DELETE 从 COMPANY 中删除指定的行，通过其 RETURNING 子句返回它们的内容；然后主查询读取该输出并将其插入 COMPANY1 TABLE –
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
WITH moved_rows AS (
DELETE FROM COMPANY
WHERE
SALARY >= 30000
RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);
上面给出的 PostgreSQL 语句将产生以下结果 –
INSERT 0 3
现在，表 COMPANY 和 COMPANY1 中的记录如下 –
testdb=# SELECT * FROM COMPANY; id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 7 | James | 24 | Houston | 10000 (4 rows) testdb=# SELECT * FROM COMPANY1; id | name | age | address | salary ----+-------+-----+-------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 (3 rows)
PostgreSQL – HAVING 子句
HAVING 子句允许我们挑选出函数结果满足某些条件的特定行。
WHERE 子句在选定的列上放置条件，而 HAVING 子句在由 GROUP BY 子句创建的组上放置条件。
句法
以下是 SELECT 查询中 HAVING 子句的位置 –
SELECT FROM WHERE GROUP BY HAVING ORDER BY
HAVING 子句必须跟在查询中的 GROUP BY 子句之后，并且如果使用，还必须在 ORDER BY 子句之前。以下是 SELECT 语句的语法，包括 HAVING 子句 –
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
例子
考虑表COMPANY 的记录如下 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是一个示例，它将显示名称计数小于 2 的记录 –
testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
这将产生以下结果 –
name ------- Teddy Paul Mark David Allen Kim James (7 rows)
现在，让我们使用以下 INSERT 语句在 COMPANY 表中再创建三个记录 –
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
现在，我们的表有以下重复名称的记录 –
id | name | age | address | salary ----+-------+-----+--------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
以下是示例，它将显示名称计数大于 1 的记录 –
testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
这将产生以下结果 –
name ------- Paul James (2 rows)
PostgreSQL – DISTINCT 关键字
PostgreSQL DISTINCT关键字与 SELECT 语句结合使用以消除所有重复记录并仅获取唯一记录。
可能会出现一个表中有多个重复记录的情况。在获取此类记录时，只获取唯一记录而不是获取重复记录更有意义。
句法
DISTINCT 关键字消除重复记录的基本语法如下 –
SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]
例子
考虑表COMPANY 的记录如下 –
# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
让我们再向该表中添加两条记录，如下所示 –
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (8, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (9, 'Allen', 25, 'Texas', 15000.00 );
现在，COMPANY 表中的记录将是 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 32 | California | 20000 9 | Allen | 25 | Texas | 15000 (9 rows)
首先，让我们看看以下 SELECT 查询如何返回重复的工资记录 –
testdb=# SELECT name FROM COMPANY;
这将产生以下结果 –
name ------- Paul Allen Teddy Mark David Kim James Paul Allen (9 rows)
现在，让我们在上面的 SELECT 查询中使用DISTINCT关键字并查看结果 –
testdb=# SELECT DISTINCT name FROM COMPANY;
如果我们没有任何重复条目，这将产生以下结果 –
name ------- Teddy Paul Mark David Allen Kim James (7 rows)
PostgreSQL – 约束
约束是对表上的数据列强制执行的规则。这些用于防止将无效数据输入到数据库中。这保证了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅应用于一列，而表级约束应用于整个表。为列定义数据类型本身就是一个约束。例如，类型为 DATE 的列将列限制为有效日期。
以下是 PostgreSQL 中可用的常用约束。
-
NOT NULL Constraint – 确保列不能有 NULL 值。
-
UNIQUE Constraint – 确保列中的所有值都不同。
-
PRIMARY Key – 唯一标识数据库表中的每一行/记录。
-
FOREIGN Key – 基于其他表中的列约束数据。
-
CHECK Constraint – CHECK 约束确保列中的所有值都满足某些条件。
-
EXCLUSION Constraint – EXCLUDE 约束确保如果使用指定的运算符在指定的列或表达式上比较任何两行，并非所有这些比较都将返回 TRUE。
非空约束
默认情况下，列可以包含 NULL 值。如果您不希望某列具有 NULL 值，则需要在此列上定义此类约束，指定该列现在不允许使用 NULL。NOT NULL 约束始终写为列约束。
NULL 与没有数据不同；相反，它代表未知数据。
例子
例如，以下 PostgreSQL 语句创建一个名为 COMPANY1 的新表并添加五列，其中三列，ID 和 NAME 和 AGE，指定不接受 NULL 值 –
CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
唯一约束
UNIQUE 约束防止两个记录在特定列中具有相同的值。例如，在 COMPANY 表中，您可能希望防止两个或更多人的年龄相同。
例子
例如，以下 PostgreSQL 语句创建一个名为 COMPANY3 的新表并添加五列。在这里，AGE 列设置为 UNIQUE，因此您不能有两个相同年龄的记录 –
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。可以有更多 UNIQUE 列，但一张表中只有一个主键。在设计数据库表时，主键很重要。主键是唯一的 ID。
我们使用它们来引用表行。在表之间创建关系时，主键成为其他表中的外键。由于“长期的编码监督”，SQLite 中的主键可以为 NULL。其他数据库不是这种情况
主键是表中的一个字段，它唯一标识了数据库表中的每一行/记录。主键必须包含唯一值。主键列不能有 NULL 值。
一张表只能有一个主键，主键可以由单个或多个字段组成。当多个字段用作主键时，它们被称为复合键。
如果表在任何字段上定义了主键，则不能有两个记录具有该字段的相同值。
例子
您已经在上面看到了各种示例，其中我们创建了以 ID 作为主键的 COMAPNY4 表 –
CREATE TABLE COMPANY4( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
外键约束
外键约束指定列（或一组列）中的值必须与出现在另一个表的某行中的值匹配。我们说这维护了两个相关表之间的引用完整性。它们被称为外键，因为约束是外键；也就是表外。外键有时称为引用键。
例子
例如，以下 PostgreSQL 语句创建一个名为 COMPANY5 的新表并添加五列。
CREATE TABLE COMPANY6( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
例如，以下 PostgreSQL 语句创建一个名为 DEPARTMENT1 的新表，它添加了三列。EMP_ID 列是外键并引用表 COMPANY6 的 ID 字段。
CREATE TABLE DEPARTMENT1( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT references COMPANY6(ID) );
检查约束
CHECK 约束使条件能够检查输入到记录中的值。如果条件评估为假，则记录违反约束并且不会输入到表中。
例子
例如，以下 PostgreSQL 语句创建一个名为 COMPANY5 的新表并添加五列。在这里，我们添加了一个带有 SALARY 列的 CHECK，因此您不能将任何 SALARY 设为零。
CREATE TABLE COMPANY5( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
排除约束
排除约束确保如果使用指定的运算符在指定的列或表达式上比较任何两行，这些运算符比较中的至少一个将返回 false 或 null。
例子
例如，以下 PostgreSQL 语句创建一个名为 COMPANY7 的新表并添加五列。在这里，我们添加一个 EXCLUDE 约束 –
CREATE TABLE COMPANY7( ID INT PRIMARY KEY NOT NULL, NAME TEXT, AGE INT , ADDRESS CHAR(50), SALARY REAL, EXCLUDE USING gist (NAME WITH =, AGE WITH <>) );
在这里，USING gist是要构建和用于强制执行的索引类型。
您需要执行命令CREATE EXTENSION btree_gist，每个数据库一次。这将安装 btree_gist 扩展，它定义了对纯标量数据类型的排除约束。
由于我们强制年龄必须相同，让我们通过向表中插入记录来查看这一点 –
INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 ); INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );
对于前两个 INSERT 语句，记录被添加到 COMPANY7 表中。对于第三个 INSERT 语句，显示以下错误 –
ERROR: conflicting key value violates exclusion constraint "company7_name_age_excl" DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).
删除约束
要删除约束，您需要知道其名称。如果名称已知，则很容易删除。否则，您需要找出系统生成的名称。psql 命令 \d table name 在这里很有帮助。一般语法是 –
ALTER TABLE table_name DROP CONSTRAINT some_name;
PostgreSQL – 连接
PostgreSQL Joins子句用于组合来自数据库中两个或多个表的记录。JOIN 是一种通过使用每个表的公共值来组合来自两个表的字段的方法。
PostgreSQL 中的连接类型是 –
- 交叉连接
- 内连接
- 左外连接
- 正确的外部连接
- 完整的外连接
在我们继续之前，让我们考虑两个表，COMPANY 和 DEPARTMENT。我们已经看到 INSERT 语句填充 COMPANY 表。因此，让我们假设 COMPANY 表中可用的记录列表 –
id | name | age | address | salary | join_date ----+-------+-----+-----------+--------+----------- 1 | Paul | 32 | California| 20000 | 2001-07-13 3 | Teddy | 23 | Norway | 20000 | 4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13 5 | David | 27 | Texas | 85000 | 2007-12-13 2 | Allen | 25 | Texas | | 2007-12-13 8 | Paul | 24 | Houston | 20000 | 2005-07-13 9 | James | 44 | Norway | 5000 | 2005-07-13 10 | James | 45 | Texas | 5000 | 2005-07-13
另一个表是 DEPARTMENT，具有以下定义 –
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
这是填充 DEPARTMENT 表的 INSERT 语句列表 –
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
最后，我们在 DEPARTMENT 表中有以下可用记录列表 –
id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7
交叉连接
CROSS JOIN 将第一个表的每一行与第二个表的每一行进行匹配。如果输入表分别具有 x 和 y 列，则结果表将具有 x+y 列。因为 CROSS JOIN 有可能生成非常大的表，所以必须注意仅在适当的时候使用它们。
以下是 CROSS JOIN 的语法 –
SELECT ... FROM table1 CROSS JOIN table2 ...
基于上表，我们可以编写一个 CROSS JOIN 如下 –
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
上面给出的查询将产生以下结果 –
emp_id| name | dept
------|-------|--------------
1 | Paul | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Allen | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Allen | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
7 | Teddy | Finance
7 | Mark | Finance
7 | David | Finance
7 | Allen | Finance
7 | Paul | Finance
7 | James | Finance
7 | James | Finance
内连接
内部联接通过基于联接谓词组合两个表（表 1 和表 2）的列值来创建新的结果表。该查询将 table1 的每一行与 table2 的每一行进行比较，以找到满足连接谓词的所有行对。当满足连接谓词时，table1 和 table2 的每对匹配的行的列值组合成一个结果行。
INNER JOIN 是最常见的连接类型，也是默认的连接类型。您可以选择使用 INNER 关键字。
以下是 INNER JOIN 的语法 –
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
基于上表，我们可以编写一个 INNER JOIN 如下 –
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面给出的查询将产生以下结果 –
emp_id | name | dept
--------+-------+------------
1 | Paul | IT Billing
2 | Allen | Engineering
左外连接
OUTER JOIN 是 INNER JOIN 的扩展。SQL 标准定义了三种类型的 OUTER JOIN：LEFT、RIGHT 和 FULL，PostgreSQL 支持所有这些。
在 LEFT OUTER JOIN 的情况下，首先执行内部连接。然后，对于表 T1 中不满足与表 T2 中任何行的连接条件的每一行，在 T2 的列中添加一个带有空值的连接行。因此，连接表对于 T1 中的每一行总是至少有一行。
以下是 LEFT OUTER JOIN 的语法 –
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
基于上表，我们可以编写一个内部连接如下 –
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面给出的查询将产生以下结果 –
emp_id | name | dept
--------+-------+------------
1 | Paul | IT Billing
2 | Allen | Engineering
| James |
| David |
| Paul |
| Mark |
| Teddy |
| James |
正确的外部连接
首先，执行内部联接。然后，对于表 T2 中不满足与表 T1 中任何行的连接条件的每一行，在 T1 的列中添加一个带有空值的连接行。这与左连接相反；结果表将始终为 T2 中的每一行都有一行。
以下是 RIGHT OUTER JOIN 的语法 –
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
基于上表，我们可以编写一个内部连接如下 –
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面给出的查询将产生以下结果 –
emp_id | name | dept
--------+-------+--------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | | Finance
完整的外连接
首先，执行内部联接。然后，对于表 T1 中不满足与表 T2 中任何行的连接条件的每一行，在 T2 的列中添加一个带有空值的连接行。此外，对于T2的每一行不满足与T1中任何一行的连接条件，添加一个在T1的列中为空值的连接行。
以下是 FULL OUTER JOIN 的语法 –
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
基于上表，我们可以编写一个内部连接如下 –
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上面给出的查询将产生以下结果 –
emp_id | name | dept
--------+-------+---------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | | Finance
| James |
| David |
| Paul |
| Mark |
| Teddy |
| James |
PostgreSQL – UNIONS 子句
PostgreSQL UNION子句/运算符用于组合两个或多个 SELECT 语句的结果而不返回任何重复的行。
要使用 UNION，每个 SELECT 必须选择相同数量的列、相同数量的列表达式、相同数据类型，并且它们的顺序相同但长度不必相同。
句法
UNION的基本语法如下 –
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
在这里，给定条件可以是基于您的要求的任何给定表达式。
例子
考虑以下两个表，(a) COMPANY表如下 –
testdb=# SELECT * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
(b) 另一个表是DEPARTMENT，如下所示 –
testdb=# SELECT * from DEPARTMENT; id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7 4 | Engineering | 3 5 | Finance | 4 6 | Engineering | 5 7 | Finance | 6 (7 rows)
现在让我们使用 SELECT 语句和 UNION 子句连接这两个表，如下所示 –
testdb=# 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;
这将产生以下结果 –
emp_id | name | dept
--------+-------+--------------
5 | David | Engineering
6 | Kim | Finance
2 | Allen | Engineering
3 | Teddy | Engineering
4 | Mark | Finance
1 | Paul | IT Billing
7 | James | Finance
(7 rows)
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 语句中加入上述两个表，如下所示 –
testdb=# 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;
这将产生以下结果 –
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
(14 rows)
PostgreSQL – NULL 值
PostgreSQL NULL是用于表示缺失值的术语。表中的 NULL 值是字段中显示为空白的值。
具有 NULL 值的字段是没有值的字段。了解 NULL 值不同于零值或包含空格的字段非常重要。
句法
创建表时使用NULL的基本语法如下 –
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
此处，NOT NULL表示该列应始终接受给定数据类型的显式值。有两列我们没有使用 NOT NULL。因此，这意味着这些列可能为 NULL。
具有 NULL 值的字段是在记录创建期间留空的字段。
例子
NULL 值在选择数据时可能会导致问题，因为在将未知值与任何其他值进行比较时，结果始终未知且不包含在最终结果中。考虑下表，公司有以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
让我们使用 UPDATE 语句将几个可空值设置为 NULL，如下所示 –
testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
现在，COMPANY 表应该有以下记录 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | | 7 | James | 24 | | (7 rows)
接下来，让我们看看使用IS NOT NULL运算符列出所有 SALARY 不为 NULL 的记录 –
testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (5 rows)
以下是IS NULL运算符的用法 ，它将列出 SALARY 为 NULL 的所有记录 –
testdb=# SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | address | salary ----+-------+-----+---------+-------- 6 | Kim | 22 | | 7 | James | 24 | | (2 rows)
PostgreSQL – 别名语法
您可以通过提供另一个名称（称为ALIAS ）来临时重命名表或列。表别名的使用意味着重命名特定 PostgreSQL 语句中的表。重命名是临时更改，数据库中的实际表名不会更改。
列别名用于为特定的 PostgreSQL 查询重命名表的列。
句法
表别名的基本语法如下 –
SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
列别名的基本语法如下 –
SELECT column_name AS alias_name FROM table_name WHERE [condition];
例子
考虑以下两个表，(a) COMPANY表如下 –
testdb=# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
(b) 另一个表是DEPARTMENT，如下所示 –
id | dept | emp_id ----+--------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7 4 | Engineering | 3 5 | Finance | 4 6 | Engineering | 5 7 | Finance | 6 (7 rows)
现在，以下是TABLE ALIAS的用法，其中我们分别使用 C 和 D 作为 COMPANY 和 DEPARTMENT 表的别名 –
testdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
上面给出的 PostgreSQL 语句将产生以下结果 –
id | name | age | dept ----+-------+-----+------------ 1 | Paul | 32 | IT Billing 2 | Allen | 25 | Engineering 7 | James | 24 | Finance 3 | Teddy | 23 | Engineering 4 | Mark | 25 | Finance 5 | David | 27 | Engineering 6 | Kim | 22 | Finance (7 rows)
让我们看一个使用COLUMN ALIAS的例子，其中 COMPANY_ID 是 ID 列的别名，COMPANY_NAME 是名称列的别名 –
testdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
上面给出的 PostgreSQL 语句将产生以下结果 –
company_id | company_name | age | dept
------------+--------------+-----+------------
1 | Paul | 32 | IT Billing
2 | Allen | 25 | Engineering
7 | James | 24 | Finance
3 | Teddy | 23 | Engineering
4 | Mark | 25 | Finance
5 | David | 27 | Engineering
6 | Kim | 22 | Finance
(7 rows)
PostgreSQL – 触发器
PostgreSQL Triggers是数据库回调函数，当指定的数据库事件发生时会自动执行/调用。
以下是关于 PostgreSQL 触发器的要点 –
-
可以指定 PostgreSQL 触发器来触发
-
在对一行尝试操作之前（在检查约束并尝试 INSERT、UPDATE 或 DELETE 之前）
-
操作完成后（检查约束并完成 INSERT、UPDATE 或 DELETE 后）
-
而不是操作（在插入、更新或删除视图的情况下）
-
-
对于操作修改的每一行，都会调用一次标记为 FOR EACH ROW 的触发器。相比之下，标记为 FOR EACH STATEMENT 的触发器只对任何给定操作执行一次，而不管它修改了多少行。
-
WHEN 子句和触发器操作都可以使用NEW.column-name和OLD.column-name形式的引用访问被插入、删除或更新的行的元素，其中 column-name 是来自触发器关联的表。
-
如果提供了 WHEN 子句，则指定的 PostgreSQL 语句仅对 WHEN 子句为真的行执行。如果未提供 WHEN 子句，则对所有行执行 PostgreSQL 语句。
-
如果为同一个事件定义了多个相同类型的触发器，它们将按名称的字母顺序触发。
-
BEFORE、AFTER 或 INSTEAD OF 关键字确定何时执行与插入、修改或删除关联行相关的触发器操作。
-
当与触发器关联的表被删除时，触发器会自动删除。
-
要修改的表必须与触发器附加到的表或视图存在于同一数据库中，并且必须仅使用tablename，而不是database.tablename。
-
CONSTRAINT 选项在指定时创建一个约束触发器。这与常规触发器相同，只是触发器触发的时间可以使用 SET CONSTRAINTS 进行调整。当约束触发器实现的约束被违反时，约束触发器预计会引发异常。
句法
创建触发器的基本语法如下 –
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ];
这里，event_name可以是对上述表table_name 的INSERT、DELETE、 UPDATE和TRUNCATE数据库操作。您可以选择在表名后指定 FOR EACH ROW。
以下是在表的一个或多个指定列上的 UPDATE 操作上创建触发器的语法，如下所示 –
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name [ -- Trigger logic goes here.... ];
例子
让我们考虑一种情况，我们希望对插入 COMPANY 表中的每条记录进行审计试验，我们将按如下方式新创建该表（如果您已经拥有，则删除 COMPANY 表）。
testdb=# CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
为了保持审计试验，我们将创建一个名为 AUDIT 的新表，只要在 COMPANY 表中有新记录的条目，就会插入日志消息 –
testdb=# CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL );
这里，ID 是 AUDIT 记录 ID，EMP_ID 是 ID，它来自 COMPANY 表，DATE 将保留在 COMPANY 表中创建记录时的时间戳。现在，让我们在 COMPANY 表上创建一个触发器，如下所示 –
testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
其中 auditlogfunc() 是一个 PostgreSQL过程并具有以下定义 –
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;
现在，我们将开始实际工作。让我们开始在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建审计日志记录。所以让我们在 COMPANY 表中创建一个记录如下 –
testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
这将在 COMPANY 表中创建一条记录，如下所示 –
id | name | age | address | salary ----+------+-----+--------------+-------- 1 | Paul | 32 | California | 20000
同时，AUDIT 表中将创建一条记录。该记录是触发器的结果，我们在 COMPANY 表上的 INSERT 操作中创建了该触发器。同样，您可以根据您的要求在 UPDATE 和 DELETE 操作上创建触发器。
emp_id | entry_date
--------+-------------------------------
1 | 2013-05-05 15:49:59.968+05:30
(1 row)
列出触发器
您可以从pg_trigger表中列出当前数据库中的所有触发器，如下所示 –
testdb=# SELECT * FROM pg_trigger;
上面给出的 PostgreSQL 语句将列出所有触发器。
如果要列出特定表上的触发器，请使用带有表名的 AND 子句，如下所示 –
testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';
上面给出的 PostgreSQL 语句也将只列出一个条目，如下所示 –
tgname ----------------- example_trigger (1 row)
丢弃触发器
以下是 DROP 命令，可用于删除现有触发器 –
testdb=# DROP TRIGGER trigger_name;
PostgreSQL – 索引
索引是数据库搜索引擎可以用来加速数据检索的特殊查找表。简单地说，索引是指向表中数据的指针。数据库中的索引与书后的索引非常相似。
例如，如果您想参考一本书中讨论某个主题的所有页面，您必须首先参考索引，该索引按字母顺序列出所有主题，然后参考一个或多个特定页码。
索引有助于加速 SELECT 查询和 WHERE 子句；但是，它会通过 UPDATE 和 INSERT 语句减慢数据输入速度。可以在不影响数据的情况下创建或删除索引。
创建索引涉及 CREATE INDEX 语句，它允许您命名索引、指定表和要索引的列，以及指示索引是按升序还是降序排列。
索引也可以是唯一的，类似于 UNIQUE 约束，因为索引可以防止存在索引的列或列组合中的重复条目。
CREATE INDEX 命令
CREATE INDEX的基本语法如下 –
CREATE INDEX index_name ON table_name;
索引类型
PostgreSQL 提供了几种索引类型：B-tree、Hash、GiST、SP-GiST 和 GIN。每个索引类型使用不同的算法，最适合不同类型的查询。默认情况下，CREATE INDEX 命令创建 B 树索引，它适合最常见的情况。
单列索引
单列索引是仅基于一个表列创建的索引。基本语法如下 –
CREATE INDEX index_name ON table_name (column_name);
多列索引
多列索引是在表的多列上定义的。基本语法如下 –
CREATE INDEX index_name ON table_name (column1_name, column2_name);
无论是创建单列索引还是多列索引，都要考虑在查询的 WHERE 子句中可能经常使用的列作为过滤条件。
如果只使用一列，则应该选择单列索引。如果 WHERE 子句中经常使用两列或更多列作为过滤器，则多列索引将是最佳选择。
唯一索引
唯一索引不仅用于性能，还用于数据完整性。唯一索引不允许将任何重复值插入到表中。基本语法如下 –
CREATE UNIQUE INDEX index_name on table_name (column_name);
部分索引
部分索引是建立在表子集上的索引；子集由条件表达式（称为部分索引的谓词）定义。该索引仅包含满足谓词的那些表行的条目。基本语法如下 –
CREATE INDEX index_name on table_name (conditional_expression);
隐式索引
隐式索引是在创建对象时由数据库服务器自动创建的索引。索引是为主键约束和唯一约束自动创建的。
例子
以下是我们将在COMPANY表上为工资列创建索引的示例–
# CREATE INDEX salary_index ON COMPANY (salary);
现在，让我们使用\d company命令列出 COMPANY 表上可用的所有索引。
# \d company
这将产生以下结果，其中company_pkey是一个隐式索引，它是在创建表时创建的。
Table "public.company"
Column | Type | Modifiers
---------+---------------+-----------
id | integer | not null
name | text | not null
age | integer | not null
address | character(50) |
salary | real |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
"salary_index" btree (salary)
您可以使用\di命令列出整个数据库范围内的索引–
DROP INDEX 命令
可以使用 PostgreSQL DROP命令删除索引。删除索引时应该小心，因为性能可能会降低或提高。
基本语法如下 –
DROP INDEX index_name;
您可以使用以下语句删除以前创建的索引 –
# DROP INDEX salary_index;
什么时候应该避免使用索引？
尽管索引旨在提高数据库的性能，但有时也应避免使用索引。以下指南指示何时应重新考虑使用索引 –
-
不应在小表上使用索引。
-
具有频繁、大批量更新或插入操作的表。
-
不应在包含大量 NULL 值的列上使用索引。
-
不应为频繁操作的列编制索引。
PostgreSQL – ALTER TABLE 命令
PostgreSQL ALTER TABLE命令用于在现有表中添加、删除或修改列。
您还可以使用 ALTER TABLE 命令在现有表上添加和删除各种约束。
句法
在现有表中添加新列的ALTER TABLE的基本语法如下 –
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE to DROP COLUMN在现有表中的基本语法如下 –
ALTER TABLE table_name DROP COLUMN column_name;
更改表中列的数据类型的 ALTER TABLE 的基本语法如下 –
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
向表中的列添加NOT NULL约束的 ALTER TABLE 的基本语法如下：
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
ALTER TABLE 将唯一约束添加到表的基本语法如下 –
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
ALTER TABLE 将CHECK CONSTRAINT 添加到表的基本语法如下 –
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
ALTER TABLE 将PRIMARY KEY约束添加到表的基本语法如下 –
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
从表中删除约束的 ALTER TABLE 的基本语法如下 –
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;
如果您使用的是 MySQL，代码如下 –
ALTER TABLE table_name DROP INDEX MyUniqueConstraint;
ALTER TABLE从表中删除 PRIMARY KEY约束的基本语法如下 –
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;
如果您使用的是 MySQL，代码如下 –
ALTER TABLE table_name DROP PRIMARY KEY;
例子
考虑我们的COMPANY表有以下记录 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000
以下是在现有表中添加新列的示例 –
testdb=# ALTER TABLE COMPANY ADD GENDER char(1);
现在，COMPANY 表已更改，以下将是 SELECT 语句的输出 –
id | name | age | address | salary | gender ----+-------+-----+-------------+--------+-------- 1 | Paul | 32 | California | 20000 | 2 | Allen | 25 | Texas | 15000 | 3 | Teddy | 23 | Norway | 20000 | 4 | Mark | 25 | Rich-Mond | 65000 | 5 | David | 27 | Texas | 85000 | 6 | Kim | 22 | South-Hall | 45000 | 7 | James | 24 | Houston | 10000 | (7 rows)
以下是从现有表中删除性别列的示例 –
testdb=# ALTER TABLE COMPANY DROP GENDER;
现在，COMPANY 表已更改，以下将是 SELECT 语句的输出 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000
PostgreSQL – TRUNCATE TABLE 命令
PostgreSQL TRUNCATE TABLE命令用于从现有表中删除完整数据。您也可以使用 DROP TABLE 命令删除完整的表，但它会从数据库中删除完整的表结构，如果您希望存储一些数据，则需要再次重新创建该表。
它对每个表具有与 DELETE 相同的效果，但由于它实际上并不扫描表，因此速度更快。此外，它会立即回收磁盘空间，而不需要后续的 VACUUM 操作。这在大表上最有用。
句法
TRUNCATE TABLE的基本语法如下 –
TRUNCATE TABLE table_name;
例子
考虑 COMPANY 表有以下记录 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下是截断的示例 –
testdb=# TRUNCATE TABLE COMPANY;
现在，COMPANY 表被截断，以下将是 SELECT 语句的输出 –
testdb=# SELECT * FROM CUSTOMERS; id | name | age | address | salary ----+------+-----+---------+-------- (0 rows)
PostgreSQL – 视图
视图是伪表。也就是说，它们不是真正的表；尽管如此，它还是作为普通表出现在 SELECT 中。视图可以表示真实表的子集，从普通表中选择某些列或某些行。视图甚至可以表示连接表。因为视图被分配了单独的权限，所以您可以使用它们来限制表访问，以便用户只能看到表的特定行或列。
视图可以包含表的所有行或从一个或多个表中选择的行。可以从一个或多个表创建视图，这取决于编写的 PostgreSQL 查询来创建视图。
视图是一种虚拟表，允许用户执行以下操作 –
-
以用户或用户类别认为自然或直观的方式构建数据。
-
限制对数据的访问，使用户只能看到有限的数据而不是完整的表格。
-
汇总各种表格中的数据，可用于生成报告。
由于视图不是普通表，因此您可能无法对视图执行 DELETE、INSERT 或 UPDATE 语句。但是，您可以创建一个规则来纠正在视图上使用 DELETE、INSERT 或 UPDATE 的这个问题。
创建视图
PostgreSQL 视图是使用CREATE VIEW语句创建的。可以从单个表、多个表或其他视图创建 PostgreSQL 视图。
基本的 CREATE VIEW 语法如下 –
CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
您可以以与在普通 PostgreSQL SELECT 查询中使用它们非常相似的方式在 SELECT 语句中包含多个表。如果存在可选的 TEMP 或 TEMPORARY 关键字，则视图将在临时空间中创建。在当前会话结束时自动删除临时视图。
例子
考虑一下，COMPANY表有以下记录 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000
现在，以下是从 COMPANY 表创建视图的示例。此视图将用于只有 COMPANY 表中的几列 –
testdb=# CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
现在，您可以使用与查询实际表类似的方式来查询 COMPANY_VIEW。以下是示例 –
testdb=# SELECT * FROM COMPANY_VIEW;
这将产生以下结果 –
id | name | age ----+-------+----- 1 | Paul | 32 2 | Allen | 25 3 | Teddy | 23 4 | Mark | 25 5 | David | 27 6 | Kim | 22 7 | James | 24 (7 rows)
删除视图
要删除视图，只需使用带有view_name的 DROP VIEW 语句。基本的 DROP VIEW 语法如下 –
testdb=# DROP VIEW view_name;
以下命令将删除我们在上一节中创建的 COMPANY_VIEW 视图 –
testdb=# DROP VIEW COMPANY_VIEW;
PostgreSQL – 事务
事务是针对数据库执行的工作单元。事务是按逻辑顺序完成的工作单元或序列，无论是由用户手动完成还是由某种数据库程序自动完成。
事务是对数据库的一个或多个更改的传播。例如，如果您正在创建记录、更新记录或从表中删除记录，那么您正在对表执行事务。控制事务以确保数据完整性和处理数据库错误非常重要。
实际上，您会将许多 PostgreSQL 查询组合到一个组中，并将作为事务的一部分一起执行所有这些查询。
交易属性
事务具有以下四个标准属性，通常由首字母缩写词 ACID 表示 –
-
原子性– 确保工作单元内的所有操作都成功完成；否则，事务将在故障点中止，并且之前的操作将回滚到它们之前的状态。
-
一致性– 确保数据库在成功提交的事务后正确更改状态。
-
隔离– 使交易能够独立运行且彼此透明。
-
持久性– 确保在系统故障的情况下提交的事务的结果或影响仍然存在。
交易控制
以下命令用于控制交易 –
-
BEGIN TRANSACTION – 开始交易。
-
COMMIT – 要保存更改，您也可以使用END TRANSACTION命令。
-
ROLLBACK – 回滚更改。
事务控制命令仅与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。在创建表或删除表时不能使用它们，因为这些操作是在数据库中自动提交的。
开始事务命令
事务可以使用 BEGIN TRANSACTION 或简单的 BEGIN 命令启动。此类事务通常持续到遇到下一个 COMMIT 或 ROLLBACK 命令。但是如果数据库关闭或发生错误，事务也会回滚。
以下是启动事务的简单语法 –
BEGIN; or BEGIN TRANSACTION;
提交命令
COMMIT 命令是用于将事务调用的更改保存到数据库的事务命令。
COMMIT 命令将自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库中。
COMMIT 命令的语法如下 –
COMMIT; or END TRANSACTION;
ROLLBACK 命令
ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务性命令。
ROLLBACK 命令只能用于撤消自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。
ROLLBACK 命令的语法如下 –
ROLLBACK;
例子
考虑COMPANY表有以下记录 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000
现在，让我们开始一个事务并从表中删除年龄 = 25 的记录，最后我们使用 ROLLBACK 命令撤消所有更改。
testdb=# BEGIN; DELETE FROM COMPANY WHERE AGE = 25; ROLLBACK;
如果您将检查 COMPANY 表是否仍有以下记录 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000
现在，让我们开始另一个事务并从表中删除年龄 = 25 的记录，最后我们使用 COMMIT 命令提交所有更改。
testdb=# BEGIN; DELETE FROM COMPANY WHERE AGE = 25; COMMIT;
如果您检查 COMPANY 表，它仍然有以下记录 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (5 rows)
PostgreSQL – 锁
锁或排他锁或写锁可防止用户修改一行或整个表。由 UPDATE 和 DELETE 修改的行然后在事务期间自动以独占方式锁定。这可以防止其他用户在提交或回滚事务之前更改行。
用户必须等待其他用户的唯一时间是当他们尝试修改同一行时。如果他们修改不同的行，则无需等待。SELECT 查询永远不必等待。
数据库自动执行锁定。但是，在某些情况下，必须手动控制锁定。手动锁定可以通过使用 LOCK 命令来完成。它允许指定事务的锁类型和范围。
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。
一旦获得，该锁将在当前事务的剩余部分中保持。没有 UNLOCK TABLE 命令；锁总是在事务结束时释放。
死锁
当两个事务等待对方完成它们的操作时，就会发生死锁。虽然 PostgreSQL 可以检测到它们并以 ROLLBACK 结束它们，但死锁仍然很不方便。为防止您的应用程序遇到此问题，请确保将它们设计为以相同的顺序锁定对象。
咨询锁
PostgreSQL 提供了创建具有应用程序定义含义的锁的方法。这些被称为咨询锁。由于系统不强制使用它们，因此正确使用它们取决于应用程序。咨询锁对于不适合 MVCC 模型的锁定策略非常有用。
例如，咨询锁的一个常见用途是模拟典型的所谓“平面文件”数据管理系统的悲观锁策略。虽然存储在表中的标志可以用于相同的目的，但咨询锁更快，避免表膨胀，并在会话结束时由服务器自动清理。
例子
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
以下示例以 ACCESS EXCLUSIVE 模式锁定 testdb 数据库中的 COMPANY 表。LOCK 语句仅在事务模式下工作 –
testdb=#BEGIN; LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;
上面给出的 PostgreSQL 语句将产生以下结果 –
LOCK TABLE
上面的消息表明表被锁定，直到事务结束并且要完成事务，您必须回滚或提交事务。
PostgreSQL – 子查询
子查询或内部查询或嵌套查询是另一个 PostgreSQL 查询中的查询，并嵌入在 WHERE 子句中。
子查询用于返回将在主查询中用作条件的数据，以进一步限制要检索的数据。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句以及 =、<、>、>=、<=、IN 等运算符一起使用。
子查询必须遵循一些规则 –
-
子查询必须括在括号内。
-
一个子查询在 SELECT 子句中只能有一个列，除非子查询的主查询中有多个列以比较其选定的列。
-
尽管主查询可以使用 ORDER BY，但不能在子查询中使用 ORDER BY。GROUP BY 可用于执行与子查询中的 ORDER BY 相同的功能。
-
返回多行的子查询只能与多个值运算符一起使用，例如 IN、EXISTS、NOT IN、ANY/SOME、ALL 运算符。
-
BETWEEN 运算符不能与子查询一起使用；但是，BETWEEN 可以在子查询中使用。
带有 SELECT 语句的子查询
子查询最常与 SELECT 语句一起使用。基本语法如下 –
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
例子
考虑具有以下记录的COMPANY表 –
id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
现在，让我们使用 SELECT 语句检查以下子查询 –
testdb=# SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
这将产生以下结果 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 (2 rows)
带有 INSERT 语句的子查询
子查询也可以与 INSERT 语句一起使用。INSERT 语句使用从子查询返回的数据插入到另一个表中。子查询中选定的数据可以使用任何字符、日期或数字函数进行修改。
基本语法如下 –
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
例子
考虑一个表 COMPANY_BKP，它具有与 COMPANY 表相似的结构，并且可以使用相同的 CREATE TABLE 来创建，并使用 COMPANY_BKP 作为表名。现在，要将完整的 COMPANY 表复制到 COMPANY_BKP，以下是语法 –
testdb=# INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
带有 UPDATE 语句的子查询
子查询可以与 UPDATE 语句结合使用。使用带有 UPDATE 语句的子查询时，可以更新表中的单列或多列。
基本语法如下 –
UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
例子
假设我们有 COMPANY_BKP 表可用，它是 COMPANY 表的备份。
以下示例将 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新 0.50 倍 –
testdb=# UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
这将影响两行，最后 COMPANY 表将有以下记录 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 1 | Paul | 32 | California | 10000 5 | David | 27 | Texas | 42500 (7 rows)
带有 DELETE 语句的子查询
子查询可以与 DELETE 语句结合使用，就像上面提到的任何其他语句一样。
基本语法如下 –
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
例子
假设我们有 COMPANY_BKP 表可用，它是 COMPANY 表的备份。
以下示例从 COMPANY 表中删除所有 AGE 大于或等于 27 的客户的记录 –
testdb=# DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );
这将影响两行，最后 COMPANY 表将有以下记录 –
id | name | age | address | salary ----+-------+-----+-------------+-------- 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 5 | David | 27 | Texas | 42500 (6 rows)
PostgreSQL – 自动增量
PostgreSQL 的数据类型有smallserial、 serial 和bigserial；这些不是真正的类型，而仅仅是创建唯一标识符列的符号方便。这些类似于其他一些数据库支持的 AUTO_INCREMENT 属性。
如果您希望串行列具有唯一约束或成为主键，则现在必须指定它，就像任何其他数据类型一样。
类型名称serial创建一个整数列。类型名称bigserial创建一个bigint列。如果您预计使用超过 2 个，则应使用bigserial31表生命周期内的标识符。类型名称smallserial创建一个smallint列。
句法
SERIAL 数据类型的基本用法如下 –
CREATE TABLE tablename ( colname SERIAL );
例子
考虑如下创建的 COMPANY 表 –
testdb=# CREATE TABLE COMPANY( ID SERIAL PRIMARY KEY, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
现在，将以下记录插入表 COMPANY –
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );
这将在表 COMPANY 中插入七个元组，COMPANY 将具有以下记录 –
id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000
PostgreSQL – 特权
每当在数据库中创建一个对象时，就会为其分配一个所有者。所有者通常是执行创建语句的人。对于大多数种类的对象，初始状态是只有所有者（或超级用户）才能修改或删除对象。要允许其他角色或用户使用它，必须授予特权或权限。
PostgreSQL 中的不同权限是 –
- 选择，
- 插，
- 更新，
- 删除，
- 截短，
- 参考，
- 扳机，
- 创造，
- 连接，
- 暂时的，
- 执行，和
- 用法
根据对象的类型（表、函数等），对对象应用权限。要为用户分配权限，请使用 GRANT 命令。
GRANT 的语法
GRANT 命令的基本语法如下 –
GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }
-
特权– 值可以是：SELECT、INSERT、UPDATE、DELETE、RULE、ALL。
-
object – 授予访问权限的对象的名称。可能的对象有：表、视图、序列
-
PUBLIC – 代表所有用户的简短形式。
-
GROUP组– 授予权限的组。
-
username – 授予权限的用户名。PUBLIC 是代表所有用户的简短形式。
可以使用 REVOKE 命令撤销权限。
REVOKE 的语法
REVOKE 命令的基本语法如下 –
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }
-
特权– 值可以是：SELECT、INSERT、UPDATE、DELETE、RULE、ALL。
-
object – 授予访问权限的对象的名称。可能的对象有：表、视图、序列
-
PUBLIC – 代表所有用户的简短形式。
-
GROUP组– 授予权限的组。
-
username – 授予权限的用户名。PUBLIC 是代表所有用户的简短形式。
例子
要了解权限，让我们首先创建一个 USER，如下所示 –
testdb=# CREATE USER manisha WITH PASSWORD 'password'; CREATE ROLE
消息 CREATE ROLE 指示创建了用户“manisha”。
考虑表COMPANY 的记录如下 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
接下来，让我们将表 COMPANY 的所有权限授予用户“manisha”，如下所示 –
testdb=# GRANT ALL ON COMPANY TO manisha; GRANT
消息 GRANT 表示所有权限都分配给了 USER。
接下来，让我们撤销用户“manisha”的权限，如下所示 –
testdb=# REVOKE ALL ON COMPANY FROM manisha; REVOKE
消息 REVOKE 指示从 USER 撤销所有权限。
您甚至可以按如下方式删除用户 –
testdb=# DROP USER manisha; DROP ROLE
消息 DROP ROLE 表明 USER ‘Manisha’ 已从数据库中删除。
PostgreSQL – 日期/时间函数和运算符
我们在数据类型一章中讨论了日期/时间数据类型。现在，让我们看看日期/时间运算符和函数。
下表列出了基本算术运算符的行为 –
| Operator | 例子 | 结果 |
|---|---|---|
| + | 日期 ‘2001-09-28’ + 整数 ‘7’ | 日期 ‘2001-10-05’ |
| + | 日期 ‘2001-09-28’ + 间隔 ‘1 小时’ | 时间戳 ‘2001-09-28 01:00:00’ |
| + | 日期 ‘2001-09-28′ + 时间 ’03:00’ | 时间戳 ‘2001-09-28 03:00:00’ |
| + | 间隔“1天”+间隔“1小时” | 间隔 ‘1 天 01:00:00’ |
| + | 时间戳 ‘2001-09-28 01:00′ + 间隔 ’23 小时’ | 时间戳 ‘2001-09-29 00:00:00’ |
| + | 时间“01:00”+间隔“3小时” | 时间 ’04:00:00′ |
| – | – 间隔“23 小时” | 间隔’-23:00:00′ |
| – | 日期’2001-10-01′ – 日期’2001-09-28′ | 整数“3”（天） |
| – | 日期 ‘2001-10-01’ – 整数 ‘7’ | 日期 ‘2001-09-24’ |
| – | 日期 ‘2001-09-28’ – 间隔 ‘1 小时’ | 时间戳 ‘2001-09-27 23:00:00’ |
| – | 时间 ’05:00′ – 时间 ’03:00′ | 间隔’02:00:00′ |
| – | 时间 ’05:00′ – 间隔 ‘2 小时’ | 时间 ’03:00:00′ |
| – | 时间戳 ‘2001-09-28 23:00′ – 间隔 ’23 小时’ | 时间戳 ‘2001-09-28 00:00:00’ |
| – | 间隔“1 天” – 间隔“1 小时” | 间隔’1天-01:00:00′ |
| – | 时间戳’2001-09-29 03:00′ – 时间戳’2001-09-27 12:00′ | 间隔 ‘1 天 15:00:00’ |
| * | 900 *间隔’1秒’ | 间隔’00:15:00′ |
| * | 21 * 间隔“1 天” | 间隔’21天’ |
| * | 双精度 ‘3.5’ * 间隔 ‘1 小时’ | 间隔’03:30:00′ |
| / | 间隔“1 小时”/双精度“1.5” | 间隔’00:40:00′ |
以下是所有可用的重要日期和时间相关功能的列表。
| S. No. | 功能说明 |
|---|---|
| 1 | AGE()
减去参数 |
| 2 | CURRENT DATE/TIME()
当前日期和时间 |
| 3 | DATE_PART()
获取子字段（相当于提取） |
| 4 | EXTRACT()
获取子字段 |
| 5 | ISFINITE()
测试有限的日期、时间和间隔（不是 +/- 无穷大） |
| 6 | JUSTIFY
调整间隔 |
年龄（时间戳，时间戳），年龄（时间戳）
| S. No. | 功能说明 |
|---|---|
| 1 |
AGE(timestamp, timestamp) 当使用第二个参数的 TIMESTAMP 形式调用时，AGE() 减去参数，产生一个“符号”结果，该结果使用年和月并且类型为 INTERVAL。 |
| 2 |
AGE(timestamp) 当仅使用 TIMESTAMP 作为参数调用时，AGE() 从 current_date（午夜）中减去。 |
函数 AGE(timestamp, timestamp) 的示例是 –
testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');
上面给出的 PostgreSQL 语句将产生以下结果 –
age ------------------------- 43 years 9 mons 27 days
函数 AGE(timestamp) 的示例是 –
testdb=# select age(timestamp '1957-06-13');
上面给出的 PostgreSQL 语句将产生以下结果 –
age -------------------------- 55 years 10 mons 22 days
当前日期/时间()
PostgreSQL 提供了许多返回与当前日期和时间相关的值的函数。以下是一些功能 –
| S. No. | 功能说明 |
|---|---|
| 1 |
CURRENT_DATE 提供当前日期。 |
| 2 |
CURRENT_TIME 提供带时区的值。 |
| 3 |
CURRENT_TIMESTAMP 提供带时区的值。 |
| 4 |
CURRENT_TIME(precision) 可选地采用精度参数，这会导致结果四舍五入到秒字段中的小数位数。 |
| 5 |
CURRENT_TIMESTAMP(precision) 可选地采用精度参数，这会导致结果四舍五入到秒字段中的小数位数。 |
| 6 |
LOCALTIME 提供无时区的值。 |
| 7 |
LOCALTIMESTAMP 提供无时区的值。 |
| 8 |
LOCALTIME(precision) 可选地采用精度参数，这会导致结果四舍五入到秒字段中的小数位数。 |
| 9 |
LOCALTIMESTAMP(precision) 可选地采用精度参数，这会导致结果四舍五入到秒字段中的小数位数。 |
使用上表中函数的示例 –
testdb=# SELECT CURRENT_TIME;
timetz
--------------------
08:01:34.656+05:30
(1 row)
testdb=# SELECT CURRENT_DATE;
date
------------
2013-05-05
(1 row)
testdb=# SELECT CURRENT_TIMESTAMP;
now
-------------------------------
2013-05-05 08:01:45.375+05:30
(1 row)
testdb=# SELECT CURRENT_TIMESTAMP(2);
timestamptz
------------------------------
2013-05-05 08:01:50.89+05:30
(1 row)
testdb=# SELECT LOCALTIMESTAMP;
timestamp
------------------------
2013-05-05 08:01:55.75
(1 row)
PostgreSQL 还提供了返回当前语句开始时间的函数，以及函数被调用瞬间的实际当前时间。这些功能是 –
| S. No. | 功能说明 |
|---|---|
| 1 |
transaction_timestamp() 它等同于 CURRENT_TIMESTAMP，但命名是为了清楚地反映它返回的内容。 |
| 2 |
statement_timestamp() 它返回当前语句的开始时间。 |
| 3 |
clock_timestamp() 它返回实际的当前时间，因此即使在单个 SQL 命令中它的值也会发生变化。 |
| 4 |
timeofday() 它返回实际的当前时间，但作为格式化的文本字符串而不是带有时区值的时间戳。 |
| 5 |
now() 它是传统的 PostgreSQL 等价于 transaction_timestamp()。 |
DATE_PART（文本，时间戳），DATE_PART（文本，间隔），DATE_TRUNC（文本，时间戳）
| S. No. | 功能说明 |
|---|---|
| 1 |
DATE_PART(‘field’, source) 这些函数获取子字段。该场参数必须是一个字符串值，不是一个名字。 有效的字段名称是：世纪、日、十年、dow、doy、纪元、小时、isodow、isoyear、微秒、千年、毫秒、分钟、月、季度、秒、时区、timezone_hour、timezone_minute、week、year。 |
| 2 |
DATE_TRUNC(‘field’, source) 此函数在概念上类似于数字的trunc函数。source是时间戳或间隔类型的值表达式。字段选择要截断输入值的精度。返回值的类型为timestamp或interval。 字段的有效值为：微秒、毫秒、秒、分钟、小时、日、周、月、季度、年、十年、世纪、千年 |
以下是 DATE_PART( ‘field’ , source) 函数的示例–
testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
date_part
-----------
16
(1 row)
testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
date_part
-----------
4
(1 row)
以下是 DATE_TRUNC( ‘field’ , source) 函数的示例–
testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
date_trunc
---------------------
2001-02-16 20:00:00
(1 row)
testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
date_trunc
---------------------
2001-01-01 00:00:00
(1 row)
EXTRACT（来自时间戳的字段），EXTRACT（来自间隔的字段）
的提取物（字段从源）函数将检索的子场，例如一年或从日期/时间值小时。的源的类型必须是一个值表达式时间戳，时间或间隔。该字段是一个标识符或字符串，用于选择要从源值中提取的字段。EXTRACT 函数返回double precision类型的值。
以下是有效的字段名称（类似于 DATE_PART 函数字段名称）：世纪、日、十年、dow、doy、epoch、小时、isodow、isoyear、微秒、千禧年、毫秒、分钟、月、季度、秒、时区、timezone_hour , timezone_minute, 周, 年。
以下是 EXTRACT( ‘field’ , source) 函数的示例–
testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
date_part
-----------
20
(1 row)
testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
date_part
-----------
16
(1 row)
ISFINITE（日期），ISFINITE（时间戳），ISFINITE（间隔）
| S. No. | 功能说明 |
|---|---|
| 1 |
ISFINITE(date) 测试有限日期。 |
| 2 |
ISFINITE(timestamp) 测试有限时间戳。 |
| 3 |
ISFINITE(interval) 测试有限区间。 |
以下是 ISFINITE() 函数的示例 –
testdb=# SELECT isfinite(date '2001-02-16'); isfinite ---------- t (1 row) testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30'); isfinite ---------- t (1 row) testdb=# SELECT isfinite(interval '4 hours'); isfinite ---------- t (1 row)
JUSTIFY_DAYS（间隔），JUSTIFY_HOURS（间隔），JUSTIFY_INTERVAL（间隔）
| S. No. | 功能说明 |
|---|---|
| 1 |
JUSTIFY_DAYS(interval) 调整间隔，使 30 天的时间段表示为月。返回区间类型 |
| 2 |
JUSTIFY_HOURS(interval) 调整间隔，使 24 小时时间段表示为天。返回区间类型 |
| 3 |
JUSTIFY_INTERVAL(interval) 使用 JUSTIFY_DAYS 和 JUSTIFY_HOURS 调整间隔，并进行额外的符号调整。返回区间类型 |
以下是 ISFINITE() 函数的示例 –
testdb=# SELECT justify_days(interval '35 days'); justify_days -------------- 1 mon 5 days (1 row) testdb=# SELECT justify_hours(interval '27 hours'); justify_hours ---------------- 1 day 03:00:00 (1 row) testdb=# SELECT justify_interval(interval '1 mon -1 hour'); justify_interval ------------------ 29 days 23:00:00 (1 row)
PostgreSQL – 函数
PostgreSQL函数，也称为存储过程，允许您在数据库中的单个函数中执行通常需要多次查询和往返的操作。函数允许重用数据库，因为其他应用程序可以直接与您的存储过程交互，而不是中间层或重复代码。
可以使用您选择的语言（如 SQL、PL/pgSQL、C、Python 等）创建函数。
句法
创建函数的基本语法如下 –
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语句。
-
RETURN子句指定要从函数返回的数据类型。所述return_datatype可以是碱，复合材料，或域类型，或者可以引用一个表列的类型。
-
函数体包含可执行部分。
-
AS 关键字用于创建独立函数。
-
plpgsql是函数实现的语言名称。这里，我们对PostgreSQL使用这个选项，它可以是SQL、C、internal，也可以是用户自定义的过程语言的名称。为了向后兼容，名称可以用单引号括起来。
例子
以下示例说明了创建和调用独立函数。此函数返回 COMPANY 表中的记录总数。我们将使用COMPANY表，其中包含以下记录 –
testdb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
函数 totalRecords() 如下 –
CREATE OR REPLACE FUNCTION totalRecords () RETURNS integer AS $total$ declare total integer; BEGIN SELECT count(*) into total FROM COMPANY; RETURN total; END; $total$ LANGUAGE plpgsql;
执行上述查询时，结果将是 –
testdb# CREATE FUNCTION
现在，让我们执行对该函数的调用并检查 COMPANY 表中的记录
testdb=# select totalRecords();
执行上述查询时，结果将是 –
totalrecords
--------------
7
(1 row)
PostgreSQL – 有用的函数
PostgreSQL 内置函数，也称为聚合函数，用于对字符串或数字数据进行处理。
以下是所有通用 PostgreSQL 内置函数的列表 –
-
PostgreSQL COUNT 函数– PostgreSQL COUNT 聚合函数用于计算数据库表中的行数。
-
PostgreSQL MAX 函数– PostgreSQL MAX 聚合函数允许我们为某个列选择最高（最大值）值。
-
PostgreSQL MIN 函数– PostgreSQL MIN 聚合函数允许我们为某个列选择最低（最小值）值。
-
PostgreSQL AVG 函数– PostgreSQL AVG 聚合函数选择某些表列的平均值。
-
PostgreSQL SUM 函数– PostgreSQL SUM 聚合函数允许选择数字列的总数。
-
PostgreSQL ARRAY 函数– PostgreSQL ARRAY 聚合函数将输入值（包括空值）连接到一个数组中。
-
PostgreSQL 数字函数– 在 SQL 中操作数字所需的 PostgreSQL 函数的完整列表。
-
PostgreSQL 字符串函数– 在 PostgreSQL 中操作字符串所需的 PostgreSQL 函数的完整列表。
PostgreSQL – C/C++ 接口
本教程将使用libpqxx库，它是 PostgreSQL 的官方 C++ 客户端 API。libpqxx 的源代码在 BSD 许可下可用，因此您可以自由地下载它、将其传递给其他人、更改它、出售它、将其包含在您自己的代码中，并与您选择的任何人共享您的更改。
安装
最新版本的 libpqxx 可从下载 Libpqxx链接下载。所以下载最新版本并按照以下步骤操作 –
wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz tar xvfz libpqxx-4.0.tar.gz cd libpqxx-4.0 ./configure make make install
在开始使用 C/C++ PostgreSQL 接口之前，在 PostgreSQL 安装目录中找到pg_hba.conf文件并添加以下行 –
# IPv4 local connections: host all all 127.0.0.1/32 md5
您可以使用以下命令启动/重启 postgres 服务器，以防它没有运行 –
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
C/C++ 接口 API
以下是重要的接口例程，它们可以满足您从 C/C++ 程序使用 PostgreSQL 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 libpqxx 官方文档，或者您可以使用商用 API。
| S. No. | API & 描述 |
|---|---|
| 1 |
pqxx::connection C( const std::string & dbstring ) 这是一个 typedef，将用于连接到数据库。这里，dbstring 提供了连接数据库所需的参数，例如dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432。 如果连接设置成功，那么它会创建带有连接对象的 C，该对象提供各种有用的函数公共函数。 |
| 2 |
C.is_open() 方法 is_open() 是连接对象的公共方法，返回布尔值。如果连接处于活动状态，则此方法返回 true，否则返回 false。 |
| 3 |
C.disconnect() 此方法用于断开已打开的数据库连接。 |
| 4 |
pqxx::work W( C ) 这是一个 typedef，将用于使用连接 C 创建事务对象，最终将用于在事务模式下执行 SQL 语句。 如果事务对象创建成功，则将其分配给变量 W，该变量将用于访问与事务对象相关的公共方法。 |
| 5 |
W.exec(const std::string & sql) 这个来自事务对象的公共方法将用于执行 SQL 语句。 |
6
W.commit()
来自事务对象的这个公共方法将用于提交事务。
7
W.abort()
这个来自事务对象的公共方法将用于回滚事务。
8
pqxx::nontransaction N( C )
这是一个 typedef，将用于使用连接 C 创建非事务对象，最终将用于在非事务模式下执行 SQL 语句。
如果事务对象创建成功，则将其分配给变量 N，该变量将用于访问与非事务对象相关的公共方法。
9
N.exec(const std::string & sql)
这个来自非事务对象的公共方法将用于执行 SQL 语句并返回一个结果对象，它实际上是一个包含所有返回记录的交互器。
连接到数据库
下面的 C 代码段显示了如何连接到在本地机器上运行的现有数据库的 5432 端口。在这里，我使用反斜杠 \ 来延续行。
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
try {
connection C("dbname = testdb user = postgres password = cohondob \
hostaddr = 127.0.0.1 port = 5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
C.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
}
现在，让我们编译并运行上述程序以连接到我们的数据库testdb，该数据库已在您的架构中可用，可以使用用户postgres和密码pass123访问。
您可以根据您的数据库设置使用用户 ID 和密码。请记住按照给定的顺序保留 -lpqxx 和 -lpq！否则，链接器会抱怨缺少名称以“PQ”开头的函数。
$g++ test.cpp -lpqxx -lpq $./a.out Opened database successfully: testdb
创建表
以下 C 代码段将用于在先前创建的数据库中创建表 –
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
char * sql;
try {
connection C("dbname = testdb user = postgres password = cohondob \
hostaddr = 127.0.0.1 port = 5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
/* Create a transactional object. */
work W(C);
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Table created successfully" << endl;
C.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上述给定的程序被编译和执行时，它将在您的 testdb 数据库中创建 COMPANY 表并显示以下语句 –
Opened database successfully: testdb Table created successfully
插入操作
以下 C 代码段显示了我们如何在上面示例中创建的 COMPANY 表中创建记录 –
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
char * sql;
try {
connection C("dbname = testdb user = postgres password = cohondob \
hostaddr = 127.0.0.1 port = 5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
/* Create a transactional object. */
work W(C);
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Records created successfully" << endl;
C.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上面给定的程序被编译和执行时，它会在 COMPANY 表中创建给定的记录并显示以下两行 –
Opened database successfully: testdb Records created successfully
选择操作
以下 C 代码段显示了我们如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
char * sql;
try {
connection C("dbname = testdb user = postgres password = cohondob \
hostaddr = 127.0.0.1 port = 5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create SQL statement */
sql = "SELECT * from COMPANY";
/* Create a non-transactional object. */
nontransaction N(C);
/* Execute SQL query */
result R( N.exec( sql ));
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "ID = " << c[0].as<int>() << endl;
cout << "Name = " << c[1].as<string>() << endl;
cout << "Age = " << c[2].as<int>() << endl;
cout << "Address = " << c[3].as<string>() << endl;
cout << "Salary = " << c[4].as<float>() << endl;
}
cout << "Operation done successfully" << endl;
C.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上面给定的程序被编译和执行时，它会产生以下结果 –
Opened database successfully: testdb ID = 1 Name = Paul Age = 32 Address = California Salary = 20000 ID = 2 Name = Allen Age = 25 Address = Texas Salary = 15000 ID = 3 Name = Teddy Age = 23 Address = Norway Salary = 20000 ID = 4 Name = Mark Age = 25 Address = Rich-Mond Salary = 65000 Operation done successfully
更新操作
下面的 C 代码段展示了我们如何使用 UPDATE 语句来更新任何记录，然后从我们的 COMPANY 表中获取和显示更新的记录 –
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
char * sql;
try {
connection C("dbname = testdb user = postgres password = cohondob \
hostaddr = 127.0.0.1 port = 5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create a transactional object. */
work W(C);
/* Create SQL UPDATE statement */
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Records updated successfully" << endl;
/* Create SQL SELECT statement */
sql = "SELECT * from COMPANY";
/* Create a non-transactional object. */
nontransaction N(C);
/* Execute SQL query */
result R( N.exec( sql ));
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "ID = " << c[0].as<int>() << endl;
cout << "Name = " << c[1].as<string>() << endl;
cout << "Age = " << c[2].as<int>() << endl;
cout << "Address = " << c[3].as<string>() << endl;
cout << "Salary = " << c[4].as<float>() << endl;
}
cout << "Operation done successfully" << endl;
C.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上面给定的程序被编译和执行时，它会产生以下结果 –
Opened database successfully: testdb Records updated successfully ID = 2 Name = Allen Age = 25 Address = Texas Salary = 15000 ID = 3 Name = Teddy Age = 23 Address = Norway Salary = 20000 ID = 4 Name = Mark Age = 25 Address = Rich-Mond Salary = 65000 ID = 1 Name = Paul Age = 32 Address = California Salary = 25000 Operation done successfully
删除操作
以下 C 代码段显示了我们如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余的记录 –
#include <iostream>
#include <pqxx/pqxx>
using namespace std;
using namespace pqxx;
int main(int argc, char* argv[]) {
char * sql;
try {
connection C("dbname = testdb user = postgres password = cohondob \
hostaddr = 127.0.0.1 port = 5432");
if (C.is_open()) {
cout << "Opened database successfully: " << C.dbname() << endl;
} else {
cout << "Can't open database" << endl;
return 1;
}
/* Create a transactional object. */
work W(C);
/* Create SQL DELETE statement */
sql = "DELETE from COMPANY where ID = 2";
/* Execute SQL query */
W.exec( sql );
W.commit();
cout << "Records deleted successfully" << endl;
/* Create SQL SELECT statement */
sql = "SELECT * from COMPANY";
/* Create a non-transactional object. */
nontransaction N(C);
/* Execute SQL query */
result R( N.exec( sql ));
/* List down all the records */
for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
cout << "ID = " << c[0].as<int>() << endl;
cout << "Name = " << c[1].as<string>() << endl;
cout << "Age = " << c[2].as<int>() << endl;
cout << "Address = " << c[3].as<string>() << endl;
cout << "Salary = " << c[4].as<float>() << endl;
}
cout << "Operation done successfully" << endl;
C.disconnect ();
} catch (const std::exception &e) {
cerr << e.what() << std::endl;
return 1;
}
return 0;
}
当上面给定的程序被编译和执行时，它会产生以下结果 –
Opened database successfully: testdb Records deleted successfully ID = 3 Name = Teddy Age = 23 Address = Norway Salary = 20000 ID = 4 Name = Mark Age = 25 Address = Rich-Mond Salary = 65000 ID = 1 Name = Paul Age = 32 Address = California Salary = 25000 Operation done successfully
PostgreSQL – JAVA 接口
安装
在我们开始在 Java 程序中使用 PostgreSQL 之前，我们需要确保我们在机器上设置了 PostgreSQL JDBC 和 Java。您可以查看 Java 教程以在您的机器上安装 Java。现在让我们检查如何设置 PostgreSQL JDBC 驱动程序。
-
从postgresql-jdbc存储库下载最新版本的postgresql-(VERSION).jdbc.jar。
-
在您的类路径中添加下载的 jar 文件postgresql-(VERSION).jdbc.jar，或者您可以将它与 -classpath 选项一起使用，如下面的示例中所述。
以下部分假设您对 Java JDBC 概念知之甚少。如果您没有，那么建议您花半个小时学习JDBC 教程，以熟悉下面解释的概念。
连接到数据库
以下 Java 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
import java.sql.Connection;
import java.sql.DriverManager;
public class PostgreSQLJDBC {
public static void main(String args[]) {
Connection c = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/testdb",
"postgres", "123");
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName()+": "+e.getMessage());
System.exit(0);
}
System.out.println("Opened database successfully");
}
}
在编译和运行上面的程序之前，在 PostgreSQL 安装目录中找到pg_hba.conf文件并添加以下行 –
# IPv4 local connections: host all all 127.0.0.1/32 md5
您可以使用以下命令启动/重新启动 postgres 服务器，以防它没有运行 –
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
现在，让我们编译并运行上面的程序来连接 testdb。在这里，我们使用postgres作为用户 ID 和123作为密码来访问数据库。您可以根据您的数据库配置和设置进行更改。我们还假设当前版本的 JDBC 驱动程序postgresql-9.2-1002.jdbc3.jar在当前路径中可用。
C:\JavaPostgresIntegration>javac PostgreSQLJDBC.java C:\JavaPostgresIntegration>java -cp c:\tools\postgresql-9.2-1002.jdbc3.jar;C:\JavaPostgresIntegration PostgreSQLJDBC Open database successfully
创建表
以下 Java 程序将用于在先前打开的数据库中创建表。确保您的目标数据库中没有此表。
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class PostgreSQLJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/testdb",
"manisha", "123");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}
}
当一个程序被编译和执行时，它将在testdb数据库中创建 COMPANY 表并显示以下两行 –
Opened database successfully Table created successfully
插入操作
以下 Java 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录 –
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class PostgreSQLJDBC {
public static void main(String args[]) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/testdb",
"manisha", "123");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch (Exception e) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
}
当上述程序被编译和执行时，它会在 COMPANY 表中创建给定的记录并显示以下两行 –
Opened database successfully Records created successfully
选择操作
以下 Java 程序显示了我们如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class PostgreSQLJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/testdb",
"manisha", "123");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
当程序被编译和执行时，它会产生以下结果 –
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
更新操作
以下 Java 代码显示了我们如何使用 UPDATE 语句更新任何记录，然后从我们的 COMPANY 表中获取并显示更新的记录 –
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class PostgreSQLJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/testdb",
"manisha", "123");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
当程序被编译和执行时，它会产生以下结果 –
Opened database successfully ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 Operation done successfully
删除操作
以下 Java 代码显示了我们如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余的记录 –
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class PostgreSQLJDBC6 {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.postgresql.Driver");
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/testdb",
"manisha", "123");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID = 2;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
当程序被编译和执行时，它会产生以下结果 –
Opened database successfully ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 Operation done successfully
PostgreSQL – PHP 接口
安装
PostgreSQL 扩展在最新版本的 PHP 5.3.x 中默认启用。可以在编译时使用–without-pgsql禁用它。您仍然可以使用 yum 命令安装 PHP -PostgreSQL 接口 –
yum install php-pgsql
在开始使用 PHP PostgreSQL 界面之前，在 PostgreSQL 安装目录中找到pg_hba.conf文件并添加以下行 –
# IPv4 local connections: host all all 127.0.0.1/32 md5
您可以使用以下命令启动/重启 postgres 服务器，以防它没有运行 –
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
Windows 用户必须启用 php_pgsql.dll 才能使用此扩展。此 DLL 包含在最新版本的 PHP 5.3.x 中的 Windows 发行版中
有关详细的安装说明，请查看我们的 PHP 教程及其官方网站。
PHP 接口 API
以下是重要的 PHP 例程，它们可以满足您从 PHP 程序使用 PostgreSQL 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 PHP 官方文档。
| S. No. | API & 描述 |
|---|---|
| 1 |
resource pg_connect ( string $connection_string [, int $connect_type ] ) 这将打开到由 connection_string 指定的 PostgreSQL 数据库的连接。 如果 PGSQL_CONNECT_FORCE_NEW 作为 connect_type 传递，则在第二次调用 pg_connect() 的情况下会创建一个新连接，即使 connection_string 与现有连接相同。 |
| 2 |
bool pg_connection_reset ( resource $connection ) 此例程重置连接。它对于错误恢复很有用。成功时返回 TRUE，失败时返回 FALSE。 |
| 3 |
int pg_connection_status ( resource $connection ) 此例程返回指定连接的状态。返回 PGSQL_CONNECTION_OK 或 PGSQL_CONNECTION_BAD。 |
| 4 |
string pg_dbname ([ resource $connection ] ) 此例程返回给定 PostgreSQL 连接资源的数据库名称。 |
| 5 |
resource pg_prepare ([ resource $connection ], string $stmtname, string $query ) 这将提交一个请求，以使用给定的参数创建一个准备好的语句并等待完成。 |
| 6 |
resource pg_execute ([ resource $connection ], string $stmtname, array $params ) 此例程发送请求以执行带有给定参数的准备好的语句并等待结果。 |
| 7 |
resource pg_query ([ resource $connection ], string $query ) 此例程在指定的数据库连接上执行查询。 |
| 8 |
array pg_fetch_row ( resource $result [, int $row ] ) 此例程从与指定结果资源关联的结果中获取一行数据。 |
| 9 |
array pg_fetch_all ( resource $result ) 此例程返回一个数组，其中包含结果资源中的所有行（记录）。 |
| 10 |
int pg_affected_rows ( resource $result ) 此例程返回受 INSERT、UPDATE 和 DELETE 查询影响的行数。 |
| 11 |
int pg_num_rows ( resource $result ) 此例程返回 PostgreSQL 结果资源中的行数，例如 SELECT 语句返回的行数。 |
| 12 |
bool pg_close ([ resource $connection ] ) 此例程关闭与给定连接资源关联的 PostgreSQL 数据库的非持久连接。 |
| 13 |
string pg_last_error ([ resource $connection ] ) 此例程返回给定连接的最后一条错误消息。 |
| 14 |
string pg_escape_literal ([ resource $connection ], string $data ) 此例程转义文本以插入文本字段。 |
| 15 |
string pg_escape_string ([ resource $connection ], string $data ) 此例程转义用于查询数据库的字符串。 |
连接到数据库
以下 PHP 代码显示了如何连接到本地机器上的现有数据库，最终将返回一个数据库连接对象。
<?php
$host = "host = 127.0.0.1";
$port = "port = 5432";
$dbname = "dbname = testdb";
$credentials = "user = postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials" );
if(!$db) {
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
?>
现在，让我们运行上面给出的程序来打开我们的数据库testdb：如果数据库成功打开，它将给出以下消息 –
Opened database successfully
创建表
以下 PHP 程序将用于在先前创建的数据库中创建表 –
<?php
$host = "host = 127.0.0.1";
$port = "port = 5432";
$dbname = "dbname = testdb";
$credentials = "user = postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials" );
if(!$db) {
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
} else {
echo "Table created successfully\n";
}
pg_close($db);
?>
当上面给定的程序被执行时，它会在你的testdb 中创建 COMPANY 表，它会显示以下消息 –
Opened database successfully Table created successfully
插入操作
以下 PHP 程序显示了我们如何在上面示例中创建的 COMPANY 表中创建记录 –
<?php
$host = "host=127.0.0.1";
$port = "port=5432";
$dbname = "dbname = testdb";
$credentials = "user = postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials" );
if(!$db) {
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
} else {
echo "Records created successfully\n";
}
pg_close($db);
?>
执行上述给定程序时，它将在 COMPANY 表中创建给定记录，并显示以下两行 –
Opened database successfully Records created successfully
选择操作
以下 PHP 程序展示了我们如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
<?php
$host = "host = 127.0.0.1";
$port = "port = 5432";
$dbname = "dbname = testdb";
$credentials = "user = postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials" );
if(!$db) {
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
exit;
}
while($row = pg_fetch_row($ret)) {
echo "ID = ". $row[0] . "\n";
echo "NAME = ". $row[1] ."\n";
echo "ADDRESS = ". $row[2] ."\n";
echo "SALARY = ".$row[4] ."\n\n";
}
echo "Operation done successfully\n";
pg_close($db);
?>
当上面给出的程序被执行时，它会产生下面的结果。请注意，字段按创建表时使用的顺序返回。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
更新操作
以下 PHP 代码显示了我们如何使用 UPDATE 语句更新任何记录，然后从我们的 COMPANY 表中获取并显示更新的记录 –
<?php
$host = "host=127.0.0.1";
$port = "port=5432";
$dbname = "dbname = testdb";
$credentials = "user = postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials" );
if(!$db) {
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
exit;
} else {
echo "Record updated successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
exit;
}
while($row = pg_fetch_row($ret)) {
echo "ID = ". $row[0] . "\n";
echo "NAME = ". $row[1] ."\n";
echo "ADDRESS = ". $row[2] ."\n";
echo "SALARY = ".$row[4] ."\n\n";
}
echo "Operation done successfully\n";
pg_close($db);
?>
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully Record updated successfully ID = 2 NAME = Allen ADDRESS = 25 SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = 23 SALARY = 20000 ID = 4 NAME = Mark ADDRESS = 25 SALARY = 65000 ID = 1 NAME = Paul ADDRESS = 32 SALARY = 25000 Operation done successfully
删除操作
以下 PHP 代码显示了我们如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余的记录 –
<?php
$host = "host = 127.0.0.1";
$port = "port = 5432";
$dbname = "dbname = testdb";
$credentials = "user = postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials" );
if(!$db) {
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
DELETE from COMPANY where ID=2;
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
exit;
} else {
echo "Record deleted successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = pg_query($db, $sql);
if(!$ret) {
echo pg_last_error($db);
exit;
}
while($row = pg_fetch_row($ret)) {
echo "ID = ". $row[0] . "\n";
echo "NAME = ". $row[1] ."\n";
echo "ADDRESS = ". $row[2] ."\n";
echo "SALARY = ".$row[4] ."\n\n";
}
echo "Operation done successfully\n";
pg_close($db);
?>
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully Record deleted successfully ID = 3 NAME = Teddy ADDRESS = 23 SALARY = 20000 ID = 4 NAME = Mark ADDRESS = 25 SALARY = 65000 ID = 1 NAME = Paul ADDRESS = 32 SALARY = 25000 Operation done successfully
PostgreSQL – Perl 接口
安装
PostgreSQL 可以使用 Perl DBI 模块与 Perl 集成，Perl DBI 模块是 Perl 编程语言的数据库访问模块。它定义了一组提供标准数据库接口的方法、变量和约定。
以下是在 Linux/Unix 机器上安装 DBI 模块的简单步骤 –
$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625 $ perl Makefile.PL $ make $ make install
如果您需要为 DBI 安装 SQLite 驱动程序，则可以按如下方式安装 –
$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz $ tar xvfz DBD-Pg-2.19.3.tar.gz $ cd DBD-Pg-2.19.3 $ perl Makefile.PL $ make $ make install
在开始使用 Perl PostgreSQL 界面之前，在 PostgreSQL 安装目录中找到pg_hba.conf文件并添加以下行 –
# IPv4 local connections: host all all 127.0.0.1/32 md5
您可以使用以下命令启动/重启 postgres 服务器，以防它没有运行 –
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
DBI 接口 API
以下是重要的 DBI 例程，它们可以满足您从 Perl 程序使用 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 Perl DBI 官方文档。
| S. No. | API & 描述 |
|---|---|
| 1 |
DBI→connect($data_source, “userid”, “password”, \%attr) 建立到请求的 $data_source 的数据库连接或会话。如果连接成功，则返回数据库句柄对象。 数据源的格式如下：DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg 是 PostgreSQL 驱动程序名称，testdb 是数据库名称。 |
| 2 |
$dbh→do($sql) 此例程准备并执行单个 SQL 语句。返回受影响的行数或 undef 出错。返回值 -1 表示行数未知、不适用或不可用。这里 $dbh 是 DBI→connect() 调用返回的句柄。 |
| 3 |
$dbh→prepare($sql) 该例程为数据库引擎稍后执行准备一条语句，并返回对语句句柄对象的引用。 |
| 4 |
$sth→execute() 该例程执行执行准备好的语句所需的任何处理。如果发生错误，则返回 undef。无论受影响的行数如何，成功的执行总是返回 true。这里 $sth 是 $dbh→prepare($sql) 调用返回的语句句柄。 |
| 5 |
$sth→fetchrow_array() 此例程获取下一行数据并将其作为包含字段值的列表返回。空字段作为列表中的 undef 值返回。 |
| 6 |
$DBI::err 这等效于 $h→err，其中 $h 是任何句柄类型，如 $dbh、$sth 或 $drh。这将从上次调用的驱动程序方法返回本机数据库引擎错误代码。 |
| 7 |
$DBI::errstr 这等效于 $h→errstr，其中 $h 是任何句柄类型，如 $dbh、$sth 或 $drh。这将返回来自最后调用的 DBI 方法的本机数据库引擎错误消息。 |
| 8 |
$dbh->disconnect() 该例程关闭先前通过调用 DBI→connect() 打开的数据库连接。 |
连接到数据库
以下 Perl 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
现在，让我们运行上面给出的程序来打开我们的数据库testdb；如果数据库成功打开，它将给出以下消息 –
Open database successfully
创建表
以下 Perl 程序将用于在先前创建的数据库中创建表 –
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
print $DBI::errstr;
} else {
print "Table created successfully\n";
}
$dbh->disconnect();
当上面给定的程序被执行时，它会在你的testdb 中创建 COMPANY 表，它会显示以下消息 –
Opened database successfully Table created successfully
插入操作
以下 Perl 程序显示了我们如何在上面示例中创建的 COMPANY 表中创建记录 –
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();
执行上述给定程序时，它将在 COMPANY 表中创建给定记录，并显示以下两行 –
Opened database successfully Records created successfully
选择操作
以下 Perl 程序显示了我们如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
更新操作
下面的 Perl 代码展示了我们如何使用 UPDATE 语句来更新任何记录，然后从我们的 COMPANY 表中获取和显示更新的记录 –
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
}else{
print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
删除操作
以下 Perl 代码显示了我们如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余的记录 –
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else{
print "Total number of rows deleted : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
PostgreSQL – Python 接口
安装
PostgreSQL 可以使用 psycopg2 模块与 Python 集成。sycopg2 是 Python 编程语言的 PostgreSQL 数据库适配器。编写 psycopg2 的目的是非常小和快速，并且像岩石一样稳定。您不需要单独安装此模块，因为默认情况下它随 Python 2.5.x 版一起提供。
如果你的机器上没有安装它，那么你可以使用 yum 命令来安装它，如下所示 –
$yum install python-psycopg2
要使用 psycopg2 模块，您必须首先创建一个代表数据库的 Connection 对象，然后您可以选择创建有助于您执行所有 SQL 语句的游标对象。
Python psycopg2 模块 API
以下是重要的 psycopg2 模块例程，它们可以满足您从 Python 程序使用 PostgreSQL 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 Python psycopg2 模块的官方文档。
| S. No. | API & 描述 |
|---|---|
| 1 |
psycopg2.connect(database=”testdb”, user=”postgres”, password=”cohondob”, host=”127.0.0.1″, port=”5432″) 此 API 打开与 PostgreSQL 数据库的连接。如果数据库打开成功，它返回一个连接对象。 |
| 2 |
connection.cursor() 此例程创建一个游标，它将在您使用 Python 进行的数据库编程中使用。 |
| 3 |
cursor.execute(sql [, optional parameters]) 该例程执行 SQL 语句。SQL 语句可以参数化（即，占位符而不是 SQL 文字）。psycopg2 模块支持使用 %s 符号的占位符 例如：cursor.execute(“insert into people values (%s, %s)”, (who, age)) |
| 4 |
cursor.executemany(sql, seq_of_parameters) 此例程针对在序列 sql 中找到的所有参数序列或映射执行 SQL 命令。 |
| 5 |
cursor.callproc(procname[, parameters]) 此例程执行具有给定名称的存储数据库过程。对于过程期望的每个参数，参数序列必须包含一个条目。 |
| 6 |
cursor.rowcount 此只读属性返回由上次执行*() 修改、插入或删除的数据库行总数。 |
| 7 |
connection.commit() 此方法提交当前事务。如果不调用此方法，则自上次调用 commit() 以来所做的任何事情都不会从其他数据库连接中看到。 |
| 8 |
connection.rollback() 此方法回滚自上次调用 commit() 以来对数据库的任何更改。 |
| 9 |
connection.close() 此方法关闭数据库连接。请注意，这不会自动调用 commit()。如果您在没有先调用 commit() 的情况下关闭数据库连接，您的更改将会丢失！ |
| 10 |
cursor.fetchone() 此方法获取查询结果集的下一行，返回单个序列，或者在没有更多数据可用时返回 None。 |
| 11 |
cursor.fetchmany([size=cursor.arraysize]) 该例程获取查询结果的下一组行，返回一个列表。当没有更多行可用时，将返回一个空列表。该方法尝试获取由 size 参数指示的尽可能多的行。 |
| 12 |
cursor.fetchall() 此例程获取查询结果的所有（剩余）行，返回一个列表。当没有可用行时，返回一个空列表。 |
连接到数据库
以下 Python 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
#!/usr/bin/python import psycopg2 conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432") print "Opened database successfully"
在这里，您还可以提供数据库testdb作为名称，如果数据库成功打开，则会显示以下消息 –
Open database successfully
创建表
以下 Python 程序将用于在先前创建的数据库中创建表 –
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print "Table created successfully"
conn.commit()
conn.close()
当上面给定的程序被执行时，它会在你的test.db 中创建 COMPANY 表，它会显示以下消息 –
Opened database successfully Table created successfully
插入操作
以下 Python 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录 –
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()
print "Records created successfully";
conn.close()
执行上述给定程序时，它将在 COMPANY 表中创建给定记录，并显示以下两行 –
Opened database successfully Records created successfully
选择操作
以下 Python 程序显示了我们如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall()
for row in rows:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
更新操作
下面的 Python 代码展示了我们如何使用 UPDATE 语句来更新任何记录，然后从我们的 COMPANY 表中获取和显示更新的记录 –
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", cur.rowcount
cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall()
for row in rows:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
删除操作
下面的 Python 代码展示了我们如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余的记录 –
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"
cur = conn.cursor()
cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", cur.rowcount
cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall()
for row in rows:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()
当上面给定的程序被执行时，它会产生以下结果 –
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
