第三章

基本类型

char(n)固定长度的字符串。

varchar(n)可变长度的字符串，最大长度n。

int 整数。

smallint 小整数。

numeric(p,d)定点数，p位数字其中d位在小数点右边。

float(n)精度至少为n的浮点数。

创建一个表:

create table department
(
    dept_name varchar(20), #最大长度为20的字符串
    building varchar(15), # 最大长度为15的字符串
    budget numeric(12, 2), # 12位数，2位小数
    primary key (dept_name) # 主码是dept_name，可选
);

sql查询的基本结构：select、from、where。查询后会出现重复。

select distinct dept_name # distinct强行去除重复
from instructor;
select all dept_name # all显式指明不去除重复
from instructor;

1
2
3

select name
from instructor 
where dept_name = 'Music' and salary > 70000;

多关系查询

1
2
3

select name, instructor.dept_name, building
from instructor,department
where instructor.dept_name=department.dept_name;

select子句用于列出查询结果中所需要的属性。

from子句是一个查询求值中需要访问的关系列表。

where子句是一个作用在from子句中关系的属性上的谓词。

通过from子句定义了一个列出的关系上的笛卡尔积。若没有where则会输出该笛卡尔积。

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

重命名关系

1
2
3

select T.name, S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;

比较运算符

select dept_name
from department 
where building like '%Watson%'; # 匹配子串包含Watson的字符串
like 'ab\%cd%' escape '\' # 用escape转义，匹配以ab%cd开头的字符串

显示次序

select name 
from instructor
where dept_name = 'Physics'
order by name;

##　升降序

1
2
3

select *
from instructor
order by salary desc, name asc; # 工资降序排列，若工资相同则姓名升序排列。

where子句

1
2
3

select name
from instructor
where salary between 90000 and 100000;

记号比较

1
2
3

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

集合运算

采用union,intersect,except对应并，交，差

(
    select course_id
    from section
    where semester = 'Fall' and year = 2009
)
union
(
    select course_id
    from section
    where semester = 'Spring' and year = 2010
)

union自动去重。

空值

空值与true,false对应，为unknown。空值与任何值运算为空值。

聚集函数

平均值：avg

最小值：min

最大值：max

总和：sum

计数：count

1
2
3

select avg(salary)
from instructor
where dept_name = 'Music';

分组聚集

group by

1
2
3

select dept_name, ave(salary) as avg_salary
from instructor
group by dept_name;

查询每个学科的平均工资，每个学科被当作一个分组。

任何没有出现在group by子句中的属性如果出现在select中的话，只能出现在聚集函数内。

having子句

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

最先根据from子句计算出一个关系。

如果出现where子句，where子句中的谓词将应用到from子句计算的结果上。

如果出现group by子句，满足where谓词的元组通过group by子句形成分组。

如果出现having子句，不满足having子句谓词的分组将被抛弃。

select子句利用剩下的分组产生出查询结果中的元组。

嵌套查询

select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
course_id in (select course_id
              from section
              where semester = 'Spring' and year = 2010);

集合的比较

select name
from instructor 
where salary > some(select salary
                   from instructor
                   where dept_name = 'Biology');

some表示至少比某一个大。

from子查询替代having

select dept_name, avg_salary
from(select dept_name,avg(salary) as avg_salary
    from instructor
    group by dept name)
    as dept_avg(dept_name, avg_salary) # 重命名子查询的结果
where avg_salary > 42000;

with子句

with max_budget(value) as 
	(select max(budget)
    from department)
select budget
from department, max_budget
where department.budget = max_budget.value;

with子句定义临时关系max_budget，在随后的查询中使用。

数据库的修改

1 2	delete from r where P;

delete只能作用于一个关系。

1 2	insert into course values('CS-437','Database Systems', 'Comp. Sci',4);

查询插入

insert into instructor
	select ID, name, dept_name, 18000
	from student
	where dept_name = 'Music' and tot_cred > 144;

每个音乐系的学分大于144的学生加入教师队伍，工资18000.

更新

1
2
3

update instructor
set salary = salary*1.05
where salary < 70000;

sha & CTF记录

数据库复习

第三章