实验三数据库的查询和视图.pdf

-.z.实验三数据库的查询和视图 T4.1 数据库的查询 1、目的和要求（1）掌握 SELECT 语句的基本语法。（2）掌握子查询的表示。（3）掌握连接查询的表示。（4）掌握 SELECT 语句的 GROUP BY 子句的作用和使用方法。（5）掌握 SELECT 语句的 ORDER BY 子句的作用和使用方法。2、实验准备（1）了解 SELECT 语句的基本语法格式。（2）了解 SELECT 语句的执行方法。（3）了解子查询的表示方法。（4）了解连接查询的表示。（5）了解 SELECT 语句的 GROUP BY 子句的作用和使用方法。（6）了解 SELECT 语句的 ORDER BY 子句的作用。3、实验内容（1）SELECT 语句的基本使用。对于实验二给出的数据库表结构，查询每个雇员的所有数据。新建一个查询，在“查询分析器”窗口中输入如下语句并执行。单击右键空白处，选择执行，在下方会出现所有结果。【思考与练习】用 SELECT 语句查询 Departments 表和 Salary 表中所有信息。新建一个查询，在“查询分析器”窗口中输入如下语句并执行。单击右键空白处，选择执行，在下方会出现所有结果。在“消息”中会发现总共有 60 行受影响。用 SECECT 语句查询 Employees 表中每个雇员的地址和。新建一个查询，在“查询分析器”窗口中输入如下代码并执行。单击右键空白处，选择执行，在下方会出现所有结果。在“消息”中会发现有 12 行记录受影响【思考与练习】A、用 SELECT 语句查询 Departments 表和 Salary 表的一列或若干行。利用 SELECT 语句查询 Departments 表和 Salary 表中 EmployeeID 为 000001 的记录，在“查询分析器”中输入以下代码。执行后在下方会出现所有 EmployeeID=000001 的记录 B、查询 Employees 表中部门号和性别，要求使用 DISTINCT 消除重复行。对表只选择*些行时，可能会出现重复行。此时就需要使用 DISTINCT 关键字消除结果中的重复行。此时使用 DISTINCT 消除在 Employees 中查询到的部门号和性别的重复行，具体代码如下。上面代码分别是使用了 DISTINCT 和没有使用 DISTINCT 关键字，下面就是两种的结果的对比。可以从上面的执行结果中看出使用了 DISTINCT 关键字，结果集中的重复行就被消除了。查询 EmployeeID 为 000001 的雇员的地址和。-.z.鼠标右键单击在弹出的菜单窗口中选择“执行”，结果中只会出现一行记录，EmployeeID为 000001 的雇员的地址和。【思考与练习】A、查询月收入高于 2000 元的员工。只需要查询员工，条件为月收入高于 2000 元员工，则至于要 Salary 表就行。用 T-SQL命令在表 Salary 表中查询月收入高于 2000 元的员工代码如下。在结果栏中显示(9 行受影响)，说明月收入高于 2000 的员工共有 9 人，如上截图所示，将所有月收入高于 2000 的员工的员工输出。B、查询 1970 年以后出生的员工的*和地址。通过分析员工信息，跟员工年龄、*、地址相关的只有表 Employees 表，所以只需要在一*表中查询即可，具体 T-SQL 查询命令如下。在结果栏中消息显示(7 行受影响)，说明 1970 年以后出生有 7 人，如上截图所示，将部分 1970 年以后出生员工*、地址输出显示。C、查询所有财务部的员工的和*。通过分析的，需要知道财务部与员工的和*，财务部只存在于 Departments 表中，员工*和只存在于 Employees 表中。因此需要在表 Departments 表和 Employees 表中进行查询。利用 T-SQL 命令查询代码如下。在结果栏中显示 3 行受影响，从上面截图中可以看出所有财务部员工有 3 人，并且查询显示其*、。查询 Employees 表中女雇员的地址和，使用 AS 子句将结果中各列的标题分别指定为地址、。从上面结果的截图发现 Address 变为地址，PhoneNumber 变为。【思考与练习】查询 Employees 表中男员工的*和出生日期，要求各列标题用中文表示。通过分析此查询属于单表查询，只需要用到 Employees 表，使用 AS 子句就能够使结果集中各列标题用中文显示。利用 T-SQL 命令查询代码如下。从上面结果的截图发现各列标题都是中文。从消息中显示(9 行受影响)说明，员工中男性有 9 人，并且全部男性的*和出生日期都已经查询显示出。查询Employees表中员工的*和性别，要求Se*值为1时显示为“男”，为0时显示为“女”。【思考与练习】查询 Employees 员工的*、住址和收入水平，2000 元以下显示为低收入，20003000元显示为中等收入，3000 元以上显示高收入。经过分析需要查询员工的*、住址和收入水平，需要在 Employees 表和 Salary 表中进行查询，并且将 Salary 表中员工的 Ine 进行分类需要用到 AS 子句。利用 T-SQL 命令进行多表查询语句如下。执行上面截图中代码，在结果栏中出现员工的*，住址和收入水平。消息中显示 12 行受影响，Employees 表中所有员工信息都已经查询输出。计算每个雇员的实际收入。经过分析需要员工的收入以及员工和员工*，就需要在 Employees 表和 Salary 表中进行查询。利用 T-SQL 命令查询如下。结果栏中就会显示 EmployeeID，Name，实际收入这三项。注意：在上面 T-SQL 命令中 EmployeeID 如果不指明为哪个表中的 EmployeeID，代码就会报错“EmployeeID 不明确”，改为 Employees.EmployeeID 或者 Salary.EmployeeID 都行。【思考与练习】-.z.使用 SELECT 语句进行简单的计算。在这里我将使用 SELECT 语句计算出 Employees 表中工龄三年以上的人数，并且查询员工，*，工龄。获得员工总数。经分析得需要得到员工总数，只需要在 Employees 表内使用内置函数 COUNT 就行。利用 T-SQL 命令执行操作如下。【思考与练习】A、计算 Salary 表中员工月收入的平均数。B、获得 Employees 表中最大的员工。C、计算 Salary 表中所有员工的总支出。D、查询财务部雇员的最高和最低实际收入。经过分析需要查询财务部雇员的最高和最低实际收入，涉及到 Employees 表、Departments 表和 Salary 表，需要在三*表中进行查询。首先确定 DepartmentName 为“财务部”的 DepartmentID。其次在 Employees 表中确定 DepartmentID 为财务部的雇员的 EmployeeID。最后在 Salary 表中通过 EmployeeID 查询财务部雇员的 Ine 和 Oute，在最后的输出时候用 Ine-Oute 的结果为实际收入。执行后在结果栏中看到财务部雇员的最高实际收入和最低实际收入。找出所有姓王的雇员的部门号。【思考与练习】A、找出所有地址中含有“*”的雇员的及部门号。注意：%代表着任意长度的字符串（长度可以为 0），“%*%”表示“*”前面和后面都能够有任意长度字符串，用来表示地址中含有“*”二字。B、查找员工中倒数第 2 个数字为 0 的员工的*、地址和学历。注意：数字占单个字符，所以通配符“_”只有一个，“%0_”表示倒数第二位为 0。找出所有收入在 20003000 元的员工。【思考与练习】找出所有在部门“1”或“2”工作的雇员的。10 使用 INTO 子句，由表 Salary 创建“收入在 1500 元以上的员工”表，包括编号和收入。在左边对象资源管理器的 YGGL 数据库的表中刷新，会发现多了表“收入在 1500 元以上的员工”。【思考与练习】使用 INTO 子句，由表 Employees 创建“男员工”表，包括编号和*。（2）子查询的使用。查找在财务部工作的雇员的情况。查询财务部工作的雇员情况，需要用到 Department 表和 Employees 表进行多表查询。首先，在 Department 表中查询 DepartmentName 为“财务部”的 DepartmentID。然后，将 DepartmentID 拿到表 Employees 表中查询所有 DepartmentID 为财务部的雇员的所有信息。利用 T-SQL 命令查询代码如下。在结果栏中显示所有工作部门为“财务部”的雇员的详细信息。【思考与练习】用子查询的方法查找所有收入在 2500 元以下的雇员的情况。进分析需要用到工资表 Salary 和员工信息表 Employees 进行查询。-.z.首先，在表 Salary 中查询 Ineall”查询到的是“李丽”，年龄小于所有研发部的，所有书本上源码表示的是“财务部雇员年龄不大于研发部雇员年龄”与题目中“不低于”冲突。所以书上源码有误，将源码改成上面截图中第 12 行即可。【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的*。分析：首先在 Employees 表里找到 EmployeeName 为“研发部”的雇员的*和查找雇员的EmployeeID，其次这样的 EmployeeID 还需要满足 Ine 大于所有的 DepartmentName 为“财务部”的雇员的 Ine，满足条件返回 True，select 到满足这样条件的雇员的 Name，否则返回FALSE。Where 子查询中嵌套使用。执行 T-SQL 命令后在结果栏中显示只有 1 人满足。（3）连接查询的使用。查询每个雇员的情况及其薪水情况。执行之后，将 Employees 表和 Salary 表中所有员工的基本情况进行输出。不足之处就是 EmployeeID 这一列有重复。【思考与练习】查询每个雇员的情况及其工作部门的情况。执行后将每个雇员的基本情况、工资情况、工作部门情况输出。使用内连接的方法查询名字为“王林”的员工的所在部门。【思考与练习】A、使用内连接方法查找出不在财务部工作的所有员工信息。内连接是指定了关键字INNER的连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。此处 INNER 关键字能够省略。查找所有不在财务部工作的员工的信息，就需要使用员工基本信息表 Employees 和部门信息表 Department 进行查询。T-SQL 命令语句如下。执行结果包含表 Employees 和表 Department 的所有字段，不去除重复字段（DepartmentID）。B、使用外连接方法查找出所有员工的月收入。外连接为指定了关键字 OUTER 关键字的连接，外连接包括以下三种连接。左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表-.z.的所有行。右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行。完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。在这里将表 Employees 和表 Salary 做右连接查询，结果表中除了包括连接条件Employees.EmployeeID=Salary.EmployeeID 外，还将包括右表（Salary 表）中的所有行（Ine和 Oute）。执行代码结果如下。查找财务部收入在 2000 元以上的雇员*及其薪水情况。分析得需要财务部收入2000元以上雇员*和薪水情况，涉及雇员基本信息的Employees表、部门信息 Department 表和薪水信息 Salary 表。此时就用到 where 语句里面多表查询将三*表做自然连接。执行上面截图中代码，在下面结果栏中显示财务部工资在 2000 元以上的雇员的*和薪水情况。【思考与练习】查询研发部在 1976 年以前出生的雇员*及其薪水情况。将三*表做自然连接后查询满足条件的雇员的*、收入和支出。（4）聚合函数的使用 求财务部雇员的平均收入。新建一个查询，在“查询分析器”窗口中输入如下语句并执行。执行截图中代码，结果栏中显示才而不平均收入。【思考与练习】查询财务部雇员的最高收入和最低收入。查询最高和最低收入，将用到聚合函数表中的 MA*（求最大值）和 MIN（求最小值）。执行上面截图中 T-SQL 命令，在结果栏中显示财务部雇员最高收入和最低收入。求财务部雇员的平均实际收入。新建一个查询，在“查询分析器”窗口中输入如下语句并执行。执行上面截图中的 T-SQL 命令，在结果栏中显示财务部雇员平均实际收入。【思考与练习】查询财务部雇员的最高和最低实际收入。需要财务部实际收入需要用表 Salary 中的 Ine 字段和 Oute 字段。其次用到聚合函数MA*和MIN。最后在用where子查询找到DepartmentName为“财务部”的雇员的EmployeeID。执行上面截图中的 T-SQL 命令，在结果栏中显示财务部雇员最高和最低实际收入。（5）GROUP BY、ORDER BY 子句的使用。查找 Employees 表中男性和女性人数。【思考与练习】A、按部门列出在该部门工作的员工的人数。B、按员工的学历分组，排列出本科、大专和硕士的人数。查找员工数超过 2 的部门名称和员工数量。执行上面截图中代码，在结果栏中显示员工数超过 2 的部门名称以及人数。【思考与练习】按照员工的工作年份分组，统计各个工作年份的人数，例如，工作 1 年的多少人，工作2 年的多少人。-.z.分析只需要 Employees 表就能够完成，通过 GROUP BY 对 WorkYear 进行分组，并统各个年份的人数。将各个雇员的情况按收入由低到高排列。新建一个查询，在“查询分析器”窗口中输入如下语句并执行。需要对雇员的收入情况进行排序，就需要使用到谓词 ORDER BY，谓词 ORDER BY 后面能够接多个排序的变量名，第一个变量为主序，下面依次类推。每一个排序列名后面可用限定词 ASC（升序）或者 DESC（降序）声明排序的方式，缺省为升序（如果不接限定词系统默认的为 ASC（升序）。下面为执行上面截图中 T-SQL 命令，在结果栏中显示的查询结果。可以观察雇员的基本信息是按照 Ine 升序排列（由低到高）。【思考与练习】A、将员工信息按照出生时间从小到大排列。执行上面截图中的 T-SQL 命令，在下面结果栏中显示雇员信息根据出生日期从小到大排列。C、在 ODER BY 子句中使用子查询，查询员工*，性别和工龄信息，要求按照实际收入从大到小排序。经分析将表 Employees 和表 Salary 根据 EmployeeID 相同做自然连接，其次根据实际收入（Ine-Oute）进行 ORDER BY 排序。最后，由于是根据实际收入（Ine-Oute）从大到小进行排序，则接在 ORDER BY 后面的谓词就需要换成 DESC。根据上面截图中 T-SQL 命令，在结果栏中显示根据实际收入进行排序输出雇员的*，性别和工龄信息。T4.1 视图的使用 1、目的和要求（1）熟悉视图的概念和作用。（2）掌握视图的创建方法。（3）掌握如何查询和修改视图。2、实验准备（1）了解视图的概念（2）了解创建视图的方法。（3）了解并掌握对视图的操作。3、实验内容（1）创建视图。创建 YGGL 数据库上的视图 DS_VIEW，视图包含 Departments 表的全部列。新建“查询分析器”窗口，在其中输入以上 T-SQL 命令，点击执行按钮。在 YGGL 数据库的视图中进行刷新，就会显示创建的 DS_VIEW 视图，如下所示。创建 YGGL 数据库上的视图 Employees_view，视图包含员工、*和实际收入 3 列。使用如下 T-SQL 命令进行操作。执行上面截图中代码，在 YGGL 数据库的视图中进行刷新，显示视图 Employees_view，如下所示。【思考与练习】A、在创建视图时 SELECT 有哪些限制？用来创建视图的 SELECT 语句，可在 SELECT 语句中查询多个表或者视图，以表明新创-.z.的视图所参照的表或视图。B、在创建视图时有哪些注意点？只有在当前数据库中才能够创建视图。视图的命名必须遵循标识符命名规则，不能够与表同名。不能够把规则、默认值或触发器与视图相关联。C、创建视图，包含员工、*、所在部门名称和实际收入这几列。在数据库 YGGL 的视图中刷新，会显示刚刚创建的视图 test_view，包含员工、*、员工部门名称和实际收入这几列。结果如下图所示。（2）查询视图 从视图 DS_VIEW 中查询部门号为 3 的部门名称。注：在上面代码下是显示错误，但是代码逻辑都正确，而且也能够正确执行，并且输出正确的结果。（不知道是什么原因）从视图 Employees_view 中查询*为“王林”的员工的实际收入。【思考与练习】A、若视图关联了*表中的所有字段，而此时该表中添加了新的字段，视图中能否查询到该字段？首先建立视图 Employees_view 并且关联表 Employees 中的所有字段。然后在表 Employees 中插入一行新的数据。最后在“查询分析器”中查询 EmployeeID 等于“000000”的雇员的所有信息。从上面的 T-SQL 命令可以看出，当视图关联的表中添加新的字段时，视图中是无法查询到该字段的，必须重新创建视图才能够查询到新添加的字段。B、自己创建一个视图，并查询视图中的字段。利用 T-SQL 命令创建一个视图 Employees_view，该视图包含雇员 EmployeeID、Name和 RealIne（实际收入），并且查询该视图中*为“王林”的雇员的基本情况。具体 T-SQL 命令代码如下。查询该视图中*为“王林”的雇员的基本情况。（3）更新视图。在更新视图前需要了解可更新视图的概念，了解什么视图时不可以进行修改的。更新视图真正更新的是和视图关联额表。向 DS_VIEW 中插入一行数据“6，广告部，广告业务”。当在“查询分析器”中输入上面代码时，会出现这样的错误。规则已经和 Departments。DepartmentID 绑定，只需要解除绑定就行了。则就将前面的 T-SQL 修改为这样就行了。当再次执行时就会发现，Departments.DepartmentID 已经没有了规则，也就说明第一次执行时规则已经解除绑定。同样当再次执行时会出字段字段已经出现，不能够重复输入primary key 相同的记录。继续在视图中刷新重新打开“编辑前 200 行”就会发现字段已经插入。修改视图 DS_VIEW，将部门号为 5 的部门名称修改为“生产车间”。执行上面的 T-SQL 命令，将更新数据之前和之后的 DS_VIEW 表进行对照，发现数据已经成功更新，说明 T-SQL 命令执行成功。DepartmentID 为 5 的 DepartmentName 已经更新为“生产车间”。执行完该命令后，使用 select 语句分别查看视图 DS_VIEW 和和基本表 Departments 中发生的变化。基本表 Departments 中的改变。-.z.视图 DS_VIEW 中发生的改变，见上面截图。将视图 Employee_view 中的员工号为“000001”的员工的*修改为“王浩”。执行上面的 T-SQL 命令后，打开视图 Employees_view，可以看出 EmployeeID=000001的雇员的 Name 已经修改为“王浩”。修改视图 DS_VIEW 中部门号为“1”的一行数据。【思考与练习】视图 Employees_view 中无法插入和删除数据，其中的 RealIne 字段也无法修改，为什么？要通过视图更新（插入、修改和删除）基本数据，必须保证视图为可更新视图，只有满足一下条件的视图才是可更新视图。A、创建视图的 SELECT 语句中没有聚合函数，且没有 TOP、GROUP BY、UNION 子句及 DISTINCT 关键字。B、创建视图的 SELECT 语句不包含从基本表列通过计算得到的列。C、创建视图的 SELECT 语句的 FROM 子句中至少要包含一个基本表。视图 Employees_view 的 SELECT 语句中的 RealIne 是通过 Ine-Oute 计算得来的，所以Employees_view 是不可更新的视图，所以也无法向其中修改数据，同样其中的 RealIne 字段也是不可修改的。（4）删除视图。删除视图 DS_VIEW。执行上面 T-SQL 命令，刷新对象资源管理器中视图，已经没有视图 DS_VIEW。（5）在界面工具中操作视图。创建视图：启动 SQL Server Management Studio，在对象资源管理器中展开“数据库-YGGL”，选择其中的“视图”项，右击鼠标，在弹出的快捷菜单上选择“新建视图”菜单项。在随后出现的“添加表”窗口中，添加所需关联的基本表。在视图窗口的关系图窗口显示了基本表的全部信息。根据所需在窗口中选择创建视图所需的字段。完成后单击“保存”按钮。查询视图：新建一个查询，输入 T-SQL 查询命令即可像查询基本表一样查询视图。在视图 Departments_view 中查询 DepartmentID=3的 DepartmentName。Departments_view 已经由上面中创建。删除视图：展开 YGGL 数据库“视图”，选择要删除的视图，右击选择“删除”选项，确认即可。在这里，将中新建的视图 Departments_view 删除。【思考与练习】总结视图与基本表的差别。视图是从一个表或者多个表（或视图）导出来的表。视图与表不同，视图是一个虚表，视图所对应的数据不进行实际的存储，数据库中只存储视图的定义。本质上视图是以 T-SQL命令的形式存在的，当用户使用视图时，系统根据视图的定义再去调用基本表中的数据，展现给用户的是根据视图的定义执行出来的需要的结果。4、实验总结。由于本次实验有数据库的查询和视图的使用两个部分，所花费的时间比较多，认真将每一行代码敲进去，认真分析，解决好每一个问题。完成这些后，自己收获还是很多的，中间也出现过很多问题，上网搜索也大致能够解决，当自己实践走在前面，同学遇到的问题我也基本遇到过，能够帮同学解决，再一次加强自己对知识，对技能的熟练程度。总得来讲，值得！终于做完了这个实验。