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 创建数据库

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

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

SQLite 创建表的语法为

1
2
CREATE TABLE table_name (
);

这时数据库student.db还是空的,我们在数据库中新建三个表:coursescstudent,在当前目录下新建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];
觉得还不错?帮我赞助点域名费吧:)