sqlite--这样那样的sql

http://mp.blog.csdn.net/postedit

说明：插入一条并返回这一条新数据

INSERT INTO Message(prono,modelno,specno,byname,sendno,notes) 
VALUES (1,1,1,'YU118PEG',1,'发送');
select * from Message where msgno=(
SELECT LAST_INSERT_ROWID() FROM Message limit 1
)

INSERT INTO Message(msgid,senddate) VALUES (
(select count(msgid)+1 from Message 
where strftime('%m-%d','now','localtime') = strftime('%m-%d',senddate)),
datetime('now'));
SELECT * FROM Message where rowid = (select LAST_INSERT_ROWID() from Message limit 1) ;


CREATE TABLE [Message] (
  [msgno] INTEGER PRIMARY KEY AUTOINCREMENT,
  [msgid] INTEGER, 
  [prono] INTEGER REFERENCES [Product]([prono]), 
  [modelno] INTEGER REFERENCES [Submodel]([modelno]), 
  [specno] INTEGER REFERENCES [Spec]([specno]), 
  [byname] VARCHAR(40), 
  [sendno] INT(8), 
  [dealno] INT(8), 
  [removeno] INT(8), 
  [flag] number(2), 
  [senddate] DATETIME, 
  [dealdate] DATETIME, 
  [removedate] DATETIME, 
  [notes] CLOB);

删除表，是主键重置

DELETE from Message;
DELETE FROM sqlite_sequence WHERE name = 'Message';