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

实验三 数据库的查询和视图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的雇员的地址和电话。 鼠标右键单击在弹出的菜单窗口中选择“执行”，结果中只会出现一行记录，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表中员工的姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”。 【思考与练习】查询Employees员工的姓名、住址和收入水平，2000元以下显示为低收入，20003000元显示为中等收入，3000元以上显示高收入。经过分析需要查询员工的姓名、住址和收入水平，需要在Employees表和Salary表中进行查询，并且将Salary表中员工的InCome进行分类需要用到AS子句。利用T-SQL命令进行多表查询语句如下。执行上面截图中代码，在结果栏中出现员工的姓名，住址和收入水平。消息中显示12行受影响，Employees表中所有员工信息都已经查询输出。 计算每个雇员的实际收入 。经过分析需要员工的收入以及员工号码和员工姓名，就需要在Employees表和Salary表中进行查询。利用T-SQL命令查询如下。结果栏中就会显示EmployeeID，Name，实际收入这三项。注意：在上面T-SQL命令中EmployeeID如果不指明为哪个表中的EmployeeID，代码就会报错“EmployeeID不明确”，改为Employees.EmployeeID或者Salary.EmployeeID都行。【思考与练习】使用SELECT语句进行简单的计算。在这里我将使用SELECT语句计算出Employees表中工龄三年以上的人数，并且查询员工号码，姓名，工龄。 获得员工总数。 经分析得需要得到员工总数，只需要在Employees表内使用内置函数COUNT就行。利用T-SQL命令执行操作如下。【思考与练习】A、 计算Salary表中员工月收入的平均数。B、 获得Employees表中最大的员工号码。C、 计算Salary表中所有员工的总支出。D、 查询财务部雇员的最高和最低实际收入。经过分析需要查询财务部雇员的最高和最低实际收入，涉及到Employees表、Departments表和Salary表，需要在三张表中进行查询。首先确定DepartmentName为“财务部”的DepartmentID。其次在Employees表中确定DepartmentID为财务部的雇员的EmployeeID。最后在Salary表中通过EmployeeID查询财务部雇员的InCome和OutCome，在最后的输出时候用 InCome-OutCome的结果为实际收入。执行后在结果栏中看到财务部雇员的最高实际收入和最低实际收入。 找出所有姓王的雇员的部门号。 【思考与练习】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进行查询。首先，在表Salary中查询InCome<2500元的雇员的EmployeeID。然后，利用得到的EmployeeID在表Employees中行选，查询所有符合EmployeeID的雇员的情况。注：上面截图中代码第5行，若改为“where EmployeeID =”就会出现如下错误表示返回值不止一个，并且现在子查询后面接的是“=”，这种情况DBMS是不允许的，所以就会出现如上所示的错误。解决方法只需要将“=”改为“in”就行了，就会解决返回值不止一个和“=”的冲突。执行上面T-SQL语句，在结果栏中显示出现结果。 查找财务部年龄不低于研发部雇员年龄的雇员的姓名。 分析得，ALL指定表达是要与子查询结果集中每个值都进行比较，当表达式与每个值都满足比较是关系时，才返回TRUE，否则返回FALSE。执行结果会发现只查询到1人满足这样的条件。同样我们也能够通过查询Employees表得到。财务部只有“王向容”1人符合年龄不小于（大于）研发部雇员年龄。注：在书上源码第12行“Birthday ! >all”查询到的是“李丽”，年龄小于所有研发部的，所有书本上源码表示的是“财务部雇员年龄不大于研发部雇员年龄”与题目中“不低于”冲突。所以书上源码有误，将源码改成上面截图中第12行即可。【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。分析：首先在Employees表里找到EmployeeName为“研发部”的雇员的姓名和查找雇员的EmployeeID，其次这样的EmployeeID还需要满足InCome大于所有的DepartmentName为“财务部”的雇员的InCome，满足条件返回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）：结果表中除了包括满足连接条件的行外，还包括左表的所有行。右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行。完全外连接 （FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。在这里将表Employees和表Salary做右连接查询，结果表中除了包括连接条件Employees.EmployeeID=Salary.EmployeeID外，还将包括右表（Salary表）中的所有行（InCome和OutCome）。执行代码结果如下。 查找财务部收入在2000元以上的雇员姓名及其薪水情况。分析得需要财务部收入2000元以上雇员姓名和薪水情况，涉及雇员基本信息的Employees表、部门信息Department表和薪水信息Salary表。此时就用到where语句里面多表查询将三张表做自然连接。执行上面截图中代码，在下面结果栏中显示财务部工资在2000元以上的雇员的姓名和薪水情况。【思考与练习】查询研发部在1976年以前出生的雇员姓名及其薪水情况。将三张表做自然连接后查询满足条件的雇员的姓名、收入和支出。（4）聚合函数的使用 求财务部雇员的平均收入。新建一个查询，在“查询分析器”窗口中输入如下语句并执行。执行截图中代码，结果栏中显示才而不平均收入。【思考与练习】查询财务部雇员的最高收入和最低收入。查询最高和最低收入，将用到聚合函数表中的MAX（求最大值）和MIN（求最小值）。执行上面截图中T-SQL命令，在结果栏中显示财务部雇员最高收入和最低收入。 求财务部雇员的平均实际收入。 新建一个查询，在“查询分析器”窗口中输入如下语句并执行。执行上面截图中的T-SQL命令，在结果栏中显示财务部雇员平均实际收入。【思考与练习】查询财务部雇员的最高和最低实际收入。需要财务部实际收入需要用表Salary中的InCome字段和OutCome字段。其次用到聚合函数MAX和MIN。最后在用where子查询找到DepartmentName为“财务部”的雇员的EmployeeID。执行上面截图中的T-SQL命令，在结果栏中显示财务部雇员最高和最低实际收入。（5）GROUP BY、ORDER BY子句的使用。 查找Employees表中男性和女性人数 。 【思考与练习】A、按部门列出在该部门工作的员工的人数。 B、按员工的学历分组，排列出本科、大专和硕士的人数。 查找员工数超过2的部门名称和员工数量。执行上面截图中代码，在结果栏中显示员工数超过2的部门名称以及人数。【思考与练习】按照员工的工作年份分组，统计各个工作年份的人数，例如，工作1年的多少人，工作2年的多少人 。分析只需要Employees表就能够完成，通过GROUP BY对WorkYear进行分组，并统各个年份的人数。 将各个雇员的情况按收入由低到高排列。 新建一个查询，在“查询分析器”窗口中输入如下语句并执行。需要对雇员的收入情况进行排序，就需要使用到谓词ORDER BY，谓词ORDER BY后面能够接多个排序的变量名，第一个变量为主序，下面依次类推。每一个排序列名后面可用限定词ASC（升序）或者DESC（降序）声明排序的方式 ，缺省为升序（如果不接限定词系统默认的为ASC（升序）。下面为执行上面截图中T-SQL命令，在结果栏中显示的查询结果。可以观察雇员的基本信息是按照InCome升序排列（由低到高）。【思考与练习】A、将员工信息按照出生时间从小到大排列。执行上面截图中的T-SQL命令，在下面结果栏中显示雇员信息根据出生日期从小到大排列。C、在ODER BY子句中使用子查询，查询员工姓名，性别和工龄信息，要求按照实际收入从大到小排序。经分析将表Employees和表Salary根据EmployeeID相同做自然连接，其次根据实际收入（InCome-OutCome）进行ORDER BY排序。最后，由于是根据实际收入（InCome-OutCome）从大到小进行排序，那么接在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语句中查询多个表或者视图，以表明新创的视图所参照的表或视图。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和RealInCome（实际收入），并且查询该视图中姓名为“王林”的雇员的基本情况。具体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中的改变。 视图DS_VIEW中发生的改变，见上面截图。 将视图Employee_view中的员工号为“000001”的员工的姓名修改为“王浩”。执行上面的T-SQL命令后，打开视图Employees_view，可以看出EmployeeID=000001的雇员的Name已经修改为“王浩”。 修改视图DS_VIEW中部门号为“1”的一行数据。 【思考与练习】 视图Employees_view中无法插入和删除数据，其中的RealInCome字段也无法修改，为什么？要通过视图更新（插入、修改和删除）基本数据，必须保证视图为可更新视图，只有满足一下条件的视图才是可更新视图。A、创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句及DISTINCT关键字。B、创建视图的SELECT语句不包含从基本表列通过计算得到的列。C、创建视图的SELECT语句的FROM子句中至少要包含一个基本表。视图Employees_view的SELECT语句中的RealInCome是通过InCome-OutCome计算得来的，所以Employees_view是不可更新的视图，所以也无法向其中修改数据，同样其中的RealInCome字段也是不可修改的。（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、实验总结。 由于本次实验有数据库的查询和视图的使用两个部分，所花费的时间比较多，认真将每一行代码敲进去，认真分析，解决好每一个问题。完成这些后，自己收获还是很多的，中间也出现过很多问题，上网搜索也大致能够解决，当自己实践走在前面，同学遇到的问题我也基本遇到过，能够帮同学解决，再一次加强自己对知识，对技能的熟练程度。总得来讲，值得！终于做完了这个实验。