笔记10：PostgreSQL学习篇

栏目: 数据库 · PostgreSQL · 发布时间: 5年前

内容简介：摘要：日常学习中对一些知识点进行总结得出该系列文章。学习笔记内容包括前端技术，Django web开发技术，数据库技术如MySQL，MongoDB，PGSQL等等。此外还有一些工具如Dock，ES等等。（本文原创，转载必须注明出处.）PostgreSQL是一个开源对象关系数据库管理系统(ORDBMS)。包括PostgreSQL语言的所有主题，如创建数据库，创建表，删除数据库，删除表，选择数据库，选择表，插入记录，更新记录，删除记录，触发器，功能，过程，游标等。帮助您更好地了解PostgreSQL语言和使用P

摘要：日常学习中对一些知识点进行总结得出该系列文章。学习笔记内容包括前端技术，Django web开发技术，数据库技术如MySQL，MongoDB，PGSQL等等。此外还有一些工具如Dock，ES等等。（本文原创，转载必须注明出处.）

PGSQL

PostgreSQL是一个开源对象关系数据库管理系统(ORDBMS)。包括PostgreSQL语言的所有主题，如创建数据库，创建表，删除数据库，删除表，选择数据库，选择表，插入记录，更新记录，删除记录，触发器，功能，过程，游标等。帮助您更好地了解PostgreSQL语言和使用PostgreSQL数据库。PostgreSQL是跨平台的，可以在许多操作系统上运行，如Linux，FreeBSD，OS X，Solaris和Microsoft Windows等。

1 PGSQL特点

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

（2）基本操作

创建数据库：CREATE DATABASE database_name;
查看数据库：postgres=#
删除数据库：DROP DATABASE
创建表：

CREATE TABLE public.student2
(
  id integer NOT NULL,
  name character(100),
  subjects character(1),
  CONSTRAINT student2_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.student2
  OWNER TO postgres;
COMMENT ON TABLE public.student2
  IS '这是一个学生信息表2';

删除表

postgres=#
postgres=# drop table student2;
DROP TABLE
postgres=#

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

CREATE SCHEMA schema_name;

在架构中创建表

-- Table: myschema.tb_test
-- DROP TABLE myschema.tb_test;

CREATE TABLE myschema.tb_test
(
  id integer,
  name character(254)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE myschema.tb_test
  OWNER TO postgres;

使用架构的优点：

模式有助于多用户使用一个数据库，而不会互相干扰。
它将数据库对象组织成逻辑组，使其更易于管理。
可以将第三方模式放入单独的模式中，以避免与其他对象的名称相冲突。

2 查询

（1）插入语句

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

INSERT INTO EMPLOYEES(  ID, NAME, AGE, ADDRESS, SALARY)  
VALUES
 (1, 'Maxsu', 25, '海口市人民大道2880号', 109990.00 ), 
(2, 'minsu', 25, '广州中山大道 ', 125000.00 ), 
(3, '李洋', 21, '北京市朝阳区', 185000.00),   
(4, 'Manisha', 24, 'Mumbai', 65000.00), 
(5, 'Larry', 21, 'Paris', 85000.00);

（2）基本数据库操作查询，修改，删除，添加，order by，分组group，条件查询，and，or，like，not，in，between，全连接，内连接，右连接，左连接等跟 mysql 数据库操作基本一样

3 高级操作

（1）视图

CREATE VIEW current_employees AS  
SELECT NAME, ID, SALARY 
FROM EMPLOYEES;

drop view view_name

（2）存储过程

CREATE OR REPLACE FUNCTION totalRecords ()  
RETURNS integer AS $total$  
declare  
    total integer;  
BEGIN  
   SELECT count(*) into total FROM EMPLOYEES;  
   RETURN total;  
END;  
$total$ LANGUAGE plpgsql;

select totalRecords ()

(3) 触发器，可以使用某个表插入一条信息之后，触发器将记录写进日志表中，配合存储过程调用触发器例子

CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY  
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

PostgreSQL触发器可用于以下目的：

验证输入数据。
执行业务规则。
为不同文件中新插入的行生成唯一值。
写入其他文件以进行审计跟踪。
从其他文件查询交叉引用目的。
访问系统函数。
将数据复制到不同的文件以实现数据一致性。

使用触发器的优点

它提高了应用程序的开发速度。因为数据库存储触发器，所以您不必将触发器操作编码到每个数据库应用程序中。
全局执法业务规则。定义触发器一次，然后将其重用于使用数据库的任何应用程序。
更容易维护如果业务策略发生变化，则只需更改相应的触发程序，而不是每个应用程序。
提高客户/服务器环境的性能。所有规则在结果返回之前在服务器中运行。

（4）索引

数据库索引的重要特点

索引使用 SELECT 查询和 WHERE 子句加速数据输出，但是会减慢使用 INSERT 和 UPDATE 语句输入的数据。
您可以在不影响数据的情况下创建或删除索引。
可以通过使用 CREATE INDEX 语句创建索引，指定创建索引的索引名称和表或列名称。
还可以创建一个唯一索引，类似于唯一约束，该索引防止列或列的组合上有一个索引重复的项。

# 单列索引
CREATE INDEX index_name  
ON table_name (column_name);
# 多列索引
CREATE INDEX multicolumn_index  
ON EMPLOYEES (name, salary);
# 唯一索引
CREATE UNIQUE INDEX index_name  
on table_name (column_name);
# 删除索引
DROP INDEX index_name;

避免使用索引？

NULL

（5）UNIONS

PostgreSQL UNION子句/运算符用于组合两个或多个SELECT语句的结果，而不返回任何重复的行。要使用UNION，每个SELECT必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同。

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;

UNION ALL 运算符用于组合两个 SELECT 语句(包括重复行)的结果。

（6）修改表

# 添加新列
alter table table_name add column_name datatype;
# 删除列
alter table table_name drop column_name;
# 修改列不为空
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
# 添加唯一约束
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
# 添加主键
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
# 删除主键
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

(7) 事务

以下命令用于控制事务：

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

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

BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

(8) 锁

锁或独占锁或写锁阻止用户修改行或整个表。在 UPDATE 和 DELETE 修改的行在事务的持续时间内被自动独占锁定。这将阻止其他用户更改行，直到事务被提交或回退。用户必须等待其他用户当他们都尝试修改同一行时。如果他们修改不同的行，不需要等待。 SELECT查询不必等待。数据库自动执行锁定。然而，在某些情况下，必须手动控制锁定。手动锁定可以通过使用 LOCK 命令完成。它允许指定事务的锁类型和范围。

LOCK 命令的基本语法如下：

LOCK [ TABLE ]
name
 IN
lock_mode


SQL

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

（9）自动增长：REAL

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

4 Python 连接PGSQL

PostgreSQL可以使用 psycopg2 模块与Python集成。 sycopg2 是用于Python编程语言的PostgreSQL数据库适配器。 psycopg2 是非常小，快速，稳定的。您不需要单独安装此模块，因为默认情况下它会随着 Python 2.5.x 版本一起发布。如果还没有在您的机器上安装它，那么可以使用 yum 命令安装它，如下所示：

$yum install python-psycopg2

Shell

要使用 psycopg2 模块，必须首先创建一个表示数据库的 Connection 对象，然后可以选择创建可以帮助您执行所有SQL语句的游标对象。

（1）连接到数据库

#!/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"

（2）创建表

以下Python程序将用于在先前创建的数据库( testdb )中创建一个表：

#!/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()

（3）插入操作

在上述示例中创建的 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 )");

conn.commit()
print "Records created successfully";
conn.close()

（4）SELECT操作

以下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()

（5）更新操作

使用 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()

（6）删除操作

使用 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()

技术交流共享QQ群

【 机器学习和自然语言QQ群：436303759 】：

机器学习和自然语言（QQ群号：436303759）是一个研究深度学习、机器学习、自然语言处理、数据挖掘、图像处理、目标检测、数据科学等AI相关领域的技术群。其宗旨是纯粹的AI技术圈子、绿色的交流环境。本群禁止有违背法律法规和道德的言谈举止。群成员备注格式：城市-自命名。微信订阅号：datathinks

以上所述就是小编给大家介绍的《笔记10：PostgreSQL学习篇》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对码农网的支持！