SQL实验四:数据库的查询和视图
- 格式:doc
- 大小:927.35 KB
- 文档页数:13
在学生选课数据库中完成规定查询,并创建视图。
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”选项。
实验四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- 错误!未定义书签。
二 〇 一 五 年 四 月题 目:数据库的查询和视图 学生姓名:孙跃 学 院:理学院 系 别:数学系专 业:信息与计算科学 班 级:信计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。