根据写的小程序来记录下关于SQL的简单使用，部分代码直接引用了程序里的代码
首先创建表：
String sql="create table stu_table(id int,sName verChar(30),sSex verChar(10),sAge int)";
db.execSQL(sql);
ID的属性是各种信息！
SQL增删查改对应的语句如下：
增加数据（两种方法）：
1、
1 private void insert(SQLiteDatabase db) { 2 3 //实例化常量值 4 ContentValues cV = new ContentValues(); 5 cV.put("name","zhangsan"); 6 cV.put("age","21"); 7 db.insert("stu_table",null,cV); //调用insert()方法插入数据 8 }
2、
1 db.execSQL("insert into stu_table(id,sName,sSex,sAge)values(?,?,?,?)", 2 new Object[]{ 3 numText.getText().toString(), 4 nameText.getText().toString(), 5 sexText.getText().toString(), 6 ageText.getText().toString()} 7 8 //接受四个用户输入的数据，直接用SQL实现增加数据 9 );
删除数据（两种方法）：
1、
1 private void delete(SQLiteDatabase db) { 2 3 //删除条件 4 String whereClause = "_id=?"; 5 6 //删除条件所在的位置 7 String[] whereArgs = {String.valueOf(2)}; 8 9 //执行删除 10 db.delete("stu_table",whereClause,whereArgs); 11 }
2、
1 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); 2 SQLiteDatabase db = my_db.getWritableDatabase(); 3 db.delete("stu_table", "id=?", new String[{numText.getText().toString()});
修改数据（两种方法）：
1、
StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); SQLiteDatabase db = my_db.getWritableDatabase(); //得到用户输入的数据 String[] sss = new String[4]; sss[0] = numText.getText().toString(); sss[1] = nameText.getText().toString(); sss[2] = sexText.getText().toString(); sss[3] = ageText.getText().toString(); db.execSQL("update stu_table set sName=?,sSex=?,sAge=? where id=?", new Object[]{ sss[1], sss[2], sss[3], sss[0] });
2、
private void update(SQLiteDatabase db) { //实例化内容值 ContentValues values = new ContentValues(); //在values中添加内容 values.put("name","lisi"); //修改条件 String whereClause = "id=?"; //修改添加参数 String[] whereArgs={String.valuesOf(1)}; //修改 db.update("usertable",values,whereClause,whereArgs); }
查询数据：
查询数据是通过Cursor查询，就像一个游标。
函数如下：
public Cursor query(String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit);
table:表名
columns:列名称数组
selection:相当于where，条件
selectionArgs:条件字句
groupBy:分组列
having：分组条件
orderBy：按照指定的排序列
limit:分页查询限制
Cursor:返回值，相当于结果集ResultSet
关于Cursor的各种方法：
|
方法名称 |
方法描述 |
|
getCount() |
获得总的数据项数 |
|
isFirst() |
判断是否第一条记录 |
|
isLast() |
判断是否最后一条记录 |
|
moveToFirst() |
移动到第一条记录 |
|
moveToLast() |
移动到最后一条记录 |
|
move(int offset) |
移动到指定记录 |
|
moveToNext() |
移动到下一条记录 |
|
moveToPrevious() |
移动到上一条记录 |
|
getColumnIndexOrThrow(String columnName) |
根据列名称获得列索引 |
|
getInt(int columnIndex) |
获得指定列索引的int类型值 |
|
getString(int columnIndex) |
获得指定列缩影的String类型值 |
1 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); SQLiteDatabase db = my_db.getReadableDatabase(); 2 String strings = numText.getText().toString(); 3 Cursor cursor = db.query("stu_table", new String[]{"id", "sName", "sSex","sAge"}, "id=?", new String[]{strings}, null, null, null, null);//查询数据 4 while (cursor1.moveToNext()) { 5 String no = cursor.getString(cursor.getColumnIndex("id")); 6 String name cursor.getString(cursor.getColumnIndex("sName")); 7 String sex = cursor.getString(cursor.getColumnIndex("sSex")); 8 String age = cursor.getString(cursor.getColumnIndex("sAge")); 9 10 String sss = " " + no + " " + name + " " + sex + " " + age + " "; 11 textView.setTextSize(30); 12 textView.setText(sss); //把查询到的数据显示到textview 13 }
下面是全部代码，
MainActivity.java代码如下：
1 package com.example.lenovo.stu_imfo; 2 3 import android.app.AlertDialog; 4 import android.content.DialogInterface; 5 import android.database.Cursor; 6 import android.database.sqlite.SQLiteDatabase; 7 import android.support.v7.app.AppCompatActivity; 8 import android.os.Bundle; 9 import android.util.Log; 10 import android.view.View; 11 import android.widget.Button; 12 import android.widget.EditText; 13 import android.widget.TextView; 14 import android.widget.Toast; 15 16 public class MainActivity extends AppCompatActivity { 17 18 private Button queBut; 19 private Button addBut; 20 private Button delBut; 21 private Button updBut; 22 private EditText numText; 23 private EditText nameText; 24 private EditText sexText; 25 private EditText ageText; 26 private TextView textView; 27 28 @Override 29 protected void onCreate(Bundle savedInstanceState) { 30 super.onCreate(savedInstanceState); 31 setContentView(R.layout.activity_main); 32 33 setView(); 34 setListener(); 35 } 36 37 //建立各种view 38 private void setView() { 39 queBut = (Button) findViewById(R.id.queBut); 40 addBut = (Button) findViewById(R.id.addBut); 41 delBut = (Button) findViewById(R.id.delBut); 42 updBut = (Button) findViewById(R.id.updBut); 43 numText = (EditText) findViewById(R.id.numText); 44 nameText = (EditText) findViewById(R.id.nameText); 45 ageText = (EditText) findViewById(R.id.ageText); 46 sexText = (EditText) findViewById(R.id.sexText); 47 textView = (TextView) findViewById(R.id.info); 48 } 49 50 //全部的监听事件 51 public void setListener() { 52 queBut.setOnClickListener(new queryListener()); 53 addBut.setOnClickListener(new InsertListener()); 54 delBut.setOnClickListener(new DeleteListener()); 55 updBut.setOnClickListener(new ModifyListener()); 56 } 57 58 59 //添加数据 60 class InsertListener implements View.OnClickListener { 61 62 @Override 63 public void onClick(View v) { 64 Boolean flag = false; 65 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); 66 SQLiteDatabase db = my_db.getWritableDatabase(); 67 final String[] ss = new String[4]; 68 ss[0] = numText.getText().toString(); 69 ss[1] = nameText.getText().toString(); 70 ss[2] = sexText.getText().toString(); 71 ss[3] = ageText.getText().toString(); //得到用户输入的数据 72 73 if (!ss[0].equals("") && !ss[1].equals("") && !ss[2].equals("") && !ss[3].equals("")) { 74 Cursor cursor = db.query("stu_table", new String[]{"id"}, "id=?", new String[]{ss[0]}, null, null, null); 75 while (cursor.moveToNext()) { 76 String No = cursor.getString(cursor.getColumnIndex("id")); 77 if (!No.equals(ss[0])) 78 flag = false; 79 else { 80 flag = true; 81 break; 82 } 83 } 84 if (flag == false) { 85 db.execSQL("insert into stu_table(id,sName,sSex,sAge)values(?,?,?,?)", 86 new Object[]{numText.getText().toString(), nameText.getText().toString(), sexText.getText().toString(), ageText.getText().toString()}); 87 Log.i("test", "——————————插入数据OK！——————————"); 88 Toast.makeText(MainActivity.this, "添加成功！", Toast.LENGTH_SHORT).show(); 89 90 } else { 91 Toast.makeText(MainActivity.this, "该学号已存在", Toast.LENGTH_SHORT).show(); 92 new AlertDialog.Builder(MainActivity.this) 93 .setTitle("提示") 94 .setMessage("该学号已存在，请重新填写信息") 95 .setIcon(R.drawable.ic) 96 .setPositiveButton("好的", new DialogInterface.OnClickListener() { 97 @Override 98 public void onClick(DialogInterface dialog, int which) { 99 setResult(RESULT_OK); 100 } 101 }).show(); 102 } 103 104 } else if (ss[1].length() == 0 || ss[2].length() == 0 || ss[3].length() == 0) { 105 new AlertDialog.Builder(MainActivity.this) 106 .setTitle("提示") 107 .setMessage("您填的信息不完整") 108 .setIcon(R.drawable.ic) 109 .setPositiveButton("我知道了", new DialogInterface.OnClickListener() { 110 @Override 111 public void onClick(DialogInterface dialog, int which) { 112 setResult(RESULT_OK); 113 } 114 }).show(); 115 } 116 117 db.close(); 118 119 } 120 } 121 122 //查询个人信息 123 class queryListener implements View.OnClickListener { 124 125 @Override 126 public void onClick(View v) { 127 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); 128 SQLiteDatabase db = my_db.getReadableDatabase(); 129 String strings = numText.getText().toString(); 130 Cursor cursor = db.query("stu_table", new String[]{"id", "sName", "sSex", "sAge"}, "id=?", new String[]{strings}, null, null, null, null); 131 if (!cursor.moveToNext()) { 132 new AlertDialog.Builder(MainActivity.this) 133 .setTitle("提示") 134 .setMessage("该学生不存在，查询失败") 135 .setIcon(R.drawable.ic) 136 .setPositiveButton("确定", new DialogInterface.OnClickListener() { 137 @Override 138 public void onClick(DialogInterface dialog, int which) { 139 setResult(RESULT_OK); 140 } 141 }).show(); 142 } else { 143 Cursor cursor1 = db.query("stu_table", new String[]{"id", "sName", "sSex", "sAge"}, "id=?", new String[]{strings}, null, null, null, null); 144 while (cursor1.moveToNext()) { 145 String no = cursor.getString(cursor.getColumnIndex("id")); 146 String name = cursor.getString(cursor.getColumnIndex("sName")); 147 String sex = cursor.getString(cursor.getColumnIndex("sSex")); 148 String age = cursor.getString(cursor.getColumnIndex("sAge")); 149 String sss = " " + no + " " + name + " " + sex + " " + age + " "; 150 textView.setTextSize(30); 151 textView.setText(sss); 152 } 153 Log.i("test", "——————————查询数据OK！——————————"); 154 my_db.close(); 155 } 156 157 } 158 } 159 160 //删除信息 161 162 class DeleteListener implements View.OnClickListener { 163 164 165 @Override 166 public void onClick(View v) { 167 new AlertDialog.Builder(MainActivity.this) 168 .setTitle("标题") 169 .setMessage("您确定要删除该学号的学生信息吗？") 170 .setIcon(R.drawable.ic) 171 .setPositiveButton("YES", new DialogInterface.OnClickListener() { 172 @Override 173 public void onClick(DialogInterface dialog, int which) { 174 setResult(RESULT_OK); 175 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); 176 SQLiteDatabase db = my_db.getWritableDatabase(); 177 db.delete("stu_table", "id=?", new String[]{numText.getText().toString()}); 178 db.close(); 179 Toast.makeText(MainActivity.this, "删除成功！", Toast.LENGTH_SHORT).show(); 180 181 } 182 183 }).setNegativeButton("No", new DialogInterface.OnClickListener() { 184 185 186 @Override 187 public void onClick(DialogInterface dialog, int which) { 188 189 } 190 }).show(); 191 192 } 193 } 194 195 196 //修改信息 197 class ModifyListener implements View.OnClickListener { 198 199 @Override 200 public void onClick(View v) { 201 202 203 StuDBHelper my_db = new StuDBHelper(MainActivity.this, "stu_db", null, 1); 204 SQLiteDatabase db = my_db.getWritableDatabase(); 205 String[] sss = new String[4]; 206 sss[0] = numText.getText().toString(); 207 sss[1] = nameText.getText().toString(); 208 sss[2] = sexText.getText().toString(); 209 sss[3] = ageText.getText().toString(); 210 if (sss[0].equals("") || sss[1].equals("") || sss[2].equals("") || sss[3].equals("")) { 211 new AlertDialog.Builder(MainActivity.this) 212 .setTitle("提示") 213 .setIcon(R.drawable.ic) 214 .setMessage("填的信息不完整！") 215 .setPositiveButton("确定", new DialogInterface.OnClickListener() { 216 @Override 217 public void onClick(DialogInterface dialog, int which) { 218 setResult(RESULT_OK); 219 } 220 }).show(); 221 } 222 if (sss[0].length() != 0 && sss[1].length() != 0 && sss[2].length() != 0 && sss[3].length() != 0) { 223 boolean flag = false; 224 Cursor cursor = db.query("stu_table", new String[]{"id"}, "id=?", new String[]{sss[0]}, null, null, null); 225 while (cursor.moveToNext()) { 226 String No = cursor.getString(cursor.getColumnIndex("id")); 227 if (No.equals(sss[0])) { 228 flag = true; 229 break; 230 } 231 } 232 if (flag == true) { 233 db.execSQL("update stu_table set sName=?,sSex=?,sAge=? where id=?", new Object[]{ 234 sss[1], sss[2], sss[3], sss[0] 235 }); 236 Toast.makeText(MainActivity.this, "修改成功！", Toast.LENGTH_SHORT).show(); 237 } 238 if (flag == false) { 239 new AlertDialog.Builder(MainActivity.this) 240 .setTitle("提示") 241 .setIcon(R.drawable.ic) 242 .setMessage("该学号不存在！") 243 .setPositiveButton("确定", new DialogInterface.OnClickListener() { 244 @Override 245 public void onClick(DialogInterface dialog, int which) { 246 setResult(RESULT_OK); 247 } 248 }).show(); 249 } 250 251 } 252 db.close(); 253 } 254 } 255 }
StuDBHelper.java代码如下：
1 package com.example.lenovo.stu_imfo; 2 3 import android.content.Context; 4 import android.database.sqlite.SQLiteDatabase; 5 import android.database.sqlite.SQLiteOpenHelper; 6 import android.util.Log; 7 8 public class StuDBHelper extends SQLiteOpenHelper { 9 public static final String TAG="TestSQL"; 10 11 public StuDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { 12 super(context, name, factory, version); 13 } 14 15 @Override 16 public void onCreate(SQLiteDatabase db) { 17 18 String sql="create table stu_table(id int,sName verChar(30),sSex verChar(10),sAge int)"; 19 Log.i(TAG,"——————————创建数据库——————————"); 20 db.execSQL(sql); 21 } 22 23 @Override 24 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 25 Log.i(TAG, "——————————数据库更新——————————"); 26 27 } 28 29 30 }
布局：
1 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" 2 android:layout_width="match_parent" 3 android:layout_height="match_parent" 4 android:layout_marginTop="30dp" 5 android:orientation="vertical"> 6 7 <LinearLayout 8 android:layout_width="wrap_content" 9 android:layout_height="wrap_content" 10 android:orientation="horizontal"> 11 12 <TextView 13 android:layout_width="wrap_content" 14 android:layout_height="wrap_content" 15 android:layout_marginLeft="30dp" 16 android:text="学号" /> 17 18 <EditText 19 android:id="@+id/numText" 20 android:layout_width="100dp" 21 android:layout_height="wrap_content" 22 android:singleLine="true" /> 23 24 <TextView 25 android:layout_width="wrap_content" 26 android:layout_height="wrap_content" 27 android:layout_marginLeft="50dp" 28 android:text="姓名" /> 29 30 <EditText 31 android:id="@+id/nameText" 32 android:layout_width="100dp" 33 android:layout_height="wrap_content" 34 android:singleLine="true" /> 35 </LinearLayout> 36 37 <LinearLayout 38 android:layout_width="wrap_content" 39 android:layout_height="wrap_content" 40 android:layout_marginTop="20dp" 41 android:orientation="horizontal"> 42 43 <TextView 44 android:layout_width="wrap_content" 45 android:layout_height="wrap_content" 46 android:layout_marginLeft="30dp" 47 android:singleLine="true" 48 android:text="性别" /> 49 50 <EditText 51 android:id="@+id/sexText" 52 android:layout_width="100dp" 53 android:layout_height="wrap_content" 54 android:singleLine="true" /> 55 56 <TextView 57 android:layout_width="wrap_content" 58 android:layout_height="wrap_content" 59 android:layout_marginLeft="50dp" 60 android:singleLine="true" 61 android:text="年龄" /> 62 63 <EditText 64 android:id="@+id/ageText" 65 android:layout_width="100dp" 66 android:layout_height="wrap_content" 67 android:singleLine="true" /> 68 </LinearLayout> 69 70 71 <Button 72 android:id="@+id/queBut" 73 android:layout_width="fill_parent" 74 android:layout_height="wrap_content" 75 android:layout_marginTop="50dp" 76 android:text="个人查询" /> 77 78 <Button 79 android:id="@+id/addBut" 80 android:layout_width="fill_parent" 81 android:layout_height="wrap_content" 82 android:layout_marginTop="10dp" 83 android:text="添加学生信息" /> 84 85 <Button 86 android:id="@+id/updBut" 87 android:layout_width="fill_parent" 88 android:layout_height="wrap_content" 89 android:layout_marginTop="10dp" 90 android:text="修改学生信息" /> 91 92 <Button 93 android:id="@+id/delBut" 94 android:layout_width="fill_parent" 95 android:layout_height="wrap_content" 96 android:layout_marginTop="10dp" 97 android:text="删除学生信息" /> 98 99 <TextView 100 android:id="@+id/info" 101 android:layout_width="wrap_content" 102 android:layout_height="wrap_content" 103 android:layout_marginLeft="30dp" 104 android:layout_marginTop="20dp" 105 android:singleLine="true" /> 106 107 </LinearLayout>
显示如下：
