SQLite:

----命令：

--cmd 操作SQLite

----创建数据库：cmd> sqlite3 Lytest.db

----附加数据库：sqlite> attach database 'Lytest.db' as 'test';

----查看数据库： .databases   查看表 .tables

----分离数据库： detach databse 'test';(使用别名)

----select * from lyuser; --语句结尾要有分号

 

----新增表：create table t1(id int primary key identity(1,1) ,name varchar(30));

----primary key 主键 ,identity(1,1)标识列, not null 非空,unique , default

 

----删除表：drop table t1;

----增：insert into t1(name ) values('Tom');

----删: delete from t1 where name='Tom';

----改:update t1 set name='Tom1' where id =1;

----查:select * from t1 where name='Tom';

------使用表填充 insert into t1(name) select name from t2;

 

----关键字：select :   distinct / as /  case [id] when 1 then 'a' else 'o' end  / ROW_NUMBER()over(order by Qty desc)

                  where： and / or / exists / is / like{% _ } 

                  group by [name]

                  having count(1)>3

 

----limit 限制获取的行数： select * from t1 limit 6;(获取6行数据) select * from t1 limit 3 offset 2;(获取第2行后的3行（3，4，5）)

----连接： cross join 交叉连接 , inner join 内连接 , outer join 外连接

 

----触发器

     create trigger trig_t1 after insert     --before/after   [insert update delete]

     on t1

     begin ... end

;

 

    create trigger trig_t1_name after insert 

of name

    on t1

    for each row

    when  len(

new.name) >5

    begin

        update t2 set name = new.name where name = 

old.name;

    end

;

    

----索引：

----create [] index idx_t1_id on t1(id);--[]:unique 唯一

----drop index idx_t1_id;

----试图：

----create view v_t1 as select id,name from t1;

----drop view v_t1;

----事务：

----begin; commit; rollback;

     begin;

     delete from t1 where id=2;

    if()

     commit;

    else rollback;