SQLite数据库数据查询练习，sqlite数据查询
题目：
(1) 向表中插入20条数据 (10分)
(2) 查询学生表所有信息 (10分)
(3) 查询学生表信息，只显示名字，院系，和学号 (10分)
(4) 查询显示计算机和英语系学生的成绩 (10分)
(5) 查询18-22岁学生的成绩 (10分)
(6) 查询表中有共有多少条数据 (10分)
(7) 查询成绩表中分数最高的学生信息 (10分)
(8) 查询显示名字叫李四的学生信息 (10分)
(9) 查询语文成绩的总和 (10分)
(10) 查询英语平均成绩 (10分)
效果图：
项目结构：
布局文件代码：
item_layout.xml:
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical"> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal"> <TextView android:id="@+id/code_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/name_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/age_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/sex_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/birth_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal"> <TextView android:id="@+id/department_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/address_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/chineseCode_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> <TextView android:id="@+id/englishCode_tv" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:textSize="18sp" /> </LinearLayout> </LinearLayout>
activity_main.xml:
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context="com.mrzhao.sqlitedemo2.MainActivity"> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal"> <Button android:id="@+id/insert_bt" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="插入" /> <Button android:id="@+id/bt1" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询1" /> <Button android:id="@+id/bt2" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询2" /> <Button android:id="@+id/bt3" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询3" /> <Button android:id="@+id/bt4" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询4" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal"> <Button android:id="@+id/bt5" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询5" /> <Button android:id="@+id/bt6" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询6" /> <Button android:id="@+id/bt7" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询7" /> <Button android:id="@+id/bt8" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询8" /> <Button android:id="@+id/bt9" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:onClick="onClick" android:text="查询9" /> </LinearLayout> <ListView android:id="@+id/listView" android:layout_width="match_parent" android:layout_height="match_parent"></ListView> </LinearLayout>
db包下：
Config文件:
public class Config { //数据库的名字 public static final String DATABASE_NAME = "student.db"; //表名 public static final String TABLE_NAME = "student"; //数据库版本号 public static final int DB_VERSION = 1; }
SQLiteHelper文件：
public class SQLiteHelper extends SQLiteOpenHelper {
public SQLiteHelper(Context context) {
super(context, Config.DATABASE_NAME, null, Config.DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建表：
db.execSQL("create table if not exists student(_id integer primary key autoincrement,Id INT(10),name VARCHAR(10),age INT(4),sex VARCHAR(10),birth VARCHAR(10),department VARCHAR(10),address VARCHAR(100),chinesScore DOUBLE(10),englishScore DOUBLE(10))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}DBUtils文件:
public class DBUtils { private Context context; private SQLiteHelper helper; //最好使用单例模式 public DBUtils(Context context) { this.context = context; helper = new SQLiteHelper(context); } /** * 向表中插入一个学生 * * @param student * @return */ public boolean insertData(Student student) { SQLiteDatabase db = helper.getReadableDatabase(); ContentValues values = new ContentValues(); values.put("Id", student.getId()); values.put("name", student.getName()); values.put("age", student.getAge()); values.put("sex", student.getSex()); values.put("birth", student.getBirth()); values.put("department", student.getDepartment()); values.put("address", student.getAddress()); values.put("chinesScore", student.getChinesScore()); values.put("englishScore", student.getEnglishScore()); long insert = db.insert(Config.TABLE_NAME, null, values); if (insert > 0) { return true; } return false; } /** * 查询全部的学生数据信息 * * @return */ public List<Student> getAllStudent() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("Id")); String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String sex = cursor.getString(cursor.getColumnIndex("sex")); String birth = cursor.getString(cursor.getColumnIndex("birth")); String department = cursor.getString(cursor.getColumnIndex("department")); String address = cursor.getString(cursor.getColumnIndex("address")); double chinesScore = cursor.getDouble(cursor.getColumnIndex("chinesScore")); double englishScore = cursor.getDouble(cursor.getColumnIndex("englishScore")); Student student = new Student(id, name, age, sex, birth, department, address, chinesScore, englishScore); list.add(student); } } return list; } /** * 查询学生表信息，只显示名字，院系，和学号 * * @return */ public List<Student> getStudentNameAndCode() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("Id")); String name = cursor.getString(cursor.getColumnIndex("name")); String department = cursor.getString(cursor.getColumnIndex("department")); Student student = new Student(id, name, 0, "", "", department, "", 0, 0); list.add(student); } } return list; } /** * 查询显示计算机和英语系学生的成绩 * * @return */ public List<Student> getAllStudentEnglshAndCompute() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME + " where department in ('英语系' , '计算机')", null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("Id")); String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String sex = cursor.getString(cursor.getColumnIndex("sex")); String birth = cursor.getString(cursor.getColumnIndex("birth")); String department = cursor.getString(cursor.getColumnIndex("department")); String address = cursor.getString(cursor.getColumnIndex("address")); double chinesScore = cursor.getDouble(cursor.getColumnIndex("chinesScore")); double englishScore = cursor.getDouble(cursor.getColumnIndex("englishScore")); Student student = new Student(id, name, age, sex, birth, department, address, chinesScore, englishScore); list.add(student); } } return list; } /** * 查询18-22岁学生的成绩 * * @return */ public List<Student> getAllStudentAge() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME + " where age > 18 and age < 22", null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("Id")); String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String sex = cursor.getString(cursor.getColumnIndex("sex")); String birth = cursor.getString(cursor.getColumnIndex("birth")); String department = cursor.getString(cursor.getColumnIndex("department")); String address = cursor.getString(cursor.getColumnIndex("address")); double chinesScore = cursor.getDouble(cursor.getColumnIndex("chinesScore")); double englishScore = cursor.getDouble(cursor.getColumnIndex("englishScore")); Student student = new Student(id, name, age, sex, birth, department, address, chinesScore, englishScore); list.add(student); } } return list; } /** * 查询表中有共有多少条数据 * * @return */ public int getStudentCount() { SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null); return cursor.getCount(); } /** * 查询成绩表中分数最高的学生信息 */ public List<Student> getAllStudentScore() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); //降序的方式排列查询数据desc 升序 asc Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME + " order by chinesScore desc", null); if (cursor != null && cursor.getCount() > 0) { //取出第一个 就是最高分 while (cursor.moveToFirst()) { int id = cursor.getInt(cursor.getColumnIndex("Id")); String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String sex = cursor.getString(cursor.getColumnIndex("sex")); String birth = cursor.getString(cursor.getColumnIndex("birth")); String department = cursor.getString(cursor.getColumnIndex("department")); String address = cursor.getString(cursor.getColumnIndex("address")); double chinesScore = cursor.getDouble(cursor.getColumnIndex("chinesScore")); double englishScore = cursor.getDouble(cursor.getColumnIndex("englishScore")); Student student = new Student(id, name, age, sex, birth, department, address, chinesScore, englishScore); list.add(student); break; } } return list; } /** * 查询显示名字叫李四的学生信息 * * @return */ public List<Student> getStudentLisi() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME + " where name = '李四'", null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToFirst()) { int id = cursor.getInt(cursor.getColumnIndex("Id")); String name = cursor.getString(cursor.getColumnIndex("name")); int age = cursor.getInt(cursor.getColumnIndex("age")); String sex = cursor.getString(cursor.getColumnIndex("sex")); String birth = cursor.getString(cursor.getColumnIndex("birth")); String department = cursor.getString(cursor.getColumnIndex("department")); String address = cursor.getString(cursor.getColumnIndex("address")); double chinesScore = cursor.getDouble(cursor.getColumnIndex("chinesScore")); double englishScore = cursor.getDouble(cursor.getColumnIndex("englishScore")); Student student = new Student(id, name, age, sex, birth, department, address, chinesScore, englishScore); list.add(student); break; } } return list; } /** * 获取语文成绩的总和 * * @return */ public double getAllStudentChineseScoreTotal() { double total = 0; SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { double chinesScore = cursor.getDouble(cursor.getColumnIndex("chinesScore")); total += chinesScore; } } return total; } /** * 获取英语的平均成绩 * @return */ public double getAllStudentEnglishScore() { double total = 0; SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { double englishScore = cursor.getDouble(cursor.getColumnIndex("englishScore")); total += englishScore; } } return total/cursor.getCount(); } }
Student自定义学生类文件：
public class Student { private int Id; private String name; private int age; private String sex; private String birth; private String department; private String address; private double chinesScore; private double englishScore; public Student(int id, String name, int age, String sex, String birth, String department, String address, double chinesScore, double englishScore) { Id = id; this.name = name; this.age = age; this.sex = sex; this.birth = birth; this.department = department; this.address = address; this.chinesScore = chinesScore; this.englishScore = englishScore; } public int getId() { return Id; } public void setId(int id) { Id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getBirth() { return birth; } public void setBirth(String birth) { this.birth = birth; } public String getDepartment() { return department; } public void setDepartment(String department) { this.department = department; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public double getChinesScore() { return chinesScore; } public void setChinesScore(double chinesScore) { this.chinesScore = chinesScore; } public double getEnglishScore() { return englishScore; } public void setEnglishScore(double englishScore) { this.englishScore = englishScore; } @Override public String toString() { return "Student{" + "Id=" + Id + ", name='" + name + '\'' + ", age=" + age + ", sex='" + sex + '\'' + ", birth='" + birth + '\'' + ", department='" + department + '\'' + ", address='" + address + '\'' + ", chinesScore=" + chinesScore + ", englishScore=" + englishScore + '}'; } }
MyAdapter适配器文件：
public class MyAdapter extends BaseAdapter { private List<Student> list; private Context context; private LayoutInflater inflater; public MyAdapter(List<Student> list, Context context) { this.list = list; this.context = context; inflater = LayoutInflater.from(context); } @Override public int getCount() { return list.size(); } @Override public Object getItem(int position) { return list.get(position); } @Override public long getItemId(int position) { return position; } @Override public View getView(int position, View convertView, ViewGroup parent) { ViewHolder holder = null; if (convertView == null) { holder = new ViewHolder(); convertView = inflater.inflate(R.layout.item_layout, parent, false); holder.codeTv = (TextView) convertView.findViewById(R.id.code_tv); holder.nameTv = (TextView) convertView.findViewById(R.id.name_tv); holder.ageTv = (TextView) convertView.findViewById(R.id.age_tv); holder.sexTv = (TextView) convertView.findViewById(R.id.sex_tv); holder.birthTv = (TextView) convertView.findViewById(R.id.birth_tv); holder.departmentTv = (TextView) convertView.findViewById(R.id.department_tv); holder.addressTv = (TextView) convertView.findViewById(R.id.address_tv); holder.chineseTv = (TextView) convertView.findViewById(R.id.chineseCode_tv); holder.englishTv = (TextView) convertView.findViewById(R.id.englishCode_tv); convertView.setTag(holder); } else { holder = (ViewHolder) convertView.getTag(); } Student student = list.get(position); holder.codeTv.setText(student.getId() + ""); holder.nameTv.setText(student.getName()); holder.ageTv.setText(student.getAge() + ""); holder.sexTv.setText(student.getSex()); holder.birthTv.setText(student.getBirth()); holder.departmentTv.setText(student.getDepartment()); holder.addressTv.setText(student.getAddress()); holder.chineseTv.setText(student.getChinesScore() + ""); holder.englishTv.setText(student.getEnglishScore() + ""); return convertView; } static class ViewHolder { TextView codeTv, nameTv, ageTv, sexTv, birthTv, departmentTv, addressTv, chineseTv, englishTv; } }
MainActivity：
public class MainActivity extends AppCompatActivity { private ListView listView; private DBUtils dbUtils; private List<Student> list = new ArrayList<>(); private MyAdapter myAdapter; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); listView = (ListView) findViewById(R.id.listView); dbUtils = new DBUtils(this); myAdapter = new MyAdapter(list, this); listView.setAdapter(myAdapter); } public void onClick(View view) { switch (view.getId()) { case R.id.insert_bt: //插入20条数据 dbUtils.insertData(new Student(1001, "张三", 16, "男", "1989", "英语系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "李四", 21, "男", "1989", "体育系", "北京市", 10, 90)); dbUtils.insertData(new Student(1001, "李四", 122, "男", "1989", "计算机", "北京市", 200, 90)); dbUtils.insertData(new Student(1001, "王五", 18, "男", "1989", "计算机", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "赵六", 19, "男", "1989", "体育系", "北京市", 140, 90)); dbUtils.insertData(new Student(1001, "呵呵", 22, "女", "1989", "计算机", "北京市", 150, 90)); dbUtils.insertData(new Student(1001, "嘻嘻", 21, "男", "1989", "体育系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "大师兄", 28, "男", "1989", "计算机", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "沙师弟", 20, "女", "1989", "语文系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "师傅", 18, "男", "1989", "英语系", "北京市", 160, 90)); dbUtils.insertData(new Student(1001, "白龙马", 19, "男", "1989", "语文系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "猪八戒", 20, "女", "1989", "英语系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "你好", 22, "男", "1989", "英语系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "马云", 967, "男", "1989", "体育系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "马化腾", 6, "女", "1989", "语文系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "张朝阳", 457, "男", "1989", "英语系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "周鸿祎", 23, "男", "1989", "计算机", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "李彦宏", 64, "男", "1989", "体育系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "王健林", 23, "男", "1989", "计算机", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "张磊", 14, "男", "1989", "语文系", "北京市", 100, 90)); dbUtils.insertData(new Student(1001, "柴伟", 16, "男", "1989", "体育系", "北京市", 100, 90)); break; case R.id.bt1: //查询学生表所有信息 List<Student> allStudent = dbUtils.getAllStudent(); list.clear(); list.addAll(allStudent); myAdapter.notifyDataSetChanged(); break; case R.id.bt2: // 查询学生表信息，只显示名字，院系，和学号 List<Student> list1 = dbUtils.getStudentNameAndCode(); list.clear(); list.addAll(list1); myAdapter.notifyDataSetChanged(); break; case R.id.bt3: // 查询显示计算机和英语系学生的成绩 List<Student> list2 = dbUtils.getAllStudentEnglshAndCompute(); list.clear(); list.addAll(list2); myAdapter.notifyDataSetChanged(); break; case R.id.bt4: // 查询18-22岁学生的成绩 List<Student> list3 = dbUtils.getAllStudentAge(); list.clear(); list.addAll(list3); myAdapter.notifyDataSetChanged(); break; case R.id.bt5: // 查询表中有共有多少条数据 int studentCount = dbUtils.getStudentCount(); Toast.makeText(this, "" + studentCount, Toast.LENGTH_SHORT).show(); break; case R.id.bt6: //查询成绩表中分数最高的学生信息 List<Student> list4 = dbUtils.getAllStudentScore(); list.clear(); list.addAll(list4); myAdapter.notifyDataSetChanged(); break; case R.id.bt7: //查询显示名字叫李四的学生信息 List<Student> list5 = dbUtils.getStudentLisi(); list.clear(); list.addAll(list5); myAdapter.notifyDataSetChanged(); break; case R.id.bt8: // 查询语文成绩的总和 double cs = dbUtils.getAllStudentChineseScoreTotal(); Toast.makeText(this, ""+cs, Toast.LENGTH_SHORT).show(); break; case R.id.bt9: //查询英语平均成绩 double es = dbUtils.getAllStudentEnglishScore(); Toast.makeText(this, ""+es, Toast.LENGTH_SHORT).show(); break; } } }
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。