SELECT

功能描述

SELECT用于从表或视图中查询数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

本文档只介绍SELECT语句兼容MySQL的特性，原Vastbase的SELECT语法未做删除和修改，详见SELECT。

Vastbase在MySQL兼容模式下，支持SELECT语句如下特性用法：

• 允许SELECT后面的非分组列不出现在GROUP BY子句中。

• 允许order by指定的列不出现在select distinct的目标列中。

• 支持使用关键字作为别名。

• 支持在UNION [ALL] 语句中使用ORDER BY 子句。

• 支持JOIN不加关联条件，返回结果为笛卡尔积的形式。即JOIN不带on/using，效果与CROSS JOIN一致。

• 支持PARTITION子句可指定多个分区查询数据。

注意事项

该功能仅在数据库兼容模式为MySQL时支持（即数据库初始化时指定DBCOMPATIBILITY='B'）。

语法格式

SELECT select_list FROM table_name [ WHERE condition ] [,...];

参数说明

• select_list

  SELECT查询输出的目标列。

  • 多个列名间用逗号隔开。

  • SELECT *表示输出表的所有列。

• table_name

  查询指定的表名。

• [ WHERE condition ]

  WHERE子句构成一个行选择表达式，用来缩小SELECT查询的范围。condition是返回值为布尔型的任意表达式，任何不满足该条件的行都不会被检索。

SELECT…GROUP BY

功能描述

GROUP BY子句可以将查询结果按某一列或多列的值分组，值相等的为一组。

Vastbase在MySQL兼容模式下，支持通过参数vastbase_sql_mode控制出现在select中的目标列，是否一定要出现在group by子句中。

• 当vastbase_sql_mode的取值包含ONLY_FULL_GROUP_BY时，select后的目标列一定要出现在group by子句中。此为默认情况。

• 当vastbase_sql_mode的取值不包含ONLY_FULL_GROUP_BY时，允许select后的目标列不出现在group by子句中。

注意事项

无。

语法格式

SELECT select_list FROM table_name [ WHERE condition ] GROUP BY column_name [,...];

参数说明

GROUP BY column_name

• 指定GROUP BY时，将查询结果按column_name列的值分组，值相等的为一组。

• 可以指定单列或多个列，多个列名间用逗号隔开。

示例

1、创建测试表student，并插入测试数据。

CREATE TABLE student(sid int,name varchar2(20),age int, class varchar2(20));
INSERT INTO student VALUES(1,'陈一',22,'java');
INSERT INTO student VALUES(2,'黄二',23,'python');
INSERT INTO student VALUES(3,'张三',21,'java');
INSERT INTO student VALUES(4,'李四',20,'C#');
INSERT INTO student VALUES(5,'王五',19,'python');
INSERT INTO student VALUES(6,'赵六',21,'java');
INSERT INTO student VALUES(7,'钱七',21,'java');
INSERT INTO student VALUES(8,'孙八',20,'C++');
INSERT INTO student VALUES(9,'杨九',19,'C++');

• vastbase_sql_mode取值包含ONLY_FULL_GROUP_BY：

  （1）查看当前vastbase_sql_mode的值。

  SHOW vastbase_sql_mode; 
  

  返回结果如下：

      vastbase_sql_mode
  --------------------------------
  ONLY_FULL_GROUP_BY,ANSI_QUOTES
  (1 row)
  

  （2）若上一步查看到的结果中不包含ONLY_FULL_GROUP_BY，可以使用如下语句，修改本次会话中的取值。退出会话后，设置将失效。

  set vastbase_sql_mode='ONLY_FULL_GROUP_BY';
  

  （3）查询1：select的目标列sid没有出现在group by子句中。

  select sid from student group by age;
  

  执行失败，报错信息如下：

  ERROR:  column "student.sid" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 1: select sid from student group by age;
                 ^
  

  （4）查询2：select的目标列sid包括在group by子句中。

  select sid from student group by sid,age;
  

  查询成功，返回结果如下：

   sid
  -----
   1
   4
   6
   5
   9
   8
   7
   3
   2
   (9 rows)
  

• vastbase_sql_mode取值不包含ONLY_FULL_GROUP_BY：

  （1）使用如下语句，修改本次会话中的取值。退出会话后，设置将失效。

  set vastbase_sql_mode='';
  

  （2）修改后，查看当前取值：

  show vastbase_sql_mode;
  

  返回结果如下：

  vastbase_sql_mode
  -------------------
  
  (1 row)
  

  （3）执行查询：select的目标列sid没有出现在group by子句中。

  select sid from student group by age;
  

  执行成功，返回结果如下，表明此时select list中可以使用非分组列。

  sid
  -----
  2
  4
  1
  3
  5
  (5 rows)
  

SELECT DISTINCT…ORDER BY

功能描述

ORDER BY子句用于对SELECT语句检索得到的数据进行升序或降序排序。

Vastbase在MySQL兼容模式下，支持通过参数vastbase_sql_mode控制出现在order by中的列，是否一定要出现在select distinct的目标列中。

• 当vastbase_sql_mode的取值包含ONLY_FULL_GROUP_BY时，出现在order by中的列，一定要出现在select distinct的目标列中。此为默认情况。

• 当vastbase_sql_mode的取值不包含ONLY_FULL_GROUP_BY时，允许order by指定的列不出现在select distinct的目标列中。

注意事项

无。

语法格式

SELECT DISTINCT select_list FROM table_name [ WHERE condition] ORDER BY column_name [,...] [ ASC | DESC ];

参数说明

• DISTINCT

  从SELECT的结果集中删除所有重复的行，使结果集中的每行都是唯一的。

• ORDER BY column_name

  指定ORDER BY时，将查询结果根据column_name的值按照顺序排序。可以指定单列或多个列，多个列名间用逗号隔开。

  对于ORDER BY表达式中包含多列的情况：

  • 首先根据最左边的列进行排序，如果这一列的值相同，则根据下一个表达式进行比较，依此类推。

  • 如果对于所有声明的表达式都相同，则按随机顺序返回。

• [ ASC | DESC ]

  • ASC：升序排列。默认值。

  • DESC：降序排列。

示例

1、创建测试表并插入数据。

create table test (id varchar(32),names varchar(32));
insert into test values('1','zhangsan');
insert into test values('12','lisi');
insert into test values('2','wangwu');
insert into test values('2','maliu');

• vastbase_sql_mode取值包含ONLY_FULL_GROUP_BY：

  （1）查看当前vastbase_sql_mode的值。

  SHOW vastbase_sql_mode; 
  

  返回结果如下：

      vastbase_sql_mode
  --------------------------------
  ONLY_FULL_GROUP_BY,ANSI_QUOTES
  (1 row)
  

  （2）若上一步查看到的结果中不包含ONLY_FULL_GROUP_BY，可以使用如下语句，修改本次会话中的取值。退出会话后，设置将失效。

  set vastbase_sql_mode='ONLY_FULL_GROUP_BY';
  

  （3）查询1：order by中的列出现在select distinct的目标列中。

  select distinct id,names from test order by id;
  

  查询成功，返回结果如下：

  id |  names
  ----+----------
  1  | zhangsan
  12 | lisi
  2  | wangwu
  2  | maliu
  (4 rows)
  

  （4）查询2：order by中的列没有出现在select distinct的目标列中。

  select distinct names from test order by id;
  

  查询失败，报错信息如下：

  ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
  LINE 1: select distinct names from test order by id;
                                                   ^
  

• vastbase_sql_mode取值不包含ONLY_FULL_GROUP_BY：

  （1）使用如下语句，修改本次会话中的取值。退出会话后，设置将失效。

  set vastbase_sql_mode='';
  

  （2）修改后，查看当前取值：

  show vastbase_sql_mode;
  

  返回结果如下：

  vastbase_sql_mode
  -------------------
  
  (1 row)
  

  （3）执行查询：order by中的列没有出现在select distinct的目标列中。

  select distinct names from test order by id;
  

  查询成功，返回结果如下，表示此时允许order by指定的列不出现在select distinct的目标列中：

   names
  ----------
   zhangsan
   lisi
   wangwu
   maliu
  (4 rows)
  

支持使用关键字作为别名

功能描述

Vastbase在MySQL兼容模式下，可以将部分非保留关键字在不加引号（如双引号，单引号或反引号）的情况下，作为SQL语句中的标识符（即别名）。

表1 支持作为别名的非保留关键字

注意事项

无。

示例

1、创建测试表。

CREATE TABLE user1_1152455 (id int NOT NULL,name varchar(32) NOT NULL);
CREATE TABLE grade_info_1152455 (owner int(4) NOT NULL,grade_num int NOT NULL,type varchar(32) NOT NULL);

2、向测试表中插入数据。

INSERT INTO user1_1152455 VALUES(1,'tm'),(2,'wwy'),(3,'zk'),(4,'qq'),(5,'lm');
INSERT INTO grade_info_1152455 VALUES(1,3,'add'),(2,3,'add'),(1,1,'reduce'),(3,3,'add'),(4,3,'add'),(5,3,'add'),(3,1,'reduce');

3、通过别名查询表。

select enable.id,enable.name,label.version account
from(
select owner,version,rank()over(order by version desc) day
from(
select owner,sum(if(type='add',grade_num,-1*grade_num)) version
from grade_info_1152455
group by owner
) status
) label
join user1_1152455 enable
on label.owner = enable.id
where day=1;

查询结果为如下：

 id | name | account
----+------+---------
  2 | wwy  |       3
  4 | qq   |       3
  5 | lm   |       3
(3 rows)

UNION [ALL] 语句中使用ORDER BY子句

功能描述

UNION计算多个SELECT语句返回行集合的并集。

Vastbase在MySQL兼容模式下支持使用UNION [ALL]语句将两个或多个查询结果合并，并通过ORDER BY子句排序。

注意事项

UNION子句有如下约束条件：

• 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。

• 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。

语法格式

select_statement UNION [ALL] select_statement  ORDER BY column_name [,...] [ ASC | DESC ];

参数说明

• select_statement

  查询语句select_statement可以是任何没有LIMIT、FOR UPDATE子句的SELECT语句。

  如果用圆括弧包围，ORDER BY和LIMIT也可以附着在子表达式里。

• ORDER BY column_name

  指定ORDER BY时，将查询结果根据column_name的值按照顺序排序。可以指定单列或多个列，多个列名间用逗号隔开。

  对于ORDER BY表达式中包含多列的情况：

  • 首先根据最左边的列进行排序，如果这一列的值相同，则根据下一个表达式进行比较，依此类推。

  • 如果对于所有声明的表达式都相同，则按随机顺序返回。

• [ ASC | DESC ]

  • ASC：升序排列。默认值。

  • DESC：降序排列。

示例

1、创建并切换至兼容模式为MySQL的数据库db_mysql。

CREATE DATABASE db_mysql dbcompatibility='B';    
\c db_mysql

2、创建测试表并插入数据。

CREATE TABLE a(id int,msg text);
CREATE TABLE b(id int,msg text);
INSERT INTO a VALUES(1,'a');
INSERT INTO a VALUES(2,'b');
INSERT INTO b VALUES(1,'a');
INSERT INTO b VALUES(2,'b');

3、使用UNION ALL语句返回所有查询结果并使用ORDER BY子句排序。

SELECT * FROM a UNION ALL SELECT * FROM b ORDER BY id;

结果返回如下：

 id | msg
----+-----
  1 | a
  1 | a
  2 | b
  2 | b
(4 rows)

4、使用UNION 语句压缩重复结果返回数据并使用ORDER BY子句排序。

SELECT * FROM a UNION SELECT * FROM b ORDER BY id;

结果返回如下：

 id | msg
----+-----
  1 | a
  2 | b
(2 rows)

JOIN不加关联条件

功能描述

支持JOIN不加关联条件，返回结果为笛卡尔积的形式。即JOIN不带on/using，效果与CROSS JOIN一致。

语法格式

[JOIN | INNER JOIN] {ON join_condition | USING ( join_column [, ...] ) }

参数说明

UNION子句

UNION计算多个SELECT语句返回行集合的并集。

UNION子句有如下约束条件：

• 除非声明了ALL子句，否则缺省的UNION结果不包含重复的行。
• 同一个SELECT语句中的多个UNION操作符是从左向右计算的，除非用圆括弧进行了标识。
• FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE和FOR KEY SHARE不能在UNION的结果或输入中声明。

一般表达式：

select_statement UNION [ALL] select_statement

select_statement可以是任何没有ORDER BY、LIMIT、FOR UPDATE，FOR NO KEY UPDATE，FOR SHARE或FOR KEY SHARE子句的SELECT语句。如果用圆括弧包围，ORDER BY和LIMIT可以附着在子表达式里。

示例

1、创建两张测试表并插入数据。

create table student(sid int, name varchar(30), class_id int);
create table class (cid int, class_name varchar(30));
insert into student values(1,'a1',100);
insert into student values(2,'a2',101);
insert into class values(100,'class1');
insert into class values(101,'class2');
insert into class values(102,'class3');

2、有关联条件JOIN。

select * from student inner join class;

返回结果为：

 sid | name | class_id | cid | class_name
-----+------+----------+-----+------------
   1 | a1   |      100 | 100 | class1
   2 | a2   |      101 | 100 | class1
   1 | a1   |      100 | 101 | class2
   2 | a2   |      101 | 101 | class2
   1 | a1   |      100 | 102 | class3
   2 | a2   |      101 | 102 | class3
(6 rows)

3、Vastbase在MySQL兼容模式下支持无关联条件的JOIN，也就是说下面语句等价于步骤2，返回结果也与步骤2相同。

select * from student,class;

支持指定多个分区查询数据

功能描述

SELECT语句中支持PARTITION子句指定多个分区查询数据。

注意事项

无。

示例

1、创建测试表并插入数据。

CREATE TABLE devices_list(
id serial primary key not null,
col int)
PARTITION BY LIST (col)
(
PARTITION p1 VALUES (10),
PARTITION p2 VALUES (20),
PARTITION p3 VALUES (30)
);
insert into devices_list(col) values(10),(10),(20),(20),(30);

2、指定多个分区查询数据。

select * from devices_list partition(p1,p2);
select * from devices_list partition(p2,p3);
select * from devices_list partition(p1,p2,p3);
select * from devices_list partition(p3,p2,p1);

返回结果分别如下：

 id | col
----+-----
  1 |  10
  2 |  10
  3 |  20
  4 |  20
(4 rows)

 id | col
----+-----
  3 |  20
  4 |  20
  5 |  30
(3 rows)

 id | col
----+-----
  1 |  10
  2 |  10
  3 |  20
  4 |  20
  5 |  30
(5 rows)

 id | col
----+-----
  1 |  10
  2 |  10
  3 |  20
  4 |  20
  5 |  30
(5 rows)