实验5-复杂查询及视图
数据库实验5 多表查询及视图
实验五：多表查询及视图一、实验目的1.掌握SELECT语句的基本语法和查询条件表示方法；2.掌握数据表的连接查询、嵌套查询、集合查询的使用方法。
3.掌握创建及管理视图的方法；二、实验学时2学时三、实验要求1.了解SELECT语句的基本语法格式和执行方法；2.掌握连接查询、嵌套查询和集合查询的语法规则；3.掌握使用界面方式和命令方式创建及管理视图；4.完成实验报告；四、实验内容1.以实验3数据库为基础，请使用T-SQL 语句实现进行以下操作：1)查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；2)查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；3)按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。
其中已修学分为考试已经及格的课程学分之和；4)查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；5)查询只被一名学生选修的课程的课程号、课程名；6)使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；7)使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；8)使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；9)使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；2.以实验数据库为基础数据，利用对象资源管理器创建以下视图：1）创建所有学生学号、姓名及年龄的信息视图v_stu_info2）创建CS系学生基本信息视图v_stu_cs3）创建选修课成绩在80分以上的学生的信息视图v_stu_80,包括学生基本情况及成绩。
3. 以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将SQL语句写在实验报告册中：1) 创建v_CS视图，包括CS系各学生的学号、姓名及年龄，要求进行修改和插入操作时仍需保证该视图只有CS系的学生;2) 创建v_CS_age20视图，包括CS系学生年龄在20岁以上的基本信息；并保证对视图文本的修改都要符合年龄大于20这个条件。
实验五：查询与视图的创建
安康学院电子与信息工程系实验报告
1．实验目的：（5分）
（1）掌握查询设计器的使用。
（2）掌握查询向导的使用。
（3）掌握视图设计器的使用。
2．实验内容：（15分）
（1）利用查询向导创建一个查询cx,查询学生表中女生的所有学生信息。
（2）对数据库“教学管理.dbc”使用查询设计器创建一个查询xscj,按系查询学生的成绩及学分，要求包含字段：学号、姓名、性别、系、课程名称、学分和成绩，并将查询保存到xscj.dbf表中。
（3）对数据库“教学管理.dbc”使用查询设计器创建一个查询，查询所有教师教学情况，要求包含字段：教师编号、教师姓名、课程编号、课程名称，并将查询结果保存在teacher.dbf表中。
（4）对数据库“教学管理.dbc”使用视图设计器创建一个视图，包含字段：学号、姓名、课程名称、学分、成绩，按成绩由低到高排序，并将该视图命名为view3。
（5）对数据库“教学管理.dbc”进行如下操作：
使用视图设计器创建一个视图，其内容包含所有选“数据结构”课程的学生学号、姓名和成绩，要求其结果按“成绩”降序排序且允许修改表“选课.dbf”中的成绩，并将视图命名为view4;在视图view4中修改某学生的成绩，然后在“选课.dbf”中验证其更新数据表的功能。
3．实验过程：（60分）
4．实验总结：（20分）。
实验五--数据查询——复杂查询
实验五数据查询——复杂查询一、实验目的1．掌握SQLServer查询语句的基本语法2．熟练使用SQL的Select语句对多表进行查询3．熟练掌握并运用SQLServer所提供的函数4．熟练使用SQL语句进行复杂的连接操作二、实验环境（实验的软件、硬件环境）硬件：PC机软件：SQL2000三、实验指导说明请复习相关的查询知识点并完成如下内容。
四、实验内容1．在订单数据库orderDB中，完成如下的查询：（1）用子查询查询员工“张小娟”所做的订单信息。
（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。
（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。
（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。
（5）查询既订购了P2*******商品，又订购了P2*******商品的客户编号，订单编号和订单金额（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。
（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。
（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。
（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。
（10）在订单主表中查询每个业务员的订单数量（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。
（13）统计客户号为“C20050001”的客户的订单数，订货总额和平均订货金额（14）统计每个客户的订单数，订货总额和平均订货金额。
（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。
（16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。
数据库实验报告 复杂数据查询
七、教师评语与成绩评定：
4
5
（19）查询所开课程的选修情况，包括没有任何学生选修的课程。 SELECT 学号, 课程名, 分数 FROM 成绩 A RIGHT OUTER JOIN 课程 B ON (A.课程编号=B.课程编号)
（20） 查询研究生与其导师的情况。 SELECT * FROM 研究生 A INNER JOIN 教师 B ON A.导师号= B.教工号 也可以写成等价的 SQL 语句： SELECT * FROM 研究生 A,导师 B WHERE A.导师号= B.教工号
（21）查询教师指导研究生的情况，包括不指导研究生的教师。
3
SELECT * FROM 导师 A LEFT OUTER JOIN 研究生 B ON (A.教工号= B.导师) 三、实验平台： Microsoft SQL Sever 四、设计流程： 1、进入查询分析器，参见进入查询分析器演示。 2、在查询分析器的命令编辑窗口输入 SQL 语句，点击执行键 ►，将执行 SQL 语 句。参见执行 SQL 语句的演示。 3、在调试程序过程中，可以用鼠标选择某些要执行的 SQL 语句，再点击执行键， 执行选择的语句。参见执行 SQL 语句的演示。 4、 注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在子表中 插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重 复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。 5、保存调试通过的 SQL 程序。 五、程序清单：
（11）根据学生、课程和成绩表，输出“计算机”课程的成绩单，包括姓名和 分数。 SELECT 姓名，分数 FROM 学生 A JOIN 成绩 B ON（A.学号=B.学号） JOIN 课程 C ON（B.课程编号=C.课程编号） WHERE C.课程名称=’计算机’
实验五-复杂查询-实验报告
实验五复杂查询1 实验目的与要求(1) 熟练掌握SQL语句的使用。
(2) 熟练使用SQL语句进行连接操作。
2 实验内容(1)在订单明细表中查询订单金额最高的订单。
select a.*from orderdetail a,ordermaster bwhere a.orderno=b.orderno and ordersum=(select max(ordersum)from ordermaster)(2)找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。
select productno 商品编号,orderno 订单编号,quantity 订货数量,quantity*price 订货金额from orderdetailwhere productno in(select productnofrom orderdetailgroup by productnohaving count(*)>=3)order by quantity desc(3)查找销售总额少于5000元的销售员编号、姓名和销售额。
select a.salerno 销售员编号,b.employeename 姓名,sum(ordersum)销售额from ordermaster a,employee bwhere a.salerno=b.employeenogroup by a.salerno,b.employeenamehaving sum(ordersum)<5000(4)找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。
select salerno 销售员编号,sum(ordersum)销售业绩from ordermastergroup by salernohaving sum(ordersum)<=5000order by sum(ordersum)desc(5)查询订购的商品数量没有超过10个的客户编号和客户名称。
实验五 视图的创建和使用
实验五视图的创建和使用5.1概述5.1.1任务一理解视图的概念视图是一个虚拟表，其内容由查询定义。
同真实的表一样，视图包含一系列带有名称的列和行数据。
但是，视图并不在数据库中以存储的数据集合形式存在。
行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
对其中所引用的基表来说，视图的作用类似于筛选。
定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。
视图被定义后便存放在数据库中，对视图中的数据的操作与对表的操作一样，可以对其进行查询、修改和删除，但对数据的操作要满足一定的条件。
当对视图所看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。
5.1.2任务二理解视图的优点用户可以根据自己的实际需要创建视图，使用视图有很多优点，主要有以下几点：1、简单性视图可以屏蔽数据的复杂性，简化用户对数据库的操作。
使用视图，用户可以不必了解数据库的结构，就可以方便地使用和管理数据。
那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2、逻辑数据独立性视图可以使应用程序和数据库表在一定程度上独立。
如果没有视图，应用一定是建立在表上的。
有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
3、安全性通过视图用户只能查询和修改他们所能见到的数据。
数据库中的其他数据则既看不见也取不到。
5.2创建视图创建视图的方法有三种:在创建视图前请考虑如下原则:只能在当前数据库中创建视图.视图名称必须遵循标识符的规则,且对每个用户必须为唯一.此外,该名称不得与该用户拥有的任何表的名称相同.可在其他视图和引用视图的过程之上建立视图.SQLServer 2000允许嵌套多达32级视图若要创建视图,数据库所有者必须授予用户创建视图的权限,并且用户对视图定义中所引用的表或视图要有适当的权限5.2.1任务一使用企业管理器创建视图使用企业管理器创建视图的具体操作步骤如下:1、打开企业管理器窗口，打开“新建视图”对话框。
数据库对象和数据的操作5-复杂查询
解决方案
工作任务——嵌套查询
技术支持：在SQL中，将一条SELECT语句作为另一条SELECT语句的一部分称为嵌
套查询。外层的SELECT语句被称为外部查询或父查询，内层的SELECT语句成为
内部查询或子查询。 嵌套查询如下。 SELECT FROM WHERE <语句> <语句> <表达式> ( IN SELECT <语句> /*内层查询或子查询*/ /*外层查询或父查询*/
解决方案
找出至少学习一门有前导课程的学生的学号(sNo)、姓名(sName)。 SELECT sNo, sName FROM Student WHERE sNo IN (SELECT sNo FROM Grade WHERE cNo IN (SELECT cNo FROM Course WHERE cPcno IS NOT NULL)) 查询年龄比“杜鹃”大的学生的学号(sNo)、课程号(cNo)、成绩(grade)。 SELECT * FROM Grade WHERE sNo IN (SELECT sNo FROM Student WHERE sBirthday< (SELECT sBirthday FROM Student WHERE sName=’杜鹃’))
确定出建好的解决问题的方案。
沟通交流： 检查评估: 学生演示已完成的项目任务，分组进行检查，每组大致5分钟。 教师根据任务要求进行检查，对小组完成任务情况给予具体点评。 实验报告要有针对性地提供任务的完成示例和分析设计思路。
解决方案
查询哪些课程(cName)被学号(sNo)为“082034101”的学生所学习。 SELECT cName FROM Course WHERE cNo IN （SELECT cNo FROM Grade WHERE sNo=’082034101’)
实验5-视图
实验五视图1实验目的（1）掌握SQL视图语句的基本使用方法，如CREATE VIEW、DROP VIEW。
（2）掌握视图更新、WITH CHECK OPTION等高级功能的使用。
2实验内容2.1 掌握SQL视图语句的基本使用方法（1）创建视图（省略视图列名）。
（2）创建视图（不能省略列名的情况）。
（3）删除视图（RESTRICT / CASCADE）。
2.2 掌握视图更新和WITH CHECK OPTION的高级使用方法（1）创建视图（WITH CHECK OPTION），并利用INSERT、DELETE和UPDATE语句加以验证。
（2）创建一个行列子集可更新视图，并利用INSERT、DELETE和UPDATE语句加以验证。
（3）创建一个不可能更新的视图，并利用更新语句验证该视图不可更新。
3实验要求（1）深入复习教材第三章SQL有关视图语句。
（2）根据书上的例子，针对TPCH数据库模式设计各种视图语句，每种类型视图语句至少要设计一个，描述清楚视图要求，运行你所设计的视图语句，并截图相应的实验结果，每幅截图并要有较为详细的描述。
也可以按照附1所列示例做实验。
（3）实验步骤和实验总结中要详细描述实验过程中出现的问题、原因和解决方法。
（4）思考题：KingbaseES把视图的定义存储在那个系统表中？如何查看某个视图的定义？4实验步骤4.1 掌握SQL视图语句的基本使用方法（1）创建视图（省略视图列名）。
（2）创建视图（不能省略列名的情况）。
（3）删除视图（RESTRICT / CASCADE）。
4.2 掌握视图更新和WITH CHECK OPTION的高级使用方法（4）创建一个行列子集可更新视图，并利用INSERT、DELETE和UPDATE语句加以验证。
（5）创建视图（WITH CHECK OPTION），并利用INSERT、DELETE和UPDATE语句加以验证。
（6）创建一个不可能更新的视图，并利用更新语句验证该视图不可更新。
实验5：使用子查询实现复杂查询
selectmToyRate,vToyName
fromToysast1
wheremToyRate=(selectMAX(mToyRate)fromToysast2
wheret1.cBrandId=t2.cBrandId)
5.检索单价（mToyRate）最高的玩具的类别（类别为Category）名称。
6.掌握SELECT INTO语句；
实验内容
完成在，GlobalToyz数据库基础上的操作，按要求完成给出的下列题目，要求写出相应数据库的T-SQL语句。
1.将’California’州的订单接收者（Recipient）信息拷贝到一张局部临时表中（表名自定义）。
select*into
#newtablefromRecipient
selectvToyName
fromtoyst1
whereexists(selectvToyNamefromToyst2wherecBrandId
in(selectcBrandIdfromToyswherevToyName='Robby the Whale')andt1.cBrandId=t2.cBrandId)
fromShopper
wherecShopperIdin(
selectcShopperId
fromOrders
wherecCartIdin(
selectcCartId
fromShoppingCart
wherecToyIdin(
selectcToyId
fromToys
wherevToyName='Robby the Whale')))
实验5：数据库——复杂数据查询与视图管理
实验5：复杂数据查询与视图管理一、实验目的1、掌握复杂查询的使用方法。
2、掌握视图的创建、视图的查询以及通过视图更新（插入、修改、删除）基本表中的数据。
二、实验任务及截图：注意：用到的表结构及信息如下：S：学生基本信息表；C：课程基本信息表；SC：学生选课信息表。
各表的结构以及数据如下所示：表 2.1 学生基本信息表(表名：S)表 2.2 课程基本信息表(表名：C)表 2.3 学生选课信息表(表名：SC)综合查询：1、求不选修C语言课程的学生学号。
select snofrom Swhere not exists(select C.*,SC.*from C,SCwhere o=o ANDame='C语言');2、查询平均成绩在60分以上的学生姓名。
select sname,AVG(SC.grade)'平均分'from S,SCwhere S.sno=SC.snogroup by snamehaving AVG(SC.grade)>603、求这样的学生姓名：该学生选修了全部课程并且其中一门课在90分以上。
select snamefrom S,SCwhere S.sno=SC.snoAND grade>90AND not exists(select*from Cwhere not exists(select*from SCwhere sno=s.snoAND cno=o))4、查询选修课程数在两门以上的学生姓名。
（用连接查询和嵌套查询两种方法实现）连接查询:select snamefrom S,SCwhere S.sno=SC.snogroup by snamehaving COUNT(sname)>2嵌套查询:select snamefrom Swhere sno in(select snofrom SCgroup by snohaving count(SC.sno)>2)5、查询所有成绩都及格的学生人数。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验5：SQL复杂查询及视图
【实验目的】
●掌握SELECT查询语句的写法。
●掌握视图的创建方法
【实验内容】
本实验用到的“商品库”和“教学库”，请先附加。
1.从学生表中查询男生的人数及女生的人数。
2.从教学库中查询每个学生的学号、选课门数和平均成绩。
3.从教学库中查询只选修了一门课程的学生学号。
提示：用group by……having….
4.从教学库中查询每个专业的学生人数，并按人数多少降序排列。
5.从商品库中查询出每种商品的总价值，并将总价值按降序排列。
6.定义一个反映学生出生年份的视图BT_S。
7.查询视图BT_S
8.建立一个反映信息系学生的视图IS_Student。
9.将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”【实验心得】。