GBase 8s DQL语法总结

前言GBase 8s数据库使用的SQL语法遵循SQL99等语法标准，加入了大量现代SQL的语法特性（如join连接、group by、窗口函数等）。具有如下特点：功能全面，风格统一。8s的SQL以统一的方式管理和操作关系数据，数据库生命周期中的全部活动均可由SQL独立完成。符合逻辑，易学易用。作为声明式语言的8s SQL只需要用户声明想做的事情即可完成所有的执行，无需用户指明详细步骤。另外，8s SQL语法经过精心设计，与英语自然语法类似，极大降低了用户的使用门槛。面向集合，使用灵活。8s SQL既可以作为交互式语言，在终端或者客户端中直接操作数据库，查看执行结果，也可以作为嵌入式语言，嵌入主流编程语言中（如C语言的ODBC、Java的JDBC等）供开发人员使用。同样的，8s的SQL语句也分为以下四大类：数据定义语言（Data Definition Language，DDL）数据查询语言（Data Query Language Language，DQL）数据操纵语言（Data Manipulation Language，DML）数据控制语言（Data Control Language，DCL）本文主要按照select语法顺序来介绍DQL语句的语法。除了介绍GBase 8s数据库的语法，还会穿插介绍相关的Oracle和MySQL语法，帮助读者加深理解，避免混淆。

select语法顺序与执行顺序-- 语法顺序
select ... from ... join ... where ... group by ... having ... order by ... limit ...

-- 执行顺序
from -> join -> where -> group by -> having -> select -> distinct -> order by -> limit

查看库名、表名和视图-- gbase写法，查看当前库中表名
info tables;

-- Oracle写法，查看当前库中表名
select table_name from user_tables;

--Oracle写法，查看当前库名
select name from v$database;

with as将子查询的结果放入语句级的临时表中（临时表在仅可在当前语句中使用，语句结束即销毁），从而简化查询，提高可读性。-- 单个子查询
with 临时表名 as 子查询

-- 多个子查询
with 临时表1 as 子查询1,

 临时表2 as 子查询2,
 临时表3 as 子查询3

集合操作-- union：对两个select的结果进行并集操作，去除重复结果，并按默认规则排序
select 列表达式 from 表1 union select 列表达式 from 表2;

-- union all：对两个select的结果进行并集操作，不去重也不排序
select 列表达式 from 表1 union all select 列表达式 from 表2;

-- intersect：对两个select的结果进行交集操作（取两个select结果的共同部分）
select 列表达式 from 表1 intersect select 列表达式 from 表2;

-- except（minus）：对两个select的结果取差集（前一个select结果减去两select结果的相同部分）
select 列表达式 from 表1 except select 列表达式 from 表2;
-- 或者
select 列表达式 from 表1 minus select 列表达式 from 表2;

别名常用于涉及多表操作的select语句中，为表或列增加别名以简化sql语句。-- 为表增加别名
select 列表达式 from 表名 as 别名;
-- 为列增加别名
select 列表达式 as 别名 from 表名;
在Oracle中，关键字“as”可省略。

pivot将某列中的各个字段作为列名，形成多个新列。语法：select * from 表名 pivot(

列表达式 --作为新列的字段
for 列名 in(列中字段1, 列中字段2, 列中字段3 ...) -- 字段1、2、3均作为新列的列名

);
举个例子：LeetCode 1179：select * from Department pivot(

sum(revenue) for month in (
    'Jan' Jan_Revenue, 
    'Feb' Feb_Revenue, 
    'Mar' Mar_Revenue, 
    'Apr' Apr_Revenue, 
    'May' May_Revenue, 
    'Jun' Jun_Revenue, 
    'Jul' Jul_Revenue, 
    'Aug' Aug_Revenue, 
    'Sep' Sep_Revenue, 
    'Oct' Oct_Revenue, 
    'Nov' Nov_Revenue, 
    'Dec' Dec_Revenue
)

);

unpivot将表中多个列（假设这几个列名为C1、C2、C3……）缩减为一个聚合列（假设聚合列列名为A，C1、C2、C3……与A中的值一一对应），而原先C1、C2、C3……列中对应的值也被整合到一个新列C中。语法如下：unpivot (C for A in (

C1 as 'C1对应的值', 
C2 as 'C2对应的值',
C3 as 'C3对应的值',
...

))
举个例子，LeetCode 1795：输入：product_idstore1store2store3095100105170null80输出：product_idstoreprice0store1950store21000store31051store1701store380select

product_id, store, price 

from

Products 

unpivot (price for store in (

store1 as 'store1',
store2 as 'store2',
store3 as 'store3'

));

case whencase

when 判断条件1 then 列表达式1
when 判断条件2 then 列表达式2
when 判断条件3 then 列表达式3
... 
else 列表达式4

end
case when整体可作为一个列表达式，或者一个值使用。

函数列筛选函数-- 查询全部列
select * from 表名;

-- 返回去重后的结果集
select distinct 列表达式 from 表名;

-- 跳过查询结果的前K行
select skip K 列表达式 from 表名;

-- 返回查询结果的前K行
select first K 列表达式 from 表名;
-- 等价于
select 列表达式 from 表名 where rownum <= K;
字符处理函数-- 将多个列的内容合并，或者拓展某列的内容
concat(列表达式或常量, 列表达式或常量 ...)

-- 取字符串的字符长度
length(列表达式)
-- 取字符串的字节长度
lengthb(列表达式)

-- 转大写字母
upper(列表达式)

-- 转小写字母
lower(列表达式)

-- 取字符串从pos开始的连续len个字符（字符串首位坐标为1）
substr(列表达式, pos, len)

-- 返回子串在主串自第pos位后第k次出现的位置
-- 参数pos及k可省略，pos及结果按字符算位置
instr(主串, 子串, pos, k)
-- 同instr，pos及结果按字节算位置
instrb(主串, 子串, pos, k)
数值处理函数-- 计算指定列中行的数量
count(列表达式)

-- 求指定列中值的和
sum(列表达式)

-- 求指定列平均值
avg(列表达式)

-- 求指定列中最大值
max(列表达式)

-- 求指定列中最小值
min(列表达式)

-- 求指定列对指定值取模后的结果
mod(列表达式, 值)

-- 对结果四舍五入至指定位小数，第二个参数默认为0，即不保留小数
round(列表达式, 值)
类型转换to_char将数值或日期类型转换成指定格式的字符串。对于数值类型，支持的格式有：（以下为部分例子）语句结果备注to_char(123.45, ‘999.9’)123.5四舍五入至一位小数to_char(12.34, ‘99.999’)12.340末尾补零to_char(1234.5, ‘9,999.9’)1,234.5to_char(10.73, ‘$99.00’)$10.73to_char(21, ‘000099’)000021补充前导零to_char(63, ‘xx’)3f十进制转十六进制其中，“9”代表数值本身的占位符，若该占位符数比结果的实际位数多，则多出来的占位符一般以空格替代。“0”为0占位符，会在结果的指定位置补“0”。“x”专门用于进制转换。

对于日期类型，支持的格式有：（以下为部分例子）语句结果to_char(date, ‘yyyy-mm-dd HH24:MI:SS’)1919-08-10 11:45:14to_char(date, ‘yyyy-mm-dd’)2023-04-01to_char(date, ‘yyyy/mm/dd’)2023/04/01to_char(date, ‘MON dd, yyyy’)APR 01, 2023其中yyyy、mm、dd等格式符可参考date类型的具体说明。to_date将字符串按照指定的格式转为日期类型，格式参数与to_char函数一致。to_date('2000-01-01', 'YYYY-MM-DD')
to_number将字符串转成数值to_number(列表达式)
其他nvl空值判断函数，如果列表达式A的结果非NULL，则返回A的值，否则返回B的值。nvl(列表达式A, 列表达式B)
listagg将某一列中，多行的值聚合成一行，语法：listagg(列名, 分隔符) within group (order by 列名) as 新列名
将指定列中同组的多个值将聚合到一行之中，排序后以指定分隔符隔开。举个例子：LeetCode 1484：输入：sell_dateproduct2020-05-30Headphone2020-06-01Pencil2020-06-02Mask2020-05-30Basketball2020-06-01Bible2020-06-02Mask2020-05-30T-Shirt输出：sell_datenum_soldproducts2020-05-303Basketball,Headphone,T-shirt2020-06-012Bible,Pencil2020-06-021Maskselect

to_char(sell_date, 'YYYY-MM-DD') as sell_date,
count(product) as num_sold,
listagg(distinct product, ',') within group (order by product) as products

from

activities

group by

sell_date;

窗口函数（Window Function）语法规则窗口函数的语法主要由三部分组成：窗口函数名(参数) over (

partition by 列表达式
order by 列表达式    

)
执行逻辑如下：将表中各行按parition子句中列表达式的值进行分组（不同于group by，窗口函数的分组不会减少行数）。若不需要分组，parition子句可省略对于每一组，单独执行order by子句（若不需要排序，该子句也可省略）。对排序后的每一组使用窗口函数，得到的结果作为新列加入表中举个例子，生成每个学生在本班级内的成绩排名：select

*, 
rank() over (
    partition by 班级
    order by 成绩 desc
) as 排序

from

表;

窗口函数原则上只能用于select子句中，其语句整体可视为列表达式。窗口函数名用于指定要使用的窗口函数，下面将详细介绍各种窗口函数专用窗口函数rank()
获得当前行的序号，若order by子句中指定的字段相等，则序号相同，且会占用后面行的位置。dense_rank()
功能和rank相似，相同字段的行，其序号也相同，但不会占用后面行的位置。row_number()
无视字段是否相等，直接按行输出序号。举个例子：成绩rankdense_rankrow_number9911187222872238722474535lead(列表达式, offset, default)
假设当前行是第i组的第j行，则lead函数用于获取第i组第j+offset行的列表达式的值。若j+offset超出当前组的范围，则返回default值。lag(列表达式, offset, default)
用法与lead相似，获取第i组第j-offset行的列表达式的值。聚合函数作为窗口函数使用聚合函数作为窗口函数使用时，假设当前行是第i组的第j行，该行的窗口函数的计算范围为：始于第i组第1行，结束于第i组第j行。举个例子，下表记录了1到3月每日的收入，现在按天统计每个月的累计收入：MonthDayIncome1115151712322283529112277314283199执行SQL如下：select

*, 
sum(Income) over (
    partition by Month
    order by Day
) as Result 

from Table;
结果如图：MonthDayIncomeResult11151515173212322542835352911462277533142828319937

join

join用于把多个表中的行结合起来。
首先在待结合的多个表中分别指定一个列，然后这些表对应列中值相等的合并为一行。指定列的列名可以互不相同。select 列表达式 from 左表名 join 右表名 on 左表.列名 = 右表.列名
join语法中常用的有inner join、full join、left join和right join四种，区别见上表。这里也举个例子，先建如下两张表，表A存储学生的姓名与学号。idname1wang2liu3kai表B存储学生的成绩。idsum180275495inner join将多张表对应列中值相等的合并为一行，若不相等或者值缺少，则整行舍弃。
执行如下sql语句：select A.id, A.name, B.sum from A join B on A.id=B.id;
得到结果：idnamesum1wang802liu75在sql语法中“inner”可省略，直接写“join”即可。full join将多张表中对应列中值相等的合并为一行，若不相等或者值缺少，行仍然保留。select A.id, A.name, B.sum from A full join B on A.id=B.id;
得到结果：idnamesum1wang802liu753kai495left join将多张表中对应列中值相等的合并为一行，若不相等或者值缺少，左表中对应行保留，右表中对应行舍弃。select A.id, A.name, B.sum from A left join B on A.id=B.id;
得到结果：idnamesum1wang802liu753kairight join同理，合并时对应列中值不相等或者缺少，右表中对应行保留，左表中对应行舍弃。cross join注意：cross join不同于上述任何一种join。cross join又称交叉连接，用于计算两张表的笛卡尔积：select * from 表1 cross join 表2;

where判断元素是否在集合中使用in或not in关键字。select 列表达式 from 表名 where 列表达式 in 集合或子查询;
其中，集合可通过以下形式定义：(值1, 值2, 值3 ...)
判断元素是否为空使用is NULL或is not NULL关键字。select 列表达式 from 表名 where 列表达式 is NULL;
like 模糊查询适用于char、varchar等类型，一般用于where子句中，来搜索符合条件的字符串。同样，可以使用“not like”来避开符合条件的字符串。使用like查询，可使用类似于正则表达式的特殊字符来匹配目标字符串。若不使用特殊字符，则like功能与“=”相同，进行全词匹配。使用“%”来匹配0个或多个字符-- 寻找以“Tom”开头的字符串
select * from Person where name like 'Tom%';
-- 寻找以“Jackson”结尾的字符串
select * from Person where name like '%Jackson';
-- 寻找含有“Van”的字符串
select * from Person where name like '%Van%';
使用“_”限制匹配的字符数量每个“_”表示匹配一个任意字符。-- 寻找以“Tom”开头，且后面还有任意4个字符的字符串
select * from Person where name like 'Tom____';

-- 寻找含一个“A”，且其前后各有一个字符的字符串
select * from Person where name like '_A_';
正则表达式匹配使用以下函数：
regexp_like(列表达式，正则表达式)-- 寻找以“BMW X”开头，后面跟着一个字符3、4或5的字符串，也就是匹配“BMW X3”、“BMW X4” 、“BMW X5”这三种字符串。
select * from Car where regexp_like(name, 'BMW X[3-5]');

group by基本用法一般和聚合函数一起使用。
group by后接列表达式，将表中的每行按列表达式中的值进行分组。也就是说，对于每一行，它们列表达式的值相等的话会被分到同一组。select 列表达式 from 表名 group by 列表达式;
假设有表T记录了学校中每个学生的成绩：gradeclassidscore11180112902339923599…………若要显示每个班学生的平均成绩：select grade, class, avg(score) from T group by grade, class;
得到结果：gradeclassscore11852399………在group by的返回结果中，每组仅显示为一行，但每组是由原表中的多行构成的，因此需要从这多行数据中选择数据组成代表该组的那一行。此时一般使用聚合函数，对列表达式进行求和、求平均、计数等方式计算出一个值来代表这一组。group by显示指定行对于group by后的每一组，如果需要原来的某一行的值来代表该组进行显示，可嵌套子查询来实现。举个例子，下表为用户登录网站的记录：idtimeaction12023-4-1Read12023-4-5Write22023-4-11Write32023-3-22Read………如果需要知道每个用户首次登录的时间，可通过group by及聚合函数直接实现：select id, min(time) from t group by id;
但如果需要知道每个用户首次登录的时间，以及当时用户的行为，action列的选择受制于time列，因此无法对action列使用聚合函数。对此可使用子查询来获取action列对应的值：select

t1.id, 
min(t1.time),
(select t0.action from t t0 where t0.id=t1.id order by t0.time limit 1) as action

from

t t1 

group by

t1.id;

having用法与where类似，后面接条件判断。但两者在select语句中的执行顺序不同。where过滤原始表或join运算后的表中不符合条件的行，having则在group by之后执行，用于过滤掉不符合条件的组。

order by给出列表达式，按列表达式的值大小进行升序排序。默认为升序排序。若要按降序排行，添加关键字“desc”。若存在多个排序条件，则继续在语句后面添加列表达式。-- 降序
select 列表达式 from 表名 order by 列表达式 desc

-- 按列表达式1排序，若对应值相同，则按列表达式2排序
select 列表达式 from 表名 order by 列表达式1 desc, 列表达式2 asc, ...
注意：order by无法对子查询使用。

limit 与 offset注意：limit与offset为MySQL特有语法。在limit语句中，查询结果索引从0开始。limit与offset一般用于最后对查询结果的筛选。其中，limit有两种使用方式：单参数limit。假设要查询结果的前k条select * from 表名 limit k;
双参数limit。需要查询结果的第i条开始的连续j条：select * from 表名 limit i, j;
offset只能与单参数limit搭配使用，可以起到与双参数limit同样的效果。假设要查第i条开始的连续j条：select * from 表名 limit j offset i;