-- 连接到 MySQL
mysql -u root -p

-- 或指定主机和端口
mysql -h localhost -P 3306 -u root -p

-- 创建数据库
CREATE DATABASE my_database;

-- 显示所有数据库
SHOW DATABASES;

-- 使用指定数据库
USE my_database;

-- 删除数据库
DROP DATABASE my_database;

-- 创建用户表（包含 JSON 和 VECTOR 类型）
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    age TINYINT UNSIGNED,
    preferences JSON,
    user_embedding VECTOR(768),
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    FULLTEXT INDEX idx_username_fulltext (username),
    INDEX idx_email (email),
    SPATIAL INDEX idx_location (location) IF EXISTS
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 查看表结构
DESCRIBE users;
SHOW CREATE TABLE users\G

-- 插入单条数据
INSERT INTO users (username, email, age)
VALUES ('zhangsan', 'zhangsan@example.com', 25);

-- 插入多条数据
INSERT INTO users (username, email, age) VALUES
    ('lisi', 'lisi@example.com', 30),
    ('wangwu', 'wangwu@example.com', 28);

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

-- 查询特定列
SELECT username, email FROM users;

-- 条件查询（AI 自动优化）
SELECT * FROM users WHERE age > 25;

-- JSON 数据查询
SELECT username,
       JSON_EXTRACT(preferences, '$.theme') as theme,
       JSON_EXTRACT(preferences, '$.language') as language
FROM users
WHERE JSON_EXTRACT(preferences, '$.newsletter') = true;

-- 向量相似性搜索（用于 AI 应用）
SELECT username,
       VECTOR_DISTANCE(user_embedding,
                       '[0.1, 0.2, 0.3, ..., 0.768]') as distance
FROM users
ORDER BY distance
LIMIT 10;

-- 全文搜索（改进的全文索引）
SELECT username,
       MATCH(username, email) AGAINST('zhangsan' IN NATURAL LANGUAGE MODE) as relevance
FROM users
WHERE MATCH(username, email) AGAINST('zhangsan' IN NATURAL LANGUAGE MODE);

-- 使用 AI 优化提示
SELECT /*+ AI_OPTIMIZE */ * FROM users WHERE age > 25 ORDER BY created_at DESC;

-- 分页查询（优化性能）
SELECT * FROM users
WHERE id > 1000
ORDER BY id
LIMIT 10;

-- 窗口函数（分析查询）
SELECT username,
       age,
       RANK() OVER (ORDER BY age DESC) as age_rank,
       DENSE_RANK() OVER (ORDER BY age DESC) as dense_age_rank
FROM users;

-- 更新单条数据
UPDATE users SET age = 26 WHERE username = 'zhangsan';

-- 更新多条数据
UPDATE users SET age = age + 1 WHERE age < 30;

-- 删除单条数据
DELETE FROM users WHERE id = 1;

-- 删除多条数据
DELETE FROM users WHERE age < 25;

-- 清空表
TRUNCATE TABLE users;

-- 日期时间示例
CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- JSON 类型用于存储结构化数据
CREATE TABLE user_preferences (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    preferences JSON,
    settings JSON
);

-- 插入 JSON 数据
INSERT INTO user_preferences (user_id, preferences, settings)
VALUES(
    1,
    '{"theme": "dark", "language": "zh-CN", "notifications": true}',
    '{"privacy": "public", "two_factor": false}'
);

-- 使用 JSON_EXTRACT 提取值
SELECT
    user_id,
    JSON_EXTRACT(preferences, '$.theme') as theme,
    JSON_EXTRACT(preferences, '$.language') as language,
    JSON_EXTRACT(preferences, '$.notifications') as notifications
FROM user_preferences;

-- 使用 JSON_SET 更新/添加值
UPDATE user_preferences
SET preferences = JSON_SET(preferences, '$.theme', 'light', '$.font_size', '14px')
WHERE user_id = 1;

-- 使用 JSON_REMOVE 删除值
UPDATE user_preferences
SET preferences = JSON_REMOVE(preferences, '$.notifications')
WHERE user_id = 1;

-- 使用 JSON_KEYS 获取所有键
SELECT JSON_KEYS(preferences) as keys FROM user_preferences;

-- JSON 数组操作
INSERT INTO user_preferences (user_id, preferences)
VALUES(2, '{"tags": ["mysql", "database", "sql"]}');

SELECT JSON_EXTRACT(preferences, '$.tags[0]') as first_tag FROM user_preferences;

-- 空间数据类型
GEOMETRY:      任意几何类型
POINT:         点 (x, y)
LINESTRING:    曲线 (点序列)
POLYGON:       多边形 (边界点序列)
MULTIPOINT:    点集合
MULTILINESTRING: 曲线集合
MULTIPOLYGON:  多边形集合
GEOMETRYCOLLECTION: 几何集合

-- 创建空间数据表
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL,
    area POLYGON,
    route LINESTRING,
    SPATIAL INDEX idx_location (location)
);

-- 插入空间数据
INSERT INTO locations (name, location)
VALUES(
    '天安门广场',
    ST_GeomFromText('POINT(116.397428 39.90923)')
);

-- 查询坐标
SELECT name, ST_X(location) as longitude, ST_Y(location) as latitude
FROM locations;

-- 计算两点距离 (米)
SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(116.397428 39.90923)'),
    ST_GeomFromText('POINT(121.473701 31.230416)')
) as distance_meters;

-- 创建多边形
INSERT INTO locations (name, area)
VALUES(
    '北京市',
    ST_GeomFromText('POLYGON((116.0 39.0, 117.0 39.0, 117.0 41.0, 116.0 41.0, 116.0 39.0))')
);

-- 判断点是否在多边形内
SELECT name FROM locations
WHERE ST_Contains(
    area,
    ST_GeomFromText('POINT(116.5 40.0)')
);

-- 向量类型用于存储嵌入向量 (AI/ML 应用)
CREATE TABLE document_embeddings (
    id INT PRIMARY KEY,
    doc_id INT NOT NULL,
    embedding VECTOR(768),  -- 768维向量 (BERT等模型常用)
    INDEX idx_embedding (embedding)
);

-- 插入向量数据 (需要转换为字符串格式)
INSERT INTO document_embeddings (doc_id, embedding)
VALUES(
    1,
    '[0.1, 0.2, 0.3, 0.0, -0.1, 0.5, -0.3, 0.8, ...]'
);

-- 向量相似性搜索 (余弦相似度)
SELECT
    doc_id,
    VECTOR_DISTANCE(embedding, '[0.1, 0.2, 0.3, 0.0, -0.1, 0.5, -0.3, 0.8, ...]', 'cosine') as similarity
FROM document_embeddings
ORDER BY similarity
LIMIT 5;

-- 欧氏距离
SELECT
    doc_id,
    VECTOR_DISTANCE(embedding, '[0.1, 0.2, 0.3, ...]', 'euclidean') as distance
FROM document_embeddings
ORDER BY distance
LIMIT 5;

-- 内积
SELECT
    doc_id,
    VECTOR_DISTANCE(embedding, '[0.1, 0.2, 0.3, ...]', 'dot_product') as score
FROM document_embeddings
ORDER BY score DESC
LIMIT 5;

-- 使用 HNSW 索引加速向量搜索
ALTER TABLE document_embeddings
ADD INDEX idx_hnsw (embedding) INDEX_TYPE = 'HNSW' PARAMETERS = '{"m": 16, "ef_construction": 200}';

-- 数据类型选择原则

-- 1. 选择最小的数据类型
-- 不推荐: INT UNSIGNED 对于 0-100 的值
CREATE TABLE age_table (
    age TINYINT UNSIGNED  -- 0-255, 比 INT 节省3字节
);

-- 2. 使用 VARCHAR(n) 而非 CHAR(n) 当字符串长度不确定时
-- CHAR(10) 始终占用10字节, VARCHAR(10) 按实际长度

-- 3. 使用 DECIMAL 而非 FLOAT/DOUBLE 用于精确数值
-- 金融计算: DECIMAL(15,2) vs FLOAT
CREATE TABLE accounts (
    balance DECIMAL(15,2)  -- 精确
);

-- 4. 使用 TIMESTAMP 而非 DATETIME 当需要自动更新时
CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 自动设置
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 自动更新
);

-- 5. 使用 JSON 类型存储灵活的结构化数据
-- 但不要滥用, 频繁查询的字段应该用独立列

-- 6. 考虑字符集: 使用 utf8mb4 支持emoji
CREATE TABLE messages (
    content VARCHAR(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
);

-- 7. 有符号 vs 无符号
CREATE TABLE counters (
    positive_count TINYINT UNSIGNED,  -- 0-255
    signed_count TINYINT               -- -128 到 127
);