-- TOC --
本文总结一些日常使用到的SQL语句的使用细节，在编码时随时给自己参考。
从应用程序的角度看，SQL语言中的REPLACE语句是非常有实用价值的，因为很多时候，一张table里的数据，你不知道哪一个会先到，哪一个后到，也有可能相同位置的数据会来很多次。
此时用replace语句是最明智的选择，代码会比较简单。replace into的含义，即如果row存在就update，如果row不存在就insert。
能不能用replace，要看table的设计，因为区分row需要primary key。如果是明确更新某一行，有primary key在手，可以replace，没有的话，就要先查询primary key，然后再replace。有的时候，查询后用update，语义可能更清晰一些。有的时候，没有primary key，但是有一个数据是unique的，直接update后跟where更简单吧。
需要注意的是，table需要至少有一个有unique约束的column来给replace into语句定位。否则，就是无脑insert。在有unique约束的table中insert，可能出Error，而用replace，就不会出Error，直接干！
sqlite> create table t3(id integer primary key, sn int, content text);
sqlite> insert into t3(id,sn,content) values (null,1,'a');
sqlite> insert into t3(id,sn,content) values (null,2,'b');
sqlite> insert into t3(id,sn,content) values (null,3,'c');
sqlite> select * from t3;
id sn content
-- -- -------
1 1 a
2 2 b
3 3 c
sqlite> replace into t3(id,sn,content) values (1,10,'aa');
sqlite> replace into t3(id,sn,content) values (2,20,'bb');
sqlite> replace into t3(id,sn,content) values (3,30,'cc');
sqlite> select * from t3;
id sn content
-- -- -------
1 10 aa
2 20 bb
3 30 cc
SQL语言中的UPDATE语句，用来更新某row的数据，或者同时更新很多row的数据。所以，replace into语句是不能完全代替update的。
update语句用来更新所有满足SQL表达式的row！这一点很重要...
下面的case，update语句不带where条件，直接将所有row更新：
sqlite> select * from t3;
id sn content
-- -- -------
1 10 aa
2 20 bb
3 30 cc
sqlite> update t3 set sn=12345;
sqlite> select * from t3;
id sn content
-- ----- -------
1 12345 aa
2 12345 bb
3 12345 cc
给update设一个where条件，更新的范围就会缩小到一部分row，或者精确定位到某一条row：
sqlite> insert into t3(id,sn,content) values (null,321,'g');
sqlite> insert into t3(id,sn,content) values (null,321,'g');
sqlite> insert into t3(id,sn,content) values (null,321,'g');
sqlite> insert into t3(id,sn,content) values (null,321,'g');
sqlite> insert into t3(id,sn,content) values (null,321,'g');
sqlite> select * from t3;
id sn content
-- ----- -------
1 12345 aa
2 12345 bb
3 12345 cc
4 321 g
5 321 g
6 321 g
7 321 g
8 321 g
sqlite> update t3 set content='xinlin' where sn=321;
sqlite> select * from t3;
id sn content
-- ----- -------
1 12345 aa
2 12345 bb
3 12345 cc
4 321 xinlin
5 321 xinlin
6 321 xinlin
7 321 xinlin
8 321 xinlin
SQLite3数据库只支持很有限的alter table语句，即只能做rename和add column。
rename table
sqlite> .table
jsond pnum ssd timed
sqlite> .mode column
sqlite> select * from pnum;
pid pnum tid
--- ------------ ---
1 GM2020090501 1
2 GM2020090401 2
3 GM2020090601 3
4 GM2020090801 7
5 GM2020090901 8
6 GM2020091101 9
7 GM2020091001 10
8 GM2020090701 11
9 GM2020091301 12
10 GM2020091701 13
11 GM2020091601 14
12 GM2020091901 15
13 GM2020091201 16
14 GM2020091401 18
15 GM2020091801 19
16 GM2020092001 20
17 GM2020091501 21
sqlite> alter table pnum rename to abcd;
sqlite> .table
abcd jsond ssd timed
sqlite> select * from abcd;
pid pnum tid
--- ------------ ---
1 GM2020090501 1
2 GM2020090401 2
3 GM2020090601 3
4 GM2020090801 7
5 GM2020090901 8
6 GM2020091101 9
7 GM2020091001 10
8 GM2020090701 11
9 GM2020091301 12
10 GM2020091701 13
11 GM2020091601 14
12 GM2020091901 15
13 GM2020091201 16
14 GM2020091401 18
15 GM2020091801 19
16 GM2020092001 20
17 GM2020091501 21
以上测试，将表pnum修改为abcd，里面的数据保持不变。
add column
sqlite> .table
abcd jsond ssd timed
sqlite> select * from abcd;
pid pnum tid
--- ------------ ---
1 GM2020090501 1
2 GM2020090401 2
3 GM2020090601 3
4 GM2020090801 7
5 GM2020090901 8
6 GM2020091101 9
7 GM2020091001 10
8 GM2020090701 11
9 GM2020091301 12
10 GM2020091701 13
11 GM2020091601 14
12 GM2020091901 15
13 GM2020091201 16
14 GM2020091401 18
15 GM2020091801 19
16 GM2020092001 20
17 GM2020091501 21
sqlite> alter table abcd add a7 text;
sqlite> select * from abcd;
pid pnum tid a7
--- ------------ --- --
1 GM2020090501 1
2 GM2020090401 2
3 GM2020090601 3
4 GM2020090801 7
5 GM2020090901 8
6 GM2020091101 9
7 GM2020091001 10
8 GM2020090701 11
9 GM2020091301 12
10 GM2020091701 13
11 GM2020091601 14
12 GM2020091901 15
13 GM2020091201 16
14 GM2020091401 18
15 GM2020091801 19
16 GM2020092001 20
17 GM2020091501 21
sqlite> alter table abcd add a text unique;
Error: Cannot add a UNIQUE column
以上测试，给表abcd增加a7列，增加后，这一列默认全部为null。增加a8列的时候出错，SQLite3不允许直接增加有unique属性的列。
修改table的column定义
SQLite并不能直接修改table的column，alter table语句只能修改table的name或者增加column。以下记录一个成功的变相修改table column的操作流程。
insert into table_name2 select * from table_name；使用 * 也说明，两个table的列与列的对应；drop table table_name；为了更安全，这一步还可以将原table_name修改为其它的名称先放在，以防万一；在数据库系统的维护过程中，总是免不了要对table做各种修改，对数据倒来倒去...
cross join
又称为笛卡尔乘积（Carterian Product），cross join简单粗暴，两个表格分别有a列和b列，分别有m行和n行，cross join之后得到的表格，就是a+b列，m*n行。
sqlite> create table aa(id integer primary key, sn int, content text);
sqlite> insert into aa(id,sn,content) values (null,1,'a');
sqlite> insert into aa(id,sn,content) values (null,2,'b');
sqlite> insert into aa(id,sn,content) values (null,3,'c');
sqlite> select * from aa;
1|1|a
2|2|b
3|3|c
sqlite> .mode column
sqlite> .header on
sqlite> create table bb(id int unique not null, seq int);
sqlite> insert into bb(id,seq) values (4,4);
sqlite> insert into bb(id,seq) values (5,5);
sqlite> select * from bb;
id seq
-- ---
4 4
5 5
sqlite> select * from aa cross join bb;
id sn content id seq
-- -- ------- -- ---
1 1 a 4 4
1 1 a 5 5
2 2 b 4 4
2 2 b 5 5
3 3 c 4 4
3 3 c 5 5
sqlite>
sqlite> select * from aa join bb;
id sn content id seq
-- -- ------- -- ---
1 1 a 4 4
1 1 a 5 5
2 2 b 4 4
2 2 b 5 5
3 3 c 4 4
3 3 c 5 5
使用cross join需要注意，这个操作有可能会产生一个非常非常大的表格！你要知道你在做什么。。
inner join
INNER JOIN不带条件的时候，就是CROSS JOIN，如上示例。
两个表格分别a列和b列，m行和n行，inner join后的到的table，列数小于等于a+b，行数小于等于m*n。因为 inner join 有条件，因此得到的表格会比无条件的cross join小。
ON：sqlite> select * from aa;
id sn content
-- -- -------
1 1 a
2 2 b
3 3 c
sqlite> select * from bb;
id seq
-- ---
4 4
5 5
sqlite> insert into bb(id,seq) values (1,7);
sqlite> insert into bb(id,seq) values (2,8);
sqlite> select * from aa join bb on aa.id=bb.id;
id sn content id seq
-- -- ------- -- ---
1 1 a 1 7
2 2 b 2 8
当aa.id=bb.id的row，才进入结果table。
使用 ON 来表示条件，是最通用的，但是表达式写起来稍微有点长，而且结果table会存在相同的列。
USING：如果两个表的列名称相同，可以用USING来表达条件：
sqlite> select * from aa join bb on aa.id=bb.id;
id sn content id seq
-- -- ------- -- ---
1 1 a 1 7
2 2 b 2 8
sqlite> select * from aa join bb using(id);
id sn content seq
-- -- ------- ---
1 1 a 7
2 2 b 8
sql表达式简单了写，而且结果table中的重复列也没有了。使用 USING 的前提是，列的名称要相同。
NATURAL JOIN：sqlite> select * from aa join bb using(id);
id sn content seq
-- -- ------- ---
1 1 a 7
2 2 b 8
sqlite> select * from aa natural join bb;
id sn content seq
-- -- ------- ---
1 1 a 7
2 2 b 8
NATURAL JOIN 会自动的去查看相同的列名，然后对他们进行 inner join 操作。
NATURAL JOIN 这个操作有一个危险点：因为它是自动去查看相同列名，如果两个表中没有相同的列名，它也会自动将 join 降级为 cross join。
sqlite> select * from sqlite_master;
type name tbl_name rootpage sql
----- --------------------- -------- -------- -------------------------------------------------------------
table aa aa 2 CREATE TABLE aa(id integer primary key, sn int, content text)
table bb bb 3 CREATE TABLE bb(id int unique not null, seq int)
index sqlite_autoindex_bb_1 bb 4
sqlite>
sqlite> create table cc(cat int, dog int);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> insert into cc(cat,dog) values (1,1);
sqlite> select * from aa natural join cc;
id sn content cat dog
-- -- ------- --- ---
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
2 2 b 1 1
2 2 b 1 1
2 2 b 1 1
2 2 b 1 1
2 2 b 1 1
3 3 c 1 1
3 3 c 1 1
3 3 c 1 1
3 3 c 1 1
3 3 c 1 1
sqlite> select * from aa join cc using(id);
Error: cannot join using column id - column not present in both tables
sqlite> select * from aa join cc on aa.id=cc.cat;
id sn content cat dog
-- -- ------- --- ---
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
而使用 ON 或者 USING，都没有这样的危险！
outer join
SQL语言定义了3中OUTER JOIN，分别是 LEFT， RIGHT和FULL，SQLite只支持 LEFT OUTER JOIN。
OUTER JOIN 是 INNER JOIN 的扩展。在执行完 INNER JOIN 的动作后，再讲LEFT table（左边的那个）中没有出现在结果table中的row，填入结果table，多出来的列（右边table中的列）填NULL。这就是OUTER JOIN。
sqlite> select * from aa outer join cc on aa.id=cc.cat;
Error: RIGHT and FULL OUTER JOINs are not currently supported
sqlite>
sqlite> select * from aa left outer join cc on aa.id=cc.cat;
id sn content cat dog
-- -- ------- --- ---
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
1 1 a 1 1
2 2 b
3 3 c
其实，在SQL语言表达式中，将右两个table互换，就是RIGHT OUTER JOIN了。
从 OUTER JOIN 的特点分析，LEFT table 中的所有row，都会一定出现的结果table中！
本文链接：https://cs.pynote.net/sf/sqlite/202203205/
-- EOF --
-- MORE --