高级SQL语句

2022-09-19 11:55:54

改变数据

insert record

插入一行

insert into foods (name, type_id) values ('Cinnamon Bobka', 1);

如果在insert语句中为每一列都提供了值，可以省去列名，顺序是表创建时的顺序。

insert into foods values(NULL, 1, 'Blueberry Bobka');

插入 a set of rows 子查询（subqueries）可以在insert语句中使用。既可以作为要插入值的一部分，也可以作为完整的要插入值。

insert into foods
values (null,
       (select id from food_types where name='Bakery'),
       'Blackberry Bobka');

作为一部分。

select * from foods where name like '%Bobka';

作为全部。

插入多列

insert into foods
select last_insert_rowid()+1, type_id, name from foods
where name='Chocolate Bobka';

这种创建方法(Create Table As Select),会丢失约束信息，包括自增列、indexes、UNIQUE约束等等。

update record

update table set update_list where predicate;

delete records

delete from table where predicate;

数据完整性(data integrity)

数据完整性是值创建和保持table之间的关系。有四种完整性

domain integrity。列中的数据
entity integrity。表中行的数据。
referential integrity。表间列的关系，比如外键。
user-defined integrity。catchall for everything else.

数据完整性通过约束实现。在SQLite中，数据完整性包含了对冲突解决的支持。

entity integrity

主值是为了保证每一个数据都可以被寻址。主值包括一列或更多列，其中有unique约束。

unique约束
primary key约束。创建table时候一定会被创建，无论是你是否定义。这个列名是rowid，有两个别名_rowid_和oid。

如果一列的约束是integer primary key，那么SQLite会为这列创建一个默认值，保证和其他行的值不同。这里也是rowid的别名。由于SQLite使用有符号64位整数来存储主值，因此主值的最大值是9，223，372，036，854，775，807。
- 如果删除了某条记录，这个rowid会被循环利用。因此，新创建的rowid不一定是严格的递增顺序。
- 如果希望rowid为严格的递增，可以在integer primary key后加上autoincrement关键字。
- 如果主值定义中包括autoinrement，那么rowid不会被重复利用，只会产生比当前最大值大的值。SQLite会保存最大的rowid在系统table中，叫做sqlite_sequence。如果大于int64max，在插入时会产生SQLITE_FULL错误。
```
sqlite> select * from sqlite_sequence;
name        seq
----------  ----------
maxed_out   10
```
```
sqlite> insert into maxed_out values(2120, 'works');
sqlite> insert into maxed_out values(null, 'works');
Error: database or disk is full
```

Domain integrity

包括type和range两个方面。

- 默认值

default关键字提供了默认值。还有三个保留字

    - current_time. (HH:MM:SS)格式
    - current_data. (YYYY-MM-DD)格式
    - current_timestamp. (YYYY-MM-DD HH:MM:SS)格式

NOT NULL约束
check constraints
check约束在任何更改生效之前被检查。触发器可以实现check约束的功能，并且可以做的更多。

外键约束

create table table_name
( column_definition refernces foreign_table(column_name)
on {delete|update} integrity_action
[not] deferrable [initially {deferred|immeiate},]
...);

integrity_action有五种。

set null
set default
cascade
restrict
`no action

Collation

Collation是指文本是如何比较的。

binary。使用memcmp()函数比较
nocase. 大小写一样。
reverse. reverse of binary collation.

collate关键字定义了某一行的collation

Storage class

integer. vary in size 1,2,3,4,6,8 bytes.SQLite会根据数值大小判断空间大小。
real. 有小数部分或指数部分，存储方式为8字节浮点数。
text. 支持各种字符编码(UTF8,UTF16)。
bolb. Binary large object。x'ABCD'开头。
NULL.

在一列中可以包含不同storage class的值。比较方式如下：

NULL最小。NULL之间没有特定顺序。
integer和real次之。
text次之。text之间比较由collation决定。
blob最大。使用memcmp()函数比较。

Views

view是虚拟的表，也被称为derived tables，因为值是由其他表生成的，在使用时候被动态生成。

create view name as select-stmt

view在SQLite中不支持update，可以用触发器来达到同样效果。

indexes(索引)

索引用来在某些情况下提高查询的速度。

索引会增加数据库的大小，因为拷贝了需要索引的列。

索引需要被维护。因此会降低插入、更新等操作的速度。

create index [unique] index_name on table_name (columns)

unique会增加约束给索引以及索引列。

drop index index_name

触发器

create [temp|temporary] trigger name 
[before|after] [insert|delete|update|update of columns] on table
action

update触发器可以为某几列指定触发条件。
create trigger name [before|after] update of column on table action
错误处理
raise(resolution, error_message);
resolution是冲突解决策略(abort,fail,ignore,rollback...)
可更新的view trigger可以在view上用instead of关键字定义

事务(transactions)

事务定义了一组SQL命令的边界，这组命令或者全部成功，或者全部失败。数据库完整性的atomic原则。

transaction scopes

事务和三个命令有关

1. begin
2. commit
3. rollback
默认每一个SQL语句在自己的事务中运行，每一个语句都会成功或失败后回滚。这种操作类型被称为autocommit mode

SQLite支持savepoint和release命令。
savepoint用户在事务中设置一个点，SQLite可以revert到这个点。

savepoint justincase
rollback [transaction] to justincase

冲突解决

以严格程度排序如下

replace.

unique约束。移除旧的列，插入新的列。SQL操作继续进行。
not null约束。 NULL值被默认值替代。如果没有默认值，使用abort策略。
删除时不会触发触发器。

ignore.约束被违反时，命令继续进行，触发违反行保持不变。fail. 违反约束时，transaction结束，已经发生的改变不会被回滚。abort. 恢复当前命令已经进行的操作，transaction结束。默认的策略。rollback. abort当前命令和整个transaction。

update or resolution table set (value_list) where predicate

数据库锁

锁和事务是紧密相关的。SQLite使用粗粒度的锁，当一个session在向数据库中写数据时，其他的session都被锁定。

unlocked
shared.读数据库时需要先获取shared lock
reserved.写数据之前要先获取reserved lock。获取reserved lock后，可以写数据了，但是不会被写入到磁盘中，会写入到缓存里。
pending. 写完数据后，要获取exclusive lock之前要获取pending lock。此时其他共享锁不能不被获取，可以继续读取。
exclusive. 其他所有的共享锁结束后，可以获取互斥锁。所有缓存的改变被写入内存。

死锁

事务类型

deferred。不获取任何锁，直到必须获取。(默认的)
immediate. 获取reserved lock当begin开始时。
exclusive.获取互斥锁在事务开始时。

当只有一个session时，使用默认的即可。

数据库管理

attaching databases

attach [database] filename as database_name

cleaning Databases

reindex. rebuild index.
vacuum. 通过重建数据库文件来清除未使用空间。

数据库配置

SQLite没有配置文件，所有的配置通过pragmas实现。

数据库连接缓存大小

一个session可以在内存中缓存多少数据库页(pages).

sqlite> pragma cache_size = 10000;
sqlite> pragma cache_size;
10000

获取数据库信息

database_list
index_info
index_list
table_info

同步写

FULL 在关键时刻会暂停，保证数据已经被写入磁盘。如果操作系统崩溃或掉电，数据库仍会在重启后不会被破坏。
Normal 在大多数关键时刻会暂停。掉电后可能会被破坏。
Off 在把数据交给操作系统后会继续执行。可能会加快操作速度。如果程序崩溃，数据是安全的。如果操作系统崩溃或电脑掉电，数据库可能会被破坏。

Temporary Storage

temp_store:, default,file或memory。临时数据的位置

temp_store_directory:,临时数据存放文件的位置。

page size， encoding， autovacuum

必须在创建数据库之前设置。

debugging

system catalog

sqlite_master是包括表、view、index、trigger的系统表。

查看查询计划(viewsing query plans)

sqlite> explain query plan select * from sqlite_master;
selectid    order       from        detail
----------  ----------  ----------  ------------------------
0           0           0           SCAN TABLE sqlite_master

码农公寓