Clickhouse数据库基本操作

一、先提出几个问题
1、  如何复制一个表？
2、  为何增加表列失败？
3、  如何替换表中某列的某个字符串？
二、先看看clickhouse的基本操作1.    创建数据库CREATE DATABASE语法：CREATE DATABASE [IF NOT EXISTS] db_name
如果数据库db_name已经存在，则不会创建新的db_name数据库。
2.    创建表CREATE TABLE语法：
CREATE TABLE [IF NOT EXISTS][db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIASexpr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...) ENGINE = engine
在指定的‘db’数据库中创建一个名为’name’的表，如果查询中没有数据库’db’，则默认使用当前选择的数据库作为’db’。后面的‘（）’括号中的内容为表结构及引擎声明。
其中：DEFAULT expr – 默认值，如果INSERT中不包含指定的列，那么将通过表达式计算它的默认值并填充它；
MATERIALIZED expr – 物化表达式，被该表达式指定的列不能被INSERT，因为它总是被计算出来的，对于INSERT而言，不需要考虑这些列。 另外，在SELECT查询中如果包含星号，此列不会被查询；
ALIAS expr – 别名，这样的列不会存储在表中。它的值不能够通过INSERT写入，同时使用SELECT查询星号时，这些列也不会被用来替换星号，但是它们可以显示的用于SELECT中，在这种情况下，在查询分析中别名将被替换。
三种方式创建表：
1)     直接创建语法：CREATE TABLE t1(id UInt16,name String) ENGINE=TinyLog
例如：create table t1(id UInt8,name String,address String)engine=MergeTree order by id





2）创建一个与其他表具有相同结构的表语法：CREATE TABLE [IF NOT EXISTS] [db.]table_name AS[db2.]name2 [ENGINE = engine]
例如：create table t2 as t1 engine=MergeTree order by id





可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与db2.name2使用相同的表引擎。
3）使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充它语法：CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...
例如：create table t3engine=TinyLog as select * from t1





3.     插入数据INSERT语法：INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), …
例如：insert into t1 (id,name,address) values(1,'aa','addr1'),(2,'bb','addr2')






也可以使用SELECT结果插入数据，语法：INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...
例如：insert into t2 select *from t1






4.     增加表的列ALTER…ADD语法：ALTER TABLE [db].name [ONCLUSTER cluster] ADD COLUMN ...
例如：alter table t1 add column age Int8增加age列类型为Int8






5.     修改表的列ALTER…MODIFY语法：ALTERTABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN ...
例如：alter table t1 modify
column age String修改age列类型为String






6.    删除列ALTER…DROP
语法：ALTERTABLE [db].name [ON CLUSTER cluster] DROP COLUMN ...
例如：alter table t1 drop column age删除age列
7.    打印数据库/表列表SHOW





打印数据库列表语法：SHOUW DATABASES
打印表列表语法：SHOW TABLES
例如：show tables






8.     查看表结构DESCRIBLE
语法：DESC|DESCRIBETABLE [db.]table [INTO OUTFILE filename] [FORMAT format]
例如：desc t1






9.    删除表语法：DROP [TEMPORARY] TABLE [IF
EXISTS] [db.]name [ON CLUSTER cluster]
例如：drop table t2






三、来解决先提出的几个问题1、  如何复制一个表？以复制demo库的t1表为例，可使用create table if not exists new_t1engine=TinyLog as select * from demo.t1






2、  为何增加表列失败？以向上问1中增加列（名为hh）为例：alter
table new_t1 add column hh String，结果报错，TinyLog存储类型不支持add column操作。






这是因为clickhouse修改表是通过alter实现的，而TinyLog不支持alter操作，若要使用alter语法，可在建表时使用MergeTree引擎。查看表引擎可通过select engine from system.parts where table=table_name查看，如下：






3、 如何替换表中某列的某个字符串？例如，替换问1中address列的‘addr’字符串为’no_where’，可使用alter table demo.t1 update address = replace(address,'addr','no_where’)where address is not null;