# 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 正體中文使用手冊](https://docs.postgresql.tw/)
#### Install GUI - PSequel:
> [PSequel, a PostgreSQL GUI Tool for macOS](https://psequel.com/)
#### Install PostgreSQL via homebrew:
1. Check if there’s any update first
```bash
brew update
brew upgrade
brew doctor
```
2. Install postgreSQL: `brew install postgresql`
```bash
brew install postgresql
```
3. Start: `brew services start postgresql`
```bash
brew services start postgresql
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)
```
## SQL: Create database & Create Table
- **Create database**: `createdb` 'db_name'
```bash
createdb 'test'
```
- **Enter db**: `psql` 'db_name'
```bash
psql 'test'
```
- **Create Table**: `CREATE TABLE` table_name (column_1 datatype, column_2 datatype, column_3 datatype)
```bash
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`
```bash
\d
```
- **Exit**: `\q`
```bash
\q
```
## SQL: Insert Into & Select
- **Insert value**: `INSERT INTO` table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);
```bash
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
```bash
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;
```bash
ALTER TABLE users ADD score smallint;
```
- **Update the information**: `UPDATE` table_name `SET` some_column = some value `WHERE` some_column = some_value;
```bash
**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 值不包括在計算中。
```bash
SELECT AVG(score) FROM users;
```
- **`SUM()`** - sum
```bash
SELECT SUM(age) FROM users;
```
- **`COUNT()`** - count：函數用於返回匹配指定條件的行數。
```bash
SELECT COUNT(name) FROM users;
```
- **`MAX()`** - max：函數返回指定列的最大值，NULL值不包括在計算中
- **`MIN()`** - min：函數返回的指定列的最小值，NULL值不包括在計算中
:::info
💡 Ref. [零基礎快速自學SQL，2天足矣！. 此文是《10周入門資料分析》系列的第7篇 | by 數據分析那些事 | 數據分析不是個事 | Medium](https://medium.com/%E6%95%B8%E6%93%9A%E5%88%86%E6%9E%90%E4%B8%8D%E6%98%AF%E5%80%8B%E4%BA%8B/%E9%9B%B6%E5%9F%BA%E7%A4%8E%E5%BF%AB%E9%80%9F%E8%87%AA%E5%AD%B8sql-2%E5%A4%A9%E8%B6%B3%E7%9F%A3-4900d8b3d7c2)
:::
## Joining tables
### Create a login table
```bash
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
```bash
INSERT INTO login (secret, name) VALUES ('abc', 'Andrei');
```
### Join tables
```bash
SELECT * FROM users JOIN login ON users.name = login.name;
```
### Delete From & Drop Table
Delete
```bash
DELETE FROM users WHERE name='Sally';
```
Drop table
```bash
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
Sign in with Wallet
Connect another wallet