[toc]

概念数据模型

结构数据模型

关系模型：二维表

三级模式结构

三级模式

三级模式两级映像

外模式——视图

模式（概念模式）——基本表

内模式——存储文件

两级映像

外模式/模式映像：逻辑独立性

模式/内模式映像：物理独立性

关系模式基本术语

外码：注意还要是另一个关系的码

完整性约束

并、差、交、笛卡尔积

重点：笛卡尔积

投影、连接

\(\sigma_{1>3}\)表示第一列大于第三列

\(\sigma_{1>'5'}\)表示第一列的值大于5

题目

查询效率最高即查询速度最快，D选项先进行查询，再笛卡尔积，可以笛卡尔积之后的元组数量少，从而查询效率高。

θ连接和等值连接

θ为=时为等值连接

自然连接

自然连接，如果\(R\times S\)中有多个相同的属性列，也要选择多个都相同的，比如例子中的要选择A、C属性都相同的

左外连接、右外连接、全外连接

除

先找C、D列在R中的，有{a, b}、{b, d}、{c, k}，然后再找这三个对应的C、D列，发现只有{b, d}对应的C、D列不能全部与S对应上

转SQL语言

投影、选择转SQL语言

投影：select

选择：where

笛卡尔积转SQL语言

自然连接转SQL语言

SQL语言

数据定义语言

建立数据库：create database 数据库名

建立表：

unique可以为空

primary key 唯一且不为空

外键可以为空

1
2
3
4
5
数据库的外键可以为空，也可以非空，但是如果非空，则这个值必须在主表中存在。
比如主表是一个班级，主键是班级id，
子表是学生分配班级表，主键是学生id，外键是所在班级id。
如果一个学生的外键id为空，说明这个学生还没有被分配到任何一个班级
如果一个学生的外键id非空，并且是班级表中的某个id，则说明学生分配到这个班级

修改基本表结构

数据操作语言

insert into 表名(列名序列) values(元组值)

insert into 表名(列名序列) select 查询语句

delete from 表名 [where 条件表达式]([]可选，不加全部删除)

update 表名 set 列名=值表达式[, 列名=值表达式...][where 条件表达式]

数据查询语言
1
2
3
4
5
6
select 目标表的列名或列表达式序列 
from 表 
[where 行条件表达式]
[group by 列名序列]
[having 组表达式]
[order by 列名 [ASC|DESC]...]

select distinct 类别 as kind from teacher：去除重复，as命名

字符串匹配运算符：[not] like

匹配串：

  • %代表任意长度的字符串
  • _代表任意单个字符

比较运算符： <>（不等于）

select * from score where 分数 between 60 and 80：[60, 80]

集合运算符：in、not in

select * from score where 分数 in (85, 86, 88)

逻辑组合：and、or

空值：is null、is not null

聚合函数

avg——数值表达式的平均值

count——指定表达式的选择的项数

min、max——指定表达式的最小、大值

sum——指定表达式的和

select count(*) as 人数 from student where 班号='09031'

where后面不能跟聚合函数，having后面可以跟聚合函数

内连接

inner join

1
select * from student st inner join score sc on st.学号=sc.学号

（st、sc为定义表的别名，on跟连接条件）

  • 等值连接

    • ```mysql select student.姓名, score.课程号, score.分数 from stundet, score wherer student.学号=score.学号 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28

          

      - 非等值连接

        - 连接关系不是等于，比如where后面是between...and...

      - 自连接

        - ![](http://typora-imagehost.oss-cn-guangzhou.aliyuncs.com/img/image-20230516194150506.png)



      ###### 外连接

      out join

      - 左外连接：以left join左边的表为主体，右边表匹配的加入
        - left outer join 或 left join
      - 右外连接：以right join右边的表为主体，左边的表匹配的加入
        - right outer join 或 right join
      - 全外连接：保留两表的不匹配信息
        - full outer join 或 full join

      ```mysql
      select course.课程名, teacher.姓名
      from course right join teacher
      on course.任课教师编号= teacher.教师编号
一般子查询
1
2
3
4
5
6
select 课程号, 学号, 分数
from score
where 课程号='3-105' and  分数 > any 
(select 分数 from score
where 课程号='3-245')
order by 分数 desc

any的意思是分数大于子查询的一个分数即可

all是要大于全部

相关子查询

检索成绩比该课程平均成绩低的学生成绩表

1
2
3
4
5
6
select 学号, 课程号, 分数
from score a
where 分数 <
(select avg(分数)
from score b 
where a.课程号=b.课程号 and 分数 is not null)
查询结果的并、交、差运算
  • union：并
  • intersect：交
  • except：差
题目

54.*:*:*

按照题目的意思可以翻译成1:*:*和*:1:*，上下会覆盖，所以是*:*:*

sql控制语句

授权

1
grant<权限>[,<权限>...][on<对象类型><对象名>]to<用户>[,<用户>...][with grant option]
  • with grant option：获得权限的用户可以将权限赋给其他用户

注意这个on

1
grant all privileges on table teacher to user1 with grant option

表的全部权限

视图

视图是从一个或者多个基本表或视图中导出的表

视图是一个虚拟表

视图创建

1
2
3
create view 视图名(列表名)
as select 查询子句
[with check option]
  • with check option表示对update、insert、delete操作时保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

视图删除

1
drop view 视图名
索引

索引改变的是数据库的内模式

关系模式

A->B

A决定B或B依赖A

函数依赖

码和推理规则

属性闭包计算

求候选码

  • L 类：所有依赖关系中仅出现在函数依赖左部的属性。　（一定包含在候选码中）

  • R 类：所有依赖关系中仅出现在函数依赖右部的属性。　　（一定不包含在候选码中）

  • LR 类：所有依赖关系中即出现在函数依赖左部又出现在函数依赖右部的属性。　　（可能包含在候选码中）

  • N 类：所有依赖关系中没有出现的属性。（一定包含在候选码中）　

步骤：

  • 先将L类和N类的加入，然后求闭包，如果等于U，则是一个候选码
  • 如果不等于U，从LR类中加入元素，求闭包

注意：

A->BC

等价于A->B和A->C

范式

第一范式（1NF）

修改需要修改多项（比如修改学生姓名），插入、删除异常（比如插入一门课程，还得填写学生，删除一门课程）

第二范式（2NF）

例如，成绩是完全函数依赖于学号和课程号，而姓名是部分函数依赖于与学号和课程号（因为姓名完全函数依赖于学号）

2NF存在问题：R1中插入、删除学院，以及每次插入学生信息都要输入学院和院长

第三范式（3NF）

比如原本的就存在学号->学院，学院->院长（传递函数依赖）

比如店长部分依赖于（书店、图书）（候选码，中的书店）

BC范式（BCNF）

非主属性对码的部分函数依赖在之前的1NF->2NF消除了，所以只存在主属性对码的部分函数依赖

3NF->BCNF

(店长，图书)->库存量，舍弃

消除了插入和删除异常

第四范式（4NF）（很少考）

候选码（任课教师，参考书）

判断部分函数依赖技巧

一般给出的都是满足1NF

先找出候选码，然后判断非主属性对候选码是否存在部分函数依赖

判断传递函数依赖技巧

主要用伪传递律。

首先，求出候选码为（时间，学生）

由（时间，学生）->教室，（时间，教室）->课程，类似于x->y，wy->z，x为（时间，学生），w为时间，y为教室。

所以有xw->z，由于x包含w，所以是（时间，学生）->课程。

又课程推出教师，所以教师传递函数依赖于候选码。

关系分解

候选码是员工号和家庭成员。

由于（姓名 部门 部门电话 部门负责人 家庭住址都是部分函数依赖于候选码），故将其分解。

分解成两个关系，对于第一个关系，可以明显发现是存在传递函数依赖的。（存在插入删除异常，比如要添加一个部分或者删除一个部门）

x->y, y->z1, y->z2

如题，则是员工号->部门，部门->部门电话，部门->部门负责人。所以将部门、部门电话、部门负责人单独列到一张表中。

无损连接和保持函数依赖

B->A, C->B 是传递函数依赖

判断的方法，对于\(\pi_{AB}(F)\)先判断其函数依赖集能不能由原F推出。其他的类似。

然后求并集，看有没有体现，可以看到D->C没有，所以对D求闭包。

数据库设计

数据库设计的策略一般有：

  • 自顶向下
  • 自底向上

数据库设计的步骤（新奥尔良法）：

  • 用户需求分析
  • 概念设计：描述概念模型比较理想的是采用E-R方法
  • 逻辑设计：将E-R图转换为关系模式，再进行规范化（范式）
  • 物理设计

需求分析

概念结构设计阶段

联系

属性

合并各分ER图

逻辑结构设计阶段

E-R图转换为关系模式

一对一联系的转换

一对多联系的转换

少的加入多的

多对多联系的转换

题目

54注意

事务

数据库的备份和恢复

备份方法

题目

封锁

分布式数据库

其他题目

1.