根据写的小程序来记录下关于SQL的简单使用，部分代码直接引用了程序里的代码
首先创建表：
"create table stu_table(id int,sName verChar(30),sSex verChar(10),sAge int)";
db.execSQL(sql);ID的属性是各种信息！
SQL增删查改对应的语句如下：
增加数据（两种方法）：
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 }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 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 }
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()});修改数据（两种方法）：
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]
});
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>
显示如下：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
