数据库技术：《数据库技术(SQL Server 2000)》电子教案（叶钰）

泰州职业技术学院电子工程系 课程授课教案 (上) 课程名称 数据库技术（SQL Server 2000）  主写教师 叶 钰  授课班级 04计算机单（1）   04计算机单（2）  使用教材 《数据库应用技术》--SQL Server篇 人民邮电出版社，徐守祥编著  课程学分 4 周学时 理论 1     实践 3    授课题目（章、节） 第一讲 SQL Server 2000关系数据库系统  学时 3 授课时间 周4第6～8节 第( 1 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 数据库的基础知识； 能够利用Access建立数据库并进行基本操作； 了解SQL Server2000系统的概况； 能够正确安装SQL Server2000。  教学重点、难点： 1、主键、外键的特点、作用及相互关系； 2、安装SQL Server2000。  大体内容 教学方法 时间安排  关系型数据库的基本概念 面授   ACCESS数据库概述 面授   SQL Server2000系统概述 面授   SQL Server 2000的安装 面授、演示                   教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   一、关系型数据库的基本概念 1．关系型数据库是指一些相关的表和其它数据库对象的集合，包含3方面内容： 凡是关系型数据库，所有的信息（数据）都存放在二维表格（表，Table）中，一个数据库中可以有多个表，每个表由若干行和若干列组成，其中，行称为记录，列称为字段，表称为关系。 在同一数据库中，表与表之间是相互关联的。这种关联性通过主键（Primary Key）和外键(Foreign Key)实现。 数据库不仅包含表，还包含其它对象，如视图、存储过程、索引等。 2．主键（PK） 当二维表中的一列或多列的组合能够唯一地表示表中的不同记录时，称它们为键（码）。如果表中存在多个键，则可以指定其中一个作为主键。 导师表 导师编号 姓名 性别 年龄 职称 所在系  1003 张浩 男 52 教授 计算机  1019 王影 女 49 副教授 外语  2104 李解放 男 58 教授 机械  2163 申明明 男 48 副教授 外贸  研究生表 学号 姓名 性别 研究方向 导师编号  200102001 肖小波 男 经济法 2163  200105006 李立 男 软件工程 1003  200203020 张宁宁 女 外国文学   200209300 白小燕 女 ACAD 2104  主键的作用是为了实施实体（如表）的完整性。实体的完整性要求每个表有且仅有一个主键、主键的值必须唯一、且不能为空。 3．外键 如果一个表中的某列或多列组合是另一表的主键，则该列或多列的组合称为外键。 在关系型数据库中，表与表之间的联系是通过公共属性实现的，即表的主键和其它表的外键。引入外键的作用为： 实现了表与表间的联系。如“导师编号”建立了2表之间的联系。 外键的取值必须是另一表主键的有效值，或空值，否则就是非法数据。 此时，添加、更新、删除均应遵从完整性规则。如：当表的主键的一个值被使用时（另一表中具有外键相同值的记录），该记录就不能被删除，即不能删除导师编号为2104的导师。 4．关系型数据库的主要特点 都采用SQL语言来操纵数据库中的数据。SQL 是20世纪70年代由IBM发展起来的，并成了工业标准。SQL Server 2000与ANSI-92 SQL完全兼容，并对其进行了扩展（称为事务型SQL（Transact-SQL））。 能够保证数据的完整性。 关系型数据库系统是一个便于修改的系统。 关系型数据库能够排除多数的冗余数据，减少了对存储空间的要求，加快了数据访问速度。 应用程序可以通过多级的安全检查来限制对数据的访问。 二、ACCESS数据库概述 数据库管理系统有很多种，如：Foxpro,Dbase,Oracle,SQL Server,Foxbase,Access等。Access是微软公司开发的MS Office办公软件中的一个组件，用于创建、管理和维护Access类型的数据库，并且可以对数据库进行查询、制作用于输入数据的窗体、输出数据的报表等，可以通过VBA设计的程序对数据库进行各种控制。 在学习SQL SERVER2000之前，必须先了解一种数据库软件，有一个直观感觉。 常见的数据库一般为关系型的数据库。一个数据库中可以有多个表，如学生管理数据库中，分别存在学生信息表、课程表、任课教师表、成绩表等，除上述几个表外，数据库中还可以含有窗体、报表、查询等附加内容。 一个Access类型的数据库就是由一个文件构成的，其扩展名为mdb。文件名就是该数据库的名称，数据库中每一个表也必须有一个名称。 表中同一字段的数据由相同类型的值构成，代表相同的意义，如姓名字段，类型为文本（或字符串）。 例：利用Access建立一个数据库，并在数据库中创建表。 （1）包括学生表（姓名、学号、性别、出生日期），课程表（课程名、课程号、学分），成绩表（学号、课程号、成绩）。 （2）在数据库中创建窗体，用于输入数据。 （3）在数据库中创建查询，用于查找各种条件的记录。 三、SQL Server2000系统概述 概述 SQL Server是一款面向高端的关系型数据库管理系统（RDBMS），而不是一个数据库。SQL Server 2000是 Microsoft公司推出的SQL Server数据库管理系统的最新版本，被称为新一代大型电于商务、数据仓库和数据库解决方案。 RDBMS是一个用来建立和管理数据库的引擎（其它如：Foxbase、Visual Foxpro、Access等）。但SQL Server在存储大容量数据、数据安全性、完整性、分布式处理、高效机制等方面远胜过其它RDBMS，它用于个人、企业数据库上。 历史 SQL Server是由Microsoft开发和推广的关系数据库管理系统（RDBMS）,它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。 后来Microsoft中止合作，独立开发，并不断更新版本和运行平台，1995年，Microsoft 推出了SQL Server 6.0版本，1996年，Microsoft 推出了SQL Server 6.5版本，1998年，SQL Server 7.0版本和用户见面，从而挤进了企业级数据库行列。SQL Server 2000（v8.0）是Microsoft公司于2000年12月推出的最新版本。 功能 SQL Server具有强大的数据管理功能，提供了丰富的管理工具支持数据的完整性管理、安全性管理和作业管理。 SQL Server具有分布式数据库和数据仓库功能，能进行分布式事务处理和联机分析处理，支持客户机／服务器结构。 SQL Server支持标准的ANSI SQL，还把标准 SQL扩展成为更为实用的 Transact－SQL。 SQL Server具有强大的网络功能，支持发布Web页面以及接收电于邮件。 特点 它可以帮助各种规模的企业来管理数据，是创建企业应用程序时首选的RDBMS。 易于安装、开发和使用。 采用图形的管理工具；能动态地配置运行环境；有丰富的管理工具（SQL Server Enterprise Manager）；可编程的管理以实现自动化管理（分布式管理工具，SQL-DMO）；方便地建立SQL Server 2000应用程序。 可收缩性和高可靠性。 可以工作于Win98以上的所有操作系统、可以管理上千个用户访问的庞大数据库、 还具有动态自我调解的特性能在笔记本和PC机上工作。 支持数据仓库（即海量数据库）。 海量数据库包含了来自面向事务的数据库的数据，用来研究趋势（决策），这些趋势（决策）决非是一般草率的检查可以发现的。 系统集成。 访问Internet的集成（有能力处理数据库非常大的Internet站点、可与Site Server一起建立和维护大型的高级的电子商务Web站点、包括对XML的支持）；与Windows NT和Win2000的安全性集成（支持使用Windows NT和Win2000的用户和域帐号作为SQL Server的注册帐号，即Windows认证）；邮件集成（可通过Exchange、邮件服务器收发邮件和页面，还可以收发包含Transact-SQL语句的邮件，此时SQL Server将执行该语句并返回结果）。 四、SQL Server 2000的安装 SQL Server 2000提供了丰富的、功能完善的数据库应用和管理工具，使用方便，用户可以快速准确地实现 SQL Server 2000提供的各种应用与管理功能。 1、安装前的注意事项 确保计算机满足SQLServer2000的软硬件要求； 在Win NT/2000中安装还需要一个或多个域用户帐号，以便SQLServer2000与其它客户端、服务器端进行通信； 以本地管理员权限的用户帐号登录系统(安装后可添加‘新登录’用户)； 关闭与SQLServer2000有关的所有服务，包括ODBC（开放数据库互连）的服务。 2、安装、运行SQL Server 2000的硬件环境要求 计算机（CPU） Intel及其兼容机，Pentium 166Mz或者更高处理器或DEC Alpha和其兼容系统。 内存（RAM） 最少64MB内存（个人版：Windows 98上最少需要32MB内存）。 硬盘空间 完全安装：180MB，典型安装：170MB，最小安装：65MB 客户端工具：90MB，Analysis Services：50MB，English Query：80MB 建议预留500MB的程序空间、500MB的数据空间 显示器 800X600及以上分辨率才能使用图形工具 3、安装、运行SQL Server 2000的软件环境要求 SQL Server 2000企业版：必须运行于安装Windows NT Server Enterprise Edition 4.0或者Windows2000 Advanced Server以及更高版本的操作系统下。 SQL Server 2000标准版：必须运行于安装Windows NT Server Enterprise Edition 4.0、Windows NT Server 4.0、Windows 2000 Server以及更高版本的操作系统下。 SQL Server 2000 个人版：可在多种操作系统下运行，如可运行于Windows 9x， Windows NT 4.0或Windows 2000的服务器版或工作站版的操作系统下。 SQL Server 2000 开发版：可运行于上述Windows 9x以外的所有操作系统下。 Internet软件：IE5.0以上。 IIS（Internet Information Services）：Internet信息服务，用于编写XML程序。 4、创建SQL Server服务帐号 在Win NT/2000系统中，SQL2000有两个重要的服务： MS SQL Server服务：直接通过Transact-SQL管理数据库； SQL Server Agent服务：管理日常的维护任务。 在运行这些服务之前，必须事先为它们分配Windows帐号。如果SQL安装在Win98/Me系统中，此时SQL2000模拟实现上述两种服务，则不需要用户帐号。 在Win2000/NT中启动SQL Server服务时，可以使用两种帐号： 本地帐号：不要求口令，SQL服务器不能通过网络与其它服务器联系。在个人PC机上安装时可用它。 域用户帐号：能够支持服务器之间的活动，如远程过程调用、复制、备份到网络Driver、远程数据源的异种连接等。 如果某一用户通过Windows2000/NT的帐号A（由系统管理员在Windows2000/NT中创建）登录Windows系统，则系统管理员要预先在SQL Server中新建该帐号的登录，此时才可连接到SQL Server实例上，也才能进一步地对数据库进行访问。当然，也可直接用系统管理员在SQL Server中新建的帐号连接到SQL Server实例上。 5、SQL Server2000的安装路径 共享文件路径：不允许改变其安装路径，被安装在系统盘的Program Files\Microsoft SQL Server\80文件夹中。 程序文件和数据文件的默认实例路径：分别被安装在系统盘的Program Files\Microsoft SQL Server\MSSql\binn及data文件夹中。可以改变其安装路径。 程序文件：指可执行文件等。 数据文件：指数据库文件、日志文件、以及系统日志、备份数据和复制数据的目录。 程序文件和数据文件的命名实例路径：分别被安装在系统盘的Program Files\Microsoft SQL Server\MSSql$InstanceName\binn及data文件夹中。可以改变其安装路径。 6、安装及安装中的一些概念 安装 Analysis Service（50MB）：包含联机分析处理（OLAP）和功能强大的数据挖掘。 安装 English Query（80MB）：安装后用户可以创建应用程序，直接使用英文进行数据库查询，从而代替了繁琐的 SQL语句。 在本地计算机上可使用所有安装、升级选项以及高级选项（注册表重建、无值守安装和维护故障转移群集的虚拟服务器）；在远程安装模式（安装到远程计算机上）中这些选项都不可用。其中【虚拟服务器】选项是在安装了 Microsoft Cluster Service系统的 Windows NT／2000企业版环境中可选。 SQL Server 2000支持在同一台计算机上最多同时运行16个SQL Server数据库引擎实例（一个为默认实例，名称为“计算机名”；15个命名实例，名称为“计算机名\命名”）。每个实例操作都与同一台计算机上的其它实例分开，每个SQL Server实例都有自己的一套系统及用户数据库、MSSQLServer、MSSQLServerAgent及注册表键，它们是不被其他实例共享的，但它们共用一个程序组，可通过服务管理器启/停、企业管理器管理。应用程序连接本机默认实例、命名实例、其他计算机上的实例的方式是没有多大差别的，一般我们在配置服务器时安装SQL Server 2000的一个或多个实例，而在客户端安装SQL Server的一个实例就行了。 在win98下安装时无“服务帐户”界面。也不支持Windows身份验证模式。     授课题目（章、节） 第二讲 SQL Server 2000配置与使用  学时 3 授课时间 周4第6～8节 第( 2 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解客户机/服务器体系结构的特征和工作模式； 2、了解SQL Server的各种组件； 3、掌握SQL Server启动和登录的方法； 4、能够利用企业管理器中管理SQL Server服务器。 掌握导入导出数据、分离附加数据库的方法  教学重点、难点： 1、启动和登录数据库服务器 2、利用企业管理器中管理SQL Server服务器  大体内容 教学方法 时间安排  SQL Server和客户机/服务器体系结构 面授   启动和登录数据库服务器 面授   利用企业管理器中管理SQL Server服务器 面授   导入导出数据、分离附加数据库 面授                   教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   一、SQL Server和客户机/服务器体系结构 主从式（主机/终端式系统） DBMS、所有数据和应用程序都存储在一台计算机中，用户通过终端发出数据请求。 特点：单点数据和单点处理方式。 2．分布式的含义 处理的分布 进行分布的处理，但数据集中在一台计算机。网络上的多台计算机从一个数据库中存取数据，然后在各自的计算机上处理。这种方式仍然属于集中式DBMS。 数据的分布 数据分布地存储在网络中的不同计算机中，所有数据在逻辑上是一个整体，构成一个逻辑的数据库。网络中每个节点都具有独立处理本地数据库中数据的能力—场地自治性，也可存取和处理异地数据库中的数据。 功能的分布 将DBMS的功能与应用程序的功能分开。设置SQL Server的服务器，用于执行RDBMS功能如事务处理和数据访问控制，而其它的计算机专门执行用户的应用程序—客户机（Client）。 客户机/服务器（简称C/S）数据库系统可分为单服务器结构和多服务器结构。 3．客户机/服务器系统 C/S是要将一个处理任务分解成多个子任务，由多台计算机分工完成： 客户端完成数据处理、数据表示、用户接口等功能； 服务器端完成DBMS的核心功能（对数据的处理、将结果返回到客户端）。 客户机向数据库服务器发出对某种数据的服务请求，由系统中最适宜完成该任务的服务器响应请求，并将结果送给客户机。服务器还实施数据完整性检查，维护数据库的其它附加数据，实施安全性检查、并发控制和查询优化等功能。 4．客户机/服务器系统的工作模式 客户机的主要任务： 管理用户界面（提供用户操作界面）； 接受用户的数据和处理请求； 处理应用程序； 产生对数据库的请求； 向服务器发出请求； 接受服务器返回的结果； 以用户需要的形式输出结果。 服务器的主要任务： 接受客户机发出的数据请求； 处理对数据库的请求； 将处理结果传送给发出请求的客户机； 进行数据完整性检查； 维护数据字典、索引和其它附加数据； 处理恢复数据； 查询、更新的优化处理。 5．客户机/服务器的主要技术特征 一个服务器可以同时为多个客户机提供服务，服务器必须具有对多用户共享资源协调的能力。 向客户机提供位置透明性服务(即提供服务器网址后，用户不必知道具体位置后就可访问服务器)。 客户和服务器之间通过报文交换来实现“服务请求、服务响应”。 具有良好的可扩充性，可扩充系统服务的规模、增加新的服务项目、提高服务性能。 6．客户机/服务器系统的组成结构 服务器平台服务器平台必须是多用户计算机系统。 客户平台 连接支持 7．网络服务器的类型 数据库服务器； 文件服务器； WEB服务器； 电子邮件服务器； 应用服务器（如视频、音频分别提供视频点播、音频点播服务）。 8、SQL Server 2000客户端组件 客户端使用为访问SQL Server中的数据而编写的应用程序来访问 Microsoft SQL Server2000。 能用于访问SQL Server 2000的客户端应用程序（组件）一般包括两部分： 传送给数据库引擎的Transact-SQL语句 一套用于传送 Transact-SQL语句及处理结果集的接口。 SQL Server 2000所支持的两类主要的客户端应用程序： 关系数据库应用程序（将Transact－SQL语句发送给数据库引擎并接受以关系结果集形式返回的结果）。使用时需要编写数据库应用程序接口（API），要求较高，可用C、C++、VB等语言编写。 Internet应用程序（将Transact－SQL语句或XPath查询发送数据库引擎，并接受以XML文档形式返回的结果）。 9．SQL Server 2000服务器端组件 SQL Server数据库引擎（MSSQLServer服务） 管理由SQL Server实例拥有的数据库的所有文件。功能包括：处理所有发自 SQL Server客户端应用程序的 Transact－SQL语句；处理存储过程，将执行的结果发给客户端；支持分布式查询以检索任一数据源的数据。 SQL Server的两种实例：一种是默认实例：服务名为 MSSQLServer；另一种是命名实例：MSSQL＄Instancename。 SQL Server代理服务（SQLServerAgent服务） 用于管理需要频繁重复执行的活动（如数据库定时备份等）、自动执行预先安排的管理任务、监视SQL Server事件、根据事件触发警报、集中管理在企业范围内分布的多个SQL Server服务器。 与 SQL Server实例的两种类型相对应，代理服务也有两种类型：一种是默认实例：服务名为SQLServerAgent；另一种是命名实例：服务名为SQLAgent＄InstanceName。 Microsoft搜索服务，包括： 索引支持：建立数据库的全文目录和索引。 查询支持：针对支持全文的检索。它支持三种类型的查询：检索词或短语、检索近义词、检索动词和名词的一些复杂变化形式。 Microsoft分布式事务处理协调器（MS DTC服务） 协调正确完成分布式事务，以确保所有服务器上的全部更新为永久性的，或在发生错误时删除所有更新、恢复提交前的状态，它允许在一个事务中访问不同服务器上的数据库。 注意：操作系统为 Microsoft Windows NT、Windows 2000的计算机支持这 4种服务器组件。由于Windows 98操作系统不支持服务，运行 Windows 98操作系统的计算机上服务器组件不能作为服务来实现。Microsoft搜索服务在 Windows Me或 Windows 98上不可用。 10．SQL Server 2000传输组件 SQL Server 2000根据客户端和服务器端是否位于同一台计算机上，采取不同的传输方法（即通信机制）。客户端和服务器端位于同一计算机上时，SQL Server 2000采用了 Windows中提供的本地命名管道、共享内存等进程间通信技术（IPC）来实现客户端和服务器端的通信；当客户端和服务器端不位于同一台计算机上时，SQL Server 2000将采用网络间的进程通信。 二、启动和登录数据库服务器 数据库在访问之前必须首先启动数据库服务器，数据库服务器实际上是SQL Server2000的一个软件，启动数据库服务器就是使该程序处于运行状态。数据库登录是基于安全管理机制，只有合法用户才可以使用 SQL Server 2000服务器。（发电—输出—缴费用电） 1、数据库服务器的启动、暂停、停止 可以通过SQL Server服务管理器进行。启动了SQL Server，也就启动了SQL Server服务，平时所说的启动SQL Server服务等价于启动SQL Server（不包括SQL Server Agent、MSDTC、Microsoft Search）。 在Windows NT/2000中可以远程管理SQL Server，而Windows 98/Me中则不可以。 SQL Server 服务管理器是服务器端最有用的实用程序。服务管理器用来启动、暂停、继续和停止数据库服务器的实时服务，其提供的实时服务包括：SQL Server、SQL Server Agent、MSDTC（Microsoft Distributed Transaction Coordinator，微软分布式事务协调器）、Microsoft Search等。 三种状态：停止、暂停、启动。 当服务器停止时，任何已经连接的访问均被中断，并且不接受其它访问。当服务器暂停时，已有的连接仍然保持，但不再接受其它连接请求。 在服务停止之前，服务器执行的操作有： 取消注册 对每个数据库执行CHECKPOINT操作 等待所有当前正在执行的Transact-SQL语句或存储过程结束。 例：打开SQL查询分析器，对数据库中的数据进行查询，测试当SQL服务处于上述三种状态时的可用情况。（对暂停，可关闭查询分析器再打开，不行了！） SQL Server服务可以自动启动，也可以通过手工启动。 自动启动（当操作系统启动时自动启动）：（1）可以在服务管理器中选择“当启动OS时自动启动服务”；（2）在企业管理器中设置服务器的属性：打开该对话框左边的树形结构（介绍树型结构中的内容），找到相应的服务器右击并选择【属性】命令，“常规”选项卡中“在操作系统启动时自动启动策略”（注：本项功能限于Windows 2000/NT）。 手工启动：（1）从服务管理器中启动（2）从企业管理器启动（3）从控制面板【管理工具】|【服务】中启动（注：限于Windows 2000/NT）。 2、修改系统默认的服务 默认服务是服务管理器界面上显示的服务（更改：右击【SQL Server服务管理器】标题并选【选项】。但只能更改本地计算机的默认服务）。 3、设置状态检测的时间间隔 SQL2000服务器各组件的状态由企业管理器和服务管理器有规律地（每隔一定的时间）进行监控。可以改变监控的时间间隔。 在企业管理器中，通过控制台的“工具”菜单打开“选项”对话框，设置对不同服务轮询时间间隔，每种服务的间隔可以不同。 在服务管理器中，打开“选项”，设置时间间隔，每种服务的间隔均相同。 在企业管理器和服务管理器中设置的间隔互不相干。 4、运行SQL服务（适用于Windows2000下） 在网络上运行SQL服务的另一种方法在命令提示符下，输入net start：检测SQL服务是否启动； net start mssqlserver：启动SQL Server服务； net stop mssqlserver：停止SQL Server服务； net start sqlserveragent：启动SQL ServerAgent服务； net stop sqlserveragent：停止SQL ServerAgent服务； net start mssql$cbf：启动SQL Server(cbf)服务； net start sqlagent$cbf：启动SQL ServerAgent(cbf)服务； 在单机上运行SQL服务的另一种方法也可输入上述的提示符命令。 5、登录数据库服务器 打开查询分析器，或先打开Enterprise Manager再通过“工具”打开查询分析器，都会弹出【连接到SQL Server】对话框（若不出现，说明用的是Windows身份验证模式，此时打开“文件/连接”就会看到该对话框，也可在Enterprise Manager中新建一个登录即可）。 注意：SQL Server下拉列表框：输入所要登录的数据库服务器名称。下拉列表框中存放的是以前曾访问过的数据库服务器名称。 安装后，第一次使用Enterprise Manager时一般要进行注册以便可对SQL Server 2000进行管理。进入SQL Server 2000时，如果采取【SQL Server身份验证】方式，系统只有唯一的一个帐号（sa），密码为安装时设定的密码，但可在Enterprise Manager中单击工具栏上的“登录”图标新建登录名（与Windows的用户无关）。 单击【确定】按钮即可与相应的数据库服务器建立连接，此时查询分析器相当于客户端计算机，它与Enterprise Manager中的注册无关。 三、在企业管理器中管理SQL Server服务器 创建服务器组：在一个网络系统中，可能有多个SQL Server服务器，可以对这些SQL Server服务器进行分组管理。分组的原则往往是依据组织结构原则。SQL Server分组管理由企业管理器来进行。首次启动企业管理器时，有一个名字叫“SQL Server 组”的默认服务器组自动被创建，但用户可以创建新的SQL Server组，在企业管理器菜单中单击操作菜单或者用右键单击SQL Server组，选择新建SQL Server 组菜单项，再在出现的服务器组对话框中输入组的名称并选择组的级别，单击确定即可。 服务器注册：指将网络系统中的某个SQL Server服务器注册到企业管理器中，以便于管理。 运行注册向导新建一个注册，此即表明可以管理SQL SERVER 2000服务器了。从“安全性”文件夹中可看到登录名（注册名自动映射为登录名）。 四、导入、导出数据 可导入、导出各种数据，如ACCESS、EXCEL、sql server数据库数据。 五、分离、附加数据库 例：将northwind数据库从SQL Server中分离，再拷贝到“我的文档”中。 将“我的文档”中的northwind数据库附加到SQL Server中，取名为“新northwind”。    授课题目（章、节） 第三讲 SQL Server 2000的系统和数据管理  学时 3 授课时间 周4第6～8节 第( 3 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解SQL Server2000系统管理的常见工具； 2、掌握数据库的逻辑结构和物理结构； 3、能熟练用企业管理器创建数据库并进行常规操作； 4、能通过SQL语句创建符合要求的数据库；  教学重点、难点： 1、数据库的物理结构 2、用SQL语句创建数据库  大体内容 教学方法 时间安排  系统管理工具 面授   SQL Server 2000的数据库结构 面授   用企业管理器创建数据库 面授   用SQL语句创建数据库                    教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   一、系统管理 SQL Server提供了大量的图形界面管理工具。主要有以下9类： 企业管理器 它是 Microsoft SQL Server 2000的 MMC管理单元，用于集中管理多个SQL服务器。它可与SQL服务运行在同一台计算机上，也可运行在不同的计算机上。它提供了访问全部 SQL Server 2000服务器和数据库配置选项的能力。可以完成： 管理登录和用户帐号、权限。 管理 SQL Server对象的备份、复制、日志；创建全文索引、数据库图表和数据库维护计划。 管理数据库及表、视图、索引、存储过程、触发器、规则、默认值和用户定义数据类型等数据库对象（其中触发器、索引等对象必须在某个具体表上右击才能看到，也可在查询分析器的对象浏览器中某个表上看到）。 导入/导出数据、转换数据。 执行各类Web管理任务（如管理SQL邮件、在网上发布数据等）。 启动、停止、配置服务器 查询分析器：简单的交互式窗口，供输入Transact-SQL命令和系统存储过程并能测试SQL命令的执行结果。 导入和导出数据：数据传递工具，可在服务器之间传递SQL Server数据、异种数据（如将 Access数据库导入到 SQL Server中，或在SQL Server中的不同数据库之间进行，或将 SQL Server中的数据导出到一个文本文件中等等）。 事件探查器（Profiler）：实时地捕获连续的服务器活动记录、监视事件、根据用户指定的准则筛选事件、将跟踪输出引导到屏幕、文件或表；可以重新播放以前捕获的跟踪，用于服务器纠错中。 服务管理器：启动、停止、暂停服务器；查看服务状态。 客户端网络实用工具：用于设置客户机与SQL服务器连接时的特性，如使用的网络协议、服务器别名等。DB-Library是SQL Server客户用来与服务器通信的库。选项卡中的选项用来处理国际字符集。 服务器网络实用工具：管理服务器网络库（Net-Library）。可指定：Microsoft SQL Server 2000实例在其上监听客户端请求的网络协议栈、从应用程序建立连接时考虑使用的服务器Net-Library顺序、Microsoft SQL Server 2000实例监听的新网络地址。 联机丛书 在 IIS 中配置 SQL XML 支持：用于配置从Web上访问SQL Server 2000时的一些参数，如虚拟目录、登录方式等。 二、SQL Server 2000的数据库结构 数据库主要是用来描述－系列对象和数据，使用特定的技术把数据组织起来，以一种计算机易于管理而最终用户也易于使用的方法把数据保存起来。 数据库以2个或多个文件的形式存储在磁盘中（物理结构），通过某种形式表现出来供用户使用（逻辑结构）。 数据库的逻辑结构指的是数据库是由哪些性质的信息所组成，SQL Server的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。 （1）数据库逻辑体系结构 创建数据库时只是创建了一个空壳，必须再在其中创建各种对象，以便保存数据。数据库逻辑组件（即对象）包括： 表 所有数据的存放场所，为2维表格。 关系图 各表之间的关系。 索引 索引是对数据库表中一个或多个列的值进行排序的结构。可以利用索引快速访问数据库表中的特定信息。 视图 视图同表一样具有显示数据的功能，但本身并不保存数据。它是保存在数据库中的SELECT查询，SELECT语句的结果集构成视图所返回的虚拟表。故又与查询不完全相同。 默认 如果在插入记录时没有指定字段的值，默认值将指定字段中所使用的值。 规则 一套约束机制。 存储过程 存储过程是T—SQL语句和可选控制流语句的预编译集合（允许用户在其中声明变量），以一个名称存储并作为一个单元处理。它存储在数据库内，可由应用程序通过一个调用执行，它有条件执行以及其它强大的编程功能。 触发器 触发器是一种特殊类型的存储过程，当使用UPDATE、INSERT或DELETE中的一种或多种操作在指定表上进行修改时，触发器会生效。触发器可以查询其它表，而且可以包含复杂的SQL语句。 用户自定义数据类型（有时不认为是对象） 重新描述数据类型。 用户和角色（有时不认为是对象） 访问数据库的人、具有一组相同权限的用户。 （2）数据库物理体系结构 页和扩展盘区（有时译为“范围”）是SQL Server 2000对数据库对象进行空间分配的数据结构（形式）。 页（即页面） 页是SQL Server 2000中数据存储空间的最小单位（大小为8KB），所有数据信息都存储在页上。每页的页首是96Bytes的系统信息（包括页的类型、页的可用空间量、拥有页的对象的标识符等）。SQL Server 2000中的8种页面类型：数据 除text、ntext、image数据外所有的数据索引 用于存储数据库的索引数据文本/图像 text、ntext、image类型的数据全局分配映射表（即全局分配映射图） 已分配的扩展盘区的信息页的可用空间 各页中剩余的空间大小索引分配映射表（即索引分配图） 各索引所分配的扩展盘区的信息大容量更改映射表 有关自上次执行BACKUP LOG语句后大容量操作所修改 的扩展盘区的信息。差异更改映射表 有关自上次执行BACKUP DATABASE语句后更改的扩展盘区的信息。 注意：1：日志文件不包含在页面中。2：记录不能跨页存放，故记录内如不包括text、ntext和 image数据，则所包含的最多数据量是8060字节。
扩展盘区（范围） 扩展盘区是SQL Server 2000为表和索引分配空间的最小单位。一个扩展盘区由8个相连的页面组成，大小为64KB。 每一个扩展盘区只能包含一个数据库对象（在统一扩展盘区的情况下）。比如，一个数据库中含有2个表和2个索引，则表和索引至少共占用空间4(64KB。 为了使空间分配更有效，对于只含有少量数据的表或索引，SQL不分配完整的扩展盘区，而是与其它对象共用扩展盘区（称为混合扩展盘区）。通常从混合扩展盘区中向新表或新索引分配页。当表或索引增长到8页时，就变成统一扩展盘区。 总之，一个数据库由文件（*.mdf、*.ldf）组成，而*.mdf文件由扩展盘区组成，扩展盘区由页组成。 物理数据库组件 SQL Server 2000数据库组件（文件）有三种类型：主要数据文件(．mdf)、次要数据文件（.ndf）、日志文件（．ldf）。每个数据库都必须有一个主要数据文件，而次要数据文件不作要求：可以没有，也可以有多个，每个数据库必须至少有一个日志文件，但可以不止一个。数据库中的所有数据和对象（如表、存储过程、触发器和视图等）都存储在其中。 主文件 该文件包含数据库的起点信息（指向数据库的其余文件），并用于存储数据。每个数据库都有一个主要数据文件。默认扩展名为mdf。     教学过程(基本内容) 辅助手段 备注   时间分配   次文件 这些文件含有不能置于主要数据文件中的所有数据。如果主文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。有些数据库可能足够大，故需要多个次要数据文件，或使用位于不同磁盘驱动器上的次文件将数据扩展到多个磁盘。默认扩展名为ndf。 事务日志文件 这些文件包含用于恢复数据库的日志信息。每个数据库都必须至少有一个日志文件。默认扩展名为ldf。 例如：创建简单的数据库sales时，可以只使用一个包含所有数据和对象的主文件和一个包含事务日志信息的日志文件。另一种情况是，创建更复杂的数据库orders时，可以使用一个主文件和五个次文件，数据库内的数据和对象扩展到这所有的六个文件中，另外有四个日志文件包含事务日志信息。 下面是在SQL默认实例上创建的数据库的逻辑文件名和物理文件名示例： 注意：1：SQL数据和日志文件可以放置在FAT或NTFS文件系统中，但不能放在压缩文件系统中。 2：SQL文件可以从它们最初指定的大小自动增长。定义文件时可以指定增量。每次填充文件时，均按这个增量值增加它的大小。如果在文件组中有多个文件，这些文件在全部填满之前不自动增长。 文件组 文件组允许对文件进行分组，以便于管理和数据的分配和放置。 例如：可以分别在三个硬盘上创建三个文件（MyData1.ndf、MyData2.ndf和MyData3.ndf），并将这三个文件指派到文件组fgroup1中。然后，可以明确地在文件组fgroup1中创建一个对象（如视图），该对象存放时将分散到三个磁盘上，因而性能得以提高。如果数据库超过单个Windows NT文件的最大大小，则可以使用次要数据文件允许数据库继续增长。 注意：1：一个文件不可以同时是多个文件组的成员。2：日志文件不能作为文件组的一部分。日志空间与数据空间分开管理。3：文件组中的文件不自动增长，除非文件组中的文件全都没有可用空间。 文件组的类型： 主文件组主文件组包含主要数据文件和任何没有明确指派给其它文件组的其它文件。系统表的所有页均分配在主文件组中。 用户定义的文件组用户定义文件组是在Create Database或Alter Database语句中，使用FileGroup关键字指定的文件组。 默认文件组每个数据库中都有一个文件组作为默认文件组运行。当SQL给创建时没有为其指定文件组的表或索引分配页时，将从默认文件组中进行分配。一次只能有一个文件组作为默认文件组。db_owner固定数据库角色成员可以将默认文件组从一个文件组切换到另一个。如果没有指定默认文件组，则主文件组是默认文件组。 文件组的优点： 可以提高数据库的查询性能； 如果数据库超过了单个文件允许的最大值，可以使用其余数据文件使数据库继续增长。 事务日志 事务是一个单元的工作，要么全部完成，要么不完成。在SQL中创建数据库时，会同时创建事务日志（必须指明存放事务日志的文件），当对数据库进行修改时，事务日志会自动记录下每一事务的开始、所作的修改、页的分配、事务的提交及回滚等。当数据库损坏后可用它来恢复（是SQL的重要容错特性）。 （从SQL7.0开始分开存放）事务日志与数据分开存放的好处为： 事务日志可以单独备份 在服务器崩溃的事件中，有可能将服务器恢复到最近的状态 事务日志不会占用数据库的空间 可以很容易地监测事务日志的空间 在向数据库和事务日志中写入时会产生较少的冲突 text、ntext、image的存储 在 SQL Server 2000中，小的text、ntext或image值可以直接存储在记录中，大的text、ntext或image值被存储在页集合中。每个表只有一个保存text、ntext和image数据的页集合，text、ntext和 image数据可以保存在同一页中。在sysindexes中indid=255的记录保存了这个页集合的起始位置。 当text、ntext或image值被存储在页集合中时，在记录的相应字段中将存放一个16位的指针来代替text、ntext或image的信息，该指针指向页集合。 三、创建、修改、删除数据库 SQL Server2000主要使用Transact－SQL语言对数据进行管理，SQL语言主要有3种子语言： 数据定义语言（DDL）：用于定义和管理SQL数据库及其中的所有对象，包括：CRE ATE、ALTER、DROP等语句。 数据操纵语言（DML）：用于选择(检索)、插入、更新和删除使用DDL定义的对象中的数据。 控制语言（DCL）：COMMIT、ROLLBACK。 数据定义语言（Data Definition Language）如下： CREATE DATABASE：创建数据库。 ALTER DATABASE：修改数据库。 DROP DATABASE：删除数据库。 CREATE TABLE：创建一个数据库表。 ALTER TABLE：修改数据表的结构。 DROP TABLE：从数据库中删除表。 CREATE VIEW：创建一个视图。 ALTER VIEW：修改一个视图。 DROP VIEW：从数据库中删除视图。 CREATE INDEX：为数据库中的表创建一个索引。 DROP INDEX：从数据库中删除索引。 CREATE PROCEDURE：创建一个存储过程。 ALTER PROCEDURE：修改一个存储过程。 DROP PROCEDURE：从数据库中删除存储过程。 CREATE TRIGGER：创建一个触发器。 ALTER TRIGGER：修改触发器 DROP TRIGGER：从数据库中删除触发器。 CREATE SCHEMA：创建一个架构对象并授权给某个用户。架构在概念上可以看作是包含表、视图和权限定义的对象，架构是SQL-92中的名称。 DROP SCHEMA：从数据库中删除一个架构。 CREATE DOMAIN：创建一个数据值域 //域是SQL-92中的名称，在SQL Server2000中已改为用户自定义数据类型 ALTER DOMAIN：改变域定义。 DROP DOMAIN：从数据库中删除一个域。 下面以数据库为例进行讲解： 注意：在创建数据库时，需要确定数据库名、所有者（创建数据库的用户）、数据库大小、数据库文件和文件组： 缺省情况下，只有系统管理员可以创建新数据库。但系统管理员可以通过授权将创建数据库的权限委派给其它用户； 所有新数据库都是model数据库的拷贝； 数据库的大小可缩放； 当新的数据库创建时，SQL自动更新master数据库中的sysdatabases系统表。 数据库命名须遵循命名规则； SQL命名规则（中文版中可以直接使用中文） 长度在1－30个字符之内 第一个字符必须是英文字母或_、@、#；第二个字符起可以是字母、数字、或上述字符 不能含有空格，除非使用引号 （1）在企业管理器中创建数据库 在企业管理器的列表中右击“数据库”后，选择“新建数据库”，在数据库属性中输入数据库名称、数据文件和日志文件的各种属性。 当建立数据库后，仍可以修改数据库的属性：右击某数据库，选择“属性”。可以设置数据文件的各种属性。 [例1]：创建一个逻辑文件名称为“学生管理数据库”，实际文件名称为“学生管理数据库.mdf”，初始容量为1MB，最大容量为50MB，且每次以5MB空间增长的数据库；创建一个逻辑文件名称为“学生管理日志”，实际文件名称为“学生管理日志.ldf”，初始容量为1MB，最大容量为5MB，且每次以20%空间增长的日志文件。 （2）使用SQL语句创建数据库（见P168） CREATE DATABASE：创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文件（即已脱离SQL的管理）中附加数据库。 CREATE DATABASE database_name [ ON ????[ < filespec > [ ,...n ] ] ????[ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ]   其中： < filespec > ::=[ PRIMARY ] ( [ NAME = logical_file_name , ]???FILENAME = 'os_file_name'????[ , SIZE = size ]???[ , MAXSIZE = { max_size | UNLIMITED } ]????[ , FILEGROWTH = growth_increment ] ) [ ,...n ] < filegroup > ::=FILEGROUP filegroup_name < filespec > [ ,...n ] [例1]： create database 学生管理数据库 on (name='学生管理数据库', --可省略单引号或用双引号代替 filename='c:\学生管理数据库.mdf', --可用双引号，但不能省略 size=1,filegrowth=5MB,maxsize=50) log on (name='学生管理日志',filename='c:\学生管理日志.ldf', size=1,maxsize=5,filegrowth=20%)  [例2]： Create database data2 on (name=d20,filename='c:\d20.mdf'), filegroup g1 (name=a,filename='c:\g21.mdf'), (name=b,filename='c:\g22.ndf')  [例3]： create database data3 on primary(name=d30,filename='c:\d30.mdf'), (name=d31,filename='c:\d31.ndf'), filegroup g1 (name=g31,filename='c:\g31.ndf'), (name=g32,filename='c:\g32.ndf'), filegroup g2 (name=g33,filename='c:\g33.ndf'), (name=g34,filename='c:\g34.ndf') log on (name=l31,filename='c:\l31.ldf'), (name='l32',filename='c:\l32.ldf')  [例4]： create database data4 on( name=d41, filename='c:\d41.ndf'), primary( name=d40, filename='c:\d40.mdf'), filegroup g1( name=g41, filename='c:\g41.ndf') log on ( name=l41, filename='c:\l41.ldf')  [例5] ： for attach的使用 先将“学生管理数据库”从企业管理器中分离，再： create database 学生管理数据库10 on(name='学生管理数据库',filename='c:\学生管理数据库.mdf') for attach 则可看到学生管理数据库又附加进企业管理器中了（注意：name项可省略；虽然表面上可更改逻辑名但起不了作用）。 [例6] ：省略数据文件和日志文件 create database 学生管理数据库11     授课题目（章、节） 第四讲 SQL Server 2000的系统和数据管理（续）  学时 3 授课时间 周4第6～8节 第( 4 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 能用企业管理器修改、删除数据库； 能熟练使用SQL语句修改、删除数据库； 了解SQL Server2000的系统数据库、示例数据库、系统表。  教学重点、难点： 1、使用SQL语句修改、删除数据库  大体内容 教学方法 时间安排  在企业管理器中修改、删除数据库 面授   使用SQL语句修改、删除数据库 面授   SQL Server2000系统数据库、示例数据库、系统表 面授                       教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   一、在企业管理器中修改数据库 打开数据库的属性对话框，可以设置数据库的数据文件、日志文件、选项等的属性。 二、使用SQL语句修改数据库 Alter Database：在数据库中添加或删除文件、文件组，更改数据库、文件、文件组的属性（数据库名称、文件及文件组的逻辑名、文件的大小等）。 ALTER DATABASE database_name {ADD FILE <filespec>[,...n] [TO FILEGROUP filegroup_name] |ADD FILEGROUP filegroup_name |ADD LOG FILE <filespec>[,...n] |REMOVE FILE logical_file_name //包括日志文件 |REMOVE FILEGROUP filegroup_name|MODIFY NAME=new_databasename |MODIFY FILE <filespec> //包括日志文件。不必指出filename项。|MODIFY FILEGROUP filegroup_name {filegroup_property|NAME=new_filegroup_name}|SET <optionspec> [,...n] [WITH <termination>] |COLLATE <collation_name> }  说明： 只有当文件组为空（不包含文件）时才能删除文件组。例：将上节例3中的DATA1数据库中的文件组G1删除。 MODIFY FILE指定要更改给定的文件，更改选项包括NAME、SIZE、FILEGROWTH和MAXSIZE。必须在<filespec>中指定NAME，以标识要更改的文件，新逻辑名用“NEWNAME=XXX”指定。如果指定了SIZE，那么新大小必须比文件当前大小要大。 filegroup_property：如readonly、default、readwrite等(在数据库的属性中可看到文件组属性) SET：指定数据库从一种状态转变为另一种状态时，何时取消不完整的事务。 [例1]： alter database data1 add file( name=df5, filename='c:\df5.ndf')  [例2]： alter database data1 add file( name=df6, filename='c:\df6.ndf')to filegroup g1  [例3]： alter database data1 add filegroup g2 alter database data1 add file( name=df8, filename='c:\df8.ndf')to filegroup g2  [例4]： alter database data1 add log file( name=dl2, filename='c:\dl2.ldf')  [例5]： alter database data1 remove file df3 alter database data1 remove filegroup g1  [例6]： alter database data1 modify name =data11 alter database data11 modify name =data1   [例7]： alter database data1 modify file( name=df8, newname=df9,size=4,filegrowth=3) alter database data1 modify filegroup g2 name=g3 alter database data1 modify filegroup g3 readonly  三、压缩数据库（使用数据库一致性检查器DBCC命令实现） DBCC SHRINKDATABASE (database_name[,target_percent][,NOTRUNCATE|TRUNCATEONLY])  target_percent：表示数据库压缩之后该数据库的自由空间在数据库整个尺寸中的百分比（不加百分号！）。 NOTRUNCATE：表示在数据库文件中保留所释放的文件空间。//TRUNCATE：截短 TRUNCATEONLY：表示将数据文件中的任何未使用的空间释放给操作系统(这是默认情况)。 注意：压缩仅限于数据库增长的部分，压缩后的数据库不能小于创建时的大小。 例：DBCC SHRINKDATABASE(data1,10,TRUNCATEONLY) 四、删除数据库 使用企业管理器删除数据库。 使用SQL语句Drop Database。 DROP DATABASE database_name [ ,...n ] 五、SQL Server2000系统数据库（P85） 它们用来操作和管理系统。 master数据库 master数据库记录SQL系统的所有系统级信息。包括登录帐号、系统配置参数、建立的用户数据库、磁盘空间及其设备的分配、其它的SQL Server等信息。 master数据库对系统来说至关重要，有些对象禁止用户直接访问。 它始终有一个可用的最新master数据库备份。 tempdb数据库 tempdb是一个全局性的临时数据库，它保存所有的临时表、临时存储过程、保存中间结果的工作表、满足任何其它的临时存储要求（在sysobjects表中可看到！），tempdb数据库在SQL Server每次启动时都重新创建，因此该数据库在系统启动时总是空的。使用它时无特殊权限要求。临时表和存储过程在连接断开时自动除去，而且当系统关闭后将没有任何连接处于活动状态，因此tempdb数据库中没有任何内容会从SQL Server的一个会话保存到另一个会话。 tempdb数据库会根据需要自动增长。不过，与其它数据库不同，每次启动数据库引擎时，它会重置为其初始大小。可以使用ALTER DATABASE 增加tempdb数据库的大小。 model数据库 model数据库自动用作在系统上创建的所有数据库的模板。可以给它加上一些对象和授权，此时创建的数据库也会具有这些对象和授权的。当运行CREATE DATABASE语句时，新数据库的第一部分通过复制model数据库中的内容创建，剩余部分由空页填充。由于SQL每次启动时都要创建tempdb数据库，所以model数据库必须一直存在于SQL Server系统中。 msdb数据库 msdb数据库供SQL Server代理程序调度警报和作业以及记录各个操作时使用（即维护历史）。 注意：在SQL Server 2000和SQL Server 7.0中，包括系统数据库在内的每个数据库都有自己的文件集，而且不与其它数据库共享这些文件。 系统数据库及日志文件 数据库文件 物理文件名  master 主数据 Master.mdf  master 日志 Mastlog.ldf  tempdb 主数据 Tempdb.mdf  tempdb 日志 Templog.ldf  model 主数据 Model.mdf  model 日志 Modellog.ldf  msdb 主数据 Msdbdata.mdf  msdb 日志 Msdblog.ldf  六、SQL Server2000的示例数据库（P101） pubs：模仿图书出版公司建立的数据库，在SQL文档中讲述SQL语句时用。 Northwind：模仿贸易公司建立的数据库，在SQL文档中介绍新特点和新功能时用。 七、SQL Server2000系统表（P86的表格列出了一些系统表） 系统表目录（也称数据字典）是由描述系统数据库、基表、视图和索引等对象结构的系统表组成。SQL Server 经常访问系统目录，检索系统正常运行所需的必要信息。系统表包括服务器级的系统表（在master中）和数据库级的系统表（在每个数据库中），用户对它们不能直接进行修改（delete、update、insert）操作，但可使用系统存储过程或系统函数进行操作。 现介绍几个主要的： 1. Sysobjects表 SQL Server 的主系统表sysobjects出现在master数据库和每个用户自定义的数据库中，它对每个数据库对象含有一行记录。 2. Syscolumns表 系统表syscolumns出现在master数据库和每个用户自定义的数据库中，它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。 3. Sysindexes表 系统表sysindexes出现在master数据库和每个用户自定义的数据库中，它对每个索引和没有聚簇索引的每个表含有一行记录，它还对包括文本/图像数据的每个表含有一行记录。 4. Sysusers表 系统表sysusers出现在master数据库和每个用户自定义的数据库中，它对整个数据库中的每个Windows NT用户、Windows NT用户组、SQL Server 用户或者SQL Server 角色含有一行记录。 5. Sysdatabases表 系统表sysdatabases对SQL Server 系统上的每个系统数据库和用户自定义的数据库含有一行记录，它只出现在master数据库中。 6. Sysdepends表 系统表Sysdepends对表、视图和存储过程之间的每个依赖关系含有一行记录，它出现在master数据库和每个用户自定义的数据库中。 7. Sysconstraints表 系统表sysconstraints对使用CREATE TABLE或者ALTER TABLE语句为数据库对象定义的每个完整性约束含有一行记录，它出现在master数据库和每个用户自定义的数据库中。    授课题目（章、节） 第五讲 数据表的创建与维护  学时 3 授课时间 周4第6～8节 第( 5 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、掌握SQL Server中常用的数据类型； 2、能通过企业管理器创建、修改、删除数据表； 3、能熟练使用SQL语句创建、修改、删除数据表；  教学重点、难点： 1、使用SQL语句创建、修改、删除数据表  大体内容 教学方法 时间安排  SQL Server中的数据类型 面授   通过企业管理器创建、修改、删除数据表 面授   通过SQL语句创建、修改、删除数据表 面授                       教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   SQL Server支持多种数据库对象，如表、视图、索引、存储过程等，它们中的数据都是以表的形式存放。 一、SQL Server中的数据类型（P111） 表中的每一列（字段）都必须声明其类型（共26种）： Bit 位整型 占1字节 只能表示0、1或NULL，用于存储两种可能性的值，如是/否。  Bigint 整型 8 可以表示-263—263之间的整数  Int 整型 4 可以表示-231—231之间的整数  smallint 小整型 2 可以表示-215—215之间的整数  tinyint 短整型 1 可以表示0—255之间的整数  decimal 精确数值型 9 可以表示-1038-1—1038-1的固定精度和范围的数据。（使用时需要指定其范围和精度）。范围是小数点左右所能存储数字的总位数，精度是小数右边存储的数字位数。  numeric 精确数值型 9 同上  money 货币型 8 常用于表示货币值，数据大小为-9220亿—9220亿  smallmoney 货币型 4 也用于表示货币值，数据大小较小  float 近似数值型 8 可以表示-1.79E+308—1.79E+308之间的近似数值，使用float(n)可以通过n来指定尾数的位数。精度15位。  real 近似数值型 4 可以表示-3.40E+38—3.40E+38之间的近似数值，不能指定宽度。精度7位。  datetime 日期时间型 8 可以表示1753年1月1日—9999年12月31日之间的任意日期和时间。精确到3ms。  smalldatetime 日期时间型 4 可以表示1900年1月1日—2079年6月6日之间的任意日期和时间。精确到分。  char 字符型 ≤8000 用于存储指定长度的字符，必须指定列长，最大为8000个字符  varchar 字符型 ≤8000 与char相似，也需要指定列长，但存储时根据字符的实际长度分配空间  nchar 统一编码字符型 ≤4000 通过双字节存储一个字符，最多可以存储4000字符. 统一编码：用双字节存储字符，而不是单字节，它允许大量的扩展字符  nvarchar 统一编码字符型 ≤4000 通过双字节存储一个字符，存储时根据字符的实际长度分配空间  text 字符型 16位指针 用于存储大量的字符数据  ntext 统一编码字符型 16位指针 用于存储大量的统一编码字符数据  image 二进制数据（即16进制） 16位指针 用于存储变长的二进制数据，可达20亿字节  timestamp（=binary(8)） 专有类型 8 用于创建一个数据库范围内唯一的数码，一个表中只能有一列timestamp列，每次插入或修改一行时，该值会被自动修改。该列数据不可识别。  uniqueidentifier=binary(16)） 专有类型 16 用于存储一个全局唯一标识符（GUID），它是全球性的全局标识符。  binary 二进制数据（即16进制） ≤8000 用于存储不超过8000Bytes长的定长的二进制数据  varbinary 二进制数据（即16进制） ≤8000 用于存储不超过8000Bytes长的非定长的二进制数据  table 专有类型  用于临时存储结果集（一组记录），只用于变量和函数返回值类型，不适用于字段。  sql_variant（新增加的） 专有类型  用于存储除text、ntext、image、timestamp、sql_variant外的各种数据类型。  空值(NULL)的含义:表示没有值、是不确定的值。它不是0或空格。比如：年龄字段，某人的年龄没有公开，就不要填写，此即NULL。数据库中主码不能取空值。 二、创建数据表（P171） 表由行和列构成。行称为元组（记录），代表一个实体的有关属性的集合，如一个学生表中的一行，表示一个学生的所有相关属性信息（姓名、学号、年龄等）。列称为属性（字段），代表实体某一方面的信息。 [例1]：要求在以前已经建立的某一数据库中创建一个学生情况表（student），结构如下： 字段名 数据类型 能否取空值  学号 整型 No，且为主键  姓名 字符（串）类型，8字节 No  性别 字符（串）类型，2字节 Yes  年龄 短整型，1字节 Yes  专业 字符（串）类型，30字节 Yes  家庭地址 字符（串）类型，50字节 Yes  1．在企业管理器中创建表 打开企业管理器后选择某一数据库右击，选择“新建/表…”，输入各个字段的名称和属性，通过工具栏中的钥匙按钮设置主键，最后单击保存按钮或在关闭子窗口时输入新表名称。 2．使用CREATE TABLE语句创建新表 CREATE TABLE [datebase_name . owner. | owner.] table_name (column_name data_type | column_name AS 计算列 {[DEFAULT 默认值] [NULL|NOT NULL] [PRIMARY KRY|UNIQUE] } [,……] )[ON 文件组名][TEXTIMAGE_ON 文件组名]  PRIMARY KEY|UNIQUE设置字段为主码或字段值必须唯一。一个表只能包含一个主码字段。 owner：必须是该数据库中已存在的用户。可用该数据库中的“用户”对象建立。 data_type：对于字符型，一般还需指定长度，其它类型不需要。 计算列：如：销售额 AS 单价*数量。 ON 文件组名：指定存储表的文件组，缺省时存储在默认文件组中。 [例2]：创建上例要求的表 use data1 create table student (sno int not null primary key, sname char(8) not null,ssex char(2) default ‘男’, sage tinyint,sspecial char(30),saddress char(50))  [例3]：创建另外两个表，一个用于保存各门课程的信息（课程编号、课程名称、学分），一个用于保存学生选修课程的情况（学号、所选课程号、成绩）。 [例4]：在表中可创建计算列： create table data1.dbo.score (score1 tinyint not null,score2 tinyint not null,total as score1+score2)  三、修改表的结构 在修改表的结构时应注意： 尽量在表中没有数据时进行修改，尤其在需要修改某列的数据类型或增加不可为空的列时； 在增加新列到表中时，保证该列允许为空； 修改已有列的数据类型时，应保证新的类型与原来类型兼容，否则修改将失败。 1．在企业管理器中修改表的结构 选择表后，使用快捷菜单中的“设计表”。最后需要保存。 使用ALTER TABLE修改表的结构 ALTER TABLE：通过更改、添加、删除列来修改表的定义。 ALTER TABLE table_name { ALTER COLUMN column_name new_data_type [NULL|NOT NULL] | ADD column_name data_type [NULL|NOT NULL] | DROP COLUMN column_name [,……]}  [例1]： alter table student add memo varchar(100),telephone bigint  [例2]： use data1 alter table student drop column saddress,memo  [例3]： use data1 alter table student alter column sname char(20) not null      授课题目（章、节） 第6讲 数据表的创建与维护（续）  学时 3 授课时间 周4第6～8节 第( 6 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、能够创建使用各种约束； 2、掌握数据操作语言的内容； 3、能熟练用企业管理器和数据操作语言对表中数据进行操作。  教学重点、难点： 1、约束的创建； 2、使用数据操作语言对表中数据进行操作  大体内容 教学方法 时间安排  约束的创建与删除 面授   数据操作语言 面授   用企业管理器和数据操作语言对表中数据进行操作 面授    面授    面授               教研室审阅意见： 教研室主任签名： 年 月 日    教学过程(基本内容) 辅助手段 备注   时间分配   四、约束（P179） 约束用于限制用户可能输入到表或字段中的值。 SQL提供的约束： 约束类型 功能  PRIMARY KEY 保证主键的实体完整性  UNIQUE 保证非主键的实体完整性  FOREIGN KEY …References 保证参照完整性  CHECK 字段完整性  DEFAULT 字段完整性  1．约束的定义 可以在创建表或修改表结构时定义约束。 创建表时定义约束，包括字段级约束（定义时紧跟在待约束的字段后，中间无逗号分隔）或表级约束（定义时放在所有字段定义的最后，前面有逗号分隔，可使用于多个字段）。 字段级约束： constraint constraint_name [null | not null][default 值 | null] [primary key | unique] [clustered | nonclustered] [foreign key references ref_table [(ref_column)]] [check (check_condition) [not for replication] ] 表级约束: constraint constraint_name [primary key | unique] [clustered | nonclustered] [foreign key(外键名) references ref_table [(ref_column)]] [check (check_condition) [not for replication] ] (字段列表) 注意：[null | not null][default 值 | null]不能用于表级约束。 修改表结构时定义/删除约束： ALTER TABLE table_name ADD 约束 (字段列表) | DROP 约束 注意：CONSTRAINT表示PRIMARY KEY、UNIQUE、FOREIGN KEY或CHECK 等约束定义的开始，用于强制数据完整性并可以为表及字段创建约束。 约束的取名在数据库内必须唯一。 2．PRIMARY KEY约束 数据库系统通过主键来保证表的实体完整性； 主键字段的取值不能重复，且不能取NULL值； 每个表只能有一个主键约束； 主键字段必须同时为NOT NULL字段； 如果表中已经存在聚簇索引，则在创建主键约束之前，必须指定创建非聚簇索引或先删除该聚簇索引。 例1：创建表级主键约束（或称索引）。 create table ex_a (xh char(9) not null,xm char(8) not null, constraint pk_index_a primary key (xh ,xm) )  例2：创建字段级主键约束。 create table ex_b (xh char(9) not null constraint pk_index_b primary key nonclustered, xm char(8) not null)  例3：为表添加主键约束。 create table ex_c (xh char(9), xm char(8)) alter table ex_c add constraint pk_index_c primary key( xh) 删除： alter table ex_c drop constraint pk_index_c  3．FOREIGN KEY约束 外键要求该列的取值必须参照其它表的主键值。外键的作用是提供了两个表之间连接方式，体现参照完整性。 一旦表中定义了某个字段的外键约束，则该字段的取值必须参照同一个表或另一个表中的主键约束或UNIQUE约束； 外键约束不能自动建立索引。 例1：创建字段级和表级外键约束。 Create table ex_d1 (xh char(9) constraint f_ex_d1 foreign key references ex_a(xh),cj int) 或： create table ex_d2 (xh char(9),cj int, constraint f_ex_d2 foreign key(xh) references ex_a(xh))  例2：添加外键约束。 alter table ex_d2 add constraint f_ex_d3 foreign key(xh) references ex_a(xh)  例3：同时创建2个外键约束，并建立表的主键约束。 create table ex_d (xh char(9) constraint f_ex_d1 foreign key references ex_a(xh), kch char(4) constraint f_ex_d2 foreign key references ex_b(kch), --创建外键约束时，要求对应的表中字段必须为主键 cj int,constraint pk_index primary key (xh,kch))  4．DEFAULT约束 每个字段只能有一个默认约束； 默认约束不能用于IDENTITY字段或TIMESTAMP字段； 创建表时不能创建表级默认约束。 语法格式：CONSTRAINT 约束名 DEFAULT 常量 FOR 字段名 例1：添加默认约束。 alter table ex_c add constraint de_xm default 'noname' for xm  例2：创建表时创建字段级默认约束。 create table ex_c (xh char(9) constraint de_a default '000',xm char(8))  5．UNIQUE约束 一个表可以有多个唯一约束 如果没有指定唯一约束索引的类型，则默认为非聚簇索引 例1：创建表级UNIQUE约束。 create table ex_c1 (xh char(9),xm char(8), constraint un_index1 unique nonclustered (xh,xm))  例2：创建字段级UNIQUE约束。 create table ex_c2 (xh char(9) constraint un_index2 unique nonclustered, xm char(8))  例3：添加唯一约束。 alter table ex_c add constraint un_index3 unique(xm)  6．CHECK约束 CHECK约束用于限制向特定字段中输入数据的范围。表级的CHECK约束还可以对多个字段列进行核查。 例1：创建表级CHECK约束。 create table ex_c1 (xh char(9),xm char(8),nl int, constraint xz1 check (nl>0 and nl<100 and xm is not null))  例2：创建字段级CHECK约束。 create table ex_c2 (xh char(9),xm char(8) constraint xz2 check (xm like ' 李%' ),nl int)  例3：添加CHECK约束。 create table ex_c3(xh char(9),xm char(8) ,nl int) alter table ex_c3 add constraint xz3 check(nl>0 and nl<100 and xm is not null)  7．删除约束 ALTER TABLE 表名 DROP 约束 例1：删除约束xz1。 alter table ex_c drop constraint xz1  五、SQL Server 2000的数据操作语言(P197) 数据操作语言（Data Manipulation Language，DML）用来在数据库中对各种数据进行操作：检索（SELECT）、插入（INSERT）、删除（DELETE）、修改（UPDATE）数据。 1、INSERT语句 向表中添加新的数据行。一次只能插入一行数据。基本语法格式： INSERT [into] table_name（column_list）VALUES（expression） expression：一个常量、变量或不包含SELECT或EXECUTE语句的表达式。 2、UPDATE语旬 更新表中的数据。使用UPDATE语句既可以一次修改一行数据也可以一次修改多行数据： UPDATE table_name SET column_name=expression，… WHERE search_condition WHERE：指定要修改的行，如无此语句表示对表中所有的行进行修改。 3、DELETE语旬 删除表中的数据行。可以一次删除一行或多行数据： DELETE [FROM] table_name WHERE search_condition： WHERE：指定要删除的行，如无此语句表示对表中所有的行进行删除。 4、SELECT语句 从表中检索数据行和列： SELECT select_list [INTO new_table] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression ［HAVING search_condition］] ［ORDER BY order_expression ［ASC | DESC］］ table source：指定用于SELECT语句的表、视图、派生表和联接表。 HAVING：通常和 GROUP BY一起使用，指定组或聚合的搜索条件。 Order_expression：指定要排序的列，可以指定多个列。 注意：ntext、text、image型的字段不能用于 ORDER BY子句 六、向表中添加记录 在student表中添加如下三条记录： sno sname ssex sage sspecial saddress  200009412 汪晓丽 女 22 计算机 上海市邯郸路220号  200108215 南键 男 21 计算机 青岛市高雄路1号  200207121 曲竹菲 女 21 会计学 厦门市屯溪路291号  1、在企业管理器中添加记录 打开企业管理器，找到所需的表，在快捷菜单中选择“打开表/返回所有行”，填入数据后能够自动进行保存。 2、使用SQL的insert语句插入记录 INSERT [INTO] <表名>|<视图名> [(<字段名1>[,<字段名2>…])] VALUES (DEFAULT | NULL | <常量1>[,<常量2>…]) | SELECT子句  [例1]： insert into score(score1,score2) values(20,30) 或 insert into score values(10,30)  [例2]：（错误：对于计算字段，不允许用户修改它的值。） insert into score values(20,30,50)  [例3]： insert into student(sno) values(99) --没有赋值的字段，值为NULL  [例4]：（关键字INTO可以省略） insert into student(sno) values(99) 或 insert student(sno) values(993)  注意：在插入记录时，如果某字段定义为NOT NULL，则其值不可缺少；如果某字段定义为Primary Key，则其值不可重复。 七、修改表中的记录 1、在企业管理器中修改记录 与添加记录的方法相同。 2、使用SQL的update语句修改 UPDATE <表名> SET <字段1>= DEFAULT | NULL |<表达式1>[,<字段2>=<表达式2>…] [WHERE <条件>]  注意：如果省略了WHERE子句，则修改所有行（记录）。 [例1]： update student set sname='abc'  [例2]： update student set sname='efg' where sno=993  [例3]：修改时可以在本字段值的基础上计算。 update student set sno=sno+1  [例4]： update student set saddress=sname+'的住址' //其中+表示字符串的连接  [例5]：（错误，不能修改计算字段。） update score set total=total+2   八、删除表中的记录 1、在企业管理器中删除记录 与添加新记录的操作过程相似。 2、使用SQL的delete语句删除 DELETE [FROM] <表名> [WHERE<条件>]  注意：当省略WHERE子句时，将删除所有记录。 [例1]： Delete from score  [例2]： delete from student where sno=994  3、删除全部记录的另一种方法 truncate table <表名> // truncate ：截短   九、删除表 将表删除后，不可以进行撤消操作，除非对数据库进行恢复。 1、在企业管理器中删除表 2、使用SQL的drop table语句删除表 DROP TABLE table_name  课堂练习： 分别用企业管理器、查询分析器在“学生管理数据库”中创建“学生专业表”、“学生专业表bak”： 学号 char(9) NOT NULL 姓名 char(9) NOT NULL 年龄 tinyint 性别 char(2) 所在院系 varchar(30) 专业 varchar(40) (2)使用企业管理器在“学生专业表”中插入一组记录，然后在所有的学号前均加上“2003”。 (3)用查询分析器在“学生专业表bak”中插入一组记录。 (4)用查询分析器在“学生专业表”中修改一组记录。 (5)用查询分析器删除“学生专业表bak”中所有记录。 (6)删除“学生专业表bak”     授课题目（章、节） 第七讲 数据库中数据表的操作（SQL查询）  学时 3 授课时间 周4第6～8节 第( 7 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、了解SQL语言的分类 2、熟练掌握SELECT语句的语法结构 3、熟练构建基于单表查询的SQL语句  教学重点、难点： 1、构建基于单表查询的SQL语句  大体内容 教学方法 时间安排  SQL语言概念 面授   SELECT语句语法结构 面授   基于单表的查询（select子句、where子句、order by子句） 面授                       教研室审阅意见： 教研室主任签名： 年 月 日   教学过程(基本内容) 辅助手段 备注   时间分配   数据库中数据表的操作（SQL查询） SQL语言（结构化查询语言）是一种在关系型数据库中定义和操纵数据的标准语言，它分为3类： 定义语言（CREATE，ALTER，DROP） 操作语言（SELECT，INSERT，UPDATE，DELETE） 控制语言（COMMIT，ROLLBACK） 查询的含义： 查询是SQL语言最核心的功能。查询就是用来描述如何从数据库中获取所需要的数据，执行查询语句后，可以返回需要查找的信息。 注意： 使用查询分析器就是通过SQL语句对SQL SERVER进行交互式的访问； 查询可以保存，供以后使用。 二、SELECT查询语句 SELECT [ ALL | DISTINCT ]<字段表达式1>[,<字段表达式2>[,…]] [INTO <新表名>] FROM <表名1>[,<表名2>[,…]] [WHERE <筛选条件表达式>] [GROUP BY <分组表达式> [HAVING <分组条件表达式>]] [ORDER BY <字段1>[,<字段2>[,…]] [ ASC | DESC ]]  SELECT语句的基本格式是由SELECT子句、FROM子句和WHERE子句（可选）组成的查询语句。其最基本的格式为：SELECT+FROM。例如：（1）要求在学生表中找出所有学生的信息；（2）要求找出所在院系为“电子学”的学生信息。 SELECT语句的功能：从指定的表中按照筛选条件找出满足条件的记录，但在结果的记录中只返回指定的几个字段。例如：（1）要求找出所有“电子学”系学生的姓名。 GROUP子句（可选）是按指定的字段（或字段表达式，如month(入学日期)）对结果记录进行分组，同一组的所有记录（该字段值相同）常合并成一条记录返回。例如：（1）要求计算出成绩表中男、女生英语各自的平均分。 HAVING只能与GROUP一同使用，表示只返回满足条件的分组。例如：（1）要求列出学生表中学生人数在10人以上的系。 SELECT语句查询的对象可以为多个表。 字段表达式、分组条件表达式中可包含集合函数。 按照下述要求建立一个“学生课程”数据库及4个表，并输入数据，以备下面使用。 学生表 学号 姓名 性别 年龄 所在院系 班级名 入学年份  200009001 葛文卿 女 22 国际贸易 国贸2班 8/29/2000  200104019 郑秀莉 女 21 会计学 会计1班 9/2/2001  200203001 刘成锴 男 18 计算机 软件2班 8/27/2002  200206001 李涛 男 19 电子学 电子1班 8/27/2002  200203002 沈香娜 女 18 计算机 软件2班 8/27/2002  200206002 李涛 男 19 电子学 电子1班 8/27/2002  200203003 肖一竹 女 19 计算机 软件2班 8/27/2002  课程表 课程号 课程名 先修课  C801 高等数学   C802 C++语言 C807  C803 数据结构 C802  C804 数据库原理 C803  C805 操作系统 C807  C806 编译原理 C803  C807 离散数学   成绩表 学号 课程号 成绩  200203001 C801 98  200203002 C804 70  200206001 C801 85  200203001 C802 99  200206002 C803 82  授课表 教师名 课程号 学时数 班级名  苏亚步 C801 72 软件2班  王立山 C802 64 软件2班  何珊 C803 72 软件2班  王立山 C804 64 软件2班  苏亚步 C801 72 电子1班   三、基于单表的查询 1．查询指定的字段 例：在学生表中查询学生的姓名和所在院系。 select 姓名,所在院系 from 学生表  例：查询所有的课程名称。 select 课程号,课程名 from 课程表  例：查询所有学生的姓名和年龄。 select 姓名,年龄 from 学生表  2．查询所有字段 查询所有字段（在输出的结果中显示所有字段的值），可以采用两种方法： 在SELECT子句中列出所有字段的名称；表示或输入时比较繁琐，但可以任意决定字段的次序。 在SELECT子句中使用通配符“*”。比较简洁，但不可以改变字段的输出次序。 例：查询全部学生选修课程的学号、课程号和成绩。 select 学号,课程号,成绩 from 成绩表  select 课程号,学号,成绩 from 成绩表  select * from 成绩表  例：查询全部学生的所有信息，要求姓名输出在最前边（第一列）。 3．基于字段的表达式 在SELECT子句中的字段表达式可以采用表达式的格式，在输出时进行计算。 例：查询每个学生的姓名和出生年份。在学生表中没有直接提供出生年份，但可以根据年龄计算：2004-年龄。（此处指“周岁”） select 姓名,2004-年龄 from 学生表  上述语句的查询结果中，第二列没有具体的标题。如果需要加标题，可以在字段表达式中加AS短语。(AS可以省略) 例： select 姓名,2004-年龄 as 出生年份 --或用’ 出生年份’、” 出生年份” from 学生表  或： select 姓名,2004-年龄 出生年份 from 学生表  例：计算课程的学分（设学分=学时数/16）。 Select 课程号,学时数/16 as 学分 from 授课表  4．使用DISTINCT短语去掉重复记录(“重复”指选出的记录信息中所选字段值均对应相同) 当表中没有定义主键时，很有可能存在重复的记录（内容完全相同的记录）。即使表中定义了主键，使用SELECT查询选择部分字段时，也有可能存在完全相同的值，如上例。 对于上例，如果要去掉重复的，可以使用语句： select distinct 姓名,2003-年龄 as 出生年份 from 学生表  例：列出学生所在的所有院系名称（不含重复内容）。 例：列出所有学生的姓名（不含重复内容）。 5．使用WHERE子句过滤记录（设置查询条件） 注意：在查询语句中，如果没有使用WHERE指定条件，则查询的对象为表中的所有记录。 SELECT <字段清单> FROM <表名> WHERE <条件表达式>  条件表达式中可以使用的运算符： 关系运算符 =，<，<=，>，>=，!=或<> 逻辑运算符 OR，AND，NOT 特殊运算符 %，_，BETWEEN…AND…，IS NULL，LIKE，IN，EXISTS NOT BETWEEN…AND…、IS NOT NULL、NOT LIKE、NOT IN、NOT EXISTS 例1：显示姓名为李涛的记录：姓名=’李涛’ 例2：所有姓李的学生：姓名 like '李%'（注：如果字符串中含有通配符%或_时，运算符号只能使用LIKE。） 例3：所有姓李且名字为两个字的学生：姓名 like '李_' 注意：‘_’代表1个字符或1个汉字。 例4：成绩在[80,90]之间：成绩 between 80 and 90（注：between应该与AND一起使用使用。）成绩不在[80,90]之间：成绩 not between 80 and 90 例5：上例也可以使用关系运算符和逻辑运算符表示：成绩>=80 and 成绩<=90 例6：Between对字符串同样有效：课程号 between 'C802' and 'C804' 例7：没有先修课的所有课程：先修课 is null 有先修课的所有课程：先修课 is not null 或：not (先修课 is null) 例8：查询高等数学,操作系统,编译原理等三门课程的信息：（注：IN相当于集合的属于运算）课程名 in ('高等数学','操作系统','编译原理') 例9：上例也可表示为：课程名='高等数学' or 课程名='操作系统' or 课程名='编译原理' 例10：查询软件2班所有女学生的信息。 例11：查询所有2002年元旦之前入学的名单。（日期使用字符串的形式表示，日期的大小可以比较。） 入学年份<’2002/1/1’ (或’2002-1-1 ’或’1-1-2002’ 或’1/1/2002’) 例12：查询年龄在19岁以下或女生的名字。 例13：查询年龄不为19岁的学生姓名。 not 年龄=19 或：年龄!=19 或：年龄<>19 例14：列出年龄不在18-20之间的学生姓名。(not between… and…) 例15：列出所有非软件2班的班级名称。( 班级名not like ‘软件2班’) 6．使用ORDER BY子句对查询结果排序 在ORDER BY子句中可以使用ASC或DESC指定排序的次序，默认为升序。 ORDER BY子句只影响查询结果的次序，但不影响原表中的记录次序。 例1：按年龄从小到大列出所有学生的信息： Select * from 学生表 order by 年龄  例2：按入学时间从晚到早列出所有学生的信息： Select * from 学生表 order by 入学年份 desc  例3：按先修课从小到大列出所有有先修课的课程名称： Select 课程名 from 课程表 where not (先修课 is null) order by 先修课 asc  例4：同时按学号(从小到大)和课程号（从大到小）的顺序列出所有学生课程成绩： Select * from 成绩表 order by 学号,课程号 desc      授课题目（章、节） 第八讲 数据库中数据表的操作（SQL查询）（续）  学时 3 授课时间 周4第6～8节 第( 8 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、能在SQL语句中熟练使用数据统计函数 2、能熟练使用GROUP BY子句对结果进行分组并筛选结果 3、能够熟练构建基于2表查询的SQL语句 4、能够构建基于3表查询的SQL语句  教学重点、难点： 1、使用GROUP BY子句对结果进行分组并筛选结果 2、构建基于2表查询的SQL语句  大体内容 教学方法 时间安排  在select语句中使用数据统计函数 面授   使用GROUP BY子句对结果分组并筛选结果 面授   基于多表的查询 面授                       教研室审阅意见： 教研室主任签名： 年 月 日  教学过程(基本内容) 辅助手段 备注   时间分配   一、数据统计函数（也称SQL的集合函数） SQL中提供了9种统计汇总的函数（集合函数，即作用于记录集合上的函数），集合函数的返回值为一个具体的值，常用的5种如下（其它4种：统计标准偏差STDEV、填充统计标准偏差STDEVP、统计方差VAR、填充方差VARP）： 函数名 功能 含义（返回值）  COUNT 统计 统计满足条件的行数（个数）  MIN 求最小值 求某字段的最小值  MAX 求最大值 求某字段的最大值  AVG 求平均值 求指定字段的算术平均值  SUM 求总和 求指定字段所有值的总和  例1：查询系部的个数： select count(所在院系) from 学生表  上例返回的结果为7，结果不对，因其中包含了重复的院系。在使用COUNT进行计数时一般同时使用DISTINCT短语： select count(distinct 所在院系) from 学生表  例2：统计19岁以上女生的人数。 select count(*) from 学生表 where 年龄>=19 and 性别='女'  或： select count(姓名) from 学生表 where 年龄>=19 and 性别='女'   注意：count(字段)和count(*)是有区别的，除非该字段的值均不为NULL。请看：统计课程表中记录的个数： select count(先修课) from 课程表 select count(*) from 课程表 例3：查询全部课程的最高分和最低分： select max(成绩) 最高分,min(成绩) 最低分 from 成绩表  例4：查询男生的年龄最大值和最小值。 例5：查询所有课程的平均分。 select avg(成绩) from 成绩表  例6：查询’C801’课程的总分。 select sum(成绩) from 成绩表 where 课程号='C801'  例7：查询所有年龄的总和。 例8：统计选修了课程的学生人数 select count(DISTINCT 学号) from 成绩表  二、使用GROUP BY子句对结果分组（或分组统计） 如果SELECT语句中包含GROUP BY，则SELECT后的字段表达式必须为以下情形之一： 集合函数表达式 与GROUP BY指定的分组字段或分组表达式（即由逗号分开的各个字段或参数为字段的函数，如month(入学年份)）相同 例1：分别求男女生的平均年龄。 select avg(年龄) 平均年龄 from 学生表 group by 性别  或： select 性别,avg(年龄) 平均年龄 from 学生表 group by 性别  例2：求所有不同的入学年份。 select 入学年份 from 学生表 group by 入学年份  例3：求每个入学年份中入学的人数。 select 入学年份,count(*) from 学生表 group by 入学年份  例4：求不相同的入学月份（不考虑年份，如2001年8月和2002年8月视为月份相同），本例中需要使用SQL的Month函数，该函数用于返回一个日期的月份（整数）。 select month(入学年份) from 学生表 group by month(入学年份)  例5：求相同月份入学的人数（不考虑年份）。 select month(入学年份),count(*) from 学生表 group by month(入学年份)  例6：求各门课程的选修人数。 三、利用HAVING筛选分组结果 HAVING用于对分组后的表进行筛选，只显示满足条件的组。HAVING与WHERE的区别： WHERE的作用对象是表；WHERE从FROM中指定的表中筛选出满足条件的记录； HAVING的作用对象是GROUP BY所产生的组；HAVING从GROUP BY的分组中筛选出满足条件的组。 例1：筛选出平均成绩在80分以上的课程及平均成绩。 select 课程号,avg(成绩) from 成绩表 group by 课程号 having avg(成绩)>=80  例2：查询选修了2门课程以上的学号和门数。 select 学号,count(课程号) from 成绩表 group by 学号 having count(课程号)>=2   四、基于多表的查询 涉及多个表的查询称为连接查询，包括： 自身连接：连接操作发生在一个表内部 等值连接：连接条件中使用=运算符(也即inner join on ) 自然连接（等值连接的特例）：在等值连接中去掉重复的字段(也即inner join on) 非等值连接：连接条件中使用除=以外的运算符 *交叉连接（cross join）：不加WHERE连接条件时包括2个表的笛卡儿积.；当加上WHERE连接条件时等价于inner join on *外连接：既包括满足条件的记录也包括不满足条件的记录（如包含左表中的全部记录及满足条件的记录（left outer join on）、包含右表中的全部记录及满足条件的记录(right outer join on)、包含左右表中的全部记录及满足条件的记录(full outer join on)）。如我班（1班）组织旅游，参加的人员为：1班全体同学及与某些同学关系极好的2班同学。 复合条件连接：多个条件组合在一起 1．多表查询连接的条件 基本格式：[<表名1>.]<字段名1> <运算符> [<表名2>.]<字段名2>[…] 表名可用别名代替。 运算符： 比较运算符=,>,>=,<,<=,!= 逻辑运算符NOT,AND,OR BETWEEN…AND等 2．自然连接 例1：列出学生选修课程的情况（等值连接） select 学生表.*,成绩表.* from 学生表,成绩表 where 学生表.学号=成绩表.学号  例2：在上例中去掉重复的字段（学号）(自然连接) select 学生表.学号,姓名,性别,年龄,所在院系,班级名,入学年份,课程号,成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号   注意：若某一字段在2表中同时出现，则引用该字段时必须用:“表名.字段”预以指明；若某一字段在2表中是唯一的，则可用：“字段”直接引用。 例3：同例2，列出学生选修课程的情况，但只显示姓名、课程号、成绩字段。 3．自身连接（板书！） 连接操作发生在一个表内部。 例1：列出所有课程课程号、课程名、先修课及先修课程的名称。 select a.课程号,a.课程名,a.先修课,b.课程名 from 课程表 a,课程表 b where a.先修课=b.课程号  例2：列出所有课程的间接先修课程号。 select a.课程名,b.先修课 间接先修课 from 课程表 a,课程表 b where a.先修课=b.课程号  例3：列出所有课程的间接先修课课程名称。 select a.课程名,b.先修课 间接先修课,c.课程名 间接先修课课程名称 from 课程表 a,课程表 b,课程表 c where a.先修课=b.课程号 and b.先修课=c.课程号  4．复合条件连接 例1：列出选修C801课程且成绩在90分以上的所有学生。 Select 学生表.学号,姓名 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 课程号='C801' and 成绩>=90  例2：列出所有选修了课程的学生姓名和课程名称。 select 姓名,课程名 from 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 成绩表.课程号=课程表.课程号  例3*：列出所有学生的学号、姓名和任课教师。 select 学生表.学号,姓名,教师名 from 学生表,成绩表,授课表 where 学生表.学号=成绩表.学号 and 成绩表.课程号=授课表.课程号 and 学生表.班级名=授课表.班级名   注意：若2表连接时不能用单一的“外键—主键”（即无主键或多主键时）连接，则需再加一条件，使多主键字段中的每个字段均有连接条件（无主键时连接用的几个字段能构成表面上的多主键）。如上例中的“授课表.班级名”不是主键，但授课表中的“课程号+班级名”即能构成表面上的多主键。此时授课表中的“课程号”、“班级名”均需与学生表中的字段连接，若某些字段不能直接连接（如授课表中有“课程号”，而学生表中无），则加上中间表“成绩表”即可。    授课题目（章、节） 第九讲 数据库中数据表的操作（SQL查询）（续）  学时 3 授课时间 周4第6～8节 第( 9 )次授课  主要参考书 《SQL Server 数据库原理及应用教程》，清华大学出版社  教学目的与要求： 1、掌握嵌套查询的工作原理 2、熟练掌握IN、ANY、ALL子查询及INTO子句的使用方法 3、了解EXISTS子查询的使用方法 掌握UNION查询、带嵌套查询的数据更新的使用方法  教学重点、难点： 1、IN、ANY、ALL子查询 2、带嵌套查询的数据更新  大体内容 教学方法 时间安排  嵌套查询 面授   UNION查询 面授   INTO子句 面授   带嵌套查询的数据更新 面授                   教研室审阅意见： 教研室主任签名： 年 月 日  教学过程(基本内容) 辅助手段 备注   时间分配   一、嵌套查询 一个SELECT查询可以放置在另一查询的WHERE子句中，称为嵌套查询。 外层查询（父查询）：最上层的查询块 子查询（内层查询）：父查询WHERE子句中的查询块 求解过程：由外向内处理 例1：查询沈香娜所在院系的所有学生姓名。 select *from 学生表 where 所在院系=(select 所在院系from 学生表 where 姓名='沈香娜')  例2：当WHERE的条件运算符为关系比较符时，要求子查询返回的值应是1个具体的值，而不是几个值。如以下查询是错误的： select * from 学生表 where 所在院系=(select 所在院系from 学生表 where 姓名='李涛') --返回了2条记录，不能比较！  例3：查询何珊所教班级（假设只有1个班）的所有任课教师的姓名。 select distinct 教师名 from 授课表where 班级名=(select 班级名from 授课表 where 教师名='何珊')  （1）带有IN的子查询 如果子查询返回的是1个以上的值，则不能使用关系比较符，可以使用IN进行集合运算。如上述例2中用IN就正确了： select 姓名 from 学生表where 所在院系 in (select 所在院系from 学生表where 姓名='李涛')  以下IN不是子查询： 例1：列出选修了C801和C804课程的学生姓名。 select 姓名 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 成绩表.课程号 in ('C801','C804')  例2：列出选修了C801和C804的学生姓名、课程名和成绩。 select 姓名,课程名,成绩 from 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 成绩表.课程号=课程表.课程号 and 成绩表.课程号 in ('C801','C804')  以下IN才是子查询： 例3*：列出选修了高等数学的学生学号和姓名。 select 学号,姓名from 学生表where 学号 in (select 学号 from 成绩表,课程表 where 课程表.课程号 =成绩表.课程号 and 课程名='高等数学' ) 或： select 学生表.学号,姓名 from 学生表, 成绩表,课程表 where 课程表.课程号 =成绩表.课程号 and 学生表.学号=成绩表.学号 and 课程名='高等数学'  （2）带有ANY或ALL的子查询 ANY即某一个（大于时可用min改写，小于时可用max改写），ALL即所有的（大于时可用max改写，小于时可用min改写）。它们前面必须要有比较运算符。 例1：列出所有比计算机系某个学生年龄大的学生姓名。 select 姓名from 学生表where 年龄>any(select 年龄 from 学生表where 所在院系='计算机') and 所在院系<>'计算机'  例2：上例等价于通过集合函数min改写： select 姓名from 学生表 where 年龄>(select min(年龄)from 学生表 where 所在院系='计算机') and 所在院系<>'计算机'  例3：列出所有院系中比计算机系学生年龄都大的学生姓名。 select 姓名 from 学生表 where 年龄>all(select 年龄 from 学生表where 所在院系='计算机') and 所在院系<>'计算机'  例4：使用集合函数max改写上例。 例5：列出所有 成绩大于学号为200206002的学生成绩的学生姓名。 select 姓名 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 成绩表.成绩>all(select 成绩 from 成绩表 where 学号='200206002')  例6：列出所有 成绩大于学号为200206002的学生成绩的学生姓名、课程名和成绩。 select 姓名,课程名,成绩 from 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 课程表.课程号=成绩表.课程号 and 成绩表.成绩>all(select 成绩 from 成绩表 where 学号='200206002')  （3）*带有EXISTS的子查询 EXISTS代表存在，EXISTS之后的子查询只查找满足条件的第一条记录，如果找到则立即停止。它只返回找到或未找到（逻辑值：真-找到/假-未找到），不返回具体的数据。 执行过程：对外查询的每一条记录，根据它与子查询相关的字段均执行一次子查询来测试是否为真，若为真，则将此记录放入结果表中。 例1：列出所有选修了C801课程的学生姓名。 select 姓名from 学生表 where exists(select * from 成绩表 where 学生表.学号=成绩表.学号 and 课程号='C801') 或： select 姓名from 学生表 where 学号 in(select 学号 from 成绩表 where 课程号='C801' )  例2：列出所有没有选修C801课程的学生姓名。 Select 姓名from 学生表 where not exists(select * from 成绩表 where学生表.学号=成绩表.学号' and 课程号='C801) )  （4）在子查询中使用集合函数 例1：列出年龄最大的学生姓名。 select 姓名 from 学生表 where 年龄=(select max(年龄) --也可用年龄>=all(…) from 学生表)  注意：若用“where 年龄=max(年龄)”，则错误。 例2：按年龄降序列出所有超过平均年龄的学生姓名和年龄。 select 姓名,年龄 from 学生表 where 年龄>(select avg(年龄) from 学生表) order by 年龄 desc       教学过程(基本内容) 辅助手段 备注   时间分配   二、UNION查询 UNION可以将两个查询结果进行“集合并”操作（将查询的结果记录相加）。UNION要求两个查询结果中含有相同的字段个数、排列顺序、数据类型，它能自动去掉重复的记录。如果均在同样的表上进行则可用or改写，否则不行。 例1：查询所有女生以及计算机系所有学生的信息。 select * from 学生表 where 性别='女' union select * from 学生表 where 所在院系='计算机'  例2：上例也可以通过OR运算表示查询条件，而本例则只能通过UNION实现：合并学生表和成绩表中的学号。 select 学号 from 学生表 union select 学号 from 成绩表  例3：列出讲授C801或C803课程的所有教师名、课程号和班级名。 Select 教师名,课程号,班级名 from 授课表 where 课程号='C801' union select 教师名,课程号,班级名 from 授课表 where 课程号='C803'  例4：列出讲授C801或C803课程的所有教师名、课程名和班级名。 Select 教师名,课程名,班级名 from 授课表 a,课程表 b where a.课程号=b.课程号 and a.课程号='C801' union select 教师名,课程名,班级名 from 授课表 a,课程表 b where a.课程号=b.课程号 and a.课程号='C803'  三、INTO子句 使用INTO子句可以将查询的结果保存到一个表中，而不显示在屏幕上（自动创建一个表—原来不存在）。 格式： SELECT <字段列表> [INTO <新表名>] FROM <表名> [WHERE<条件>]  如果要创建临时表，可以在INTO子句中表名之前加#或##。 新表的结构由“ <字段列表>”决定。 新表的结果由SELECT的查询结果确定。 例1：创建计算机系学生表。 select * --可用“学号 number，姓名 name”更改字段名 into 计算机系学生 from 学生表 where 所在院系='计算机'  注意：请通过SELECT语句或企业管理器查看“计算机系学生”表的结构和内容。 如果在查询语句中包含UNION和INTO子句，则要求INTO子句应放置在第一个SELECT语句中。 例2：新建一个表，用于存放计算机和国际贸易系的所有学生名单。 select * into 学生分类表 from 学生表 where 所在院系='计算机' union select * from 学生表 where 所在院系='国际贸易'  例3：使用INTO子句创建空表—只含有表的结构（当WHERE子句中的条件得不到满足时，只创建一个空表，而不含有任何记录数据）。创建一个不含有记录的学生表。 select * into 空学生表 from 学生表 where 所在院系='泰州系'  例4：将上例的表创建为临时表（在表名之间加#或##）。引用时仍加上#或##.。 注意：退出SQL Server后，临时表自动消失。 四、数据更新（此处讲解高级操作） 插入记录 删除记录 修改记录 1．插入子查询结果（可一次性插入多条记录） INSERT INTO <表名> [(<字段1>[,<字段2>...])] SELECT [(<字段A>[,<字段B>...])] FROM <表名> [WHERE<条件表达式>]  注意：向表中插入记录时，SELECT中源字段次序、个数应该与目标字段一致，且类型兼容。 例1：创建一个“新学生表”，要求包括4个字段：学生学号，学生姓名，学生性别和所在院系。再将所有计算机系或所有女学生的记录插入到该表中。 create table 新学生表 ( 学生学号 char(9) not null primary key, 学生姓名 char(8), 学生性别 char(2), 所在院系 char(10) ) insert into 新学生表 select 学号,姓名,性别,所在院系 from 学生表 where 所在院系='计算机' or 性别='女' select * from 新学生表  2．带子查询的修改语句 例1：将电子学系所有学生的成绩加8分。 update 成绩表 set 成绩=成绩+8 where 学号 in(select 学号 from 学生表 where 所在院系='电子学') select * from 成绩表 或： update 成绩表 set 成绩=成绩+8 where exists(select 所在院系 from 学生表 where 学生表.学号=成绩表.学号 and 所在院系='电子学') 或： update 成绩表 set 成绩=成绩+8 where '电子学'=(select 所在院系 from 学生表 where 学生表.学号=成绩表.学号)  3．带子查询的删除语句 例1：删除电子学系所有学生的选修记录。 delete from 成绩表 where学号 in(select 学号 from 学生表 where 所在院系='电子学')  练习： 编写一条[SELECT]语句，从[学生表]中查询[学号]和[姓名]。 编写一条[SELECT]语句，要求从[学生表]中检索学号为[200108011]学生的年龄。 要求使用[BETWEEN]短语的[SELECT]语句，罗列年龄在18~20岁之间的男同学的详细信息。 罗列出所有姓“李”的学生清单。 列出学生的姓名，重名的记录自动去掉。 显示“电子系”的学生以及性别为“男”的学生名单。 列出“计算机系”和“电子学系”学生的学号、姓名和所在院系。 统计[学生表]中一共有多少名20岁以上的女同学。 求出所有女学生的平均年龄。 查找年龄最大和最小的男学生年龄。 新建一个表名为[学生分类表]，内容包括计算机系和国际贸易系的所有学生名单。 创建一个临时表，并且显示其内容。 创建一个[简单学生表]，要求：包含4个字段“学生学号、学生姓名、学生性别和所在院系”，然后将[学生表]中“国际贸易”系或者女学生的记录全部插入到[简单学生表]中，最后显示插入的记录。 删除“计算机系”所有学生的选修课程记录。 列出所有没有选修课程“C804”的学生清单。 计算每个学生的选修课门数。 计算每个班学生的平均年龄。 计算每个班的最小年龄。 建一新表，包括姓名、课程、成绩。 删除新表中成绩低于70分的记录。