PostgreSQL Notes

installation

• brew update before installation.
• brew install postgresql
• download PSequel

Command

creat database

• creatdb ‘dbname’

creat table

• CREATE TABLE table_name (dataName dataType, dataName dataType);
  • ex. CREATE TABLE users (name text, age smallint, birthday date);

insert data into table

• INSERT INTO table_name(dataName1, dataName2, dataName3) VALUES (value1, value2, value3);
  • ex. INSERT INTO users(name, age, birthday) VALUES (‘Gary‘, 25, 1994-01-01);

check datas

• SELECT dataName1,dataName2,dataName3 FROM table_name;
  • ex. SELECT name, age, birthday FROM users;

check datas with condition

• SELECT * FROM users WHERE name LIKE ‘A%’;

  • search all datas that name start with A.

• SELECT * FROM users ORDER BY weight DESC/ASC;

  • select all datas with descending/ascending order. 降序/升序

alter table

• ALTER TABLE table_name ADD dataName dataType;
  • ex. ALTER TABLE users ADD weight smallint;

update datas

• UPDATE table_name SET dataName = value WHERE dataName = value;
  • UPDATE users SET weight = 60 WHERE name = ‘Gary’;

functions

• AVG() calculate average of datas
• SUM() calculate sum of datas
• COUNT() count numbers of datas