[hadoop@bigdata ~]$ sudo systemctl start clickhouse-server
[hadoop@bigdata ~]$ clickhouse-client --host=bigdata --port=9000 -m --user=bigdata --password=123456
ClickHouse client version 21.2.2.8 (official build).
Connecting to bigdata:9000 as user bigdata.
Connected to ClickHouse server version 21.2.2 revision 54447.
bigdata :) show databases;
SHOW DATABASES
Query id: aad8f902-2908-447f-8683-8df1c58ee47c
┌─name────┐
│ default │
│ system │
└─────────┘
3 rows in set. Elapsed: 0.004 sec.
bigdata :) CREATE TABLE test
:-] (
:-] id Int32,
:-] name String
:-] ) ENGINE = Memory;
CREATE TABLE test
(
`id` Int32,
`name` String
)
ENGINE = Memory
Query id: 7adb3381-57f1-4c6b-847a-f82590888cc9
Ok.
0 rows in set. Elapsed: 0.020 sec.
5.3 插入一笔数据
bigdata :) insert into test values(1,'hadoop'),(2,'spark');
INSERT INTO test VALUES
Query id: 50160821-b508-42b7-a344-05087d6932a9
Ok.
2 rows in set. Elapsed: 0.005 sec.
5.4 查询数据
bigdata :) select * from test;
SELECT *
FROM test
Query id: 51dba55f-39df-4c66-832a-20ecb3117c51
┌─id─┬─name───┐
│ 1 │ hadoop │
│ 2 │ spark │
└────┴────────┘
2 rows in set. Elapsed: 0.005 sec.
5.5 修改数据
bigdata :) ALTER TABLE test UPDATE name = 'spark1' where id = 2;
ALTER TABLE test
UPDATE name = 'spark1' WHERE id = 2
Query id: 3ede61c5-72b9-4af1-b75b-33882da9288e
Ok.
0 rows in set. Elapsed: 0.003 sec.
bigdata :) select * from test;
SELECT *
FROM test
Query id: d9045117-ec85-4146-82c8-6b11109adf69
┌─id─┬─name───┐
│ 1 │ hadoop │
│ 2 │ spark1 │
└────┴────────┘
2 rows in set. Elapsed: 0.005 sec.
5.6 删除数据
bigdata :) ALTER TABLE test delete where id = 2;
ALTER TABLE test
DELETE WHERE id = 2
Query id: 9cecf262-aa88-4ba2-81b8-5db27046e1d2
Ok.
0 rows in set. Elapsed: 0.005 sec.
bigdata :) select * from test;
SELECT *
FROM test
Query id: 931e9a24-3f86-48ec-9df0-e8f4c7b3d603
┌─id─┬─name───┐
│ 1 │ hadoop │
└────┴────────┘
1 rows in set. Elapsed: 0.006 sec.
S - scale. Valid range: [ 0 : P ]. 小数部分可以有多少个小数位数。
加减乘除运算：
add, subtract: S = max(S1, S2)：加、减
multuply: S = S1 + S2：乘法
divide: S = S1：除法
6.4 Boolean 类型
There is no separate type for boolean values. Use UInt8 type, restricted to the values 0 or 1.
可以使用 UInt8 中 0、1来表示
6.5 String 类型
Strings of an arbitrary length. The length is not limited. The value can contain an arbitrary set of bytes, including null bytes.
The String type replaces the types VARCHAR, BLOB, CLOB, and others from other DBMSs.
6.6 Fixedstring 类型
固定长度的 String 类型
FixedString(N)
bigdata :) CREATE TABLE test_fixedstring
:-] (
:-] id Int32,
:-] name FixedString(5)
:-] ) ENGINE = Memory;
CREATE TABLE test_fixedstring
(
`id` Int32,
`name` FixedString(5)
)
ENGINE = Memory
Query id: a7394848-4af2-4cb5-acd6-230188b035b0
Ok.
0 rows in set. Elapsed: 0.008 sec.
bigdata :) insert into test_fixedstring values(1,'spark');
INSERT INTO test_fixedstring VALUES
Query id: 3811b8f1-d68c-4457-bc57-b45be5c719b6
Ok.
1 rows in set. Elapsed: 0.005 sec.
bigdata :) insert into test_fixedstring values(2,'hadoop');
INSERT INTO test_fixedstring VALUES
Query id: 8aeb2b97-b44f-40ab-bdee-ff6106a75606
Exception on client:
Code: 131. DB::Exception: String too long for type FixedString(5): while executing 'FUNCTION CAST(assumeNotNull(_dummy_0) :: 2, 'FixedString(5)' :: 1) -> cast(assumeNotNull(_dummy_0), 'FixedString(5)') FixedString(5) : 4': data for INSERT was parsed from query
Connecting to database bigdata at bigdata:9000 as user default.
Connected to ClickHouse server version 21.2.2 revision 54447.
6.7 UUID 类型
A universally unique identifier (UUID) is a 16-byte number used to identify records
一般是配合 generateUUIDv4 函数一起使用
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
bigdata :) CREATE TABLE test_uuid
:-] (
:-] id UUID,
:-] name String
:-] ) ENGINE = Memory;
CREATE TABLE test_uuid
(
`id` UUID,
`name` String
)
ENGINE = Memory
Query id: d3fde3cd-609f-497c-b662-4f75d252b076
Ok.
0 rows in set. Elapsed: 0.004 sec.
bigdata :) insert into test_uuid(name) values('spark'),('hadoop');
INSERT INTO test_uuid (name) VALUES
Query id: a513ba06-55a5-4dfd-accf-acfa03636969
Ok.
2 rows in set. Elapsed: 0.005 sec.
bigdata :) select * from test_uuid;
SELECT *
FROM test_uuid
Query id: 69a9a816-1308-41aa-8d1d-64759557795b
┌───────────────────────────────────id─┬─name───┐
│ 00000000-0000-0000-0000-000000000000 │ spark │
│ 00000000-0000-0000-0000-000000000000 │ hadoop │
└──────────────────────────────────────┴────────┘
2 rows in set. Elapsed: 0.007 sec.
1870
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
