目录ClickHouse的JOIN逻辑INSERT SELECT临时表窗口函数单引号和双引号
下面只展示和MySQL或HIVE区别较大的语法
例如ClickHouse建库语法和MySQL几乎1卵样的不予展示
ClickHouse的JOIN逻辑
# 创建左表CREATE TABLE default.temp_left( `a` String COMMENT \’匹配键\’)ENGINE = MergeTreeORDER BY (a);INSERT INTO default.temp_leftSELECT \’A1\’ AS a UNION ALLSELECT \’A1\’ AS a UNION ALLSELECT \’A2\’ AS a UNION ALLSELECT \’A3\’ AS a;# 创建右表CREATE TABLE default.temp_right( `a` String COMMENT \’匹配键\’, `b` Nullable(UInt32), `c` UInt32)ENGINE = MergeTreeORDER BY (a);INSERT INTO default.temp_rightSELECT \’A2\’ AS a,9 AS b,8 AS c UNION ALLSELECT \’A3\’ AS a,9 AS b,8 AS c UNION ALLSELECT \’A4\’ AS a,9 AS b,8 AS c;# 左联SELECT *FROM default.temp_left leLEFT JOIN default.temp_right ri ON le.a=ri.a
左联测试结果
在右表中，b允许空，a和c冇允许空 左联后，联不上的a是空字符串，联不上的b是NULL，联不上的c是0
INSERT SELECTINSERT INTO t2WITH a AS (SELECT * FROM t1)SELECT * FROM a;
和HIVE、MySQL等不一样，ClickHouse的INSERT写在WITH之前
临时表当回话结束时，临时表将随会话一起消失临时表仅能用Memory表引擎无法为临时表指定数据库，它是在数据库之外创建的当查询没有指定库，且临时表与另一个表名相同 时，会优先使用临时表CREATE TEMPORARY TABLE temp_t(`a` String,`b` Int32);INSERT INTO temp_t VALUES (\’AB\’,3),(\’CC\’,4);SELECT * FROM temp_t;# 结束会话后，临时表不存在
窗口函数CREATE TEMPORARY TABLE sales(name String COMMENT \’产品\’,city String COMMENT \’城市\’,sale Int32 COMMENT \’销量\’);INSERT INTO sales VALUES(\’椰子\’,\’佛山\’,99),(\’雪梨\’,\’佛山\’,77),(\’苹果\’,\’佛山\’,88),(\’椰子\’,\’广州\’,80),(\’雪梨\’,\’广州\’,80),(\’苹果\’,\’广州\’,70);
SELECT city ,groupArray(name) OVER (PARTITION BY city)FROM sales;
SELECT city, name, sale, rank() OVER(PARTITION BY city ORDER BY sale DESC)FROM sales;
单引号和双引号
多数情况使用单引号
SELECT \”abc\”;# 报错SELECT \’abc\’;# 正常查询，返回字符串CREATE TABLE default.temp_t( `a` String COMMENT \”匹配键\”)ENGINE=Log;# 字段注释使用双引号报错CREATE TABLE default.temp_t( `a` String COMMENT \’匹配键\’)ENGINE=Log;# 正常建表
到此这篇关于MySQL ClickHouse不同于SQL的语法介绍的文章就介绍到这了,更多相关MySQL ClickHouse内容请搜索悠久资源网以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源网！
