ClickHouse

clickhouse 操作语法大全

创建数据库

CREATE DATABASE IF NOT EXISTS db_name

删除数据库

DROP DATABASE IF EXISTS db_name

表操作

创建表

CREATE TABLE [IF NOT EXISTS] table_name [ON CLUSTER cluster]
(
  column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  ...
) ENGINE = engine_name [PARTITION BY partition_expr] [ ORDER BY order_expr] [PRIMARY KEY [key_exprs] [sample]] [SETTINGS settings_name[=value], ...]

删除表

DROP TABLE [IF EXISTS] table_name

查看表结构

DESCRIBE table_name
SHOW CREATE TABLE table_name

修改表结构

ALTER TABLE table_name ADD COLUMN new_column_name new_column_type [AFTER|FIRST existing_column_name]
ALTER TABLE table_name MODIFY COLUMN column_name modification [AFTER|FIRST existing_column_name]
ALTER TABLE table_name DROP COLUMN column_name

复制表结构

CREATE TABLE new_table_name AS TABLE old_table_name

插入数据

INSERT INTO table_name [(column1,column2,...)] VALUES (val1,val2,...),(val1,val2,...),…

更新数据

UPDATE table_name SET column1=value1,column2=value2,... [WHERE condition]

删除数据

DELETE FROM table_name [WHERE condition]

查询语句基本形式

SELECT [DISTINCT] select_expr [, select_expr2, ... ]
FROM table1 [JOIN table2 ON condition] [WHERE condition] [GROUP BY group_expr] [HAVING having_condition] [ORDER BY order_expr [ASC|DESC] ,... ] [LIMIT n] [OFFSET n]

常见查询语句

SELECT * FROM table_name                      # 查询表中所有数据
SELECT column1,column2 FROM table_name        # 查询表中指定列的数据
SELECT column1, COUNT(*) FROM table_name      # 统计表中指定列的数量
SELECT * FROM table1 JOIN table2 ON condition # 多表关联查询
SELECT * FROM table_name WHERE condition      # 条件查询
SELECT column1, SUM(column2) FROM table_name  # 统计表中某一列的和
SELECT * FROM table_name ORDER BY column_name DESC  # 按照某一列的值进行降序排序
SELECT * FROM table_name LIMIT 10             # 返回前10行数据
SELECT * FROM table_name OFFSET 10            # 跳过前10行数据返回后面的数据
SELECT column1 FROM table_name GROUP BY column1  # 对某一列进行分组并统计数量
SELECT column1, COUNT(*) FROM table_name GROUP BY col
(0)
分享:

本文由:xiaoshu168 作者:xiaoshu611发表,转载请注明来源!

相关阅读