【测试SQLite】测试SQLite支持的SQL语句分类

-- 创建用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增长
    username TEXT NOT NULL UNIQUE, -- 用户名，不为空且唯一
    password TEXT NOT NULL, -- 密码，不为空
    email TEXT NOT NULL UNIQUE, -- 电子邮件，不为空且唯一
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 创建时间，默认为当前时间戳
);

-- 创建产品表
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增长
    name TEXT NOT NULL, -- 产品名称，不为空
    description TEXT, -- 产品描述
    price REAL NOT NULL, -- 价格，不为空
    stock INTEGER NOT NULL, -- 库存，不为空
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 创建时间，默认为当前时间戳
);

-- 创建订单表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增长
    user_id INTEGER NOT NULL, -- 用户ID，不为空
    total REAL NOT NULL, -- 总价，不为空
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认为当前时间戳
    FOREIGN KEY (user_id) REFERENCES users (id) -- 外键，引用用户表的ID
);

-- 创建订单项目表
CREATE TABLE order_items (
    order_id INTEGER NOT NULL, -- 订单ID，不为空
    product_id INTEGER NOT NULL, -- 产品ID，不为空
    quantity INTEGER NOT NULL, -- 数量，不为空
    price REAL NOT NULL, -- 价格，不为空
    PRIMARY KEY (order_id, product_id), -- 主键，由订单ID和产品ID组成的复合主键
    FOREIGN KEY (order_id) REFERENCES orders (id), -- 外键，引用订单表的ID
    FOREIGN KEY (product_id) REFERENCES products (id) -- 外键，引用产品表的ID
);

-- 基础表创建
CREATE TABLE basic_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL,
    hire_date DATE DEFAULT CURRENT_DATE
);

-- 带有各种约束的表创建
CREATE TABLE constraint_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    phone TEXT CHECK (LENGTH(phone) = 10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id) REFERENCES basic_table (id)
);

-- 增加列
ALTER TABLE basic_table ADD COLUMN address TEXT;

-- 删除列（SQLite 不支持直接删除列，需要创建新表并迁移数据）
-- 迁移示例
CREATE TABLE new_basic_table AS SELECT id, name, age, salary, hire_date FROM basic_table;
DROP TABLE basic_table;
ALTER TABLE new_basic_table RENAME TO basic_table;

-- 修改列（SQLite 不支持直接修改列属性，需要创建新表并迁移数据）
-- 迁移示例
CREATE TABLE modified_basic_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL,
    hire_date DATE DEFAULT CURRENT_DATE,
    address TEXT
);
INSERT INTO modified_basic_table (id, name, age, salary, hire_date, address)
SELECT id, name, age, salary, hire_date, address FROM basic_table;
DROP TABLE basic_table;
ALTER TABLE modified_basic_table RENAME TO basic_table;

-- DEFAULT: 在创建表时指定默认值
CREATE TABLE test_default (
    id INTEGER PRIMARY KEY,
    name TEXT DEFAULT 'Unknown'
);

-- UNSIGNED: 无符号整数类型
CREATE TABLE test_unsigned (
    id INTEGER UNSIGNED PRIMARY KEY,
    value INTEGER UNSIGNED
);

-- 插入带有日期的数据
INSERT INTO users (username, password, email, created_at) VALUES ('user1', 'password1', 'user1@example.com', '2024-05-20');
INSERT INTO products (name, description, price, stock, created_at) VALUES ('Product 1', 'Description for Product 1', 100.00, 50, '2024-05-20');
INSERT INTO orders (user_id, total, created_at) VALUES (1, 100.00, '2024-05-20');
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 2, 200.00);

-- 查询特定日期的数据
SELECT * FROM users WHERE created_at = '2024-05-20';
SELECT * FROM orders WHERE created_at BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59';

-- 更新日期数据
UPDATE users SET created_at = '2024-05-21' WHERE id = 1;
UPDATE orders SET created_at = '2024-05-21 10:00:00' WHERE id = 1;

-- 查询更新后的日期数据
SELECT * FROM users WHERE created_at = '2024-05-21';
SELECT * FROM orders WHERE created_at BETWEEN '2024-05-21 00:00:00' AND '2024-05-21 23:59:59';

-- 使用日期函数进行查询
SELECT * FROM users WHERE DATE(created_at) = '2024-05-21';

-- 删除特定日期的数据
DELETE FROM users WHERE DATE(created_at) = '2024-05-21';
DELETE FROM orders WHERE DATE(created_at) = '2024-05-21';

-- 检查是否成功删除特定日期的数据
SELECT * FROM users;
SELECT * FROM orders;

-- 删除表
DROP TABLE constraint_table;

-- 创建索引
CREATE INDEX idx_basic_table_name ON basic_table (name);
CREATE UNIQUE INDEX idx_constraint_table_email ON constraint_table (email);

-- 创建复合索引
CREATE INDEX idx_basic_table_name_age ON basic_table (name, age);

-- 删除索引
DROP INDEX idx_basic_table_name;

-- 创建简单视图
CREATE VIEW view_basic_table AS 
SELECT id, name, salary FROM basic_table;

-- 创建带条件的视图
CREATE VIEW view_high_salary AS 
SELECT id, name, salary FROM basic_table WHERE salary > 50000;

-- 删除视图
DROP VIEW view_basic_table;

-- 创建触发器，在插入数据到 basic_table 时记录日志
CREATE TABLE log_table (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT,
    operation TEXT,
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_after_insert_basic_table
AFTER INSERT ON basic_table
BEGIN
    INSERT INTO log_table (table_name, operation) VALUES ('basic_table', 'INSERT');
END;

-- 创建触发器，在删除数据时记录日志
CREATE TRIGGER trg_after_delete_basic_table
AFTER DELETE ON basic_table
BEGIN
    INSERT INTO log_table (table_name, operation) VALUES ('basic_table', 'DELETE');
END;

-- 删除触发器
DROP TRIGGER trg_after_insert_basic_table;

-- 创建带外键的表
CREATE TABLE department (
    dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dept_name TEXT NOT NULL
);

CREATE TABLE employee (
    emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    emp_name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES department (dept_id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- 创建主键约束
CREATE TABLE constraint_table (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT UNIQUE
);

-- 创建唯一约束
CREATE TABLE unique_constraint_table (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT
);

-- 创建外键约束
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

-- 创建检查约束
CREATE TABLE check_constraint_table (
    id INTEGER PRIMARY KEY,
    age INTEGER CHECK (age >= 18)
);

-- 创建数据库
CREATE DATABASE test_database;

-- 删除数据库
DROP DATABASE test_database;

-- 创建用户
CREATE USER new_user IDENTIFIED BY 'password';

-- 删除用户
DROP USER old_user;

-- 授予权限
GRANT SELECT ON table_name TO user_name;

-- 撤销权限
REVOKE INSERT ON table_name FROM user_name;

-- 创建存储过程
CREATE PROCEDURE calculate_tax (IN price DECIMAL(10,2), OUT tax DECIMAL(10,2))
BEGIN
    SET tax = price * 0.1;
END;

-- 删除存储过程
DROP PROCEDURE calculate_tax;

-- 创建函数
CREATE FUNCTION get_customer_name (customer_id INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE customer_name VARCHAR(100);
    SELECT name INTO customer_name FROM customers WHERE id = customer_id;
    RETURN customer_name;
END;

-- 删除函数
DROP FUNCTION get_customer_name;

-- 创建序列
CREATE SEQUENCE seq_id START WITH 1 INCREMENT BY 1;

-- 删除序列
DROP SEQUENCE seq_id;

-- 获取序列下一个值
SELECT NEXT VALUE FOR seq_id;

-- 重置序列
ALTER SEQUENCE seq_id RESTART WITH 100;

-- 创建表空间（SQLite 不支持）
CREATE TABLESPACE ts1 LOCATION '/path/to/disk1';

-- 删除表空间（SQLite 不支持）
DROP TABLESPACE ts1;

-- 将表移到指定表空间（SQLite 不支持）
ALTER TABLE table_name SET TABLESPACE ts1;

-- 创建事件（SQLite 不支持）
CREATE EVENT event_name ON SCHEDULE AT '2024-05-25 03:00:00' DO DELETE FROM logs WHERE timestamp < NOW() - INTERVAL 1 MONTH;

-- 删除事件（SQLite 不支持）
DROP EVENT event_name;

-- 创建调度任务（SQLite 不支持）
CREATE SCHEDULE schedule_name
    ON SCHEDULE EVERY 1 DAY
    STARTS '2024-05-25 00:00:00'
    DO BACKUP DATABASE;

-- 删除调度任务（SQLite 不支持）
DROP SCHEDULE schedule_name;

-- 备份数据库（SQLite 通常使用外部工具如sqlite3命令行工具进行备份）
.backup test_database backup_file;

-- 恢复数据库（SQLite 通常使用外部工具如sqlite3命令行工具进行恢复）
.restore backup_file;

-- 创建存储过程（SQLite 不直接支持存储过程，但可以使用用户定义函数来模拟）
CREATE PROCEDURE calculate_total(IN price REAL, IN quantity INTEGER, OUT total REAL)
BEGIN
    SET total = price * quantity;
END;

-- 调用存储过程
CALL calculate_total(10.50, 5, @result);
SELECT @result;

-- 删除存储过程（SQLite 不支持，但可以删除用户定义函数）
DROP PROCEDURE calculate_total;

-- 创建用户定义函数
CREATE FUNCTION greet(name TEXT) RETURNS TEXT
BEGIN
    RETURN 'Hello, ' || name || '!';
END;

-- 调用用户定义函数
SELECT greet('John');

-- 删除用户定义函数
DROP FUNCTION greet;

-- 创建全文搜索虚拟表
CREATE VIRTUAL TABLE documents USING fts5(content TEXT);

-- 插入数据
INSERT INTO documents(content) VALUES ('This is a document.');

-- 全文搜索
SELECT * FROM documents WHERE documents MATCH 'document';

-- 删除全文搜索虚拟表
DROP TABLE documents;

-- 加载模块
.load /path/to/module.so

-- 卸载模块
.UNLOAD module_name;

-- 创建外部表（SQLite 不直接支持，但可以通过使用虚拟表和外部工具实现）
CREATE VIRTUAL TABLE external_table USING csv(filename='/path/to/file.csv');

-- 查询外部表
SELECT * FROM external_table;

-- 删除外部表（SQLite 不直接支持，但可以删除虚拟表）
DROP TABLE external_table;

-- 加载扩展模块
.load /path/to/extension_module.so

-- 查看已加载的扩展模块
.echo on
-- 可以查看到已加载的模块信息

-- 卸载扩展模块
.UNLOAD extension_module;

-- 创建存储过程（SQLite 不直接支持存储过程，但可以通过创建包含多个SQL语句的脚本来模拟）
CREATE PROCEDURE get_user(IN user_id INTEGER)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END;

-- 调用存储过程
CALL get_user(1);

-- 删除存储过程
DROP PROCEDURE get_user;

-- 创建用户定义函数
CREATE FUNCTION calculate_discount(price REAL, discount_rate REAL) RETURNS REAL
BEGIN
    RETURN price * (1 - discount_rate);
END;

-- 调用用户定义函数
SELECT calculate_discount(100, 0.1);

-- 删除用户定义函数
DROP FUNCTION calculate_discount;

-- 创建外部表（SQLite 不直接支持，但可以通过虚拟表和外部工具实现）
CREATE VIRTUAL TABLE external_table USING csv(filename='/path/to/file.csv');

-- 查询外部表
SELECT * FROM external_table;

-- 删除外部表（SQLite 不直接支持，但可以删除虚拟表）
DROP TABLE external_table;

-- 创建全文搜索虚拟表
CREATE VIRTUAL TABLE documents USING fts5(content TEXT);

-- 插入数据
INSERT INTO documents(content) VALUES ('This is a document.');

-- 全文搜索
SELECT * FROM documents WHERE documents MATCH 'document';

-- 删除全文搜索虚拟表
DROP TABLE documents;

-- 创建触发器
CREATE TRIGGER after_insert_user
AFTER INSERT ON users
BEGIN
    INSERT INTO user_logs(user_id, action, timestamp) VALUES (NEW.id, 'INSERT', CURRENT_TIMESTAMP);
END;

-- 删除触发器
DROP TRIGGER after_insert_user;

-- 创建分区表（SQLite 不直接支持，但可以通过表分割和视图来模拟）
CREATE TABLE partitioned_table (
    id INTEGER,
    name TEXT,
    partition_key INTEGER,
    PRIMARY KEY (id, partition_key)
);

-- 创建分区
CREATE TABLE partition_1 AS SELECT * FROM partitioned_table WHERE partition_key = 1;
CREATE TABLE partition_2 AS SELECT * FROM partitioned_table WHERE partition_key = 2;

-- 创建视图以查询分区表
CREATE VIEW partitioned_view AS
SELECT * FROM partition_1
UNION ALL
SELECT * FROM partition_2;

-- 删除分区表
DROP TABLE partition_1;
DROP TABLE partition_2;

-- 删除视图
DROP VIEW partitioned_view;

-- 创建临时表（SQLite 不需要显式创建临时表，可以直接使用临时表）
CREATE TEMP TABLE temp_table (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 插入数据
INSERT INTO temp_table VALUES (1, 'John'), (2, 'Alice');

-- 查询临时表
SELECT * FROM temp_table;

-- 删除临时表（会话结束后自动删除）
-- 不需要显式删除

-- 创建备份表（SQLite 不需要显式创建备份表，可以直接使用）
CREATE TABLE backup_table AS SELECT * FROM original_table;

-- 查询备份表
SELECT * FROM backup_table;

-- 删除备份表
DROP TABLE backup_table;

-- 创建包含多个数据类型的表
CREATE TABLE mixed_type_table (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    height REAL,
    birth_date DATE
);

-- 创建连接表
CREATE TABLE user_role (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- 创建带自增长列的表
CREATE TABLE auto_increment_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

-- 插入数据
INSERT INTO auto_increment_table (name) VALUES ('John');
INSERT INTO auto_increment_table (name) VALUES ('Alice');

-- 查询数据
SELECT * FROM auto_increment_table;

-- 创建视图
CREATE VIEW user_summary AS
SELECT id, username, email FROM users WHERE role = 'admin';

-- 查询视图
SELECT * FROM user_summary;

-- 创建全局临时表
CREATE GLOBAL TEMP TABLE global_temp_table (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 插入数据
INSERT INTO global_temp_table VALUES (1, 'John');

-- 查询数据
SELECT * FROM global_temp_table;

-- 查询中使用列别名
SELECT id AS user_id, name AS user_name FROM users;

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_product_price ON products(price);

-- 删除索引
DROP INDEX idx_username;

-- 创建全文搜索索引
CREATE VIRTUAL TABLE documents_fts USING fts5(content TEXT);

-- 插入数据
INSERT INTO documents_fts(content) VALUES ('This is a document.');

-- 全文搜索
SELECT * FROM documents_fts WHERE documents_fts MATCH 'document';

-- 删除全文搜索索引
DROP TABLE documents_fts;

-- 创建计算列
CREATE TABLE calculated_column_table (
    id INTEGER PRIMARY KEY,
    quantity INTEGER,
    price REAL,
    total REAL AS (quantity * price)
);

-- 创建分组集
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER,
    price REAL
);

-- 查询并分组集
SELECT product_id, SUM(quantity) AS total_quantity, SUM(price) AS total_price FROM sales GROUP BY product_id;

-- 创建架构
CREATE SCHEMA my_schema;

-- 在指定架构中创建表
CREATE TABLE my_schema.users (
    id INTEGER PRIMARY KEY,
    username TEXT
);

-- 查询指定架构中的表
SELECT * FROM my_schema.users;

-- 删除架构及其包含的对象
DROP SCHEMA my_schema CASCADE;

-- 创建角色
CREATE ROLE admin_role;

-- 授予角色权限
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO admin_role;

-- 创建用户
CREATE USER admin_user IDENTIFIED BY 'password';

-- 授予用户角色
GRANT admin_role TO admin_user;

-- 创建外键约束
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

-- 创建存储过程（SQLite 不直接支持存储过程，但可以使用用户定义函数来模拟）
CREATE PROCEDURE calculate_total(IN price DECIMAL(10,2), IN quantity INT, OUT total DECIMAL(10,2))
BEGIN
    SET total = price * quantity;
END;

-- 删除存储过程
DROP PROCEDURE calculate_total;

-- 创建用户定义函数
CREATE FUNCTION get_customer_name (customer_id INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE customer_name VARCHAR(100);
    SELECT name INTO customer_name FROM customers WHERE id = customer_id;
    RETURN customer_name;
END;

-- 删除用户定义函数
DROP FUNCTION get_customer_name;

-- 创建全文搜索虚拟表
CREATE VIRTUAL TABLE documents_fts USING fts5(content TEXT);

-- 插入数据
INSERT INTO documents_fts(content) VALUES ('This is a document.');

-- 全文搜索
SELECT * FROM documents_fts WHERE documents_fts MATCH 'document';

-- 删除全文搜索虚拟表
DROP TABLE documents_fts;

-- 创建序列
CREATE SEQUENCE seq_id START WITH 1 INCREMENT BY 1;

-- 获取序列下一个值
SELECT NEXT VALUE FOR seq_id;

-- 重置序列
ALTER SEQUENCE seq_id RESTART WITH 100;

-- 删除序列
DROP SEQUENCE seq_id;

-- 创建表空间（SQLite 不支持）
CREATE TABLESPACE ts1 LOCATION '/path/to/disk1';

-- 删除表空间（SQLite 不支持）
DROP TABLESPACE ts1;

-- 将表移到指定表空间（SQLite 不支持）
ALTER TABLE table_name SET TABLESPACE ts1;

-- 创建事件（SQLite 不支持）
CREATE EVENT event_name ON SCHEDULE AT '2024-05-25 03:00:00' DO DELETE FROM logs WHERE timestamp < NOW() - INTERVAL 1 MONTH;

-- 删除事件（SQLite 不支持）
DROP EVENT event_name;

-- 创建调度任务（SQLite 不支持）
CREATE SCHEDULE schedule_name
    ON SCHEDULE EVERY 1 DAY
    STARTS '2024-05-25 00:00:00'
    DO BACKUP DATABASE;

-- 删除调度任务（SQLite 不支持）
DROP SCHEDULE schedule_name;

-- 备份数据库（SQLite 通常使用外部工具如sqlite3命令行工具进行备份）
.backup test_database backup_file;

-- 恢复数据库（SQLite 通常使用外部工具如sqlite3命令行工具进行恢复）
.restore backup_file;

-- 创建存储过程
CREATE PROCEDURE calculate_tax (IN price DECIMAL(10,2), OUT tax DECIMAL(10,2))
BEGIN
    SET tax = price * 0.1;
END;

-- 删除存储过程
DROP PROCEDURE calculate_tax;

-- 创建函数
CREATE FUNCTION get_customer_name (customer_id INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE customer_name VARCHAR(100);
    SELECT name INTO customer_name FROM customers WHERE id = customer_id;
    RETURN customer_name;
END;

-- 删除函数
DROP FUNCTION get_customer_name;

-- 单条插入
INSERT INTO basic_table (name, age, salary) VALUES ('Alice', 30, 50000.00);

-- 批量插入
INSERT INTO basic_table (name, age, salary) VALUES 
    ('Bob', 35, 60000.00),
    ('Charlie', 40, 70000.00),
    ('David', 45, 80000.00);

-- 从另一个表插入数据
INSERT INTO basic_table (name, age, salary)
SELECT name, age, salary FROM temp_table;

-- 正常插入操作
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', 'user2@example.com');

INSERT INTO products (name, description, price, stock) VALUES ('Product 1', 'Description 1', 10.99, 100);
INSERT INTO products (name, description, price, stock) VALUES ('Product 2', 'Description 2', 20.99, 200);

-- 重复数据插入操作（唯一约束）
-- 此处尝试插入已存在的username和email
INSERT INTO users (username, password, email) VALUES ('user1', 'password3', 'user3@example.com'); -- 应该失败，username已存在
INSERT INTO users (username, password, email) VALUES ('user3', 'password3', 'user1@example.com'); -- 应该失败，email已存在

-- 外键约束测试
-- 尝试插入不存在的user_id
INSERT INTO orders (user_id, total) VALUES (100, 50.00); -- 应该失败，user_id不存在

-- 正常插入订单及订单项目
INSERT INTO orders (user_id, total) VALUES (1, 100.00);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 2, 20.00);

-- 单表更新
UPDATE basic_table SET salary = 55000.00 WHERE name = 'Alice';

-- 带条件更新
UPDATE basic_table SET salary = salary * 1.1 WHERE age > 40;

-- 跨表更新（SQLite 不支持 JOIN UPDATE，但可以通过子查询实现）
UPDATE basic_table 
SET salary = (
    SELECT salary * 1.05 FROM basic_table WHERE name = 'Alice'
) 
WHERE name = 'Alice';

-- 批量更新
UPDATE basic_table 
SET salary = salary * 1.05 
WHERE age BETWEEN 30 AND 40;


-- 更新用户信息：将用户名为'john_doe'的用户的密码修改为'new_password'
UPDATE users SET password = 'new_password' WHERE username = 'john_doe';

-- 更新产品信息：将名为'Laptop'的产品的描述修改为'Powerful gaming laptop'
UPDATE products SET description = 'Powerful gaming laptop' WHERE name = 'Laptop';

-- 更新订单信息：将总价大于1000的订单的总价修改为1200
UPDATE orders SET total = 1200 WHERE total > 1000;

-- 更新订单项目信息：将订单号为1，产品号为1的订单项目的数量修改为2
UPDATE order_items SET quantity = 2 WHERE order_id = 1 AND product_id = 1;

-- 更新不存在的用户：尝试将用户名为'non_existent_user'的用户的邮箱修改为'new_email@example.com'
-- 期望结果：不会影响任何行，因为用户不存在，所以不会有行受到影响
UPDATE users SET email = 'new_email@example.com' WHERE username = 'non_existent_user';

-- 更新时违反唯一约束：尝试将用户名为'john_doe'的用户的邮箱修改为'john_new@example.com'，这个邮箱已经被另一个用户使用
-- 期望结果：更新操作失败，不会修改任何行，并且数据库保持不变
UPDATE users SET email = 'john_new@example.com' WHERE username = 'john_doe';

-- 更新时违反外键约束：尝试将订单表中不存在的用户ID（999）更新到订单表中的某条订单的user_id字段
-- 期望结果：更新操作失败，不会修改任何行，并且数据库保持不变
UPDATE orders SET user_id = 999 WHERE id = 1;

-- 内联更新
-- 在订单表中，将所有订单总额小于100的订单的总额增加10%
UPDATE orders SET total = total * 1.1 WHERE total < 100;

-- 在订单表中，将用户ID为1的所有订单的总额增加50
UPDATE orders SET total = total + 50 WHERE user_id = 1;

-- 单表删除
DELETE FROM basic_table WHERE name = 'David';

-- 带条件删除
DELETE FROM basic_table WHERE age > 40;

-- 删除所有数据
DELETE FROM basic_table;

-- 删除单个表中的所有数据
DELETE FROM users;

-- 删除单个表中满足条件的数据
DELETE FROM products WHERE price > 1000.00;

-- 使用子查询来删除符合条件的数据
DELETE FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000.00);

-- 删除关联表中的数据
DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id = 1);

-- 删除具有外键关联的主表数据（将级联删除从属表中的数据）
DELETE FROM users WHERE id = 1;

-- 删除具有外键关联的从属表数据（如果未使用级联删除）
DELETE FROM orders WHERE user_id = 2;

-- 删除所有表中的数据（谨慎使用！）
DELETE FROM users;
DELETE FROM products;
DELETE FROM orders;
DELETE FROM order_items;

-- 测试内联条件删除
-- 准备数据
INSERT INTO users (username, password, email) VALUES 
('user1', 'password1', 'user1@example.com'),
('user2', 'password2', 'user2@example.com'),
('user3', 'password3', 'user3@example.com');

-- 删除 username 为 'user2' 的用户
DELETE FROM users WHERE username = 'user2';

-- 清空 users 表（TRUNCATE）
-- 注意：SQLite不支持TRUNCATE TABLE语法，因此我们使用DELETE FROM来清空表
DELETE FROM users;

-- 合并数据（模拟）
INSERT INTO target_table (id, name, value)
SELECT source_id, source_name, source_value
FROM source_table
ON CONFLICT(id) DO UPDATE SET
name = excluded.name,
value = excluded.value;

-- 复制数据到另一个表
INSERT INTO new_table (name, age, salary)
SELECT name, age, salary FROM basic_table;

-- 删除表数据并备份
CREATE TABLE backup_table AS SELECT * FROM basic_table;
DELETE FROM basic_table;

-- 清理旧数据
DELETE FROM basic_table WHERE hire_date < DATE('now', '-1 year');

-- 数据备份与维护
CREATE TABLE backup_table AS SELECT * FROM basic_table;
VACUUM; -- 压缩数据库文件

-- 开始事务
BEGIN TRANSACTION;

-- 插入数据
INSERT INTO basic_table (name, age, salary) VALUES ('Eve', 25, 45000.00);

-- 提交事务
COMMIT;

-- 数据转换与格式化
SELECT 
    name, 
    age, 
    '$' || CAST(salary AS TEXT) AS formatted_salary 
FROM 
    basic_table;

-- 数据的统计与分析
SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM 
    basic_table;

-- 数据的加密与解密（SQLite不原生支持加密，需要借助扩展库或应用层加密）
-- 示例：使用SQLCipher扩展库
PRAGMA key='password'; -- 设置加密密码
ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'password';
SELECT sqlcipher_export('encrypted');
DETACH DATABASE encrypted;

-- 数据的随机生成
INSERT INTO basic_table (name, age, salary) 
VALUES 
    ('Random1', ROUND(RANDOM() * 50 + 20), ROUND(RANDOM() * 50000 + 30000)),
    ('Random2', ROUND(RANDOM() * 50 + 20), ROUND(RANDOM() * 50000 + 30000)),
    ('Random3', ROUND(RANDOM() * 50 + 20), ROUND(RANDOM() * 50000 + 30000));

-- 数据的格式化输出
.mode column
.headers on
SELECT * FROM basic_table;

-- 数据的版本控制与变更跟踪
-- 使用触发器实现数据变更记录
CREATE TABLE change_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT,
    operation TEXT,
    old_data TEXT,
    new_data TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_change_log
AFTER INSERT OR UPDATE OR DELETE ON basic_table
BEGIN
    INSERT INTO change_log (table_name, operation, old_data, new_data) 
    VALUES ('basic_table', 
            CASE
                WHEN OLD.id IS NULL THEN 'INSERT'
                WHEN NEW.id IS NULL THEN 'DELETE'
                ELSE 'UPDATE'
            END,
            OLD.*,
            NEW.*);
END;

-- 数据的转置与旋转（SQLite 不支持原生的数据转置与旋转，但可以通过复杂的查询实现）
SELECT 
    MAX(CASE WHEN age = 30 THEN name ELSE NULL END) AS age_30,
    MAX(CASE WHEN age = 35 THEN name ELSE NULL END) AS age_35,
    MAX(CASE WHEN age = 40 THEN name ELSE NULL END) AS age_40
FROM 
    basic_table;

-- 数据的归一化与反归一化（通过标准化或反标准化数据）
-- 示例：将工资数据进行归一化
SELECT 
    name, 
    (salary - MIN(salary)) / (MAX(salary) - MIN(salary)) AS normalized_salary 
FROM 
    basic_table;

-- 数据的检查与验证（通过约束和触发器实现）
-- 示例：检查年龄是否大于等于18岁
CREATE TRIGGER trg_check_age
BEFORE INSERT ON basic_table
BEGIN
    SELECT CASE WHEN NEW.age < 18 THEN RAISE(FAIL, 'Age must be 18 or above') END;
END;

-- 数据的清理与过滤（通过DELETE语句实现）
DELETE FROM basic_table WHERE salary < 30000;

-- 数据的聚合与分类（通过GROUP BY实现）
SELECT 
    CASE 
        WHEN age < 30 THEN 'Young'
        WHEN age >= 30 AND age < 40 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS age_group,
    COUNT(*) AS count
FROM 
    basic_table
GROUP BY 
    age_group;

-- 数据的分区与汇总（通过窗口函数实现）
SELECT 
    name, 
    age,
    salary,
    SUM(salary) OVER (PARTITION BY age) AS total_salary_by_age
FROM 
    basi_table;

-- 数据的连接与匹配（通过JOIN实现）
SELECT 
    basic_table.name, 
    department.dept_name 
FROM 
    basic_table 
JOIN 
    department ON basic_table.dept_id = department.ept_id;

-- 数据的时间处理与计算（通过日期函数实现）
SELECT 
    name,
    hire_date,
    STRFTIME('%Y', hire_date) AS hire_year,
    DATE('now') - hire_date AS days_since_hire
FROM 
    basic_table;

-- 数据的序列与编号（通过ROW_NUMBER()窗口函数实现）
SELECT 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS ranking,
    name,
    salary
FROM 
    basic_table;

-- 数据的交叉与分割（通过交叉连接实现）
SELECT 
    t1.name AS name1,
    t2.name AS name2
FROM 
    basic_table t1
CROSS JOIN 
    basic_table t2
WHERE 
    t1.name != t2.name;

-- 数据的分析与预测（通过统计函数实现）
SELECT 
    name,
    age,
    salary,
    AVG(salary) OVER (PARTITION BY age) AS avg_salary_by_age,
    LAG(salary) OVER (ORDER BY age) AS prev_salary,
    LEAD(salary) OVER (ORDER BY age) AS next_salary
FROM 
    basic_table;

-- 数据的归档与压缩（通过归档表和压缩数据库实现）
-- 创建归档表
CREATE TABLE archived_basic_table AS SELECT * FROM basic_table WHERE hire_date < DATE('now', '-1 year');

-- 压缩数据库
VACUUM;

-- 数据的审核与审计（通过触发器实现数据变更记录）
CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT,
    operation TEXT,
    old_data TEXT,
    new_data TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_audit_log
AFTER INSERT OR UPDATE OR DELETE ON basic_table
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data) 
    VALUES ('basic_table', 
            CASE
                WHEN OLD.id IS NULL THEN 'INSERT'
                WHEN NEW.id IS NULL THEN 'DELETE'
                ELSE 'UPDATE'
            END,
            OLD.*,
            NEW.*);
END;

-- 数据的转换与映射（通过CASE语句实现）
SELECT 
    name,
    CASE
        WHEN age < 30 THEN 'Young'
        WHEN age >= 30 AND age < 50 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS age_group
FROM 
    basic_table;

-- 数据的版本控制与变更跟踪（通过版本号和变更日志实现）
ALTER TABLE basic_table ADD COLUMN version INTEGER DEFAULT 1;

CREATE TABLE change_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT,
    operation TEXT,
    old_data TEXT,
    new_data TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_change_log
AFTER INSERT OR UPDATE OR DELETE ON basic_table
BEGIN
    INSERT INTO change_log (table_name, operation, old_data, new_data) 
    VALUES ('basic_table', 
            CASE
                WHEN OLD.id IS NULL THEN 'INSERT'
                WHEN NEW.id IS NULL THEN 'DELETE'
                ELSE 'UPDATE'
            END,
            OLD.*,
            NEW.*);
    UPDATE basic_table SET version = version + 1 WHERE id = NEW.id;
END;

-- 数据的权限控制与访问管理（通过授权和角色管理实现）
-- 创建角色
CREATE ROLE admin;

-- 授予角色权限
GRANT ALL ON basic_table TO admin;

-- 创建用户并授予角色
CREATE USER user1 WITH PASSWORD 'password1';
GRANT admin TO user1;

-- 撤销权限
REVOKE ALL ON basic_table FROM admin;

-- 数据的分组与汇总（通过GROUP BY和聚合函数实现）
SELECT 
    department,
    COUNT(*) AS num_employees,
    AVG(salary) AS avg_salary
FROM 
    employee_table
GROUP BY 
    department;

-- 数据的筛选与过滤（通过WHERE子句实现）
SELECT * FROM employee_table WHERE age > 30 AND department = 'IT';

-- 数据的拆分与合并（通过字符串函数实现）
SELECT 
    SUBSTR(name, 1, 1) AS first_initial,
    SUBSTR(name, 2) AS remaining_name
FROM 
    employee_table;

-- 数据的加密与解密（SQLite不原生支持加密，需要借助扩展库或应用层加密）
-- 示例：使用SQLCipher扩展库
PRAGMA key='password'; -- 设置加密密码
ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'password';
SELECT sqlcipher_export('encrypted');
DETACH DATABASE encrypted;

-- 数据的唯一性与一致性（通过UNIQUE约束实现）
CREATE TABLE unique_table (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE
);

-- 数据的随机化与分布（通过RANDOM函数实现）
SELECT 
    name, 
    RANDOM() AS random_number 
FROM 
    employee_table;

-- 数据的缓存与优化（通过索引和视图实现）
CREATE INDEX idx_name ON employee_table (name);

CREATE VIEW high_salary_employees AS 
SELECT * FROM employee_table WHERE salary > 50000;

-- 数据的批量导入与导出（通过导入导出命令实现）
-- 导出数据到CSV文件
.headers on
.mode csv
.output data.csv
SELECT * FROM employee_table;
.output stdout

-- 从CSV文件导入数据
.mode csv
.import data.csv employee_table

-- 数据的动态生成与填充（通过循环和自增函数实现）
INSERT INTO numbers_table (number) VALUES (1);
WITH RECURSIVE generate_numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM generate_numbers WHERE n < 100
)
INSERT INTO numbers_table (number)
SELECT n FROM generate_numbers;

-- 数据的滑动窗口与移动平均（通过窗口函数实现）
SELECT 
    date,
    value,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales_data;

-- 数据的差异与比较（通过JOIN和比较操作符实现）
SELECT 
    t1.name,
    t1.salary AS current_salary,
    t2.salary AS previous_salary,
    t1.salary - t2.salary AS salary_change
FROM 
    employee_table t1
JOIN 
    previous_employee_table t2 ON t1.id = t2.id;

-- 数据的周期性与季节性（通过日期函数和条件过滤实现）
SELECT 
    date,
    value,
    CASE 
        WHEN STRFTIME('%m', date) IN ('03', '04', '05') THEN 'Spring'
        WHEN STRFTIME('%m', date) IN ('06', '07', '08') THEN 'Summer'
        WHEN STRFTIME('%m', date) IN ('09', '10', '11') THEN 'Fall'
        WHEN STRFTIME('%m', date) IN ('12', '01', '02') THEN 'Winter'
    END AS season
FROM 
    sales_data;

-- 数据的时间序列与趋势（通过时间函数和聚合函数实现）
SELECT 
    STRFTIME('%Y-%m', date) AS month,
    AVG(value) AS avg_value
FROM 
    sales_data
GROUP BY 
    month;

-- 数据的地理空间处理（使用SQLite的地理空间扩展）
CREATE VIRTUAL TABLE locations USING fts5(name, latitude, longitude);
INSERT INTO locations (name, latitude, longitude) VALUES ('New York', 40.7128, -74.0060);
INSERT INTO locations (name, latitude, longitude) VALUES ('Los Angeles', 34.0522, -118.2437);
SELECT * FROM locations WHERE distance(latitude, longitude, 40.7128, -74.0060) < 50;

-- 数据的机器学习与预测（通过扩展库实现）
-- 示例：使用SQLiteML实现线性回归
CREATE TABLE training_data (x INTEGER, y INTEGER);
INSERT INTO training_data VALUES (1, 2), (2, 3), (3, 4), (4, 5), (5, 6);
SELECT linearmodel_create('lm', 'training_data', 'y', 'x');
SELECT linearmodel_predict('lm', 6);

-- 数据的分布式处理（使用SQLite的分布式扩展）
-- 示例：使用DBHub.io进行分布式查询
ATTACH DATABASE 'https://dbhub.io/user/db/example.db' AS remote;
SELECT * FROM remote.employee_table;

-- 数据的多语言处理（通过国际化和本地化实现）
CREATE TABLE multilingual_data (
    id INTEGER PRIMARY KEY,
    english TEXT,
    chinese TEXT
);
INSERT INTO multilingual_data (english, chinese) VALUES ('Hello', '你好');
SELECT english, chinese FROM multilingual_data;

-- 数据的图像处理与分析（通过扩展库实现）
-- 示例：使用SQLite的图像处理扩展
CREATE VIRTUAL TABLE images USING rtree(id, left, top, right, bottom);
INSERT INTO images VALUES (1, 0, 0, 100, 100);
SELECT * FROM images WHERE intersects(left, top, right, bottom, 50, 50, 60, 60);

-- 数据的音频处理与分析（通过扩展库实现）
-- 示例：使用SQLite的音频处理扩展
CREATE VIRTUAL TABLE audio USING audiotable(id, data);
INSERT INTO audio VALUES (1, 'path/to/audio.wav');
SELECT * FROM audio WHERE audiotable_detect_language(data) = 'English';

-- 数据的半结构化处理与分析（通过JSON和XML函数实现）
CREATE TABLE json_data (id INTEGER PRIMARY KEY, data JSON);
INSERT INTO json_data (data) VALUES ('{"name": "John", "age": 30}');
SELECT json_extract(data, '$.name') AS name FROM json_data;

-- 数据的自然语言处理与分析（通过全文搜索实现）
CREATE VIRTUAL TABLE documents USING fts5(content);
INSERT INTO documents VALUES ('This is a document about SQLite.');
SELECT * FROM documents WHERE documents MATCH 'SQLite';

-- 数据的事件流处理与分析（通过流处理库实现）
-- 示例：使用SQLite的流处理扩展
CREATE STREAM sensor_data (time TIMESTAMP, value REAL);
INSERT INTO sensor_data VALUES ('2024-05-20 08:00:00', 25.5);
SELECT * FROM sensor_data WHERE time >= '2024-05-20 08:00:00';

-- 数据的知识图谱处理与分析（通过图数据库实现）
-- 示例：使用SQLite的图数据库扩展
CREATE TABLE graph (source TEXT, target TEXT);
INSERT INTO graph VALUES ('A', 'B'), ('B', 'C'), ('C', 'D');
WITH RECURSIVE traverse_graph(source, target, depth) AS (
    SELECT source, target, 1 FROM graph
    UNION ALL
    SELECT g.source, g.target, tg.depth + 1 FROM graph AS g
    JOIN traverse_graph AS tg ON g.source = tg.target
)
SELECT * FROM traverse_graph;

-- 数据的可视化与报表（通过查询和图表库实现）
-- 示例：使用SQLite的图表扩展
CREATE VIEW sales_report AS 
SELECT 
    STRFTIME('%Y-%m', date) AS month,
    SUM(amount) AS total_sales
FROM 
    sales_data
GROUP BY 
    month;
SELECT * FROM sales_report;

-- 数据的智能推荐与个性化（通过机器学习和推荐算法实现）
-- 示例：使用SQLite的推荐扩展
CREATE TABLE user_preferences (user_id INTEGER, item_id INTEGER, rating INTEGER);
INSERT INTO user_preferences VALUES (1, 1, 5), (1, 2, 4), (2, 1, 4), (2, 3, 5);
SELECT * FROM recommender_model('user_preferences');

-- 数据的异常检测与处理（通过统计方法实现）
-- 示例：使用Z分数进行异常检测
WITH stats AS (
    SELECT 
        AVG(value) AS mean,
        STDEV(value) AS std_dev
    FROM 
        sensor_data
)
SELECT 
    sensor_data.*,
    (sensor_data.value - stats.mean) / stats.std_dev AS z_score
FROM 
    sensor_data
CROSS JOIN 
    stats;

-- 数据的内容分析与分类（通过文本处理函数和机器学习模型实现）
CREATE TABLE text_data (id INTEGER PRIMARY KEY, text TEXT, category TEXT);
INSERT INTO text_data (text) VALUES ('This is a positive review'), ('This is a negative review');
CREATE VIRTUAL TABLE text_classifier USING classifier(tokenize='porter', type='svm');
INSERT INTO text_classifier(text, category) SELECT text, category FROM text_data;
SELECT * FROM text_classifier WHERE text MATCH 'positive';

-- 数据的语义分析与推理（通过语义网络实现）
-- 示例：使用SQLite的语义网络扩展
CREATE TABLE semantic_network (subject TEXT, predicate TEXT, object TEXT);
INSERT INTO semantic_network VALUES ('cat', 'is_a', 'mammal'), ('mammal', 'is_a', 'animal');
SELECT * FROM semantic_network WHERE subject = 'cat';

-- 数据的动态更新与变更（通过触发器实现）
CREATE TRIGGER update_timestamp AFTER UPDATE ON employee_table
BEGIN
    UPDATE employee_table SET last_updated = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- 数据的分级控制与权限管理（通过视图和授权实现）
CREATE VIEW sensitive_data AS SELECT * FROM employee_table WHERE salary > 100000;
GRANT SELECT ON sensitive_data TO manager_role;

-- 数据的连续查询与复杂分析（通过子查询和窗口函数实现）
WITH monthly_sales AS (
    SELECT 
        STRFTIME('%Y-%m', date) AS month,
        SUM(amount) AS total_sales
    FROM 
        sales_data
    GROUP BY 
        month
)
SELECT 
    month,
    total_sales,
    AVG(total_sales) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    monthly_sales;

-- 数据的实时流式处理与分析（通过流处理库实现）
-- 示例：使用SQLite的流处理扩展
CREATE STREAM real_time_data (time TIMESTAMP, value REAL);
INSERT INTO real_time_data VALUES ('2024-05-20 08:00:00', 25.5);
SELECT * FROM real_time_data WHERE time >= '2024-05-20 08:00:00';

-- 数据的多模态处理与分析（通过多模态扩展库实现）
-- 示例：使用SQLite的多模态扩展库
CREATE VIRTUAL TABLE multimedia_data USING multimodal(name TEXT, image BLOB, audio BLOB, video BLOB);
INSERT INTO multimedia_data VALUES ('Item 1', 'path/to/image.jpg', 'path/to/audio.wav', 'path/to/video.mp4');
SELECT * FROM multimedia_data;

-- 数据的关联规则挖掘与分析（通过关联规则挖掘算法实现）
-- 示例：使用SQLite的关联规则挖掘扩展库
CREATE TABLE market_basket (transaction_id INTEGER, item TEXT);
INSERT INTO market_basket VALUES (1, 'apple'), (1, 'banana'), (2, 'apple'), (2, 'orange');
SELECT * FROM apriori('market_basket', 0.5, 0.5);

-- 数据的时间序列模式挖掘与分析（通过时间序列模式挖掘算法实现）
-- 示例：使用SQLite的时间序列模式挖掘扩展库
CREATE TABLE sensor_data (time TIMESTAMP, value REAL);
INSERT INTO sensor_data VALUES ('2024-05-20 08:00:00', 25.5), ('2024-05-20 08:15:00', 26.0);
SELECT * FROM sequence_mining('sensor_data', 'time', 2, 0.5);

-- 数据的演化与迁移（通过数据迁移工具实现）
-- 示例：使用SQLite的数据迁移工具
ATTACH DATABASE 'old_data.db' AS old;
INSERT INTO new_table SELECT * FROM old.old_table;

-- 数据的模型管理与部署（通过模型管理平台实现）
-- 示例：使用SQLite的模型管理扩展库
CREATE MODEL classification_model USING svm(features, target);

-- 数据的文档化与注释（通过注释实现）
-- 示例：在表上添加注释
COMMENT ON TABLE employee_table IS 'This table stores employee information.';

-- 数据的增强与增值（通过数据增值服务实现）
-- 示例：使用SQLite的增值服务扩展库
SELECT * FROM enrich_data('employee_table');

-- 用户表
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT NOT NULL
);

-- 权限表
CREATE TABLE permissions (
    perm_id INTEGER PRIMARY KEY AUTOINCREMENT,
    role TEXT NOT NULL,
    resource TEXT NOT NULL,
    can_read BOOLEAN NOT NULL,
    can_write BOOLEAN NOT NULL,
    can_delete BOOLEAN NOT NULL
);

-- 插入用户
INSERT INTO users (username, password, role) VALUES ('admin', 'admin_password', 'admin');
INSERT INTO users (username, password, role) VALUES ('editor', 'editor_password', 'editor');
INSERT INTO users (username, password, role) VALUES ('viewer', 'viewer_password', 'viewer');

-- 插入权限
INSERT INTO permissions (role, resource, can_read, can_write, can_delete) VALUES 
('admin', 'all', 1, 1, 1),
('editor', 'articles', 1, 1, 0),
('viewer', 'articles', 1, 0, 0);

-- 给编辑用户授予删除文章的权限
UPDATE permissions SET can_delete = 1 WHERE role = 'editor' AND resource = 'articles';

-- 给查看用户授予写文章的权限
UPDATE permissions SET can_write = 1 WHERE role = 'viewer' AND resource = 'articles';

-- 从编辑用户撤销删除文章的权限
UPDATE permissions SET can_delete = 0 WHERE role = 'editor' AND resource = 'articles';

-- 从查看用户撤销写文章的权限
UPDATE permissions SET can_write = 0 WHERE role = 'viewer' AND resource = 'articles';

-- 查询编辑用户的文章权限
SELECT * FROM permissions WHERE role = 'editor' AND resource = 'articles';

-- 查询查看用户的文章权限
SELECT * FROM permissions WHERE role = 'viewer' AND resource = 'articles';

-- 示例：插入文章时检查权限
INSERT INTO articles (title, content, author_id) 
SELECT 'New Article', 'This is a new article.', user_id 
FROM users 
WHERE username = 'editor' AND (SELECT can_write FROM permissions WHERE role = users.role AND resource = 'articles') = 1;

-- 示例：删除文章时检查权限
DELETE FROM articles 
WHERE article_id = 1 AND 
(SELECT can_delete FROM permissions WHERE role = (SELECT role FROM users WHERE username = 'editor') AND resource = 'articles') = 1;

-- 示例：查询文章时检查权限
SELECT * FROM articles 
WHERE (SELECT can_read FROM permissions WHERE role = (SELECT role FROM users WHERE username = 'viewer') AND resource = 'articles') = 1;

-- 用户登录验证
SELECT * FROM users WHERE username = 'admin' AND password = 'admin_password';

-- 验证用户是否具有特定权限
SELECT * FROM permissions WHERE role = 'editor' AND resource = 'articles' AND can_write = 1;

-- 添加新角色
INSERT INTO permissions (role, resource, can_read, can_write, can_delete) VALUES ('author', 'articles', 1, 1, 0);

-- 删除角色及其权限
DELETE FROM permissions WHERE role = 'author';

-- 给编辑用户的角色继承管理员的所有权限
INSERT INTO permissions (role, resource, can_read, can_write, can_delete) 
SELECT 'editor', resource, can_read, can_write, can_delete FROM permissions WHERE role = 'admin';

-- 修改编辑用户的角色的某些权限
UPDATE permissions SET can_delete = 0 WHERE role = 'editor' AND resource = 'articles';

-- 给超级管理员角色添加特殊权限
INSERT INTO permissions (role, resource, can_read, can_write, can_delete) VALUES ('super_admin', 'all', 1, 1, 1);

-- 确认用户是否拥有超级管理员权限
SELECT * FROM permissions WHERE role = 'super_admin' AND resource = 'all';

-- 给特定用户授予特定资源的特定权限
INSERT INTO permissions (role, resource, can_read, can_write, can_delete) VALUES ('editor', 'specific_resource', 1, 0, 0);

-- 确认特定用户是否拥有特定资源的特定权限
SELECT * FROM permissions WHERE role = 'editor' AND resource = 'specific_resource';

-- 创建新用户账户
INSERT INTO users (username, password, role) VALUES ('new_user', 'new_password', 'viewer');

-- 禁用用户账户
UPDATE users SET status = 'disabled' WHERE username = 'new_user';

-- 删除用户账户
DELETE FROM users WHERE username = 'new_user';

-- 管理员重置用户密码
UPDATE users SET password = 'new_password' WHERE username = 'user_to_reset';

-- 管理员将用户角色从编辑者调整为查看者
UPDATE users SET role = 'viewer' WHERE username = 'user_to_adjust';

-- 创建用户登录记录表
CREATE TABLE login_logs (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 记录用户登录信息
INSERT INTO login_logs (user_id) VALUES ((SELECT user_id FROM users WHERE username = 'user_to_log'));

-- 创建用户操作日志表
CREATE TABLE activity_logs (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    activity TEXT,
    activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 记录用户操作日志
INSERT INTO activity_logs (user_id, activity) VALUES ((SELECT user_id FROM users WHERE username = 'user_to_log'), 'Performed action X');

-- 开始一个简单事务
BEGIN TRANSACTION;

-- 提交一个简单事务
COMMIT;

-- 回滚一个简单事务
ROLLBACK;

-- 使用保存点
BEGIN TRANSACTION;

-- 插入一些数据
INSERT INTO users (username, password, email) VALUES ('test_user1', 'password1', 'test1@example.com');

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入更多数据
INSERT INTO users (username, password, email) VALUES ('test_user2', 'password2', 'test2@example.com');

-- 回滚到保存点
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('nested_user1', 'password', 'nested1@example.com');

-- 第一个保存点
SAVEPOINT savepoint1;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('nested_user2', 'password', 'nested2@example.com');

-- 第二个保存点
SAVEPOINT savepoint2;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('nested_user3', 'password', 'nested3@example.com');

-- 回滚到第二个保存点
ROLLBACK TO savepoint2;

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 插入用户和订单
INSERT INTO users (username, password, email) VALUES ('transaction_user', 'password', 'trans@example.com');
INSERT INTO orders (user_id, total) VALUES ((SELECT id FROM users WHERE username = 'transaction_user'), 500.00);

-- 检查插入是否成功
SELECT * FROM users WHERE username = 'transaction_user';
SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'transaction_user');

-- 如果以上操作成功，则提交事务
COMMIT;

-- 如果操作失败，则回滚事务
ROLLBACK;

BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('exception_user', 'password', 'exception@example.com');

-- 模拟异常（尝试插入重复的唯一键）
INSERT INTO users (username, password, email) VALUES ('exception_user', 'password', 'exception@example.com');

-- 如果插入成功，则提交事务
COMMIT;

-- 如果插入失败，则回滚事务
ROLLBACK;

BEGIN TRANSACTION;

-- 插入用户
INSERT INTO users (username, password, email) VALUES ('complex_user', 'password', 'complex@example.com');

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入订单
INSERT INTO orders (user_id, total) VALUES ((SELECT id FROM users WHERE username = 'complex_user'), 1000.00);

-- 创建第二个保存点
SAVEPOINT savepoint2;

-- 插入订单项目
INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES ((SELECT id FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'complex_user')), 1, 2, 500.00);

-- 回滚到第一个保存点
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 查询用户信息
SELECT * FROM users WHERE username = 'atomic_user';

-- 插入订单
INSERT INTO orders (user_id, total) VALUES ((SELECT id FROM users WHERE username = 'atomic_user'), 200.00);

-- 更新用户信息
UPDATE users SET email = 'atomic@example.com' WHERE username = 'atomic_user';

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 插入用户信息
INSERT INTO users (username, password, email) VALUES ('rollback_user', 'password', 'rollback@example.com');

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入订单
INSERT INTO orders (user_id, total) VALUES ((SELECT id FROM users WHERE username = 'rollback_user'), 300.00);

-- 插入订单项目
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ((SELECT id FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'rollback_user')), 1, 2, 150.00);

-- 回滚到保存点
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 插入用户信息
INSERT INTO users (username, password, email) VALUES ('multi_savepoint_user', 'password', 'multi_savepoint@example.com');

-- 创建保存点1
SAVEPOINT savepoint1;

-- 插入订单
INSERT INTO orders (user_id, total) VALUES ((SELECT id FROM users WHERE username = 'multi_savepoint_user'), 400.00);

-- 创建保存点2
SAVEPOINT savepoint2;

-- 插入订单项目
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ((SELECT id FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'multi_savepoint_user')), 1, 2, 200.00);

-- 回滚到保存点1
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 设置事务超时时间为10秒
PRAGMA busy_timeout = 10000;

-- 插入大量数据（模拟长时间执行）
INSERT INTO big_table (data) SELECT randomblob(1000000) FROM generate_series(1, 10000);

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 查询用户信息
SELECT * FROM users WHERE username = 'transaction_user';

-- 查询订单信息
SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE username = 'transaction_user');

-- 提交事务
COMMIT;

BEGIN TRANSACTION;

-- 插入用户信息
INSERT INTO users (username, password, email) VALUES ('nested_transaction_user', 'password', 'nested@example.com');

-- 创建保存点
SAVEPOINT savepoint1;

-- 开始内部事务
BEGIN TRANSACTION;

-- 尝试插入重复的数据，模拟异常
INSERT INTO users (username, password, email) VALUES ('nested_transaction_user', 'password', 'nested@example.com');

-- 如果出现异常，回滚到保存点
ROLLBACK TO savepoint1;

-- 提交内部事务
COMMIT;

-- 提交外部事务
COMMIT;

-- 设置事务隔离级别为READ UNCOMMITTED
PRAGMA read_uncommitted = true;

-- 设置事务隔离级别为READ COMMITTED
PRAGMA read_uncommitted = false;

-- 设置事务隔离级别为REPEATABLE READ
PRAGMA read_uncommitted = false;

-- 设置事务隔离级别为SERIALIZABLE
PRAGMA read_uncommitted = false;

-- 创建一个长时间执行的事务
BEGIN TRANSACTION;

-- 插入大量数据
INSERT INTO big_table (data) SELECT randomblob(1000000) FROM generate_series(1, 10000);

-- 查询操作（模拟长时间执行）
SELECT * FROM users;

-- 提交事务
COMMIT;

-- 查看当前活动的事务
SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'sqlite_transaction';

-- 终止指定的事务
PRAGMA foreign_keys = true; -- 需要启用外键约束
DELETE FROM sqlite_transaction WHERE tid = <transaction_id>;

-- 在多个客户端连接中执行并发事务操作
-- 客户端1
BEGIN TRANSACTION;
-- 客户端2
BEGIN TRANSACTION;
-- ... 其他客户端
-- 提交或回滚事务

-- 客户端1开始一个读事务
BEGIN TRANSACTION;

-- 客户端2尝试修改受客户端1事务影响的数据
-- 如果客户端1的事务一直处于活动状态，客户端2的事务将被阻塞

-- 客户端1开始一个写事务
BEGIN TRANSACTION;

-- 客户端2尝试读取受客户端1事务影响的数据
-- 如果客户端1的事务一直处于活动状态，客户端2的读操作将被阻塞

-- 启用事务日志
PRAGMA journal_mode = WAL;

-- 使事务日志满，导致后续事务操作被阻塞

-- 设置事务日志文件的最大大小
PRAGMA journal_size_limit = 1000000; -- 1MB

-- 当事务日志文件达到最大大小时，SQLite会自动回滚事务并清空日志文件，以防止文件过大影响性能

-- 开启间隔提交模式
PRAGMA synchronous = OFF;

-- 在大批量插入或更新操作后，显式提交事务以减少IO压力和提高性能

-- 调整事务日志模式和同步方式
PRAGMA journal_mode = MEMORY; -- 使用内存模式以提高性能
PRAGMA synchronous = OFF; -- 关闭同步以减少IO压力

-- 完成大量数据插入或更新操作后，显式提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 模拟错误：尝试插入重复的唯一键
INSERT INTO users (username, password, email) VALUES ('user1', 'password2', 'user2@example.com');

-- 提交事务
COMMIT;

-- 如果发生错误，事务将自动回滚

-- 开始事务
BEGIN TRANSACTION;

-- 模拟长时间执行的操作
INSERT INTO big_table (data) SELECT randomblob(1000000) FROM generate_series(1, 10000);

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 模拟异常处理：尝试插入无效数据
INSERT INTO users (username, password, email) VALUES (NULL, 'password', 'invalid_email');

-- 如果发生异常，记录错误日志并回滚事务
-- INSERT INTO error_log (timestamp, message) VALUES (CURRENT_TIMESTAMP, 'Failed to insert user data.');

-- 提交事务
COMMIT;

-- 客户端1
BEGIN TRANSACTION;
-- 执行事务操作
COMMIT;

-- 客户端2
BEGIN TRANSACTION;
-- 执行事务操作
COMMIT;

-- 客户端3
BEGIN TRANSACTION;
-- 执行事务操作
COMMIT;

-- ...

-- 客户端1
BEGIN TRANSACTION;
-- 执行读取操作
SELECT * FROM users;
-- 客户端2
BEGIN TRANSACTION;
-- 执行写入操作
INSERT INTO users (username, password, email) VALUES ('new_user', 'password', 'new@example.com');
-- 提交客户端2的事务
COMMIT;
-- 客户端1再次执行读取操作
SELECT * FROM users;
-- 客户端1提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 模拟可能出现的错误：违反唯一约束
INSERT INTO users (username, password, email) VALUES ('user1', 'password2', 'user2@example.com');

-- 如果出现错误，则回滚事务并重试
ROLLBACK;

-- 再次尝试插入数据
BEGIN TRANSACTION;
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');
COMMIT;

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 查询用户信息
SELECT * FROM users WHERE username = 'user1';

-- 客户端2开始事务
BEGIN TRANSACTION;

-- 修改用户密码
UPDATE users SET password = 'new_password' WHERE username = 'user1';

-- 客户端1尝试修改用户邮箱
UPDATE users SET email = 'new_email@example.com' WHERE username = 'user1';

-- 提交客户端1的事务
COMMIT;

-- 提交客户端2的事务
COMMIT;

-- 在数据库A上开始事务
BEGIN TRANSACTION;

-- 在数据库B上开始事务
BEGIN TRANSACTION;

-- 在数据库A上插入数据
INSERT INTO databaseA.users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 在数据库B上插入数据
INSERT INTO databaseB.orders (user_id, total) VALUES ((SELECT id FROM databaseA.users WHERE username = 'user1'), 100.00);

-- 提交数据库B上的事务
COMMIT;

-- 提交数据库A上的事务
COMMIT;

-- 开启异步事务提交模式
PRAGMA synchronous = NORMAL;

-- 开始事务
BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 异步提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 提交事务（模拟失败）
COMMIT;

-- 如果事务提交失败，则进行回滚处理
ROLLBACK;

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 查询用户信息
SELECT * FROM users WHERE username = 'user1';

-- 客户端2同时修改用户信息
UPDATE users SET password = 'new_password' WHERE username = 'user1';

-- 客户端1再次查询用户信息
SELECT * FROM users WHERE username = 'user1';

-- 提交客户端1的事务
COMMIT;

-- 提交客户端2的事务
COMMIT;

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 修改用户密码
UPDATE users SET password = 'new_password' WHERE username = 'user1';

-- 客户端2同时查询用户信息
SELECT * FROM users WHERE username = 'user1';

-- 客户端1提交事务
COMMIT;

-- 客户端2再次查询用户信息
SELECT * FROM users WHERE username = 'user1';

-- 设置事务隔离级别为READ UNCOMMITTED
PRAGMA read_uncommitted = true;

-- 设置事务隔离级别为READ COMMITTED
PRAGMA read_uncommitted = false;

-- 设置事务隔离级别为REPEATABLE READ
PRAGMA read_uncommitted = false;

-- 设置事务隔离级别为SERIALIZABLE
PRAGMA read_uncommitted = false;

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 客户端2同时尝试修改相同数据
BEGIN TRANSACTION;

-- 客户端1提交事务
COMMIT;

-- 客户端2再次尝试提交事务
COMMIT;

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 在事务内部嵌套事务
BEGIN TRANSACTION;

-- 客户端1提交内部事务
COMMIT;

-- 客户端1再次尝试提交外部事务
COMMIT;

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 客户端2同时尝试修改相同数据
BEGIN TRANSACTION;

-- 客户端1尝试修改其他数据，导致死锁
UPDATE users SET password = 'new_password' WHERE username = 'user2';

-- 客户端1尝试提交事务，但被阻塞
COMMIT;

-- 客户端2尝试提交事务，也被阻塞

-- 在外部环境中手动取消事务或等待超时

-- 设置客户端1事务的优先级为低
PRAGMA cache_size = -2000000; -- 设置缓存大小为负值表示低优先级

-- 客户端1开始事务
BEGIN TRANSACTION;

-- 客户端2同时尝试修改相同数据
BEGIN TRANSACTION;

-- 客户端1尝试提交事务，但由于低优先级而被阻塞
COMMIT;

-- 客户端2尝试提交事务，得到执行
COMMIT;

-- 开启延迟提交模式
PRAGMA synchronous = NORMAL;

-- 开始事务
BEGIN TRANSACTION;

-- 插入大量数据
INSERT INTO big_table (data) SELECT randomblob(1000000) FROM generate_series(1, 10000);

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入更多数据
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', 'user2@example.com');

-- 回滚到保存点
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 修改数据
UPDATE users SET password = 'new_password' WHERE username = 'user1';

-- 预留一部分数据，但不提交事务
RESERVE 10000; -- 预留 10000 字节的空间

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点1
SAVEPOINT savepoint1;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 创建保存点2
SAVEPOINT savepoint2;

-- 更新数据
UPDATE users SET password = 'new_password' WHERE username = 'user1';

-- 回滚到保存点1
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建命名保存点
SAVEPOINT my_savepoint;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 释放保存点
RELEASE savepoint1;

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 回滚到保存点
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT savepoint1;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 更新数据
UPDATE users SET password = 'new_password' WHERE username = 'user1';

-- 回滚到保存点
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 在事务1中插入数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');

-- 提交事务1
COMMIT;

-- 开始事务2
BEGIN TRANSACTION;

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 在事务2中查询数据
SELECT * FROM users WHERE username = 'user1';

-- 提交事务2
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 开始事务2
BEGIN TRANSACTION;

-- 在事务2中插入数据
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', 'user2@example.com');

-- 提交事务2
COMMIT;

-- 回滚到保存点（事务2的修改不受影响）
ROLLBACK TO my_savepoint;

-- 提交事务1
COMMIT;

-- 开启外键约束
PRAGMA foreign_keys = ON;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 在事务中插入数据
INSERT INTO users (username, password, email) VALUES ('user3', 'password3', 'user3@example.com');

-- 创建订单，但用户ID为不存在的用户，违反外键约束
INSERT INTO orders (user_id, total) VALUES (1000, 50.00);

-- 提交事务
COMMIT;

-- 创建一个触发器，当插入用户时自动插入订单
CREATE TRIGGER insert_order AFTER INSERT ON users
BEGIN
    INSERT INTO orders (user_id, total) VALUES (NEW.id, 0.00);
END;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 在事务中插入数据
INSERT INTO users (username, password, email) VALUES ('user4', 'password4', 'user4@example.com');

-- 提交事务
COMMIT;

-- 创建一个索引
CREATE INDEX idx_username ON users (username);

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 在事务中插入数据
INSERT INTO users (username, password, email) VALUES ('user5', 'password5', 'user5@example.com');

-- 提交事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 在事务中插入数据
INSERT INTO users (username, password, email) VALUES ('user6', 'password6', 'user6@example.com');

-- 查询用户数据
SELECT * FROM users WHERE username = 'user6';

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 查询用户数据
SELECT * FROM users WHERE username = 'user6';

-- 提交事务
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询用户数据
SELECT * FROM users WHERE username = 'user6';

-- 开始事务2
BEGIN TRANSACTION;

-- 尝试修改相同数据，但被锁定
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 提交事务1
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询用户数据
SELECT * FROM users WHERE username = 'user6';

-- 开始事务2
BEGIN TRANSACTION;

-- 尝试修改相同数据，但被阻塞
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 提交事务1
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询用户数据
SELECT * FROM users WHERE username = 'user6';

-- 更新用户数据
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 再次查询用户数据，观察是否使用缓存
SELECT * FROM users WHERE username = 'user6';

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 再次查询用户数据，观察是否使用缓存
SELECT * FROM users WHERE username = 'user6';

-- 提交事务
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询用户数据
SELECT * FROM users WHERE username = 'user6';

-- 开始事务2
BEGIN TRANSACTION;

-- 尝试修改相同数据
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 提交事务1
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 尝试插入重复数据，触发异常
INSERT INTO users (username, password, email) VALUES ('user6', 'password6', 'user6@example.com');

-- 捕获异常，回滚到保存点
EXCEPTION WHEN UNIQUE_CONSTRAINT_VIOLATION THEN
    ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

-- 开启外键约束
PRAGMA foreign_keys = ON;

-- 创建两个表
CREATE TABLE parent (id INTEGER PRIMARY KEY);
CREATE TABLE child (id INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES parent(id));

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 插入父表数据
INSERT INTO parent (id) VALUES (1);

-- 插入子表数据
INSERT INTO child (id, parent_id) VALUES (1, 1000);

-- 提交事务
COMMIT;

-- 创建一个触发器，当插入数据时触发约束
CREATE TRIGGER check_id BEFORE INSERT ON parent
BEGIN
    SELECT CASE WHEN EXISTS (SELECT 1 FROM parent WHERE id = NEW.id) THEN RAISE(ABORT, 'ID already exists') END;
END;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 尝试插入重复数据，触发触发器
INSERT INTO parent (id) VALUES (1);

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

-- 在不同的数据库链接中进行操作

-- 数据库链接1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询数据
SELECT * FROM users WHERE username = 'user6';

-- 数据库链接2
BEGIN TRANSACTION;

-- 尝试修改相同数据
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 提交数据库链接2的事务
COMMIT;

-- 回滚到保存点（数据库链接1的修改不受影响）
ROLLBACK TO my_savepoint;

-- 提交数据库链接1的事务
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 模拟长时间运行的操作
INSERT INTO big_table (data) SELECT randomblob(1000000) FROM generate_series(1, 10000);

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询数据
SELECT * FROM users WHERE username = 'user6';

-- 开始事务2
BEGIN TRANSACTION;

-- 尝试修改相同数据，但被阻塞
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 回滚到保存点（事务2的修改不受影响）
ROLLBACK TO my_savepoint;

-- 提交事务1
COMMIT;

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询数据并加锁
SELECT * FROM users WHERE username = 'user6' FOR UPDATE;

-- 在另一个事务中尝试修改相同数据，但被阻塞

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 插入数据
INSERT INTO users (username, password, email) VALUES ('user6', 'password6', 'user6@example.com');

-- 提交事务1
COMMIT;

-- 开始事务2
BEGIN TRANSACTION;

-- 查询数据（能够查询到已提交的数据）
SELECT * FROM users WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询数据并加锁
SELECT * FROM users WHERE username = 'user6' FOR UPDATE;

-- 开始事务2
BEGIN TRANSACTION;

-- 尝试修改相同数据，但被阻塞
UPDATE users SET password = 'new_password' WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 回滚到保存点（事务2的修改不受影响）
ROLLBACK TO my_savepoint;

-- 提交事务1
COMMIT;

-- 开始事务1
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 查询数据并加锁
SELECT * FROM users WHERE username = 'user6' FOR UPDATE;

-- 开始事务2
BEGIN TRANSACTION;

-- 查询相同数据（能够读取到，但不能写入）
SELECT * FROM users WHERE username = 'user6';

-- 提交事务2
COMMIT;

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 提交事务1
COMMIT;

-- 创建一个检查约束
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL CHECK (price > 0)
);

-- 开始事务
BEGIN TRANSACTION;

-- 创建保存点
SAVEPOINT my_savepoint;

-- 尝试插入无效数据，触发检查约束
INSERT INTO products (id, name, price) VALUES (1, 'Product1', -10.00);

-- 回滚到保存点
ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

-- 查询所有用户
SELECT * FROM users;

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

-- 条件查询
SELECT * FROM users WHERE username = 'alice';

-- 复杂条件查询
SELECT * FROM users WHERE username = 'alice' AND email LIKE '%@example.com';

-- 使用OR条件
SELECT * FROM users WHERE username = 'alice' OR username = 'bob';

-- 使用IN条件
SELECT * FROM users WHERE username IN ('alice', 'bob', 'carol');

-- 使用BETWEEN条件
SELECT * FROM products WHERE price BETWEEN 50.00 AND 100.00;

-- 使用LIKE条件
SELECT * FROM users WHERE email LIKE '%example.com';

CREATE TABLE t1(x REAL);
INSERT INTO t1 VALUES(1.55e+308),(1.23),(3.2e-16),(-1.23),(-1.55e308);
SELECT sum(x), decimal_sum(x) FROM t1;

WITH t1(x) AS (VALUES(1.0),(-9e+999),(2.0),(+9e+999),(3.0))
 SELECT sum(x) FROM t1;

-- 计算用户表中密码的平均长度
SELECT avg(length(password)) AS avg_password_length FROM users;

-- 计算产品表中产品数量
SELECT count(*) AS product_count FROM products;

-- 计算订单表中的订单数量
SELECT count(*) AS order_count FROM orders;

-- 将用户表中所有用户名连接为一个字符串
SELECT group_concat(username, ', ') AS all_usernames FROM users;

-- 将订单表中订单总额连接为一个字符串，并使用' | '作为分隔符
SELECT group_concat(total, ' | ') AS all_order_totals FROM orders;

-- 找出产品表中价格最高的产品
SELECT max(price) AS max_price FROM products;

-- 找出用户表中注册时间最早的日期
SELECT min(created_at) AS earliest_registration FROM users;

-- 将产品表中所有产品名称连接为一个字符串，并使用' / '作为分隔符
SELECT string_agg(name, ' / ') AS all_product_names FROM products;

-- 计算订单表中所有订单的总额
SELECT sum(total) AS total_sales FROM orders;

-- 计算订单项目表中所有订单项目的数量
SELECT total(quantity) AS total_order_items FROM order_items;

-- 分组并计算每个用户的订单数
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;

-- 分组并计算每个产品的销售数量
SELECT product_id, SUM(quantity) AS total_quantity FROM order_items GROUP BY product_id;

-- 使用HAVING过滤分组结果
SELECT dept_id, AVG(salary) AS average_salary FROM employees GROUP BY dept_id HAVING AVG(salary) > 50000;

-- 按用户名排序
SELECT * FROM users ORDER BY username ASC;

-- 按价格降序排序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序
SELECT * FROM employees ORDER BY dept_id ASC, salary DESC;

-- 内连接查询
SELECT 
    orders.id AS order_id, 
    users.username, 
    products.name AS product_name, 
    order_items.quantity, 
    order_items.price 
FROM 
    orders 
JOIN 
    users ON orders.user_id = users.id 
JOIN 
    order_items ON orders.id = order_items.order_id 
JOIN 
    products ON order_items.product_id = products.id;

-- 左连接查询
SELECT 
    users.username, 
    orders.id AS order_id, 
    orders.total 
FROM 
    users 
LEFT JOIN 
    orders ON users.id = orders.user_id;

-- 右连接查询（SQLite不支持右连接，可以通过调整表顺序模拟）
SELECT 
    orders.id AS order_id, 
    users.username, 
    orders.total 
FROM 
    orders 
LEFT JOIN 
    users ON orders.user_id = users.id;

-- 简单子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100.00);

-- 相关子查询
SELECT username, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users;

-- 从子查询中选择
SELECT * FROM (SELECT username, email FROM users) AS subquery WHERE email LIKE '%example.com';

-- 使用子查询进行聚合
SELECT 
    dept_id, 
    (SELECT AVG(salary) FROM employees AS e WHERE e.dept_id = d.dept_id) AS average_salary 
FROM 
    department AS d;
    
-- 使用子查询进行多层嵌套查询
SELECT * 
FROM 
    (SELECT * FROM users WHERE id IN 
        (SELECT user_id FROM orders WHERE total > 1000)
    ) AS subquery;

-- UNION操作
SELECT username FROM users WHERE email LIKE '%@example.com'
UNION
SELECT username FROM archived_users WHERE email LIKE '%@example.com';

-- UNION ALL操作
SELECT username FROM users WHERE email LIKE '%@example.com'
UNION ALL
SELECT username FROM archived_users WHERE email LIKE '%@example.com';

-- INTERSECT操作
SELECT username FROM users WHERE email LIKE '%@example.com'
INTERSECT
SELECT username FROM archived_users WHERE email LIKE '%@example.com';

-- EXCEPT操作
SELECT username FROM users WHERE email LIKE '%@example.com'
EXCEPT
SELECT username FROM archived_users WHERE email LIKE '%@example.com';

-- JOIN内连接查询：返回两个表中匹配的行
SELECT *
FROM orders
JOIN users ON orders.user_id = users.id;

-- LEFT JOIN左连接查询：返回左表中的所有行，以及右表中匹配的行（如果有的话）
SELECT *
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN右连接查询：返回右表中的所有行，以及左表中匹配的行（如果有的话）
SELECT *
FROM orders
RIGHT JOIN users ON orders.user_id = users.id;

-- 全外连接查询：返回左右两个表中的所有行，并将不匹配的行填充为 NULL
SELECT *
FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION ALL
SELECT *
FROM users
RIGHT JOIN orders ON users.id = orders.user_id
WHERE users.id IS NULL OR orders.id IS NULL;

-- 全外连接查询（另一种形式）：返回左右两个表中的所有行，并将不匹配的行填充为 NULL
SELECT *
FROM users
LEFT OUTER JOIN orders ON users.id = orders.user_id
UNION ALL
SELECT *
FROM users
RIGHT OUTER JOIN orders ON users.id = orders.user_id
WHERE users.id IS NULL OR orders.id IS NULL;

-- 窗口函数：排名
SELECT 
    username, 
    email, 
    RANK() OVER (ORDER BY created_at) AS rank 
FROM 
    users;

-- 窗口函数：移动平均
SELECT 
    product_id, 
    order_date, 
    price, 
    AVG(price) OVER (PARTITION BY product_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg 
FROM 
    order_items;

-- 窗口函数：累计和
SELECT 
    product_id, 
    order_date, 
    price, 
    SUM(price) OVER (PARTITION BY product_id ORDER BY order_date) AS cumulative_sum 
FROM 
    order_items;

-- 示例1：查询产品价格加上10的结果
SELECT *, price + 10 AS new_price FROM products;

-- 示例2：查询用户年龄减去5的结果
SELECT *, age - 5 AS modified_age FROM users;

-- 示例3：查询订单数量乘以2的结果
SELECT *, quantity * 2 AS doubled_quantity FROM orders;

-- 示例4：查询产品价格除以2的结果
SELECT *, price / 2 AS half_price FROM products;

-- 示例5：查询订单数量取余3的结果
SELECT *, quantity % 3 AS remainder FROM orders;

-- 查询用户年龄大于等于18岁的用户：
SELECT * FROM users WHERE age >= 18;

-- 查询产品价格大于50的产品：
SELECT * FROM products WHERE price > 50;

-- 查询订单数量小于等于10的订单：
SELECT * FROM orders WHERE quantity <= 10;

-- 查询订单数量小于10的订单：
SELECT * FROM orders WHERE quantity < 10;

-- 查询产品名称不等于'Apple'的产品：
SELECT * FROM products WHERE name <> 'Apple';

-- 查询产品名称不等于'Apple'的产品：
SELECT * FROM products WHERE name != 'Apple';

-- 使用 AS 关键字给查询结果列起别名
SELECT username AS user_name, email AS user_email FROM users;

-- 使用 BY 关键字指定排序字段
SELECT * FROM products ORDER BY price DESC;

-- 使用 DESC 关键字按降序排序
SELECT * FROM users ORDER BY age DESC;

-- 使用 ASC 关键字按升序排序（默认）
SELECT * FROM orders ORDER BY quantity ASC;

-- 使用 DISTINCT 关键字查询不重复的产品名称
SELECT DISTINCT name FROM products;

-- 插入测试数据到 users 表
INSERT INTO users (username, password, email) VALUES 
('alice', 'password1', 'alice@example.com'),
('bob', 'password2', 'bob@example.com');

-- 插入测试数据到 products 表
INSERT INTO products (name, description, price, stock) VALUES 
('Laptop', 'Gaming Laptop', 1500.00, 10),
('Phone', 'Smartphone', 800.00, 20);

-- 插入测试数据到 orders 表
INSERT INTO orders (user_id, total) VALUES 
(1, 2300.00),
(2, 800.00);

-- 插入测试数据到 order_items 表
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES 
(1, 1, 1, 1500.00),
(1, 2, 1, 800.00),
(2, 2, 1, 800.00);

-- 测试基本算术运算
-- 加法
SELECT 2 + 3 AS addition;

-- 减法
SELECT 5 - 3 AS subtraction;

-- 乘法
SELECT 4 * 2 AS multiplication;

-- 除法
SELECT 10 / 2 AS division;

-- 取余
SELECT 10 % 3 AS modulus;

-- 复杂表达式
SELECT (10 + 5) * 2 / (3 - 1) AS complex_expression;

-- 测试数值函数
-- 绝对值
SELECT ABS(-5) AS absolute_value;

-- 四舍五入
SELECT ROUND(3.14159, 2) AS rounded_value;

-- 幂运算
SELECT POWER(2, 3) AS power_value;

-- 开平方
SELECT SQRT(16) AS square_root;

-- 随机数
SELECT RANDOM() % 100 AS random_value;

-- 余数
SELECT 10 % 4 AS modulus;

-- 最大值和最小值
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;

-- 平均值
SELECT AVG(price) AS average_price FROM products;

-- 合计
SELECT SUM(price) AS total_price FROM products;

-- 测试数值表达式在查询中的应用
-- 计算订单中每个产品的总价
SELECT 
    order_id, 
    product_id, 
    quantity, 
    price, 
    quantity * price AS total_item_price 
FROM 
    order_items;

-- 为用户计算总消费
SELECT 
    users.username, 
    SUM(orders.total) AS total_spent 
FROM 
    users 
JOIN 
    orders ON users.id = orders.user_id 
GROUP BY 
    users.username;

-- 在查询中使用复杂表达式
-- 计算库存总值
SELECT 
    name, 
    price, 
    stock, 
    price * stock AS total_stock_value 
FROM 
    products;

-- 使用 IN 运算符查询属于指定用户角色的用户
SELECT * FROM users WHERE role IN ('admin', 'editor');

-- 使用 OR 运算符查询属于指定用户角色或拥有特定电子邮件地址的用户
SELECT * FROM users WHERE role = 'admin' OR email = 'example@example.com';

-- 使用 AND 运算符查询属于指定用户角色且年龄大于等于18岁的用户
SELECT * FROM users WHERE role = 'admin' AND age >= 18;

-- 使用 NOT 运算符查询不属于指定用户角色的用户
SELECT * FROM users WHERE NOT role = 'admin';

-- 使用 BETWEEN 运算符查询订单数量在10到20之间的订单
SELECT * FROM orders WHERE quantity BETWEEN 10 AND 20;

-- 使用 EXISTS 关键字检查是否存在与用户ID相关联的订单
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- 示例1：查询用户名以字母'A'开头的用户
SELECT * FROM users WHERE username LIKE 'A%';

-- 示例2：查询用户名包含字母'n'的用户
SELECT * FROM users WHERE username LIKE '%n%';

-- 示例3：查询邮箱地址以'@example.com'结尾的用户
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 示例4：查询产品名称以字母'B'开头且长度为5的产品
SELECT * FROM products WHERE name LIKE 'B____';

-- 示例5：查询用户名不以字母'A'开头的用户
SELECT * FROM users WHERE username NOT LIKE 'A%';

-- 示例1：查询用户名以字母 'J' 开头的用户
SELECT * FROM users WHERE username GLOB 'J*';

-- 示例2：查询用户名包含字母 'a' 或 'e' 的用户
SELECT * FROM users WHERE username GLOB '*[ae]*';

-- 示例3：查询邮箱地址以 'gmail.com' 结尾的用户
SELECT * FROM users WHERE email GLOB '*@gmail.com';

-- 示例4：查询产品名称以字母 'S' 开头，且长度为 6 的产品
SELECT * FROM products WHERE name GLOB 'S?????';

-- 示例5：查询用户名不以字母 'A' 开头的用户
SELECT * FROM users WHERE NOT username GLOB 'A*';

-- 使用位或运算符（|）查询用户角色为 'admin' 或 'editor' 的用户
SELECT * FROM users WHERE role = ('admin' | 'editor');

-- 使用位与运算符（&）查询用户角色为 'admin' 且年龄大于等于 30 岁的用户
SELECT * FROM users WHERE role = 'admin' & age >= 30;

-- 使用左移运算符（<<）查询订单数量扩大两倍的订单
SELECT * FROM orders WHERE quantity << 1;

-- 使用右移运算符（>>）查询订单数量缩小一半的订单
SELECT * FROM orders WHERE quantity >> 1;

-- 使用位取反运算符（~）查询用户年龄取反后的用户
SELECT * FROM users WHERE age = ~age;

-- 使用位运算函数进行位操作
SELECT 
    username, 
    permissions, 
    permissions & 1 AS can_read, 
    permissions & 2 AS can_write, 
    permissions & 4 AS can_delete 
FROM 
    users;

-- 将用户名转换为小写并选择匹配的用户
SELECT * FROM users WHERE LOWER(username) = 'john_doe';

-- 将产品名称转换为大写并按名称排序
SELECT * FROM products ORDER BY UPPER(name);

-- 将订单总额转换为大写并选择大于特定值的订单
SELECT * FROM orders WHERE UPPER(total) > 1000.00;

-- 将用户名和邮箱地址都转换为小写并插入新用户
INSERT INTO users (username, password, email) VALUES (LOWER('New_User'), 'newpassword', LOWER('new_user@example.com'));

-- 将产品名称转换为大写并更新产品描述
UPDATE products SET description = 'Updated description' WHERE UPPER(name) = 'LAPTOP';

-- 将订单总额转换为小写并计算总金额
SELECT SUM(LOWER(total)) FROM orders;

-- 在users表和orders表之间进行内连接，使用user_id列进行连接
SELECT * FROM users INNER JOIN orders USING (user_id);

-- 在orders表和order_items表之间进行内连接，使用id列进行连接
SELECT * FROM orders INNER JOIN order_items USING (id);

-- 在products表和order_items表之间进行内连接，使用product_id列进行连接
SELECT * FROM products INNER JOIN order_items USING (product_id);

-- 在users表和orders表之间进行左连接，使用user_id列进行连接
SELECT * FROM users LEFT JOIN orders USING (user_id);

-- 在orders表和order_items表之间进行左连接，使用id列进行连接
SELECT * FROM orders LEFT JOIN order_items USING (id);

-- 在products表和order_items表之间进行左连接，使用product_id列进行连接
SELECT * FROM products LEFT JOIN order_items USING (product_id);

-- 在users表和orders表之间进行右连接，使用user_id列进行连接
SELECT * FROM users RIGHT JOIN orders USING (user_id);

-- 在orders表和order_items表之间进行右连接，使用id列进行连接
SELECT * FROM orders RIGHT JOIN order_items USING (id);

-- 在products表和order_items表之间进行右连接，使用product_id列进行连接
SELECT * FROM products RIGHT JOIN order_items USING (product_id);

-- 使用CASE语句进行条件查询
SELECT 
    username, 
    email, 
    CASE 
        WHEN email LIKE '%@example.com' THEN 'Internal'
        ELSE 'External'
    END AS email_type 
FROM 
    users;

-- 使用CASE语句进行分组和聚合
SELECT 
    dept_id, 
    SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_salary_count,
    SUM(CASE WHEN salary <= 50000 THEN 1 ELSE 0 END) AS low_salary_count 
FROM 
    employees 
GROUP BY 
    dept_id;

-- 简单CTE
WITH EmployeeSales AS (
    SELECT 
        e.id, 
        e.name, 
        SUM(o.total) AS total_sales 
    FROM 
        employees AS e
    JOIN 
        orders AS o ON e.id = o.user_id 
    GROUP BY 
        e.id
)
SELECT * FROM EmployeeSales;

-- 递归CTE
WITH RECURSIVE Numbers AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1 FROM Numbers WHERE number < 10
)
SELECT number FROM Numbers;

-- 查询前5条用户记录
SELECT * FROM users LIMIT 5;

-- 查询第6到第10条用户记录
SELECT * FROM users LIMIT 5 OFFSET 5;

-- 假设已经创建并填充了以下表: users, products, orders, order_items

-- 插入一些测试数据
INSERT INTO users (username, password, email) VALUES 
('alice', 'password123', 'alice@example.com'),
('bob', 'password123', 'bob@example.com'),
('charlie', 'password123', 'charlie@example.com'),
('dave', 'password123', 'dave@example.com'),
('eve', 'password123', 'eve@example.com');

INSERT INTO products (name, description, price, stock) VALUES 
('Laptop', 'A high-end gaming laptop', 1500.00, 10),
('Smartphone', 'Latest model smartphone', 800.00, 20),
('Tablet', '10-inch tablet', 300.00, 15),
('Headphones', 'Noise-cancelling headphones', 150.00, 30),
('Monitor', '4K UHD monitor', 400.00, 5);

-- 基础的LIMIT测试
-- 选择前两个用户
SELECT * FROM users LIMIT 2;

-- 带OFFSET的LIMIT测试
-- 跳过前两个用户，选择接下来的两个用户
SELECT * FROM users LIMIT 2 OFFSET 2;

-- 在products表上进行LIMIT和OFFSET测试
-- 选择前两个产品
SELECT * FROM products LIMIT 2;

-- 跳过前两个产品，选择接下来的三个产品
SELECT * FROM products LIMIT 3 OFFSET 2;

-- 综合查询测试，包括联接、排序、LIMIT和OFFSET
-- 获取订单和用户信息，按订单时间排序，选择前三个订单
SELECT 
    orders.id AS order_id, 
    users.username, 
    orders.total, 
    orders.created_at 
FROM 
    orders 
JOIN 
    users ON orders.user_id = users.id 
ORDER BY 
    orders.created_at DESC 
LIMIT 3;

-- 获取订单和用户信息，按订单时间排序，跳过第一个订单，选择接下来的两个订单
SELECT 
    orders.id AS order_id, 
    users.username, 
    orders.total, 
    orders.created_at 
FROM 
    orders 
JOIN 
    users ON orders.user_id = users.id 
ORDER BY 
    orders.created_at DESC 
LIMIT 2 OFFSET 1;

-- 在复杂联接查询中使用LIMIT和OFFSET
-- 获取用户、订单及订单项目的详细信息，按订单总额排序，选择前两个订单
SELECT 
    orders.id AS order_id, 
    users.username, 
    products.name AS product_name, 
    order_items.quantity, 
    order_items.price 
FROM 
    orders 
JOIN 
    users ON orders.user_id = users.id 
JOIN 
    order_items ON orders.id = order_items.order_id 
JOIN 
    products ON order_items.product_id = products.id 
ORDER BY 
    orders.total DESC 
LIMIT 2;

-- 在复杂联接查询中使用LIMIT和OFFSET
-- 获取用户、订单及订单项目的详细信息，按订单总额排序，跳过第一个订单，选择接下来的两个订单
SELECT 
    orders.id AS order_id, 
    users.username, 
    products.name AS product_name, 
    order_items.quantity, 
    order_items.price 
FROM 
    orders 
JOIN 
    users ON orders.user_id = users.id 
JOIN 
    order_items ON orders.id = order_items.order_id 
JOIN 
    products ON order_items.product_id = products.id 
ORDER BY 
    orders.total DESC 
LIMIT 2 OFFSET 1;

-- 使用ROLLUP进行递归聚合
SELECT 
    dept_id, 
    user_id, 
    COUNT(*) AS order_count 
FROM 
    orders 
GROUP BY 
    ROLLUP (dept_id, user_id);

-- 使用CUBE进行交叉聚合
SELECT 
    dept_id, 
    user_id, 
    COUNT(*) AS order_count 
FROM 
    orders 
GROUP BY 
    CUBE (dept_id, user_id);

-- 使用Pivot进行转置
SELECT * FROM (
    SELECT user_id, product_id, quantity FROM order_items
) 
PIVOT (
    SUM(quantity) AS total_quantity 
    FOR product_id IN ([1], [2], [3])
);

-- 使用Unpivot进行逆转置
SELECT user_id, product_id, total_quantity 
FROM (
    SELECT * FROM order_items
) AS SourceTable
UNPIVOT (
    total_quantity FOR product_id IN ([1], [2], [3])
) AS UnpivotTable;

-- 使用UNPIVOT函数进行逆转置操作
SELECT 
    user_id,
    MAX(CASE WHEN product_id = 1 THEN quantity ELSE NULL END) AS product_1_quantity,
    MAX(CASE WHEN product_id = 2 THEN quantity ELSE NULL END) AS product_2_quantity,
    MAX(CASE WHEN product_id = 3 THEN quantity ELSE NULL END) AS product_3_quantity
FROM 
    order_items
GROUP BY 
    user_id;

-- 使用全文搜索进行复杂查询
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('database' IN BOOLEAN MODE);

-- 使用WITH LOCKED进行行级锁定
SELECT * FROM users WHERE id = 1 WITH LOCKED;

-- 使用EXISTS进行相关子查询
SELECT username FROM users WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);

-- 使用NOT EXISTS进行相关子查询
SELECT username FROM users WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id);

-- 使用ANY子查询进行条件判断
SELECT username FROM users WHERE id = ANY (SELECT user_id FROM orders WHERE total > 1000);

-- 使用ALL子查询进行条件判断
SELECT username FROM users WHERE id = ALL (SELECT user_id FROM orders WHERE total > 1000);

-- 使用自连接进行复杂的关联查询
SELECT 
    e1.name AS employee_name, 
    e2.name AS manager_name 
FROM 
    employees AS e1 
JOIN 
    employees AS e2 ON e1.manager_id = e2.id;

-- 使用GROUP_CONCAT进行字符串聚合
SELECT 
    dept_id, 
    GROUP_CONCAT(username) AS employee_list 
FROM 
    employees 
GROUP BY 
    dept_id;

-- 使用XMLAGG进行XML数据聚合
SELECT 
    dept_id, 
    XMLAGG(XMLELEMENT(NAME "employee", username)) AS employee_list 
FROM 
    employees 
GROUP BY 
    dept_id;

-- 使用FOR UPDATE进行行级锁定
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 使用RETURNING子句返回受影响的行
INSERT INTO users (username, email) VALUES ('test_user', 'test@example.com') RETURNING *;

-- 使用自然连接进行表连接
SELECT 
    orders.id, 
    users.username, 
    products.name 
FROM 
    orders 
NATURAL JOIN 
    users 
NATURAL JOIN 
    order_items 
NATURAL JOIN 
    products;

-- 使用CASE表达式进行条件分支
SELECT 
    username, 
    CASE 
        WHEN salary >= 50000 THEN 'High'
        ELSE 'Low'
    END AS salary_level 
FROM 
    employees;
    
-- 使用CAST函数进行数据类型转换
SELECT 
    username,
    CAST(age AS TEXT) AS age_text
FROM 
    users;

-- 使用COALESCE函数处理空值
SELECT 
    username, 
    COALESCE(email, 'No email') AS email_address 
FROM 
    users;

-- 使用GREATEST和LEAST函数查找最大和最小值
SELECT 
    GREATEST(salary1, salary2, salary3) AS max_salary, 
    LEAST(salary1, salary2, salary3) AS min_salary 
FROM 
    salaries;

-- 使用HAVING子句进行分组后的过滤
SELECT 
    dept_id, 
    COUNT(*) AS employee_count 
FROM 
    employees 
GROUP BY 
    dept_id 
HAVING 
    COUNT(*) > 5;

-- 使用ROW_NUMBER函数进行分组排序
SELECT 
    username, 
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank 
FROM 
    employees;

-- 使用RANK和DENSE_RANK函数进行排名
SELECT 
    username, 
    RANK() OVER (ORDER BY salary DESC) AS rank, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank 
FROM 
    employees;

-- 使用NTILE函数进行分组划分
SELECT 
    username, 
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile 
FROM 
    employees;

-- 使用GROUPING函数判断是否进行了分组
SELECT 
    dept_id, 
    GROUPING(dept_id) AS is_grouped, 
    COUNT(*) AS employee_count 
FROM 
    employees 
GROUP BY 
    dept_id 
WITH ROLLUP;

-- 使用GROUPING SETS进行多重分组
SELECT 
    dept_id, 
    user_id, 
    COUNT(*) AS order_count 
FROM 
    orders 
GROUP BY 
    GROUPING SETS ((dept_id), (user_id), (dept_id, user_id));

-- 使用JSON_EXTRACT函数从JSON数据中提取信息
SELECT 
    JSON_EXTRACT(details, '$.name') AS product_name, 
    JSON_EXTRACT(details, '$.price') AS product_price 
FROM 
    products;

-- 使用JSON_ARRAYAGG进行JSON数据聚合
SELECT 
    dept_id, 
    JSON_ARRAYAGG(username) AS employee_list 
FROM 
    employees 
GROUP BY 
    dept_id;

-- 使用JSON_VALUE函数从JSON数据中提取信息
SELECT 
    JSON_VALUE(details, '$.name') AS product_name, 
    JSON_VALUE(details, '$.price') AS product_price 
FROM 
    products;

-- 使用XML函数从XML数据中提取信息
SELECT 
    XMLQUERY('$.name' PASSING details RETURNING CONTENT) AS product_name, 
    XMLQUERY('$.price' PASSING details RETURNING CONTENT) AS product_price 
FROM 
    products;

-- 使用XMLQUERY函数从XML数据中提取信息
SELECT 
    XMLQUERY('$.name' PASSING details RETURNING CONTENT) AS product_name, 
    XMLQUERY('$.price' PASSING details RETURNING CONTENT) AS product_price 
FROM 
    products;

-- 使用PI函数获取圆周率值
SELECT PI();

-- 使用RAND函数生成随机数
SELECT 
    username, 
    RAND() AS random_number 
FROM 
    users;

-- 使用UUID函数生成唯一标识符
SELECT 
    username, 
    UUID() AS unique_id 
FROM 
    users;

-- 使用CONCAT函数进行字符串连接
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name 
FROM 
    employees;

-- 使用EXTRACT函数提取日期时间信息
SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day
FROM 
    orders;

-- 使用STRTIME函数格式化日期时间字符串
SELECT 
    username, 
    STRFTIME('%Y-%m-%d', created_at) AS formatted_created_at
FROM 
    users;

-- 使用CURRENT_TIMESTAMP获取当前时间戳
SELECT 
    username,
    last_login,
    CURRENT_TIMESTAMP AS current_timestamp
FROM 
    users;

-- 使用NULLIF函数处理特定值
SELECT 
    username, 
    NULLIF(email, 'example@example.com') AS email_address 
FROM 
    users;

-- 测试 IFNULL 函数是否正确处理空值

-- 插入一些测试数据
INSERT INTO users (username, password, email) VALUES ('user1', NULL, 'user1@example.com');
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', NULL);
INSERT INTO users (username, password, email) VALUES ('user3', 'password3', 'user3@example.com');

-- 测试 IFNULL 函数对于空密码的情况
SELECT username, IFNULL(password, 'No password set') AS password FROM users;

-- 测试 IFNULL 函数对于空邮箱的情况
SELECT username, IFNULL(email, 'No email provided') AS email FROM users;

-- 测试 IFNULL 函数对于非空值的情况
SELECT username, IFNULL(email, 'No email provided') AS email FROM users WHERE username = 'user3';

-- 测试 IFNULL 函数与其他函数一起使用
SELECT username, IFNULL(UPPER(email), 'No email provided') AS email FROM users;

-- 清理测试数据
DELETE FROM users;

-- 使用BIT_AND和BIT_OR进行位运算
SELECT 
    username, 
    BIT_AND(permissions) AS combined_permissions,
    BIT_OR(permissions) AS combined_permissions
FROM 
    users;

-- 使用HASH函数进行哈希计算
SELECT 
    username, 
    HASH(password, 'sha256') AS hashed_password 
FROM 
    users;

-- 使用GENERATE_SERIES生成序列
SELECT 
    n 
FROM 
    GENERATE_SERIES(1, 10) AS n;

-- 使用RECURSIVE递归查询
WITH RECURSIVE DepartmentTree AS (
    SELECT id, name, parent_id FROM departments WHERE id = 1
    UNION ALL
    SELECT d.id, d.name, d.parent_id FROM departments d JOIN DepartmentTree dt ON d.parent_id = dt.id
)
SELECT * FROM DepartmentTree;

-- 创建索引以便于测试
CREATE INDEX idx_user_username ON users (username);
CREATE INDEX idx_product_name ON products (name);

-- 分析表以生成统计信息
ANALYZE;

-- 以下是一些查询，我们将观察它们是否利用了索引和统计信息

-- 选择查询，期望利用 username 索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE username = 'john_doe';

-- 范围查询，期望利用 price 索引
EXPLAIN QUERY PLAN SELECT * FROM products WHERE price BETWEEN 1000 AND 2000;

-- 多表连接查询，期望使用适当的索引和连接策略
EXPLAIN QUERY PLAN SELECT * FROM orders 
JOIN users ON orders.user_id = users.id 
JOIN order_items ON orders.id = order_items.order_id 
JOIN products ON order_items.product_id = products.id;

-- 分组查询，期望利用合适的索引和聚合函数
EXPLAIN QUERY PLAN SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent FROM orders GROUP BY user_id;

-- 尝试附加数据库
ATTACH DATABASE 'test_db.db' AS test_db;

-- 检查是否成功附加数据库
SELECT name FROM sqlite_master WHERE type='table' AND name='users' UNION ALL SELECT name FROM test_db.sqlite_master WHERE type='table';

-- 开始一个事务
BEGIN TRANSACTION;

-- 在事务中执行插入操作
INSERT INTO users (username, password, email) VALUES ('test_user', 'test_password', 'test@example.com');

-- 在事务中执行更新操作
UPDATE users SET email = 'new_test@example.com' WHERE username = 'test_user';

-- 在事务中执行删除操作
DELETE FROM users WHERE username = 'test_user';

-- 提交事务
COMMIT;

-- 检查是否成功提交事务，检查用户表是否有'test_user'，如果有，则回滚事务
BEGIN TRANSACTION;

SELECT * FROM users WHERE username = 'test_user';

-- 如果用户存在，则回滚事务
ROLLBACK;

-- 测试注释

name GLOB '*helium*'
glob('*helium*',name)

name LIKE '%neon%' 
like('%neon%',name)

scss复制代码hex(randomblob(16))

lower(hex(randomblob(16)))

-- 测试abs(X)函数是否正确
-- 创建一个测试数据
INSERT INTO test_table (id, name) VALUES (1, 'Negative Number'), (2, 'Positive Number'), (3, 'Zero');

-- 在Negative Number列中插入一个负数
UPDATE test_table SET id = -1 WHERE name = 'Negative Number';

-- 在Positive Number列中插入一个正数
UPDATE test_table SET id = 1 WHERE name = 'Positive Number';

-- 查询测试结果
SELECT 
    name,
    id,
    ABS(id) AS absolute_value
FROM 
    test_table;

-- 创建测试数据库
CREATE TABLE test_changes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

-- 插入初始数据
INSERT INTO test_changes (name) VALUES ('Row 1'), ('Row 2'), ('Row 3');

-- 测试INSERT语句
INSERT INTO test_changes (name) VALUES ('New Row');
SELECT changes(); -- 应该返回1，因为只有一行被插入

-- 测试UPDATE语句
UPDATE test_changes SET name = 'Updated Row' WHERE id = 2;
SELECT changes(); -- 应该返回1，因为只有一行被更新

-- 测试DELETE语句
DELETE FROM test_changes WHERE id = 3;
SELECT changes(); -- 应该返回1，因为只有一行被删除

-- 测试没有影响行的语句
SELECT * FROM test_changes WHERE id = 10;
SELECT changes(); -- 应该返回0，因为上一条SELECT语句没有影响行数

-- 删除测试表
DROP TABLE test_changes;

-- 测试char(X1,X2,...,XN)函数是否正确
-- 该函数将ASCII码值转换为字符

-- 测试单个参数
SELECT char(65); -- 应该返回大写字母'A'

-- 测试多个参数
SELECT char(72, 101, 108, 108, 111); -- 应该返回字符串'Hello'

-- 测试混合参数
SELECT char(72, 101, 108, 108, 111, 32, 87, 111, 114, 108, 100); -- 应该返回字符串'Hello World'

-- 测试空参数
SELECT char(); -- 应该返回空字符串

-- 创建一个测试数据
INSERT INTO products (name, description, price, stock) VALUES 
    ('Product A', NULL, 100.00, 10),
    ('Product B', 'Description B', NULL, 5),
    ('Product C', 'Description C', 150.00, 20),
    ('Product D', NULL, NULL, 0);

-- 使用COALESCE函数查询价格和描述，如果价格或描述为空，则返回默认值
SELECT 
    name,
    COALESCE(description, 'No description available') AS description,
    COALESCE(price, 0.00) AS price
FROM 
    products;

-- 测试concat函数的语法是否正确

-- 创建一个测试表
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);

-- 向测试表中插入一些数据
INSERT INTO test_table (first_name, last_name) VALUES ('John', 'Doe');
INSERT INTO test_table (first_name, last_name) VALUES ('Jane', 'Doe');
INSERT INTO test_table (first_name, last_name) VALUES ('Alice', 'Smith');

-- 使用concat函数将first_name和last_name拼接成full_name
SELECT id, concat(first_name, ' ', last_name) AS full_name FROM test_table;

-- 在用户表中插入几条记录
INSERT INTO users (username, password, email) VALUES 
('user1', 'password1', 'user1@example.com'),
('user2', 'password2', 'user2@example.com'),
('user3', 'password3', 'user3@example.com');

-- 测试concat_ws()函数
-- 将用户名和邮箱用逗号分隔合并成一个字符串
SELECT concat_ws(',', username, email) AS user_info FROM users;

-- 将用户名、邮箱和密码用“-”分隔合并成一个字符串
SELECT concat_ws('-', username, email, password) AS user_credentials FROM users;

-- 测试 FORMAT 函数是否正确格式化字符串
SELECT FORMAT('Hello, %s!', 'world'); -- 预期结果: 'Hello, world!'

-- 测试带有多个参数的格式化
SELECT FORMAT('Product: %s, Price: %.2f', 'Laptop', 1500.50); -- 预期结果: 'Product: Laptop, Price: 1500.50'

-- 测试使用数字和字符串作为参数
SELECT FORMAT('User ID: %d, Username: %s', 1, 'john_doe'); -- 预期结果: 'User ID: 1, Username: john_doe'

-- 测试日期格式化
SELECT FORMAT('Today is %Y-%m-%d', DATE('now')); -- 预期结果: 'Today is <当前日期>'

-- 测试无效的格式字符串（缺少参数）
SELECT FORMAT('This is a test: %s'); -- 预期结果: 报错，缺少参数

-- 测试无效的格式字符串（多余参数）
SELECT FORMAT('This is a test: %s', 'extra', 'extra2'); -- 预期结果: 'This is a test: extra'，多余参数被忽略

-- 测试 glob(X,Y) 函数的语法和功能

-- 创建一个用于测试的用户表
CREATE TABLE test_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE
);

-- 插入一些测试数据
INSERT INTO test_users (username) VALUES ('john_doe');
INSERT INTO test_users (username) VALUES ('jane_smith');
INSERT INTO test_users (username) VALUES ('james_brown');
INSERT INTO test_users (username) VALUES ('bob_dylan');
INSERT INTO test_users (username) VALUES ('alice_wonderland');

-- 测试1: 使用 glob 进行模式匹配，查找以 'j' 开头的用户名
SELECT * FROM test_users WHERE username GLOB 'j*';

-- 预期结果: 返回 'john_doe', 'jane_smith', 'james_brown'

-- 测试2: 使用 glob 进行模式匹配，查找以 'doe' 结尾的用户名
SELECT * FROM test_users WHERE username GLOB '*doe';

-- 预期结果: 返回 'john_doe'

-- 测试3: 使用 glob 进行模式匹配，查找包含 'w' 的用户名
SELECT * FROM test_users WHERE username GLOB '*w*';

-- 预期结果: 返回 'james_brown', 'bob_dylan', 'alice_wonderland'

-- 测试4: 使用 glob 进行模式匹配，查找以 'j' 开头和 'e' 结尾的用户名
SELECT * FROM test_users WHERE username GLOB 'j*e';

-- 预期结果: 返回 'jane_smith'

-- 测试5: 使用 glob 进行模式匹配，查找只有两个字符的用户名
SELECT * FROM test_users WHERE username GLOB '__';

-- 预期结果: 返回 'jw'

-- 清理测试数据
DROP TABLE IF EXISTS test_users;

-- 测试hex(X)函数是否正确
-- 在这个测试中，我们将对用户表中的用户名进行转换并进行比较

-- 创建一个测试用户
INSERT INTO users (username, password, email) VALUES ('test_user', 'testpassword', 'test@example.com');

-- 使用hex(X)函数将用户名转换为十六进制
SELECT hex(username) AS hex_username FROM users WHERE username = 'test_user';

-- 测试 IFNULL(X, Y) 函数是否能正确处理 NULL 值

-- 插入一条记录到 users 表，其中 email 字段为空
INSERT INTO users (username, password, email) VALUES ('jane_doe', 'securepassword', NULL);

-- 使用 IFNULL 函数来选择用户名和电子邮件地址，如果电子邮件地址为 NULL，则替换为字符串 'Email not provided'
SELECT 
    username, 
    IFNULL(email, 'Email not provided') AS email 
FROM 
    users;

-- 插入一条记录到 users 表，其中 email 字段不为空
INSERT INTO users (username, password, email) VALUES ('bob_smith', 'password123', 'bob@example.com');

-- 使用 IFNULL 函数来选择用户名和电子邮件地址，如果电子邮件地址为 NULL，则替换为字符串 'Email not provided'
SELECT 
    username, 
    IFNULL(email, 'Email not provided') AS email 
FROM 
    users;

-- 测试IIF函数语法是否正确
-- 创建一个临时测试表
CREATE TEMPORARY TABLE test_table (
    id INTEGER PRIMARY KEY,
    condition INTEGER NOT NULL,
    value_true TEXT NOT NULL,
    value_false TEXT NOT NULL
);

-- 向测试表中插入一些测试数据
INSERT INTO test_table (condition, value_true, value_false) VALUES 
    (1, 'True Value', 'False Value'),
    (0, 'True Value', 'False Value');

-- 使用IIF函数进行测试
-- 当条件为真时返回 value_true，否则返回 value_false
SELECT 
    condition, 
    IIF(condition = 1, value_true, value_false) AS result 
FROM 
    test_table;

-- 测试INSTR(X, Y)函数是否正确工作

-- 创建一个测试表
CREATE TABLE test_strings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    main_string TEXT NOT NULL,
    sub_string TEXT NOT NULL
);

-- 插入测试数据
INSERT INTO test_strings (main_string, sub_string) VALUES ('hello world', 'hello');
INSERT INTO test_strings (main_string, sub_string) VALUES ('hello world', 'world');
INSERT INTO test_strings (main_string, sub_string) VALUES ('hello world', 'foo');

-- 测试INSTR函数返回正确的位置
SELECT 
    main_string, 
    sub_string, 
    INSTR(main_string, sub_string) AS position 
FROM 
    test_strings;

-- 插入一条用户数据
INSERT INTO users (username, password, email) VALUES ('test_user', 'test_password', 'test@example.com');

-- 获取最后插入行的ID
SELECT last_insert_rowid() AS last_insert_id;

-- 验证最后插入行的ID是否正确
SELECT id FROM users WHERE id = last_insert_rowid();

-- 测试长度为0的字符串
SELECT length('') AS length_empty_string;

-- 测试长度为非零的字符串
SELECT length('Hello') AS length_hello_string;

-- 测试包含空格的字符串的长度
SELECT length('Hello World') AS length_hello_world_string;

-- 测试包含特殊字符的字符串的长度
SELECT length('特殊字符') AS length_special_characters_string;

-- 测试NULL值的长度
SELECT length(NULL) AS length_null_value;

-- 测试 like(X,Y) 语法
SELECT * FROM users WHERE username LIKE 'joh%'; -- 匹配以 'joh' 开头的所有用户名

-- 测试 like(X,Y,Z) 语法
SELECT * FROM products WHERE name LIKE '%laptop%'; -- 匹配名称中包含 'laptop' 的所有产品

-- 假设我们想测试 likelihood(X,Y) 语法，表示在事件 X 发生的情况下事件 Y 也发生的概率

-- 例如，我们想测试在用户下订单的情况下，订单中包含产品的概率

-- 首先，我们需要计算事件 X 和事件 Y 同时发生的次数，然后除以事件 X 发生的总次数，即 likelihood(X,Y) = count(X and Y) / count(X)

-- 计算事件 X 发生的总次数（用户下订单的次数）
SELECT COUNT(*) AS x_count FROM orders;

-- 计算事件 X 和事件 Y 同时发生的次数（用户下订单并且订单中包含产品的次数）
SELECT COUNT(*) AS xy_count
FROM orders
JOIN order_items ON orders.id = order_items.order_id;

-- 最终计算 likelihood(X,Y)
SELECT (CAST((SELECT COUNT(*) FROM orders) AS REAL) / (SELECT COUNT(*) FROM orders)) AS likelihood_xy;

-- 在用户表中插入一些测试数据
INSERT INTO users (username, password, email) VALUES
    ('user1', 'password123', 'user1@example.com'),
    ('user2', 'P@ssw0rd', 'user2@example.com'),
    ('user3', 'securepassword', 'user3@example.com');

-- 查询密码强度，使用LIKELY(X)函数判断
SELECT 
    username,
    password,
    LIKELY(password LIKE '%[0-9]%') AS contains_number,
    LIKELY(password LIKE '%[A-Z]%') AS contains_uppercase,
    LIKELY(password LIKE '%[a-z]%') AS contains_lowercase,
    LIKELY(LENGTH(password) >= 8) AS is_long_enough
FROM 
    users;

-- 测试 load_extension(X)：加载扩展模块
-- 假设我们有一个名为 "my_extension.so" 的扩展模块文件

-- 尝试加载扩展模块
SELECT load_extension('my_extension.so');

-- 如果加载成功，则将返回 1，如果失败则返回 0
-- 请注意：在SQLite中，默认情况下，load_extension() 函数是被禁用的。要启用它，必须在编译SQLite时启用 SQLITE_ENABLE_LOAD_EXTENSION 宏，并且在运行时设置 allow_load_extension pragma。

-- 测试 load_extension(X, Y)：加载扩展模块并指定入口点函数名
-- 假设我们有一个名为 "my_extension2.so" 的扩展模块文件，其中包含一个名为 "custom_function" 的自定义函数

-- 尝试加载扩展模块并指定入口点函数名
SELECT load_extension('my_extension2.so', 'custom_function');

-- 如果加载成功并且自定义函数存在，则不会有任何输出
-- 如果加载成功但自定义函数不存在，则会出现错误消息，指示找不到指定的函数
-- 如果加载失败，则会出现错误消息

-- 在测试前，请确保 "my_extension.so" 和 "my_extension2.so" 这两个文件存在，并且具有相应的扩展模块和自定义函数。

-- 测试 lower(X) 函数
-- 在这个示例中，我们将对不同列使用 lower(X) 函数，以测试它的功能和正确性

-- 测试 lower(X) 在 SELECT 查询中的应用
SELECT 
    lower(username) AS lowercase_username,
    lower(email) AS lowercase_email 
FROM 
    users;

-- 测试 lower(X) 在 INSERT 语句中的应用
-- 在插入数据时，将 username 和 email 转换为小写
INSERT INTO users (username, password, email) VALUES (lower('John_Doe'), 'securepassword', lower('john@example.com'));

-- 测试 lower(X) 在 UPDATE 语句中的应用
-- 在更新数据时，将 username 和 email 转换为小写
UPDATE users SET username = lower(username), email = lower(email) WHERE id = 1;

-- 测试 lower(X) 在 WHERE 条件中的应用
-- 在 WHERE 条件中使用 lower(X) 函数来进行大小写不敏感的匹配
SELECT * FROM users WHERE lower(username) = lower('John_Doe');

-- 测试 lower(X) 在 ORDER BY 子句中的应用
-- 在 ORDER BY 子句中使用 lower(X) 函数进行大小写不敏感的排序
SELECT * FROM users ORDER BY lower(username);

-- 测试 ltrim(X)

-- 创建一个测试表
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    text_column TEXT
);

-- 插入测试数据
INSERT INTO test_table (text_column) VALUES 
('   Hello'),
('   World'),
('   SQLite'),
('   is'),
('   awesome   ');

-- 使用 ltrim(X) 函数删除开头的空格
SELECT text_column, ltrim(text_column) AS trimmed_text FROM test_table;

-- 结果应该是去除了开头空格的字符串

-- 测试 ltrim(X,Y)

-- 创建一个包含特殊字符的测试表
CREATE TABLE special_chars (
    id INTEGER PRIMARY KEY,
    text_column TEXT
);

-- 插入测试数据
INSERT INTO special_chars (text_column) VALUES 
('!@#Hello'),
('!@#World'),
('!@#SQLite'),
('!@#is'),
('!@#awesome!@#');

-- 使用 ltrim(X,Y) 函数删除开头的特殊字符
SELECT text_column, ltrim(text_column, '!@#') AS trimmed_text FROM special_chars;

-- 结果应该是去除了开头特殊字符的字符串

-- 测试 max() 和 min() 函数

-- 最大整数值
SELECT MAX(id) AS max_id FROM users;

-- 最小整数值
SELECT MIN(id) AS min_id FROM users;

-- 最大实数值
SELECT MAX(price) AS max_price FROM products;

-- 最小实数值
SELECT MIN(price) AS min_price FROM products;

-- 最大字符串值
SELECT MAX(name) AS max_name FROM products;

-- 最小字符串值
SELECT MIN(name) AS min_name FROM products;

-- 创建测试数据
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
INSERT INTO users (username, email) VALUES ('user2', NULL);
INSERT INTO users (username, email) VALUES (NULL, 'user3@example.com');
INSERT INTO users (username, email) VALUES (NULL, NULL);

-- 测试 NULLIF(X, Y) 函数

-- Case 1: X 和 Y 都为非 NULL 值，且 X ≠ Y，返回 X
SELECT NULLIF('apple', 'banana') AS result; -- 应返回 'apple'

-- Case 2: X 和 Y 都为非 NULL 值，且 X = Y，返回 NULL
SELECT NULLIF('apple', 'apple') AS result; -- 应返回 NULL

-- Case 3: X 和 Y 都为 NULL，返回 NULL
SELECT NULLIF(NULL, NULL) AS result; -- 应返回 NULL

-- Case 4: X 为非 NULL，Y 为 NULL，返回 X
SELECT NULLIF('apple', NULL) AS result; -- 应返回 'apple'

-- Case 5: X 为 NULL，Y 为非 NULL，返回 NULL
SELECT NULLIF(NULL, 'banana') AS result; -- 应返回 NULL

-- Case 6: X 和 Y 均为列引用，测试在实际数据中的应用
SELECT username, NULLIF(email, '') AS cleaned_email FROM users;
-- 应返回：
-- username | cleaned_email
-- user1    | user1@example.com
-- user2    | NULL
-- NULL     | user3@example.com
-- NULL     | NULL

-- 测试 octet_length(X) 函数的使用情况

-- 创建测试数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', 'user2@example.com');
INSERT INTO products (name, description, price, stock) VALUES ('Product 1', 'Description 1', 100.00, 10);
INSERT INTO products (name, description, price, stock) VALUES ('Product 2', 'Description 2', 200.00, 20);

-- 测试 octet_length(X) 函数对字符串的计算
SELECT 
    username,
    octet_length(username) AS username_length,
    email,
    octet_length(email) AS email_length
FROM 
    users;

-- 期望输出：
-- username | username_length | email             | email_length
-- ---------|-----------------|-------------------|--------------
-- user1    | 5               | user1@example.com| 16
-- user2    | 5               | user2@example.com| 16

-- 测试 octet_length(X) 函数对列的计算
SELECT 
    name,
    octet_length(name) AS name_length,
    description,
    octet_length(description) AS description_length
FROM 
    products;

-- 期望输出：
-- name      | name_length | description  | description_length
-- ----------|-------------|--------------|-------------------
-- Product 1 | 9           | Description 1| 12
-- Product 2 | 9           | Description 2| 12

-- 测试printf函数的使用

-- 1. 测试基本的格式化输出
SELECT printf('Hello, %s!', 'world');

-- 2. 测试整数格式化
SELECT printf('The number is: %d', 123);

-- 3. 测试浮点数格式化
SELECT printf('The value is: %.2f', 3.14159);

-- 4. 测试多个参数
SELECT printf('Name: %s, Age: %d', 'John', 30);

-- 5. 测试使用变量
WITH vars AS (SELECT 'Alice' AS name, 25 AS age)
SELECT printf('Name: %s, Age: %d', name, age) FROM vars;

-- 测试 quote(X) 函数
-- 创建一个临时表用于测试
CREATE TEMPORARY TABLE temp_test (
    id INTEGER PRIMARY KEY,
    value TEXT
);

-- 插入一些测试数据
INSERT INTO temp_test (value) VALUES ('John');
INSERT INTO temp_test (value) VALUES ('Tom''s Pizza');
INSERT INTO temp_test (value) VALUES ('Alice "Smith"');
INSERT INTO temp_test (value) VALUES ('100');
INSERT INTO temp_test (value) VALUES ('This is a test');

-- 查询原始数据
SELECT * FROM temp_test;

-- 测试 quote(X) 函数
-- 将字符串值带引号插入到另一个表中
CREATE TEMPORARY TABLE quoted_values (
    id INTEGER PRIMARY KEY,
    quoted_value TEXT
);

-- 使用 quote(X) 函数将值带引号插入到新表中
INSERT INTO quoted_values (quoted_value) SELECT quote(value) FROM temp_test;

-- 查询带引号的值
SELECT * FROM quoted_values;

-- 清理临时表
DROP TABLE temp_test;
DROP TABLE quoted_values;

-- 测试SQLite的random()和randomblob(N)函数

-- 插入一些示例数据以供测试
INSERT INTO users (username, password, email) VALUES ('user1', 'pass1', 'user1@example.com');
INSERT INTO users (username, password, email) VALUES ('user2', 'pass2', 'user2@example.com');
INSERT INTO users (username, password, email) VALUES ('user3', 'pass3', 'user3@example.com');

-- 使用random()函数生成随机数
SELECT random() AS random_number;

-- 使用random()函数生成指定范围的随机数（0到1之间）
SELECT random() * 100 AS random_between_0_and_100;

-- 使用randomblob(N)函数生成长度为N的随机字节串
SELECT randomblob(10) AS random_bytes;

-- 使用randomblob(N)函数生成随机的用户名
SELECT 
    substr(hex(randomblob(4)), 1, 8) AS random_username 
FROM 
    users 
LIMIT 1;

-- 使用randomblob(N)函数生成随机密码
SELECT 
    substr(hex(randomblob(4)), 1, 8) AS random_password 
FROM 
    users 
LIMIT 1;

-- 使用randomblob(N)函数生成随机邮箱
SELECT 
    substr(hex(randomblob(4)), 1, 8) || '@example.com' AS random_email 
FROM 
    users 
LIMIT 1;

-- 创建一个测试表
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    random_data BLOB,  -- 用于存储随机数据
    hex_data TEXT      -- 用于存储随机数据的十六进制表示
);

-- 插入一条记录，同时使用hex(randomblob(16))生成随机数据，并存储其十六进制表示
INSERT INTO test_table (random_data, hex_data) VALUES (randomblob(16), hex(randomblob(16)));

-- 查询表中的数据，验证随机数据是否被正确转换为十六进制表示
SELECT * FROM test_table;

-- 更新表中的记录，将随机数据字段更新为新的随机值，并更新对应的十六进制表示
UPDATE test_table SET random_data = randomblob(16), hex_data = hex(random_data);

-- 再次查询表中的数据，确认更新后的数据是否正确
SELECT * FROM test_table;

-- 删除表中的所有记录
DELETE FROM test_table;

-- 重新插入一些记录，以备后续测试使用
INSERT INTO test_table (random_data, hex_data) VALUES (randomblob(16), hex(randomblob(16)));
INSERT INTO test_table (random_data, hex_data) VALUES (randomblob(16), hex(randomblob(16)));
INSERT INTO test_table (random_data, hex_data) VALUES (randomblob(16), hex(randomblob(16)));

-- 使用带条件的查询，查找特定的十六进制表示对应的记录
SELECT * FROM test_table WHERE hex_data = 'your_hex_value_here';

-- 使用子查询，查找随机数据长度大于某个值的记录
SELECT * FROM test_table WHERE LENGTH(random_data) > 8;

-- 删除表，清理测试数据
DROP TABLE test_table;

-- 创建测试表
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    random_hash TEXT
);

-- 插入随机数据
INSERT INTO test_table (random_hash) VALUES (lower(hex(randomblob(16))));
INSERT INTO test_table (random_hash) VALUES (lower(hex(randomblob(16))));
INSERT INTO test_table (random_hash) VALUES (lower(hex(randomblob(16))));

-- 查看插入的数据
SELECT * FROM test_table;

-- 在 users 表中将名为 'john_doe' 的用户的邮箱替换为 'john@example.com'
UPDATE users SET email = REPLACE(email, 'john_doe', 'john@example.com') WHERE username = 'john_doe';

-- 在 products 表中将所有产品描述中的 'high-end' 替换为 'premium'
UPDATE products SET description = REPLACE(description, 'high-end', 'premium');

-- 在 orders 表中将所有订单中的总金额中的 '$' 替换为空字符串
UPDATE orders SET total = REPLACE(total, '$', '');

-- 在 order_items 表中将所有订单项目的价格中的小数点 '.' 替换为逗号 ','
UPDATE order_items SET price = REPLACE(price, '.', ',');

-- 将 users 表中的所有用户名的首字母替换为大写字母
UPDATE users SET username = REPLACE(username, SUBSTR(username, 1, 1), UPPER(SUBSTR(username, 1, 1)));

-- 将 products 表中所有产品名称中的 'Laptop' 替换为 'Desktop'
UPDATE products SET name = REPLACE(name, 'Laptop', 'Desktop');

-- 将 orders 表中创建时间为 '2023' 年的订单的创建时间替换为当前时间
UPDATE orders SET created_at = REPLACE(created_at, '2023', CURRENT_TIMESTAMP);

-- 将 order_items 表中所有订单项目的数量中的 '1' 替换为 '10'
UPDATE order_items SET quantity = REPLACE(quantity, '1', '10');

-- 测试 round(X) 函数，对数字 X 进行四舍五入
SELECT ROUND(123.456); -- 结果应为 123
SELECT ROUND(123.567); -- 结果应为 124
SELECT ROUND(-123.456); -- 结果应为 -123
SELECT ROUND(-123.567); -- 结果应为 -124

-- 测试 round(X, Y) 函数，对数字 X 进行保留 Y 位小数的四舍五入
SELECT ROUND(123.456, 2); -- 结果应为 123.46
SELECT ROUND(123.567, 2); -- 结果应为 123.57
SELECT ROUND(-123.456, 2); -- 结果应为 -123.46
SELECT ROUND(-123.567, 2); -- 结果应为 -123.57

-- 测试 round(X, Y) 函数，当 Y 为负数时，对 X 的 Y 位数左侧进行四舍五入
SELECT ROUND(123.456, -1); -- 结果应为 120
SELECT ROUND(123.567, -1); -- 结果应为 120
SELECT ROUND(-123.456, -1); -- 结果应为 -120
SELECT ROUND(-123.567, -1); -- 结果应为 -120

-- 测试 round(X, Y) 函数，当 Y 为零时，对 X 进行四舍五入到整数
SELECT ROUND(123.456, 0); -- 结果应为 123
SELECT ROUND(123.567, 0); -- 结果应为 124
SELECT ROUND(-123.456, 0); -- 结果应为 -123
SELECT ROUND(-123.567, 0); -- 结果应为 -124

-- 测试 rtrim(X) 函数
-- 在 users 表的 username 列上测试 rtrim(X)
SELECT username, rtrim(username) AS trimmed_username
FROM users;

-- 测试 rtrim(X,Y) 函数
-- 在 products 表的 description 列上测试 rtrim(X,Y)，Y 是指定的要删除的字符
SELECT description, rtrim(description, '.') AS trimmed_description
FROM products;

-- 添加包含空格的测试数据
INSERT INTO users (username, password, email) VALUES ('  user1  ', 'password1', 'user1@example.com');
INSERT INTO products (name, description, price, stock) VALUES ('Product1', 'Description with trailing spaces.  ', 100.00, 5);

-- 再次测试 rtrim(X) 函数以处理空格
SELECT username, rtrim(username) AS trimmed_username
FROM users;

-- 再次测试 rtrim(X,Y) 函数以处理空格
SELECT description, rtrim(description, ' ') AS trimmed_description
FROM products;

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

-- 插入测试数据
INSERT INTO sign_test (value) VALUES (10);   -- 正数
INSERT INTO sign_test (value) VALUES (-5);   -- 负数
INSERT INTO sign_test (value) VALUES (0);    -- 零
INSERT INTO sign_test (value) VALUES (NULL); -- 空值
INSERT INTO sign_test (value) VALUES (3.14); -- 正浮点数
INSERT INTO sign_test (value) VALUES (-2.71);-- 负浮点数

-- 使用 sign(X) 函数进行查询并显示结果
SELECT 
    id, 
    value, 
    sign(value) AS sign_value 
FROM 
    sign_test;

-- 插入测试数据
INSERT INTO users (username, password, email) VALUES 
('john_doe', 'securepassword', 'john@example.com'),
('jane_doe', 'anotherpassword', 'jane@example.com'),
('jack_doe', 'yetanotherpassword', 'jack@example.com');

INSERT INTO products (name, description, price, stock) VALUES 
('Laptop', 'A high-end gaming laptop', 1500.00, 10),
('Lamp', 'A desk lamp', 20.00, 100),
('Lamb', 'A delicious lamb meat', 12.00, 50);

-- 测试SOUNDEX函数
-- SOUNDEX函数用于返回字符串的语音编码值，可以用于比较发音相似的字符串

-- 测试SOUNDEX用于查找发音相似的用户名
SELECT username, SOUNDEX(username) AS soundex_code
FROM users;

-- 查找发音类似于 'john_doe' 的用户名
SELECT username
FROM users
WHERE SOUNDEX(username) = SOUNDEX('john_doe');

-- 测试SOUNDEX用于查找发音相似的产品名
SELECT name, SOUNDEX(name) AS soundex_code
FROM products;

-- 查找发音类似于 'Laptop' 的产品名
SELECT name
FROM products
WHERE SOUNDEX(name) = SOUNDEX('Laptop');

-- 查找发音类似于 'Lamp' 的产品名
SELECT name
FROM products
WHERE SOUNDEX(name) = SOUNDEX('Lamp');

-- 查找发音类似于 'Lamb' 的产品名
SELECT name
FROM products
WHERE SOUNDEX(name) = SOUNDEX('Lamb');

-- 插入测试数据
INSERT INTO users (username, password, email) VALUES ('john_doe', 'securepassword', 'john@example.com');
INSERT INTO products (name, description, price, stock) VALUES ('Laptop', 'A high-end gaming laptop', 1500.00, 10);
INSERT INTO orders (user_id, total) VALUES (1, 1500.00);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 1, 1500.00);

-- 获取编译选项 (sqlite_compileoption_get)
-- 这将返回编译时定义的选项，如果存在则返回选项名，不存在则返回NULL
SELECT sqlite_compileoption_get(0); -- 获取第一个编译选项
SELECT sqlite_compileoption_get(1); -- 获取第二个编译选项
-- 可以继续增加索引直到返回NULL为止

-- 检查特定编译选项是否使用 (sqlite_compileoption_used)
-- 如果选项被使用则返回1，否则返回0
SELECT sqlite_compileoption_used('ENABLE_LOAD_EXTENSION'); -- 检查是否启用加载扩展
SELECT sqlite_compileoption_used('THREADSAFE'); -- 检查线程安全选项是否启用

-- 查询偏移量 (sqlite_offset)
-- 使用OFFSET功能进行分页查询
-- 查询订单表中的数据并获取偏移量
SELECT id, total FROM orders LIMIT 1 OFFSET 0; -- 获取第1条记录
SELECT id, total FROM orders LIMIT 1 OFFSET 1; -- 获取第2条记录

-- 注意: 由于sqlite_offset是一个虚函数，实际用例可能会不同，这里只是作为参考
-- 检查OFFSET是否正确应用，通过检查返回的行数
SELECT COUNT(*) FROM (SELECT id FROM orders LIMIT 1 OFFSET 1); -- 确认返回1行

-- 使用 sqlite_version() 获取 SQLite 版本号
-- 该函数返回当前 SQLite 库的版本号
SELECT sqlite_version() AS current_version;

-- 测试 sqlite_offset(X) 函数
-- sqlite_offset(X) 返回与 BLOB 的第一个字节相对于其所在页的偏移量
-- 该函数通常用于内部调试或高级查询优化
-- 在我们的测试环境中创建一个包含 BLOB 数据的表
CREATE TABLE blob_test (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data BLOB
);

-- 插入示例 BLOB 数据
INSERT INTO blob_test (data) VALUES (zeroblob(100)); -- 创建一个长度为100字节的BLOB数据

-- 查询 BLOB 数据的偏移量
SELECT id, sqlite_offset(data) AS blob_offset FROM blob_test;

-- 使用 sqlite_source_id() 获取 SQLite 源代码版本的唯一标识符
-- 该函数返回一个标识当前源代码版本的字符串
SELECT sqlite_source_id() AS source_id;

-- 清理测试数据
DROP TABLE blob_test;

-- 创建测试表
CREATE TABLE string_test (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    test_string TEXT NOT NULL
);

-- 插入测试数据
INSERT INTO string_test (test_string) VALUES ('Hello, World!');

-- 测试 substr(X, Y, Z)
-- 预期输出：'ello' (从第2个字符开始，取4个字符)
SELECT id, test_string, substr(test_string, 2, 4) AS substr_test FROM string_test;

-- 测试 substr(X, Y)
-- 预期输出：'World!' (从第8个字符开始到结束)
SELECT id, test_string, substr(test_string, 8) AS substr_test FROM string_test;

-- 测试 substring(X, Y, Z)
-- 由于SQLite不支持substring函数，此查询会失败
-- 预期输出：错误，提示函数不存在
-- SELECT id, test_string, substring(test_string, 2, 4) AS substring_test FROM string_test;

-- 测试 substring(X, Y)
-- 由于SQLite不支持substring函数，此查询会失败
-- 预期输出：错误，提示函数不存在
-- SELECT id, test_string, substring(test_string, 8) AS substring_test FROM string_test;

-- 为了验证，尝试执行并捕获错误信息
BEGIN TRANSACTION;
SAVEPOINT test_savepoint;

-- 尝试执行 substring(X, Y, Z)
SELECT id, test_string, 
CASE 
    WHEN (SELECT count(*) FROM pragma_function_list WHERE name='substring') = 0 THEN 'Function not supported' 
    ELSE substring(test_string, 2, 4) 
END AS substring_test 
FROM string_test;

-- 尝试执行 substring(X, Y)
SELECT id, test_string, 
CASE 
    WHEN (SELECT count(*) FROM pragma_function_list WHERE name='substring') = 0 THEN 'Function not supported' 
    ELSE substring(test_string, 8) 
END AS substring_test 
FROM string_test;

ROLLBACK TO test_savepoint;
RELEASE test_savepoint;

-- 测试插入操作
INSERT INTO users (username, password, email) VALUES ('john_doe', 'securepassword', 'john@example.com');
INSERT INTO users (username, password, email) VALUES ('jane_doe', 'securepassword', 'jane@example.com');
-- 应该返回 2，因为插入了2行
SELECT total_changes();

-- 测试更新操作
UPDATE users SET email = 'john_new@example.com' WHERE username = 'john_doe';
-- 应该返回 1，因为更新了1行
SELECT total_changes();

-- 测试删除操作
DELETE FROM users WHERE username = 'jane_doe';
-- 应该返回 1，因为删除了1行
SELECT total_changes();

-- 测试多次操作后的total_changes
INSERT INTO products (name, description, price, stock) VALUES ('Laptop', 'A high-end gaming laptop', 1500.00, 10);
INSERT INTO products (name, description, price, stock) VALUES ('Mouse', 'Wireless mouse', 25.00, 100);
UPDATE products SET stock = 20 WHERE name = 'Laptop';
DELETE FROM products WHERE name = 'Mouse';
-- 应该返回 4，因为插入了2行，更新了1行，删除了1行
SELECT total_changes();

-- 创建测试数据
CREATE TABLE test_trim (
    id INTEGER PRIMARY KEY,
    text_value TEXT NOT NULL
);

INSERT INTO test_trim (text_value) VALUES
    ('   Hello   '),
    ('  World  '),
    ('   Greetings   ');

-- 测试 TRIM(X)
SELECT 
    text_value AS original_text, 
    TRIM(text_value) AS trimmed_text 
FROM 
    test_trim;

-- 期望结果：去除每个文本值的前导和尾随空格

-- 测试 TRIM(X,Y)
SELECT 
    text_value AS original_text, 
    TRIM(text_value, 'lHe') AS trimmed_text 
FROM 
    test_trim;

-- 期望结果：去除每个文本值的前导和尾随包含在指定字符串 'lHe' 中的字符

-- 测试 typeof(X) 函数是否正确

-- 检查 users 表中各列的数据类型
SELECT 
    typeof(id) AS id_type, 
    typeof(username) AS username_type, 
    typeof(password) AS password_type, 
    typeof(email) AS email_type, 
    typeof(created_at) AS created_at_type 
FROM 
    users;

-- 检查 products 表中各列的数据类型
SELECT 
    typeof(id) AS id_type, 
    typeof(name) AS name_type, 
    typeof(description) AS description_type, 
    typeof(price) AS price_type, 
    typeof(stock) AS stock_type, 
    typeof(created_at) AS created_at_type 
FROM 
    products;

-- 检查 orders 表中各列的数据类型
SELECT 
    typeof(id) AS id_type, 
    typeof(user_id) AS user_id_type, 
    typeof(total) AS total_type, 
    typeof(created_at) AS created_at_type 
FROM 
    orders;

-- 检查 order_items 表中各列的数据类型
SELECT 
    typeof(order_id) AS order_id_type, 
    typeof(product_id) AS product_id_type, 
    typeof(quantity) AS quantity_type, 
    typeof(price) AS price_type 
FROM 
    order_items;

-- 测试unhex(X)函数
-- unhex(X)函数用于将十六进制字符串转换为二进制数据

-- 测试用例1: 将十六进制字符串转换为二进制数据
SELECT unhex('48656C6C6F'); -- 预期结果: 'Hello'

-- 测试用例2: 将包含空格的十六进制字符串转换为二进制数据
SELECT unhex('4865 6C6C6F'); -- 预期结果: 'Hello'

-- 测试用例3: 将包含特殊字符的十六进制字符串转换为二进制数据
SELECT unhex('4865_6C6C6F'); -- 预期结果: 'Hello'

-- 测试用例4: 将空的十六进制字符串转换为二进制数据
SELECT unhex(''); -- 预期结果: ''

-- 测试用例5: 将非法的十六进制字符串转换为二进制数据
-- 这里使用的十六进制字符串中包含非法字符'G'，unhex函数应该返回空值或错误
SELECT unhex('48656G6C6C6F'); -- 预期结果: NULL 或 错误

-- 测试unhex(X,Y)函数
-- unhex(X,Y)函数用于将十六进制字符串转换为二进制数据，并将其放入指定的二进制数据缓冲区中

-- 测试用例6: 将十六进制字符串转换为二进制数据，并存入指定的二进制数据缓冲区
-- 在这个测试用例中，我们将 'Hello' 转换为二进制数据并将其放入名为 'buffer' 的二进制数据缓冲区
SELECT unhex('48656C6C6F', 'buffer'); 

-- 查看 'buffer' 缓冲区的内容
SELECT buffer; -- 预期结果: 'Hello'

-- 测试用例7: 使用unhex(X,Y)函数时指定的缓冲区不存在
-- 在这个测试用例中，我们尝试将数据存入名为 'non_existing_buffer' 的不存在的二进制数据缓冲区
-- unhex函数应该返回空值或错误
SELECT unhex('48656C6C6F', 'non_existing_buffer'); -- 预期结果: NULL 或 错误

-- 测试SQLite的unicode(X)函数

-- 插入一些测试数据
INSERT INTO users (username, password, email) VALUES ('user1', 'pass1', 'user1@example.com');
INSERT INTO products (name, description, price, stock) VALUES ('Product 1', 'Description 1', 100.00, 50);
INSERT INTO orders (user_id, total) VALUES (1, 100.00);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 1, 100.00);

-- 测试字符串的unicode码点值
-- 字符串：Hello World!
SELECT unicode('Hello World!');

-- 字符串：你好世界！
SELECT unicode('你好世界！');

-- 字符串：😊
SELECT unicode('😊');

-- 获取用户名为'user1'的用户的用户名和密码的unicode码点值
SELECT 
    username, 
    unicode(username) AS username_unicode, 
    unicode(password) AS password_unicode 
FROM 
    users 
WHERE 
    username = 'user1';

-- 获取产品名为'Product 1'的产品的名称和描述的unicode码点值
SELECT 
    name, 
    unicode(name) AS name_unicode, 
    unicode(description) AS description_unicode 
FROM 
    products 
WHERE 
    name = 'Product 1';

-- 获取订单ID为1的订单总额的unicode码点值
SELECT 
    id, 
    unicode(total) AS total_unicode 
FROM 
    orders 
WHERE 
    id = 1;

-- 获取订单项目的数量和价格的unicode码点值
SELECT 
    quantity, 
    unicode(price) AS price_unicode 
FROM 
    order_items 
WHERE 
    order_id = 1;

-- 在users表中进行unlikely函数的测试
-- 测试目的：确保unlikely函数对于不太可能出现的值提供了正确的优化提示

-- 插入一条用户记录，ID设为100，这样后面的查询中ID不等于100的记录就很少
INSERT INTO users (id, username, password, email) VALUES (100, 'testuser', 'testpassword', 'test@example.com');

-- 查询ID不等于100的用户，使用unlikely函数提示ID为100的记录很少出现
EXPLAIN QUERY PLAN SELECT * FROM users WHERE unlikely(id=100);

-- 查询ID等于100的用户，使用unlikely函数提示ID为100的记录很少出现
EXPLAIN QUERY PLAN SELECT * FROM users WHERE unlikely(id=100);

-- 查询username等于'testuser'的用户，使用unlikely函数提示这是一种不太可能的情况
EXPLAIN QUERY PLAN SELECT * FROM users WHERE unlikely(username='testuser');

-- 查询username不等于'testuser'的用户，使用unlikely函数提示这是一种不太可能的情况
EXPLAIN QUERY PLAN SELECT * FROM users WHERE unlikely(username='testuser');

-- 查询email等于'test@example.com'的用户，使用unlikely函数提示这是一种不太可能的情况
EXPLAIN QUERY PLAN SELECT * FROM users WHERE unlikely(email='test@example.com');

-- 查询email不等于'test@example.com'的用户，使用unlikely函数提示这是一种不太可能的情况
EXPLAIN QUERY PLAN SELECT * FROM users WHERE unlikely(email='test@example.com');

-- 创建一个测试表
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- 插入一些测试数据
INSERT INTO test_table (name) VALUES ('john'), ('Doe'), ('Mary'), ('Smith');

-- 查询原始数据
SELECT * FROM test_table;

-- 使用 UPPER(X) 函数将 name 字段转换为大写
SELECT id, UPPER(name) AS uppercase_name FROM test_table;

-- 清理测试数据
DROP TABLE test_table;

-- 创建一个包含 BLOB 字段的表
CREATE TABLE files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    filedata BLOB
);

-- 插入带有 zeroblob 数据的记录
INSERT INTO files (filename, filedata) VALUES ('test_file_1', zeroblob(1024)); -- 1 KB 的零值 BLOB
INSERT INTO files (filename, filedata) VALUES ('test_file_2', zeroblob(2048)); -- 2 KB 的零值 BLOB

-- 验证插入的数据
SELECT id, filename, length(filedata) as blob_length FROM files;

-- 更新记录中的 BLOB 数据
UPDATE files SET filedata = zeroblob(512) WHERE filename = 'test_file_1'; -- 更新为 512 字节的零值 BLOB

-- 验证更新的数据
SELECT id, filename, length(filedata) as blob_length FROM files;

-- 插入带有 NULL BLOB 数据的记录作为对比
INSERT INTO files (filename, filedata) VALUES ('test_file_3', NULL);

-- 验证所有数据
SELECT id, filename, length(filedata) as blob_length FROM files;

-- 为 users 表的 username 字段创建唯一索引
CREATE UNIQUE INDEX idx_username ON users (username);

-- 为 products 表的 price 字段创建普通索引，以便快速按价格查询商品
CREATE INDEX idx_price ON products (price);

-- 为 orders 表的 user_id 字段创建索引，以便快速按用户 ID 查询订单
CREATE INDEX idx_user_id ON orders (user_id);

-- 为 order_items 表的 order_id 字段创建联合索引，以便快速按订单 ID 查询订单项目
CREATE INDEX idx_order_id ON order_items (order_id);

-- 为 order_items 表的 product_id 字段创建联合索引，以便快速按产品 ID 查询订单项目
CREATE INDEX idx_product_id ON order_items (product_id);

-- 创建一个触发器，每当在用户表中插入新行时，自动向订单表中插入一条默认订单
CREATE TRIGGER insert_default_order AFTER INSERT ON users
BEGIN
    INSERT INTO orders (user_id, total) VALUES (NEW.id, 0.00);
END;

-- 创建一个触发器，每当在订单表中插入新行时，更新该订单的总金额
CREATE TRIGGER update_order_total AFTER INSERT ON order_items
BEGIN
    UPDATE orders 
    SET total = (
        SELECT SUM(oi.quantity * p.price) 
        FROM order_items oi 
        JOIN products p ON oi.product_id = p.id 
        WHERE oi.order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
END;

-- 测试基于现有表的虚拟表创建语法

-- 创建一个基于已有表users的虚拟表
CREATE VIRTUAL TABLE users_fts USING fts4(content='users', username, email);

-- 创建一个基于已有表products的虚拟表
CREATE VIRTUAL TABLE products_fts USING fts4(content='products', name, description);

-- 创建一个基于多个表的虚拟表，以实现全文搜索
CREATE VIRTUAL TABLE search_fts USING fts4(content='users, products', username, email, name, description);

-- 创建一个基于现有表orders的虚拟表，使用外部内容提供者
CREATE VIRTUAL TABLE orders_external USING external_module(content='orders', user_id, total);

-- 创建一个基于自定义外部内容提供者的虚拟表
CREATE VIRTUAL TABLE custom_external USING external_module(content='custom_provider', column1, column2);

-- 使用SQLite日期和时间函数进行测试

-- 1. date()
-- 返回指定时间值的日期部分
SELECT date('2024-05-21 12:34:56');

-- 2. time()
-- 返回指定时间值的时间部分
SELECT time('2024-05-21 12:34:56');

-- 3. datetime()
-- 将日期和时间值以特定格式组合起来
SELECT datetime('2024-05-21 12:34:56', '+1 day', '-2 hours');

-- 4. julianday()
-- 返回指定时间值的朱利安日数
SELECT julianday('2024-05-21 12:34:56');

-- 5. unixepoch()
-- 将Unix时间戳转换为日期和时间值
SELECT datetime(1590044486, 'unixepoch');

-- 6. strftime()
-- 格式化日期和时间值
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');

-- 7. timediff()
-- 计算两个时间值之间的时间差
SELECT timediff('2024-05-21 12:34:56', '2024-05-20 10:00:00');

-- 删除指定条件的数据
-- 删除用户名为 'john_doe' 的用户
DELETE FROM users WHERE username = 'john_doe';

-- 删除某个订单及其关联的订单项目
-- 删除订单号为 1 的订单及其相关订单项目
DELETE FROM orders WHERE id = 1;

-- 删除所有库存不足的产品
-- 删除库存小于等于 0 的产品
DELETE FROM products WHERE stock <= 0;

-- 删除所有用户
DELETE FROM users;

-- 删除所有订单项目
DELETE FROM order_items;

-- 删除所有订单
DELETE FROM orders;

-- 删除所有数据（清空所有表）
DELETE FROM users;
DELETE FROM products;
DELETE FROM order_items;
DELETE FROM orders;

-- 创建第二个数据库以供测试
ATTACH DATABASE 'second_database.db' AS second_db;

-- 创建第二个数据库的表
CREATE TABLE second_db.test_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- 插入数据到第二个数据库的表
INSERT INTO second_db.test_table (name) VALUES ('Test Data');

-- 查询第二个数据库的表
SELECT * FROM second_db.test_table;

-- 从第二个数据库中分离
DETACH DATABASE second_db;

-- 尝试查询已分离的数据库的表，应该会报错
-- SELECT * FROM second_db.test_table;

-- 测试DROP INDEX语法
-- 创建一个索引
CREATE INDEX idx_username ON users (username);
-- 尝试删除存在的索引
DROP INDEX IF EXISTS idx_username;
-- 尝试删除不存在的索引
DROP INDEX IF EXISTS idx_nonexistent_index;

-- 测试DROP TABLE语法
-- 尝试删除存在的表
DROP TABLE IF EXISTS users;
-- 尝试删除不存在的表
DROP TABLE IF EXISTS nonexistent_table;

-- 测试DROP TRIGGER语法
-- 创建一个触发器
CREATE TRIGGER my_trigger
AFTER INSERT ON users
BEGIN
    -- 触发器逻辑
END;
-- 尝试删除存在的触发器
DROP TRIGGER IF EXISTS my_trigger;
-- 尝试删除不存在的触发器
DROP TRIGGER IF EXISTS nonexistent_trigger;

-- 测试DROP VIEW语法
-- 创建一个视图
CREATE VIEW user_details AS
SELECT id, username, email FROM users;
-- 尝试删除存在的视图
DROP VIEW IF EXISTS user_details;
-- 尝试删除不存在的视图
DROP VIEW IF EXISTS nonexistent_view;

-- 开始一个事务
BEGIN TRANSACTION;

-- 在 users 表中插入一条新记录
INSERT INTO users (username, password, email) VALUES ('test_user', 'testpassword', 'test@example.com');

-- 查询 users 表，确认新记录已插入（事务尚未提交）
SELECT * FROM users WHERE username = 'test_user';

-- 提交事务
END TRANSACTION;

-- 查询 users 表，确认提交成功
SELECT * FROM users WHERE username = 'test_user';

-- 查询用户表的执行计划
EXPLAIN QUERY PLAN SELECT * FROM users;

-- 带条件的选择查询的执行计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE username = 'john_doe';

-- 多表联接查询的执行计划
EXPLAIN QUERY PLAN 
SELECT 
    orders.id AS order_id, 
    users.username, 
    products.name AS product_name, 
    order_items.quantity, 
    order_items.price 
FROM 
    orders 
JOIN 
    users ON orders.user_id = users.id 
JOIN 
    order_items ON orders.id = order_items.order_id 
JOIN 
    products ON order_items.product_id = products.id;

-- 分组查询的执行计划
EXPLAIN QUERY PLAN 
SELECT 
    user_id, 
    COUNT(*) AS order_count, 
    SUM(total) AS total_spent 
FROM 
    orders 
GROUP BY 
    user_id;

-- 排序查询的执行计划
EXPLAIN QUERY PLAN SELECT * FROM products ORDER BY price DESC;

-- 子查询的执行计划
EXPLAIN QUERY PLAN 
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- LIKE操作符测试
-- 搜索用户名以 'john' 开头的用户
SELECT * FROM users WHERE username LIKE 'john%';

-- 搜索邮箱以 'example.com' 结尾的用户
SELECT * FROM users WHERE email LIKE '%@example.com';

-- GLOB操作符测试
-- 搜索用户名以 'J' 或 'j' 开头的用户
SELECT * FROM users WHERE username GLOB '[Jj]*';

-- 搜索用户名为三个字符长，且以 'j' 开头的用户
SELECT * FROM users WHERE username GLOB 'j??';

-- REGEXP操作符测试
-- 搜索用户名以 'john' 开头的用户
SELECT * FROM users WHERE username REGEXP '^john';

-- 搜索用户名包含至少一个数字的用户
SELECT * FROM users WHERE username REGEXP '[0-9]';

-- MATCH操作符测试（仅在全文搜索虚拟表上有效）
-- 假设我们有一个全文搜索虚拟表名为 'products_fts'
-- 搜索产品描述中包含 'high-end' 的产品
SELECT * FROM products_fts WHERE products_fts MATCH 'high-end';

-- extract操作符测试
-- 提取用户电子邮件地址中的域名部分
SELECT email, SUBSTR(email, INSTR(email, '@') + 1) AS domain FROM users;

-- 测试BETWEEN操作符
-- 1. 测试数字范围
SELECT * FROM products WHERE price BETWEEN 1000 AND 2000;

-- 2. 测试日期范围
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- 3. 测试字符串范围（按字母顺序）
SELECT * FROM users WHERE username BETWEEN 'a' AND 'm';

-- 4. 测试NULL值范围
SELECT * FROM users WHERE email BETWEEN 'a@example.com' AND 'm@example.com';

-- 5. 测试排除边界值
SELECT * FROM products WHERE price BETWEEN 1000 AND 2000 AND price NOT BETWEEN 1500 AND 1800;

-- 6. 测试反向范围
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-31' AND '2024-01-01';

-- 7. 测试非数值、非日期、非字符串类型的列
-- 假设有一列存储用户积分
SELECT * FROM users WHERE points BETWEEN 100 AND 200;

-- 8. 测试包含NULL的范围
SELECT * FROM users WHERE email BETWEEN 'a@example.com' AND 'z@example.com';

-- 9. 测试混合数据类型的范围（SQLite 在比较不同类型时会进行自动转换）
-- 假设有一列存储用户年龄
SELECT * FROM users WHERE age BETWEEN 18 AND '30';

-- 10. 测试范围包含边界值的情况
SELECT * FROM products WHERE price BETWEEN 1000 AND 2000 AND (price = 1000 OR price = 2000);

-- 测试 CASE 语法

-- 简单 CASE 表达式
SELECT 
    id,
    CASE 
        WHEN price > 1000 THEN 'Expensive'
        WHEN price <= 1000 THEN 'Affordable'
        ELSE 'Unknown'
    END AS price_category
FROM 
    products;

-- CASE 表达式中的聚合函数
SELECT 
    user_id,
    COUNT(*) AS order_count,
    CASE 
        WHEN COUNT(*) > 5 THEN 'Frequent'
        WHEN COUNT(*) <= 5 THEN 'Occasional'
        ELSE 'Unknown'
    END AS order_frequency
FROM 
    orders
GROUP BY 
    user_id;

-- CASE 表达式中的子查询
SELECT 
    id,
    (CASE 
        WHEN (SELECT COUNT(*) FROM order_items WHERE order_id = orders.id) > 0 THEN 'Has Items'
        ELSE 'No Items'
    END) AS order_status
FROM 
    orders;

-- CASE 表达式中的多条件
SELECT 
    id,
    CASE 
        WHEN stock > 10 AND price < 1000 THEN 'In Stock and Affordable'
        WHEN stock > 10 AND price >= 1000 THEN 'In Stock but Expensive'
        WHEN stock <= 10 THEN 'Low Stock'
        ELSE 'Unknown'
    END AS product_status
FROM 
    products;

-- CASE 表达式中的嵌套
SELECT 
    id,
    CASE 
        WHEN stock > 10 THEN
            CASE 
                WHEN price < 1000 THEN 'Affordable'
                WHEN price >= 1000 THEN 'Expensive'
                ELSE 'Unknown'
            END
        ELSE 'Low Stock'
    END AS product_status
FROM 
    products;

-- 测试IN语法：查询用户名为'alice'和'bob'的用户
SELECT * FROM users WHERE username IN ('alice', 'bob');

-- 测试NOT IN语法：查询用户名不为'alice'和'bob'的用户
SELECT * FROM users WHERE username NOT IN ('alice', 'bob');

-- 测试IN语法：查询购买了产品1和产品2的订单
SELECT * FROM orders WHERE id IN (SELECT order_id FROM order_items WHERE product_id IN (1, 2));

-- 测试NOT IN语法：查询未购买产品1和产品2的订单
SELECT * FROM orders WHERE id NOT IN (SELECT order_id FROM order_items WHERE product_id IN (1, 2));

-- 测试IN语法：查询购买了产品名称为'Laptop'和'Mouse'的订单
SELECT * FROM orders WHERE id IN (SELECT order_id FROM order_items WHERE product_id IN (SELECT id FROM products WHERE name IN ('Laptop', 'Mouse')));

-- 测试NOT IN语法：查询未购买产品名称为'Laptop'和'Mouse'的订单
SELECT * FROM orders WHERE id NOT IN (SELECT order_id FROM order_items WHERE product_id IN (SELECT id FROM products WHERE name IN ('Laptop', 'Mouse')));

-- 测试常规列名
SELECT id, username, email FROM users;

-- 测试特殊列名"ROWID"
SELECT ROWID, username, email FROM users;

-- 测试特殊列名"OID"
-- 在SQLite中，"OID"是"ROWID"的别名，因此使用"ROWID"和"OID"是等价的
SELECT OID, username, email FROM users;

-- 测试特殊列名"_ROWID_"
-- "_ROWID_"是"ROWID"的别名，用于WITHOUT ROWID表中
-- 但由于我们的表没有使用WITHOUT ROWID，所以在这里使用"ROWID"即可
SELECT _ROWID_, username, email FROM users;

-- 检查是否存在符合条件的记录
SELECT 
    id, 
    username 
FROM 
    users 
WHERE 
    EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- 检查是否存在符合条件的记录，并且用户名不是 "admin"
SELECT 
    id, 
    username 
FROM 
    users 
WHERE 
    EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
    AND username != 'admin';

-- 检查是否存在符合条件的记录，并且订单金额大于 1000
SELECT 
    id, 
    username 
FROM 
    users 
WHERE 
    EXISTS (
        SELECT 1 
        FROM orders 
        WHERE orders.user_id = users.id 
        AND orders.total > 1000
    );

-- 检查是否存在用户没有下过订单的情况
SELECT 
    id, 
    username 
FROM 
    users 
WHERE 
    NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- 检查是否存在用户没有下过订单的情况，并且用户名不是 "guest"
SELECT 
    id, 
    username 
FROM 
    users 
WHERE 
    NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
    AND username != 'guest';

-- 子查询测试

-- 1. 简单的子查询，选择用户名和订单数
SELECT 
    username, 
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count 
FROM 
    users;

-- 2. 带条件的子查询，选择用户名和该用户最近的订单总额
SELECT 
    username, 
    (SELECT SUM(total) FROM orders WHERE orders.user_id = users.id ORDER BY created_at DESC LIMIT 1) AS latest_order_total 
FROM 
    users;

-- 3. 子查询与多表联接结合，选择用户名、产品名称和该用户购买过的产品数
SELECT 
    u.username, 
    p.name AS product_name, 
    (SELECT COUNT(*) 
     FROM order_items 
     WHERE order_items.product_id = p.id 
     AND order_items.order_id IN (SELECT id FROM orders WHERE orders.user_id = u.id)
    ) AS product_count 
FROM 
    users u 
JOIN 
    orders o ON u.id = o.user_id 
JOIN 
    order_items oi ON o.id = oi.order_id 
JOIN 
    products p ON oi.product_id = p.id;

-- 4. 子查询中使用聚合函数，选择用户名和该用户平均每笔订单的总额
SELECT 
    username, 
    (SELECT AVG(total) FROM orders WHERE orders.user_id = users.id) AS avg_order_total 
FROM 
    users;

-- 5. 子查询中的条件查询，选择购买过产品ID为1的用户列表
SELECT 
    username 
FROM 
    users 
WHERE 
    EXISTS (SELECT 1 FROM order_items WHERE order_items.product_id = 1 AND order_items.order_id IN (SELECT id FROM orders WHERE orders.user_id = users.id));

-- 查询所有用户的用户名及其最近一次订单的订单号和总金额
SELECT 
    u.username, 
    o.id AS latest_order_id, 
    o.total AS latest_order_total 
FROM 
    users u
LEFT JOIN 
    (
        SELECT 
            user_id, 
            id, 
            total 
        FROM 
            orders 
        WHERE 
            id = (
                SELECT 
                    MAX(id) 
                FROM 
                    orders 
                WHERE 
                    user_id = u.id
            )
    ) AS o ON u.id = o.user_id;

-- 查询每个产品的名称以及被购买过的总次数
SELECT 
    p.name, 
    (
        SELECT 
            COUNT(*) 
        FROM 
            order_items oi 
        WHERE 
            oi.product_id = p.id
    ) AS purchase_count 
FROM 
    products p;

-- 查询每个用户的用户名以及其购买的产品总数量
SELECT 
    u.username, 
    (
        SELECT 
            SUM(oi.quantity) 
        FROM 
            orders o 
        JOIN 
            order_items oi ON o.id = oi.order_id 
        WHERE 
            o.user_id = u.id
    ) AS total_quantity 
FROM 
    users u;

-- 测试 CAST 表达式语法

-- 创建一个用于测试的临时表
CREATE TEMP TABLE test_cast (
    id INTEGER PRIMARY KEY,
    value TEXT
);

-- 插入一些测试数据
INSERT INTO test_cast (value) VALUES ('123'), ('3.14'), ('abc'), ('2022-05-21');

-- 测试 NONE CAST
SELECT value, CAST(value AS NONE) AS casted_value_none FROM test_cast;

-- 测试 TEXT CAST
SELECT value, CAST(value AS TEXT) AS casted_value_text FROM test_cast;

-- 测试 REAL CAST
SELECT value, CAST(value AS REAL) AS casted_value_real FROM test_cast;

-- 测试 INTEGER CAST
SELECT value, CAST(value AS INTEGER) AS casted_value_integer FROM test_cast;

-- 测试 NUMERIC CAST
SELECT value, CAST(value AS NUMERIC) AS casted_value_numeric FROM test_cast;

-- 删除临时表
DROP TABLE test_cast;

-- WHERE clause of a SELECT statement
SELECT * FROM users WHERE id = 1;

-- WHERE clause of an UPDATE statement
UPDATE products SET stock = stock - 1 WHERE id = 5;

-- WHERE clause of a DELETE statement
DELETE FROM orders WHERE total > 1000;

-- ON clause of a join in a SELECT statement
SELECT *
FROM orders
JOIN users ON orders.user_id = users.id;

-- USING clause of a join in a SELECT statement
SELECT *
FROM orders
JOIN users USING (user_id);

-- HAVING clause of a SELECT statement
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;

-- WHEN clause of an SQL trigger
-- Assuming a trigger exists
CREATE TRIGGER update_stock_trigger
AFTER INSERT ON order_items
BEGIN
    UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;

-- WHEN clause of a CASE expression
SELECT id, name,
       CASE
           WHEN price > 1000 THEN 'Expensive'
           WHEN price > 500 THEN 'Moderate'
           ELSE 'Cheap'
       END AS price_category
FROM products;

-- 1. 测试字符串函数
-- 字符串连接函数 CONCAT
SELECT CONCAT(username, '@example.com') AS email FROM users;

-- 字符串长度函数 LENGTH
SELECT username, LENGTH(username) AS username_length FROM users;

-- 子字符串函数 SUBSTR
SELECT username, SUBSTR(username, 1, 3) AS username_substr FROM users;

-- 2. 测试数值函数
-- 求和函数 SUM
SELECT SUM(price) AS total_price FROM products;

-- 平均值函数 AVG
SELECT AVG(price) AS avg_price FROM products;

-- 最大值函数 MAX
SELECT MAX(price) AS max_price FROM products;

-- 最小值函数 MIN
SELECT MIN(price) AS min_price FROM products;

-- 绝对值函数 ABS
SELECT ABS(-10) AS absolute_value;

-- 3. 测试日期时间函数
-- 当前日期时间函数 CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP AS current_time;

-- 日期时间格式化函数 STRFTIME
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now') AS formatted_time;

-- 日期时间计算函数 DATE
SELECT DATE('now') AS current_date;

-- 日期时间差函数 DATEDIFF
SELECT DATEDIFF('2024-05-21', '2024-05-01') AS days_diff;

-- 4. 测试条件函数
-- CASE 表达式
SELECT 
    CASE 
        WHEN price > 1000 THEN 'Expensive'
        ELSE 'Affordable'
    END AS price_category 
FROM 
    products;

-- 5. 测试聚合函数
-- COUNT 函数
SELECT COUNT(*) AS user_count FROM users;

-- GROUP_CONCAT 函数
SELECT user_id, GROUP_CONCAT(product_id) AS ordered_products FROM orders GROUP BY user_id;

-- 创建索引
CREATE INDEX idx_users_username ON users (username);
CREATE INDEX idx_products_name ON products (name);

-- 使用索引进行选择查询
SELECT * FROM users INDEXED BY idx_users_username WHERE username = 'john_doe';

-- 使用索引进行排序查询
SELECT * FROM products INDEXED BY idx_products_name ORDER BY name ASC;

-- 使用联合索引进行条件查询
SELECT * FROM order_items INDEXED BY order_items_order_id_product_id WHERE order_id = 1 AND product_id = 1;

-- 使用索引进行连接查询
SELECT 
    orders.id AS order_id, 
    users.username, 
    products.name AS product_name, 
    order_items.quantity, 
    order_items.price 
FROM 
    orders INDEXED BY orders_user_id 
JOIN 
    users ON orders.user_id = users.id 
JOIN 
    order_items ON orders.id = order_items.order_id 
JOIN 
    products ON order_items.product_id = products.id;

-- 使用索引进行分组查询
SELECT 
    user_id, 
    COUNT(*) AS order_count, 
    SUM(total) AS total_spent 
FROM 
    orders INDEXED BY orders_user_id 
GROUP BY 
    user_id;

-- 使用索引进行子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders INDEXED BY orders_user_id WHERE total > 1000);

-- 测试第一种形式：INSERT INTO table VALUES(...);
-- 向 users 表插入一条用户记录
INSERT INTO users VALUES (1, 'john_doe', 'securepassword', 'john@example.com', '2024-05-21 12:00:00');

-- 测试第二种形式：INSERT INTO table SELECT ...;
-- 向 products 表插入来自另一个表的数据
INSERT INTO products SELECT * FROM products_backup;

-- 测试第三种形式：INSERT INTO table DEFAULT VALUES;
-- 向 orders 表插入一条默认值记录
INSERT INTO orders DEFAULT VALUES;

-- 在 users 表中创建一个具有唯一约束的示例表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

-- 尝试插入一个已存在的用户名，预期结果：事务回滚，没有新行被插入
INSERT INTO users (username, password) VALUES ('john_doe', 'password1');
INSERT INTO users (username, password) VALUES ('john_doe', 'password2') ON CONFLICT(ROLLBACK);

-- 尝试插入一个已存在的用户名，预期结果：事务回滚，没有新行被插入
INSERT INTO users (username, password) VALUES ('jane_doe', 'password3');
INSERT INTO users (username, password) VALUES ('jane_doe', 'password4') ON CONFLICT(ABORT);

-- 尝试插入一个已存在的用户名，预期结果：INSERT 操作失败，返回错误信息
INSERT INTO users (username, password) VALUES ('mary_smith', 'password5');
INSERT INTO users (username, password) VALUES ('mary_smith', 'password6') ON CONFLICT(FAIL);

-- 尝试插入一个已存在的用户名，预期结果：忽略插入冲突，不触发错误
INSERT INTO users (username, password) VALUES ('mark_johnson', 'password7');
INSERT INTO users (username, password) VALUES ('mark_johnson', 'password8') ON CONFLICT(IGNORE);

-- 尝试插入一个已存在的用户名，预期结果：替换已存在的行
INSERT INTO users (username, password) VALUES ('sarah_williams', 'password9');
INSERT INTO users (username, password) VALUES ('sarah_williams', 'password10') ON CONFLICT(REPLACE);

-- 显示 users 表中的内容，以验证上述操作的结果
SELECT * FROM users;

-- 创建一个测试表
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- PRAGMA语句测试
-- 打开数据库检查的PRAGMA设置
PRAGMA foreign_keys;

-- 设置PRAGMA，使得插入自动分配ID
PRAGMA autoincrement = 1;

-- 查看数据库版本信息
PRAGMA user_version;

-- 修改数据库版本信息
PRAGMA user_version = 2;

-- 查看数据库的schema
PRAGMA table_info(test_table);

-- 查看数据库中所有表的列表
PRAGMA table_info;

-- 检查数据库是否为可写状态
PRAGMA writable_schema;

-- 查看数据库中所有索引的列表
PRAGMA index_list(test_table);

-- 查看特定索引的详细信息
PRAGMA index_info(index_name);

-- 查看表的存储信息
PRAGMA page_count;

-- 查看表的空闲页数
PRAGMA freelist_count;

-- 查看表的最大页数
PRAGMA max_page_count;

-- 查看SQLite的配置参数
PRAGMA compile_options;

-- 查看SQLite的限制信息
PRAGMA limits;

-- 查看数据库中索引的状态
PRAGMA index_xinfo(index_name);

-- 查看数据库中各表的详细信息
PRAGMA database_list;

-- 查看数据库中的设置
PRAGMA database_list;

-- 查看数据库的自动VACUUM模式
PRAGMA auto_vacuum;

-- 设置数据库的自动VACUUM模式
PRAGMA auto_vacuum = FULL;

-- 查看数据库的递增计数器
PRAGMA autoincrement;

-- 查看数据库的缓存大小
PRAGMA cache_size;

-- 设置数据库的缓存大小
PRAGMA cache_size = 10000;

-- 查看数据库的缓存使用情况
PRAGMA cache_spill;

-- 查看数据库的编码
PRAGMA encoding;

-- 设置数据库的编码
PRAGMA encoding = 'UTF-8';

-- 查看数据库的递增计数器
PRAGMA foreign_key_check;

-- 开始数据库的递增计数器
PRAGMA foreign_key_check = ON;

-- 查看数据库的全局锁状态
PRAGMA locking_mode;

-- 设置数据库的全局锁状态
PRAGMA locking_mode = EXCLUSIVE;

-- 查看数据库的日志模式
PRAGMA journal_mode;

-- 设置数据库的日志模式
PRAGMA journal_mode = WAL;

-- 查看数据库的主页大小
PRAGMA page_size;

-- 设置数据库的主页大小
PRAGMA page_size = 4096;

-- 查看数据库的预留字节
PRAGMA reserved_bytes;

-- 设置数据库的预留字节
PRAGMA reserved_bytes = 1000;

-- 查看数据库的主键
PRAGMA primary_key;

-- 设置数据库的主键
PRAGMA primary_key = ON;

-- 查看数据库的同步状态
PRAGMA synchronous;

-- 设置数据库的同步状态
PRAGMA synchronous = NORMAL;

-- 查看数据库的表空间名称
PRAGMA tablespace_name;

-- 查看数据库的WAL模式
PRAGMA wal_checkpoint;

-- 设置数据库的WAL模式
PRAGMA wal_checkpoint(FULL);

-- 创建一个索引以便后续测试
CREATE INDEX idx_username ON users(username);

-- 检查索引是否存在
PRAGMA index_list('users');

-- 重新构建索引
REINDEX idx_username;

-- 检查是否重新构建成功
PRAGMA index_info('idx_username');

-- 创建一个 SAVEPOINT
SAVEPOINT sp1;

-- 在 SAVEPOINT 下执行一些插入操作
INSERT INTO users (username, password, email) VALUES ('alice', 'password123', 'alice@example.com');
INSERT INTO products (name, price, stock) VALUES ('Phone', 599.99, 20);

-- 查看保存点下的数据是否已经插入
SELECT * FROM users WHERE username = 'alice';
SELECT * FROM products WHERE name = 'Phone';

-- 如果需要回滚到保存点，则执行以下语句
-- ROLLBACK TO SAVEPOINT sp1;

-- 如果保存点下的操作全部成功，提交事务
-- COMMIT;

-- 创建一个临时表用于测试
CREATE TEMP TABLE temp_users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE
);

-- 插入一条数据
INSERT INTO temp_users (username, email) VALUES ('user1', 'user1@example.com');

-- 尝试插入一条相同的数据，但是email与现有记录冲突
INSERT INTO temp_users (username, email) VALUES ('user2', 'user1@example.com');

-- 查询已有数据，应该只有一条记录
SELECT * FROM temp_users;

-- 使用REPLACE语法插入相同的数据
REPLACE INTO temp_users (username, email) VALUES ('user2', 'user1@example.com');

-- 再次查询已有数据，现在应该只有一条记录，但是username应该被更新为'user2'
SELECT * FROM temp_users;

-- 清理测试用的临时表
DROP TABLE temp_users;

-- 在用户表中插入一条新记录，并返回插入的id和用户名
INSERT INTO users (username, password, email)
VALUES ('test_user', 'testpassword', 'test@example.com')
RETURNING id, username;

-- 更新产品表中的库存，并返回更新后的库存数量
UPDATE products
SET stock = stock - 1
WHERE id = 1
RETURNING stock;

-- 删除订单表中的一条记录，并返回被删除的订单ID和总金额
DELETE FROM orders
WHERE id = 1
RETURNING id, total;

-- 通过插入一条订单记录和订单项目记录来演示多条返回值
BEGIN TRANSACTION;
INSERT INTO orders (user_id, total)
VALUES (1, 1500.00);
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (LAST_INSERT_ROWID(), 1, 1, 1500.00);
COMMIT;
SELECT o.id AS order_id, oi.id AS order_item_id
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = LAST_INSERT_ROWID();

-- 插入一条新记录，如果唯一索引或主键已存在则更新
INSERT INTO users (username, password, email)
VALUES ('john_doe', 'securepassword', 'john@example.com')
ON CONFLICT(username) DO UPDATE SET
password = 'newpassword', email = 'updated_john@example.com';

-- 期望结果：如果 'john_doe' 用户已存在，则更新密码和电子邮件

-- 插入一条新记录，如果唯一索引或主键已存在则忽略
INSERT OR IGNORE INTO users (username, password, email)
VALUES ('jane_doe', 'securepassword', 'jane@example.com');

-- 期望结果：如果 'jane_doe' 用户已存在，则忽略插入操作

-- 插入一条新记录，如果唯一索引或主键已存在则不执行任何操作
INSERT OR REPLACE INTO users (username, password, email)
VALUES ('jane_doe', 'newpassword', 'updated_jane@example.com');

-- 期望结果：如果 'jane_doe' 用户已存在，则替换现有记录的密码和电子邮件

-- 在插入新记录时，同时更新相关表的数据
INSERT INTO orders (user_id, total)
VALUES (
    (SELECT id FROM users WHERE username = 'john_doe'),
    1500.00
)
ON CONFLICT(user_id) DO NOTHING;

-- 期望结果：如果与 'john_doe' 相关的订单已存在，则不执行任何操作

-- 在插入新记录时，同时更新相关表的数据
INSERT INTO orders (user_id, total)
VALUES (
    (SELECT id FROM users WHERE username = 'john_doe'),
    1500.00
)
ON CONFLICT(user_id) DO UPDATE SET
total = total + 1500.00;

-- 期望结果：如果与 'john_doe' 相关的订单已存在，则更新订单总额

-- 在插入新记录时，如果用户已存在则更新密码和电子邮件，否则插入新用户
INSERT INTO users (username, password, email)
VALUES ('john_doe', 'securepassword', 'john@example.com')
ON CONFLICT(username) DO UPDATE SET
password = 'newpassword', email = 'updated_john@example.com'
WHERE username = 'john_doe';

-- 期望结果：如果 'john_doe' 用户已存在，则更新密码和电子邮件，否则插入新用户

-- 插入一条新记录，如果唯一索引或主键已存在则忽略
INSERT INTO users (username, password, email)
VALUES ('john_doe', 'securepassword', 'john@example.com')
ON CONFLICT(username) DO NOTHING;

-- 期望结果：如果 'john_doe' 用户已存在，则忽略插入操作

-- 创建一个测试数据库（假设数据库文件名为test.db）
-- 这个数据库文件包含了之前定义的表结构

-- 打开测试数据库
ATTACH DATABASE 'test.db' AS test_db;

-- 插入一些数据以确保数据库文件有一定的大小
INSERT INTO users (username, password, email) VALUES ('test_user1', 'password1', 'test1@example.com');
INSERT INTO products (name, description, price, stock) VALUES ('Test Product 1', 'Test description', 10.99, 100);
INSERT INTO orders (user_id, total) VALUES (1, 10.99);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 1, 10.99);

-- 关闭数据库连接，确保数据被写入磁盘
DETACH DATABASE test_db;

-- 打开测试数据库
ATTACH DATABASE 'test.db' AS test_db;

-- 查询数据库文件大小（单位为字节）
SELECT 'Before VACUUM: ', page_count * page_size AS size_before_vacuum FROM pragma_page_count(), pragma_page_size();

-- 执行VACUUM命令来优化数据库文件大小
VACUUM;

-- 查询优化后的数据库文件大小
SELECT 'After VACUUM: ', page_count * page_size AS size_after_vacuum FROM pragma_page_count(), pragma_page_size();

-- 关闭数据库连接
DETACH DATABASE test_db;

-- 创建测试数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', 'user2@example.com');

INSERT INTO products (name, description, price, stock) VALUES ('Product 1', 'Description 1', 10.0, 100);
INSERT INTO products (name, description, price, stock) VALUES ('Product 2', 'Description 2', 20.0, 50);

INSERT INTO orders (user_id, total) VALUES (1, 30.0);
INSERT INTO orders (user_id, total) VALUES (2, 25.0);

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 3, 30.0);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (2, 2, 1, 25.0);

-- 测试WITH子句
WITH total_spent AS (
    SELECT user_id, SUM(total) AS total_spent FROM orders GROUP BY user_id
)
SELECT users.username, total_spent.total_spent
FROM users
JOIN total_spent ON users.id = total_spent.user_id;

-- 创建一个索引以便后续测试
CREATE INDEX idx_username ON users(username);

-- 检查索引是否存在
PRAGMA index_list('users');

-- 重新构建索引
REINDEX idx_username;

-- 检查是否重新构建成功
PRAGMA index_info('idx_username');

-- 创建一个 SAVEPOINT
SAVEPOINT sp1;

-- 在 SAVEPOINT 下执行一些插入操作
INSERT INTO users (username, password, email) VALUES ('alice', 'password123', 'alice@example.com');
INSERT INTO products (name, price, stock) VALUES ('Phone', 599.99, 20);

-- 查看保存点下的数据是否已经插入
SELECT * FROM users WHERE username = 'alice';
SELECT * FROM products WHERE name = 'Phone';

-- 如果需要回滚到保存点，则执行以下语句
-- ROLLBACK TO SAVEPOINT sp1;

-- 如果保存点下的操作全部成功，提交事务
-- COMMIT;

-- 创建一个临时表用于测试
CREATE TEMP TABLE temp_users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE
);

-- 插入一条数据
INSERT INTO temp_users (username, email) VALUES ('user1', 'user1@example.com');

-- 尝试插入一条相同的数据，但是email与现有记录冲突
INSERT INTO temp_users (username, email) VALUES ('user2', 'user1@example.com');

-- 查询已有数据，应该只有一条记录
SELECT * FROM temp_users;

-- 使用REPLACE语法插入相同的数据
REPLACE INTO temp_users (username, email) VALUES ('user2', 'user1@example.com');

-- 再次查询已有数据，现在应该只有一条记录，但是username应该被更新为'user2'
SELECT * FROM temp_users;

-- 清理测试用的临时表
DROP TABLE temp_users;

-- 在用户表中插入一条新记录，并返回插入的id和用户名
INSERT INTO users (username, password, email)
VALUES ('test_user', 'testpassword', 'test@example.com')
RETURNING id, username;

-- 更新产品表中的库存，并返回更新后的库存数量
UPDATE products
SET stock = stock - 1
WHERE id = 1
RETURNING stock;

-- 删除订单表中的一条记录，并返回被删除的订单ID和总金额
DELETE FROM orders
WHERE id = 1
RETURNING id, total;

-- 通过插入一条订单记录和订单项目记录来演示多条返回值
BEGIN TRANSACTION;
INSERT INTO orders (user_id, total)
VALUES (1, 1500.00);
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (LAST_INSERT_ROWID(), 1, 1, 1500.00);
COMMIT;
SELECT o.id AS order_id, oi.id AS order_item_id
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = LAST_INSERT_ROWID();

-- 插入一条新记录，如果唯一索引或主键已存在则更新
INSERT INTO users (username, password, email)
VALUES ('john_doe', 'securepassword', 'john@example.com')
ON CONFLICT(username) DO UPDATE SET
password = 'newpassword', email = 'updated_john@example.com';

-- 期望结果：如果 'john_doe' 用户已存在，则更新密码和电子邮件

-- 插入一条新记录，如果唯一索引或主键已存在则忽略
INSERT OR IGNORE INTO users (username, password, email)
VALUES ('jane_doe', 'securepassword', 'jane@example.com');

-- 期望结果：如果 'jane_doe' 用户已存在，则忽略插入操作

-- 插入一条新记录，如果唯一索引或主键已存在则不执行任何操作
INSERT OR REPLACE INTO users (username, password, email)
VALUES ('jane_doe', 'newpassword', 'updated_jane@example.com');

-- 期望结果：如果 'jane_doe' 用户已存在，则替换现有记录的密码和电子邮件

-- 在插入新记录时，同时更新相关表的数据
INSERT INTO orders (user_id, total)
VALUES (
    (SELECT id FROM users WHERE username = 'john_doe'),
    1500.00
)
ON CONFLICT(user_id) DO NOTHING;

-- 期望结果：如果与 'john_doe' 相关的订单已存在，则不执行任何操作

-- 在插入新记录时，同时更新相关表的数据
INSERT INTO orders (user_id, total)
VALUES (
    (SELECT id FROM users WHERE username = 'john_doe'),
    1500.00
)
ON CONFLICT(user_id) DO UPDATE SET
total = total + 1500.00;

-- 期望结果：如果与 'john_doe' 相关的订单已存在，则更新订单总额

-- 在插入新记录时，如果用户已存在则更新密码和电子邮件，否则插入新用户
INSERT INTO users (username, password, email)
VALUES ('john_doe', 'securepassword', 'john@example.com')
ON CONFLICT(username) DO UPDATE SET
password = 'newpassword', email = 'updated_john@example.com'
WHERE username = 'john_doe';

-- 期望结果：如果 'john_doe' 用户已存在，则更新密码和电子邮件，否则插入新用户

-- 插入一条新记录，如果唯一索引或主键已存在则忽略
INSERT INTO users (username, password, email)
VALUES ('john_doe', 'securepassword', 'john@example.com')
ON CONFLICT(username) DO NOTHING;

-- 期望结果：如果 'john_doe' 用户已存在，则忽略插入操作

-- 创建一个测试数据库（假设数据库文件名为test.db）
-- 这个数据库文件包含了之前定义的表结构

-- 打开测试数据库
ATTACH DATABASE 'test.db' AS test_db;

-- 插入一些数据以确保数据库文件有一定的大小
INSERT INTO users (username, password, email) VALUES ('test_user1', 'password1', 'test1@example.com');
INSERT INTO products (name, description, price, stock) VALUES ('Test Product 1', 'Test description', 10.99, 100);
INSERT INTO orders (user_id, total) VALUES (1, 10.99);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 1, 10.99);

-- 关闭数据库连接，确保数据被写入磁盘
DETACH DATABASE test_db;

-- 打开测试数据库
ATTACH DATABASE 'test.db' AS test_db;

-- 查询数据库文件大小（单位为字节）
SELECT 'Before VACUUM: ', page_count * page_size AS size_before_vacuum FROM pragma_page_count(), pragma_page_size();

-- 执行VACUUM命令来优化数据库文件大小
VACUUM;

-- 查询优化后的数据库文件大小
SELECT 'After VACUUM: ', page_count * page_size AS size_after_vacuum FROM pragma_page_count(), pragma_page_size();

-- 关闭数据库连接
DETACH DATABASE test_db;

-- 创建测试数据
INSERT INTO users (username, password, email) VALUES ('user1', 'password1', 'user1@example.com');
INSERT INTO users (username, password, email) VALUES ('user2', 'password2', 'user2@example.com');

INSERT INTO products (name, description, price, stock) VALUES ('Product 1', 'Description 1', 10.0, 100);
INSERT INTO products (name, description, price, stock) VALUES ('Product 2', 'Description 2', 20.0, 50);

INSERT INTO orders (user_id, total) VALUES (1, 30.0);
INSERT INTO orders (user_id, total) VALUES (2, 25.0);

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (1, 1, 3, 30.0);
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (2, 2, 1, 25.0);

-- 测试WITH子句
WITH total_spent AS (
    SELECT user_id, SUM(total) AS total_spent FROM orders GROUP BY user_id
)
SELECT users.username, total_spent.total_spent
FROM users
JOIN total_spent ON users.id = total_spent.user_id;