数据库基础

关系模型

• Database = set of named relations (or tables)
• Each relation has a set of named attributes (or columns)
• Each tuple (or row) has a value for each attribute
• Each attribute has a type (or domain)
• NULL - special value for "unknown" or "undefined"
• Key - attribute whose value is unique in each tuple Or set of attributes whose values are unique
• Schema - Structural description of relations in database
• Instance - actual contents at given point in time

关系代数

这里之所以要讲关系代数，是因为关系代数定义了一套在表上运算且输出结果也是表的代数运算。这些运算可以混合使用得到所希望查询的表达式。关系代数定义了关系查询语言中使用的基本运算。

• 选择：返回输入关系中满足谓词的行。
• 投影：对输入关系的所有行输出指定的属性，从输出中去除重复的元组。
• 并：输出两个关系中元组的并。
• 集合差：找出在一个关系中而不在另一个关系中的元组。
• 笛卡尔积：从两个输入关系中输出所有的元组对（无论它们在共同属性上的取值是否相同）。
• 更名：为关系代数表达式的结果取一个名字。
• 集合交：在两个关系中都存在的的元组。
• 自然连接：从两个输入关系中输出在相同名字的所有属性上取值相同的元组对。

表的创建、删除、修改

在SQL中创建关系（表）的语法如下。

create table College(cName text, state text, enrollment int);
create table Student(sID int, sName text, GPA real, sizeHS int);
create table Apply(sID int, cName text, major text, decision text);

SQL查询语句

delete from Student;
delete from College;
delete from Apply;

insert into Student values (123, 'Amy', 3.9, 1000);
insert into Student values (234, 'Bob', 3.6, 1500);
insert into Student values (345, 'Craig', 3.5, 500);
insert into Student values (456, 'Doris', 3.9, 1000);
insert into Student values (567, 'Edward', 2.9, 2000);
insert into Student values (678, 'Fay', 3.8, 200);
insert into Student values (789, 'Gary', 3.4, 800);
insert into Student values (987, 'Helen', 3.7, 800);
insert into Student values (876, 'Irene', 3.9, 400);
insert into Student values (765, 'Jay', 2.9, 1500);
insert into Student values (654, 'Amy', 3.9, 1000);
insert into Student values (543, 'Craig', 3.4, 2000);

insert into College values ('Stanford', 'CA', 15000);
insert into College values ('Berkeley', 'CA', 36000);
insert into College values ('MIT', 'MA', 10000);
insert into College values ('Cornell', 'NY', 21000);

insert into Apply values (123, 'Stanford', 'CS', 'Y');
insert into Apply values (123, 'Stanford', 'EE', 'N');
insert into Apply values (123, 'Berkeley', 'CS', 'Y');
insert into Apply values (123, 'Cornell', 'EE', 'Y');
insert into Apply values (234, 'Berkeley', 'biology', 'N');
insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
insert into Apply values (345, 'Cornell', 'CS', 'Y');
insert into Apply values (345, 'Cornell', 'EE', 'N');
insert into Apply values (678, 'Stanford', 'history', 'Y');
insert into Apply values (987, 'Stanford', 'CS', 'Y');
insert into Apply values (987, 'Berkeley', 'CS', 'Y');
insert into Apply values (876, 'Stanford', 'CS', 'N');
insert into Apply values (876, 'MIT', 'biology', 'Y');
insert into Apply values (876, 'MIT', 'marine biology', 'N');
insert into Apply values (765, 'Stanford', 'history', 'Y');
insert into Apply values (765, 'Cornell', 'history', 'N');
insert into Apply values (765, 'Cornell', 'psychology', 'Y');
insert into Apply values (543, 'MIT', 'CS', 'N');

利用笛卡尔积

select sID,sName,GPA
from Student
where GPA > 3.6;

select distinct sName,major
from Student,Apply
where Student.sID = Apply.sID;

select distinct sName,major
from Student,Apply
where Student.sID = Apply.sID;

select distinct College.cName
from  College,Apply
where  College.cName = Apply.cName  and enrollment > 20000 and major = 'CS';

select Student.sID,Student.sName,Student.GPA,Apply.cName,enrollment
from Student,Apply,College
where Student.sID = Apply.sID and Apply.cName = College.cName
order by GPA desc, enrollment;

select sID,major
from Apply
where major like '%bio%';

select sID,sName,GPA,sizeHS,GPA*(sizeHS/1000.0) as scaledGPA
from student;

自连接

select S.sID,S.sName,S.GPA,A.cName,enrollment
from Student S,Apply A,College C
where S.sID = A.sID and A.cName = C.cName
order by GPA desc, enrollment;

select  S1.sID,S1.sName,S1.GPA,S2.sID,S2.sName,S2.GPA
from Student S1, Student S2
where S1.GPA = S2.GPA and S1.sID < S2.sID;

union、intersect、except

select cName as name  from College
union
select sName as name from Student;

select sID  from Apply where major = 'CS'
intersect
select sID  from Apply where major = 'EE';

select sID  from Apply where major = 'CS'
except
select sID  from Apply where major = 'EE';

union操作符默认是排序、去重的，取消可以使用union all；

子查询

select sID,sName
from Student
where sID in (select sID from Apply where major = 'CS');

select sID,sName
from Student
where sID in (select sID from Apply where major = 'CS')
and sID not in (select sID from Apply where major = 'EE');

select cName,state
from College C1
where exists (select state from College C2 
where C1.state = C2.state and C1.cName <> C2.cName);

select sName,GPA
from Student S1
where not exists (select * from Student S2 where S2.GPA > S1.GPA);

select sID,sName,sizeHS
from Student S1
where exists (select * from Student S2 where S2.sizeHS < S1.sizeHS);

select * from
(select sID,sName,GPA,GPA*(sizeHS/1000.0) as scaledGPA from Student) G
where abs(G.scaledGPA - GPA) > 1.0;

select College.cName,state,GPA
from College,Apply,Student
where college.cName = apply.cName
and Apply.sID = Student.sID
and GPA >= all (select GPA from Student,Apply where student.sID = apply.sID
    and Apply.cName = college.cName);

select cName,state,
(select distinct GPA from apply,student 
where college.cName = apply.cName 
and apply.sID = student.sID
and GPA >= all (select GPA from student,apply where student.sID = apply.sID and apply.cName = college.cName)) as GPA
from college;

any、all

select sID,sName,sizeHS
from Student
where sizeHS > any (select sizeHS from Student);

select cName
from College S1
where enrollment > all (select enrollment from College S2 where S2.cName <> S1.cName);

avg、min、max、count、