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

本文主要是介绍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: mainProcess: com.xq.mygreendao, PID: 1742android.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 sidfrom studentorder by sid desclimit 1 offset 5);

其他

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

这篇关于Android-sqlite的命令，保证数据库只有最新的若干条的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---