SQLServer实用教程(第三版)实验4数据库的查询和视图.pdf

实验四 数据库的查询和视图 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 实验内容 语句的基本使用。对于实验给出的数据库表结构，查询每个雇员的所有数据。新建一个查询，在查询分析器中输入如下语句并执行：GO SELECT*FROM 【思考与练习】用 SELECT 语句查询 Departments 和 Salary 表中所有的数据信息。用 SELECT 语句查询 Employees 表中每个雇员的地址和电话。新建一个查询，在查询分析器中输入如下语句并执行：Use YGGL GO SELECT Address PhoneNumber FROM Employees【思考与练习】a.用 SELECT 语句查询 Deparments 和 Salary 表的一列或若干列。b.查询 Employees 表中的部门号和性别，要求使用 DISTINCT 消除重复行。c.查询 EmployeeID 为 000001 的雇员的地址和电话。Use YGGL GO SELECT Address PhoneNumber FROM Employees WHERE EmployeeID=000001 【思考与练习】a.查询月收入高于 2000 元的员工号码。b.查询 1970 年以后出生的员工的姓名和住址。c.查询所有财务部的员工的号码和姓名。查询 Employees 表中女雇员的地址和电话，使用 AS 子句将结果中各列的标题分别指定为地址、电话。USE YGGL GO SELECT Address AS 地址，PhoneNumber AS 电话 FROM Employees WHERE Sex=0【思考与练习】查询 Employees 表中男员工的姓名和出生日期，要求将各列标题用中文标示。查询 Employees 表中员工的姓名和性别，要求 SEX 值为时显示为“男”，为 0时显示为“女”。SELECT 姓名，Sex=1 THEN 男 Sex=1 THEN 女 END 性别 Employees【思考与练习】查询 Employees 员工的姓名、住址和收入水平，2000 元以下显示低收入，2000-3000 元显示为中等收入，3000 元以上显示为高收入。计算每个雇员的实际收入 USE YGGL GO SELECT EployeeID,实际收入=income-OutCome FROM【思考与练习】使用语句进行简单的计算。获得员工总数。（）FROM 【思考与练习】计算表中员工月收入的平均数。获得表中最大员工号码。计算表中所有员工的总支出。查询财务部雇员的最高和最低实际收入。找出所有姓王的雇员的部门号。USE YGGL GO SE 王【思考与练习】找出所有其地址中含有“中山”的雇员的号码及部门 查找员工号码倒数第二个数字为的员工的姓名、地址和学历。找出所有收入在元之间的员工号码。SELECT EmployeeID from Salary where InCome between 2000 and 3000【思考与练习】找出所有在部门“1”或“2”工作的雇员的号码。注意：了解在SELECT语句中LIKE、BETWEENAND、IN、NOT以及CONTAIN谓词的作用。使用 into 子句，由表 Salary 创建“收入在 1500 元以上的员工”表，包括编号和收入 USE YGGL GO select EmployeeID as 编号,Income as 收入 into 收入在1500元以上的员工 from Salary where InCome1500【思考与练习】使用 Into 子句，由表 Employees 创建“男员工”表，包括编号和姓名。（）子查询的使用。查找在财务部工作的雇员的情况 USE YGGL GO select*from Employees where DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】用子查询的方法查找所有收入在 2500 元以下的雇员的情况 查找财务部年龄不低于研发部雇员年龄的雇员的姓名。USE YGGL GO select Name from Employees where DepartmentID in (select DepartmentID from Departments where DepartmentName=财务部 )and Birthday！all (select Birthday from Employees where DepartmentID in(select DepartmentID from Departments where DepartmentName=研发部 )【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。查找比所有财务部的雇员收入都高的雇员的姓名。USE YGGL GO Select Name from Employees where EmployeeID in (select EmployeeID from Salary where InCome all (select InCome from Salary where EmployeeID in (select EmployeeID from Employees where DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。（2）连接查询的使用。查询每个雇员的情况及其薪水的情况。USE YGGL GO select Employees.*,Salary.*from Employees,Salary where Employees.EmployeeID=Salary.EmployeeID【思考与练习】查询每个雇员的情况及其工作部门的情况。使用内连接的方法查询名字为“王林”的员工所在的部门 select DepartmentName from Departments Join Employees on Departments.DepartmentID=Employees.DepartmentID where Employees.Name=王林【思考与练习】a 使用内连接的方法查找出不在财务部工作的所有员工信息。b 使用外连接方法查找出所有员工的月收入。查找财务部收入在 2000 元以上的雇员姓名及其薪水详情。新建一个查询，在查询分析器中输入如下语句并执行。select Name,Income,Outcome from Employees,Salary,Departments where Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName=财务部 and Income2000【思考与练习】查询研发部在 1976 以前出生的雇员姓名及其薪水详情。（）聚合函数的使用。1.求财务部雇员的平均收入 新建一个查询，在查询分析器中输入如下语句并执行。USE YGGL GO select AVG(Income)as 财务部平均收入 from Salary where EmployeeID in (select EmployeeID from Employees where DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】查询财务部雇员的最高和最低收入。3.求财务部的平均实际收入 新建一个查询，在查询分析其中输入如下语句并执行。USE YGGL GO select avg(Income-Outcome)as 财务部平均实际收入 from Salary where EmployeeID in (select EmployeeID from Employees where DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】查询财务部雇员的最高和最低实际收入。求财务部雇员的总人数 select COUNT(EmployeeID)from Employees where DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部 )【思考与练习】统计财务部收入在 2500 以上的雇员的人数。（5）GROUP BY、ORDER BY 子句的使用：查找 Employees 表中男性和女性的人数 select Sex,COUNT(Sex)from Employees group by Sex;【思考与练习】a 按部门列出在该部门工作的员工的人数。b 按员工的学历分组，排列出本科、大专和硕士的人数。查找员工数超过 2 的部门名称和员工数量。select Employees.DepartmentID,COUNT(*)as 人数 from Employees,Departments WHERE Employees.DepartmentID=Departments.DepartmentID group by Employees.DepartmentID having COUNT(*)2【思考与练习】按员工的工作年份分组，统计各个工作年份的人数，例如，工作一年的多少人，工作两年的多少人。将各雇员的情况按收入由低到高排列。select Employees.*,Salary.*from Employees,Salary where Employees.EmployeeID=Salary.EmployeeID order by InCome【思考与练习】a将员工的信息按出生的时间从小到大排列。b.在 order by 子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列。T4.2 视图的使用 1.目的和要求(1)熟悉视图的概念和作用；(2)掌握视图的创建方法；(3)掌握如何让查询和修改视图。2.实验准备（）了解视图的概念；（）了解创建视图的方法；（）了解并掌握对视图的操作。3.实验内容（）创建视图。（）创建 YGGL 数据库上的视图 DS_VIEW，视图包含 Departments 表的全部列。CREATE VIEW DS_VIEW AS SELECT*FROM Departments 创建 YGGL 数据库上的视图 Employees-view，视图包含“员工号码”、“姓名”、“实际收入”三列。使用如下 SQL 语句：CREATE VIEW Employees_view（EmployeeID，Name，realIncome）AS SELECT Employees.EmployeeID，Name，Income-OutCome FROM Employees，salary WHERE Employees.EmployeeID=salary.EmployeeID【思考与练习】a 在创建视图时 SELECT 语句有那些限制？b 再创建视图时有哪些注意点？c 创建视图，包含员工号码、姓名、所在部门名称和实际收入这几列。（2）查询视图：从视图 DS_VIEW 中查询出部门号为 3 的部门名称。select DepartmentName from DS_view where DepartmentID=3 从视图 Employees_view 中查询出姓名为“王林”的员工的实际收入 select RealIncome from Employees_view where Name=王林【思考与练习】a 若视图关联了某表中的所有字段，而此时刻表中添加了新的字段，视图 中能否查询到该字段？b自己创建一个视图，并查询视图中的字段。（3）更新视图。在更新视图前需要了解可更新视图的概念，了解什么视图是不可以进行修改的。更新视图真正更新的是和视图关联的表。向视图 DS_VIEW 中插入一行数据“6，广告部，广告业务”insert into DS_VIEW values(6,广告部,广告业务)执行完该命令，使用 SELECT 语句分别查看视图 DS_VIEW 和基本表Departments 中发生的变化。尝试向视图 Employees_view 中插入一行数据看看会发生什么情况。修改视图 DS_VIEW，将部门号为 5 的部门名称修改为“生产车间”update DS_VIEW set DepartmentName=生产车间 where DepartmentID=5 执行完该命令，使用 SELECT 语句分别查看视图 DS_VIEW 和基本表Departments 中发生的变化。将视图 Employees_view中员工号为“000001”的员工的姓名修改为“王浩”update Employees_view set Name=王浩 where EmployeeID=000001 删除视图 DS_VIEW 中部门号为“1”的一行数据 delect from DS_VIEW where DepartmentID=1【思考与练习】视图 Employees_view 中无法插入和删除数据，其中的realincome 字段也无法修改，为什么？(4)删除视图 删除视图 DS_VIEW.drop VIEW DS_VIEW（5）在界面工具中操作视图 创建视图：启动 SQL Server Management Studio，在对象资源管理器中展开“数据库YGGL”选择其中的视图项，右击鼠标，在弹出的快捷菜单上选择“新建视图”菜单项，在随后出现的“添加表”窗口中，添加所需关联的基本表。在视图窗口中的关系图窗口显示基表的全部信息。根据需要在窗口中选择创建视图所需的字段。完成后单击“保存”按钮保存。查询视图:新建一个查询，输入 T-SQL 查询命令即可像查询表一样查询视图。删除视图：展开 YGGL 数据库“视图”，选择要删除的视图，右击选择“删除”选项，确认即可。4.思考与练习 总结视图与基本表的差别。