部分sql语句

事务:

BEGIN TRANSACTION;
BEGIN TRY
-- 第一个操作：插入数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- 第二个操作：更新数据
UPDATE table_name SET column1 = new_value WHERE condition;

-- 第三个操作：删除数据
DELETE FROM table_name WHERE condition;
COMMIT; -- 如果所有操作都成功，则提交事务

END TRY
BEGIN CATCH
ROLLBACK; -- 发生异常时回滚事务
END CATCH;

多行数据进行拼接，并以逗号分隔的功能（以期刊名称分组）

SELECT 期刊名称, GROUP_CONCAT(doi SEPARATOR ',') AS names FROM articles GROUP BY 期刊名称;

临时会话:

创建临时表，当前会话使用：
CREATE TEMPORARY TABLE temp_table_name AS SELECT column1, column2, … FROM your_query;
创建临时表，当前查询语句使用：
WITH order_totals AS (
SELECT SUM(total) AS total_amount
FROM orders
WHERE order_date <= '2022-02-28' )； SELECT total_amount FROM order_totals; 

根据条件进行条件判断和返回不同的结果：

SELECT * , CASE WHEN access='Open Access' THEN 'open' ELSE 'no' END as '权限' FROM 5_15_articles 更新: UPDATE students SET grade = CASE WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END
WHERE score >= 60;

触发器：

SELECT *
FROM pg_proc
WHERE proname = 'set_date_on_insert';

SELECT trigger_name, event_object_table, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'journal_article_list';

CREATE TRIGGER update_trigger
BEFORE INSERT OR UPDATE ON journal_article_list
FOR EACH ROW
EXECUTE FUNCTION set_date_on_update();