其SQL語
句的格式為：
create table table_name
(column_name1 column_type, column_name2 column_type, …);
例如：
create table Student(stud_no text primary key, stud_name text);
如果你想知道學號為‘102’以外的學生名字，可以寫如下：
select stud_no, stud_name from Student where stud_no ‘102’ ;
如果你想知道學號不是‘101’，而且學生名字不是‘Linda’的資料，可以寫為：
select stud_name from Student
where stud_no ‘101’ and stud_name ‘Linda’;
這就把兩個表格聯結起來：
select Student.stud_name, Course.course_name from Student, Course
where Student.stud_no = Course.stud_no;
這會進行加總了：
select SUM(score1), SUM(score2) from New_Course;
還可計算平均值：
select AVG(score1), AVG(score2) from New_Course
New_Course 表格
course_name stud_no score1 score2
DATABASE 101 70.0 74.5
ART 101 60.0 45.0
DATABASE 102 95.0 90.5
GROUP BY 子句可建立比較小的組(Group)，並且對每一個組進行加總等運
算。換句話說，它產生每一組的整體性資訊。例如：
select course_name, SUM(score1), SUM(score2) from New_Course
group by course_name
得出結果為：
course_name SUM(score1) SUM(score2)
DATABASE 165.0 165.0
ART 60.0 45.0
藉由 GROUP BY 語句，可以讓 SUM()等函數對屬於一組的資料進行運算。當
你指定「GROUP BY 區域」時， 屬於同一個區域的一組資料將只會得到一行
(Column)的值。◆
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override public void onCreate(SQLiteDatabase db) {}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
}
private DatabaseHelper mOpenHelper;
public void onClick(View v){
if (v == btn){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql = "create table Student(" + "stud_no text not null, "
+ "stud_name text );";
try { db.execSQL(sql); setTitle("create table ok!"); }
catch (SQLException e) {
Log.e("ERROR", e.toString());
setTitle("create table Error!");
}}
if (v == btn2){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql = "drop table Student";
try { db.execSQL(sql); setTitle("drop table ok!"); }
catch (SQLException e) {
Log.e("ERROR", e.toString());
setTitle("drop table Error!");
}}
f (v == btn3){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
String sql_1 = "insert into Student (stud_no, stud_name) values('S108', 'Lily Chen');";
String sql_2 = "insert into Student (stud_no, stud_name) values('S201', 'Tom Kao');";
String sql_3 = "insert into Student (stud_no, stud_name) values('S333', 'Peter Rabbit');";
try { db.execSQL(sql_1); db.execSQL(sql_2); db.execSQL(sql_3);
setTitle("insert records ok!");
} catch (SQLException e) { Log.e("ERROR", e.toString()); }
}
if (v == btn4){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
String col[] = {"stud_no", "stud_name" };
cur = db.query("Student", col, null, null, null, null, null);
Integer n = cur.getCount(); String ss = Integer.toString(n);
setTitle(ss + " records"); cur.moveToFirst();
}
if (v == btn5) finish();
}}
***************8
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION); }
@Override public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE Student("
+ "stud_no" + " TEXT PRIMARY KEY,"
+ "stud_name" + " TEXT" + ");"); }
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}
}
@@@@@@@@@
if (v == btn3){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("stud_no", "S108"); cv.put("stud_name", "Lily Chen");
db.insert("Student", null, cv);
cv = new ContentValues(); cv.put("stud_no", "S201");
cv.put("stud_name", "Tom Kao");
db.insert("Student", null, cv);
cv = new ContentValues(); cv.put("stud_no", "S333");
cv.put("stud_name", "Peter Rabbit");
db.insert("Student", null, cv); setTitle("insert record ok!"); }
if (v == btn4){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
String col[] = {"stud_no", "stud_name" };
cur = db.query("Student", col, null, null, null, null, null);
Integer n = cur.getCount(); String ss = Integer.toString(n);
setTitle(ss + " records"); cur.moveToFirst(); }
if (v == btn5){
if(!cur.isAfterLast()){
String ss = cur.getString(0) + ", " + cur.getString(1);
setTitle(ss); cur.moveToNext(); }
else setTitle("======"); }
if (v == btn6){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("stud_no", "S288"); cv.put("stud_name", "Linda Wang");
db.update("Student", cv, "stud_no = 'S201'", null); }
if (v == btn7){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
db.delete("Student", "stud_no = 'S108'", null); }
if(v.equals(btn8)){
mOpenHelper = new DatabaseHelper(v.getContext());
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
if (db != null) db.close();
this.finish();
}}}
1. 欲新增一筆資料時，上一個範例使用 SQL 語句來達成，如下：
String sql = "insert into Student (stud_no, stud_name)
values ('S108', 'Lily Chen');";
db.execSQL(sql_1);
在本範例則採用另一種途徑，將資料先存入 Android的 ContentValues物件裡，
然後將此物件當成參數而傳遞給 db.insert()函數，如下：
ContentValues cv = new ContentValues();
cv.put("stud_no", "S108"); cv.put("stud_name", "Lily Chen");
db.insert("Student", null, cv);
2. 查詢時，把欲查出的欄位名稱存於字串陣列裡，再把它傳給 db.query()函數即
可。如下：
String col[] = {"stud_no", "stud_name" };
cur = db.query("Student", col, null, null, null, null, null);
Integer n = cur.getCount();
查詢之後，db.query()回傳資料庫游標(Cursor)值給 cur，然後 cur.getCount()就
傳回所查到的資料筆數。
3. 指令： if( !cur.isAfterLast() ){
String ss = cur.getString(0) + ", " + cur.getString(1);
// …….
cur.moveToNext();
}
由 cur.isAfterLast()判斷目前是否已經超出最後一筆資料了。如果不是，就藉由
cur.getString(0)取得第 1 個欄位的資料；而 cur.getString(0)取得第 2 個欄位的資
料值。
扫一扫
2397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
