PostgreSQL

Two types of database

Relational Database

• Consists of 2 or more tables with columns and rows
• The relation between tables and fields is called a schema
  • foreign key
  • primary key
• Use SQL to communicate with the server
• MySQL, PostgreSQL

NoSQL / Non Relational Database

• mongoDB
  • document oriented, stores information as documents
  • use it’s own mongoDB query language
• redis

Install PostgreSQL

簡介 - PostgreSQL 正體中文使用手冊

Install GUI - PSequel:

PSequel, a PostgreSQL GUI Tool for macOS

Install PostgreSQL via homebrew:

1. Check if there’s any update first

   ​​​​brew update
   ​​​​brew upgrade
   ​​​​brew doctor
   

2. Install postgreSQL: brew install postgresql

   ​​​​brew install postgresql
   

3. Start: brew services start postgresql

   ​​​​brew services start postgresql
   ​​​​==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)
   

   

SQL: Create database & Create Table

• Create database: createdb 'db_name'
  ​​​​createdb 'test'
  

• Enter db: psql 'db_name'
  ​​​​psql 'test'
  

• Create Table: CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype)
  ​​​​CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);
  ​​​​CREATE TABLE users (name text, age smallint, birthday date);
  

• List of relations: \d
  ​​​​\d
  

  
• Exit: \q
  ​​​​\q
  

SQL: Insert Into & Select

• Insert value: INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);
  ​​​​INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);
  ​​​​INSERT INTO users (name, age, birthday) VALUES ('Andrei', 31, '1992-01-25');
  

• Grab information: SELECT column FROM table_name
  ​​​​SELECT name, age, birthday FROM users;
  ​​​​SELECT * FROM users;
  

  • SELECT column_name FROM table_name
  • * - grab the entire columns
  • filter: SELECT * FROM users **WHERE** name **LIKE** 'A%' - name starts with A
  • sort: SELECE * FROM users **ORDER BY** score **DESC**

SQL: Alter Table & Update

• Add another column to a existing table: ALTER TABLE table_name ADD column_name datatype;

  ​​​​ALTER TABLE users ADD score smallint;
  

• Update the information: UPDATE table_name SET some_column = some value WHERE some_column = some_value;

  ​​​​**UPDATE** table_name
  ​​​​**SET** some_column = some_value
  ​​​​**WHERE** some_column = some_value;
  
  ​​​​UPDATE users SET score=50 WHERE name='Andrei';
  ​​​​UPDATE users SET score=100 WHERE name='Jack' OR name='Sally';
  

SQL: Functions

• AVG() - average：AVG 函數返回數值列的平均值。NULL 值不包括在計算中。
  ​​​​SELECT AVG(score) FROM users;
  

• SUM() - sum
  ​​​​SELECT SUM(age) FROM users;
  

• COUNT() - count：函數用於返回匹配指定條件的行數。
  ​​​​SELECT COUNT(name) FROM users;
  

• MAX() - max：函數返回指定列的最大值，NULL值不包括在計算中
• MIN() - min：函數返回的指定列的最小值，NULL值不包括在計算中

💡 Ref. 零基礎快速自學SQL，2天足矣！. 此文是《10周入門資料分析》系列的第7篇 | by 數據分析那些事 | 數據分析不是個事 | Medium

Joining tables

Create a login table

CREATE TABLE login (
	ID serial NOT NULL PRIMARY KEY,
	secret VARCHAR (100) NOT NULL,
	name text UNIQUE NOT NULL
);

• serial - datatype
• NOT NULL
• PRIMARY KEY - one per table

Insert value

INSERT INTO login (secret, name) VALUES ('abc', 'Andrei');

Join tables

SELECT * FROM users JOIN login ON users.name = login.name;

Delete From & Drop Table

Delete

DELETE FROM users WHERE name='Sally';

Drop table

DROP TABLE login;

補充：Exercises: SQL Commands

To learn more about SQL and do some fun exercises, follow this free tutorial which gives some fun interactive exercises for you: https://www.khanacademy.org/computing/computer-programming/sql