SQLite简介
SQLite是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库,这意味着与其他数据库不一样,您不需要在系统中配置。就像其他数据库,SQLite 引擎不是一个独立的进程,可以按应用程序需求进行静态或动态连接SQLite 直接访问其存储文件。
SQLite 命令
本节介绍SQLite中常用的命令,这些命令被称为 SQLite 的点命令,这些命令的不同之处在于它们不以分号;结束。
首先在终端输入sqlite3命令,就会进入sqlite3操作界面:
如果需要获取可用的点命令清单,可以输入.help,如下:
其中比较常用的命令有:
.exit / .quit | 退出SQLite |
.header(s) on | off | 打开或关闭头部显示 |
.mode MODE | 设置输出模式,MODE可以是以下(仅常用命令)之一:
|
.nullvalue STRING | 在NULL值的地方输出STRING字符串 |
.output | 发送输出 |
.width NUM | 以“column”模式设置列宽度 |
SQLite 语法
大小写敏感性
SQLite不区分大小写,要特别注意。但也有一些命令是大小写敏感的,如GLOB和glob在 SQLite 的语句中有不同的含义。
注释
SQLite注释是附加的注释,可以在SQLite代码中添加注释以增加其可读性,可以出现在任何空白处,包括在表达式内和其他SQL语句的中间,但它们不能嵌套。
SQL 注释以两个连续的 "-" 字符(ASCII 0x2d)开始,并扩展至下一个换行符(ASCII 0x0a)或直到输入结束,以先到者为准。也可以使用 C 风格的注释,以 "/*" 开始,并扩展至下一个 "*/" 字符对或直到输入结束,以先到者为准。SQLite的注释可以跨越多行。
SQLite 数据类型
SQLite数据类型是一个用来指定任何对象的数据类型的属性。SQLite中的每一列,每个变量和表达式都有相关的数据类型。可以在创建表的同时使用这些数据类型。SQLite使用一个更普遍的动态类型系统。在SQLite中,值的数据类型与值本身是相关的,而不是与它的容器相关。
SQLite存储类
每个存储在SQLite数据库中的值都具有以下存储类之一:
存储类 | 描述 |
NULL | 值是一个NULL值 |
INTERGER | 值是一个带符号的整数,根据值的大小存储在1~8个字节内 |
REAL | 值是一个浮点值,存储为8字节的IEEE浮点数字 |
TEXT | 值是一个文本字符串,使用数据库编码存储 |
BLOB | 值是一个blob数据,完全根据它的输入存储 |
SQLite的存储类稍微比数据类型更普遍,INTERGER存储类,例如,包含6中不同长度的整数数据类型。
SQLite 创建数据库
语法:
sqlite3命令的基本语法如下:
sqlite3 DataBaseName.db
通常情况下,数据库名称应该是唯一的,另外也可以使用.open来建立新的数据库文件。
sqlite>.open DataBaseName.db
示例如下:
上图中使用.open命令成功创建数据库文件test1.db,test2.db。
SQLite 创建表
SQLite的CREATE TABLE语句用于在任何给定的数据库创建一个新表。创建基本表,涉及到命名表、定义列及每一列的数据类型。
CREATE TABLE语句的基本语法如下:
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
CREATE TABLE是告诉数据库系统创建一个新表的关键字,CREATE TABLE语句后跟着表的唯一的名称或标识。
上图中使用.open语句创建了students.db数据库,然后创建了stu_info表,其中包含了学生编号(no,唯一标识符,不可以重复)、姓名(name)、性别(sex)、年龄(age)和班级(class)信息。
使用.tables可以查看已经创建的表,.schema可以查看表的详细信息。
下面使用同样的方法创建了课程表(school_timetable)和成绩表(scores)。
SQLite Insert 语句
SQLite的INSERT INTO语句用于向数据库的某个表中添加新的数据行。
INSERT INTO语句有两种基本语法,如下所示:
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
在这里,column1, column2,...columnN 是要插入数据的表中的列的名称。
如果要为表中的所有列添加值,也可以不需要在SQLite查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite的INSERT INTO语法如下:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
SQLite Select语句
SQLite的SELECT语句用于从SQLite数据库表中获取数据,以结果表的形式返回数据。这些结果表也被称为结果集。
SQLite中SELECT语句的基本用法如下:
SELECT column1, column2, columnN FROM table_name;
在这里,column1, column2...是表的字段,它们的值即是需要获取的。如果想获取所有可用的字段,那么可以使用下面的语法:
SELECT * FROM table_name;
示例如下:
上图中使用insert语句向表stu_info中插入了一条学生信息,然后使用select获取数据。
上途中使用.headers on命令打开了头部显示,然后通过.mode column和.width NUM命令设置了表的宽度。
接下来通过同样的步骤添加了其他学生的信息,如下:
接下来向课程表以及成绩表中添加了信息:
SQLite Delete语句
SQLite的DELETE查询用于删除表中已有的记录。可以使用带有WHERE子句的DELETE查询来删除选定行,否则所有的记录都会被删除。
带有WHERE子句的DELETE查询的基本语法如下:
DELETE FROM table_name
WHERE [condition];
例如删除成绩表中学号(ID)为3且课程编号也为3的这条信息:
SQLite Where子句
SQLite的WHERE子句用于指定从一个表或多个表中获取数据的条件。如果满足给定的条件,即为真(true)时,则从表中返回特定的值。您可以使用 WHERE 子句来过滤记录,只获取需要的记录。WHERE子句不仅可用在 SELECT 语句中,它也可用在UPDATE、DELETE等语句中。
SQLite的带有WHERE子句的SELECT语句的基本语法如下:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
例如在成绩表中查找学号(ID)为1的同学然后输出他的成绩:
在学生信息表中查找姓名中带有熊的同学然后输出他的所有信息:
在学生信息表中查找所有年龄在18~21之间(包含18,21)的同学,并输出他们的姓名和性别信息:
SQLite Join
SQLite的Join子句用于结合两个或多个数据库中表的记录。JOIN是一种通过共同值来结合两个表中字段的手段。
SQL定义了三种主要类型的连接:
-
交叉连接 - CROSS JOIN
-
内连接 - INNER JOIN
-
外连接 - OUTER JOIN
交叉连接 - CROSS JOIN
交叉连接(CROSS JOIN)把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有x和y行,则结果表有x*y行。由于交叉连接(CROSS JOIN)有可能产生非常大的表,使用时必须谨慎,只在适当的时候使用它们。
内连接 - INNER JOIN
内连接(INNER JOIN)根据连接谓词结合两个表(table1 和 table2)的列值来创建一个新的结果表。查询会把table1中的每一行与table2中的每一行进行比较,找到所有满足连接谓词的行的匹配对。当满足连接谓词时,A和B行的每个匹配对的列值会合并成一个结果行。内连接(INNER JOIN)是最常见的连接类型,是默认的连接类型。INNER关键字是可选的。
外连接 - OUTER JOIN
外连接(OUTER JOIN)是内连接(INNER JOIN)的扩展。虽然SQL标准定义了三种类型的外连接:LEFT、RIGHT、FULL,但 SQLite只支持左外连接(LEFT OUTER JOIN)。外连接(OUTER JOIN)声明条件的方法与内连接(INNER JOIN)是相同的,使用ON、USING或NATURAL关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成,外连接(OUTER JOIN)将从一个或两个表中任何未连接的行合并进来,外连接的列使用 NULL 值,将它们附加到结果表中。
这是一个交叉连接,结合学生信息表的新名和课程表中的课程和学分创建了一个新表。
这是一个内连接,查找成绩表中的学生ID与学生信息表中的学生编号(no)相同的行进行匹配,当满足匹配条件的时候,就会生成一个结果行,然后得到一个新表。其中order by score desc语句是将新表中的行按照成绩高低(降序)进行排序。
这是一个外连接,与内连接相同的方式进行查找,然后输出符合条件的信息。
SQLite 删除表
SQLite的DROP TABLE语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。DROP TABLE语句的基本语法如下。可以选择指定带有表名的数据库名称,如下所示:
DROP TABLE database_name.table_name;
下面使用该语句删除当前数据库中的课程表(school_timetable),操作完成后只剩下成绩表和学生信息表。
至此,SQLite的基本操作介绍完毕。