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

实验三数据库的查询和视图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元以下显示为低收入，2000~3000元显示为中等收入，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。

⑨找出所有收入在2000~3000元的员工号码。

【思考与练习】找出所有在部门“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这一列有重复。