iT邦幫忙

2022 iThome 鐵人賽

DAY 10
1
自我挑戰組

資料庫新手入門--以PostgreSQL為例系列 第 10

Day 10 資料表的設定兩三事

  • 分享至 

  • xImage
  •  

在前一天的內容當中,
我們知道使用ENUM來限制資料只能是固定的某些值,
也知道了如何寫入TIMESTAMP格式,
今天要來講更多的設定與限制方法以及認識一個特別的資料NULL。

目錄

  • 設定預設值(DEFAULT)
  • 更新資料表(ALTER)
  • 沒有資料(NULL)
  • 不允許沒有資料(NOT NULL)
  • 確認資料要符合某個條件(CHECK)
  • 唯一值(UNIQUE/PRIMARY KEY)

設定預設值(DEFAULT)

資料庫可以設定預設值,當資料沒有寫入的時候,就寫入這個預設值,
TIMESTAMP有一個特殊的預設值為當前時間(CURRENT_TIMESTAMP),
我們可以使用這個預設值讓系統自動寫入當下的時間,超級方便阿!

CREATE TABLE login (
	account VARCHAR(200),
	level VARCHAR(200),
	login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO login(account, level)
VALUES ('秦假仙','LV0');

更新資料表(ALTER)

有時候我們想在資料表建立之後,改變一些設定,但是又不想整組刪掉,
可以使用 ALTER TABLE 來進行更新。

CREATE TABLE employees (
	name VARCHAR(200),
	is_marry BOOLEAN
);

ALTER TABLE employees 
ALTER COLUMN name SET DATA TYPE VARCHAR(300);

備註 : VSCode 對於COLUMN支援度不好,不會自動提示這個單字。

沒有資料(NULL)

開門見山的說NULL跟0是不同的,使用上最常見的差異是計算平均值,NULL會忽略,0會計算。

  • NULL代表沒有資料
  • 0 代表存在資料但數值為 0

建立一個機台保養次數統計表,資料內容有機台名稱(name)跟機台保養次數(count),
時間來到九月這個打考績感傷的季節時間,課長大大想了解機台平均保養次數,
使用AVG函數來獲得平均值。

CREATE TABLE tools_pm (
	name VARCHAR(200),
	count INT
);

INSERT INTO tools_pm(name,count)
VALUES ('M43TS001',4),
('M43TS002',5),
('M43TS003',4),
('M43TS004',3),
('M43TS005',0);

-- 平均3.2次
SELECT AVG(count) FROM tools_pm;

補充說明 : AVG是聚合函數(Aggregation function)的其中一種,聚合的意思是聚合了許多數字變成一個數字,如> 果覺得不好記,也可以把聚合函數想成計算函數,但還是要認識Aggregation function 這個單字,因為日後查資料> 應用會非常的常見。

但是有一個問題,工程師小其負責的機台M43TS001-M43TS005,
編號1到4號的機台是產線生產中的機台,編號5的機台是客戶剛移轉過來的機台,
尚未完成設定,我們不希望平均保養次數把編號5也算進去,
因此我們要把編號5的機台保養次數設定為NULL,再使用AVG進行計算,
得到的答案才是真正生產中機台的平均值,呼~差點就被打乙等了:)

-- 把剛剛輸入的資料先刪掉 (請不要在真實專案上隨意使用)
DELETE FROM tools_pm;

-- 重新寫入
INSERT INTO tools_pm(name,count)
VALUES ('M43TS001',4),
('M43TS002',5),
('M43TS003',4),
('M43TS004',3),
('M43TS005',NULL);

-- 平均4次
SELECT AVG(count) FROM tools_pm;

注意 : 有設定預設值(DEFAULT)的欄位則不能插入NULL。

要找出NULL的資料要用IS。

SELECT * FROM tools_pm 
WHERE count IS NULL;

不允許沒有資料(NOT NULL)

對於有些資料欄位,我們並不希望沒有資料(NULL),這在資料庫是可以辦得到的,
可以使用不允許沒有資料(NOT NULL)這個限制(Constraint),讓此欄位不能為空。
舉例來說,機台保養紀錄表一定要有機台名稱,絕對不允許沒有資料(NULL),
我們來把上個例子更新一下資料表,把機台名稱設定為不允許沒有資料。

ALTER TABLE tools_pm
ALTER COLUMN name SET NOT NULL;

也可以在資料表一開始的建立階段就直接寫入這個限制。

CREATE TABLE tools_pm (
	name VARCHAR(200) NOT NULL,
	count INT
);

確認資料要符合某個條件(CHECK)

ENUM適合限制固定內容,CHECK則是適合拿來判斷,以下面這個例子來說,
計畫資料表儲存計畫名稱與計畫經費,我們希望計畫經費一定是大於0元以上,
這樣才合理。

CREATE TABLE projects (
    name VARCHAR(300) NOT NULL,
    expenditure INT CHECK (expenditure > 0)
);

如果一開始的資料表沒有限制,也可以用ADD CONSTRAINT來補上CHECK限制。

CREATE TABLE projects (
    name VARCHAR(300) NOT NULL,
    expenditure INT 
);

ALTER TABLE projects
ADD CONSTRAINT expenditure_positive CHECK (expenditure > 0);

實驗一下,寫入中忍考試計畫的經費為負50萬元,結果會是顯示此筆資料插入失敗。

INSERT INTO projects(name, expenditure)
VALUES ('中忍考試', -500000);

[補充]多欄位CHECK驗證

CREATE TABLE users (
		id SERIAL PRIMARY KEY,
		name VARCHAR(40) NOT NULL,
		created_at TIMESTAMP NOT NULL,
		update_at TIMESTAMP NOT NULL,
		CHECK (created_at < update_at)
);

唯一值(UNIQUE/PRIMARY KEY)

多數時候,資料需要被識別,每一筆資料需要有一個欄位能夠區別唯一值,換句話說,
能夠透過那個欄位來找到這筆資料,例如Email帳號、身分證、學生的學號
是現實中能夠識別唯一值的例子,資料庫可以透過設計來建立唯一值,
通常有以下幾種做法:

  • 使用自定義編號,user1, user2 …
  • 使用亂數,通常由某些程式產生 ghjouh39kkdi, neodgjg5t62 …
  • 單純使用流水號,由資料庫提供方法自動產生 1, 2 …

使用 UNIQUE 讓欄位變成唯一值

CREATE TABLE users (
	id INT NOT NULL UNIQUE
    name VARCHAR(300),
);

另一個方法是設置為主鍵約束(PRIMARY KEY),
它一樣擁有唯一值加上不能為空值的特性,
但是還有另一個特性是一張表只會有一個欄位能夠設定PRIMARY KEY,
用於關聯表格。

  • PRIMARY KEY = NOT NULL + UNIQUE
  • 一張資料表只能有一個PRIMARY KEY

關聯表格會在日後說明,目前只需要知道PRIMARY KEY代表唯一值即可。

CREATE TABLE users (
	id INT PRIMARY KEY
    name VARCHAR(300),
);

但是當我們不想自己輸入這個唯一值,而是希望資料庫幫我們寫入時,
這時候就要設置專門用來做這件事情的 SERIAL 特殊資料型別。
資料庫會自動提供流水號(數字),因此在寫入資料的時候,
不需要寫入這個欄位的值,這個技巧在資料庫設計當中很常見。

CREATE TABLE users (
	id SERIAL PRIMARY KEY
    name VARCHAR(300),
);

備註 : SERIAL 是一種會自動寫入流水號的 INT


上一篇
Day 09 寫入資料
下一篇
Day 11 資料怎麼弄(CRUD)
系列文
資料庫新手入門--以PostgreSQL為例30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言