实验三 大数据库地查询和视图

《实验三 大数据库地查询和视图》由会员分享，可在线阅读，更多相关《实验三 大数据库地查询和视图（34页珍藏版）》请在装配图网上搜索。

1、word实验三 数据库的查询和视图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、实验容1SELECT语句的根本使用。 对于实验二给出的数据库表结构，查询每个雇员

2、的所有数据。新建一个查询，在“查询分析器窗口中输入如下语句并执行。单击右键空白处，选择执行，在下方会出现所有结果。【思考与练习】用SELECT语句查询Departments表和Salary表中所有信息。新建一个查询，在“查询分析器窗口中输入如下语句并执行。单击右键空白处，选择执行，在下方会出现所有结果。在“消息中会发现总共有60行受影响。 用SECECT语句查询Employees表中每个雇员的地址和。新建一个查询，在“查询分析器窗口中输入如下代码并执行。单击右键空白处，选择执行，在下方会出现所有结果。在“消息中会发现有12行记录受影响【思考与练习】 A、用SELECT语句查询Departmen

3、ts表和Salary表的一列或假如干行。利用SELECT语句查询Departments表和Salary表中 EmployeeID为000001的记录，在“查询分析器中输入以下代码。执行后在下方会出现所有EmployeeID=000001的记录B、查询Employees表中部门号和性别 ，要求使用DISTINCT消除重复行。对表只选择某些行时，可能会出现重复行。此时就需要使用DISTINCT关键字消除结果中的重复行。此时使用DISTINCT消除在Employees中查询到的部门号和性别的重复行，具体代码如下。上面代码分别是使用了DISTINCT和没有使用DISTINCT关键字，下面就是两种的结果

4、的比照。可以从上面的执行结果中看出使用了DISTINCT关键字，结果集中的重复行就被消除了。 查询EmployeeID为000001的雇员的地址和。鼠标右键单击在弹出的菜单窗口中选择“执行，结果中只会出现一行记录，EmployeeID为000001的雇员的地址和。【思考与练习】 A、查询月收入高于2000元的员工。只需要查询员工，条件为月收入高于2000元员工，那么至于要Salary表就行。用T-SQL命令在表Salary表中查询月收入高于2000元的员工代码如下。在结果栏中显示(9 行受影响)，说明月收入高于2000的员工共有9人，如上截图所示，将所有月收入高于2000的员工的员工输出。B、

5、查询1970年以后出生的员工的和地址。通过分析员工信息，跟员工年龄、地址相关的只有表Employees表，所以只需要在一表中查询即可，具体T-SQL查询命令如下。在结果栏中消息显示(7 行受影响)，说明1970年以后出生有7人，如上截图所示，将局部1970年以后出生员工、地址输出显示。C、查询所有财务部的员工的和。通过分析的，需要知道财务部与员工的和，财务部只存在于Departments表中，员工和只存在于Employees表中。因此需要在表Departments表和Employees表中进展查询。利用T-SQL命令查询代码如下。 在结果栏中显示3行受影响，从上面截图中可以看出所有财务部员工有

6、3人，并且查询显示其、。 查询Employees表中女雇员的地址和，使用AS子句将结果中各列的标题分别指定为地址、。从上面结果的截图发现Address变为地址，PhoneNumber变为。【思考与练习】查询Employees表中男员工的和出生日期，要求各列标题用中文表示。通过分析此查询属于单表查询，只需要用到Employees表，使用AS子句就能够使结果集中各列标题用中文显示。利用T-SQL命令查询代码如下。从上面结果的截图发现各列标题都是中文。从消息中显示(9 行受影响)说明，员工中男性有9人，并且全部男性的和出生日期都已经查询显示出。 查询Employees表中员工的和性别，要求Sex值为

7、1时显示为“男，为0时显示为“女。【思考与练习】查询Employees员工的、住址和收入水平，2000元以下显示为低收入，20003000元显示为中等收入，3000元以上显示高收入。经过分析需要查询员工的、住址和收入水平，需要在Employees表和Salary表中进展查询，并且将Salary表中员工的Ine进展分类需要用到AS子句。利用T-SQL命令进展多表查询语句如下。执行上面截图中代码，在结果栏中出现员工的，住址和收入水平。消息中显示12行受影响，Employees表中所有员工信息都已经查询输出。 计算每个雇员的实际收入 。经过分析需要员工的收入以与员工和员工，就需要在Employees

8、表和Salary表中进展查询。利用T-SQL命令查询如下。结果栏中就会显示EmployeeID，Name，实际收入这三项。注意：在上面T-SQL命令中EmployeeID如果不指明为哪个表中的EmployeeID，代码就会报错“EmployeeID不明确，改为Employees.EmployeeID或者Salary.EmployeeID都行。【思考与练习】使用SELECT语句进展简单的计算。在这里我将使用SELECT语句计算出Employees表中工龄三年以上的人数，并且查询员工，工龄。 获得员工总数。 经分析得需要得到员工总数，只需要在Employees表使用置函数COUNT就行。利用T-S

9、QL命令执行操作如下。【思考与练习】A、 计算Salary表中员工月收入的平均数。B、 获得Employees表中最大的员工。C、 计算Salary表中所有员工的总支出。D、 查询财务部雇员的最高和最低实际收入。经过分析需要查询财务部雇员的最高和最低实际收入，涉与到Employees表、Departments表和Salary表，需要在三表中进展查询。首先确定DepartmentName为“财务部的DepartmentID。其次在Employees表中确定DepartmentID为财务部的雇员的EmployeeID。最后在Salary表过EmployeeID查询财务部雇员的Ine和Oute，在最

10、后的输出时候用 Ine-Oute的结果为实际收入。执行后在结果栏中看到财务部雇员的最高实际收入和最低实际收入。 找出所有姓王的雇员的部门号。【思考与练习】A、 找出所有地址中含有“的雇员的与部门号。 注意：%代表着任意长度的字符串长度可以为0，“%表示“前面和后面都能够有任意长度字符串，用来表示地址中含有“二字。B、 查找员工中倒数第2个数字为0的员工的、地址和学历。注意：数字占单个字符，所以通配符“_只有一个，“%0_表示倒数第二位为0。 找出所有收入在20003000元的员工。【思考与练习】找出所有在部门“1或“2工作的雇员的。10 使用INTO子句，由表Salary创建“收入在1500元

11、以上的员工表，包括编号和收入。在左边对象资源管理器的YGGL数据库的表中刷新，会发现多了表“收入在1500元以上的员工。【思考与练习】使用INTO子句，由表Employees创建“男员工表，包括编号和。2子查询的使用。 查找在财务部工作的雇员的情况。查询财务部工作的雇员情况，需要用到Department表和Employees表进展多表查询。首先，在Department表中查询DepartmentName为“财务部的DepartmentID。然后，将DepartmentID拿到表Employees表中查询所有DepartmentID为财务部的雇员的所有信息。利用T-SQL命令查询代码如下。 在结

12、果栏中显示所有工作部门为“财务部的雇员的详细信息。【思考与练习】用子查询的方法查找所有收入在2500元以下的雇员的情况。进分析需要用到工资表Salary和员工信息表Employees进展查询。首先，在表Salary中查询Ineall查询到的是“丽，年龄小于所有研发部的，所有书本上源码表示的是“财务部雇员年龄不大于研发部雇员年龄与题目中“不低于冲突。所以书上源码有误，将源码改成上面截图中第12行即可。【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的。分析：首先在Employees表里找到EmployeeName为“研发部的雇员的和查找雇员的EmployeeID，其次这样的E

13、mployeeID还需要满足Ine大于所有的DepartmentName为“财务部的雇员的Ine，满足条件返回True，select到满足这样条件的雇员的Name，否如此返回FALSE。Where子查询中嵌套使用。执行T-SQL命令后在结果栏中显示只有1人满足。3连接查询的使用。 查询每个雇员的情况与其薪水情况。执行之后，将Employees表和Salary表中所有员工的根本情况进展输出。不足之处就是EmployeeID这一列有重复。【思考与练习】查询每个雇员的情况与其工作部门的情况。执行后将每个雇员的根本情况、工资情况、工作部门情况输出。 使用连接的方法查询名字为“王林的员工的所在部门。【思

14、考与练习】A、 使用连接方法查找出不在财务部工作的所有员工信息。连接是指定了关键字INNER的连接，连接按照ON所指定的连接条件合并两个表，返回满足条件的行。此处INNER关键字能够省略。查找所有不在财务部工作的员工的信息，就需要使用员工根本信息表Employees和部门信息表Department进展查询。T-SQL命令语句如下。执行结果包含表Employees和表Department的所有字段，不去除重复字段DepartmentID。B、 使用外连接方法查找出所有员工的月收入。外连接为指定了关键字OUTER关键字的连接，外连接包括以下三种连接。左外连接LEFT OUTER JOIN：结果表中

15、除了包括满足连接条件的行外，还包括左表的所有行。右外连接RIGHT OUTER JOIN：结果表中除了包括满足连接条件的行外，还包括右表的所有行。完全外连接 FULL OUTER JOIN：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。在这里将表Employees和表Salary做右连接查询，结果表中除了包括连接条件Employees.EmployeeID=Salary.EmployeeID外，还将包括右表Salary表中的所有行Ine和Oute。执行代码结果如下。 查找财务部收入在2000元以上的雇员与其薪水情况。分析得需要财务部收入2000元以上雇员和薪水情况，涉与雇员根本信息

16、的Employees表、部门信息Department表和薪水信息Salary表。此时就用到where语句里面多表查询将三表做自然连接。执行上面截图中代码，在下面结果栏中显示财务部工资在2000元以上的雇员的和薪水情况。【思考与练习】查询研发部在1976年以前出生的雇员与其薪水情况。将三表做自然连接后查询满足条件的雇员的、收入和支出。4聚合函数的使用 求财务部雇员的平均收入。新建一个查询，在“查询分析器窗口中输入如下语句并执行。执行截图中代码，结果栏中显示才而不平均收入。【思考与练习】查询财务部雇员的最高收入和最低收入。查询最高和最低收入，将用到聚合函数表中的MAX求最大值和MIN求最小值。执行

17、上面截图中T-SQL命令，在结果栏中显示财务部雇员最高收入和最低收入。 求财务部雇员的平均实际收入。 新建一个查询，在“查询分析器窗口中输入如下语句并执行。执行上面截图中的T-SQL命令，在结果栏中显示财务部雇员平均实际收入。【思考与练习】查询财务部雇员的最高和最低实际收入。需要财务部实际收入需要用表Salary中的Ine字段和Oute字段。其次用到聚合函数MAX和MIN。最后在用where子查询找到DepartmentName为“财务部的雇员的EmployeeID。执行上面截图中的T-SQL命令，在结果栏中显示财务部雇员最高和最低实际收入。5GROUP BY、ORDER BY子句的使用。 查

18、找Employees表中男性和女性人数 。【思考与练习】A、按部门列出在该部门工作的员工的人数。B、按员工的学历分组，排列出本科、大专和硕士的人数。 查找员工数超过2的部门名称和员工数量。执行上面截图中代码，在结果栏中显示员工数超过2的部门名称以与人数。【思考与练习】按照员工的工作年份分组，统计各个工作年份的人数，例如，工作1年的多少人，工作2年的多少人 。分析只需要Employees表就能够完成，通过GROUP BY对WorkYear进展分组，并统各个年份的人数。 将各个雇员的情况按收入由低到高排列。 新建一个查询，在“查询分析器窗口中输入如下语句并执行。需要对雇员的收入情况进展排序，就需要

19、使用到谓词ORDER BY，谓词ORDER BY后面能够接多个排序的变量名，第一个变量为主序，下面依次类推。每一个排序列名后面可用限定词ASC升序或者DESC降序声明排序的方式 ，缺省为升序如果不接限定词系统默认的为ASC升序。下面为执行上面截图中T-SQL命令，在结果栏中显示的查询结果。可以观察雇员的根本信息是按照Ine升序排列由低到高。【思考与练习】A、将员工信息按照出生时间从小到大排列。执行上面截图中的T-SQL命令，在下面结果栏中显示雇员信息根据出生日期从小到大排列。C、在ODER BY子句中使用子查询，查询员工，性别和工龄信息，要求按照实际收入从大到小排序。经分析将表Employee

20、s和表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表

21、的全部列。新建“查询分析器窗口，在其中输入以上T-SQL命令，点击执行按钮。在YGGL数据库的视图中进展刷新，就会显示创建的DS_VIEW视图，如下所示。 创建YGGL数据库上的视图Employees_view，视图包含员工、和实际收入3列。使用如下T-SQL命令进展操作。执行上面截图中代码，在YGGL数据库的视图中进展刷新，显示视图Employees_view，如下所示。【思考与练习】A、在创建视图时SELECT有哪些限制？用来创建视图的SELECT语句，可在SELECT语句中查询多个表或者视图，以明确新创的视图所参照的表或视图。B、在创建视图时有哪些注意点？只有在当前数据库中才能够创建视图

22、。视图的命名必须遵循标识符命名规如此，不能够与表同名。不能够把规如此、默认值或触发器与视图相关联。C、创建视图，包含员工、所在部门名称和实际收入这几列。在数据库YGGL的视图中刷新，会显示刚刚创建的视图test_view，包含员工、员工部门名称和实际收入这几列。结果如如下图所示。2查询视图 从视图DS_VIEW中查询部门号为3的部门名称。注：在上面代码下是显示错误，但是代码逻辑都正确，而且也能够正确执行，并且输出正确的结果。不知道是什么原因 从视图Employees_view中查询为“王林的员工的实际收入。【思考与练习】A、假如视图关联了某表中的所有字段，而此时该表中添加了新的字段，视图中能否

23、查询到该字段？首先建立视图Employees_view并且关联表Employees中的所有字段。然后在表Employees中插入一行新的数据。最后在“查询分析器中查询EmployeeID等于“000000的雇员的所有信息。从上面的T-SQL命令可以看出，当视图关联的表中添加新的字段时，视图中是无法查询到该字段的，必须重新创建视图才能够查询到新添加的字段。B、自己创建一个视图，并查询视图中的字段。利用T-SQL命令创建一个视图Employees_view，该视图包含雇员EmployeeID、Name和RealIne实际收入，并且查询该视图中为“王林的雇员的根本情况。具体T-SQL命令代码如下。查

24、询该视图中为“王林的雇员的根本情况。3更新视图。在更新视图前需要了解可更新视图的概念，了解什么视图时不可以进展修改的。更新视图真正更新的是和视图关联额表。 向DS_VIEW中插入一行数据“6，广告部，广告业务。当在“查询分析器中输入上面代码时，会出现这样的错误。规如此已经和Departments。DepartmentID绑定，只需要解除绑定就行了。那么就将前面的T-SQL修改为这样就行了。当再次执行时就会发现，Departments.DepartmentID已经没有了规如此，也就说明第一次执行时规如此已经解除绑定。同样当再次执行时会出字段字段已经出现，不能够重复输入primary key一样的

25、记录。继续在视图中刷新重新打开“编辑前200行就会发现字段已经插入。 修改视图DS_VIEW，将部门号为5的部门名称修改为“生产车间。执行上面的T-SQL命令，将更新数据之前和之后的DS_VIEW表进展对照，发现数据已经成功更新，说明T-SQL命令执行成功。DepartmentID为5的DepartmentName已经更新为“生产车间。执行完该命令后，使用select语句分别查看视图DS_VIEW和和根本表Departments中发生的变化。根本表Departments中的改变。 视图DS_VIEW中发生的改变，见上面截图。 将视图Employee_view中的员工号为“000001的员工的修

26、改为“王浩。执行上面的T-SQL命令后，打开视图Employees_view，可以看出EmployeeID=000001的雇员的Name已经修改为“王浩。 修改视图DS_VIEW中部门号为“1的一行数据。【思考与练习】 视图Employees_view中无法插入和删除数据，其中的RealIne字段也无法修改，为什么？要通过视图更新插入、修改和删除根本数据，必须保证视图为可更新视图，只有满足一下条件的视图才是可更新视图。A、创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句与DISTINCT关键字。B、创建视图的SELECT语句不包含从根本表列通过计算得到的

27、列。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，选择其中的“视图项，右击鼠标，在弹出的快捷菜单上选择

28、“新建视图菜单项。在随后出现的“添加表窗口中，添加所需关联的根本表。在视图窗口的关系图窗口显示了根本表的全部信息。根据所需在窗口中选择创建视图所需的字段。完成后单击“保存按钮。 查询视图：新建一个查询，输入T-SQL查询命令即可像查询根本表一样查询视图。在视图Departments_view中查询DepartmentID=3的DepartmentName。Departments_view已经由上面中创建。 删除视图：展开YGGL数据库“视图，选择要删除的视图，右击选择 “删除选项，确认即可。在这里，将中新建的视图Departments_view删除。【思考与练习】总结视图与根本表的差异。视图是

29、从一个表或者多个表或视图导出来的表。视图与表不同，视图是一个虚表，视图所对应的数据不进展实际的存储，数据库中只存储视图的定义。本质上视图是以T-SQL命令的形式存在的，当用户使用视图时，系统根据视图的定义再去调用根本表中的数据，展现给用户的是根据视图的定义执行出来的需要的结果。4、实验总结。 由于本次实验有数据库的查询和视图的使用两个局部，所花费的时间比拟多，认真将每一行代码敲进去，认真分析，解决好每一个问题。完成这些后，自己收获还是很多的，中间也出现过很多问题，上网搜索也大致能够解决，当自己实践走在前面，同学遇到的问题我也根本遇到过，能够帮同学解决，再一次加强自己对知识，对技能的熟练程度。总得来讲，值得！终于做完了这个实验。34 / 34