Android SQLite（一.简介和常用语法）

一.SQLite简介和常用语法
二.数据库创建，升级及降级

一.简介

SQLite是一个轻量级的数据库，实现了自给自足的，无服务器的，零配置的，事务性的SQL数据库引擎。

二.基本SQL语法

SQLite 不区分大小写，但有一些命令是大小写敏感的，例如：GLOB和glob

1.数据类型

• 每个存储在SQLite的值都属于以下存储类之一

存储类	说明
NULL	值是一个NULL值
INTEGER	值是一个带符号的整数，根据值的大小存储在1、2、3、4、6或8个字节中
REAL	值是一个浮点值，存储为8字节的IEEF浮点数
TEXT	值是一个文本字符串，使用数据库编码存储
BLOB	值是一个blob数据，完全根据它的输入存储

关于SQLite存储类型和数据类型可以参考这篇文章:SQLite剖析之数据类型

2.创建表和删除表

• 创建表
  CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建新表，涉及到命名表，定义列及每一列的数据类型和名称
  约束： 限定数据列的规则
  • NOT NULL ：确保列不能有NULL值
  • DEFAULT ：当某列没有指定值时，为该列提供默认值
  • UNIQUE ：确保某列中所有的值是不同的
  • PRIMARY KEY ：唯一标识数据库表中的每一条记录
  • CHECK ：CHECK约束确保某列中的所有值满足一定条件
    (设计表的时候，约束条件一定要设计好，不支持删除约束和修改约束)

CRATE TABLE <TABBLENAME> (
column1 datatype,
column2 datatype,
.....
columnN datatype);

实例，创建一个Student表，ID作为主键，自增

CREATE TABLE student (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    gender TEXT,
    score INTEGER
);

如下:成功在当前数据库下创建一个名为student的表

• 删除表

DROP TABLE <TABLENAME>

3.增删改查

• 增
  INSERT INTO语句用于向数据库某个表中添加新的数据行
  语法如下：

INSERT INTO <TABLENAME> (column1,column2,……columnN) 
VALUES (value1,value2,……valueN);
//或者为表中所有列插入数据（顺序和表保持一致）
INSERT INTO <TABLENAME> 
VALUES(value1,value2,……valueN);

实例：

INSERT INTO student (name, score, age, gender)
VALUES
    ("张三", 100, 20, "男");

结果如下：

• 删
  DELETE 语句用于删除表中已有记录。可以使用WHRER 字句指定删除行，不然删除所有的。

DELETE 
FROM <TABLENAME>  
     WHERE  <condition>;

实例：

有student表数据如下，现在删除所有年龄大于20的数据

DELETE 
FROM 
      student 
      WHERE 
      age >20;

结果如下：删除了age大于20的数据

• 改
  UPDATE 语句用于修改表中已有记录。可以使用WHRER 字句指定修改行，不然修改所有行

UPDATE <TABLENAME> 
               SET column1 = value1,column2 = value2,……columnN = valueN 
               WHERE <condition>;

实例：
有student表数据如下，现在将所有学生分数小于90的男人变为女人（O(∩_∩)O）

UPDATE student 
               SET gender = "女" 
               WHERE score < 90;

结果如下：所有小于90分的都变成女人

• 查
  SELECT 语句用于从表中获取数据，以结果表的形式返回数据。

SELECT column1,column2,……columnN 
               FROM <TABLENAME> 
               WHERE <codition>;
//或者查询所有列的数据
SELECT * FROM <TABLENAME>

实例：

有student表数据如下，查询年龄在20到22之间且分数大于60的数据，并按照成绩升序排列

SELECT
    name,
    age,
    score
FROM
    student
WHERE
    age BETWEEN 20
AND 22
AND score > 60
ORDER BY
    score ASC

结果如下：

4 高级功能

高级功能很多了，篇幅有限，这里只说三个Android中经常会用到的修改表，多表查询和子查询

• 修改表
  ALTER TABLE命令用来修改表。可以使用ALTER TABLE 命令来修改表名称和在已存在的表中添加额外的列，除此之外ALTER TABLE 命令不支持其他操作。
  语法如下：

//修改表名
ALTER TABLE <old_table_name> RENAME TO <new_table_name>;
//新增字段
ALTER TABLE <TABLENAME> ADD COLUMN culumanName dataType;

实列（改名很简单不举例了）：

已用表student，字段如下，先新增一个classId字段

ALTER TABLE student ADD COLUMN classId INTEGER;

结果如下：在student表中新增了classId字段列

• 多表查询
  JOIN子句用于结合两个或多个数据库中表的记录。

  • 交叉连接：CROSS JOIN
    SELECT ... FROM table1 CROSS JOIN table2 ...;

  • 内连接：INNER JOIN
    SELECT ... FROM table1 INNER JOIN table2 ON condition;
    如果多个表存在相同的字段，可以简化成如下语句
    SELECT ... FROM table1 JOIN table2 USING(column1, ...);
    NATURAL JOIN自动连接多表中的相同字段相等值合并成一行
    SELECT ... FROM table1 NATURAL JOIN table2 ... ;

  • 外连接：OUTER JOIN
    SELECT ... FROM table1 LEFT OUTER JOIN table2 ON condition;
    如果多个表的条件字段相同，可以简化为如下语句
    SELECT ... FROm table1 LEFT OUTER JOIN table2 USING(column1, ...);

实例：有如下两张表student和school，数据如下： student表 school表

交叉连接：CROSS JOIN
交叉连接将所有连接的表内符合条件的行相乘，即如果是两个表，表1符合条件的右X行，表2符合条件的有Y行，最后的查询结果是X*Y行
如下：student表中classId=10001的有三行，school表没有条件返回所有3行，最后结果会是9行数据：
SELECT student.classId as class ,name,monitor FROM student CROSS JOIN school WHERE class=10001;
结果如下：

内部连接：INNER JOIN
内部连接根据匹配条件的连接词，筛选出匹配条件的行，然后将多表的内容合并为一行
如下：筛选出每个班的班长及其信息，从上面表数据可知一共只有三个班每个班只能有一共monitor，所以最终筛选出三行数据

SELECT
    name,
    age,
    gender,
    score,
    stu.classId AS class,
    schoolNum,
    avgScore AS classAvgScore
FROM
    student AS stu
INNER JOIN school AS sch ON stu.classId = sch.classId
AND stu.name = sch.monitor;

结果如下：

外部连接
外连接是内连接的扩展。SQLite只支持左外连接，可以简写成LEFT JOIN。
外连接的连接的方式和内连接相同，连接合并的方式也和内一样，区别是不能符合连接条件的行的也会合并一起，之所以叫左为连接就是以左边的表行为基准，无法连接的列会展示NULL。

举个栗子：
例1：上面的内连接的查询改成左外连接，已知完全符合连接条件的有三行数据，但是左表共有10行数据都会合并，但是剩下的7条数，左表没有的数据会展示成NULL

SELECT
    name,
    age,
    gender,
    score,
    stu.classId AS class,
    schoolNum,
    avgScore AS classAvgScore
FROM
    student AS stu
LEFT JOIN school AS sch ON stu.classId = sch.classId
AND stu.name = sch.monitor;

结果如下：

例2：school表插入一行，如下

然后将例1的查询语句左表换成school表，如下：

SELECT
    name,
    age,
    gender,
    score,
    stu.classId AS class,
    schoolNum,
    avgScore AS classAvgScore
FROM
    school AS sch
LEFT JOIN student AS stu  ON stu.classId = sch.classId
AND stu.name = sch.monitor;

按照之前的结论，会根据左表产生4条数据，且第4条数据因为不匹配连接，坐标没有的字段都一个是NULL值。看下结果如下，和之前总结的结论一致：

• 子查询
  子查询又叫嵌套查询，是将另一个SQLite查询内嵌入WHERE子句中的查询。
  子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。
  规则：

1. 子查询必须用括号括起来。
2. 一般情况下子查询在SELECT字句中只能用有一个列，除非在主查询中有多列，与子查询所选的列进行比较。
3. OEDER BY 不能用在子查询中，但可以使用GROUP BY。
4. 子查询返回多余一行，只能以多值运算符一起使用，如IN运算符。
5. BETWEEN运算符不能喝子查询一起使用，但可以在子查询中使用。

SELECT 语句中子查询所有
语法：

SELECT column1, ... columnN 
FROM tableName
WHERE column1 condition 
              （SELECT column1, ... columnN 
                  FROM  tableName
                  WHERE ...）

示例：
已存在Student表，数据如下：

student表
查询大于平均分数的学生，SQL如下
SELECT * FROM student WHERE score > (SELECT avg(score) FROM student);
结果如下：

INSERT中使用子查询
语法：

INSERT INTO <tableName> (column1, ... columnN)
                        SELECT *|column1, ... columnN FROM <tableName>
                        WHERE ...;

示例：
现在有个name表，将上面的student表中name都插入到新表中，SQL如下

INSERT INTO name SELECT
    name
FROM
    student;

结果：

UPDATE中使用子查询
语法

UPDATE <tableName> SET columnName = columnValue
               WHERE  <condition> 
               (SELECT <column> 
                FROM <tableName>
                WHERE ...);

示例：将Student表中小于平均分数学生的classId都变为10004
SQL如下：

UPDATE student
SET classId = 10004
WHERE
    score < (
        SELECT
            avg(score)
        FROM
            student
    );

结果： 原表数据 改变后表数据

DELETE中使用
语法：

DELETE FROM <tableName>
               WHERE <condition> 
               (SELECT <column> 
                FROM <tableName>
                WHERE ...);

示例：删除小于平均分数的数据
SQL

DELETE
FROM
    student
WHERE
    score < (
        SELECT
            avg(score)
        FROM
            student
    );

结果：