数据库知识点整理).doc

HFUT_YanxUNIT 1四个基本概念1.数据(Data):数据库中存储的基本对象2.数据库的定义 :数据库(Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据集合3.数据库管理系统（简称DBMS）：位于用户与操作系统之间的一层数据管理软件（系统软件）。用途:科学地组织和存储数据;高效地获取和维护数据主要功能: 数据定义功能; 数据操纵功能; 数据库的运行管理; 数据库的建立和维护功能(实用程序)4.数据库系统（Database System，简称DBS）：指在计算机系统中引入数据库后的系统数据库系统的构成 数据库 数据库管理系统（及其开发工具） 应用系统 数据库管理员（DBA)和用户数据管理技术的发展过程人工管理阶段文件系统阶段数据库系统阶段数据库系统管理数据的特点如下(1) 数据共享性高、冗余少；(2) 数据结构化；(3) 数据独立性高；(4) 由DBMS进行统一的数据控制功能数据模型用来抽象、表示和处理现实世界中的数据和信息的工具。通俗地讲数据模型就是现实世界数据的模拟。数据模型三要素。数据结构：是所研究的对象类型的集合，它是刻画一个数据模型性质最重要的方面;数据结构是对系统静态特性的描述数据操作:对数据库中数据允许执行的操作及有关的操作规则;对数据库中数据的操作主要有查询和更改（包括插入、修改、删除）;数据操作是对系统动态特性的描述数据的约束条件:数据及其联系应该满足的条件限制E-R图实体：矩形框表示属性：椭圆形(或圆角矩形)表示联系：菱形表示组织层数据模型层次模型网状模型关系模型(用“二维表”来表示数据之间的联系)基本概念:l 关系（Relation） :一个关系对应通常说的一张表l 元组（记录）: 表中的一行l 属性（字段） ： 表中的一列，给每一个属性名称即属性名l 分量 ：元组中的一个属性值，分量为最小单位，不可分l 主码（Key）：表中的某个属性组，它可以唯一确定一个元组。l 域（Domain）：属性的取值范围。l 关系模式 ：对关系的描述。一般表示为： 关系名（属性1，属性2，属性n）关系模型的数据完整性约束实体完整性 参照完整性 用户定义的完整性DBS三级模式结构: 外模式、概念模式、内模式(一个数据库只有一个内模式)UNIT 2在进行数据库的操作时，会出现以下几方面的问题：1.数据冗余; 2. 插入异常; 3. 删除异常; 4. 更新异常好的关系模式应避免以上问题函数依赖设有关系模式R(A1,A2,An)，X和Y均为A1,A2,An的子集如果XY，但Y不包含于X，则称XY是非平凡的函数依赖。如果XY，则称X为决定因子。如果Y函数不依赖于X，则记作X Y 。如果XY，并且YX，则记作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关系方案2：S-D（Sno，Dept），S-L（Sno，Loc）方案3：S-D（Sno，Dept），D-L（Dept，Loc）UNIT 3Sql功能命令动词数据定义CREATE DROP ALTER数据查询SELECT数据操纵INSERT UPDATE DELETE数据控制GRANT REVOKESQL的数据类型：数值型；字符串型；日期时间类型；货币类型 数据定义功能DROP DATABASECREATE DATABASE数据库 DROP VIEWCREATE VIEW视图DROP INDEXCREATE INDEX索引ALTER TABLEDROP TABLECREATE TABLE基本表修改语句删除语句创建语句操作对象 ALTER DATABASE建立表CREATE TABLE 例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 表名 (asc | desc ，）l unique：用于指定为表创建唯一索引，即不允许存在索引值相同的两行。 l clustered：用于指定创建的索引为聚簇索引。默认是非聚簇索引。l 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注：索引名前一定要加：表名12SELECT FROM WHERE GROUP BY HAVING ORDER BYORDER BY - 需要哪些列- 来自于哪些表- 根据什么条件查询- 对查询结果进行分组- 指定组的选择条件- 对查询结果进行排序简单查询 （单表查询）例1. 查询全体学生的学号与姓名 SELECT Sno，Sname FROM Student比较大小例8查询所有年龄在20岁以下的学生的姓名及年龄。SELECT Sname, Sage FROM Student WHERE Sage = 20确定范围 BETWEENAND和NOT BETWEENAND确定集合（IN)IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组使用IN的格式为：列名 NOT IN （常量1, 常量2, 常量n）IN的含义为：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；NOT IN的含义正好相反：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录；例12查询信息系、数学系和计算机系学生的姓名和性别。SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系, 数学系, 计算机系)此句等价于：SELECT Sname, Ssex FROM Student WHERE 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 Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno也可以用右外连接实现： SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT OUTER JOIN Student ON Student.Sno = SC.Sno使用子查询进行基于集合的测试例40查询与“刘晨”在同一个系学习的学生。 SELECT Sno, Sname, Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname = 刘晨)使用子查询进行基于集合的测试例41查询成绩为大于90分的学生的学号、姓名。SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Grade 90 )使用子查询进行比较测试通过比较运算符（=、=、 ( SELECT AVG(Grade) from SC WHERE Cno = c02)使用子查询进行存在性测试例44查询选修了c01号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC 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删除数据(DELETE)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.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)AS SELECT Sno, Sname, 2006-Sage FROM Student删除视图DROP VIEW 视图可简化查询语句例：将信息系学生的视图中学号为9512102的学生姓名改为“刘呈”UPDATE IS_Student SET Sname=刘呈 WHERE Sno =9512102例：在信息系学生的视图中找出年龄小于20岁的学生SELECT Sno, Sage FROM IS_Student WHERE Sage 20Unit 4数据库设计的基本步骤需求分析阶段（信息要求、处理要求、安全性与完整性要求）概念结构设计阶段逻辑结构设计阶段物理结构设计阶段数据库实施阶段数据库运行和维护阶段DBMS常用存取方法：索引方法，目前主要是B+树索引方法聚簇（Cluster）方法HASH方法UNIT 5DBMS对数据库的安全保护功能是通过四方面实现的，即安全性控制、完整性控制、并发性控制和数据库恢复。完整性控制数据库的完整性是指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库造成无效操作。 关系模型的完整性包括实体完整性，参照完整性和用户定义完整性。对于违反实体完整性和用户定义完整性规则的操作一般都是采用拒绝执行的方式进行处理。完整性约束条件的作用对象可以是表、元组和列。并发控制数据库的并发操作导致的数据库不一致性主要有以下四种：丢失修改（Lost Update）：当两个事务T1和T2读入同一数据做修改，并发执行时， T2把T1或T1把T2的修改结果覆盖掉。 污读（Dirty Read）: 事务T1更新了数据R，事务T2读取了更新后的数据R，事务T1由于某种原因被撤消，修改无效，数据R恢复原值。事务T2得到的数据与数据库的内容不一致，这种情况称为“污读”。不可重读（Unrepeatable Read）:事务T1读取了数据后，事务T2更新了T1读取的数据，当事务T1再读取数据以进行相同操作时，得到的两次值不一致，这种情况称为“不可重读”产生“幽灵”数据:指当事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中的部分记录，或者在其中添加了部分记录，则当T1再次按相同条件读取数据时，发现其中莫名其妙地少了（对删除）或多了（对插入）一些记录。这样的数据对T1来说就是“幽灵”数据或称“幻影”数据。并发控制的主要方式是封锁机制，即加锁（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锁。三个封锁协议的主要区别在于X锁（对写数据）S锁（对只读数据） 不丢失修改（写） 不读脏数据（读）可重复读（读）一级事务全程加锁不加 二级事务全程加锁事务开始加，读完放 三级事务全程加锁事务全程加锁 预防死锁两种方法：一次封锁法、顺序封锁法数据库的恢复数据库运行故障：事务故障（可以利用日志文件撤消此事务对数据库已进行的修改）系统故障介质故障（重装数据库，然后利用备份或镜像设备恢复数据库。）UNIT 6使用Transact-SQL语言创建数据库CREATE DATABASE 数据库名 ON , n LOG ON , n 例2：创建一个数据库，数据库名称为：“人事信息数据库”，此数据库包含一个数据文件和一个事务日志文件。数据文件只有主数据文件，其逻辑文件名为“人事信息数据库”，其物理文件名为“人事信息数据库.mdf”，存放位置在默认目录下，其初始大小为10MB，最大大小为30MB，自动增长时的递增量为5MB。事务日志文件的逻辑文件名为“人事信息日志”，物理文件名为“人事信息日志.ldf”，也存放在默认目录下，初始大小为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 ServerMssqlData人事信息日志.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 对学生管理数据库进行事务日志备份，并备份到MyBKLog1备份设备上。 BACKUP LOG 学生管理数据库 TO MyBKLog1 DIFFERENTIAL：表示进行差异备份； INIT：表示本次备份数据库将重写备份设备，即覆盖掉本设备上以前进行的所有备份； NOINIT：表示本次备份数据库将追加到备份设备上，即不覆盖掉本设备上以前进行的所有备份；使用Transact-SQL语句实现恢复例1假设已对学生管理数据库进行了完全备份，并备份到MyBK_1备份设备上，假设此备份设备只含有对学生管理数据库的完全备份。则恢复学生管理数据库的备份的语句为：RESTORE DATABASE 学生管理数据库 FROM MyBK_1