SQLite 函数

本章我们将介绍 SQLite 内置函数。 SQLite 数据库中有三种类型的函数:核心函数,聚合函数和日期&时间函数。

我们将介绍每组 SQLite 函数中的一些函数。

SQLite 核心函数

在这一组中,我们具有各种函数。 有些是数字函数,有些是文本处理。 其他人则做一些非常具体的事情。

sqlite> SELECT sqlite_version() AS 'SQLite Version';
SQLite Version
--------------
3.16.2

sqlite_version()函数返回 SQLite 库的版本。

sqlite> SELECT random() AS Random;
Random             
-------------------
1056892254869386643   

random()函数返回-9223372036854775808 和+9223372036854775807 之间的伪随机整数。

sqlite> SELECT abs(11), abs(-5), abs(0), abs(NULL);
abs(11)             abs(-5)      abs(0)      abs(NULL) 
------------------  -----------  ----------  ----------
11                  5            0           NULL  

abs()函数返回数字参数的绝对值。

sqlite> SELECT max(Price), min(Price) FROM Cars;
max(Price)  min(Price)
----------  ----------
350000      9000  

在我们的示例中,max()min()函数从Cars表中返回最昂贵和最便宜的汽车。

sqlite> .width 18
sqlite> SELECT upper(Name) AS 'Names in capitals' FROM Friends;
Names in capitals 
------------------
JANE              
THOMAS            
FRANK             
ELISABETH         
MARY              
LUCY              
JACK  

upper()函数将字符转换为大写字母。

sqlite> SELECT lower(Name) AS 'Names in lowercase' FROM Friends
   ...> WHERE Id IN (1, 2, 3);
Names in lowercase
------------------
jane              
thomas            
frank  

使用lower()函数,我们将前三行的名称更改为小写字母。

sqlite> SELECT length('ZetCode');
length('ZetCode') 
------------------
7 

length()函数返回字符串的长度。

sqlite> SELECT total_changes() AS 'Total changes';
Total changes
-------------
3    

自打开当前数据库连接以来,total_changes()函数返回由INSERTUPDATEDELETE语句引起的行更改数。 在当前的数据库连接中,我们已经完成了三个INSERT语句,所以总更改等于三个。

sqlite> .width 5
sqlite> SELECT sqlite_compileoption_used('SQLITE_DEFAULT_FOREIGN_KEYS') AS 'FK';
FK   
-----
0  

sqlite_compileoption_used()函数返回一个布尔值,具体取决于在构建过程中是否使用了该选项。 在我们的例子中,我们检查默认情况下是否强制执行FOREIGN KEY约束。 该函数返回 0,这意味着默认情况下不强制执行约束。 我们使用PRAGMA语句进行更改。 (PRAGMA 外键= 1;)

sqlite> SELECT typeof(12), typeof('ZetCode'), typeof(33.2), typeof(NULL), 
   ...> typeof(x'345edb');
typeof(12)    typeof('ZetCode')   typeof(33.2)  typeof(NULL)  typeof(x'345edb')
------------  ------------------  ------------  ------------  -----------------
integer       text                real          null          blob  

typeof()函数返回参数的数据类型。

SQLite 聚合函数

通过聚合函数,我们可以获得一些统计数据。 带有单个参数的聚合函数可以在DISTINCT关键字之后。 在这种情况下,重复元素将在传递到聚合函数之前被过滤。

我们总结一下Cars表中的内容。

sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600 

请注意,没有重复的记录。

sqlite> SELECT count(*) AS '# of cars' FROM Cars;
# of cars 
----------
8     

count()函数返回表中的行数-共有八辆汽车。

Orders表中,我们确实有重复的客户记录。

sqlite> SELECT * FROM Orders;
Id          OrderPrice  Customer  
----------  ----------  ----------
1           1200        Williamson
2           200         Robertson 
3           40          Robertson 
4           1640        Smith     
5           100         Robertson 
6           50          Williamson
7           150         Smith     
8           250         Smith     
9           840         Brown     
10          440         Black     
11          20          Brown    

从逻辑上讲,每个客户可以下多个订单。 我们如何计算订单数量以及如何计算客户数量?

sqlite> SELECT count(Customer) AS '# of orders'  FROM Orders;
# of orders
-----------
11   

该 SQL 语句返回订单数。 要计算唯一客户的数量,我们必须利用DISTINCT子句。

sqlite> SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders;
# of customers
--------------
5   

Orders表中有 5 个客户。 他们下了 11 个订单。

接下来,我们将演示count(*)count(ColumnName)函数之间的区别。 区别在于它们处理NULL值的方式。

sqlite> .nullvalue NULL

首先,我们更改sqlite3显示NULL值的方式。 默认情况下,NULL值显示为空字符串。

sqlite> CREATE TABLE TESTING(Id INTEGER);
sqlite> INSERT INTO Testing VALUES (1), (2), (3), (NULL), (NULL);

在这里,我们创建具有 3 个数字值和 2 个NULL值的表Testing

sqlite> SELECT last_insert_rowid();
5    

last_insert_rowid()函数返回最后插入的行的 ID。

sqlite> SELECT count(*) AS '# of rows' FROM Testing;
# of rows 
----------
5  

count(*)返回表中的行数。 它考虑了 NULL 值。

sqlite> SELECT count(Id) AS '# of non NULL values' FROM Testing;
# of non NULL values
--------------------
3 

count(Id)仅计算非NULL值。

sqlite> SELECT avg(Price) AS 'Average price' FROM Cars;
Average price
-------------
72721.125   

avg()函数返回所有非NULL记录的平均值。 在我们的示例中,我们在Cars表中显示了汽车的平均价格。

最后,我们提到sum()函数。 它将所有非NULL值相加。

sqlite> SELECT sum(OrderPrice) AS Sum FROM Orders;
Sum     
--------
4930   

在这里,我们计算客户下的订单数量。

SQLite 日期和时间函数

SQLite 具有处理日期和时间的函数。 这些函数采用各种时间字符串,修饰符和格式。

sqlite> .header OFF
sqlite> SELECT date('now');
2014-11-17    

带有now字符串的date()函数返回当前日期。

sqlite> SELECT datetime('now');
2018-07-20 09:57:38

datetime()函数返回当前日期和时间。

sqlite> SELECT time('now');
09:57:56

time()函数给出当前时间。

sqlite> SELECT time(), time('now');
09:58:30    09:58:30  
sqlite> SELECT date(), date('now');
2018-07-20  2018-07-20 

now字符串可以省略。

date()time()datetime()函数的第一个参数是时间字符串。 可以紧跟一个或多个修饰符。

sqlite> SELECT date('now', '2 months');
2018-09-20

在此示例中,“ 2 个月”是修饰语。 它会将当前日期增加两个月。 因此该函数返回从今天起两个月的日期。

sqlite> SELECT date('now', '-55 days');
2018-05-26

也可以使用负修饰符。 在此示例中,我们从今天开始提取 55 天。

sqlite> SELECT date('now', 'start of year');
2018-01-01

使用start of year修饰符,我们可以得到年初的日期,例如 1 月 1 日。

sqlite> SELECT datetime('now', 'start of day');
2018-07-20 00:00:00 

借助start of day修饰符,我们可以了解当天的开始时间。

sqlite> SELECT date('now', 'weekday 6');
2018-07-21  

weekday修饰符前进到下一个日期,其中星期日是 0,星期一 1,…,星期六 6。在本示例中,我们获得最近的星期六的日期。

修饰符可以组合。

sqlite> SELECT date('now', 'start of year', '10 months', 'weekday 4');
2018-11-01 

该 SQL 语句返回当年 11 月的第一个星期四。 在此示例中,我们使用了三个修饰符:start of year+x monthsweekday xnow时间字符串给出当前日期。 start of year将日期向后移动到年初。 10 months在当月(1 月)增加了 10 个月。 最后,weekday 4修饰符将日期提前到第一个星期四。

strftime()函数返回根据指定为第一个参数的格式字符串格式化的日期和时间。 第二个参数是时间字符串。 可以紧跟一个或多个修饰符。

sqlite> SELECT strftime('%d-%m-%Y');
20-07-2018  

我们可以使用strftime()函数以其他格式返回日期。

sqlite> SELECT 'Current day: ' || strftime('%d');
Current day: 20   

该 SQL 语句返回每月的当前日期。 我们使用了strftime()函数。

sqlite> SELECT 'Days to XMas: ' || (strftime('%j', '2018-12-24') - strftime('%j', 'now'));
Days to XMas: 157

在这里,我们计算了到圣诞节为止的天数。 %j修饰符给出时间字符串的年份。

赞(0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址