SQL实验四：数据库的查询和视图.doc

学校代码：10128学 号：201220905048 数据库原理及应用实验报告题 目：数据库的查询和视图学生姓名：孙跃学 院：理学院系 别：数学系专 业：信息与计算科学班 级：信计12-2任课教师：侯睿 二 一 五 年 四 月121、 练习目的1、数据库的查询（1）掌握SELECT语句的基本语法；（2）掌握子查询的表示；（3）掌握连接查询的表示；（4）掌握SELECT语句的GROUP BY子句的作用与使用方法；（5）掌握SELECT语句的ORDER BY子句的作用与使用方法；2、视图的使用（1）熟悉视图的概念和作用；（2）熟悉视图的创建方法；（3）熟悉如何查询和修改视图。2、 练习准备1、数据库的查询（1）了解SELECT语句的基本语法格式；（2）了解SELECT语句的执行方法；（3）了解子查询的表示方法；（4）了解连接查询的表示；（5）了解SELECT语句的GROUP BY子句的作用与使用方法；（6）了解SELECT语句的ORDER BY子句的作用；2、视图的使用（1）了解视图的概念；（2）了解创建视图的方法；（3）了解并掌握对视图的操作。3、 实验程序实验4.1 数据库的查询1、(1)对于实验2给出的数据库结构，查询每个雇员的所有数据.USE YGGLGOSELECT * FROM Employees(2)用SELECT语句查询Employees表中每个雇员的地址和电话.SELECT Address,PhoneNumber FROM Employees(3)查询EmployeeID为000001的雇员的地址和电话.SELECT Address,PhoneNumberFROM EmployeesWHERE EmployeeID=000001GO(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)找出所有收入在20003000之间的员工号码.SELECT EmployeeIDFROM SalaryWHERE InCome BETWEEN 2000 AND 3000(10)使用INTO子句，由表Salary创建“收入在1500以上的员工表”，包括编号和收入.SELECT EmployeeID as 编号,InCome as 收入INTO 收入在以上的员工FROM SalaryWHERE InCome15002、子查询的使用.(1)查询在财务部工作的雇员的情况.SELECT *FROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=财务部)(2)查询财务部年龄不低于研究部雇员年龄的雇员的姓名.SELECT NameFROM EmployeesWHERE DepartmentID IN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=财务部)AND Birthday!ALL(SELECT BirthdayFROM EmployeesWHERE DepartmentID IN(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=研发部)(3)查找比所有财务部的雇员收入都高的雇员的姓名.SELECT NameFROM EmployeesWHERE EmployeeID IN(SELECT EmployeeIDFROM SalaryWHERE InComeALL(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.DepartmentIDWHERE Employees.Name=王林(3)查询财务部收入在2000以上的雇员姓名及其薪水详情.SELECT Name,InCome,OutComeFROM Employees,Salary,DepartmentsWHERE Employees.EmployeeID=Salary.EmployeeIDAND Employees.DepartmentID=Departments.DepartmentIDAND DepartmentName=财务部AND InCome20004、聚合函数的使用.(1)求财务部雇员的平均收入.SELECT AVG(InCome)AS财务部平均收入FROM Salary WHERE EmployeeID IN(SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=财务部)(2)财务部雇员的平均实际收入.SELECT AVG(InCome-OutCome)AS财务部平均实际收入FROM Salary WHERE EmployeeID IN(SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=财务部)(3)求财务部雇员的总人数SELECT COUNT(EmployeeID)FROM EmployeesWHERE DepartmentID=(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName=财务部)5、GROUP BYORDER 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、创建视图1 创建YGGL数据库上的视图DS_VIEW,视图包含Departments表的全部列。2 创建YGGL数据库上的视图Employees_view，视图包含员工号码、姓名和实际收入三列。2、查询视图（1）从视图DS_VIEW,中查询出部门号为3的部门名称。（2）从视图Employees_view查询出姓名为“王林”的员工的实际收入。CREATEVIEWEmployees_view(EmployeeID,Name,RealIncome)ASSELECTEmployees.EmployeeID,Name,InCome-OutComeFROMEmployees,SalaryWHEREEmployees.EmployeeID=Salary.EmployeeID3、更新视图 在更新视图前需要了解可更新视图的概念，了解什么视图时不可以进行修改的。更新视图真正更新的是和视图关联的表。（1）向视图 DS_VIEW中插入一行数据：“6，广告部，广告业务”。（2）修改视图DS_VIEW,将部门号为5的部门名称修改为“生产车间”。（3）修改视图Employees_view中员工号为“000001”的员工姓名为“王浩”。（4）删除视图DS_VIEW中部门号为“1”的一行数据。SELECT DepartmentNameFROM DS_VIEWWHERE DepartmentID=3SELECT RealIncomeFROM Employees_viewWHERE Name=王林INSERTINTODS_VIEWVALUES(6,广告部,广告业务部)UPDATEDS_VIEWSETDepartmentName=生产车间WHEREDepartmentID=5DELETEFROMDS_VIEWWHEREDepartmentID=14、删除视图 删除视图DS_VIEW。DROP VIEW DS_VIEW在界面工具中操作视图。（1）创建视图 ：启动“SQL Server Management Studio”然后在“对象资源管理器”中展开“数据库”，在”PXSCJ”中选择其中的“视图”项，右击鼠标，在弹出的的快捷菜单上选择“新建视图”菜单项。在随后出现的添加表窗口中，添加需要关联的基本表。在视图窗口的关系图窗口显示基表的全部列信息。根据需要在窗口中选择创建视图所需的字段。完成后单击“保存”按钮保存。（2）查询视图：新建一个查询，输入T-SQL查询命令即可和查询表一样查询视图。删除视图：展开YGGL数据库-“视图”-选择要删除的视图-右击选择“删除”选项，确认即可。4、 实验结果一、(1) 对于实验2给出的数据库结构，查询每个雇员的所有数据.(2)用SELECT语句查询Employees表中每个雇员的地址和电话.(3)查询EmployeeID为000001的雇员的地址和电话.(4)查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别制定地址和电话(5)查询Employees表中员工姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”.(6)计算每个员工的实际收入.(7)获得员工总数.(8)找出所有姓王的雇员的部门号.(9)找出所有收入在20003000之间的员工号码.(10)使用INTO子句，由表Salary创建“收入在1500以上的员工表”，包括编号和收入.二、子查询的使用.(1)查询在财务部工作的雇员的情况.(2)查询财务部年龄不低于研究部雇员年龄的雇员的姓名.(3)查找比所有财务部的雇员收入都高的雇员的姓名.三、连接查询的使用.(1)查询每个雇员的情况及其薪水的情况(2)使用内连接的方法查询名字为“王林”的员工所在的部门(3)查询财务部收入在2000以上的雇员姓名及其薪水详情.四、聚合函数的使用.(1)求财务部雇员的平均收入.(2)财务部雇员的平均实际收入.(3)求财务部雇员的总人数五、GROUP BYORDER BY子句的使用.(1)Emoyee