SQLite的基本用法介绍

SQLite作为一个广泛使用的嵌入式数据库

关于SQLite

SQLite是一个嵌入式关系数据库引擎。

项目自2000年开始，iOS/Android内置，是目前非常流行的嵌入式数据库
轻量，官方称包含全部feature的库不到600KB
稳定，跨平台兼容，向后兼容，官方称支持到2050年
直接读写文件，一个完整的包含多个表、索引、触发器和视图的SQL数据库，可以包含在一个单一的文件中。
主流版本V3

SQL（Structured Query Language）是一门数据库计算机语言，设计来用在RDBMS中管理数据的。SQLite实现了SQL-92标准的大部分feature。

一些基本定义：

关系型数据库是一系列组织在表（table）中的数据的集合，表之间存在关系，表包含了行与列。

一张表（table）是一组值的集合，这些值以table的列为模型进行组织，列使用名称进行标识。一个数据库系统的scheme是其结构描述，定义了表、值域、关系、视图、索引、函数、触发器等。

数据表中的每一行（row）数据代表了一个单一的隐式结构化的数据，也称之为一个记录（record）。每一列（column）是一组特定类型数据的集合，其中每一个值是表格中每一行所在列的值。一个值域（field）是指一行一列交叉点的数据。一个主键（primary key）可以唯一标识表中的一个记录。一个外键（foreign key）是两个table之间的引用约束。触发器（trigger）是些响应作用在特定table中特定事件的一些程序代码。视图（view）是对一个或多个表的数据的具体的视角，用来以特定次序、突出或隐藏某些数据。视图由一些存储好的查询组成，就如同操作一个虚拟的由查询结果集组成的表一样。与普通表不同的是，视图没有物理的表结构，是动态的计算型的。

事务（Transaction）是一组对数据库数据操作的原子性的操作单元。事务中的SQL语句要么全部提交到数据库中，要么回滚。SQL的结果集（result set）是从数据库中执行SELECT语句的一系列行数据。索引（index）是一种用来提高数据操作。

基本命令

创建数据库文件
> sqlite3 test.db

查询数据库中的表：
sqlite> .tables
Cars

查询语句
sqlite> SELECT * FROM Friends;
1|Jane|F
2|Thomas|M
3|Franklin|M
4|Elisabeth|F
5|Mary|F
6|Lucy|F
7|Jack|M
  
修改
sqlite> .separator :

.mode column
  
.headers on
.nullvalue NULL

sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books;
Name         Title     
-----------  ----------
Jane Austen  Emma      
Leo Tolstoy  War and Pe
Joseph Hell  Catch XII 
Charles Dic  David Copp
Joseph Hell  Good as Go
Leo Tolstoy  Anna Karen
  
  
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: on
        mode: column
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: store.db
      
sqlite> .schema Cars
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);

读进sql语句文件执行
sqlite> .read friends.sql

导出数据表结构与数据
sqlite> .output cars2.sql
sqlite> .dump Cars
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Mercedes',57127);
INSERT INTO Cars VALUES(3,'Skoda',9000);
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT INTO Cars VALUES(5,'Bentley',350000);
INSERT INTO Cars VALUES(6,'Citroen',21000);
INSERT INTO Cars VALUES(7,'Hummer',41400);
INSERT INTO Cars VALUES(8,'Volkswagen',21600);
COMMIT;


$ tail -5 ~/.sqlite_history 
  
$ ~/.sqliterc 配置文件，比如.mode，.headers等
  
 sqlite> .help

SQLite数据类型：Type Affinity

integer: 有符号整型，根据数值大小，可能存 1/2/3/4/6/8 bytes
real: 浮点类型，8 bytes
text: 字符串（支持 utf-8、utf-16）
blob: 二进制数据，输入啥，就存啥
null: NULL 值

SQLite语句

CREATE TABLE Testing(Id INTEGER);
CREATE TABLE IF NOT EXISTS Testing(Id INTEGER);
CREATE TABLE Cars2 AS SELECT * FROM Cars;

sqlite> ATTACH DATABASE 'test2.db' AS test2;
CREATE TABLE test2.Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO test2.Cars VALUES(1, 'Porsche', 107699);

CREATE TEMPORARY TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);

sqlite> DROP TABLE IF EXISTS Testing;

sqlite> ALTER TABLE Names RENAME TO NamesOfFriends;
sqlite> ALTER TABLE NamesOfFriends ADD COLUMN Email TEXT;

SQLite表达式

1 2	SELECT quote(x'345eda2348587aeb'); BLOB literals are string literals containing hexadecimal data and preceded by a single 'x' or 'X' character.

操作符（Operator）

||
*    /    %
+    -
<<   >>   &    |
<    <=   >    >=
=    ==   !=   <>   IS  IS NOT  IN  LIKE  GLOB  BETWEEN  REGEXP
AND   
OR

一元、二元操作符：算术、布尔、关系、位

sqlite> SELECT * FROM Cars WHERE Name LIKE 'Vol%';
the percent sign (%) matches an arbitrary number of characters (including zero characters).
  
  PRAGMA case_sensitive_like = 1;

CASE WHEN ELSE表达式

1
2
3

sqlite> SELECT Val, CASE WHEN Val>0 THEN 'positive'
   ...> WHEN Val < 0 THEN 'negative'
   ...> ELSE 'zero' END FROM Numbers;

CURD

INSERT INTO Cars(Id, Name, Price) VALUES(1, 'Audi', 52642);
INSERT OR REPLACE INTO Cars VALUES(4, 'Volvo', 29000);
INSERT OR FAIL INTO Cars VALUES(4, 'Bentley', 350000);
INSERT OR IGNORE INTO Cars VALUES(4, 'Bentley', 350000);
INSERT INTO Cars2 SELECT * FROM Cars;

UPDATE Cars SET Name='Skoda Octavia' WHERE Id=3;

SELECT Name, Price FROM Cars LIMIT 4 OFFSET 2 ORDER BY Price DESC;
SELECT Name, Price AS 'Price of car' FROM Cars;
SELECT * FROM Cars ORDER BY Price, Name DESC;
SELECT * FROM Orders WHERE Id=6;

sqlite> SELECT Customer FROM Orders WHERE Customer LIKE 'B%';
Customer  
----------
Brown     
Black     
Brown  
  
SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%';

// The GROUP BY clause is used to combine database records with identical values into a single record. It is often used with the aggregate functions.
SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;
SELECT sum(OrderPrice) AS Total, Customer FROM Orders 
        GROUP BY Customer HAVING sum(OrderPrice)>1000;

约束

SQLite中有如下约束:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

sqlite> CREATE TABLE People(Id INTEGER, LastName TEXT NOT NULL,     ...> FirstName TEXT NOT NULL, City TEXT);
sqlite> CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE);
sqlite> CREATE TABLE Brands(Id INTEGER PRIMARY KEY, BrandName TEXT);
sqlite> CREATE TABLE Brands(Id INTEGER, BrandName TEXT, PRIMARY KEY(Id, BrandName));

外键（FOREIGN KEY）指向另一个表中的PRIMARY KEY。外键约束：

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER,      FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId));

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, 
    FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId) ON DELETE CASCADE);

注意：In SQLite, foreign keys are not enforced by default. To enforce the foreign key, the library must be compiled with proper flags, it must be at least version 3.6.19, and the pragma key for foreign keys must be set.

CHECK约束

1	CREATE TABLE Orders(Id INTEGER PRIMARY KEY, OrderPrice INTEGER CHECK(OrderPrice>0), Customer TEXT);

Default约束

1	sqlite> CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, Name TEXT, ...> City TEXT DEFAULT 'not available');

JOIN语句，将数据库中两个或多个表，进行合并，有两种JOIN：inner / outer。inner join最常见，会从数据库表中SELECT仅查询出那些有匹配值的记录。inner join也有三种，INNER JOIN、NATURAL INNER JOIN、CROSS INNER JOIN。

1	sqlite> SELECT Name, Day FROM Customers AS C JOIN Reservations ...> AS R ON C.CustomerId=R.CustomerId;

sqlite> SELECT * FROM Customers;
CustomerId  Name      
----------  ----------
1           Paul Novak
2           Terry Neil
3           Jack Fonda
4           Tom Willis
sqlite> SELECT * FROM Reservations;
Id          CustomerId  Day       
----------  ----------  ----------
1           1           2009-22-11
2           2           2009-28-11
3           2           2009-29-11
4           1           2009-29-11
5           3           2009-02-12
sqlite> SELECT Name, Day From Customers AS C JOIN Reservations AS R ON C.CustomerId = R.CustomerId;
Name        Day       
----------  ----------
Paul Novak  2009-22-11
Terry Neil  2009-28-11
Terry Neil  2009-29-11
Paul Novak  2009-29-11
Jack Fonda  2009-02-12

NATURAL INNER JOIN automatically uses all the matching column names for the join. In our tables, we have a column named CustomerId in both tables. 也就是自动使用两个表名称相匹配的列

CROSS INNER JOIN就是相乘，没什么实用意义

OUTER JOIN不要求每个记录在两张表中能匹配，也有三种：LEFT OUTER JOINS、RIGHT OUTER JOINS、FULL OUTER JOINS，SQLite只支持left

The LEFT OUTER JOIN returns all values from the left table, even if there is no match with the right table.也就是说LEFT JOIN会返回左边表的全部值，同时对右边表进行匹配，如果右边表的外键能匹配到左边，则相应有一条记录，

比如下例中，可以查询到所有客户的预定情况

sqlite> SELECT Name, Day From Customers LEFT JOIN Reservations ON Customers.CustomerId = Reservations.CustomerId;
等同于：
sqlite> SELECT Name, Day FROM Customers LEFT JOIN Reservations
   ...> USING (CustomerId);

Name        Day       
----------  ----------
Paul Novak  2009-22-11
Paul Novak  2009-29-11
Terry Neil  2009-28-11
Terry Neil  2009-29-11
Jack Fonda  2009-02-12
Tom Willis

SQLite核心函数

sqlite_version
random
abs、max、min
upper、lower
length
sqlite_compileoption_used
count: SELECT count(*) AS '# of cars' FROM Cars;
		SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders;
	count(*)包括空值的行，count(Id)则不包括
date('now')、datetime('now')

视图

A view is a specific look on data in from one or more tables. It can arrange data in some specific order or highlight or hide some data. A view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables, a view does not form part of the physical schema. It is a dynamic, virtual table computed or collated from data in the database.

1
2
3

sqlite> CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Price < 30000; sqlite> SELECT * FROM CheapCars; Name        ----------- Skoda       Volvo       Citroen     Volkswagen 
.tables中可以看到这个CheapCars视图（虚拟表
DROP VIEW

触发器

Triggers are database operations that are automatically performed when a specified database event occurs.

1	sqlite> CREATE TABLE Log(Id INTEGER PRIMARY KEY, OldName TEXT, ...> NewName TEXT, Date TEXT);

1	CREATE TRIGGER mytrigger UPDATE OF Name ON Friends BEGIN INSERT INTO Log(OldName, NewName, Date) VALUES (old.Name, [new.Name](http://new.Name), datetime('now')); END;

sqlite> SELECT name, tbl_name FROM sqlite_master WHERE type='trigger';
name        tbl_name  
----------  ----------
logtrigger  Friends   
sqlite> SELECT * FROM Friends;
Id          Name        Sex       
----------  ----------  ----------
1           Jane        F         
2           Thomas      M         
3           Franklin    M         
4           Elisabeth   F         
5           Mary        F         
6           Lucy        F         
7           Jack        M         
sqlite> UPDATE Friends SET Name='Frank' WHERE Id=3;
sqlite> SELECT * FROM Friends;
Id          Name        Sex       
----------  ----------  ----------
1           Jane        F         
2           Thomas      M         
3           Frank       M         
4           Elisabeth   F         
5           Mary        F         
6           Lucy        F         
7           Jack        M         
sqlite> SELECT * FROM Log;
Id          OldName     NewName     Date               
----------  ----------  ----------  -------------------
1           Franklin    Frank       2021-04-15 08:01:23

事务

A transaction begins with BEGIN TRANSACTION and ends with COMMIT.

In SQLite, any command other than SELECT starts an implicit transaction.

BEGIN TRANSACTION;
CREATE TABLE Test(Id INTEGER NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
COMMIT;

第4个insert会失败，但前面3个会成功

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Test(Id INTEGER NOT NULL);
INSERT INTO Test VALUES(11);
INSERT INTO Test VALUES(12);
INSERT INTO Test VALUES(13);
INSERT INTO Test VALUES(NULL);
ROLLBACK;

要么全部成功，要么回滚

updated at 2019-08-12