数据库系统概念

数据库系统概念

引言

数据视图

1. 物理层：最低层次的抽象，描述数据实际上是怎么样存储的。
2. 逻辑层：描述数据库中存储什么数据以及这些数据间存在什么关系。
3. 视图层：描述数据数据库的某个部分。
   

实例与模式

1. 实例：特定时刻存储在数据库中的信息的集合；
   a) 模式的具体值；
   b) 反映数据库某一时刻的状态；
   c) 同一模式可以有很多实例；
   d) 实例随着数据的更新而变动。

2. 模式：数据库的总体设计数据库逻辑结构和特征的描述；是类型的描述；反映数据的结构和联系；模型相对稳定；

   a) 物理模式：物理层 内模式 存储模式
   b) 逻辑模式：逻辑层 全局模式 概念模式
   c) 子模式：视图层 外模式 用户模式 局部模式
   物理数据的独立性：不依赖于物理模型。
   一个数据库只有一个内模式

3. 二级映射

• 外模式/模式映射：模式变，外模式不变
• 模式/内模式映射：内模式变，模式不变

数据模型

数据+数据联系+数据语义+一致性约束
a) 关系模型
b) 实体联系模型
c) 基于对象的数据模型
d) 半结构化数据模型…

数据库语言

1. 数据操纵语言（DML）
   a) 过程化
   b) 声明式
2. 数据定义语言（DDL）
   a) 域约束
   b) 参照完整性
   c) 断言
   d) 授权

数据库设计

需求分析——概念设计——逻辑设计——物理设计

SQL

1. 创建关系

create table section(
	course_id varchar(20),
	sec_id varchar(9) not null,
	year numeric(4,0),
	semester varchar(6),
	building varchar(16),
	room_number varchar(11),
	primary key (course_id, sec_id ,semester,year),
	foreign key(course_id) references courses(course_id)
);

主键也可以合并属性一起写

create table course(
	course_id varchar(20) primary key,
	title varchar(20),
	dept_name varchar(10),
	credits numeric(2,0),
	foreign key (dept_name) references department(dept_name)
	);

2. alter table
为已有关系增加属性，新属性取值为null。

alter table r add A D

关系为r，A为增加属性的名字，D为增加属性的域。

e.g.

alter table instructor add age int;

去掉属性为：

alter table r drop A;
alter table instructor drop age;

3. delete 和drop
delete用于从关系中删除元祖

delete from student

删除student关系中所有元组，但保留关系；
drop table r，直接删掉r，删掉后只能用create table重建。
从instructor中删除位于Waston大楼的系工作的教师。

delete from instructor
where dept_name in (select dept_name
from department
where building='Waston');

删除工资低于大学平均工资的教师

delete from instructor
where salary <(
select avg(salary)
from instructor);

4. insert

insert into department values('Biology','Warson','90000');
insert into instructor values ('22',null, 'Physics',9000);

想让Music系每个修满144学分的学生成为Music系的教师，工资为18000.

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

5. update

update instructor
set salary=salary*1.05
where salary<(select avg(salary)from instructor)

update具有case结构

update instructor
set salary = 
case
	when salary<=10000 then salary*1.05
	else salary*1.03
end;

5. select

select dept_name from instructor;

• 此处，dept_name会出现重复，去重的方法是使用distinct。

select distinct dept_name from instructor;

• select可以带有运算符

select salary*1.1 from instructor;

• select使用where进行满足特定谓词的元组

select name from instructor where depart_name='Sci' and salary >5000;

between表示一个值在两者中间

select name
from instructor
where salary between 9000 and 10000;

SQL允许使用()

select name
from instructor,teachers
where (instructor.ID,dept_name)=(teacher.ID,'Biology');

• *表示所有的属性

select instructor.*
from instructor,teachers
where instructor.ID=teacher.ID;

表示instructor中所有属性都被选中，select *表示from子句结果关系的所有属性都被选中。

• 排序

select *
from instructor
order by salary desc, name asc;

order by使得查询结果按照顺序显示，默认升序asc，desc降序。

6. natural join自然连接
对于大学中所有讲课的教师，找出他们的姓名及所讲述的所有课程标识。

select name, course_id
from instructor, teachers
where instructor.ID=teacher.ID;

以上是通过笛卡尔积把instructor和teachers组合起来，可以改成

select name, course_id
from instructor natural join teachers;

自然连接会把共同属性ID取值相同的找出来，找出名字一样的。
为了避免不必要的相等属性，使用join using。这里的join其实相当于inner join。

select name,title
from (instructor natural join teachers)join course using course_id;

或者使用join on

select name, title
from student join takes on student.ID=takes.ID

7. as更名

select name,course_id
from instructor,teachers
where instructor.ID = teachers.ID;

as可以用在select中，也可以用在from中，上式可以改为：

select name as instructor_name, course_id
from instructor,teachers
where instructor.ID = teachers.ID;

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

当需要比较同一个关系中的元组的情况。

select distinct T.name
from instructor as T, instructor as S
where T.salary >S.salary;

8. 字符串运算

• upper转换成大写；lower转换成小写
• %匹配任意子串
• _匹配任意一个字符
  找出所在建筑名称中包含子串Watson的所有系名

select dept_name
from department
where building like "%Watson%";

• 使用escape来定义转义字符，例如匹配所有以ab%cd开头的字符串：
  like 'ab\%cd%' escape '\'
• not like用来搜索不匹配项

9. 集合运算

• union并运算，自动去除重复的

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

找出2009年秋季开课或者在2010年开课或者两个学期都开课的所有课程。
如果想要保留所有重复，使用union all代替union。

• 交运算intersect，自动去除重复的

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

两个学期都开课的所有课程。
如果想要保留所有重复，使用intersect all代替intersect。

• 差运算except，自动去除重复的

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

2009秋课程-2010春的课程。
如果想要保留所有重复，使用except all代替except。
10. 空值

select name
from instructor
where salary is null;

注意与unknown的关系。
11. 聚类
avg min max sum count
找出CS系教师的平均工资

select avg(salary) as avg_salary
from instructor
where dept_name='CS';

2010年春教授一门课程的教师数目,distinct表示去除重复，count用以计算一个关系中元组的个数。

select count(distinct ID)
from teachers
where semester = 'Spring' and year = 2000;

注意：count(*)不允许使用distinct。

• 分组聚类：group by用来给出一个或者多个属性，用来构造分组。

select dept_name, avg(salary)
from department
group by dept_name;

找出每个系的平均工资。
找出每个系在2010年春教授课程的老师人数。

select dept_name ,count(distinct ID)as inst_count
from instructor natural join teachers
where semester = 'Spring' and year = 2010
group by dept_name;

注意：任何没有出现在group by中的属性，只能出现在聚类函数内部，否则就是不对的。 例如，上面ID只在count中，如果单独出来，每个老师都有ID，而每组又只输出一个ID，无法选择。

• having在形成分组后起作用，表示分组后的分组条件

select dept_name, avg(salary) as avg_salary
from instrutor
group by dept_name
having avg(salary) > 40000

顺序：from —— where —— group by —— having —— select

嵌套子查询

2009年秋季开课以及在2010年开课的课程。

• 使用in和not in

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

select distint name
from instructor
where name not in ("Sherry","Smith");

some表示至少比某一个要大

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

注意：=some等价于in;<>all等价于not in;所以，=all并不等价于in，<>some也不等于not in。

• from中的子查询

select max(tot_salary)
from (
select dept_name,sum(salary)
from instructor
group by dept_name)as dept_total(dept_name,tot_salary);

外连接

• 左外连接：只保留出现在左外连接运算左边关系中的元组
• 右外连接：只保留出现在右外连接右边关系中的元组
• 全外连接：保留出现在两个关系中所有元组

找出所有一门课也没有选修的学生ID

select ID
from student natural left outer join takes
where course_id is null;

左外连接和右外连接是对称的，以下两者结果一样，只是出现顺序不太一样。

select *
from takes natural right outer join student;

select *
from student natural left outer join takes;

• where和on在natural left join中的区别
  on条件是在生成临时表时使用的条件，不管on中条件是否为真，都会返回左边表中的记录；其属于外连接声明的一部分，而外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果。
  where是在临时表生成之后，再对临时表进行过滤的条件。条件为假全部过滤掉。

视图

• 虚关系：并不预先计算并存储，而是使用虚关系的时候才通过执行查询被计算出来。
• 任何像这种不是逻辑模型发一部分，但作为虚关系对用户可见的关系称为视图。
• 视图名可以出现在关系名出现的任何地方。

每个系中所有教师的工资总和

create view department_total_salary(dept_name,total_salary) as
select dept_name,sum(salary)
from instructor
group by dept_name

• 物化视图：如果用于定义视图的实际关系改变，视图也跟着修改。保持物化视图一直处于最新状态的过程称为物化视图的维护。
• 除了一些有限的情况外，一般不允许对视图关系进行修改。

事务

事务由查询或者更新的语句序列组成。

• Commit work提交当前事务；
• Rollback work回滚当前事务。
• 一旦事务执行了Commit work，她的影响就不能被Rollback work撤销了。

事务四大性质：

1. 原子性：事务的所有操作在数据库中要么全部正确反映出来，要么完全不反应；
2. 一致性：隔离执行事务时保持数据库的一致性；
3. 隔离性：尽管多个事务可能并发执行，但互相感觉不到；
4. 持久性：一个事务完成后，他对数据库的改变必须是永久的。

事务隔离性级别

1. 可串行化
2. 可重复读：只允许读取已提交的数据，而且一个事务两次读取一个数据项期间，其他事务不得更新该数据；
3. 已提交读： 只允许读取已提交的事务；
4. 未提交读：允许读取未提交数据。

不允许脏写：一个数据项已经被另外一个尚未提交或中止的事务写入，则不允许对数据项执行写操作。

完整性约束

完整性约束保障授权用户对数据库做出的修改不会破坏数据的一致性。

• not null
• unique 注明形成超码
• check保证属性满足某种条件
• 级联删除

SQL的数据类型和模式

• date
• time
• timestamp=date+time
• default
• blob大对象数据类型

索引

create index student_ID on student(ID);

授权

grant select on department to Amit,Satoshi;
grant update(budget) on department to Amit;
revoke select on department from Amit;
revoke update(budget) on department from Amit;
#权限转移
grant select on department to Amit with grant option; 

• 角色

create role instructor;
grant instructor to Amit;