数据库知识点整理(全)

UNIT 1四个基本概念1数据（Data）:数据库中存储的基本对象2数据库的定义：数据库（Database,简称DB）是长期储存在计算机内、有组织的、可共享的大 量数据集合3数据库管理系统（简称DBMS）：位于用户与操作系统之间的一层数据管理软件（系统软件）。用途:科学地组织和存储数据;高效地获取和维护数据主要功能: 数据定义功能; 数据操纵功能; 数据库的运行管理; 数据库的建立和维 护功能（实用程序）4.数据库系统（Database system,简称DBS）：指在计算机系统中引入数据库后的系统 数据库系统的构成数据库数据库管理系统（及其开发工具）应用系统数据库管理员（DBA）和用户数据管理技术的发展过程人工管理阶段文件系统阶段数据库系统阶段数据库系统管理数据的特点如下（1） 数据共享性高、冗余少；（2） 数据结构化；（3） 数据独立性高；（4） 由 DBMS 进行统一的数据控制功能数据模型用来抽象、表示和处理现实世界中的数据和信息的工具。通俗地讲数据模型就是现实世界数 据的模拟。数据模型三要素。数据结构：是所研究的对象类型的集合,它是刻画一个数据模型性质最重要的方面;数据结 构是对系统静态特性的描述数据操作:对数据库中数据允许执行的操作及有关的操作规则;对数据库中数据的操作主要有 查询和更改（包括插入、修改、删除） ;数据操作是对系统动态特性的描述数据的约束条件:数据及其联系应该满足的条件限制E-R图实体：矩形框表示属性：椭圆形（或圆角矩形）表示.院联系:组感成绩n课趕m.讲授如师姓学生卫Y迤4一- iWT I入謠菱形表示课:讲授组织层数据模型层次模型网状模型关系模型（用“二维表”来表示数据之间的联系）基本概念: 关系（Relation）:个关系对应通常说的一张表元组（记录）：表中的一行 属性（字段） : 表中的一列，给每一个属性名称即属性名 分量 :元组中的一个属性值，分量为最小单位，不可分 主码（Key）：表中的某个属性组，它可以唯一确定一个元组。 域（Domain）：属性的取值范围。关系模式：对关系的描述。一般表示为：关系名（属性1,属性2,，属性n） 关系模型的数据完整性约束 实体完整性 参照完整性 用户定义的完整性DBS三级模式结构外模式、概念模式、内模式（一个数据库只有一个内模式）数揭库系统的模式结构UNIT 2在进行数据库的操作时，会出现以下几方面的问题：1.数据冗余; 2. 插入异常; 3. 删除异常; 4. 更新异常 好的关系模式应避免以上问题函数依赖设有关系模式R(A1,A2,-,An), X和/均为A1,A2,-,An的子集如果x-y,但/不包含于X,则称XT是非平凡的函数依赖。如果x-y,则称x为决定因子。如果y函数不依赖于X,则记作X y。如果x-y,并且y-x,则记作x y。范式从外到里依此增加 模式分解的准则： 模式分解具有无损连接性；模式分解能够保持函数依赖>规范化理论主要是研究关系中各属性之间的依赖关系，根据依赖关系的不同，我们 介绍了不包含子属性的第一范式,到消除了属性间的部分依赖关系的第二范式,>再到消除了属性间的传递依赖关系的第三范式，最后到每个决定因子都必须是候选 码的 BCNF。>范式的每一次升级都是通过模式分解实现的，在进行模式分解时应注意保持分解后 的关系能够具有无损连接性并能保持原有的函数依赖关系。>对于一般的数据库应用来说，设计到第三范式就足够了。因为规范化程度越高分解 得越细,表的个数越多,则在检索操作时会因连接而降低检索效率。例：S-D-L (Sno, Dept, Loc)有函数依赖:Sno - Dept, Dept - Loc不是第三范式的。至少可以有三种分解方案,分别为方案 1： S-L(Sno,Loc), D-L (Dept, Loc)，将 S-D-L分解投影得到 S-L和 D-L 关系O-L门然连按结论：右棗巾不汹足无册逢接性方案 2： S-D( Sno，Dept)，S-L( Sno，Loc)力 牡2 将S-D-L分解投影側到吕D和S-L关系SnoI>?ptSOIDISO 2T>2S03D2SU4L>3S-DSaioLocSU1LlS(I2L2S03L2S041L1S-L自然连接表2 3如果假盪学生SOSAkDZ寒转到广D3索，则霜在表-门CS03, D2> 改湘(SO3, 河肘还需匿在表S-L <503, L2> 改为 <S03, L1 > 如黒这两个修改饋育同时进则数 抑曲屮就会出现不一致信息"迭是由于这杆外焼得到的翩十关累模式浬冇保持原兔的 函数依娩关辜适曲的亠 原召的函数依*ftDep七一 Lou在分牌后聘比了两牛笑莠模式上 “囚Utl令解童李2没有輝持愿克的函数依極关塞.也不呈好的分解互法"结论：方案2满足无搠雄接性但没有保持原有的函数依赖关系.方案 3： S-D (Sno, Dept), D-L (Dept, Loc)方案3 :将SD-L分解投影得到S-D和D-L关系SnoDeptSOIDIS02D2S03D2S04D3DeptLocDILID2L2D3LI自然连接SnoDeptLocSOIDILIS02D2L2S03D2L2S04D3LIS*DD-L结论：方案3既满足无损连接性，又保持原有的函数依赖关系. 故它是个好的分解方法UNIT 3Sql功能命令动词数据定义CREATE DROP ALTER数据查询SELECT数据操纵INSERT UPDATE DELETE数据控制GRANT REVOKESQL的数据类型：数值型；字符串型；日期时间类型；货币类型 数据定义功能操作对象创建语句删除语句修改语句基本表CREATE TABLEDROP TABLEALTER TABLE索引CREATE INDEXDROP INDEX视图CREATE VIEWDROP VIEW数据库CREATEDATABASEDROPDATABASEALTER DATABASE建立表 CREATE TABLE列名说1fl5n&5£«Snnuait 45鞍为in|-;:Ssex-性别字誓丰，EfcJtte'単露求"立.吕昭年楮Sdepl字符尬，氏區阪默认丹计祿机戏taursczCREATE TABLE Sludent (时(7 ) PRIMARY KESnsime Chflr(1(i) NOT HULUS&ex eMr(2) CHECK：(S5« = ,B, OR Ssejc = pi,)1*Sage tinyinl CHECK (Sage >= 15 AND Sage <=45). * 】总日呂就 曲pi旷f3d '出OJ倍tCrw>字轴=中忙度为m,CrLiuu:嘿稈窑丫荷卒，自更为aaCc 亦!d it事分'ffR'联值大刊Se-aes Let71取但大P&Mod字HIffg*fftAToCREATE TOBLE Couree (Cno charfl0) NOT NULL, Cname Dhar2D) NOT NULL, Credirt tinyinrl CHECK (Ceredrt > 0»r Semester tinyl nt CHECK (Semester 叫 Period 耐 CHECK刑曙说明救is类旳farfr. Qjfeff工阳丿引jh siiri«i l®jh?j环课程却辛符臥乩懂为W :RtW,引州310的丹科片戳GREATElft日LE 5C(Sno char(7|i NOT NULL,CnoNOT NL1LUGrade tln0niLCHECK 4Gf ade >= d and Grade <=巾(1叽PRIMARY KEY ( Snu, Cue ). iFOREIGHKEY( Srw ) REFERENCES Sludsril： iFOREIGN KEY (Cno ) REFERENCES CoursrCnc )例1.为SC表添加“修课类别”列，此列的定义为：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 表名(列名1 asc | desc,.) unique :用于指定为表创建唯一索引，即不允许存在索引值相同的两行。 clustered：用于指定创建的索引为聚簇索引。默认是非聚簇索引。 asc 升序， desc 降序，默认为 asc例 1：为学生表建立按学号升序索引create index student_sno on student(sno)例 2：为选课表按学号升序和课程号降序建唯一索引create unique index SCI on sc(sno， cno desc)例 3: 为学生表按系升序建立聚簇索引。create clustered index TI on student (sdept)删除索引语句基本格式： drop index 表名.索引名 例 4 ：删除表 SC 的索引 SCI。drop index SC.SCI注：索引名前一定要加：表名- 需要哪些列- 来自于哪些表- 根据什么条件查询- 对查询结果进行分组- 指定组的选择条件- 对查询结果进行排序SELECT 目标列名序列FROM 数据源WHERE 检索条件表达式GROUP BY 分组依据列HAVING 组提取条件ORDER BY排序依 据列ORDER BY 排序依据列简单查询 （单表查询） 例 1. 查询全体学生的学号与姓名SELECT Sno，Sname FROM Student比较大小例 8查询所有年龄在 20 岁以下的学生的姓名及年龄。SELECT Sname, Sage FROM Student WHERE Sage 20或：SELECT Sname, Sage FROM Student WHERE NOT Sage = 20 确定范围BETWEENAND 和 NOT BETWEENAND确定集合（初IN 是一个逻辑运算符，可以用来查找属性值属于指定集合的元组使用IN的格式为：列名NOT IN （常量1,常量2, 常量n）IN的含义为：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合 查询条件的记录；NOT IN的含义正好相反：当列中的值与某个常量值相同时，则结果为False，表明此记录为 不符合查询条件的记录；例 12查询信息系、数学系和计算机系学生的姓名和性别。SELECT Sname, Ssex FROM StudentWHERE Sdept IN （'信息系', '数学系', '计算机系'）此句等价于： SELECT Sname, Ssex FROM StudentWHERE Sdept = 信息系' OR Sdept = 数学系' OR Sdept = 计算机系' 字符匹配LIKE 用于查找指定列名与匹配串常量匹配的元组（模糊查询） 匹配串是一种特殊的字符串，它不仅包含普通字符，还可以包括通配符。 通配符用于表示任意的字符或字符串。_：匹配任意一个字符；%：匹配0个或多个字符； ：匹配 中的任意一个字符；卜：不匹配中的任意一个字符。LIKE 运算符的一般形式为： 列名 NOT LIKE 匹配串 在LIKE运算符前边也可以使用NOT运算符，表示对结果取反。例 14 查询姓张'的学生的详细信息。SELECT * FROM Student WHERE Sname LIKE 张%'例 15查询学生表中姓张'、姓李'和姓刘'的学生的情况。SELECT * FROM Student WHERE Sname LIKE 张李刘%' 例16查询名字中第2个字为小'或大'字的学生的姓名和学号。SELECT Sname, Sno FROM Student WHERE Sname LIKE '小、大%'例 17 查询所有不姓“刘”的学生。SELECT Sname FROM Student WHERE Sname 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连接的条件表示必须全部满足所有的条件的结果才为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 GROUP 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, Grade FROM StudentLEFT OUTER JOIN SCON Student.Sno = SC.Sno也可以用右外连接实现：SELECT Student.Sno, Sname,Cno, Grade FROM SCRIGHT OUTER JOIN StudentON Student.Sno = SC.Sno 使用子查询进行基于集合的测试 例 40 查询与“刘晨”在同一个系学习的学生。SELECT Sno, Sname, Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM StudentWHERE Sname = '刘晨') 使用子查询进行基于集合的测试 例 41查询成绩为大于90 分的学生的学号、姓名。SELECT Sno, Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Grade 90 ) 使用子查询进行比较测试通过比较运算符(=、=、=)，将一个表达式的值与子查询返回的值进行比较 例43.查询修了 'c02'课程且成绩高于此课程的平均成绩的学生的学号和成绩。SELECT Sno , Grade FROM SCWHERE Cno = 'c02' and Grade (SELECT AVG(Grade) from SCWHERE Cno = 'c02') 使用子查询进行存在性测试 例44.查询选修了 'c01 '号课程的学生姓名。SELECT Sname FROM StudentWHERE EXISTS(SELECT * FROM SCWHERE Sno = Student.Sno AND Cno =c01')数据更改功能插入数据INSERT语句的格式为:INSERT INTO 表名( 列名表)VALUES (值列表) 例1将新生记录(9521105,陈冬，男，信息系，18岁)插入到Student表中。INSERT INTO Student VALUES (9521105' , 更新数据(UPDATE丿陈冬' , '男', 18, '信息系')UPDATE语句的语法格式为:UPDATE 表名SET 列名=表达式，n WHERE 更新条件 例2将9512101'学生的年龄改为21岁。UPDATE Student SET Sage = 21 WHERE Sno = '9512101'删除数据QEIE7习DELETE语句的语法格式为：DELETE FROM 表名WHERE 删除条件例 3删除计算机系不及格学生的修课记录用子查询实现:用多表连接实现:DELETE FROM SCWHERE Grade < 60 AND Sno IN (SELECT Sno FROM StudeWHERE Sdept = 计算机系' )DELETE FROM SCFROM SC JOIN Student ON SC.Sno =Student.SnoWHERE Sdept =计算机系' AND Grade < 60定义视图CREATE VIEW 视图名（视图列名表） AS子查询语句例 1建立信息系学生的视图。CREATE VIEW IS_StudentASSELECT Sno, Sname, SageFROM Student WHERE Sdept = '信息系'例 4定义一个反映学生出生年份的视图CREATE VIEW BT_S（Sno, Sname, Sbirth）ASSELECT Sno, Sname, 2006-SageFROM Student删除视图DROP VIEW 视图名视图可简化查询语句例：将信息系学生的视图中学号为9512102 的学生姓名改为“刘呈”UPDATE IS_Student SET Sname=刘呈'WHERE Sno =9512102'例：在信息系学生的视图中找出年龄小于20 岁的学生SELECT Sno, SageFROM IS_Student WHERE Sage 20Unit 4数据库设计的基本步骤 需求分析阶段（信息要求、处理要求、安全性与完整性要求）概念结构设计阶段逻 辑结构设计阶段物理结构设计阶段数据库实施阶段数据库运行和维护阶段DBMS常用存取方法 索引方法，目前主要是B+树索引方法聚簇（Cluster）方法HASH 方法UNIT 5DBMS对数据库的安全保护功能是通过四方面实现的，即安全性控制、完整性控制、并发性 控制和数据库恢复。完整性控制数据库的完整性是指保护数据库中数据的正确性、有效性和相容性,防止错误的数据进入数 据库造成无效操作。关系模型的完整性包括实体完整性，参照完整性和用户定义完整性。对于违反实体完整性和用户定义完整性规则的操作一般都是采用拒绝执行的方式进行处理。 完整性约束条件的作用对象可以是表、元组和列。并发控制数据库的并发操作导致的数据库不一致性主要有以下四种：丢失修改（Lost Update）：当两个事务T1和T2读入同一数据做修改，并发执行时，T2把T1 或T1把T2的修改结果覆盖掉。污读（DirtvRead）:事务T1更新了数据R,事务T2读取了更新后的数据R,事务T1由于某 种原因被撤消，修改无效，数据R恢复原值。事务T2得到的数据与数据库的内容不一致， 这种情况称为“污读”。不可重读（Unrepeatable Read）:事务T1读取了数据后，事务T2更新了 T1读取的数据，当事 务T1再读取数据以进行相同操作时，得到的两次值不一致，这种情况称为“不可重读"产生“幽灵”数据:指当事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删 除了其中的部分记录，或者在其中添加了部分记录，则当T1再次按相同条件读取数据时， 发现其中莫名其妙地少了（对删除）或多了（对插入）一些记录。这样的数据对T1来说就 是“幽灵”数据或称“幻影”数据。并发控制的主要方式是封锁机制，即加锁L&king）。排它锁（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锁。三个封锁协议的主要区别在于X锁（对写数据）S锁（对只读数据）不丢失修改（写）不读脏数据（读）可重复读（读）一级事务全程加锁不加V二级事务全程加锁事务开始加，读完放VV三级事务全程加锁事务全程加锁VVV预防死锁两种方法：一次封锁法、顺序封锁法数据库的恢复 数据库运行故障：事务故障（可以利用日志文件撤消此事务对数据库已进行的修改）系统故障 介质故障（重装数据库，然后利用备份或镜像设备恢复数据库。）UNIT 6使用 Transact-SQL 语言创建数据库CREATE DATABASE 数据库名ON 文件格式 , n LOG ON 文件格式 , n 例 2：创建一个数据库，数据库名称为：“人事信息数据库”，此数据库包含一个数据文件和 一个事务日志文件。数据文件只有主数据文件，其逻辑文件名为“人事信息数据库”，其物 理文件名为“人事信息数据库.mdf”，存放位置在默认目录下，其初始大小为10MB,最大大 小为30MB,自动增长时的递增量为5MB。事务日志文件的逻辑文件名为“人事信息日志”， 物理文件名为“人事信息日志df”，也存放在默认目录下，初始大小为3MB，最大大小为 12MB，自动增长时的递增量为2MB。CREATE DATABASE 人事信息数据库ON(NAME =人事信息数据库，FILENAME = 'C:program filesMicrosoft SQL ServerMssqlData人事信息数据库.mdf ', SIZE = 10,MAXSIZE = 30,FILEGROWTH = 5 )LOG ON( NAME =人事信息日志,FILENAME = 'C:program filesMicrosoft SQL ServerMssqlDataA 事信息日志.ldf ',SIZE = 3,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表的查询权和插入权。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 表的修改权。DENY 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)备份数据库日志的语句格式为：BACKUP LOG 数据库名 TO < 备份设备名 > | DISK | TAPE = 物理备份文件名'WITH INIT | NOINIT , NO_LOG | TRUNCATE_ONLY | NO_TRUNCATE 例1对学生管理数据库进行完全备份，并备份到MyBK_1备份设备上（假设此备份设备已 创建好）。BACKUP DATABASE 学生管理数据库 TO MyBK_1例 2对学生管理数据库进行完全备份，并覆盖掉备份设备上已有的内容BACKUP DATABASE 学生管理数据库 TO MyBK_1 WITH INIT例3.对学生管理数据库进行事务日志备份，并备份到MyBKLogl备份设备上。BACKUP LOG学生管理数据库TO MyBKLogl> DIFFERENTIAL：表示进行差异备份；> INIT :表示本次备份数据库将重写备份设备，即覆盖掉本设备上以前进行的所有备 份；> NOINIT:表示本次备份数据库将追加到备份设备上，即不覆盖掉本设备上以前进行 的所有备份；使用Transact SQL语句实现恢复例1.假设已对学生管理数据库进行了完全备份，并备份到MyBK_1备份设备上，假设此备 份设备只含有对学生管理数据库的完全备份。则恢复学生管理数据库的备份的语句为:RESTORE DATABASE 学生管理数据库 FROM MyBK_1