1
2

SELECT * FROM employees;
SELECT name, salary FROM employees;

1
2
3
4
5

SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE department = 'IT' AND salary > 6000;
SELECT * FROM employees WHERE name LIKE 'A%';  -- 名字以A开头
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

1
2

SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY department ASC, salary DESC;

1

SELECT DISTINCT department FROM employees;

1
2
3
4
5
6
7

SELECT 
    COUNT(*) as total_employees,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary,
    SUM(salary) as total_salary
FROM employees;

1
2
3
4
5
6

SELECT 
    department,
    COUNT(*) as emp_count,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department;

1
2
3
4
5
6

SELECT 
    department,
    COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

1
2
3

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

1
2
3

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

1
2
3

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

1
2
3
4

SELECT e.name, d.department_name, p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN projects p ON e.id = p.leader_id;

1
2
3

SELECT name, salary,
    (SELECT AVG(salary) FROM employees) as company_avg
FROM employees;

1
2
3
4
5

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE budget > 100000
);

1
2
3
4
5
6

SELECT name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM projects p 
    WHERE p.leader_id = e.id
);

1
2
3
4
5
6
7

SELECT name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

1
2
3
4
5
6
7
8

WITH department_stats AS (
    SELECT 
        department,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM department_stats WHERE avg_salary > 6000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

WITH RECURSIVE org_chart AS (
    -- 基础情况
    SELECT employee_id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归情况
    SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

1
2
3
4
5
6
7
8

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
    LAG(salary) OVER (ORDER BY salary) as prev_salary
FROM employees;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- SQL Server
SELECT *
FROM (
    SELECT department, salary
    FROM employees
) AS SourceTable
PIVOT (
    AVG(salary)
    FOR department IN ([IT], [HR], [Finance])
) AS PivotTable;

1
2
3
4
5
6

INSERT INTO employees (name, salary, department) 
VALUES ('John Doe', 5000, 'IT');

-- 插入查询结果
INSERT INTO high_paid_employees (name, salary)
SELECT name, salary FROM employees WHERE salary > 8000;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'IT';

-- 使用子查询更新
UPDATE employees 
SET salary = (
    SELECT AVG(salary) FROM employees
) 
WHERE salary IS NULL;

1
2
3
4
5
6
7

DELETE FROM employees WHERE salary < 3000;

-- 使用子查询删除
DELETE FROM employees 
WHERE department_id IN (
    SELECT id FROM departments WHERE active = 0
);

1
2
3
4
5

-- PostgreSQL
INSERT INTO employees (id, name, salary)
VALUES (1, 'John', 5000)
ON CONFLICT (id) 
DO UPDATE SET name = EXCLUDED.name, salary = EXCLUDED.salary;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 如果一切正常
COMMIT;

-- 如果出现错误
ROLLBACK;

1
2

CREATE INDEX idx_employee_department ON employees(department);
CREATE INDEX idx_employee_name_salary ON employees(name, salary);

1
2
3
4
5

-- MySQL
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

-- SQL Server
SELECT * FROM employees ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

1
2
3
4
5
6

-- 查看执行计划
EXPLAIN SELECT * FROM employees WHERE salary > 5000;

-- SQL Server
SET STATISTICS TIME ON;
SELECT * FROM employees WHERE salary > 5000;

1
2
3
4
5
6
7

SELECT 
    UPPER(name) as upper_name,
    LOWER(name) as lower_name,
    LENGTH(name) as name_length,
    SUBSTRING(name, 1, 3) as name_prefix,
    CONCAT(first_name, ' ', last_name) as full_name
FROM employees;

1
2
3
4
5
6

SELECT 
    CURRENT_DATE as today,
    EXTRACT(YEAR FROM hire_date) as hire_year,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) as anniversary,
    DATEDIFF(CURRENT_DATE, hire_date) as days_employed
FROM employees;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    name,
    salary,
    CASE 
        WHEN salary < 3000 THEN 'Low'
        WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
        ELSE 'High'
    END as salary_level,
    COALESCE(bonus, 0) as bonus_amount
FROM employees;