SQL数据库总复习.ppt

《SQL数据库总复习.ppt》由会员分享，可在线阅读，更多相关《SQL数据库总复习.ppt（122页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL数据库总复习数据库技术与应用数据库技术与应用SQL ServerSQL Server总总 复复 习习2021/3/22考试说明考试说明 考试时间：考试时间：150150分钟（分钟（2020周四）周四）考试地点：考试地点：待定待定 考试形式：考试形式：闭卷考试闭卷考试2021/3/23考试题型考试题型卷面卷面100100分，共分，共4 4道大题：道大题：单项选择题，单项选择题，2020题，每题题，每题1 1分，共分，共2020分分填空题，填空题，1010空，每空空，每空1.51.5分，共分，共1515分分操作题，操作题，2 2题，每题题，每题5 5分，共分，共1010分分(文科生用文科生用

2、)视图、数据约束视图、数据约束程序设计题，程序设计题，2 2题，每题题，每题5 5分，共分，共1010分分(理工科生用理工科生用)控制流程设计、存储过程设计控制流程设计、存储过程设计综合设计题，共综合设计题，共4545分分给给定定一一数数据据库库应应用用中中的的E-R图图，将将图图按按照照关关系系数数据据库库设设置置特特点点转转换换成成关关系系模模式，并做相应设计：式，并做相应设计：利用利用T-SQL语言根据要求设计出相应的语言根据要求设计出相应的SQL语句，包括：语句，包括：创建规则、创建表、创建各类查询等；创建规则、创建表、创建各类查询等；查查询询包包括括：基基本本查查询询(包包括括分分组

3、组统统计计)、嵌嵌套套查查询询(以以单单值值嵌嵌套套查查询询为为主主)、连连接接查查询询(以内连接为主以内连接为主)；常用的系统存储过程。；常用的系统存储过程。2021/3/24考试范围考试范围(理工科理工科)第第第第 1 1 章章章章数据库系统概论数据库系统概论数据库系统概论数据库系统概论第第第第 2 2 章章章章关系数据库基础原理关系数据库基础原理关系数据库基础原理关系数据库基础原理第第第第 3 3 章章章章SQL ServerSQL Server数据库基础数据库基础数据库基础数据库基础第第第第 4 4 章章章章 SQL ServerSQL Server数据库的管理数据库的管理数据库的管理

4、数据库的管理第第第第 5 5 章章章章 SQL ServerSQL Server表的管理表的管理表的管理表的管理第第第第 6 6 章章章章数据查询数据查询数据查询数据查询(外、交叉连接除外外、交叉连接除外外、交叉连接除外外、交叉连接除外)第第第第 7 7 章章章章索引与视图索引与视图索引与视图索引与视图(全文索引除外全文索引除外全文索引除外全文索引除外)第第第第 8 8 章章章章数据完整性数据完整性数据完整性数据完整性 第第第第 9 9章章章章程序设计程序设计程序设计程序设计(游标管理与应用除外游标管理与应用除外游标管理与应用除外游标管理与应用除外)第第第第 10 10 章章章章存储过程存储过

5、程存储过程存储过程 2021/3/25课程基本要求课程基本要求 通过本课程的学习，要求大家了解计算机数据库的应用技通过本课程的学习，要求大家了解计算机数据库的应用技术，知道术，知道 Microsoft SQL Server 2000Microsoft SQL Server 2000 的主要功能和使用方法。的主要功能和使用方法。Microsoft SQL Server 2000 Microsoft SQL Server 2000 是一种很有效的数据库管理工是一种很有效的数据库管理工是一种很有效的数据库管理工是一种很有效的数据库管理工具，它是在具，它是在具，它是在具，它是在 Windows Win

6、dows 环境下运行的一个强大的关系型数据库管环境下运行的一个强大的关系型数据库管环境下运行的一个强大的关系型数据库管环境下运行的一个强大的关系型数据库管理系统。它具有很强的交互性、数据独立性和共享性，具有最小理系统。它具有很强的交互性、数据独立性和共享性，具有最小理系统。它具有很强的交互性、数据独立性和共享性，具有最小理系统。它具有很强的交互性、数据独立性和共享性，具有最小的数据冗余。通过本课程的学习，使大家能准确理解什么是数据的数据冗余。通过本课程的学习，使大家能准确理解什么是数据的数据冗余。通过本课程的学习，使大家能准确理解什么是数据的数据冗余。通过本课程的学习，使大家能准确理解什么是数

7、据库以及数据库在各个领域的应用，掌握数据库及应用开发方法，库以及数据库在各个领域的应用，掌握数据库及应用开发方法，库以及数据库在各个领域的应用，掌握数据库及应用开发方法，库以及数据库在各个领域的应用，掌握数据库及应用开发方法，具备利用数据库工具开发数据库应用系统的基本技能，为今后应具备利用数据库工具开发数据库应用系统的基本技能，为今后应具备利用数据库工具开发数据库应用系统的基本技能，为今后应具备利用数据库工具开发数据库应用系统的基本技能，为今后应用数据库技术管理信息、利用信息打下基础。用数据库技术管理信息、利用信息打下基础。用数据库技术管理信息、利用信息打下基础。用数据库技术管理信息、利用信息

8、打下基础。2021/3/26学习目标学习目标(理工科理工科)掌握数据库系统的基本概念和理论掌握数据库系统的基本概念和理论 掌握关系、关系规范化基本原理和理论掌握关系、关系规范化基本原理和理论 掌握数据库设计方法与步骤掌握数据库设计方法与步骤 掌握关系代数运算规则及应用掌握关系代数运算规则及应用 掌握掌握SQL ServerSQL Server数据库基础数据库基础 掌握掌握SQL ServerSQL Server数据库管理数据库管理 掌握掌握SQL ServerSQL Server表的管理、数据查询表的管理、数据查询 掌握掌握SQL ServerSQL Server索引与视图、数据完整性索引与视

9、图、数据完整性 掌握掌握Transact-SQLTransact-SQL程序设计程序设计 掌握存储过程设计方法掌握存储过程设计方法2021/3/27第第 1 章章 数据库系统概论数据库系统概论 1.1 1.1 数据库管理技术的发展数据库管理技术的发展数据库管理技术的发展数据库管理技术的发展 1.2 1.2 数据库和数据库系统数据库和数据库系统数据库和数据库系统数据库和数据库系统 1.3 1.3 数据模型数据模型数据模型数据模型 1.4 1.4 数据库新技术数据库新技术数据库新技术数据库新技术2021/3/28本本 章章 要要 点点了解数据了解数据库技技术的的发展展了解数据了解数据库系系统的的组

10、成和特点成和特点掌握数据掌握数据库最常用的最常用的术语和基本概念和基本概念掌握数据掌握数据库系系统的三的三级模式模式结构构掌握三大掌握三大经典数据模型典数据模型2021/3/29第第 1 章章 数据库系统概论数据库系统概论思考题思考题2.2.常用的数据模型可分为几种？常用的数据模型可分为几种？1.什么是数据库？什么是数据库？数据库系统由什么组成？数据库系统由什么组成？3.数据库的模式结构有哪三级？站在不同视角看到的数据库的模式结构有哪三级？站在不同视角看到的 是什么模式？有何特征？是什么模式？有何特征？2021/3/210数据库特点数据库特点:数据真正实现了结构化数据真正实现了结构化数据的共享

11、性高，冗余度低，易扩充数据的共享性高，冗余度低，易扩充数据独立性高数据独立性高数据由数据由DBMS统一管理和控制统一管理和控制学生部门学生部门教务部门教务部门人事部门人事部门学校学校数据库数据库数据库管理系统数据库管理系统人事管理应用程序人事管理应用程序学生管理应用程序学生管理应用程序教务管理应用程序教务管理应用程序图图1-3 应用程序和数据库的关系应用程序和数据库的关系2021/3/211 数据库系统的组成数据库系统的组成用户用户用户应用系统应用开发工具数据库管理系统操作系统数据库数据库管理员DBMS2021/3/212数据库系统的结构由三级模式数据库系统的结构由三级模式(外模式外模式、模式

12、模式和和内模式内模式)组成组成 数据库的三级模式结构数据库的三级模式结构2021/3/213外模式外模式(对应于用户对应于用户现实世界现实世界)模式模式(对应于程序员对应于程序员信息世界信息世界)内模式内模式(对应于底层对应于底层计算机世界计算机世界)2021/3/214补贴系别姓名学号住址系别性别姓名学号学位学分系别姓名学号学位 出身年龄系别性别姓名学号概念模式与外模式的关系举例概念模式与外模式的关系举例概念模式与外模式的关系举例概念模式与外模式的关系举例2021/3/215学号姓名性别系别年龄住址出身学位学分补贴2021/3/216S(S(学号学号,姓名姓名,性别性别,年龄年龄,住址住址,

13、学分学分,学位学位)S(S(学号学号,姓名姓名,住址住址)学籍科学籍科S(S(学号学号,姓名姓名,学分学分)房产科房产科视图视图视图视图模式模式2021/3/217数数 据据 模模 型型常见的数据模型：常见的数据模型：层次数据模型、网状数据模型、关系数据模型层次数据模型、网状数据模型、关系数据模型树叶树 枝根通过通过树树形结构表示实体及联系。如描述学校管理机构。每形结构表示实体及联系。如描述学校管理机构。每个个结点结点表示一个表示一个实体（型）实体（型），箭头表示实体（型）间的联系箭头表示实体（型）间的联系（由父到子）。（由父到子）。（1）层次数据模型层次数据模型学校管理机构学校管理机构202

14、1/3/218根多个父多个父结点结点通过通过网状结构网状结构表示实体及联系。表示实体及联系。“网网”中每个结点表示一中每个结点表示一个实体个实体(型型)，结点之间箭头表示实体，结点之间箭头表示实体(型型)间的联系。间的联系。（2 2）网状数据模型网状数据模型2021/3/219关关系系模模型型与与层层次次模模型型和和网网状状模模型型相相比比有有着着本本质质的的差差别别，它是用它是用二维表格二维表格来表示实体及其相互之间的联系。来表示实体及其相互之间的联系。（3 3）关系数据模型）关系数据模型编号编号姓名姓名性别性别婚否婚否出生日期出生日期职称职称基本工资基本工资简历简历23101张张伶俐伶俐女

15、女已婚已婚09/24/56教授教授138023102罗罗稼宛稼宛男男已婚已婚11/27/73讲师讲师56023103黎达仁黎达仁男男未婚未婚12/23/81助教助教45023104顾顾高粲高粲男男已婚已婚01/27/63副教授副教授97023105黄丹秋黄丹秋女女未婚未婚07/15/79助教助教480表表1-1 教师关系教师关系2021/3/220第第 2 章章 关系关系数据库基本原理数据库基本原理 2.1 2.1 关系数据库概述关系数据库概述关系数据库概述关系数据库概述 2.2 2.2 关系代数的基本原理关系代数的基本原理关系代数的基本原理关系代数的基本原理 2.3 2.3 关系模式的分解关

16、系模式的分解关系模式的分解关系模式的分解 2.4 2.4 关系模型的完整性约束关系模型的完整性约束关系模型的完整性约束关系模型的完整性约束 2.5 2.5 数据库的设计方法数据库的设计方法数据库的设计方法数据库的设计方法2021/3/221本本 章章 要要 点点了解关系型数据库了解关系型数据库掌握关系代数的基本原理掌握关系代数的基本原理了解关系模式的分解了解关系模式的分解掌握关系模型的完整性约束掌握关系模型的完整性约束掌握数据库的设计步骤掌握数据库的设计步骤熟练掌握概念结构设计中的熟练掌握概念结构设计中的E-RE-R模型，模型，能将能将E-RE-R模型转换成关系模型。模型转换成关系模型。202

17、1/3/222第第 2 章章 关系关系数据库基本原理数据库基本原理思考题思考题2.2.并、交、差运算与笛卡尔积运算有何不同？并、交、差运算与笛卡尔积运算有何不同？1.数据库设计分为哪几个步骤数据库设计分为哪几个步骤？3.关系的三种特殊运算关系的三种特殊运算(投影、选择、连接投影、选择、连接)如何对应到如何对应到SQL查询？查询？4.NF、3NF要消除哪些函数依赖？要消除哪些函数依赖？5.关系模式具有哪些数据完整性，不同的数据约束对应关系模式具有哪些数据完整性，不同的数据约束对应的是何种完整性？的是何种完整性？2021/3/2231.并并设设A、B同为同为n元关系，则元关系，则A、B的的并并也是

18、一个也是一个n元关系，元关系，记作记作A B。例如例如例如例如规格规格大小大小abbacbAB规格规格大小大小bgba规格规格大小大小abbacbbgA B是是A的也是的也是B的但剔除重复的的但剔除重复的并运算的实际意义并运算的实际意义:实现实现插入插入或或新增新增新元组的操作新元组的操作2021/3/2242交交设设A、B同为同为n元关系，则元关系，则A、B的的交交也是一个也是一个n元关系，元关系，记作记作AB。AB包含了所有同属于包含了所有同属于A、B的元组。的元组。例如例如例如例如规格规格大小大小abbacbAB规格规格大小大小bgba规格规格大小大小baA B是是A的并且也是的并且也是

19、B的的交运算的实际意义交运算的实际意义:实现实现一层不变一层不变或或始终坚持始终坚持部分元组的操作部分元组的操作2021/3/2253差差设设A、B同为同为n元关系，则元关系，则A、B的的差差也是一个也是一个n元关系，元关系，记作记作A-B。A-B包含了所有属于包含了所有属于A但不属于但不属于B的元组。的元组。例如例如例如例如规格规格大小大小abbacbAB规格规格大小大小bgba规格规格大小大小abcbA B是是A的但剔除是的但剔除是B的的差运算的实际意义差运算的实际意义:实现实现删除删除或或取消取消部分元组的操作部分元组的操作2021/3/2264连接连接设设A是一个包含是一个包含m个元组

20、的个元组的k1元关系，元关系，B是一个包含是一个包含n个个元组的元组的k2元关系，则元关系，则A、B的的连接连接是一个包含是一个包含mn个元个元组的组的k1+k2元关系，记作元关系，记作AB。例如例如例如例如规格规格大小大小abbacbAB质地质地de规格规格大小大小质地质地abdbadcbdabebaecbeA BR和和S的不同组合的不同组合2021/3/2275投影投影设设 R=R(A1，A2，An)是一个是一个n元关系，元关系，i1，i2，im是是1，2，n的一个子集，并且的一个子集，并且 i1i218)实 例 4.更改表更改表employee2以添加未验证检查约束以添加未验证检查约束C

21、K_Age。2021/3/298ALTER TABLE employeeADD CONSTRAINT sex_default DEFAULT 男 FOR sex实 例5.在在Sales数据库中，为员工表数据库中，为员工表employee的的sex列列添加默认约束添加默认约束 sex_default，默认值是，默认值是“男男”2021/3/299第第9章章Transact-SQL程序设计程序设计 9.1 数据与表达式数据与表达式 9.2 函函 数数 9.3 程序控制流语句程序控制流语句2021/3/2100本章要点掌握常量与局部变量及声明掌握常量与局部变量及声明了解用户定义数据类型了解用户定义数

22、据类型了解不同数据类型的常量表示了解不同数据类型的常量表示了解常用函数的应用了解常用函数的应用掌握简单的程序设计方法掌握简单的程序设计方法2021/3/21011常量常量 在程序运行中保持不变的量。在程序运行中保持不变的量。字符串常量字符串常量Cincinnati、O、Brien、Process X is 50%complete.二进制常量二进制常量0 xAE、0 x12Ef、0 x69048AEFD010E、0 x(空串空串)日期日期/时间常量时间常量 Sep 3,2005 1:34:34.122 、9/3/2005 1PM、9.3.2005 13:00 、9-3-2005数值常量数值常量1

23、894、101.5E5、2.0、$12.5逻辑数据常量逻辑数据常量使用数字使用数字0或或1表示，非表示，非0的数字当作的数字当作1处理。处理。空值空值空值空值NULL可以表示整型、实型、字符型数据。可以表示整型、实型、字符型数据。2021/3/21022变量变量在程序运行中发生变化的量，用于临时存放数据。在程序运行中发生变化的量，用于临时存放数据。变量有变量有名字名字与与数据类型数据类型两个属性，变量名要符合标识符。两个属性，变量名要符合标识符。全局变量全局变量局部变量局部变量 2021/3/2103 使用使用DECLARE语句定义语句定义 DECLARE local_variable dat

24、a_type,.n 变量名最大长度为变量名最大长度为30个字符。一条个字符。一条DECLARE语句可以语句可以 定义多个变量，各变量之间使用逗号隔开。定义多个变量，各变量之间使用逗号隔开。例例 DECLARE name varchar(30),type int 可用查询语句可用查询语句 SELECT 为局部变量赋值为局部变量赋值 SELECT variable_name=expression,nSELECT variable_name=expression,n例如例如 DECLARE int_var int,BirthDate datetimeSELECT int_var=12,BirthDa

25、te=1992-2-20局部变量没有被赋值前，其值是局部变量没有被赋值前，其值是NULL。局部变量局部变量2021/3/2104利用利用UPDATE为局部变量赋值为局部变量赋值例例9-2 将将sell_order表中的表中的transporter_id列值为列值为“T001”、goods_id列值为列值为“G00003”的的order_num列的列的2倍值赋给局倍值赋给局部变量部变量order_num。DECLARE order_num floatUPDATE sell_orderSET order_num=order_num*2WHERE transporter_id=T001 AND go

26、ods_id=G00003select order_num as 临时单数量临时单数量2021/3/2105用用SET给局部变量赋值给局部变量赋值SET语句格式为：语句格式为：SET local_variable=expression注：注：一个一个SET语句只能为一个变量赋值。语句只能为一个变量赋值。例例9-3 计算计算employee表的记录数并赋值给局部变量表的记录数并赋值给局部变量rows。DECLARE rows intSET rows=(SELECT COUNT(*)FROM employee)SELECT rowsPRINT rows2021/3/2106常用的内置函数常用的内置

27、函数:系统函数系统函数日期函数日期函数字符串函数字符串函数数学函数数学函数聚合函数聚合函数游标函数游标函数2021/3/21079.3.1 语句句块和注和注释1.条件执行语句条件执行语句 IFELSE选择控制IF.ELSE的语法格式：的语法格式：IF Boolean_expression sql_statement|statement_block ELSE sql_statement|statement_block 功能说明：功能说明：ELSE并不一定是需要的并不一定是需要的布尔表达式为真执行布尔表达式为真执行IF后面的语句块，为假执行后面的语句块，为假执行 ELSE后面的语句块。后面的语句块

28、。IF.ELSE可以嵌套。可以嵌套。2021/3/21089.3.1 语句句块和注和注释2.多分支条件执行语句多分支条件执行语句 CASE函数函数CASE搜索函数格式：搜索函数格式：CASE WHEN Boolean_expression1 THEN result_expression1 .n ELSE else_result_expression END解读：解读：CASE WHEN 布尔表达式布尔表达式1 THEN 结果表达式结果表达式1 .n ELSE 否则结果表达式否则结果表达式 END功能：功能：若布尔表达式若布尔表达式1的值为真，的值为真，返回该结果表达返回该结果表达1的值，的值，

29、否则，返回否则结果表达式否则，返回否则结果表达式的值。的值。2021/3/21099.3.1 语句句块和注和注释循环控制循环控制WHILE循环语句的语法格式：循环语句的语法格式：WHILE boolean_expression sql_statement|statement_block BREAK sql_statement|statement_block CONTINUE 说明：说明：当布尔表达式为当布尔表达式为真真(TRUE)时，重复执行时，重复执行SQL语句块，直语句块，直到为到为假假(FALSE)为止。为止。执行执行SQL语句时若遇语句时若遇BREAK，强行退出循环；若遇，强行退出循环

30、；若遇CONTINUE，结束本轮循环，重回布尔表达式判断。，结束本轮循环，重回布尔表达式判断。循环可以嵌套循环可以嵌套2021/3/21109.3.1 语句句块和注和注释IF(SELECT avg(unit_price)FROM goods WHERE supplier_id=S001)9799.0 SELECT supplier_id为为S001的商品的平均单价比的商品的平均单价比9799大大ELSE SELECT supplier_id为为S001的商品的平均单价比的商品的平均单价比9799小小例9-23 判断表goods中supplier_id为“S001”的商品的平均单价是否大于979

31、9。运行结果如下：运行结果如下：supplier_id为S001的商品的平均单价比9799大2021/3/21119.3.2 选择控制选择控制例9-25 嵌套IF.ELSE语句的使用。IF(SELECT SUM(order_num)FROM sell_order)50 PRINT 他们是最佳的客户他们是最佳的客户ELSE IF(SELECT SUM(order_num)FROM sell_order)30 PRINT 必须与他们保持联络必须与他们保持联络 ELSE PRINT 再想想办法吧再想想办法吧!2021/3/21129.3.2 选择控制选择控制SELECT goods_name AS

32、商品名称商品名称,CASE WHEN stock_quantity-order_quantity=3 THEN 紧急进货紧急进货 WHEN stock_quantity-order_quantity=10 THEN 暂缓进货暂缓进货 ELSE 货物充足货物充足 END AS 进货判断进货判断FROM goods例9-27 根据goods表中库存货物数量与订货量之差，使用CASE搜索函数判断该商品是否进货。2021/3/21139.3 程序控制流程序控制流语句句DECLARE s int,n int,t int,c intSET S=0SET n=1WHILE n=10 -循环求循环求10项和项

33、和 BEGIN SET c=1 SET t=1 WHILE c=n -循环求某一项的阶层循环求某一项的阶层 BEGIN SET t=t*c SET c=c+1 END SET s=s+t SET n=n+1 ENDSELECT s,n例9-32 计算s=1!+2!+10!。2021/3/2114第第10章章 存储过程与触发器存储过程与触发器n10.1 存储过程概述存储过程概述n10.2 存储过程的创建与使用存储过程的创建与使用2021/3/2115本章要点了解存储过程的类型了解存储过程的类型掌握带参数的存储过程的创建、调用掌握带参数的存储过程的创建、调用2021/3/2116 1存储过程的类型

34、：存储过程的类型：系统存储过程系统存储过程用户定义存储过程用户定义存储过程临时存储过程临时存储过程扩展存储过程扩展存储过程2021/3/2117 2常用的系统存储过程：常用的系统存储过程：sp_helpdbsp_help sp_addtypesp_helpindexsp_renamesp_helptextsp_bindrulesp_unbindrulesp_bindefault sp_unbindefaultsp_unbindefault2021/3/21183使用使用Transact-SQL语句语句创建存储过程创建存储过程语法格式：语法格式：CREATE PROC procedure_nam

35、eparameter data_type VARYING=default OUTPUT,.n AS sql_statement,.n 2021/3/2119实 例CREATE PROC sell_info employee_name varchar(20)AS SELECT employee_name,goods_name,classification_name,order_numFROM employee e INNER JOIN sell_order s ON e.employee_id=s.employee_idJOIN goods g ON g.goods_id=s.goods_idJ

36、OIN goods_classification gc ON gc.classification_id=g.classification_idWHERE employee_name LIKE employee_name例例10-3创建带参数的存储过程，从表创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返回输入的员工名、该的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。员工销售的商品名、商品类别、销售量等信息。2021/3/2120存储过程存储过程sell_info以以employee_name变量作为输入参数，执变量作为输入参数，执行时，可以省略参数名，直接给参数值。在行时，可以省略参数名，直接给参数值。在SQL查询分析器中查询分析器中输入命令：输入命令：EXEC sell_info 东方牧东方牧运行结果：运行结果：参数值可以包含通配符参数值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“钱钱”的员的员工的销售情况可以使用以下命令：工的销售情况可以使用以下命令：EXEC sell_info 钱钱%2021/3/2121寄 予全面复习，争取好成绩！全面复习，争取好成绩！2021/3/2122