使用数据库，最基础的学习都是增、删、改、查，然后才会去了解基础函数和高阶函数，今天就来看看大火的 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上给一个免费的关注吗？
