--创建表                              主键

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;

}