SQL在软件测试中的终极应用：不止查询，更用于造数与校验

sql 体验AI代码助手 代码解读复制代码-- 内连接查询订单与用户信息
SELECT o.order_id, o.amount, u.username, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

sql 体验AI代码助手 代码解读复制代码-- 分析用户订单行为模式
SELECT user_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) as running_total,
       AVG(amount) OVER (PARTITION BY user_id) as avg_order_amount
FROM orders
WHERE order_date >= '2023-01-01';

sql 体验AI代码助手 代码解读复制代码BEGIN TRANSACTION;
-- 插入测试用户
INSERT INTO users (username, email, created_at) 
VALUES ('test_user', 'test@example.com', NOW());
-- 插入测试订单
INSERT INTO orders (user_id, amount, status)
VALUES (0, 100.00, 'pending');
COMMIT;

sql 体验AI代码助手 代码解读复制代码-- 生成1000个测试用户
INSERT INTO users (username, email, created_at)
SELECT 
    CONCAT('user_', seq) as username,
    CONCAT('user_', seq, '@example.com') as email,
    DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY) as created_at
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 AS seq
    FROM 
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
         UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
         UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
         UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
) numbers
WHERE seq BETWEEN 1 AND 1000;

sql 体验AI代码助手 代码解读复制代码-- 创建符合真实分布的订单数据
INSERT INTO orders (user_id, amount, status, created_at)
SELECT 
    user_id,
    -- 金额符合正态分布
    ROUND(100 + RAND() * 900, 2) as amount,
    -- 状态分布：70%已完成，20%待处理，10%已取消
    CASE WHEN RAND() < 0.7 THEN 'completed'
         WHEN RAND() < 0.9 THEN 'pending'
         ELSE 'cancelled' END as status,
    -- 时间分布：过去30天内
    DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 30) DAY)
FROM users
WHERE created_at < DATE_ADD(NOW(), INTERVAL -7 DAY)
LIMIT 5000;

less 体验AI代码助手 代码解读复制代码-- 为用户数据脱敏
CREATE TABLE test_users AS
SELECT 
    id,
    CONCAT('user_', id) as username,
    CONCAT(MD5(email), '@example.com') as email,
    -- 保留手机号格式但替换数字
    CONCAT('1', FLOOR(RAND() * 10), FLOOR(RAND() * 10), '****', FLOOR(RAND() * 10), FLOOR(RAND() * 10), FLOOR(RAND() * 10), FLOOR(RAND() * 10)) as phone,
    created_at
FROM production_users
WHERE is_active = 1;

sql 体验AI代码助手 代码解读复制代码-- 检查外键约束完整性
SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- 检查订单金额一致性
SELECT 
    user_id,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as total_completed,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) as total_refunded,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) - 
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) as net_amount
FROM orders
GROUP BY user_id
HAVING net_amount != (
    SELECT balance FROM user_balances ub WHERE ub.user_id = orders.user_id
);

sql 体验AI代码助手 代码解读复制代码-- 验证优惠券使用规则
SELECT 
    c.coupon_code,
    c.discount_type,
    c.discount_value,
    COUNT(o.id) as usage_count,
    SUM(o.amount) as total_amount,
    -- 验证折扣是否正确应用
    CASE 
        WHEN c.discount_type = 'percentage' THEN 
            SUM(o.amount * c.discount_value / 100)
        ELSE 
            SUM(c.discount_value)
    END as total_discount
FROM coupons c
JOIN orders o ON o.coupon_id = c.id
WHERE o.created_at BETWEEN c.valid_from AND c.valid_to
GROUP BY c.id
HAVING total_discount != c.max_discount_amount;

sql 体验AI代码助手 代码解读复制代码-- 数据质量检查脚本
SELECT 
    'users' as table_name,
    'missing_email' as check_type,
    COUNT(*) as issue_count
FROM users 
WHERE email IS NULL OR email = ''
UNION ALL
SELECT 
    'orders',
    'negative_amount',
    COUNT(*)
FROM orders
WHERE amount < 0
UNION ALL
SELECT 
    'products',
    'duplicate_product_name',
    COUNT(*) - COUNT(DISTINCT product_name)
FROM products;

sql 体验AI代码助手 代码解读复制代码-- 清空并初始化测试数据
DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE username LIKE 'test_%');
DELETE FROM users WHERE username LIKE 'test_%';
-- 插入特定测试场景数据
INSERT INTO users (username, email, balance) VALUES
('test_user_1', 'test1@example.com', 1000.00),
('test_user_2', 'test2@example.com', 500.00);
INSERT INTO products (product_name, price, stock) VALUES
('test_product_a', 100.00, 10),
('test_product_b', 50.00, 5);

vbnet 体验AI代码助手 代码解读复制代码-- 验证订单处理结果
SELECT 
    CASE 
        WHEN o.status = 'completed' AND u.balance = original_balance - o.amount THEN 'PASS'
        WHEN o.status = 'failed' AND u.balance = original_balance THEN 'PASS'
        ELSE 'FAIL'
    END as test_result,
    o.*,
    u.balance as current_balance
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN (SELECT user_id, balance as original_balance FROM users_bak) ub ON u.id = ub.user_id
WHERE o.order_number = 'TEST_ORDER_123';

sql 体验AI代码助手 代码解读复制代码-- 使用存储过程生成大批量测试数据
DELIMITER $$
CREATE PROCEDURE GeneratePerformanceData(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num_records DO
        INSERT INTO performance_test (data_value, timestamp_col)
        VALUES (RAND() * 1000, NOW() - INTERVAL FLOOR(RAND() * 365) DAY);
        SET i = i + 1;

        -- 每1000条提交一次
        IF i % 1000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
END$$
DELIMITER ;
-- 调用存储过程生成10万条测试数据
CALL GeneratePerformanceData(100000);

vbnet 体验AI代码助手 代码解读复制代码-- 使用CTE进行多步骤数据验证
WITH 
user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_spent
    FROM orders
    WHERE created_at >= '2023-01-01'
    GROUP BY user_id
),
user_payments AS (
    SELECT user_id, SUM(amount) as total_paid
    FROM payments
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT 
    u.id,
    u.username,
    uo.order_count,
    uo.total_spent,
    up.total_paid,
    (uo.total_spent - up.total_paid) as balance_difference
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
JOIN user_payments up ON u.id = up.user_id
WHERE ABS(uo.total_spent - up.total_paid) > 0.01;

sql 体验AI代码助手 代码解读复制代码-- 创建临时表存储中间结果
CREATE TEMPORARY TABLE temp_test_scenario AS
SELECT 
    u.id as user_id,
    p.id as product_id,
    ROUND(RAND() * 100, 2) as order_amount
FROM users u
CROSS JOIN products p
WHERE u.username LIKE 'test_%'
AND p.product_name LIKE 'test_%'
LIMIT 100;
-- 使用临时表数据执行测试
INSERT INTO orders (user_id, product_id, amount, status)
SELECT user_id, product_id, order_amount, 'pending'
FROM temp_test_scenario;
-- 验证数据是否正确插入
SELECT COUNT(*) as orders_created
FROM orders o
JOIN temp_test_scenario tts ON o.user_id = tts.user_id AND o.product_id = tts.product_id;

sql 体验AI代码助手 代码解读复制代码START TRANSACTION;
-- 设置测试初始状态
UPDATE account SET balance = 1000.00 WHERE account_number = 'TEST_001';
-- 执行测试操作
UPDATE account SET balance = balance - 100.00 WHERE account_number = 'TEST_001';
INSERT INTO transaction_log (account_number, amount, type) 
VALUES ('TEST_001', 100.00, 'withdrawal');
-- 验证结果
SELECT 
    balance,
    CASE WHEN balance = 900.00 THEN 'PASS' ELSE 'FAIL' END as test_result
FROM account
WHERE account_number = 'TEST_001';
ROLLBACK; -- 回滚事务，恢复测试环境

sql 体验AI代码助手 代码解读复制代码-- 检查当前数据库环境
SELECT DATABASE() as current_database;
-- 确认是测试环境后再执行操作
SET @is_test_env := (SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'test_company');
IF @is_test_env > 0 THEN
    -- 在测试环境中执行数据构造
    INSERT INTO test_data (...)
    VALUES (...);
ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not in test environment!';
END IF;

sql 体验AI代码助手 代码解读复制代码-- 创建专用测试账号
CREATE USER 'test_engineer'@'%' IDENTIFIED BY 'secure_password';
-- 授予必要权限
GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'test_engineer'@'%';
GRANT EXECUTE ON PROCEDURE test_db.GenerateTestData TO 'test_engineer'@'%';
-- 禁止危险操作
REVOKE DROP, ALTER, GRANT OPTION ON test_db.* FROM 'test_engineer'@'%';