--创建表 主键
create table student(id integer PRIMARY KEY AUTOINCREMENT ,name varchar(20),age integer )
--主键 PRIMARY KEY
--自动增长 AUTOINCREMENT
--删除表
drop table student
--修改表的结构
alter table student add sex varchar(20)
--添加数据
insert into student (name,age) values ('shadan',18)
insert into student (name,age) values ('shabi',25)
--删除记录(where 条件)
--delete from student 全部删除
delete from student where name='shabi'
Android对象删除 SQLiteDatabase db = getWritableDatabase();
db.delete("TimeRecordTB", "Date = ?", new String[]{date});//由于date的数据中有空格 ,所有不能用sql的语句
db.close();
--修改(把shabi改为sibi)
update student set name='sibi', age=58 where name='shadan' (这是SQL语句修改)
Android对象用id修改name SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put("DisplayName", contacts.getName()); db.update("ContactTB", values,"Id=?",new String[]{id});
--查询(*所以的字段)
select * from student
--查询多个字段
select name,age from student
--查询年龄为25的人
select * from student where age=18
--查询年龄25 name=shabi
select * from student where age=25 and name='shabi'
- 倒序读取 "select * from 表名 order by id desc"
- 读取所有 "select * from 表名"
- 删除所有 "delete from 表名"
- 删除 "delete from 表名 where 标识字段 = %s" 如果表标识字段中有空格,不能用语句来删除,用Android的数据库对象删
- 修改 "update 表名 set 被修改的字段=修改后的结果 where 标识字段 = %s"
- 类型一定要转成string类型保存
- false true是关键字 修改的时候要用 "\"" +false+"\""转成string
-
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
for (int i = oldVersion; i < newVersion; i++) {
switch (i) {
case 1:
onUpgradeToVersion2(db);
break;
case 2:
// onUpgradeToVersion3(db);
break;
case 3:
// onUpgradeToVersion4(db);
break;
case 4:
// onUpgradeToVersion5(db);
break;
case 5:
// onUpgradeToVersion6(db);
break;
}}}
@Override
public void onCreate(SQLiteDatabase db) {
/*
"create table if not exists 表的名字"
+"(Id integer primary key autoincrement," //id是自动增长的
+"字段名 varchar,"
+"字段名 varchar)";
*/
String Contact = "create table if not exists ContactTB"
+ "(Id integer primary key autoincrement,"
+ "DisplayName varchar,"
+ "Number varchar,"
+ "Server varchar)";
db.execSQL(Contact);
//数据库更新版本
onUpgradeToVersion2(db);
}
private void onUpgradeToVersion3(SQLiteDatabase db) {
//表末添加尾字段
String Wang_1 = "ALTER TABLE WangchangTB ADD COLUMN Heheda VARCHAR";
String Wang_2 = "ALTER TABLE MeetingRoomTB ADD COLUMN Hehedani VARCHAR";
db.execSQL(Wang_1);
db.execSQL(Wang_2);
}
private void onUpgradeToVersion6(SQLiteDatabase db) {
//删除表
String s6 = "drop table if exists WangchangTB";
db.execSQL(s6);
}
private void onUpgradeToVersion5(SQLiteDatabase db) {
//表删除字段时,先根据原来的表创建一个临时的表(比原来的表少一个要删除的字段),并复制数据
//删除原来的表
//将临时的表名称改为原来的表
String wang5 = "create table MeetingRoomTBtow as select Id,RoomNumber,Compere,RoomMembers,MeetState from MeetingRoomTB";
String s1 = "drop table if exists MeetingRoomTB";
String s2 = "alter table MeetingRoomTBtow rename to MeetingRoomTB";
db.execSQL(wang5);
db.execSQL(s1);
db.execSQL(s2);
}
private void onUpgradeToVersion2(SQLiteDatabase db) {
//创建表,参会者的信息
String Attendance = "create table if not exists AttendanceTB"
+ "(Id integer primary key autoincrement,"
+ "meetNumber varchar,"
+ "name varchar,"
+ "isSpeaker varchar,"
+ "isOpenMicrophone varchar,"
+ "isOpenCamera varchar,"
+ "type varchar)";
db.execSQL(Attendance);
}
-数据库的操作
/*
增 ....这里的类型一定要转成string类型
*/
public synchronized boolean addAttendance2db(AttendanceInfo attendanceInfo){
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put("meetNumber", attendanceInfo.getMeetNumber());
values.put("name", attendanceInfo.getName());
values.put("isSpeaker", attendanceInfo.isSpeaker()+"");
values.put("isOpenCamera", attendanceInfo.isOpenCamera()+"");
values.put("type", attendanceInfo.isType()+"");
values.put("isOpenMicrophone", attendanceInfo.isOpenMicrophone()+"");
try {
db.insert("AttendanceTB", null, values);
} catch (Exception e) {
e.printStackTrace();
return false;
}finally {
db.close();
}
return true;
}
//增加整个集合
public synchronized boolean addAttendance2db(List list) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
try {
for(AttendanceInfo attendanceInfo : list) {
values.put("meetNumber", attendanceInfo.getMeetNumber());
values.put("name", attendanceInfo.getName());
values.put("isSpeaker", attendanceInfo.isSpeaker()+"");
values.put("isOpenCamera", attendanceInfo.isOpenCamera()+"");
values.put("type", attendanceInfo.isType()+"");
values.put("isOpenMicrophone", attendanceInfo.isOpenMicrophone()+"");
db.insert("AttendanceTB", null, values);
}
} catch (Exception e) {
e.printStackTrace();
return false;
}finally {
db.close();
}
return true;
}
/**
* 删除参会者
*/
public synchronized boolean deleteAttendance2db(String number){
SQLiteDatabase db = getWritableDatabase();
try {
String sql = String.format("delete from AttendanceTB where name = %s", number);
db.execSQL(sql);
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
db.close();
}
return true;
}
/**
* 删除所有
*/
public synchronized boolean deleteAttendance2db(){
SQLiteDatabase db = getWritableDatabase();
try {
String sql = String.format("delete from AttendanceTB ");
db.execSQL(sql);
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
db.close();
}
return true;
}
//改
public synchronized boolean setMeetContactsInfo(MeetContacts contacts) {
SQLiteDatabase db = getWritableDatabase();
try {
String sql = String.format("update MeetContactsTB set State="+contacts.getStateTemp()+" where ContactsId="+contacts.getId());
db.execSQL(sql);
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
db.close();
}
//通知局部刷新
SMUIManager.instance().sendMessage(NotifyMessage.NOTIFY_ITEM_RANGE_CHANGED_CONTACTS, null);
return true;
}
/*
*根据联系人号码,查询联系人
*/
public synchronized MeetContacts findMeetContactInfo(String numberID) {
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try {
String sql = String.format(Locale.getDefault(), "select * from MeetContactsTB where ContactsId="+numberID);
cursor = db.rawQuery(sql, null);
while (cursor.moveToNext()) {
MeetContacts meetContacts = new MeetContacts();
meetContacts.setName(cursor.getString(cursor.getColumnIndex("Name")));
meetContacts.setId(cursor.getString(cursor.getColumnIndex("ContactsId")));
return meetContacts;
}
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
try {
cursor.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
db.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
