python alter table_SQLite3的ALTER TABLE语句

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属性的列。

-- EOF --