SELECT查询表数据

基本语法

SELECT [DISTINCT] expr_list

[FROM [database_name.]table | (subquery) | table_function] [FINAL]

[SAMPLE sample_coeff]

[ARRAY JOIN ...]

[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)

[PREWHERE expr]

[WHERE expr]

[GROUP BY expr_list] [WITH TOTALS]

[HAVING expr]

[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]

[LIMIT [offset_value, ]n BY columns]

[LIMIT [n, ]m] [WITH TIES]

[UNION ALL ...]

[INTO OUTFILE filename]

[FORMAT format]

使用示例

• 查看ClickHouse集群信息：

  select * from system.clusters;

• 显示当前节点设置的宏：

  select * from system.macros;

• 查看数据库容量：

  select
  sum(rows) as "总行数",
  formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",
  formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",
  round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100,
  0) "压缩率"
  from system.parts;

• 查询test表容量，where条件根据实际情况添加修改：

  select
  sum(rows) as "总行数",
  formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",
  formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",
  round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100,
  0) "压缩率"
  from system.parts
  where table in ('test')
  and partition like '2020-11-%'
  group by table;