SQLite常用命令及Android数据库操作详解

发表于

1.常用的sqlite语句

创建数据库

1
sqlite3 DatabaseName.db

创建表

1
2
3
4
5
6
7
sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY   NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

删除表

1
DROP TABLE database_name.table_name;

修改表

1
2
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

插入

1
2
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

删除

1
DELETE FROM COMPANY WHERE ID = 7;

修改

1
UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

不加WHERE表示所有数据。

索引

一般使用唯一索引。

1
2
CREATE UNIQUE INDEX index_name
on table_name (column_name);

如果多个列在Where子句都使用频繁，则可以使用组合索引。

1
2
CREATE INDEX index_name
on table_name (column1, column2);

删除索引

1
DROP INDEX index_name;
1
SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

约束

  • NOT NULL 约束：确保某列不能有 NULL 值。
  • DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
  • UNIQUE 约束：确保某列中的所有值是不同的。
  • PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
  • CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
1
2
3
4
5
6
7
CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL   DEFAULT 50000.00 CHECK(SALARY > 0)
);

查询

查询所有记录

1
SELECT * FROM COMPANY;

WHERE AND OR

1
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
1
SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

比较运算符

假设变量 a=10，变量 b=20，则：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
运算符	            描述	                                 实例
==	检查两个操作数的值是否相等，如果相等则条件为真。         (a == b) 不为真。
=	检查两个操作数的值是否相等，如果相等则条件为真。          (a = b) 不为真。
!=	检查两个操作数的值是否相等，如果不相等则条件为真。        (a != b) 为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真。	     (a <> b) 为真。
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真。	(a > b) 不为真。
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真。	(a < b) 为真。
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。	(a >= b) 不为真。
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。	(a <= b) 为真。
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真。	(a !< b) 为假。
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真。	(a !> b) 为真。

逻辑运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
运算符	                            描述
AND	         AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	     BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	     EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN	         IN运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN	     IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	     LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	     GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	         NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	         OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	     NULL 运算符用于把某个值与 NULL 值进行比较。
IS	         IS 运算符与 = 相似。
IS NOT	     IS NOT 运算符与 != 相似。
||	         连接两个不同的字符串，得到一个新的字符串。
UNIQUE	     UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

指定字段

1
SELECT ID, NAME, SALARY FROM COMPANY;

指定列宽

1
2
sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

Joins

现有两个表

1
2
3
4
5
6
7
8
9
10
11
COMPANY表：
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
1
2
3
4
5
6
7
DEPARTMENT表：
ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineerin  2
3           Finance     7

交叉连接 - CROSS JOIN

1
SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineerin
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineerin
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineerin
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineerin
7           Mark        Finance
1           David       IT Billing
2           David       Engineerin
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineerin
7           Kim         Finance
1           James       IT Billing
2           James       Engineerin
7           James       Finance

内连接 - INNER JOIN
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的，可以不输。

1
2
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;
1
2
3
4
5
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
7           James       Finance

外连接 - OUTER JOIN
一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。

1
2
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;
1
2
3
4
5
6
7
8
9
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
            Teddy
            Mark
            David
            Kim
7           James       Finance

Like 子句

  • 百分号（%）： 零个、一个或多个数字或字符
  • 下划线（_）：一个单一的数字或字符
1
2
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
1
2
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

GLOB子句

1
2
3
4
5
6
7
8
9
语句                          描述
WHERE SALARY GLOB '200*'    查找以 200 开头的任意值
WHERE SALARY GLOB '*200*'   查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*'    查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB '2??'     查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB '*2'      查找以 2 结尾的任意值
WHERE SALARY GLOB '?2*3'    查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB '2???3'   查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

Limit 子句

限制提取行数

1
SELECT * FROM COMPANY LIMIT 6;

从第三位开始提取 3 个记录

1
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Order By

  • ASC 升序
  • DESC 降序
1
SELECT * FROM COMPANY ORDER BY SALARY ASC;

Group By

对相同的数据进行分组

GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

1
2
3
4
5
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
1
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Having 子句

1
2
3
4
5
6
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
1
SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

2.新建数据库以及更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME="test.db";//数据库名称
    private static final int SCHEMA_VERSION=2;//版本号,则是升级之后的,升级方法请看onUpgrade方法里面的判断
    public DatabaseHelper(Context context) {//构造函数,接收上下文作为参数,直接调用的父类的构造函数
        super(context, DATABASE_NAME, null, SCHEMA_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {//创建的是一个午餐订餐的列表,id,菜名,地址等等
        db.execSQL("CREATE TABLE restaurants (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, address TEXT, type TEXT, notes TEXT, phone TEXT);");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion==1 && newVersion==2) {//升级判断,如果再升级就要再加两个判断,从1到3,从2到3
            db.execSQL("ALTER TABLE restaurants ADD phone TEXT;");
        }
    }
}

3.增删改查

查

1
2
3
public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy, String limit)

这些参数的含义在上面的sqlite语法中基本都讲过了。

1
public Cursor rawQuery(String sql, String[] selectionArgs)

操作Cursor(图片来自这里)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public List<PointBean> getAllPoints() {  
        String sql = "select * from points";  
        SQLiteDatabase db = helper.getWritableDatabase();  
        List<PointBean> pointList = new ArrayList<PointBean>();  
        PointBean point = null;  
        Cursor cursor = db.rawQuery(sql, null);  
        while (cursor.moveToNext()) {  
            point = new PointBean();  
            point.setPointName(cursor.getString(cursor  
                    .getColumnIndex("point_name")));  
            point.setLongitude(cursor.getDouble(cursor  
                    .getColumnIndex("longitude")));  
            point.setLatitude(cursor.getDouble(cursor  
                    .getColumnIndex("latitude")));  
            pointList.add(point);  
        }  
        return pointList;  
    }

4.数据库导入

5.常用框架

如果您觉得这篇文章不错，可以打赏支持下哦，谢谢