Skip to main content

SQLite ALTER表及其局限性

与SQL标准和其他数据库系统不同,SQLite支持ALTER TABLE语句的功能非常有限。

通过使用SQLite ALTER TABLE语句,可以执行两个操作:

使用SQLite ALTER TABLE重命名表

要重命名表,请使用以下ALTER table rename To语句:

ALTER TABLE existing_table
RENAME TO new_table;

在重命名表之前,您应该了解以下要点:

让我们以重命名表为例。

首先,创建一个名为devices的表,该表有三列:name、model、serial;并在设备表中插入新行。

CREATE TABLE devices (
name TEXT NOT NULL,
model TEXT NOT NULL,
Serial INTEGER NOT NULL UNIQUE
);

INSERT INTO devices (name, model, serial)
VALUES('HP ZBook 17 G3 Mobile Workstation','ZBook','SN-2015');

试试看

其次,使用ALTER TABLE RENAME TO语句将设备表更改为设备表,如下所示:

ALTER TABLE devices
RENAME TO equipment;

试试看

第三,从设备表中查询数据以验证重命名操作。

SELECT
name,
model,
serial
FROM
equipment;

试试看

使用SQLite ALTER TABLE向表中添加新列

可以使用SQLite ALTER TABLE语句向现有表中添加新列。在这种情况下,SQLite会在现有列列表的末尾追加新列。

下面说明了ALTER TABLE ADD COLUMN语句的语法:

ALTER TABLE table_name
ADD COLUMN column_definition;

新专栏有一些限制:

例如,可以将名为location的新列添加到设备表中:

ALTER TABLE equipment 
ADD COLUMN location text;

试试看

使用SQLite ALTER TABLE重命名列

SQLite在3.20.0版中增加了对使用ALTER TABLE RENAME column语句重命名列的支持

下面显示了ALTER TABLE RENAME COLUMN语句的语法:

ALTER TABLE table_name
RENAME COLUMN current_name TO new_name;

有关如何重命名列的详细信息,请查看重命名列教程。

使用SQLite ALTER TABLE执行其他操作

如果要执行其他操作,例如删除列,请使用以下步骤:

SQLite-ALTER-TABLE-Steps

以下脚本说明了上述步骤:

-- disable foreign key constraint check
PRAGMA foreign_keys=off;

-- start a transaction
BEGIN TRANSACTION;

-- Here you can drop column
CREATE TABLE IF NOT EXISTS new_table(
column_definition,
...
);
-- copy data from the table to the new_table
INSERT INTO new_table(column_list)
SELECT column_list
FROM table;

-- drop the table
DROP TABLE table;

-- rename the new_table to the table
ALTER TABLE new_table RENAME TO table;

-- commit the transaction
COMMIT;

-- enable foreign key constraint check
PRAGMA foreign_keys=on;

SQLite ALTER TABLE DROP列示例

SQLite不支持ALTER TABLE DROP COLUMN语句。要删除列,需要使用上述步骤。

以下脚本将创建两个表用户和收藏夹,并将数据插入这些表中:

CREATE TABLE users(
UserId INTEGER PRIMARY KEY,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
Email TEXT NOT NULL,
Phone TEXT NOT NULL
);

CREATE TABLE favorites(
UserId INTEGER,
PlaylistId INTEGER,
FOREIGN KEY(UserId) REFERENCES users(UserId),
FOREIGN KEY(PlaylistId) REFERENCES playlists(PlaylistId)
);

INSERT INTO users(FirstName, LastName, Email, Phone)
VALUES('John','Doe','john.doe@example.com','408-234-3456');

INSERT INTO favorites(UserId, PlaylistId)
VALUES(1,1);

下面的语句从users表返回数据:

SELECT * FROM users;

下面的语句返回favorites表中的数据:

SELECT * FROM favorites;

假设您想删除用户表的phone列。

首先,禁用外键约束检查:

PRAGMA foreign_keys=off;

第二,开始一项新交易:

BEGIN TRANSACTION;

第三,创建一个新表来保存除phone列之外的users表的数据:

CREATE TABLE IF NOT EXISTS persons (
UserId INTEGER PRIMARY KEY,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
Email TEXT NOT NULL
);

第四,将数据从用户表复制到人员表:

INSERT INTO persons(UserId, FirstName, LastName, Email)
SELECT UserId, FirstName, LastName, Email
FROM users;

第五,删除用户表:

DROP TABLE users;

第六,将persons表重命名为users表:

ALTER TABLE persons RENAME TO users;

第七,提交事务:

COMMIT;

第八,启用外键约束检查:

PRAGMA foreign_keys=on;

以下是删除电话列后的用户表:

SELECT * FROM users;

总结