1 常用的SQL命令
作用 | SQL |
---|---|
列出所有数据库 | show databases; |
进入某一个数据库 | use dbName; |
列出数据库中所有的表 | show tables; |
创建数据库 | create database [if not exists] dbName; |
删除数据库 | drop database dbName; |
创建表 | create [temporary] table [if not exists] tableName [ON CLUSTER cluster] ( fieldName dataType ) engine = EngineName(parameters); |
清空表 | truncate table tableName; |
删除表 | drop table tableName; |
创建视图 | create view view_name as select … |
创建物化视图 | create [MATERIALIZED] view [if not exists] [db.]tableName [to [db.]name] [engine=engine] [populate] as select … |
2 select查询语法
ClickHouse中完整select的查询语法如下(除了SELECT关键字和expr_list以外,中括号中的字句都是可选的):
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
[LIMIT [n, ]m]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]
如果查询中不包含DISTINCT,GROUP BY,ORDER BY子句以及IN和JOIN子查询,那它将仅使用O(1)数量的内存来完全流式的处理查询,否则这个查询将消耗大量的内存,除非你指定了这些系统配置:max_memory_usage, max_rows_to_group_by, max_rows_to_sort, max_rows_in_distinct, max_bytes_in_distinct, max_rows_in_set, max_bytes_in_set, max_rows_in_join, max_bytes_in_join, max_bytes_before_external_sort, max_bytes_before_external_group_by。它们规定了可以使用外部排序(将临时表存储到磁盘中)以及外部聚合,目前系统不存在关于Join的配置。