1.Natural Join
自然连接操作对两个关系进行操作,并生成一个关系作为结果
看代码,1.要求是找到教员教的课程
select name, course_id
from instructor natural join teaches;
这俩代码一个意思
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;
注意:
- 自然连接只考虑在两种关系的模式中出现的属性上具有相同值的元组对
- from子句中可以有多个表natural join
- 小心不相关的相同名称的属性被错误地等同
Example
列出教师的姓名和他们所教授课程的名称
- instructor表
- teaches表
- courses表
代码:
- Incorrect version
(makes course.dept_name=instructor.dept_name)
select name, title
from instructor natural join teaches natural join course;
- Correct version
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;
select name, title
from (instructor natural join teaches)
join course using(course_id);
join…using…
- 指定一个属性名列表。
- 两个输入都必须具有指定名称的属性
(比如上面代码中制定了course_id)
比如:
r1 join r2 using(A1, A2)
- 操作类似于r1 natural join r2,
- 但要一对来自r1的元组t1
来自r2的元组t2
匹配t1.A1 = t2.A1 、t1.A2 = t2.A2 - 即便存在t1.A3 == t2.A3 但不在考虑范围
2.Rename Operation
用 as
来重命名,(as是可以omitted)
和python 中导入头文件的用法类似:
from bs4 import BeautifulSoup as bs
SQL语句:
– 重命名表达式
select ID, name, salary/12 as monthly_salary
from instructor
– 重命名表
select distinct T. name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’
– Keyword as is optional and may be omitted
instructor as T ≡ instructor T
3.String Operations
使用 like
表达式 | 含义 |
---|---|
% | 通配符,匹配任何子字符串 |
_ . | (下划线、小数点分别都是)匹配任意字符 |
’%dar%' | 包含子串“dar”的所有串 |
like ‘100 %’ escape '\‘ | 匹配字符串“100%”(转义符‘\’, 使 “%” 作被处理对象) |
‘_ _ _’ | 匹配任何恰好长度为3的字符串 |
… |
有点像import re的正则表达
Example
找到名字中包含子串“dar”的所有教师的名字
select name
from instructor where name like '%dar%'
4. * denote all attributes
The asterisk symbol “ * ” can be used in the select clause to denote “all attributes.”
Select instructor.*
From instructor, teaches
Where instructor.ID=teaches.ID
5. order by、降序desc,升序asc
order by: 给出一种顺序投影属性。
desc for descending order
asc for ascending order.
Example
按薪资降序列出整个教员关系
薪水相同,按名字升序排列
select *
from instructor
order by salary desc, name asc;
6.where子句的谓词(predicates)
between
下面俩代码 等价的
select name
from instructor
where salary between 90000 and 100000;
select name
from instructor
where salary <= 100000 and salary >= 90000;
(,) = (,)、 (,) <= (,) …
下面俩代码 也等价的
select name, course_id
from instructor, teaches
where instructor.ID= teaches.ID and dept_name = 'Biology';