2022年数据库知识点整理整理 .pdf

《2022年数据库知识点整理整理 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库知识点整理整理 .pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、HFUT_Yan x 1 UNIT 1 四个基本概念1.数据(Data):数据库中存储的基本对象2.数据库的定义:数据库(Database,简称 DB)是长期储存在计算机内、有组织的、可共享的大量数据集合3.数据库管理系统（简称 DBMS）：位于用户与操作系统之间的一层数据管理软件（系统软件）。用途:科学地组织和存储数据;高效地获取和维护数据主要功能:数据定义功能;数据操纵功能;数据库的运行管理;数据库的建立和维护功能(实用程序)4.数据库系统（Database System，简称 DBS）：指在计算机系统中引入数据库后的系统数据库系统的构成数据库数据库管理系统（及其开发工具）应用系统数据库管

2、理员（DBA)和用户数据管理技术的发展过程人工管理阶段文件系统阶段数据库系统阶段数据库系统管理数据的特点如下(1)数据共享性高、冗余少；(2)数据结构化；(3)数据独立性高；(4)由 DBMS 进行统一的数据控制功能数据模型用来抽象、表示和处理现实世界中的数据和信息的工具。通俗地讲数据模型就是现实世界数据的模拟。数据模型三要素。数据结构：是所研究的对象类型的集合，它是刻画一个数据模型性质最重要的方面;数据结构是对系统静态特性的描述数据操作:对数据库中数据允许执行的操作及有关的操作规则;对数据库中数据的操作主要有查询和更改（包括插入、修改、删除）;数据操作是对系统动态特性的描述数据的约束条件:数

3、据及其联系应该满足的条件限制E-R图实体：矩形框表示属性：椭圆形(或圆角矩形)表示名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 12 页 -HFUT_Yan x 2 联系：菱形表示组织层数据模型层次模型网状模型关系模型(用“二维表”来表示数据之间的联系)基本概念:关系（Relation）:一个关系对应通常说的一张表元组（记录）:表中的一行属性（字段）：表中的一列，给每一个属性名称即属性名分量：元组中的一个属性值，分量为最小单位，不可分主码（Key）：表中的某个属性组，它可以唯一确定一个元组。域（Domain）：属性的取值范围。关系模式：对关系的描述。一般表示为：关系名（属性1，

4、属性 2，属性 n）关系模型的数据完整性约束实体完整性参照完整性用户定义的完整性DBS三级模式结构:外模式、概念模式、内模式(一个数据库只有一个内模式)名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 12 页 -HFUT_Yan x 3 UNIT 2 在进行数据库的操作时，会出现以下几方面的问题：1.数据冗余;2.插入异常;3.删除异常;4.更新异常好的关系模式应避免以上问题函数依赖设有关系模式R(A1,A2,An)，X和 Y均为 A1,A2,An的子集如果 X Y，但 Y不包含于 X，则称 X Y是非平凡的函数依赖。如果 X Y，则称 X为决定因子。如果 Y函数不依赖于X，则记

5、作 X Y。如果 X Y，并且 Y X，则记作 X?Y。范式从外到里依此增加模式分解的准则：模式分解具有无损连接性；模式分解能够保持函数依赖规范化理论主要是研究关系中各属性之间的依赖关系，根据依赖关系的不同，我们介绍了不包含子属性的第一范式，到消除了属性间的部分依赖关系的第二范式，再到消除了属性间的传递依赖关系的第三范式，最后到每个决定因子都必须是候选码的 BCNF。范式的每一次升级都是通过模式分解实现的，在进行模式分解时应注意保持分解后的关系能够具有无损连接性并能保持原有的函数依赖关系。对于一般的数据库应用来说，设计到第三范式就足够了。因为规范化程度越高,分解得越细,表的个数越多,则在检索操

6、作时会因连接而降低检索效率。例：S-D-L（Sno，Dept，Loc）有函数依赖：SnoDept，DeptLoc不是第三范式的。至少可以有三种分解方案，分别为：方案 1：S-L（Sno，Loc），D-L（Dept，Loc）,将 S-D-L分解投影得到S-L和 D-L关系方案 2：S-D（Sno，Dept），S-L（Sno，Loc）名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 12 页 -HFUT_Yan x 4 方案 3：S-D（Sno，Dept），D-L（Dept，Loc）UNIT 3 Sql 功能命令动词数据定义CREATE DROP ALTER 数据查询SELECT 数据

7、操纵INSERT UPDATE DELETE 数据控制GRANT REVOKE SQL的数据类型：数值型；字符串型；日期时间类型；货币类型数据定义功能DROPDATABASECREATE DATABASE数据库DROP VIEWCREATE VIEW视图DROP INDEXCREATE INDEX索引ALTER TABLEDROPTABLECREATE TABLE基本表修改语句删除语句创建语句操作对象ALTER DATABASE 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 12 页 -HFUT_Yan x 5 建立表 CREATE TABLE 例 1为 SC表添加“修课类别”

8、列，此列的定义为：XKLB char(4)ALTER TABLE SC ADD XKLB char(4)NULL 例 2将新添加的XKLB的类型改为char(6)。ALTER TABLE SC ALTER COLUMN XKLB char(6)例 3删除 Course 表的 Period 列ALTER TABLE Course DROP COLUMN Period 索引：聚簇索引、非聚簇索引:复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。建立索引格式：create unique clustered index 索引名 on 表名(asc|desc ，）uniqu

9、e：用于指定为表创建唯一索引，即不允许存在索引值相同的两行。clustered：用于指定创建的索引为聚簇索引。默认是非聚簇索引。asc 升序，desc 降序，默认为asc 例 1：为学生表建立按学号升序索引create indexstudent_sno on student(sno)例 2：为选课表按学号升序和课程号降序建唯一索引create unique index SCI on sc(sno，cnodesc)例 3:为学生表按系升序建立聚簇索引。create clustered index TI on student(sdept)删除索引语句基本格式：drop index 例 4：删除表

10、SC的索引 SCI。drop index SC.SCI 注：索引名前一定要加：表名名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 12 页 -HFUT_Yan x 6 SELECT FROM WHERE GROUP BY HAVING ORDER BY ORDER BY -需要哪些列-来自于哪些表-根据什么条件查询-对查询结果进行分组-指定组的选择条件-对查询结果进行排序简单查询（单表查询）例 1.查询全体学生的学号与姓名SELECT Sno，Sname FROM Student 比较大小例 8查询所有年龄在20 岁以下的学生的姓名及年龄。SELECT Sname,Sage FR

11、OM Student WHERE Sage=20 确定范围BETWEEN,AND 和 NOT BETWEEN,AND 确定集合（IN)IN 是一个逻辑运算符，可以用来查找属性值属于指定集合的元组使用 IN 的格式为：列名 NOT IN（常量 1,常量 2,常量n）IN 的含义为：当列中的值与IN 中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；NOT IN 的含义正好相反：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录；例 12查询信息系、数学系和计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student

12、WHERE Sdept IN(信息系,数学系,计算机系)此句等价于：SELECT Sname,Ssex FROM Student WHERE Sdept=信息系OR Sdept=数学系OR Sdept=计算机系字符匹配LIKE用于查找指定列名与匹配串常量匹配的元组（模糊查询）匹配串是一种特殊的字符串，它不仅包含普通字符，还可以包括通配符。通配符用于表示任意的字符或字符串。_：匹配任意一个字符；%：匹配 0 个或多个字符；：匹配 中的任意一个字符；：不匹配 中的任意一个字符。LIKE运算符的一般形式为：列名NOT LIKE 在 LIKE运算符前边也可以使用NOT运算符，表示对结果取反。例 14查

13、询姓张的学生的详细信息。SELECT*FROM Student WHERE Sname LIKE 张%例 15查询学生表中姓张、姓李和姓刘的学生的情况。SELECT*FROM Student WHERE Sname LIKE 张李刘%例 16查询名字中第2 个字为小或大字的学生的姓名和学号。名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 12 页 -HFUT_Yan x 7 SELECT Sname,Sno FROM Student WHERE Sname LIKE _小大%例 17查询所有不姓“刘”的学生。SELECT Sname FROM Student WHERE Snam

14、e NOT LIKE 刘%例 18从学生表中查询学号的最后一位不是2、3、5 的学生情况。SELECT*FROM Student WHERE Sno LIKE%235 涉及空值的查询判断取值为空的语句格式为：列名IS NULL 判断取值不为空的语句格式为：列名IS NOT NULL 例 19查询无考试成绩的学生的学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL 不可写为WHERE Grade=null 多重条件查询在 WHERE子句中可以使用逻辑运算符AND 和 OR来组成多条件查询用 AND 连接的条件表示必须全部满足所有的条件的结果才

15、为True 用 OR连接的条件表示只要满足其中一个条件结果即为True 对查询结果进行排序排序子句的格式为：ORDER BY ASC|DESC ，,n ASC表示对列进行升序排序，DESC表示对列进行降序排序。默认为升序排序。使用计算函数汇总数据例 26.计算 9512101 号学生的考试总成绩之和SELECT SUM(Grade)FROM SC WHERE Sno=9512101 对查询结果进行分组计算一般形式为：GROUP BY ，,n HAVING 例 28统计每门课程的选课人数，列出课程号和人数。SELECT Cno as 课程号,COUNT(Sno)as 选课人数FROM SC GR

16、OUP BY Cno 多表连接查询1.内连接内连接的格式为：SELECT FROM 表 1 INNER JOIN 表 2 ON 例 32查询每个学生及其修课的情况SELECT*FROM Student INNER JOIN SC ON Student.Sno=SC.Sno 外连接外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。ANSI方式的外连接的语法格式为：FROM 表 1 LEFT|RIGHT OUTER JOIN 表 2 ON 例 39查询学生的修课情况，包括修了课程的学生和没有修课的学生。SELECT Student.Sno,Sname,Cno,Gra

17、de FROM Student LEFT OUTER JOIN SC ON Student.Sno=SC.Sno 也可以用右外连接实现：SELECT Student.Sno,Sname,Cno,Grade FROM SC 名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 12 页 -HFUT_Yan x 8 RIGHT OUTER JOIN Student ON Student.Sno=SC.Sno 使用子查询进行基于集合的测试例 40查询与“刘晨”在同一个系学习的学生。SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN(SELEC

18、T Sdept FROM Student WHERE Sname=刘晨)使用子查询进行基于集合的测试例 41查询成绩为大于90 分的学生的学号、姓名。SELECT Sno,Sname FROM Student WHERE Sno IN(SELECTSno FROM SC WHERE Grade 90)使用子查询进行比较测试通过比较运算符（=、=、(SELECT AVG(Grade)from SC WHERE Cno=c02)使用子查询进行存在性测试例 44查询选修了c01号课程的学生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC

19、 WHERE Sno=Student.Sno AND Cno=c01)数据更改功能插入数据INSERT语句的格式为：INSERT INTO （）VALUES（值列表）例 1将新生记录（9521105，陈冬，男，信息系，18 岁）插入到Student 表中。INSERT INTO Student VALUES(9521105,陈冬,男,18,信息系)更新数据（UPDATE)UPDATE语句的语法格式为:UPDATE SET ,n WHERE 例 2将 9512101学生的年龄改为21 岁。UPDATE Student SET Sage=21 WHERE Sno=9512101 删除数据(DELE

20、TE)DELETE 语句的语法格式为：DELETE FROM WHERE 例 3删除计算机系不及格学生的修课记录用子查询实现:DELETE FROM SC WHERE Grade 60 AND Sno IN(SELECT Sno FROM Stude WHERE Sdept=计算机系)用多表连接实现:DELETE FROM SC FROM SC JOIN Student ON SC.Sno=Student.Sno WHERE Sdept=计算机系 AND Grade 60定义视图名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 12 页 -HFUT_Yan x 9 CREATE V

21、IEW （视图列名表）AS 子查询语句例 1建立信息系学生的视图。CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept=信息系 例 4定义一个反映学生出生年份的视图CREATE VIEW BT_S(Sno,Sname,Sbirth)AS SELECT Sno,Sname,2006-Sage FROM Student 删除视图DROP VIEW 视图可简化查询语句例：将信息系学生的视图中学号为9512102 的学生姓名改为“刘呈”UPDATE IS_Student SET Sname=刘呈 WHERE S

22、no=9512102例：在信息系学生的视图中找出年龄小于20 岁的学生SELECT Sno,Sage FROM IS_Student WHERE Sage 20 Unit 4 数据库设计的基本步骤需求分析阶段（信息要求、处理要求、安全性与完整性要求）概念结构设计阶段逻辑结构设计阶段物理结构设计阶段数据库实施阶段数据库运行和维护阶段DBMS常用存取方法：索引方法，目前主要是B+树索引方法聚簇（Cluster）方法HASH方法UNIT 5 DBMS对数据库的安全保护功能是通过四方面实现的，即安全性控制、完整性控制、并发性控制和数据库恢复。完整性控制数据库的完整性是指保护数据库中数据的正确性、有效性

23、和相容性，防止错误的数据进入数据库造成无效操作。关系模型的完整性包括实体完整性，参照完整性和用户定义完整性。对于违反实体完整性和用户定义完整性规则的操作一般都是采用拒绝执行的方式进行处理。完整性约束条件的作用对象可以是表、元组和列。并发控制数据库的并发操作导致的数据库不一致性主要有以下四种：丢失修改（Lost Update）：当两个事务T1和 T2读入同一数据做修改，并发执行时，T2把 T1或 T1把 T2的修改结果覆盖掉。名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 12 页 -HFUT_Yan x 10 污读（Dirty Read）:事务 T1更新了数据R，事务 T2读取了

24、更新后的数据R，事务 T1由于某种原因被撤消，修改无效，数据R 恢复原值。事务T2得到的数据与数据库的内容不一致，这种情况称为“污读”。不可重读（Unrepeatable Read）:事务 T1读取了数据后，事务T2更新了 T1读取的数据，当事务 T1再读取数据以进行相同操作时，得到的两次值不一致，这种情况称为“不可重读”产生“幽灵”数据:指当事务T1 按一定条件从数据库中读取了某些数据记录后，事务T2 删除了其中的部分记录，或者在其中添加了部分记录，则当T1 再次按相同条件读取数据时，发现其中莫名其妙地少了（对删除）或多了（对插入）一些记录。这样的数据对T1 来说就是“幽灵”数据或称“幻影”

25、数据。并发控制的主要方式是封锁机制，即加锁（Locking）。排它锁（Exclusive Lock，又称写锁，简称为X 锁）-事务 T 对数据对象A加了 X锁，则允许 T读取和修改A，但不允许其它事务再对A 加任何类型的锁,直到 T释放了 A 上的锁。共享锁（Share Lock，又称读锁，,简称为 S锁）-事务 T对数据对象A 加了S锁，则事务T 可以读 A，但不能修改A，其它事务只能再对A 加 S锁，而不能加 X锁，直到 T 释放了 A 上的 S锁。三个封锁协议的主要区别在于预防死锁两种方法：一次封锁法、顺序封锁法数据库的恢复数据库运行故障：事务故障（可以利用日志文件撤消此事务对数据库已进

26、行的修改）系统故障介质故障（重装数据库，然后利用备份或镜像设备恢复数据库。）UNIT 6 使用Transact-SQL语言创建数据库CREATE DATABASE 数据库名ON ,n LOG ON ,n 例 2：创建一个数据库，数据库名称为：“人事信息数据库”，此数据库包含一个数据文件和一个事务日志文件。数据文件只有主数据文件，其逻辑文件名为“人事信息数据库”，其物理文件名为“人事信息数据库.mdf”，存放位置在默认目录下，其初始大小为10MB，最大大小为 30MB，自动增长时的递增量为5MB。事务日志文件的逻辑文件名为“人事信息日志”，X锁（对写数据）S锁（对只读数据）不 丢 失 修 改（写

27、）不 读 脏 数据（读）可 重 复 读（读）一级事务全程加锁不加 二级事务全程加锁事务开始加，读完放 三级事务全程加锁事务全程加锁 名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 12 页 -HFUT_Yan x 11 物理文件名为“人事信息日志.ldf”，也存放在默认目录下，初始大小为3MB，最大大小为12MB，自动增长时的递增量为2MB。CREATE DATABASE 人事信息数据库ON(NAME=人事信息数据库,人事信息数据库.mdf,SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOG ON(NAME=人事信息日志,人事信息日志.ldf,SIZE=3,

28、MAXSIZE=12,FILEGROWTH=2)使用Transact-SQL语句删除数据库语法格式为：DROP DATABASE 数据库名,n 例：用 SQL语句删除 Test1和 Test2数据库：DROP DATABASE Test1,Test2 使用Transact-SQL语句修改数据库（ALTER DATABASE)创建触发器(CREATE TRIGGER)使用SQL语句管理权限（1）授权语句GRANT 例 1：为用户 user1 授予 Student 表的查询权。GRANT SELECT ON Student TO user1 例 2：为用户 user1 授予 SC表的查询权和插入权

29、。GRANT SELECT，INSERT ON SC TO user1 例 3：授予 user1 和 user2 具有创建数据库表和视图的权限。GRANT CREATE TABLE,CREATE VIEW TO user1,user2（2）收回权限语句REVOKE例 1：收回用户user1 授予 Student 表的查询权。REVOKE SELECT ON Student FROM user1 例 2：收回授予user1 创建数据库表的权限。REVOKE CREATE TABLE FROM user1（3）拒绝权限语句DENY 例 1：拒绝用户user1 对 Student 表的修改权。DEN

30、Y UPDATE ON Student TO user1 例 2：拒绝 user1 创建视图的权限。DENY CREATE VIEW TO user1 使用Transact-SQL的备份语句进行备份（1）备份数据库的语句格式为：BACKUP DATABASE 数据库名TO|DISK|TAPE =物理备份文件名 WITH DIFFERENTIAL ,INIT|NOINIT （2）备份数据库日志的语句格式为：名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 12 页 -HFUT_Yan x 12 BACKUP LOG 数据库名TO|DISK|TAPE =物理备份文件名 WITH IN

31、IT|NOINIT ,NO_LOG|TRUNCATE_ONLY|NO_TRUNCATE 例 1对学生管理数据库进行完全备份，并备份到MyBK_1 备份设备上（假设此备份设备已创建好）。BACKUP DATABASE 学生管理数据库TO MyBK_1 例 2对学生管理数据库进行完全备份，并覆盖掉备份设备上已有的内容BACKUP DATABASE 学生管理数据库TO MyBK_1 WITH INIT 例3对学生管理数据库进行事务日志备份，并备份到MyBKLog1 备份设备上。BACKUP LOG 学生管理数据库 TO MyBKLog1 DIFFERENTIAL：表示进行差异备份；INIT：表示本次备份数据库将重写备份设备，即覆盖掉本设备上以前进行的所有备份；NOINIT：表示本次备份数据库将追加到备份设备上，即不覆盖掉本设备上以前进行的所有备份；使用Transact-SQL语句实现恢复例 1假设已对学生管理数据库进行了完全备份，并备份到MyBK_1 备份设备上，假设此备份设备只含有对学生管理数据库的完全备份。则恢复学生管理数据库的备份的语句为：RESTORE DATABASE 学生管理数据库FROM MyBK_1 名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 12 页 -