数据库原理与技术

最新推荐文章于 2023-02-08 18:13:26 发布

Nown1

最新推荐文章于 2023-02-08 18:13:26 发布

阅读量1.4k

点赞数 3

分类专栏：学习文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_46331393/article/details/120472122

版权

学习专栏收录该内容

4 篇文章 2 订阅

订阅专栏

第一章绪论

1、数据库、数据库管理系统、数据库系统、数据库应用系统

数据库（DB）：长期存储在计算机内有组织、可共享的大量数据的集合
数据库管理系统（DBMS）：一个操纵和管理数据库的软件，由一组计算机程序组成（Oracle，MySQL，DB2，SqlServer等）
数据库系统（DBS）：计算机引入数据库和数据库管理系统的组成。一般由硬件、软件、数据库、用户构成
数据库应用系统（DBAS）：数据库系统及其应用程序的组成，即在数据库系统环境下建立起来的为某种应用服务的软硬件集合

2、数据库的产生与发展

人工管理阶段：
- 数据不能独立保存
- 应用程序管理数据
- 数据不能共享
- 数据不具有独立性
- 数据没有软件系统进行管理，程序员要同时规定数据逻辑结构设计数据物理结构
文件系统阶段：
- 数据冗余度大、共享性差、易产生数据不一致
- 数据独立性差
- 用户负担重
- 数据无结构（数据文件按位置存放，记录之间没有联系，故是无结构的）
数据库系统阶段：
- 目的是解决数据独立性问题，即克服程序与数据文件的相互依赖
- 三个阶段：
  - 层次性数据库和网状数据库
  - 关系型数据库
  - 面向对象、键值型、文档型等新型数据库

3、文件系统和数据库系统的区别和联系？

区别：

文件系统用文件将数据长期保存在外存，而数据库系统用数据库统一存储数据
文件系统的程序和数据文件有一定联系，耦合度大。数据库系统程序和数据分离，实现数据独立
文件系统通过OS对文件读写实现数据管理，数据库系统使用DBMS实现对数据库的统一管理
文件系统共享单位是文件，数据库系统共享单位是记录和字段

联系：

都是数据组织的管理技术，程序与程序与数据之间的存取方法转换
文件系统初步将数据从程序中分离出来，但共享性差，冗余大；数据库系统进一步实现数据分离。数据库系统是在文件系统的基础上发展形成的

4、数据模型

数据库中用于抽象、模拟和处理现实世界数据和信息的工具
三个层次
- 概念数据模型（概念模型）：独立于计算机系统的数据模型，常用的是E-R图
- 逻辑数据模型（数据模型）：用户从数据库中看到的模型，如层次数据模型（树状结构，根节点无双亲节点，其他节点有且仅有一个双亲结点）、网状数据模型（网状结构，图。每个节点可以没有双亲结点，也可以拥有一个及更多的双亲节点）、关系模型（用二维表格表示实体集和联系。查询效率较前两种低）和面向对象模型
- 物理数据模型（物理模型）：对数据底层的抽象描述数据在系统内部的表示方式和存取方法，在磁盘上的存储方式和存取方法，面向计算机系统
组成要素：
- 数据结构：对实体模型和实体间联系的表达和实现
- 数据操作：用于指定数据结构的任何有效实例执行的操作或推导过程
- 数据完整性约束：数据及其联系应具备的制约和依赖关系

5、理解实体、实体特性、实体集、实体标识符的概念

6、理解一对一、一对多和多对多的概念，并能举例说明

7、绘制E-R图

找出实体集和实体属性
找出实体集之间的联系（注意关系和功能的区别。比如管理员管理某个车间是联系，而管理员查询所有用户是功能）
找出实体集联系派生出的属性
绘制E－R图

先把每个实体集分开画出其属性

矩形表示实体集，菱形表示联系，菱形两侧表示两个实体集的对应关系，如1：1，1：ｎ，ｍ：ｎ，如有派生属性用椭圆表示）

例题

设计一个学生档案管理系统，学生的信息主要包括学生的学号、姓名、性别，入学年份、出生日期、联系电话，宿舍等，和学生相关的信息还有学生所在学院，所学专业，所在班级，班主任等。其中一个学院可以有多个专业，一个专业可以有多个班级，一个专业只属于某个学院，一个班级也只属于某个专业，一个班级只能有一个班主任，一个班主任也只能带一个班级，学院信息包括学院代号、学院名称、学院负责人，专业信息包括专业代号、专业名称、专业负责人，班级信息包括班级代号、班级名称，班主任信息包括工号、姓名、职称，和班主任有关信息包括所在学院和所在系部，一个教师只能在一个学院下的一个系部工作。

要求：

⑴ 确定有哪些实体，每个实体包括哪些属性。

⑵ 找出实体间的联。

⑶ 画出E-R图。

在这里插入图片描述

8、理解数据库的三级模式和两级映像保证了数据独立性

三级模式：
- 概念模式（模式）：数据库中全局逻辑结构和特征的描述；一个数据库只有一个模式；是数据库系统模式结构的中间层（和物理存储和应用程序无关）
- 外部模式（外模式、子模式）：介于模式和应用之间，数据库用户使用的局部逻辑结构和特征的描述。一个数据库有多个外模式，是模式的子集
- 内模式（存储模式）：数据库物理结构和存储方式的描述，是数据库内部表达方式；一个数据库只有一个内模式
两级映像：
- 外模式/模式映像：定义各个（说明外模式/模式映像不止一个，一个外模式对应一个）外模式和模式的对应关系，保证逻辑独立性
- 模式/内模式映像：定义全局逻辑结构和存储结构之间的对应关系（一个数据库只有一个模式/内模式映像），保证了物理独立性
数据独立性：
- 程序和数据之间相互独立，互不影响；某一层模式的改变不会影响上一层模式
  - 逻辑独立性：模式发生改变，外模式无需改变
  - 物理独立性：内模式改变，模式无需改变

9、用户通过DBMS访问数据库过程

接受数据请求
DBMS对用户请求进行分析
DBMS向OS发出操作请求
OS对数据库中的数据进行处理并将结果放到缓冲区，发送读完标志
DBMS将缓冲区数据经模式映射成用户逻辑记录放到用户工作区，发送回答成功与否的标志

第二章关系型数据库

1、理解什么是关系模式

关系数据结构
- 域：一组具有相同类型值的集合
- 笛卡儿积：依次取一个集合的每一个元素和另一个集合的每个元素组成一条新的元素，由这些元素组成新的集合
- 关系：笛卡儿积的一个有限子集
- 码
  - 候选码：在一个关系中能唯一确定一条记录的最小属性集（是一个集合，可以是多个属性）
  - 主码：候选码不唯一，去其中一个作为主码
  - 外码：关系的一个属性但不是本关系的码，且其值要么为空要么与某一关系（可能是本关系）的主码对应
- 关系的性质：属性不可再分，没有完全相同的行和列，行和列顺序无关
关系操作
- 检索操作：查找数据
- 更新操作：插入、删除或者修改数据
关系完整性约束
- 实体完整性：主属性不能为空
- 参照完整性：外码要么为空要么等于某个关系的主码
- 用户自定义完整性：用户根据某一具体应用使数据必须满足的约束

2、关系代数

交运算（∩）、并运算（∪）、差运算（-）、广义笛卡儿积（X）
选择运算（σ）、投影运算（∏）、连接运算（在两个关系的笛卡尔积中选择符合条件的记录）、自然连接（▷◁）（两个具有相同的属性列的关系做自然连接，只选择相同属性列的值相同的记录。注意多个关系做自然连接时应注意关系的顺序，相邻连个关系要有相同属性列，比如学生▷◁学习▷◁课程是对的，学生▷◁课程▷◁学习是错的）、除运算（÷）（一个集合包含另一个集合）
基本运算：并运算、差运算、笛卡儿积、选择、投影

例题

查询选修了180101号课程的学生姓名

∏_姓名（σ_{课程号=‘180101’}（学习▷◁学生））
查询至少选修了180001号学生选修的所有课程的学生姓名
- 要用到学习和学生两张表
- 小集合是180001号学生选修的课程，大集合是某个学生选修的课程
- 两个集合通过课程号来进行除运算，要看到的姓名，因此小集合只要投影出课程号，大集合要投影出课程号和学号，最终筛选出的学号和学生表进行自然连接，投影出学生姓名
∏_姓名（（∏_{学号，课程号}（学习）÷∏_课程号（σ_{学号=‘180001’}（学习）））▷◁学生）
查询既选了”数据库原理“又选了”操作系统“的学生姓名
- 需要用到学生表、学习表、课程表
- 注意这里的既。。。。又。。。应该用交运算，而不是且
  
  正确答案：
  
  ∏_姓名（（∏_学号（σ_{课程名=’DB‘}（学习▷◁课程））∩∏_学号（σ_{课程名=’OS‘}（学习▷◁课程）））▷◁学生）
  
  错误案例：
  
  ∏_姓名（σ_{课程名=’DB‘^课程名=’OS‘}（学生▷◁学习▷◁课程））
  
  这样查找的条件其实是课程名既为“数据库原理”又是“操作系统”。很明显，每条记录课程名要么是“数据库原理”要么是“操作系统”，因此没有符合条件的记录，返回的结果为空
- 如果查询选修了“数据库原理”或“操作系统”的学生姓名呢？
  
  可以用或，也可以用并运算
- 如果查询选修了“数据库原理”但是没有选修“操作系统”的学生姓名呢？
  
  可以用差运算。
  
  但不能使选择条件是 课程号=’数据库原理‘^课程号!=’操作系统‘。因为这样实际上只要选了数据库原理，课程名肯定就不等于’操作系统‘，相当于第二个条件没用
查询所有未选修“数据库原理” 的学生姓名
- 用到学习、学生两张表
- 没有发生，应当用差运算，用全部的减去已发生的，而不是不等于
  
  正确答案：
  
  ∏_姓名（（∏_学号（学生）—∏_学号（σ_{课程名=’DB‘}（学习▷◁课程）））▷◁学生）
  
  错误案例：
  
  ∏_姓名（σ_{课程名!=’DB‘}（学生▷◁学习▷◁课程））
  
  这样查找，其实只要选修的课程不是数据库原理就会被返回到结果集。比如某个学生选修了数据库原理和操作系统，当检索选修操作系统的那条记录时就会被返回到结果集。也就是说，只要不是仅选了数据库原理一门课的，就会被返回到结果集

3、查询优化

等价变换，主要是连接运算换成笛卡儿积和选择运算
画出初始的查询树
下放选择（如果某个选择只涉及一张表就把这个选择放到对应表的上面，如果是在笛卡儿积之后则放到笛卡儿积运算上面）
下放投影（如果下一步是选择运算，则要添加投影项，如果是笛卡儿积则把投影项下放到相应分支。依次下放直到每个选择上面都有一个投影）
写出优化后的关系表达式（先子节点后双亲节点，笛卡儿积和选择可以合并成自然连接）

例题

在这里插入图片描述

第三章 SQL

1、SQL的特点和功能

特点：
- 高度非过程化：用户只要关心“做什么”，无需关心“怎么做”
- 功能完备且一体化：具有数据定义（CREATE，DROP，ALTER）和数据查询（SELECT）、数据操作（UPDATE，INSERT，DELETE）、数据控制（GRANT，REVOKE）功能且被集成到一个语言系统中
- 统一语法结构
- 语言简洁，易学易用

2、定义功能

创建基本表（数据库中实际存在的表，一个关系对应一张表）

create table student(
	id int not null primary key,
    -- 如果只有一个属性作为主键，可以直接在定义这个属性时田间 primary key关键字。那么它是不能为空的
    name varchar(10) null default null,
    -- 这里有用户自定义完整性。姓名最多为10个字符，可以为空且默认为空
    birthday date null default null，
    sex int not null,
    	check(sex in ('男','女'))
    -- 当我们指定某个属性域时，可以使用check关键字
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table study(
	stud_id int not null references student(id),
    -- 当某个属性为外码，可以使用references关键字，后面指定是哪个表的哪个属性
	cour_id int not null references course(id),
    grade int null default,
    priamry key (stud_id,cour_id)
    -- 当主码包含多个属性时，可以使用primary key(value1,value2)的方式指定
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table course(
	id int not null auto_increment primary key,
    -- 主键生成策略可以是自增 auto_increment
    name varchar(10) null default null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建视图（并不实际存在，是从一张或多张基本表中导出的）

create view stud_grade as
(
	select student.name,study.grade,course.name
    	from student,study,course
    		where student.id=study.stud_id and course.id=study.cour_id
);

基本表的修改和删除

-- 添加属性列，新添加的属性列的值都为NULL
	-- 为“学生”表（student）添加入学日期（admission_date）属性列，该列值的类型为日期（date）
alter table student add admission_date date;
-- 删除属性列，如果要删除的属性列规定为NOT NULL则不能删除
	-- 删除“学生”表（student）中入学日期（admission_date）属性列
alter　table student drop admission_date;
-- 添加约束
	-- 为“学生”表（student）添加约束，使name是唯一的
alter table student add unique(name);
	-- 为“学习”表（student）添加约束，使外码stud_id依赖于student表中的id属性
alter table study add foreign key (sutd_id) references student(id);
-- 删除约束
	-- 删除学生表的主码约束
alter table student drop primary key;
-- 修改
	-- 将学生表的id属性改为varchar（20）
alter table student modify id varchar(20);
-- 删除基本表
	-- 删除学生表
drop table student;

3、索引

根据关系表中某些字段的值按照一定结构存放的文件
目的是为了方便数据库引擎少占用资源，高效找到想要的数据

4、数据查询

1、投影和选择

在学生表中找到学号为‘123456’的学生的姓名

select 姓名 from 学生 where 学号='123456'
-- 可以使用*代替表中所有属性
-- 用and表示‘且’，用or表示‘或’
-- between value1 and value2 代表的是	值>=value1 and 值<=value2
-- 为空：is null 不为空： not null

2、排序

按成绩降序排列所有选择了‘123456’号课程的学生学号和成绩

select 学号,成绩 from 学习 where 课程号='123456' order by 成绩 desc;
-- desc代表降序，asc代表升序
-- 语法： order by <列名> desc/asc

3、连接

查询所有选修了”数据库原理“课程的学生姓名

select 姓名 from 学生,学习,课程
	where 学习.学号=学生.学号 and 课程.课程号=学习.课程号;
-- 主要是自然连接，只要让对应的属性列相等就可以了

4、in 关键字

查找所有计算机、机电、外文学院学生姓名和学院名称

select 姓名,学院名称
	from 学生，学院
		where 学生.学院编号=学院.学院编号 and 学院名称 in ('计算机','机电','外文');

查询所有非计算机、机电、外文学院学生姓名和学院名称

select 姓名,学院名称
	from 学生，学院
		where 学生.学院编号=学院.学院编号 and 学院名称 not in ('计算机','机电','外文');

5、like关键字（模糊查询）

查询所有王姓用户

select * from user where name like '王%';
-- %代表不定长度的字符
-- _代表一个字符。比如找出所有姓名第二个字为“小”的用户
select * from use where name like '_小%';
-- 如果查找的匹配字段中含有% 或者_ 可以用\转义
-- 查找课程名为“DB_Design"的课程信息
select * from 课程 where name like 'DB\_Design';

6、集函数使用

查询“张三”同学平均分

select name,avg(成绩) from 学生,学习 where 学生.学号=学习.学号 and 学生.name='张三';

查询计算机学院人数

select count(*) from 学生,学院 where 学生.学院编号=学院.学院编号 and 学院名称='计算机';

查询每个学生学号、姓名、年龄

select 学号,姓名,year(now())-year(birthday) as age from 学生;

常见的集函数有：
- 平均：avg（<某一个列>）
- 求和：sum(<某一个列>)
- 最大值：max(<某一个列>)
- 最小值：min(<某一个列>)
- 计数：count(<列名1>，<列名2>)

注意集函数只能用于结果，不能用于where中作为条件

-- 查找001号课程成绩小于平均成绩的学生姓名和成绩
	-- 正确答案：
select 姓名,成绩 from 学生,学习 where 学习.学号=学生.学号 and 学习.课程号='001' and 成绩<(
	select avg(成绩) from 学习 where 课程号='001');
	
	-- 错误案例：
select 姓名,成绩 from 学生,学习 where 学习.学号=学生.学号 and 学习.课程号='001' and 成绩<avg(成绩);

7、分组查询

查询学号在’091501‘到’091508‘之间至少选修了三门课的学生学号和选课数量

select 学号,count(课程号)
-- 这里集函数的作用对象是筛选出来的每一组记录。
-- 也就是满足where，且按学号分组之后满足having条件的
	from 学习 where 学号 between '091501' and '091508' 
		group by 学号
			-- 先按照学号分组
		having count(*)>=3;
			-- 分组之后判断条件是某一组的记录数量大于等于3

8、limit关键字（分页查询）

查询平均分高于80的前5~10名的学生的学号、姓名、平均成绩

select 学生.学号,姓名,avg(成绩) as 平均分 
-- 可以给集函数结果取别名
from 学生,学习
	where 学生.学号=学习.学号 
		group by 学习.学号
		-- 需要按学生的学号进行分组
		having avg(成绩)>80
		-- 对分组之后的每一组进行筛选，要求平均分高于80
		order by avg(成绩) desc
		-- 前5~10名，因此需要降序排序，同时选择的记录应当从下标4开始的6条记录（下标是从0开始的，第五名的下标就是4）
		limit 4,6;

9、嵌套查询

子查询（内层查询）不涉及父查询（外层查询）表中的某个属性为无关子查询，否则为相关子查询

带有in关键字

查询计算机学院的所有学生学号、姓名

-- 连接查询
select 学号,姓名 from 学生,学院 where 学生.学员编号=学院.学员编号 and 学院名称='计算机'

-- 用in关键字引导的嵌套查询
	-- 第一步找到计算机学院的学院编号
	-- 第二步查找所有学生表中学院编号在第一步的结果集中的记录
select 学号,姓名 from 学生 where 学院编号 in(
	select 学院编号 from 学院 where 学院名称='计算机'
);

带有比较运算

查找001号课程成绩小于平均成绩的学生姓名和成绩

select 姓名,成绩 from 学生,学习 where 学习.学号=学生.学号 and 学习.课程号='001' and 成绩<(
	select avg(成绩) from 学习 where 课程号='001'
);

带有比较的嵌套查询可以使用any或者all关键字。any代表结果集中的某一个；all代表结果集中所有

-- 查找其他学院中比计算机学院中  某一个学生  年龄小的学生名单
select 姓名 from 学生 where 学院<>'计算机' and year(now())-year(生日)>any(
	select year(now())-year(生日) from 学生 where 学院='计算机'
); 

-- 查找其他学院中比计算机学院中  所有学生  年龄都小的学生名单
select 姓名 from 学生 where 学院<>'计算机' and year(now())-year(生日)>all(
	select year(now())-year(生日) from 学生 where 学院='计算机'
);

带有exists关键字

带有exists关键字的子查询不返回任何结果，只返回逻辑真或逻辑假。如果子查询结果不为空返回为真，否则为假

查询没有选修数据库原理的学生学号和姓名

-- exists引导的嵌套查询
select 学号,姓名 from 学生 where not exists(
	select * from 学习,课程 where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 课程名='数据库原理'
    -- 注意这里的子查询中，第一个条件用到了父查询中学生表的学号属性，因此是相关子查询
);

-- 等价于用not in 引导的嵌套查询：
select 学号,姓名 from 学生 where 学号 not in(
	select * from 学习,课程 where 学习.课程号=课程.课程号 and 课程名='数据库原理'
);

10、集合运算

集合的并运算：UNION

-- 查询选修了180101或180102号课程的学生的学号，姓名和成绩
(select 学号,姓名,成绩 from 学生,学习 where 课程号='180101')
union
-- union去除重复，union all 不会去除重复
(select 学号,姓名,成绩 from 学生,学习 where 课程号='180102');

集合的交运算： INTERSECT

-- 查询选修了180101和180102号两门课程的学生的学号，姓名和成绩
(select 学号,姓名,成绩 from 学生,学习 where 课程号='180101')
intersect
(select 学号,姓名,成绩 from 学生,学习 where 课程号='180102');

集合的差运算： EXCEPT

-- 查询选修了180101号课程但是没有选择180102号课程的学生的学号，姓名和成绩
(select 学号,姓名,成绩 from 学生,学习 where 课程号='180101' and 学生.学号=学习.学号)
except
(select 学号,姓名,成绩 from 学生,学习 where 课程号='180102' and 学生.学号=学习.学号);


-- 有些数据库是不支持except关键字的，比如MySQL数据库
-- 可以用not in关键字
select 学号,姓名,成绩 from 学生 as stud1,学习 as learn1 where 课程号='180101' 
	and 学生.学号=学习.学号 
	and 学号 not in (
		select 学号 from 学生 as stud2,学习 as learn2 where learn2.课程号='180102' and stud2.学号=learn2.学号
	);
-- 也可以用not exists关键字
select 学号,姓名,成绩 from 学生 as stud1,学习 as learn1 where 课程号='180101' 
	and 学生.学号=学习.学号 
	and not exists (
		select 学号 from 学生 as stud2,学习 as learn2 where learn2.课程号='180102' 
        	and stud2.学号=learn2.学号
        	and stud1.学号=stud2.学号
	);

集合的除运算：
- 一个集合包含另一个集合
- 原理是： A÷B= ┒（B-A）

由于一些原因，大部分笔记已经丢失，目前只有这些，未来会更新

Nown1

关注

3
点赞
踩
18

收藏

觉得还不错? 一键收藏
1
评论
数据库原理与技术

第一章绪论1、数据库、数据库管理系统、数据库系统、数据库应用系统数据库（DB）：长期存储在计算机内有组织、可共享的大量数据的集合数据库管理系统（DBMS）：一个操纵和管理数据库的软件，由一组计算机程序组成（Oracle，MySQL，DB2，SqlServer等）数据库系统（DBS）：计算机引入数据库和数据库管理系统的组成。一般由硬件、软件、数据库、用户构成数据库应用系统（DBAS）：数据库系统及其应用程序的组成，即在数据库系统环境下建立起来的为某种应用服务的软硬件集合2、数据库的产生与发展
复制链接

扫一扫