关系数据库01
关系指代表，元组指代行，属性指代列。元组是一组值的序列。
超码(superkey)是一个或多个属性的集合。超码可以使在一个关系中唯一地标识一个元组。超码的任意超集也是超码，候选码是最小超码。
主码(primary key)代表被选中的、主要用来在一个关系中区分不同元组的候选码。
一个关系模式(r1)可能在它的属性中包括另一个关系模式(r2)的主码。这个属性在r1上被称为参照r2的外码。r1也称外码依赖的参照关系。
以后会用到的关系
- department(dept_name, building, budget)
- course(course_id, title, dept_name, credits)
- student(ID, name, dept_name, tot_cred)
- advisor(s_id, i_id)
- takes(ID, course_id, sec_id, semester, year, grade)
- classroom(building, room_number, capacity)
- time_slot(time_slot_id, day, start_time, end_time)
SQL查询语言
基本类型(域)
- char(n)：长度n，长度不够会追加空格
- varchar(n)：可变长度字符串，最大长度n
- int
- smallint
- numeric(p,d)：定点数，精度由用户指定，共p位数字(加上符号位)，d位小数点后精度--numeric(3,1)只能存储44.5，不能存储444.5
- real, double precision：浮点数和双精度浮点数
- float(n)：精度至少为n的浮点数
create table命令：
create table r
(A1 D1,
A2 D2,
...
An Dn,
<完整性约束1>，
...
<完整性约束k>);
注意使用分号结束。
完整性约束：
- primary key(A1,A2,...)
- foreign key(A1,A2,...) references: 给定属性上的取值必须对应于关系s中某元组在主码属性上的取值
- not null:在该属性上不允许空值
create table teachers
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id, sec_id, semester, year) references sections,
foreign key (ID) references instructor);
新创建的关系是空的，使用insert插入。
insert into instructor
values (10211, 'Smith', 'Biology', 66000);
delete删除元组
delete from student;
drop table删除关系
drop table r; --删除被去掉关系的所有信息
delete from r; --保留关系r，删除r中所有元组
alter table：给r添加列A，属性的域为D
alter table r add A D;
alter table r drop A;
SQL查询基本结构由select、from和where组成
- select列出需要查询的列
- from是需要访问的关系表
- where是from子句中的限定
- 运算顺序为：from、where、select
1. 单关系查询
select name
from instructor
where dept_name = 'Comp.Sci.' and salary > 70000;
在select后面可以加distinct去除重复，加all显示指明不去重
2. 多关系查询
-- dept_name出现在 instructor和 department中
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;
3. 自然连接
自然连接运算作用两个表并产生一个表作为结果。
自然连接选出同名属性中相等的元组，进行投影去重组合，或者说外连接。
select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;
-- 可以写成
select name, course_id
from instructor natural join teaches;
-- instructor和teaches在共同属性ID上的取值相同，且只有ID
需要注意的是，自然连接多个表时，需要同时满足多个表中的属性都相等
select name, title
from instructor natural join teaches natural join course;
在instructor和teaches的自然连接结果，他们自然连接的结果会导致属性的增加，再与course进行自然连接时，会需要在两个属性(dept_name, course_id)上取值相同，而下面这种形式则不一样
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;
这种情况会先计算instructor和teaches根据ID属性的自然连接，然后将自然连接的结果与course_id相匹配。所以，用户可以指定需要哪些列相等：
select name, title
from (instructor natural join teaches ) join course using (course_id);
其他运算
- 更名运算(old-name as new-name)
--改变输出的属性名
select name as instructor_name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;
--改变关系名
select T.name, S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;
- 字符串运算
SQL对大小写敏感(在MySQL和SQL Server中不区分大小写)
函数upper(大写),lower(小写),trim(去空格)
使用like实现字符串模式匹配：
1.(%):匹配任意字符串。2.(_)：匹配任意一个字符。
select dept_name
from department
where building like '%Watson%';
--找出建筑名中包含'Watson'的系名
--也可以在like后使用escape定义转义字符
--like 'ab \% cd%' escape '\'
- 排序
--order by默认圣墟
select *
from instructor
order by salary dese, name asc;
- where子句谓词
where salary between 90000 and 100000;
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
集合运算
使用union、intersect、except对关系进行并、交、差运算。
- 三种操作均自带去重，保留重复则在后面加all(union all)
SQL中涉及到空值的运算均为 unknown
- 1<null 为unknown
- true and unknown 为unknown
- false and unknown 为false
- 如果where计算结果为false或unknown，则不能加入到结果
聚集函数
- avg,min,max,sum,count
- group by：分组聚集，group by指定属性相同的为一组。
- having：针对group by中构成的分组
- 如果出现了where，则只应用在from的结果关系上
- 如果出现了group by, 满足where的元组通过group by分组
- 如果出现了having, 将应用到每个分组
- 对空值的聚集操作，会忽略空值
select course_id, semester, year, sec_id, ave(tot_cred)
from takes natural join student
where year = 2009
group by course_id, semester, year, sec_id
having count(ID) >= 2;
-- 查询对于2009年的每个课程段，如果该课程段有至少两名同学选课，找出选修该课程段的所有学生的总学分的平均值
嵌套子查询
一个select-from-where子查询嵌套在另一个查询where语句中
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);
-- 找出在2009年秋和2010年春同时开课的所有课程，另一种方式是使用intersect
1.集合的比较
1.1比较时可以使用some和all
- some:至少比某一个大(>some)
- all:大于全部(>all)
1.2测试子查询中是否存在元组
- exists:子句的返回值是bool值
--找出选修了biology系开设的所有课程的学生
--not exists(B except A)：关系A包含关系B
select S.ID, S.name
from student as S
where not exists((select course_id
from course
where dept_name = 'Biology')
except(
select T.course_id
from takes as T
where S.ID = T.ID));
in、not in和exists、not exists的区别：
- exists后面一般都是子查询，当子查询返回行数时，exists返回true
- in的查询效率高于exists，in后面的查询是返回结果集的
- 执行顺序：exists先执行外部查询，外部查询每一行进行子查询；in先执行子查询返回结果集
- not in 只有当子查询中，select后的字段有not null时用not in
- not in 的执行顺序是查询每条记录，not exists是根据索引查询，不会按条查询
- not exists查询效率高于not in
--当表中cid存在null值，not in 不对空值进行处理
select * from class
where cid not in
(select distinct cid from stu where cid is not null)
--查询那些班级中没有学生的
select * from class2
where not exists
(select * from stu1 where stu1.cid = class2.cid)
3.使用unique查询是否存在重复元素
- unique可以在创建表时使用，限制重复
4.with子句提供临时定义关系的方法
--定义max_budget,找出具有最大预算值的系
with max_budget(value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department, budget = max_budget.value;
2.数据库的修改
2.1删除
delete from r
where P;
2.2插入
--让Music系修满144学分的学生成为Music系的教师
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_cred > 144;
2.3更新
--update 表 set 列 = 新值 where 列名称 = 某值
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
