数据库系统概念

基本类型：
char(n)：固定长度的字符串，用户指定长度n。也可以使用全称character。
varchar(n)：可变长度的字符串，用户指定最大长度n，等价于全称character varying。
smallint：小整数类型（和机器相关的整数类型的子集）。
numeric(p,d)：定点数，精度由用户指定。这个数有p位数字（加上一个符号位），其中d位数字在小数点右边。所以在一个这种类型的字段上，numeric(3,1)可以精确储存44.5,但是不能精确储存444.5或0.32这样的数。
real，double，precision：浮点数与双精度浮点数，精度与机器有关。
float(n)：精度至少为n位的浮点数。
datetime 日期
image 图片
以下采用MySQL实现

建表

create table table_name(
column_name type,
primary key (column_name),
foreign key (column_name2) references table_name2(coulun_name2)
);

--1.创建department表
create table department(
dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key (dept_name)
);

--2.创建course表
create table course(
course_id varchar(7),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
primary key (course_id),
foreign key (dept_name) references department(dept_name)
);

--3.创建instructor表
create table instructor(
ID varchar(5),
name varchar(20) NOT NUll,
dept_name varchar(20),
salary numeric(8,2),
primary key(ID),
foreign key (dept_name) references department(dept_name)
);

--4.创建section表
create table section(
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primary key (course_id,sec_id,semester,year),
foreign key (course_id) references course(course_id)
);

--5.创建teachers表
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 section(course_id,sec_id,semester,year),
foreign key (ID) references instructor(ID)
);

--6.创建student表
create table student
(
ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred varchar(20),
primary key (ID),
foreign key (dept_name)references department(dept_name)
);

--7.创建takes表
create table takes
(
ID varchar(5),
course_id varchar(7),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
primary key (ID,course_id,sec_id,semester,year),
foreign key (course_id,sec_id,semester,year) references section(course_id,sec_id,semester,year),
foreign key (ID) references student(ID)
);

--8.创建classroom表
create table classroom
(
building varchar(15),
room_no varchar(7),
capacity numeric(4,0),
primary key (building,room_no)
);

--9.创建prereq表
create table prereq
(
course_id varchar(7),
prereq_id varchar(8),
primary key (course_id,prereq_id),
foreign key (course_id)references course(course_id)
);

--10.创建advisor表
create table advisor
(
i_id varchar(5),
s_id varchar(5),
primary key (s_id),
foreign key (i_id)references instructor(ID),
foreign key (s_id)references student(ID)
);

--11.创建time_slot表
create table time_slot
(
time_slot_id varchar(4),
day varchar(20),
start_time datetime,
end_time datetime,
primary key (time_slot_id)
);

插入数据

1. 默认：insert into table_name values();
2. insert into table_name(column_name1,column_name2…) values();
3. 在查询结果的基础上插入元组

--1.department
insert into department
values('biology','Watson','90000');
insert into department
values('comp.Sci.','Taylor','100000');
insert into department
values('elec.Eng.','Taylor','85000');
insert into department
values('finance','Painter','120000');
insert into department
values('history','Painter','50000');
insert into department
values('music','packard','80000');
insert into department
values('physics','Watson','70000');

--2.course
insert into course
values('BIO-101','intro.to biology','biology','4');
insert into course
values('BIO-301','genetics','biology','4');
insert into course
values('BIO-399','computational biology','biology','3');
insert into course
values('CS-101','intro.to biology','comp.Sci.','4');
insert into course
values('CS-190','game_design','comp.Sci.','4');
insert into course
values('CS-315','robotics','comp.Sci.','3');
insert into course
values('CS-319','image processing','comp.Sci.','3');
insert into course
values('CS-347','database system concepts','comp.Sci.','3');
insert into course
values('EE-181','intro.to biology','elec.Eng.','3');
insert into course
values('FIN-201','investment banking','finance','3');
insert into course
values('HIS-351','world history','history','3');
insert into course
values('MU-199','music video production','music','3');
insert into course
values('PHY-101','physical principles','physics','4');

--3.instructor
insert into instructor
values('10101','srinivasan','comp.Sci.','65000');
insert into instructor
values('12121','wu','finance','90000');
insert into instructor
values('15151','mozart','music','40000');
insert into instructor
values('22222','einstein','physics','95000');
insert into instructor
values('32343','el said','history','60000');
insert into instructor
values('33456','gold','physics','87000');
insert into instructor
values('45565','katz','comp.Sci.','75000');
insert into instructor
values('58583','califieri','history','62000');
insert into instructor
values('76543','singh','finance','80000');
insert into instructor
values('76766','crick','biology','72000');
insert into instructor
values('83821','brandt','comp.Sci.','92000');
insert into instructor
values('98345','kim','elec.Eng.','80000');

--4.section
insert into section
values('BIO-101','1','Summer','2009','Paiter','514','B');
insert into section
values('BIO-301','1','Summer','2010','Paiter','514','A');
insert into section
values('CS-101','1','Fall','2009','Packard','101','H');
insert into section
values('CS-101','1','Spring','2010','Packard','101','F');
insert into section
values('CS-190','1','Spring','2009','Taylor','3128','E');
insert into section
values('CS-190','2','Spring','2009','Taylor','3128','A');
insert into section
values('CS-315','1','Spring','2010','Watson','120','D');
insert into section
values('CS-319','1','Spring','2010','Watson','100','B');
insert into section
values('CS-319','2','Spring','2010','Taylor','3128','C');
insert into section
values('CS-347','1','Fall','2009','Taylor','3128','A');
insert into section
values('EE-181','1','Spring','2009','Taylor','3128','C');
insert into section
values('FIN-201','1','Spring','2010','Packard','101','B');
insert into section
values('HIS-351','1','Spring','2010','Painter','514','C');
insert into section
values('MU-199','1','Spring','2010','Packard','101','D');
insert into section
values('PHY-101','1','Fall','2009','Watson','100','A');

--5.teachers
insert into teachers
values('10101','cs-101','1','fall','2009');
insert into teachers
values('10101','cs-315','1','spring','2010');
insert into teachers
values('10101','cs-347','1','fall','2009');
insert into teachers
values('12121','fin-201','1','spring','2010');
insert into teachers
values('15151','mu-199','1','spring','2010');
insert into teachers
values('22222','phy-101','1','fall','2009');
insert into teachers
values('32343','his-351','1','spring','2010');
insert into teachers
values('45565','cs-101','1','spring','2010');
insert into teachers
values('45565','cs-319','1','spring','2010');
insert into teachers
values('76766','bio-101','1','summer','2009');
insert into teachers
values('76766','bio-301','1','summer','2010');
insert into teachers
values('83821','cs-190','1','spring','2009');
insert into teachers
values('83821','cs-190','2','spring','2009');
insert into teachers
values('83821','cs-319','2','spring','2010');
insert into teachers
values('98345','ee-181','1','spring','2009');

--6.student
insert into student
values('00128','zhang','comp.Sci.','102');
insert into student
values('12345','shankar','comp.Sci.','32');
insert into student
values('19991','brandt','history','80');
insert into student
values('23121','chavez','finance','110');
insert into student
values('44553','peltier','physics','56');
insert into student
values('45678','levy','physics','46');
insert into student
values('54321','williams','comp.Sci.','54');
insert into student
values('55739','sanchez','music','38');
insert into student
values('70557','snow','physics','0');
insert into student
values('76543','brown','comp.Sci.','58');
insert into student
values('76653','aoi','elec.eng.','60');
insert into student
values('98765','bourikas','elec.Eng.','98');
insert into student
values('98988','tanaka','biology','120');

--7.takes
insert into takes
values('00128','cs-101','1','fall','2009','a');
insert into takes
values('00128','cs-347','1','fall','2009','a-');
insert into takes
values('12345','cs-101','1','fall','2009','c');
insert into takes
values('12345','cs-190','2','spring','2009','a');
insert into takes
values('12345','cs-315','1','spring','2010','a');
insert into takes
values('12345','cs-347','1','fall','2009','a');
insert into takes
values('19991','his-351','1','spring','2010','b');
insert into takes
values('23121','fin-201','1','spring','2010','c+');
insert into takes
values('44553','phy-101','1','fall','2009','b-');
insert into takes
values('45678','cs-101','1','fall','2009','f');
insert into takes
values('45678','cs-101','1','spring','2010','b+');
insert into takes
values('45678','cs-319','1','spring','2010','b');
insert into takes
values('54321','cs-101','1','fall','2009','a-');
insert into takes
values('54321','cs-190','2','spring','2009','b+');
insert into takes
values('55739','mu-199','1','spring','2010','a-');
insert into takes
values('76543','cs-101','1','fall','2009','a');
insert into takes
values('76543','cs-319','2','spring','2010','a');
insert into takes
values('76653','ee-181','1','spring','2009','c');
insert into takes
values('98765','cs-101','1','fall','2009','c-');
insert into takes
values('98765','cs-315','1','spring','2010','b');
insert into takes
values('98988','bio-101','1','summer','2009','a');
insert into takes
values('98988','bio-301','1','summer','2010',null);

--8.classroom
insert into classroom
values('Packard','101','500');
insert into classroom
values('Painter','514','10');
insert into classroom
values('Taylor','3128','70');
insert into classroom
values('Watson','100','30');
insert into classroom
values('Watson','120','50');

--9.prereq
insert into prereq(course_id,prereq_id)
values('BIO-301','BIO-101');
insert into prereq(course_id,prereq_id)
values('BIO-399','BIO-101');
insert into prereq(course_id,prereq_id)
values('CS-190','CS-101');
insert into prereq(course_id,prereq_id)
values('CS-315','CS-101');
insert into prereq(course_id,prereq_id)
values('CS-319','CS-101');
insert into prereq(course_id,prereq_id)
values('CS-347','CS-101');
insert into prereq(course_id,prereq_id)
values('EE-181','PHY-101');

--10.advisor
insert into advisor(s_id,i_id)
values('00128','45565');
insert into advisor(s_id,i_id)
values('12345','10101');
insert into advisor(s_id,i_id)
values('23121','76543');
insert into advisor(s_id,i_id)
values('44553','22222');
insert into advisor(s_id,i_id)
values('45678','22222');
insert into advisor(s_id,i_id)
values('76543','98345');
insert into advisor(s_id,i_id)
values('76653','98345');
insert into advisor(s_id,i_id)
values('98988','76766');

--11.time_slot
insert into time_slot(time_slot_id,day,start_time,end_time)
values('A','M','2009-06-08 8:00','2009-06-08 8:50');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('B','M','2009-06-08 9:00','2009-06-08 9:50');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('C','M','2009-06-08 11:00','2009-06-08 11:50');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('D','M','2009-06-08 13:00','2009-06-08 13:50');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('E','T','2009-06-08 10:30','2009-06-08 11:45');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('F','T','2009-06-08 14:30','2009-06-08 15:45');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('G','M','2009-06-08 16:00','2009-06-08 16:50');
insert into time_slot(time_slot_id,day,start_time,end_time)
values('H','W','2009-06-08 10:00','2009-06-08 12:30');

查询

SQL查询的基本结构

单关系查询
多关系查询
自然连接

附加的基本运算

更名运算
字符串运算
排序元组的显示次序
where子句谓词

集合运算

并运算
交运算
差运算

聚集函数

基本聚集
分组聚集
having字句

嵌套子查询

集合成员资格
集合的比较
空关系测试
重复元组存在性测试
from字句中的子查询
with字句
标量子查询

数据库的修改

插入、删除、更新

连接表达式

外链接（连接类型和条件）

视图

视图定义和视图更新

完整性约束

单关系上的约束（not null,unique,check()），复杂的check条件与断言
create assertion <assertion_name> check <pridicate>;

模式

创建索引
create index index_name on table_name(column_name);

授权

grant <权限列表> on <关系名或视图名> to <用户|角色列表>

转移

with grant option;

回收

revoke <权限列表> on <关系名或视图名> from <用户|角色列表>

角色

角色可授予给用户或者其他角色

查询练习

(找出名字不是“wu”和“gold”的所有指导老师的姓名)
select distinct name
from instructor
where name not in('wu','gold');
等效：
select distinct name
from instructor
where name<>'wu' and name<>'gold';-- <> 不等于可以替换为 ！=

(找出所有工资高于历史系任何一个指导老师的指导老师的姓名)
select name,salary
from instructor
where salary>all(select salary
from instructor
where dept_name='History');
等效：
select name
from instructor
where salary>(select max(salary)
from instructor
where dept_name='History');

--删除元组
delete from r;
--删除r的模式，关系
drop table r;

alter table r add a d;--增加属性
alter table r drop a;--删除属性
alter table r modify a d;--修改属性

count --总数
avg --平均
distinct --不重复
having --附加条件
sum --求总和

--自然连接 natural join
select name,instructor.dept_name,building from instructor,department where department.dept_name=instructor.dept_name;--查询每个教师所在的院系名称及其所在楼名，等效于下面这句话
select name,instructor.dept_name,building from instructor natural join department;--等效于join ... on...、join ... using()、join ... on true where ...、in ...、exists ...

select name,title from instructor natural join teachers,course where teachers.course_id=course.course_id;--查询每个教师教授的课程，等效于natural join ... join ... on ...,也等效于下面这句话
select name,title from instructor natural join teachers natural join course;--如果前面两张表自然连接之后产生的字段与后面表相同的字段个数超过1，则不能使用这种方式
select name,title from (instructor natural join teachers) join course using(course_id);

--更名运算 as
select instructor.name,teachers.course_id from instructor ,teachers where instructor.ID=teachers.ID;--查询大学老师中教课的老师以及他们教课的课程号
select T.name,S.course_id from instructor as T,teachers as S where T.ID=S.ID;

--字符串运算，大小写敏感
"Intro%"匹配任何以"Intro"打头的字符串。
"%Comp%"匹配任何包含"Comp"字串的字符串。
"___"匹配任何只含三个字符的字符串。
"___%"匹配至少含三个字符的字符串。
用比较运算符like来表达模式
select dept_name from department where building like '%Watson%';--找出所有包含字串'Watson'的所有系名
select dept_name from department where building like 'ab\%cd%';--查询所有以"ab%cd"开头的系名

--排列元组的显示次序 order by
select * from instructor where dept_name='comp.Sci.' order by name;--查询计算机系的老师并按照名字字母排序，默认是升序

--比较运算符 between and--not between
select * from instructor where salary between 70000 and 90000;--查询工资在70000到90000的教师信息

union;--并运算，自动去除重复，想保留重复就使用union all
(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年春季开的课程号

intersect;--交运算，自动去除重复，想保留重复就使用intersect all
(select course_id from section where semester='Fall' and year=2009) intersect (select course_id from section where semester='Spring' and year=2010);--查询在2009年秋季并且是2010年春季开的课程号

except;--差运算，自动去除重复，想保留重复就使用except all
(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年春季开设的课程号

--嵌套子查询，相当于 intersect交运算
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);

not in;--不在
select distinct course_id from section where semester='Fall' and year=2009 and course_id not in(select course_id from section where semester='Spring' and year=2010);

all;--比什么都。。
select name from instructor where salary>all(select salary from instructor where dept_name='Biology');--等于下面这句话
select distinct T.name from instructor as T,instructor as S where T.salary>S.salary and S.dept_name='biology';

some;--至少比。。
select name from instructor where salary>some(select salary from instructor where dept_name='biology'
);

exists;--存在
select distinct 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);

not exists;--不存在
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));

unique;-- 唯一的，测试是否存在重复元组，唯一即没有重复时返回ture

超码、候选码、主码

超码是一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一的标识一个元组。若t1[K]=t2[K]，总有t1[R]=t2[R]。可用属性闭包是否包含关系中所有属性来判断属性是否为超码。

候选码是最小超码，不包含无关紧要的属性，它们的任何真子集都不能成为超码。

主码是关系的一个候选码，代表数据库设计者选中的，主要用来在一个关系中区别不同元组的候选码。

函数依赖

满足函数依赖α->β的条件是：对实例中所有元组对t1，t2，若t1[α]=t2[α]，则t1[β]=t2[β]

Armstrong公理

• 自反律。若α为一属性集且β包含于α，则α->β成立。
• 增补律。若α->β成立且γ为一属性集，则γα->γβ成立。
• 传递律。若α->β和β->γ成立，则α->γ成立。
• 下面规则为由Armstrong公理推理得出
• 合并律。若α->β和α->γ成立，则α->βγ成立。
• 分解律。α->βγ成立，则α->β和α->γ成立。
• 伪传递律。若α->β和γβ->δ成立，则αγ->δ成立。（可用增补律和传递律证明）

正则覆盖

1、利用合并律替换左边具有相同属性集的函数依赖；

2、寻找函数依赖集中的无关属性，将它从函数依赖集中删除。

判断是否是无损分解

如果R1∩R2是R1或R2的超码，R上的分解就是无损分解。

判断是否是保持依赖

对F的每一个α->β使用下面的过程：

result=α; 
repeat 
   for each 分解后的Ri 
       t=(result∩Ri)+∩Ri 
       result=result∪t 
   util (result没有变化)

如果result包含 β中的所有属性，则函数依赖α->β保持。分解是保持依赖的当且仅当上述过程中F的所有函数依赖都保持。

第一范式

属性不可分或者行与列的交叉点只有一个值或者每个字段只存储一个值。

BCNF

所有α->β都是平凡函数依赖（即β含于α）或者所有α是R的超码。

第三范式

存在一个属性，它所函数依赖的属性既不是主码也不是候选码。没有一个非关键属性依赖于另一个非关键属性。

判断或分解第三范式

所有α->β都是平凡函数依赖（即β含于α）或者所有α是R的超码。（β-α）中的每个属性都包含于R的一个候选码中。