MySQL ClickHouse不同于SQL的语法介绍

数据运维 2023-04-16 捡田螺的小男孩手机阅读

目录 ClickHouse的JOIN逻辑 INSERT SELECT 临时表窗口函数单引号和双引号下面只展示和MySQL或HIVE区别较大的语法例如ClickHouse建库语法和MySQL几乎1卵样的不予展示 ClickHouse的JOIN逻辑 # 创建左

                        目录ClickHouse的JOIN逻辑INSERT SELECT临时表窗口函数单引号和双引号

下面只展示和MySQL或HIVE区别较大的语法

例如ClickHouse建库语法和MySQL几乎1卵样的不予展示

ClickHouse的JOIN逻辑

创建左表 CREATE TABLE default.temp_left( a String COMMENT '匹配键' )ENGINE = MergeTree ORDER BY (a); INSERT INTO default.temp_left SELECT 'A1' AS a UNION ALL SELECT 'A1' AS a UNION ALL SELECT 'A2' AS a UNION ALL SELECT 'A3' AS a;
创建右表 CREATE TABLE default.temp_right( a String COMMENT '匹配键', b Nullable(UInt32), c UInt32 )ENGINE = MergeTree ORDER BY (a); INSERT INTO default.temp_right SELECT 'A2' AS a,9 AS b,8 AS c UNION ALL SELECT 'A3' AS a,9 AS b,8 AS c UNION ALL SELECT 'A4' AS a,9 AS b,8 AS c;
左联 SELECT * FROM default.temp_left le LEFT JOIN default.temp_right ri ON le.a=ri.a

左联测试结果

在右表中，b允许空，a和c冇允许空左联后，联不上的a是空字符串，联不上的b是NULL，联不上的c是0

INSERT SELECT

INSERT INTO t2 WITH 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);