嵌入式数据库Sqlite3之如何使用子句和函数

共计 8017 个字符，预计需要花费 21 分钟才能阅读完成。

这篇文章主要讲解了“嵌入式数据库 Sqlite3 之如何使用子句和函数”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“嵌入式数据库 Sqlite3 之如何使用子句和函数”吧！

数据库准备

新建数据库，company.db。设计一个表格 employee，内容如下：

idnameagedepsalary1 马云 21market60002 马化腾 22tech70003 李彦宏 23trs86004 张朝阳 24trs60005 罗永浩 26tech89006 王欣 25market4000

根据上述表格，我们首先确定主键是 id。创建表格命令如下：

CREATE TABLE employee(id integer primary key, name text,age integer , dep text, salary integer);

insert into employee values (1, 马云 ,21, market ,6000); insert into employee values (2, 马化腾 ,22, tech ,7000); insert into employee values (3, 李彦宏 ,23, trs ,8000); insert into employee values (4, 张朝阳 ,24, trs ,6000); insert into employee values (5, 罗永浩 ,26, tech ,8900); insert into employee values (6, 王欣 ,25, market ,4000); insert into employee values (7, 一口 ,18, market ,4000);

order 子句

我们可以使用 order 子句实现对记录的排序：

select * from employee order by age;

在这里插入图片描述

Where 子句与逻辑运算符

SQLite 的 WHERE 子句用于指定从一个表或多个表中获取数据的条件。如果满足给定的条件，即为真 (true) 时，则从表中返回特定的值。您可以使用  WHERE 子句来过滤记录，只获取需要的记录。WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE   语句中，等等，这些我们将在随后的章节中学习到。

语法

SQLite 的带有 WHERE 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN FROM table_name WHERE [condition]

实例

您还可以使用比较或逻辑运算符指定条件，比如、、=、=, = ,LIKE、NOT，等等。

下面的实例演示了 SQLite 逻辑运算符的用法。

下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：

SELECT * FROM EMPLOYEE WHERE AGE  = 25 AND SALARY  = 6500;

在这里插入图片描述

下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：

SELECT * FROM EMPLOYEE WHERE AGE = 25 OR SALARY = 65000;

在这里插入图片描述

下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录，结果显示所有的记录，意味着没有一个记录的 AGE 等于 NULL：

SELECT * FROM EMPLOYEE WHERE AGE IS NOT NULL;

where 子句还可以配合 like 子句一起使用。下面的 SELECT 语句列出了 NAME 以   Ki 开始的所有记录，Ki 之后的字符不做限制：

SELECT * FROM EMPLOYEE WHERE NAME LIKE  马 %

下面的 SELECT 语句列出了 AGE 的值为 22 或 25 的所有记录：

SELECT * FROM EMPLOYEE WHERE AGE IN ( 22, 25 );

在这里插入图片描述

下面的 SELECT 语句列出了 AGE 的值既不是 25 也不是 27 的所有记录：

SELECT * FROM EMPLOYEE WHERE AGE NOT IN ( 22, 25 );

在这里插入图片描述

下面的 SELECT 语句列出了 AGE 的值在 22 与 25 之间的所有记录：

SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 22 AND 25;

在这里插入图片描述

group by 子句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。举例：

统计整个公司工资总和：

select sum(salary) from employee;

在这里插入图片描述

统计每个部门的工资总和：

select dep, sum(salary) from employee group by dep;

统计各部门的工资总和并且要求 id 值大于 3

select dep, sum(salary) from employee where id 3 group by dep; where 子句要放在 group by 的前面。

在这里插入图片描述

having 子句

having 子句是 group by 的条件子句，where 子句先发生，然后才是 having   子句执行。HAVING 子句中能够使用三种要素：常数，聚合函数，GROUP BY 子句中指定的列名(聚合建)，用 having 就一定要和 group by 连用，  用 group by 不一有 having(它只是一个筛选条件用的)

举例

统计各部门的工资总和并且要求 id 值大于 3

select dep, sum(salary) from employee where id 3 group by dep having sum(salary) 5000;

查找重复记录的方法

我们先插入依据名字相同的记录。

insert into employee values (8, 一口 ,19, market ,5000); select id,name from employee group by name having count(*)   1;

可以看到，结果显示了相同名字的重复记录。

显示名字相同的多项

select id,name,dep,salary from employee group by name having count(*)   1;

显示 table 中所有的记录

select count(*) from employee;

显示所有记录的个数

select dep,avg(salary) from employee group by dep;

显示 dep 下每一组的平均值

select * from employee where id   3 intersect select * from employee where id   9;

显示 id 3 id 9 的所有记录：即 4 – 8 的记录

select * from employee where id   3 union all select * from employee where id   9;

显示所有的大于 3 并且小于 9 的，并集(如果有相同的，会重复显示)

select * from studentnew where id   3 union all select * from studentnew where id   9;

显示大于 9 的记录

select * from employee where id   3 union all select * from studentnew where id   6;

显示大于 6 的记录，(与上一个进行比较)

select *from employee where salary= (select salary from employee order by salary desc limit 1); select *from employee where salary= (select max(salary) from employee );

显示最高工资的所有员工的记录

select name,max(salary) from employee;

在这里插入图片描述

显示 table 中名字不相同的员工;

select distinct name from employee;

显示所有员工的名字;

select all name from employee;

函数

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite   内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多函数的说明，可以参考 SQLite   的官方文档，下面列举常用的一些函数

序号函数 描述 1.COUNTSQLite COUNT 聚集函数是用来计算一个数据库表中的行数。2.MAXSQLite MAX 聚合函数允许我们选择某列的最大值。3.MINSQLite MIN 聚合函数允许我们选择某列的最小值。4.AVGSQLite AVG 聚合函数计算某列的平均值。5.SUMSQLite SUM 聚合函数允许为一个数值列计算总和。6.RANDOMSQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。7.ABSSQLite ABS 函数返回数值参数的绝对值。8.UPPERSQLite UPPER 函数把字符串转换为大写字母。9.LOWERSQLite LOWER 函数把字符串转换为小写字母。10.LENGTHSQLite LENGTH 函数返回字符串的长度。11.sqlite_versionSQLite sqlite_version 函数返回 SQLite 库的版本。

SQLite COUNT 函数

SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：

SELECT count(*) FROM EMPLOYEE ;

执行结果：

在这里插入图片描述

SQLite MAX 函数

SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例：

SELECT max(salary) FROM EMPLOYEE ;

执行结果：

SQLite MIN 函数

SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例：

SELECT min(salary) FROM EMPLOYEE ;

执行结果：

在这里插入图片描述

SQLite AVG 函数

SQLite AVG 聚合函数计算某列的平均值。下面是实例：

SELECT avg(salary) FROM EMPLOYEE ;

执行结果：

SQLite SUM 函数

SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例：

SELECT sum(salary) FROM EMPLOYEE ;

执行结果：

在这里插入图片描述

SQLite RANDOM 函数

SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807   之间的伪随机整数。下面是实例：

SELECT random() AS Random;

执行结果：

在这里插入图片描述

SQLite ABS 函数

SQLite ABS 函数返回数值参数的绝对值。下面是实例：

SELECT abs(5), abs(-15), abs(NULL), abs(0), abs(ABC

执行结果：

在这里插入图片描述

SQLite UPPER 函数

SQLite UPPER 函数把字符串转换为大写字母。下面是实例：

insert into employee values (9, yikoulinux ,22, market ,8000); SELECT upper(name) FROM EMPLOYEE ;

执行结果：

SQLite LOWER 函数

SQLite LOWER 函数把字符串转换为小写字母。下面是实例：

SELECT lower(name) FROM EMPLOYEE ;

执行结果：

SQLite LENGTH 函数

SQLite LENGTH 函数返回字符串的长度。下面是实例：

SELECT name, length(name) FROM EMPLOYEE ;

执行结果：

SQLite sqlite_version 函数

SQLite sqlite_version 函数返回 SQLite 库的版本。下面是实例：

SELECT sqlite_version() AS  SQLite Version

执行结果：

SQLite 时间 / 日期函数：

datetime()

datetime() 产生日期和时间 无参数表示获得当前时间和日期 **

select datetime();

在这里插入图片描述

有字符串参数则把字符串转换成日期

select datetime(2012-01-07 12:01:30

2012-01-07 12:01:30

select date(2019-09-09 , +1 day , +1 year

2013-01-09

select datetime(2019-09-09 00:20:00 , +1 hour , -12 minute

2019-09-09 01:08:00

select datetime(now , start of year

2020-01-01 00:00:00

select datetime(now , start of month

2020-09-01 00:00:00

select datetime(now , start of day

2020-09-13 00:00:00

select datetime(now , localtime

结果：2020-09-12 20:26:35

date()

date()用于产生日期

select date(2019-09-09 12:01:30

2019-09-09

select date(now , start of year

2020-01-01

select date(2019-09-09 , +1 month

2019-10-09

time()

time() 用于产生时间。

select time();

03:28:49

select time(23:18:59

23:18:59

select time(23:18:59, start of day 

00:00:00

在时间 / 日期函数里可以使用如下格式的字符串作为参数：

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now

其中 now 是产生现在的时间。

日期不能正确比较大小, 会按字符串比较，日期默认格式 dd-mm-yyyy

strftime()

strftime() 对以上三个函数产生的日期和时间进行格式化。

strftime()函数可以把 YYYY-MM-DD HH:MM:SS 格式的日期字符串转换成其它形式的字符串。strftime(格式, 日期 / 时间,   修正符, 修正符, hellip;)

它可以用以下的符号对日期和时间进行格式化：

%d  在该月中的第几天, 01-31 %f  小数形式的秒，SS.SSS %H  小时, 00-23 %j  算出某一天是该年的第几天，001-366 %m  月份，00-12 %M  分钟, 00-59 %s  从 1970 年 1 月 1 日到现在的秒数  %S  秒, 00-59 %w  星期, 0-6 (0 是星期天) %W  算出某一天属于该年的第几周, 01-53 %Y  年, YYYY %%  百分号

select strftime(%Y.%m.%d %H:%M:%S , now

2020.09.13 03:32:49

select strftime(%Y.%m.%d %H:%M:%S , now, localtime 

2020.09.12 20:33:24

SELECT LIKE 用法

在 SQL 结构化查询语言中，LIKE 语句有着至关重要的作用。

语法

LIKE 语句的语法格式是：

select * from  表名  where  字段名  like  对应值（子串），

它主要是针对字符型字段的，它的作用是在一个字符型字段列中检索包含对应子串的。

A):% 包含零个或多个字符的任意字符串：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

LIKE Mc% 将搜索以字母 Mc 开头的所有字符串(如 McBadden)。

LIKE %inger 将搜索以字母 inger 结尾的所有字符串(如 Ringer、Stringer)。

LIKE %en% 将搜索在任何位置包含字母 en 的所有字符串(如 Bennet、Green、McBadden)。

B:_(下划线) 任何单个字符：LIKE _heryl 将搜索以字母 heryl 结尾的所有六个字母的名称(如 Cheryl、Sheryl)。

C：[] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

LIKE [CK]ars[eo]n 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson(如 Carson)。

LIKE [M-Z]inger 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称(如 Ringer)。

D：[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：LIKE M[^c]% 将搜索以字母 M   开头，并且第二个字母不是 c 的所有名称(如 MacFeather)。

E：* 它同于 DOS 命令中的通配符，代表多个字符：c* c 代表 cc,cBc,cbc,cabdfec 等多个字符。

F：? 同于 DOS 命令中的? 通配符，代表单个字符 :b?b 代表 brb,bFb 等

G：# 大致同上，不同的是代只能代表单个数字。k#k 代表 k1k,k8k,k0k。

F：[!] 排除 它只代表单个字符

举例

下面我们来举例说明一下：

例 1，查询 name 字段中包含有“明”字的。

select * from table1 where name like  % 明 %

例 2，查询 name 字段中以“李”字开头。

select * from table1 where name like  李 *

例 3，查询 name 字段中含有数字的。

select * from table1 where name like  %[0-9]%

例 4，查询 name 字段中含有小写字母的。

select * from table1 where name like  %[a-z]%

例 5，查询 name 字段中不含有数字的。

select * from table1 where name like  %[!0-9]%

以上例子能列出什么值来显而易见。但在这里，我们着重要说明的是通配符“”与“%”的区别。很多朋友会问，为什么我在以上查询时有个别的表示所有字符的时候用 % 而不用“”? 先看看下面的例子能分别出现什么结果：

select * from table1 where name like  * 明 *  select * from table1 where name like  % 明 %

大家会看到，前一条语句列出来的是所有的记录，而后一条记录列出来的是 name 字段中含有“明”的记录，所以说，当我们作字符型字段包含一个子串的查询时最好采用“%”而不用“”, 用“”的时候只在开头或者只在结尾时，而不能两端全由“*”代替任意字符的情况下。

感谢各位的阅读，以上就是“嵌入式数据库 Sqlite3 之如何使用子句和函数”的内容了，经过本文的学习后，相信大家对嵌入式数据库 Sqlite3 之如何使用子句和函数这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！