数据库实用技术-实验四.doc

数据查询和试图实验报告单院（系） 计算机学院 专业 计算机科学与技术 班级 姓名 学号 同组人 实验室 S4305 组号 日期 课程 数据库实用技术 指导教师 成绩 实验项目编号 8103203004 实验项目名称 数据查询和视图 一、实验目的(1)掌握SELECT语句的基本语法；(2)掌握子查询的表示；(3)掌握连接查询的表示;(4)掌握SELECT语句的GROUP BY子句的作用和使用方法；(5)掌握SELECT语句的ORDER BY子句的作用和使用方法；(6)熟悉视图的概念和作用；(7)掌握视图的创建方法；(8)掌握如何让查询和修改视图。 二、实验环境WIN7操作系统 SQL Server2008三、实验原理（或要求）（1）了解SELECT语句的基本语法格式；（2）了解SELECT语句的执行方法；（3）了解子查询的表示方法；（4）了解连接查询的表示；（5）了解SELECT语句的GROUPBY子句的作用和使用方法；（6）了解SELECT语句的ORDER BY子句的作用；（7）了解视图的概念；（8）了解创建视图的方法；（9）了解并掌握对视图的操作四、实验步骤（1）SELECT语句的基本使用USE YGGLGOSELECT * FROM Employees 用SELECT语句查询Employees表中所有数据信息USE YGGLGOSELECT * FROM Departments,Salary GO【思考与练习】用SELECT 语句查询Departments和Salary表中的所有数据信息。USE YGGLGOSELECT Address, PhoneNumber FROM Employees GO用SELECT语句查询Employees表中每个雇员的地址和电话。【思考与练习】 /*用SELECT语句查询Departments表中部门名*/ USE YGGLGOSELECT DepartName FROM Departments GOa. 用SELECT语句查询Deparments和Salary表的一列或若干列。/*用SELECT语句查询Salary表中的收入和支出*/USE YGGLGOSELECT InCome,OutCome FROM Salary GOUSE YGGLGOSELECT DISTINCT (DepartmentID),sex FROM Employees GO b.用SELECT语句查询Employees表中部门号和性别,并用DISTINCT消除重复行。 USE YGGLGOSELECT Address, PhoneNumber FROM Employees WHERE EmployeeID = 000001 GO 查询EmployeeID为000001的雇员的地址和电话。 【思考与练习】 USE YGGLGOSELECT EmployeeID,InCome FROM Salary WHERE InCome 2000 GOa.查询Salary表中月收入高于2000元的员工号码。USE YGGLGOSELECT Name,Address FROM Employees WHERE Birthday 1970 GOb.查询1970年以后出生员工的姓名和地址 USE YGGLGOSELECT EmployeeID ,name from Employees WHERE DepartmentID in(SELECT DepartmentID FROM Departments WHERE DepartName= 财务部) GO c.查询所有财务部的员工的号码和姓名。USE YGGL GO SELECT Address AS 地址,PhoneNumber AS 电话FROM Employees WHERE Sex=0GO查询 Employees 表中女雇员的地址和电话，使用 AS 子句将结果中各列的标题分别指定为地址、电话。【思考与练习】 USE YGGL GO SELECT Name AS 姓名, Birthday AS 出生日期FROM Employees WHERE Sex=1GO查询 Employees 表中男员工的姓名和出生日期，要求将各列标题用中文表示USE YGGL GO SELECT Name AS 姓名, CASE WHEN Sex=1 THEN 男 WHEN Sex=0 THEN 女 END AS 性别 FROM EmployeesGO 查询 Employees 表中员工的姓名和性别，要求 SEX 值为时显示为“男” ，为 0 时显示为“女”。【思考与练习】查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，20003000元显示为中等收入，3000元以上显示为高收入。USE YGGL GO SELECT Name,Address,CASE WHEN Income-OutCome 3000 then 高收入 ELSE 中等收入 END AS 收入等级 FROM Employees,Salary WHERE Employees.EmployeeID =Salary.EmployeeID GOUSE YGGL GO SELECT EmployeeID,实际收入=Income-OutCome FROM SalaryGO计算每个雇员的实际收入。 获得员工总数。USE YGGL GO SELECT COUNT (*)FROM EmployeesGOUSE YGGL GO SELECT AVG(Income)AS 平均收入 FROM SalaryGOa .计算salary表中员工月收入的平均数。b.获得Employees表中最大的员工号码。USE YGGL GO SELECT MAX(EmployeeID)AS 最大员工号码 FROM EmployeesGOUSE YGGL GOSELECT SUM(OutCome) AS总支出 FROM SalaryGOc.计算salary表中的所有员工的总支出。-最高收入SELECT MAX(INcome-OutCome) FROM Salary ,Employees,DepartmentsWHERE Salary.EmployeeID=Employees.EmployeeID AND Employees.DepartmentID=Departments.DepartmentID AND DepartName=财务部GOd查询财务部雇员的最高和最低实际收入。-最低收入SELECT MIN(INcome-OutCome) FROM Salary ,Employees,DepartmentsWHERE Salary.EmployeeID=Employees.EmployeeID AND Employees.DepartmentID=Departments.DepartmentID AND DepartName=财务部GOUSEYGGLGO SELECT DepartmentID FROM EmployeesWHERE Name LIKE 王%找出所有姓王的雇员的部门号。【思考与练习】 SELECT EmployeeID ,DepartmentID FROM EmployeesWHERE Address LIKE%中山%GOa找出所有其地址中含有“中山”的雇员的号码及部门SELECT Education,Address,Name FROM Employees WHERE EmployeeID LIKE%0_GOb查找员工号码倒数第二个数字为0的员工的姓名、地址和学历。SELECT EmployeeID FROM Salary WHERE Income BETWEEN 2000 AND 3000GO找出所有收入在2000-3000元之间的员工号码。【思考与练习】SELECT EmployeeID FROM Employees WHERE DepartmentID = 1 OR DepartmentID =2GO找出所有在部门“1”或“2”工作的雇员的号码。 SELECT EmployeeID as 编号,InCome as 收入 INto 收入在元以上的员工 FROM Salary WHERE InCome 1500GO 使用INTO子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和收入【思考与练习】SELECT EmployeeID,Name INto 男员工表 FROM Employees WHERE sex=1GO使用INTO子句，由表Employees创建“男员工”表，包括编号和姓名。 （2）子查询的使用。 查找在财务部工作的雇员的情况。SELECT * FROM Employees WHERE DepartmentID = ( SELECT DepartmentID FROM Departments WHERE DepartName =财务部 ) 【思考与练习】SELECT * FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM Salary WHERE INcome ALL ( SELECT Birthday FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartName=研发部 )【思考与练习】SELECT Name FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM Salary WHERE EmployeeID IN ( SELECT EmployeeID FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartName=研发部 )AND INcomeALL(SELECT INcome FROM Salary WHERE EmployeeID IN ( SELECT EmployeeID FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartName=财务部 )用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。 （3）子查询的使用。SELECT Employees.*,Salary .* FROM Employees,Salary WHERE Employees.EmployeeID=Salary.EmployeeID查找在财务部工作的雇员的情况及其薪水的情况。【思考与练习】SELECT Employees.*,Departments.* FROM Employees,Departments WHERE Employees.DepartmentID = Departments.DepartmentID查询每个雇员的情况及其工作部门的情况。SELECT DepartName FROM Departments JOIN Employees ON Departments.DepartmentID = Employees.DepartmentID WHERE Employees.Name = 王林 使用内连接的方法查询名字为“王林”的员工所在的部门.【思考与练习】 SELECT Employees.* FROM Employees INner JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID WHERE DepartName!=财务部a. 使用内连接的方法查找出不在财务部工作的所有员工信息。SELECT Employees .Name AS 姓名,Salary .InCome AS 收入 FROM Employees LEFT OUTER JOIN Salary ON Employees .EmployeeID =Salary .EmployeeIDb.使用外连接方法查找出所有员工的月收入。 查找财务部收入在2000元以上的雇员姓名及其薪水详情。新建一个查询，在查询分析器中输入如下语句并执行。SELECT Name, InCome, OutCome FROM Employees, Salary, Departments WHERE Employees.EmployeeID = Salary.EmployeeID AND Employees.DepartmentID = Departments.DepartmentID AND DepartName = 财务部 AND Income 2000【思考与练习】SELECT Name ,InCome FROM (Employees JOIN Salary ON Employees.EmployeeID =Salary .EmployeeID) JOIN Departments ON Employees .DepartmentID =Departments .DepartmentID WHERE DepartName =研发部 AND Birthday 2 找员工数超过2的部门名称和员工数量。【思考与练习】按员工的工作年份分组，统计各个工作年份的人数，例如，工作SELECT WorkYear ,COUNT(*) AS 人数 FROM Employees GROUP BY WorkYear一年的多少人，工作两年的多少人。 SELECT Employees.* ,Salary.INcome FROM Employees ,Salary WHERE Employees.EmployeeID=Salary.EmployeeID ORDER BY INcome将各雇员的情况按收入由低到高排列。【思考与练习】a.将员工的信息按出生的时间从小到大排列。SELECT * FROM Employees ORDER BY Birthday b.在orderby子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实SELECT Name ,sex,workyear,INcome-OutCome FROM Salary ,Employees WHERE Salary.EmployeeID=Employees.EmployeeID ORDER BY INcome-OutCome DESC际收入从大到小排列。 视图的使用（1）创建视图。CREATE VIEW DS_VIEW AS SELECT*FROM Departments 创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列。 创建YGGL数据库上的视图Employees-view，视图包含“员工号码”、“姓名”、“实际收入”三列。CREATE VIEW Employees_view(EmployeeID,Name,realIncome) AS SELECT Employees.EmployeeID,Name,Income-OutCome FROM Employees,salary WHERE Employees.EmployeeID=salary.EmployeeID使用如下SQL语句： （2）查询视图：SELECT DepartName FROM DS_VIEW WHERE DepartmentID=3 从视图DS_VIEW中查询出部门号为3的部门名称。SELECT RealIncome FROM Employees_view WHERE Name=王林 从视图Employees_view中查询出姓名为“王林”的员工的实际收入（3）更新视图。 INSERT INTO DS_VIEW VALUES(6,广告部,广告业务)向视图DS_VIEW中插入一行数据“6，广告部，广告业务”执行完该命令，使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。尝试向视图Employees_view中插入一行数据看看会发生什么情况。视图DS_VIEW发生如下变化：基本表Departments发生如下变化：修改视图UPDATE DS_VIEW SET DepartName=生产车间 WHERE DepartmentID=5DS_VIEW，将部门号为5的部门名称修改为“生产车间”SELECT *FROM DS_VIEW执行完该命令，使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。 视图DS_VIEW发生如下变化：SELECT *FROM Departments基本表Departments发生如下变化： 视图Employees_view中员工号为“000001”的员工的姓名修改为“王浩”UPDATE Employees_view SET Name = 王浩 WHERE EmployeeID = 000001 SELECT * FROM Employees_view 删除视图DS_VIEW中部门号为“1”的一行数据DELETE FROM DS_VIEW WHERE DepartmentID=1SELECT * FROM DS_VIEW(4)删除视图删除视图DS_VIEW. DROP VIEW DS_VIEW（5）在界面工具中操作视图创建视图：启动SQLServerManagementStudio，在对象资源管理器中展开“数据