sqlite

Database; use; embedded relational database

alter

英[ˈɔ:ltə(r )] 美[ˈɔltɚ]

vt.Change; change; remodel (house); (person) grow old

vi.Change; modify

SQLite Alter command syntax

Function:SQLite's ALTER TABLE command does not modify existing tables by performing a complete dump and reloading of data. You can use the ALTER TABLE statement to rename a table and to add additional columns to an existing table.

In SQLite, the ALTER TABLE command does not support other operations except renaming tables and adding columns to existing tables.

Syntax: The basic syntax of ALTER TABLE used to rename an existing table is as follows:

ALTER TABLE database_name.table_name RENAME TO new_table_name;

The basic syntax of ALTER TABLE used to add a new column to an existing table is as follows:

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

SQLite Alter command example

 COMPANY 表有如下记录：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
现在，让我们尝试使用 ALTER TABLE 语句重命名该表，如下所示：

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
上面的 SQLite 语句将重命名 COMPANY 表为 OLD_COMPANY。现在，让我们尝试在 OLD_COMPANY 表中添加一个新的列，如下所示：

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
现在，COMPANY 表已经改变，使用 SELECT 语句输出如下：

ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
请注意，新添加的列是以 NULL 值来填充的。