SELECT

功能描述

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

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

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

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

允许SELECT后面的非分组列不出现在GROUP BY子句中。
支持DISTINCT语句的目标列中，使用ORDER BY中未列出的列。
支持使用关键字作为别名。
支持在UNION [ALL] 语句中使用ORDER BY 子句。
支持JOIN不加关联条件，返回结果为笛卡尔积的形式。即JOIN不带on/using，效果与CROSS JOIN一致。
支持PARTITION子句可指定多个分区查询数据。
支持子查询识别上层查询字段别名
支持字符串类型的数字排序
关联的同名字段不需要指定表名
支持INNER JOIN连接条件使用WHERE子句
表达式的别名，支持使用单引号的字符串。
order by支持collation： gbk_chinese_ci。

注意事项

该功能仅在数据库兼容模式为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 支持作为别名的非保留关键字

ABORT_P	ABSOLUTE_P	ACCESS	ACCOUNT	ACTION	ADD_P	ADMIN
AGGREGATE	ALGORITHM	ALSO	ALTER	ALWAYS	APP	APPEND
ASSERTION	ASSIGNMENT	ATTRIBUTE	AUDIT	AUTOEXTEND	AUTOMAPPED	AUTOEXTEND_SIZE
AVG_ROW_LENGTH	BACKWARD	BARRIER	BEFORE	BEGIN_P	BEGIN_NON_ANOYBLOCK	BLANKS
BLOCKCHAIN	CACHE	CALL	CALLED	CANCELABLE	CASCADE	CASCADED
AFTER	ARCHIVE	AUTO_INCREMENT	BLOB_P	CATALOG_P	CLASS	COLUMN_ENCRYPTION_KEY
CHAIN	CHANGE	CHARACTERISTICS	CHARACTERSET	CHARSET	CHECKPOINT	CHECKSUM
CLEAN	CLIENT	CLIENT_MASTER_KEY	CLIENT_MASTER_KEYS	CLOB	CLOSE	CLUSTER
COLUMN_ENCRYPTION_KEYS	COLUMNS	COMMENT	COMMENTS	COMMIT	COMMITTED	COMPATIBLE_ILLEGAL_CHARS
COMPLETION	COMPRESS	COMPRESSION	CONDITION	CONFIGURATION	CONNECT	CONNECTION
CONSTRAINTS	CONTAINS	CONTENT_P	CONTINUE_P	CONTVIEW	CONVERSION_P	COORDINATOR
COPY	COST	CSV	CUBE	CURRENT_P	CURSOR	CYCLE
DATABASE	DATABASES	DATAFILE	DATANODE	DATANODES	DATATYPE_CL	DATE_FORMAT_P
COMPLETE	CONSTANT	COORDINATORS	DATA_P	DAY_HOUR_P	DEFAULTS	DESCRIBE
DISK	DUPLICATE	LISTEN	LOAD	LOCAL	LOCATION	LOCK_P
DAY_MICROSECOND_P	DAY_MINUTE_P	DAY_P	DAY_SECOND_P	DBCOMPATIBILITY_P	DEALLOCATE	DECLARE
DEFERRED	DEFINER	DEFINER	DELETE_P	DELIMITER	DELIMITERS	DELTA
DETERMINISTIC	DICTIONARY	DIRECT	DIRECTORY	DISABLE_P	DISCARD	DISCONNECT
DISTRIBUTE	DISTRIBUTION	DOCUMENT_P	DOMAIN_P	DOUBLE_P	DROP	DUMPFILE
EACH	ESCAPE	EXTENSION	FORCE	HOUR_SECOND_P	INHERITS	KEYS
ELASTIC	ESCAPED	EXTERNAL	FORMATTER	HOLD	INITIAL_P	KEY_BLOCK_SIZE
ENABLE_P	ESCAPING	FAMILY	FORWARD	IDENTIFIED	INITRANS	KEY_PATH
ENCLOSED	EVENT	FAST	FUNCTION	IDENTITY_P	INLINE_P	KEY_STORE
ENCODING	EVENTS	FEATURES	FUNCTIONS	IGNORE_EXTRA_DATA	INPLACE	KILL
ENCRYPTED	EVERY	FENCED	GENERATED	IMMEDIATE	INPUT_P	LABEL
ENCRYPTED_VALUE	EXCHANGE	FIELDS	GLOBAL	IMMUTABLE	INSENSITIVE	LANGUAGE
ENCRYPTION	EXCLUDE	FILEHEADER_P	GRANTED	IMPLICIT_P	INSERT	LARGE_P
ENCRYPTION_TYPE	EXCLUDING	FILLER	GRANTS	INCLUDE	INSERT_METHOD	LAST_P
ENDS	EXCLUSIVE	FILL_MISSING_FIELDS	HANDLER	INCLUDING	INSTEAD	LC_COLLATE_P
ENGINE_ATTRIBUTE	EXECUTE	FILTER	HEADER_P	INCREMENT	INTERNAL	LC_CTYPE_P
ENGINE_P	EXPANSION	FIRST_P	HOSTS	INCREMENTAL	INVISIBLE	LEAKPROOF
ENFORCED	EXPIRED_P	FLUSH	HOUR_MICROSECOND_P	INDEXES	INVOKER	LEVEL
EOL	EXPLAIN	FOLLOWING	HOUR_MINUTE_P	INFILE	IP	LINES
ERRORS	EXTENDED	FOLLOWS_P	HOUR_P	INHERIT	ISOLATION	LIST
MASKING	MODE	NOTIFY	PACKAGE	PRECEDING	QUARTER	REINDEX
MASTER	MODEL	NOWAIT	PACKAGES	PREDICT	QUERY	RELATIVE_P
LOCKED	LOG_P	LOGGING	LOGIN_ANY	LOGIN_FAILURE	LOGIN_SUCCESS	LOGOUT
LOGS	LOOP	MAPPING	REUSE	REVOKE	ROLE	ROLES
MATCH	MODIFIES	NULLCOLS	PARSER	PREFERRED	QUICK	RELEASE
MATCHED	MONTH_P	NULLS_P	PARTIAL	PREFIX	QUOTE	RELOPTIONS
MATERIALIZED	MOVE	NUMSTR	PARTIAL_EMPTY_PREC	PREPARE	RANDOMIZED	REMOTE_P
MAX_ROWS	MOVEMENT	OBJECT_P	PARTITION	PREPARED	RANGE	REMOVE
MAXEXTENTS	NAME_P	OF	PARTITIONS	PRESERVE	RATIO	RENAME
ROLLBACK	ROLLUP	ROTATION	ROUTINE	ROWS	RULE	ROW_FORMAT
MAXSIZE	NAMES	OFF	PASSING	PRIOR	RAW	REORGANIZE
MAXTRANS	NEXT	OIDS	PASSWORD	PRIORER	RAW	REPEAT
MEMORY	NGRAM	OPTIMIZATION	PCTFREE	PRIVATE	READ	REPEATABLE
MERGE	NO	OPTIMIZE	PER_P	PRIVILEGE	READS	REPLACE
SAMPLE	SAVEPOINT	SCHEDULE	SCHEMA	SCHEMAS	SCROLL	SEARCH
MICROSECOND_P	NO_WRITE_TO_BINLOG	OPTION	PERCENT	PRIVILEGES	REASSIGN	REPLICA
MIN_ROWS	NOCOMPRESS	OPTIONALLY	PERM	PROCEDURAL	REBUILD	RESET
MINEXTENTS	NODE	OPTIONS	PLAN	PROCESSLIST	RECHECK	RESIZE
MINUTE_MICROSECOND_P	NOLOGGING	OUTFILE	PLANS	PROFILE	RECURSIVE	RESOURCE
MINUTE_P	NOMAXVALUE	OWNED	POLICY	PUBLICATION	REDISANYVALUE	RESTART
MINUTE_SECOND_P	NOMINVALUE	OWNER	POOL	PUBLISH	REF	RESTRICT
MINVALUE	NOTHING	PACK_KEYS	PRECEDES_P	PURGE	REFRESH	RETURNS
SECOND_MICROSECOND_P	SHUTDOWN	STANDALONE_P	STORED	TEMPLATE	TSTIME	USE
SECOND_P	SIBLINGS	START	STRATIFY	TEMPORARY	TYPE_P	USEEOF
SECONDARY_ENGINE_ATTRIBUTE	SIMPLE	STARTING	STREAM	TEMPTABLE	TYPES_P	VACUUM
SECURITY	SIZE	STARTS	STRICT_P	TERMINATED	UNBOUNDED	VALID
SEPARATOR_P	SKIP	STATEMENT	STRIP_P	THAN	UNCOMMITTED	VALIDATE
SEQUENCE	SLAVE	STATEMENT_ID	SUBPARTITION	TIMESTAMP_FORMAT_P	UNENCRYPTED	VALIDATION
SEQUENCES	SLICE	STATISTICS	SUBPARTITIONS	TIME_FORMAT_P	UNDEFINED	VALIDATOR
SERIALIZABLE	SMALLDATETIME_FORMAT_P	STATS_AUTO_RECALC	SUBSCRIPTION	TRANSACTION	UNKNOWN	VALUE_P
SERVER	SNAPSHOT	STATS_PERSISTENT	SYNONYM	TRANSFORM	UNLIMITED	VARIABLES
SESSION	SOURCE_P	STATS_SAMPLE_PAGES	SYSID	TRIGGER	UNLISTEN	VCGROUP
SET	SPACE	STATUS	SYS_REFCURSOR	TRIGGERS	UNLOCK	VERSION_P
SETS	SPILL	STDIN	SYSTEM_P	TRUNCATE	UNLOGGED	VIEW
SHARE	SPLIT	STDOUT	TABLES	TRUSTED	UNTIL	VISIBLE
SHIPPABLE	SQL	STORAGE	TABLESPACE	TSFIELD	UNUSABLE	VOLATILE
SHOW	STABLE	STORE_P	TEMP	TSTAG	WORK	WORKLOAD
WEAK	WHILE_P	WHITESPACE_P	WRAPPER	WRITE	XML_P	YEAR_MONTH_P
YEAR_P	YES_P	ZONE	WAIT	UPDATE

注意事项

无。

示例

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、创建测试表并插入数据。

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');

2、使用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)

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

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

结果返回如下：

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

关联的同名字段不需要指定表名

功能描述

Vastbase在MySQL兼容模式下，支持多表关联时同名字段不需要指定表名。当存在多表关联的时，对于同名字段，group by和order by 后可以省略掉表名，表名默认跟目标列中出现的表名保持一致。

从V2.2 Build 10(Patch No.9)开始，参数vastbase_sql_mode同时控制了B模式下的多种SQL校验规则，请注意参数取值对其它功能的影响。

注意事项

支持版本：
- Vastbase G100 V2.2 Build 10(Patch No.8)及以后补丁版本。
- Vastbase G100 V2.2 Build 12及以后版本。
仅当参数vastbase_sql_mode的取值中不包含ONLY_FULL_GROUP_BY时支持此特性。

示例

1、设置参数vastbase_sql_mode的值（会话级别）。

set vastbase_sql_mode='';

2、创建测试表，两张表中存在同名字段”customernumber”。

create table customers(
    customernumber integer NOT NULL,
    customername varchar(50) NOT NULL,
    contactlastname varchar(50) NOT NULL,
    contactfirstname varchar(50) NOT NULL,
    addressline1 varchar(50) NOT NULL,
    addressline2 varchar(50),
    city varchar(50) NOT NULL,
    state varchar(50),
    postalcode varchar(15),
    country varchar(50) NOT NULL,
    salesrepemployeenumber integer,
    creditlimit numeric(10,2))
    WITH (orientation=row,compression=no,fillfactor=80);

CREATE TABLE orders(                   
	ordernumber integer(32) NOT NULL,  
	orderdate date NOT NULL,           
	requireddate date NOT NULL,        
	shippeddate date,                  
	status varchar(15) NOT NULL,       
	comments text,                     
	customernumber integer(32) NOT NULL
);

3、插入测试数据。

insert into customers values('103','Atelier graphique', 'Schmitt','Carine','54,rue Royale',NULL,'Nantes',NULL,'44000','France','1370','21000');
insert into customers values('112','Signal Gift Stores','king','Jean','8489 Strong St.',NULL,'Las Vegas',NULL,'83030','USA','1166','71800');
insert into customers values('114','Australian Collectors','Co.','Ferguson Peter','636 St Kilda Road','Level 3','Melbourne','Victoria','3004','Australia','1611','117300');

insert into orders values('10100','2013-01-06','2013-01-13','2013-01-10','Shipped',NULL,'103');
insert into orders values('10102','2013-01-10','2013-01-18','2013-01-14','shipped',NULL,'112');
insert into orders values('10101','2013-01-09','2013-01-18','2013-01-11','shipped','check on availability.','114');

4、使用group by、having、order by不指定表名查询。

SELECT
customers.customerNumber,  
customerName,
orderDate,
LEAD(orderDate,1) OVER (
PARTITION BY customerNumber
ORDER BY orderDate ) nextOrderDate
FROM
orders
INNER JOIN customers USING (customerNumber)
group by customerNumber,orderDate
having customerNumber
order by customerNumber;

结果返回如下：

 customerNumber |     customerName      | orderDate  | nextOrderDate
----------------+-----------------------+------------+---------------
            103 | Atelier graphique     | 2013-01-06 |
            112 | Signal Gift Stores    | 2013-01-09 |
            114 | Australian Collectors | 2013-01-10 |
(3 rows)

INNER JOIN

功能描述

在MySQL兼容模式下支持INNER JOIN连接条件使用WHERE子句。

语法格式

SELECT col_name FROM table1 INNER JOIN table2 WHERE condition

参数说明

col_name

字段名。
table1、table2

内连接的表名。
condition

条件表达式。

示例

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

create table test1(id int,name varchar(10),score numeric,date1 date,c1 bytea);
insert into test1 values(1,'aaa',97.1,'1999-12-12','0101');
insert into test1 values(5,'bbb',36.9,'1998-01-12','0110');
insert into test1 values(3,'ccc',89.2,'2200-03-12','0111');
insert into test1 values(7,'uuu',99.9,'1987-01-01','1000');
insert into test1 values(30,'ooo',90.1,'2023-01-30','1001');
insert into test1 values(6,'hhh',60,'2022-12-22','1010');
insert into test1 values(7,'fff',71,'2001-11-23','1011');
insert into test1 values(-1,'yaya',77.7,'2008-09-10','1100');
insert into test1 values(7,'fff',71,'2001-11-23','1011');  
create table test2(id int,name varchar(10),score numeric,date1 date,c1 bytea);
insert into test2 values(1,'aaa',99.1,'1998-12-12','0101');
insert into test2 values(2,'hhh',36.9,'1996-01-12','0110');
insert into test2 values(3,'ddd',89.2,'2000-03-12','0111');
insert into test2 values(7,'uuu',60.9,'1997-01-01','1000');
insert into test2 values(9,'ooo',90.1,'2021-01-30','1001');
insert into test2 values(6,'hhh',90,'2022-10-22','1010');
insert into test2 values(11,'eee',71,'2011-11-20','1011');
insert into test2 values(-1,'yaya',76.7,'2008-09-10','1100');
insert into test2 values(7,'uuu',60.9,'1997-01-01','1000');

2、使用where指定连接条件查询数据。

select * from test1 inner join test2 where test1.id=test2.id and test1.score>60;

结果返回如下：

 id | name | score |   date1    |     c1     | id | name | score |   date1    |     c1
----+------+-------+------------+------------+----+------+-------+------------+------------
  1 | aaa  |  97.1 | 1999-12-12 | \x30313031 |  1 | aaa  |  99.1 | 1998-12-12 | \x30313031
  3 | ccc  |  89.2 | 2200-03-12 | \x30313131 |  3 | ddd  |  89.2 | 2000-03-12 | \x30313131
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
 -1 | yaya |  77.7 | 2008-09-10 | \x31313030 | -1 | yaya |  76.7 | 2008-09-10 | \x31313030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
(9 rows)

3、缺省INNER关键字查询数据。

select * from test1 join test2 where test1.date1=test2.date1;

结果返回如下：

 id | name | score |   date1    |     c1     | id | name | score |   date1    |     c1
----+------+-------+------------+------------+----+------+-------+------------+------------
 -1 | yaya |  77.7 | 2008-09-10 | \x31313030 | -1 | yaya |  76.7 | 2008-09-10 | \x31313030
(1 row)

4、查询具体列值。

select test1.score from test2 join test1 where test2.id=test1.id and test2.name='yaya';

结果返回如下：

 score
-------
  77.7
(1 row)

5、使用distinct关键字返回唯一不同的值。

select distinct test1.id from test1 inner join test2 where test1.id=test2.id;

结果返回如下：

 id
----
  1
  3
  6
  7
 -1
(5 rows)

6、使用order by语句。

select * from test1 inner join test2 where test1.id=test2.id order by 1,2;

结果返回如下：

 id | name | score |   date1    |     c1     | id | name | score |   date1    |     c1
----+------+-------+------------+------------+----+------+-------+------------+------------
 -1 | yaya |  77.7 | 2008-09-10 | \x31313030 | -1 | yaya |  76.7 | 2008-09-10 | \x31313030
  1 | aaa  |  97.1 | 1999-12-12 | \x30313031 |  1 | aaa  |  99.1 | 1998-12-12 | \x30313031
  3 | ccc  |  89.2 | 2200-03-12 | \x30313131 |  3 | ddd  |  89.2 | 2000-03-12 | \x30313131
  6 | hhh  |    60 | 2022-12-22 | \x31303130 |  6 | hhh  |    90 | 2022-10-22 | \x31303130
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
(10 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)

支持子查询识别上层查询字段别名

功能描述

Vastbase G100 在MySQL兼容性模式下支持子查询中引用上层查询输出列的别名。

示例

1、创建测试表。

create table tab_1 (a1 int, b1 int, c1 int, d1 int,e1 text,f1 date);
create table tab_2 (a2 int, b2 int, c2 int, d2 int,e2 text,f2 date);
create table tab_3 (a3 int, b3 int, c3 int, d3 int,e3 text,f3 date);

2、插入测试数据。

insert into tab_1 values
(1, 1, 1, 1,'A10Z','2000-03-01')
,(1, 1, 1, 2,'A1Z' ,'2000-03-02')
,(1, 1, 2, 1,'A11Z','2000-03-05')
,(1, 1, 2, 2,'A2Z' ,'2000-03-04')
,(1, 2, 1, 1,'A13Z','2000-03-05')
,(1, 2, 1, 2,'A3Z' ,'2000-03-19')
,(1, 2, 2, 1,'A15Z','2000-03-05')
,(1, 2, 2, 2,'A4Z' ,'2000-03-19')
,(1, 3, 3, 3,'A10Z','2000-03-05')
,(2, 5, 5, 5,'A5Z' ,'2000-03-10')
,(2, NULL, 6, 6,null,'2000-04-01')
,(2, 6, NULL, 6,'A15Z',null)
,(2, 6, 6, NULL,'A6Z','2000-03-11')
,(2, NULL, NULL, 7,'A5Z','2000-03-02')
,(2, NULL, 7, NULL,'A7Z','2000-03-03')
,(2, 7, NULL, NULL,'A10Z','2000-03-10')
,(3, NULL, NULL, NULL,'A9Z','2000-03-05')
;

insert into tab_1 select generate_series(1, 100)%98, generate_series(1, 100)%20, generate_series(1, 100)%13, generate_series(1, 100)%6,'A'||(generate_series(11,20))||'Z', date'2000-03-01'+generate_series(11,20);

insert into tab_2 select * from tab_1;
insert into tab_2 select generate_series(1, 50)%48, generate_series(1, 50)%28, generate_series(1, 50)%12, generate_series(1, 50)%9,'A'||(generate_series(1,10))||'Z', date'2000-03-01'+generate_series(1,30);

insert into tab_3 select generate_series(1, 100)%98, generate_series(1, 100)%20, generate_series(1, 100)%13, generate_series(1, 100)%6,'A'||(generate_series(1,20))||'Z', date'2000-03-01'+generate_series(1,74);

3、使用上层查询字段别名进行查询。

select a1 alias_a1,b1,c1,d1,e1,f1 from tab_1 where a1 = (with tmp as (select d2 from tab_2 where b2=alias_a1)
select count(*) from (select 'abc', d2 from tmp))
order by 1,2,3,4;

结果返回如下：

 alias_a1 | b1 | c1 | d1 |  e1  |     f1
----------+----+----+----+------+------------
       11 | 11 | 11 |  5 | A11Z | 2000-03-12
(1 row)

字符串类型的数字排序

功能描述

Vastbase支持将数据定义的是varchar类型，实际存放的是字符串类型的数字按照数字顺序排序。

语法格式

SELECT [column] FROM [table] order by (column+0);

参数说明

column+0

表示将字符串排序方式改为按照数字排序的语法。

示例

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');

2、正常查询。

select * from test;

结果显示如下：

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

3、排序查询进行对比。

select * from test order by (id+0);

结果显示如下，字段id按数字顺序排序：

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

表达式的别名，支持使用单引号的字符串

功能描述

在MySQL兼容模式下，支持类似如下的两种单引号别名书写方式。

select 1 as 'col';

和

select 1 'col';

使用单引号作为别名和使用双引号作为别名等价，都保留原始字符串且不受到关键字影响。

注意事项

为兼容原始的部分语法内容，需要保留部分类型转化的写法。表示类型转化的情况如：
```
select bool '1';
```
被视为类型转化的类型名如下所示：

year、Json、Jsonb、Box、Circle、Polygon、Bytea、Date、Timetz、Timestamptz、Varchar、Bpchar、nvarchar2、Bpchar、Text、Time、Timestamp、Smalldatetime、int4、int1、int2、int8、float4、numeric、float8、Bool、Bit、Varbit、Binary、Name、Xml、Abstime、Path、point、txid_snapshot、Lseg、tinterval、money、Reltime

其他没有展示在上文列表里的type暂时不支持。

上文的type展示的是pg_type的typname内容，实际需要使用其类型对应的实际类型写法，例如timetz 使用时是 SELECT time with time zone '1';
此功能实现后，无法支持原始如下内容表示的类型转化：
```
select pg_catalog.date '2020-01-01';
```
即，无法使用schema.type '字符串'的表示形式来表示对字符串的类型转化，类似这样的写法都会被认为字符串是其修饰的列的别名。

示例

1、创建行存表并插入数据。

CREATE TABLE table_1172321 (id int,col1 varchar(20)
) WITH (ORIENTATION = ROW);
insert into table_1172321 values(1,'zhangsan'),(2,'lisi');

2、支持在别名使用单引号。

select col1  '姓 名' from table_1172321;
select col1  'name_test' from table_1172321;

返回结果分别为：

  姓 名
----------
 zhangsan
 lisi
(2 rows)

 name_test
-----------
 zhangsan
 lisi
(2 rows)

3、指定类型表示类型转化。

select year '2020' from table_1172321;

返回结果为：

 year
------
 2020
 2020
(2 rows)

order by支持collation： gbk_chinese_ci

功能描述

在MySQL兼容模式下，ORDER BY支持拉丁语系字符的字符集转换和排序操作，即使用CONVERT函数和COLLATE子句来按照指定的字符集和排序规则进行排序。

CONVERT函数支持拉丁语系字符的字符集转换功能，支持将拉丁语系字符转为GBK的字符集。
order by支持使用collation： gbk_chinese_ci来指定使用GBK字符集和gbk_chinese_ci排序规则进行排序。

注意事项

该功能受参数enable_convert_illegal_chars影响，当该参数设置为on时，支持转换无法识别的字符。当编码为GBK时，会将不支持的字符转换为3F，即“?”。

语法格式

无。

示例

1、设置参数enable_convert_illegal_chars为on。

set enable_convert_illegal_chars on;

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

create table my_table_1173425(col1 int,col2 varchar(30),col3 varchar(30));
insert into my_table_1173425 values(1,'张三','男');
insert into my_table_1173425 values(1,'张四','男');
insert into my_table_1173425 values(1,'臧三','男');
insert into my_table_1173425 values(1,'臧腾','男');
insert into my_table_1173425 values(1,'āēģīŗš','男');
insert into my_table_1173425 values(1,'Break āēģīŗš','男');

3、查询数据。

select * from my_table_1173425 order by col2;

返回结果为：

 col1 |     col2     | col3
------+--------------+------
    1 | āēģīŗš       | 男
    1 | Break āēģīŗš | 男
    1 | 张三         | 男
    1 | 张四         | 男
    1 | 臧三         | 男
    1 | 臧腾         | 男
(6 rows)

4、调用convert函数将字段col2转换为GBK字符集并按照gbk_chinese_ci排序规则进行排序。

select * from my_table_1173425 order by convert(col2 using gbk) collate gbk_chinese_ci;

返回结果为：

 col1 |     col2     | col3
------+--------------+------
    1 | Break āēģīŗš | 男
    1 | āēģīŗš       | 男
    1 | 臧三         | 男
    1 | 臧腾         | 男
    1 | 张三         | 男
    1 | 张四         | 男
(6 rows)

VastbaseG100

SELECT

功能描述

注意事项

语法格式

参数说明

SELECT…GROUP BY

功能描述

注意事项

语法格式

参数说明

示例

SELECT DISTINCT…ORDER BY

功能描述

注意事项

语法格式

参数说明

示例

支持使用关键字作为别名

功能描述

注意事项

示例

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

功能描述

注意事项

语法格式

参数说明

示例

关联的同名字段不需要指定表名

功能描述

注意事项

示例

INNER JOIN

功能描述

语法格式

参数说明

示例

JOIN不加关联条件

功能描述

语法格式

参数说明

示例

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

功能描述

注意事项

示例

支持子查询识别上层查询字段别名

功能描述

示例

字符串类型的数字排序

功能描述

语法格式

参数说明

示例

表达式的别名，支持使用单引号的字符串

功能描述

注意事项

示例

order by支持collation： gbk_chinese_ci

功能描述

注意事项

语法格式

示例

弹框头部