sqlite的特殊使用其SQL語句的格式為： create table table_name (

文件名称: sqlite的特殊使用

所属分类: Android

开发工具:

文件大小: 43kb

下载次数: 0

上传时间: 2011-09-18

提供者: leiwenq********

下载 (43kb)

不能下载？报告错误

详细说明：其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.s tud_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 個欄位的資料值。 ...展开收缩

(系统自动生成,下载前可以参看下载内容)