在文章《嵌入式数据库sqlite3命令操作基础篇-增删改查，小白一文入门》的增删改查中讲解了如何实现sqlite3的基本操作。本文介绍了其他一些比较复杂的操作。比如where,orderby,having,like,function等用法。数据库准备好创建一个新的数据库，company.db。设计一个formemployee，内容如下：idnameagedepsalary1马云21market60002马化腾22tech70003李彦宏23trs86004张朝阳24trs60005罗永浩26tech89006王鑫25market4000根据上表，我们首先确定主键是id。建表命令如下：CREATETABLEemployee(idintegerprimarykey,nametext,ageinteger,deptext,salaryinteger);insertintoemployeevalues(1,'马云',21,'market',6000);insertintoemployeevalues(2,'马化腾',22,'tech',7000);insertintoemployeevalues(3,'李彦宏',23,'trs',8000);insertintoemployeevalues(4,'张朝阳',24,'trs',6000);insertintoemployeevalues(5,'罗永浩',26,'科技',8900);insertintoemployeevalues(6,'王鑫',25,'market',4000);insertintoemployeevalues(7,'一口',18,'market',4000);order子句我们可以使用order子句来实现记录排序：select*fromemployeeorderbyage;这里插一张图来说明Where子句和逻辑运算符SQLite的WHERE子句用于指定从一张表或多张表中获取数据的条件。如果满足给定条件，则返回表中的特定值，即true。您可以使用WHERE子句过滤记录以仅获取所需的记录。WHERE子句不仅可以用在SELECT语句中，还可以用在UPDATE、DELETE语句等中，我们将在后面的章节中学习。语法SQLite中带有WHERE子句的SELECT语句的基本语法如下：SELECTcolumn1,column2,columnNFROMtable_nameWHERE[condition]Instance也可以使用比较或逻辑运算符来指定条件，例如>,<,=,>=,<=、LIKE、NOT等。以下示例演示了SQLite逻辑运算符的用法。下面的SELECT语句列出所有AGE大于或等于25且薪水大于或等于65000.00的记录：SELECT*FROMEMPLOYEEWHEREAGE>=25ANDSALARY>=6500;insertpicturedescriptionhere下面的SELECT语句列出AGE大于等于25或salary大于等于65000.00的所有记录：SELECT*FROMEMPLOYEEWHEREAGE>=25ORSALARY>=65000;此处插入图片描述下面的SELECT语句列出了所有AGE不为NULL的记录，结果显示所有记录，表示没有记录的AGE等于NULL：SELECT*FROMEMPLOYEEWHEREAGEISNOTNULL;where子句也可以与like子句一起使用。下面的SELECT语句列出了所有NAME以'Ki'开头的记录，'Ki'后面的字符没有限制：SELECT*FROMEMPLOYEEWHERENAMELIKE'horse%';以下SELECT语句列出AGE值为22或25的记录Allrecords:SELECT*FROMEMPLOYEEWHEREAGEIN(22,25);在此处插入图片描述以下SELECT语句列出AGE值既不是25也不是27的所有记录：SELECT*FROMEMPLOYEEWHEREAGENOTIN(22,25);在此处插入图片描述以下SELECT语句列出AGE值介于22和25之间的所有记录：SELECT*FROMEMPLOYEEWHEREAGEBETWEEN22AND25;在此插入图片说明groupby子句GROUPBY语句用于组合聚合函数，根据一个或多个列对对结果集进行分组。例子：统计整个公司的工资总和：selectsum(salary)fromemployee;这里插入图片描述，统计各部门工资总和：selectdep,sum(salary)fromemployeegroupbydep；统计各部门工资总和，要求id值大于3selectdep,sum(salary)fromemployeewhereid>3groupbydep;where子句应该放在groupby之前。在这里插入一张图片来描述having子句。having子句是groupby的条件子句。where子句先出现，然后having子句被执行。HAVING子句中可以使用三个元素：常量、聚合函数和GROUPBY子句中指定的列名（聚合构造）。使用having时，必须和groupby一起使用。使用groupby并不总是有having（只是一个用于过滤条件）比如要统计每个部门的工资总和要求id值大于3selectdep,sum(salary)fromemployeewhereid>3groupbydephavingsum(salary)>5000;要查找重复记录，我们首先插入具有相同名称的记录。inserintoemployeevalues(8,'一口',19,'market',5000);selectid,namefromemployeegroupbynamehavingcount(*)>1;您可以看到结果显示具有相同名称的重复记录。显示多个同名项selectid,name,dep,salaryfromemployeegroupbynamehavingcount(*)>1;显示表中的所有记录selectcount(*)fromemployee;显示selectdep,avg(salary)fromemployeegroupbydep的所有记录数；显示每个dep下组的平均值select*fromemployeewhereid>3interselectselect*fromemployeewhereid<9;显示id>3&&id<9的所有记录：即从4到8的记录select*fromemployeewhereid>3unionallselect*fromemployeewhereid<9;显示所有大于3小于9的记录对，union（如果有相同的会重复显示）select*fromstudentnewwhereid>3unionallselect*fromstudentnewwhereid<9;显示大于9的记录select*fromemployeewhereid>3unionallselect*fromstudentnewwhereid<6;显示大于6的记录，（继续上一条对比）select*fromemployeewheresalary=(selectsalaryfromemployeeorderbysalarydesclimit1);select*fromemployeewheresalary=(selectmax(salary)fromemployee);显示所有工资最高的员工的记录selectname,max(salary)fromemployee;在此处插入图片说明，以显示表中的名称不是同一名员工;selectdistinctnamefromemployee;显示所有员工的姓名;selectallnamefromemployee;functionSQLite有许多用于处理字符串或数字数据的内置函数。下面列出了一些有用的SQLite内置函数，所有函数都不区分大小写，这意味着您可以使用小写或大写或混合使用这些函数。更多功能说明可以参考SQLite官方文档。下面介绍一些常用的函数序号函数&说明1.COUNTSQLiteCOUNT聚合函数用于统计数据库表中的行数。2.MAXSQLiteMAX聚合函数允许我们选择一列的最大值。3.MINSQLiteMIN聚合函数允许我们选择一列的最小值。4.AVGSQLiteAVG聚合函数计算一列的平均值。5.SUMSQLiteSUM聚合函数允许计算数字列的总和。6.RANDOMSQLiteRANDOM函数返回一个介于-9223372036854775808和+9223372036854775807之间的伪随机整数。7.ABSSQLiteABS函数返回数字参数的绝对值。8.UPPERSQLiteUPPER函数将字符串转换为大写字母。9.LOWERSQLiteLOWER函数将字符串转换为小写字母。10.LENGTHSQLiteLENGTH函数返回字符串的长度。11.sqlite_versionSQLitesqlite_version函数返回SQLite库的版本。SQLiteCOUNT函数SQLiteCOUNT聚合函数用于计算数据库表中的行数。下面是一个例子：SELECTcount(*)FROMEMPLOYEE;执行结果：Insertpicturehere说明SQLiteMAX函数SQLiteMAX聚合函数允许我们选择一列的最大值。下面是一个例子：SELECTmax(salary)FROMEMPLOYEE;执行结果：SQLiteMIN函数SQLiteMIN聚合函数允许我们选择一列的最小值。下面是一个例子：SELECTmin(salary)FROMEMPLOYEE;执行结果：Insertpicturehere说明SQLiteAVG函数SQLiteAVG聚合函数计算某列的平均值。下面是一个例子：SELECTavg(salary)FROMEMPLOYEE;执行结果：SQLiteSUM函数SQLiteSUM聚合函数允许您计算数字列的总和。下面是一个例子：SELECTsum(salary)FROMEMPLOYEE;执行结果：在此处插入图片说明SQLiteRANDOM函数SQLiteRANDOM函数返回一个介于-9223372036854775808和+9223372036854775807之间的伪随机整数。下面是一个例子：SELECTRandom()ASRandom;执行结果：Insertpicturehere说明SQLiteABS函数SQLiteABS函数返回数字参数的绝对值。下面是一个例子：SELECTabs(5),abs(-15),abs(NULL),abs(0),abs("ABC");执行结果：insertpicturehere描述SQLiteUPPER函数SQLiteUPPER函数将字符串转换为大写字母。下面是一个例子：insertintoemployeevalues(9,'yikoulinux',22,'market',8000);SELECTupper(姓名)FROMEMPLYEE;执行结果：SQLiteLOWER函数SQLiteLOWER函数将字符串转换为小写字母。下面是一个例子：SELECTlower(name)FROMEMPLOYEE;执行结果：SQLiteLENGTH函数SQLiteLENGTH函数返回字符串的长度。下面是一个例子：SELECTname,length(name)FROMEMPLOYEE;执行结果：SQLitesqlite_version函数SQLitesqlite_version函数返回SQLite库的版本。下面是一个例子：SELECTsqlite_version()AS'SQLiteVersion';执行结果：SQLite时间/日期函数：datetime()datetime()生成日期时间无参数表示获取当前时间日期**selectdatetime();insertpicturedescriptionhere如果有字符串参数，将字符串转为日期selectdatetime('2012-01-0712:01:30');2012-01-0712:01:30selectdate('2019-09-09','+1day','+1year');2013-01-09selectdatetime('2019-09-0900:20:00','+1hour','-12minute');2019-09-0901:08:00selectdatetime('now','startofyear');2020-01-0100:00:00selectdatetime('now','startofmonth');2020-09-0100:00:00selectdatetime('now','startofday');2020-09-1300:00:00selectdatetime('now','localtime');result:2020-09-1220:26:35date()date()用于生成日期selectdate('2019-09-0912:01:30');2019-09-09selectdate('now','startofyear');2020-01-01selectdate('2019-09-09','+1month');2019-10-09time()time()用于生成时间。selecttime();03:28:49selecttime('23:18:59');23:18:59selecttime('23:18:59','startofday');00:00:00可以用在时间/日期函数使用以下格式的字符串作为参数：YYYY-MM-DDYYYY-MM-DDHH:MMYYYY-MM-DDHH:MM:SSYYYY-MM-DDHH:MM:SS.SSSHH:MMHH:MM:SSHH:MM:SS.SSS现在现在是当前时间。日期不能正确比较大小，会按字符串比较。默认日期格式dd-mm-yyyystrftime()strftime()格式化以上三个函数生成的日期和时间。strftime()函数可以将YYYY-MM-DDHH:MM:SS格式的日期字符串转换成其他形式的字符串。strftime(format,date/time,modifier,modifier,...)它可以使用以下符号格式化日期和时间：%ddayofthemonth,01-31%ffractionalseconds,SS.SSS%Hhour,00-23%j计算年中的第几天，001-366%m月，00-12%M分钟，00-59%s从1970年1月1日到现在的秒数%S秒，00-59%wweek,0-6(0isSunday)%W计算属于年的某一天的编号,01-53%Y年,YYYY%%%signselectstrftime('%Y.%m.%d%H:%M:%S','now');2020.09.1303:32:49selectstrftime('%Y.%m.%d%H:%M:%S','now','localtime');2020.09.1220:33:24SELECTLIKE用法在SQL结构化查询语言中，LIKE语句起着至关重要的作用。语法LIKE语句的语法格式为：select*from表名wherefieldnamelike对应的值（substring），主要针对字符型字段，其作用是在字符型中检索对应的子串字段列。A):%任何包含零个或多个字符的字符串：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])中的任何单个字符：LIKE'[CK]ars[eo]n'将搜索以下字符串：Carsen、Karsen、Carson和Karson（例如卡森）。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:[!]Exclude只代表单个字符Example下面举个例子来说明：Example1，查询名称字段包含单词“Ming”。select*fromtable1wherenamelike'%明%'例2，查询的name字段以“李”字开头。select*fromtable1wherenamelike'李*'例3，查询姓名字段包含数字。select*fromtable1wherenamelike'%[0-9]%'例4，查询名称字段包含小写字母。select*fromtable1wherenamelike'%[a-z]%'例5，查询名称字段不包含数字。select*fromtable1wherenamelike'%[!0-9]%'在上面的例子中可以列出什么值是显而易见的。但在这里，我们主要关注通配符“”和“%”的区别。很多朋友会问，为什么我在上面的查询中用“%”而不是“”来表示所有的字符呢？我们来看下面例子的结果：select*fromtable1wherenamelike'*明*'select*fromtable1wherenamelike'%明%'会看到前面的语句列出了所有的记录，后面的记录列出了包含“明”的记录"在名称字段中，所以当我们让一个字符字段包含一个子字符串时，最好使用"%"而不是""。使用“”时，只在开头或只在结尾，不能用任何字符两端的“*”代替。SQLITE数据库的操作暂时结束。虽然是轻量级数据库，但是数据库操作的基本思想是一样的。基本上就是什么都学。在下一章中，我们将学习如何通过C语言程序来操作数据库。本文转载自微信公众号“一口Linu”，可通过以下二维码关注。转载本文请联系艺口丽奴公众号。
