Clickhouse数据库六：Sql操作

布满荆棘的人生 2022-11-12 10:52 194阅读 0赞

# Clickhouse数据库六：Sql操作 #

## 6.1 insert ##

*  insert values

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

*  insert select

INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

## 6.2 update和delete ##

ClickHouse提供了Delete 和Update的能力，这类操作被称为`Mutation查询`，它可以看做Alter 的一种。

虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，`Mutation语句是一种很“重”的操作，而且不支持事务`。

**“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。**

*  删除操作

alter table t_order_smt delete where sku_id ='sku_001';

*  修改操作

alter table t_order_smt 
    update total_amount=toDecimal32(2000.00,2) 
    where id =102;

由于操作比较“重”，所以 Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间。

## 6.3 查询操作 ##

clickhouse基本上与标准SQL 差别不大。

*  支持如下操作:  
    WITH clause  
    FROM clause  
    SAMPLE clause  
    JOIN clause  
    PREWHERE clause  
    WHERE clause  
    GROUP BY clause  
    LIMIT BY clause  
    HAVING clause  
    SELECT clause  
    DISTINCT clause  
    LIMIT clause  
    UNION ALL clause  
    INTO OUTFILE clause  
    FORMAT clause
 *  不支持  
    不支持窗口函数。  
    不支持自定义函数。
 *  group by 子句特殊说明:  
    GROUP BY 操作增加了 `with rollup\with cube\with totals` 用来计算小计和总计。

1.  插入数据

insert into  t_order_mt
    values(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
    (102,'sku_002',2000.00,'2020-06-01 12:00:00'),
    (103,'sku_004',2500.00,'2020-06-01 12:00:00'),
    (104,'sku_002',2000.00,'2020-06-01 12:00:00')
    (105,'sku_003',600.00,'2020-06-02 12:00:00'),
    (106,'sku_001',1000.00,'2020-06-04 12:00:00'),
    (107,'sku_002',2000.00,'2020-06-04 12:00:00'),
    (108,'sku_004',2500.00,'2020-06-04 12:00:00'),
    (109,'sku_002',2000.00,'2020-06-04 12:00:00'),
    (110,'sku_003',600.00,'2020-06-01 12:00:00')

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70]

1.  with rollup: 从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with rollup;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 1]

1.  with cube: 各种维度组合进行聚合

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with cube;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 2]

1.  with taotals: 仅仅多了一个总计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with totals;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 3]

## 6.4 alter操作 ##

同mysql的修改字段基本一致

*  新增字段

alter table tableName  add column  newcolname  String after col1;

*  修改字段类型

alter table tableName  modify column  newcolname  String    ；

*  删除字段

alter table tableName  drop column  newcolname   ;

## 6.5导出数据 ##

clickhouse-client  --query "select toHour(create_time) hr ,count(*) from test1.order_wide where 
    dt='2020-06-23'  group by hr" --format CSVWithNames> ~/rs1.csv

支持的数据格式: https://clickhouse.tech/docs/v19.14/en/interfaces/formats/

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70]: /images/20221022/299a96e0550e432790c4d8a731d42ecc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 1]: /images/20221022/667c188a1f014935b759ca1ea378fa07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 2]: /images/20221022/9f1ac35ede8b4af08b956ad9fd5c20dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 3]: /images/20221022/777290315d1e4813b1f9d9d72ebcbc59.png