clickhouse基本语法

一.基本语法演示

show databases;
create database if not exists test1;
use test1;
select currentDatabase();   //查看当前使用的数据库drop database test1;
drop database test1;

二.基本的建表语法

• 注意在ck中关键字严格区分大小写
• ck中建表的时候，一定指定表引擎

create table tb_test1(id Int8,name String)engine=Memory;  //Memory引擎    --存储在内存中的引擎

desc tb_test1;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int8   │              │                    │         │                  │                │
│ name │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

三.DDL基础

create table tb_test1(id Int8,name String)engine=Memory();  //当引擎是Memory时无法修改表结构
//只有MergeTree支持表结构的修改MergeTree一定指定主键和排序字段 
//order by代表两个含义
create table test_alter1(
id Int8,
name String
)engine=MergeTree()order by id;  

• 查看建表语句 查看引擎类型和一些参数值

show create table test_alter1;
┌─statement────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE doit23.test_alter1(  
  `id` Int8,`name` String)ENGINE = MergeTreeORDER BY idSETTINGS index_granularity = 8192           │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘

• 修改表结构

alter table test_alter1 add column age UInt8;  //添加列
desc test_alter1;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int8   │              │                    │         │                  │                │
│ name │ String │              │                    │         │                  │                │
│ age  │ UInt8  │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘


alter table test_alter1 add column gender String after name;  //指定位置添加列
desc test_alter1;
┌─name───┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id     │ Int8   │              │                    │         │                  │                │
│ name   │ String │              │                    │         │                  │                │
│ gender │ String │              │                    │         │                  │                │
│ age    │ UInt8  │              │                    │         │                  │                │
└────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘


alter table test_alter1 drop column age;  //删除列
desc test_alter1;
┌─name───┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id     │ Int8   │              │                    │         │                  │                │
│ name   │ String │              │                    │         │                  │                │
│ gender │ String │              │                    │         │                  │                │
└────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘


alter table test_alter1 modify column gender UInt8 defalut 0;   //修改字段的数据类型
desc test_alter1;
┌─name───┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id     │ Int8   │              │                    │         │                  │                │
│ name   │ String │              │                    │         │                  │                │
│ gender │ UInt8  │ DEFAULT      │ 0                  │         │                  │                │
└────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘


alter table test_alter1 comment column name '用户名';  //添加表注释，内部使用的编码默认是UTF8
desc test_alter1;
┌─name───┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id     │ Int8   │              │                    │         │                  │                │
│ name   │ String │              │                    │ 用户名  │                  │                │
│ gender │ UInt8  │ DEFAULT      │ 0                  │         │                  │                │
└────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘


rename table test_alter1 to test_alter;  //修改表名
show tables;
┌─name───────┐
│ test_alter │
└────────────┘


rename table test_array to tb_array,test_date to tb_date;  //批量修改表名
show tables;
┌─name─────────┐
│ tb_array     │
│ tb_date      │ 
└──────────────┘

• 修改表位置

show tables from doit23;  //查看某个数据库下所有表
┌─name───────┐
│ test_alter │
└────────────┘

rename table tb_test1 to doit23.tb_test;    //移动表到另一个数据库中
show tables from doit23;
┌─name───────┐
│ tb_test    │
│ test_alter │
└────────────┘

• 设置表属性

create table test_prp(
id UInt8,
age UInt8 default 0,
name String comment '用户名',
role String  default 'VIP' comment '角色'
)engine=Memory;
desc test_prp;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ UInt8  │              │                    │         │                  │                │
│ age  │ UInt8  │ DEFAULT      │ 0                  │         │                  │                │
│ name │ String │              │                    │ 用户名  │                  │                │
│ role │ String │ DEFAULT      │ 'VIP'              │ 角色    │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

• 数据的导入方式

 create table tb_insert(
 id Int8,
 name String,
 age UInt8
 )engine=Memory;
 insert into tb_insert values(1,'zs',23),(2,'ls',24);   //第一种
 select * from tb_insert;
 ┌─id─┬─name─┬─age─┐
 │  1 │ zs   │  23 │
 │  2 │ ls   │  24 │
 └────┴──────┴─────┘


create table tb_insert1 as tb_insert;
desc tb_insert1;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int8   │              │                    │         │                  │                │
│ name │ String │              │                    │         │                  │                │
│ age  │ UInt8  │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
insert into tb_insert1 select * from tb_insert;   //第二种
select * from tb_insert1;
┌─id─┬─name─┬─age─┐
│  1 │ zs   │  23 │
│  2 │ ls   │  24 │
└────┴──────┴─────┘


create table tb_insert2(
uid Int8,
name String,
age UInt8,
city String,
gender String
)engine=Memory

cat user.txt
1,zss,23,BJ,M
2,lss,33,NJ,M
3,ww,21,SH,F

cat user.txt | clickhouse-client -q 'insert into table doit23.tb_insert2 format CSV' //第三种
select * from tb_insert2;
┌─uid─┬─name─┬─age─┬─city─┬─gender─┐
│  1  │ zss  │  23 │  BJ  │  M     │
│  2  │ lss  │  24 │  NJ  │  M     │
│  3  │ ww   │  24 │  SH  │  F     │
└─────┴──────┴─────┴──────┴────────┘
clickhouse-client -q 'insert into doit23.tb_insert2 format CSV' < user.txtselect * from tb_insert2;
┌─uid─┬─name─┬─age─┬─city─┬─gender─┐
│  1  │ zss  │  23 │  BJ  │  M     │
│  2  │ lss  │  24 │  NJ  │  M     │
│  3  │ ww   │  24 │  SH  │  F     │
└─────┴──────┴─────┴──────┴────────┘
┌─uid─┬─name─┬─age─┬─city─┬─gender─┐
│  1  │ zss  │  23 │  BJ  │  M     │
│  2  │ lss  │  24 │  NJ  │  M     │
│  3  │ ww   │  24 │  SH  │  F     │
└─────┴──────┴─────┴──────┴────────┘

clickhouse-client --format_csv_delimiter=',' -q 'insert into doit23.tb_insert2 format CSV' < user.txt //可以指定分隔符

• 更新删除数据只有MergeTree引擎的数据才能修改

alter table test_muta drop partition 'SH';     //删除分区数据
alter table test_muta delete where id=3;  --- 一定加条件     //条件删除数据
alter table tb_update update name='李四' where id=2;   //更新数据