-- 1. 连接数据库
USE test_database;

-- 2. 查看表结构
DESCRIBE users;

-- 3. 简单查询
SELECT * FROM users LIMIT 10;

-- 4. 条件查询
SELECT username, email FROM users WHERE age > 25;

-- 5. 插入测试数据
INSERT INTO users (username, email, age) VALUES ('test_user', 'test@example.com', 28);

💡 测试工程师专用技巧

SQL在测试中的常见应用场景

-- 1. 准备测试数据
INSERT INTO test_users (username, password, role) VALUES
('admin_user', 'admin123', 'admin'),
('normal_user', 'user123', 'user'),
('guest_user', 'guest123', 'guest');

-- 2. 验证测试结果
SELECT COUNT(*) as order_count FROM orders WHERE user_id = 123 AND status = 'completed';

-- 3. 清理测试数据
DELETE FROM test_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 4. 数据一致性检查
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

测试数据管理最佳实践

-- 1. 使用事务确保数据一致性
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('test_user_1', 'test1@example.com');
INSERT INTO profiles (user_id, full_name) VALUES (LAST_INSERT_ID(), 'Test User One');
COMMIT;

-- 2. 创建测试数据快照
CREATE TABLE users_backup AS SELECT * FROM users WHERE username LIKE 'test_%';

-- 3. 批量数据操作
UPDATE users SET status = 'inactive' WHERE last_login < DATE_SUB(NOW(), INTERVAL 30 DAY);

🔧 实用工具与技巧

常用SQL命令速查表

-- 数据库操作
SHOW DATABASES;                    -- 查看所有数据库
CREATE DATABASE test_db;           -- 创建数据库
USE test_db;                       -- 使用数据库
DROP DATABASE test_db;             -- 删除数据库

-- 表操作
SHOW TABLES;                       -- 查看所有表
DESCRIBE table_name;               -- 查看表结构
SHOW CREATE TABLE table_name;      -- 查看建表语句

-- 索引操作
SHOW INDEX FROM table_name;        -- 查看表索引
CREATE INDEX idx_name ON table_name(column);  -- 创建索引
DROP INDEX idx_name ON table_name; -- 删除索引

-- 性能分析
EXPLAIN SELECT * FROM users WHERE age > 25;  -- 查看执行计划
SHOW PROCESSLIST;                  -- 查看当前进程

测试环境配置技巧

-- 1. 设置SQL模式（严格模式，便于发现问题）
SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 2. 查看数据库配置
SHOW VARIABLES LIKE 'innodb%';
SHOW STATUS LIKE 'Threads%';

-- 3. 性能监控
SELECT
    TABLE_SCHEMA as database_name,
    TABLE_NAME as table_name,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as table_size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test_database'
ORDER BY table_size_mb DESC;

📚 测试场景实战案例

案例1：API测试数据准备

-- 创建API测试用户
CREATE TABLE api_test_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    api_key VARCHAR(100) UNIQUE NOT NULL,
    rate_limit INT DEFAULT 1000,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入不同权限级别的测试用户
INSERT INTO api_test_users (username, api_key, rate_limit, status) VALUES
('admin_api_user', 'admin_key_123', 10000, 'active'),
('normal_api_user', 'normal_key_456', 1000, 'active'),
('limited_api_user', 'limited_key_789', 100, 'active'),
('suspended_api_user', 'suspended_key_000', 0, 'suspended');

案例2：性能测试数据分析

-- 创建性能测试结果表
CREATE TABLE performance_test_results (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(100),
    concurrent_users INT,
    response_time_ms INT,
    throughput_rps DECIMAL(10,2),
    error_rate DECIMAL(5,2),
    test_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 分析性能测试结果
SELECT
    test_name,
    concurrent_users,
    AVG(response_time_ms) as avg_response_time,
    MAX(response_time_ms) as max_response_time,
    AVG(throughput_rps) as avg_throughput,
    AVG(error_rate) as avg_error_rate
FROM performance_test_results
WHERE test_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY test_name, concurrent_users
ORDER BY test_name, concurrent_users;

案例3：自动化测试数据验证

-- 创建测试结果验证视图
CREATE VIEW test_data_validation AS
SELECT
    'User Count Check' as validation_type,
    CASE
        WHEN COUNT(*) BETWEEN 1000 AND 2000 THEN 'PASS'
        ELSE 'FAIL'
    END as result,
    COUNT(*) as actual_count,
    '1000-2000' as expected_range
FROM users
UNION ALL
SELECT
    'Order Status Check' as validation_type,
    CASE
        WHEN COUNT(*) = 0 THEN 'PASS'
        ELSE 'FAIL'
    END as result,
    COUNT(*) as actual_count,
    '0' as expected_range
FROM orders WHERE status = 'invalid';

-- 执行数据验证
SELECT * FROM test_data_validation;

🎉 进阶学习方向

数据库设计：学习范式理论，设计高效的测试数据模型
SQL优化：掌握执行计划分析，优化复杂查询
存储过程：编写可复用的数据库逻辑
数据库监控：学习性能监控和故障诊断
NoSQL数据库：扩展到MongoDB、Redis等非关系型数据库

🤝 贡献指南

如果你有好的SQL实践经验或者发现了文档中的问题，欢迎提交PR或者Issue！

记住： SQL不仅是查询语言，更是测试工程师分析和验证数据的强大工具。掌握SQL，让数据为你的测试工作服务！

SQL数据库技术

目录