ClickHouse06-ClickHouse中基础的增删改查

使用数据库，最基础的学习都是增、删、改、查，然后才会去了解基础函数和高阶函数，今天就来看看大火的 ClickHouse 中简单的增删改查怎么写？

• 创建数据库：create database
• 创建表格：create table
• 修改表格：alter table/column
• 插入数据：insert row
• 删除数据：delete row
• 更新数据：update row
• 查询数据：select
• 查看建表语句：show

创建数据库：create database

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)] [COMMENT 'Comment']

示例：

CREATE DATABASE helloworld

创建表格：create table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1] [COMMENT 'comment for column'],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2] [COMMENT 'comment for column'],
    ...
) ENGINE = engine
  COMMENT 'comment for table'

样例：

CREATE TABLE helloworld.my_first_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)

从一个select语句创建table:

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

修改表格：alter table/column

ALTER [TEMPORARY] TABLE [db].name [ON CLUSTER cluster] ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN ...

ALTER TABLE helloworld.my_first_table ADD COLUMN added3 UInt32 AFTER metric;

插入数据：insert row

INSERT INTO [TABLE] [db.]table [(c1, c2, c3)] [SETTINGS ...] VALUES (v11, v12, v13), (v21, v22, v23), ...

示例：

INSERT INTO helloworld.my_first_table (*) VALUES (1, 'a', now(),100) ;

INSERT INTO helloworld.my_first_table VALUES (1, 'a', now(),100) ;

INSERT INTO helloworld.my_first_table (user_id,message) VALUES (1, 'a'),(2, 'b'),(3, 'c') ;

删除数据：delete row

ALTER TABLE  [ON CLUSTER cluster] DELETE WHERE filter_expr

示例：

ALTER TABLE helloworld.my_first_table DELETE WHERE user_id=101

对于MergeTree类型的表引擎，可以使用DELETE语句进行删除

DELETE FROM [db.]table [ON CLUSTER cluster] WHERE expr;

使我们熟知的delete语句： DELETE FROM hits WHERE Title LIKE '%hello%';

更新数据：update row

ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr

示例：

ALTER TABLE helloworld.my_first_table UPDATE metric = 100 WHERE user_id=101

查询数据：select

语法基本是通用的：

[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]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 ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr] [INTERPOLATE [(expr_list)]]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type [LEVEL level]] ]
[FORMAT format]

查看建表语句：show

有时候我们想看一下表或者数据库的创建语句

SHOW [CREATE] [TEMPORARY] TABLE|DICTIONARY|VIEW|DATABASE [db.]table|view [INTO OUTFILE filename] [FORMAT format]

示例:
SHOW TABLE t 
SHOW DATABASE db 
SHOW CREATE TABLE t
SHOW CREATE DATABASE db

示例：

 :) show create table t

SHOW CREATE TABLE t

Query id: 0fa1cd73-d329-423f-8147-3ee52c5202e7

┌─statement─────────────────────────────────────────────┐
│ CREATE TABLE default.t
(
    `id` Int32
)
ENGINE = Memory │
└───────────────────────────────────────────────────────┘

---

如果喜欢我的文章的话，可以去GitHub上给一个免费的关注吗？