sqlite数据库
目录
3.嵌套查询，在上一级查询的结果上进行查询，也是类似于编程语言中的多层循环... 6
4.集合查询，参加操作的行的列数、数据类型都必须相同... 6
5.基于派生表的查询，子查询的结果可以出现在FROM子句中作为临时的派生表，sqlite3不支持... 6
正文
一、安装sqlite3
进入https://www.sqlite.org/，下载for widows和sqlite-amalgamation-….zip的全部文件；
创建文件夹sqlite3_32(bin,include,lib)或sqlite3_64(bin,include,lib)；
创建环境路径E:\CodeBlocks\MinGW\bin; E:\sqlite3_32\bin，这一步不是必须的，但如果经常用命令行的话会方便很多；
sqlite3_32/lib路径下命令行执行dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3dll.a；
Build options->Search directories->Compiler选入E:\sqlite3_32\include、Build options->Search directories->Linker选入E:\sqlite3_32\lib、Build options->Linker settings加入E:\sqlite3_32\lib\libsqlite3dll.a；
如果出现0xc000007b的错误提示，表示使用的32或64位版本和机器不兼容（比如32位的电脑上用了64位的dll）。
二、定义数据
1.进入和定义数据库
sqlite3的shell命令
sqlite3 <[路径]数据库的名称>
2.基本表的定义、删除、修改
sql语句中的单引号和双引号没有必然区别，有时候能够起到互为转义的作用
(1)基本数据类型
| NULL | 空值 |
| INT | 整形 |
| NUMBERIC | 精确数值 |
| VARCHAR | 可变长的文本 |
| FLOAT | 浮点数 |
| DOUBLE | 双精度浮点数 |
| DECIMAL(10,5) | 精确小数 |
| BOOLEAN | 布尔值 |
| DATE | 日期 |
//定义一个主键自增的表，这个地方要用integer否则自增会报错
CREATE TABLE peopleinfo(
id integer primary key autoincrement,
name text not null,
age integer,
height float);
(2)完整性约束条件
比如PRIMARY KEY、FOREIGN KEY(Cpno) REFERENCES Course(Cno)、NOT NULL
(3)定义基本表
sql标准语句
CREATE TABLE <表名>(<列名>< 数据类型>[ 列级完整性约束条件]
[,<列名>< 数据类型>[ 列级完整性约束条件]]
…
[,<标记完整性约束条件>]);
(4)删除基本表
sql标准语句
CASCADE级联、与该表相关的对象全部删除，RESTRICT限制、如果该表没有相关的对象就可以执行这些语句
DROP TABLE <表名称> [RESTRICT|CASCADE];
(5)修改基本表
sql标准语句
ALTER TABLE <表名>
[ADD [COLUMN] <新列名>< 数据类型>[ 完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASECADE|RESTRICT]]
[DROP CONSTRANT <完整性约束> [CASECADE|RESTRICT]]
[ALTER COLUMN <列名>< 数据类型>];
sqlite3的shell命令
ALTER TABLE 数据库名称.表名称ADD [COLUMN] <新列名>< 数据类型>[ 完整性约束];
ALTER TABLE数据库名称. 表名称 RENAME TO 新名称;
3.索引的建立和删除
当表的数据相当庞大而且需要查询时，使用索引是很有必要的
标准sql语句
(1)建立索引
UNIQUE表示索引中的每个索引值值对应一个数据记录，CLUSTER表示聚簇索引；索引可以建在一列或多列上，次序默认是ASC升序、次序DESC是降序
CREATE [UNIQUE][CLUSTER] INDEX <索引的名称>
ON <表的名称>(<列名> [次序] [,<列名>[次序]]…);
(2)修改索引
sqlite3不支持修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
(3)删除索引
DROP INDEX <索引名>;
4.触发器的定义和删除
标准sql语句
定义触发器
CREATE TRIGGER <触发器名称>
{BEFORE|AFTER} <触发事件> ON <表的名称>
REFERENCING NEW|OLD ROW AS <变量>
FOR EACH {ROW|STATEMENT}
[WHEN <触发条件>] <触发动作体>
删除触发器
DROP TRIGGER <触发器名称> ON <表的名称>;
sqlite3的shell命令
定义触发器
事件名称可以是INSERT、UPDATE、DELETE；在sqlite_master这张表中可以找到触发器的信息，最前面是type类型
CREATE TRIGGER 触发器名称 [BEFORE|AFTER] 事件名称
ON 表的名称
BEGIN
类似于sql的逻辑语句
END;
删除触发器
DROP TRIGGER 触发器名称;
二、查询数据
标准sql语句
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>,…]
FROM <表或视图的名称> [,<表或视图的名称>,…] | (<SELECT 语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列的名称1> [HAVING <条件表达式>]]
[ORDER BY <列的名称2> [ASC|DESC]];
1.单表查询
查询指定列
//Sno、Sname是Student表中的列，但是列举出来的列的顺序可以是任意的
SELECT Sno,Sname FROM Student;
查询全部列
SELECT * FROM Student;
查询经过计算的值
SELECT Sname,2014-Sage FROM Student;
//加入字符串常量、函数
SELECT Sname NAME,'Year of birth:' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student;
消除完全重复的行
//默认是全部ALL
SELECT DISTINCT Sno FROM SC;
查询满足条件的行
//比较大小：会用到WHERE子句和运算符（!=或<>，!>，!<等）
SELECT Sname,Sage FROM Student WHERE Sage<20;
//在某个范围内、不在某个范围内
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
//在某个集合内、不在某个集合内
SELECT Sname,Ssex FROM Student WHERE Sdept IN('CS','MA','IS');
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN('CS','MA','IS');
//字符串匹配，[NOT] LIKE ‘用于匹配的字符串’ [ESCAPE ‘转义字符’]，%表示匹配任意长度的字符串、_匹配一个字符
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';
SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
//涉及空值的查询，实际过程中有些数据可能为空
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
//多种条件的查询，用AND、OR来连接各个查询条件，AND的优先级高于OR但是可以用括号来改变
SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20;
ORDER BY子句，对上一级结果按照一列或多列进行升序ASC（默认值）、降序DESC输出
SELECT * FROM Student ORDER BY Sdept,Sage DESC;
聚集函数，只能用于SELECT子句和GROUP BY中的HAVING子句
| COUNT([DISTINCT|ALL] <列的名称>) | 统计满足条件的列的数目 |
| SUM([DISTINCT|ALL] <列的名称>) | 列值的总和 |
| AVG([DISTINCT|ALL] <列的名称>) | 列值的平均值 |
| MAX([DISTINCT|ALL] <列的名称>) | 列中的最大值 |
| MIN([DISTINCT|ALL] <列的名称>) | 列中的最小值 |
| LOWER() | 将字符串转换为小写 |
| UPPER() | 将字符串转换为大写 |
| RANDOM() | 获取一个随机数 |
| ABS() | 求绝对值 |
| LENGTH() | 求字符串的长度 |
//获取学生的总数目
SELECT COUNT(*) FROM Student;
//计算3号课程的平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno='3';
GROUP BY子句，对一列或多列进行分组、之后对每一组使用聚集函数后输出，如果最终选出的组还需要满足一定的条件，那就使用HAVING条件子句
SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>2;
2.连接查询，查询将涉及多张表
等值与非等值的连接查询，类似编程中的双层循环，之前建立的索引在这里会加快查询的过程
//WHERE子句有一定的格式，“表1.列的名称 比较运算符 表2.列的名称”或者“表1.列的名称 BETWEEN 表2.列的名称 AND 表2.列的名称”
SELECT Student.Sno,SC.Cno,Sage FROM Student,SC WHERE Student.Sno=SC.Sno;
自身连接
//先修课的先修课
SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno=SECOND.Cno;
外连接也是一种连接查询，无损一个表然后尽可能的填充输出
//这个一个左外连接，类似的有右外连接
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
多表连接，按先后顺序先进行两个表的连接，得到的结果继续和下一个表进行连接
//跨表查询每个学生的学号、姓名、选修的课程名和对应的成绩
SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
3.嵌套查询，在上一级查询的结果上进行查询，也是类似于编程语言中的多层循环
带有IN的子查询
//不相关子查询，查找和“刘晨”在同一个系的学生
SELECT * FROM Student WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname='刘晨');
带有比较运算符的子查询
//相关子查询，找出每个学生超过他自身选修课程平均成绩的课程号
SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);
带有ANY(SOME)或ALL的子查询，sqlite3不支持应该用后一种办法来代替
SELECT Sname,Sage FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept<>'CS';
SELECT Sname,Sage FROM Student WHERE Sage<(SELECT MAX(Sage) FROM Student WHERE Sdept='CS') AND Sdept<>'CS';
带有量词EXISTS或NOT EXISTS的子查询，子查询的结果返回true或false
//查询所有选修了课程1的学生姓名
SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
//查询选修了全部课程的学生姓名，可能要用到量词转换规律解决类似的至多至少问题
SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));
4.集合查询，参加操作的行的列数、数据类型都必须相同
//可能会用到并操作UNION、交操作INTERSECT、差操作EXCEPT
SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19;
5.基于派生表的查询，子查询的结果可以出现在FROM子句中作为临时的派生表，sqlite3不支持
//找出每个学生超过他选修课程平均成绩的课程号
SELECT Sno,Cno FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade) WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;
三、更新数据
标准sql语句
1.添加数据
INSERT INTO <表名>[属性列1,…,属性列n]
VALUES(数据1,…,数据n);
或者
INSERT INTO <表名>[属性列1,…,属性列n]
[数据集合];
2.修改数据
sql标准语句
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>,…]
[WHERE <条件>];
3.删除数据
DELETE FROM <表名称> [WHERE <条件>];
四、特殊处理
1.空值
插入空值
INSERT INTO SC VALUES('201215126','1',NULL);
空值的判断，使用IS NULL或IS NOT NULL
2.视图
在表的基础上抽象出自己感兴趣的部分得到视图，视图有着和表类似的操作；数据库不会存放视图的数据，只是存放它的定义；WITH CHECK OPTION表示更新视图时要时刻加上子查询中的条件；sqlite3不支持WITCH CHECK OPTION、不支持CASCADE、不支持更新视图
建立视图
CREATE VIEW <视图的名称> [(<列的名称>[,<列的名称>…])]
AS <子查询>
[WITH CHECK OPTION]
删除视图
//CASCADE表示与之相关的都一并删除
DROP VIEW <视图的名称> [CASCADE];
五、附录
1.参考网址
| www.sqlite.org | 下载sqlite3 |
| www.runoob.com | 中文教程 |
2.sqlite3的常用命令
命令区分大小写
| .databases | 查阅当前数据库 |
| .mode <选项> | 输出模式，选项可以是：逗号分隔csv、左对齐的列column、<table> 代码html、insert语句insert、每行一个值line、separator 字符串分隔list、Tab 分隔tabs、TCL 列表tcl |
| .tables | 查阅当前数据库中的表 |
| ctrl+c | 退出 |
| .schema <表名称> | 查询表的结构 |
| sqlite3 <[路径]数据库名称> .dump > <[路径]名称.sql> | 将数据库导出到文件 |
| sqlite3 <[路径]名称.db> < <[路径]文件名称.sql> | 用sql文件恢复数据库 |
| .open [路径]名称.db | 进入sqlite3后打开某个数据库 |
| .excel | 可以将查询到的结果导出到excel文件 |
| .indices <表名称> | 列出表的索引 |
| .output <文件名> | 将输出结果保存到文件，也可以是stdout |
| .read <文件名> | 执行文件中的sql语句 |
| .show | 显示数据库中的系统设置 |
| .timer <ON|OFF> | 计时器 |
| select 表达式; | 对表达式进行运算 |
| sqlite_version() | 获取sqlite的版本号 |
| .exit | 退出sqlite |
| -- | 注释一条语句 |
