SQLite——简单使用

一、Sqlite的基础类型

INTEGER:            整型

TEXT:                   文本类型

REAL:                   浮点型

NULL:                   数据值为空

BLOB：                表示一个数据块。 （解释：在实际的编程开发当中我们经常要处理一些大容量二进制数据的存储，如图片或者音乐等等。对于这些二进制数据（blob字段）我们不能像处理普通的文本那样简单的插入或者查询，为此SQLite提供了一组函数来处理这种BLOB字段类型。）

注：SQLite不提供Date与Time这两种类型。如果需要可以将这两种类型，转化成字符串或者整型

二、SQLite提供的约束条件

PRIMARY KEY：主键  

AUTOINCRIEMENT：自增

UNIQUE：唯一

DEFAULT：默认值

NOT NULL：数据不为空

CHECK(判断语句)：判断数据的数据是否符合条件

FOREIGN KEY(name) REFERENCE  [table](name)：外键

举例：有学生(Students)和班级(Classes)两张表
create students (

  id integer primary key autoincriement,

  name text unique,

  class_id integer check(class < 3),//表示class只能数输入<3的数  

  age  integer not null,

  foreign key(class) reference classes(class_id)  //表示：students.class_id的数据必须存在于classess.class_id

)

三、外键的组成与使用

组成：

FOREIGN KEY(子表中的字段)  REFERENCES(父表中的字段)

[ON {DELETE|UPDATE}  action

[NOT] DEFERRABLE INITIALLY {DEFERRED|IMMEDIATE}]

解析：

第二行表示当父表的字段更新或删除，子表中发生的操作DELETE表示删除，UPDATE表示更新   

默认为：NO ACTION

类型：（只支持单个外键约束）

NO ACTION：        不执行                             

                                使用：NO ACTION

RESTRICT：          应用程序禁止修改父键   

                                使用：ON DELETE/UPDATE RESTRICT

SET NULL：           当父键被删除或更新的时候，子键相对应的数据被设为null    

                                使用：ON DELETE/UPDATE SET NULL

SET DEFAULT：    当父键被删除或更新的时候，子键相对应的字段被设为default值，如果default值不在父键内，就会抛出异常。

 使用：ON DELETE/UPDATE SET DEFAULT

CASCADE：           将父键上的操作传给子键，就是如果删除cls_id=2则子表中的cls_id=2这一行也会被删除。

                                使用：ON DELETE/UPDATE CASCADE

第三行表示当父表的字段更新或删除，子表相对于第二行操作的时机。

默认为：立即执行

类型：记住两个就好了

DEFERRABLE                                                 立即执行

DEFERRABLE INITIALLY DEFERRED           延迟执行

注意事项：

在使用外键前，必须执行  PRAGMA foreign_keys = ON 语句

四、SQL语句的使用

①、insert语句：

insert into 表名[字段列表] values(值，值，值...);

举例：

insert into students values("章",2,12);

或者：
insert into students(name,age) values("哈",13);

特殊使用：

批量插入数据

insert into stu select * from students;    //将搜索到的数据批量插入

insert into stu(name,age) select name, age from students; 

或者可以这样

create table stu as select * from students;  //这里省去了建表的动作，相当于赋值了students表给stu表

②、select语句：

select 字段名 from 表名 where 筛选条件 group by 分组 having 分组的条件筛选 order by 排序 limit 返回数据的数量

offset 数据从起始位置的偏移量

示例：

select * from studens where count = 2 group by cls_id having cls_id >1 order by desc limit 3 offset 1;

从字段名开始：

1、distinct ：去除select中的重复行。

select distinct cls_id from studens;

2、as(设定暂时的名字)：

select cls_id as class from students where class = 2;   //class就是修改后的名字

3、使用聚合函数：

select count(cls_id) from students;

常用聚合函数列表：

1、 求个数：count
2、 求总和：sum
3、 求最大值：max
4、 求最小值：min
5、 求平均值：avg

6、字符串转化成大写：Upper()

7.、字符串转化成小写：Lower()

8、绝对值：abs()

从from 表名 开始

1、多表连接

第一种方式：(较少使用，一般使用内连接代替)

select * from classes,students where students.cls_id = classes.id

第二种方式(内连接)：

语法：from 表名 inner join 表名 on 连接的条件

select * from student inner join classes on students.cls_id = classes.cls_id

第三种方式（左外连接：假设以cls_id连接，如果表一含有表二不存在的cls_id则仍然显示，并以null代替数据）

语法：from 表名 left outer join 表名 on 连接的条件。

注：SQLite只支持左外连接，和内连接。不支持右外连接。

从where 语句开始

常用操作符：

+  、-、*、 /、> 、<、 >=、 <= 、!=等，跟平常的运算符是一致的

where cls_id < 3

常用逻辑操作符：

and(且) ,or(或)、not(非)

where cls_id between 1 and 3   表示在1~3内

where cls_id > 1 and cls_id < 3

模糊查询：

where name like "t%";//表示以t开头，%表示内容随意 

where name like "%t"//表示以t结尾的字符串

从group by语句开始

作用：将数据按照某个字段分组，相同的字段内容为一组

group by cls_id  //表示 以cls_id分组。

注：因为group by之后就变成了聚合数据，所以搜索的时候也只能使用聚合数据表示。

也就是不能select name from students group by cls_id，显示name字段，以为name是单个数据，不是聚合的数据。

cls_id已经被设置成了单个数据，所以还可以显示。

从having语句开始

作用：筛选group by之后的表的内容，所以是配合group by使用的。

having cls_id = 2   

从order by 开始

作用：设置字段的排列顺序

order by class_id desc

标识符：

desc：大到小          asc：小到大

默认以asc排序

从limit语句开始

作用：设定数据显示的开始位置和显示的个数

limit 5 offset 2    从开始的第二位获取5条数据

③、Update语句

格式：update 表名 set 给字段赋值 where 判断选择哪条数据作为修改对象。

举例：

update students set name = "王五" where name = "张三"

④、delete语句

格式：delete from 表名 where 删除的数据的条件

举例：

delete from studends where cls_id = 1;

五、修改表

首先，SQLite只支持修改表名和字段名

①、修改表名

alert table 表名 rename to 新表名

举例：

alert table students rename to stu_table

②、在表中添加字段名

alert table 表名 add column 字段名 约束条件

举例：

alert table stu_table add column parent_num integer;

但是由于，SQLite无法删除字段名，所以只能重新创建表，来间接的完成删除字段名。

举例：

create table stu_tmp as select id,name,cls_id from students;

drop table students;//删除表

alert table stu rename to students //将表名更换回来

六、数据库在Android中的使用

①、继承数据库辅助类SQLiteOpenHelper

命名：作用+DbHepler

基本格式：

public class StuInfoDbHelper extends SQLiteOpenHelper {
    //数据库名
    private static final String DB_NAME = "stuinfo.db";
    //版本号
    private static final int version = 1;
  
    public StuInfoDbHelper(Context context) {
        super(context, DB_NAME, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

②、创建students与classes表

public class StuInfoDbHelper extends SQLiteOpenHelper {
    //命名需要创建的表名，格式为 表名+TABLE，设为公共成员变量
    public static final String STUDENTS_TABLE = "students";
    public static final String CLASSES_TABLE = "classes";
    //命名表的字段名，格式为 表名(可缩写)+字段名+COL
    public static final String STU_ID_COL = "id";
    public static final String STU_NAME_COL = "name";
    public static final String STU_CLS_ID_COL = "cls_id";

    public static final String CLS_CLS_ID_COL = "cls_id";
    public static final String CLS_STU_NUM_COL = "stu_num";

    //数据库名
    private static final String DB_NAME = "stuinfo.db";
    //版本号
    private static final int version = 1;
    //创建表
    private static final String CREATE_CLASSES_TABLE = "CREATE TABLE "+
            CLASSES_TABLE+" ("+CLS_CLS_ID_COL+" integer primary key,"+
            CLS_STU_NUM_COL+" integer not null);";
    private static final String CREATE_STUDENT_TABLE = "CREATE TABLE "+
            STUDENTS_TABLE+" ("+STU_ID_COL+" integer primary key increment,"+
            STU_NAME_COL+" text not null,"+STU_CLS_ID_COL+" integer not null"+
            "foreign key(cls_id) reference classes(cls_id));";

    public StuInfoDbHelper(Context context) {
        super(context, DB_NAME, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //因为Students表中使用了外键，所以必须先声明
        db.execSQL("PRAGMA foreign_keys = ON");
        db.execSQL(CREATE_CLASSES_TABLE);
        db.execSQL(CREATE_STUDENT_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        
    }
}

这样一个数据库就创建完毕了，那么怎么使用四大操作语句呢。

③、SQL语句在代码中的应用

使用插入语句

insertWithOnComflict(String table,String nullColumnHack,ContentValues initialValues,int conflictAlgorithm)

table:表名

nullColumHack：表示插入null数据的列名，当且仅当参数3为null或者不包含数据的时候有效。

initialValues：表示数据的字段和字段值。ContentValues类似Map一样，使用put(String key,String value)来填充数据

conflictAlgorithm：表示当插入数据发生冲突时候的行为

系统提供的标识符：

CONFLICT_ROLLBACK：回滚，停止当前行为，并恢复到操作前的状态。

CONFLICT_FAIL：停止当前行为，但之前插入的语句不受到影响，后续的语句不执行。

CONFLICT_IGNORE/NONE：停止当前语句的插入，但是之前、后面语句的插入不受到影响，继续执行。

CONFLICT_REPLACE：如果只是违反了唯一性原则，新的数据替换旧的数据

一般的插入语句：

insert(String table,String nullColumnHack,ContentValues initialValues)

使用删除语句

delete(String table,String whereClause,String[] whereArgs)

table:表名

whereClause：删除的条件。

whereArgs：当whereClause语句中使用了占位符(?)的时候，该参数表示，占位符的具体数值

举例：

delete(tableName,"cls_id > ? and cls_id < ?",new String[]{"1","3"})

完整的就是：where cls_id >1 and cks_id <3

使用更新语句

updateWidthOnConflict(String table,ContentValue values,String whereClause,String[] whereArgs,int conflictAlgoritm)

这个就跟刚才语句的方式一样。

一般更新语句：

update(String table,ContentValue values,String whereClause,String[] whereArgs)

使用查询语句：

Cursor query(String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderby,String limit)

columns：表示需要显示的字段

selection：表示where语句

selectionArgs：表示where语句的占位符。

其他参数跟普通的SQL语句一样了

那么如何使用返回的参数Cursor呢。

首先我们要知道Cursor的结构：

根据图，我们可以得知，cursor初始化光标未指向数据，然后通过特定方法获取字段的值，之后通过方法移动光标。

获取字段的值的方法：

getString(int index)：index表示字段所在的位置索引。

如果不知道字段所在的位置，则使用

getColumnIndex(String columnName)：根据字段名，查找字段所在的位置索引。

当然不止getString()还有getInt(),getDouble()等方法获取函数值。

移动光标的方法：

moveToFirst()：移动到第一个数据

moveToLast()：移动到最后一个数据

moveToNext()；移动到下一个数据

其他主要方法：

getCount()：获取数据的个数

isLast()：是否光标在最后一个位置。

isClose()：光标是否关闭

close()：关闭光标     //使用结束时候，一定要记得使用，否则会占用内存。

注：Cursor不是线程安全的。

④、简单的示例：

public class MainActivity extends AppCompatActivity {
    //获取数据库
    private SQLiteDatabase mDataBase;
    //初始化View
    private Button mBtnQuery;
    private TextView mTvShow;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDataBase = new StuInfoDbHelper(this).getWritableDatabase();
        initView();
        insertData();
    }

    private void initView(){
        mBtnQuery = (Button)findViewById(R.id.main_btn_query);
        mTvShow = (TextView) findViewById(R.id.main_tv_showData);

        mBtnQuery.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                mTvShow.setText(queryStu());
            }
        });
    }
    //插入数据
    private void insertData(){
        //插入Classes表
        //第一种方式
        ContentValues values1 = new ContentValues();
        values1.put(StuInfoDbHelper.CLS_CLS_ID_COL,1);
        values1.put(StuInfoDbHelper.CLS_STU_NUM_COL,30);
        mDataBase.insert(StuInfoDbHelper.CLASSES_TABLE,null,values1);
        //普通的方式
        mDataBase.execSQL("insert into "+StuInfoDbHelper.CLASSES_TABLE+
        " values(2,40);");
        mDataBase.execSQL("insert into "+StuInfoDbHelper.CLASSES_TABLE+
                " values(3,30);");

        //插入Stu表的数据
        mDataBase.execSQL("insert into "+StuInfoDbHelper.STUDENTS_TABLE+
                "(name,cls_id)"+ " values('张三',1)");
        mDataBase.execSQL("insert into "+StuInfoDbHelper.STUDENTS_TABLE+
                "(name,cls_id)"+ " values('李四',2)");
        mDataBase.execSQL("insert into "+StuInfoDbHelper.STUDENTS_TABLE+
                "(name,cls_id)"+ " values('王五',3)");
    }

    private String queryStu(){
        //查询
        Cursor cursor = mDataBase.query(StuInfoDbHelper.STUDENTS_TABLE,new String[]{
                StuInfoDbHelper.STU_ID_COL,StuInfoDbHelper.STU_NAME_COL,StuInfoDbHelper.STU_CLS_ID_COL
        },null,null,null,null,null);
        StringBuilder sb = new StringBuilder();
        //循环输出
        while (cursor.moveToNext()){
            sb.append(cursor.getInt(0));
            sb.append(cursor.getString(1));
            sb.append(cursor.getInt(2)+"\n");
        }
        cursor.close();//记得要关闭光标
        return sb.toString();
    }
}

效果

七、使用AS提供的Sqlite3来查询数据

我用的是虚拟机（真机需要root），可以从Android Device Monitor下可以知道，database是

存储在data/data/com.项目空间名/databases/下

那么如何查看数据库呢？

方法一、导出数据库文件，然后用网上的数据库查看器查看。

方法二、使用AS自带的SQLite3来查看数据库

1、打开cmd编辑器

使用：adb devices 查看当前连接的手机设备

使用：adb shell  进入到linux编辑界面

linux指令的基本用法：

cd+空格+文件夹：表示跳转到当前目录的文件夹下

ls：表示显示当前目录的文件

2、通过指令，跳转到StuInfo.db所在的文件夹下

3、通过SQLite3 调用数据库

使用：sqlite3 stuinfo.db 

然后就是sqlite3的操作了：

.table：表示当前数据库下的表

其他使用方式：可以通过.help查询

然后就可以用查询语句来查找数据了

八、仿AsyncTask制作数据库的加载器

因为调用数据库其实是一种耗时的操作，所以最好将这些操作放进子线程中，防止应用ANR。

public abstract class DbCommand<T> {
    //创建线程池，排队执行
    private static final ExecutorService THREAD_POOL = Executors.newSingleThreadExecutor();
    //规定在主线程才能使用该方法（同AsyncTask）
    private final Handler mHandler = new Handler();
    public final void execute(){
        THREAD_POOL.execute(new Runnable() {
            @Override
            public void run() {
                //获取异步执行的数据
                final T data = doInBackground();
                //用Handle切换执行环境
                mHandler.post(new Runnable() {
                    @Override
                    public void run() {
                        onPostExecute(data);
                    }
                });
            }
        });
    }

    public abstract T doInBackground();
    public abstract void onPostExecute(T data);
}