Postgresql学习笔记-高级语法篇

Postgresql学习笔记-高级语法篇

Postgresql 约束

• Postgresql约束用于规定表中的数据规则。

• 如果存在违反约束的数据行为，行为会被约束终止。

• 约束可以在创建表的时候就规定（通过CREATE TABLE 语句），或者在表创建之后规定（通过ALTER TABLE 语句）。

• 约束确保了数据库中数据的准确性和可靠性。

• 约束可以是列级或者表级。列级约束仅适用于列，表级约束被应用到整个表。

• 以下是在Postgresql中常用的约束：

  名称	说明
  NOT NULL	指示某列不能存储NULL值
  UNIQUE	确保某列的值都是唯一的
  PRIMARY Key	NOT NULL和UNIQUE的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  FOREIGN Key	保证一个表中的数据匹配另一个表中的值的参照完整性。
  CHECK	保证列中的值符合指定的条件。
  EXCLUSION	排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将返回false或空值。

  语法实例

  CREATE TABLE COMPANY3(
      ID INT PRIMARY KEY NOT NULL,
      NAME TEXT NOT NULL,
      AGE INT NOT NULL UNIQUE,
      ADDRESS CHAR(50),
      SALARY REAL CHECK(SALARY > 0),
      EXCLUDE USING gist
      (NAME WITH =,   -- 如果满足NAME相同，AGE不相同则允许插入，否则不允许插入。
       AGE WITH <>)   -- 其比较的结果是如果整个表达式返回true，则不允许插入，否则允许。
  )
  

  注意

  • 主键是非空约束和唯一约束的组合，一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。
  • 外键约束，将指定列（或一组列）中的值必须匹配另一个表的某一行中出现的值。通常一个表中的外键指向另一个表中的UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。

• 删除约束

  ALTER TABLE table_name DROP CONSTRAINT some_name;
  

Postgresql UNION操作符

• UNION操作符用于合并两个或多个SELECT 语句的结果集。

• UNION内部的每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT语句中的列的顺序必须相同。

• 基础语法

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

• 演示

  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 join department on company.id=department.emp_id;
  -- 若要连接有重复行的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 join department on company.id=department.emp_id;
  

Postgresql 触发器

• 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

• 关于触发器有几个比较重要的点：

  • PostgreSQL 触发器可以在下面几种情况下触发：
    • 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
    • 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
    • 更新操作（在对一个视图进行插入、更新、删除时）。
  • 触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，不选中 FOR EACH STATEMENT，不管修改了多少行，每个语句标记的触发器执行一次。
  • WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。
  • 如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。
  • BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
  • 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，必须只使用 tablename，而不是 database.tablename。
  • 当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。

  语法

  --创建触发器时的基础语法如下：
  CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
  ON table_name
  [
   -- 触发器逻辑....
  ];
  --在这里，event_name可以是在所提到的表table_name上的INSERT、DELETE和UPDATE数据库操作。也可以在表名后选择指定FOR EACH ROW。
  --例，在UPDATE操作上在表的一个或多个指定列上创建触发器的语法：
  CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
  ON table_name
  [
    -- 触发器逻辑....
  ]
  

  举例

  --创建company表和audit表，company表用于生产表，audit表用于日志记录。
  -- company表 --
  CREATE TABLE COMPANY(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL
  );
  -- audit表 --
  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()是postgresql一个程序，其定义如下：
  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;
  

• 列出触发器

  select * from pg_trigger;
  

• 删除触发器

  drop trigger ${trigger_name} on ${table_of_trigger_dependent};
  -- 举例 --
  -- 删除本文上表company上的触发器example_trigger的指令为：
  drop trigger example_trigger on company;
  

Postgresql索引

• 索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。

• 使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

  语法

  CREATE INDEX index_name ON table_name;
  

• 索引类型

  类型	语法
  单列索引	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 (column_name);
  局部索引	CREATE INDEX index_name on table_name (conditional)
  隐式索引	建表时由数据库服务器自动创建，一般为主键约束和唯一约束

  注意

  • 不管是单列索引还是组合索引，该索引必须是在WHERE子句的过滤条件中使用非常频繁的列。

  • 唯一索引的使用，一方面提高了查询性能，同时也保护了数据的完整性，不允许任何重复的值插入到表中。

  • 删除索引：

    drop index index_name;
    

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

    • 表数据较小时不需要使用索引
    • 进行频繁插入或更新操作的表不需要使用
    • 不应该使用在含有大量的NULL值的列上
    • 不使用在频繁操作的列上

Postgresql ALTER TABLE命令

• 在postgresql中，alter table命令用于添加、修改、删除一张已经存在表的列。

• 也可以使用alter table命令添加和删除约束。

• 语法

  • 在一张已存在的表上添加列的语法：

    -- table_name ：表名 ; column_name：新列名; datatype：数据类型
    ALTER TABLE table_name ADD column_name datatype;
    

  • 在一张已存在的表上删除列的语法：

    ALTER TABLE table_name DROP COLUMN coulumn_name;
    

  • 修改表中某列的DATA_TYPE（数据类型）的语法：

    ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
    

  • 给表中某列添加NOT NULL约束，语法如下：

    ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
    

  • 给表中某列ADD UNIQUE CONSTRAINT（添加UNIQUE约束），语法如下：

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

  • 给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束）：

    ALTER TABLE table_name
    ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
    

  • 给表ADD PRIMARY KEY（添加主键）：

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

  • 删除约束：

    ALTER TABLE table_name
    DROP CONSTRAINT MyUniqueConstraint;
    

Postgresql View(视图)

• view(视图)是一张假表，只不过是通过相关的名称存储在数据库中的一个Postgresql语句。

• view(视图)实际上是一个以预定义的postgresql查询形式存在的表的组合。

• view(视图)可以包含一个表的所有行或从一个或多个表选定行。

• view(视图)可以从一个或多个表创建，这取决于要创建视图的Postgresql查询。

• view(视图)是一种虚拟表，允许用户实现以下几点：

  • 用户或用户组认为更自然或直挂查找结构数据的方式。
  • 限制数据访问，用户只能看到有限的数据，而不是完整的表。
  • 汇总各种表中的数据，用于生成报告。

• PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

• 语法

  -- 创建视图
  CREATE [TEMP | TEMPORARY] VIEW view_name AS
  SELECT column1, column2.....
  FROM table_name
  WHERE [condition];
  -- 注意：可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。
  -- 实例：从表company创建视图 --
  CREATE VIEW COMPANY_VIEW AS
  SELECT ID, NAME, AGE
  FROM  COMPANY; 
  -- 查询视图
  select * from view_name;
  -- 实例
  select * from company_view;
  -- 删除视图
  drop view view_name;
  --实例
  drop view company_view;
  

Postgresql TRANSACTION（事务）

• TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

• 数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：

  • 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

• 事务的属性：

  • 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  • 持久性：已被提交的事务对数据库的修改应该永久保存在数据库中。

• 事务控制

  • BEGIN TRANSACTION：开始一个事务。
    • COMMIT：事务确认，或者可以使用END TRANSACTION命令。
    • ROLLBACK：事务回滚。
  • 事务控制命令只与INSERT、UPDATE和DELETE一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

• 实例

  --创建company表，并写入数据（已有）
  -- 开始一个事务，并从表中删除 age = 25 的记录，最后，我们使用 ROLLBACK 命令撤消所有的更改
  begin; 
  delete from company where age = 25;
  rollback; --回滚
  -- 此时表中数据不会被删除
  -- 我们开始另一个事务，从表中删除 age = 25 的记录，最后我们使用 COMMIT 命令提交所有的更改
  begin;
  delete from company where age = 25;
  commit; -- 提交
  -- 再次检查数据，表中记录已被删除
  

Postgresql LOCK（锁）

• 锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表，一般用在并发较高的数据库中。

• 锁的类型：

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

• 语法

  LOCK [TABLE]
  name
  IN
  lock_mode
  -- name：要锁定的现有表的名称（可选模式限定）。如果只在表名之前指定，则只锁定该表。如果未指定，则锁定该表及其所有子表（如果有）
  -- lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：ACCESS SHARE，ROW SHARE， ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，ACCESS EXCLUSIVE。
  -- 实例
  -- 将COMPANY表锁定为ACCESS EXCLUSIVE模式：
  BEGIN;
  LOCK TABLE COMPANY IN ACCESS EXCLUSIVE MODE;
  

Postgresql 子查询

• 子查询或称为内部查询、嵌套查询，指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。

• 一个 SELECT 语句的查询结果能够作为另一个语句的输入值。

• 子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，并可使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

• 子查询必须遵循的几个规则：

  • 子查询必须用括号括起来。
  • 子查询在SELECT子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
  • ORDER BY不能用在子查询中，虽然主查询可以使用ORDER BY。可以在子查询中使用GROUP BY，功能与ORDER BY相同。
  • 子查询返回多于一行，只能与多值运算符一起使用，如IN运算符。
  • BETWEEN运算符不能与子查询一起使用，但是，BETWEEN可在子查询内使用。

• SELECT语句中的子查询使用

  SELECT column_name [, column_name ]
  FROM   table1 [, table2 ]
  WHERE  column_name OPERATOR
        (SELECT column_name [, column_name ]
        FROM table1 [, table2 ]
        [WHERE])
  -- 实例
  SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY  WHERE SALARY > 45000) ;
  

• INSERT 语句中的子查询使用

  INSERT INTO table_name [ (column1 [, column2 ]) ]
     SELECT [ *|column1 [, column2 ] ]
     FROM table1 [, table2 ]
     [ WHERE VALUE OPERATOR ]
  -- 实例
  --将具有相同结构的两个表进行数据的复制。
  insert into company_bak select * from company where id in(select id from company);
  

• UPDATE 语句中的子查询使用

  UPDATE table
  SET column_name = new_value
  [ WHERE OPERATOR [ VALUE ]
     (SELECT COLUMN_NAME
     FROM TABLE_NAME)
     [ WHERE) ]
  -- 实例
  update company set salary = salary*0.5 where age in (select age from company_bak where age >= 27);
  

• DELETE 语句中的子查询使用

  DELETE FROM TABLE_NAME
  [ WHERE OPERATOR [ VALUE ]
     (SELECT COLUMN_NAME
     FROM TABLE_NAME)
     [ WHERE) ]
  -- 实例
  DELETE FROM company where age in (select age from company_bak where age > 27);
  

Postgresql AUTO INCREMENT（自动增长）

• AUTO INCREMENT（自动增长） 会在新记录插入表中时生成一个唯一的数字。

• PostgreSQL 使用序列来标识字段的自增长，数据类型有smallserial、serial 和 bigserial 。这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性。

• 使用Mysql设置自动增长的语句如下:

  CREATE TABLE IF NOT EXISTS `runoob_tbl`(
     `runoob_id` INT UNSIGNED AUTO_INCREMENT,
     `runoob_title` VARCHAR(100) NOT NULL,
     `runoob_author` VARCHAR(40) NOT NULL,
     `submission_date` DATE,
     PRIMARY KEY ( `runoob_id` )
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  -- mysql是使用AUTO_INCREMENT这个属性来标识字段的自增。
  

• Postgresql是使用序列来标识字段的自增长：

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

• SMALLSERIAL,SERIAL,BIGSERIAL范围比较

  序列名	存储大小	范围
  SMALLSERIAL	2字节	1~32767
  SERIAL	4字节	1~2147483647
  BIGSERIAL	8字节	1~922 337 2036 854 775 807

• 举例

  -- 现在向runoob表中插入几条数据
  insert into runoob(alttext,imgurl) values('123','123');
  insert into runoob(alttext,imgurl) values('45','45');
  insert into runoob(alttext,imgurl) values('67','67');
  insert into runoob(alttext,imgurl) values('89','89');
  --通过select * from runoob发现ID从1开始自增长。
  

Postgresql PRIVILEGES（权限）

• 无论何时创建数据库对象，都会为其分配一个所有者，所有者通常是执行 create 语句的人。

• 对于大多数类型的对象，初始状态是只有所有者(或超级用户)才能修改或删除对象。要允许其他角色或用户使用它，必须为该用户设置权限。

  在 PostgreSQL 中，权限分为以下几种：

  SELECT INSERT UPDATE DELETE TRUNCATE REFERENCES TRIGGER CREATE CONNECT TEMPORARY EXECUTE USAGE

  根据对象的类型（表、函数等），将制定权限应用于该对象。

  要向用户分配权限，可以使用GRANT命令。

• GRANT语法

  -- 基本语法如下：
  GRANT privilege [, ...]
  ON object [, ...]
  TO { PUBLIC | GROUP group | username }
  -- 解释 --
  -- privilege - 值可以为：SELECT,INSERT,UPDATE,DELETE,RULE,ALL.
  -- object-要授予访问权限的对象名称。可能的对象有：table,view,sequence.
  -- PUBLIC-表示所有用户。
  -- GROUP group-为用户组授予权限。
  -- username-要授予权限的用户名。PUBLIC是代表所有用户的简短形式。
  -- 另外可以使用REVOKE命令取消权限，REVOKE语法 --
  REVOKE privilege [, ...]
  ON object [, ...]
  FROM { PUBLIC | GROUP groupname | username }
  

• 实例

  -- 创建一个用户：
  CREATE USER runoob WITH PASSWORD '123456';
  -- 给runoob分配(全部ALL)权限：
  GRANT ALL ON COMPANY TO runoob;
  -- 将runoob的权限撤销：
  REVOKE ALL ON COMPANY FROM runoob;
  -- 给runoob分配单个权限：
  GRANT INSERT ON COMPANY TO runoob;
  -- 将runoob的单个权限撤销：
  REVOKE INSERT ON COMPANY FROM runoob;
  -- 删除用户runoob:
  DROP USER runoob;
  

Postgresql的时间/日期函数使用

• 日期类型的模糊查询是不能直接进行的，要先转换成字符串然后再查询。

  通常使用to_char和to_date来进行日期转字符串和字符串转日期。

• 获取系统时间函数

  -- 获取当前完整时间
  select now();
  select current_timestamp;
  -- 获取当前日期
  select current_date;
  -- 获取当前时间
  select current_time;
  

• 时间的计算

  -- 当前时间节点的两年后
  select now() + interval '2 years';
  select now() + interval '2 year';
  select now() + interval '2 y';
  select now() + interval '2 Y';
  select now() + interval '2Y';
  -- 当前时间节点的一个月后
  select now() + interval '1 month';
  -- 当前时间节点的三周前
  select now() - interval '3 week';
  -- 当前时间节点的十分钟后
  select now() + '10 min';
  -- 计算两个时间差
  select age(now(),timestamp '1998-04-08');  --精确到毫秒
  select age(timestamp '1998-04-08');  --精确到天
  

• 时间字段的截取

  在开发过程中，经常要取日期的年，月，日，小时等值，PostgreSQL 提供一个非常便利的EXTRACT函数。

  EXTRACT(field FROM source)
  -- field 表示取的时间对象，
  -- source 表示取的日期来源，类型为 timestamp、time 或 interval。
  -- 取年份
  select extract(year from now());
  -- 取月份
  select extract(month from now());
  -- 取天数
  select extract(day from timestamp '2019-08-30');
  select extract(DAY FROM INTERVAL '40 days 1 minute');
  -- 查看现在剧1970-01-01 00:00:00 UTC的秒数
  select extract(epoch from now());
  -- 把epoch值转换回时间戳
  select TIMESTAMP WITH TIME ZONE 'epoch' + 1567130417 * INTERVAL '1 second';