跳至主要内容

[SQL] Table Manipulate 常用指令

:thumbsup: PostgreSQL Tutorial

Data Types

在 PostgreSQL 中提供了下面這幾種資料型態:

  • Boolean: 在宣告時可以使用 booleanbool
  • Character: char
  • Number
  • Temporal (date and time-related data types)
  • Special Types
  • Array

Boolean

keywords: boolean, bool
  • 在宣告時可以使用 booleanbool 作為關鍵字都可
  • 在 PostgreSQL 中會自動把 1, yes, y, t 轉換成 true;而 0, no, n, false 則轉換為 false
  • 當從 Boolean 欄位中選出資料來呈現時,PostgreSQL 會以 tf 來呈現,空格則顯示為 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 位元。
  • realfloat8 則是兩倍精確的浮點數(8-byte)
  • numericnumeric(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 之後記得要按「重新整理」才看得到新增的表。

imgur

範例

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 COLUMNDROP 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 NULLUNIQUE
  • 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 tablechild table;而被 foreign key 指稱到的那張表稱作 referenced tableparent 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 constrainttable 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;