SQLite 简介 及 使用

SQLite

SQLite 是遵守 ACID 的关系数据库管理系统，它包含在一个相对小的C程序库中。与许多其它数据库管理系统不同，SQLite 不是一个客户端/服务器结构的数据库引擎，而是被集成在用户程序中。

SQLite 遵守 ACID，实现了大多数 SQL 标准。它使用动态的、弱类型的SQL语法。它作为嵌入式数据库，是应用程序，如网页浏览器，在本地/客户端存储数据的常见选择。它可能是最广泛部署的数据库引擎，因为它正在被一些流行的浏览器、操作系统、嵌入式系统所使用。同时，它有许多程序设计语言的语言绑定。

SQLite 语句

调整打印显示效果

sqlite> .mode column
sqlite> .header on

检索单个列

select prod_name from products;

检索多个列

select prod_id, prod_name, prod_price from products;

检索所有列

select * from products;

检索不同的值

select vend_id from products;
select DISTINCT vend_id from products;

限制结果

SELECT prod_name FROM Products LIMIT 5;
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

排序数据

SELECT prod_name FROM Products ORDER BY prod_name;

在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。如果它不是最后的子句，将会出现错误消息。

通常，ORDER BY 子句中使用的列将是为显示而选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

按多个列排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;

按列位置排序

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;

指定排序方向

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

WHERE 子句过滤数据

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;
SELECT prod_name, prod_price FROM Products WHERE prod_price <= 10;
SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01'; --不匹配
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10; --范围
SELECT prod_name FROM Products WHERE prod_price IS NULL; --空值

组合 WHERE 子句

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

SQL 在处理 OR 操作符前，优先处理 AND 操作符，如果需要改变计算顺序，使用括号来改变顺序。

SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;

SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

LIKE 操作符

百分号（%）通配符

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%'; --检索任意以 Fish 起头的词

通配符 % 看起来像是可以匹配任何东西，但有个例外，这就是 NULL。子句 WHERE prod_name LIKE '%' 不会匹配产品名称为 NULL 的行。

下划线通配符

下划线的用途与 % 通配符一样，但它只匹配单个字符，而不是多个字符。

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';

方括号 [] 通配符

SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
SELECT cust_contact FROM Customers WHERE NOT cust_contact LIKE '[JM]%' ORDER BY cust_contact;

拼接字段

SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' FROM Vendors ORDER BY vend_name;

别名

SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title FROM Vendors ORDER BY vend_name;

执行算术计算

SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_num = 20008;
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

文本处理函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;
复

• LEFT()（或使用子字符串函数）返回字符串左边的字符
• RIGHT()（或使用子字符串函数）返回字符串右边的字符
• LENGTH()（也使用 DATALENGTH() 或 LEN()）返回字符串的长度
• UPPER()（Access 使用 UCASE()）将字符串转换为大写
• LOWER()（Access 使用 LCASE()）将字符串转换为小写
• LTRIM() 去掉字符串左边的空格
• RTRIM() 去掉字符串右边的空格
• SOUNDEX() 返回字符串的 SOUNDEX 值

日期和时间处理函数

SELECT order_num FROM Orders WHERE strftime('%Y', order_date) = '2012';
复制代码

数值处理函数

• ABS() 返回一个数的绝对值
• COS() 返回一个角度的余弦
• EXP() 返回一个数的指数值
• PI() 返回圆周率
• SIN() 返回一个角度的正弦
• SQRT() 返回一个数的平方根
• TAN() 返回一个角度的正切

聚集函数示例

SELECT AVG(prod_price) AS avg_price FROM Products;
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
复制代码

SELECT count(vend_id) AS avg_price FROM Products WHERE vend_id = 'DLL01';
SELECT MAX(prod_price) AS max_price FROM Products;
SELECT MIN(prod_price) AS min_price FROM Products;
复制代码

SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
复制代码

• AVG() 返回某列的平均值
• COUNT() 返回某列的行数
• MAX() 返回某列的最大值
• MIN() 返回某列的最小值
• SUM() 返回某列值之和

聚集不同值

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
复制代码

组合聚集函数

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price)