Basic Knowledge

type
status
date
slug
summary
tags
category
icon
password

PostgreSQL Database Tutorial

Temporary Postgres Database: https://pg-sql.com/
📖
This presentation is provided by Hex School ( 六角學院 )
SQL 資料庫
SQL 資料庫
https://gamma.app/docs/SQL--23heyix4r93u5u0?mode=doc
SQL 資料庫

The Three Musketeers of Databases

  • Table is a complete set of data that includes all related information.
  • Columns define the attributes and data types of the data.
  • Rows are horizontal collections of data, representing a complete record.

Column Data Types

Data Types
Description
Use Case
Example
Example Column
INTEGER
整數
ID、年齡、數量
1, 42, -15
age INTEGER
VARCHAR(n)
可變長度字串
名稱、描述
"John Doe"
name VARCHAR(50)
TIMESTAMP
日期時間
建立/更新時間
'2024-03-20 15:30:00'
created_at TIMESTAMP
DECIMAL(p,s)
精確小數
金額計算
199.99
price DECIMAL(10,2)
BOOLEAN
真/假值
狀態、開關
true, false
is_active BOOLEAN
JSON
JSON資料
彈性資料結構
'{"name": "John"}'
settings JSON

Insert

Select

Column Alias

Comment

Filter Data

Set up the Database Environment ( https://pg-sql.com/ )

Comparison Operators

Operators
Meaning
Example
=
等於
WHERE price = 100
>
大於
WHERE price > 100
<
小於
WHERE price < 100
>=
大於等於
WHERE price >= 100
<=
小於等於
WHERE price <= 100

Logical Operators

Operators
Meaning
Example
AND
且，兩個條件都要成立
WHERE category = '3C' AND price > 1000
OR
或，其中一個條件成立
WHERE category = '3C' OR category = '配件'

Set and Range Operators

Operators
Meaning
Example
BETWEEN
在指定的範圍內
WHERE price BETWEEN 100 AND 500
IN
包含在列出的值中
WHERE category IN ('3C', '配件')
NOT IN
不包含在列出的值中
WHERE category NOT IN ('3C')

Update

Delete

VariablesSection Assignment - The Novice Sales Adventures at a Furniture Store
Loading...
Catalog
Article List