数据库总复习

1、总复习1关系数据库原理,第1部分 关系数据库原理学习要求,掌握数据库的基本概念 掌握ER概念模型 掌握关系数据模型 掌握ER图转化为关系模式/表 掌握关系规范化 掌握关系的三种范式,一、数据库的基本概念,数据(Data) 是数据库中存储的基本对象，是描述事物的符号。如：数字、文字、图形、图像、声音等。 数据库(Database，DB) 是储存在计算机内、有组织的、可共享的数据集合。 数据库管理系统(DBMS) 是位于用户与操作系统之间的管理数据库的软件。如：SQL Server 2000 数据库系统(DBS) 是指在计算机系统中引入数据库后的系统，包括：计算机系统、数据库、数据库管理系统、人员

2、。,二、ER概念模型,数据模型的含义 对现实世界（存在于人脑之外的客观世界）的模拟。 数据模型的类型 概念数据模型、逻辑数据模型、物理数据模型 概念模型的含义 用于信息世界（现实世界在人脑中的反映）的建模，是现实世界到信息世界的第一次抽象。,二、ER概念模型,概念模型的表示方法 实体联系图(ER图) ER图的基本要素 实体：现实世界中任何可以被认识、区分的事物。 属性：实体所具有的特性。 实体间的联系 一对一联系、一对多联系、多对多联系 课堂练习：判断实体间的联系类型。 学校中有若干个系和若干个教师，每个教师只能属于一个系，一个系可以有多名教师，系与教师的关系类型是_。教师与学生的关系类型是_

3、。,二、ER概念模型,ER图的绘制方法 长方形实体 椭 圆属性 菱 形联系 ER图的设计原则： 确定实体类型及属性。 确定联系类型及属性。 把实体类型和联系类型组合成ER图。,二、ER概念模型,课堂练习：某工厂生产多种产品，每种产品由不同的零件组装而成，有的零件可用在不同的产品上。产品有产品号和产品名两个属性，零件有零件号和零件名两个属性。根据语义设计ER模型。 分析：ER模型中有两个实体，产品（产品号，产品名），零件（零件号，零件名），产品与零件是M;N的联系。,零件,产品,组装,三、关系数据模型,逻辑数据模型 含义：是用户从数据库所看到的数据模型。 用概念数据模型表示的数据必须转化为逻辑数

4、据模型表示的数据，才能在DBMS中实现。 类型（四种） 关系模型（最常用） 例 ： SQL Server 2000 层次模型 网状模型 面向对象模型,三、关系数据模型,关系数据模型的含义 用二维表格结构表示实体及实体之间的联系的数据模型 完成信息世界到机器世界的第二次抽象。 具有如下性质的二维表格，又称为一个关系。 表中每一个属性值都是不可分解的。 表中不允许出现相同的行/列。 表中没有行序、没有列序。 说明：1个关系就是1张二维表，反过来则不一定。,四、ER图转化为关系模式/表,关系模式的表示方法 用关系名和包含的属性名的集合表示。 例如：学生（学号，姓名，性别，年龄，系编号） 关系模式与关

5、系(表)的区别 转化原则 每个实体转换为一个关系。 每个联系也转换成一个关系。 对于1:1的联系，每个实体的码均是该联系关系的候选码 对于1:N的联系，关系的码是N端实体的码。 对于M:N的联系，关系的码是诸实体码的组合。 有相同码的关系可以合并。,四、ER图转化为关系模式/表,课堂练习:将下面的ER图转化成关系模式,并指出主码与外码。,零件(零件号，零件名) 产品(产品号，产品名) 组装(零件号，产品号，零件个数） 主码：零件号+产品号 外码：产品号、零件号,四、ER图转化为关系模式/表,问题：在参照完整性中，为什么外键属性的值可以为空？什么情况下才为空？ 答:(1)外码属性的值可以为空,它

6、表示该属性的值尚未确定。(2)为空的前提条件是该外码属性不是其所在关系的主属性。例如： 学生(学号,姓名,性别,专业号) 专业(专业号,专业名) 其中：专业号是学生表中的一个外码，不是学生表的主属性，可以为空，其语义是该学生的专业尚未确定。 但若该外码属性是其所在关系的主属性，则不能为空。例如： 选修(学号,课程号,成绩) 课程(课程号,课程名,学分) 在选修表中，课程号虽然也是一个外部码，但它又是选修表的主属性，所以不能为空，因为关系模型必须满足实体完整性。,五、关系的规范化,函数依赖 是指关系中各属性或属性组之间的相互依赖关系。 函数依赖的类型 完全函数依赖 部分函数依赖 传递函数依赖 例

7、如：SCD(学号,姓名,课程号,成绩,所在学院,学院电话) “学号+课程号” “成绩”，是完全函数依赖。 “姓名、所在学院、学院电话”与主码是部分函数依赖。 “学号”“所在学院”“学院电话”，则“学院电话”传递依赖于“学号”。,五、关系的规范化,关系规范化的含义 为了区分关系模式的优劣，把关系模式分为各种不同等级的范式。 将低级范式转换为若干个高级范式过程。 范式的类型 1NF：关系模式R的所有属性的值都不可再分解值。 2NF：关系模式R为1NF，且R中每个非主属性完全函数依赖于R的主码(复合码) 。 3NF：关系模式R为2NF，且R中每个非主属性都不传递函数依赖于R的某个候选码。,五、关系的

8、规范化,将关系模式进行分解:,SC（学号，课程号，成绩） SD1（学号，姓名，所在学院） SD2（学院名，学院电话）,五、关系的规范化,小结： 1NF中的每个属性值是不可再分的，它是关系模式的最低要求。 消除1NF中的部分函数依赖，可转换为2NF。 消除2NF中的传递函数依赖，可转换为3NF。,练习是非题,数据库系统不包含数据库管理系统。 关系运算中的选择是按照给定的条件选择记录组成一个新的表。 关系模式满足第二范式，则它一定满足第一范式 参照完整性与引用完整性是两个不同的概念。 实体完整性是指表中的每一行都必须能够唯一标识，且不存在重复的数据行。 在WindowsXP操作系统上能够安装SQL

9、 Server 2000个人版的服务器。,总复习2数据库和表的管理,引：,数据库的设计步骤： 收集、分析数据 建立概念模型（ER图） 建立逻辑模型（关系模式） 关系规范化 1NF 2NF 3NF 创建数据库,第一次抽象,第二次抽象,第2部分 数据库和表的管理学习要求,数据库概述 掌握数据库的创建 掌握数据库的管理 掌握数据表和表约束的创建 掌握数据表的管理 掌握数据表的操作,一、数据库概述,SQL Server数据库的含义 是存放各种对象(表、视图、规则、默认值、存储过程、用户、角色等)的逻辑实体。 数据库的结构 数据库文件有三种类型： 主数据文件(有且只有只有1个，扩展名是 .mdf) 次数

10、据文件(0个或多个，扩展名是 .ndf) 事务日志文件(1个或多个，扩展名是 .ldf) 文件组的类型 主文件组:包括主数据文件，没有指明组的其它文件 次文件组:用户自定义文件组,一、数据库概述,系统数据库（6个） master数据库：记录了SQL Server的所有系统信息。 model数据库：是系统所有数据库的模板。 所有在系统中创建的新数据库的内容，在刚创建时都和model数据库完全一样。 msdb数据库：是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。 tempdb数据库：是一个临时数据库，用于存放所有连接到系统的用户的临时表和临时存储过程。比如：查询时会用到它。

11、pubs和northwind数据库,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名,创建1个大小与model的数据库完全相同的用户数据库。 其初始大小为1MB,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名 ON PRIMARY ( NAME = 逻辑文件名， FILENAME = 磁盘文件名 ，SIZE = 初始大小 ，MAXSIZE = 最大容量 ，FILEGROWTH = 增长量 ） , n ,主文件组,主文件组的 次数据文件n,创建自定义大小的用户数据库,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名 ON PRIMAR

12、Y ( NAME = 逻辑文件名， FILENAME = 磁盘文件名 ，SIZE = 初始大小 ，MAXSIZE = 最大容量 ，FILEGROWTH = 增长量 ） , n ， FILEGROUP 组文件名 () , n ,主文件组,次文件组,次文件组的 次数据文件,主文件组的 次数据文件n,主数据文件,主数据文件,创建自定义大小的用户数据库,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名 ON PRIMARY ( NAME = 逻辑文件名， FILENAME = 磁盘文件名 ，SIZE = 初始大小 ，MAXSIZE = 最大容量|unlimited ，FILEGRO

13、WTH = 增长量 ） , n ， FILEGROUP 组文件名 () , n LOG ON ( NAME = 逻辑文件名， FILENAME = 磁盘文件名 ，SIZE = 初始大小 ，MAXSIZE = 最大容量|unlimited ，FILEGROWTH = 增长量 ） ， n ,主文件组,次文件组,次文件组的 次数据文件,主文件组的 次数据文件n,主数据文件,主数据文件,日志文件n,创建自定义大小的用户数据库,例: 创建d1的数据库,create database d1 on primary ( name=data1, filename= d:sqld1_data1.mdf , siz

14、e=1, maxsize=10, filegrowth=20% ) , ( name=data2, filename= d:sqld1_data2.ndf , size=4, maxsize=10, filegrowth=1 ) , filegroup g1 ( name=data3, filename= d:sqld1_data3.ndf , size=5, maxsize=unlimited, filegrowth=1 ) log on ( name=log1, filename= d:sqld1_log1.ldf , size=1, maxsize=unlimited, filegrow

15、th=20% ),三、数据库的管理,显示数据库 exec sp_helpdb 数据库名 修改数据库： alter database 修改数据库名 增加文件组 重命名文件组 删除文件组 增加数据库中的次要数据文件和事务日志文件 修改文件属性 文件的初始大小、最大容量、增长幅度 删除数据库中的次要数据文件和事务日志文件,三、数据库的管理,alter database d1 add filegroup g2 alter database d1 modify filegroup g2 name=g3 alter database d1 remove filegroup g3,在数据库d1中增加一个文件

16、组g2,将数据库d1中的文件组g2更名为g3,删除数据库d1中的文件组g3,注意：删除文件组前必须保证该文件组为空，若其中有文件则应先删除。,三、数据库的管理,alter database d1 add file (name=data4, filename=d:sqld1_data4.mdf , size=1, maxsize=5, filegrowth=1) , (name=data5, filename=d:sqld1_data5.ndf , size=1, maxsize=5, filegrowth=1) to filegroup g1 alter database d1 add log

17、 file (name=log2, filename=d:sqld1_log2.ldf , size=1, maxsize=5, filegrowth=1),增加到次文件组g1,增加数据文件到主文件组,增加日志文件,三、数据库的管理,alter database d1 modify file (name=data2, size=10, maxsize=20, filegrowth=10%) alter database d1 remove file data4 alter database d1 modify name= aa drop database aa 说明：当数据库处于下列情况不能被

18、删除。 当有用户正在数据库使用时。 当数据库正在被恢复时。 当数据库正在参与复制时。,修改数据文件的属性,删除数据文件,将数据库d1的名字修改为aa,删除数据库,四、数据表和表约束的创建,建表时要涉及到的问题 表的列数、列名 每列的数据类型 列上的约束：防止出现非法数据，保证数据完整性。 建表的命令格式 create table 表名 (字段名 类型(长度) 列级完整性约束条件 ,n ) 创建表约束的方法 建表时：在每列的定义后，或在所有列定义后创建约束 表已存在：只能通过修改表，添加约束,四、数据表和表约束的创建,约束的类型 主键（PRIMARY KEY）约束 惟一（UNIQUE）约束 外键

19、（FOREIGN KEY）约束 检查（CHECK）约束 默认值（DEFAULT）约束 空值（NULL）约束 主键约束与惟一性约束的区别： 表中只能定义1个主键约束，但可定义多个惟一约束。 定义了惟一约束的列可以为空值，而定义了主键约束的列不能为空值。,实现参照完整性,四、数据表和表约束的创建,实例：在“学籍管理”库中有3个表。结构如下： 创建“学籍管理”库，其所有参数都取默认值。 在“学籍管理”数据库中创建student，T1，T2表。,四、数据表和表约束的创建,在列定义之后创建约束 create database 学籍管理 go use 学籍管理 create table student (

20、 学号 int primary key, 姓名 char(10) not null , 出生日期 datatime null ),四、数据表和表约束的创建,在所有列定义后创建约束 create table t1 (学号 char（10） , 语文 tinyint null, 数学 tinyint null constraint pk_t1 primary key (学号) ),四、数据表和表约束的创建,先建表，后添加约束 create table t2 (学号 int not null , 选修课程 char(20) not null , 成绩 tinyint null ) alter tab

21、le t2 add constraint pk_t2 primary key (学号,选修课程) alter table t2 add constraint ck_t2 check(成绩=0 and 成绩=100),四、数据表和表约束的创建,练习：在market数据库中有2个表。结构如下： 创建market数据库，其所有参数都取默认值。 在market数据库中创建goods和orders表。,四、数据表和表约束的创建,create database market go use market create table goods ( 货品名称 char(20) primary key, 单价 m

22、oney not null , 库存量 int not null ),在列定义之后创建约束,四、数据表和表约束的创建,create table orders (订单号 int not null identity (1,1) , 货品名称 char(20) null, 数量 int not null constraint pk_orders primary key (订单号)， constraint ck_orders check(数量0)， constraint fk_orders foreign key (货品名称) references goods(货品名称) ),五、数据表的管理,修改表

23、结构：alter table 表名 增加列: add 列定义 修改列: alter column 列定义 删除列: drop column 列名 重命名数据表： exec sp_rename 表名，新表名 删除数据表： drop table 表名,五、数据表的管理,实例：在T1表中增加1列： 英语，int ，取值0100。 alter table t1 add 英语 int constraint ck_eng check (英语=0 and 英语=100) 实例：将“英语”列的类型修改为“ tinyint”。 提示：由于“英语”列上有约束，所以应先删除该约束，再修改该列 alter table

24、 t1 drop constraint ck_eng alter table t1 alter column 英语 tinyint 实例：删除“英语”列。 alter table t1 drop column 英语,六、数据表的操作,插入数据 insert into 表名 (字段名列表) values (字段值列表) 修改数据 update 表名 set 列名 = 表达式 | null | default , n ) where 逻辑表达式 删除数据 delete 表名 where 逻辑表达式 ,练习:是非题,创建数据库时，filegrowth参数是用来设置数据库的最大容量的。 创建数据库时，

25、size参数是用来设置数据库的最大容量。 insert语句后面必须要INTO。,练习:填空题,_系统数据库主要用来进行复制、作业调度和管理报警等活动。 当一个表带有约束后，执行对表的各种_操作时，将自动_相应的约束，只有符合约束条件的合法操作才能被真正执行。 在一个已存在数据的表中增加不带默认值的列，一定要保证所增加的列允许_值。 在定义基本表时，将年龄属性限制在2040岁之间的这种数据值的约束属于DBMS的_ 功能。,练习:填空题,修改数据库定义的命令关键字为_，删除整个数据库的命令关键字为_。 约束包括_、_、_、_、_。 哪种完整性用以指定列的数据输入是否具有正确的数据类型、格式以及有效

26、的数据范围_。 对表中记录的维护工作主要有增加、_和_操作，它们均可通过企业管理器或T-SQL语句完成 通过对列插入值的限制，哪一种约束实施了域完整性？_。 指定返回的列来控制，选择行就是水平地划分数据，限制返回结果中的行，这由SELECT语句中的_子句指定选择条件来控制。,练习:选择题,语句“ use master go select * from sysfiles go ”包括（）个批处理。A.1B.2C.3D.4 当数据库损坏时，数据库管理员可通过何种方式恢复数据库（）。 A.事务日志文件B.主数据文件 C.DELETE语句D.联机帮助文件 下面关于tempdb数据库描述不正确的是（）。

27、 A.是一个临时数据库B.属于全局资源 C.没有权限限制D.是用户建立新数据库的模板 使用“CREATE DATABASE AAA”命令所建立数据库文件的初始大小是（）字节。 A.1MB.2MC.3MD.4M,练习:选择题,SQLServer 2000的物理存储主要包括3类文件是: A.主数据文件、次数据文件、事务日志文件 B.主数据文件、次数据文件、文本文件 C.表文件、索引文件、存储过程 D.表文件、索引文件、图表文件 数据库中的数据在哪种情况下，可以被删除? A .当有用户使用此数据库时 B.当数据库正在恢复时 C.当数据库正在参与复制时 D.当数据库被设置为只读时 表的主键约束是用来强

28、制哪种数据完整性的? A.实体完整性 B.参照完整性 C.域完整性 D.都是,练习:选择题,在alter table语句中使用了哪个子句可以使表上创建的检查约束暂时无效。 A.check constraintB.nocheck constraint C.with nocheckD.drop constraint 不允许在关系中出现重复记录的约束是通过: A.外部关系键实现 B.索引实现 C.主关系键实现 D.惟一索引实现 若公司招聘中不允许17周岁以下的人员招聘，应使用什么完整性。 A.实体完整性B.域完整性 C.参照完整性D.用户定义完整性,总复习3 查询语句,第3部分 数据库的查询学习要求

29、,掌握关系的运算方法 掌握数据库的简单查询方法 掌握数据库的汇总查询方法 掌握数据库的连接查询方法,一、查询的类型,关系的运算：投影、选择、连接 简单查询 对一张表的原始数据进行查询 汇总查询 对表中数据进行求总和、求平均、求最大值、求最小值的查询 连接查询 从多个相关的表中查询数据,二、简单查询,语句格式 select 字段列表 from 表名 where 条件,二、简单查询,例：分析下列查询语句执行的功能和结果。 select * from xsqk select 学号 , 课程号 , 成绩, 成绩+ 5 from xs_kc select 学号, 课程号, 成绩 as 原成绩, 调整成绩

30、=成绩+ 5 from xs_kc select 学号, 姓名, 其职务是, 备注 from xsqk select * from xsqk where 专业名=计算机网络 and 性别=1 select 学号，姓名，出生日期 from xsqk where 出生日期 between 1980-07-01 and 1980-07-31 select * from xsqk where 姓名 like 陈% ,三、数据汇总查询,使用的方法有三种： 使用聚合函数(sum、avg、count、max、min ) 使用group by子句 Select语句的格式 select 列名表 from 表名，

31、n where 条件 group by 列名 having 逻辑表达式 order by 列名 ,投影运算,选择运算,连接运算,排序,三、数据汇总查询,聚合函数的语句格式： select 聚合函数( * | all | distinct 列名) from 表名 where 条件 说明： 当select子句中有聚合函数且没有group by子句时，就不能再指定其它的列名。 All ：计算该列值非空的记录的个数，默认为all。 Distinct：计算该列值非空且不同的记录的个数(不计算重复行)。 * ：计算所有记录的个数。,三、数据汇总查询,例：使用聚合函数进行汇总查询。 select avg (

32、成绩) as 平均成绩, max(成绩) as 最高分, min (成绩) as 最低分 from xs_kc select count (学号) as 女生人数 from xsqk where 性别=0 select count (课程号) as 课程总数 from xs_kc select count (distinct 课程号) as 课程门数 from xs_kc 注意：一个聚合函数返回一个单值。,三、数据汇总查询,分类汇总的语法格式： select 列名,聚合函数 from 表名 where 条件 group by 列名 having 逻辑表达式 注意： select 中的列名必须是

33、group by子句中出现了的列名。,用于对生成的组进行条件筛选,三、数据汇总查询,where子句与having子句的区别： where子句作用于表(在分组之前对表中的记录先筛选)。 having子句作用于组(在分组之后对生成的组进行筛选)。 having子句中可以有聚合函数 where子句中不能有聚合函数。 当二者同时出现时 先where 后group by 再having,三、数据汇总查询,例：分析下列查询语句执行的功能。 select 专业名, count (专业名) from xsqk group by 专业名 select 学号, avg(成绩) from xs_kc group by 学号 having avg(成绩)75,统计各专业的人数,统计平均成绩超过75分的学生学号和平均成绩,三、数据汇总查询,练习： 按学号分组汇总总分高于100分的学生记录,并按总分的降序排列。 select 学号, sum(成绩) as 各学生的总分 from xs_kc group by 学号 having sum(成绩)100 order by sum(成绩) desc 查询课程号为103的成绩记录,并按成绩的降序排列。 select 课程号,成绩 from xs_kc w