数据库第三章
-
数据定义语言(Data-Definition Languagem DDL)
-
数据操纵语言(Data-Manipulation Languagem DML)
-
完整性(integrity)
-
视图定义(view definition)
-
事务控制(transaction control)
-
嵌入式SQL和动态SQL
-
授权
SQL数据定义
-
每个关系的模式
-
每个属性的取值类型
-
完整性约束
-
每个关系维护的索引集合
-
-
每个关系在磁盘上的物理存储结构
基本类型
-
char:：固定长度的字符串
-
varchar：可变长度的字符创
-
int： 整数类型
-
smallint： 小数类型
-
numeric(p, d)：定点数。这个数有p位数字，其中d为数字在小数点右边
-
real，double precision：浮点数与双精度浮点数
-
float(n)：精度至少为n为的浮点数
insert into instructor values(10211, 'Smith', 'Biology', 66000)
?
delete from student;
?
drop table r;
?
delete from r;
?
alter table r add A D
?
alter table r drop A
?
select dept_name from inistructor
?
select distinct dept_name from instructor
?
select all dept_name from instructor
并运算
union运算自动去除重复
(select course_id
from section
where semester = 'Fall' and year = 2009)
union
(select course_id
from section
where semester = 'Spring' and year = 2010);
如果我们想保留所有重复，就必须用union all 代替union
(select course_id
from section
where semester = 'Fall' and year = 2009)
union all
(select course_id
from section
where semester = 'Spring' and year = 2010);
交运算
(select course_id
from section
where semester = 'Fall' and year = 2009)
intersect
(select course_id
from section
where semester = 'Spring' and year = 2010);
差运算
(select course_id
from section
where semester = 'Fall' and year = 2009)
except
(select course_id
from section
where semester = 'Spring' and year = 2010);
聚集函数
-
ave
-
min
-
max
-
sum
-
count
嵌套子查询
找出在2009年秋季和2010年春季学期同事开课的所有课程
差找在2009年秋季开课的所有课程，看他们是否也在2010年春季开课的课程集合中的成员
(select course_id
from section
where semester = 'Spring' and year = 2010)
?
?
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 S, ID, S, name
from student as S
where not exists((select course_id
where dept_name = 'Biology')
except
(select T.course_id
from takes as T
where S.ID = T.ID))
重复元组存在性测试
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)
更新
update instructor set salary = salary * 1.05