数据库基本知识点总结(二)[云图智联]

一、相关概念和知识点

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

       18. 数据库设计必须遵循结构设计和行为设计相结合的原则。

       19. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
       20. 三种常用抽象方法是分类、聚集和概括。
       21. 局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。

       22. 数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。
       23. 确定数据存放位置和存储结构需要考虑的因素主要有： 存取时间、 存储空间利用率和维护代价等。

二、细说数据库三范式

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

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

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

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

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

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

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

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

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

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

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

2.4 具体实例剖析

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

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

       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#;

(想要了解更多的职场,职业规划方面的经验,文章第一时间发布于云图智联官网)