(1)修改表名:ALTER TABLE titles_test RENAME TO titles_2017;
(2)构建触发器:
create trigger audit_log AFTER INSERT ON employees_test
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END;
(3)构建视图:
CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor
(4)截取字符
substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置(注意第一个字符的位置为1,而不为0),取值范围是±(1~length(X)),当Y等于length(X)时,则截取最后一个字符;当Y等于负整数-n时,则从倒数第n个字符处截取。Z是要截取字符串的长度,取值范围是正整数,若Z省略,则从Y处一直截取到字符串末尾;若Z大于剩下的字符串长度,也是截取到字符串末尾为止。
(5)分组输出
SQLite的聚合函数group_concat(X,Y),其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号。此函数必须与 GROUP BY 配合使用。
(6)case...when....
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(CASE b.btype
WHEN
1
THEN s.salary *
0.1
WHEN
2
THEN s.salary *
0.2
ELSE s.salary *
0.3
END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date =
'9999-01-01'