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

-- 内连接查询订单与用户信息SELECT o.order_id, o.amount, u.username, u.emailFROM orders oINNER JOIN users u ON o.user_id = u.idWHERE o.status = 'pending';

-- 分析用户订单行为模式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_amountFROM ordersWHERE order_date >= '2023-01-01';

BEGIN TRANSACTION;-- 插入测试用户INSERT INTO users (username, email, created_at) VALUES ('test_user', 'test@example.com', NOW());-- 插入测试订单INSERT INTO orders (user_id, amount, status)VALUES (LAST_INSERT_ID(), 100.00, 'pending');COMMIT;

-- 生成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_atFROM (    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) numbersWHERE seq BETWEEN 1 AND 1000;

-- 创建符合真实分布的订单数据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 usersWHERE created_at < DATE_ADD(NOW(), INTERVAL -7 DAY)LIMIT 5000;

-- 为用户数据脱敏CREATE TABLE test_users ASSELECT     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_atFROM production_usersWHERE is_active = 1;

-- 检查外键约束完整性SELECT o.*FROM orders oLEFT JOIN users u ON o.user_id = u.idWHERE 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_amountFROM ordersGROUP BY user_idHAVING net_amount != (    SELECT balance FROM user_balances ub WHERE ub.user_id = orders.user_id);

-- 验证优惠券使用规则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_discountFROM coupons cJOIN orders o ON o.coupon_id = c.idWHERE o.created_at BETWEEN c.valid_from AND c.valid_toGROUP BY c.idHAVING total_discount != c.max_discount_amount;

-- 数据质量检查脚本SELECT     'users' as table_name,    'missing_email' as check_type,    COUNT(*) as issue_countFROM users WHERE email IS NULL OR email = ''UNION ALLSELECT     'orders',    'negative_amount',    COUNT(*)FROM ordersWHERE amount < 0UNION ALLSELECT     'products',    'duplicate_product_name',    COUNT(*) - COUNT(DISTINCT product_name)FROM products;

-- 清空并初始化测试数据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);

-- 验证订单处理结果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_balanceFROM orders oJOIN users u ON o.user_id = u.idJOIN (SELECT user_id, balance as original_balance FROM users_bak) ub ON u.id = ub.user_idWHERE o.order_number = 'TEST_ORDER_123';

-- 使用存储过程生成大批量测试数据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);

-- 使用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_differenceFROM users uJOIN user_orders uo ON u.id = uo.user_idJOIN user_payments up ON u.id = up.user_idWHERE ABS(uo.total_spent - up.total_paid) > 0.01;

-- 创建临时表存储中间结果CREATE TEMPORARY TABLE temp_test_scenario ASSELECT     u.id as user_id,    p.id as product_id,    ROUND(RAND() * 100, 2) as order_amountFROM users uCROSS JOIN products pWHERE 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_createdFROM orders oJOIN temp_test_scenario tts ON o.user_id = tts.user_id AND o.product_id = tts.product_id;

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_resultFROM accountWHERE account_number = 'TEST_001';ROLLBACK; -- 回滚事务，恢复测试环境

-- 检查当前数据库环境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;

-- 创建专用测试账号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'@'%';