学渣版数据库复习资料.doc

数据库（DataBase,DB），存放数据的仓库，具有永久储存、有组织和可共享三个基本特点。数据库管理系统（DBMS），主要功能包括：（1）数据定义功能。（2）数据组织、存储和管理功能。（3）数据操纵功能。（4）数据库的事务管理和运行管理。（5）数据库的建立和维护功能。（6）其他包括DBMS与网络中其他软件系统的通信等功能。数据库系统（DBS）具有管理和控制数据库功能的计算机系统。数据库系统（DBS）数据库（DataBase,DB）=数据库管理系统（DBMS）数据库系统的三级模式结构是指数据库系统由外模式、模式、内模式构成。模式：也称逻辑模式一个数据库只有一个模式。外模式：也称子模式或用户模式，是与某一应用有关的数据的逻辑。内模式：也称存储模式，是数据在数据库内部的表示方式。数据库的两级映像功能（1） 外模式/模式映像：定义了外模式与模式之间的对应关系，模式改变时，外模式可以不变。同一个系统中，对每个外模式来说，只有一个外模式/模式映像。（2） 模式/内模式映像：定义了数据的全局逻辑结构与存储结构之间的对应关系，数据库中只有一个模式/内模式映像。实体间的联系（1） 一对一联系（1：1）（2） 一对多联系（1：n）（3） 多对多联系（n：m）数据模型的三要素1.数据结构（层次模型、网状模型和关系模型）2.数据操作3.数据的完整性约束条件关系模式术语1. 关系：一个关系数据通常对应通常说的一张二维表。2. 元组：表中的一行称为一个元组。3. 属性：表中的一列称为一个属性。4. 域：域就是属性的取值范围。5. 分量：分量是元组中的一个属性值。6. 主码：主码是在关系中能唯一标识元组的属性集。7. 关系模式：关系的描述称为关系模式。（一般指表头）关系的性质（1） 任意两个元组（两行）不能完全相同。（2） 关系中元组（行）的次序可以任意调换。（3） 关系中属性（列）的次序可以任意调换。（4） 同一列中的分量必须来自同一个域，即同一种数据类型。（5） 关系中属性名必须互不相同，但可以来自同一个域。（6） 每一个分量必须取原子值，即每一分量都必须是不可再分的数据项。关系完整性概念：是为了保证数据库中数据的正确性和相容性，通常包括实体完整性、参照完整性和用户定义完整性。关系完整性约束（主要是用来满足现实世界客观要求）（1） 实体完整性：在组成主键的属性上，不允许出现空值。（2） 参照完整性：不允许引用不存在的元组。（3） 用户定义的完整性。创建数据库：create database 数据库名称重命名数据库：sp_renamedb 原名 , 新名 删除数据库：drop database 数据库名称日期和时间数据类型包括datetime和smalldatetime两种类型。货币数据的数据类型有两种：money和Smallmoney.创建约束(1) 主键约束：primary key(2) 非空约束：not null(3) 检查约束：check (列名 in （男，女）)(4) 默认值约束：default（计算机）(5) 唯一性约束：unique not null(6) 范围约束：check（sage=18 and =100）(7) 外部键约束：foreign key (sno) references 与之连接的表名 (sno)重命名表：sp_rename oidname.newname删除列：drop column 列名索引的作用：提高查询速度数据查询:select sno from Student where sno=2005Select:用于指定所选择的要查询的特定表中都列，它可以是星号（*）、表达式、列表、变量等。Into：用于指定所要生成新表的名称。From：用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。Where：用来限定查询的范围和条件。Group by：子句是分组查询子句。Having：子句用于指定子句的条件。Group by子句、having子句和集合函数一起可以实现对每组成一行和一个汇总值。Order by子句可以根据一个列或者多个列来排序查询结果，在该语句中既可以使用列名，也可以使用相对列号。Asc表示升序排序，desc表示降序排序。Select * from sc order by cno asc,score descCompute子句使用集合函数在查询的结果集中生成汇总。Compute by 子句用于增加各行列汇总行。Select sno,score from sc order by cno compute sum(score) by cno(统计成绩表中成绩的汇总值)使用distinct关键字消除重复行：select distinct (sno) from scTop关键字：指定返回结果前N行：select top N *from Studentnull值表示列的数据值未知或不可用。Avg:求平均值Count:计数函数，用于计算组中成员个数，返回值为int类型。Count_big:计数函数。返回值类型为bigint。Max:求最大值。Min：求最小值。Sum:求和。Stdev:计算统计标准差。Var:统计方差。表格链接Select sname,sc.sno,cno,scoreFrom student inner join sc on student.sno=sc.sno获取系统时间Select getdate()Select year(getdate)获取年份视图：是一种数据库对象，是从一个或多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果。视图和基本表的区别：基本表是实实在在可以保存数据的实体，可以写入数据，建立各种触发器，主键，约束等，而视图只是一条可以从基本表中获得数据的语句，不能在视图上建立对象，视图是从一个或多个基本表导出的虚表。例6 带输入参数和一个输出参数的存储过程。统计指定课程的平均成绩，并将统计的结果作为输出参数。Create proc proc2cn char(20)avg_geade int outputAsSelect avg_grade=avg(score) from scJoin course c on o=oWhere cname=cn执行此存储过程：Declaree avg_grade intExec proc2 高等数学,avg_grade outputPrint avg_gradeP113习题全局变量：由系统定义和维护，用户只能查看，其名成开始局部变量：有用户定义和使用，其名称以开始存储过程：是SQL语句和可选控制流语句的欲编辑合。触发器：是一种特殊存的存储过程。其特殊性在于他不需要由用户调用执行，而是当用户对表的数据进行某些操作时自动触发执行。存储过程和触发器的区别：存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是特定时间出现的时候，自动执行或激活的，与链接用数据库中的用户或者应用程序无关。Deleted表：用于存储delete和update语句所影响的行的复本，deleted表和触发器表通常没有相同行。Inserted表：用于存储insert和update语句所影响的行的副本，inserted表中的行是触发器本中新行的副本。执行带多个参数的存储过程时，参数的传递方式有两种：1. 按参数位置传递；2. 按参数名传递。数据库设计的过程：需求分析；概念设计；逻辑设计；物理设计；数据库设计6个基本阶段1. 需求分析阶段；2. 概念结构设计阶段；3. 逻辑结构设计阶段；4. 数据库物理设计阶段；5. 数据库实施阶段；6. 数据库运行和维护阶段。关系数据库模式设计问题1. 数据冗余；2. 插入异常；3. 更新异常；4. 删除异常。函数依赖的概念：P161范式概念：符合某一级别的关系模式1、求姓名是以“李”打头的学生（匹配查询）。select * from Student Where sname like 李%(1).求姓名中含有“良”的学生(匹配查询)。select * from Student Where sname like %良%(2).求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生(匹配查询)select * from Student Where sname like %马_2、求选修了课程01001的最高、最低与平均成绩（组函数）。select max(score) 最高成绩,min(score) 最低成绩,avg(score) 平均成绩 from sc Where cno=01001(1).求各门课程的平均成绩与总成绩(分组查询)。select cno,avg(score) 平均成绩,sum(score) 总成绩 from scgroup by cno3.求学生人数不足4人的班级及其相应的学生数（分组查询）。select clno,count(clno) 学生人数 from Studentgroup by clno having count(clno)44、求与张维明年龄相同的学生的姓名和出生年月（子查询）。select sname,sbir from student Where Sage=(select sage from student where sname=张维明)and sname张维明5、 求选修了课程名为计算机网络的学生的学号和姓名（子查询）Select sno,sname from student where sno in(select sno from sc where cno=(select cno from course where cname=计算机网络)5查询每个学生选修的课程的平均分，并按学号升序排序。select sno 学号,avg(score) 平均分 from SCgroup by snoorder by sno8查询学生人数不足4人的班级及其相应的学生数。select clno,count(clno) from studentgroup by clnohaving count(clno)=3 order by sum(Score)4、查询有学生选修的每门课程的课程号、课程名、平均分数、学分及任课教师姓名，且平均分数大于等于70分。select o,cname,avg(score),credits,tnamefrom sc join course c on o=ojoin teaching t1 on o=ojoin teacher t2 on t1.tno=t2.tnogroup by o,cname,credits,tnamehaving avg(score)=70在SC表中定义一个更新和插入触发器，在此触发器中保证成绩在0100.-如果触发器已存在，将其删除If exists(select name from sysobjects where name=Tri_Ins_Upd and type=TR)Drop trigger Tri_Ins_UpdGo-创建触发器Create trigger Tri_Ins_Upd on sc for insert,updata asIf exists(select * from inserted where score100)Begin Print 成绩必须在0100之内 RollbackEndGo-使用insert命令插入数据：Insert into sc values(20080102,02002,105)-使用update命令修改数据：Updata sc Set score=-201、假设某商业集团数据库中有一关系模式R如下：R (商店编号，商品编号，数量，部门编号，负责人) 如果规定：(1) 每个商店的每种商品只在一个部门销售； (2) 每个商店的每个部门只有一个负责人； (3) 每个商店的每种商品只有一个库存数量。 试回答下列问题： (1) 根据上述规定，写出关系模式R的基本函数依赖；（商店编号，商品编号）部门编号（商店编号, 部门编号）负责人（商店编号，商品编号）数量(2) 找出关系模式R的候选码； （商店编号，商品编号）（商店编号, 部门编号）（商店编号，商品编号）(3) 试问关系模式R最高已经达到第几范式？为什么？因为R中存在着非主属性“负责人”对候选码（商店编号, 部门编号）的传递函数依赖，所以R属于2NF,不属于3NF(4) 如果R不属于3NF，请将R分解成3NF模式集。 R1(商店编号，商品编号, 数量，部门编号)R2（商店编号，部门编号，负责人）3、某数据库中有如下的表： 学生（学号，姓名，性别，出生日期，民族，住址，电话，班级）教师（系别，姓名，职称，课程编号，专业编号）课程（课程编号，课程名）成绩（课程编号，学号，成绩）专业（专业编号，专业名称）1)有如下的成绩查询，根据该查询回答问题： （1） 该查询来自哪几个表？Student ,Course ,SC.（2）在此“成绩列表”视图的基础上，写出SQL查询语句：查询80年以后出生的男同学Select * from 成绩列表 where year(出生日期)1980查询选修了计算机课程并成绩及格的学生的学号、姓名、课程名称、成绩。Select 学号,姓名,课程名称,成绩 from成绩列表 where (课程名称=计算机 and 成绩60) 学生成绩低于60的增加5分 Update成绩列表Set 成绩=成绩+5where成绩602）用SQL语句创建以下存储过程：创建一个为学生表添加学生记录的存储过程。create procedure stu学号 char(8),姓名 char(8),性