终端格式化:
.mode column
.header on
匹配条件语法:
where 列名 like 列值
1.若列值为数字 相当于 列名=列值;
2.若列值为字符串 可以用通配符"%"代表缺少的字符(一个或多个)。
例子:
select * from persons where id
like 3 ;
select * from persons addr
like '
%jing
%';
创建视图:
1. 创建视图:
create view PersonsGrade as select name,addr,score,year from persons,grade where persons.id=grade.id and name='Lucy';2. 删除视图 :
drop view 视图名;
触发器:
1.创建触发器:
语法: create trigger 触发器名【before | after】[insert | update | delete ] on 表名 begin 语句 ; end ;
ege:
create trigger tg_delete after delete on persons begin
delete from grade where id=old.id;
end;
注意:old.id 等价于persons.id , 但此处不能写persons.id, old.id 代表删除行的id(id 代表两个表的关联列)
2. 查看触发器和删除触发器
查看:
select name from sqlite_master where type='trigger' and tbl_name='表名
' ;
删除:
drop trigger 触发器名;
3. 写入日志
create table log(time text,date text);
create trigger tg_update before update on persons begin insert into log log values(datetime('now'),'update persons' );end;
索引:
创建索引
语法:create index 索引名 on 表名(列名);
查看索引: .indices
删除索引: drop index 索引名;
ege:
create index persons_id_index on persons( id ) ;
select * from peersons order by id desc ;
索引中定义多个列
create index persons_id_name_index on persons( id , name );