postgresql —— 零碎笔记

聚合函数

-- 聚合查询
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'
GROUP BY city
HAVING max(temp_lo) < 40; -- HAVING 子句始终包含聚合函数, 否则没有意义

　

更新语句

-- 更新语句
UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

 

事务

-- 事务
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

 

单引号与双引号

francs=> create table test_3(id int4,name text);  
CREATE TABLE  
  
francs=> insert into test_3 (id,name) values (1,'kate''s horse');  
INSERT 0  1  
  
francs=>  select  *  from test_3;  
id | name   
----+--------------  
 1  | kate's horse

 

233