PostgreSQL入门

简介

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。

特征

• 函数：通过函数，可以在数据库服务器端执行指令程序。

• 索引：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST 索引。

• 触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。

• 规则：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。

• 数据类型：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据等。

• 全文检索：通过 Tsearch2 或 OpenFTS，8.3版本中内嵌 Tsearch2。

数据类型

名字	存储长度	描述	范围
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

操作

创建数据库

1.命令方式

CREATE DATABASE dbname

2.pgAdmin 工具创建数据库

选择数据库

1.命令方式
使用 \l 用于查看已经存在的数据库：

postgres=# \l

接下来我们可以使用 \c + 数据库名 来进入数据库：

postgres=# \c runoobdb

2.pgAdmin 工具

删除库

1.命令方式
例如，我们删除一个 runoobdb 的数据库：

postgres=# DROP DATABASE runoobdb

2.pgAdmin 工具

创建表格

CREATE TABLE 语法格式如下：

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
)

以下创建了一个表，表名为 COMPANY 表格，主键为 ID，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
)

---

可以使用 \d 命令来查看表格是否创建成功；
可以使用\d tablename 查看表格信息；

删除表格

DROP TABLE table_name

PostgreSQL增删改查

插入数据

在PostgreSQL中，INSERT查询用于在表中插入新行。 您可以一次插入单行或多行到表中。

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

实例：

INSERT INTO EMPLOYEES(  ID, NAME, AGE, ADDRESS, SALARY)  
VALUES
 (1, '张三', 25, '北京市', 189990.00 ), 
(2, '李四', 25, '广州市 ', 125000.00 ), 
(3, '王五', 21, '上海市', 185000.00),   
(4, '赵六', 24, '兰州市', 65000.00), 
(5, '田七', 21, '酒泉市', 8500.00);

查询

语法：

SELECT "column1", "column2".."column" FROM "table_name";

实例：

SELECT * FROM "table_name"

更新

语法：

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

删除

语法：

DELETE FROM table_name  
WHERE [condition];

Python操作PostgreSQL

PostgreSQL可以使用psycopg2模块与Python集成。

连接到数据库

import psycopg2

conn = psycopg2.connect(database="testdb", user="postgres", password="pass123", host="127.0.0.1", port="5432")

创建表

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);''')


conn.commit()
conn.close()

插入操作

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 )");

SELECT操作

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")

更新操作

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit

删除操作

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit