• 1. 第1章 设计数据库(时间:2次课,4学时)
  • 2. 第1章 设计数据库1.1 工作场景导入 1.2 数据库概述 1.3 需求分析 1.4 概念模型设计 1.5 逻辑模型设计 1.6 物理模型设计 1.7 数据库实现、运行和维护 1.8 SQL Server 2008简介 1.9 回到工作场景 1.10 工作训练营
  • 3. 1.1 工作场景导入(1)为了提高教务管理工作水平,提高学校日常管理工作信息化、智能化的要求,教务处要求信息管理员小孙创建一个学生成绩管理系统。学生成绩管理系统所涉及的信息包括校内所有的系、班级、学生、课程和学生成绩。 学生成绩管理系统的具体实施步骤分成两步。 第一步,创建一个学生成绩数据库,将系统所有的信息存储在数据库中。 第二步,以学生成绩数据库为基础创建学生成绩管理系统,通过Windows应用程序或浏览器来完成系统信息的修改和查询。
  • 4. 1.1 工作场景导入(2)(1) 什么是数据库?数据库的发展历史是怎样的? (2) 怎样完成需求分析? (3) 怎样完成概念模型设计? (4) 怎样完成逻辑模型设计? (5) 怎样完成物理模型设计? (6) 怎样完成数据库实施、运行和维护? (7) 什么是SQL Server 2008?
  • 5. 1.2 数据库概述 1.2.1 数据库基本概念 1.2.2 数据库发展历史 1.2.3 数据库系统结构
  • 6. 1.2.1 数据库基本概念(1)数据是描述事物的符号记录。数据包括文字、图形、图像及声音等。 数据库(Database,DB)是一个长期存储在计算机内的、有组织的、可共享的、统一管理的数据集合。数据库中的数据是按照一定的数据模型组织、描述和存储的,有较小的冗余度、较高的数据独立性和易扩展性。 数据库管理系统(Database Management System,DBMS)是使用和管理数据库的系统软件,负责对数据库进行统一的管理和控制。所有对数据库的操作都交由数据库管理系统完成,这使得数据库的安全性和完整性得以保证。
  • 7. 1.2.1 数据库基本概念(2)数据库管理员(Database Administrator,DBA)是专门负责管理和维护数据库服务器的人。 数据库系统(Database Systems,DBS)是由数据库及其相关应用软件、支撑环境和使用人员所组成的系统,专门用于完成特定的业务信息处理。数据库系统通常由数据库、数据库管理系统、数据库管理员、用户和应用程序组成。
  • 8. 1.2.2 数据库发展历史数据库的发展大致可划分为以下几个阶段:人工管理阶段、文件系统阶段、数据库系统阶段。
  • 9. 1.2.3 数据库系统结构数据库系统通常采用3级模式结构。 模式:模式也称逻辑模式,表示数据库中全体数据的逻辑结构、数据之间的联系、安全性和完整性要求,是完整的数据视图。模式所描述的逻辑结构包含了整个数据库。 外模式:外模式也称子模式或者用户模式,表示数据库用户能够使用的部分数据的逻辑结构和特征,是用户的数据视图。外模式面向用户,用于描述用户所关心的数据。 内模式:内模式也称存储模式,表示数据库中数据物理结构和存储结构。内模式描述了数据库在物理存储设备上的存储方式。
  • 10. 1.3 需求分析 1.3.1 需求分析的任务 1.3.2 需求分析的方法 1.3.3 需求分析的成果
  • 11. 1.3.1 需求分析的任务需求分析的内容是充分调查研究,收集基础数据,了解系统运行环境,明确用户需求,确定新系统的功能,最终得到系统需求分析说明书,作为设计数据库的依据。
  • 12. 1.3.2 需求分析的方法在做需求分析时,首先要了解用户单位的组织机构组成,然后调查用户单位的日常业务活动流程。在此基础上,明确用户的信息需求和系统概念需求,明确用户对系统的性能和成本的要求,确认数据项,产生系统需求说明书。需求分析的调查方法包括跟班作业、开调查会、请专人调查、发放用户调查表和查阅原系统有关记录。
  • 13. 1.3.3 需求分析的成果需求分析生成的结果包括数据字典、数据流图、判定表和判定树等。
  • 14. 1.4 概念模型设计 1.4.1 数据模型 1.4.2 概念模型 1.4.3 概念模型设计的任务 1.4.4 概念模型设计的方法 1.4.5 概念模型设计的成果 1.4.6 实体-联系模型
  • 15. 1.4.1 数据模型数据模型是用来描述现实世界的数据、数据之间的联系、数据的语义和完整性约束的工具。数据模型包括概念模型、逻辑模型和物理模型。
  • 16. 1.4.2 概念模型概念模型要能真实地反映现实世界,包括事物和相互之间的联系,能满足用户对数据的处理要求,是表示现实世界的一个真实模型。
  • 17. 1.4.3 概念模型设计的任务概念模型设计的任务是根据需求分析说明书对现实世界进行数据抽象,建立概念模型。
  • 18. 1.4.4 概念模型设计的方法概念模型的设计方法有4种,分别是自顶向下、自底向上、逐步扩张和混合策略。 自顶向下是指先设计概念模型的总体框架,再逐步细化。 自底向上是指先设计局部概念模型,再合并成总体。 逐步扩张是指先设计概念模型的主要部分,再逐步扩充。 混合策略是指将自顶向下和自底向上相结合,先设计概念模型的总体框架,再根据框架来合并各局部概念模型。
  • 19. 1.4.5 概念模型设计的成果概念模型有实体-联系模型、面向对象的数据模型、二元数据模型、语义数据模型、函数数据模型等。
  • 20. 1.4.6 实体-联系模型(1)实体-联系模型是用E-R图来描述现实世界的概念模型。 实体-联系模型中的基本概念有以下几个:实体,属性,实体集,联系,键,域。
  • 21. 1.4.6 实体-联系模型(2)E-R图的内容包括实体型、属性和联系。 E-R图中,实体型用矩形表示,内有实体型名称;属性用椭圆形表示,内有属性名称,并用直线与所属实体型相连;联系用菱形表示,内有联系名称,并用直线与实体型相连,且在联系旁边注明联系的类型(如1∶1、1∶n或者m∶n)。如果联系有属性,那么也要用直线将属性和联系相连。
  • 22. 1.4.6 实体-联系模型(3)采用实体-联系模型进行概念模型设计的步骤分为3步。 设计局部实体-联系模型。 设计全局实体-联系模型。 优化全局E-R模型。
  • 23. 1.5 逻辑模型设计1.5.1 逻辑模型 1.5.2 关系模型概念 1.5.3 逻辑模型设计的任务 1.5.4 关系模型设计的方法
  • 24. 1.5.1 逻辑模型逻辑模型通常由数据结构、数据操作和完整性约束组成。其中,数据结构是指表示与数据类型、内容、性质等有关的系统静态特性,数据操作是数据库检索和更新操作的含义、规则和实现的语言,数据的约束条件是逻辑模型中数据及其联系所要遵守的完整性规则的集合。 已有的逻辑模型有层次模型、网状模型和关系模型。
  • 25. 1.5.2 关系模型概念(1)关系:表。 元组:表中的一行。关系中的元组不能重复,而且理论上没有顺序。 属性:表中的一列。关系中的属性值具有原子性,不可分解。 码:表中可以唯一确定一个元组的属性或者属性组。 候选码:表中所有可以唯一确定一个元组的属性或者属性组。 主码:表中唯一确定一个元组的属性或者属性组。
  • 26. 1.5.2 关系模型概念(2)外码:不是表中的码,而是与另一个表中的主码相对应的属性或者属性组。 域:属性的取值范围。 分量:一个元组中的某个属性值。 关系模式:对关系的描述,可写成:关系名(属性1,属性2,…,属性n)。 关系模型的数据操作主要包括查询、插入、更新和删除。 关系模型的完整性分为3类,是实体完整性、参照完整性和用户自定义完整性。
  • 27. 1.5.3 逻辑模型设计的任务逻辑模型设计的任务是把概念结构模型转换为所使用的DBMS所支持的逻辑模型。
  • 28. 1.5.4 关系模型设计的方法将实体-联系模型转换成关系模型的步骤分为3步。 (1)将E-R图转换为关系模式集合。 (2)对关系模式集合进行规范化处理,满足一定的范式。 (3)优化关系模式,定义数据完整性、安全性,评估性能。
  • 29. 1.6 物理模型设计1.6.1 物理模型设计的任务 1.6.2 物理模型设计的方法
  • 30. 1.6.1 物理模型设计的任务物理模型的设计是要选取一个最适合数据库应用环境的物理结构,包括数据库的存储记录格式、存储记录安排和存取方法,使得数据库具有良好的响应速度、足够的事务流量和适宜的存储空间。它与系统硬件环境、存储介质性能和DBMS有关。
  • 31. 1.6.2 物理模型设计的方法在关系模型数据库中,物理模型主要包括存储记录结构的设计、数据存放位置、存取方法、完整性、安全性和应用程序。
  • 32. 1.7 数据库实现、运行和维护数据库实现的内容包括使用DBMS创建实际数据库结构、加载初始数据、编制和调试相应的数据库系统应用程序。 数据库的运行内容是指使用已加载的初始数据对数据库系统进行试运行、制订合理的数据备份计划、调整数据库的安全性和完整性条件。 数据库的维护内容是对系统的运行进行监督,及时发现系统的问题,给出解决方案。
  • 33. 1.8 SQL Server 2008简介1.8.1 SQL Server 2008产品性能 1.8.2 SQL Server 2008产品版本 1.8.3 SQL Server 2008管理工具
  • 34. 1.8.1 SQL Server 2008产品性能SQL Server 是一个关系数据库管理系统,最初是由Microsoft、Sybase及Ashton-Tate三家公司开发的。 SQL Server 2008是一个重要的产品版本,它推出了许多新的特性和关键的改进,引入了用于提高开发人员、架构师和管理员效率的新功能,改进了包括T-SQL语句、数据类型和管理功能,添加了许多新特性,引入了商业智能。这使得SQL Server 2008成为迄今为止功能最强大和内容最全面的SQL Server版本。
  • 35. 1.8.2 SQL Server 2008产品版本SQL Server 2008分为SQL Server 2008企业版、标准版、工作组版、Web版、开发者版、Express版和Compact 3.5版。
  • 36. 1.8.3 SQL Server 2008管理工具SQL Server Management Studio Business Intelligence Development Studio SQL Server配置管理器 SQL Server Profiler 数据库引擎优化顾问 命令行工具
  • 37. 1.9 回到工作场景创建一个学生成绩数据库,所涉及的信息包括校内所有的系、班级、学生、课程和学生成绩。 根据学生成绩数据库E-R图,转换得到学生成绩数据库关系模式如下。 系(系编号,系名称)。 班级(班级编号,班级名称,专业,系编号)。 学生(学生编号,姓名,班级编号,生日,性别,住址)。 课程(课程编号,课程名称,课程类别,学分)。 成绩(学生编号,课程编号,成绩)。
  • 38. 1.10 工作训练营设计一个商品信息管理数据库,商品信息管理数据库的信息内容如下。 每个业务员有工号、姓名,每种商品有商品编号、商品名称、价格、库存数量。每个业务员可以销售多种商品,每种商品可以由多个业务员销售。销售记录有销售编号、商品编号、数量、销售日期、工号。每种商品可以由多个供应商供应,每个供应商有供应商编号、供应商名称、联系电话。每个供应商可以供应多种商品,每个供应记录有供应编号、商品编号、数量、价格、供应日期、供应商编号。 先画出商品信息管理数据库的E-R图,再转换成关系模型。
  • 39. 第2章 创建数据库 (时间:1次课,2学时)
  • 40. 第2章 创建数据库2.1 工作场景导入 2.2 SQL Server数据库 2.3 数据库的创建与操作 2.4 文件组及其创建与使用 2.5 回到工作场景 2.6 工作实训营
  • 41. 2.1 工作场景导入 (1)信息管理员小孙已建立了学生成绩数据库的模型,下面小孙要使用SQL Server 2008数据库管理系统来完成学生成绩数据库的创建。 学生成绩数据库的逻辑名称是StudentScore。其中的数据文件StudentScore的初始大小为10MB,文件增长设置为“按10%增长”,最大文件大小设置为100MB,日志文件StudentScore_log的初始大小为2MB,文件增长设置为“按10MB增长”,最大文件大小设置为“不限制文件增长”。此外,学生成绩数据库还有一个数据文件Student_Data,名称为Student_Data.ndf,文件增长设置为“按10%增长”,最大文件大小设置为50MB,其文件组设置为STUDENT文件组。
  • 42. 2.1 工作场景导入(2)(1) 如何创建数据库? (2) 如何创建表? (3) 如何创建并使用文件组?
  • 43. 2.2 SQL Server数据库 2.2.1 SQL Server数据库类型 2.2.2 数据库的文件组成 2.2.3 事务和事务日志 2.2.4 数据存储方式
  • 44. 2.2.1 SQL Server数据库类型SQL Server 2008中的数据库包括两类,一类是系统数据库,另一类是用户数据库。 系统数据库在SQL Server 2008安装时就被安装,和SQL Server 2008数据库管理系统共同完成管理操作。在SQL Server 2008中,系统数据库共有5个,即master、model、tempdb、msdb和resource数据库。 用户数据库是由SQL Server 2008的用户在SQL Server 2008安装后创建,专门用于存储和管理用户的特定业务信息。
  • 45. 2.2.2 数据库的文件组成SQL Server数据库建立后,通常其包含的文件包括3类。 主要数据文件。主要数据文件的文件扩展名是 .mdf。主要数据文件在数据库创建时生成,可存储用户数据和数据库中的对象。每个数据库有一个主要数据文件。 次要数据文件。次要数据文件的文件扩展名是.ndf。每个数据库的次要数据文件个数可以是0至多个。 事务日志。事务日志的文件扩展名是.ldf。事务日志文件在数据库创建时生成,用于记录所有事务以及每个事务对数据库所做的修改,这些记录就是恢复数据库的依据。
  • 46. 2.2.3 事务和事务日志事务是SQL Server中最基本的工作单元,它由一个或多个T-SQL语句组成,执行一系列操作。事务中修改数据的语句要么全都执行,要么全都不执行。 SQL Server 2008 数据库中的事务日志用于记录所有事务以及每个事务对数据库所做的修改。
  • 47. 2.2.4 数据存储方式页是 SQL Server 中数据存储的基本单位。区是由8个物理上连续的页构成的集合。 在 SQL Server 中,页的大小为8KB。每页的开头是96字节的标头,用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。 SQL Server以区作为管理页的基本单位。所有页都存储在区中。一个区包括8个物理上连续的页(即64KB)。 SQL Server 有两种类型的区,即统一区和混合区。
  • 48. 2.3 数据库的创建与操作2.3.1 创建数据库 2.3.2 查看数据库 2.3.3 修改数据库 2.3.4 删除数据库
  • 49. 2.3.1 创建数据库(1)创建数据库有两种途径:一种是在对象资源管理器中通过菜单创建数据库;另一种是在查询编辑器中输入创建数据库的T-SQL语句并运行,完成创建数据库操作。 创建数据库的T-SQL语句是CREATE DATABASE语句。
  • 50. 2.3.1 创建数据库(2)【实例2.1】创建数据库Student。其中主数据文件的逻辑名称是Studentdata,对应的物理文件是C:\Studentdata.mdf,初始大小是10MB,最大尺寸是50MB,增长幅度是5%。日志文件的逻辑名称是Studentlog,对应的物理文件是C:\Studentlog.ldf,初始大小是5MB,最大尺寸是10MB,增长幅度是1MB。 CREATE DATABASE Student ON (NAME = Studentdata, FILENAME = 'C:\Studentdata.mdf', SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ) LOG ON ( NAME = Studentlog,FILENAME = 'C:\Studentlog.ldf', SIZE = 5MB,MAXSIZE = 10MB,FILEGROWTH = 1MB )
  • 51. 2.3.2 查看数据库(1)可以使用系统存储过程sp_helpdb查看所有或者特定数据库的信息,数据库的信息包括数据库的名称、大小、所有者、ID、创建日期、数据库选项及数据库所有文件的信息。
  • 52. 2.3.2 查看数据库(2)【实例2.2】使用系统存储过程sp_helpdb查看所有数据库的信息。 USE master GO EXEC sp_helpdb GO
  • 53. 2.3.3 修改数据库(1)修改数据库有两种途径:一种是在对象资源管理器中通过菜单修改数据库;另一种是在查询编辑器中输入修改数据库的T-SQL语句并运行,完成修改数据库操作。 修改数据库的T-SQL语句是ALTER DATABASE语句。
  • 54. 2.3.3 修改数据库(2)【实例2.4】设置数据库Student的名称为MyStudent。 USE master GO ALTER DATABASE Student Modify Name = MyStudent GO
  • 55. 2.3.4 删除数据库(1) 删除数据库有两种途径:一种是在对象资源管理器中右击数据库,然后在弹出的快捷菜单中选择【删除】命令来删除数据库;另一种是在查询编辑器中输入删除数据库的T-SQL语句并运行,完成修改数据库操作。 删除数据库的T-SQL语句是DROP DATABASE语句。
  • 56. 2.3.4 删除数据库(2) 【实例2.7】删除数据库Student USE master GO DROP DATABASE Student GO
  • 57. 2.4 文件组及其创建与使用2.4.1 文件组 2.4.2 创建文件组 2.4.3 使用文件组
  • 58. 2.4.1 文件组SQL Server 数据库是由一组文件组成,数据和日志信息分属不同文件,每个文件属于一个数据库。文件组是数据库中数据文件的逻辑组合,可以通过文件组将数据文件分组,便于存放和管理数据。
  • 59. 2.4.2 创建文件组(1)可以在创建数据库时创建文件组,因此创建文件组和创建数据库的方法是一样的。一种是在对象资源管理器中通过菜单创建数据库,另一种是在查询编辑器中输入创建数据库的T-SQL语句并运行,完成创建数据库的操作。 可以使用CREATE DATABASE语句创建带文件组的数据库。
  • 60. 2.4.2 创建文件组(2)【实例2.8】创建数据库Student。其中,主要数据文件的逻辑名称是Studentdata,对应的物理文件是C:\Studentdata.mdf,初始大小是10MB,最大尺寸是50MB,增长幅度是5%。日志文件的逻辑名称是Studentlog,对应的物理文件是C:\Studentlog.ldf,初始大小是5MB,最大尺寸是10MB,增长幅度是1MB。添加文件组STUDENTGROUP,添加次要数据文件Studentadddata,物理文件为C:\Studentadd.ndf,初始大小为10MB,最大尺寸为50MB,自动增长为5MB。
  • 61. 2.4.2 创建文件组(3)CREATE DATABASE Student ON PRIMARY ( NAME = Studentdata, FILENAME = 'C:\Studentdata.mdf', SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ), FILEGROUP STUDENTGROUP ( NAME = Studentadddata, FILENAME = 'C:\Studentadddata.ndf', SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ) LOG ON ( NAME = Studentlog,FILENAME = 'C:\Studentlog.ldf', SIZE = 5MB,MAXSIZE = 10MB,FILEGROWTH = 1MB )
  • 62. 2.4.3 使用文件组(1)创建数据库后,可以添加文件组和文件组中的数据文件,方法有两种:一种是在对象资源管理器中通过菜单添加文件组;另一种是在查询编辑器中输入修改数据库的T-SQL语句并运行,完成添加文件组操作。 可以使用ALTER DATABASE语句为数据库添加文件组。
  • 63. 2.4.3 使用文件组(2)【实例2.9】为数据库Student添加文件组STUDENTGROUP,添加次要数据文件Studentadddata,物理文件为C:\Studentadddata.ndf,初始大小为10MB,最大尺寸为50MB,自动增长为5MB,其他采用默认设置。 ALTER DATABASE Student ADD FILEGROUP STUDENTGROUP ALTER DATABASE Student ADD FILE ( NAME = Studentadddata, FILENAME = 'C:\Studentadddata.ndf', SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ) TO FILEGROUP STUDENTGROUP
  • 64. 2.5 回到工作场景(1)根据第1章中的数据库设计,创建学生成绩数据库StudentScore。其中的数据文件StudentScore的初始大小为10MB,文件增长设置为“按10%增长”,最大文件大小设置为100MB,日志文件StudentScore_log的初始大小为2MB,文件增长设置为“按10MB增长”,最大文件大小设置为“不限制文件增长”。 (2)为学生成绩数据库添加文件组和数据文件。文件组名称为STUDENT。文件初始大小为3MB,文件增长设置为“按10%增长”,最大文件大小设置为100MB。文件保存在C盘根目录下,名称为Student_Data.ndf。
  • 65. 2.6 工作训练营(1)(1)创建数据库MyStudent。其中主要数据文件的逻辑名称是MyStudentdata,对应的物理文件是C:\MyStudentdata.mdf,初始大小是20MB,最大文件大小是100MB,增长幅度是2%。日志文件的逻辑名称是MyStudentlog,对应的物理文件是C:\MyStudentlog.ldf,初始大小是5MB,最大文件大小是20MB,增长幅度是1MB。 (2)查看MyStudent数据库的信息。 (3)设置数据库MyStudent的名称为MyStudent1。
  • 66. 2.6 工作训练营(2)(4)删除数据库MyStudent。 (5)创建文件组SCOREGROUP,添加数据文件Score_Data,对应的物理文件是C:\MyScore_Data.nd,将其文件组设置为新创建的SCOREGROUP文件组,将Score_Data的文件增长设置为“按10%增长”,最大文件大小设置为50MB。
  • 67. 第3章 创建和管理表(时间:1次课,2学时)
  • 68. 第3章 创建和管理表3.1 工作场景导入 3.2 数据类型 3.3 表的创建与操作 3.4 数据完整性 3.5 回到工作场景 3.6 工作实训营
  • 69. 3.1 工作场景导入 (1)信息管理员小孙已完成了学生成绩数据库的创建,接下来需要创建数据库中所有表,并且完成对所有表的数据完整性的设置,确保不符合要求的数据不能存储在数据库中。具体要求如下: 创建系别表,该表名称为Department。 创建班级表,该表名称为Class。
  • 70. 3.1 工作场景导入(2)(1) 什么是数据类型? (2) 如何创建和使用自定义数据类型? (3) 如何创建表? (4) 如何确保表中各记录的特定字段不为空值且互不相同? (5) 如何使表中字段存在默认值? (6) 如何使表中字段的值满足某个特定的条件表达式? (7) 如何使一个表中的特定字段值引用自另一个表中的特定字段的已有值?
  • 71. 3.2 数据类型3.2.1 SQL Server数据类型 3.2.2 用户定义数据类型 3.2.3 创建用户定义数据类型
  • 72. 3.2.1 SQL Server数据类型(1)SQL Server 提供系统数据类型集,该类型集定义了可与 SQL Server 一起使用的所有数据类型。此外,还可以创建用户定义数据类型,丰富了SQL Server数据库中可使用的数据类型。 SQL Server中所提供的系统数据类型主要可分为以下几类: 精确数字:tinyint,smallint,integer(int),bigint,decimal,numeric,bit 近似数字:float,real 货币:smallmoney,money
  • 73. 3.2.1 SQL Server数据类型(2)日期和时间:datetime,smalldatetime,date,time 字符串:char,varchar,nchar,nvarchar 二进制:binary,varbinary 大值:text,ntext,image 游标:cursor 时间戳:timestamp 表:table 在字符串类型中,char和varchar存储的是非UNICODE字符数据,而nchar和nvarchar存储的是UNICODE字符数据。
  • 74. 3.2.2 用户定义数据类型用户定义数据类型是基于SQL Server系统的数据类型。 创建的用户定义数据类型将隶属于所存在的用户数据库。
  • 75. 3.2.3 创建用户定义数据类型(1)创建用户定义数据类型有两种途径:一种是在对象资源管理器中通过菜单创建用户定义数据类型;另一种是在查询编辑器中输入创建用户定义数据类型的T-SQL语句并运行,完成创建用户定义数据类型操作。 可以使用CREATE TYPE语句创建用户定义数据类型。
  • 76. 3.2.3 创建用户定义数据类型(2)【实例3.1】创建用户定义数据类型IDNUMBER,基于系统提供的 char 数据类型,长度为17,内容是数字和字母,用于保存身份证号码。 CREATE TYPE IDNUMBER FROM char(17) NOT NULL
  • 77. 3.3 表的创建与操作3.3.1 创建表 3.3.2 在表中录入记录 3.3.3 修改表 3.3.4 删除表
  • 78. 3.3.1 创建表(1)创建表有两种途径:一种是在对象资源管理器中通过菜单创建表;另一种是在查询编辑器中输入创建表的T-SQL语句并运行,完成创建表的操作。 可以使用CREATE TABLE语句创建表。
  • 79. 3.3.1 创建表(2)【实例3.2】创建表mytable,该表中有3个字段,分别是:ID,tinyint型,标识列,标识种子值为1,标识增量值为1;str,char型,长度为10,不可为空;dt,datetime型,可为空。 CREATE TABLE mytable(ID tinyint IDENTITY(1,1) NOT NULL, str char(10) NOT NULL,dt datetime NULL)
  • 80. 3.3.2 在表中录入记录在已创建的表中可以输入记录,方法是在对象资源管理器中右击该表,在弹出的快捷菜单中选择【编辑前200行】命令。未输入值的字段值将显示Null。
  • 81. 3.3.3 修改表(1)修改表有两种途径:一种是在对象资源管理器中通过菜单修改表;另一种是在查询编辑器中输入修改表的T-SQL语句并运行,完成修改表的操作。 可以使用ALTER TABLE语句修改表,来更改、添加或删除列。
  • 82. 3.3.3 修改表(2)【实例3.3】在表mytable中添加字段column_add,varchar类型,长度为20,可为空。 ALTER TABLE mytable ADD column_add VARCHAR(20) NULL
  • 83. 3.3.4 删除表(1) 删除表有两种途径:一种是在对象资源管理器中通过菜单删除表;另一种是在查询编辑器中输入删除表的T-SQL语句并运行,完成删除表的操作。 可以使用DROP TABLE语句删除表。
  • 84. 3.3.4 删除表(2) 【实例3.6】从当前数据库中删除表mytable。 DROP TABLE mytable
  • 85. 3.4 数据完整性3.4.1 数据完整性及其分类 3.4.2 PRIMARY KEY约束 3.4.3 UNIQUE约束 3.4.4 DEFAULT定义 3.4.5 CHECK约束 3.4.6 FOREIGN KEY约束
  • 86. 3.4.1 数据完整性及其分类数据完整性分为以下几类:实体完整性、域完整性和引用完整性。 实体完整性:指通过表中字段或字段组合能将表中各记录的唯一性区别开来。 域完整性:指表中特定字段的值的有效取值。 引用完整性:数据库中的表和表之间的字段值是有联系的,甚至表自身的字段值也是有联系的,其中一个表中的某个字段值不但要符合其数据类型,而且必须是引用另一个表中某个字段现有的值。
  • 87. 3.4.2 PRIMARY KEY约束(1)一个表由若干字段构成,其中的一个或一组字段值可以用来唯一标识表中每一行,这样的一个或一组字段称为表的主键,用于实施实体完整性。在创建或修改表时,可以通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个主键,也就是说只能有一个PRIMARY KEY 约束。 创建主键有两种途径:一种是在创建表时创建主键;另一种是修改现有表来创建主键。 可以使用CREATE TABLE语句在创建表时创建主键。可以用ALTER TABLE语句为现有表创建主键。
  • 88. 3.4.2 PRIMARY KEY约束(2)【实例3.7】创建表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上添加主键,该主键约束的名称是PK_mytable。 CREATE TABLE mytable (ID tinyint NOT NULL, str char(10) NOT NULL,dt datetime NULL, CONSTRAINT PK_mytable PRIMARY KEY CLUSTERED (ID ASC))
  • 89. 3.4.2 PRIMARY KEY约束(3)【实例3.8】修改表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上添加主键。 ALTER TABLE mytable ADD CONSTRAINT PK_mytable PRIMARY KEY CLUSTERED (ID ASC)
  • 90. 3.4.3 UNIQUE约束(1)当表中除主键列之外,还有其他字段需要保证取值不重复时,可以使用 UNIQUE 约束。 一个表只能有一个主键,但可以对一个表定义多个 UNIQUE 约束。UNIQUE 约束允许 NULL 值,但因为使用UNIQUE约束的字段值强制唯一性,因此该字段值只能有一个空值。 创建UNIQUE约束有两种途径。一种是在创建表时创建UNIQUE约束;另一种是修改现有表来创建UNIQUE约束。 可以用CREATE TABLE语句在创建表时创建UNIQUE约束。可以用ALTER TABLE语句修改现有表创建UNIQUE约束。
  • 91. 3.4.3 UNIQUE约束(2)【实例3.9】创建表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上添加UNIQUE约束,该UNIQUE约束的名称是IX_mytable。 CREATE TABLE mytable (ID tinyint NOT NULL, str char(10) NOT NULL, dt datetime NULL, CONSTRAINT IX_mytable UNIQUE NONCLUSTERED (ID ASC))
  • 92. 3.4.3 UNIQUE约束(3)【实例3.10】修改表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上添加UNIQUE约束。 ALTER TABLE mytable ADD CONSTRAINT IX_mytable UNIQUE NONCLUSTERED (ID ASC)
  • 93. 3.4.4 DEFAULT定义(1)在实际业务中,希望系统能为某些没有确定值的字段赋予一个默认值,而不是设为NULL,这时可以为字段设置DEFAULT 定义。一个字段只有在不可为空的时候才能设置DEFAULT定义。 创建DEFAULT定义有两种途径:一种是在创建表时创建DEFAULT定义;另一种是修改现有表来创建DEFAULT定义。 可以用CREATE TABLE语句在创建表时创建DEFAULT定义。可以用ALTER TABLE语句为现有表创建DEFAULT定义。
  • 94. 3.4.4 DEFAULT定义(2)【实例3.11】创建表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空,默认值为undefined,名称为DF_mytable_str;dt,datetime型,可为空。 CREATE TABLE mytable (ID tinyint NOT NULL,str char(10) NOT NULL CONSTRAINT DF_mytable_str DEFAULT ' undefined',dt datetime NULL)
  • 95. 3.4.4 DEFAULT定义(3)【实例3.12】修改表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。在str字段上添加DEFAULT定义,名称为DF_mytable_str,值为字符串“undefined”。 ALTER TABLE mytable ADD CONSTRAINT DF_mytable_str DEFAULT (' undefined ') FOR str
  • 96. 3.4.5 CHECK约束(1)CHECK约束为所属字段值一个逻辑表达式来限定有效值范围,该逻辑表达式中不包含其他字段值,返回值为TRUE或FALSE。 创建CHECK约束有两种途径:一种是在创建表时创建CHECK约束;另一种是修改现有表来创建CHECK约束。 可以用CREATE TABLE语句在创建表时创建CHECK约束。可以用ALTER TABLE语句修改现有表创建CHECK约束。
  • 97. 3.4.5 CHECK约束(2)【实例3.13】创建表mytable,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。创建CHECK约束CK_mytable,内容是ID字段值大于等于0。 CREATE TABLE mytable (ID tinyint NOT NULL, str char(10) NOT NULL , dt datetime NULL, CONSTRAINT CK_mytable CHECK (ID>=0))
  • 98. 3.4.5 CHECK约束(3)【实例3.14】修改表mytable,该表中有3个字段,分别是:ID,tinyint型,标识列,标识种子值为1,标识增量值为1;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上添加CHECK约束,名称为CK_mytable,内容是ID值大于等于0。 ALTER TABLE dbo.mytable WITH CHECK ADD CONSTRAINT CK_mytable CHECK (ID>=0)
  • 99. 3.4.6 FOREIGN KEY约束(1)外键用于建立一个或多个表的字段之间的引用联系。首先,被引用表的关联字段上应该创建PRIMARY KEY约束或UNIQUE约束,然后,在引用表的字段上创建FOREIGN KEY 约束,从而创建外键。 创建FOREIGN KEY约束有两种途径:一种是在创建表时创建FOREIGN KEY约束;另一种是修改现有表来创建FOREIGN KEY约束。 可以用CREATE TABLE语句在创建表时创建FOREIGN KEY约束。可以用ALTER TABLE语句修改现有表创建FOREIGN约束。
  • 100. 3.4.6 FOREIGN KEY约束(2)【实例3.15】已有表mytable,该表中有3个字段,分别是:ID,tinyint型,主键;str,char型,长度为10,不可为空;dt,datetime型,可为空。创建表mytable1,该表中有3个字段,分别是:ID,tinyint型,标识列,标识种子值为1,标识增量值为1;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上创建外键FK_mytable1_mytable,表明ID字段对mytable表的ID字段的引用。 CREATE TABLE dbo.mytable1 (ID tinyint NOT NULL, str char(10) NOT NULL,dt datetime NULL CONSTRAINT FK_mytable1_mytable FOREIGN KEY(ID) REFERENCES dbo.mytable (ID))
  • 101. 3.4.6 FOREIGN KEY约束(3)【实例3.16】修改表mytable,该表中有3个字段,分别是:ID,tinyint型,主键;str,char型,长度为10,不可为空;dt,datetime型,可为空。修改表mytable1,该表中有3个字段,分别是:ID,tinyint型,不可为空;str,char型,长度为10,不可为空;dt,datetime型,可为空。在ID字段上创建外键FK_mytable1_mytable,表明ID字段对mytable表的ID字段的引用。 ALTER TABLE dbo.mytable1 WITH CHECK ADD CONSTRAINT FK_mytable1_mytable FOREIGN KEY(ID) REFERENCES dbo.mytable (ID)
  • 102. 3.5 回到工作场景(1)(1)创建用户定义数据类型zipcode,用于设置所有表中的邮编为长度是6的字符串。 (2)创建系别表Department、班级表Class和学生表Student。 (3)为学生表和班级表添加约束。内容是:学生表中,学生编号必须不为空而且各学生的学生编号不相同;班级表中,班级编号必须不为空而且各班级的班级编号不相同。 (4)为班级表添加约束。内容是:班级表中,要求各班级的名称也不相同。
  • 103. 3.5 回到工作场景(2)(5)为学生表添加约束。内容是:学生表中,各学生的性别在录入时如果为空则为“男”。 (6)为学生表添加约束。内容是:学生表中,学生的年龄必须大于等于15岁。 (7)为学生表添加约束。内容是:学生表中,各学生的班级编号必须是班级表中已出现过的班级编号。
  • 104. 3.6 工作训练营(1)创建用户定义数据类型scoretype,基于整数,用于保存成绩,范围是0~100。 创建课程表Course。 创建成绩表Scre。 班级表中,班级编号必须不为空而且各班级编号不相同。 班级表中,专业在录入时如果为空则为“待定”。 班级表中,班级编号必须为5个数字。
  • 105. 3.6 工作训练营(2)(7)班级表中,除班级编号不同外,班级名称也不能相同。 (8)班级表中,系编号必须是系别表中已出现过的系编号。
  • 106. 第4章 插入、更新 和删除记录 (时间:1次课,2学时)
  • 107. 第4章 插入、更新和删除记录 4.1 工作场景导入 4.2 插入记录 4.3 更新记录 4.4 删除记录 4.5 回到工作场景 4.6 工作实训营
  • 108. 4.1 工作场景导入(1)信息管理员小孙已创建了学生成绩数据库,创建了数据库中的表,并且完成了所有表的数据完整性的设置。接下来,需要完成所有表中记录的录入。 教务处工作人员小周需要添加一个新的班级记录,班级号是20803,班级名称是“机电200803”,专业是“机电一体化”,系别号是2。 小周需要添加一个新的班级记录,班级号是20804,班级名称是“机电200804”,专业待定,系别号是2。
  • 109. 4.1 工作场景导入(2)如何插入单个记录或多个记录? (2) 如何更新记录? (3) 如何删除记录?
  • 110. 4.2 插入记录4.2.1 插入单个记录 4.2.2 插入多个记录
  • 111. 4.2.1 插入单个记录(1)INSERT语句可以在表中或视图中插入单个或多个记录。语法格式如下: INSERT [INTO] table_or_view_name [(column_list)] data_values 使用INSERT语句向表中插入一行记录时,如果按表中各列的顺序给出新记录的所有字段值,那么在表名后不必指定字段列表;如果没有按表中各列的顺序给出新记录的字段值,或是只给出新记录的部分字段值时,在表名后必须指定字段列表。
  • 112. 4.2.1 插入单个记录(2)【实例4.1】向Student表中插入一条记录,学号是20802002,姓名是王良娣,班级号是20802,生日是1980年1月30日,性别是女,地址是江苏省南京市,邮编是210006,电话是13230394930,入学日期是2008年9月1日,毕业日期空缺,状态是在校,备注空缺。 INSERT INTO Student VALUES('20802002','王良娣','20802','1980-1-30','女','江苏省南京市','210006','13230394930','2008-9-1',null,'在校',null)
  • 113. 4.2.2 插入多个记录(1)可以使用INSERT和 SELECT 子查询配合使用,将查询结果的记录插入表中。INSERT语句中,目的表后的字段列表必须和SELECT子查询中的字段列表数目相同,数据类型按顺序一一对应。 可以使用SELECT 和INTO,用一个表中的记录来创建一个新表。使用SELECT和INTO生成的新表的结构是由SELECT后面所跟的字段列表确定的。
  • 114. 4.2.2 插入多个记录(2)【实例4.3】现有表OldStudent,结构与Student完全一样。现要求将OldStudent表中的所有记录复制到Student表中,仅复制OldStudent表中的学号、姓名、班级号和性别字段。 INSERT INTO Student(studentid,studentname,classid,sex) SELECT studentid,studentname,classid,sex FROM OldStudent
  • 115. 4.2.2 插入多个记录(3)【实例4.4】要求将Student表中的所有记录复制到尚未创建的NewStudent表中,仅复制Student表中的学号、姓名、班级号和性别字段。 SELECT studentid,studentname,classid,sex INTO NewStudent FROM Student
  • 116. 4.3 更新记录 4.3.1 简单更新记录 4.3.2 使用子查询更新记录
  • 117. 4.3.1 简单更新记录(1)UPDATE 语句可以更新表或视图中单行、多行或所有行的记录。语法格式如下: UPDATE   table_or_view_name SET { column_name = { expression | DEFAULT | NULL }[ ,...n ] [WHERE { }]
  • 118. 4.3.1 简单更新记录(2)【实例4.5】将Student表中所有的备注内容更新为“待定”。 UPDATE Student SET memo='待定'
  • 119. 4.3.2 使用子查询更新记录 (1)有时需要根据其他表中的记录来控制本表中记录的更新,这时就要使用子查询来更新记录。子查询的语法格式如下: From table_or_view [INNER] JOIN table_or_view ON search_condition
  • 120. 4.3.2 使用子查询更新记录 (2)【实例4.7】表OldStudent与表Student结构完全相同,将Student表中学号在OldStudent表中也存在的记录的班号修改为20802。 UPDATE Student SET classid ='20802' FROM Student JOIN OldStudent ON Student.studentid=OldStudent.studentid
  • 121. 4.4 删除记录 4.4.1 简单删除记录 4.4.2 清空记录 4.4.3 使用子查询删除记录
  • 122. 4.4.1 简单删除记录(1)DELETE 语句可删除表或视图中的一行或多行记录。语法格式如下: DELETE < table_or_view_name > [ ,...n ] [ WHERE { }]
  • 123. 4.4.1 简单删除记录(2)【实例4.8】删除Student表中学号是20802003的记录。 DELETE FROM Student WHERE studentid='20802003'
  • 124. 4.4.2 清空记录 (1)如果要删除表中的所有记录,除了用DELETE语句,还可以用TRUNCATE TABLE语句来完成。TRUNCATE TABLE 与不含 WHERE 子句的 DELETE 语句类似。但是,TRUNCATE TABLE 速度更快,并且使用更少的系统资源和事务日志资源。
  • 125. 4.4.2 清空记录 (2)【实例4.10】清空Student表中所有记录。 TRUNCATE TABLE Student
  • 126. 4.4.3 使用子查询删除记录 (1)有时需要根据其他表中的记录来控制本表中记录的删除,这时就要使用子查询来删除记录。子查询的语法格式如下: From table_or_view [INNER] JOIN table_or_view ON search_condition
  • 127. 4.4.3 使用子查询删除记录 (2)【实例4.11】删除Student表中所有学号在OldStudent表中出现过的记录。 DELETE FROM Student FROM Student JOIN OldStudent ON Student.studentid=Oldstudent.studentid
  • 128. 4.5 回到工作场景(1)(1)小周需要添加一个新的班级记录,班级号是20803,班级名称是“机电200803”,专业是“机电一体化”,系别号是2。 (2)小周需要添加一个新的班级记录,班级号是20804,班级名称是“机电200804”,专业待定,系别号是2。 (3)小周将班级表中系别号是1的记录复制到一个新建表FirstClass表中。 (4)小周需要将所有班级名称是“电子信息工程技术”的班级更名为“电子信息工程”。 (5)小周需要将所有系名称是“机电系”的班级名称更新成“机电技术”。
  • 129. 4.5 回到工作场景(2)(6) 小周需要删除所有班级号是20802的女同学记录。 (7) 小周需要删除班级名称是“机电200802”的学生记录。 (8) 小周需要清空FirstClass表。
  • 130. 4.6 工作训练营(1)插入课程表Course和成绩表Score中的记录。 (2) 小吴需要添加一门新课程,课程号是00300101,课程名称是移动通信,类型是专业课,学分是4。 (3) 小吴需要将课程表中所有专业课的课程记录复制到一个新建表FirstCourse表中。 (4) 小吴需要将所有专业课的学分加1。 (5) 小吴需要将所有课程名称是“高等数学”的成绩减5分。
  • 131. 4.6 工作训练营(2)(6) 小吴需要删除学生编号是20702的成绩记录。 (7) 小吴需要删除姓名是李大春学生的成绩记录。 (8) 小吴需要清空FirstCourse表。
  • 132. 第5章 查询(时间:2次课,4学时)
  • 133. 第5章 查询5.1 工作场景导入 5.2 简单查询 5.3 多表连接 5.4 子查询 5.5 回到工作场景 5.6 工作实训营
  • 134. 5.1 工作场景导入(1)教务处工作人员小吴在工作中需要查询数据库中的数据。现有以下的查询需求: 查询学生表中所有学生的学号和姓名。 查询学生表中所有学生的年龄。 查询学生表班级编号为“10801”的学生学号和姓名。 查询所有姓“李”并且名字为两个字的学生姓名和班级编号。 查询20702班的学生或所有班级的女学生的姓名和电话号码。
  • 135. 5.1 工作场景导入(2)(1) 如何查询存储在数据库表中的记录? (2) 如何对原始记录进行分组统计? (3) 如何对来自多个表的数据进行查询? (4) 如何动态设置选择记录的条件?
  • 136. 5.2 简单查询5.2.1 结果集字段列表 5.2.2 查询的筛选条件 5.2.3 结果集格式 5.2.4 分组汇总
  • 137. 5.2.1 结果集字段列表(1)SELECT语句由以下几部分组成。 SELECT子句 FROM子句 WHERE子句 GROUP子句 HAVING子句 ORDER BY子句
  • 138. 5.2.1 结果集字段列表(2)查询语句的结果集字段列表用SELECT子句来设置。 SELECT子句在SELECT语句中必须出现,用法为在SELECT后面加上一个字段列表,用于定义SELECT 语句的结果集字段。 字段列表按排列顺序表示结果集中的所有字段,字段之间以逗号分隔。结果集中的字段可以是来自源表和视图的字段,也可以是一个表达式。结果集字段的值由结果集中记录的对应字段或表达式计算得到。 如果返回源表和视图中的所有字段,可以在结果集字段列表中使用“*”。
  • 139. 5.2.1 结果集字段列表(3)【实例5.1】查询学生表中所有学生的信息。 SELECT Studentid,Studentname,Classid,Birthday,Sex, Address,Postalcode,Tel,Enrolldate, Graduatedate,State,Memo FROM Student SELECT * FROM Student
  • 140. 5.2.2 查询的筛选条件(1)WHERE子句用于给定源表和视图中记录的筛选条件。只有符合筛选条件的记录才能为结果集提供数据;否则将不入选结果集。WHERE子句中的筛选条件由一个或多个条件表达式组成。
  • 141. 5.2.2 查询的筛选条件(2)【实例5.2】查询所有女学生的姓名和出生日期。 SELECT Studentname,Birthday FROM Student WHERE Sex='女'
  • 142. 5.2.3 结果集格式(1)使用ORDER BY 子句可以将查询结果按一个或多个字段进行排序,排序的字段应出现在SELECT子句中的字段列表中,或是FROM子句中的表中的字段。当ORDER BY子句中有多个字段时,应按该字段列表的顺序对结果集进行排序。ORDER BY子句语法格式如下。 ORDER BY order_list [ ASC | DESC ]
  • 143. 5.2.3 结果集格式(2)【实例5.8】查询10701班所有学生的姓名、学号和出生日期,结果按出生日期升序排列。 SELECT Studentname,Studentid,Birthday FROM Student WHERE Classid='10701' ORDER BY Birthday
  • 144. 5.2.3 结果集格式(3)如果只需要在排序的结果集中选取前面给定数量的记录而不是全部,可以使用 TOP 子句来指定结果集中返回的记录数。TOP子句语法格式如下: TOP ( expression ) [ PERCENT ] [ WITH TIES ]
  • 145. 5.2.3 结果集格式(4)【实例5.9】查询课程号为00100001的所有成绩,取前5名。 SELECT TOP(5) Studentid,Courseid,Score FROM Score WHERE Courseid='00100001' ORDER BY Score DESC
  • 146. 5.2.3 结果集格式(5)DISTINCT 关键字可从 SELECT 语句的结果中消除重复的行。如果没有指定 DISTINCT关键字,将返回所有行,包括重复的行。DISTINCT关键字语法格式如下: DISTINCT column_list
  • 147. 5.2.3 结果集格式(6)【实例5.10】查询学生表中所有的班级编号,要求无重复。 SELECT DISTINCT Classid FROM Student
  • 148. 5.2.3 结果集格式(7)为了增强结果集的可读性,便于理解各列数据的含义,可以使用AS子句来设置结果集列的名称。AS 子句语法格式如下: AS alien_name
  • 149. 5.2.3 结果集格式(8)【实例5.11】查询20701班学生的学号、姓名,结果集中另加两列,标题分别为“学号”和“姓名”。 SELECT Studentid, Studentname, Studentid AS 学号, Studentname AS 姓名 FROM Student WHERE Classid='20701'
  • 150. 5.2.3 结果集格式(9)UNION 运算符将两个或多个 SELECT 语句的结果集合并成一个结果集。能够合并的SELECT语句的结果集都必须具有相同的结构,即列数必须相同,各列的数据类型必须兼容。合并后的结果集将使用产生第一个结果集的SELECT语句的字段列表,并删除重复的记录。UNION的语法格式如下: SELECT语句 UNION SELECT语句 [UNION …]
  • 151. 5.2.3 结果集格式(10)【实例5.12】查询10701班学生的姓名和电话,查询20802班学生的姓名和电话,合并两次查询的结果集。 SELECT Studentname, Tel FROM Student WHERE Classid=‘10701‘ UNION SELECT Studentname, Tel FROM Student WHERE Classid='20802'
  • 152. 5.2.4 分组汇总(1)聚合函数可对一组值执行特定的计算,并返回单个值。Microsoft SQL Server中使用的聚合函数如表所示。函 数含 义AVG 平均值COUNT 计数MAX 最大值MIN 最小值SUM 和
  • 153. 5.2.4 分组汇总(2)GROUP BY子句将结果集中的记录根据一个或多个列或表达式的值组合成一个个组,每一组生成一条结果集记录。GROUP BY子句语法格式如下: GROUP BY column_list
  • 154. 5.2.4 分组汇总(3)HAVING子句对分组统计的结果设置筛选条件,使用HAVING子句时必须同时使用GROUP BY子句。 HAVING子句和WHERE子句的筛选作用不同,两者作用于不同的对象。WHERE子句是对原始记录进行筛选。HAVING子句是对分组统计的结果进行筛选,因此HAVING子句中可以出现聚合函数。HAVING子句语法格式如下: HAVING search_condition
  • 155. 5.2.4 分组汇总(4)【实例5.13】查询各班学生最早的出生日期。 SELECT Classid, MIN(Birthday) FROM Student GROUP BY Classid
  • 156. 5.2.4 分组汇总(5)【实例5.14】查询各班学生最早的出生日期,要求早于1989年1月1日。 SELECT Classid, MIN(Birthday) FROM Student GROUP BY Classid HAVING MIN(Birthday)< '1989-1-1'
  • 157. 5.3 多表连接 5.3.1 表的别名 5.3.2 内连接 5.3.3 外连接 5.3.4 交叉连接 5.3.5 自连接 5.3.6 多表连接
  • 158. 5.3.1 表的别名(1)多表连接的SELECT语句中,各字段分别来自不同的表,因此要在语句中对字段的描述要么采用完整或部分完整的命名方式,要么为表指定别名来提高语句的可读性。 完整的数据库对象名称包括4个组成部分:服务器名称、数据库名称、架构名称和对象名称,其语法格式为:[服务器名称].[数据库名称].[架构名称].[对象名称]。这样的命名方法适用于远程调用数据和同时使用不同的数据库。
  • 159. 5.3.1 表的别名(2)【实例5.15】查询所有女学生的姓名和学号。 SELECT Studentname, Studentid FROM Student WHERE Sex='女‘ SELECT StudentScore.dbo.Student.Studentname, StudentScore.dbo.Student.Studentid FROM StudentScore.dbo.Student WHERE StudentScore.dbo.Student.Sex='女‘ SELECT St.Studentname, Sr.Studentid FROM StudentScore.dbo.Student AS St WHERE St.Sex='女'
  • 160. 5.3.2 内连接 (1)在多表连接中,内连接使用频率最高。内连接是指返回多个表中完全符合连接条件的记录的连接查询。 内连接中,最常用的是两个表进行内连接。语法格式如下: SELECT table_or_view1.column_list, table_or_view2.column_list FROM table_or_view1 IINER JOIN table_or_view2 ON table1_or_view.column_name1= table_or_view2.column_name2 [WHERE { }]
  • 161. 5.3.2 内连接 (2)【实例5.16】查询“电子200701”班所有学生的姓名、学号和班级编号。 SELECT Studentid, Studentname, Student.Classid FROM Student INNER JOIN Class ON Student.Classid=Class.Classid WHERE Classname='电子200701‘ SELECT Studentid, Studentname, S.Classid FROM Student AS S INNER JOIN Class AS C ON S.Classid=C.Classid WHERE Classname= '电子200701'
  • 162. 5.3.3 外连接 (1)如果希望其中某个表中的记录——甚至是两个表中的记录——即使不符合连接条件也要返回,这时就要使用外连接。 外连接分为3类:左外连接、右外连接和完全外连接。外连接语法格式如下: SELECT table_or_view1.column_list, table_or_view2.column_list FROM table_or_view1 LEFT | RIGHT | FULL OUTER JOIN table_or_view2 ON table1_or_view.column_name1=table_or_view2. column_name2 [WHERE { }]
  • 163. 5.3.3 外连接 (2)【实例5.17】查询所有学生的姓名、学号和班级编号,所有的学生都必须在结果集中存在。 SELECT Class.Classid, Studentid, Studentname FROM Student LEFT OUTER JOIN Class ON Student.Classid=Class.Classid
  • 164. 5.3.4 交叉连接 (1)可以使用交叉连接来生成连接的源表的笛卡儿积,结果集的记录数是第一个表的记录数乘以第二个表的记录数。交叉连接语法格式如下: SELECT table_or_view1.column_list, table_or_view2.column_list FROM table_or_view1 CROSS JOIN table_or_view2 [WHERE { }]
  • 165. 5.3.4 交叉连接 (2)【实例5.19】查询所有学生的姓名、学号和班级编号的交叉连接。 SELECT Class.Classid, Student.Classid,Studentid, Studentname FROM Student CROSS JOIN Class
  • 166. 5.3.5 自连接 (1)在某些查询中,虽然查询内容只与一个表有关,但需要对表中不同的记录进行对比,这时就要采用自连接查询,将该表与自身连接。自连接语法格式如下: SELECT A.column_list, B.column_list FROM table_or_view1 AS A INNER JOIN table_or_view1 AS B ON A.column_name1=B.column_name2 [WHERE { }]
  • 167. 5.3.5 自连接 (2)【实例5.20】查询与学号为10701001的学生的同班同学的姓名和学号。 SELECT A.Studentid, A.Studentname, B.Studentid, B.Studentname FROM Student AS A INNER JOIN Student AS B ON A.Classid=B.Classid WHERE B.Studentid='10701001' AND A.Studentid!=B.Studentid
  • 168. 5.3.6 多表连接 (1)使用3个或3个以上的表进行多表连接时,在连接部分先写连接的表名,再写连接条件,依次将所有表写入语句。
  • 169. 5.3.6 多表连接 (2)【实例5.21】查询电子工程系所有学生的姓名、学号和班级编号。 SELECT Studentid, Studentname, Student.Classid FROM Student INNER JOIN Class ON Student.Classid=Class.Classid INNER JOIN Department ON Classid.Departid=Department.Departid WHERE Departname='电子工程系'
  • 170. 5.4 子查询5.4.1 子查询用做单个值 5.4.2 子查询用做集合 5.4.3 相关子查询
  • 171. 5.4.1 子查询用做单个值(1)返回单个值的子查询可以用于SELECT语句中所有使用单个值的地方,使用的前提是此处的数据类型与使用的子查询的返回值的数据类型相同。子查询用做单个值时,往往在比较运算符之后。
  • 172. 5.4.1 子查询用做单个值(2)【实例5.22】查询“电子200701”班所有学生的姓名、学号和班级编号。 SELECT Studentid, Studentname, Classid FROM Student WHERE Classid=(SELECT Classid FROM Class WHERE ClassName='电子200701')
  • 173. 5.4.2 子查询用做集合 (1)子查询在WHERE子句中使用IN(或 NOT IN),将字段值与包含零个值或多个值的集合进行比较。 子查询在WHERE子句中使用ANY(或 ALL),也是将字段值与包含零个值或多个值的子查询结果集进行比较。例如,>ALL(子查询)的用法表示大于子查询结果集中所有的值,往往用于求最大值;
  • 174. 5.4.2 子查询用做集合(2)【实例5.23】查询班级名称为电子200701、电子200702、机电200701、机电200702的学生学号、姓名和出生日期。 SELECT Studentid, Studentname, Birthday FROM Student WHERE Classid IN (SELECT Classid FROM Class WHERE Classname IN('电子200701','电子200702','机电200701','机电200702'))
  • 175. 5.4.2 子查询用做集合(3)【实例5.24】查询20702班年龄比20701班所有学生都小的学生学号、姓名和出生日期。 SELECT Studentid, Studentname, Birthday FROM Student WHERE Classid='20702' AND Birthday>ALL(SELECT Birthday FROM Student WHERE Classid='20701')
  • 176. 5.4.2 子查询用做集合(4)【实例5.26】查询“电子工程系”学生的学号、姓名和出生日期。 SELECT Studentid, Studentname, Birthday FROM Student WHERE EXISTS(SELECT Classid FROM Class WHERE Student.Classid=Class.Classid AND EXISTS(SELECT Departid FROM Department WHERE Class.Departid=Department.Departid AND Departname='电子工程系'))
  • 177. 5.4.3 相关子查询(1)子查询和外部查询语句之间的联系有两种:一种是子查询不依靠外部查询而执行,这类查询语句的执行顺序是先执行子查询,再将子查询的结果代入外部查询而执行;另一种是子查询必须依靠外部查询而执行,因为其语句中有外部查询的表中字段,这类子查询就称为相关子查询。 相关子查询的执行顺序是先执行外部查询,每次选择外部查询的一行记录,然后将该记录的字段值代入子查询执行,子查询的结果最后返回外部查询而得到最终结果。
  • 178. 5.4.3 相关子查询 (2)【实例5.27】查询已修4门课程的学生学号和姓名。 SELECT S.Studentid, S.Studentname FROM Student AS S WHERE (SELECT COUNT(*) FROM Score AS SC WHERE SC.Studentid=S.Studentid) =4
  • 179. 5.5 回到工作场景(1)(1)查询学生表中所有学生的学号和姓名。 (2)查询学生表中所有学生的年龄。 (3)查询学生表班级编号为“10801”的学生学号和姓名。 (4)查询所有姓“李”并且名字为两个字的学生姓名和班级编号。 (5)查询20702班的学生或所有班级的女学生的姓名和电话号码。
  • 180. 5.5 回到工作场景(2)(6) 查询所有不姓“李”的学生姓名和班级编号。 (7)查询所有出生日期早于1988年4月1日或晚于1988年7月31日的学生姓名、学号和出生日期。 (8)查询所有电话号码不为空的学生姓名、学号和电话号码。 (9)查询10701班所有学生的姓名、学号和出生日期,结果按性别升序和出生日期降序排列。 (10)查询课程号为00100001的所有成绩,取前5%。
  • 181. 5.5 回到工作场景(3)(11)查询课程号为00100001的所有成绩,取前5名(含并列名次)。 (12)查询各班级的人数。 (13)查询总人数大于2人的各班级人数。 (14)查询所有互为同班同学的学号和姓名。 (15)查询已修课程门数高于200701001号的所有学生的学号和成绩。
  • 182. 5.5 回到工作场景(4)(16)查询10701班的学生学号、姓名和出生日期。 (17) 查询班级名称不是电子200701、电子200702、机电200701、机电200702的学生学号、姓名和出生日期。 (18)查询不是“电子工程系”学生的学号、姓名和出生日期。 (19)查询与学号为10701001的学生同班同学的学号和姓名。 (20)查询成绩低于该门课程平均成绩的学生编号、课程编号和成绩。
  • 183. 5.6 工作训练营(1)(1) 查询课程表中所有课程的课程编号和课程名称。 (2) 查询课程表课程编号为00100001课程的名称和学分。 (3) 查询所有学分等于4的课程编号和课程名称。 (4) 查询所有学分等于4的基础课的课程编号和课程名称。 (5) 查询成绩表中小于80分或大于90分的学生编号、课程编号和成绩。
  • 184. 5.6 工作训练营(2)(6) 查询成绩表中课程编号为00100001的学生编号、课程编号和成绩,结果按成绩升序排列。 (7) 查询成绩表中学生编号为10701001的所有成绩,取前3项。 (8) 查询成绩表中学生编号为10701001的学生编号、课程编号和成绩,要求结果集中各栏标题分别为“学生编号”、“课程编号”和“成绩”。 (9) 查询成绩表中课程编号为00100001的最高成绩。 (10) 查询成绩表中各门课程的最高成绩,要求大于90分。
  • 185. 5.6 工作训练营(3)(11) 查询“高等数学”课程的所有学生编号和成绩。 (12) 查询电子工程系学生的学生编号、课程编号和成绩。 (13) 查询班级名称为电子200701、电子200702、机电200701、机电200702的学生的学生编号、课程编号和成绩。 (14) 查询20702班比20701班所有学生都小的学生的学生编号、课程编号和成绩。 (15) 查询比所有10701班学生的平均成绩高的学生的学生编号、课程编号和成绩。
  • 186. 第6章 使用T-SQL语言(时间:1次课,2学时)
  • 187. 第6章 使用T-SQL语言6.1 工作场景导入 6.2 T-SQL 语言 6.3 T-SQL语法要素 6.4 T-SQL程序 6.5 错误信息处理 6.6 事务 6.7 回到工作场景 6.8 工作实训营
  • 188. 6.1 工作场景导入(1)现在,教务处工作人员小吴在工作中需要使用SQL Server完成更多的操作。具体的操作需求如下。 判断2010年是否为闰年。 在学生表中插入记录,学生编号为20702100,班级编号为20702,如果有错误则输出错误信息。 将班级编号为10701的班级记录的班级编号更改为10703,然后将该班级所有学生的班级编号也更改为10703。如果有错误,则输出“无法更改班级编号”,并撤销所有数据更改。
  • 189. 6.1 工作场景导入(2)(1) 在SQL Server中能使用的编程语言是什么?有什么语法元素? (2) 能不能在SQL Server中的编程语言中进行错误处理? (3) 如果有多个T-SQL语句需要作为一个不可分割的执行单元,该怎么做?
  • 190. 6.2 T-SQL 语言SQL(Structured Query Language,结构化查询语言)是一种数据库查询和程序设计语言。它不具备用户界面、文件等编程功能,专用于存取数据以及查询、更新和管理关系数据库系统。SQL是高级的非过程化编程语言,用户只要通过SQL语句提出操作要求而无需关心数据存放方法和系统如何完成操作。 美国国家标准局(ANSI)与国际标准化组织(ISO)已经制定了SQL标准。1992年,ISO和IEC联合发布了SQL国际标准,称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。不同类型的数据库只要遵循SQL标准,就可以使用相同的SQL语言来操作。
  • 191. 6.3 T-SQL语法要素 6.3.1 标识符 6.3.2 数据类型 6.3.3 常量 6.3.4 变量 6.3.5 运算符 6.3.6 表达式 6.3.7 函数 6.3.8 注释 6.3.9 保留关键字
  • 192. 6.3.1 标识符(1)常规标识符:常规标识符要求符合标识符的格式规则。常规标识符的格式规则是:首字符必须是字母、下划线_、符号@或数字符号#之一,后续字符必须是字母、下划线_、符号@、数字符号、美元符号$或十进制数字(0~9)之一;不能是T-SQL的保留字;不允许有空格或其他特殊字符。 例如,mydatabase,_35a,@@five,@five5。
  • 193. 6.3.1 标识符(2)分隔标识符:分隔标识符可以不符合标识符的格式规则,但在使用时必须包含在双引号或者方括号内。 例如,my table是非法的,[my table]和 “my table”是合法的。
  • 194. 6.3.2 数据类型T-SQL语言的数据类型同前面的第3章创建表中的数据类型内容。
  • 195. 6.3.3 常量常量又称为字面量,用于表示确定的数据,值在程序运行中不变,其格式与值的数据类型相关。 例如,'abc',15,0x13ff,0,3.2,'12/15/2010' ,'12:30:12',$5.5。
  • 196. 6.3.4 变量T-SQL语言中局部变量以一个符号@开始,在程序中必须先声明再使用。DECLARE语句完成局部变量声明并赋初值null。SET语句和SELECT语句对局部变量进行赋值。 T-SQL语言中全局变量以两个符号@开始,由SQL Server系统提供,保存了SQL Server系统的当前状态信息,用户只能使用,不能创建。
  • 197. 6.3.5 运算符比较运算符:> < = <= >= != <> !< !> 逻辑运算符:AND OR NOT LIKE ANY ALL IN SOME 算术运算符:+ - * / % 一元运算符:+ - 位运算符:& | ~ ^ 字符串串联运算符:+ 赋值运算符:=
  • 198. 6.3.6 表达式表达式由常量、变量、函数、字段和运算符等组合而成。 表达式中如果有多个运算符,将根据 SQL Server 运算符优先级顺序由高到低来分别进行运算。两个运算符的优先级相同时,按照书写顺序从左到右进行运算。如果表达式中运算顺序与上述规定不一致时,可以使用括号调整运算符的优先级,表达式在括号中的部分优先级最高,括号可以嵌套使用。 表达式中如果用运算符对两个不同数据类型的操作数进行计算,将根据数据类型优先级将优先级较低的数据类型转换为优先级较高的数据类型。该转换是隐式转换。
  • 199. 6.3.7 函数SQL Server 函数完成特定的功能。 SQL Server函数包括系统函数和用户自定义函数。
  • 200. 6.3.8 注释单行注释:一行的全部或部分内容是注释,在该行注释开始位置之前使用“--”。 多行注释:注释范围跨行,在该注释块开始位置之前加“/*”,在该注释块结束位置之后使用“*/”。
  • 201. 6.3.9 保留关键字Microsoft SQL Server 保留了一些专用的关键字,这些关键字具有特定的含义。数据库中对象的名称不能与保留关键字相同。如果存在这样的名称,那么需要使用“分隔标识符”来引用对象。建议在实际应用中不要使用保留关键字作为数据库对象的名称。
  • 202. 6.4 T-SQL程序 6.4.1 控制流 6.4.2 批处理 6.4.3 脚本
  • 203. 6.4.1 控制流(1)T-SQL程序由3种结构组成,即顺序、选择和循环。T-SQL的控制流关键字将T-SQL语句组织起来,成为具备一定功能的程序。 T-SQL的控制流关键字包括BEGIN...END、BREAK、GOTO、CONTINUE、IF...ELSE、WHILE、RETURN、WAITFOR等。
  • 204. 6.4.1 控制流(2)【实例6.5】输出3~20之间所有的素数。
  • 205. 6.4.2 批处理批处理是指一个T-SQL语句的执行组合,一个批处理里有一个或多个T-SQL语句。 在输入批处理时,SQL Server将GO命令作为结束批处理的标志。
  • 206. 6.4.3 脚本如果需要重复使用输入的T-SQL语句,可以先把T-SQL语句保存在SQL脚本文件中。
  • 207. 6.5 错误信息处理6.5.1 TRY...CATCH 6.5.2 @@ERROR系统函数
  • 208. 6.5.1 TRY...CATCHT-SQL 代码中的错误可使用TRY...CATCH构造进行处理,其结构类似于JAVA和C++语言中的异常处理类。TRY...CATCH的语法格式如下: BEGIN TRY      { sql_statement | statement_block } END TRY BEGIN CATCH      [ { sql_statement | statement_block } ] END CATCH [ ; ]
  • 209. 6.5.2 @@ERROR系统函数@@ERROR系统函数返回执行的上一个T-SQL 语句的错误号,值为整数类型。如果上一个T-SQL 语句执行成功,则@@ERROR 系统函数的返回值是0;如果该语句生成错误,则@@ERROR系统函数的返回值就是该语句的错误号。每运行一个T-SQL语句,@@ERROR系统函数的值都随之更新。@@ERROR系统函数的语法格式如下: @@ERROR
  • 210. 6.6 事务6.6.1 事务的概念及其属性 6.6.2 事务的分类及其使用
  • 211. 6.6.1 事务的概念及其属性事务是SQL Server单个逻辑工作单元,事务中包含了多个操作。事务的作用是保证数据逻辑的一致性,从而保证数据满足业务规则要求。 事务有4个属性,包括原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability),简称ACID。
  • 212. 6.6.2 事务的分类及其使用(1)事务分成3类:显式事务、自动提交事务和隐式事务。 显式事务代码中,有明确的事务启动和结束的T-SQL语句。用于启动事务的语句是BEGIN TRANSACTION语句,用于结束事务的语句是COMMIT TRANSACTION和ROLLBACK TRANSACTION。
  • 213. 6.6.2 事务的分类及其使用(2)自动提交事务是SQL Server的默认事务管理模式。每个T-SQL语句作为一个事务单独提交,不需要事务启动和结束语句。 隐式事务使用T-SQL语句SET IMPLICIT_TRANSACTIONS ON将隐性事务模式设置为打开,之后的每一个语句将自动启动一个新事务,该事务完成后依序启动下一个 T-SQL 语句的事务。
  • 214. 6.7 回到工作场景(1)查询判断2010年是否为闰年。 (2)在学生表中插入记录,学生编号为20702100,班级编号为20702,如果有错误则输出错误信息。 (3)将班级编号为10701的班级记录的班级编号更改为10703,然后将该班级所有学生的班级编号也更改为10703。如果有错误,则输出“无法更改班级编号”,并撤销所有数据更改。
  • 215. 6.8 工作训练营(1) 输出1+2+3+4+…+99+100的结果。 (2) 输出字符串“ABCDEFGHIJ”中间的6个字符。 (3) 判断2010年9月1日是该年份第几天(不用系统函数)。 (4) 计算并输出10*10、20*10、30*10的结果,结果用tinyint变量@result保存,如果有错误则输出错误号和错误信息。 (5) 用事务在系别表中添加一条系记录,系名是“通信工程系”,在班级表中添加一条班级记录,班级编号是30901,班级名称是通信200901,专业是通信工程,系别编号是3。如果有错误,则输出“无法添加记录”,并撤销所有记录删除操作。
  • 216. 第7章 使用视图和索引 优化查询 (时间:1次课,2学时)
  • 217. 第7章 使用视图和索引优化查询 7.1 工作场景导入 7.2 视图 7.3 索引 7.4 回到工作场景 7.5 工作实训营
  • 218. 7.1 工作场景导入(1)现在,在工作中需要使用SQL Server完成更多的操作。具体的操作需求如下。 信息管理员小孙需要创建视图Viewstudentscore,包括学生的学生编号、学生姓名、课程名称和成绩,工作人员小周使用该视图查询所有学生的姓名、课程名称和成绩。 信息管理员小孙需要在学生表的学生编号字段上创建聚集索引PK_Student,在学生表的班级编号和姓名字段上创建非聚集索引IX_Student。
  • 219. 7.1 工作场景导入(2)(1) 什么是视图?视图有什么特点? (2) 如何创建、使用、修改视图? (3) 如何提高查询速度和优化数据库性能? (4) 什么是索引?索引有哪些分类? (5) 如何创建、修改、删除索引? (6) 如何设计和优化索引?
  • 220. 7.2 视图7.2.1 视图及其分类 7.2.2 创建视图 7.2.3 使用视图 7.2.4 修改视图 7.2.5 删除视图
  • 221. 7.2.1 视图及其分类(1)视图在使用时如同真实的表一样,也包含字段和记录。 视图和表的不同之处在于,视图是一个虚拟表,除索引视图以外,视图在数据库中仅保存其定义,其中的记录在使用视图时动态生成。 视图中的记录可以来自当前数据库的一个或多个表或视图,也可以来自远程数据库的一个或多个表或视图。 视图中的记录不但可以查询,而且可以进行更新。
  • 222. 7.2.1 视图及其分类(2)视图分为3种,即标准视图、索引视图和分区视图。 标准视图选取了来自一个或多个数据库中一个或多个表及视图中的数据,在数据库中仅保存其定义,在使用视图时系统才会根据视图的定义生成记录。 索引视图在数据库中不仅保存其定义,生成的记录也被保存,还可以创建唯一的聚集索引。使用索引视图可以加快查询速度,从而提高查询性能。 分区视图将一个或多个数据库中的一组表中的记录抽取且合并。分区视图的作用是将大量的记录按地域分开存储,使得数据安全和处理性能得到提高。
  • 223. 7.2.2 创建视图(1)创建视图有两种途径:一种是在对象资源管理器中通过菜单创建视图;另一种是在查询编辑器中输入创建视图的T-SQL语句并运行,完成创建视图的操作。 可以使用CREATE VIEW语句创建视图。
  • 224. 7.2.2 创建视图(2)【实例7.1】创建视图Viewclass,只显示班级表中的班级编号、班级名称和系编号。 CREATE VIEW Viewclass AS SELECT Classid, Classname,Departid FROM Class
  • 225. 7.2.3 使用视图(1)视图创建后,可以如同使用表一样使用视图。在SELECT、INSERT、UPDATE、DELETE语句中,只要是表的位置,都可以用视图来代替。
  • 226. 7.2.3 使用视图(2)【实例7.4】下列两个T-SQL语句运行结果一样吗? SELECT Classid, Classname, Departid FROM Class SELECT Classid, Classname, Departid FROM Viewclass 结果是一样的。
  • 227. 7.2.4 修改视图(1)可以使用ALTER VIEW语句修改视图。在查询编辑器中输入修改视图的语句并运行,完成修改视图操作。
  • 228. 7.2.4 修改视图(2)【实例7.9】修改视图Viewclassdept,只显示班级表中的系别编号为2的班级编号、班级名称,而且保证对视图中的修改必须满足系别编号为2的条件。 ALTER VIEW Viewclassdept AS SELECT Classid, Classname FROM Class WHERE Departid=2 WITH CHECK OPTION
  • 229. 7.2.5 删除视图(1)视图是基于表或其他视图的,因此建议在删除视图前先查看其依赖关系。 删除视图有两种途径:一种是在对象资源管理器中通过菜单删除视图;另一种是在查询编辑器中输入删除视图的T-SQL语句并运行,完成删除视图操作。 可以使用DROP VIEW语句删除视图。
  • 230. 7.2.5 删除视图(2)【实例7.10】从当前数据库中删除视图Viewclassdept。 DROP VIEW viewclassdept
  • 231. 7.3 索引7.3.1 索引及其分类 7.3.2 创建索引 7.3.3 修改索引 7.3.4 删除索引 7.3.5 设计和优化索引
  • 232. 7.3.1 索引及其分类SQL Server数据库中也可以通过适当的索引帮助,减少查询工作量,提高查询特定信息的速度。 SQL Server数据库的索引分为聚集索引和非聚集索引两类。 表中记录根据聚集索引的键值排列顺序来存储在物理介质上,因此一个表最多只能有一个聚集索引。 非聚集索引的键值顺序和表中记录在物理介质上的存储位置顺序是不一致的。一个表可以有多个非聚集索引。 SQL Server还有一种索引,是唯一索引。
  • 233. 7.3.2 创建索引(1)创建索引有两种途径:一种是在对象资源管理器中通过菜单创建索引;另一种是在查询编辑器中输入创建索引的T-SQL语句并运行,完成创建索引操作。 可以使用CREATE INDEX语句创建索引。
  • 234. 7.3.2 创建索引(2)【实例7.11】在Department表的Departid字段上创建升序的聚集索引PK_Department,在Department表的Departname字段上创建降序的唯一非聚集索引IX_Department。 CREATE CLUSTERED INDEX PK_Department ON Department(Departid ASC) CREATE UNIQUE NONCLUSTERED INDEX IX_Department ON Department(Departname DESC)
  • 235. 7.3.3 修改索引(1)修改索引有两种途径:一种是在对象资源管理器中通过菜单修改索引;另一种是在查询编辑器中输入修改索引的T-SQL语句并运行,完成修改索引操作。 如果修改索引所包含的字段,可以直接使用CREATE INDEX语句完成。如果需要启用或禁用索引,重新生成或重新组织索引,或者设置索引选项,可以使用ALTER INDEX语句。
  • 236. 7.3.3 修改索引(2)【实例7.12】在Department表中禁用索引IX_Department。 ALTER INDEX IX_Department ON Department DISABLE
  • 237. 7.3.4 删除索引(1)删除索引有两种途径:一种是在对象资源管理器中通过菜单删除索引;另一种是在查询编辑器中输入删除索引的T-SQL语句并运行,完成删除索引操作。 可以使用DROP INDEX语句删除索引。
  • 238. 7.3.4 删除索引(2)【实例7.16】从Department表上删除索引IX_Department。 DROP INDEX IX_Department ON Department
  • 239. 7.3.5 设计和优化索引首先,在设计索引前必须完成以下任务:了解数据库的基本业务,是以大量增、删、改记录为主要操作的联机事务处理 (OLTP) 数据库,还是数据变化较少、主要用于查询和分析的决策支持系统 (DSS) 或数据仓库 (OLAP) 数据库;了解常用的查询所涉及的表及表中的字段;确定可用于提高查询性能的索引选项;确定索引的存储位置等。 其次,在创建索引后,随着业务的不断应用,要对现有的索引进行检验和评估。SQL Server提供了两个工具:一个是SQL Server Profiler;另一个是数据库引擎优化顾问。
  • 240. 7.4 回到工作场景(1) 创建视图Viewstudentscore,包括学生的学生编号、学生姓名、课程名称和成绩,并使用该视图查询所有学生的学生编号、姓名、课程名称和成绩。 (2)修改视图Viewstudentscore,要求添加学生的班级编号,并使用该视图查询所有学生的姓名、班级编号、课程名称和成绩。 (3)使用视图Viewstudentscore修改学生成绩:学生编号是10701001,课程名称是高等数学,成绩是100。 (4)在学生表的学生编号字段上创建聚集索引PK_Student,在学生表的班级编号和姓名字段上创建非聚集索引IX_Student。
  • 241. 7.5 工作训练营(1) 创建视图Viewclasscourse,查询班级编号、班级名称和课程名称。 (2) 查询班级编号是10701的班级的所有课程名称。 (3) 修改视图Viewclasscourse,查询系别编号、班级编号、班级名称和课程名称。 (4) 删除视图Viewclasscourse。 (5) 在课程表的课程编号字段上创建聚集索引PK_Course,在课程表的课程名称字段上创建非聚集索引IX_Course。
  • 242. 第8章 用户自定义函数 (时间:1次课,2学时)
  • 243. 第8章 用户自定义函数8.1 工作场景导入 8.2 用户自定义函数介绍 8.3 创建用户自定义函数 8.4 使用用户自定义函数 8.5 修改用户自定义函数 8.6 删除用户自定义函数 8.7 回到工作场景 8.8 工作实训营
  • 244. 8.1 工作场景导入 (1)每到学期末,辅导员老师都要邮寄学生的成绩表。成绩表包括以下内容。 该学生本学期所修的课程总数。 列出该学生每门课程的成绩。显示的内容包括学号、姓名、课程号、课程名称和课程成绩。 列出某门课程的最高分、最低分及平均分。显示的内容包括课程号、课程名称、课程最高分、课程最低分和课程平均分。 如今,要求信息管理员小孙创建用户自定义函数来实现以上功能。
  • 245. 8.1 工作场景导入(2)(1)如何创建用户自定义函数? (2)如何使用用户自定义函数? (3)如何修改和删除用户自定义函数?
  • 246. 8.2 用户自定义函数介绍 8.2.1 标量值函数 8.2.2 内联表值函数 8.2.3 多语句表值函数
  • 247. 8.2.1 标量值函数标量值函数的返回值是一个确定类型的标量值即一个单值,该返回值的数据类型为除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE类型外的其他数据类型。 标量用户自定义函数的函数体语句是定义在BEGIN…END语句之内的。
  • 248. 8.2.2 内联表值函数SQL Server中的用户自定义函数并不只是局限于返回标量值,它可以返回一些更复杂的内容——表。根据表的形式不同,分为内联表值函数和多语句表值函数。 内联表值函数以表的形式返回一个返回值,即它返回的是一个表。内联表值函数没有由BEGIN…END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。内联表值函数功能相当于一个参数化的视图。
  • 249. 8.2.3 多语句表值函数多语句表值函数可以看作标量值函数和内联表值函数的结合体。它的返回值是一个表,但它和标量型函数一样有一个用BEGIN…END语句括起来的函数体,返回值的表中的数据是由函数体中的语句插入的。 可以进行多次查询,对数据进行多次筛选与合并,弥补了内联表值函数的不足。
  • 250. 8.3 创建用户自定义函数8.3.1 创建标量值函数 8.3.2 创建内联表值函数 8.3.3 创建多语句表值函数
  • 251. 8.3.1 创建标量值函数(1)创建标量值函数有两种途径:一种是在对象资源管理器中通过菜单创建标量值函数;另一种是在查询编辑器中输入创建标量值函数的T-SQL语句并运行,完成创建标量值函数操作。 可以使用CREATE FUNCTION语句创建标量值函数。
  • 252. 8.3.1 创建标量值函数(2)【实例8.1】在Student数据库中,创建一个标量值函数MaxScoreOfAll,功能是求出所有课程的最高分。 CREATE FUNCTION MaxScoreOfAll() RETURNS INT AS BEGIN DECLARE @maxscore INT SELECT @maxscore=MAX(score) FROM Score RETURN @maxscore END
  • 253. 8.3.1 创建标量值函数(3)【实例8.2】在Student数据库中,创建一个标量值函数MaxCourseScore,功能是求出某门课程的最高分。 CREATE FUNCTION MaxCourseScore (@Courseid char(8)) RETURNS INT AS BEGIN DECLARE @maxcoursescore INT SELECT @maxcoursescore=MAX(score) FROM Score WHERE Courseid=@Courseid RETURN @maxcoursescore END
  • 254. 8.3.2 创建内联表值函数(1)创建内联表值函数有两种途径:一种是在对象资源管理器中通过菜单创建内联表值函数;另一种是在查询编辑器中输入创建内联表值函数的T-SQL语句并运行,完成创建内联表值函数操作。 可以使用CREATE FUNCTION语句创建内联表值函数。
  • 255. 8.3.2 创建内联表值函数(2)【实例8.3】在Student数据库中,创建一个内联表值函数CourseScore,功能是根据课程号列出某门课程的学生成绩,内容包括课程号、学号、课程成绩。 CREATE FUNCTION CourseScore (@Courseid CHAR(8)) RETURNS TABLE AS RETURN ( SELECT Courseid,Studentid,Score FROM Score WHERE Courseid=@Courseid )
  • 256. 8.3.3 创建多语句表值函数(1)创建多语句表值函数有两种途径:一种是在对象资源管理器中通过菜单创建多语句表值函数;另一种是在查询编辑器中输入创建多语句表值函数的T-SQL语句并运行,完成创建多语句表值函数操作。 可以使用CREATE FUNCTION语句创建多语句表值函数。
  • 257. 8.3.3 创建多语句表值函数(2)【实例8.5】在Student数据库中,创建一个多语句表值函数CourseScoreInfo,功能是根据课程号列出某门课程的课程号、课程名称、课程最高分和课程最低分。 CREATE FUNCTION CourseScoreInfo (@Courseid CHAR(8)) RETURNS @CourseScoreInfo TABLE ( Cid CHAR(8),Cname NVARCHAR(30), maxscore INT,minscore INT) AS
  • 258. 8.3.3 创建多语句表值函数(3)BEGIN INSERT INTO @CourseScoreInfo(Cid,Cname,maxscore) SELECT Course.courseid,Coursen ame,max(score) FROM Score INNER JOIN Course ON course.Courseid=Score.Courseid WHERE course.Courseid=@Courseid GROUP BY course.Courseid,course.Coursename UPDATE @CourseScoreInfo SET minscore= (SELECT MIN(score) FROM Score WHERE Courseid=@Courseid ) RETURN END
  • 259. 8.4 使用用户自定义函数8.4.1 使用标量值函数 8.4.2 使用内联表值函数 8.4.3 使用多语句表值函数
  • 260. 8.4.1 使用标量值函数(1)标量值函数的返回值是一个单值。调用标量值函数的方法有两种:在SELECT语句中调用和使用EXEC语句执行。 在SELECT语句中的调用形式: SELECT 函数名(参数1,参数2,……) 使用EXEC语句的执行形式: EXEC 函数名 实参值1,实参值2,…… EXEC 函数名 形参名1=实参值1,形参名2=实参值2,……
  • 261. 8.4.1 使用标量值函数(2)【实例8.6】使用以上两种方法来调用无参的标量值函数MaxScoreOfAll。 SELECT dbo.MaxScoreOfAll() DECLARE @maxscore INT EXEC @maxscore=MaxScoreOfAll SELECT @maxscore as'所有课程最高分'
  • 262. 8.4.1 使用标量值函数(3)【实例8.7】分别使用SELECT语句和EXEC语句来调用带有参数的标量值函数MaxCourseScore。 SELECT dbo.MaxCourseScore('00100001') DECLARE @maxscore INT EXEC @maxscore=MaxCourseScore '00100002' SELECT @maxscore '00100002课程最高分'
  • 263. 8.4.2 使用内联表值函数内联表值函数只能通过SELECT语句调用。 【实例8.8】使用SELECT语句调用内联表值函数CourseScore。 SELECT * FROM CourseScore('00100002')
  • 264. 8.4.3 使用多语句表值函数多语句表值函数只能通过SELECT语句调用。 【实例8.10】使用SELECT语句调用多语句表值函数CourseScoreInfo。 SELECT * FROM CourseScoreInfo('00100002')
  • 265. 8.5 修改用户自定义函数(1)修改用户自定义函数有两种途径:一种是在对象资源管理器中通过菜单修改用户自定义函数;另一种是在查询编辑器中输入修改用户自定义函数的T-SQL语句并运行,完成修改用户自定义函数操作。 可以使用ALTER FUNCTION语句修改用户自定义函数。
  • 266. 8.5 修改用户自定义函数(2)【实例8.11】修改内联表值函数CourseScore,功能是通过课程号和班级号查询出某班级的某门课程的成绩信息。 ALTER FUNCTION [dbo].[CourseScore] (@Courseid CHAR(8),@Classid CHAR(10)) RETURNS TABLE AS RETURN (SELECT Courseid,Classid,student.Studentid,Score FROM Score INNER JOIN Student ON Score.Studentid=Student.Studentid WHERE Courseid=@Courseid AND Classid=@Classid)
  • 267. 8.6 删除用户自定义函数(1)删除用户自定义函数有两种途径:一种是在对象资源管理器中通过菜单删除用户自定义函数;另一种是在查询编辑器中输入删除用户自定义函数的T-SQL语句并运行,完成删除用户自定义函数操作。 可以使用DROP FUNCTION语句删除用户自定义函数。
  • 268. 8.6 删除用户自定义函数(2)【实例8.12】删除用户自定义函数MaxScore。 DROP FUNCTION dbo.MaxScore
  • 269. 8.7 回到工作场景(1)创建用户自定义函数用来统计某学生本学期所修的课程总数。 (2)创建用户自定义函数用来实现查询某学生每门课程的成绩。 (3)创建用户自定义函数用来统计某课程的最高分、最低分和平均分。
  • 270. 8.8 工作训练营(1) 创建函数StdCount用来统计某个班级学生人数,并在查询编辑器中使用该函数。 (2) 创建函数nameSheet用来实现点名册功能,点名册内容包括学号、姓名、性别。创建完成之后在查询编辑器中使用该函数。 (3) 创建函数totalScore实现总成绩单功能,成绩单内容包括学号、姓名、性别和总成绩。创建完成之后在查询编辑器中使用该函数。 (4) 修改函数nameSheet,在点名册中增加年龄一列。 (5) 删除函数StdCount。
  • 271. 第9章 存储过程 (时间:1次课,2学时)
  • 272. 第9章 存储过程9.1 工作场景导入 9.2 存储过程介绍 9.3 不带参数的存储过程 9.4 带参数的存储过程 9.5 回到工作场景 9.6 工作实训营
  • 273. 9.1 工作场景导入 (1)学生在校期间,学校经常需要查询学生这样或那样的信息,现列出几个常用的查询。 查询某系有哪几个班级。 查询某个班级的学生信息,班级的默认值为10701班级。 输入学号,输出该学生所在班级。 通过姓名来查询指定学生所在班级和系部名称。 为了实现这些查询的重用性,建议信息管理员小孙创建存储过程来实现以上功能。
  • 274. 9.1 工作场景导入(2)(1)如何创建存储过程? (2)如何使用存储过程? (3)如何修改和删除存储过程? (4)如何在存储过程中使用输入参数和输出参数?
  • 275. 9.2 存储过程介绍存储过程(Stored Procedure)是SQL Server的数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。 存储过程是将一组实现特定功能的T-SQL语句封装起来,经编译后存放在数据库服务器端,用户通过指定存储过程名称和参数来调用它。 Microsoft SQL Server 2008系统提供了3种类型的存储过程,即系统存储过程、扩展存储过程和用户存储过程。
  • 276. 9.3 不带参数的存储过程9.3.1 创建存储过程 9.3.2 使用存储过程 9.3.3 修改存储过程 9.3.4 删除存储过程
  • 277. 9.3.1 创建存储过程(1)在Microsoft SQL Server 2008系统中,使用CREATE PROCEDURE语句或者CREATE PROC语句创建存储过程。在创建存储过程时,该存储过程的名称在当前数据库实例中的名称必须唯一。 创建存储过程有两种途径:一种是在对象资源管理器中通过菜单创建存储过程;另一种是在查询编辑器中输入创建存储过程的T-SQL语句并运行,完成创建存储过程操作。 可以使用CREATE PROCEDURE语句创建存储过程。
  • 278. 9.3.1 创建存储过程(2)【实例9.1】在Student数据库中,查询10701001号学生的成绩情况。使用存储过程实现,该存储过程不需要使用任何参数,存储过程名称设置为StdScore。 CREATE PROCEDURE StdScore AS BEGIN SELECT * FROM Score WHERE Studentid='10701002' END
  • 279. 9.3.2 使用存储过程(1)存储过程的创建就是为了在程序中调用它。在Microsoft SQL Server 2008系统中,可以用两种方法来执行存储过程。 在对象资源管理器中使用菜单命令执行存储过程。 在查询编辑器中使用EXECUTE语句执行存储过程。
  • 280. 9.3.2 使用存储过程(2)【实例9.4】使用EXECUTE语句执行StdScore存储过程。 EXECUTE StdScore
  • 281. 9.3.3 修改存储过程(1)修改存储过程有两种途径:一种是在对象资源管理器中通过菜单修改存储过程;另一种是在查询编辑器中输入修改存储过程的T-SQL语句并运行,完成修改存储过程操作。 可以使用ALTER PROCEDURE语句修改存储过程。
  • 282. 9.3.3 修改存储过程(2)【实例9.6】修改前面创建的StdScore存储过程,查询的各字段使用别名显示。 ALTER PROCEDURE [dbo].[StdScore] AS BEGIN SELECT Courseid '课程号',Studentid'学号',Score'成绩' FROM Score WHERE Studentid='10701002' END
  • 283. 9.3.4 删除存储过程(1)删除储过程有两种途径:一种是在对象资源管理器中通过菜单删除存储过程;另一种是在查询编辑器中输入删除存储过程的T-SQL语句并运行,完成删除存储过程操作。 可以使用DROP PROCEDURE语句删除存储过程。
  • 284. 9.3.4 删除存储过程(2)【实例9.7】删除存储过程StdScore。 DROP PROCEDURE dbo.StdScore
  • 285. 9.4 带参数的存储过程9.4.1 带输入参数的存储过程 9.4.2 带输出参数的存储过程
  • 286. 9.4.1 带输入参数的存储过程(1)输入参数是当执行存储过程时,需要给存储过程传递参数值。 在CREATE PROCEDURE的语法格式中给出了参数的格式。 [<@Param1_name> [VARYING] [=DefaultValue][OUTPUT ]
  • 287. 9.4.1 带输入参数的存储过程(2)【实例9.8】创建一个带输入参数的存储过程实现查询指定学号的学生成绩。 CREATE PROCEDURE SScore @Studentid CHAR(13) AS BEGIN SELECT * FROM Score WHERE Studentid=@Studentid END
  • 288. 9.4.1 带输入参数的存储过程(3) 调用带输入参数的存储过程也是使用EXEC语句,语法格式如下: EXEC 函数名 实参值1,实参值2,… EXEC 函数名 形参名1=实参值1,形参名2=实参值2,…
  • 289. 9.4.1 带输入参数的存储过程(4)【实例9.10】使用EXEC语句分别执行带有输入参数的两个存储过程SScore和StdScoreByName。 EXEC SScore '10702002' EXEC SScore @studentid='10702002'
  • 290. 9.4.2 带输出参数的存储过程(1) 输出参数使用OUTPUT关键字说明,当存储过程执行结束,将输出参数的值传递给调用程序。 【实例9.11】创建一个存储过程AddProc,其作用是求两个整数之和。要求使用输出参数存储两数之和。 CREATE PROCEDURE AddProc @Param1 INT,@Param2 INT,@Result INT OUTPUT AS BEGIN SET @Result=@Param1+@Param2 END
  • 291. 9.4.2 带输出参数的存储过程(2)在这种情况下,需要使用OUTPUT关键字指定接收运算结果的变量,并且要事先声明该变量。 DECLARE @sum int EXEC AddProc 12,46,@sum OUTPUT PRINT @sum
  • 292. 9.5 回到工作场景(1)创建存储过程用来查询某系有哪几个班级。 (2)创建存储过程用来实现查询某班级的学生信息,学生信息包括学号、学生姓名、班级号、性别、地址、电话号码字段信息。 (3)创建存储过程用来查询某学生所在班级的班级号,其中要求输入参数为学号,输出参数为班级号。 (4)创建存储过程用来查询指定学生所在班级和系部名称。
  • 293. 9.6 工作训练营(1) 创建存储过程getallStd用来查询所有学生信息,并在查询编辑器中调用该存储过程。 (2) 修改存储过程getallStd,用来查询指定系部的学生信息。例如,给出系部为“机电系”,调用该存储过程就可以查询出机电系的学生信息。修改完成之后在查询编辑器中调用该存储过程。 (3) 创建存储过程getDeptname实现根据班级号查询系部名称。要求使用输入参数和输出参数。 (4) 创建存储过程getCourseinfo实现根据学生姓名查询该学生所修课程名称和学分。要求输入参数姓名采用模糊查询形式,并给出默认值'李%'。 (5) 删除存储过程getallStd。
  • 294. 第10章 触发器 (时间:1次课,2学时)
  • 295. 第10章 触发器10.1 工作场景导入 10.2 触发器介绍 10.3 创建触发器 10.4 使用触发器 10.5 修改触发器 10.6 删除触发器 10.7 回到工作场景 10.8 工作实训营
  • 296. 10.1 工作场景导入(1)根据工作需要,学校管理人员需要统计学校各班级的学生人数,现在Class表中加入一个字段StdNum用来存放班级人数信息。但班级人数StdNum的值是一个动态的,比如,学生退学或者转班都会影响StdNum的值。 现希望信息管理员小孙能够实现以下功能。 当某班新来一名学生,即向Student表中增加一条记录,这时能够自动更新Class表中班级人数的值。 不允许用户对Score表进行修改、删除操作。
  • 297. 10.1 工作场景导入(2)(1)触发器的工作原理是什么? (2)如何创建和使用触发器? (3)如何修改和删除触发器? (4)如何使用触发器中的临时表?
  • 298. 10.2 触发器介绍10.2.1 INSERT触发器 10.2.2 DELETE触发器 10.2.3 UPDATE触发器 10.2.4 INSTEAD OF触发器
  • 299. 10.2 触发器介绍触发器是SQL Server数据库应用中的一个重要对象。它是一个被指定关联到一个表的数据对象。 触发器不能被直接调用执行,它只能自动执行。在用户试图对指定的表执行指定的数据操作时自动执行。 在SQL Server中,按照触发事件的不同可以将触发器分为两大类:数据操纵语言(Data Manipulation Language,DML)触发器和数据定义语言(Data Definition Language,DDL)触发器。 根据数据操纵语言事件的不同,可以将DML触发器分为INSERT触发器、UPDATE触发器和DELETE触发器。
  • 300. 10.2.1 INSERT触发器当有人向表中插入新的数据时,INSERT触发器将会被触发执行。 对于新插入的记录来说,SQL Server会创建一个新行的副本并把该副本插入到一个特殊表中,这个特殊表是inserted表,该表只在触发器的作用域内存在。 inserted表只在触发器激活时存在,在触发器触发之前或完成之后这个表都是不存在的。
  • 301. 10.2.2 DELETE触发器DELETE触发器和INSERT触发器的工作方式相同,在有人删除表中数据时,DELETE触发器将会被触发执行。 只是因为进行删除操作而没有插入操作,所以inserted表是空的。 每条删除的记录都会被插入到另一个表中,该表称为deleted表。和inserted表相似,该表只存在于触发器激活的时间内。
  • 302. 10.2.3 UPDATE触发器当对表中现有的数据进行修改时,就会触发UPDATE触发器。UPDATE触发器和前面的触发器很类似,唯一的改变就是没有updated表。 在UPDATE触发器中创建的临时表为inserted表和deleted表。
  • 303. 10.2.4 INSTEAD OF触发器 INSTEAD OF触发器又称为替代触发器,它仅仅起到激活触发器的作用,一旦激活触发器后该语句停止执行,立即转去执行触发器的程序,相当于禁止某种操作。 INSTEAD OF触发器可以在表或视图上创建,每个表或视图只能有一个INSTEAD OF触发器。 INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用INSTEAD OF 触发器。
  • 304. 10.3 创建触发器10.3.1 创建DML触发器 10.3.2 创建DDL触发器
  • 305. 10.3.1 创建DML触发器(1)在Microsoft SQL Server 2008系统中,使用CREATE TRIGGER语句创建DML触发器。在CREATE TRIGGER语句中指定了定义触发器的相关内容。 创建DML触发器的基本语法格式如下: CREATE TRIGGER trigger_name ON table_name|view_name [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} {[INSERT][,][DELETE][,][UPDATE]} AS Sql_statement
  • 306. 10.3.1 创建DML触发器(2)【实例10.1】在Class表中创建一个INSERT触发器,当向Class表中插入一条新的班级信息之后,显示该班级所在系的班级总数。 CREATE TRIGGER class_ai_tri ON Class AFTER INSERT AS BEGIN DECLARE @num INT SELECT @num=COUNT(*) FROM Class WHERE Departid=(SELECT Departid FROM inserted) PRINT '该系班级总数更新为'+char(48+@num)+'个' END
  • 307. 10.3.1 创建DML触发器(3)【实例10.2】创建一个触发器,当在Student表中删除一个学生的信息,同时将该学生的成绩删除。 CREATE TRIGGER student_ad_tri ON Student AFTER DELETE AS BEGIN DELETE FROM Score WHERE Studentid=(SELECT Studentid FROM deleted) END
  • 308. 10.3.1 创建DML触发器(4)【实例10.3】创建触发器,功能是在Student表中修改某个学生的学号,同时将该学号更新到成绩表Score中。 CREATE TRIGGER student_au_tri ON Student AFTER UPDATE AS BEGIN DECLARE @oldstdid CHAR(13),@newstdid CHAR(13) SELECT @oldstdid=studentid FROM deleted SELECT @newstdid=studentid FROM inserted UPDATE Score SET Studentid=@newstdid WHERE Studentid=@oldstdid END
  • 309. 10.3.1 创建DML触发器(5)【实例10.4】创建INSTEAD OF触发器,不允许删除Department表中数据。 CREATE TRIGGER Dep_instead_delete ON Department INSTEAD OF DELETE AS BEGIN PRINT '不允许对Department表执行DELETE操作' END
  • 310. 10.3.2 创建DDL触发器DDL触发器与DML触发器有许多类似的地方,如可以自动触发完成规定的操作、都可以使用CREATE TRIGGER语句创建等。 创建DDL触发器的基本语法格式如下: CREATE TRIGGER trigger_name ON{ALL SERVER|DATABASE} [WITH ENCRYPTION] {FOR|AFTER}{event_type} AS Sql_statement
  • 311. 10.4 使用触发器(1)调用触发器不需要像调用存储过程那样进行显式调用,触发器是当所属表执行触发事件的时候自动触发的。
  • 312. 10.4 使用触发器(2)【实例10.6】使用10.3节中创建的class_ai_tri触发器。 在查询编辑器中运行INSERT语句: INSERT INTO Class VALUES('20803','机电200803','机电一体化','2') 执行结果如下: 消息 该系班级总数更新为5个 (1行受影响)
  • 313. 10.4 使用触发器(3)【实例10.7】使用10.3节中创建的student_ad_tri触发器。 在查询编辑器中运行DELETE语句: DELETE FROM Student WHERE Studentid=' 20702003' 执行结果: 在Course表中将学号为20702003的学生成绩记录全部删除。
  • 314. 10.4 使用触发器(4)【实例10.8】使用10.3节中创建的student_au_tri触发器。 在查询编辑器中运行UPDATE语句: UPDATE Student SET Studentid=' 10701009' WHERE Studentid=' 10701001' 执行结果: 在Course表中将原学号为10701001的学生学号更改为10701009。
  • 315. 10.4 使用触发器(5)【实例10.9】使用10.3节中创建的DDL触发器Std_trigger。 在查询编辑器中运行DROP TABLE语句: DROP TABLE Department 执行结果如下: 不能删除表 消息3609,级别16,状态2,第1行 事务在触发器中结束。批处理已中止。
  • 316. 10.5 修改触发器(1)修改触发器有两种途径:一种是在对象资源管理器中通过菜单修改触发器;另一种是在查询编辑器中输入修改触发器的T-SQL语句并运行,完成修改触发器操作。 可以使用ALTER TRIGGER语句修改用户自定义函数。
  • 317. 10.5 修改触发器(2)【实例10.10】修改Student数据库中在Class表上定义的触发器class_ai_tri,将其输出的信息稍做修改。 ALTER TRIGGER class_ai_tri ON Class AFTER INSERT AS BEGIN DECLARE @num INT SELECT @num=COUNT(*) FROM Class WHERE Departid=(SELECT Departid FROM inserted) PRINT '班级插入成功,该系班级总数更新为'+char(48+@num)+'个' END
  • 318. 10.6 删除触发器(1)删除触发器有两种途径:一种是在对象资源管理器中通过菜单删除触发器;另一种是在查询编辑器中输入删除触发器的T-SQL语句并运行,完成删除触发器操作。 可以使用DROP TRIGGER语句删除触发器。
  • 319. 10.6 删除触发器(2)【实例10.11】删除存储过程Std_trigger。 DROP TRIGGER dbo. Std_trigger
  • 320. 10.7 回到工作场景(1)当某班新来一名学生,即向Student表中增加一条记录,这时能够自动更新Class表中班级人数的值。 (2)当某班有学生退学,即在Student表中删除一条记录,这时能够自动更新Class表中班级人数的值。 (3)当有学生转班级,即在Student表中将某个学生的班级号进行修改,这时能够自动更新Class表中所涉及班级的人数信息。 (4)现要求用户不允许对Score表中信息进行修改、删除操作。
  • 321. 10.8 工作实训营(1)(1) 在Department表中增加一个字段DepNum,用来统计系部的班级个数。字段的数据类型为int,字段的值为班级个数。 (2) 创建触发器,在Class表中增加一个班级时,更新Department表中的DepNum字段值。 (3) 创建替代触发器,不允许对Course表进行修改操作。 (4) 创建触发器,在第7章创建的视图Viewclass中修改Classid字段值。 (5) 创建DDL触发器,禁止修改Course表的结构。
  • 322. 10.8 工作实训营(2)(6) 修改(5)中创建的触发器,不但禁止修改Course表的结构,也不允许删除Course表。
  • 323. 第11章 管理数据库安全 (时间:2次课,4学时)
  • 324. 第11章 管理数据库安全11.1 工作场景导入 11.2 SQL Server2008安全机制 11.3 SQL Server2008验证模式 11.4 登录 11.5 数据库用户 11.6 权限 11.7 角色 11.8 回到工作场景 11.9 工作实训营
  • 325. 11.1 工作场景导入(1)为了保证数据库中信息的安全,需要信息管理员小孙对学生成绩数据库进行数据库安全性的设置。具体要求如下: 本数据库中需要有3种用户:管理员、教师、学生。这3种用户拥有操作数据库的不同权限。 管理员拥有学生成绩数据库的读写权限。 教师拥有成绩表的读写权限。 学生只能读取成绩表的信息,而不能修改其中信息。
  • 326. 11.1 工作场景导入(2)(1)什么是数据库安全机制? (2)如何创建数据库用户? (3)什么是用户权限? (4)如何设置用户权限?
  • 327. 11.2 SQL Server2008安全机制SQL Server安全性主要是指允许那些具有相应的数据访问权限的用户能够登录到SQL Server 并访问数据以及对数据库对象实施各种权限范围内的操作,同时要拒绝所有的非授权用户的非法操作。 Microsoft SQL Server 2008系统提供了一整套保护数据安全的机制,包括登录、用户、权限、角色等手段,可以有效地实现对系统访问和数据访问的控制。
  • 328. 11.3 SQL Server2008验证模式11.3.1 Windows身份验证 11.3.2 混合身份验证
  • 329. 11.3.1 Windows身份验证Windows身份验证模式是指要登录到SQL Server系统的用户身份是由Windows系统来进行验证,也就是说SQL Server系统使用Windows操作系统中的用户信息验证账号和密码。 采用这种验证方式,只要登录Windows操作系统,登录SQL Server时就不需要再输入账号和密码了。 SQL Server 2008默认本地Windows账号可以不受限制地访问数据库。
  • 330. 11.3.2 混合身份验证(1)混合身份验证是Windows身份验证和SQL Server身份验证的混合验证模式。 采用混合验证方式登录SQL Server,允许用户使用Windows身份验证和SQL Server身份验证进行登录。SQL Server身份验证模式是输入登录名和密码来登录数据库服务器。这些登录名和密码与Windows操作系统无关。 在混合身份验证模式中,系统会判断账号在Windows操作系统下是否可信,对于可信任连接,系统直接采用Windows身份验证机制,如果是非可信任连接,SQL Server 会自动通过账户的存在性和密码的匹配性来进行验证。
  • 331. 11.3.2 混合身份验证(2)第一次安装SQL Server2008时需要指定身份验证模式,对于已经指定身份验证模式的SQL Server服务器,可以通过SQL Server Management Studio进行修改。
  • 332. 11.4.1 创建Windows登录 11.4.2 创建SQL Server登录 11.4.3 管理登录名11.4 登录
  • 333. 11.4 登录SQL Server服务器的身份验证模式设置完成后,需要创建登录名来控制数据库的合法登录。 创建登录名有两种途径:一种是在对象资源管理器中通过菜单创建登录名;另一种是在查询编辑器中输入创建登录名的T-SQL语句并运行,完成创建登录名操作。 可以使用CREATE LOGIN语句创建登录名。
  • 334. 11.4.1 创建Windows登录【实例11.1】创建Windows登录Mary,默认数据库是 master,默认语言是简体中文。 CREATE LOGIN [SP100\Mary] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[简体中文]
  • 335. 11.4.2 创建SQL Server登录(1) 在创建SQL Server登录名前必须将SQL Server的验证模式设置为混合验证模式,并在创建SQL Server登录名时,需要指定该登录名的密码。
  • 336. 11.4.2 创建SQL Server登录(2)【实例11.2】创建一个SQL Server登录名TESTLogin,密码为123,默认数据库是master,默认语言是简体中文,不对此登录账户强制实施密码过期策略和本计算机的 Windows 密码策略。 CREATE LOGIN [TESTLogin1] WITH PASSWORD='123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[简体中文], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  • 337. 11.4.3 管理登录名(1)修改登录名有两种途径:一种是在对象资源管理器中通过菜单修改登录名;另一种是在查询编辑器中输入修改登录名的T-SQL语句并运行,完成修改登录名操作。 可以使用ALTER LOGIN语句修改登录名。
  • 338. 11.4.3 管理登录名(2)【实例11.3】将登录名TESTLogin的密码修改为001122。 ALTER LOGIN [TESTLogin] WITH PASSWORD='001122'
  • 339. 11.4.3 管理登录名(4)删除登录名有两种途径:一种是在对象资源管理器中通过菜单删除登录名;另一种是在查询编辑器中输入删除登录名的T-SQL语句并运行,完成删除登录名操作。 可以使用DROP LOGIN语句删除登录名。
  • 340. 11.4.3 管理登录名(5)【实例11.4】删除登录名SP100\Mary。 DROP LOGIN [SP100\Mary]
  • 341. 11.5.1 创建数据库用户 11.5.2 管理数据库用户11.5 数据库用户
  • 342. 11.5 数据库用户数据库用户是使用数据库的用户账号,是登录名在数据库中的映射,是在数据库中执行操作和活动的执行者。 用户定义信息存放在每个数据库的sysusers表中。 SQL Server把登录名与用户名的关系称为映射。在SQL Server中,一个登录名可以被授权访问多个数据库,但一个登录名在每个数据库中只能映射一次。
  • 343. 11.5.1 创建数据库用户(1)创建数据库用户有两种途径:一种是在对象资源管理器中利用菜单操作;另一种是在查询编辑器中执行创建数据库用户的T-SQL语句。 可以使用CREATE USER语句创建数据库用户。
  • 344. 11.5.1 创建数据库用户(2)【实例11.6】创建Student数据库的一个用户名User2,该用户名对应于TESTLogin1 登录名。 USE [Student] CREATE USER [User2] FOR LOGIN [TESTLogin1]
  • 345. 11.5.2 管理数据库用户(1)管理数据库用户有两种途径:一种是在对象资源管理器中利用菜单操作;另一种是在查询编辑器中执行修改数据库用户的T-SQL语句。 可以使用ALTER USER语句修改数据库用户。
  • 346. 11.5.2 管理数据库用户(2)【实例11.6】修改Student数据库的一个用户名User2,该用户名对应于TESTLogin2 登录名。 USE [Student] ALTER USER [User2] FOR LOGIN [TESTLogin2]
  • 347. 11.6.1 权限类型 11.6.2 设置用户权限11.6 权限
  • 348. 11.6.1 权限类型在Microsoft SQL Server 2008系统中,按照权限是否与特定的对象有关,可以把权限分为针对所有对象的权限和针对特殊对象的权限。 针对所有对象的权限有CONTROL、ALTER、ALTER ANY、CREATE及TAKE OWNERSHIP等。 针对特殊对象的权限有SELECT、UPDATE、INSERT、DELETE及EXECUTE等。
  • 349. 11.6.2 设置用户权限(1)GRANT语句用来给用户授予权限。 在Microsoft SQL Server 2008系统中,对用户账号授予权限有两种途径:一种是在对象资源管理器中利用菜单操作;另一种是在查询编辑器中执行GRANT语句。
  • 350. 11.6.2 设置用户权限(2)【实例11.9】为Student数据库的用户账号User1授予SELECT权限。 GRANT SELECT ON Student TO User1
  • 351. 11.6.2 设置用户权限(3)DENY语句用来拒绝权限。 在Microsoft SQL Server 2008系统中,对用户账号拒绝权限有两种途径:一种是在对象资源管理器中利用菜单操作;另一种是在查询编辑器中执行DENY语句。
  • 352. 11.6.2 设置用户权限(4)【实例11.10】不允许User1用户对Student表执行UPDATE、INSERT、DELETE操作。 DENY UPDATE,INSERT,DELETE ON Student TO User1
  • 353. 11.6.2 设置用户权限(5)REVOKE语句用来撤消权限。 在Microsoft SQL Server 2008系统中,对用户账号撤销权限有两种途径:一种是在对象资源管理器中利用菜单操作;另一种是在查询编辑器中执行REVOKE语句。
  • 354. 11.6.2 设置用户权限(6)【实例11.11】为Student数据库的用户账号User1撤销SELECT权限。 REVOKE SELECT ON Student FROM User1
  • 355. 11.7 角色11.7.1 角色分类 11.7.2 创建角色 11.7.3 指派角色
  • 356. 11.7.1 角色分类在SQL Server中主要有两种角色类型,即服务器级角色和数据库级角色。 服务器级角色独立于各个数据库,它是由系统预定义的,用户不能创建新的服务器级角色,只能选择合适的服务器级角色。 数据库级角色定义在数据库级别上,是指对数据库执行特有的管理及操作。在SQL Server中有两种数据库级角色:固定数据库级角色和自定义数据库级角色。
  • 357. 11.7.2 创建角色(1)在Microsoft SQL Server 2008系统中,创建角色有两种途径:一种是在对象资源管理器中利用菜单操作;另一种是在查询编辑器中执行创建角色的T-SQL语句。 CREATE ROLE语句用来创建角色。
  • 358. 11.7.2 创建角色(2)【实例11.12】在Student数据库中创建一个数据库角色Role1。 CREATE ROLE Role1 AUTHORIZATION User1
  • 359. 11.7.3 指派角色(1)如果向自定义的数据库级角色中添加成员,可以使用sp_addrolemember存储过程。利用该存储过程可以将一个数据库用户添加到一个数据库级角色中,使其成为该数据库级角色的成员。
  • 360. 11.7.3 指派角色(2)【实例11.13】向数据库级角色Role1中添加成员guest。 sp_addrolemember 'Role1', 'guest'
  • 361. 11.8 回到工作场景(1)创建一个管理员用户,管理员名称为adminTest,密码为123,默认数据库是Student。权限是可以对Student数据库执行所有的操作。 (2)创建教师用户,用户名为Teacher,密码为123,默认数据库是Student。权限是只能对Score表执行所有的操作。 (3)创建学生用户,用户名为Std,密码为123,默认数据库是Student。权限是只能对Score表执行查询操作。
  • 362. 11.9 工作实训营在StudentScore数据库中创建用户账号和角色,并授予操作权限,具体步骤如下。 (1) 在SQL Server Management Studio中创建登录名Login1,密码为123。 (2) 将dbcreator角色的权限分配给Login1。 (3) 为Student数据库创建一个用户账号Db1,密码为123。 (4) 为用户账号Db1设置一些权限。 (5) 为Student数据库创建一个数据库角色R1。 (6) 删除登录名Login1。 (7) 删除用户账号Db1。
  • 363. 第12章 备份和还原数据库 (时间:1次课,2学时)
  • 364. 第12章 备份和还原数据库12.1 工作场景导入 12.2 备份和还原 12.3 完整数据库备份 12.4 数据库和事务日志备份 12.5 差异备份 12.6 文件或文件组备份 12.7 回到工作场景 12.8 工作实训营
  • 365. 12.1 工作场景导入(1)学校要更换服务器,希望将学生成绩数据库从原服务器转移到新服务器上,这时信息管理员小孙要把数据库进行备份,转移到新服务器上,然后进行数据库还原操作。具体要求如下: 将学生成绩数据库进行完整数据库备份。 将Student数据库备份进行还原。
  • 366. 12.1 工作场景导入(2)(1)什么是数据库的备份和还原? (2)数据库备份的类型是什么? (3)如何进行数据库备份? (4)如何进行数据库还原?
  • 367. 12.2 备份和还原12.2.1 备份 12.2.2 还原
  • 368. 12.2.1 备份(1)数据库备份就是制作数据库中数据库结构和数据的复制,将其存放在安全、可靠的位置,以便以后能够顺利地将被破坏了的数据库安全地还原。 在数据库备份过程中涉及备份设备、备份类型的内容。 备份设备:在进行备份之前必须先创建备份设备。备份设备是指将数据库备份到的目标载体,即备份到何处。在SQL Server 2008中,允许使用两种类型的备份设备,即磁盘备份设备和磁带备份设备。
  • 369. 12.2.1 备份(2)SQL Server 2008系统中提供了以下4种备份类型。 完整数据库备份 差异数据库备份 事务日志备份 文件和文件组备份
  • 370. 12.2.2 还原数据库还原操作就是当数据库出现故障时,将备份的数据库加载到系统,从而使数据库恢复到备份时的正确状态。还原是为了实现备份的目的而进行的操作。 针对不同的数据库备份类型,可以采取不同的数据库还原方法。在SQL Server 2008系统中,提供了以下3种数据库的还原方法。 数据库:还原和恢复整个数据库。 文件和文件组:还原和恢复一个数据文件或者一组数据文件。 事务日志:还原和恢复事务日志。
  • 371. 12.3.1 创建备份 12.3.2 还原备份12.3 完整数据库备份
  • 372. 12.3.1 创建备份(1)在创建任何类型的数据库备份之前,都要创建备份设备。创建备份设备有两种方法:一是在对象资源管理器中使用菜单命令创建备份设备;二是使用存储过程创建备份设备。 存储过程sp_addumpdevice用来创建备份设备。
  • 373. 12.3.1 创建备份(2)【实例12.1】在磁盘上创建一个备份设备test_device。 EXEC sp_addumpdevice 'disk','test_device','d:\backup\test_device.bak'
  • 374. 12.3.1 创建备份(3)创建完整数据库备份有两种方法:一种是使用SQL Server Management Studio工具备份数据库;另一种是使用BACKUP命令来备份数据库。 BACKUP DATABASE语句用于创建数据库备份。
  • 375. 12.3.1 创建备份(4)【实例12.2】采用完整数据库备份模式,将Student数据库备份。 BACKUP DATABASE Student to 学生成绩数据库备份
  • 376. 12.3.2 还原备份(1)对于完整数据库备份的还原操作有两种方法:一种是使用SQL Server Management Studio工具还原数据库;另一种是使用T-SQL语句来还原数据库。 RESTORE DATABASE语句用于还原数据库备份。
  • 377. 12.3.2 还原备份(2)【实例12.3】将实例12.2中创建的Student数据库备份进行还原。 RESTORE DATABASE Student FROM 学生成绩数据库备份 WITH REPLACE
  • 378. 12.4 数据库和事务日志备份12.4.1 创建备份 12.4.2 还原备份
  • 379. 12.4.1 创建备份(1)创建数据库日志备份有两种方法:一种是使用SQL Server Management Studio工具备份数据库;另一种是使用BACKUP命令来备份数据库。 BACKUP LOG语句用于创建数据库日志备份。
  • 380. 12.4.1 创建备份(2)【实例12.4】采用数据库和事务日志备份模式,将Student数据库备份。 BACKUP LOG Student to Student事务日志备份
  • 381. 12.4.2 还原备份(1)对于数据库日志备份的还原操作有两种方法:一种是使用SQL Server Management Studio工具还原数据库日志;另一种是使用T-SQL语句来还原数据库日志。 RESTORE LOG语句用于还原数据库日志备份。
  • 382. 12.4.2 还原备份(2)【实例12.5】将实例12.4创建的Student数据库事务日志备份进行还原。 RESTORE LOG Student FROM Student事务日志备份
  • 383. 12.5 差异备份12.5.1 创建备份 12.5.2 还原备份
  • 384. 12.5.1 创建备份(1)创建数据库差异备份有两种方法:一种是使用SQL Server Management Studio工具备份数据库;另一种是使用BACKUP命令来备份数据库。 BACKUP DATABASE语句用于创建数据库差异备份。
  • 385. 12.5.1 创建备份(2)【实例12.6】采用差异备份模式将Student数据库进行备份。 BACKUP DATABASE Student TO Student差异备份 WITH DIFFERENTIAL
  • 386. 12.5.2 还原备份差异备份的两种还原方法与完整数据库备份的还原操作类似。
  • 387. 12.6.1 创建备份 12.6.2 还原备份12.6 文件或文件组备份
  • 388. 12.6.1 创建备份(1)【实例12.7】采用文件或文件组备份的模式,将Student数据库备份。Student数据库有两个数据文件StudentScore和StudentScore_Data及事务日志文件StudentScore_log。将文件StudentScore备份到备份设备s1backup中,将事务日志文件备份到slogbackup中。
  • 389. 12.6.1 创建备份(2)EXEC sp_addumpdevice'disk','s1backup','d:\backup\s1backup.bak' EXEC sp_addumpdevice'disk','slogbackup','d:\backup\slogbackup.bak' GO BACKUP DATABASE Student FILE='StudentScore' TO s1backup BACKUP LOG Student TO slogbackup
  • 390. 12.6.2 还原备份【实例12.8】将实例12.7创建的Student数据库事务日志备份进行还原。 RESTORE DATABASE Student FILE='StudentScore' FROM s1backup
  • 391. 12.7 回到工作场景将学生成绩数据库Student进行完整数据库备份。 还原Student数据库。
  • 392. 12.8 工作实训营对StudentScore数据库进行备份与还原操作。 (1) 采用完整数据库备份方法,对StudentScore数据库进行备份。 (2) 将Score表中,学号为107010001的成绩删除。 (3) 还原StudentScore数据库,查看Score表中学号为107010001的成绩是否存在。 (4) 使用差异备份方法,重复以上3步操作。
  • 393. 第13章 导入和导出数据库中的数据(时间:1次课,2学时)
  • 394. 第13章 导入和导出数据库中的数据13.1 工作场景导入 13.2 SSIS 13.3 使用SSIS 13.4 回到工作场景 13.5 工作实训营
  • 395. 13.1 工作场景导入(1)根据需要现希望信息管理员小孙能够对数据库中的数据进行导入和导出操作。 导出:将10701班学生的学号、姓名、性别导出到D:\Std10701.csv文件中。 导入:将已经导出的Std10701.csv文件数据导入到新建数据库Std中。
  • 396. 13.1 工作场景导入(2)(1)什么是SSIS? (2)如何使用SSIS工具? (3)如何创建SSIS包? (4)如何执行SSIS包?
  • 397. 13.2 SSIS13.2.1 SSIS介绍 13.2.2 SSIS工作方式
  • 398. 13.2.1 SSIS介绍SSIS是从Microsoft SQL Server 2005开始引入的,是SQL Server Integration Services的简称,是生成高性能数据集成解决方案(包括数据仓库的提取、转换和加载(ETL)包)的平台。 SSIS包括:用于生成和调试包的图形工具和向导;用于执行工作流函数(如 FTP 操作)、执行 SQL 语句或发送电子邮件的任务;用于提取和加载数据的数据源和目标;用于清理、聚合、合并和复制数据的转换;用于管理 Integration Services 的管理服务 Integration Services 服务;以及用于对 Integration Services 对象模型编程的应用程序编程接口 (API)。
  • 399. 13.2.2 SSIS工作方式(1)Microsoft SQL Server 2008 Integration Services (SSIS) 包括一组向导,可指导你逐步完成在数据源之间复制数据、构造简单包、创建包配置、部署 Integration Services 项目和迁移 SQL Server DTS 包的步骤。 SSIS程序包实质上就是程序。该程序包中存储了一套指令,以便进行移动、赋值、编辑等处理。数据导入导出向导是能够自动建立这样的程序包的工具,为的是可以实现简单的导入或导出操作。
  • 400. 13.2.2 SSIS工作方式(2)可以导入或导出的数据源有以下几种:SQL Server、平面文件、Microsoft Access、Microsoft Excel、其他OLE DB访问接口。 使用SSIS包进行导入或导出操作包括以下两个过程:创建SSIS包,执行SSIS包。
  • 401. 13.3.1 创建SSIS 13.3.2 执行SSIS13.3 使用SSIS
  • 402. 13.3.1 创建SSIS(1)【实例13.1】将Student数据库中的Student表中数据导出至Student.csv,并生成Package1.dtsx。
  • 403. 13.3.1 创建SSIS(2)【实例13.2】将Score表中10701班的学生成绩数据导出至Score10701.csv。 SELECT student.studentid,student.studentname,score.courseid, score.score FROM score INNER JOIN student ON student.studentid=score.studentid WHERE classid='10701'
  • 404. 13.3.1 创建SSIS(3)【实例13.3】将实例13.2中导出的10701班学生成绩数据导入到新建数据库Score中,文件源为Score10701.csv,并生成Package3.dtsx。
  • 405. 13.3.2 执行SSIS包(1)SSIS创建之后,并没有向目标文件导入数据,只有在执行SSIS包之后,才会根据SSIS包的设置信息将数据导入到目标文件中。 执行SSIS包的方法就像是在调试Visual Studio项目,只要按一下F5键,单击工具栏上的绿色箭头,或者在【解决方案资源管理器】中右击将要执行的SSIS包,在弹出的快捷菜单中选择【执行包】命令。在构建时,会看到数据流任务先是黄色,然后变成绿色,绿色表明执行成功。
  • 406. 13.3.2 执行SSIS包(2)【实例13.4】执行已创建的Package1.dtsx、Package2.dtsx和Package3.dtsx 3个SSIS包。
  • 407. 13.4 回到工作场景(1)将学生信息导出到Std10701.csv文件。 (2)将Std10701.csv文件中的数据导入到新建Std数据库中。
  • 408. 13.5 工作实训营对Student数据库进行导入和导出操作。 (1) 将学号为10701001的学生成绩导出到文件studentScore.csv,要求包含学生学号、姓名、课程名、成绩。 (2) 将高等数学课程的成绩导出到mathScore.csv,要求包含课程号、课程名、学生姓名和成绩。 (3) 将mathScore.csv文件数据导入到新建数据库MathScore中。
  • 409. 第14章 学生管理系统案例(时间:1次课,2学时)
  • 410. 第14章 学生管理系统案例14.1 工作场景导入 14.2 程序设计介绍 14.3 回到工作场景 14.4 工作实训营
  • 411. 14.1 工作场景导入(1)本工作场景中所设计的学生管理系统包含两个版本: 基于Windows的学生管理系统中包含以下功能。 ① 管理员登录功能。 ② 管理员能够添加学生信息。 基于Web的学生管理系统中包含以下功能。 ① 用户登录功能。 ② 用户注册功能。 ③ 用户能够查询学生信息。
  • 412. 14.1 工作场景导入(2)(1)根据需求,系统要实现哪些功能模块? (2)如何创建Windows应用程序? (3)如何创建Web应用程序? (4)如何设计后台数据库? (5)如何连接数据库? (6)登录功能如何实现? (7)注册功能如何实现? (8)添加学生功能如何实现? (9)查询学生功能如何实现?
  • 413. 14.2 程序设计介绍14.2.1 Microsoft Visual Studio2008集成环境 14.2.2 C#语言 14.2.3 ASP.NET
  • 414. 14.2.1 Microsoft Visual Studio集成环境Visual Studio 是一套完整的开发工具集,用于生成 ASP.NET Web应用程序、XML Web Services、桌面应用程序和移动应用程序。 Visual Studio 2008的新特性如下。 (1) 软件开发更智能。 (2) WCF的新增功能。 (3) 多定向支持。 (4) 微软整合Windows Live到Visual Studio中。 (5) Visual Studio 2008中的SQL数据库发布。 (6) Visual Studio 2008加入拼写检查器。
  • 415. 14.2.2 C#语言C#是微软公司在2000年6月发布的一种新的编程语言,是微软为了推行.NET战略,特别为.NET平台设计的一种新语言。 C#是由C和C++发展而来的一种简单、高效、面向对象、类型安全的程序设计语言。C#既提供Visual Basic的易用性,又提供Java和C++语言的灵活性及强大功能。
  • 416. 14.2.3 ASP.NET(1)ASP.NET 是 ASP(微软动态服务器网页技术)的最新版本,它是 Microsoft.NET 框架的组成部分,同时也是创建动态交互网页的强有力的工具。
  • 417. 14.2.3 ASP.NET(2)ASP.NET有以下几个新特性。 (1) 更好的语言支持。 (2) 可编程的控件。 (3) 事件驱动的编程。 (4) 基于 XML 的组件。 (5) 用户身份验证,带有账号和角色。 (6) 更强的性能。 (7) 更容易配置和开发。
  • 418. 14.3 回到工作场景14.3.1 基于Windows的学生管理系统 14.3.2 基于Web的学生管理系统
  • 419. 14.3.1 基于Windows的学生管理系统(1) 分析各个功能模块 (1) 用户登录功能模块。 功能:对用户名和密码进行验证,验证通过之后登录系统。 (2) 用户注册功能模块。 功能:用户注册功能。 (3) 查询学生信息模块。 功能:可以根据两种途径来查询学生信息:根据班级号查询、根据学生姓名查询。
  • 420. 14.3.1 基于Windows的学生管理系统(2)开发过程简介 (1)数据库设计。 (2)连接数据库。 (3)系统登录界面(Login.cs)。 (4)系统主界面(mainForm.cs)。 (5)添加学生信息界面(AddStd.cs)。 (6)调试运行。
  • 421. 14.3.2 基于Web的学生管理系统(1)开发过程简介 (1)数据库设计。 (2)连接数据库。 (3)系统登录界面(Default.aspx)。 (4)用户注册界面(Register.aspx)。 (5)查询学生信息界面(stdBrowse.aspx)。 (6)调试运行。
  • 422. 14.4 工作实训营(1)设计开发一个功能较为全面的图书管理系统,该系统分为后台管理员系统和前台图书借阅网站。后台管理员系统是Windows应用程序,前台图书借阅系统是Web应用程序。 后台管理员系统应具备以下功能。 (1)图书信息维护。主要完成图书馆新进图书的编号、登记、入馆等操作。 (2)读者信息维护。主要完成读者信息的添加、修改和删除等操作,只有是系统中的合法读者才有资格进行图书的借阅活动。
  • 423. 14.4 工作实训营(2) 前台图书借阅网站应具备以下功能。 (1) 借书/还书处理。主要完成读者的借书和还书活动,记录读者借、还书情况并及时反映图书的在库情况。 (2) 读者借阅记录。让每位读者能及时了解自己的借书情况,包括曾经借阅记录及未还书记录。 (3) 图书书目检索。读者能够根据不同的信息对图书馆的存书情况进行查找。 (4) 图书超期通知。可以统计出到目前为止超期未归还的图书及相应的读者信息。