【测试SQLite】测试SQLite支持的SQL语句分类 - ToB企服应用市场:ToB评测及商务社交产业平台

-- 创建用户表
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;

复制代码

-- 测试注释

复制代码

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 或错误

复制代码