最近看到資料,對建表有了進一步認識,原來建表還可以這樣建立。寫下來，做個記錄。
CREATE TABLE payroll
( employee_id INT NOT NULL
, base_salary DOUBLE
, bonus DOUBLE
, commission DOUBLE
, total_pay DOUBLE GENERATED ALWAYS AS
??????????? (base_salary*(1+bonus) + commission)
)INSERT INTO payroll VALUES (1, 100, 0.1, 20, DEFAULT);
或者
INSERT INTO payroll (employee_id, base_salary, bonus, commission)
VALUES (1, 100, 0.1, 20);
這樣就是建表的時候加入了計算了。省好多事情。很方便。
復雜的如下：
CREATE TABLE payroll2
( employee_id INT NOT NULL
, employee_type CHAR(1) NOT NULL
, base_salary DOUBLE
, bonus DOUBLE
, commission DOUBLE
, total_pay DOUBLE GENERATED ALWAYS AS
( CASE employee_type
WHEN 'B' THEN base_salary*(1+bonus)
WHEN 'C' THEN (base_salary + commission)
ELSE 0
END
)
)
這樣對數據操作很方便。也很有實用價值。