预习报告部分
一、实验目的
1.掌握SELECT语句的基本语法;
2.掌握子查询的表示;
3.掌握连接查询的表示;
4.掌握数据汇总的方法;
5.掌握SELECT语句的GROUP BY子句的作用和使用方法;
6.掌握SELECT语句的ORDER BY子句的作用和使用方法。
二、实验用仪器设备、器材或软件环境
联想拯救者Y9000P笔记本,MySQL软件环境
实验报告部分
一、仪器设备型号及编号
LAPTOP-81P3TJ4L
二、实验器材或软件环境
Microsoft SQL Server Management Stdio
三、实验内容及结果
1.SELECT语句的基本使用
(1)对于实验1给出的数据库表结构,查询每个雇员的所有数据。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT *
FROM Employees
GO
【思考与练习】
用SELECT语句查询Departments和Salary表的所有记录。
SELECT *
FROM Departments
SELECT *
FROM Salary
(2)查询每个雇员的地址和电话。
USE YGGL
SELECT Address,PhoneNumber
FROM Employees
GO
【思考与练习】
用SELECT语句查询Departments和Salary表的一列或若干列。
#查询每个部门的名称
USE YGGL
SELECT DepartmentName
FROM Departments
GO
#查询每个职工的收入和支出
USE YGGL
SELECT InCome,OutCome
FROM Salary
GO
(3)查询EmployeeID为000001的雇员的地址和电话。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Address,PhoneNumber
FROM Employees
WHERE EmployeeID='000001'
GO
【思考与练习】
用SELECT语句查询Departments和Salary表中满足指定条件的1列或若干列。
#查询DepartmentID为3的部门的部门名称
USE YGGL
SELECT DepartmentName
FROM Departments
WHERE DepartmentID='1'
GO
#查询EmployeeID为000001的雇员的收入和支出
USE YGGL
SELECT InCome,OutCome
FROM Salary
WHERE EmployeeID='000001'
GO
(4)查询Employees表中女雇员的地址和电话,使用AS子句将结果中各列的标题分别指定为地址、电话。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Address AS地址,PhoneNumber AS电话
FROM Employees
WHERE sex=0
Go
注意:使用AS子句可指定目标列的标题。
- 计算每个雇员的实际收入。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Name as '职工姓名', InCome-OutCome as '实际收入'
FROM Salary,Employees
WHERE Salary.EmployeeID=Employees.EmployeeID
GO
找出所有姓王的雇员的部门号。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Name as '姓名',Departments.DepartmentID as '部门号'
FROM Employees,Departments
WHERE Name LIKE '王%'AND Departments.DepartmentID=Employees.DepartmentID
GO
【思考与练习】找出所有其地址中含有‘‘中山”的雇员的号码及部门号。
USE YGGL
SELECT EmployeeID as '雇员号码',Departments.DepartmentID as '部门',
Address as '地址'
FROM Employees , Departments
WHERE Address LIKE '%中山%' AND Departments.DepartmentID=Employees.DepartmentID
GO
- 找出所有收入在2000~3000元之间的雇员号码。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT EmployeelD
FROM Salary
WHERE InCome BETWEEN 2000 AND 3000
GO
【思考与练习】
找出所有在部门‘1’或‘2’工作的雇员的号码。
USE YGGL
SELECT PhoneNumber as '电话号码',DepartmentID as '部门号'
FROM Employees
WHERE DepartmentID = '1' OR DepartmentID = '2'
GO
注意:在SELECT语句中LIKE、BETWEEN…AND,,IN,NOT及CONTAIN谓词的作用。
2.子查询的使用
(1)查找在财务部工作的雇员的情况。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT *
FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='财务部')
GO
【思考与练习】
用子查询的方法查找所有收入在2500元以下的雇员的情况。
USE YGGL
SELECT *
FROM Employees
WHERE EmployeeID IN
(SELECT EmployeeID
FROM Salary
WHERE InCome<2500)
GO
(2)查找财务部年龄不低于研发部雇员年龄的雇员的姓名。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Name
FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName’财务部’)
AND
Birthday!>ALL(SELECT Birthday
FROM Employees
WHERE DepartmentlD IN
(SELECT DepartmentlD
FROM Departments
WHERE DepartmentName=’研发部’))
GO
【思考与练习】
用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。
USE YGGL
SELECT Name
FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='财务部')
AND
Birthday!>ALL(SELECT Birthday
FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='研发部'))
GO
(3)查找比所有财务部的雇员收入都高的雇员的姓名。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Name
FROM Employees
WHERE EmployeeID IN
(SELECT EmployeelD
FROM Salary
WHERE InCome> .
ALL ( SELECT InCome
FROM Salary
WHERE EmployeelD IN
(SELECT EmployeelD
FROM Employees
WHERE DepartmentlD=
(SELECT DepartmentlD
FROM Departments
WHERE DepartmentName=’财务部’))))
GO
【思考与练习】
用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。
USE YGGL
SELECT Name
FROM Employees
WHERE Birthday<ALL(SELECT Birthday
FROM Employees
WHERE DepartmentID IN
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='研发部'))
GO
3.连接查询的使用
- 查询每个雇员的情况以及其薪水的情况。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Emploees.*,Salary.*
FROM Employees,Salary
WHERE Elmployees.EmployeelD=Salary.EmployeelD
GO
【思考与练习】查询每个雇员的情况以及其工作部门的情况。
USE YGGL
SELECT Employees.EmployeeID,Employees.Name,Employees.Birthday,Employees.Sex,Employees.Zip,Employees.PhoneNumber,Employees.EmailAddress,Employees.DepartmentID,Departments.DepartmentName
FROM Employees,Departments
WHERE Employees.DepartmentID=Departments.DepartmentID
GO
- 查找财务部收入在2200元以上的雇员姓名及其薪水详情。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Name,InCome,OutCome
FROM Employees,Salary,Departments
WHERE Employees.EmployeeID=Salary.EmployeeID AND Employees.DepartmentID=Departments.DepartmentID AND DepartmentName='财务部'AND InCome>2200
GO
【思考与练习】查询研发部在1966年以前出生的雇员姓名及其薪水详情。
USE YGGL
SELECT Name,Birthday,InCome,OutCome
FROM Employees,Salary,Departments
WHERE Employees.EmployeeID=Salary.EmployeeID AND Employees.DepartmentID=Departments.DepartmentID AND DepartmentName='研发部'AND Birthday<'1966-01-01'
GO
4.数据汇总
(1)求财务部雇员的平均收入。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT AVG(InCome)AS '财务部平均收入'
FROM Salary
WHERE EmployeeID IN
(SELECT EmployeeID
FROM Employees
WHERE DepartmentID=
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='财务部'))
GO
【思考与练习】查询财务部雇员的最高和最低收入。
USE YGGL
SELECT MAX(InCome) AS '财务部雇员最高收入',MIN(InCome) AS '财务部雇员最低收入'
FROM Salary
WHERE EmployeeID IN
(SELECT EmployeeID
FROM Employees
WHERE DepartmentID=
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='财务部'))
GO
- 求财务部雇员的平均实际收入。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT AVG(1nCome-OutCome)AS‘财务部平均实际收入’
FROM Salary
WHERE EmployeelD IN
(SELECT EmployeeID
FROM Employees
WHERE DepartanentlD =
(SELECT DepartmentlD
FROM Departments
WHERE DepamnentName=’财务部’))
GO
【思考与练习】
查询财务部雇员的最高和最低实际收入。
USE YGGL
SELECT MAX(InCome-OutCome) AS '财务部雇员最高实际收入',MIN(InCome-OutCome) AS '财务部雇员最低实际收入'
FROM Salary
WHERE EmployeeID IN
(SELECT EmployeeID
FROM Employees
WHERE DepartmentID =
(SELECT DepartmentID
FROM Departments
WHERE DepartmentName='财务部'))
GO
- 求财务部雇员的总人数。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT COUNT(EmployeelD)
FROM Employees
WHERE DepartmentlD=
(SELECT DepartmentlD
FROM Departments
WHERE DepartmentName=’财务部’)
GO
【思考与练习】统计财务部收入在2500元以上雇员的人数。
USE YGGL
SELECT COUNT(Employees.EmployeeID) AS '财务部收入>2500人数'
FROM Employees,Salary,Departments
WHERE Employees.EmployeeID=Salary.EmployeeID AND
Employees.DepartmentID=Departments.DepartmentID AND
DepartmentName = '财务部' AND InCome > 2500
GO
5.GROUP BY,ORDER BY子句的使用
(1)求各部门的雇员数。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT DepartmentID as '部门号',COUNT(EmployeeID) as '雇员数'
FROM Employees
GROUP BY DepartmentID
GO
【思考与练习】统计各部门收入在2000元以上雇员的人数。
USE YGGL
SELECT DepartmentID as '部门号',COUNT(Employees.EmployeeID) as '收入>2000人数'
FROM Employees,Salary
WHERE Employees.EmployeeID=Salary.EmployeeID AND InCome > 2000
GROUP BY DepartmentID
GO
(2)将各雇员的情况按收入由低到高排列。
在查询分析器的窗口输入如下的语句并执行:
USE YGGL
SELECT Employees.EmployeeID,Employees.Name,Employees.Birthday,Employees.Sex,Employees.Zip,Employees.PhoneNumber,Employees.EmailAddress,Employees.DepartmentID,Salary.InCome
FROM Employees,Salary
WHERE Employees.EmployeeID=Salary.EmployeeID
ORDER BY InCome
GO
【思考与练习】将各雇员的情况按出生时间先后排列。
USE YGGL
SELECT *
FROM Employees
ORDER BY Birthday
GO