select instructor.name, department.building
from instructor, department
where instructor.dept_name = department.dept_name;
自然连接
自然连接两个表相同属性名的值相等，然后连起来为一个新的元组
select name coure_id
from instructor natural join teaches;
# 指定属性
select name, title
from (instructor natural join teaches) join course using (course_id);
附加的基本运算
更名
select I.name as instructor_name
from instructor as I;
字符串运算
# 使用like来进行模式匹配，%匹配任意子串，_匹配任意一个字符
select dept_name
from departname
where building like '%a%';
排序
select *
from instructor
orderby salary desc;
集合运算
# 并
(select course_id
from course
where dept_name = 'cs')
union
(select course_id
from course
where dept_name = 'ee')
# 交
(select course_id
from course
where dept_name = 'cs')
intersect
(select course_id
from course
where dept_name = 'ee')
# 差
(select course_id
from course
where dept_name = 'cs')
except
(select course_id
from course
where dept_name = 'ee')
聚集函数
平均值：avg
最小值：min
最大值：max
总和：sum
计数：count
# 基本聚集
select avg(salary)
from instructor
where dept_name = 'cs';
# 分组聚集
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;
# having子句，having是在形成分组之后才起作用，且出现having子句中，但是没被聚集的属性必须出现在group by子句中。
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;
having avg(salary) > 42000;
# all代表所有，some代表至少有一个
select name
from instructor
where salary > all(some) (select salary
from instructor
where dept_name = 'biology');
空关系测试
# 找出选修了biology系开设的所有课程的学生
select s.id, s.name
from student as s
where not exists((select course_id
from course
where dept_name = 'biology'
except
(select t.course_id
from takes as t
where s.id = t.id));
from子句中的子查询
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
from instructor
groupby dept_name)
where avg_salary > 42000;
with子句
# with子句新建了一个临时表
with max_budget(value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget = max_budget.value;
高级一点的sql操作
连接表达式
而内连接就是普通连接
# 会保留student中所有的元组
select *
from student natural left join takes
# 创建一个函数
create function instructor_of(dept_name varchar(20))
return table(
id varchar(5),
name varchar(20))
return table
(select id, name
from instructor
where instructor_dept_name = dept_name );
# 调用一个函数
call instructor_of('cs');
