SQLite3 学习笔记总结

于 2020-07-18 16:11:51 发布
阅读量523 收藏 3
点赞数
分类专栏： 数据库 文章标签： sqlite sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xchenhao/article/details/107430649
版权

SQLite3导图

文章目录

总结

命令
终端命令
sqlite3 [databaseName.db]
sqlite3 test.db .dump > db.sql
sqlite3 test.db < db.sql
sqlite 命令
.help
.exit 或 .quit
.import <file> <table>
.mode <mode>
.show
.databases
.tables
.schema <table>         # 例： .schema sqlite_master
.indices [table]
子句
DISTINCT
表达式
WHERE
AND/OR
GROUP BY/HAVING
ORDER BY
OFFSET/LIMIT
运算符
算术运算符：+、-、*、/、%
比较运算符
==
=
!=
>
<
>=
<=
!<
!>
逻辑运算符
AND

BETWEEN

EXISTS
例：
 AGE 存在于子查询返回的结果中的所有记录
SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
IN
NOT IN
LIKE：
  % 代表零/一/多个数字或字符
  _ 代表一个单一的数字或字符
例：
SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_’

GLOB：与 LIKE 作用相同，不同之处在于它是大小写敏感的
  * 代表零/一/多个数字或字符
  ? 代表一个单一的数字或字符
例：
SELECT FROM table_name
WHERE column GLOB '*XXXX*'

SELECT FROM table_name
WHERE column GLOB 'XXXX?'

NOT

OR

IS NULL

IS

IS NOT

||：连接两个不同的字符串，得到一个新的字符串

UNIQUE
位运算符：&、|、~、<<、>>
DDL
数据类型
  • NULL
  • INTEGER
  • REAL(浮点数）
  • TEXT
  • BLOB
数据库操作
ATTACH DATABASE ‘test.db’ AS ‘mydb’
DETACH DATABASE ‘mydb’
表操作
  • CREATE
CREATE TABLE <dbName.tableName> (
	colName1 dataType colAttr,
	colName2 dataType colAttr
);
  • DROP
DROP TABLE <dbName.tableName>;
  • ALTER
重命名表：
ALTER TABLE database_name.table_name RENAME TO new_table_name;
例
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

添加列：
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
例
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

注：新添加的列是以 NULL 值来填充的
DML
INSERT
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];
UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
DELETE
DELETE FROM table_name
WHERE [condition];

实现 TRUNCATE 功能：
在 SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据。

DELETE FROM table_name;
DELETE FROM sqlite_sequence WHERE name = 'table_name';

当 SQLite 数据库中包含自增列时，会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列：name 和 seq。name 记录自增列所在的表，seq 记录当前序号（下一条记录的编号就是当前序号加 1）。如果想把某个自增列的序号归零，只需要修改 sqlite_sequence 表就可以了。
UPDATE sqlite_sequence SET seq = 0 WHERE name = 'table_name';
  • DQL：SELECT
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
高级
PRAGMA

PRAGMA pragma_name;
PRAGMA pragma_name = value;

字段约束
  • NOT NULL
  • DEFAULT
  • UNIQUE
  • PRIMARY KEY 只能用于 INTEGER 字段
  • AUTOINCREMENT
  • CHECK
例：
CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME TEXT NOT NULL UNIQUE,
   AGE INT NOT NULL DEFAULT 0,
   ADDRES  CHAR(50),
   SALARY REAL CHECK(SALARY > 0)
);

不能更改列：重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。

JOIN
CROSS JOIN：笛卡尔积
>SELECT ... FROM table1 CROSS JOIN table2 ...


INNER JOIN：默认的连接类型
>SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

自然连接（NATURAL JOIN）类似于 JOIN...USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
SELECT ... FROM table1 NATURAL JOIN table2...

OUTER JOIN：SQLite3 只支持 LEFT
（LEFT/RIGHT/FULL）
>SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
···

##### UNION
···sql
UNION：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
别名
表别名：
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

字段别名：
ELECT column_name AS alias_name
FROM table_name
WHERE [condition];

关键字 AS 可被省略
SELECT id AS identification, name AS nickname FROM company;
SELECT id identification, name AS nickname FROM company;
触发器
是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用
SQLite 只支持 FOR EACH ROW 触发器（Trigger）
- 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句
- BEFORE 或 AFTER 关键字决定何时执行触发器动作
- 当触发器相关联的表删除时，自动删除触发器（Trigger）
- 要修改的表必须存在于同一数据库中
- 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常

语法
CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

— 删除触发器
DROP TRIGGER trigger_name;

例：
CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
sqlite_master
查看所有触发器：
SELECT name FROM sqlite_master
WHERE type = 'trigger';

SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

查看所有索引：
SELECT * FROM sqlite_master WHERE type = 'index';
索引
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。


CREATE INDEX index_name ON table_name;

否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。

===
单列索引：
CREATE INDEX index_name
ON table_name (column_name);

唯一索引：
CREATE UNIQUE INDEX index_name
on table_name (column_name);

组合索引：
CREATE INDEX index_name
on table_name (column1, column2);

隐式索引：
列添加 UNIQUE 约束后会自动创建隐式索引
	•	INDEXD BY
INDEXD BY 指定必须需要命名的索引来查找前面表中值。

如果索引名 index-name 不存在或不能用于查询，然后 SQLite 语句的准备失败

"NOT INDEXED" 子句规定当访问前面的表（包括由 UNIQUE 和 PRIMARY KEY 约束创建的隐式索引）时，没有使用索引
使指定了 "NOT INDEXED"，INTEGER PRIMARY KEY 仍然可以被用于查找条目


SELECT|DELETE|UPDATE column1, column2...
FROM table_name
INDEXED BY (index_name)
WHERE (CONDITION);

例：
SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
视图
预定义的 SQLite 语句

SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句
但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

语法：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

如果使用了可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。

删除视图：
DROP VIEW view_name;
事务
事务属性：
ACID

事务控制：只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的

BEGIN; 或 BEGIN TRANSACTION;

COMMIT; 或 END TRANSACTION;

ROLLBACK;
子查询
SQL 注入
EXPLAIN
用于交互式分析和排除故障：

EXPLAIN [SQLite Query]

EXPLAIN  QUERY PLAN [SQLite Query]
VACUUM
通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。

如果表中没有明确的整型主键（INTEGER PRIMARY KEY），VACUUM 命令可能会改变表中条目的行 ID（ROWID）。VACUUM 命令只适用于主数据库，附加的数据库文件是不可能使用 VACUUM 命令。

如果有一个活动的事务，VACUUM 命令就会失败。VACUUM 命令是一个用于内存数据库的任何操作。由于 VACUUM 命令从头开始重新创建数据库文件，所以 VACUUM 也可以用于修改许多数据库特定的配置参数。

$ sqlite3 database_name "VACUUM;"

sqlite> VACUUM;

sqlite> VACUUM table_name;
其它：AUTO_VACUUM
函数
日期与时间函数
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)	
julianday(timestring, modifier, modifier, ...)	

数学与字符串函数
数学函数：

COUNT
MAX/MIN
AVG
SUM
RANDOM
ABS

字符串函数：
UPPER
LOWER
LENGTH

其它函数
sqlite_version
接口：与编程语言交互
参考
  • https://www.runoob.com/sqlite/sqlite-tutorial.html
  • http://songti.net/thread-636-1-1.html
陈挨踢
关注
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

陈挨踢

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值