金仓数据库Kingbase ES MySQL模式DML支持order by 和limit子句基本功能介绍

关键字：

KingbaseES、DML、子查询、order by子句、limit子句

1.什么是DML？

DML 即数据库操作语言（Data Manipulation Language）用户通过它可以实现对数据的基本操作。主要有INSERT、UPDATE、DELETE、MERGE等语法。

2.什么是子查询？

当一个查询是另一个查询的条件时，称之为子查询（Sub-query）。子查询最常用于SELECT语句的WHERE子句中用来筛选出用户所需的更小结果集或作为临时表来使用，完成更为复杂表联结数据的检索功能。

3.MySQL模式DML支持order by和limit子句功能介绍

MySQL语法上支持UPDATE/DELETE语句后跟LIMIT和ORDER BY子句， LIMIT row_count子句表示更新/删除目标表的row_count条，ORDER BY子句根据ORDER BY指定的顺序进行目标表的更新和删除；LIMIT和ORDER BY子句也可同时存在于UPDATE和DELETE语句后，表示按照order by指定的顺序，更新或者删除limit子句的row_count条。

DELETE语法格式：

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

UPDATE语法格式：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET {[{ column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]] | [ ROW = record]}
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

4.DML支持order by和limit子句使用示例

DELETE支持order by 和limit子句使用示例：

create table emp1(id int, grade varchar(100));
create table emp2(id int, grade varchar(100));
insert into emp1 values(1,'A_sdkjfls');
insert into emp1 values(2,'B_sdkjfls');
insert into emp1 values(3,'C_sdkjfls');
insert into emp2 values(1,'A_sdkjfls');
insert into emp2 values(2,'B_sdkjfls');
insert into emp2 values(3,'C_sdkjfls');
test=# delete from emp1 limit 1 returning *;
id | grade
----+-----------
1 | A_sdkjfls
(1 行记录)

DELETE 1
test=# delete from emp1 limit all returning *;
id | grade
----+-----------
2 | B_sdkjfls
3 | C_sdkjfls
(2 行记录)

DELETE 2
test=# delete from emp2 order by id asc nulls last returning *;
id | grade
----+-----------
1 | A_sdkjfls
2 | B_sdkjfls
3 | C_sdkjfls
(3 行记录)

DELETE 3
insert into emp1 values(1,'A_sdkjfls');
insert into emp1 values(2,'B_sdkjfls');
insert into emp1 values(3,'C_sdkjfls');
id | grade
----+-----------
3 | C_sdkjfls
2 | B_sdkjfls
(2 行记录)

DELETE 2

update支持order by和limit子句使用示例：
create table emp1(id int, grade varchar(100));
insert into emp1 values(1,'A_sdkjfls');
insert into emp1 values(2,'B_sdkjfls');
insert into emp1 values(3,'C_sdkjfls');
test=# update emp1 set id = 4 limit 1 returning *;
id | grade
----+-----------
4 | A_sdkjfls
(1 行记录)

UPDATE 1
test=# update emp1 set id = id+1 limit all returning *;
id | grade
----+-----------
3 | B_sdkjfls
4 | C_sdkjfls
5 | A_sdkjfls
(3 行记录)

UPDATE 3
id | grade
----+-----------
2 | B_sdkjfls
3 | C_sdkjfls
4 | A_sdkjfls
(3 行记录)

UPDATE 3
test=# update emp1 set id = id+1 order by id using > nulls last limit all returning *;
id | grade
----+-----------
5 | A_sdkjfls
4 | C_sdkjfls
3 | B_sdkjfls
(3 行记录)

更多信息，参见https://help.kingbase.com.cn/v8/index.html