语法:
select 需要的属性
from 表1,表2
where 表1.属性 = 表2.属性(这里面的属性需要在两个表里面都一样)(这里的 表1.和 表2.不能省略。)
select dept_name
from course,section
where course.course_id = section.course_id and dept_name = 'Comp.Sci'
select
from 表1 join 表2 on 表1.属性 = 表2.属性
还可以写成这样。效率比较高
Select *
From emp join dept on emp.dpetno = dept.deptno
Where loc = 'new York'
from 表1 natural join 表2 on 条件
select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = 'Comp.Sci.'
语法:
select 属性1 from 表1 where 条件1
集合运算符
select 属性2 from 表2 where 条件2
数值 + null, null <> null null = null 结果是 unknown 所以选择值是unknown的:
where 属性 is null
注意:空值参与排序。
unknown 和 true 的逻辑运算以及结果:
OR: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
– AND: (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
– NOT: (not unknown) = unknown
– “P is unknown” evaluates to true if predicate P evaluates to unknown
小练习:
查询先修课的先修课的名字(自连接)
use university
select distinct P.prereq_id
from prereq as C,prereq as P
where C.prereq_id = P.course_id
查询两个工资相等的老师
select distinct A.name,B.name
from instructor as A,instructor as B
where A.salary = B.salary and A.name <> B.name