Fork me on GitHub

Android之Sqlite

Sqlite数据库

简介
是一款微型数据库，它用在嵌入式系统中，比如Android系统。它跟传统的数据库有比较大的差别。省略了大多数的功能，比如权限，管理、触发器、存储过程等。只保留对数据库最常用到的增删改查操作。（弱类型的数据库）。

数据库相关名词

• DB（数据库）：按一定的数据结构来存放数据的仓库
• DBMS（数据库管理系统）：包括了DB，还多了一些操作管理维护DB的相关应用
• DBA（数据库管理员）
• 标准的SQL（结构化查询语言）
• 用于对数据库进行操作和管理的语言

所有数据库都通用

• 分类：
• DDL（数据定义语言）：创建库、创建表、删除库、删除表
• DML（数据操作语言）：插入、删除、修改数据
• DQL（数据查询语言）：数据查询语言
• DCL（数据控制语言）：创建用户、设置权限、撤销权限等。

市场上

  • OrmLite JDBC和Android的轻量级ORM java包
  • Sugar 用超级简单的方法处理Android数据库
  • GreenDAO 一种轻快地将对象映射到SQLite数据库的ORM解决方案
  • ActiveAndroid 以活动记录方式为Android SQLite提供持久化
  • SQLBrite SQLiteOpenHelper和ContentResolver的轻量级包装
  • Realm 移动数据库：一个SQLite和ORM的替换品
  • Room Google提供的SQL

表的管理

  • 打开/创建数据库
// 进入指定的数据库，如不存在则创建
sqlite3  数据库名
	比如：sqlite3 test.db
  • 创建表
语法
create table 表名（
	列名1 数据类型 约束条件，
	列名2 数据类型 约束条件，
	......）;
比如 省市区表中的省表
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY AUTOINCREMENT|NOT NULL,
   province_name  TEXT    NOT NULL,
   province_cd    TEXT    NOT NULL
);	
查看表结构
SELECT * from sqlite_master where type="table";

建立索引

create index index_name on table_name(filedX);

针对表中的filedX字段建立别称为index_name的索引。
index_name:索引名称
filedX:数据库中的字段

sqlite数据库支持的数据类型（5种类型）

类型 备注
null 空
integer 整型
real 浮点型
text/varchar 字符串
blob 二进制

sqlite支持的约束条件

约束条件

类型 备注
primary key 主键约束，非空且唯一每张表都应该设置一个主键，找不到合适的主键的话，可以添加一个自增长列（只能是整型）来作为表的主键。一张表最多只能有一个主键
not null 非空约束
unique 唯一约束，可以为空
check 检查约束
default 默认约束
foreign key 外键约束
AUTOINCREMENT 自增长
foreign key：外键约束
主要用于维护引用的完整性（默认没有开启，要想使用必须手动开启）
手动开启外键约束：PRAGMA foreign_keys = ON
外键列的值必须在主键列中存在，否则就会出错。
语法：字段名 数据类型 references 表名（字段名）

删除表

语法
drop table 表名

常用的指令

类型 备注
.quit 退出
.tables 查看数据库中所有表名
.headers on/off 显示/隐藏表头
.schema 表名 查看表结构
select * from 表名 查看指定表内的所有数据

数据的操作

  • 插入（insert）
语法
insert into 表名 (字段列表) values(值列表);

注意事项：
• 插入完整的一条数据时可以省略字段名
• 插入的列的个数和值的个数要对应
• 字符串都必须加上单引号，数值可加可不加
• 插入的数据必须符合建表时所规定的约束条件
• 自增长列我们不用手动去插入值

  • 更新（update）
语法
update 表名 set 字段名1 = ？,字段名2 = ？, ... [where 条件];
  • 删除（delete）
语法
delete from 表名 [where 条件];

注意事项：
• 不加条件默认删除表中所有的记录。
• 删除主键记录的时候，如果外键表有记录引用此记录的主键的时候会出错

  • 查询
语法
select 列名1,列名2,...from 表名[where 条件]
[group by 列名] [having 条件] [order by 列名 asc/desc] [limit 初始位置,查询个数]

注意事项：
• 注意条件的顺序
• select from 表名：代表所有列，一般在测试的时候使用，开发中不使用
• 查询结果可以只显示指定的列

select column1,column2 from table_name where expression;
查询某列,column个数>1

select * from table_name;
查询全部内容

select * from table_name limit 10;
限制查询的个数

select * from table_name order by filedx limit 10;
根据字段列进行排序查询

select * from table_name order by filedx asc/desc
根据字段列进行升序或降序查询

select * from table_name where filedx='';
查找复合条件的所有内容

select * from table_name where filedx like '%XXX%';
模糊查找复合条件的所有内容

select * from table_name where filed1='' and filed2 >=数字 order by filed2 desc limit 10;
查找满足条件1和条件2并且按照倒序排序最多10笔

select count(*) from table_name;
查找总数

select count(*) from table_name filedx >= 1000;
查找复合条件的个数
  • 给列取别名
select name as 姓名,address as 家庭住址 from stu_info;
// as 可以省略
select name 姓名,address 家庭住址 from stu_info; 
select stu_info.name,stu_info.address from stu_info;
当多表查询的时候，可能不同表中有同名的列，所以查询同名列的时候必须列名之前加上表名。
  • 给表取别名
select s.name,s.address from stu_info [as] s;
  • 带条件的查询语句
select name,age,gender,address from stu_info where address='上海'; select name,age,gender,address from stu_info where age>=13;

多表查询

  • 内联（inner join）
    要求：把两张表中满足条件的记录拼成一条记录。
    有两种写法：
//1)
select i.name,i.age,i.gender,i.address,c.math,c.english from stu_info as i,stu_score c where i.stuno=c.sno;
//2)
select i.name,i.age,i.gender,i.address,c.math,c.english from stu_info as i inner join stu_score as c on i.stuno=c.sno;

  • 外联（outer join）
    • 左外查询（left [outer] join）
    以左表为基准表，先和右表做内联，接下来左表中匹配不到的记录也会拼成一条记录，只是值为null
    • 右外查询（right [outer] join） sqlite不支持

  • 交叉连接（cross join）没有连接条件。

示例：
select i.name,i.age,i.gender,i.address,c.math,c.english from stu_info as i cross join stu_score as c;
  • 按列来进行排序
select name,age,gender,address from stu_info order by age asc;
select name,age,gender,address from stu_info order by age desc;
select name,age,gender,address from stu_info order by age desc,name desc;

以多个列来排序，先按age降序，再把age相同的记录按名字降序排序
获取指定个数的记录
要求：查询出班级中年龄最大的前三个学员的个人信息

select name,age,address from stu_info order by age desc limit 0,3;

查询指定字段值（不）为空的记录
要求：求没有参加英语考试的学员信息

//错误写法：
select sno from stu_score where english = null; 
//正确写法：
select sno from stu_score where english is null;
select sno from stu_score where english is not null;
  • 聚合函数
    count（列名）、sum（列名）、max（列名）、min（列名），avg（列名）在做查询语句的时候，我们不能使用列名和聚合函数一起查询，因为列查询出来的个数可能有多个，而聚合函数的结构只有一个，不能组成一张规则的表。
  • 分组查询
    要求：打印输出班级中每个城市所对应的学生数。
    注意事项：
    • 在分组中使用聚合函数，聚合函数统计的不是表中的所有记录，而是统计每个组中的记录
    • 在分组查询中，分组的列可以和聚合函数一起查询
    • 可以以多个列进行分组。
select address,count(*)as 人数 from stu_info group by address having 人数>1;

  • where 和 having的区别？
    where在分组之前对表中所有的记录进行条件过滤，而having必须用在group by 之后，主要用来对组进行设置过滤条件。
    模糊查询
    关键字: like
    • % : 代表任意多个字符（0到多）
    • _ : 代表任意一个字符（1）
    示例：
    • 查询姓名中包含字母a的所有人
    select name,age,address from stu_info where name like '%a%';
    • 查询姓名中第二个字母为h的人员信息
    select name,age,address from stu_info where name like '_h%';

  • 其他语句
    PRAGMA table_info(表名); 查看表的字段

Android中操作

创建数据库

继承系统提供的SQLiteOpenHelper支持的类型,比如

public class XXSQLiteHelper extends SQLiteOpenHelper{
public XXSQLiteHelper(Context context,String name,SQLiteDatabase.CursorFactory factory,int version){
	super(context,"DbName",null,1.0)
}
@Override
public void onCreate(SQLiteDatabase db) {
	//执行建表
	}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	//进行数据库是否升级
	}
}

建表

//建表语句
CREATE TABLE TABLE_NAME IF NOT EXISTS(field1 INTEGER PRIMARY KEY, field2 int,field3)
//删表
DROP TABLE IF EXISTS TABLE_NAME
//版本升级 修改版本号

增

//sql语句
insert into table_name values(field1, field2, field3, ...);

//系统提供的
ContentValues values = new ContentValues();
cv.put("key",value);
//影响的行数
long insert = db.insert(table_name,null,cv);

删

//sql语句
delete from table_name where filed1;

//系统提供的
//删除影响的行数
int delete = database.delete(table_name, field2 + " LIKE? ", new String[]{value});

改

//sql语句
update from table_name set filed1=“oldValue” where filed2="oldValue"

//系统提供的
ContentValues values = new ContentValues();
values.put(key, value);
//影响的行数
int update = database.update(table_name, values, filed2 + " LIKE? ", new String[]{value2});

查

//sql语句
//查询所有内容
select * from table_name;
//查询限制个数
select * from table_name limit 10;
//根据字段查看限制笔数
select * from table_name order by filed1 limit 10;
//根据字段倒序查看限制笔数
select * from table_name order by filed1 desc limit 10;
//根据字段查看指定类型的数据
select * from table_name where filed2 ='Jodie Foster';
//查看字段匹配的字符
select * from table_name where starring like 'Jodie%';
//查看符合条件的字段
select filed1, filed2 from table_name where filed1 like 'Jodie%' and filed2 >= 1985 order by filed2 desc limit 10;
//查看表中的数量
select count(*) from table_name;
//查看表述符合条件的数量
select count(*) from table_name where filed1 >= 1985;

//系统提供的
//方法1
String[] columns = new String[]{filed1, filed2...};
Cursor cursor = database.query(table_name, columns, filed2 + " = ?", new String[]{value}, null, null, null);
//返回的Cursor

//方法2
String querySql = "select * from" + table_name + " where " + filed2 + " = ?";
Cursor cursor = database.rawQuery(querySql, new String[]{value});
//返回的Cursor

其他命令

直接执行shell命令
sqlite3 [options] xx.db "sql语句"
options: -html html表格形式输出

sqlite3 xx.db ".dump" > output.sql
数据库[到出来]

数据备份
sqlite3 xx.db ".dump" > output.sql
sqlite3 xx.db < output.sql
利用输出的资料建立一个一模一样的数据库就是备份数据库的操作
大量插入资料操作时:
begin;
执行你的操作
commit;
posted @ 2021-06-05 11:39  KevinAt2022  阅读(150)  评论(0)    收藏  举报