[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');

❗ 把另一個 table 的資料複製進來時，id 的值也會一併複製進來。

UPDATE：更新資料

keywords: UPDATE, SET, RETURNING

-- 一般更新資料的方式
UPDATE table_name
SET column_1 = value_1,
    column_2 = value_2,
    ...
WHERE <condition>;

-- 將資料從某欄位複製到另一欄位
UPDATE table_name
SET column_1 = column_2;

-- 更新完後回傳更新過的 row
UPDATE table_name
SET column_1 = value_1
WHERE <condition>;
RETURNING column_1, column_2;

• ❗ 如果沒有寫 WHERE 條件的話，該 table 中所有該 column 的值都會被修改。
• 使用 RETURNING 可以在資料更新完畢後回傳資料。

Examples

-- 一般更新資料的方式
UPDATE link
SET description = 'The number one website in the World'
WHERE name = 'google';

-- 將資料從某欄位複製到另一欄位
UPDATE link
SET description = name;

-- 更新完後回傳更新過的 row
UPDATE link
SET description = 'New description'
WHERE name = 'bing'
RETURNING id, url, name, description;

DELETE

keywords: DELETE FROM

DELETE FROM table_name
WHERE <condition>;

-- 列出被刪除的資料
DELETE FROM table_name
WHERE <condition>
RETURNING *;

Examples

DELETE FROM link
WHERE id IN (5, 6);

ALTER TABLE：更改 Table 中 Column 內容

keywords: ALTER TABLE, ADD, DROP, RENAME, RENAME TO

ALTER TABLE table_name <action>;

-- 新增欄位
ALTER TABLE table_name ADD [COLUMN] column_name <type>;

-- 刪除欄位
ALTER TABLE table_name DROP [COLUMN] column_name;

-- 更改欄位名稱
ALTER TABLE table_name RENAME [COLUMN] column_name TO new_column_name;

-- 更改資料表名稱
ALTER TABLE table_name RENAME TO new_table_name;

-- 更改欄位結構
ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT]
ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL | DROP NOT NULL]

-- 新增 constraint
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

• ADD COLUMN 和 DROP COLUMN 後的 COLUMN 可以省略。

PostgreSQL ALTER TABLE @ psql tutorial

Examples

-- 新增欄位 active
ALTER TABLE link ADD [COLUMN] active boolean;

-- 刪除欄位 active
ALTER TABLE link DROP [COLUMN] active;

-- 修改欄位名稱，從 url 改成 href
ALTER TABLE link RENAME [COLUMN] url TO href;

-- 修改資料表名稱
ALTER TABLE link RENAME TO url_table;

-- 修改 constraint：加入 foreign key(fk) 的會連動操作
ALTER TABLE books ADD CONSTRAINT books_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE;

DROP TABLE

keywords: DROP TABLE, RESTRICT

DROP TABLE [IF EXISTS] <table_name>;

-- 如果有任何相依的資料存在則不能移除（預設）
DROP TABLE [IF EXISTS] <table_name> RESTRICT;

-- 如果有任何相依的資料存在仍要移除
DROP TABLE [IF EXISTS] <table_name> CASCADE;

在 PostgreSQL 中預設就會帶有 RESTRICT。

More ABOUT Constraint

Column Constraints and Table Constraints

keywords: NOT NULL, UNIQUE, PRIMARY KEY, CHECK, REFERENCES

Table Constraints 和 Column Constraints 非常類似，差別在於table constraint 可以針對一張表上的許多欄位進行設定，而非個別欄位（column）。

• NOT NULL：欄位的值不能是 NULL。
• UNIQUE：該欄位的值在該來為中必須是唯一的（一般來說只能有一個 NULL ，但在 PostgreSQL 例外，它可以有很多 NULL，因為在 postgreSQL 中每一個 NULL 都是獨特的） 。
• PRIMARY KEY：同時結合了 NOT NULL 和 UNIQUE。
• CHECK：可以在新增或更新資料的時候檢查某一條件。
• REFERENCES：把此張表的資料和另一張表的資料建立關聯。

-- column constraints
CREATE TABLE person (
  id serial PRIMARY KEY,
  first_name VARCHAR (50),
  last_name VARCHAR (50),
  email VARCHAR (50) UNIQUE    -- column constraints
);

-- table constraints
CREATE TABLE person (
  id SERIAL  PRIMARY KEY,
  first_name VARCHAR (50),
  last_name  VARCHAR (50),
  email      VARCHAR (50),
  UNIQUE(email)
);

Primary Keys 和 Foreign Keys

主鍵（Primary Key, PK）

• 主鍵（primary key）是用來辨認該列資料是唯一的（unique）
• 每張表（table）都可以有一個且只能有一個主鍵

在建立 Table 時加上 PRIMARY KEY：

CREATE TABLE table_name (
  column_name data_type PRIMARY KEY,
  column_name data_type,
  ...
);

外部鍵（Foreign Key, FK)

• 外部鍵（foreign key）是用來辨認其他表中指稱的是哪一筆資料，具體來說。foreign key 指稱到的是另一張表的 primary key。
• 包含 foreign key 的表稱作 referencing table 或 child table；而被 foreign key 指稱到的那張表稱作 referenced table 或 parent table
• 一張表中可以擁有多個 foreign keys。

CHECK

keywords: CHECK, CONSTRAINT

可以使用 CONSTRAINT 來定義錯誤時顯示的錯誤名稱

-- 當 salary 不符合 CHECK 的條件時，預設錯誤訊息會顯示
-- ERROR:  new row for relation "new_user" violates check constraint "new_user_salary_check"
CREATE TABLE new_user(
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  birth_date DATE CHECK(birth_date > '1900-01-01' ),
  join_date DATE CHECK(join_date > birth_date),
  salary integer CHECK(salary > 0) -- 預設錯誤時會顯示 new_user_salary_check
);

-- 可以透過 CONSTRAINT 來自定錯誤訊息
-- ERROR:  new row for relation "new_user" violates check constraint "positive_salary"
CREATE TABLE new_user(
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  salary integer CONSTRAINT positive_salary CHECK(salary > 0) -- 錯誤時顯示 positive_salary
);

-- 新增不符合規則的資料時會出現錯誤
INSERT INTO new_user(first_name, birth_date, join_date, salary)
VALUES('Joe', '1980-02-02', '1990-04-04', -10);

NULL

• 理論上，在資料庫中 NULL 指的是 unknown 或缺漏值，它和「空字串（empty）」或 0 不同。例如，當我們不知道該使用者的 email 時，該 email 顯示 NULL，但若該使用者是沒有 email 時，則將該 email 顯示為 ''（空字串）。

Examples

CREATE TABLE salespeople (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  phone VARCHAR(50)
);

UNIQUE / INDEX

PostgreSQL UNIQUE Constraint @ PostgreSQL Tutorial

-- 使用 column constraint
CREATE TABLE salespeople (
  id serial PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  phone VARCHAR(50) UNIQUE
);

-- 使用 table constraint
CREATE TABLE salespeople (
  id serial PRIMARY KEY,
  username VARCHAR(50),
  phone VARCHAR(50),
  UNIQUE(username, phone)
);

注意：使用 column constraint 和 table constraint 的意義不同。使用 column constraint 時，只根據個別欄位做 UNIQUE，例如，username 不能重複，phone 也不能重複。但使用 table constraint 時，會同時根據兩個欄位做 UNIQUE，例如，username 可以重複，phone 也可以重複，但 「username 且 phone」 兩者不能重複。

將已存在的欄位改成 Unique

• 使用 CONCURRENTLY 讓建立 INDEX 的動作可以併發操作，避免 TABLE 因為建立 index 而被 lock 住，但整體處理時間可能會拉長
• 如果會根據 create time 做搜尋分頁功能的話，可以把 created_at 加入 index 以加速搜尋

-- 建立一個 student 表，但尚未設定 Unique
CREATE TABLE student (
  student_id serial PRIMARY KEY,
  name varchar(255),
  phone varchar(50),
  email varchar(255)
);

-- 需要先根據某一欄位設定 unique index
CREATE UNIQUE INDEX CONCURRENTLY student_phone_key
ON student (phone);

-- 需要先根據某一欄位設定 index
CREATE INDEX CONCURRENTLY users_index_created_at ON users(created_at);

-- 在 student 表中新增 constraint 並使用 student_phone_email_key 這個 index
ALTER TABLE student
ADD CONSTRAINT unique_student_phone_key
UNIQUE USING INDEX student_phone_key;

Views

views 是一個資料庫中用來儲存 query 的物件，它只是一個虛擬的資料表（virtual table）。透過 view 可以幫助我們簡化複雜的 query，而我們可以直接去 query 這個 view。

PostgreSQL View @ PostgreSQL Tutorial

Create View

CREATE VIEW view_name AS query;

舉例來說，可以把原本的這段 query 存成一個 view：

-- 原本的 query
SELECT customer_id, address.address_id, first_name, last_name, email, address, phone
FROM customer
JOIN address
ON customer.address_id = address.address_id;

-- 把 query 建立成一個 view
CREATE VIEW customer_info AS
SELECT customer_id, address.address_id, first_name, last_name, email, address, phone
FROM customer
JOIN address
ON customer.address_id = address.address_id;

-- 直接 query 剛剛的 view
SELECT * FROM customer_info;

修改 View 的名稱

keywords: ALTER VIEW, RENAME TO

-- 將 VIEW 的名稱從 customer_info 改成 customer_master_list
ALTER VIEW <old_view_name> RENAME TO <new_view_name>;

ALTER VIEW customer_info RENAME TO customer_master_list;

移除 View

keywords: DROP VIEW

DROP VIEW [IF EXISTS] customer_info;