sqlite学习笔记
内容主要来自: https://www.sqlite.net.cn/tutorial/2.html
目录
1. SQLite入门
1.1 开始
在官网(https://www.sqlite.org/download.html)的Windows 区下载预编译的二进制文件(Precompiled Binaries for Windows):
sqlite-dll-win32-x86-3380500.zip或sqlite-dll-win64-x64-3380500.zip
sqlite-tools-win32-x86-3380500.zip
两个都要下载.
把下载的两个.zip文件解压出来都放到文件夹: D:\sqlite,
得到5个文件:
D:\sqlite>
sqldiff.exe
sqlite3.def
sqlite3.dll
sqlite3.exe
sqlite3_analyzer.exe
把D:\sqlite\添加到环境变量PATH中.
打开cmd窗口, 输入命令 sqlite3, 即可进入sqlite3的环境.
具体操作命令见第3章.
D: \xx\xx> sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite>
1.2 点命令
使用.help可以获取点命令的清单
| 命令 | 选项 | 描述 |
|---|---|---|
| .backup | ?DB? FILE | 备份 DB 数据库(默认是 "main")到 FILE 文件. |
| .bail | ON|OFF | 发生错误后停止. 默认为 OFF. |
| .databases | 列出数据库的名称及其所依附的文件. | |
| .dump | ?TABLE? | 以 SQL 文本格式转储数据库. 如果指定了 TABLE 表, 则只转储匹配 LIKE 模式的 TABLE 表. |
| .echo | ON|OFF | 开启或关闭 echo 命令. |
| .exit | 退出 SQLite 提示符. | |
| .explain | ON|OFF | 开启或关闭适合于 EXPLAIN 的输出模式. 如果没有带参数, 则为 EXPLAIN on, 即开启 EXPLAIN. |
| .header(s) | ON|OFF | 开启或关闭头部显示. |
| .help | 显示消息. | |
| .import | FILE TABLE | 导入来自 FILE 文件的数据到 TABLE 表中. |
| .indices | ?TABLE? | 显示所有索引的名称. 如果指定了 TABLE 表, 则只显示匹配 LIKE 模式的 TABLE 表的索引. |
| .load | FILE ?ENTRY? | 加载一个扩展库. |
| .log | FILE|off | 开启或关闭日志. FILE 文件可以是 stderr(标准错误)/stdout(标准输出). |
| .mode | MODE | 设置输出模式, MODE 可以是下列之一:
|
| .nullvalue | STRING | 在 NULL 值的地方输出 STRING 字符串. |
| .output | FILENAME | 发送输出到 FILENAME 文件. |
| .output | stdout | 发送输出到屏幕. |
| STRING... | 逐字地输出 STRING 字符串. | |
| .prompt | MAIN CONTINUE | 替换标准提示符. |
| .quit | 退出 SQLite 提示符. | |
| .read | FILENAME | 执行 FILENAME 文件中的 SQL. |
| .schema | ?TABLE? | 显示 CREATE 语句. 如果指定了 TABLE 表, 则只显示匹配 LIKE 模式的 TABLE 表. |
| .separator | STRING | 改变输出模式和 .import 所使用的分隔符. |
| .show | 显示各种设置的当前值. | |
| .stats | ON|OFF | 开启或关闭统计. |
| .tables | ?PATTERN? | 列出匹配 LIKE 模式的表的名称. |
| .timeout | MS | 尝试打开锁定的表 MS 毫秒. |
| .width | NUM NUM | 为 "column" 模式设置列宽度. |
| .timer | ON|OFF | 开启或关闭 CPU 定时器. |
sqlite> .databases
main: E:\myfile\python\pycharm\test\sqlite3.db
sqlite>
sqlite> .dump book
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE book(
id int primary key,
sort int,
name text,
price real,
category int,
foreign key (category) references category(id) on update cascade on delete cascade
);
INSERT INTO book VALUES(2,3,'Python Intro',17.5,2);
COMMIT;
sqlite>
1.3 格式化输出
sqlite>.header on --显示表头
sqlite>.mode column --输出格式为左对齐的列
sqlite>.timer on --开始cpu定时器
sqlite>
1.4 语法规则
| 语法 | 说明 |
|---|---|
| 大小写 | SQLite不区分大小写, 但个别命令要区分, 比如GLOB和glob是不相同的. |
| 注释 |
|
| 语句 | 以关键字开始, 以分号结束, 常用关键字
|
2. 数据类型
2.1 常用类型
SQLite中, 每一列、每个变量、每个表达式都有相关的数据类型.
可以在创建表时使用这些数据类型.
| 存储类 | 描述 |
|---|---|
| NULL | 值是一个 NULL 值. |
| INTEGER | 值是一个带符号的整数, 根据值的大小存储在 1、2、3、4、6 或 8 字节中. |
| REAL | 值是一个浮点值, 存储为 8 字节的 IEEE 浮点数字. |
| TEXT | 值是一个文本字符串, 使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储. |
| BLOB | 值是一个 blob 数据, 完全根据它的输入存储. |
2.2 亲和(Affinity)类型
什么是亲和性:
SQLite不强制数据类型约束, 任何数据类型可以插入任何列.
比如, 可以向整型列中插入任意长度的字符串, 可以向bool型列中插入浮点数.
在create table时, 虽然对每列指定了数据类型, 但实际插入数据时并不会限制.
任何列均可接受任意长度的字符串(有一个例外: INTEGER PRIMARY KEY的列只能存储64位整数).
当向整型列中插入字符串时, SQLite会试图将该字串转换成一个整数, 如果转换成功则插入该整数, 如果转换失败, 则插入字符串.
这种特性被称为类型或列亲和性(type or column affinity).
SQLite有5种数据类型:
| 亲和类型 | 描述 |
|---|---|
| TEXT | 数值型数据在被插入之前, 需要先被转换为文本格式, 之后再插入到目标字段中. |
| NUMERIC | |
| INTEGER | 对于亲缘类型为INTEGER的字段, 其规则等同于NUMERIC, 唯一差别是在执行CAST表达式时. |
| REAL | 其规则基本等同于NUMERIC, 唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式. |
| NONE | 不做任何的转换, 直接以该数据所属的数据类型进行存储. |
2.3 亲和类型及类型名称
2.4 boolean数据类型
SQLite没有单独的 Boolean 存储类. 相反, 布尔值被存储为整数 0(false)和 1(true).
2.5 Date与Time数据类型
SQLite 没有一个单独的用于存储日期和/或时间的存储类, 但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值.
3 操作数据库
3.1 创建
如下命令创建了一个数据库文件test.db.
PS E:\myfile\python\pycharm\test> sqlite3.exe test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>
sqlite> .databases
main: E:\myfile\python\pycharm\test\test.db
--也可以使用.open命令:
-- 1) 如果test1.db存在, 就直接打开它.
-- 2) 如果test1.db不存在, 就创建它.
sqlite> .open test1.db
3.2 导出
使用.dump命令, 把数据库导出到文本文件中.
PS E:\myfile\python\pycharm\test> sqlite3 test.db .dump > test.db.sql
PS E:\myfile\python\pycharm\test> cat test.db.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
PS E:\myfile\python\pycharm\test>
还可以从.sql文件中恢复数据库
--注, 在window下, 直接用上一步导出的test.db.sql会报错,
-- 而使用手工书写的test.sql没有问题.
sqlite3 test.db < test.sql
3.3 附加
假设这样一种情况, 当在同一时间有多个数据库可用, 您想使用其中的任何一个.
SQLite 的 ATTACH DATABASE 语句是用来选择一个特定的数据库, 并把数据库文件与一个名称连接起来.
使用该命令后, 所有的 SQLite 语句将在附加的数据库下执行.
PS E:\myfile\python\pycharm\test> sqlite3 sqlite3.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>
sqlite> .database
main: E:\myfile\python\pycharm\test\sqlite3.db
sqlite>
sqlite> attach database 'test.db' as 'test'; --添加一个现有的数据库test.db, 数据库名叫test.
sqlite>
sqlite> .database
main: E:\myfile\python\pycharm\test\sqlite3.db
test: E:\myfile\python\pycharm\test\test.db
sqlite>
注意, main和temp这两个名称是保留的, 所以不能被attach
sqlite> attach database 'test.db' as 'temp';
Error: database temp is already in use
sqlite>
sqlite> attach database 'test.db' as 'main';
Error: database main is already in use
sqlite>
3.4 分离
与attach相反, detach命令把数据库文件与名称的关联进行分离.
如果同一个数据库文件被附加上多个别名, detach只断开当前名称的连接, 其余名称连接仍然有效.
注意: 无法分离main和temp.
注意: 如果是内存中的数据库或临时数据库, detach后对应数据库将会被摧毁, 内容将会丢失.
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db
3 currentDB /home/sqlite/testDB.db
sqlite>
sqlite> DETACH DATABASE 'currentDB';
sqlite> .databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db
4 操作表
4.1 列出表名称.table
语法:
1. .table
2. select tbl_name from sqlite_master where type='table'; --见4.4查询表
4.2 创建表(CREATE)
语法:
CREATE TABLE database_name.table_name(
column0 data_type PRIMARY KEY(one or more columns),
column1 data_type,
column2 data_type,
...
columnN data_type,
);
例:
PS E:\myfile\python\pycharm\test> sqlite3 test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>
sqlite> CREATE TABLE company( --create a table company
...> id INT PRIMARY KEY NOT NULL,
...> name TEXT NOT NULL,
...> age INT NOT NULL,
...> address CHAR(50),
...> salary REAL
...> );
sqlite> CREATE TABLE department( --create a table department
...> id INT PRIMARY KEY NOT NULL,
...> dept CHAR(50) NOT NULL,
...> emp_id INT NOT NULL
...> );
sqlite>
sqlite> .tables --列出附加数据库中的所有表.
company department
sqlite>
sqlite> .schema company --得到表的完整信息
CREATE TABLE company(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50),
salary REAL
);
sqlite>
4.3 删除表(DROP)
语法:
DROP TABLE database_name.table_name;
例:
sqlite> .tables
company department
sqlite>
sqlite> DROP TABLE company;
sqlite>
sqlite> .tables
department
sqlite>
4.4 查询表(SELECT)
获取库中所有表的名称:
sqlite> --注意, tbl_name和sqlite_master都是固定名称, 不能随便取
sqlite> select tbl_name from sqlite_master where type='table';
tbl_name
---------------
department
company
Run Time: real 0.007 user 0.000000 sys 0.000000
sqlite>
获取表的完整信息
sqlite> select sql from sqlite_master where type='table' and tbl_name='company';
CREATE TABLE company(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50),
salary REAL
)
sqlite>
5 操作表中的记录
5.1 增(INSERT)
语法:
INSERT INTO table_name VALUES(value0, value1, value2, …); --为所有列添加值
INSERT INTO table_name (colx, coly, cloz, …) VALUES(valuex, valuey, valuez, …); --为指定列添加值
例:
sqlite> CREATE TABLE company(
...> id INT PRIMARY KEY NOT NULL,
...> name TEXT NOT NULL,
...> age INT NOT NULL,
...> address CHAR(50),
...> salary REAL
...> );
sqlite>
sqlite> INSERT INTO company(id, name, age, address, salary) VALUES(1, 'Paul' , 32, 'California', 20000.0);
sqlite> INSERT INTO company(id, name, age, address, salary) VALUES(2, 'Allen', 25, 'Texas' , 15000.0);
sqlite> INSERT INTO company(id, name, age, address, salary) VALUES(3, 'Teddy', 23, 'Norway' , 20000.0);
sqlite> INSERT INTO company(id, name, age, address, salary) VALUES(4, 'Mark' , 25, 'Rich-mond' , 65000.0);
sqlite> INSERT INTO company(id, name, age, address, salary) VALUES(5, 'David', 27, 'Texas' , 85000.0);
sqlite> INSERT INTO company(id, name, age, address, salary) VALUES(6, 'Kim' , 22, 'South-Hall', 45000.0);
sqlite> INSERT INTO company VALUES(7, 'James', 24, 'Houston' , 10000.0);
sqlite>
也可以使用另一个表的数据来填充表格
语法:
INSERT INTO first_table_name [(colx, coly, colz, ...)]
SELECT colo, colp, colq FROM second_table_name
[WHERE condition];
5.2 删(DELETE)
用于删除表中已经有的记录
语法:
DELETE FROM table-name
[WHERE condition];
只删除WHERE子句匹配的记录.
如果没有WHERE子句, 则删除所有记录.
sqlite> select * from company;
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 Texas 1.0
2 Allen 25 Texas 1.0
3 Teddy 23 Texas 1.0
4 Mark 25 Texas 1.0
5 David 27 Texas 1.0
6 Kim 22 Texas 1.0
7 James 24 Texas 1.0
sqlite> delete from company where id=6; --删除id=6的记录
sqlite> select * from company;
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 Texas 1.0
2 Allen 25 Texas 1.0
3 Teddy 23 Texas 1.0
4 Mark 25 Texas 1.0
5 David 27 Texas 1.0
7 James 24 Texas 1.0
sqlite>
sqlite> delete from company; --删除所有记录
sqlite> select * from company;
sqlite>
5.3 改(UPDATE)
语法:
UPDATE table-name
SET colA=valueA, colB=valueB, …
[WHERE condition]
只对WHERE匹配到的条目进行修改.
如果不指定WHERE子句, 则会修改所有的记录
sqlite> select * from 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
sqlite>
sqlite> update company set address='Texas' where id=6;
sqlite> select * from 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 Texas 45000.0
7 James 24 Houston 10000.0
对所有条目修改addr和salary.
sqlite> update company set address='Texas', salary=1.0;
sqlite> select * from company;
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 Texas 1.0
2 Allen 25 Texas 1.0
3 Teddy 23 Texas 1.0
4 Mark 25 Texas 1.0
5 David 27 Texas 1.0
6 Kim 22 Texas 1.0
7 James 24 Texas 1.0
sqlite>
5.4 查(SELECT)
语法:
SELECT col1, col2, … FROM table_name;
SELECT * FROM table_name;
例:
sqlite> --设置显示格式
sqlite> .header on
sqlite> .mode column
sqlite> .timer on
sqlite>
sqlite> --获取company内的所有字段
sqlite> SELECT * FROM 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
Run Time: real 0.009 user 0.000000 sys 0.000000
sqlite>
sqlite> --获取部分字段
sqlite> SELECT name, salary FROM company;
name salary
---------- ----------
Paul 20000.0
Allen 15000.0
Teddy 20000.0
Mark 65000.0
David 85000.0
Kim 45000.0
James 10000.0
Run Time: real 0.016 user 0.000000 sys 0.000000
sqlite>
sqlite> --设置列宽度, 单位为字符数
sqlite> .width 2, 10 --第一列2个字符(会导致显示不全), 第二列10个字符.
sqlite> SELECT name, salary FROM company;
na salary
-- ----------
Pa 20000.0
Al 15000.0
Te 20000.0
Ma 65000.0
Da 85000.0
Ki 45000.0
Ja 10000.0
Run Time: real 0.016 user 0.000000 sys 0.000000
sqlite>
子查询+外查询
sqlite> --查询salary>60000的age, 有两个, 25, 27
sqlite> select age from company where salary>60000;
age
----------
25
27
sqlite> --子查询:salary>60000的age, 返回25和27, 外查询:age大于25的条目.
sqlite> select * from company where age>(select age from company where salary>60000);
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
5 David 27 Texas 85000.0
sqlite>
6 运算符与表达式
6.1 运算符
在WHERE语句中执行操作.
用于指定语句中的条件、连接多个条件.
算术运算符、比较运算符、逻辑运算符、位运算符.
6.1.1 算术运算符
| 算术运算符 | 描述 |
|---|---|
| + | 加 |
| - | 减 |
| * | 乘 |
| / | 除 |
| % | 模 |
例:
sqlite> .mode line --每行一个值
sqlite>
sqlite> select 12 + 5;
12 + 5 = 17
sqlite>
sqlite> select 12 - 5;
12 - 5 = 7
sqlite>
sqlite> select 12 * 5;
12 * 5 = 60
sqlite>
sqlite> select 12 / 5; --整数相除得整数
12 / 5 = 2
sqlite>
sqlite> select 12.0 / 5; --real相除得real
12.0 / 5 = 2.4
sqlite>
sqlite> select 12 % 5;
12 % 5 = 2
sqlite>
6.1.2 比较运算符
| 比较运算符 | 描述 |
|---|---|
| == | 相等 |
| = | 相等, 与==同 |
| != | 不相等 |
| <> | 不相等, 与!=同 |
| >、>= | 大于、大于等于 |
| <、<= | 小于、小于等于 |
| !> | 不大于 |
| !< | 不小于 |
例:
sqlite> .mode column
sqlite> .headers on
sqlite> select * from 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
sqlite>
sqlite> select * from company where salary>50000; --大于
id name age address salary
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-mond 65000.0
5 David 27 Texas 85000.0
sqlite>
sqlite> select * from company where salary=20000; --等于
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
sqlite>
sqlite> select * from company where salary!=20000; --不等于
id name age address salary
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.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
sqlite>
sqlite> select * from company where salary<>20000; --不等于
id name age address salary
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.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
sqlite>
6.1.3. 逻辑运算符
| 逻辑运算符 | 描述 |
|---|---|
| AND | 两个表达式都为真, 则返回true |
| BETWEEN | 操作数在指定范围内, 则返回true |
| EXISTS | 子查询有结果集, 则返回true |
| IN | 操作数等于列表中的值之一, 则返回true |
| NOT IN | 操作数不等于列表中的所有值, 则返回true |
| LIKE | 操作数与模式匹配, 则返回true |
| GLOB | |
| NOT | 反转其它布尔运算符的结果 |
| OR | 任一表达式为真, 则返回true |
| IS NULL | 把操作数与NULL值比较 |
| IS | 与=相似 |
| IS NOT | 与!=相似 |
| || | 连接两个字符串为一个新字符串 |
| UNIQUE | 搜索表中每一行, 如果唯一, 则返回true |
sqlite> select * from 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
sqlite>
6.1.3.1 AND
例
sqlite> --使用and
sqlite> select * from company where age>=25 and salary>=65000;
id name age address salary
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-mond 65000.0
5 David 27 Texas 85000.0
sqlite>
6.1.3.2 OR
sqlite> --使用or
sqlite> select * from company where age>=25 or salary>=65000;
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-mond 65000.0
5 David 27 Texas 85000.0
sqlite>
6.1.3.3 IS, IS NOT
例:
sqlite> --使用is
sqlite> select * from company where age is null;
sqlite>
sqlite> --使用is not
sqlite> select * from company where age is not null;
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
sqlite>
6.1.3.4 LIKE
作用:用于匹配指定模式的文本值, 如果匹配则返回1.
通配符:
| 通配符 | 匹配的数目 | 匹配的内容 |
|---|---|---|
| %(百分号) | 0个、1个、多个 | 数字或字符 |
| _(下划线) | 1个 | 数字或字符 |
例:
| 语句 | 说明 |
|---|---|
| where salary like ‘200%’ | 以200开头的值 |
| where salary like ‘%200%’ | 在任意位置包含200的值 |
| where salary like ‘_00%’ | 第二第三位为00的值 |
| where salary like ‘2_%_%’ | 以2开头且长度至少为3的值 |
| where salary like ‘%2’ | 以2结尾的值 |
| where salary like ‘_2%3’ | 第二位为2, 最后一位为3的值 |
| where salary like ‘2___3’ | 第二位为2, 最后一位为3, 长度为5的值 |
sqlite> --使用like, 以Ki开始的字符
sqlite> select * from company where name like 'Ki%';
id name age address salary
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
sqlite>
6.1.3.5 GLOB
作用:用于匹配指定模式的文本值, 如果匹配则返回1.
glob与like区别:
- glob的pattern对大小写敏感;
- 通配符不同
通配符:
| 通配符 | 匹配的数目 | 匹配的内容 |
|---|---|---|
| *(星号) | 0个、1个、多个 | 数字或字符 |
| ?(问号) | 1个 | 数字或字符 |
例
sqlite> select * from 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
sqlite> --匹配Pa*的记录
sqlite> select * from company where name glob 'Pa*';
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
sqlite> --匹配PA*的记录, 大小写敏感, 所以无返回
sqlite> select * from company where name glob 'PA*';
sqlite> --匹配Pa的记录, 这个patten要求name长度为2, 所以无返回
sqlite> select * from company where name glob 'Pa';
sqlite> --使用like, 匹配时忽略大小写, 所以‘PA%’可以匹配Paul
sqlite> select * from company where name like 'PA%';
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
sqlite>
6.1.3.6 IN, NOT IN
sqlite> --使用in, 注意, 只包含23和27, 不是23到27之间的值.
sqlite> select * from company where age in (23, 27);
id name age address salary
---------- ---------- ---------- ---------- ----------
3 Teddy 23 Norway 20000.0
5 David 27 Texas 85000.0
sqlite>
sqlite> --使用not in, 与in相反
sqlite> select * from company where age not in (23, 27);
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-mond 65000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
sqlite>
6.1.3.7 BETWEEN a AND b
sqlite> --使用between A and B, 界于23和27之间的值, 包括边界
sqlite> select * from company where age between 23 and 27;
id name age address salary
---------- ---------- ---------- ---------- ----------
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
7 James 24 Houston 10000.0
sqlite>
6.1.3.8 EXISTS
exists操作符, 只返回真/假, 下例的select id, name, salary还是从总库中查找数据, 而不是在exists的结果中查找.
sqlite> select id, name, salary from company where exists(select age from company where salary>65000);
id name salary
---------- ---------- ----------
1 Paul 20000.0
2 Allen 15000.0
3 Teddy 20000.0
4 Mark 65000.0
5 David 85000.0
6 Kim 45000.0
7 James 10000.0
sqlite>
6.1.4 位运算符
按位与:&
按位或:|
按位非:~
左移:<<
右移:>>
| 式子 | 值 | DEC | HEX |
|---|---|---|---|
| A | 0011 1100 | 60 | 0x3C |
| B | 0000 1101 | 13 | 0x0D |
| A&B | 0000 1100 | 12 | 0x0C |
| A|B | 0011 1101 | 61 | 0x3D |
| ~A | 1100 0011 | 195->-61 | 0xC3 |
| A>>2 | 0000 1111 | 15 | 0x0F |
| A<<2 | 1111 0000 | 240 | 0xF0 |
sqlite> .mode line
sqlite> select 60&13;
60&13 = 12
sqlite> select 60|13;
60|13 = 61
sqlite> select ~60;
~60 = -61
sqlite> select 60>>2;
60>>2 = 15
sqlite> select 60<<2;
60<<2 = 240
sqlite>
十进制数60, 按位取反, 得到-61, 过程如下:
1. 十进制 : 60 对应 二进制8'b0011_1100.
2. 按位取反: ~60 对应 二进制8'b1100_0011, 最高位为1, 负数, 计算机认为负数应该是按补码存储的, 需要推出它的原码, 才能知道是什么值.
3. 得到补码: 对应 补码 8'b1100_0011,
3. 计算反码: 对应 反码 8'b1100_0010, 补码=反码+1, 反码=补码-1.
4. 计算原码: 对应 原码 8'b1011_1101, 最高位是符号位, 不取反, 其余位取反.
5. 十进制 : 0b(1011_1101)=0x(-3D)=-61, 最高位是符号位, 最终得到~60 = -61.
6.2 表达式
语法:
SELECT colA, colB, …
FROM table_name
[WHERE CONDITION | EXPRESSION]
6.2.1 布尔表达式
例:
SELECT * FROM company WHERE salary=10000;
salary=10000就是一个布尔表达式, 它通过匹配单个值获取数据.
6.2.2 数值表达式
用来执行查询中的任何数学运算.
可以使用内置函数:avg()、sum()、count()等
例:调用count函数, 计算表中内容的条数
sqlite> select count(*) as "item_cnt" from company;
item_cnt = 7 -- company中共7条内容
sqlite>
6.2.3 日期表达式
sqlite> select current_timestamp;
current_timestamp = 2020-05-12 07:30:23
sqlite>
7 子句
7.1 WHERE子句
作用:
where子句用于指定(从一个表或多个表中)获取数据的条件.
可以使用where子句来过滤记录, 只获取需要的记录.
where子句可以用在SELECT、UPDATE、DELETE语句中.
语法:
SELECT column-list
FROM table_name
[WHERE condition];
例
sqlite> select * from company where age>=25 and salary>=65000;
id name age address salary
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-mond 65000.0
5 David 27 Texas 85000.0
sqlite>
sqlite> select * from company where name glob 'Ki*';
id name age address salary
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
sqlite>
7.2 limit子句
作用:用于限制select语句返回的数据数量
语法:
SELECT column-list
FROM table-name
LIMIT [row cnt] [OFFSET row-num];
例:
sqlite> select * from 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
sqlite> select * from company limit 3; --限制只提取3条记录
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
sqlite> select * from company limit 3 offset 2; --限制跳过两条记录后, 提取3条
id name age address salary
---------- ---------- ---------- ---------- ----------
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-mond 65000.0
5 David 27 Texas 85000.0
sqlite>
7.3 order by子句
语法:
SELECT column-list
FROM table-name
[WHERE condition]
ORDER BY colA [ASC | DESC], colB [ASC | DESC], … ;
例:
全体数据:
sqlite> select * from 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
按age排序, 默认升序.
sqlite> select * from company order by age;
id name age address salary
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
3 Teddy 23 Norway 20000.0
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-mond 65000.0
5 David 27 Texas 85000.0
1 Paul 32 California 20000.0
按age排序, 降序
sqlite> select * from company order by age desc;
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
5 David 27 Texas 85000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-mond 65000.0
7 James 24 Houston 10000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
sqlite>
先按age降序排序, 如果age相同, 再按salary降序排序
sqlite> select * from company order by age desc, salary desc;
id name age address salary
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
5 David 27 Texas 85000.0
4 Mark 25 Rich-mond 65000.0
2 Allen 25 Texas 15000.0
7 James 24 Houston 10000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
sqlite>
7.4 group by子句
作用:
用在SELECT语句中, 对相同的数据进行分组.
SELECT语句中, 以WHERE/GROUP BY/ORDER BY的顺序出现.
语法:
SELECT column-list
FROM table-name
WHERE condition
GROUP BY colA, colB, …
ORDER BY colX, colY, …
例:
全体数据, 其中有几个名字相同的:James, Paul
sqlite> select id,name,salary from company order by name;
id name salary
---------- ---------- ----------
2 Allen 15000.0
5 David 85000.0
7 James 10000.0
9 James 5000.0
10 James 5000.0
6 Kim 45000.0
4 Mark 65000.0
1 Paul 20000.0
8 Paul 20000.0
3 Teddy 20000.0
把name相同人的salary进行求和:
其中三个James的记录合并为一条, 总工资为20000.0, 两条Paul记录合并为一条, 总工资为40000.0
sqlite> select id,name,sum(salary) from company group by name order by name;
id name sum(salary)
---------- ---------- -----------
2 Allen 15000.0
5 David 85000.0
7 James 20000.0
6 Kim 45000.0
4 Mark 65000.0
1 Paul 40000.0
3 Teddy 20000.0
sqlite>
7.5 having子句
作用:允许指定条件来过滤将出现在最终结果中的分组结果.
与WHERE语句的关系:
WHERE子句:设置的条件作用到所选列上;
HAVING子句:设置的条件作用到(由GROUP BY子句创建的)分组上;
语法:HAVING子句必须放在GROUP BY与ORDER BY子句之间.
SELECT column-list
FROM table-name
WHERE conditions
GROUP BY colA, colB, …
HAVING conditions
ORDER BY colX, colY, …;
例:
全体记录
sqlite> select id,name,salary from company order by name;
id name salary
---------- ---------- ----------
2 Allen 15000.0
5 David 85000.0
7 James 10000.0
9 James 5000.0
10 James 5000.0
6 Kim 45000.0
4 Mark 65000.0
1 Paul 20000.0
8 Paul 20000.0
3 Teddy 20000.0
选择name计数>=2的记录:
sqlite> select id, name, salary from company group by name having count(name)>=2;
id name salary
---------- ---------- ----------
7 James 10000.0
1 Paul 20000.0
sqlite>
7.6 distinct关键字
作用:与SELECT语句一起使用, 用于消除所有重复记录, 并只获取唯一一次记录.
语法:
SELECT DISTINCT column-list
FROM table-name
WHERE condition;
例:
全体数据:
sqlite> select id,name, salary from company order by name;
id name salary
---------- ---------- ----------
2 Allen 15000.0
5 David 85000.0
7 James 10000.0
9 James 5000.0
10 James 5000.0
6 Kim 45000.0
4 Mark 65000.0
1 Paul 20000.0
8 Paul 20000.0
3 Teddy 20000.0
sqlite>
去除name重复的记录, 返回没有任何重复的条目.
sqlite> select distinct name from company order by name;
name
----------
Allen
David
James
Kim
Mark
Paul
Teddy
sqlite>
8. 进阶教程
8.1 PRAGMA
作用:pragma是一个特殊的命令, 用于在SQLite环境内部控制"环境变量"和"状态标志".
语法:
读取:PRAGMA pragma-name;
设置:PRAGMA pragma-name=value;
| 变量 | 作用 | - |
|---|---|---|
| auto_vacuum | 控制数据库文件大小是否自动缩小 | - |
| cache_size | - | - |
| case_sesitive_like | - | - |
| count_changes | - | - |
| database_list | - | - |
| encoding | - | - |
| foreign_keys | 外键 | OFF, ON |
| freelist_count | - | - |
| index_info | - | - |
| index_list | - | - |
| journal_mode | - | - |
| max_page_count | - | - |
| page_count | - | - |
| page_size | - | - |
| parser_trace | - | - |
| recursive_triggers | - | - |
| schema_version | - | - |
| secure_delete | - | - |
| sql_trace | - | - |
| synchronous | - | - |
| temp_store_directory | - | - |
| user_version | - | - |
| writable_schema | - | - |
8.2 约束
定义:在表的数据列上强制执行的规则. 用来限制插入到表中的数据类型. 保证数据的准确性和可靠性.
约束可以是列级或表级. 列级约束仅适用于列, 表级约束应用到整个表.
常用的约束如下:
| 约束 | 说明 |
|---|---|
| NOT NULL | 该列不能有NULL值 |
| DEFAULT | 该列没指定值时, 为该列提供默认值 |
| UNIQUE | 该列所值都是不同的 |
| PRIMARY KEY | 唯一标识数据库表中的各记录 |
| CHECK | 确保某列中所有值满足一定条件 |
8.2.1 not null
默认情况:列可以保存null值, 使用not null约束后, 该列不允许null值.
null不同于没有数据, null代表未知的数据.
create table compary(
id int primary key not null
);
8.2.2 default约束
如果在insert into语句中没有提供特定值, default约束为该列提供一个默认值
create table compary(
id int primary key not null,
salary real default 50000.0 --指定默认值
);
8.2.3 unique约束
防止在一个特定的列存在相同的值.
如果某列在其它表中被设置为foreign key, 则该列必须设置为primary key或者unique, 见9.1 foreign key.
create table compary(
id int primary key not null,
name text not null unique, --设置唯一性
salary real default 50000.0
);
8.2.4 primary key
primary key唯一标识数据库表中的每个记录.
一个表可以有多个unique列, 但只能有一个primary key.
primary key是唯一的ID.
可以使用primary key来引用表中的行.
可以把primary key设置为其它表的外键, 以此来创建表之间的关系.
SQLite中primary key可以是null(自动递增autoincrement?).
create table compary(
id int primary key autoincrement, --id设置为primary key, 如果insert值为null时, 则自动递增.
name text not null unique,
salary real default 50000.0
);
8.2.5 check约束
check约束检查值的条件, 如果条件值为false, 则记录违反约束, 不能输入到表.
create table company(
id integer primary key autoincrement, --int类型不能AUTOINCREMENT, AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY.
salary real CHECK(salary>0)
);
insert into company values(null, 0); --报错, Error: CHECK constraint failed: company
8.2.6 删除约束
SQLite支持ALTER TABLE的有限子集(见8.7节).
在SQLite中, ALTER TABLE命令允许用户重命名表, 或向现有表中添加一个新的列.
但以下操作不被允许: 重命名列, 删除一列, 给一个表添加约束, 给一个表删除约束.
8.3 Join
Join子句用于结合两个或多个表的记录.
Join通过共同值来结合表中的字段.
三种主要类型的连接:Cross join(交叉连接)/Inner join(内连接)/Outer join(外连接)
其中inner join是默认的连接方式;
以如下表进行说明:
表定义
create table grade(
gradeid text not null primary key,
name text not null
);
create table class(
classid text not null primary key,
name text not null,
gradeid text references grade(gradeid) ON UPDATE CASCADE
);
表赋值:
--grade 1, 2
INSERT INTO grade VALUES("1", "grade_1");
INSERT INTO grade VALUES("2", "grade_2");
--class 1-1, 1-2, 2-1, 2-2
INSERT INTO class VALUES("1_1", "class_1_1", "1");
INSERT INTO class VALUES("1_2", "class_1_2", "1");
INSERT INTO class VALUES("2_1", "class_2_1", "2");
INSERT INTO class VALUES("2_2", "class_2_2", "2");
查看表:
sqlite> select * from grade;
gradeid name
---------- ----------
1 grade_1
2 grade_2
sqlite>
sqlite> select * from class;
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_1 class_2_1 2
2_2 class_2_2 2
8.3.1 Cross Join
语法:SELECT * FROM table1 CROSS JOIN table2 [CROSS JOIN table3 …]
作用:把第一个表的每一行与第二个表的每一行进行连接.
1) 第一个表的第一行 与 第二个表的每一行进行连接.
2) 第一个表的第二行 与 第二个表的每一行进行连接.
...
n) 第一个表的第n行 与 第二个表的每一行进行连接.
相当于两层的循环遍历.
例:
grade表:2行, 2列
class表:4行, 3列
SELECT * FROM grade CROSS JOIN class
生成一个2*4行, 2+3列的表
由于结果集巨大, 所以比较少用(几乎不用)
sqlite> select * from grade cross join class;
gradeid name classid name gradeid
---------- ---------- ---------- ---------- ----------
1 grade_1 1_1 class_1_1 1
1 grade_1 1_2 class_1_2 1
1 grade_1 2_1 class_2_1 2
1 grade_1 2_2 class_2_2 2
2 grade_2 1_1 class_1_1 1
2 grade_2 1_2 class_1_2 1
2 grade_2 2_1 class_2_1 2
2 grade_2 2_2 class_2_2 2
8.3.2 Inner Join
作用:
类似Cross Join, 但需要满足一定条件才会连接, 如果不加约束条件, 则结果跟cross join一样.
返回的行数在0~n*m之间, 列数不超过x+y列.
语法:
SELECT * FROM table1 [INNER] JOIN table2 ON condition;
SELECT * FROM table1 [INNER] JOIN table2 USING(column1, …);
SELECT * FROM table1 NATURAL JOIN table2 …
有3种方法指定InnerJoin的判断条件:
| 方法 | 优点 | 缺点 |
|---|---|---|
| on表达式 | 无 | 1. 语句长, 2. 存在重复列 |
| using表达式 | 返回的结果集中无重复字段 | 每个字段必须存在于各个表中: USING(gradeid)要求table1和table2中都有gradeid列 |
| natural join | sql自动检测表中每一列是否匹配, 即使表结构发生变化, 也不用修改SQL语句, 可以自动适应变化 | 同上, 要求被连接的表中有相同的列名. 与USING不同的是, NATURAL JOIN自动匹配这些列, 不需要用户指定. |
使用ON CONDITION
sqlite> select * from grade join class on grade.gradeid=class.gradeid;
gradeid name classid name gradeid
---------- ---------- ---------- ---------- ----------
1 grade_1 1_1 class_1_1 1
1 grade_1 1_2 class_1_2 1
2 grade_2 2_1 class_2_1 2
2 grade_2 2_2 class_2_2 2
使用USING:using(gradeid)返回的结果集中, gradeid只出现了一次
sqlite> select * from grade inner join class using(gradeid);
gradeid name classid name
---------- ---------- ---------- ----------
1 grade_1 1_1 class_1_1
1 grade_1 1_2 class_1_2
2 grade_2 2_1 class_2_1
2 grade_2 2_2 class_2_2
使用NATURAL JOIN:没有成功, 原因不明.
8.3.3 Outer Join
作用:
sqlit3中outer join与inner join类似, 需要ON/USING的约束条件, 否则返回CROSS JOIN的结果.
与INNER JOIN不同的是:如果存在不满足条件的行, 将保留左边表格的行, 右边表格的行忽略, 这就是LEFT OUTER JOIN.
sqlite3只支持LEFT OUTER JOIN, 不支持RIGHT OUTER JOIN和FULL OUTER JOIN.
语法:
SELECT * FROM table1 LEFT OUTER JOIN table2 ON condition;
SELECT * FROM table1 LEFT OUTER JOIN table2 USING(column1, …);
(此处有一个图...)
例1:由于grade.gradeid在class中都能找到匹配的数据行, 所以返回的结果与inner join相同.
sqlite> select * from grade left outer join class on grade.gradeid=class.gradeid;
gradeid name classid name gradeid
---------- ---------- ---------- ---------- ----------
1 grade_1 1_1 class_1_1 1
1 grade_1 1_2 class_1_2 1
2 grade_2 2_1 class_2_1 2
2 grade_2 2_2 class_2_2 2
例2:给grade表中添加一个新的grade数据, 没有class与之对应, 则outer join后, 这个新grade对应的class列为空.
sqlite> INSERT INTO grade VALUES("3", "grade_3");
sqlite> SELECT * FROM grade LEFT OUTER JOIN class USING(gradeid);
gradeid name classid name
---------- ---------- ---------- ----------
1 grade_1 1_1 class_1_1
1 grade_1 1_2 class_1_2
2 grade_2 2_1 class_2_1
2 grade_2 2_2 class_2_2
3 grade_3
8.4 别名
作用: 可以暂时把表或列重命名为另一个名字, 这被称为别名. 重命名是临时的改名, 数据库中实际名称不会改变.
语法:
表别名:SELECT t0.c0, t0.c1, t1.c0, t1.c1 FROM table0 AS t0, table1 AS t1 WHERE t0.id=t1.eid;
列别名:SELECT t0.c0 as t0c0 FROM table0 as t0;
例, 将表grade别名为g, class别名为c, 方便书写.
sqlite> select g.gradeid, g.name, c.name from grade as g, class as c where g.gradeid=c.gradeid;
gradeid name name
---------- ---------- ----------
1 grade_1 class_1_1
1 grade_1 class_1_2
2 grade_2 class_2_1
2 grade_2 class_2_2
上例的结果集中有个问题, 有两列name, g.name和c.name都是name, 这时可以用列别名
例:
sqlite> SELECT g.gradeid, g.name AS gname, c.name AS cname FROM grade AS g, class AS c WHERE g.gradeid=c.gradeid;
gradeid gname cname
---------- ---------- ----------
1 grade_1 class_1_1
1 grade_1 class_1_2
2 grade_2 class_2_1
2 grade_2 class_2_2
sqlite>
8.5 触发器
SQLite触发器(Trigger)是数据库的回调函数, 会在指定的数据库事件发生时自动执行/调用.
SQLite触发器的要点如下:
- SQLite Trigger触发条件:
- 可以指定在特定的表发生改变(delete, insert, update)时触发,
- 也可以在指定表的(一个或多个)列发生更新时触发.
- SQLite只支持FOR EACH ROW Trigger, 不支持For each statement trigger. 所以不用明确指定for each row trigger.
for each row trigger的意思是: 操作语句每修改一行, 就触发一次, 比如删除10行数据变触发10次.
for each statement trigger的意思是: 一条操作语句只触发一次. - 如果有WHERE子句, 则只针对WHERE子句为真的行执行函数, 如果没有WHERE子句, 则对所有行执行函数.
- BEFORE和AFTER关键字决定何时执行触发动作(在关联行被修改的之前或者之后执行触发器动作).
- 当Trigger相关联的表被删除时, Trigger也自动被删除.
- 被修改的表必须在同一数据库中, Trigger附加的表必须使用tablename, 不能使用database.tablename.
- RAISE()可用于在Trigger程序内抛出异常.
8.5.1 创建trigger
语法:
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table-name
BEGIN
--触发器逻辑--
END;
其中event_name可以是在table_name上的insert/delete/update操作, 比如
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table-name
BEGIN
--触发器逻辑--
END;
例1:
sqlite> CREATE TABLE grade(
...> gradeid text not null primary key,
...> name text not null
...> );
sqlite>
sqlite> CREATE TABLE recorder(
...> id integer primary key autoincrement,--自增ID
...> entry_date text not null
...> );
sqlite>
sqlite> CREATE TRIGGER recorder_log AFTER insert ON grade
...> BEGIN
...> insert into recorder values(NULL, datetime('now'));
...> END;
sqlite>
sqlite> INSERT INTO grade VALUES("4", "grade_4");
sqlite> INSERT INTO grade VALUES("5", "grade_5");
sqlite> SELECT * FROM recorder;
id entry_date
---------- -------------------
1 2020-06-04 07:11:48
2 2020-06-04 07:12:09
sqlite>
8.5.2 查看trigger
sqlite> --查看所有trigger
sqlite> select name from sqlite_master where type='trigger';
name
------------
recorder_log
sqlite>
sqlite> --查看某个表上的trigger
sqlite> select name from sqlite_master where type='trigger' and tbl_name='grade';
name
------------
recorder_log
sqlite>
sqlite> --查看某个表上的trigger
sqlite> select name from sqlite_master where type='trigger' and tbl_name='class';
sqlite>
8.5.3 删除trigger
语法
DROP TRIGGER trigger_name;
例子:
DROP TRIGGER recorder_log;
8.6 索引Index
8.6.1 定义
作用:
索引是一种特殊的查找表, 数据库搜索引擎用它来加快数据检索速度.
索引是一个指向表中数据的指针. 数据库中的索引类似于一本书的目录.
索引有助于加快SELECT查询WHERE子句, 但会减慢UPDATE和INSERT语句.
索引可以创建或删除, 对索引的操作不会影响数据.
语法:
CREATE INDEX index_name ON table_name(column_name); --创建索引
DROP INDEX index_name; --删除索引
注意以下情况避免使用索引
- 索引不应该使用在较小的表上.
- 索引不应该使用在有频繁大批量修改操作(更新/插入)的表上.
- 索引不应该使用在含有大量NULL值的列上.
- 索引不应该使用在频繁操作的列上.
8.6.2 四种索引
| 名称 | 说明 | 语法 |
|---|---|---|
| 单列索引 | 基于表的一个列创建的索引 | CREATE INDEX index_name ON table_name(column_name); |
| 唯一索引 | 唯一索引不允许任何重复的值插入到表中 | CREATE UNIQUE INDEX index_name ON table_name(column_name); |
| 组合索引 | 基于表中的两个或多个列上创建的索引 | CREATE INDEX index_name ON table_name(column1, column2); |
| 隐式索引 | 创建对象时, 数据库服务器自动创建的索引, 索引自动创建为主键约束和唯一约束 | .... |
8.6.3 查看索引:
sqlite> .indices grade --列出grade表上所有可用的索引
sqlite_autoindex_grade_1 --这个是创建表时创建的隐式索引
sqlite>
sqlite> create index gidx on grade(name); --创建一个新索引
sqlite> .indices grade --再次查看表上的索引
gidx --这是新创建的索引
sqlite_autoindex_grade_1 --这是隐式索引
sqlite>
sqlite> select * from sqlite_master where type='index'; --查看数据库范围的所有索引.
type name tbl_name rootpage sql
---------- ------------------------ ---------- ---------- ----------
index sqlite_autoindex_grade_1 grade 5
index sqlite_autoindex_class_1 class 7
index gidx grade 9 CREATE IND
sqlite>
8.6.4 子句INDEXED BY
说明:
子句"INDEXED BY index_name;"规定必须使用命名的索引来查找对应表中的值.
如果index_name不存在或不能用于查询, SQLite语句会失败.
语法:
SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);
举例:
sqlite> create index gidx on grade(name);
sqlite>
sqlite> select * from grade;
gradeid name
---------- ----------
1 grade_1
2 grade_2
4 grade_4
5 grade_5
sqlite>
sqlite> select * from grade INDEXED BY gidx WHERE name='grade_2'; --使用indexed by, where中使用索引对应的列, 可以获取数据.
gradeid name
---------- ----------
2 grade_2
sqlite>
sqlite> select * from grade INDEXED BY gidx WHERE gradeid>2; --使用indexed by, 但where中使用非gidx索引的列, 不能获取数据
Error: no query solution
sqlite> select * from grade WHERE gradeid>2; --不使用indexed by, 能获取数据
gradeid name
---------- ----------
4 grade_4
5 grade_5
8.7 Alter命令
作用: ALTER TABLE命令, 在不执行完整的转储和数据重载的情况下, 用来修改已有的表.
语法, 支持两种修改:
ALTER TABLE database_name.table_name RENAME TO new_table_name; --重命名已有的表
ALTER TABLE database_name.table_name ADD COLUMN column_def …; --已有的表中添加列
--注意, 新添加的列以null来填充.
sqlite> select * from grade;
gradeid name
---------- ----------
1 grade_1
2 grade_2
3 grade_3
4 grade_4
5 grade_5
sqlite> --将表grade改名为new_grade
sqlite> ALTER TABLE grade RENAME TO new_grade;
sqlite> select * from grade;
Error: no such table: grade
sqlite> select * from new_grade;
gradeid name
---------- ----------
1 grade_1
2 grade_2
3 grade_3
4 grade_4
5 grade_5
sqlite> --给new_grade中添加一列
sqlite> ALTER TABLE new_grade ADD COLUMN schoolid text;
sqlite> select * from new_grade;
gradeid name schoolid
---------- ---------- ----------
1 grade_1 --注意, 新添加的列以null来填充.
2 grade_2
3 grade_3
4 grade_4
5 grade_5
sqlite>
8.8 清空表数据
SQLite中没有Truncate table命令, 但可以使用delete命令将表中的数据全部删除.
DELETE FROM table_name --这种方法不能将递增数归零
DELETE FROM sqlite_sequence WHERE name='table_name' --可以将递增数归零.
8.9 视图View
View是一种虚表, 是通过相关的名称存储在数据库中的SQLite语句(以预定义的SQLite查询形式存在的表的组合).
View是只读的, 不能在View上执行delete/insert/update语句.
可以在View上创建触发器, 当尝试delete/insert/update View时触发.
作用:
- 用户/用户组 查找结构数据的方式更直观;
- 限制数据访问, 用户只能看到有限的数据, 而不是完整的表;
- 汇总多个表中的数据, 用于生成报告;
创建View:
使用CREATE VIEW语句创建, view可以从单一的表创建, 也可以从多个表创建, 也可以从其它view创建.
CREATE [TEMP|TEMPORARY] VIEW view_name AS
SELECT column1, column2, …
FROM table_name
[WHERE condition];
删除View:
DROP VIEW view_name;
例子:
--全体数据
sqlite> select * from class;
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_1 class_2_1 2
2_2 class_2_2 2
sqlite>
sqlite> --创建view
sqlite> create view cls_view as select name, gradeid from class where gradeid=2;
sqlite>
sqlite> --查询view
sqlite> select * from cls_view;
name gradeid
---------- ----------
class_2_1 2
class_2_2 2
sqlite>
sqlite> drop view cls_view;
sqlite>
8.10 事务Transaction
8.10.1 说明
事务是一个对数据库执行的工作单元.
事务是以逻辑顺序完成的"工作单位或序列", 可以由用户手工操作完成, 也可以由数据库程序自动完成.
事务是指一个或多个更改数据库的扩展.
例如: 以下两个操作就是在表上执行事务: 创建记录, 从表中删除记录.
重要的是要控制事务, 以确保数据的完整性和处理数据库错误.
可以把请多的SQLite查询联合成一组, 把所有这些放在一起作为事务的一部分进行执行.
8.10.2 事务的属性
事务有四个标准属性, 根据首字母缩写为ACID.
- 原子性(Atomicity): 确保工作单位内的所有操作都成功完成, 否则事务会在出现故障时终止, 之前的操作也会回滚到以前的状态.
- 一致性(Consistency): 确保数据库在成功提交的事务上正确地改变状态.
- 隔离性(Isolation): 使事务操作相互独立和透明.
- 持久性(Durability): 确保已提交事务的结果或效果在系统发生故障的情况下仍然存在.
8.10.3 事务控制
使用下面的命令来控制事务
- BEGIN TRANSACTION: 开始处理事务.
- COMMIT: 保存更改, 或者可以使用END TRANSACTION命令.
- ROLLBACK: 回滚所做的更改.
事务控制命令只与DML命令(INSERT, UPDATE, DELETE)一起使用. 他们不能在创建表或删除表时使用, 因为这些操作在数据库中是自动提交的.
8.10.3 BEGIN TRANSACTION命令
可以使用BEGIN TRANSACTION(或BEGIN)命令来启动事务, 然后事务会持续执行下去, 直到遇到下一个COMMIT或ROLLBACK命令.
不过在数据库关闭或发生错误时, 事务处理也会回滚.
语法:
BEGIN;
--or
BEGIN TRANSACTION;
8.10.4 COMMIT命令
作用: 把事务调用的更改保存到数据库中. 该命令把自上次COMMIT或ROLLBACK命令以来的所有事务保存到数据库.
语法:
COMMIT;
--or
END TRANSACTION;
8.10.4 ROLLBACK命令
作用: 用于撤消尚未保存到数据库的事务. 只能撤消自上次发出COMMIT或ROLLBACK命令以来的事务.
语法:
ROLLBACK;
例子:
sqlite> --全体数据
sqlite> select * from class;
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_1 class_2_1 2
2_2 class_2_2 2
sqlite>
sqlite> --一个事务, DELETE后回滚
sqlite> BEGIN;
sqlite> delete from class where classid='2_1';
sqlite> select * from class; --delete后, 显示数据被删除
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_2 class_2_2 2
sqlite> ROLLBACK; --rollback后, 被删除的数据又回来了.
sqlite> select * from class;
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_1 class_2_1 2
2_2 class_2_2 2
sqlite>
sqlite> --another transaction, 删除数据后提交
sqlite> BEGIN;
sqlite> DELETE FROM class WHERE classid='2_1';
sqlite> COMMIT; --提交
sqlite> SELECT * FROM class; --COMMIT后, 数据真被删除了.
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_2 class_2_2 2
sqlite>
8.11 子查询
8.11.1 说明
子查询又叫内部查询/嵌套查询, 指的是在SQLite查询的WHERE子句中嵌入查询语句.
也就是说: 一个SELECT语句的查询结果可以作为另一个语句的输入值.
子查询可以与SELECT, INSERT, UPDATE, DELETE语句一起使用.
可伴随使用运算符: =, <, <=, >, >=, IN, BETWEEN, 等.
子查询要遵守的规则:
- 子查询要用括号括起来;
- 子查询在SELECT子句中只能在一个列, 除非在主查询中有多列, 与子查询的所选列进行比较.
- ORDER BY 不能用在子查询中, 虽然主查询可以使用 ORDER BY. 可以在子查询中使用 GROUP BY, 功能与 ORDER BY 相同
- 子查询返回多于一行, 只能与多值运算符一起使用, 如 IN 运算符;
- BETWEEN 运算符不能与子查询一起使用, 但是, BETWEEN 可在子查询内使用.
8.11.2 SELECT语句中的子查询使用:
语法:
SELECT column0[, column1, …]
FROM table1[, table2, …]
WHERE column_name OPERATOR
(SELECT column0[, column1, …]
FROM table1[, table2, …]
[WHERE]
);
例:
sqlite> create table student(
...> id int not null,
...> name text not null,
...> score int not null
...> );
sqlite> insert into student values(1, 'Name1', 80);
sqlite> insert into student values(2, 'Name2', 90);
sqlite> insert into student values(3, 'Name3', 85);
sqlite> insert into student values(4, 'Name4', 65);
sqlite> --查找最高分数
sqlite> select max(score) from student;
max(score)
----------
90
sqlite> --查找最高分数对应学生的信息
sqlite> select * from student where score=(select max(score) from student); --使用子查询
id name score
---------- ---------- ----------
2 Name2 90
sqlite>
sqlite> --等价于以下语句
sqlite> select id, name, max(score) from student;
id name max(score)
---------- ---------- ----------
2 Name2 90
sqlite>
8.11.2 INSERT语句中的子查询使用:
语法
INSERT INTO table_name [(column1[, column2, …])]
SELECT [*|column1[, column2, …]]
FROM table1[, table2]
[WHERE VALUE OPERATOR];
例子
sqlite> --student表中的全体数据
sqlite> select * from student;
id name score
---------- ---------- ----------
1 Name1 80
2 Name2 90
3 Name3 85
4 Name4 65
sqlite>
sqlite> 创建一个与student数据结构相同的表student_new
sqlite> create table student_new(
...> id int not null,
...> name text not null,
...> score int not null
...> );
sqlite>
sqlite> --把分数大于85的数据 从student插入到student_new.
sqlite> insert into student_new select * from student where id in(select id from student where score>=85);
sqlite> select * from student_new;
id name score
---------- ---------- ----------
2 Name2 90
3 Name3 85
sqlite>
sqlite> --感觉跟如下语句没差别啊, 为什么要像上条insert语句写那么长???
sqlite> insert into student_new select * from student where score>=85;
sqlite> select * from student_new;
id name score
---------- ---------- ----------
2 Name2 90
3 Name3 85
2 Name2 90
3 Name3 85
sqlite>
8.11.3 UPDATE语句中的子查询使用:
语法
UPDATE table_name SET column1=new_value
[WHERE OPERATOR [ VALUE]];
(SELECT column_name
FROM table_name
[WHERE]);
例子
sqlite> --原始数据
sqlite> select * from student;
id name score
---------- ---------- ----------
1 Name1 80
2 Name2 90
3 Name3 85
4 Name4 65
sqlite> --把特定学生(另一个表格中存在的且分数>=85的学生)的分数减半
sqlite> update student set score=score*0.5 where id in(select id from student_new where score>=85);
sqlite> select * from student;
id name score
---------- ---------- ----------
1 Name1 80
2 Name2 45
3 Name3 42.5
4 Name4 65
sqlite>
8.11.4 DELETE语句中的子查询使用:
语法
DELETE FROM table_name
[WHERE OPERATOR [ VALUE]];
(SELECT column_name
FROM table_name
[WHERE]);
例子
sqlite> --原始数据
sqlite> select * from student;
id name score
---------- ---------- ----------
1 Name1 80
2 Name2 45
3 Name3 42.5
4 Name4 65
sqlite>
sqlite> --把特定学生(另一个表格中存在的且分数>=85的学生)的记录删除
sqlite> delete from student where id in (select id from student_new where score>=85);
sqlite> select * from student;
id name score
---------- ---------- ----------
1 Name1 80
4 Name4 65
sqlite>
8.12 自动递增Autoincrement
AUTOINCREMENT是一个关键字, 作用于表中的某些列, 这些列的值自动递增.
AUTOINCREMENT只能用于整型(INTEGER)字段.
语法:
CREATE TABLE table_name(
column1 integer autoincrement, --必须是integer类型.
column2 datatype,
…
);
每次向表中insert数据时, 不必为column1赋值, 它自动加1.
例子:
sqlite> --创建表格
sqlite> create table visitor(
...> name text not null,
...> id integer primary key autoincrement
...> );
sqlite> --插入数据, id给值null, 数据库自动为它赋递增的值
sqlite> insert into visitor values ('name0', null);
sqlite> insert into visitor values ('name1', null);
sqlite> insert into visitor values ('name2', null);
sqlite> insert into visitor values ('name3', null);
sqlite>
sqlite> select * from visitor;
name id
---------- ----------
name0 1
name1 2
name2 3
name3 4
sqlite> --
sqlite> --删除数据, 并不会影响递增数据的值, 继续insert数据时, 递增列继续递增.
sqlite> delete from visitor where name='name1';
sqlite> select * from visitor;
name id
---------- ----------
name0 1
name2 3 --删除id=2的记录, 后面的id不会改变.
name3 4
sqlite> insert into visitor values ('name4', null);
sqlite> select * from visitor;
name id
---------- ----------
name0 1
name2 3
name3 4
name4 5 --新增数据, id在4基础上继续增加.
sqlite>
8.13 注入
如果您的站点允许用户通过网页输入, 并将输入内容插入到 SQLite 数据库中, 这个时候您就面临着一个被称为 SQL 注入的安全问题.
需要防止这种情况发生, 确保脚本和SQLite语句的安全.
注入通常在请求用户输入时发生, 比如需要用户输入姓名, 但用户却输入了一个 SQLite 语句, 而这语句就会在不知不觉中在数据库上运行.
8.14 解释Explain
在使用SQLite语句之前, 可以使用"EXPLAIN"关键字或者"EXPLAIN QUERY PLAN"短语, 用于描述表的细节.
如果省略了EXPLAIN关键字或者短语, 任何修改都会引起SQLite语句的查询行为. 并返回有关SQLite语句如何操作的信息.
注意:
- 来自EXPLAIN和EXPLAIN QUERY PLAN的输出只用于交互式分析和排除故障.
- 输出格式的细节可能会随着SQLite版本的不同而有变化.
- 应用程序不应该使用EXPLAIN或EXPLAIN QUERY PLAN, 因为其确切的行为是可变的且只有部分会被记录.
语法:
EXPLAIN [SQLite Query]
EXPLAIN QUERY PLAN [SQLite Query]
例子:
sqlite> --全体数据
sqlite> select * from class;
classid name gradeid
---------- ---------- ----------
1_1 class_1_1 1
1_2 class_1_2 1
2_2 class_2_2 2
sqlite>
sqlite> --使用explain
sqlite> explain select * from class;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 9 0 00 Start at 9
1 OpenRead 0 6 0 3 00 root=6 iDb=0; class
2 Rewind 0 8 0 00
3 Column 0 0 1 00 r[1]=class.classid
4 Column 0 1 2 00 r[2]=class.name
5 Column 0 2 3 00 r[3]=class.gradeid
6 ResultRow 1 3 0 00 output=r[1..3]
7 Next 0 3 0 01
8 Halt 0 0 0 00
9 Transaction 0 0 15 0 01 usesStmtJournal=0
10 Goto 0 1 0 00
sqlite>
sqlite> --使用explain query plan
sqlite> explain query plan select * from class;
QUERY PLAN
`--SCAN TABLE class
sqlite>
sqlite> --使用explain query plan
sqlite> explain query plan select * from class where classid='1_2';
QUERY PLAN
`--SEARCH TABLE class USING INDEX sqlite_autoindex_class_1 (classid=?)
sqlite>
8.15 Vacuum
操作: VACUUM命令通过复制主数据库中的内容到一个临时数据库文件, 然后清空主数据库, 并从副本中重新载入原始的数据库文件.
作用: 消除空闲页, 把表中的数据排列为连续的, 另外会清理数据库文件结构.
如果表中没有明确的整型主键(INTEGER PRIMARY KEY), VACUUM命令可能会改变表中条目的行ID(ROWID).
VACUUM命令只适用于主数据库, 附加的数据库文件是不可能使用VACUUM命令.
如果有一个活动的事务, VACUUM命令会失败.
VACUUM命令是一个用于内存数据库的任何操作.
由于VACUUM命令从头开始重新创建数据库文件, 所以VACUUM也可以用于修改许多数据库特定的配置参数.
8.15.1 手动VACUUM
$ sqlite3 database_name "VACUUM;" # 在命令行对整个数据库发出VACUUM命令的语法
sqlite> VACUUM; --在SQLite提示符中运行VACUUM.
sqlite> VACUUM table_name; --在特定表中运行VACUUM.
8.15.1 自动VACUUM(Auto-VACUUM)
操作: SQLite的auto-VACUUM与VACUUM不大一样, 它只是把空闲页移到数据库末尾, 从而减小数据库大小.
作用: 通过这样做, 它可以明显地把数据库碎片化, 而VACUUM则是反碎片化. 所以Auto-VACUUM只会让数据库更小.
在SQLite的提示符中, 可以通过下面的编译运行, 启用/禁止SQLite的Auto-VACUUM:
sqlite> PRAGMA auto_vacuum = NONE ; -- 0, means disable auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 1, means enable incremental vacuum
sqlite> PRAGMA auto_vacuum = FULL ; -- 2, means enable auto vacuum
也可以从命令行中运行命令来检查auto-vacuum设置:
$ sqlite3 database_name "PRAGMA auto_vacuum;"
8.16 日期时间
8.16.1 说明
SQLite支持以下5个日期和时间函数:
| 序号 | 函数 | 说明 |
|---|---|---|
| 1 | date(timestring, modifier, modifier, ...) | 以YYYY-MM-DD格式返回日期. |
| 2 | time(timestring, modifier, modifier, ...) | 以HH:MM:SS格式返回时间. |
| 3 | datetime(timestring, modifier, modifier, ...) | 以YYYY-MM-DD HH:MM:SS格式返回. |
| 4 | julianday(timestring, modifier, modifier, ...) | 返回从格林尼治时间的公元前4714年11月24日正午算起的天数. |
| 5 | strtime(format, timestring, modifier, modifier, ...) | 根据第一个参数指定的格式字符串返回格式化的日期. |
8.16.2 时间字符串timestring
timestring可以是采用下面任何一种格式
| 序号 | timestring | 例子 |
|---|---|---|
| 1 | YYYY-MM-DD | 2010-12-30 |
| 2 | YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
| 3 | YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
| 4 | MM-DD-YYYY HH:MM | 12-30-2010 12:10 |
| 5 | HH:MM | 12:10 |
| 6 | YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
| 7 | HH:MM:SS | 12:10:04 |
| 8 | YYYYMMDD HHMMSS | 20101230 1210 |
| 9 | now | 2013-05-07 |
可以使用"T"作为分隔日期和时间的文字字符.
8.16.3 修饰符Modifier
timestring后边可以跟着0个或多个修饰符, 这将改变由上述一个函数返回的日期和时间.
修饰符应从左到右使用.
可在SQLite中使用的修饰符:
| 序号 | modifier | 说明 |
|---|---|---|
| 1 | NNN days | . |
| 2 | NNN hours | . |
| 3 | NNN minutes | . |
| 4 | NNN .NNNN seconds | . |
| 5 | NNN months | . |
| 6 | NNN years | . |
| 7 | start of month | . |
| 8 | start of year | . |
| 9 | start of day | . |
| 10 | weekday N | . |
| 11 | unixpoch | . |
| 12 | localtime | . |
| 13 | utc | . |
8.16.4 格式化
SQLite提供了函数strftime()来格式化任何日期和时间.
| 序号 | 格式 | 说明 |
|---|---|---|
| 1 | %d | 一月中的第几天, 01~31 |
| 2 | %f | 带小数部分的秒，SS.SSS |
| 3 | %H | 小时, 00~23 |
| 4 | %j | 一年中的第几天，001-366 |
| 5 | %J | 儒略日数，DDDD.DDDD |
| 6 | %m | 月, 00~12 |
| 7 | %M | 分, 00~59 |
| 8 | %s | 从 1970-01-01 算起的秒数 |
| 9 | %S | 秒, 00~59 |
| 10 | %w | 一周中的第几天，0-6 (0 is Sunday) |
| 11 | %W | 一年中的第几周，01-53 |
| 12 | %Y | 年, YYYY |
| 13 | %% | %, 字符本身 |
8.16.5 例子
sqlite> select date('now'); --当前日期
date('now')
-----------
2022-09-19
sqlite>
sqlite> select date('now', 'start of month', '+1 month', '-1 day'); --当前月份的最后一天
date('now', 'start of month', '+1 month', '-1 day')
---------------------------------------------------
2022-09-30
sqlite>
sqlite> select datetime(1092941466, 'unixepoch'); --时间戳对应的日期和时间
datetime(1092941466, 'unixepoch')
---------------------------------
2004-08-19 18:51:06
sqlite> select datetime(1092941466, 'unixepoch', 'localtime'); --时间戳对应的本地时区的日期和时间
datetime(1092941466, 'unixepoch', 'localtime')
----------------------------------------------
2004-08-20 02:51:06
sqlite>
sqlite> select strftime('%s', 'now'); --返回当前时间的时间戳.
strftime('%s', 'now')
---------------------
1663572322
sqlite>
sqlite> select julianday('now') - julianday('2022-01-01'); -- 某日期到现在的天数.
julianday('now') - julianday('2022-01-01')
------------------------------------------
261.310867708176
sqlite>
sqlite> select strftime('%s', 'now') - strftime('%s', '2022-01-01 00:00:00'); --某日期时间到现在的秒数.
strftime('%s', 'now') - strftime('%s', '2022-01-01 00:00:00')
-------------------------------------------------------------
22577433
sqlite>
sqlite> select date('now', 'start of year', '+9 months', 'weekday 2'); --当年10月第1个星期二的日期
date('now', 'start of year', '+9 months', 'weekday 2')
------------------------------------------------------
2022-10-04
sqlite>
sqlite> select (julianday('now') - 2440587.5) * 86400.0; --从Unix纪元算起的时间(以秒为单位), 类似于strftime('%s', 'now'), 不同的是这里包括小数部分.
(julianday('now') - 2440587.5) * 86400.0
----------------------------------------
1663572897.68002
sqlite>
sqlite> select time('12:00', 'localtime'); -- utc->本地时间 转换.
time('12:00', 'localtime')
--------------------------
20:00:00
sqlite> select time('12:00', 'utc'); -- 本地时间->utc 转换.
time('12:00', 'utc')
--------------------
04:00:00
sqlite>
sqlite>
8.17 常用函数
| 函数 | 作用 | null |
|---|---|---|
| COUNT | 聚集函数, 计算数据库表中行数 | - |
| MAX | 聚合函数, 选择某列的最大值 | - |
| MIN | 聚合函数, 选择某列的最小值 | - |
| AVG | 聚合函数, 计算某列的平均值 | - |
| SUM | 聚合函数, 为一个数值列计算总合 | - |
| RANDOM | 返回介于(-9223372036854775808, +9223372036854775807)之间的伪随机数 | - |
| ABS | 返回数值参数的绝对值 | - |
| UPPER | 把字符串转为大写字母 | - |
| LOWER | 把字符串转为小写字母 | - |
| LENGTH | 返回字符串长度 | - |
| sqlite_version | 返回SQLite库的版本 | - |
例子:
sqlite> select * from grade;
gradeid name
---------- ----------
1 grade_1
2 grade_2
4 grade_4
5 grade_5
sqlite> select count(*) from grade;
count(*)
----------
4
sqlite> select max(gradeid) from grade;
max(gradeid)
------------
5
sqlite> select min(gradeid) from grade;
min(gradeid)
------------
1
sqlite>
sqlite> select avg(gradeid) from grade;
avg(gradeid)
------------
3.0
sqlite>
sqlite> select sum(gradeid) from grade;
sum(gradeid)
------------
12
sqlite>
sqlite> select random() as 'RDM';
RDM
--------------------
-4450169040852162096
sqlite>
sqlite> select abs(5), abs(-15), abs(NULL), abs(0), abs('ABC');
abs(5) abs(-15) abs(NULL) abs(0) abs('ABC')
---------- ---------- ---------- ---------- ----------
5 15 0 0.0
sqlite>
sqlite> select upper(name) from grade;
upper(name)
-----------
GRADE_1
GRADE_2
GRADE_4
GRADE_5
sqlite>
sqlite> select name, length(name) from grade;
name length(name)
---------- ------------
grade_1 7
grade_2 7
grade_4 7
grade_5 7
sqlite>
sqlite> select sqlite_version() as 'vsn';
vsn
----------
3.27.2
sqlite>
9 其它操作
9.1 foreign key(外键)
SQLite从3.6.19开始支持foreign key约束.
以下例子说明foreign key的用法
--foreign key默认关闭, 需要打开
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys=ON;
sqlite> PRAGMA foreign_keys;
1
--先定义两个没有foreign key的表:
create table users(
id integer primary key autoincrement,
name text not null unique -- foreign key must be unique
);
create table projects(
id integer primary key autoincrement,
proj text not null unique -- foreign key must be unique
);
--接下来定义一个table, 包含两个foreign key,
create table works(
id integer primary key autoincrement,
user_name text,
proj_name text,
--cascade表示对一个表的删除/修改会同步到其它表格
foreign key(user_name) references users(name) on delete cascade on update cascade,
foreign key(proj_name) references projects(proj) on delete cascade on update cascade
);
--插入数据
insert into users(id, name) values(null, '张三');
insert into users(id, name) values(null, '李四');
insert into projects(id, proj) values(null, 'Project1');
insert into projects(id, proj) values(null, 'Project2');
insert into projects(id, proj) values(null, 'Project3');
insert into works(id, user_name, proj_name) values(null, '张三', 'Project1');
insert into works(id, user_name, proj_name) values(null, '张三', 'Project2');
insert into works(id, user_name, proj_name) values(null, '李四', 'Project2');
insert into works(id, user_name, proj_name) values(null, '李四', 'Project3');
sqlite> --查看
sqlite> .header on
sqlite> .mode column
sqlite> select * from works; --查看数据
id user_name proj_name
---------- ---------- ----------
1 张三 Project1
2 张三 Project2
3 李四 Project2
4 李四 Project3
sqlite>
sqlite> select * from projects;
id proj
---------- ----------
1 Project1
2 Project2
3 Project3
sqlite>
sqlite> --Project2为Proj2.new
sqlite> update projects set proj='Proj2.new' where proj='Project2';
sqlite>
sqlite> select * from projects; --查看数据, projects表格中的Project2被修改了.
id proj
---------- ----------
1 Project1
2 Proj2.new --projects表格数据修改成功.
3 Project3
sqlite>
sqlite> select * from works where proj_name like 'Proj%2%'; --查看数据, works表格中的Project2也被修改了.
id user_name proj_name
---------- ---------- ----------
2 张三 Proj2.new --Project2被修改为Proj2.new了
3 李四 Proj2.new --Project2被修改为Proj2.new了
sqlite>
sqlite> .q --退出
