实验四吕恩在.docx
《实验四吕恩在.docx》由会员分享,可在线阅读,更多相关《实验四吕恩在.docx(16页珍藏版)》请在冰豆网上搜索。
实验四吕恩在
学校代码:
10128
学号:
《数据库原理及应用》实验报告
(
二〇一六年五月
数据库的查询
1、实验目的
(1)掌握select语句的基本语法
(2)掌握子查询的表示
(3)掌握连接查询的表示
(4)掌握select语句的groupby子句的作用和使用方法
(5)掌握select语句的orderby子句的作用和使用方法
2、实验内容
(1)基本查询
(2)子查询
(3)连接查询
(4)使用聚合函数查询
(5)查询结果分组和排序
3、实验程序
--1.1
useYGGL
go
select*fromEmployees;
--1.2
useYGGL
go
selectAddress,PhoneNumber
fromEmployees;
--1.3
selectAddress,PhoneNumber
fromEmployees
whereEmployeeID='000001'
go
--1.4
selectAddressas地址,PhoneNumberas电话
fromEmployees
whereSex=0
--1.5
selectNameas姓名,
case
whenSex=1then'男'
whenSex=0then'女'
endas性别
fromEmployees
--1.6
selectEmployeeID,实际收入=InCome-OutCome
fromSalary
--1.7
selectcount(*)
fromEmployees
--1.8
selectDepartmentID
fromEmployees
whereNamelike'王%'
--1.9
selectEmployeeID
fromSalary
whereInComebetween2000and3000;
--1.10
selectEmployeeIDas编号,InComeas收入
into收入在1500元以上的员工
fromSalary
whereInCome>1500
--2.1两表在部门号上有联系
select*fromEmployees
whereDepartmentID=
(
selectDepartmentID
fromDepartments
whereDepartmentName='财务部'
);
selectEmployees.*fromEmployees,Departments
whereEmployees.DepartmentID=Departments.DepartmentID
andDepartments.DepartmentName='财务部'
--2.2
selectName
fromEmployees
whereDepartmentIDin
(
selectDepartmentID
fromDepartments
whereDepartmentName='财务部'
)
and
Birthday!
>all
(
selectBirthdayfromEmployees
whereDepartmentIDin(
selectDepartmentID
fromDepartments
whereDepartmentName='研发部'
)
);
--2.3
selectNamefromEmployees
whereEmployeeIDin
(
selectEmployeeIDfromSalary
whereIncome>all
(selectInComefromSalary
whereEmployeeIDin
(
selectEmployeeIDfromEmployees
whereDepartmentID=
(
selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
)
)
)
)
--3.1
selectEmployees.*,Salary.*
fromEmployees,Salary
whereEmployees.EmployeeID=Salary.EmployeeID;
--3.2
selectDepartmentName
from
DepartmentsjoinEmployees
on
Departments.DepartmentID=Employees.DepartmentID
whereEmployees.Name='王林';
--3.3
selectName,InCome,OutCome
fromEmployees,Salary,Departments
whereEmployees.EmployeeID=Salary.EmployeeID
andEmployees.DepartmentID=Departments.DepartmentID
andDepartmentName='财务部'
andInCome>2000
--4.1
selectavg(InCome)as'财务部平均收入'
fromSalary
whereEmployeeIDin
(
selectEmployeeIDfromEmployees
whereDepartmentIDin
(
selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
)
)
--4.2
selectavg(InCome-OutCome)as'财务部平均实际收入'
fromSalary
whereEmployeeIDin
(
selectEmployeeIDfromEmployees
whereDepartmentIDin
(
selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
)
)
--4.3
selectcount(EmployeeID)
fromEmployees
whereDepartmentID=
(
selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
);
--5.1
selectSex,count(Sex)
fromEmployees
groupbySex;
--5.2
selectEmployees.DepartmentID,count(*)as人数
fromEmployees,Departments
whereEmployees.DepartmentID=Departments.DepartmentID
groupbyEmployees.DepartmentID
havingcount(*)>2;
--5.3
selectEmployees.*,Salary.*
fromEmployees,Salary
whereEmployees.EmployeeID=Salary.EmployeeID
orderbyInCome;
4、实验结果
图1-1
图1-2
图1-3
图1-4
图1-5
图1-6
图1-7
图1-8
图1-9
图1-10
图2-1
图2-2
图2-3
图3-1
图3-2
图3-3
图4-1
图4-2
图4-3
图5-1
图5-2
图5-3
五.实验总结
通过此次试验,我们掌握了select语句的基本语法以及子查询和连接查询的表示,还有groupby子句和orderby子句的作用及使用方法。
视图的使用
一、实验目的
(6)熟悉视图的概念和作用
(7)掌握视图的创建方法
(8)掌握如何查询和修改视图
二、实验内容
(6)创建视图
(7)查询视图
(8)更新视图
三、实验程序
--1.1
createviewDS_VIEW
as(select*fromDepartments)
go
--1.2
createviewEmployees_view(EmployeeID,Name,RealIncome)
as
selectEmployees.EmployeeID,Name,InCome-OutCome
fromEmployees,Salary
whereEmployees.EmployeeID=Salary.EmployeeID
go
--2.1
selectDepartmentName
fromDS_VIEW
whereDepartmentID='3';
--2.2
selectRealIncome
fromEmployees_view
whereName='王林'
--3.1
insertintoDS_VIEWvalues('6','广告部','广告业务')
--3.2
updateDS_VIEW
setDepartmentName='生产车间'
whereDepartmentID='5'
--3.3
updateEmployees_view
setName='王浩'
whereEmployeeID='000001'
--3.4
deletefromDS_VIEW
whereDepartmentID='1';
四、实验结果
图1
图2-1
图2-2
图3
图4
五、实验总结
通过本次实验后,我们练习和熟悉了视图的创建,查询和修改方法,整体来看,视图的增删改查基本和表的操作方法是一样的。