SQL

语法

alt text

SELECT

distinct 去重

1
2


select `distinct/all` dept_name 
from instructor

四则运算和重命名

1

select ID, name, salary/12 as monthly_salary

FROM

natural join 自然连接

1
2
3
4


select name, course_id 
from instructor natural join teaches 
where instructor. dept_name = ‘Art’
// 等价于加上 where instructor.ID = teaches.ID and

WHERE

SQL allows the use of the logical connectives and, or, and not The operands of the logical connectives can be expressions involving the comparison operators <, <=, >, >=, =, and <>.（以及between A and B 用法）

Tuple comparison

1
2
3


select name, course_id 
from instructor, teaches 
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

Tuple Variables

涉及到对instructor关系中属性salary的不同值的比较

1
2
3


select distinct T.name 
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’

执行顺序

FROM - 从表中获取数据

WHERE - 对原始行数据进行筛选

GROUP BY - 对筛选后的数据进行分组

HAVING - 对分组后的聚合结果进行筛选

SELECT - 选择要显示的列

在 WHERE 子句中不能直接使用 COUNT 等聚合函数，因为执行顺序的问题。

1
2
3
4
5


-- ✅ 正确：使用HAVING对分组结果进行筛选
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;  -- 这里COUNT()在每组内计算

CTE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


WITH 
department_stats AS (
    SELECT 
        department,
        AVG(salary) as avg_salary,
        COUNT(*) as employee_count
    FROM employees
    GROUP BY department
),
high_avg_departments AS (
    -- 这个CTE引用了前一个CTE
    SELECT 
        department,
        avg_salary
    FROM department_stats
    WHERE avg_salary > 6000
)
-- 主查询
SELECT 
    e.name,
    e.salary,
    h.avg_salary as department_avg
FROM employees e
JOIN high_avg_departments h ON e.department = h.department
WHERE e.salary > h.avg_salary;

GROUP BY

Attributes in select clause outside of aggregate functions must appear in group by list（必须要有聚合运算）错误示例：

1
2
3
4


/* erroneous query */
select dept_name, ID, avg (salary) -- ID 不在聚合属性中
from instructor
group by dept_name

正确用法：（可以有多个聚合运算）

product	category	sales_amount
A	Electronics	100
B	Electronics	150
A	Electronics	200
C	Clothing	80
B	Electronics	120

1
2
3
4
5
6


SELECT 
    category,
    COUNT(*) as transaction_count,
    SUM(sales_amount) as total_sales
FROM sales
GROUP BY category;

category	transaction_count	total_sales
Electronics	4	570
Clothing	1	80

常用聚合函数

1
2
3
4
5
6
7
8
9


SELECT 
    department,
    COUNT(*) as employee_count,      -- 计数
    AVG(salary) as avg_salary,       -- 平均值
    SUM(salary) as total_salary,     -- 求和
    MAX(salary) as max_salary,       -- 最大值
    MIN(salary) as min_salary        -- 最小值
FROM employees
GROUP BY department;

ORDER BY

1
2


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