clickhouse基础语法简介

1.常用SQL语法

-- 列出数据库列表
show databases;
 
-- 列出数据库中表列表
show tables;
 
-- 创建数据库
create database test;

--选中数据库
use test;
 
-- 删除一个表
drop table if exists test.t1;
 
-- 创建第一个表
create /*temporary*/ table /*if not exists*/ test.m1 (
 id UInt16
,name String
) ENGINE = Memory
;
-- 插入测试数据
insert into test.m1 (id, name) values (1, 'abc'), (2, 'bbbb');
 
-- 查询
select * from test.m1;

 

2.默认值

默认值 的处理方面， ClickHouse 中，默认值总是有的，如果没有显示式指定的话，会按字段类型处理：

• 数字类型， 0
• 字符串，空字符串
• 数组，空数组
• 日期， 0000-00-00
• 时间， 0000-00-00 00:00:00
• 注：NULLs 是不支持的

3.数据类型

• 整型：UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64
  
• 枚举类型：Enum8,Enum16
  存储为Int8或Int16的一组枚举字符串值。例：
  Enum8（'hello'= 1，'world'= 2）， 这个数据类型有两个值  'hello'和'world'。
  该数值必须在-128..127为Enum8和-32768..32767为Enum16。枚举的每个成员也必须有不同的数字。空字符串是一个有效的值。数字不需要是连续的，可以以任何顺序。是顺序无关的。

  在内存中，数据的存储方式与数字类型Int8和Int16相同。以文本格式读取时，读取字符串并查找相应的数值。如果找不到，则会抛出异常。当以文本格式写入时，查找存储的数字并写出相应的字符串。
  如果数字不对应已知的值，则会抛出异常。以二进制格式，信息以与Int8和Int16相同的方式保存。Enum的隐式默认值是具有最小数值的值

• 字符串(String、FixedString 和 UUID)
  1)String
          String 不限制长度，相当于Varchar、Text、Clob 和 Blob 等字符类型
  2)FixedString(N)
         FixedString(N)相当于Char，长度固定，数据长度不够时，添加空字节（null）；长度过长返回错误消息
  3)UUID
         32位，格式8-4-4-4-12，如果未被赋值，则用0填充

• 日期时间类型
  1)Date: 2020-02-02 精确到天
  2)DateTime: 2020-02-02 20:20:20 精确到秒
  3)DateTime64: 2020-02-02 20:20:20.335 精确到亚秒，可以设置精度均支持字符串写入

• 数组类型 Array(T)
  T 类型的数组。T型可以是任何类型，包括数组。我们不推荐使用多维数组，因为它们不被很好的支持（例如，除了内存表之外，你不能在多维数组中存储多维数组）

• 元组：Tuple
  由多个元素组成，允许不同类型
  创建数据：(T1, T2, …)，Tuple(T1, T2, …)

• 嵌套（Nested(Name1 Type1, Name2 Type2, ...)）

  嵌套数据结构类似于嵌套表。嵌套数据结构的参数（列名和类型）与 CREATE 查询类似。每个表可以包含任意多行嵌套数据结构。
  只支持一级嵌套。嵌套结构的列中，若列的类型是数组类型，那么该列其实和多维数组是相同的，所以目前嵌套层级的支持很局限（MergeTree 引擎中不支持存储这样的列）
  大多数情况下，处理嵌套数据结构时，会指定一个单独的列。为了这样实现，列的名称会与点号连接起来。这些列构成了一组匹配类型。在同一条嵌套数据中，所有的列都具有相同的长度。
  不能对整个嵌套数据结构执行 SELECT。只能明确列出属于它一部分列。

  CREATE TABLE test.visits
  (
      CounterID UInt32,
      StartDate Date,
      Sign Int8,
      IsNew UInt8,
      VisitID UInt64,
      UserID UInt64,
      ...
      Goals Nested
      (
          ID UInt32,
          Serial UInt32,
          EventTime DateTime,
          Price Int64,
          OrderID String,
          CurrencyID UInt32
      ),
      ...
  ) ENGINE = CollapsingMergeTree(StartDate, intHash32(UserID), (CounterID, StartDate, intHash32(UserID), VisitID), 8192, Sign)

  
  作者：盗梦者_56f2
  链接：https://www.jianshu.com/p/39e675ccbf3b
  来源：简书
  著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

• 浮点数 Float32 和 Float64
  
  float精度是：7~8位有效数字
  double精度是：16~17位有效数字

• 定点数 Decimal32、Decimal64 和Decimal128
  Decimal(P, S)：P代表精度，决定总位数（整数部分+小数部分），取值范围是1~38; S代表规模，决定小数位数，取值范围是0～P。
  

• 缺失值（Nullable(TypeName)）

  允许用特殊标记NULL表示"缺失值"，可以与 TypeName 的正常值存放一起。例如，Nullable(Int8) 类型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。
  对于 TypeName，不能使用复合数据类型 Array和 Tuple。复合数据类型可以包含 Nullable 类型值，例如Array(Nullable(Int8))。
  Nullable 类型字段不能包含在表索引中。
  除非在 ClickHouse 服务器配置中另有说明，否则 NULL 是任何 Nullable 类型的默认值。
  使用 Nullable 几乎总是对性能产生负面影响。

   CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog
  #
  INSERT INTO t_null VALUES (1, NULL)

   

4.物化列

指定 MATERIALIZED 表达式，即将一个列作为物化列处理了，这意味着这个列的值不能从insert 语句获取，只能是自己计算出来的。同时，物化列也不会出现在 select * 的结果中：

drop table if exists test.m2;
create table test.m2 (
 a MATERIALIZED (b+1)
,b UInt16
) ENGINE = Memory;
insert into test.m2 (b) values (1);
select * from test.m2;
select a, b from test.m2; --这个可以查出来

 

5.表达式列

ALIAS 表达式列某方面跟物化列相同，就是它的值不能从 insert 语句获取。不同的是， 物化列 是会真正保存数据（这样查询时不需要再计算），

而表达式列不会保存数据（这样查询时总是需要计算），只是在查询时返回表达式的结果。

create table test.m3 (a ALIAS (b+1), b UInt16) ENGINE = Memory;
insert into test.m3(b) values (1);
select * from test.m3;
select a, b from test.m3;