Android 数据库

徒步青云
242 阅读9分钟

简介

1、层次模型：树状结构

2、网状模型：网状结构

3、关系模型：数学上的关系，给数据库中存储的数据加上结构

一、关系型数据库基础

1、概念

1、关系：可以看作表，表具有行和列。列表示属性，每个行可以别看作表中的一个条目（entry）或元组（tuple）

2、属性：每个属性呗分配了一个域，用来表示这个属性可以有怎样的数据值，即用来限定属性的数据类型和数值范围

关系的属性的数量，被称为关系的度。度数分别为1、2、3的关系可被称为一元、二元、三元关系；度数高于3的，称为n元关系。

元组的数量称为基数，一般来说，基数时刻都在变化，但度数很少发生改变，或一直不变。

2、第一范式

  • 关系名唯一
  • 属性名唯一
  • 属性取值唯一：在每个元组中，每个属性只能有一个唯一值。
  • 属性值符合域约束
  • 元组唯一：两个元组可以有部分属性值相同，但关系中不应该有完全相同的元组。
  • 属性顺序不重要：各个顺序的顺序对元组之间的关系没有影响，因为每个元组是用名字来指向属性的，与属性名字的顺序无关。
  • 元组顺序不重要：元组可被添加到关系中，也可以从关系中移除。所以元组的顺序对关系无影响。

3、关联

关联是定义不同关系之间联系，通过键来定义两个关系增加的关联。

超键( super key):在关系中能唯一标识元组的属性集称为关系模式的超键。

候选键(candidate key):不含有多余属性的超键称为候选键。

主键( primary key):用户选作元组标识的一个候选键称为主键

外键(foreign key)如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键则该属性集是关系模式R1的外键。

结合实例的具体解释：

假设有如下两个表：

学生（学号，姓名，性别，身份证号，教师编号）

教师（教师编号，姓名，工资）

超键：

由超键的定义可知，学生表中含有学号或者身份证号的任意组合都为此表的超键。如：（学号）、（学号，姓名）、（身份证号，性别）等。

候选键：

候选键属于超键，它是最小的超键，就是说如果再去掉候选键中的任何一个属性它就不再是超键了。学生表中的候选键为：（学号）、（身份证号）。

主键：

主键就是候选键里面的一个，是人为规定的，例如学生表中，我们通常会让“学号”做主键，教师表中让“教师编号”做主键。

外键：

外键比较简单，学生表中的外键就是“教师编号”。外键主要是用来描述两个表的关系。

4、关系代数

关系代数的所有运算都可以被分解成5种基本运算的组合。5种基本运算分别是并集、差集、选择、投影和笛卡尔积。

关系A：

Color
Red
White
Blue

关系B：

Color
Orange
White
Black

1、并集（A U B）：并集运算符输出一个包含运算关系的所有元组的关系，可以看成“或”运算。

A U B：

Color
Orange
White
Black
Orange
Black

2、交集（A ∩ B）:交集运算符将同时存在关系A和关系B中的元组抽离出来。

A ∩ B：

Color
White

3、差集（A - B）：差集运算符是找出仅存在于关系A而不存在关系B中的元组。

Color
Red
Blue

4、笛卡尔积（A × B）

笛卡尔积就是将关系A的元组与关系B中的元组进行组合。其输出关系的度数是所有运算关系度数的和，同时他的基数是所有关系基数的积。

A.ColorB.Color
RedOrange
RedWhite
RedBlack
WhiteOrange
WhiteWhite
WhiteBlack
BlueOrange
BlueWhite
BlueBlack

5、选择（σpredicate(A)σ_{predicate}(A)）

选择运算符就是从运算对象中选择出符合指定谓词的元组，它是一元运算符。

6、投影（Πa1,a2,...,an(A)Π_{a1,a2,...,an}(A)）

投影运算符是运算对象中仅含有指定属性的所有元组。它也是一元运算符。

7、连接

可以把连接和笛卡尔积看着一类操作，但通常不需要像笛卡尔积那样找两个关系的所有组合，而是选出其中符合某些条件的内容。 自然连接(θ-)是其中被经常用到的一种，它可以通过某些共同属性将两个输入关系连接起来。

二、SQL介绍

1、数据库定义语言（DDL）

  • CREATE：创建一个新的数据库对象
  • ALTER：修改现有的数据库对象
  • DROP：删除一个数据库对象

1.1、表

CREATE TABLE

image.png

CREATE TABLE device (model TEXT NOT NULL,
    nickname TEXT,
    display_size_inches REAL);

ALTER TABLE

ALTER TABLE语句可以用来修改现有的表，如添加新的列或重命名表。但是无法重命名或删除某列。

image.png

ALTER TABLE device ADD COLUMN memory_mb REAL;

DROP TABLE

image.png

DROP TABLE device;

1.2、索引

索引是用来提高查询速度的数据库对象。与表不同，索引在创建后无法更改，因此ALTER关键字对索引不起作用。如果需要更改索引，只能先DROP INDEX语句将它删除再通过CREATE INDEX语句重建。

CREATE INDEX

CREATE INDEX语句需要应该用来定义索引的列名。最简单的索引的定义只有单个需要频繁查询的列。

image.png

CREATE INDEX idx_device_model ON device(model);

DROP INDEX

image.png

DROP INDEX idx_device_model

1.3、视图

视图在数据库中并不是真是存在，只是保存了生成视图时执行查询返回的结果，可以把视图看成数据库中的虚拟表。跟表一样，也可以对它执行查询并获取结果。但是它不支持DELETE、INSERT或UPDATE操作。

CREATE VIEW

image.png

CREATE VIEW device_name AS SELECT model, nickname FROM device;

DROP VIEW

image.png

DROP VIEW device_name;

1.4、触发器

触发器提供了一种响应数据库事件的方式。例如，可以创建触发器，在数据库添加或删除行时执行一段SQL语句。

www.jianshu.com/p/f53170c46…

CREATE TRIGGER

image.png 当device表新增数据时记录插入时间。

ALTER TABLE device ADD COLUMN insert_date INTEGER;

CREATE TRIGGER insert_date AFTER INSERT ON device
    BEGIN
        UPDATE device
        SET insert_date = datetime('now');
        WHERE _ROWID_ = NEW._ROWID_;
    END;

DROP TRIGGER

image.png

DROP TRIGGER insert_date;

2、数据库操作语言（DML）

DML包含以下3种对表的操作

  • INSERT：添加新行
  • UPDATE：修改行的属性值
  • DELETE：删除行

INSERT

INSERT语句在表中添加新行。可以通过3种方式指定往表中插入什么数据，以及往哪些列中插入值：使用VALUES关键字、使用SELECT语句，还有使用过DEFAULT关键字。

image.png

CREATE TABLE t ("id" INTEGER PRIMARY KEY, "text" TEXT DEFAULT "hello world");
INSERT INTO t DEFAULT VALUES;

INSERT INTO encoder_config (name,value,chengji,parents)
    SELECT name,value,chengji,parents
    FROM media_config WHERE 16<=ID AND ID<38;

INSERT INTO device (model, nickname, display_size_inches)
    VALUES ("Nexus One", "Passion", 3.7);

UPDATE

UPDATE语句用来修改表中现有的数据。通过WHERE子句限制具体受影响的行，如果没有WHERE子句，就会修改表中的所有行。

image.png

UPDATE device SET model = "Nexus 4" WHERE device_size_inches > 4;

DELETE

image.png

DELETE FROM device WHERE display_size_inches > 4;

SELECT

image.png

SELECT * FROM device WHERE display_size_inches > 4;

ORDER BY

SELECT语句返回的行保持着原来的顺序。我们可以使用ORDER BY子句对查询结果进行排序。

SELECT * FROM device ORDER BY model;

还可以在ORDER BY子句后通过ASC或DESC关键字控制排序，默认使用升序。

JOIN

CREATE TABLE manufacturer (id INTEGER PRIMARY KEY AUTOINCREMENT,
    short_name TEXT,
    long_name TEXT);

INSERT INTO manufacturer (short_name, long_name)
    VALUES ("HTC", "HTC Corporation");
INSERT INTO manufacturer (short_name, long_name)
    VALUES ("Samsung", "Samsung Electronics");
INSERT INTO manufacturer (short_name, long_name)
    VALUES ("LG", "LG Electronics");

ALTER TABLE device
    ADD COLUMN manufacturer_id INTEGER REFERENCES manufacturer(id);
    
UPDATE device SET manufacturer_id = 1 where model = "Nexus One";
UPDATE device SET manufacturer_id = 2 WHERE model IN ("Nexus S", "Galaxy Nexus");
UPDATE device SET manufacturer_id = 3 where model = "Nexus 4";

SELECT model, nickname, display_size_inches, short_name, long_name
FROM device
JOIN manufacturer
ON (device.manufacturer_id = manufacturer.id);

三、SQLite介绍

sqlite3 -version

1、全文索引(FTS)

www.sqlite.org/fts3.html#i… www.verydoc.net/sqlite/0000…

CREATE VIRTUAL TABLE person USING fts4(first_name, middle_name, last_name);

2、原子事务

SQLite可以用2种方式支持原子事务：日志模式和预写日志模式（write-ahead-log，简称WAL）。

日志模式：在主数据库文件目录下生成一个以-journal后缀的日志文件，当开始执行一个事务时，SQLite先将当前数据库内容写到这个日志文件中，然后把事务执行的操作更新到当前数据库文件中。如果事务需要回滚，那么日志文件中的内容会被放到当前的数据库文件中。

预写日志模式：事务的变更先被写到WAL文件，主数据库文件不变。提交事务时，提交记录也写到WAL文件。所以在预写模式下执行事务，写操作发生在WAL文件中，主数据库仍然可读。因为读写分离，所以在性能上，尤其是读操作多时，WAL模式在性能上会更优一些。

在一定的时候，WAL文件需要加入到主数据库文件中，我们称这个时间点位checkpoint。在默认配置下，当WAL文件达到一定大小时，就会合并。App是不需要干预这个合并的操作的。

3、多线程支持

SQLite支持多种线程模式：单线程、多线程和序列化（默认）。

4、SQLite数据类型

类型含义
INTEGER整数值。实际存储到文件中时，分别占1、2、3、4、5或8个字节
REAL浮点数类型。所有浮点数都被存储出8位的IEEE浮点数
TEXT用于存储字符串吗，编码为数据库编码
BLOB用于存储二进制数据。在数据库文件中存储的数据和SQL中传入的数据一样
NULL用于存储空值

类型亲缘性

SQLite对列数据的存储基于实际的数据而非定义的数据类型，在CREATE语句中定义列时加上数据类型，建议该列包含何种数据类型。以为SQLite类型是动态确定的，任何数据都可以被存储在任意列中。这和这个列声明时的数据类型无关，和这个列之前所含数据是何种类型也无关。

CREATE TABLE person (firstname VARCHAR(255)
                    age INT,
                    heightin_feet DOUBLE);

上面示例中，INT和INTEGER有亲缘性，VARCHAR和TEXT有亲缘性，DOUBLE和REAL有亲缘性。这样使得SQLite兼容其余数据库的建表语句。

复制表和删除表

CREATE TABLE data_table (column1 TEXT NOT NULL,
    column2 TEXT NOT NULL,
    column3 TEXT NOT NULL);
    
INSERT INTO data_table
    VALUES ('row1_column1', 'row1_column2', 'row1_column3');
INSERT INTO data_table
    VALUES ('row2_column1', 'row2_column2', 'row2_column3');
INSERT INTO data_table
    VALUES ('row3_column1', 'row3_column2', 'row3_column3');

CREATE TABLE temp_table AS SELECT column1, column2 FROM data_table;
DROP TABLE data_table;
ALTER TABLE temp_table RENAME TO data_table;

事务

SQLiteDatabase db = getDatabase();
db.beginTransaction();
try {
    // insert/update/delete
    // insert/update/delete
    // insert/update/delete
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}
avatar
文章
阅读
粉丝