当前位置：首页 > 编程日记 > 正文

【数据库】适用于SQLite的SQL语句（二）

编程日记 2025-01-06 21:20:00

- - - 九、视图VIEW
    - - 1、创建视图
      - 2、删除视图
    - 十、虚拟表
    - - 1、创建虚拟表
      - 2、删除虚拟表
    - 十一、时间和日期的函数
    - 十二、分析和故障排除
    - 十三、SQL语句中的表达式
    - - 1、运算符
      - 2、字面值
      - 3、参数
    - 十四、插入 INSERT
    - 十五、SQLite关键字
    - 十六、解决冲突 ON CONFLICT

九、视图VIEW

视图是基于真实数据上创建的虚拟表，在查询语句SELECT中可以当表来使用。

1、创建视图

CREATE VIEW "VIEW_NAME" AS "SQL 语句";

2、删除视图

DROP VIEW "VIEW_NAME";

十、虚拟表

虚拟表和普通表类似，但是并不实际存储数据。

1、创建虚拟表

CREATE VIRTUAL TABLE

2、删除虚拟表

使用删除普通表的语法：

DROP TABLE

十一、时间和日期的函数

date(timestring, modifier, modifier, …)：返回日期，格式为YYYY-MM-DD；
time(timestring, modifier, modifier, …)：返回时间，格式为HH:MM:SS；
datetime(timestring, modifier, modifier, …)：返回日期时间：格式为YYYY-MM-DD HH:MM:SS；
julianday(timestring, modifier, modifier, …)：返回儒略日（Julian Day）；
儒略日（Julian Day）是公元前4714年11月24日（格林尼治公历）自格林威治中午以来的天数。
strftime(format, timestring, modifier, modifier, …)：返回格式化时间；
和C标准库的strftime类似。代替格式如下：

d		一个月中的一天：00
％F		小数秒：SS.SSS
％H		时间：00-24
％j		一年中的一天：001-366
％J		儒略日（Julian Day）天数
％m		月：01-12
％M		分钟：00-59
％s		自1970-01-01以来的秒数
％S		秒：00-59
％w		0-6的星期几，星期日== 0
％W		一年中的第几周：00-53
％Y		年：0000-9999
%%		％

函数中timestring格式可以是如下格式：

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

函数中modifier取值如下：

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc

例子：

计算当前日期
SELECT date('now');计算当月的最后一天。
SELECT date('now','start of month','+1 month','-1 day');给定unix时间戳1092941466，计算日期和时间。
SELECT datetime(1092941466, 'unixepoch');给定unix时间戳1092941466，计算日期和时间，并补偿您的本地时区。
SELECT datetime(1092941466, 'unixepoch', 'localtime');计算当前的unix时间戳。
SELECT strftime('%s','now');计算自美国独立宣言签署以来的天数。
SELECT julianday('now') - julianday('1776-07-04');计算自2004年某个特定时刻以来的秒数：
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');计算当年10月的第一个星期二的日期。
SELECT date('now','start of year','+9 months','weekday 2');计算以秒为单位的自unix纪元以来的时间（类似于strftime（'％s'，'now'），但包括小数部分）：
SELECT (julianday('now') - 2440587.5)*86400.0;

十二、分析和故障排除

EXPLAIN + sql语句：打印虚拟机指令；
EXPLAIN QUERY PLAN + sql语句：用于交互式调试，对sql查询语句说明；

select * from tt;
id|name|age|abc
1|XiaoHong|18|
2|XiaoMing|19|
3|Xiao1|88|
EXPLAIN select * from tt;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    00  Start at 10  
1     OpenRead       0     2     0     4              00  root=2 iDb=0; tt
2     Rewind         0     9     0                    00               
3       Rowid          0     1     0                    00  r[1]=rowid   
4       Column         0     1     2                    00  r[2]=tt.name 
5       Column         0     2     3                    00  r[3]=tt.age  
6       Column         0     3     4                    00  r[4]=tt.abc  
7       ResultRow      1     4     0                    00  output=r[1..4]
8     Next           0     3     0                    01               
9     Halt           0     0     0                    00               
10    Transaction    0     0     9     0              01  usesStmtJournal=0
11    Goto           0     1     0                    00        EXPLAIN QUERY PLAN select * from tt;
QUERY PLAN
`--SCAN TABLE tt	//该SELECT语句通过对表tt执行全表扫描来实现

十三、SQL语句中的表达式

1、运算符

优先级由高到低

||
*    /    %
+    -
<<   >>   &    |
<    <=   >    >=
=    ==   !=   <>   IS   IS NOT   IN   LIKE   GLOB   MATCH   REGEXP
AND   
OR
// 一元运算符
-    +    ~    NOT	COLLATE

2、字面值

整数、浮点数、字符串、BLOB或NULL

3、参数

用于 sqlite3_bind()接口

?NNN  ： NNN为数字，表示第几个参数，介于1到SQLITE_MAX_VARIABLE_NUMBER之间；
?   ：问号后不带数字，表示已分配的最大参数加一，为了和其它数据库引擎兼容，不推荐使用；
:AAAA ： 参数名称AAAA，和问号不带数字相同，会按顺序自动分配编码
@AAAA ：和上一个冒号：相同，只是参数名称带@
$AAAA：用于Tcl的语法

十四、插入 INSERT

三种基本形式

INSERT INTO table VALUES(...);
INSERT INTO table SELECT ...;
INSERT INTO table DEFAULT VALUES;	// 插入默认值

十五、SQLite关键字

关键字不能用作表、索引、列、数据库、用户定义的函数、归类、虚拟表模块或任何其他命名对象的名称。
关键字列表

ABORT
ACTION
ADD
AFTER
ALL
ALTER
ANALYZE
AND
AS
ASC
ATTACH
AUTOINCREMENT
BEFORE
BEGIN
BETWEEN
BY
CASCADE
CASE
CAST
CHECK
COLLATE
COLUMN
COMMIT
CONFLICT
CONSTRAINT
CREATE
CROSS
CURRENT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATABASE
DEFAULT
DEFERRABLE
DEFERRED
DELETE
DESC
DETACH
DISTINCT
DO
DROP
EACH
ELSE
END
ESCAPE
EXCEPT
EXCLUDE
EXCLUSIVE
EXISTS
EXPLAIN
FAIL
FILTER
FIRST
FOLLOWING
FOR
FOREIGN
FROM
FULL
GLOB
GROUP
GROUPS
HAVING
IF
IGNORE
IMMEDIATE
IN
INDEX
INDEXED
INITIALLY
INNER
INSERT
INSTEAD
INTERSECT
INTO
IS
ISNULL
JOIN
KEY
LAST
LEFT
LIKE
LIMIT
MATCH
NATURAL
NO
NOT
NOTHING
NOTNULL
NULL
NULLS
OF
OFFSET
ON
OR
ORDER
OTHERS
OUTER
OVER
PARTITION
PLAN
PRAGMA
PRECEDING
PRIMARY
QUERY
RAISE
RANGE
RECURSIVE
REFERENCES
REGEXP
REINDEX
RELEASE
RENAME
REPLACE
RESTRICT
RIGHT
ROLLBACK
ROW
ROWS
SAVEPOINT
SELECT
SET
TABLE
TEMP
TEMPORARY
THEN
TIES
TO
TRANSACTION
TRIGGER
UNBOUNDED
UNION
UNIQUE
UPDATE
USING
VACUUM
VALUES
VIEW
VIRTUAL
WHEN
WHERE
WINDOW
WITH
WITHOUT

十六、解决冲突 ON CONFLICT

在INSERT和UPDATE命令，关键字“ ON CONFLICT”应替换为“ OR；
五中解决冲突的算法：ROLLBACK、ABORT、FAIL、IGNORE和REPLACE。默认是ABORT。

https://www.dkcj.cn/info/35152.html

目录