clickhouse sql创健用户指定profiles clickhouse 创建表

1-常用 SQL 命令:

创建表(create)的基本语法：

- 1）、表的类型分为两种：临时表temporary和持久化表
- 2）、创建表示，建议写上数据库名称
- 3）、如果使用CK集群，创建表时，在表的名称后面加上：`ON CLUSTER clusterId`
- 4）、每个数据表创建时，需要指定存储引擎，非常关键属性，决定业务查询性能

`engine = EngineName(parameters);`

create [temporary] table [if not exists] dbName.tableName [ON CLUSTER cluster] (
fieldName dataType
) engine = EngineName(parameters);

视图创建：对表数据投影（Project），选择部分字段或者聚合操作得到数据，视图View中数据仅仅只能读取
create view view_name as select ...

2- ClickHouse的SQL语法:
简介: ClickHouse数据库支持SQL语法只有三种：查询SELECT、插入INSERT和修改ALTER（实现UPDATE和DELETE）

select语法:

1）、==SELECT 语法==：查询检索数据，类似MySQL数据库中语法
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]

insert语法:

ClickHouse中完整insert的主要用于向系统中添加数据，有如下几种方式：

- 1）、语法一：通用插入数据(常用)

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

- 2）、语法二：

`INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set`

- 3）、语法三：子查询插入数据(常用)

`INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...`

ALTER 语法:

用于对ClickHouse表进行添加列、删除列和修改列以及数据的更新和删除

[ClickHouse中的ALTER只支持MergeTree系列，Merge和Distributed引擎的表]

-- ALTER 语法
CREATE TABLE mt_table
(
    `date` Date, 
    `id` UInt8, 
    `name` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(date)
ORDER BY id
SETTINGS index_granularity = 8192 ;

SETTINGS：
配置项：
index_granularity:默认8192，表示索引的粒度，即MergeTree的索引在默认情况下，每间隔8192行才生成一个索引。通常不需要修改此参数。
index_granularity_bytes:默认10MB(1010241024)表示自适应间隔大小的特性，即根据每一批写入数据的体量大小，动态划分间隔大小。设置为0表示不启用自适应功能。


insert into mt_table values ('2020-09-15', 1, 'zhangsan');
insert into mt_table values ('2020-09-15', 2, 'lisi');
insert into mt_table values ('2020-09-15', 3, 'wangwu');

-- 添加列
alter table mt_table add column age UInt8 ;
-- 查看表结构
desc mt_table ;
-- 查看数据
select * from mt_table ;


-- 修改列
alter table mt_table modify column age UInt16 ;

-- 删除列
alter table mt_table drop column age ;

UPDATE和DELETE:

[从使用场景来说，Clickhouse是个分析型数据库。这种场景下，数据一般是不变的，
因此Clickhouse对update、delete的支持是比较弱的，实际上并不支持标准的update、delete操作。]

-- update和delete，基于alter语法实现

CREATE TABLE tbl_test_users
(
    `id` UInt64,
    `email` String,
    `username` String,
    `gender` UInt8,
    `birthday` Date,
    `mobile` FixedString(13),
    `pwd` String,
    `regDT` DateTime,
    `lastLoginDT` DateTime,
    `lastLoginIP` String
)
ENGINE = MergeTree()     -- 必须制定相关引擎  不然直接影响了CK的性能
PARTITION BY toYYYYMMDD(regDT)    --制定分区
ORDER BY id     -- 排序
SETTINGS index_granularity = 8192;   -- MergeTree索引的配置项  固定写法


-- 插入数据
insert into tbl_test_users(id, email, username, gender, birthday, mobile, pwd, regDT, lastLoginDT,lastLoginIP) values (1,'[e-mail]','督咏',2,'1992-05-31','13306834911','7f930f90eb6604e837db06908cc95149','2008-08-06 11:48:12','2015-05-08 10:51:41','106.83.54.165'),(2,'[e-mail]','上磊',1,'1983-10-11','15302753472','7f930f90eb6604e837db06908cc95149','2008-08-10 05:37:32','2014-07-28 23:43:04','121.77.119.233'),(3,'[e-mail]','涂康',1,'1970-11-22','15200570030','96802a851b4a7295fb09122b9aa79c18','2008-08-10 11:37:55','2014-07-22 23:45:47','171.12.206.122'),(4,'[e-mail]','金俊振',1,'2002-02-10','15207308903','96802a851b4a7295fb09122b9aa79c18','2008-08-10 14:47:09','2013-12-26 15:55:02','61.235.143.92'),(5,'[e-mail]','阴福',1,'1987-09-01','13005861359','96802a851b4a7295fb09122b9aa79c18','2008-08-12 21:58:11','2013-12-26 15:52:33','182.81.200.32');

-- 更新
ALTER TABLE tbl_test_users UPDATE username='张三' WHERE id=1;

-- 删除
ALTER TABLE tbl_test_users DELETE WHERE id=1;