数据库的查询和视图.docx

1、数据库的查询和视图实验四 数据库的查询和视图T4.1 数据库的查询1.目的与要求(1)掌握select语句的基本语法;(2)掌握子查询的表示(3)掌握连接查询的表示(4)掌握select语句的group by子句的作用和使用方法(5)掌握select语句的order by子句的作用和使用方法2 实验准备（1）了解SELECT语句的基本语法格式；（2）了解SELECT语句的执行方法；（3）了解子查询的表示方法；（4）了解连接查询的表示；（5）了解SELECT语句的GROUPBY子句的作用和使用方法；（6）了解SELECT语句的ORDER子句的作用；3实验内容语句的基本使用。对于实验给出的数据库表

2、结构，查询每个雇员的所有数据。新建一个查询，在查询分析器中输入如下语句并执行：GOSELECT* FROM【思考与练习】 用SELECT语句查询Departments和Salary表中所有的数据信息。用SELECT语句查询Employees表中每个雇员的地址和电话。【思考与练习】a. 用SELECT语句查询Deparments和Salary表的一列或若干列。b. 查询Employees表中的部门号和性别，要求使用DISTINCT消除重复行。c. 查询EmployeeID为000001的雇员的地址和电话。【思考与练习】a. 查询月收入高于2000元的员工号码。b. 查询1970年以后出生的员工的

3、姓名和住址。c. 查询所有财务部的员工的号码和姓名。d查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话。【思考与练习】使用语句进行简单的计算。获得员工总数。【思考与练习】计算表中员工月收入的平均数。获得表中最大员工号码。计算表中所有员工的总支出。查询财务部雇员的最高和最低实际收入。USE YGGL SELECT MAX(InCome-OutCome) AS 最高实际收入， MIN(InCome-OutCome) AS 最低实际收入 FROM SalaryGO 找出所有姓王的雇员的部门号。USE YGGLSELECT DepartmentID FR

4、OM Employees WHERE Name LIKE 王% Go 【思考与练习】找出所有其地址中含有“中山”的雇员的号码及部门USE YGGL SELECT ,EmployeeID,DepartmentID FROM EmployeesWHERE Address LIKE 中山GO查找员工号码倒数第二个数字为的员工的姓名、地址和学历。USE YGGL SELECT Name,Address,EducationFROM EmployeesWHERE EmployeeID = LIKE %0_GO 找出所有收入在元之间的员工号码。 （1） 子查询的使用。查找在财务部工作的雇员的情况USE YG

5、GLGOselect * from Employeeswhere DepartmentID= ( select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】用子查询的方法查找所有收入在2500元以下的雇员的情况查找财务部年龄不低于研发部雇员年龄的雇员的姓名。【思考与练习】查询每个雇员的情况及其工作部门的情况。使用内连接的方法查询名字为“王林”的员工所在的部门select DepartmentName from DepartmentsJoin Employees on Departments.DepartmentI

6、D=Employees.DepartmentIDwhere Employees.Name=王林【思考与练习】a 使用内连接的方法查找出不在财务部工作的所有员工信息。b 使用外连接方法查找出所有员工的月收入。1 查找财务部收入在2000元以上的雇员姓名及其薪水详情。USE YGGL【思考与练习】查询研发部在1976以前出生的雇员姓名及其薪水详情。（2） 聚合函数的使用。1.求财务部雇员的平均收入新建一个查询，在查询分析器中输入如下语句并执行。USE YGGLGOselect AVG(Income) as 财务部平均收入 from Salarywhere EmployeeID in (select

7、 EmployeeID from Employeeswhere DepartmentID= (select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】3.求财务部的平均实际收入【思考与练习】求财务部雇员的总人数（5）GROUP BY、ORDER BY子句的使用： 查找Employees表中男性和女性的人数select Sex,COUNT(Sex) from Employeesgroup by Sex;【思考与练习】a 按部门列出在该部门工作的员工的人数。USE YGGLSELECT DepartmentID,C

8、OUNT(DepartmentID) AS 人数 FROM Employees GROUP BY DepartmentIDb 按员工的学历分组，排列出本科、大专和硕士的人数。USE YGGLSELECT Education AS 学历,COUNT(Education) AS 人数 FROM Employees GROUP BY Education查找员工数超过2的部门名称和员工数量。【思考与练习】将各雇员的情况按收入由低到高排列。USE YGGL SELECT* FROM Employees ORDER BY InComeGOT4.2视图的使用1. 目的和要求(1) 熟悉视图的概念和作用；(2

9、) 掌握视图的创建方法；(3) 掌握如何让查询和修改视图。2. 实验准备（1） 了解视图的概念；（2） 了解创建视图的方法；（3） 了解并掌握对视图的操作。3. 实验内容（1） 创建视图。（2） 创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列。CREATE VIEW DS_VIEWAS SELECT*FROM Departments创建YGGL数据库上的视图Employees-view，视图包含“员工号码”、“姓名”、“实际收入”三列。使用如下SQL语句：CREATE VIEW Employees_view（EmployeeID，Name，realIncom

10、e）ASSELECT Employees.EmployeeID，Name，Income-OutComeFROM Employees，salaryWHERE Employees.EmployeeID=salary.EmployeeID【思考与练习】a 在创建视图时SELECT语句有那些限制？不能使用COMPUTE或者COMPUTEBY子句；除非和TOP子句一起使用，否则，不能使用ORDERBY子句；不能使用INTO关键字；不能使用OPTION子句；不能引用临时表和表变量。b 再创建视图时有哪些注意点？1.在CREATE VIEW语句中，不能包括ORDER BY，COMPUTE或者COMPUTE

11、BY 子句也不能出现INTO关键字2.创建视图所参考基表的列数最多为1024列3.创建视图不能参考临时表4.尽量避免使用外连接创建视图5.在一个批处理语句中，CREATE VIEW 语句不能和其他TRANSACT-SQL语句混合使用c 创建视图，包含员工号码、姓名、所在部门名称和实际收入这几列。CREATE VIEW Employees_View(EmployeeID,Name,DepartmentName,RealInCome) AS SELECT Departments.DepartmentID,name,DepartmentName,InCome-OutCome as RealInCom

12、e FROM Employees,Departments,Salary where Departments.DepartmentID =Employees.DepartmentID and Employees.EmployeeID =Salary.EmployeeID（2）查询视图： 从视图DS_VIEW中查询出部门号为3的部门名称。select DepartmentName from DS_viewwhere DepartmentID=3从视图Employees_view中查询出姓名为“王林”的员工的实际收入select RealIncome from Employees_viewwhere

13、 Name=王林【思考与练习】a若视图关联了某表中的所有字段，而此时刻表中添加了新的字段，视图 中能否查询到该字段？不能，必须重新创建视图才能查询到新字段。b自己创建一个视图，并查询视图中的字段。SELECT *FROM Employees_ViewWHERE EmployeeID = 1（3）更新视图。在更新视图前需要了解可更新视图的概念，了解什么视图是不可以进行修改的。更新视图真正更新的是和视图关联的表。向视图DS_VIEW中插入一行数据“6，广告部，广告业务”insert into DS_VIEW values(6,广告部,广告业务)执行完该命令，使用SELECT语句分别查看视图DS_V

14、IEW和基本表Departments中发生的变化。尝试向视图Employees_view中插入一行数据看看会发生什么情况。修改视图DS_VIEW，将部门号为5的部门名称修改为“生产车间”update DS_VIEW set DepartmentName=生产车间where DepartmentID=5执行完该命令，使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。将视图Employees_view中员工号为“000001”的员工的姓名修改为“王浩”update Employees_viewset Name=王浩where EmployeeID=00000

15、1删除视图DS_VIEW中部门号为“1”的一行数据delect from DS_VIEWwhere DepartmentID=1【思考与练习】视图Employees_view中无法插入和删除数据，其中的realincome字段也无法修改，为什么？因为视图employees-view中的字段realincome是基本表列通过计算所得的列，所以无法修改。(4)删除视图删除视图DS_VIEW.drop VIEW DS_VIEW（5）在界面工具中操作视图1 创建视图：启动SQL Server Management Studio，在对象资源管理器中展开“数据库YGGL”选择其中的视图项，右击鼠标，在弹出

16、的快捷菜单上选择“新建视图”菜单项，在随后出现的“添加表”窗口中，添加所需关联的基本表。在视图窗口中的关系图窗口显示基表的全部信息。根据需要在窗口中选择创建视图所需的字段。完成后单击“保存”按钮保存。2 查询视图:新建一个查询，输入T-SQL查询命令即可像查询表一样查询视图。3 删除视图：展开YGGL数据库“视图”，选择要删除的视图，右击选择“删除”选项，确认即可。4. 思考与练习总结视图与基本表的差别。1、视图是已经编译好的sql语句；而表不是。2、视图没有实际的物理记录；而表有。3、视图是窗口；表是内容4、标致用物理空间而视图不占用物理控件，视图只是逻辑概念的存在；表可以即使对它修改，但视图只能有创建的语句来修改。5、表示内模式，视图是外模式。6、视图是产看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些sql语句的集合。从安全的角度来说，视图可以不给用户接触数据表，从而不知道表结构。7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。8、视图的建立和删除只影响视图本身，不影响对应的基本表。