金仓数据库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 行记录)
UPDATE 3
