PostgreSQL的使用2

PostgreSQL的使用2
一、约束
约束创建时间：1.表格创建阶段(Create table) 2.表格修改阶段（Alter table)
几类约束：

约束	作用
not null	不为空约束
unique	确保某一列值的唯一性
pk	主键
fk	外键
check	保证某列的值符合某一条件

其中fk与mysql有所不一致：
外键约束：

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)-->这里直接用references
);

check约束复习：

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

删除约束：

ALTER TABLE table_name DROP CONSTRAINT some_name;

二、JOIN连接
join类型

详解：
cross join:就是mysql的join,也就是笛卡尔积
inner join:就是mysql的natural join，保存相同的
left outer join:左外连接，左表相关列的值都保存，如果右表没有则显示空
right outer join:右外连接，与左外连接反过来即可
full outer join:外连接，也就是左外和右外合起来而已

语法：** inner join/left outer join/right outer join/full outer join** on **

三、Union
一般用得比较少，几乎可以用join代替，且join比较简洁
语法：

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL/UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

四、NULL和AS
NULL：
·设置某个值为空
·选择某属性为空/不为空的对象

SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL

AS：与mysql一致，不赘述

五、触发器
PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用
5.1 几个重要的点：
1.触发器的触发时间点：
①执行操作前（在检查约束并尝试插入、更新或删除之前）
②执行操作后（在检查约束并尝试插入、更新或删除之后）
③执行操作时（在检查约束并尝试插入、更新或删除时）

2.触发器的调用次数
FOR EACH ROW 当操作修改时每行调用一次
STAMENT 不管修改了多少行，每个语句标记的触发器执行一次

5.2 实例理解
Company 表：

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

AUDIT表：每当 COMPANY 表中有一个新的记录项时，日志消息将被插入其中

CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

在company表上创建触发器

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

其中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;

总体上来将便是：在表上创建一个触发器，当向该表插入、删除、更新操作时候，会执行触发器定义的函数，实现我们需要的相应功能，由于此过程是由插入、删除、更新操作触发的，因此叫做触发器。

删除触发器drop trigger example_trigger on company;
六、索引
加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
6.1 创建索引

CREATE INDEX index_name ON table_name;

6.2 索引的类型
单例索引：一列

CREATE INDEX index_name
ON table_name (column_name);

组合索引：多列

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

唯一索引：

CREATE UNIQUE INDEX index_name
ON table_name (column1_name, column2_name);

局部索引：表的子集上构建的索引
隐式索引：隐式索引 是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束
6.3 删除索引

DROP INDEX index_name;

七、ALTER TABLE命令

作用：添加、删除、修改表的列；添加、删除约束
1.添加列：

ALTER TABLE company add age int(10);

2.删除列：

ALTER TABLE company **drop** age int(10);

3.修改列名称和类型：

alter table teacher rename column age to age1 

alter table teacher alter age1 type smallint using age1::smallint

注意这里不同于mysql的modify
4.添加约束

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

5.添加主键

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

6.删除约束和主键都是 DROP CONSTRAINT

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

八、TRUNCATE TABLE命令
删除表的数据但不删除表的结构，相当于Mysql的delete from
语法：RUNCATE TABLE table_name;

九、视图
和mysql一致

CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

十、事务
1、TRANSACTION：通常包含了一个序列的对数据库的读/写操作
2、目的：
3、事务的ACID原则：老生常谈了
4、事务控制
·事务的开启：BEGIN或BEGIN TRANSACTION
遇到COMMIT或ROLLBACK才停止，不过在数据库关闭或发生错误时，事务处理也会回滚

·事务提交:COMMIT
把事务调用的更改保存到数据库中的事务命令，即确认事务

·事务回滚：ROLLBACK
用于撤消尚未保存到数据库的事务命令，即回滚事务

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

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

十一、锁
1.作用：保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。

2.分类：排它锁（Exclusive Locks）和共享锁（Share Locks）。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

十二、子查询和自增
子查询于mysql一致，不赘述
在PostgreSQL中，自增可以用smallserial、serial 和 bigserial来解决

CREATE TABLE runoob
(
    id serial NOT NULL,
    alttext text,
    imgurl text
)

十三、权限
PRIVILEGES

GRANT授权，REVOKE取消权限


十四、常用函数
1.聚合函数（mysql的聚积函数）

其余函数可以到菜鸟教程看
https://www.runoob.com/postgresql/postgresql-functions.html