《数据库总复习》PPT课件.ppt

总复习1关系数据库原理,第1部分 关系数据库原理学习要求,掌握数据库的基本概念掌握ER概念模型掌握关系数据模型掌握ER图转化为关系模式/表掌握关系规范化掌握关系的三种范式,一、数据库的基本概念,数据(Data)是数据库中存储的基本对象，是描述事物的符号。如：数字、文字、图形、图像、声音等。数据库(Database，DB)是储存在计算机内、有组织的、可共享的数据集合。数据库管理系统(DBMS)是位于用户与操作系统之间的管理数据库的软件。如：SQL Server 2000数据库系统(DBS)是指在计算机系统中引入数据库后的系统，包括：计算机系统、数据库、数据库管理系统、人员。,二、ER概念模型,数据模型的含义对现实世界（存在于人脑之外的客观世界）的模拟。数据模型的类型概念数据模型、逻辑数据模型、物理数据模型概念模型的含义用于信息世界（现实世界在人脑中的反映）的建模，是现实世界到信息世界的第一次抽象。,二、ER概念模型,概念模型的表示方法实体联系图(ER图)ER图的基本要素实体：现实世界中任何可以被认识、区分的事物。属性：实体所具有的特性。实体间的联系一对一联系、一对多联系、多对多联系课堂练习：判断实体间的联系类型。学校中有若干个系和若干个教师，每个教师只能属于一个系，一个系可以有多名教师，系与教师的关系类型是_。教师与学生的关系类型是_。,二、ER概念模型,ER图的绘制方法长方形实体椭 圆属性菱 形联系ER图的设计原则：确定实体类型及属性。确定联系类型及属性。把实体类型和联系类型组合成ER图。,二、ER概念模型,课堂练习：某工厂生产多种产品，每种产品由不同的零件组装而成，有的零件可用在不同的产品上。产品有产品号和产品名两个属性，零件有零件号和零件名两个属性。根据语义设计ER模型。分析：ER模型中有两个实体，产品（产品号，产品名），零件（零件号，零件名），产品与零件是M;N的联系。,零件,产品,组装,三、关系数据模型,逻辑数据模型含义：是用户从数据库所看到的数据模型。用概念数据模型表示的数据必须转化为逻辑数据模型表示的数据，才能在DBMS中实现。类型（四种）关系模型（最常用）例：SQL Server 2000层次模型网状模型面向对象模型,三、关系数据模型,关系数据模型的含义用二维表格结构表示实体及实体之间的联系的数据模型完成信息世界到机器世界的第二次抽象。具有如下性质的二维表格，又称为一个关系。表中每一个属性值都是不可分解的。表中不允许出现相同的行/列。表中没有行序、没有列序。说明：1个关系就是1张二维表，反过来则不一定。,四、ER图转化为关系模式/表,关系模式的表示方法用关系名和包含的属性名的集合表示。例如：学生（学号，姓名，性别，年龄，系编号）关系模式与关系(表)的区别转化原则每个实体转换为一个关系。每个联系也转换成一个关系。对于1:1的联系，每个实体的码均是该联系关系的候选码对于1:N的联系，关系的码是N端实体的码。对于M:N的联系，关系的码是诸实体码的组合。有相同码的关系可以合并。,四、ER图转化为关系模式/表,课堂练习:将下面的ER图转化成关系模式,并指出主码与外码。,零件(零件号，零件名)产品(产品号，产品名)组装(零件号，产品号，零件个数）主码：零件号+产品号 外码：产品号、零件号,四、ER图转化为关系模式/表,问题：在参照完整性中，为什么外键属性的值可以为空？什么情况下才为空？答:(1)外码属性的值可以为空,它表示该属性的值尚未确定。(2)为空的前提条件是该外码属性不是其所在关系的主属性。例如：学生(学号,姓名,性别,专业号)专业(专业号,专业名)其中：专业号是学生表中的一个外码，不是学生表的主属性，可以为空，其语义是该学生的专业尚未确定。但若该外码属性是其所在关系的主属性，则不能为空。例如：选修(学号,课程号,成绩)课程(课程号,课程名,学分)在选修表中，课程号虽然也是一个外部码，但它又是选修表的主属性，所以不能为空，因为关系模型必须满足实体完整性。,五、关系的规范化,函数依赖是指关系中各属性或属性组之间的相互依赖关系。函数依赖的类型完全函数依赖部分函数依赖传递函数依赖例如：SCD(学号,姓名,课程号,成绩,所在学院,学院电话)“学号+课程号”“成绩”，是完全函数依赖。“姓名、所在学院、学院电话”与主码是部分函数依赖。“学号”“所在学院”“学院电话”，则“学院电话”传递依赖于“学号”。,五、关系的规范化,关系规范化的含义为了区分关系模式的优劣，把关系模式分为各种不同等级的范式。将低级范式转换为若干个高级范式过程。范式的类型1NF：关系模式R的所有属性的值都不可再分解值。2NF：关系模式R为1NF，且R中每个非主属性完全函数依赖于R的主码(复合码)。3NF：关系模式R为2NF，且R中每个非主属性都不传递函数依赖于R的某个候选码。,五、关系的规范化,将关系模式进行分解:,SC（学号，课程号，成绩）SD1（学号，姓名，所在学院）SD2（学院名，学院电话）,五、关系的规范化,小结：1NF中的每个属性值是不可再分的，它是关系模式的最低要求。消除1NF中的部分函数依赖，可转换为2NF。消除2NF中的传递函数依赖，可转换为3NF。,练习是非题,数据库系统不包含数据库管理系统。关系运算中的选择是按照给定的条件选择记录组成一个新的表。关系模式满足第二范式，则它一定满足第一范式参照完整性与引用完整性是两个不同的概念。实体完整性是指表中的每一行都必须能够唯一标识，且不存在重复的数据行。在WindowsXP操作系统上能够安装SQL Server 2000个人版的服务器。,总复习2数据库和表的管理,引：,数据库的设计步骤：收集、分析数据建立概念模型（ER图）建立逻辑模型（关系模式）关系规范化1NF 2NF 3NF创建数据库,第一次抽象,第二次抽象,第2部分 数据库和表的管理学习要求,数据库概述掌握数据库的创建掌握数据库的管理掌握数据表和表约束的创建掌握数据表的管理掌握数据表的操作,一、数据库概述,SQL Server数据库的含义是存放各种对象(表、视图、规则、默认值、存储过程、用户、角色等)的逻辑实体。数据库的结构数据库文件有三种类型：主数据文件(有且只有只有1个，扩展名是.mdf)次数据文件(0个或多个，扩展名是.ndf)事务日志文件(1个或多个，扩展名是.ldf)文件组的类型主文件组:包括主数据文件，没有指明组的其它文件次文件组:用户自定义文件组,一、数据库概述,系统数据库（6个）master数据库：记录了SQL Server的所有系统信息。model数据库：是系统所有数据库的模板。所有在系统中创建的新数据库的内容，在刚创建时都和model数据库完全一样。msdb数据库：是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。tempdb数据库：是一个临时数据库，用于存放所有连接到系统的用户的临时表和临时存储过程。比如：查询时会用到它。pubs和northwind数据库,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名,创建1个大小与model的数据库完全相同的用户数据库。其初始大小为1MB,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名 ON PRIMARY(NAME=逻辑文件名，FILENAME=磁盘文件名，SIZE=初始大小，MAXSIZE=最大容量，FILEGROWTH=增长量）,n,主文件组,主文件组的次数据文件n,创建自定义大小的用户数据库,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名 ON PRIMARY(NAME=逻辑文件名，FILENAME=磁盘文件名，SIZE=初始大小，MAXSIZE=最大容量，FILEGROWTH=增长量）,n，FILEGROUP 组文件名(),n,主文件组,次文件组,次文件组的次数据文件,主文件组的次数据文件n,主数据文件,主数据文件,创建自定义大小的用户数据库,二、用户数据库的创建命令格式,CREATE DATABSE 数据库名 ON PRIMARY(NAME=逻辑文件名，FILENAME=磁盘文件名，SIZE=初始大小，MAXSIZE=最大容量|unlimited，FILEGROWTH=增长量）,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,size=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,filegrowth=20%),三、数据库的管理,显示数据库exec sp_helpdb 数据库名修改数据库：alter database 修改数据库名增加文件组重命名文件组删除文件组增加数据库中的次要数据文件和事务日志文件修改文件属性文件的初始大小、最大容量、增长幅度删除数据库中的次要数据文件和事务日志文件,三、数据库的管理,alter database d1 add filegroup g2alter database d1 modify filegroup g2 name=g3alter database d1 remove filegroup g3,在数据库d1中增加一个文件组g2,将数据库d1中的文件组g2更名为g3,删除数据库d1中的文件组g3,注意：删除文件组前必须保证该文件组为空，若其中有文件则应先删除。,三、数据库的管理,alter database d1add 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 g1alter database d1add log 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 data4alter database d1 modify name=aadrop database aa说明：当数据库处于下列情况不能被删除。当有用户正在数据库使用时。当数据库正在被恢复时。当数据库正在参与复制时。,修改数据文件的属性,删除数据文件,将数据库d1的名字修改为aa,删除数据库,四、数据表和表约束的创建,建表时要涉及到的问题表的列数、列名每列的数据类型列上的约束：防止出现非法数据，保证数据完整性。建表的命令格式create table 表名(字段名 类型(长度)列级完整性约束条件,n)创建表约束的方法建表时：在每列的定义后，或在所有列定义后创建约束表已存在：只能通过修改表，添加约束,四、数据表和表约束的创建,约束的类型主键（PRIMARY KEY）约束惟一（UNIQUE）约束外键（FOREIGN KEY）约束检查（CHECK）约束默认值（DEFAULT）约束空值（NULL）约束主键约束与惟一性约束的区别：表中只能定义1个主键约束，但可定义多个惟一约束。定义了惟一约束的列可以为空值，而定义了主键约束的列不能为空值。,实现参照完整性,四、数据表和表约束的创建,实例：在“学籍管理”库中有3个表。结构如下：创建“学籍管理”库，其所有参数都取默认值。在“学籍管理”数据库中创建student，T1，T2表。,四、数据表和表约束的创建,在列定义之后创建约束create database 学籍管理gouse 学籍管理create table student(学号 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 table 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 marketgouse marketcreate table goods(货品名称 char(20)primary key,单价 money 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(货品名称),五、数据表的管理,修改表结构：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 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参数是用来设置数据库的最大容量的。创建数据库时，size参数是用来设置数据库的最大容量。insert语句后面必须要INTO。,练习:填空题,_系统数据库主要用来进行复制、作业调度和管理报警等活动。当一个表带有约束后，执行对表的各种_操作时，将自动_相应的约束，只有符合约束条件的合法操作才能被真正执行。在一个已存在数据的表中增加不带默认值的列，一定要保证所增加的列允许_值。在定义基本表时，将年龄属性限制在2040岁之间的这种数据值的约束属于DBMS的_ 功能。,练习:填空题,修改数据库定义的命令关键字为_，删除整个数据库的命令关键字为_。约束包括_、_、_、_、_。哪种完整性用以指定列的数据输入是否具有正确的数据类型、格式以及有效的数据范围_。对表中记录的维护工作主要有增加、_和_操作，它们均可通过企业管理器或T-SQL语句完成通过对列插入值的限制，哪一种约束实施了域完整性？_。指定返回的列来控制，选择行就是水平地划分数据，限制返回结果中的行，这由SELECT语句中的_子句指定选择条件来控制。,练习:选择题,语句“use master go select*from sysfiles go”包括（）个批处理。A.1B.2C.3D.4当数据库损坏时，数据库管理员可通过何种方式恢复数据库（）。A.事务日志文件B.主数据文件C.DELETE语句D.联机帮助文件下面关于tempdb数据库描述不正确的是（）。A.是一个临时数据库B.属于全局资源C.没有权限限制D.是用户建立新数据库的模板使用“CREATE DATABASE AAA”命令所建立数据库文件的初始大小是（）字节。A.1MB.2MC.3MD.4M,练习:选择题,SQLServer 2000的物理存储主要包括3类文件是:A.主数据文件、次数据文件、事务日志文件B.主数据文件、次数据文件、文本文件C.表文件、索引文件、存储过程D.表文件、索引文件、图表文件数据库中的数据在哪种情况下，可以被删除?A.当有用户使用此数据库时 B.当数据库正在恢复时C.当数据库正在参与复制时 D.当数据库被设置为只读时表的主键约束是用来强制哪种数据完整性的?A.实体完整性 B.参照完整性 C.域完整性 D.都是,练习:选择题,在alter table语句中使用了哪个子句可以使表上创建的检查约束暂时无效。A.check constraintB.nocheck constraintC.with nocheckD.drop constraint不允许在关系中出现重复记录的约束是通过:A.外部关系键实现 B.索引实现C.主关系键实现 D.惟一索引实现若公司招聘中不允许17周岁以下的人员招聘，应使用什么完整性。A.实体完整性B.域完整性C.参照完整性D.用户定义完整性,总复习3 查询语句,第3部分 数据库的查询学习要求,掌握关系的运算方法掌握数据库的简单查询方法掌握数据库的汇总查询方法掌握数据库的连接查询方法,一、查询的类型,关系的运算：投影、选择、连接简单查询对一张表的原始数据进行查询汇总查询对表中数据进行求总和、求平均、求最大值、求最小值的查询连接查询从多个相关的表中查询数据,二、简单查询,语句格式select 字段列表from 表名where 条件,二、简单查询,例：分析下列查询语句执行的功能和结果。select*from xsqk select 学号,课程号,成绩,成绩+5 from xs_kc select 学号,课程号,成绩 as 原成绩,调整成绩=成绩+5from xs_kcselect 学号,姓名,其职务是,备注 from xsqkselect*from xsqk where 专业名=计算机网络 and 性别=1select 学号，姓名，出生日期 from xsqk where 出生日期 between 1980-07-01 and 1980-07-31select*from xsqk where 姓名 like 陈%,三、数据汇总查询,使用的方法有三种：使用聚合函数(sum、avg、count、max、min)使用group by子句 Select语句的格式select 列名表 from 表名，n where 条件 group by 列名 having 逻辑表达式 order by 列名,投影运算,选择运算,连接运算,排序,三、数据汇总查询,聚合函数的语句格式：select 聚合函数(*|all|distinct 列名)from 表名 where 条件 说明：当select子句中有聚合函数且没有group by子句时，就不能再指定其它的列名。All：计算该列值非空的记录的个数，默认为all。Distinct：计算该列值非空且不同的记录的个数(不计算重复行)。*：计算所有记录的个数。,三、数据汇总查询,例：使用聚合函数进行汇总查询。select avg(成绩)as 平均成绩,max(成绩)as 最高分,min(成绩)as 最低分 from xs_kcselect count(学号)as 女生人数 from xsqk where 性别=0select count(课程号)as 课程总数 from xs_kcselect count(distinct 课程号)as 课程门数 from xs_kc 注意：一个聚合函数返回一个单值。,三、数据汇总查询,分类汇总的语法格式：select 列名,聚合函数 from 表名 where 条件 group by 列名 having 逻辑表达式 注意：select 中的列名必须是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(成绩)100order by sum(成绩)desc查询课程号为103的成绩记录,并按成绩的降序排列。select 课程号,成绩 from xs_kc where 课程号=103 order by 成绩 desc,三、数据汇总查询,查询每门课程的最高分的学生记录。select 课程号,max(成绩)as 每门课最高分 from xs_kc group by 课程号查询每个学生的最高分课程记录。select 学号,max(成绩)as 每个学生的最高分 from xs_kc group by 学号,四、连接查询(多表查询）,连接运算：把多个表中的行按给定的条件进行拼接从而形成新表。连接运算的类型：内连接：只连接满足条件的行(重点)外连接：也连接不满足条件的行左外连接、右外连接、全外连接自连接：同一张表的内或外连接无限制连接,四、连接查询(多表查询）,内连接(自然连接),语法格式1:select 列名表from 表1,表2where 表1.列名1=表2.列名2格式2:select 列名表from 表1 inner join 表2 on 表1.列名1=表2.列名2,连接条件：当两个表中的列名相同时，需加上表名修饰；否则，可省去表名。,四、连接查询(多表查询）,左外连接：,语法格式1：select 列名表from 表1,表2 where 表1.列名1*=表2.列名2格式2:select 列名表from 表1 left outer join 表2 on 表1.列名1=表2.列名2,四、连接查询(多表查询）,右外连接：,语法格式1：select 列名表from 表1,表2 where 表1.列名1=*表2.列名2格式2：select 列名表from 表1 right outer join 表2 on 表1.列名1=表2.列名2,四、连接查询(多表查询）,全外连接,语法格式：select 列名表from 表1 full outer join 表2 on 表1.列名1=表2.列名2,四、连接查询(多表查询）,例：查询不及格学生的学号,姓名,课程号,成绩信息。分析：确定要查询内容的来源。确定连接条件(要查询的表通过什么列名相关联)。select xsqk.学号,姓名,课程号,成绩 from xsqk join xs_kc on xsqk.学号=xs_kc.学号 where 成绩60,四、连接查询(多表查询）,练习：查询学生的学号、姓名、专业名、课程名、成绩信息。select xsqk.学号,姓名,专业名,课程名,成绩 from xsqk join xs_kc on xsqk.学号=xs_kc.学号 jion kc on xs_kc.课程号=kc.课程号,