SQL基础语法 #

TimescaleDB SQL特点：

完全兼容PostgreSQL
├── 标准SQL语法
├── 所有数据类型
├── 所有操作符
├── 所有函数
└── 所有索引类型

时序扩展
├── 超表函数
├── 时间桶函数
├── 超函数
├── 连续聚合
└── 时序特定操作

SQL语句分类：

DDL（数据定义语言）
├── CREATE - 创建对象
├── ALTER - 修改对象
├── DROP - 删除对象
└── TRUNCATE - 清空表

DML（数据操作语言）
├── INSERT - 插入数据
├── UPDATE - 更新数据
├── DELETE - 删除数据
└── SELECT - 查询数据

DCL（数据控制语言）
├── GRANT - 授予权限
└── REVOKE - 撤销权限

TCL（事务控制语言）
├── BEGIN - 开始事务
├── COMMIT - 提交事务
├── ROLLBACK - 回滚事务
└── SAVEPOINT - 保存点

-- 单行注释

/*
 * 多行注释
 * 可以跨越多行
 */

-- SQL语句不区分大小写（关键字）
SELECT * FROM users;  -- 推荐
select * from users;  -- 也可以

-- 但标识符区分大小写（如果用双引号）
SELECT * FROM "Users";  -- 表名必须完全匹配大小写

-- 合法的标识符
CREATE TABLE sensor_data (
    time TIMESTAMPTZ,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION
);

-- 使用双引号可以包含特殊字符
CREATE TABLE "sensor-data" (
    "time-stamp" TIMESTAMPTZ,
    "sensor-id" INTEGER
);

-- 命名规范建议
-- 1. 使用小写字母
-- 2. 使用下划线分隔单词
-- 3. 避免使用保留字
-- 4. 见名知意

-- 数值类型
INTEGER          -- 整数
BIGINT           -- 大整数
SMALLINT         -- 小整数
DECIMAL(p,s)     -- 精确小数
NUMERIC(p,s)     -- 精确小数
REAL             -- 单精度浮点
DOUBLE PRECISION -- 双精度浮点

-- 字符串类型
CHAR(n)          -- 定长字符串
VARCHAR(n)       -- 变长字符串
TEXT             -- 无限长字符串

-- 时间类型
DATE             -- 日期
TIME             -- 时间
TIMESTAMP        -- 时间戳（无时区）
TIMESTAMPTZ      -- 时间戳（带时区）- TimescaleDB推荐
INTERVAL         -- 时间间隔

-- 布尔类型
BOOLEAN          -- true/false

-- 其他类型
JSON             -- JSON数据
JSONB            -- 二进制JSON
UUID             -- UUID
ARRAY            -- 数组

-- 创建数据库
CREATE DATABASE tsdb;

-- 带参数创建
CREATE DATABASE tsdb
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- 连接数据库
\c tsdb

-- 删除数据库
DROP DATABASE IF EXISTS tsdb;

-- 创建TimescaleDB扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 查看已安装扩展
\dx

-- 查看扩展详情
\dx+ timescaledb

-- 创建传感器数据表
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION,
    location    VARCHAR(100)
);

-- 添加主键
ALTER TABLE sensor_data
ADD PRIMARY KEY (time, sensor_id);

-- 添加索引
CREATE INDEX idx_sensor_id ON sensor_data (sensor_id);
CREATE INDEX idx_time ON sensor_data (time DESC);

-- 将普通表转换为超表
SELECT create_hypertable(
    'sensor_data',      -- 表名
    'time',             -- 时间列
    if_not_exists => TRUE
);

-- 带空间分区的超表
SELECT create_hypertable(
    'sensor_data',
    'time',
    partitioning_column => 'sensor_id',
    number_partitions => 4
);

-- 查看超表信息
SELECT * FROM timescaledb_information.hypertables;

-- 添加列
ALTER TABLE sensor_data
ADD COLUMN battery_level DOUBLE PRECISION;

-- 修改列类型
ALTER TABLE sensor_data
ALTER COLUMN location TYPE TEXT;

-- 添加约束
ALTER TABLE sensor_data
ADD CONSTRAINT chk_temperature 
CHECK (temperature BETWEEN -50 AND 100);

-- 删除列
ALTER TABLE sensor_data
DROP COLUMN IF EXISTS battery_level;

-- 删除表
DROP TABLE IF EXISTS sensor_data;

-- 级联删除（删除依赖对象）
DROP TABLE IF EXISTS sensor_data CASCADE;

-- 插入单行数据
INSERT INTO sensor_data (time, sensor_id, temperature, humidity)
VALUES (NOW(), 1, 25.5, 60.0);

-- 插入多行数据
INSERT INTO sensor_data (time, sensor_id, temperature, humidity) VALUES
    (NOW() - INTERVAL '1 hour', 1, 24.8, 58.5),
    (NOW() - INTERVAL '2 hours', 1, 24.5, 57.2),
    (NOW() - INTERVAL '3 hours', 2, 26.1, 61.0);

-- 插入所有列
INSERT INTO sensor_data VALUES
    (NOW(), 3, 23.9, 59.5, 1013.25, 'Beijing');

-- 从其他表插入
INSERT INTO sensor_data_archive
SELECT * FROM sensor_data
WHERE time < NOW() - INTERVAL '30 days';

-- 带条件插入
INSERT INTO high_temp_sensors (time, sensor_id, temperature)
SELECT time, sensor_id, temperature
FROM sensor_data
WHERE temperature > 30;

-- 使用COPY命令导入CSV
COPY sensor_data (time, sensor_id, temperature, humidity)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true);

-- 导出数据
COPY (
    SELECT * FROM sensor_data
    WHERE time > NOW() - INTERVAL '7 days'
) TO '/path/to/export.csv'
WITH (FORMAT csv, HEADER true);

-- 查询所有数据
SELECT * FROM sensor_data;

-- 查询特定列
SELECT time, sensor_id, temperature
FROM sensor_data;

-- 条件查询
SELECT * FROM sensor_data
WHERE sensor_id = 1
ORDER BY time DESC;

-- 限制结果数量
SELECT * FROM sensor_data
ORDER BY time DESC
LIMIT 100;

-- 查询最近一小时数据
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour';

-- 查询特定时间范围
SELECT * FROM sensor_data
WHERE time BETWEEN '2024-01-01' AND '2024-01-31';

-- 查询今天的数据
SELECT * FROM sensor_data
WHERE time >= CURRENT_DATE;

-- 查询昨天的数据
SELECT * FROM sensor_data
WHERE time >= CURRENT_DATE - INTERVAL '1 day'
  AND time < CURRENT_DATE;

-- 基本聚合
SELECT 
    sensor_id,
    COUNT(*) as reading_count,
    AVG(temperature) as avg_temp,
    MAX(temperature) as max_temp,
    MIN(temperature) as min_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY sensor_id;

-- 使用时间桶分组
SELECT 
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) as avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- 创建传感器信息表
CREATE TABLE sensors (
    sensor_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100),
    type VARCHAR(50)
);

-- 连接查询
SELECT 
    s.name,
    s.location,
    sd.time,
    sd.temperature
FROM sensor_data sd
JOIN sensors s ON sd.sensor_id = s.sensor_id
WHERE sd.time > NOW() - INTERVAL '1 hour';

-- 更新单个字段
UPDATE sensor_data
SET temperature = 25.6
WHERE time = '2024-01-01 10:00:00'
  AND sensor_id = 1;

-- 更新多个字段
UPDATE sensor_data
SET 
    temperature = 25.6,
    humidity = 61.0
WHERE sensor_id = 1
  AND time > NOW() - INTERVAL '1 hour';

-- 条件更新
UPDATE sensor_data
SET temperature = temperature * 1.1
WHERE temperature < 20;

-- 基于其他表更新
UPDATE sensor_data sd
SET location = s.location
FROM sensors s
WHERE sd.sensor_id = s.sensor_id;

-- 删除特定数据
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 条件删除
DELETE FROM sensor_data
WHERE sensor_id = 1
  AND temperature IS NULL;

-- 删除所有数据（保留表结构）
TRUNCATE TABLE sensor_data;

-- 添加保留策略（自动删除旧数据）
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- 查看保留策略
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 删除保留策略
SELECT remove_retention_policy('sensor_data');

-- 开始事务
BEGIN;

-- 执行操作
INSERT INTO sensor_data VALUES
    (NOW(), 1, 25.5, 60.0);

UPDATE sensors SET last_reading = NOW()
WHERE sensor_id = 1;

-- 提交事务
COMMIT;

-- 或者回滚
ROLLBACK;

BEGIN;

INSERT INTO sensor_data VALUES
    (NOW(), 1, 25.5, 60.0);

-- 创建保存点
SAVEPOINT sp1;

INSERT INTO sensor_data VALUES
    (NOW(), 2, 26.0, 61.0);

-- 回滚到保存点
ROLLBACK TO SAVEPOINT sp1;

-- 提交事务
COMMIT;

-- 当前时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;

-- 时间计算
SELECT NOW() - INTERVAL '1 day';
SELECT NOW() + INTERVAL '1 hour';
SELECT time_bucket('1 hour', NOW());

-- 时间提取
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());

-- 时间格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

-- 基本聚合
SELECT COUNT(*) FROM sensor_data;
SELECT SUM(temperature) FROM sensor_data;
SELECT AVG(temperature) FROM sensor_data;
SELECT MAX(temperature) FROM sensor_data;
SELECT MIN(temperature) FROM sensor_data;

-- 统计函数
SELECT STDDEV(temperature) FROM sensor_data;
SELECT VARIANCE(temperature) FROM sensor_data;

-- 分位数
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY temperature)
FROM sensor_data;

-- 字符串连接
SELECT 'Temperature: ' || temperature || '°C'
FROM sensor_data;

-- 字符串处理
SELECT UPPER(location) FROM sensor_data;
SELECT LOWER(location) FROM sensor_data;
SELECT SUBSTRING(location, 1, 10) FROM sensor_data;

-- 字符串查找
SELECT * FROM sensor_data
WHERE location LIKE '%Beijing%';

-- 创建B-tree索引
CREATE INDEX idx_sensor_id ON sensor_data (sensor_id);

-- 创建时间索引
CREATE INDEX idx_time ON sensor_data (time DESC);

-- 创建复合索引
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time DESC);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_reading
ON sensor_data (time, sensor_id);

-- 查看索引
\di

-- 删除索引
DROP INDEX IF EXISTS idx_sensor_id;

-- 重建索引
REINDEX INDEX idx_sensor_id;

-- 并发创建索引（不锁表）
CREATE INDEX CONCURRENTLY idx_sensor_location
ON sensor_data (location);