SQL实验四数据库的查询和视图.doc
1、 练习目的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,PhoneNumberFROM Employees<3>查询EmployeeID为000001的雇员的地址和 .SELECT Address,PhoneNumberFROM EmployeesWHERE EmployeeID='000001'GO<4>查询Employees表中女雇员的地址和 ,使用AS子句将结果中各列的标题分别制定地址和 .SELECT Address AS 地址,PhoneNumber ASFROM EmployeesWHERE Sex=0<5>查询Employees表中员工#和性别,要求Sex值为1时显示为"男",为0时显示为"女".SELECTNameAS #,CASEWHEN Sex=1 THEN'男'WHEN Sex=0 THEN'女'ENDAS 性别FROM Employees<6>计算每个员工的实际收入.SELECT EmployeeID,实际收入=Ine-OuteFROM Salary<7>获得员工总数.SELECTCOUNT<*>FROM Employees<8>找出所有姓王的雇员的部门号.SELECT DepartmentIDFROM EmployeesWHERENameLIKE'王%'<9>找出所有收入在20003000之间的员工.SELECT EmployeeIDFROM SalaryWHERE Ine BETWEEN 2000 AND 3000<10>使用INTO子句,由表Salary创建"收入在1500以上的员工表",包括编号和收入.SELECT EmployeeID as 编号,Ine as 收入INTO 收入在以上的员工FROM SalaryWHERE Ine>15002、子查询的使用.<1>查询在财务部工作的雇员的情况.SELECT*FROM EmployeesWHERE DepartmentID=<SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'><2>查询财务部年龄不低于研究部雇员年龄的雇员的#.SELECTNameFROM EmployeesWHERE DepartmentID IN<SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'>ANDBirthday!>ALL<SELECT BirthdayFROM EmployeesWHERE DepartmentID IN<SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='研发部'>><3>查找比所有财务部的雇员收入都高的雇员的#.SELECTNameFROM EmployeesWHERE EmployeeID IN<SELECT EmployeeIDFROM SalaryWHERE Ine>ALL<SELECT IneFROM 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以上的雇员#与其薪水详情.SELECTName,Ine,OuteFROM Employees,Salary,DepartmentsWHERE Employees.EmployeeID=Salary.EmployeeIDAND Employees.DepartmentID=Departments.DepartmentIDAND DepartmentName='财务部'AND Ine>20004、聚合函数的使用.<1>求财务部雇员的平均收入.SELECTAVG<Ine>AS'财务部平均收入'FROM Salary WHERE EmployeeID IN<SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=<SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'>><2>财务部雇员的平均实际收入.SELECTAVG<Ine-Oute>AS'财务部平均实际收入'FROM Salary WHERE EmployeeID IN<SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=<SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'>><3>求财务部雇员的总人数SELECTCOUNT<EmployeeID>FROM EmployeesWHERE DepartmentID=<SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName='财务部'>5、GROUP BYORDER BY子句的使用.<1>Emyees表中的男性和女性的人数SELECT Sex,COUNT<Sex>FROM EmployeesGROUPBY Sex;<2>查找员工数超过2人的部门名称和员工数量.SELECT<Employees.DepartmentID>,COUNT<*>AS人数FROM Employees,DepartmentsWHERE Employees.DepartmentID=Department.DepartmentIDGROUPBY Employees.DepartmentIDHAVINGCOUNT<*>>2<3>将各雇员的情况按收入由低到高排列.SELECT Employees.*,Salary.*FROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeIDORDER BY Ine实验4.2 视图的使用1、创建视图1 创建YGGL数据库上的视图DS_VIEW,视图包含Departments表的全部列.2 创建YGGL数据库上的视图Employees_view,视图包含员工、#和实际收入三列.2、查询视图1从视图DS_VIEW,中查询出部门号为3的部门名称.2从视图Employees_view查询出#为"王林"的员工的实际收入.CREATE VIEW Employees_view<EmployeeID,Name,RealIne>ASSELECT Employees.EmployeeID,Name,Ine-OuteFROM 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 RealIneFROM Employees_viewWHERE Name='王林'INSERT INTO DS_VIEW VALUES<'6','广告部','广告业务部'>UPDATE DS_VIEW SET DepartmentName='生产车间'WHERE DepartmentID='5'DELETE FROM DS_VIEWWHERE DepartmentID='1'4、删除视图 删除视图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>Emoyees表中的男性和女性的人数<2>查找员工数超过2人的部门名称和员工数量.<3>将各雇员的情况按收入由低到高排列.实验2.2视图的使用的结果5、 实验总结通本次实验,我对数据库的查询和视图进行了上机练习,对课堂上所学的知识进行了重新巩固,对不明白的地方重新进行了复习,在上机操作方面自己也有了很大的提高,掌握了SELECE语句的基本语法、GROUP BY、ORDER BY语句的作用和使用方法,子查询的表示,连接查询的表示,熟悉了视图的概念和作用,掌握了视图的创建、查询和修改,通过实验我也体会到上机操作一定要谨慎认真,否则会导致意外的麻烦.
