周末无事水文章,期末备考的总结资料
第三章 SQL
3.1 SQL查询语言概况
3.2 SQL数据定义
3.2.1 基本类型
- char(n):固定长度n的字符串(长度不够自动补空格)
- varchar(n):可变长度的最大长度为n的字符串
- int:整型
- smallint:小整型
- numeric(p,d):定点数
- real,double precision:浮点数与双精度浮点数
- float(n):精度至少为n的浮点数
3.2.2 基本定义模式
- 建表
- 修改/删除表
–delete from r
,意思是删除表中所有的内容
3.3 SQL查询的基本结构
3.3.1 单关系查询
- 强行删除重复:
select distinct dept_name from instructor;
- 显示指明不去除重复:
select all dept_name from instructor;
3.3.2 多关系查询
select A1, A2, ..., An
from r1, r2, ..., rm
where P;
3.3.3 自然连接
select name, title
from instructor
natural join teaches natural join course;
3.4 附加的基本运算
3.4.1 更名运算
- 变量名:
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.';
3.4.2 字符串运算
- 包含子串:
select name from instructor where name like '%in%' ;
3.4.3 select子句红的属性说明
3.4.4 排序元组的显示次序
select distinct name
from instructor
order by name;
3.4.5 where子句谓词
3.5 集合运算
3.5.1 并运算
select course_id
from section
where semester = 'Fall' and year = 2009
union
select course_id
from section
where semester = 'Spring' and year = 2010;
3.5.2 交运算
select course_id
from section
where semester = 'Fall' and year = 2009
intersect
select course_id
from section
where semester = 'Spring' and year = 2010;
3.5.3 查运算
select course_id
from section
where semester = 'Fall' and year = 2009
except
select course_id
from section
where semester = 'Spring' and year = 2010;
3.6 空值
3.7 聚集(Aggregate)函数
3.7.1 基本聚集
- avg: 平均值
- min: 最小值
- max: 最大值
- sum: 值的求和
- count: 值的数量
3.7.2 分组聚集
- group by语法:
select dept_name, avg (salary) from instructor group by dept_name;
3.7.3 having子句
- 分组限定的条件:
select dept_name, avg (salary) from instructor group by dept_name having avg (salary) > 42000;
3.7.4 对空值和布尔值的聚集
3.8 嵌套子查询
3.8.1 集合成员资格
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);
3.8.2 集合的比较
select name
from instructor
where salary > some
(select salary
rom instructor
where dept_name = 'Biology');
- 注:其中some表示一些,也可被all替代,意思为全部
3.8.3 空关系测试
select course_id
from section as S
where semester = ’Fall’ and year = 2009 and
exists (select *
from section as T
where semester = ’Spring’ and year= 2010 and S.course_id = T.course_id);
- 注:子查询为非空时返回true值
3.8.4 重复元组存在性测试
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id= R.course_id
and R.year = 2009);
- 注:子查询结果中没有重复的元组,unique结构将返回true值
3.9 数据库的修改
3.9.1 删除
delete from r where P;
3.9.2 插入
insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
3.9.3 更新
update instructor
set salary = salary * 1.03
where salary > 100000;