理论 [ 数据库基本概念及操作 ]

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

数据库（DataBase，DB）是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合

数据库的基本结构分三个层次：

(1)物理数据层。　　它是数据库的最内层，是物理存贮设备上实际存储的数据的集合。

(2)概念数据层。　　它是数据库的中间一层，是数据库的整体逻辑表示。指出了每个数据的逻辑定义及数据间的逻辑联系，是存贮记录的集合。

(3)逻辑数据层。　　它是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合

概念设计：实体，实体集，属性，实体标识符

逻辑设计：字段，记录，文件，关键码

物理介质中数据描述：bit, byte, word, block, bucket, volume

数据模型三要素：数据结构、数据操作，数据完整性约束【学生管理】

数据模型分类：

【1】概念数据模型：具体世界的抽象化（E-R模型）

【2】结构数据模型：（网状数据模型，层次数据模型，关系模型---以二维表的形式来表达实体集，面向对象模型，面向对象关系模型）

【3】物理数据模型：描述数据在物理存储介质上的组织结构（DBMS能够自动完成大部分物理数据模型的实现工作，而设计者只设计索引、聚集等特殊结构）

DBMS管理层次：应用层，语言翻译处理层（DDL,DML查询计算），数据存取层（事务、日志、封锁、存取路径）、数据存储层（缓冲区）、操作系统、数据库

E-R模型：

实体、属性、联系 || 如果其中有一个属性或者多个属性构成的子集合能够唯一标识整个属性集合，则称该属性子集为属性集合的键或者码

一个候选键一旦取定，就称其为属性集得主键或主码 || 如果一个实体的某个属性集合本身不是给实体的键，但是另一实体的键，则称其为外键 || 属性的取值范围称为域

笛卡尔乘积

  若  A={a,b},  B={1, 2, 3} 

  则A x B= {（a,1), (a,2), (a,3), (b,1), (b,2),(b,3) }

关系形式化定义

R(U,D, dom, F) 　５元组

nR  关系名

nU  组成关系的属性名的集合

nD  为属性组U中属性的域

nDom  为属性向域的映象集合

nF  为属性间数据的依赖关系集合

约束完整性

实体完整性、参照完整性、用户自定义完整性

【规则】若属性A是关系R的主属性，则属性A不能取空值

【规则】若F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系）则对于R中每个元组在F上的值必须为：

(1)或者取空值（F的每个属性值均为空值）

(2)或者等于S中某个元组的主码值。

【例】学生关系 S有学号Sno、学生姓名Same、性别Sex、系名SD、年龄Age属性；课程关系C有课程号Cno、课程名Cname、先修课程号PCno属性；学生选课关系SC有学号Sno、课程号 Cno、成绩Grade属性。写出这三个关系模式。

解：

• (1)学生关系模式S（Sno，Sname，Sex，SD，Age）
• (2)课程关系模式C（Cno，Cname，PCno）Dom（PCno）=Cno。
• (3)学生选课关系模式SC（Sno，Cno，Grade）。SC关系中的Sno、Cno又分别为外码。因为它们分别是S、C关系中的主码。

【例】关系学生(学号，姓名，性别，专业号，年龄)中，主码为“学号”，则“学号”不能取空值。在关系选修(学号，课程号，成绩)中，“学号、课程号”为主码，则“学号”和“课程号”两个属性都不能取空值。

关系代数的基本运算
并、交、差及广义笛卡尔积，选择、投影、连接以及除法

 
 

n连接(Join  JN)：在两个关系的笛卡尔积上选择满足条件的元组

此外连接还可以表示为：(R)JN R.AθS.B(S)或SL R.AθS.B(R × S)

AθB

• A，B为度数相等到的属性组；
• θ为比较运算符，例：A>B
• 根据θ的不同，又分为：
• 等值连接：A=B
• 自然连接（NJN）：AB为相同属性组，且去除重复的属性且等值
• n左连接 （LJN）：“R左连接S”的结果关系是包括所有来自R的元组和那些连接字段相等处的S的元组
• 右连接 （RJN）：包括所有来自S的元组和那些连接字段相等处的R的元组

【例】

（1）检索学习课程号为C2的学生学号和成绩

         Πs#, grade(σc#=‘c2’(SC))

（2）检索学习课程号为C2的学生学号和姓名

        Πs#, sname(σc#=‘c2’ (S  |*|    SC) )

（3）检索学习课程名为MATHS的学生学号和姓名

        Πs#, sname(σcname=‘MATHS’ (S   |*|    SC  |*|       C) )

（4）检索学习课程号为C2或C4的学生学号

        Πs# (σc#=‘c2’ V c#=‘c4’ (SC) )

（5）检索至少学习课程号为C2和C4的学生学号

       Π1(σ 1=4 Λ2=‘c2’ Λ 5=‘c4’(SC |*| SC) )

（6）检索不学习课程号为C2的学生的姓名和年龄

         Πsname,age(S)- Πsname,age (σc#=‘c2’(S  |*|    SC) )

（7）检索学习全部课程的学生姓名

         Πsname(S |*|   (Πs#,c#(SC) ÷ Πc#(C)))

聚集操作

指输入一个值的集合，然后根据该值集合得到一个单一的值作为结果。常用的有max，min，avg，sum，count等。

 一般格式：

          集计函数统计字段（表）

分组聚集操作

对关系中的元组按某一条件进行分组，并在分组的元组上使用聚集函数，这就是分组聚集。

 一般格式：

          分组字段G 集计函数统计字段（表）

查询优化处理

SELECTStudent.Sname

      FROM Student,SC

      WHER Student.Sno=SC.Sno

                and SC.Cno=‘2’

• 将查询转化成某种内部表示，通常是语法树；
• 根据一定的等价变换规则把语法树转换成标准形式
• 选择低层的操作算法
• 生成查询计划

输入：一个关系表达式的语法树

输出：计算该表达式的程序

方法：

• 将σF1Ùσ2 Ù... Ù σFn(E)变换为σF1(σF2 (…(σFn(E))…)
• 尽量把s移到树的叶端
• 尽可能将投影操作移到树的叶端
• 把选择和投影的串接合并成单个选择和投影
• 将上述得到的语法树的内节点分组
• 生成一个程序，每组结点的计算是程序中的一步

【例】

--》

--》等价转换

--》投影

数据定义（DDL）

§定义、删除、修改关系模式（基本表）

§定义、删除视图（View）

§定义、删除索引（Index）

 数据操纵（DML）

§数据查询

§数据增、删、改

 数据控制（DCL）

§用户访问权限的授予、收回

Select A1, A2, ..., An

From r1, r2, ..., rm

Where P

运算符

• 比较：<、<=、>、>=、=、<>、not +~
• 确定范围：
•   BetweenA and B、NotBetween A and B
• 确定集合：IN、NOTIN
• 字符匹配：LIKE，NOTLIKE
• 空值：IS NULL、ISNOT NULL
• 多重条件：AND、OR、NOT
• like
• 字符匹配：Like、Not Like
• 通配符：
• % —— 匹配任意字符串
• _  —— 匹配任意一个字符
• 大小写敏感

【例】列出姓张的学生的学号、姓名。

  Select sno，sname

             From Student

             Where sname LIKE ‘张%’

【例】列出张姓且单名的学生的学号、姓名。

  Select sno，sname

               From  Student

               Where sname LIKE ‘张_ _’

建表

Create Table Student (

  sno char(10) primary key(sno) ,

  sname stu_name ,

  sagesmallint,

  ssex char(1),

  sdept char(2))

删除表

DropTable  r

DropTable student

修改表结构

删除表中的某属性

去除属性及相应的数据

  Alter Table  r Drop (Column) A

  Alter Table  student Drop Column phone 

增加表中的属性

向已经存在的表中添加属性

  Alter Table  r Add  A  D

  AlterTable student Add phone char(16) Default “default”

修改表中的某属性

修改属性及相应的数据

  Alter  Table r Alter Column  A D  (Rename To B)

  Alter Table student Alter Column phone char(12)

索引

CREATE [ UNIQUE ] [ CLUSTERED |NONCLUSTERED]  INDEX index-name On TableName(Column,Column,…)

  Create Index YearIndex On Movie(year)

  Create Clustered Index SnoIndex On student(sno)

  DropIndex YearIndex

聚合函数

• 平均值：Avg
• 总和：Sum
• 最小值：Min
• 最大值：Max
• 计数：Count

忽略null

Count：参数不同，可能计也可能不计

Sum：不将其计入

Avg：具有 Null的元组不参与

Max / Min：不参与

Group by

【例】统计各系学生的人数。

          Select sdept，count(*) as stu_count

          From Student

         Group By sdept

Having

【例】列出具有两门（含）以上不及格的学生的学号、不及格的课目数。

 Select sno，count(sno) From SC

                     Where grade < 60

                                 Group By sno

                                    Havingcount(sno) >= 2

Where是先筛后分组；Having是先分组后筛

子查询

单值比较

【例】找出与200601同龄的学生

   Select *  From Student

  Where sage = ( Select sage From Student

  Where sno = ‘200601’ )

多值比较

 运算符：In、All、Some(Any)、Exists

【例】查询选修‘C01’课程的学生的学号、姓名。

             Select sno,sname

             From Student

           Where sno IN

           （Select sno From SC

           Where cno = ‘C01’ ）

【例】找出不是最小年龄的学生

         Select * From student

        Where sage > some

         ( Select sage From Student )

【例】找出具有最高平均成绩的学号及平均成绩

         Select sno ,avg(grade)

         From SC

        Group By sno

        Having avg(grade) >= all

        ( Select avg(grade) From SC Group By sno )

【例】列出选修了C01课程的学生的学号、姓名

         Select sno,sname

         From Student

       Where Exists ( Select * From SC

       Where SC.sno = Student.sno

        And cno = ‘C01’)

【例】列出没有选C01课程的学生的学号、姓名

         Select sno,sname

         From Student

       Where Not Exists

         ( Select * From SC

       Where SC.sno = Student.sno

        And cno = ‘C01’)

数据删除

                 Delete From r Where P

数据插入

Insert Into Student Values ( ‘200601’, ‘Gloria’,25, ‘F’, ‘CS’)

Insert Into SC

        Select sno,cno,null

        From Student,Course

        Where sdept = ‘CS’ and

          cno = ‘C05’

更新

Update Student

                           Set sdept = ‘MA’

                         Where sno = ‘200601’

【例】将选修C05课程的学生的成绩改为该课的平均成绩

             Update SC

                 Set grade = ( Select avg(grade )

        From SC

                   Where cno= ‘C05’)

       Where cno = ‘C05’