DELETE語法
更新時間 2025-02-14 10:25:02
最近更新時間: 2025-02-14 10:25:02
分享文章
本頁介紹天翼云TeleDB數據庫的DELETE語法。
帶條件刪除
teledb=# select * from t_update ;
id | name | age
----+--------------+-----
1 | multi_column | 0
2 | test | 3
3 | 123 | 4
4 | | 4
(4 rows)
teledb=# delete from t_update where id = 3;
DELETE 1null 條件的表達方式。
teledb=# delete from t_update where name is null;
DELETE 1
teledb=# select * from t_update ;
id | name | age
----+--------------+-----
1 | multi_column | 0
2 | test | 3
(2 rows)多表關聯刪除數據
teledb=# select * from t_update ;
id | name | age
----+--------------+-----
1 | multi_column | 0
2 | test | 3
(2 rows)
teledb=# select * from teledb_serial;
id | nickname
----+-----------
1 | returning
(1 row)
teledb=# delete from t_update using teledb_serial where t_update.id = teledb_serial.id;
DELETE 1
teledb=# select * from t_update ;
id | name | age
----+------+-----
2 | test | 3
(1 row)返回刪除數據
teledb=# delete from t_update returning *;
id | name | age
----+------+-----
2 | test | 3
(1 row)
DELETE 1returning 特性可以返回 DML（insert、update、delete）修改的數據，降低應用復雜度。
刪除所有數據
teledb=# insert into t_update select t,random()::text from generate_series(1,100000) as t;
INSERT 0 100000
Time: 235.715 ms
teledb=# delete from t_update;
DELETE 100000
Time: 44.429 ms使用truncate 方法是全表刪除更高效的方法。
teledb=# insert into t_update select t,random()::text from generate_series(1,100000) as t;
INSERT 0 100000
Time: 244.021 ms
teledb=# truncate table t_update;
TRUNCATE TABLE
Time: 21.953 ms數據表使用復合類型
teledb=# create table t_my_type (f1 int,f_my_type my_type);
CREATE TABLE
teledb=# insert into t_my_type values(1,row(1,'teledb'));
INSERT 0 1
teledb=# select * from t_my_type;
f1 | f_my_type
----+-------------
1 | (1,teledb)
(1 row)
