SQLServer有效教程第三版实验4数据库的查询与视图.docx
《SQLServer有效教程第三版实验4数据库的查询与视图.docx》由会员分享，可在线阅读，更多相关《SQLServer有效教程第三版实验4数据库的查询与视图.docx（13页珍藏版）》请在冰点文库上搜索。
SQLServer有效教程第三版实验4数据库的查询与视图
实验四数据库的查询和视图
数据库的查询
（1）把握select语句的大体语法;
（2）把握子查询的表示
（3）把握连接查询的表示
（4）把握select语句的groupby子句的作用和利用方式
（5）把握select语句的orderby子句的作用和利用方式
2实验预备
（1）了解SELECT语句的大体语法格式；
（2）了解SELECT语句的执行方式；
（3）了解子查询的表示方式；
（4）了解连接查询的表示；
（5）了解SELECT语句的GROUPBY子句的作用和利用方式；
（6）了解SELECT语句的ORDER ＢＹ子句的作用；
3实验内容
ＳＥＬＥＣＴ语句的大体利用。
①关于实验２给出的数据库表结构，查询每一个雇员的所有数据。
新建一个查询，在查询分析器中输入如下语句并执行：
ＵＳＥ ＹＧＧＬ
GO
SELECT*
FROMＥｍｐｌｏｙｅｅｓ
【试探与练习】用SELECT语句查询Departments和Salary表中所有的数据信息。
用SELECT语句查询Employees表中每一个雇员的地址和。
新建一个查询，在查询分析器中输入如下语句并执行：
UseYGGL
GO
SELECTAddressPhoneNumber
FROMEmployees
【试探与练习】
a.用SELECT语句查询Deparments和Salary表的一列或假设干列。
b.查询Employees表中的部门号和性别，要求利用DISTINCT排除重复行。
c.查询EmployeeID为000001的雇员的地址和。
UseYGGL
GO
SELECTAddressPhoneNumber
FROMEmployees
WHEREEmployeeID=’000001’
【试探与练习】
a.查询月收入高于2000元的员工号码。
b.查询1970年以后诞生的员工的姓名和住址。
c.查询所有财务部的员工的号码和姓名。
查询Employees表中女雇员的地址和，利用AS子句将结果中各列的题目别离指定为地址、。
USEYGGL
GO
SELECTAddressAS地址，PhoneNumberAS
FROMEmployees
WHERESex=0
【试探与练习】查询Employees表中男员工的姓名和诞生日期，要求将各列题目用中文标示。
⑤查询Employees表中员工的姓名和性别，要求SEX值为时显示为“男”，为0时显示为“女”。
SELECT Ｎａｍｅ ＡＳ姓名，
ＣＡＳＥ
ＷＨＥＮSex=1THEN 男
ＷＨＥＮSex=1THEN 女
ENDＡＳ 性别
ＦＲＯＭEmployees
【试探与练习】查询Employees员工的姓名、住址和收入水平，2000元以下显示低收入，2000-3000元显示为中等收入，3000元以上显示为高收入。
⑥计算每一个雇员的实际收入
USEYGGL
GO
SELECTEployeeID,实际收入=income-OutCome
FROMＳａｌａｒｙ
【试探与练习】利用ＳＥＬＥＣＴ语句进行简单的计算。
⑦取得员工总数。
ＳＥＬＥＣＴ ＣＯＵＮＴ（＊）
FROM Ｓａｌａｒｙ
【试探与练习】
ａ．计算ｓａｌａｒｙ表中员工月收入的平均数。
ｂ．取得Ｅｍｐｌｏｙｅｅｓ表中最大员工号码。
ｃ．计算Ｓａｌａｒｙ表中所有员工的总支出。
ｄ．查询财务部雇员的最高和最低实际收入。
⑧找出所有姓王的雇员的部门号。
USEYGGL
GO
SEＬＥＣＴ ＤｅｐａｒｔｍｅｎｔＩＤ
ＦＲＯＭ Ｅｍｐｌｏｙｅｅｓ
ＷＨＥＲＥ Ｎａｍｅ ＬＩＫＥ 王％
【试探与练习】
ａ．找出所有其地址中含有“中山”的雇员的号码及部门．
ｂ．查找员工号码倒数第二个数字为０的员工的姓名、地址和学历。
⑨找出所有收入在２０００－３０００元之间的员工号码。
SELECTEmployeeID
fromSalary
whereInComebetween2000and3000
【试探与练习】找出所有在部门“1”或“2”工作的雇员的号码。
注意：
了解在SELECT语句中LIKE、BETWEEN…AND、IN、NOT和CONTAIN谓词的作用。
⑩利用into子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和收入
USEYGGL
GO
selectEmployeeIDas编号,Incomeas收入
into收入在1500元以上的员工
fromSalary
whereInCome>1500
【试探与练习】利用Into子句，由表Employees创建“男员工”表，包括编号和姓名。
（1）子查询的利用。
①查找在财务部工作的雇员的情形
USEYGGL
GO
select*
fromEmployees
whereDepartmentID=
（
selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
）
【试探与练习】用子查询的方式查找所有收入在2500元以下的雇员的情形
②查找财务部年龄不低于研发部雇员年龄的雇员的姓名。
USEYGGL
GO
selectName
fromEmployees
whereDepartmentIDin
（selectDepartmentID
fromDepartments
whereDepartmentName='财务部'
）
and
Birthday！
>all
（selectBirthday
fromEmployees
whereDepartmentIDin
（
selectDepartmentIDfromDepartments
whereDepartmentName='研发部'
）
）
【试探与练习】用子查询的方式查找研发部比所有财务部雇员收入都高的雇员的姓名。
③查找比所有财务部的雇员收入都高的雇员的姓名。
USEYGGL
GO
SelectNamefromEmployees
whereEmployeeIDin
（selectEmployeeIDfromSalary
whereInCome>all
（selectInComefromSalary
whereEmployeeIDin
（selectEmployeeIDfromEmployees
whereDepartmentID=
（selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
）
）
）
）
【试探与练习】用子查询的方式查找所有年龄比研发部雇员年龄都大的雇员的姓名。
（2）连接查询的利用。
1查询每一个雇员的情形及其薪水的情形。
USEYGGL
GO
selectEmployees.*,Salary.*
fromEmployees,Salary
where=
【试探与练习】查询每一个雇员的情形及其工作部门的情形。
②利用内连接的方式查询名字为“王林”的员工所在的部门
selectDepartmentNamefromDepartments
JoinEmployeeson=
where='王林'
【试探与练习】
a．利用内连接的方式查找出不在财务部工作的所有员工信息。
b．利用外连接方式查找出所有员工的月收入。
2查找财务部收入在2000元以上的雇员姓名及其薪水详情。
新建一个查询，在查询分析器中输入如下语句并执行。
selectName,Income,Outcome
fromEmployees,Salary,Departments
where=
and=
andDepartmentName='财务部'
andIncome>2000
【试探与练习】查询研发部在1976以前诞生的雇员姓名及其薪水详情。
（2）聚合函数的利用。
新建一个查询，在查询分析器中输入如下语句并执行。
USEYGGL
GO
selectAVG（Income）as'财务部平均收入'
fromSalary
whereEmployeeIDin
（
selectEmployeeID
fromEmployees
whereDepartmentID=
（
selectDepartmentID
fromDepartments
whereDepartmentName='财务部'
）
）
【试探与练习】查询财务部雇员的最高和最低收入。
新建一个查询，在查询分析其中输入如下语句并执行。
USEYGGL
GO
selectavg（Income-Outcome）as'财务部平均实际收入'
fromSalary
whereEmployeeIDin
（
selectEmployeeID
fromEmployees
whereDepartmentID=
（selectDepartmentID
fromDepartments
whereDepartmentName='财务部'
）
）
【试探与练习】查询财务部雇员的最高和最低实际收入。
③求财务部雇员的总人数
selectCOUNT（EmployeeID）fromEmployees
whereDepartmentID=
（selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
）
【试探与练习】统计财务部收入在2500以上的雇员的人数。
（5）GROUPBY、ORDERBY子句的利用：
①查找Employees表中男性和女性的人数
selectSex,COUNT（Sex）
fromEmployees
groupbySex;
【试探与练习】
a．按部门列出在该部门工作的员工的人数。
b．按员工的学历分组，排列出本科、大专和硕士的人数。
②查找员工数超过2的部门名称和员工数量。
select,COUNT（*）as'人数'
fromEmployees,Departments
WHERE=
groupby
havingCOUNT（*）>2
【试探与练习】按员工的工作年份分组，统计各个工作年份的人数，例如，工作一年的多少人，工作两年的多少人。
3将各雇员的情形按收入由低到高排列。
selectEmployees.*,Salary.*
fromEmployees,Salary
where=
orderbyInCome
【试探与练习】
a．将员工的信息按诞生的时刻从小到大排列。
b.在orderby子句中利用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列。
视图的利用
1.目的和要求
（1）熟悉视图的概念和作用；
（2）把握视图的创建方式；
（3）把握如何让查询和修改视图。
2.实验预备
（1）了解视图的概念；
（2）了解创建视图的方式；
（3）了解并把握对视图的操作。
3.实验内容
（1）创建视图。
（2）①创建YGGL数据库上的视图DS_VIEW，视图包括Departments表的全数列。
CREATEVIEWDS_VIEW
ASSELECT*FROMDepartments
②创建YGGL数据库上的视图Employees-view，视图包括“员工号码”、“姓名”、“实际收入”三列。
利用如下SQL语句：
CREATEVIEWEmployees_view（EmployeeID，Name，realIncome）
AS
SELECT，，Name，Income-OutCome
FROMEmployees，salary
WHERE=
【试探与练习】
a．在创建视图时SELECT语句有那些限制？
b．再创建视图时有哪些注意点？
c．创建视图，包括员工号码、姓名、所在部门名称和实际收入这几列。
（2）查询视图：
①从视图DS_VIEW中查询出部门号为3的部门名称。
selectDepartmentNamefromDS_view
whereDepartmentID='3'
②从视图Employees_view中查询出姓名为“王林”的员工的实际收入
selectRealIncomefromEmployees_view
whereName='王林'
【试探与练习】
a．假设视图关联了某表中的所有字段，而现在刻表中添加了新的字段，视图中可否查询到该字段？
b．自己创建一个视图，并查询视图中的字段。
（3）更新视图。
在更新视图前需要了解可更新视图的概念，了解什么视图是不能够进行修改的。
更新视图真正更新的是和视图关联的表。
①向视图DS_VIEW中插入一行数据“6，广告部，广告业务”
insertintoDS_VIEWvalues（'6','广告部','广告业务'）
执行完该命令，利用SELECT语句别离查看视图DS_VIEW和大体表Departments中发生的转变。
尝试向视图Employees_view中插入一行数据看看会发生什么情形。
②修改视图DS_VIEW，将部门号为5的部门名称修改成“生产车间”
updateDS_VIEW
setDepartmentName='生产车间'
whereDepartmentID='5'
执行完该命令，利用SELECT语句别离查看视图DS_VIEW和大体表Departments中发生的转变。
③将视图Employees_view中员工号为“000001”的员工的姓名修改成“王浩”
updateEmployees_view
setName='王浩'
whereEmployeeID='000001'
④删除视图DS_VIEW中部门号为“1”的一行数据
delectfromDS_VIEW
whereDepartmentID='1'
【试探与练习】视图Employees_view中无法插入和删除数据，其中的realincome字段也无法修改，什么缘故？
（4）删除视图
删除视图DS_VIEW.
dropVIEWDS_VIEW
（5）在界面工具中操作视图
1创建视图：
启动SQLServerManagementStudio，在对象资源治理器中展开“数据库→YGGL”选择其中的视图项，右击鼠标，在弹出的快捷菜单上选择“新建视图”菜单项，在随后显现的“添加表”窗口中，添加所需关联的大体表。
在视图窗口中的关系图窗口显示基表的全数信息。
依照需要在窗口当选择创建视图所需的字段。
完成后单击“保留”按钮保留。
2查询视图:
新建一个查询，输入T-SQL查询命令即可像查询表一样查询视图。
3删除视图：
展开YGGL数据库→“视图”，选择要删除的视图，右击选择“删除”选项，确认即可。
4.试探与练习
总结视图与大体表的不同。
升级会员 