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)