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

本文详细介绍了SQL的基本操作,包括创建表、插入数据、删除记录、更新信息及查询数据等常见任务。此外,还提供了如何处理常见错误及确保数据一致性的实用技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

创建表

//创建表
    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 :取出三条数据
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值