数据库的查询和视图.docx
《数据库的查询和视图.docx》由会员分享,可在线阅读,更多相关《数据库的查询和视图.docx(15页珍藏版)》请在冰点文库上搜索。
数据库的查询和视图
实验四数据库的查询和视图
T4.1数据库的查询
1.目的与要求
(1)掌握select语句的基本语法;
(2)掌握子查询的表示
(3)掌握连接查询的表示
(4)掌握select语句的groupby子句的作用和使用方法
(5)掌握select语句的orderby子句的作用和使用方法
2实验准备
(1)了解SELECT语句的基本语法格式;
(2)了解SELECT语句的执行方法;
(3)了解子查询的表示方法;
(4)了解连接查询的表示;
(5)了解SELECT语句的GROUPBY子句的作用和使用方法;
(6)了解SELECT语句的ORDER BY子句的作用;
3实验内容
SELECT语句的基本使用。
①对于实验2给出的数据库表结构,查询每个雇员的所有数据。
新建一个查询,在查询分析器中输入如下语句并执行:
USE YGGL
GO
SELECT*
FROMEmployees
【思考与练习】
用SELECT语句查询Departments和Salary表中所有的数据信息。
用SELECT语句查询Employees表中每个雇员的地址和电话。
【思考与练习】
a.用SELECT语句查询Deparments和Salary表的一列或若干列。
b.查询Employees表中的部门号和性别,要求使用DISTINCT消除重复行。
c.查询EmployeeID为000001的雇员的地址和电话。
【思考与练习】
a.查询月收入高于2000元的员工号码。
b.查询1970年以后出生的员工的姓名和住址。
c.查询所有财务部的员工的号码和姓名。
d.查询Employees表中女雇员的地址和电话,使用AS子句将结果中各列的标题分别指定为地址、电话。
【思考与练习】使用SELECT语句进行简单的计算。
⑦获得员工总数。
【思考与练习】
a.计算salary表中员工月收入的平均数。
b.获得Employees表中最大员工号码。
c.计算Salary表中所有员工的总支出。
d.查询财务部雇员的最高和最低实际收入。
USEYGGL
SELECTMAX(InCome-OutCome)AS最高实际收入,
MIN(InCome-OutCome)AS最低实际收入
FROMSalary
GO
⑧找出所有姓王的雇员的部门号。
USEYGGL
SELECTDepartmentID
FROMEmployees
WHERENameLIKE'王%'
Go
【思考与练习】
a.找出所有其地址中含有“中山”的雇员的号码及部门.
USEYGGL
SELECT,EmployeeID,DepartmentID
FROMEmployees
WHEREAddressLIKE‘中山’
GO
b.查找员工号码倒数第二个数字为0的员工的姓名、地址和学历。
USEYGGL
SELECTName,Address,Education
FROMEmployees
WHEREEmployeeID=LIKE’%0_’
GO
⑨找出所有收入在2000-3000元之间的员工号码。
(1)子查询的使用。
①查找在财务部工作的雇员的情况
USEYGGL
GO
select*
fromEmployees
whereDepartmentID=
(
selectDepartmentIDfromDepartments
whereDepartmentName='财务部'
)
【思考与练习】用子查询的方法查找所有收入在2500元以下的雇员的情况
②查找财务部年龄不低于研发部雇员年龄的雇员的姓名。
【思考与练习】查询每个雇员的情况及其工作部门的情况。
②使用内连接的方法查询名字为“王林”的员工所在的部门
selectDepartmentNamefromDepartments
JoinEmployeesonDepartments.DepartmentID=Employees.DepartmentID
whereEmployees.Name='王林'
【思考与练习】
a.使用内连接的方法查找出不在财务部工作的所有员工信息。
b.使用外连接方法查找出所有员工的月收入。
1查找财务部收入在2000元以上的雇员姓名及其薪水详情。
USEYGGL
【思考与练习】查询研发部在1976以前出生的雇员姓名及其薪水详情。
(2)聚合函数的使用。
1.求财务部雇员的平均收入
新建一个查询,在查询分析器中输入如下语句并执行。
USEYGGL
GO
selectAVG(Income)as'财务部平均收入'
fromSalary
whereEmployeeIDin
(
selectEmployeeID
fromEmployees
whereDepartmentID=
(
selectDepartmentID
fromDepartments
whereDepartmentName='财务部'
)
)
【思考与练习】
3.求财务部的平均实际收入
【思考与练习】
③求财务部雇员的总人数
(5)GROUPBY、ORDERBY子句的使用:
①查找Employees表中男性和女性的人数
selectSex,COUNT(Sex)
fromEmployees
groupbySex;
【思考与练习】
a.按部门列出在该部门工作的员工的人数。
USEYGGL
SELECTDepartmentID,COUNT(DepartmentID)AS'人数'
FROMEmployees
GROUPBYDepartmentID
b.按员工的学历分组,排列出本科、大专和硕士的人数。
USEYGGL
SELECTEducationAS'学历',COUNT(Education)AS'人数'
FROMEmployees
GROUPBYEducation
②查找员工数超过2的部门名称和员工数量。
【思考与练习】将各雇员的情况按收入由低到高排列。
USEYGGL
SELECT*
FROMEmployees
ORDERBYInCome
GO
T4.2视图的使用
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
SELECTEmployees.EmployeeID,Name,Income-OutCome
FROMEmployees,salary
WHEREEmployees.EmployeeID=salary.EmployeeID
【思考与练习】
a.在创建视图时SELECT语句有那些限制?
不能使用COMPUTE或者COMPUTE BY子句;除非和TOP子句一起使用,否则,不能使用ORDER BY子句;不能使用INTO关键字;不能使用OPTION子句;不能引用临时表和表变量。
b.再创建视图时有哪些注意点?
1.在CREATEVIEW语句中,不能包括ORDERBY,COMPUTE或者COMPUTEBY子句也不能出现INTO关键字2.创建视图所参考基表的列数最多为1024列3.创建视图不能参考临时表4.尽量避免使用外连接创建视图5.在一个批处理语句中,CREATEVIEW语句不能和其他TRANSACT-SQL语句混合使用
c.创建视图,包含员工号码、姓名、所在部门名称和实际收入这几列。
CREATEVIEW
Employees_View
(
EmployeeID,Name,DepartmentName,RealInCome
)
ASSELECTDepartments.DepartmentID,name,DepartmentName,InCome-OutComeasRealInCome
FROMEmployees,Departments,Salary
whereDepartments.DepartmentID=Employees.DepartmentID
andEmployees.EmployeeID=Salary.EmployeeID
(2)查询视图:
①从视图DS_VIEW中查询出部门号为3的部门名称。
selectDepartmentNamefromDS_view
whereDepartmentID='3'
②从视图Employees_view中查询出姓名为“王林”的员工的实际收入
selectRealIncomefromEmployees_view
whereName='王林'
【思考与练习】
a.若视图关联了某表中的所有字段,而此时刻表中添加了新的字段,视图中能否查询到该字段?
不能,必须重新创建视图才能查询到新字段。
b.自己创建一个视图,并查询视图中的字段。
SELECT*
FROMEmployees_View
WHEREEmployeeID=1
(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字段也无法修改,为什么?
因为视图employees-view中的字段realincome是基本表列通过计算所得的列,所以无法修改。
(4)删除视图
删除视图DS_VIEW.
dropVIEWDS_VIEW
(5)在界面工具中操作视图
1创建视图:
启动SQLServerManagementStudio,在对象资源管理器中展开“数据库→YGGL”选择其中的视图项,右击鼠标,在弹出的快捷菜单上选择“新建视图”菜单项,在随后出现的“添加表”窗口中,添加所需关联的基本表。
在视图窗口中的关系图窗口显示基表的全部信息。
根据需要在窗口中选择创建视图所需的字段。
完成后单击“保存”按钮保存。
2查询视图:
新建一个查询,输入T-SQL查询命令即可像查询表一样查询视图。
3删除视图:
展开YGGL数据库→“视图”,选择要删除的视图,右击选择“删除”选项,确认即可。
4.思考与练习
总结视图与基本表的差别。
1、视图是已经编译好的sql语句;而表不是。
2、视图没有实际的物理记录;而表有。
3、视图是窗口;表是内容4、标致用物理空间而视图不占用物理控件,视图只是逻辑概念的存在;表可以即使对它修改,但视图只能有创建的语句来修改。
5、表示内模式,视图是外模式。
6、视图是产看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些sql语句的集合。
从安全的角度来说,视图可以不给用户接触数据表,从而不知道表结构。
7、表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表。
8、视图的建立和删除只影响视图本身,不影响对应的基本表。