数据库原理及应用实训指导书

数据库原理及应用数据库原理及应用 实训指导书实训指导书 经济管理学院信息管理教研室 2007 年 2 月 前 言 数据库技术是计算机科学技术中发展最快的领域之一 也是应用范围最 广 实用性很强的技术之一 它已成为信息社会的核心技术和重要基础 数据 库原理及应用 是经济管理学院大部分专业的必修课程 其主要目的是使学生 在较好掌握数据库系统原理的基础上 熟练掌握主流数据库管理系统 如 SQL Server 2000 的应用技术 并利用常用的数据库应用系统开发工具 如 VB Delphi PB NET 平台等 进行数据库应用系统的设计与开发 在 Internet 高速发展的信息化时代 信息资源的经济价值和社会价值越来 越明显 建设以数据库为核心的各类信息系统 对提高企业的竞争力与效益 改善部门的管理能力与管理水平 均具有实实在在的重要意义 本实训指导书 能合理安排课程实训内容 引导读者逐步掌握数据库应用的各种技术 为数据 库应用系统的设计与开发打好基础 本实训指导书是经济管理学院信息管理教研室在从事数据库课程教学的 基础上 为满足 数据库原理及应用 课程的教学需要 配合选用 数据库原理 及应用 陆慧娟编著 科学出版社 教材而编写的 全书共分为十二个实训内 容 其中 非信管专业只做前 6 个实训项目 信管专业要完成前十个实训项目 后 2 个为选做实训项目 适合非信管专业 14 学时的实训教学和信管专业 30 学时的教学 本实训指导书共有 20 多个实训目的 基本覆盖了数据库与原理 课程的主要内容 实训内容在安排上由浅入深 从验证观查到自己着手编写程序 步步引导 学生迈进数据库的大门 每个实训内容分为 实训目的 实训要求 实训课时 实训内容与步骤 等环节 个别实训还配有 实训练习 供学生在课下或实训 完成较快的学生在课上做 本书中的全部代码均在 SQL Server2000 个人版上调试通过 由于时间仓促 编者水平有限 书中难免有错误 疏漏和欠妥之处 敬请 广大读者与同行专家批评指正 问题反馈与探讨 fujl 2008 编者 2007 年 2 月 目 录 前前 言言 实训一实训一 数据库的创建与管理数据库的创建与管理 1 实训二实训二 基本表的创建与管理基本表的创建与管理 9 实训三实训三 数据更新操作数据更新操作 17 实训四实训四 数据表的简单查询数据表的简单查询 21 实训五实训五 数据表的高级查询数据表的高级查询 26 实训六实训六 数据完整性约束数据完整性约束 30 实训七实训七 视图的应用视图的应用 32 实训八实训八 存储过程与触发器存储过程与触发器 34 实训九实训九 数据库设计数据库设计 40 实训十实训十 数据库应用程序开发数据库应用程序开发 43 实训十一实训十一 用户管理和权限管理用户管理和权限管理 44 实训十二实训十二 数据的传输操作数据的传输操作 47 注 带 号的为选做实训内容 1 实训一 数据库的创建与管理 实训目的实训目的 1 掌握使用企业管理器创建 修改与删除数据库的方法 2 掌握使用 SQL 语句创建 修改与删除数据库的方法 实训要求实训要求 1 在 F 盘新建 sy1 和 sy2 两个文件夹 2 注意观察企业管理器中所包含的内容 3 注意查询分析器的启动及使用的方法 4 每操作完一步要求正确 边运行边查看结果 实训学时实训学时 2 学时 实训教师可以根据学生实际情况进行适当的调整 也可要求学生在课下完成 部分实训任务 实训内容与步骤实训内容与步骤 一 使用企业管理器建立数据库一 使用企业管理器建立数据库 1 实训内容 根据需要建立一个学生的数据库 XSDB 数据文件及日志文件都建立在 F 盘的 sy1 的文件夹下 数据文件大小为 2MB 最大为 4MB 每次文件增大 1MB 日志文件 初始大小为 2MB 最大为 40MB 每次增加 1MB 2 实训步骤 1 打开 企业管理器 操作步骤 开始 程序 Microsoft SQL Server 企业管理器 注意观察企业管理的 控制台中所包含的内容 2 建立数据库 操作步骤 在控制台中找到数据库项 右击此项后 在弹出菜单中选择 新建数据 库 B 项 如图 1 1 所示 小知识 小知识 一般情况下 在菜单项或按钮中如出现 表明要打开一个新窗口 3 输入数据库的名字 在打开的 数据库属性 对话框中 选择 常规 选项卡 设置数据库的名字为 XSDB 如图 1 2 所示 4 设置数据库数据文件及日志文件的属性 选择 数据文件 选项卡 根据实训要求依次设置数据库文件的初始大小 文件增 长方式和文件的最大尺寸限制 如图 1 3 所示 2 选择 事务日志 选项卡 根据实训要求依次设置日志文件的初始大小 文件增长 方式和文件的最大尺寸限制 如图 1 4 所示 图 1 1 新建数据库 图 1 2 新建数据库属性 常规选项设置 3 图 1 3 新建数据库属性 数据文件选项设置 图 1 4 新建数据库属性 事务日志选项设置 4 5 设置完新建数据库的所有内容后 点击 确定 按钮 一个名为 XSDB 的 数据库就创建完毕了 你可以观察一下控制台数据库选项下的变化及企业管理器右侧项 目列表中的内容变化情况 二 使用企业管理器修改数据库二 使用企业管理器修改数据库 1 实训内容 修改刚才新建的数据库 XSDB 把数据文件的初始大小改为 4M 文件增长限制改 为 8M 日志文件的初始大小改为 2M 文件增长限制改为 20M 2 实训步骤 在企业管理器中打开服务器中的 数据库 项 右击名为 XSDB 的数据库 在弹 出菜单中选择 属性 项 出现如图 1 5 所示的对话框 分别选择 数据文件 和 事 务日志 选项卡 按实训内容进行相应的修改 图 1 5 修改数据库 XKDB 的属性 三 使用企业管理器删除数据库三 使用企业管理器删除数据库 1 实训内容 将刚才创建的数据库删除 2 实训步骤 在企业管理器中打开服务器中的 数据库 项 右击名为 XSDB 的数据库 在弹 出菜单中选择 删除 项 出现如图 1 6 所示的对话框 点击 是 则删除了名为 5 XSDB 的数据库 对话框中的复选框可以选择是否同时删除数据库备份以及恢复历史 图 1 6 删除数据库的确认对话框 四 使用四 使用 SQL 语句在查询分析器中建立数据库语句在查询分析器中建立数据库 1 实训内容 使用查询分析器建立名为 XSDB 的数据库 数据文件及日志文件都建立在 F 盘的 sy2 文件夹下 数据文件大小为 2MB 最大为 4MB 每次文件增大 1MB 日志文件初 始大小为 2MB 最大为 40MB 每次增加 1MB 2 实训步骤 1 打开 查询分析器 操作步骤 开始 程序 Microsoft SQL Server 查询分析器 2 登录服务器 界面如图 1 7 所示 使用相应的用户名和密码登录 实训室的用 户名 as 密码为空 图 1 7 登录服务器 小知识小知识 在图1 7中的SQL Server中填入 或 local 都是连接的本地服务器 即本台机器上安装的SQL Server 6 3 创建数据库 创建名为 XSDB 的数据库 在查询分析器中输入如下语句 注注 意 在意 在 SQL 语句中标点符号使用的是英文半角字符语句中标点符号使用的是英文半角字符 Use Master GO CREATE DATABASE XSDB ON NAME XSDB data FILENAME F sy2 XSDB mdf SIZE 2 MAXSIZE 4 FILEGROWTH 1 LOG ON NAME XSDB log FILENAME F sy2 XSDB LDF SIZE 2MB MAXSIZE 40MB FILEGROWTH 1MB GO USE XSDB 选择所使用的数据库 今后在使用前首先要选择相应的数据库 GO 4 运行代码创建数据库 点击绿色三角形图标运行全部代码全部代码 检查是否有错误 发生 并进行改正 注意 注意 点击保存按钮 可以将输入的 SQL 语句保存下来 备今后使用 五 使用五 使用 SQL 语句在查询分析器中修改数据库语句在查询分析器中修改数据库 1 实训内容 请使用 SQL 语句把刚才建立的 XSDB 数据库的数据文件的初始大小改为 4M 文件 增长限制改为 8M 日志文件的初始大小改为 2M 文件增长限制改为 20M 2 实训步骤 在查询分析器中输入如下代码 并运行 USE master GO ALTER DATABASE XSDB MODIFY FILE NAME XSDB data SIZE 4MB MAXSIZE 8MB GO 7 ALTER DATABASE XSDB MODIFY FILE NAME XSDB log SIZE 2MB MAXSIZE 20MB GO 思考问题 思考问题 用 SQL 语句对数据库进行修改时 指定文件的大小是否可以小于原 来的大小 六 使用六 使用 SQL 语句在查询分析器中删除数据库语句在查询分析器中删除数据库 1 除去单个数据库 DROP DATABASE XSDB 2 除去多个数据库 DROP DATABASE pubs XKDB 可以不做 只做了解 8 实训练习实训练习 使用查询分析器建立数据库 dinghuo 并把相关数据库数据文件和日志文件放在 F sy1 中 数据文件初始大小为 3MB 每次增长 5MB 最大为 100MB 日志文件初始大 小为 4MB 每次增长 2MB 最大无限制 并将脚本文件保存在 F sy1 文件夹下 名称为 LX1 sql 9 实训二 基本表的创建与管理 实训目的实训目的 1 掌握使用企业管理器创建 修改与删除基本表的方法 2 掌握使用 SQL 语句创建 修改与删除基本表的方法 3 掌握在两种环境下设置数据约束的方法 实训要求实训要求 1 实训前进行相关知识的预习 2 了解企业管理器及查询分析器中常用菜单及工具图标的用途 实训学时实训学时 2 学时 实训教师可以根据学生实际情况进行适当的调整 也可要求学生在课下完成 部分实训任务 实训内容与步骤实训内容与步骤 实训内容实训内容 根据实训需要建立三个基本表 即学生表 Student 课程表 Cource 学生课程 成绩表 SC 经过调查后各个表的字段如表 2 1 至 2 3 所示 表 2 1 学生关系 student 结构 列名说明数据类型约束 Sno学号字符串 长度为 10主码 Sname姓名字符串 长度为 10非空 Ssex性别字符串 长度为 2取 男 或 女 Sage年龄整数取值 15 45 Sdept所在系字符串 长度为 20默认为 管理系 表 2 2 课程关系 cource 结构 列名说明数据类型约束 Cno课程号字符串 长度为 4主码 Cname课程名字符串 长度为 20非空 Cxf学分整数取值大于 0 Cxq学期整数取值大于 0 Cxs学时整数取值大于 0 表 2 3 学生课程成绩关系 SC 结构 列名说明数据类型约束 Sno学号字符串 长度为 10主码 引用 Student 的 SNO Cno课程号字符串 长度为 4主码 引用 Course 的 CNO grade成绩整数取值 0 100 10 一 在企业管理器中实现一 在企业管理器中实现 1 关系表的定义 关系表的定义 使用 SQL Server 的企业管理器创建新表的步骤如下 以建立表 2 1 为例 1 打开服务器组 然后打开服务器 2 打开 数据库 文件夹 再展开 XSDB 数据库 3 右击 表 从弹出的快捷菜单中单击 新建表 项 进入如图 2 1 所示的窗 口 4 窗口分为上下两个设置栏 上面的设置栏 可以在 列名 数据类型 长度 和 允许空 四个栏目内分别按表 2 1 中的说明填入相应属性值 根据数据类 型的不同 在下面的设置栏 可以更改 默认值 等选项 具体可参见图 2 1 5 设置主键 鼠标定位到第一行 单击 设置主键 工具图标 则给第一行的 属性设置了主键 设置主键的行会在该行左侧出现一个小钥匙的图标进行标识 具体参 见图 2 1 6 设置表的约束 单击 设置约束 工具图标 则会弹出一个 属性 对话框 选择 Check 约束 选项卡 点击 新建 则会建立一个约束 在 约束表达式 中输 入 ssex 男 or ssex 女 则给 Ssex 字段添加了一个约束 限制该字段中的内容只 能取 男 和 女 两个值 再点击 新建 按钮 并在 约束表达式 中输入 Sage 15 and Sage 15 and sage0 Cxq tinyint CHECK Cxq 0 Cxs int PRIMARY KEY Cno CREATE TABLE SC Sno char 10 NOT NULL Cno char 10 NOT NULL Grade tinyint CHECK Grade 0 AND Grade 100 PRIMARY KEY Sno Cno 14 FOREIGN KEY Sno REFERENCES Student Sno FOREIGN KEY Cno REFERENCES Course Cno 运行代码 如有错误 检查后 请改正 2 修改表结构 修改表结构 ALTER TABLE 语句可以对表添加列 删除列和修改列的定义 定义主码和外码 也可以添加和删除约束 ALTER TABLE 的语句格式如下 ALTER TABLE alter column add column 约束 drop column add primary key 列名 n add foreign key 列名 references 表名 列名 add unique 在查询分析器中实现以下实训项目 a 对 SC 表中添加 选课类别 列 列名为 XKLB 字符串 长度为 4 ALTER TABLE SC Add XKLB char 4 null b 将新添加的 XKLB 的类型改为 char 15 Alter table sc Alter column XKLB char 15 c 删除 SC 中的 XKLB 列 Alter table sc Drop column XKLB 运行并查看结果 在输入完每个小例子的时候都要看一下 SC 表结构是否相应的修 改 注意 要进行适当的刷新 如图 2 7 所示 15 图 2 7 修改表结构及结果显示 小知识 小知识 对于SQL server 2000的查询分析器 将要执行的代码选定后 如图2 7 再点击运行按钮就可以运行所选定的代码了 3 删除表 删除表 当数据库中的某些表不再使用时 可以将其删除 以释放数据库空间 节省存储介 质 如果使用 SQL 语句 其格式如下 DROP TABLE 如果要删除 SC 表 其语句格式如下 DROP TABLE SC 16 实训练习实训练习 在实训一中建立的 dinghuo 数据库中建立两个表 分别是顾客表和订购表 顾客表 顾客 ID 姓名 电话 地址 其中顾客 ID 是主码 gk gkid gkxm gkdh gkdz 订购表 订购 ID 商品名称 顾客 ID 订购数量 订货日期 交货日期 其中顾 客 ID 参照顾客表中的顾客 ID dh dhid dhmc gkid dhsl dhrq jhrq 要求 要求 1 建立以上两个表结构的 SQL 语句 完成相应的要求 SQL 语句保存在 F sy 文件下名称为 LX2 sql 2 修改表结构当顾客没有提供地址值时 使用默认值 UNKNOWN 3 限制订购表中的 订购数量 必须大于 0 4 限制订购表的 订货日期 必须早于 交货日期 17 实训三 数据更新操作 实训目的实训目的 1 掌握使用 SQL 语言向表中插入数据 修改数据和删除数据的方法 2 掌握在 SQL Server 中进行数据更新的方法 实训要求实训要求 1 使用查询分析器完成以下各种操作 2 注意查询分析器所提供的其他功能 3 对实训的 SQL 语句进行统一保存 以便日后使用 实训学时实训学时 2 学时 实训教师可以根据学生实际情况进行适当的调整 也可要求学生在课下完成 部分实训任务 实训内容与步骤实训内容与步骤 说明 本实训使用信息管理与信息系统实训室机器中已安装好的数据库 XKDB 数 据库中存有与实训二中相同的数据表 一 插入数据一 插入数据 在 SQL 语言中使用 INSERT 语句在表中添加新数据 INSERT 语句的语法格式为 INSERT INTO VALUES 值列表 其中 列名列表中的列名必须是表定义中的列名 值列表 中的值可以是常量也 可以是 NULL 值 各值之间用逗号分隔 动手做 动手做 将基本信息为 陈冬 男 会计系 23 的学生插入到学生表中 新加一门课程 只知道课程编号和课程名称 C112 矿业运筹学 思考问题 思考问题 如果在 SC 表中插入一条 80 的记录 是否能执行成功 如果出 现问题 说明为什么 注意 注意 请接着做完本实训中的实训习题部分 请将你个人的信息插入到学生表中 请将一卡通号的前两位去掉 请将学号为 姓名为 王倩 性别 女 年龄 21 院系为 管理学院 的学生信息插 入到学生表中 18 二 修改数据二 修改数据 在 SQL 语句中使用 UPDATE 语句对数据进行修改 UPDATE 语句的语法格式为 UPDATE SET n WHERE 1 无条件更新 无条件更新 无条件更新是将所有的值进行相应的变化 即修改表中的所有数据行 动手做 动手做 将所有学生的系别都改为 信息系 将所有学生的年龄都加 1 请将所有学生的成绩提高 5 分 请将所有课程的学时增加 10 学时 2 有条件更新 有条件更新 根据某种条件有选择性的进行修改 在 where 子句中指定更改数据的条件 1 基于本表条件的更新 将不及格的学生的成绩改为 55 UPDATE SC SET GRADE 55 WHERE GRADE 60 将学号为 学生的年龄改为 19 UPDATE SUTDENT SET SAGE 19 WHERE SNO 将 的姓名改为 何洁 同时将年龄改为 23 UPDATE STUDENT SET SNAME 何洁 SAGE 23 WHERE SNO 动手做 动手做 请将你个人的年龄增加 1 岁 请将管理系学生年龄大于 24 岁的学生的年龄减 1 岁 请将第 3 学期学时少于 40 学时的课程的学时改为 45 学时 2 基于其它表条件的更新 将管理系全体学生成绩都加 5 分 UPDATE SC SET GRADE GRADE 5 WHERE SNO IN SELECT SNO FROM STUDENT WHERE SDEPT 管理系 19 将年龄小于 21 岁的学生成绩加 1 分 UPDATE SC SET GRADE GRADE 1 WHERE SNO IN SELECT SNO FROM STUDENT HERE SAGE 21 动手做 动手做 请将选修 数据库原理与应用 这门课程的学生成绩增加 5 分 将信息系学生姓名中含有 丁 字的学生成绩改为 67 三 删除数据三 删除数据 DELETE 语句的语法格式为 DELETE FROM WHERE 1 有条件删除 有条件删除 1 基于本表条件的删除 删除年龄大于 22 岁的学生 DELETE STUDENT WHERE SAGE 22 动手做 动手做 请将学时少于 20 学时的课程信息删除 请将选课表 SC 中学生成绩为空的信息删除 2 基于其它表条件的删除 删除只要成绩小于 60 的学生的信息 DELETE STUDENT WHERE SNO IN SELECT SNO FROM SC WHERE GRADE 60 思考问题 思考问题 能不能执行成功 为什么 如果想删除该学生应如何操作 动手做 动手做 请将学时少于 20 学时的学生选课信息删除 删除采矿系学生的选课信息 请将选修了会计学基础这门课程的数力系的男生信息删除 2 无条件删除 无条件删除 删除 SC 表中所有的选课记录 DELETE FROM SC 或 DELETE SC 20 实训练习实训练习 因今后实训需要向表中插入以下数据 注意要保存 SQL 语句 名为 LX3 sql 1 将下列数据插入到 student 表中 SnoSnameSsexsageSdept 李勇男19管理系 王敏女20管理系 张立男20经济系 吴宾男22管理系 张海女21会计系 李奇男23机械系 王大力男20会计系 2 将下列数据插入到 course 表中 CnoCnameccreditssemesterperiod 数据库原理及应用35 管理学原理42 计算机网络3 数据结构45 系统工程24 DB 练习册 DB 作业本 程序设计语言4 3 将下列数据插入到 SC 表中 SnoCnoGrade 30 45 59 84 80 84 65 91 69 71 78 21 实训四 数据表的简单查询 实训目的实训目的 1 掌握 SELECT 语句的基本使用方法 2 掌握 WHERE 子句的运用方法 3 掌握通配符及排序查询的方法 实训要求实训要求 1 使用查询分析器完成以下各种操作 2 及时保存 SQL 语句 实训结束后 将语句传至服务器 3 本部分实训内容使用机器中的 XKDB 数据库进行操作 实训学时实训学时 4 学时 实训教师可以根据学生实际情况进行适当的调整 也可要求学生在课下完成 部分实训任务 实训内容与步骤实训内容与步骤 注意 注意 本部分实训内容使用机器中的 XKDB 数据库进行操作 SELECT 语句的格式 SELECT INTO 新的表 FROM WHERE GROUP BY 分组依据列 HAVING 组提取条件 ORDER BY 排序依据列 ASC DESC 一 选择表中若干列一 选择表中若干列 通过 SELECT 子句的中指定要查询的列来实现本功能 1 查询指定的列 查询指定的列 查询全体学生的学号和姓名 SELECT SNO SNAME FROM STUDENT 查询全体学生的姓名 学号和所在系 SELECT SNAME SNO SDEPT FROM STUDENT 查询所有选课学生的学号 思考为什么会有重复思考为什么会有重复 SELECT SNO FROM SC 思考问题思考问题 执行完 操作后 观察结果集有什么现象发生 为什么 22 动手做 动手做 查询所有课程的课程编号和课程名称 查询所有学生的姓名 院系和年龄 小知识 小知识 在指定列查询过程中 所指定列的顺序可以与表中定义的顺序不一致 2 查询全部的列 查询全部的列 如果要查询表中的全部列 可以使用两种方法 一种是在中列出所 有的列名 另一种方法是简单在中写星号 查询全体学生的信息 使用第一种方法 即中列出所有的列名 SELECT SNO SNAME SSEX SAGE SDEPT FROM STUDENT 使用第二种方法 即用星号 来代替 SELECT FROM STUDENT 动手做 动手做 查询选课表中的所有信息 查询课程表中的全部信息 3 查询经过计算的列 查询经过计算的列 查询全体学生的姓名及出生年份 提示 本年度 2006 SAGE 即为出生年份 SELECT SNAME 2006 SAGE FROM STUDENT 查询全体学生的姓名和出生年份 并在年份前加一个列 此列的每行数据均为 出生年份是 常量值 SELECT SNAME 出生年份是 2006 SAGE FROM STUDENT 注意 注意 选择列表中的常量和计算是对表中的每行进行的 改变列标题的语法格式为 列名 表达式 AS 列标题 或 列标题 列名 表达式 则查询 可写为 SELECT SNAME 2004 SAGE 出生年份 FROM STUDENT 或 SELECT SNAME 2006 SAGE AS 出生年份 FROM STUDENT 查询 可写为 SELECT SNAME 出生年份是 AS 常量字段 2006 SAGE AS 出生年份 23 FROM STUDENT 二 选择表中的若干元组二 选择表中的若干元组 1 DISTINCT 和和 TOP 的用法的用法 消除取值重复行的方法是使用 DISTINCT 语句 也可以使用 TOP n 返回结果中的 前 n 行数据 查询有选课的学生的学号 SELECT DISTINCT SNO FROM SC 查询出前两个选了课学生的学号 SELECT DISTINCT TOP 2 SNO FROM SC 2 WHERE 子句的使用子句的使用 1 比较 比较运算符 比较 比较运算符 动手做 动手做 查询管理系全体学生的姓名 查询大于 20 岁学生的所有信息 查询成绩不及格的学生的学号 查询年龄小于 18 岁的学生的姓名 院系 查询学时小于 40 的全部课程信息 2 确定范围 确定范围 动手做 动手做 查询年龄在 20 22 岁之间的学生的姓名 所在系和年龄 思考问题 思考问题 上面的查询有几种实现方法 查询课程的学时在 40 60 分之间的课程信息 3 确定集合 确定集合 IN 语法格式为 列名 NOT IN 常量 1 常量 2 常量 n 查询管理系 经济系学生的姓名和性别 SELECT SNAME SSEX FROM STUDENT WHERE SDEPT IN 管理系管理系 经济系经济系 查询不是管理系 经济系学生的姓名和性别 24 SELECT SNAME SSEX FROM STUDENT WHERE SDEPT NOT IN 管理系管理系 经济系经济系 动手做 查询年龄为 19 22 38 岁的所有学生的信息 查询成绩为 58 79 89 分的选课信息 查询课程名称为数据库原理 税法 财务管理的课程信息 4 字符匹配 字符匹配 动手做 动手做 查询姓 张 的学生的详细信息 查询学生表中姓 张 姓 李 和姓 刘 的学生的情况 查询名字中第 2 个字为 小 或 大 字的学生的姓名和学号 查询所有不姓 刘 的学生的情况 查询学生表中学生的学号最后一位不是不是 2 3 5 学生的情况 查询课程表中第一个字是 数 财 的课程信息 用 SQL 语句看看是否有人与你的第二个字相同 查询学生姓名中带有 文 字的学生信息 查询学生的学号最后一位是 不是 1 3 5 7 9 的学生的学号和姓名 小知识 小知识 如果在所要查找的字符中存在 或 应该如何去查找 如查找课程名称 为 DB 开头的课程 解 使用转意字符ESCAPE SELECT FROM COURSE WHERE CNAME LIKE DB ESCAPE ESCAPE 短语表示 为转换字符 在表达示中 后面的 或 不再具有通配符的 含义 转义为普通的 字符 查询情况如图4 8所示 5 多重条件 逻辑谓词 多重条件 逻辑谓词 25 AND 表示只有在全部满足所有条件时结果才为 TRUE OR 表示只要满足其中一个 条件结果即为 TRUE 查询管理系年龄在 22 岁以下的学生的姓名 年龄和学号 SELECT SNAME SAGE SNO FROM STUDENT WHERE SDEPT 管理系 AND SAGE 22 三 排序三 排序 排序的语法格式为 ORDER BY ASC DESC n 动手做 动手做 将学生按年龄的升序排序 查询选修课程号为的学生的学号和成绩 查询结果按成绩降序排序 查询全体学生的信息 查询结果按院系名升序排序 同系的学生按年龄降序排序 查询选课的成绩 结果按成绩降序排列 查询管理学院的女生信息 结果按年龄升序排序 查询课程的所有信息 结果按学时降序排序 课程名升序排序 26 实训五 数据表的高级查询 实训目的实训目的 1 掌握计算函数的使用方法 2 掌握分组的含义及使用方法 3 掌握多表查询的方式 4 理解子查询的运用 实训要求实训要求 1 使用查询分析器完成以下各种操作 2 按要求完成每一个实训项目 3 及时保存 SQL 语句 实训结束后统一上教源代码 实训学时实训学时 2 学时 实训教师可以根据学生实际情况进行适当的调整 也可要求学生在课下完成 部分实训任务 实训内容与步骤实训内容与步骤 说明 本实训使用信息管理与信息系统实训室机器中已安装好的数据库 XKDB 数 据库中存有与实训二中相同的数据表 一 计算函数汇总数据一 计算函数汇总数据 动手做 动手做 统计学生总人数 统计选修了课程的学生的人数 计算学号为 的学生的考试各科成绩之和 计算课程 的学生的考试平均成绩 查询选修了课程 的学生的最高分和最低分 思考问题 思考问题 计算函数能不能出现在 WHERE 子句中 例如查询年龄最大学生的 姓名是否可以写成 SELECT SNAME STUDENT WHERE SAGE MAX SAGE 请通过上机验证后说明 27 二 分组计算二 分组计算 GROUP BY 语法格式为 GROUP BY n HAVING 注意注意 分组依据列不能是 text ntext image 和 bit 类型的列 统计每门课程的选课人数 列出课程号和人数 SELECT CNO COUNT SNO FROM SC GROUP BY CNO 分析 该语句首先查询结果按 CNO 的值分组 所有 CNO 值相同的元组归为一 组 然后再用 COUNT 函数对每一组进行计算 求得每组的学生人数 动手做 动手做 查询每名学生的选课门数和所有成绩的平均值 查询选修了 3 门以上课程的学生的学号 查询选取课门数等于或大于 4 门的学生的平均成绩和选课门数 SELECT SNO AVG GRADE COUNT FROM SC GROUP BY SNO HAVING COUNT 三 多表联接查询三 多表联接查询 1 内连接 内连接 内连接的格式为 FROM 表 1 INNER JOIN 表 2 ON 内联接条件格式为 查询管理系学生的选课情况 要求列出学生的姓名 所修课的课号和成绩 SELECT SNAME CNO GRADE FROM STUDENT JOIN SC ON STUDENT SNO SC SNO WHERE SDEPT 管理系 下面的语句也可以实现上面的功能 SELECT SNAME CNO GRADE FROM STUDENT SC WHERE STUDENT SNO SC SNO AND SDEPT 管理系 动手做 动手做 查询管理学院选修 计算机网络 课程的学生的姓名 课程名和成绩 查询所有选修 计算机网络 课程的学生的情况 要求列出学生的姓名和所在系 28 查询课程的成绩情况 要求列出课程的名称和成绩 2 自联接 自联接 它是一种特殊的内联接 它是指相互联接的表在物理上为同一张表 但可以在连辑 上分为两张表 使用自联接必须为表取两个别名 使之在逻辑上成为两张表 查询与李勇在同一个系学习的学生的姓名和所在的系 SELECT S2 SNAME S2 SDEPT FROM STUDENT S1 JOIN STUDENT S2 ON S1 SDEPT S2 SDEPT WHERE S1 SNAME 李勇 AND S2 SNAME 李勇 动手做 动手做 查询与你本人在一个学院学习的学生姓名 性别和年龄 3 外连接 外连接 ANSI 方式的外连接的语法格式为 FROM 表 1 LEFT RIGHT OUTER JOIN 表 2 ON 查询学生的选课情况 学号 姓名 课程号 成绩 包括选课和没有选课的学生 SELECT STUDENT SNO SNAME CNO GRADE FROM STUDENT LEFT OUTER JOIN SC ON STUDENT SNO SC SNO 动手做 动手做 查询计算机学院学生的选课情况 包括选课和没有选课的学生 思考问题 思考问题 试比较三种联接查询方式不同之处及外联接查询中三种查询方式的不 同之处 四 子查询四 子查询 1 使用子查询进行基于集合的测试 使用子查询进行基于集合的测试 查询与吴宾在同一个系的学生 SELECT SNO SNAME SDEPT FROM STUDNT WHERE SDEPT IN SELECT SDEPT FROM STUDENT WHERE SNAME 吴宾 查询成绩大于 90 分的学生的学号和姓名 SELECT SNO SNAME FROM STUDENT WHERE SNO IN SELECT SNO FROM SC WHERE GRADE 90 或 SELECT SNO SNAME FROM STUDENT SC 29 WHERE STUDENT SNO SC SNO AND GRADE 90 查询了选修了 数据库原理及应用 课程的学生的学号和姓名 SELECT SNO SNAME FROM STUDENT WHERE SNO IN SELECT SNO FROM SC WHERE CNO IN SELECT CNO FRON COURSE WHERE CNAME 数据库原理及应用 或 SELECT STUDENT SNO SNAME FROM STUDENT SC COURSE WHERE STUDENT SNO SC SNO AND SC CNO COURSE CNO AND CNAME 数据库原理及应用 查询年龄最大学生的姓名 SELECT SNAME FROM STUDENT WHERE SAGE IN SELECT MAX SAGE FROM STUDENT 思考问题 思考问题 这里的 IN 能不能用 来代替 2 使用子查询进行比较测试 使用子查询进行比较测试 使用子查询进行比较测试时 要求子查询语句必须是返回单值的查询语句 查询选修了课程号为 且成绩高于此课程的平均成绩的学生的学号和成绩 SELECT SNO GRADE FROM SC WHERE CNO AND GRADE SELECT AVG GRADE FROM SC WHERE CNO 动手做 动手做 查询学生年龄低于平均年龄的学生学号 姓名 性别和年龄 3 使用子查询进行存在性测试 使用子查询进行存在性测试 小知识 由于带EXISTS的子查询只能返回真或假值 因此在子查询中指定的列 名是没有意义的 所以在有EXISTS的子查询中 其目标列名序列通常都用 查询选修了课程 的学生姓名和系 SELECT SNAME SDEPT FROM STDENT WHERE EXISTS SELECT FROM SC WHERE SNO STUDENT SNO AND CNO 查询没有选修的学生的学号 姓名和所在系 SELECT SNO SNAME SDEPT FROM STUDENT WHERE NOT EXISTS SELECT FROM SC WHERE SNO STUDENT SNO AND CNO 30 实训六 数据完整性约束 实训目的实训目的 掌握数据完整性约束的作用及实现方法 实训要求实训要求 1 使用查询分析器完成以下各种操作 2 自学本实训中涉及到的相关内容 3 根据实训要求创建所用到的关系表 实训学时实训学时 2 学时 实训教师可以根据学生实际情况进行适当的调整 也可要求学生在课下完成 部分实训任务 实训内容与步骤实训内容与步骤 说明 本实训使用信息管理与信息系统实训室机器中已安装好的数据库 XKDB 第一步 打开 查询分析器 建立下面的表 USE XKDB GO CREATE TABLE student p Sno CHAR 10 NOT NULL Sname CHAR 10 Ssex CHAR 2 Sage TINYINT Sdept CHAR 20 Stel CHAR 13 CREATE TABLE course p Cno char 10 not null Cname char 20 CREATE TABLE sc p Sno CHAR 10 Cno CHAR 10 Grade TINYINT 31 第二步 完成以下内容 将 student p 表的学号设置为主键 所使用的 SQL 语句为 检验 向 student p 表中插入 古月 女 23 管理学院 和 丹青 男 22 管理系 两条数据 执行成功 出现问题 分析原因 将 student p 表的姓名设置成 UNIQUE 约束 所使用的 SQL 语句为 检验 向 student p 表中插入 李奇 女 23 管理学院 和 李奇 男 22 管理学院 执行成功 出现问题 分析原因 为 sc p 表加外键约束 sc p 学号引用 student p 表的学号 所使用的 SQL 语句为 检验 向 sc p 表中插入 92483 60 执行成功 出现问题 分析原因 设置 student p 表的 SDEPT 属性的默认值是 电气系 所使用的 SQL 语句为 检验 插入学号为 姓名为丁一 性别为男 年龄为 20 电话为 执行成功 出现问题 分析原因 设置 student p 表的性别只能为 男 或 女 所使用的 SQL 语句为 检验 插入学号为 姓名为李月 性别为 AA 年龄为 22 执行成功 出现问题 分析原因 在学生表中添加学生的年龄必须大于 18 岁 所使用的 SQL 语句为 检验 向 student p 中插入 张丽 女 17 管理学院 执行成功 出现问题 分析原因 32 实训七 视图的应用 实训目的实训目的 1 掌握视图的定义与使用方法 2 理解视图的作用 实训要求实训要求 1 本次实训要求在查询分析器中进行 2 复习视图的定义及使用方法 实训学时实训学时 2 学时 本实训可以根据专业性质和实训学时选做 实训内容与步骤实训内容与步骤 一 定义视图一 定义视图 说明 本实训使用信息管理与信息系统实训室机器中已安装好的数据库 XKDB 注意 注意 请先执行 USE XKDB 1 定义单源表视图 定义单源表视图 建立年龄大于 18 岁的学生的学号 姓名 性别和所在系的视图 AGE STD CREATE VIEW AGE STD AS select sno sname ssex sdept from student where sage 18 检验 SELECT FROM AGE STD 动手做 动手做 建立计算机学院年龄大于 22 岁的学生的信息视图 建立课程学时小于 40 且是第 3 学期上课的课程编号 课程名称及学时的视图 建立课程信息的视图 建立学生信息的视图 2 定义多源表视图 定义多源表视图 建立学生姓名 课程名称和成绩的视图 SCG VIEW CREATE VIEW SCG VIEW AS SELECT STUDENT SNO SNAME CNAME GRADE FROM STUDENT SC COURSE WHERE STUDENT SNO SC SNO AND COURSE CNO SC CNO 33 检验 SELECT FROM SCG VIEW 选修了课程号为 的学生的视图 SJJG VIEW CREATE VIEW SJJG VIEW SNO SNAME GRADE AS SELECT STUDENT SNO SNAME SAGE FROM STUDENT SC WHERE STUDENT SNO SC SNO AND SC CNO 检验 SELECT FROM SJJG VIEW 动手做 动手做 建立经管学院学生选课信息的视图 3 在已有视图上定义新视图 在已有视图上定义新视图 选修了课程号为 并且成绩在 80 分以上的学生的视图 SJJG80 VIEW CREATE VIEW SJJG80 VIEW AS SELECT FROM SJJG VIEW WHERE GRADE 80 检验 SELECT FROM SJJG80 VIEW 三 视图的使用三 视图的使用 实际上 上面视图的定义中的检验部分就是对视图使用的例子 请根据所学过的知 识 完成下面的操作 查询平均成绩在 80 分以上的学生的学号和平均成绩 所使用的 SQL 语句为 查询年龄大于 18 岁 且性别为女的学生信息 34 实训八 存储过程与触发器 实训目的实训目的 1 掌握存储过程与触发器的定义方法 2 掌握存储过程与触发器的使用方法 实训要求实训要求 1 本次实训要求在企业管理器和查询分析器中分别实现进行 2 认真体会存储过程和触发器的作用 3 实训前认真阅读教材中的相关内容 实训学时实训学时 2 学时 本实训可以要求学生课下完成一定的实训任务 实训内容与步骤实训内容与步骤 说明 本实训使用信息管理与信息系统实训室机器中已安装好的数据库 XKDB 注意 注意 如果在查询分析器中 请先执行 USE XKDB 一 存储过程的创建及使用一 存储过程的创建及使用 1 在企业管理器中实现 在企业管理器中实现 创建一个名为 xsxx 的存储过程 完成的功能是在 student 表中查询数力系学生的 学号 姓名 性别和出生日期的内容 输入的代码如下 CREATE PROCEDURE xsxx AS SELECT SNO AS 学号 SNAME AS 姓名 SSEX AS 性别 2007 SAGE AS 出生日期 FROM STUDENT WHERE SDEPT 数力系 检查语法并保存该存储过程 动手做 动手做 创建一个名为 xkxx 的存储过程 完成的功能是查询计算机学院学生的选课情况 只包含已经选课的 要求得到学生的学号 姓名 性别 课程名称和成绩 小知识 若修改存储过程 在企业管理器中执行以下操作 选择指定的服务器 和数据库 单击 存储过程 文件夹 在右边的页框中右击要查看的存储过程 在弹出 的快捷菜单中选择 属性 选项 将弹出 存储过程属性 对话框 在此对话框中可直 接修改存储过程的代码 小知识 若重命名存储过程 则选择存储过程后 右击要操作的存储过程 在 弹出的快捷菜单中选择 重命名 选项 35 小知识 若删除存储过程 则选择存储过程后 右击要操作的存储过程 在弹 出的快捷菜单中选择 删除 选项 2 在查询分析器中创建存储过程 在查询分析器中创建存储过程 1 创建无参存储过程 创建名为 xscj 的存储过程 要求返回学生的学号 姓名 性别 课程名称和成绩 步骤 打开 XKDB 数据库 use XKDB 查询是否已经存在该存储过程 如果存在 则删除它 if exists select name from sysobjects where name xscj and type p drop procedure xscj go 小知识 在上面的语句中提供了一种删除存储过程的方法即 DROP PROCEDURE 存储过程名存储过程名 建立存储过程 CREATE PROCEDURE xscj as select s sno sname ssex cname grade from student s course c sc where s sno sc sno and o o 动手做 动手做 仿照上面的例子 创建一个选修数据库学生信息的存储过程 xxsjk 要求返回学 生的学号 姓名 性别和年龄 2 创建有参存储过程 创建名为 stuinfo 的有参存储过程 根据输入的学号查询此学生的相关信息 步骤 删除已存在的存储过程 use XKDB if exists select name from sysobj