创建表
//创建表
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 :取出三条数据