1

CREATE DATABASE test

1

DROP DATABASE test

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE IF NOT EXISTS test.my_first_table (
    `event_name` String,
    `created_at` DateTime,
    `user_id` UInt32,
    `country` String,
    `login_type` Nullable(Enum('Google' = 1, 'Facebook' = 2, 'Apple' = 3, 'Vistor' = 4)),
    `revenue_usd` Nullable(Float64)
) 
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(created_at)
ORDER BY (created_at, event_name, user_id)
SETTINGS index_granularity = 8192

1

DROP TABLE my_first_table

1

INSERT INTO my_first_table (*) VALUES ('install', '2024-11-01 13:14:45', 1000034, 'SA', 2, NULL)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 新增列
ALTER TABLE my_first_table ADD COLUMN status Nullable(UInt8)
ALTER TABLE my_first_table ADD COLUMN city Nullable(String) AFTER country

-- 修改列类型
ALTER TABLE my_first_table MODIFY COLUMN status Nullable(Enum('离线' = 0, '忙碌' = 1, '空闲' = 2, 'Live' = 3))

-- 修改列取值
ALTER TABLE my_first_table UPDATE login_type = 1 WHERE user_id = 1000034

-- 删除列
ALTER TABLE my_first_table DROP COLUMN city

1
2
3
4
5
6
7
8

SELECT
    *
FROM system.parts
WHERE table = 'my_first_table'
ORDER BY
    partition DESC
LIMIT 3
FORMAT Vertical

1

SELECT timezoneOf(now())

1

SELECT version(), hostName(), currentDatabase(), currentUser()

1

SELECT COUNT(*) FROM my_first_table SETTINGS max_execution_time=1

1

SELECT hasColumnInTable('test', 'my_first_table', 'user_id')

1

SELECT splitByChar('_', 'abc_def_12_')[3]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
  toDateTime(0),
  toDateTime(NULL),

  toDateTime(1713265060),
  fromUnixTimestamp(1713265060),
  toDateTime(1713265060, 'Asia/Shanghai'),

  toDate(NOW(), 'Asia/Shanghai'),
  toDateTime(NOW(), 'Asia/Shanghai'),
  toHour(toDateTime(NOW(), 'Asia/Shanghai')),
  NOW() + INTERVAL 8 HOUR, -- 另一种转时区的方法

  toUnixTimestamp(toDateTime('2024-10-16 03:17:47'))

1
2
3

SELECT
  now() AS x,
  toDate(x)

1

ALTER TABLE my_first_table DROP PARTITION 20240418

1

DELETE FROM my_first_table WHERE created_at >= '2024-04-19'