笔记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学习篇》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 【每日笔记】【Go学习笔记】2019-01-04 Codis笔记
- 【每日笔记】【Go学习笔记】2019-01-02 Codis笔记
- 【每日笔记】【Go学习笔记】2019-01-07 Codis笔记
- Golang学习笔记-调度器学习
- Vue学习笔记(二)------axios学习
- 算法/NLP/深度学习/机器学习面试笔记
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Web软件用户界面设计指南
林锐、唐勇、石志强 / 电子工业出版社 / 2005-5-1 / 20.00元
Web软件用户界面设计指南,ISBN:9787121010163,作者:林锐等编著一起来看看 《Web软件用户界面设计指南》 这本书的介绍吧!