-- DECIMAL 精确存储金额
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance DECIMAL(12, 2)  -- 最多 12 位，其中 2 位小数
);

-- FLOAT 近似存储
CREATE TABLE measurements (
    id INTEGER PRIMARY KEY,
    temperature REAL  -- 近似值，可能有微小误差
);

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title VARCHAR(200),    -- 标题，最长 200 字符
    status CHAR(1),         -- 状态：D=草稿，P=已发布
    content TEXT             -- 正文，不限长度
);

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date DATE,              -- 只关心日期
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 自动记录创建时间
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active BOOLEAN DEFAULT 1  -- 1=启用，0=禁用
);

-- 插入布尔值
INSERT INTO users (name, is_active) VALUES ('张三', TRUE);   -- 存为 1
INSERT INTO users (name, is_active) VALUES ('李四', FALSE);  -- 存为 0

-- 查询时使用布尔值
SELECT * FROM users WHERE is_active = 1;

-- NULL 参与运算的结果都是 NULL
SELECT NULL + 1;        -- 结果：NULL
SELECT NULL || 'hello'; -- 结果：NULL

-- 判断 NULL 必须用 IS / IS NOT
SELECT * FROM employees WHERE department_id IS NULL;     -- ✅ 正确
SELECT * FROM employees WHERE department_id = NULL;      -- ❌ 永远返回空
SELECT * FROM employees WHERE department_id IS NOT NULL; -- ✅ 正确

-- 创建商品表，指定各列类型
CREATE TABLE products_demo (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    created_at DATE
);

-- 插入各种类型的数据
INSERT INTO products_demo (name, price, stock, description, is_active, created_at)
VALUES ('机械键盘', 299.50, 100, '青轴87键机械键盘，适合打字', TRUE, '2024-01-15');

INSERT INTO products_demo (name, price, stock, description, is_active, created_at)
VALUES ('无线鼠标', 89.00, 200, NULL, TRUE, '2024-02-20');

-- 查看数据和类型表现
SELECT name, price, stock, description, is_active, created_at
FROM products_demo;

name      price    stock  description              is_active  created_at
--------  -------  -----  -----------------------  ---------  ----------
机械键盘  299.50   100    青轴87键机械键盘，适合打字  1          2024-01-15
无线鼠标  89.00    200    (NULL)                    1          2024-02-20

-- 创建测试表
CREATE TABLE null_test (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

INSERT INTO null_test VALUES (1, 10);
INSERT INTO null_test VALUES (2, NULL);
INSERT INTO null_test VALUES (3, 20);

-- NULL 参与算术运算
SELECT id, value, value + 100 AS add_result
FROM null_test;

id  value  add_result
--  -----  ----------
1   10     110
2   (NULL) (NULL)
3   20     120

-- 判断 NULL：IS vs =
SELECT id, value,
    CASE WHEN value IS NULL THEN '是NULL' ELSE '非NULL' END AS check_result
FROM null_test;

id  value  check_result
--  -----  ------------
1   10     非NULL
2   (NULL) 是NULL
3   20     非NULL

-- NULL 与聚合函数：COUNT 不计 NULL，SUM/AVG 忽略 NULL
SELECT COUNT(value) AS count_val,      -- 2（不计 NULL）
       COUNT(*) AS count_all,          -- 3（计所有行）
       SUM(value) AS sum_val,          -- 30（忽略 NULL）
       AVG(value) AS avg_val           -- 15（30/2，不是30/3）
FROM null_test;

count_val  count_all  sum_val  avg_val
---------  ---------  -------  -------
2          3          30       15.0

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,     -- 用户名，唯一且非空
    email VARCHAR(100) NOT NULL UNIQUE,       -- 邮箱
    password_hash CHAR(64) NOT NULL,          -- SHA256 哈希，固定 64 字符
    age INTEGER CHECK (age >= 0 AND age <= 150),  -- 年龄，合理范围校验
    balance DECIMAL(12, 2) DEFAULT 0.00,      -- 账户余额，精确到分
    bio TEXT,                                  -- 个人简介，不限长度
    is_verified BOOLEAN DEFAULT 0,            -- 是否验证邮箱
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 查询员工信息，部门为空时显示"未分配"
SELECT e.name,
       COALESCE(d.name, '未分配') AS department,
       COALESCE(e.salary, 0) AS salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;