[SQL] Table Manipulate 常用指令
:thumbsup: PostgreSQL Tutorial
Data Types
在 PostgreSQL 中提供了下面這幾種資料型態:
- Boolean: 在宣告時可以使用
boolean
或bool
- Character:
char
- Number
- Temporal (date and time-related data types)
- Special Types
- Array
Boolean
keywords: boolean
, bool
- 在宣告時可以使用
boolean
或bool
作為關鍵字都可 - 在 PostgreSQL 中會自動把
1
,yes
,y
,t
轉換成true
;而0
,no
,n
,false
則轉換為false
。 - 當從 Boolean 欄位中選出資料來呈現時,PostgreSQL 會以
t
和f
來呈現,空格則顯示為NULL
。
String
keywords: char
, char(n)
, varchar(n)
PostgreSQL 提供三種不同的字串型態:
- 對於單一個文字(a single character)使用
char
。 - 對於固定長度的文字(fixed-length character)使用
char(n)
,當你輸入的字串少於它時,PostgreSQL 會補上空格;當字串多於它時,PostgreSQL 則會報錯。 - 對於變動長度的文字(variable-length character)使用
varchar(n)
,你可以儲存到最多n
個文字,當輸入的字串少於它時,PostgreSQL 不會自動補上空格。
Number
keywords: smallint
, int
, serial
, float(n)
, real / float8
, numeric / numeric(p,s) / numeric(p,)
在 PostgreSQL 中提供兩種不同的數值型態—整數(integers)和浮點數(floating-point numbers)。
整數(Integers)
整數可以分成三類:
- 以帶有正負號的二位元數字儲存
smallint
(small integer),並且介於 -32768 ~ 32767 之間,也就是 2 的 16 次方。 - 四位元的整數使用
int
(integer),可以介於 -214783648 ~ 214783647,也就是 2 的 31 次方。 serial
和 integer 一樣,但是 PostgreSQL 會自動在欄位中填入數值,類似在其他資料庫中的AUTO_INCREMENT
。
浮點數(Floating-point Numbers)
浮點數也可以分成三類:
float(n)
表示其浮點數的精確度至少為n
,最多到 8 位元。real
或float8
則是兩倍精確的浮點數(8-byte)numeric
或numeric(p,s)
是實數(real number)並且有 p 位數(p digits)並且可以到小數點後 s 位,其中numeric(p,)
是最精確的數字。
Temporal
keywords: date
, time
, timestamp
, interval
, timstamptz
用來和日期與時間有關的資料:
date
time
timestamp
可以儲存日期(date)和時間(time)interval
用來儲存 timestamps 間的差距timstamptz
同時儲存 timestamp 和時區(timezone)
CREATE TABLE
-- 不包含 table constraints
CREATE TABLE table_name(
column_name_1 TYPE column_constraint,
column_name_2 TYPE column_constraint,
);
-- 包含 table constraints
CREATE TABLE table_name(
column_name_1 TYPE column_constraint,
column_name_2 TYPE column_constraint,
table_constraint (column_name_1, column_name_2),
INHERITS existing_table_name;
);
- 透過
INHERITS
可以讓新建出來的 table 包含所有 existing table 中有的欄位。 - ❗ 建立完 Table 之後記得要按「重新整理」才看得到新增的表。
範例
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR UNIQUE NOT NULL
age INT ,
first_name VARCHAR NOT NULL,
comment TEXT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp,
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE TABLE distinct_demo (
id serial NOT NULL PRIMARY KEY,
bcolor VARCHAR,
);
複製資料表結構 Copy Table Structure
keywords: LIKE
使用 LIKE
關鍵字可以根據原本的 table (table_name
) 新增出一個欄位與結構(schema structure)均相同的表(table_name_copy
):
CREATE TABLE table_name_copy (LIKE table_name);
Example Codes
CREATE TABLE account (
user_id serial PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(355) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);
CREATE TABLE role(
role_id serial PRIMARY KEY,
role_name VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE account_role(
user_id integer NOT NULL,
role_id integer NOT NULL,
grant_date timestamp without time zone,
PRIMARY KEY (user_id, role_id),
CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
REFERENCES role (role_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
REFERENCES account (user_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
INSERT:新增資料
keywords: INSERT INTO
, VALUES
-- 新增一筆資料
INSERT INTO table_name(column_1, column_2, ...)
VALUES(value1, value2, ...);
-- 新增多筆資料
INSERT INTO table_name(column_1, column_2, ...)
VALUES(value1, value2, ...),
(value1, value2, ...),
...;
-- 根據另一張表的資料來新增資料
INSERT INTO table_name
SELECT column_1, column_2
FROM another_table
WHERE <condition>;
- 當要新增的資料型態是字串時,記得使用「單引號」括起來。
- 在新增一列資料時,沒有給值的欄位,預設會自動補入
NULL
(也可以設定其他預設值)。
Example Code
-- 新增一筆資料
INSERT INTO link(url, name)
VALUES('www.google.com', 'google');
-- 新增多筆資料
INSERT INTO link(url, name)
VALUES('www.yahoo.com', 'yahoo'),
('www.bing.com', 'bing');
將一個表的資料[複製](#複製資料表結構 Copy Table Structure)到另一個表中:
-- 根據 link table 建立一個結構相同的 link_copy table
CREATE TABLE link_copy (LIKE link);
-- 把 link table 中的部分資料複製一份到 link_copy table 中
INSERT INTO link_copy
SELECT * FROM link
WHERE name IN ('google', 'yahoo');