SQLite 数据库基础教程

发表于 | 更新于 | 分类于 | 阅读次数：

目录

  • SQLite 创建数据库
  • SQLite Like 子句
  • SQLite Glob 子句
  • SQLite Limit 子句
  • SQLite Distinct 关键字
  • SQLite JOIN 子句
    • 交叉连接 - CROSS JOIN
    • 内连接 - INNER JOIN
    • 外连接 - OUTER JOIN
  • SQLite Having 子句
  • SQLite Delete 语句

SQLite

一、SQLite 创建数据库

SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库。您不需要任何特殊的权限即可创建一个数据。打开cmd，输入

1
2
3
4
5
sqlite3 student.db
/*
在当前目录下创建名为student的数据库，
数据库创建成功后会弹出一个 sqlite> 的提示符
*/

SQLite 创建表的语法为

1
2
CREATE TABLE table_name (
);

这时数据库student.db还是空的，我们在数据库中新建三个表：course、sc和student，在当前目录下新建sql_scripts.txt文本文档，并将下列代码写入文本文档，然后在sqlite>的提示符后输入.read data.txt加载，则三个表在student.db中新建完毕。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/*创建course表*/
CREATE TABLE course (
  cno char(2) NOT NULL, --课程编号，不能为空
  cname char(20),  --课程名
  cpno char(2),   --
  ccredit char(2),  --课程学分
  primary KEY (cno) --设置课程编号为主键
);
INSERT INTO "course" VALUES('1','数据库','5','4');
INSERT INTO "course" VALUES('2','数学','','2');
INSERT INTO "course" VALUES('3','信息系统','1','4');
INSERT INTO "course" VALUES('4','操作系统','6','3');
INSERT INTO "course" VALUES('5','数据结构','7','4');
INSERT INTO "course" VALUES('6','数据处理','','2');
INSERT INTO "course" VALUES('7','PASCAL语言','6','4');
/*创建student表*/
CREATE TABLE student(
sno char(5) not null,
sname char(20),
ssex char(2),
sage int,
sdept char(20)
);
INSERT INTO "student" VALUES('95001','李勇','男',20,'CS');
INSERT INTO "student" VALUES('95002','刘晨','女',19,'IS');
INSERT INTO "student" VALUES('95003','王敏','女',18,'MA');
INSERT INTO "student" VALUES('95004','张立','男',19,'IS');
INSERT INTO "student" VALUES('95005','王强','男',17,'IS');
INSERT INTO "student" VALUES('95015','张三','男',20,'CS');
INSERT INTO "student" VALUES('95019','李四','男',20,'CS');
INSERT INTO "student" VALUES('95020','陈冬','男',18,'IS');
/*创建sc表（学生课程成绩）*/
CREATE TABLE sc
(
sno char(5), --学号
cno char(2), --课程编号
grade int, --成绩
primary key(sno,cno) --主键设置为学号和课程编号
);
INSERT INTO "sc" VALUES('95001','1',65);
INSERT INTO "sc" VALUES('95001','2',88);
INSERT INTO "sc" VALUES('95001','3',57);
INSERT INTO "sc" VALUES('95001','4',79);
INSERT INTO "sc" VALUES('95001','5',45);
INSERT INTO "sc" VALUES('95001','6',90);
INSERT INTO "sc" VALUES('95001','7',81);
INSERT INTO "sc" VALUES('95002','2',90);
INSERT INTO "sc" VALUES('95002','3',80);
INSERT INTO "sc" VALUES('95002','4',55);
INSERT INTO "sc" VALUES('95003','2',0);
INSERT INTO "sc" VALUES('95019','2',66);
INSERT INTO "sc" VALUES('95020','1',NULL);
COMMIT;
/*COMMIT用来将事务写入数据库*/

然后，使用SELECT查询语句可以查看各个表中的内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
sqlite> select *
   ...> from student;
95001|李勇|男|20|CS
95002|刘晨|女|19|IS
95003|王敏|女|18|MA
95004|张立|男|19|IS
95005|王强|男|17|IS
95015|张三|男|20|CS
95019|李四|男|20|CS
95020|陈冬|男|18|IS
sqlite> select *
   ...> from course;
1|数据库|5|4
2|数学||2
3|信息系统|1|4
4|操作系统|6|3
5|数据结构|7|4
6|数据处理||2
7|PASCAL语言|6|4
sqlite> select *
   ...> from sc;
95001|1|65
95001|2|88
95001|3|57
95001|4|79
95001|5|45
95001|6|90
95001|7|81
95002|2|90
95002|3|80
95002|4|55
95003|2|0
95019|2|66
95020|1|
sqlite>

二、QLite Like 子句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：

-百分号 （%）代表零个、一个或多个数字或字符

-下划线 （_）代表一个单一的数字或字符，这些符号可以被组合使用

比如，查找student表中，所有姓李的学生

1
2
3
4
5
sqlite> SELECT sname
   ...> from student
   ...> WHERE sname LIKE '李%';
李勇
李四
语句 描述
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’ 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

三、SQLite Glob 子句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

-星号 （*）代表零个、一个或多个数字或字符
-问号 （?）代表一个单一的数字或字符
语句 描述
WHERE SALARY GLOB ‘200*’ 查找以 200 开头的任意值
WHERE SALARY GLOB ‘200‘ 查找任意位置包含 200 的任意值
WHERE SALARY GLOB ‘?00*’ 查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB ‘2??’ 查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB ‘*2’ 查找以 2 结尾的任意值
WHERE SALARY GLOB ‘?2*3’ 查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB ‘2???3’ 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

四、SQLite Limit 子句

SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。带有 LIMIT 子句的 SELECT 语句的基本语法如下：

1
2
3
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

1
2
3
SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行，如下一个实例所示

1
2
3
4
5
6
7
8
9
10
11
12
sqlite> SELECT *
   ...> FROM student
   ...> LIMIT 3;
95001|李勇|男|20|CS
95002|刘晨|女|19|IS
95003|王敏|女|18|MA
sqlite> SELECT grade
   ...> FROM sc
   ...> LIMIT 3 OFFSET 2;
57
79
45

五、SQLite Distinct 关键字

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

1
2
3
4
5
6
7
8
9
10
sqlite> SELECT ssex
   ...> FROM student;
男
女
女
男
男
男
男
男

用于消除重复记录的 DISTINCT 关键字的基本语法如下：

1
2
3
SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]
1
2
3
4
sqlite> SELECT DISTINCT ssex
   ...> FROM student;
男
女

六、SQLite JOIN 子句

SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。

SQL 定义了三种主要类型的连接：

  • 交叉连接 CROSS JOIN
  • 内连接 INNER JOIN
  • 外连接 OUTER JOIN

交叉连接 - CROSS JOIN

交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

下面是交叉连接（CROSS JOIN）的语法：

1
SELECT ... FROM table1 CROSS JOIN table2 ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
/*student表中的sname和course表中的cname cross join*/
sqlite> SELECT sname,cname
   ...> FROM student
   ...> CROSS JOIN course;
李勇|数据库
李勇|数学
李勇|信息系统
李勇|操作系统
李勇|数据结构
李勇|数据处理
李勇|PASCAL语言
刘晨|数据库
刘晨|数学
刘晨|信息系统
刘晨|操作系统
刘晨|数据结构
刘晨|数据处理
刘晨|PASCAL语言
王敏|数据库
王敏|数学
王敏|信息系统
王敏|操作系统
王敏|数据结构
王敏|数据处理
王敏|PASCAL语言
张立|数据库
张立|数学
张立|信息系统
张立|操作系统
张立|数据结构
张立|数据处理
张立|PASCAL语言
王强|数据库
王强|数学
王强|信息系统
王强|操作系统
王强|数据结构
王强|数据处理
王强|PASCAL语言
张三|数据库
张三|数学
张三|信息系统
张三|操作系统
张三|数据结构
张三|数据处理
张三|PASCAL语言
李四|数据库
李四|数学
李四|信息系统
李四|操作系统
李四|数据结构
李四|数据处理
李四|PASCAL语言
陈冬|数据库
陈冬|数学
陈冬|信息系统
陈冬|操作系统
陈冬|数据结构
陈冬|数据处理
陈冬|PASCAL语言

内连接 - INNER JOIN

内连接（INNER JOIN）根据连接条件结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接条件的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。

下面是内连接（INNER JOIN）的语法：

1
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

比如要查询有选修课学生的考试成绩（有的有选修课，有的没有）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sqlite> SELECT student.sno,sname,grade
   ...> FROM student
   ...> INNER JOIN sc
   ...> ON student.sno = sc.sno;
95001|李勇|65
95001|李勇|88
95001|李勇|57
95001|李勇|79
95001|李勇|45
95001|李勇|90
95001|李勇|81
95002|刘晨|90
95002|刘晨|80
95002|刘晨|55
95003|王敏|0
95019|李四|66
95020|陈冬|

如果还需要查找得到该学生的选修课程名，那么就需要连接多个表，还用到了AND运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sqlite> SELECT sname,student.sno,cname,grade
   ...> FROM sc INNER JOIN student,course
   ...> ON sc.cno = course.cno AND student.sno =  sc.sno;
李勇|95001|数据库|65
李勇|95001|数学|88
李勇|95001|信息系统|57
李勇|95001|操作系统|79
李勇|95001|数据结构|45
李勇|95001|数据处理|90
李勇|95001|PASCAL语言|81
刘晨|95002|数学|90
刘晨|95002|信息系统|80
刘晨|95002|操作系统|55
王敏|95003|数学|0
李四|95019|数学|66
陈冬|95020|数据库|

外连接 - OUTER JOIN

外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来（有的时候需要包含没有关联行的那些行），外连接的列使用 NULL 值，将它们附加到结果表中。

下面是左外连接（LEFT OUTER JOIN）的语法：

1
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

左外连接（LEFT OUTER JOIN）的左边的表是参照的主体，即根据连接条件，除了返回主体表与非主体中连接成功的行外，还返回主体表与非主体表连接不成功的记录。所以，左外连接中表的连接先后会导致不同的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sqlite> SELECT student.sno,sname,grade
   ...> FROM student LEFT OUTER JOIN sc
   ...> ON student.sno = sc.sno;
95001|李勇|65
95001|李勇|88
95001|李勇|57
95001|李勇|79
95001|李勇|45
95001|李勇|90
95001|李勇|81
95002|刘晨|90
95002|刘晨|80
95002|刘晨|55
95003|王敏|0
95004|张立|
95005|王强|
95015|张三|
95019|李四|66
95020|陈冬|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sqlite> SELECT student.sno,sname,grade
   ...> FROM sc LEFT OUTER JOIN student
   ...> ON student.sno = sc.sno;
95001|李勇|65
95001|李勇|88
95001|李勇|57
95001|李勇|79
95001|李勇|45
95001|李勇|90
95001|李勇|81
95002|刘晨|90
95002|刘晨|80
95002|刘晨|55
95003|王敏|0
95019|李四|66
95020|陈冬|

七、SQLite Having 子句

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

有必要说清楚分组GROUP BY与排序ORDER BY的区别，分组是将输出的结果以分组的形式呈现，而排序则是让输出结果按照顺序进行排列，换言之，分组的结果不一定是按照顺序排列的。where和having的功能也差不多，区别在于where是在数据分组前进行过滤，而having是在数据分组后进行过滤的。

下面是 HAVING 子句在 SELECT 查询中的位置：

1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
1
2
3
4
5
6
7
sqlite> SELECT *
   ...> FROM student
   ...> GROUP BY sage
   ...> HAVING sage > 18
   ...> ORDER BY sdept;
95019|李四|男|20|CS
95004|张立|男|19|IS

八、SQLite Delete 语句

SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。

带有 WHERE 子句的 DELETE 查询的基本语法如下：

1
2
DELETE FROM table_name
WHERE [condition];
------本文结束，欢迎收藏本站、分享、评论或联系作者！------
相关文章
点击查看
赠人玫瑰 手有余香
0%