|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/joining-data-and-using-triggers-in-postgresql/
课程评论：没有评论
*** **PostgreSQL 数据连接与触发器课程总结** 本课程深入探讨 PostgreSQL 数据库中的核心概念： **数据连接 (Joins)** 和 **触发器 (Triggers)**。 **数据连接 (Joins)** * **核心概念：** Join 用于将两个或多个表中的记录组合起来，依据它们之间共有的值。 * **工作原理：** Join 操作会将来自不同数据集（表）的列合并成一个新的行集合。所有 Join 操作都基于 **笛卡尔积 (Cartesian Product)**，即两个数据集中所有可能组合的集合。Join 操作会在一个临时表中生成行集合，并作用于两个或多个表格，这些表格至少需要一个共同字段，并且这些共同字段之间必须保持某种关联。Join 操作不会改变基表（源表）的结构。 * **PostgreSQL 中的 Join 类型：** * Cross Join * Inner Join * Left Outer Join * Right Outer Join * Full Outer Join **触发器 (Triggers)** * **核心概念：** 触发器是当与表关联的事件发生时自动调用的函数。可关联的事件包括：INSERT、UPDATE、DELETE 或 TRUNCATE。 * **工作原理：** 触发器是一种特殊的用户定义函数，可以绑定到一个表。创建触发器需要先定义一个触发器函数，然后将该函数绑定到目标表。触发器的特殊之处在于它会在特定事件发生时自动调用。 * **触发器类型：** PostgreSQL 主要提供两种类型的触发器： * **行级触发器 (Row Level Triggers)：** 对受影响的每一行都调用一次。 * **语句级触发器 (Statement Level Triggers)：** 对整个 SQL 语句只调用一次。 * **触发时机：** * **Before 触发器：** 在事件发生前调用。此时可以跳过当前行的操作，或修改将要插入或更新的行。 * **After 触发器：** 在事件发生后调用。此时，所有更改都可供触发器访问。 * **课程实践：** 本课程将指导学员创建和管理一个基本的触发器。 **课程概览** 本课程为期全面，旨在使用户能够熟练掌握 PostgreSQL 中的数据操作。学员将学习如何利用各种 Join 技术有效地组合来自多个表的数据，并掌握如何实现触发器来自动化数据库中的各种操作。 ***
The PostgreSQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.The main concept which is focusing on a join is that, two or more data sets, when joined, combined their columns into a new set of rows, including each of the columns requested from each of the data sets. All joins are standing on the foundation of Cartesian product. The Cartesian product is the set of all possible combinations between two data sets. A join creates a set of rows in a temporary table and works on two or more tables, and each table should at least one common field and must maintain a relation between the common fields. Join keeps the structure unchanged of the base tables.Types of PostgreSQL JOINCross JoinInner JoinLeft Outer JoinRight Outer JoinFull Outer JoinA PostgreSQL trigger is a function invoked automatically whenever an event associated with a table occurs. An event could be any of the following: INSERT, UPDATE, DELETE or TRUNCATE.A trigger is a special user-defined function that binds to a table. To create a new trigger, you must define a trigger function first, and then bind this trigger function to a table. The difference between a trigger and a user-defined function is that a trigger is automatically invoked when an event occurs.PostgreSQL provides two main types of triggers: row and statement level triggers. The differences between the two are how many times the trigger is invoked and at what time. For example, if you issue an UPDATE statement that affects 20 rows, the row level trigger will be invoked 20 times, while the statement level trigger will be invoked 1 time.You can specify whether the trigger is invoked before or after an event. If the trigger is invoked before an event, it can skip the operation for the current row or even change the row being updated or inserted. In case the trigger is invoked after the event, all changes are available to the trigger. We will create and manage a basic trigger in this course.This course provides a comprehensive guide to working with data in PostgreSQL, focusing on data joins and triggers. Students will learn how to combine data from multiple tables using various join techniques and how to implement triggers to automate actions in the database.
