数据库实用技术-实验四

数据查询和试图1院（系）计算机学院专业计算机科学与技术班级姓名学号同组人实验室S4305组号日期课程数据库实用技术指导教师成绩实验项目编号8103203004实验项目名称数据查询和视图一、实验目的(1)掌握SELECT语句的基本语法；(2)掌握子查询的表示；(3)掌握连接查询的表示;(4)掌握SELECT语句的GROUPBY子句的作用和使用方法；(5)掌握SELECT语句的ORDERBY子句的作用和使用方法；(6)熟悉视图的概念和作用；(7)掌握视图的创建方法；(8)掌握如何让查询和修改视图。二、实验环境WIN7操作系统SQLServer2008三、实验原理（或要求）（1）了解SELECT语句的基本语法格式；（2）了解SELECT语句的执行方法；（3）了解子查询的表示方法；（4）了解连接查询的表示；（5）了解SELECT语句的GROUPBY子句的作用和使用方法；（6）了解SELECT语句的ORDERBY子句的作用；（7）了解视图的概念；（8）了解创建视图的方法；（9）了解并掌握对视图的操作实验报告单数据查询和试图2四、实验步骤（1）SELECT语句的基本使用①用SELECT语句查询Employees表中所有数据信息【思考与练习】用SELECT语句查询Departments和Salary表中的所有数据信息。USEYGGLGOSELECT*FROMEmployeesUSEYGGLGOSELECT*FROMDepartments,SalaryGO数据查询和试图3②用SELECT语句查询Employees表中每个雇员的地址和电话。【思考与练习】a.用SELECT语句查询Deparments和Salary表的一列或若干列。USEYGGLGOSELECTAddress,PhoneNumberFROMEmployeesGO/*用SELECT语句查询Salary表中的收入和支出*/USEYGGLGOSELECTInCome,OutComeFROMSalaryGO/*用SELECT语句查询Departments表中部门名*/USEYGGLGOSELECTDepartNameFROMDepartmentsGO数据查询和试图4b.用SELECT语句查询Employees表中部门号和性别,并用DISTINCT消除重复行。③查询EmployeeID为000001的雇员的地址和电话。USEYGGLGOSELECTAddress,PhoneNumberFROMEmployeesWHEREEmployeeID='000001'GOUSEYGGLGOSELECTDISTINCT(DepartmentID),sexFROMEmployeesGO数据查询和试图5【思考与练习】a.查询Salary表中月收入高于2000元的员工号码。b.查询1970年以后出生员工的姓名和地址USEYGGLGOSELECTName,AddressFROMEmployeesWHEREBirthday1970GOUSEYGGLGOSELECTEmployeeID,InComeFROMSalaryWHEREInCome2000GO数据查询和试图6c.查询所有财务部的员工的号码和姓名。④查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话。【思考与练习】查询Employees表中男员工的姓名和出生日期，要求将各列标题用中文表示USEYGGLGOSELECTNameAS姓名,BirthdayAS出生日期FROMEmployeesWHERESex=1GOUSEYGGLGOSELECTEmployeeID,namefromEmployeesWHEREDepartmentIDin(SELECTDepartmentIDFROMDepartmentsWHEREDepartName='财务部')GOUSEYGGLGOSELECTAddressAS地址,PhoneNumberAS电话FROMEmployeesWHERESex=0GO数据查询和试图7⑤查询Employees表中员工的姓名和性别，要求SEX值为时显示为“男”，为0时显示为“女”。【思考与练习】查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入。USEYGGLGOSELECTNameAS姓名,CASEWHENSex=1THEN'男'WHENSex=0THEN'女'ENDAS性别FROMEmployeesGO数据查询和试图8⑥计算每个雇员的实际收入。USEYGGLGOSELECTName,Address,CASEWHENIncome-OutCome2000then'低收入'WHENIncome-OutCome3000then'高收入'ELSE'中等收入'ENDAS'收入等级'FROMEmployees,SalaryWHEREEmployees.EmployeeID=Salary.EmployeeIDGOUSEYGGLGOSELECTEmployeeID,实际收入=Income-OutComeFROMSalaryGO数据查询和试图9⑦获得员工总数。a.计算salary表中员工月收入的平均数。b.获得Employees表中最大的员工号码。c.计算salary表中的所有员工的总支出。USEYGGLGOSELECTMAX(EmployeeID)AS'最大员工号码'FROMEmployeesGOUSEYGGLGOSELECTSUM(OutCome)AS'总支出'FROMSalaryGOUSEYGGLGOSELECTCOUNT(*)FROMEmployeesGOUSEYGGLGOSELECTAVG(Income)AS'平均收入'FROMSalaryGO数据查询和试图10d．查询财务部雇员的最高和最低实际收入。⑧找出所有姓王的雇员的部门号。--最高收入SELECTMAX(INcome-OutCome)FROMSalary,Employees,DepartmentsWHERESalary.EmployeeID=Employees.EmployeeIDANDEmployees.DepartmentID=Departments.DepartmentIDANDDepartName='财务部'GOUSEYGGLGOSELECTDepartmentIDFROMEmployeesWHERENameLIKE‘王%’--最低收入SELECTMIN(INcome-OutCome)FROMSalary,Employees,DepartmentsWHERESalary.EmployeeID=Employees.EmployeeIDANDEmployees.DepartmentID=Departments.DepartmentIDANDDepartName='财务部'GO数据查询和试图11【思考与练习】a．找出所有其地址中含有“中山”的雇员的号码及部门．b．查找员工号码倒数第二个数字为0的员工的姓名、地址和学历。⑨找出所有收入在2000-3000元之间的员工号码。SELECTEmployeeID,DepartmentIDFROMEmployeesWHEREAddressLIKE'%中山%'GOSELECTEducation,Address,NameFROMEmployeesWHEREEmployeeIDLIKE'%0_'GOSELECTEmployeeIDFROMSalaryWHEREIncomeBETWEEN2000AND3000GO数据查询和试图12【思考与练习】找出所有在部门“1”或“2”工作的雇员的号码。⑩使用INTO子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和收入【思考与练习】使用INTO子句，由表Employees创建“男员工”表，包括编号和姓名。SELECTEmployeeID,NameINto男员工表FROMEmployeesWHEREsex='1'GOSELECTEmployeeIDas编号,InComeas收入INto收入在元以上的员工FROMSalaryWHEREInCome1500GOSELECTEmployeeIDFROMEmployeesWHEREDepartmentID='1'ORDepartmentID='2'GO数据查询和试图13（2）子查询的使用。①查找在财务部工作的雇员的情况。【思考与练习】用子查询的方法查找所有收入在2500元以下的雇员的情况。②查找财务部年龄不低于研发部雇员年龄的雇员的姓名。SELECT*FROMEmployeesWHEREEmployeeIDIN(SELECTEmployeeIDFROMSalaryWHEREINcome2500)GOSELECTNameFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartName='财务部')ANDSELECT*FROMEmployeesWHEREDepartmentID=(SELECTDepartmentIDFROMDepartmentsWHEREDepartName='财务部')数据查询和试图14【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。SELECTNameFROMEmployeesWHEREEmployeeIDIN(SELECTEmployeeIDFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIDFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartName='研发部'))ANDINcomeALL(SELECTINcomeFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIDFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartName='财务部'))))Birthday!ALL(SELECTBirthdayFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartName='研发部'))数据查询和试图15（3）子查询的使用。①查找在财务部工作的雇员的情况及其薪水的情况。【思考与练习】查询每个雇员的情况及其工作部门的情况。③使用内连接的方法查询名字为“王林”的员工所在的部门.SELECTEmployees.*,Salary.*FROMEmployees,SalaryWHEREEmployees.EmployeeID=Salary.EmployeeIDSELECTEmployees.*,Departments.*FROMEmployees,DepartmentsWHEREEmployees.DepartmentID=Departments.DepartmentIDSELECTDepartNameFROMDepartmentsJOINEmployeesONDepartments.DepartmentID=Employees.DepartmentIDWHEREEmployees.Name='王林'数据查询和试图16【思考与练习】a.使用内连接的方法查找出不在财务部工作的所有员工信息。b.使用外连接方法查找出所有员工的月收入。③查找财务部收入在2000元以上的雇员姓名及其薪水详情。新建一个查询，在查询分析器中输入如下语句并执行。SELECTEmployees.*FROMEmployeesINnerJOINDepartmentsONEmployees.DepartmentID=Departments.Departme