在前一天的內容當中,
我們知道使用ENUM來限制資料只能是固定的某些值,
也知道了如何寫入TIMESTAMP格式,
今天要來講更多的設定與限制方法以及認識一個特別的資料NULL。
資料庫可以設定預設值,當資料沒有寫入的時候,就寫入這個預設值,
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 TABLE 來進行更新。
CREATE TABLE employees (
name VARCHAR(200),
is_marry BOOLEAN
);
ALTER TABLE employees
ALTER COLUMN name SET DATA TYPE VARCHAR(300);
備註 : VSCode 對於COLUMN支援度不好,不會自動提示這個單字。
開門見山的說NULL跟0是不同的,使用上最常見的差異是計算平均值,NULL會忽略,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;
對於有些資料欄位,我們並不希望沒有資料(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
);
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)
);
多數時候,資料需要被識別,每一筆資料需要有一個欄位能夠區別唯一值,換句話說,
能夠透過那個欄位來找到這筆資料,例如Email帳號、身分證、學生的學號
是現實中能夠識別唯一值的例子,資料庫可以透過設計來建立唯一值,
通常有以下幾種做法:
使用 UNIQUE 讓欄位變成唯一值
CREATE TABLE users (
id INT NOT NULL UNIQUE
name VARCHAR(300),
);
另一個方法是設置為主鍵約束(PRIMARY KEY),
它一樣擁有唯一值加上不能為空值的特性,
但是還有另一個特性是一張表只會有一個欄位能夠設定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