数据库

csdn

zhihu

第一节

重点知识点

数据库系统由数据库、数据库管理系统、应用系统和数据库管理员构成。
数据模型的组成要素是：数据结构、数据操作、完整性约束条件。
实体型之间的联系分为一对一、一对多和多对多三种类型。
常见的数据模型包括：关系、层次、网状、面向对象、对象关系映射等几种。
关系模型的完整性约束包括：实体完整性、参照完整性和用户定义完整性。
阐述数据库三级模式、二级映象的含义及作用。

数据库三级模式反映的是数据的三个抽象层次：

模式是对数据库中全体数据的逻辑结构和特征的描述。

内模式又称为存储模式，是对数据库物理结构和存储方式的描述。

外模式又称为子模式或用户模式，是对特定数据库用户相关的局部数据的逻辑结构和特征的描述。

数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序，通过外模式/模式映象与逻辑模式建立联系，实现数据的逻辑独立性。模式/内模式映象建立模式与内模式之间的一对一映射，实现数据的物理独立性。

第二节

重要知识点

  1. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。

  2. 关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。

  3.关系模式是对关系的描述，五元组形式化表示为：R（U，D，DOM，F），其中

 R —— 关系名

 U —— 组成该关系的属性名集合

 D —— 属性组 U 中属性所来自的域

 DOM —— 属性向域的映象集合

 F —— 属性间的数据依赖关系集合

第三节

第四节

名词解释

数据依赖：反映一个关系内部属性与属性之间的约束关系，是现实世界属性间相互联系的抽象，属于数据内在的性质和语义的体现。
规范化理论：是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题。
函数依赖：简单地说，对于关系模式的两个属性子集X和Y，若X的任一取值能唯一确定Y的值，则称Y函数依赖于X，记作X→Y。
非平凡函数依赖：对于关系模式的两个属性子集X和Y，如果X→Y，但Y!⊆X，则称X→Y为非平凡函数依赖；如果X→Y，但Y⊆X，则称X→Y为非平凡函数依赖。
完全函数依赖：对于关系模式的两个属性子集X和Y，如果X→Y，并且对于X的任何一个真子集X’，都没有X’→Y，则称Y对X完全函数依赖。
范式：指符合某一种级别的关系模式的集合。在设计关系数据库时，根据满足依赖关系要求的不同定义为不同的范式。
规范化：指将一个低一级范式的关系模式，通过模式分解转换为若干个高一级范式的关系模式的集合的过程。
1NF：若关系模式的所有属性都是不可分的基本数据项，则该关系模式属于1NF。
2NF：1NF关系模式如果同时满足每一个非主属性完全函数依赖于码，则该关系模式属于2NF。
3NF：若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码，则该关系模式属于3NF。
BCNF：若一个关系模式的每一个决定因素都包含码，则该关系模式属于BCNF。
数据库设计：是指对于一个给定的应用环境，构造优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。
数据库设计的6个基本步骤：需求分析，概念结构设计，逻辑结构设计，物理结构设计，数据库实施，数据库运行和维护。
概念结构设计：指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。
逻辑结构设计：将概念结构模型（基本E-R图）转换为某个DBMS产品所支持的数据模型相符合的逻辑结构，并对其进行优化。
物理结构设计：指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。
抽象：指对实际的人、物、事和概念进行人为处理，抽取所关心的共同特性，忽略非本质的细节，并把这些特性用各种概念精确地加以描述，这些概念组成了某种模型。
数据库设计必须遵循结构设计和行为设计相结合的原则。
数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
三种常用抽象方法是分类、聚集和概括。
局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。
数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。
确定数据存放位置和存储结构需要考虑的因素主要有：存取时间、存储空间利用率和维护代价等。

三范式

第一范式（1NF）无重复的列

先看一个不符合第一范式的表结构，如下：

员工编码	姓名	年龄
001	销售部小张	28
002	运营部小黄	25
003	技术部小高	22

在这一个表中的，姓名字段下的数据是可以再进行拆分的，因此它不符合第一范式，那怎么样才符合第一范式呢？如下：

员工编码	部门	姓名	年龄
001	销售部	小张	28
002	运营部	小黄	25
003	技术部	小高	22

   第一范式（1NF）中数据库表的每一列都是不可分割的基本数据项
   同一列中不能有多个值
   即实体中的某个属性不能有多个值或者不能有重复的属性。
   简而言之，第一范式就是无重复的列。

   在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式（2NF）属性完全依赖于主键[消除部分子函数依赖]

表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。

再通俗点讲就是，一个表只能描述一件事情。

我们用一个经典案例进行解析。

学号	姓名	年龄	课程名称	成绩	学分
001	小张	28	语文	90	3
001	小张	28	数学	90	2
002	小黄	25	语文	90	3
002	小黄	25	语文	90	3
003	小高	22	数学	90	2

我们先分析一下表结构。

\1. 假设学号是表中的唯一主键，那由学号就可以确定姓名和年龄了，但是却不能确定课程名称和成绩。

\2. 假设课程名称是表中的唯一主键，那由课程名称就可以确定学分了，但是却不能确定姓名、年龄和成绩。

\3. 虽然通过学号和课程名称的联合主键，可以确定除联合主键外的所有的非主键值，但是基于上述两个假设，也不符合第二范式的要求。

那我们应该如何调整表结构，让它能复合第二范式的要求呢？

我们可以基于上述的三种主键的可能，拆分成 3 张表，保证一张表只描述一件事情。

学生表 - 学号做主键

学号	姓名	年龄
001	小张	28
002	小黄	25
003	小高	22

课程表 - 课程名称做主键

课程名称	学分
语文	3
数学	2

成绩表 - 学号和课程名称做联合主键

学号	课程名称	成绩
001	语文	90
001	数学	90
002	语文	90
002	语文	90
003	数学	90

这时候我们可能会想，为什么我们就要遵循第二范式呢？不遵循第二范式会造成什么样的后果呢？

造成整表的数据冗余。

如，学生表，可能我就只有2个学生，每个学生都有许多的信息，比如，年龄、性别、身高、住址…如果与课程信息放到同一张表中，可能每个学生有3门课程，那数据总条数就会变成6条了。但是通过拆分，学生表我们只需要存储 2 条学生信息，课程表只需要存储 3 条课程信息，成绩表就只需保留学号、课程名称和成绩字段。

更新数据不方便。

假设，课程的学分发生了变更，那我们就需要把整表关于该课程的学分都要更新一次，但如果我们拆分出课程表，那我们就只需要把课程表中的课程信息更新就行。

插入数据不方便或产生异常。

① 假设主键是学号或课程名称，我们新增了某个课程，需要把数据插入到表中，这时，可能只有部分人有选修这门课程，那我们插入数据的时候还要规定给哪些人插入对应的课程信息，同时可能由于成绩还没有，我们需要对成绩置空，后续有成绩后还得重新更新一遍。

② 假设主键是学号和课程名称的联合主键。同样也是新增了某课程，但是暂时没有人选修这门课，缺少了学号主键字段数据，会导致课程信息无法插入。

  满足第二范式（2NF）必须先满足第一范式（1NF）。

  第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。

  为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。 
  第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是属性完全依赖于主键。

第三范式（3NF）属性不依赖于其它非主属性[消除传递依赖]

在满足第二范式的情况下，消除传递依赖。即，在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取某非主键字段 B。

仍然用一个经典例子来解析

学号	姓名	班级	班主任
001	小黄	一年级（1）班	高老师

这个表中，学号是主键，它可以唯一确定姓名、班级、班主任，符合了第二范式，但是在非主键字段中，我们也可以通过班级推导出该班级的班主任，所以它是不符合第三范式的。

那怎么设计表结构，才是符合第三范式的呢？

学生表

学号	姓名	班级
001	小黄	一年级（1）班

班级表

班级	班主任
一年级（1）班	高老师

通过把班级与班主任的映射关系另外做成一张映射表，我们就成功地消除了表中的传递依赖了。

  满足第三范式（3NF）必须先满足第二范式（2NF）。

  简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

  例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

具体实例剖析

  下面列举一个学校的学生系统的实例，以示几个范式的应用。

   在设计数据库表结构之前，我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息，说关心的问题有如下几个方面。

   1）学生有那些基本信息 
   2）学生选了那些课，成绩是什么 
   3）每个课的学分是多少 
   4）学生属于那个系，系的基本信息是什么。

   首先第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统（DBMS）中，不允许你把数据库表的一列再分成二列或多列，因此做出的都是符合第一范式的数据库。 

   我们再考虑第二范式，把所有这些信息放到一个表中(学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办电话)下面存在如下的依赖关系。 
   1）（学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话) 
   2） (课程名称) → (学分) 
   3）（学号，课程）→ (学科成绩)

根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表：

   学生：Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)； 
   课程：Course(课程名称, 学分)； 
   选课关系：SelectCourse(学号, 课程名称, 成绩)。

   事实上，对照第二范式的要求，这就是满足第二范式的数据库表，若不满足第二范式，会产生如下问题

数据冗余：同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。

更新异常： 1)若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。
2)假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。

删除异常：假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。

   我们再考虑如何将其改成满足第三范式的数据库表，接着看上面的学生表Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)，关键字为单一关键字"学号"，因为存在如下决定关系：

  （学号）→ (姓名, 年龄，性别，系别，系办地址、系办电话)

但是还存在下面的决定关系
(学号) → (所在学院)→(学院地点, 学院电话)
即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。
它也会存在数据冗余、更新异常、插入异常和删除异常的情况（这里就不具体分析了，参照第二范式中的分析）。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了：

   学生：(学号, 姓名, 年龄, 性别，系别)； 
   系别：(系别, 系办地址、系办电话)。

SQL语句总结

SQL语句中常用关键词及其解释如下：

1）SELECT
将资料从数据库中的表格内选出，两个关键字：从 (FROM) 数据库中的表格内选出 (SELECT)。语法为
SELECT “栏位名” FROM “表格名”。
2）DISTINCT
在上述 SELECT 关键词后加上一个 DISTINCT 就可以去除选择出来的栏位中的重复，从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
SELECT DISTINCT “栏位名” FROM “表格名”。
3）WHERE
这个关键词可以帮助我们选择性地抓资料，而不是全取出来。语法为
SELECT “栏位名” FROM “表格名” WHERE “条件”
4）AND OR
上例中的 WHERE 指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的 (像上一页的例子)，也可能是复杂的。复杂条件是由二或多个简单条件透过 AND 或是 OR 的连接而成。语法为：
SELECT “栏位名” FROM “表格名” WHERE “简单条件” {[AND|OR] “简单条件”}+
5）IN
在 SQL 中，在两个情况下会用到 IN 这个指令；这一页将介绍其中之一：与 WHERE 有关的那一个情况。在这个用法下，我们事先已知道至少一个我们需要的值，而我们将这些知道的值都放入 IN 这个子句。语法为：
SELECT “栏位名” FROM “表格名” WHERE “栏位名” IN (‘值一’, ‘值二’, …)
6）BETWEEN
IN 这个指令可以让我们依照一或数个不连续 (discrete)的值的限制之内抓出资料库中的值，而 BETWEEN 则是让我们可以运用一个范围 (range) 内抓出资料库中的值，语法为：
SELECT “栏位名” FROM “表格名” WHERE “栏位名” BETWEEN ‘值一’ AND ‘值二’
7）LIKE
LIKE 是另一个在 WHERE 子句中会用到的指令。基本上， LIKE 能让我们依据一个模式(pattern) 来找出我们要的资料。语法为：
SELECT “栏位名” FROM “表格名” WHERE “栏位名” LIKE {模式}
8）ORDER BY
我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大 (ascending) 或是由大往小(descending)。在这种情况下，我们就可以运用 ORDER BY 这个指令来达到我们的目的。语法为：
SELECT “栏位名” FROM "表格名 [WHERE “条件”] ORDER BY “栏位名” [ASC, DESC]
9）函数
函数允许我们能够对这些数字的型态存在的行或者列做运算，包括 AVG (平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM (总合)。语法为：
SELECT “函数名”(“栏位名”) FROM “表格名”
10）COUNT
这个关键词能够帮我我们统计有多少笔资料被选出来，语法为：
SELECT COUNT(“栏位名”) FROM “表格名”
11）GROUP BY
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。语法为：
SELECT “栏位1”, SUM(“栏位2”) FROM “表格名” GROUP BY “栏位1”
12）HAVING
该关键词可以帮助我们对函数产生的值来设定条件。语法为：
SELECT “栏位1”, SUM(“栏位2”) FROM “表格名” GROUP BY “栏位1” HAVING (函数条件)
13）ALIAS
我们可以通过ALIAS为列名称和表名称指定别名，语法为：
SELECT “表格别名”.“栏位1” “栏位别名” FROM “表格名” “表格别名”

下面为一个例子，通过它我们应该能很好地掌握以上关键词的使用方法。

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.S#
from (select s#,score from SC where C#=’001′) a,
(select s#,score from SC where C#=’002′) b
where a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩；
select S#,avg(score)
from sc
group by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname

4、查询姓“李”的老师的个数；
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;

5、查询没学过“叶平”老师课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select S#,Sname
from Student
where S# in
(select S#
from SC ,Course ,Teacher
where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=’叶平’));

8、查询所有课程成绩小于60分的同学的学号、姓名；
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

9、查询没有学全所有课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S#
group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in （select C# from SC where S#=‘1001’）;

11、删除学习“叶平”老师课的SC表记录；
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=‘叶平’;

12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT L.C# 课程ID,L.score 最高分,R.score 最低分
FROM SC L ,SC R
WHERE L.C# = R.C#
and
L.score = (SELECT MAX(IL.score)
FROM SC IL,Student IM
WHERE IL.C# = L.C# and IM.S#=IL.S#
GROUP BY IL.C#)
and
R.Score = (SELECT MIN(IR.score)
FROM SC IR
WHERE IR.C# = R.C#
GROUP BY IR.C# );

13、查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S# ) T1
WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
ORDER BY 平均成绩 desc;

14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC)
ORDER BY t1.C#;

15、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC )
ORDER BY t1.C#;

数据库

数据库

第一节

相关概念

重点知识点

第二节

相关概念

重要知识点

第三节

相关概念

第四节

相关概念和知识

名词解释

三范式

SQL语句总结