SQL实验四：数据库的查询和视图
实现SQL Server数据库中的视图和查询
（9）VIEW_METADATA：指定为引用视图的查询 请求浏览模式的元数据时，SQL Server 将向 DBLIB、 ODBC 和 OLE DB API 返回有关视图的元数据信息， 而不是返回基表或表。浏览模式的元数据是由 SQL Server 向客户端 DB-LIB、ODBC 和 OLE DB API 返 回的附加元数据，它允许客户端 API 实现可更新的客 户端游标。浏览模式的元数据包含有关结果集内的列所 属的基表信息。 对于用 VIEW_METADATA 选项创建 的视图，当描述结果集中视图内的列时，浏览模式的元 数据返回与基表名相对的视图名。当用 VIEW_METADATA 创建视图时，如果该视图具有 INSERT 或 UPDATE INSTEAD OF 触发器，则视图 的所有列（timestamp 除外）都是可更新的。
（3）定义视图的语句是一个 SELECT查询语句。 该语句可以使用多个表或其它视图。若要从创 建视图的 SELECT 子句所引用的对象中选择， 必须具有适当的权限。视图不必是具体某个表 的行和列的简单子集。可以用具有任意复杂性 的 SELECT 子句，使用多个表或其它视图来创 建视图。 （4）在索引视图定义中，SELECT 语句必须是 单个表的语句或带有可选聚合的多表 JOIN。
SELECT子句
SELECT子句用于指定要返回的列，其完整的 语法如下： SELECT [ ALL│DISTINCT ] [ TOP n [PERCENT]] 列名 [[AS] 别名]│别名=表达式 } [ ,...n ]
FROM子句
只要SELECT子句有要查询的列，就必须使 用FROM子句指定进行查询的单个或者多个表。 此外，SELECT语句要查询的数据源除了表以外 还可以是视图，视图相当于一个临时表，其语 法格式如下：
数据库实验4_数据库查询与视图
在学生选课数据库中完成规定查询，并创建视图。
1.查询线性代数不及格的同学的学号和姓名；SQL语句为：SELECT名单$.学号,姓名FROM名单$,学生选课信息和成绩$,课程$WHERE名单$.学号=学生选课信息和成绩$.学号AND课程$.课号=学生选课信息和成绩$.课号AND课程$.课程名='线性代数'AND学生选课信息和成绩$.成绩<60;执行后结果为：2.查询没有选课记录的同学的所有基本信息；SQL语句为：SELECT*FROM名单$WHERE NOTEXISTS(SELECT*FROM学生选课信息和成绩$WHERE学号=名单$.学号);执行后结果为：3.查询具有简介先修课的课程信息及对应的先修课名；SQL语句为：SELECTFIRST.课号,SECOND.课程名FROM课程$FIRST,课程$SECONDWHEREFIRST.先修课号=SECOND.课号;执行后结果如下：4.统计高等数学（1）的平均成绩；SQL语句为：SELECT AVG(成绩)FROM学生选课信息和成绩$,课程$WHERE学生选课信息和成绩$.课号=课程$.课号AND课程$.课程名='高等数学(1)'; 执行后结果如下：5.统计各门课的选课人数；SQL语句为：SELECT课号,COUNT(学号)FROM学生选课信息和成绩$GROUPBY课号;执行后结果为：6.查询选修5门课以上的学生的学号；SQL语句为：SELECT学号FROM学生选课信息和成绩$GROUPBY学号HAVING COUNT(*)>5;执行后结果为：7.用你的学号查询和你一个班的同学的学号和姓名；SQL语句为：SELECT学号,姓名FROM名单$WHERE班级IN(SELECT班级FROM名单$WHERE学号='201000800145');执行后结果如下：8.查询高等数学（1）成绩比你高出10分以上的同学的姓名和对应成绩；SQL语句为：SELECT姓名,成绩FROM名单$,学生选课信息和成绩$WHERE名单$.学号=学生选课信息和成绩$.学号AND学生选课信息和成绩$.课号='82006010'AND成绩>(SELECT成绩+10FROM学生选课信息和成绩$,课程$WHERE学生选课信息和成绩$.课号=课程$.课号AND课程$.课程名='高等数学(1)' AND学生选课信息和成绩$.学号='201000800145')执行后结果为：9.找到每门课获得最高成绩的同学的学号、姓名、课名和成绩；选做。
数据库的视图操作实验报告
实验（四）数据库的视图操作实验一、实验目的和要求（1）掌握SQL Server中的视图创建向导和图表创建向导的使用方法；（2）加深对视图和SQL Server图表作用的理解。
（3）掌握数据库安全性的操作方法。
二、实验内容和原理1. 基本操作实验（1）在SQL Server企业管理器中调出Create View Wizard（创建图表向导），按下列Transact-SQL描述的视图定义，创建借阅_计算机图书视图。
CREATE VIEW 借阅_计算机图书AS SELECT 图书.*,借阅.*FROM 图书,借阅WHERE图书.编号=借阅.书号AND图书.类别=‘计算机’（2）在SQL server企业管理器中调出Create View Wizard（创建图表向导），完成在图书-读者数据库中建立一个图书_借阅图表操作。
要求该图表包括图书和借阅两个表，并包括图书与借阅之间的“图书.书号＝借阅.书号”外码与被参照表之间的关联。
（3）查看上述实验结果。
如果结果有误，予以纠正。
2. 提高操作实验在学生-课程数据库中用Transact-SQL语句描述下列视图定义。
（1）从学生表中建立查询所有男（女）生信息的视图STU_SEX。
视图的列名为SNO、SNAME、SSEX和SAGE。
（2）从课程表中建立查询所有课程先修课信息的视图课程_PRE。
视图的列名为课程号、课程名称和先修课名称。
（3）从选修表中建立查询成绩大于等于80信息的视图STU_CJ1。
视图的列名为学号、课程号和成绩。
（4）从学生、选修和课程三个表建立查询学生选修情况的视图STU_CJ2。
视图的列名为姓名、课程名称和成绩。
（5）从学生、选修和课程三个表建立查询学生选修情况并且成绩小于80的视图STU_CJ3。
视图的列名为姓名、课程名称和成绩。
（6）利用Transact-SQL命令修改视图STU_SEX。
把视图的列名改为学号、姓名、性别和年龄，把加上“WITH CHECK OPTION”选项。
实现SQLServer数据库中的视图和查询
2．视图
视图看上去同表似乎一模一样，具有一组命名的 字段和数据项，但它其实是一个虚拟的表，在物理上 并不实际存在。视图是由查询数据库表产生的，它限 制了用户能看到和修改的数据。视图一旦定义后，就 可以和基本表一样被查询、被删除，也可以在一个视 图基础上再定义新的视图。 视图兼有表和查询的特点：与查询相类似的是， 视图可以用来从一个或多个相关联的表或视图中提取 有用信息；与表相类似的是，视图可以用来更新其中 的信息，并将更新结果永久保存在磁盘上。我们可以 用视图使数据暂时从数据库中分离成为游离数据，以 便在主系统之外收集和修改数据。
3、使用DROP VIEW删除视图 删除视图的语法格式如下。 DROP VIEW 视图名1，…，视图名n 使用该语句一次可以删除多个视图。
§4 通过视图访问数据 使用视图管理表中的数据包括插入、更新 和删除三种操作，在操作时要注意以下几点： 修改视图中的数据时，可以对基于两个以 上基表或视图的视图进行修改，但是不能同时 影响两个或者多个基表，每次修改都只能影响 一个基表。 不能修改那些通过计算得到的列，例如年龄 和平均分等。 若在创建视图时定义了 WITH CHECK OPTION选项，那么使用视图修改基表中的数 据时，必须保证修改后的数据满足定义视图的 限制条件。
二、视图的优缺点
当对通过视图看到的数据进行修改时，相应 的基本表的数据也要发生变化，同时，若基本表 的数据发生变化，则这种变化也可以自动地反映 到视图中。 视图有很多优点，主要表现在： 1、视点集中 视图集中即是使用户只关心它感兴趣的某些 特定数据和他们所负责的特定任务。这样通过只 允许用户看到视图中所定义的数据而不是视图引 用表中的数据而提高了数据的安全性。
数据库实验4 SQL的视图、数据控制
实验四SQL的视图、数据控制[实验目的]1.理解SQL的视图以及与基本表的区别；2.掌握SQL视图的定义、查询、更新。
3.掌握SQL Server中有关用户、权限的管理方法。
4.掌握SQL语言的控制功能5.加深对数据库安全性的理解[实验时数] 2 学时[实验内容]1.创建、查看、修改和删除视图。
2.创建、编辑和删除数据库图表。
[实验方法]1. 创建视图假设在图书—读者数据库已经建立了图书、读者和借阅3个表，它们的结构为：图书(书号，类别，出版社，作者，书名，定价)；借阅(书号，读者书证号，借阅日期)；读者(书证号，姓名，单位，性别，电话)。
如果要在上述3个表的基础上建立一个视图，取名为: 读者_VIEW。
其操作用SQL语句表示为：CREATE VIEW 读者_VIEWAS SELECT图书.*，借阅.*FROM 图书，借阅，读者WHERE 图书.书号=借阅.书号AND 借阅.读者书证号=读者.书证号；下面利用SQL Server 2000中提供的视图创建向导，来创建读者_VIEW视图。
1)打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库。
2)选择菜单：工具 向导，如图5- 错误！未定义书签。
所示。
3)在如图5-18所示的向导选择对话框中，单击数据库左边的“+”号，使之展开。
选择“创建视图向导”选项，单击[确定]按钮。
4)进入创建视图向导后，首先出现的是欢迎进入创建视图向导对话框，其中简单介绍了该向导的功能，如图5- 错误！未定义书签。
所示。
单击[下一步]按钮后，就会出现如图5-20所示的“选择数据库名称”对话框。
5)在“选择数据库名称”对话框中，选择视图所属的数据库。
本例的数据库为“图书_读者”。
单击[下一步]按钮，则进入如图5- 错误！未定义书签。
所示的选择表对话框。
签。
在向导对话框中选择视图向导图5- 错误！未定义书签。
欢迎进入创建视图向导对话框图5- 错误！未定义书签。
SQL Server2016实用教程 第4章 数据库的查询和视图
8．聚合函数
（1）SUM和AVG。 SUM和AVG分别用于求表达式中所有值项的总和与平均值，格式为：
SUM /AVG ( [ ALL | DISTINCT ] 表达式 ) 其中，“表达式”可以是常量、列、函数或表达式，其数据类型只能是int、smallint、
tinyint、bigint、decimal、numeric、float、real、money和smallmoney。 【例4.8】 求所有课程的总学分和选修101课程的学生的平均成绩。
1．选择所有列
执行结果如图4.1所示。
2．选择一个表中指定的列
可选择一个表中的部分列，各列名之间要以逗号分隔。 【例4.2】 查询xsb表中计算机专业学生的学号、姓名和总学分。 T-SQL命令如下， SELECT 学号, 姓名, 总学分 FROM xsb WHERE 专业 = '计算机'
3．定义列别名
第4章 数据库的查询和视图
——数据库的查询
数据库的查询
下面介绍SELECT语句，它是T-SQL的核心。语法主体格式如下：
SELECT <输出列> [ INTO 新表 ] [ FROM { <表源> } [ , ... ] ] [ WHERE <条件> ] [GROUP BY <分组条件> ] [ HAVING <分组统计条件>] [ ORDER BY <排序顺序>] [ FOR 子句] [ORVER 子句] [OPTION 子句] /*指定查询结果输出列*/ /*指定查询结果存入新表*/ /*指定查询源：表或视图*/ /*指定查询条件*/ /*指定查询结果分组条件*/ /*指定查询结果分组统计条件*/ /*指定查询结果排序顺序*/ /*指定查询结果 */ /*确定行集的分区和排序 */ /*指定的查询提示 */
实现SQLServer数据库中的视图和查询
1．基本表 基本表是独立存在的表。在SQL Server中 创建了一个基本表，那么可以在表中添加记 录，这些记录存放在硬盘上。“学生表”、 “课程表”、“成绩表”等等，都是基本表。
2．视图
视图看上去同表似乎一模一样，具有一组命名的 字段和数据项，但它其实是一个虚拟的表，在物理上 并不实际存在。视图是由查询数据库表产生的，它限 制了用户能看到和修改的数据。视图一旦定义后，就 可以和基本表一样被查询、被删除，也可以在一个视 图基础上再定义新的视图。 视图兼有表和查询的特点：与查询相类似的是， 视图可以用来从一个或多个相关联的表或视图中提取 有用信息；与表相类似的是，视图可以用来更新其中 的信息，并将更新结果永久保存在磁盘上。我们可以 用视图使数据暂时从数据库中分离成为游离数据，以 便在主系统之外收集和修改数据。
6、安全性 视图可以作为一种安全机制。通过视图用 户只能查看和修改他们所能看到的数据。其它 数据库或表既不可见也不可以访问。如果某一 用户想要访问视图的结果集，必须授予其访问 权限。视图所引用表的访问权限与视图权限的 设置互不影响。 7、视图的缺点 视图的缺点主要表现在其对数据修改的限 制上。
三、使用视图的场合 1）表的行或列的子集。 2）两个或多个表的联合。 3）两个或多个表的连接。 4）其他视图的子集。 5）视图与表的组合。
参数说明： (1)视图名称必须符合标识符规则。可以选择是 否指定视图所有者名称。 (2)CREATE VIEW子句中的列名是视图中显示 的列名。只有在下列情况下，才必须命名 CREATE VIEW 子句中的列名：当列是从算术 表达式、函数或常量派生的，两个或更多的列可 能会具有相同的名称（通常是因为联接），视图 中的某列被赋予了不同于派生来源列的名称。当 然也可以在 SELECT 语句中指派列名。 注意：如果未指定列名，则视图列将获得与 SELECT 语句中的列相同的名称。
SQL查询及视图
• 视图和基本表不同，视图是一个虚表，数据库中只 存储视图的定义（存在数据字典中），实际的数据 不存储。 • 基本表本身是实际存在，一个基本表就是一个关系 ，它不是有其他表导出的表。 • 视图和基本表示相互联系的。 • 视图是从一个或几个基本表（或视图）导出的表， 某一用户可以定义若干个视图，因此对某一用户而 言，它的外模式是由若干个基本表和若干个视图组 成的。
（1）创建视图S1（Sno,Gavg,Cnum）将所 有成绩在80分以上的同学的学号、选修门 数组成一个视图； Create view S1(Sno, Gavg,Cnum) As Select Sno,Avg(grade),Count(Cno) From SC Where Grade>=80 Group by Sno
• [题5] 已知一个关系数据库的模式如下： S(SNO,SNAME,SCITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,JCITY) SPJ(SNO,PNO,JNO,QTY) 其中S表示供应商，它的各属性依次为供应商号， 供应商名，供应商所在城市； • P表示零件，它的各属性依次为零件号，零件名， 零件颜色，零件重量； • J表示工程，它的各属性依次为工程号，工程名， 工程所在城市； • SPJ表示供货关系，它的各属性依次为供应商号， 零件号，工程号，供货数量。 请用SQL语句分别表示下面两个查询要求： ⑴找出北京的任何工程都不购买的零件的零件号。 ⑵按工程号递增的顺序列ROM P WHERE NOT EXISTS (SELECT * FROM SPJ,S WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.SCITY=’北京’)； ② SELECT JNO,SUM(QTY) FROM SPJ GROUP BY JNO ORDER BY JNO ASC;
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
二 〇 一 五 年 四 月题 目：数据库的查询和视图 学生姓名：孙跃 学 院：理学院 系 别：数学系专 业：信息与计算科学 班 级：信计12-2 任课教师：侯睿《数据库原理及应用》实验报告一、练习目的1、数据库的查询（1）掌握SELECT语句的基本语法；（2）掌握子查询的表示；（3）掌握连接查询的表示；（4）掌握SELECT语句的GROUP BY子句的作用与使用方法；（5）掌握SELECT语句的ORDER BY子句的作用与使用方法；2、视图的使用（1）熟悉视图的概念和作用；（2）熟悉视图的创建方法；（3）熟悉如何查询和修改视图。
二、练习准备1、数据库的查询（1）了解SELECT语句的基本语法格式；（2）了解SELECT语句的执行方法；（3）了解子查询的表示方法；（4）了解连接查询的表示；（5）了解SELECT语句的GROUP BY子句的作用与使用方法；（6）了解SELECT语句的ORDER BY子句的作用；2、视图的使用（1）了解视图的概念；（2）了解创建视图的方法；（3）了解并掌握对视图的操作。
三、实验程序实验4.1 数据库的查询1、(1)对于实验2给出的数据库结构，查询每个雇员的所有数据.USE YGGLGOSELECT*FROM Employees(2)用SELECT语句查询Employees表中每个雇员的地址和电话.SELECT Address,PhoneNumberFROM Employees(3)查询EmployeeID为000001的雇员的地址和电话.SELECT Address,PhoneNumberFROM EmployeesWHERE EmployeeID='000001'GO(4)查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别制定地址和电话. SELECT Address AS地址,PhoneNumber AS电话FROM EmployeesWHERE Sex=0(5)查询Employees表中员工姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”. SELECT Name AS姓名,CASEWHEN Sex=1 THEN'男'WHEN Sex=0 THEN'女'END AS性别FROM Employees(6)计算每个员工的实际收入.SELECT EmployeeID,实际收入=InCome-OutComeFROM Salary(7)获得员工总数.SELECT COUNT(*)FROM Employees(8)找出所有姓王的雇员的部门号.SELECT DepartmentIDFROM EmployeesWHERE Name LIKE'王%'(9)找出所有收入在2000~3000之间的员工号码.SELECT EmployeeIDFROM SalaryWHERE InCome BETWEEN 2000 AND 3000(10)使用INTO子句，由表Salary创建“收入在1500以上的员工表”，包括编号和收入. SELECT EmployeeID as编号,InCome as收入INTO收入在以上的员工FROM SalaryWHERE InCome>15002、子查询的使用.(1)查询在财务部工作的雇员的情况.SELECT*FROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部')(2)查询财务部年龄不低于研究部雇员年龄的雇员的姓名.SELECT NameFROM EmployeesWHERE DepartmentID IN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部')ANDBirthday!>ALL(SELECT BirthdayFROM EmployeesWHERE DepartmentID IN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='研发部'))(3)查找比所有财务部的雇员收入都高的雇员的姓名.SELECT NameFROM EmployeesWHERE EmployeeID IN(SELECT EmployeeIDFROM SalaryWHERE InCome>ALL(SELECT InComeFROM SalaryWHERE EmployeeID IN(SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=(SELECT EmployeeIDFROM DepartmentsWHERE DepartmentName='财务部')))))3、连接查询的使用(1)查询每个雇员的情况及其薪水的情况SELECT Employees.*,Salary.*FROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeID(2)使用内连接的方法查询名字为“王林”的员工所在的部门SELECT DepartmentNameFROM Departments JOIN EmployeesON Departments.DepartmentID=Employees.DepartmentID WHERE ='王林'(3)查询财务部收入在2000以上的雇员姓名及其薪水详情. SELECT Name,InCome,OutComeFROM Employees,Salary,DepartmentsWHERE Employees.EmployeeID=Salary.EmployeeIDAND Employees.DepartmentID=Departments.DepartmentID AND DepartmentName='财务部'AND InCome>20004、聚合函数的使用.(1)求财务部雇员的平均收入.SELECT AVG(InCome)AS'财务部平均收入'FROM SalaryWHERE EmployeeID IN(SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'))(2)财务部雇员的平均实际收入.SELECT AVG(InCome-OutCome)AS'财务部平均实际收入'FROM SalaryWHERE EmployeeID IN(SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'))(3)求财务部雇员的总人数SELECT COUNT(EmployeeID)FROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部')5、GROUP BY\ORDER BY子句的使用.(1)Emyees表中的男性和女性的人数SELECT Sex,COUNT(Sex)FROM EmployeesGROUP BY Sex;(2)查找员工数超过2人的部门名称和员工数量.SELECT(Employees.DepartmentID),COUNT(*)AS人数FROM Employees,DepartmentsWHERE Employees.DepartmentID=Department.DepartmentIDGROUP BY Employees.DepartmentIDHAVING COUNT(*)>2(3)将各雇员的情况按收入由低到高排列.SELECT Employees.*,Salary.*FROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeIDORDER BY InCome实验4.2 视图的使用1、创建视图①创建YGGL数据库上的视图DS_VIEW,视图包含Departments表的全部列。
②创建YGGL数据库上的视图Employees_view，视图包含员工号码、姓名和实际收入三列。
2、查询视图（1）从视图DS_VIEW,中查询出部门号为3的部门名称。
（2）从视图Employees_view查询出姓名为“王林”的员工的实际收入。
CREATE VIEW Employees_view(EmployeeID,Name,RealIncome)ASSELECT Employees.EmployeeID,Name,InCome-OutComeFROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeID3、更新视图在更新视图前需要了解可更新视图的概念，了解什么视图时不可以进行修改的。
更新视图真正更新的是和视图关联的表。
（1）向视图 DS_VIEW中插入一行数据：“6，广告部，广告业务”。
（2）修改视图DS_VIEW,将部门号为5的部门名称修改为“生产车间”。
（3）修改视图Employees_view中员工号为“000001”的员工姓名为“王浩”。
（4）删除视图DS_VIEW中部门号为“1”的一行数据。
SELECT DepartmentNameFROM DS_VIEWWHERE DepartmentID='3'SELECT RealIncomeFROM Employees_viewWHERE Name='王林'INSERT INTO DS_VIEW VALUES('6','广告部','广告业务部')UPDATE DS_VIEWSET DepartmentName='生产车间'WHERE DepartmentID='5'DELETE FROM DS_VIEWWHERE DepartmentID='1'4、删除视图删除视图DS_VIEW。