Android-sqlite的命令，保证数据库只有最新的若干条

创建表

//创建表
    create table student(sid integer primary key autoincrement not null,
gender varchar(20)  not null,
score  integer  not null);

增

insert into student(sname,gender,score) values('Li','male',88);



CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer);
INSERT INTO Cars VALUES(1,'Audi',52642);

删

delete from student where sid=3;

改

update student set score=100,gender='male' where sname='Mike';

查

select * from student;
select * from student where gender='female';
//模糊查询
select * from student where sname like '%a%';
select * from student where sname like 'a%';
//排序查询
select * from student order by score desc;    降序
select * from student order by score；         默认升序
select * from student order by score asc;    升序

报错：

android.database.sqlite.SQLiteException: no such column: (Sqlite code 1)

AndroidRuntime: FATAL EXCEPTION: main
  Process: com.xq.mygreendao, PID: 1742
 android.database.sqlite.SQLiteException: no such column: 许强 (Sqlite code 1): , while compiling: insert into user(name,age,sex,salary) values(许强,18,男,160000), (OS error - 2:No such file or directory)
 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:925)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:536)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
 at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
   at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1965)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1890)
    at com.xq.mygreendao.db.helper.UserSqlUtils.insertSQL(UserSqlUtils.java:52)
    at com.xq.mygreendao.MainActivity.insertSql(MainActivity.java:256)
     at com.xq.mygreendao.MainActivity.onClick(MainActivity.java:125)
        at android.view.View.performClick(View.java:5647)
       at android.view.View$PerformClick.run(View.java:22479)
        at android.os.Handler.handleCallback(Handler.java:761)
        at android.os.Handler.dispatchMessage(Handler.java:98)
        at android.os.Looper.loop(Looper.java:156)
          at android.app.ActivityThread.main(ActivityThread.java:6623)
      at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:942)
       at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:832)

检查建表字段的类型，然后打印输出的语句是否符合：

字段都为字符串。

 public void insertSQL(String name, String age, String sex, String salary) {
        SQLiteDatabase db = DBHelper.getInstance(context).getReadableDatabase();
        //String stu_sql="insert into user(name,age,sex,salary) values('许强','18','男','12700')";
        String strSql = "insert into " + UserTableUtil.TABLE_NAME +
                "(" + UserTableUtil.NAME + "," + UserTableUtil.AGE + "," + UserTableUtil.SEX + "," + UserTableUtil.SALARY + ")" +
                " values('" + name + "','" + age + "','" + sex + "','" + salary + ")";
        Log.e("UserSqlUtils", "插入数据====" + strSql);
        db.execSQL(strSql);
    }

打印log：

insert into user(name,age,sex,salary) values('许强','18','男','500000)

发现最后少了一个单引号

修改后：

 public void insertSQL(String name, String age, String sex, String salary) {
        SQLiteDatabase db = DBHelper.getInstance(context).getReadableDatabase();
        //String stu_sql="insert into user(name,age,sex,salary) values('许强','18','男','12700')";
        String strSql = "insert into " + UserTableUtil.TABLE_NAME +
                "(" + UserTableUtil.NAME + "," + UserTableUtil.AGE + "," + UserTableUtil.SEX + "," + UserTableUtil.SALARY + ")" +
                " values('" + name + "','" + age + "','" + sex + "','" + salary + "')";
        Log.e("UserSqlUtils", "插入数据====" + strSql);
        db.execSQL(strSql);
    }

再次运行打印log：

insert into user(name,age,sex,salary) values('许强','18','男','500000')

保证数据库只有最新的5条数据

drop table if exists student;

create table student
(
    sid    integer primary key autoincrement not null,
    sname  varchar(20)                       not null,
    gender varchar(20)                       not null,
    score  integer                           not null
);


insert into student(sname, gender, score)
values ('Li1', 'male', 88);
insert into student(sname, gender, score)
values ('Li2', 'male', 88);
insert into student(sname, gender, score)
values ('Li3', 'male', 88);
insert into student(sname, gender, score)
values ('Li4', 'male', 88);
insert into student(sname, gender, score)
values ('Li5', 'male', 88);

select count(*)
from student;

insert into student(sname, gender, score)
values ('Li6', 'male', 88);
insert into student(sname, gender, score)
values ('Li7', 'male', 88);

-- 查询表中总共多少条数据
select count(*)
from student;

-- 按sid倒叙查询第六条数据的sid
select sid
from student
order by sid desc
limit 1 offset 5;

-- 删除表中sid小于等于第六条sid的数据( 核心 )
delete
from student
where sid <= (select sid
              from student
              order by sid desc
              limit 1 offset 5);

其他

SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
//offset 2：偏移两条数据，从第三个开始
//limit 3 ：取出三条数据