SQLite功能

发表于
  • .databases命令查看数据库文件所在目录
  • .quit退出sqlite提示符
  • .tables查看已创建数据表
  • .schema查看数据表完整信息

运算符
表达式
AND/OR运算符

SQLite创建数据库

  • $sqlite3 DatabaseName.db
    • 在命令窗口所在目录下创建DatabaseName.db文件

通常数据库名称在数据库管理系统内应是唯一的

  • .dump点命令可将数据库导出到文本文件中
    • $sqlite3 DatabaseName.db .dump > filename.sql
  • 可以从导出的文本文件直接恢复为数据库
    • $sqlite3 DatabaseName.db < filename.sql

SQLit附加数据库

  • ATTACH DATABASE 'DatabaseName.db' As 'Alias-Name';
    • 相当于给数据库DatabaseName.db设置了一个别名

数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库，不能用于附加；否则会得到警示：Error: database TEMP(MAIN) is already in use

SQLite分离数据库

  • DETACH DATABASE 'Alias-Name';
    • 相当于将数据库的一个别名去掉

无法分离 main 或 temp 数据库

SQLite创建表

CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, );

例如：

1
2
3
4
5
6
7
$sqlite> CREATE TABLE UESRS(
   UID INT PRIMARY KEY     NOT NULL,
   NAME            TEXT    NOT NULL,
   AGE             INT     NOT NULL,
   ADDRESS         CHAR(50),
   SALARY          REAL
);
  • UID是主键
  • NOT NULL约束字段为非空

SQLite删除表

  • DROP TABLE database_name.table_name;

例如：

  • sqlite>DROP TABLE users;

SQLite添加数据行

1
2
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) 
VALUES (value1, value2, value3,...valueN);
  • 为表中所有列添加值可以不需指定列名称，只需确保顺序一致
    • INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

  • 在一个有一组字段的表上使用select语句填充数据到另一个表中:

1
2
3
4
INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

SQLite获取数据

  • SELECT column1, column2, columnN FROM table_name;
  • SELECT * FROM table_name;

例如：

  • $sqlite>.header on
  • $sqlite>.mode column

  • $sqlite> SELECT * FROM users;

    • 前二个命令用于格式化输出

  • 设置输出列宽度：

    • $sqlite>.width 10, 20, 10
    • 第一列为10，第二列为20，第三列为10

  • $sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

    • 列出数据库中已创建的表
    • 类似于.tables点命令

  • $sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

    • 列出表‘COMPANY’的字段信息
    • 类似于.schema点命令

SQLite修改记录

  • UPDATE子句：
1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

SQLite删除记录

  • DELETE子句：
1
2
DELETE FROM table_name
WHERE [condition];

SQLite过滤记录

1
2
3
SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]
  • 也可用在UPDATE、DELETE语句中

  • 使用比较或逻辑运算符指定条件，比如 >、<、=、LIKE、NOT等等

  • 例如：

    • $sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
    • $sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
    • $sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
    • $sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
    • $sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
      • ‘%’和’*’是通配符号
    • $sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
    • $sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

  • SQL子查询：

    • $sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
    • $sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

模式匹配

  • LIKE运算符 : 匹配通配符指定模式文本值
    • %百分号 : 代表零个、一个或多个数字或字符
    • _下划线 : 代表一个单一的数字或字符
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
SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'
or 
SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX_'
  • GLOB运算符 : 匹配通配符指定模式的文本值
    • *星号 : 代表零个、一个或多个数字或字符
    • ?问号 : 代表一个单一的数字或字符

GLOB是大小写敏感的，而LIKE大小写一样

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
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'

获取数据的行数限制

  • LIMIT 子句：返回不超过number行数据
1
2
3
SELECT column1, column2, columnN 
FROM table_name
LIMIT [number of rows]
  • LIMIT子句与OFFSET子句一起使用：
    • 返回从[row number]的下一行开始直到给定的[number of rows]为止的所有行
1
2
3
SELECT column1, column2, columnN 
FROM table_name
LIMIT [number of rows] OFFSET [row number]

获取数据的排序

  • ORDER BY 子句：
1
2
3
4
SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
    • ASC升序，DESC降序，默认升序

例如：

  • $sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
  • $sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY DESC;

获取数据的分组

  • GROUP BY 子句：
1
2
3
4
5
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

例如 : $sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

  • Having 子句：允许指定条件来过滤将出现在最终结果中的分组结果
    • HAVING 子句须放在 GROUP BY 子句之后，在 ORDER BY 子句之前
1
2
3
4
5
6
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
  • 例如 : 显示名称计数小于 2 的所有记录
    • sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

SQLite消除重复记录

  • Distinct 关键字：在一个表中有多个重复的记录的情况下只获取唯一一次记录，而不是获取重复记录
1
2
3
SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]
    • 例如：$sqlite> SELECT DISTINCT name FROM COMPANY;

more

参考 : http://www.runoob.com/sqlite/