SQLSERVE-R2008实用教学教材实验参考-答案~(实验4~).doc

1、-_实验 4 数据库的查询和视图一、SELECT 语句的基本使用1. 查询 Employees 表中所有数据2. 查询 Employees 表中指定字段数据3. 查询 Employees 表中的部门号和性别，要求使用 Distinct 消除重复行4. 使用 WHERE 子句查询表中指定的数据查询编号为000001的雇员的地址和电话查询月收入高于 2000 元的员工号码查询 1970 年以后出生的员工的姓名和住址5. 使用 AS 子句为表中字段指定别名查询 Employees 表中女雇员的地址和电话，并将列标题显示为地址和电话查询 Employees 表中男雇员的姓名和出生日期，并将列标题显示为

2、姓名和出生日期6. 使用使用 CASE 子句查询 Employees 表中员工的姓名和性别，要求 Sex 值为 1 时显示“男” ，为 0 时显示“女”查询 Employees 表中员工的姓名、住址和收入水平， 2000 元以下显示为低收入，20003000 地显示为中等收入，3000 元以上显示为高收入。7. 使用 SELECT 语句进行简单计算计算每个雇员的实际收入8. 使用内置函数获得员工总数计算 Salary 表中员工月收入的平均数获得 Employees 表中最大的员工号码计算 Salary 表中所有员工的总支出查询财务部雇员的最高和最低实际收入9. 模糊查询找出所有姓王的雇员的部门

3、号找出所有地址中含有“中山”的雇员的号码及部门号找出员工号码中倒数第二个数字为 0 的员工的姓名、地址和学历10. BetweenAnd和 Or 的使用找出收入在 20003000 元之间的雇员编号找出部门为“1”或“2”的雇员的编号11. 使用 INTO 子句，由源表创建新表由表 Salary 创建“SalaryNew”表，要求包括编号和收入，选择收入在 1500 元以上的雇员由表 Employees 创建“EmployeesNew”表，要求包括编号和姓名，选择所有男员工二、子查询的使用1. 查找在财务部工作的雇员情况2. 用子查询的方法查找所有收入在 2500 以下的雇员的情况3. 查找财

4、务部年龄不低于研发部雇员年龄的雇员姓名4. 用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名5. 查找比所有财务部的雇员收入都高的雇员的姓名6. 用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名三、连接查询的使用-_1. 查询每个雇员的情况及薪水的情况2. 查询每个雇员的情况及其工作部门的情况3. 使用内连接的方法查询名字为“王林”的雇员所在的部门4. 使用内连接的方法查找出不在财务部工作的所有雇员信息5. 使用外连接方法查找出所有员工的月收入6. 查找财务部收入在 2000 元以上的雇员姓名及其薪水详情7. 查询研发部在 1976 年以前出生的雇员姓名及其薪水详请四、聚

5、合函数的使用1. 求财务部雇员的平均收入2. 查询财务部雇员的最高和最低收入3. 求财务部雇员的平均实际收入4. 查询财务部雇员的最高和最低实际收入5. 求财务部雇员的总人数6. 统计财务部收入在 2500 元以上的雇员人数五、GROUP BY、ORDER BY 子句的使用1. 查找 Employees 表中男性和女性的人数2. 按部门列出在该部门工作的员工的人数3. 按员工的学历分组，排列出本科、大专、硕士的人数4. 查找员工数超过 2 的部门名称和雇员数量5. 按员工的工作年份分组，统计各个工作年份的人数，例如工作 1 年的多少人，工作 2 年的多少人6. 将雇员的情况按收入由低到高排列7

6、. 将员工信息按出生时间从小到大排列8. 在 ORDER BY 子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列六、视图的使用1. 创建视图（1 ）在数据库 YGGL 上创建视图 Departments_View，视图包含 Department 表的全部列（2 ）创建视图 Employees_Departments_View，视图包含员工号码、姓名、所在部门名称（3 ）创建视图 Employees_Salary_View，视图包含员工号码、姓名和实际收入三列2. 查询视图从视图 Employees_Salary_View 中查询出姓名为“王林”的员工的实际收入3. 更

7、新视图（1 ）向视图 Departments_View 中添加一条记录（6 ， 广告部 ， 广告业务 ）执行完命令后，分别查看 Departments_View 和 Department 表中发生的变化（2 ）尝试向 Employees_Departments_View 中添加一条记录，看看会发生什么情况（3 ）尝试向 Employees_Salary_View 中添加一条记录，看看会发生什么情况（4 ）将视图 Departments_View 中，部门号为6的部门名称修改为 生产车间（5 ）删除视图 Departments_View 中最新增加的的一条记录4. 删除视图 Employees_

8、Departments_View5. 在界面工具中操作视图-_一、SELECT 语句的基本使用1. 查询 Employees 表中所有数据SELECT * FROM Employees;2. 查询 Employees 表中指定字段数据SELECT EmployeeID,Name,DepartmentID FROM Employees;3. 查询 Employees 表中的部门号和性别，要求使用 Distinct 消除重复行SELECT Distinct DepartmentID,Sex FROM Employees;4. 使用 WHERE 子句查询表中指定的数据查询编号为000001的雇员的地

9、址和电话Select Address,PhoneNumber FROM Employees WHERE EmployeeID=000001;查询月收入高于 2000 元的员工号码SELECT EmployeeID FROM Salary WHERE InCome2000;查询 1970 年以后出生的员工的姓名和住址SELECT Name,Address FROM Employees WHERE YEAR(Birthday)1970;SELECT Name,Address FROM Employees WHERE Birthday1970;5. 使用 AS 子句为表中字段指定别名查询 Emplo

10、yees 表中女雇员的地址和电话，并将列标题显示为地址和电话SELECT Address AS 地址,PhoneNumber AS 电话 FROM Employees;查询 Employees 表中男雇员的姓名和出生日期，并将列标题显示为姓名和出生日期SELECT Name AS 姓名,Birthday AS 出生日期 FROM Employees WHERE Sex=1;6. 使用使用 CASE 子句查询 Employees 表中员工的姓名和性别，要求 Sex 值为 1 时显示“男” ，为 0 时显示“女”SELECT Name AS 姓名,CASEWHEN Sex=1 THEN 男WHEN

11、 Sex=0 THEN 女ENDAS 性别FROM Employees;查询 Employees 表中员工的姓名、住址和收入水平， 2000 元以下显示为低收入，20003000 地显示为中等收入，3000 元以上显示为高收入。SELECT a.Name AS 姓名,a.Address as 住址,CASEWHEN b.InCome3000 THEN 高收入ENDAS 收入水平FROM Employees a,Salary bWHERE a.EmployeeID=b.EmployeeID;7. 使用 SELECT 语句进行简单计算在 Salary 表中，计算每个雇员的实际收入，并显示雇员编号和

12、实际收入SELECT EmployeeID AS 雇员编号 ,InCome-OutCome AS 实际收入 FROM Salary;-_8. 使用聚合函数获得员工总数SELECT COUNT(*) FROM Employees;计算 Salary 表中员工月收入的平均数SELECT AVG(InCome) AS 平均收入 FROM Salary;获得 Employees 表中最大的员工号码SELECT MAX(EmployeeID) AS 雇员编号 FROM Employees;计算 Salary 表中所有员工的总支出SELECT SUM(OutCome) AS 总支出 FROM Salary

13、;查询财务部雇员的最高和最低实际收入SELECT MAX(InCome-OutCome) AS 最高收入,MIN (InCome-OutCome) AS 最低收入 FROM Salary;9. 模糊查询找出所有姓王的雇员的部门号SELECT DepartmentID AS 部门号 FROM Employees WHERE Name like 王%;找出所有地址中含有“中山”的雇员的编号及部门号SELECT EmployeeID AS 雇员编号, DepartmentID AS 部门号 FROM Employees WHERE Address like %中山%找出雇员编号中倒数第二个数字为 0

14、 雇员的编号的姓名、地址和学历SELECT EmployeeID AS 雇员编号, Name AS 姓名,Address AS 地址, Education AS 学历 FROM Employees WHERE SUBSTRING(EmployeeID,5,1)=0;10. BetweenAnd和 Or 的使用找出收入在 20003000 元之间的雇员编号SELECT EmployeeID,InCome FROM Salary WHERE InCome BETWEEN 2000 AND 3000;找出部门为“1”或“2”的雇员的编号SELECT EmployeeID,DepartmentID F

15、ROM Employees WHERE DepartmentID=1 OR DepartmentID=2;11. 使用 INTO 子句，由源表创建新表由表 Salary 创建“SalaryNew”表，要求包括编号和收入，选择收入在 1500 元以上的雇员SELECT EmployeeID,InComeINTO SalaryNewFROM SalaryWHERE InCome1500;由表 Employees 创建“EmployeesNew”表，要求包括编号和姓名，选择所有男员工SELECT EmployeeID,NameINTO EmployeesNewFROM EmployeesWHERE

16、Sex=1;二、子查询的使用1. 查找在财务部工作的雇员情况-分析:雇员表和部门表通过 DepartmentID进行关联-1)在 Departments表中查找财务部的编号-_-2)在 Employees表中查找部门的雇员情况SELECT * FROM Employees WHERE DepartmentID =(SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部);2. 用子查询的方法查找所有收入在 2500 以下的雇员的情况-分析:员工表和收入表通过 EmployeeID进行关联SELECT * FROM Employ

17、ees WHERE EmployeeID IN (SELECT EmployeeID FROM Salary WHERE InCome ALL(SELECT Birthday FROM Employees WHERE DepartmentID in(SELECT DepartmentID FROM Departments WHERE DepartmentName=研发部);4. 用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名-1)查找财务部雇员的收入-2)查找研发部雇员的编号,条件是收入比所有财务部雇员收入都高-3)通过编号找到雇员姓名SELECT Name FROM Emplo

18、yees WHERE EmployeeID IN (SELECT EmployeeID FROM Salary WHERE EmployeeID IN(SELECT EmployeeID FROM Employees WHERE DepartmentID =(SELECT DepartmentID FROM Departments WHERE DepartmentName=研发部) AND InCome ALL(SELECT InCome FROM Salary WHERE EmployeeID IN(SELECT EmployeeID FROM Employees WHERE Departm

19、entID =(SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部);5. 查找比所有财务部的雇员收入都高的雇员的姓名-分析:1) 查找所有财务部的雇员收入-2)查找其他雇员编号,条件是收入比所有财务部的雇员收入都高-3)通过编号找到姓名SELECT Name FROM Employees WHERE EmployeeID IN(SELECT EmployeeID FROM Salary WHERE InComeALL(SELECT InCome FROM Salary Where EmployeeID IN (SELEC

20、T EmployeeID FROM Employees Where DepartmentID =(SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部);6. 用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名-分析:年龄都大等价于生日都小-1)找到所有研发部雇员的生日-2)找到其他部门雇员的姓名,条件是生日比研发部的所有雇员的生日都小SELECT Name FROM Employees WHERE Birthday 2000 AND c.DepartmentName=财务部; 7. 查询研发部在 1976 年以前出

21、生的雇员姓名及其薪水详请SELECT a.*,b.InCome FROM Employees aINNER JOIN Salary bON a.EmployeeID=b.EmployeeIDINNER JOIN Departments cON a.DepartmentID=c.DepartmentIDWHERE c.DepartmentName=研发部 AND a.Birthday1976四、聚合函数的使用1. 求财务部雇员的平均收入SELECT AVG(a.InCome) FROM Salary a-_INNER JOIN Employees bON a.EmployeeID=b.Emplo

22、yeeIDINNER JOIN Departments cON c.DepartmentID=b.DepartmentIDWHERE c.DepartmentName=财务部 ;2. 查询财务部雇员的最高和最低收入SELECT MIN(a.InCome),MAX(a.InCome) FROM Salary aINNER JOIN Employees bON a.EmployeeID=b.EmployeeIDINNER JOIN Departments cON c.DepartmentID=b.DepartmentIDWHERE c.DepartmentName=财务部 ;3. 求财务部雇员的平

23、均实际收入SELECT AVG(a.InCome-a.OutCome) FROM Salary aINNER JOIN Employees bON a.EmployeeID=b.EmployeeIDINNER JOIN Departments cON c.DepartmentID=b.DepartmentIDWHERE c.DepartmentName=财务部 ;4. 查询财务部雇员的最高和最低实际收入SELECT MIN(a.InCome-a.OutCome),MAX(a.InCome-a.OutCome) FROM Salary aINNER JOIN Employees bON a.Em

24、ployeeID=b.EmployeeIDINNER JOIN Departments cON c.DepartmentID=b.DepartmentIDWHERE c.DepartmentName=财务部 ;5. 求财务部雇员的总人数SELECT COUNT(a.EmployeeID) FROM Employees aINNER JOIN Departments bON a.DepartmentID=b.DepartmentIDWHERE b.DepartmentName=财务部 ;6. 统计财务部收入在 2500 元以上的雇员人数SELECT COUNT(a.EmployeeID) FRO

25、M Employees aINNER JOIN Departments bON a.DepartmentID=b.DepartmentIDINNER JOIN Salary cON c.EmployeeID=A.EmployeeIDWHERE c.InCome2500 AND b.DepartmentName=财务部;五、GROUP BY、ORDER BY 子句的使用1. 查找 Employees 表中男性和女性的人数SELECT Sex,COUNT(Sex) FROM Employees GROUP BY Sex;2. 按部门列出在该部门工作的员工的人数-使用内连接的方法-_SELECT b

26、.DepartmentName,COUNT(a.EmployeeID) FROM Employees aINNER JOIN Departments bON a.DepartmentID=b.DepartmentIDGROUP BY b.DepartmentName;-使用多表查询方法,GROUP BY后面的字段, 必须出现在SELECT语句要查询的字段中。SELECT b.DepartmentName,COUNT(a.EmployeeID) FROM Employees a,Departments bWHERE a.DepartmentID=b.DepartmentIDGROUP BY b.

27、DepartmentName;3. 按员工的学历分组，排列出本科、大专、硕士的人数SELECT a.Education,COUNT(a.EmployeeID) FROM Employees aGROUP BY a.Education;4. 查找员工数超过 2 的部门名称和雇员数量SELECT b.DepartmentName,COUNT(a.EmployeeID) FROM Employees a,Departments bWHERE a.DepartmentID=b.DepartmentIDGROUP BY b.DepartmentNameHAVING COUNT(a.EmployeeID)

28、2;5. 按员工的工作年份分组，统计各个工作年份的人数，例如工作 1 年的多少人，工作 2 年的多少人SELECT a.WorkYear,COUNT(a.EmployeeID) FROM Employees a GROUP BY a.WorkYear;6. 将雇员的情况按收入由低到高排列SELECT a.*,b.InCome FROM Employees a,Salary bWHERE a.EmployeeID=b.EmployeeIDORDER BY b.InCome ASC;7. 将员工信息按出生时间从小到大排列SELECT * FROM Employees ORDER BY Birthd

29、ay;8. 在 ORDER BY 子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列SELECT a.Name,a.Sex,a.WorkYear,b.InCome-b.OutCome FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeIDORDER BY b.InCome-b.OutCome DESC;六、视图的使用1. 创建视图（1 ）在数据库 YGGL 上创建视图 Departments_View，视图包含 Department 表的全部列USE YGGLGOCREATE VIEW Departments_

30、ViewAS SELECT * FROM Departments;-_（2 ）创建视图 Employees_Departments_View，视图包含员工号码、姓名、所在部门名称USE YGGLGOCREATE VIEW Employees_Departments_ViewAS SELECT a.EmployeeID,a.Name,b.DepartmentName FROM Employees a, Departments bWHERE a.DepartmentID=b.DepartmentID;（3 ）创建视图 Employees_Salary_View，视图包含员工号码、姓名和实际收入三列

31、USE YGGLGOCREATE VIEW Employees_Salary_View(EmployeeID,Name,RealInCome)AS SELECT a.EmployeeID,a.Name,b.InCome-b.OutCome FROM Employees a, Salary bWHERE a.EmployeeID=b.EmployeeID;2. 查询视图从视图 Employees_Salary_View 中查询出姓名为“王林”的员工的实际收入SELECT * FROM Employees_Salary_ViewWHERE Name=王林;3. 更新视图（1 ）向视图 Depart

32、ments_View 中添加一条记录（6 ， 广告部 ， 广告业务 ）执行完命令后，分别查看 Departments_View 和 Department 表中发生的变化INSERT INTO Departments_View VALUES(6,广告部 ,广告业务);（2 ）尝试向 Employees_Departments_View 中添加一条记录，看看会发生什么情况INSERT INTO Employees_Departments_View VALUES(777777,小林,综合业务部);-视图或函数Employees_Departments_View 不可更新，因为修改会影响多个基表。-D

33、epartmentID分别为Employees表中的外键和Departments表中的主键,并且均不为空,-这条INSERT 语句相当于给为表Employees和表Departments添加一条DepartmentID为空的记录,显然是非法的.（3 ）尝试向 Employees_Salary_View 中添加一条记录，看看会发生什么情况INSERT INTO Employees_Salary_View VALUES(777777,小林,3000);-对视图或函数Employees_Salary_View 的更新或插入失败，因其包含派生域或常量域。-RealInCome 就是派生域,它是由 In

34、Come-OutCome 获得的 .（4 ）将视图 Departments_View 中，部门号为6的部门名称修改为 生产车间UPDATE Departments_View SET DepartmentName=生产车间 WHERE DepartmentID=6;（5 ）删除视图 Departments_View 中最新增加的的一条记录DELETE FROM Departments_View WHERE DepartmentID=6;总结：一般不建议直接对视图进行增加、修改、删除的操作。4. 删除视图 Employees_Departments_ViewDROP VIEW Employees_Departments_View;-_-删除视图的操作并不会删除基表.5. 在界面工具中操作视图演示新建视图和查询视图