Sqlite中sql⾼级⽤法整理
⼀、主键约束
PRIMARY KEY 约束唯⼀标识数据库表中的每个记录。
在⼀个表中可以有多个 UNIQUE 列，但只能有⼀个主键。
在设计数据库表时，主键是很重要的。主键是唯⼀的 ID。
我们使⽤主键来引⽤表中的⾏。
可通过把主键设置为其他表的外键，来创建表之间的关系。
由于”长期存在编码监督”，在 SQLite 中 主键值 可 以是 NULLL，这是与其他数据库不同的地⽅。NULL 与没有数 据是不⼀样的，它代表着未知的数据。
⼀个表只能有⼀个主键，它可以由⼀个或多个字段组成。当多个字段作为主键，它们被称为复合键。
如果⼀个表在任何字段上定义了⼀个主键，那么在这些字段上不能有两个记录具有相同的值。
==注意：在做排序操作时，升序NULL排最前⾯，降序NULL排最后⾯。==
⼆、ALTER TABLE 语句
ALTER TABLE 语句⽤于在已有的表中添加、修改或删除列。
SQL ALTER TABLE 语法如下:
如需在表中添加列，请使⽤下列语法:
ALTER TABLE table_name
ADD column_name datatype
要删除表中的列，请使⽤下列语法：
ALTER TABLE table_name
DROP COLUMN column_name
注释：某些数据库系统不允许这种在数据库表中删除列的⽅式 (DROP COLUMN column_name)。SQLite ⽀持 ALTER TABLE 的有限⼦集。在 SQLite 中，ALTER TABLE 命令允许⽤户重命名表，或向现有表添加⼀个新的列。重命名列，删除⼀列，或从⼀个表中添加或删除约束都是不可能的。
要改变表中列的数据类型，请使⽤下列语法：
ALTER TABLE table_name
ALTER COLUMN column_name datatype
三、连接
SQL 定义了三种主要类型的连接：
交叉连接 - CROSS JOIN
内连接 - INNER JOIN
外连接 - OUTER JOIN
1.交叉连接
交叉连接（CROSS JOIN）把第⼀个表的每⼀⾏与第⼆个表的每⼀⾏进⾏匹配。如果两个输⼊表分别有 x 和 y 列，则结果表有 x+y 列。由于交叉连接（CROSS JOIN）有可能产⽣⾮常⼤的表，使⽤时必须谨慎，只在适当的时候使⽤它们。
下⾯是交叉连接（CROSS JOIN）的语法：
SELECT ... FROM table1 CROSS JOIN table2 ...
2.内连接
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建⼀个新的结果表。查询会把 table1 中的每⼀⾏与table2 中的每⼀⾏进⾏⽐较，到所有满⾜连接谓词的⾏的匹配对。当满⾜连接谓词时，A 和 B ⾏的每个匹配对的列值会合并成⼀个结果⾏。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
下⾯是内连接（INNER JOIN）的语法：
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
为了避免冗余，并保持较短的措辞，可以使⽤ USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定⼀个或多个列的列表：SELECT ... FROM table1 JOIN table2 USING ( column1 ,...)...
⾃然连接（NATURAL JOIN）类似于 JOIN…USING，只是它会⾃动测试存在两个表中的每⼀列的值之间相等值：SELECT ... FROM table1 NATURAL
3.外连接（难点）
外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但SQLite 只⽀持 左外连接（LEFT OUTER JOIN）。
外连接（OUTER JOIN）声明条件的⽅法与内连接（INNER JOIN）是相同的，使⽤ ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的⽅式进⾏计算。⼀旦主连接计算完成，如果左表的某⾏在右表中没有匹配⾏，则在相关联的结果集⾏中右表的所有选择列表列均为空值。 这⾥这么说，可能不是很好理解，我们先看看做外连接的语法，接着看⼀个实例帮助理解。
下⾯是左外连接（LEFT OUTER JOIN）的语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
下⾯看⼀个具体的例⼦：
a表 ：
id name
1张三
2李四
3王五
b表：
id job parent_id
1231
2342
3344
查询语句：
select a.*,b.* from a left join b on a.id=b.parent_id`
结果为：
a.id a.name
b.id b.job b.parent_id`
1张三1231
2李四2342
3王武
四、触发器（Trigger）
SQLite 的触发器是数据库的回调函数，它会⾃动执⾏/指定的数据库事件发⽣时调⽤。
它会在指定的数据库事件发⽣时⾃动执⾏/调⽤。以下是关于 SQLite 的触发器（Trigger）的要点：
SQLite 的触发器（Trigger）可以指定在特定的数据库表发⽣ DELETE、INSERT 或 UPDATE时触发，或在⼀个或多个指定表的列发⽣更新时触发。
SQLite 只⽀持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的。
WHEN ⼦句和触发器（Trigger）动作可能访问使⽤表单 lumn-name 和 lumn-name的
引⽤插⼊、删除或更新的⾏元素，其中 column-name 是从与触发器关联的表的列的名称。
NEW 与 OLD 详解
NEW 和 OLD⽤来表⽰触发器的所在表中，触发了触发器的那⼀⾏数据。
具体地：
在 INSERT 型触发器中，NEW ⽤来表⽰将要（BEFORE）或已经（AFTER）插⼊的新数据；
在 UPDATE 型触发器中，OLD ⽤来表⽰将要或已经被修改的原数据，NEW ⽤来表⽰将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD ⽤来表⽰将要或已经被删除的原数据；
使⽤⽅法： lumnName （columnName 为相应数据表某⼀列名）
另外，OLD 是只读的，⽽ NEW 则可以在触发器中使⽤ SET 赋值，这样不会再次触发触发器，造成循环调⽤（如每插⼊⼀个学⽣前，都在其学号前加“2013”）。
如果提供 WHEN ⼦句，则只针对 WHEN ⼦句为真的指定⾏执⾏ SQL 语句。如果没有提供 WHEN ⼦
句，则针对所有⾏执⾏ SQL 语句。
BEFORE 或 AFTER 关键字决定何时执⾏触发器动作，决定是在关联⾏的插⼊、修改或删除之前或者之后执⾏触发器动作。
当触发器相关联的表删除时，⾃动删除触发器（Trigger）。
要修改的表必须存在于同⼀数据库中，作为触发器被附加的表或视图，且必须只使⽤ tablename，⽽不是database.tablename。
⼀个特殊的 SQL 函数 RAISE() 可⽤于触发器程序内抛出异常。
创建 触发器（Trigger） 的基本语法如下：
CREATE OR REPLACE TRIGGER TG_NAME
BEFORE | AFTER
INSERT OR DELETE OR UPDATE [OF COL1,COL2,...]
ON表名
[FOR EACH ROW]
[WHEN条件]
DECLARE
...
BEGIN
....
EXCEPTION
...
END;
注释: A) FOR EACH ROW 代表⾏级触发器.
B) WHEN: 只有符合该条件的时候才进⾏触发.
C) 可以通过INSERTING,DELETING,UPDATING来识别INSERT,DELETE,UPDATE操作.
D) ⽤new 和old(称为伪记录)来访问数据变更前后的值。
批量更新sql语句 E) 触发器中的操作与触发事件(INSERT,UPDATE,DELETE)⼀起进⾏COMMIT和ROLLBACK;
以下是在 UPDATE 操作上在表的⼀个或多个指定列上创建触发器（Trigger）的语法：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic
END;
实例
让我们假设⼀个情况，我们要为被插⼊到新创建的 COMPANY 表（如果已经存在，则删除重新创建）中的每⼀个记录保持审计试验：
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
为了保持审计试验，我们将创建⼀个名为 AUDIT 的新表。每当 COMPANY 表中有⼀个新的记录项时，⽇志消息将被插⼊其中：
sqlite> CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
在这⾥，ID 是 AUDIT 记录的 ID，EMP_ID 是来⾃ COMPANY 表的 ID，DATE 将保持 COMPANY 中记录被创建时的时间戳。所以，现在让我们在 COMPANY 表上创建⼀个触发器，如下所⽰：
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
五、视图（View）
视图（View）只不过是通过相关的名称存储在数据库中的⼀个 SQLite 语句。视图（View）实际上是⼀个以预定义的 SQLite 查询形式存在的表的组合。
视图（View）可以包含⼀个表的所有⾏或从⼀个或多个表选定⾏。
视图（View）可以从⼀个或多个表创建，这取决于要创建视图的 SQLite 查询。
视图（View）是⼀种虚表，允许⽤户实现以下⼏点：
1. ⽤户或⽤户组查结构数据的⽅式更⾃然或直观。
2. 限制数据访问，⽤户只能看到有限的数据，⽽不是完整的表。
3. 汇总各种表中的数据，⽤于⽣成报告。
4. SQLite 视图是只读的，因此可能⽆法在视图上执⾏ DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建⼀个触发
器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
创建视图:
SQLite 的视图是使⽤ CREATE VIEW 语句创建的。SQLite 视图可以从⼀个单⼀的表、多个表或其他视图创建。
CREATE VIEW 的基本语法如下：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1,
FROM table_name
WHERE [condition];
您可以在 SELECT 语句中包含多个表，这与在正常的 SQL SELECT 查询中的⽅式⾮常相似。如果使⽤了可选的 TEMP 或TEMPORARY 关键字，则将在临时数据库中创建视图。
sqlite内存数据库、临时数据库
内存数据库： 在SQLite中，数据库通常是存储在磁盘⽂件中的。然⽽在有些情况下，我们可以让数据库始终驻留在内存中。在创建并打开数据库后，不会有任何磁盘⽂件被⽣成，取⽽代之的是，⼀个新的数据库在纯内存中被成功创建了。由于没有持久化，该数据库在当前数据库连接被关闭后就会⽴刻消失。
临时数据库： 和内存数据库⼀样，在连接关闭后，临时数据库将⾃动消失，其底层⽂件也将被⾃动删除。尽管磁盘⽂件被创建⽤于存储临时数据库中的数据信息，但是实际上临时数据库也会和内存数据
库⼀样通常驻留在内存中，唯⼀不同的是，当临时数据库中数据量过⼤时，SQLite为了保证有更多的内存可⽤于其它操作，因此会将临时数据库中的部分数据写到磁盘⽂件中，⽽内存数据库则始终会将数据存放在内存中。
六、索引（Index)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论