PostgreSQL 入门

PostgreSQL 入门

简介:

1. 是一个免费的对象-关系数据库服务器(ORDBMS)
2. PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。

ORDBMS 术语

- 数据库: 数据库是一些关联表的集合。

- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

- 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。

- 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。

- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

- 外键：外键用于关联两个表。

- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

PostgreSQL 特征

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

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

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

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

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

等。

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

- NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 数据库的外部数据包装器。

- 数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 进行 ETL。

PSQL语法

#  Linux 操作系统连接数据库. 
	sudo -i -u postgres 
# 查看 帮助 \help
	\help <指令>
    # 例如: 
    	\help SELECT

PostgreSQL 命令

# abort 用于退出当前事务    

### alter 系列
# alter aggregate 修改 聚合函数的定义
# alter collation 修改 排序规则定义
# alter conversion 修改 编码转换的定义
# alter database  修改 数据库
# alter default privileges 定义 默认访问的权限
# alter domain 修改 域的定义
# alter function  修改 函数的定义
# alter group 修改 用户组
# alter index 修改 索引的定义。
# alter language 修改 过程语言的定义
# alter operator 修改 操作符的定义
# alter operator class 修改 操作符的定义
# alter schema 修改 模式的定义
# alter sequence 修改 序列生成器的定义
# alter table 修改 表的定义
# alter tablespace 修改 表空间的定义
# alter trigger 修改 触发器的定义
# alter type 修改 类型的定义
# alter user 修改 数据库帐号

### create 系列
# create aggregate 定义 新的聚合函数
# create cast 定义 用户定义的转换
# create constraint trigger 定义新的约束触发器
# create conversion 定义新的编码转换
# create database  创建新的数据库
# create domina 创建新域
# create function 定义一个新函数
# create group 定义一个用户组
# create index 定义一个新索引
# create language 定义新的过程语言
# create operator 定义 新的操作符
# 

### 其他系列
# analyze 收集 与数据库有关的统计
# begin 开始一个事务块
# checkpoint 强制 一个事务日志检查点
# close  关闭 游标
# cluster 根据 某个 索引对某个 表盘簇化排序
# comment 定义 或 改变 一个对象的注释
# commit 提交当前事务
# copy 在表 和 文件 之间拷贝数据

建库建表

# 建库
	- CREATE DATABASE dbname;
# 选择数据库
	- \c  dbname
# 建表
    CREATE TABLE table_name(
       column1 datatype,
       column2 datatype,
       column3 datatype,
       .....
       columnN datatype,
       PRIMARY KEY( 一个或多个列 )
    );

创建模式SCHEMA

# 模式schema 可以看成是一个表的集合
	- 一个模式可以包含视图/索引/数据类型/函数/操作符等
	- 相同对象名称可以被应用于不同模式不会冲突
   	- 模式的优势:
        1. 允许多个用户使用一个数据库,不会相互干扰
        2. 将数据库对象组织成逻辑组以便更容易管理
        3. 第三方应用的对象可以放在独立的模式中. 不会与其他对象的名称发生冲突

# 创建模式
	- create schema myschema;
# 建立模式表
    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)
    );
# 删除模式
	- drop schema myschema;

psql 数据操作

# CRUD
	- 与 mysql无异
    
# where 条件语句

# and 且 & or 或 操作

# like 匹配
	- 两种通配符: %  和 _ (占位符)
    - like '%a' # 已a结尾
    - like 'a%' # 已a开头
    - like '%a%' # 包含a的数据
    - like 'a%%'  # 已a开头的字符,长度大于3的数据
    
    - like '_00%'  # 第二第三个位置是0的数据
    - like '2___3' # 2 开头 ,3 结尾长度为5的数据
    - like '_2%3'  # 第二个位置上为2,且已3结尾的数据

# limit 限制数据的数量
	- limit 1 # 取一条
    - limit 3 offset 2 # 偏移2条后,取3条

# order by 排序 
	- desc 降序
    - asc 升序 默认
    
# group by 分组 
	- ** 分组后, 只有分组的字段可以单独查询
    - ** 分组后, 可对非分组的数据进行聚合操作

# with 子句提供了一种编写辅助语句的方法
	- 解释: 两张不同的表(最好是有关联的表),相当于将一部分SQL语句固定,可在select中使用
    - 用法: 
        WITH RECURSIVE t(n) AS (
           VALUES (0)
           UNION ALL
           SELECT SALARY FROM COMPANY WHERE SALARY < 20000
        )
        SELECT sum(n) FROM t;
        
# having 
	- 分组后的条件语句
    - 例如: 
        SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
        
# distinct 去重

PostgreSQL 约束

# NOT NULL：
	- 指示某列不能存储 NULL 值。

# UNIQUE：
	- 确保某列的值都是唯一的。

# PRIMARY Key：
	- NOT NULL 和 UNIQUE 的结合。确保某列（或两个列
多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

# FOREIGN Key：
	- 保证一个表中的数据匹配另一个表中的值的参照完整性。
	- 关键字 :references  表名(字段)
        
# CHECK：
	- 保证列中的值符合指定的条件。

# EXCLUSION ：
	- 排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。
    
    
    
# 删除约束
	ALTER TABLE table_name DROP CONSTRAINT some_name;

PostgreSQL 连接(JOIN)

# CROSS JOIN ：交叉连接	
	- 数据大量冗余
    - 笛卡尔积

# INNER JOIN：内连接
	- 仅包含两个表都有的数据
    
# LEFT OUTER JOIN：左外连接
	- 以左侧表为主
    - 当左侧出现的数据,右侧未曾出现,则右侧空匹配
    
# RIGHT OUTER JOIN：右外连接
	- 以右侧表为主
    - 当右侧出现的数据,左侧未曾出现,则左侧空匹配.
    
# FULL OUTER JOIN：全外连接
	- 左右连接 合成表联合, 不同于内连接(会有空值出现)

PostgreSQL UNION 操作符

#  UNION 
	- 操作符合并两个或多个 SELECT 语句的结果
	- 操作符用于合并两个或多个 SELECT 语句的结果集。
	- 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型
	- 每个 SELECT 语句中的列的顺序必须相同
    
    
# NION 例子:
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 语句
    - 允许重复的值
    
# UNION ALL 例子:
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
    ON COMPANY.ID = DEPARTMENT.EMP_ID;

PostgreSQL 别名

# AS 创建别名

PostgreSQL 触发器

# 触发器
	- 是数据库的回调函数
    - 会在指定事件发生时,自动执行/调用
    - 特点:
        1. 在执行之前触发(插入/更新/删除之前)
        2. 在执行之后触发(插入/更新/删除之后)
        3. 更新操作(对视图进行插入/更新/删除)
     	4. 触发器 for each row属性时可选的. 修改时每行调用一次
        5. 触发器 FOR EACH STATEMENT , 不管修改多少行.每个语句标记的触发器执行一次
        6. when子句和触发器在引用 NEW.columns-name 和 OLD.columns-name 表单插入,删除或更新时可以访问每一行元素.
其中columns-name是触发器关联的表中的列的名称
		7. 存在when 子句 ,PSQL语句只会执行 when子句成立的那一行,如果没有when子句,则每一行都执行
    	8. before 和 after 关键字决定何时执行触发器动作,决定是在关联行的插入/修改/删除. 之前或之后执行
        9. 修改表必须存在同一数据库中,作为触发器被附加的表或视图,且必须只使用tablename,而不是database.tablename
        10. 当创建约束触发器时会指定约束选选项,这与常规触发器相同. 只是可以使用这种约束来调整触发器的时间.
        11. 当触发器的实现的约束被违反,将跑出异常

触发器案例

# 1. 创建触发器函数 auditlogfunc
    CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
       BEGIN
          INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
          RETURN NEW;
       END;
    $example_table$ LANGUAGE plpgsql;

# 2. 创建触发器
	CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
  
# 3. 执行 插入语句,将会在 AUDIT表中插入一条记录
	INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
    
    
# 4. 查询触发器
	SELECT * FROM pg_trigger;
    
# 5. 删除触发器
	drop trigger example_trigger on company;

PostgreSQL 索引

# 索引
	- 加速搜索引擎检索
	- 加速 select 和 where 子句
	- 减慢 update 和 insert 语句数据的录入
	- create index 创建索引
	- 索引具有唯一性

索引类型

# 1. 单 索引
	CREATE INDEX index_name
ON table_name (column_name);

# 2. 组合索引(多列)
	CREATE INDEX index_name
ON table_name (column1_name, column2_name);

# 3. 唯一索引
	CREATE UNIQUE INDEX index_name
on table_name (column_name);

# 4. 局部索引
	- 局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行
    
	CREATE INDEX index_name
on table_name (conditional_expression);

# 5. 隐式索引
	隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

什么情况下要避免使用索引？

索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。

PostgreSQL View（视图）

# 视图 
	- 预定义 查询形式存在的表组合
    - 可以查询一个所有行 或 从一个/多个表选定行
    - 可从一个 或 多个 表创建
# 视图 虚拟表特点
	- 用户/用户组更直观的查找方式
    - 限制数据访问,只能看到有限的数据,不是完整的表
    - 汇总各种表中的数据,可生成报告
   	- 视图 `只读` 无法进行读写操作. 可在视图上建立触发器,利用触发器执行读写操作

视图案例

# 1. 创建视图
	CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE
FROM  COMPANY;

# 2. 使用视图查询
	SELECT * FROM COMPANY_VIEW;

# 3. 删除视图 
	drop view view_name

PostgreSQL TRANSACTION（事务）

# ACID 属性
	- 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

	- 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
	
	- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
	
	- 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

事务案例

# 1. 启动事务
	- BEGIN;
	- BEGIN TRANSACTION;

# 2. 提交事务
	- commit

# 3. 回滚
	- rollback 
	
# 例子🌰	
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

PostgreSQL 子查询

# 子查询
	- 内部查询,嵌套查询,在where子句种迁入查询语句
	- 一个select 查询的结果作为另外一个语句的输入值
	- 必须使用 花括号 () 括起来
	- 子查询 select子句中只能有一个列. 除非主查询多列,与子查询选中的列比较 
	- order by 不能在子查询种使用.
	- group by 可以再子查询种使用
	- 子查询返回很多行,只能与多值运算符一起使用.如IN
	- BETWEEN 运算符不能与子查询一起使用. between可以在子查询内部使用

# 案例:
	 SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY  WHERE SALARY > 45000) ;

# 子查询 插入案例
	INSERT INTO COMPANY_BKP SELECT * FROM COMPANY  WHERE ID IN (SELECT ID FROM COMPANY) ;
    
# 子查询 更新案例
	UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );
    
# 子查询 删除案例
	DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );

PostgreSQL LOCK（锁）

# 锁 🔒
	- 为了保持数据库一致性,阻止用户修改一行/整表
	- 一般用于较高的并发
	- 排他锁和共享锁
	- 锁 只在事务下执行
	
# 语法:
	LOCK 表
	name in lock_mode
	
	- name : 锁的名称
	- lock_mode : 锁的模式: ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。
	
	
# 死锁
	两个事物彼此等待对方完成. 

# 咨询锁
	咨询锁
PostgreSQL 提供了创建具有应用程序定义含义的锁的方法。这些被称为咨询锁。由于系统不强制使用它们，所以正确使用它们取决于应用程序。咨询锁对于不适合 MVCC 模型的锁定策略非常有用。

例如，咨询锁的一个常见用途是模拟所谓"平面文件"数据管理系统中典型的悲观锁定策略。虽然存储在表中的标志可以用于相同的目的，但是通知锁更快，避免了表膨胀，并且在会话结束时由服务器自动清理。


# 案例
	BEGIN;
	LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;