数据库的查询和视图.doc

学校代码： 10128学 号： 201320905005 数据库原理及应用实验报告（题 目：数据库的查询和视图学生姓名：周亚东学 院：理学院系 别：数学系专 业：信息与计算科学班 级：信计13-1任课教师：侯睿 二一六年五月184.1数据库的查询1、 实验目的(1) .掌握SELECT语句的基本语法；(2) .掌握子查询的表示；(3) .掌握链接查询的表示；(4) .掌握SELECT语句的GROUP BY子句的作用和使用方法；(5) .掌握SELECT语句的ORDER BY子句的作用和使用方法；2、 实验内容1) .基本查询(1) .对于实验2给出的数据库表结构，查询每个雇员的所有数据(2) .用SELECT语句查询Employees表中每个雇员的地址和电话(3) .查询EmployeeID为000001的雇员的地址和电话(4) .查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话(5) .查询Employees表中员工的姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”(6) .计算每个雇员的实际收入(7) .获得员工总数(8) .找出所有姓王的雇员的部门号(9) .找出所有收入在2000-3000元之间的员工号码(10) .使用INTO子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和姓名2) .子查询(1) .查找在财务部工作的雇员的情况(2) .查找财务部年龄不低于研发部雇员年龄的雇员的姓名(3) .查找比所有财务部的雇员收入都高的雇员的姓名3) .连接查询(1) .查询每个雇员的情况及其薪水情况(2) .使用内连接的方法查询名字为“王林”的员工所在的部门(3) .查找财务部收入在2000元以上的雇员明明及其薪水详情4) .使用聚合函数查询(1) .求财务部雇员的平均收入(2) .求财务部雇员的平均实际收入(3) .求财务部雇员的总人数5) .查询结果分组和排序(1) .查找Employees表中男性和女性的人数(2) .查找员工数超过2人的部门名称和员工数量(3) .将各雇员的情况按收入由低到高排列3、 实验程序1).基本查询(1).对于实验2给出的数据库表结构，查询每个雇员的所有数据代码：USE YGGL GO SELECT * FROM Employees(2).用SELECT语句查询Employees表中每个雇员的地址和电话代码：SELECT Address，PhoneNumber FROM Employees(3).查询EmployeeID为000001的雇员的地址和电话代码：SELECT Address，PhoneNumber FROM Employees WHERE EmployeeID=000001 GO(4).查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话代码：SELECT Address AS 地址,PhoneNumber AS 电话 FROM Employees WHERE Sex=0(5).查询Employees表中员工的姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”代码：SELECT Name AS 姓名, CASE WHEN Sex=1THEN男 WHEN Sex=0THEN女 END AS 性别 FROM Employees(6).计算每个雇员的实际收入代码：SELECT ID,实际收入=InCome-Expense FROM Salary(7).获得员工总数代码：SELECT COUNT(*) FROM Employees(8).找出所有姓王的雇员的部门号代码：SELECT DepartmentID FROM Employees WHERE Name LIKE王%(9).找出所有收入在2000-3000元之间的员工号码代码：SELECT ID FROM Salary WHERE InCome BETWEEN 2000 AND 3000(10).使用INTO子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和姓名代码：SELECT ID AS 编号,InCome AS 收入 INTO 收入在1500元以上的员工 FROM Salary WHERE InCome15002).子查询(1).查找在财务部工作的雇员的情况代码：SELECT * FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 )(2).查找财务部年龄不低于研发部雇员年龄的雇员的姓名代码：SELECT Name FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 )ANDBirthday!ALL( SELECT Birthday FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName=研发部 )(3).查找比所有财务部的雇员收入都高的雇员的姓名代码：SELECT Name FROM Employees WHERE EmployeeID IN ( SELECT EmployeeID FROM Salary WHERE InComeALL ( SELECT InCome FROM Salary WHERE EmployeeID IN ( SELECT EmployeeID FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 ) ) ) )3).连接查询(1).查询每个雇员的情况及其薪水情况代码：USE YGGLGOSELECT Employees.*,Salary.* FROM Employees,Salary WHERE Employees.EmployeeID=Salary.ID(2).使用内连接的方法查询名字为“王林”的员工所在的部门代码：SELECT DepartmentName FROM Departments JOIN Employees ON Departments.DepartmentID=Employees.DepartmentID WHERE Employees.Name=王林(3) .查找财务部收入在2000元以上的雇员明明及其薪水详情代码：SELECT Name,Income,Expense FROM Employees,Salary,Departments WHERE Employees.EmployeeID=Salary.ID AND Employees.DepartmentID=Departments.DepartmentID AND DepartmentName=财务部 AND Income20004).使用聚合函数查询(1).求财务部雇员的平均收入代码：SELECT AVG(Income)AS财务部平均收入 FROM Salary WHERE ID IN ( SELECT EmployeeID FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 ) )(2).求财务部雇员的平均实际收入代码：SELECT AVG(Income-Expense)AS财务部平均实际收入 FROM Salary WHERE ID IN ( SELECT EmployeeID FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 ) )(3).求财务部雇员的总人数代码：SELECT COUNT(EmployeeID) FROM Employees WHERE DepartmentID= ( SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 )5).查询结果分组和排序(1).查找Employees表中男性和女性的人数代码：SELECT Sex,COUNT(Sex) FROM Employees GROUP BY Sex;(2).查找员工数超过2人的部门名称和员工数量代码：SELECT Employees.DepartmentID,COUNT(*)AS 人数 FROM Employees,Departments WHERE Employees.DepartmentID=Departments.DepartmentID GROUP BY Employees.DepartmentID HAVING COUNT(*)2(3) .将各雇员的情况按收入由低到高排列代码：SELECT Employees.*,Salary.* FROM Employees,Salary WHERE Employees.EmployeeID=Salary.ID ORDER BY Income4、 实验结果1).基本查询(1).对于实验2给出的数据库表结构，查询每个雇员的所有数据(2).用SELECT语句查询Employees表中每个雇员的地址和电话(3).查询EmployeeID为000001的雇员的地址和电话(4).查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话(5).查询Employees表中员工的姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”(6).计算每个雇员的实际收入(7).获得员工总数(8).找出所有姓王的雇员的部门号(9).找出所有收入在2000-3000元之间的员工号码(10).使用INTO子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和姓名2).子查询(1).查找在财务部工作的雇员的情况(2).查找财务部年龄不低于研发部雇员年龄的雇员的姓名(3).查找比所有财务部的雇员收入都高的雇员的姓名3).连接查询(1).查询每个雇员的情况及其薪水情况(2).使用内连接的方法查询名字为“王林”的员工所在的部门(3) .查找财务部收入在2000元以上的雇员明明及其薪水详情4).使用聚合函数查询(1).求财务部雇员的平均收入(2).求财务部雇员的平均实际收入(3).求财务部雇员的总人数5).查询结果分组和排序(1).查找Employees表中男性和女性的人数(2).查找员工数超过2人的部门名称和员工数量(3).将各雇员的情况按收入由低到高排列4.2 视图的使用一、实验目的1.熟悉视图的概念和作用2.掌握视图的创建方法3.掌握如何查询和修改视图二、实验内容1)创建视图(1).创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列(2).创建YGGL数据库上的视图Employees_view，视图包含”员工号码”、“姓名”和“实际收入”三列2)查询视图(1).从视图DS_VIEW中查询出部门号为“3”的部门名称(2) .从视图Employees_view中查询出姓名为“王林”的员工的实际收入3)更新视图(1).向视图DS_VIEW中插入一行数据“6，广告部，广告业务”(2) .修改视图DS_VIEW，将部门号为“5”的部门名称修改为“生产车间”(3) .将视图Employees_view中员工号为“000001”的员工的姓名修改为“王浩”(4) .删除视图DS_VIEW中部门号为“1”的一行数据4)删除视图3、 实验程序1)创建视图(1).创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列CREATE VIEW DS_VIEW AS SELECT* FROM Departments(2).创建YGGL数据库上的视图Employees_view，视图包含”员工号码”、“姓名”和“实际收入”三列CREATE VIEW Employees_view(ID,name,RealIncome) AS SELECT Employees.Employees,name,Income-Outcome FROM Employees,SalaryWHERE Employees.Employees=Salary.ID2)查询视图(1).从视图DS_VIEW中查询出部门号为“3”的部门名称SELECT DepartmentNameFROM DS_VIEWWHERE DepartmentsID=3 (2).从视图Employees_view中查询出姓名为“王林”的员工的实际收入SELECT RealIncomeFROM Employees_viewWHERE Name=朱俊3)更新视图(1).向视图DS_VIEW中插入一行数据“6，广告部，广告业务”INSERT INTO DS_VIEW VALUES(6,广告部,广告业务)(5) .修改视图DS_V