例子: CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END; 说明: 创建了一个名为update_customer_address的触发器,当用户更新customers表中的address字段时,将触发并更新orders表中的address字段为新的值。 比如执行如下一条语句: UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones'; 数据库将自动执行如下语句: UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
CREATE INDEX
为给定表或视图创建索引。
语法:
sql-statement ::=
CREATE [UNIQUE] INDEX index-name ON [database-name .]table-name ( column-name[, column-name]* ) [ ON CONFLICT conflict-algorithm]
column-name ::=
name[ COLLATE collation-name][ ASC | DESC ]
例子: CREATE INDEX idx_email ON customers (email); 说明: 为customers表中的email创建一个名为idx_email的字段。
结构删除
DROP TABLE
删除表定义及该表的所有索引。
语法:
sql-command ::=
DROP TABLE [database-name.]table-name
例子: DROP TABLE customers;
DROP VIEW
删除一个视图。
语法:
sql-command ::=
DROP VIEW view-name
例子: DROP VIEW master_view;
DROP TRIGGER
删除一个触发器。
语法:
sql-statement ::=
DROP TRIGGER [database-name .]trigger-name
例子: DROP TRIGGER update_customer_address;
DROP INDEX
删除一个索引。
语法:
sql-command ::=
DROP INDEX [database-name .]index-name
例子: DROP INDEX idx_email;
数据操作
INSERT
将新行插入到表。
语法:
sql-statement ::=
INSERT [OR conflict-algorithm] INTO [database-name .]table-name[(column-list)] VALUES(value-list) | INSERT [OR conflict-algorithm] INTO [database-name .]table-name[(column-list)]select-statement
UPDATE
更新表中的现有数据。
语法:
sql-statement ::=
UPDATE [ OR conflict-algorithm][database-name .]table-name SET assignment[, assignment]* [WHERE expr]
assignment ::=
column-name = expr
DELETE
从表中删除行。
语法:
sql-statement ::=
DELETE FROM [database-name .]table-name[WHERE expr]