PostgreSQL学习记录——第三天

今天继续学PG，感觉前面的基本操作还比较快，毕竟用的也不少。到后面稍微开始有点慢了，继续努力吧✌️

NULL

在表中默认可以输入NULL，但从前面建过的表中就能知道可以指定该列不能驶入输入NULL。

语法大致如下

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

NOT NULL在使用后将使该列如果没有字段输入将无法更新记录，有NULL的可以不添加字段也能更新记录。

NULL和其他语言里一样都表示空，但空并不是一个数，他是不存在，而0是存在。所以NULL和0是不等价的。

这里添加的表和上一篇blog中JOIN部分的预先准备一样（因为咱没搭载服务器，没法开下载链接🤣）

使用下面的命令进行操作

UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL WHERE ID IN (6, 7);

IS NOT NULL

前面说了空现在说不为空（又莫名想到了leetcode上写if not _）。

用该命令列出所有薪资不为空的记录

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

IS NULL

用下面命令列出为空的记录（直接处刑，把没工资的列出来然后开咯~）

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

别名

别名和其他的一样都是用AS去设置，大概写一下语法就进入下一个part

表的别名

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

列的别名

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

触发器

这部分原来没咋用过，目测需要花点时间了

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

找资料的时候看到的几个PG的触发器比较重要的点，加了点我自己的理解

• 触发器可以在下面几种情况下触发
  • 在执行操作之前（在检查约束并尝试插入、删除、更新之前）
  • 在执行操作之后（在检查约束并插入、删除、更新之后）
  • 更新操作（在对一个表进行插入、删除、更新时）
• 触发器的FOR EACH ROW属性是可选的，如果选中则在操作修改时每一行调用一次。
• 选中FOR EACH STATEMENT属性时，不管修改多少行都只会执行一次。
• WHEN子句和触发器操作在引用NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。
• 如果存在 WHEN 子句，PostgreSQL 语句只会执行 WHEN 子句成立的那一行，如果没有 WHEN 子句，PostgreSQL 语句会在每一行执行。
• BEFORE 和 AFTER 决定触发器在什么时候执行。
• 要修改的表必须和当前操作数据库在一个目录下，不可以使用调用
• 约束触发器
  • 约束触发器并不是在事件发生前后执行而是可以在指定的时间点触发
  • 约束触发器可以使用约束选项IMMEDIATE和DEFERRED来调整执行时间

基本语法如下

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
[
 -- 触发器逻辑....
];

用DROP TABLE COMPANY;删掉前面part用的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
);

然后开始操作，首先建立一个函数

建立之前先说一下我对函数的基本语法的理解。

这是我用chatGPT搞的一个示例

CREATE OR REPLACE FUNCTION get_sum(a NUMERIC, b NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

通过直译我们可以发现，创建函数的基本语句就是

CREATE [OR REPLACE] FUNCTION 函数名称 (参数列表)
RETURNS 返回类型 AS $$
BEGIN
    -- 函数主体
    -- 包含SQL语句和/或过程语言代码
END;
$$
 LANGUAGE 语言名称;

然后我们回到建立函数

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;

创建后我们来建立触发器

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

然后向表中插入数据

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

插入后我们用SELECT去找AUDIT库就可以看到我们已经添加了数据了。

列出触发器

列出所有触发器的语法如下

SELECT * FROM pg_trigger;

然后再来看看列出特定触发器的语法

SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

删除触发器

基本语法如下

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

删除我们前面建立的触发器的命令如下

drop trigger example_trigger on company;

触发器我自己感觉是更适合大量任务进行时的自动化，可以节省很多人力成本。不过这点好像面试不会特别去考，也不知道为啥。

索引

终于来到了面试官最最最最喜爱的索引，我自己的经历我感觉除了三范式这个问题出现概率高之外，就是索引会考很多。我自己就遇到过下面几个问题（特指MySQL，毕竟我简历上写的MySQL）

• 什么是索引，为什么用索引
• 索引的数据结构，他们之间有什么区别
• 索引的种类有哪些
• B树和B+树有啥区别（这个我当时懵掉了，之前还复习过）
• 索引的使用场景
• 之后我也会把我大概能遇到的想到的写成Blog然后发布出来，应该会更倾向于机器学习，毕竟这个我更擅长。

基本概念咱就跳过，都是一样的，不知道的拖出去乱棍打死

基本语法如下

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

不管是单列还是组合索引都需要建立在频繁多次使用WHERE的情况下，反推出使用场景之一就是频繁使用WHERE的情况下。

只有一列被使用就用单列索引，多列被使用就用多列索引

唯一索引

唯一索引是为了提高性能、保证数据完整性，保证无重复值进入。

CREATE UNIQUE INDEX index_name
on table_name (column_name);

局部索引

局部索引是在子集上建立索引，子集由一个条件表达式上定义，索引只包含符合条件的行

CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;

隐式索引

数据库会自动创建

删除索引

基本语法如下

DROP INDEX index_name;

索引准则

• 适合于中大型表，不适合于小表，小表一般全表扫描更快
• 不适用于需要频繁进行大批量的增删改的操作，更适合于大量查询的表
• 不应该在含有大量NULL的上
• 不应该建立在需要被频繁操作的列上
• 多个字段经常一起被查询可以考虑建立联合索引
• 多个字段且字段没有重复可以考虑唯一索引
• 字段多且有重复考虑普通索引

ALTER TABLE

ALTER TABLE 用于添加、修改、删除一张已经存在的表，也可以用来添加和删除约束。

语法

添加列

ALTER TABLE table_name ADD column_name datatype;

删除列

ALTER TABLE table_name DROP COLUMN column_name;

修改数据类型

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

添加UNIQUE约束

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

添加CHECK约束

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

添加主键

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

添加NOT NULL约束

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NNULL;

删除约束

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

对应MySQL是

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

删除主键

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

对应MySQL是

ALTER TABLE table_name
DROP PRIMARY KEY;

删除NOT NULL约束

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

TRUNCATE TABLE

在使用DROP TABLE TABLE_NAME时会直接删除表内容和表结构，如果想使用该表就需要重新建立

TRUNCATE TABLE就可以只删除表内容保留表结构，并且由于他不扫描表，所以速度上比DELETE更快

基本语法如下

TRUNCATE TABLE  table_name;