视图与数据查询 SQL基本语句范文.docx
- 文档编号:11764275
- 上传时间:2023-04-01
- 格式:DOCX
- 页数:16
- 大小:323.44KB
视图与数据查询 SQL基本语句范文.docx
《视图与数据查询 SQL基本语句范文.docx》由会员分享,可在线阅读,更多相关《视图与数据查询 SQL基本语句范文.docx(16页珍藏版)》请在冰豆网上搜索。
视图与数据查询SQL基本语句范文
实验四视图与数据查询
一、实验目的:
熟悉SQLSERVER2000系统开发环境及图形化操作,进一步了解各部分的操作
掌握虚拟数据表的原理即实现方法
掌握数据查询技术的构成要素及实现方法
进一步熟悉企业管理器的操作方法,对比掌握数据表与视图
二、实验原理:
(一)查询的创建
所谓查询就是根据我们的要求,在一张或多张数据表中找出满足一定条件的我们所关注的数据,排除不需要或不关心的信息。
查询的实现方法:
在企业管理器中右击你想查看的表,选择“打开表”菜单项下面的“查询”命令,打开“查询设计器”窗口。
1、查询设计器
(1)关系图窗格。
此区域以图形方式显示表或视图等对象以及它们之间的连接关系。
(2)网格窗格。
用户可以在此区域中指定查询选项,例如要显示哪些数据列、如何对结果进行排序以及选择哪些行等。
(3)SQL窗格。
显示查询或视图对应的SELECT语句。
(4)结果窗格。
显示满足查询条件的数据。
在查询设计器中,该区域显示最近执行的选择查询的结果。
可以通过编辑该网格单元中的值对数据进行修改,而且可以添加或删除数据。
2、设置查询条件
(1)设置显示列。
显示列就是在输出结果中满足条件的记录所显示的列信息。
在网格窗格中,显示列的“输出”栏被标识为√。
如果你查询的数据来至于多张数据表,在“关系图窗格”中单击鼠标右键,选择添加表,将要查询的数据表添加进去。
(2)设置查询条件。
在网格窗格中,单击空白行的“列”栏,从菜单中选择查询条件中的列。
例如要查询所属部门编号等于2的记录,则选择Dep_id,然后在“准则”中输入=2。
注意,将Dep_id所在行中的输出标记去掉,因为结果集中不需要显示所属部门编号。
条件可以设置多个,相与的条件放在同一列,相或的条件放在不同列,假如查询部门编号为2的姓陈的人,则在设置好上述条件后,在姓名后面的“准则”栏写上like‘陈%’。
(3)设置排列顺序。
设置结果集中的数据按照指定列的升序或降序排列,注意排序可以设置多种规则,比如第一排序按部门,则按部门的拼音顺序排列,如果一个部门的人很多,可以设置第二排序规则,比如按职工号排序。
(4)运行查询。
设置完成后,单击工具栏中的“运行”按钮,运行查询。
3、带有统计功能的查询
当查询的数据需要数据表中的多行数据统计计算得来的时候,在查询中将使用统计功能,单击工具栏上的“使用groupby”,在“网格窗格”中将增加一列“分组”,如下图所示,在需要计算的列后面点击下拉框,选择计算的函数即可。
下图是查询每位同学的总成绩。
统计也可以带查询条件,如果一般的根据数据直接给条件,可以在后面准则中设置,例如:
查询铁运专业的所有学生的总成绩如下图:
但是如果查询的条件也带有统计功能,这时可以在空白行中设置统计列,但是不输出,在准则中设置查询条件即可,例如:
查询至少选修了2门以上课程的学生的总成绩,则在下面添加统计行,如下图所示。
这里需要说明,查询不能保存,如果我们希望把建立的查询保存起来,则需要用视图来创建。
(二)视图的创建
视图可以被看成是虚拟表或存储查询。
可通过视图访问的数据不作为独特的对象存储在数据库内。
数据库内存储的是SELECT语句。
SELECT语句的结果集构成视图所返回的虚拟表。
用户可以用引用表时所使用的方法,在Transact-SQL语句中通过引用视图名称来使用虚拟表。
使用视图可以实现下列任一或所有功能:
(1)将用户限定在表中的特定行上。
例如,只允许雇员看见工作跟踪表内记录其工作的行。
(2)将用户限定在特定列上。
例如,对于那些不负责处理工资单的雇员,只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列,而不能看见任何包含工资信息或个人信息的列。
(3)将多个表中的列联接起来,使它们看起来象一个表。
(4)聚合信息而非提供详细信息。
例如,显示一个列的和,或列的最大值和最小值。
也就是说视图就是可以保存的查询,故查询设计器与视图设计器一样,只是在视图设计页面中增加了保存功能。
视图的创建,在企业管理器中,打开数据库文件夹,在打开自己需要建立视图的数据库,鼠标右键单击视图,选择新建,进入视图设计器,如下图所示,这里就不在赘述。
(三)视图的修改
在企业管理器中,右击需要修改的视图,选择“设计视图”命令,就可以打开视图设计器,可以对设计的视图进行修改。
在“关系图网格”中,右击空白区域,在菜单中选择“属性”命令,打开视图属性对话框,如下图所示。
在视图属性对话框中,选中“加密浏览”复选框。
选中后,将弹出一个对话框,提示用户是否确定对视图加密。
加密过程是不可逆转的,对视图加密后,因为再也看不到视图定义,所以永远不能再对视图进行修改。
如果需要修改加密视图,则必须删除该视图并重新创建一个。
如果对加密的视图打开“视图设计器”,则会弹出一个对话框,提示用户不能打开加密的视图。
(四)视图的删除
在企业管理器中,右击要删除的视图,在弹出菜单中选择“删除”,打开“除去对象”对话框。
单击“全部除去”按钮,可以将视图删除。
如果不希望删除视图,可以单击“取消”按钮。
(五)视图的使用
视图如同一张数据表,可以鼠标点击右键,选择打开视图,查看视图中的数据,可以接受数据表的所有操作,即:
查询数据、添加数据、修改数据、删除数据,因此,在SQL数据库中,视图与数据表属于同一类对象,不能同名。
三、实验内容
在企业管理器中,打开实验三的数据库,练习建立如下十个查询,并建立如下十个查询的视图。
(1)查询score表中成绩在60到80之间的所有记录。
(2)查询student表中所有姓王的学生记录。
(3)查询score表中成绩为85,86或88的记录。
(4)查询student表中所有男生记录,并以class降序排列。
(5)查询student表中“04031”班的学生人数。
(6)查询score表中成绩为“3-105”号课程的平均成绩。
(7)查询所有学生的sname,cname和degree.
(8)查询成绩比该课程平均成绩低的学生成绩。
(9)查询04031班至少选修了两门课的学生的姓名。
(10)查询每位教室所授课程的平均成绩,包括教师编号,姓名,课程代码,课程名称,平均成绩。
按成绩降序排列
四、课后作业
1、练习视图的创建与加密。
2、测试通过视图向数据表添加、修改数据,总结在什么时候不能进行数据的修改、添加和删除。
3、总结数据表与视图的区别与联系。
4、总结视图与查询的区别与联系。
5、进一步熟悉数据库的备份、恢复和转换。
SQL基本语句
1.题目
假定表employees和表department包含有表A.4和表A.5所示的数据
表A.4employees表中所包含的数据
empid
name
department
memo
birthdate
salary
0001
王丽
1
打字员
1980-3-12
1000
0002
李红
4
副经理
1964-8-25
4000
0003
王亮
2
1200
0004
张小阳
1
2100
0005
刘大伟
3
1200
0006
赵思佳
3
2000
0007
李福祥
2
3000
0008
王晓思
4
经理
1970-1-12
5000
表A.5departments表中所包含的数据
depid
depname
memo
depid
depname
memo
1
开发部
3
销售部
2
集成部
4
服务部
按照要求写出相应的T—SQL语句。
2操作过程
过程如下:
查询employees表中的所有数据。
语句如下:
SELECT*FROMemployees
查询employees表中的所有数据,并将名标题行用汉语表示。
语句如下:
SELECTempidAS员工编号,nameAS姓名,departmentAS部门编号,memoAS备注,birthdateAS出生日期,salaryAS工资FROMemployees
查询departments表中的所有数据。
语句如下:
SELECT*FROMdepartments
查询employees表中的所有数据,并用departments表中的部门名称来取代其编号显示出来。
语句如下:
SELECTemployees.empid,employees.name,departments.depname,employees.birthdate,employees.salary
FROMdepartmentsINNERJOINemployees
ONdepartments.depid=employees.department
按部门编号的顺序查询employees表中的所有数据,并用departments表中的部门名称来取代其编号显示出来。
语句如下:
SELECTemployees.empid,employees.name,departments.depname,employees.birthdate,employees.salary
FROMdepartmentsINNERJOINemployees
ONdepartments.depid=employees.department
ORDERBYemployees.department
查询employees表中工作超过2000的职工。
语句如下:
SELECT*FROMemployeesWHEREsalary>2000
查询employees表中工资超过1500,并且属于开发部的人员,并用departments表中的部门名称来取代其编号显示出来,语句如下:
SELECTemployees.empid,employees.name,departments.depname,employees.birthdate,employees.salary
FROMdepartmentsINNERJOINemployees
ONdepartments.depid=employees.department
WHEREdepartments.depname='开发部'ANDemployees.salary>1500
查询employees表中姓王的职工的所有数据。
语句如下:
SELECT*FROMemployeesWHEREnameLIKE'王%'
查询employees表中姓名只含有两个字的职工。
语句如下:
SELECT*FROMemployeesWHEREnameLIKE'__'
找出employees表中所有20世纪60年代出生的职工。
语句如下:
SELECT*FROMemployeesWHEREyear(birthdate)LIKE'196[0-9]'
找出employees表中所有未添写出生日期的职工。
语句如下:
SELECT*FROMemployeesWHEREbirthdateISNULL
查询所有开发部员工的工资总额。
语句如下:
SELECTsum(employees.salary)as工资和,departments.depnameas部门
fromdepartmentsinnerjoin
employeesondepartments.depid=employees.department
Where(departments.depname='开发部')
Groupbydepartments.depname
查询所有不属于开发部的员工的基本信息。
语句如下:
Selecte.empid,e.name,e.birthdate,
e.salary,departments.depnameas部门
fromdepartmentsinnerjoin
employeesaseondepartments.depid=e.department
wherenot(departments.depname='开发部')
显示工资最高的三位员工的信息。
语句如下:
Selecttop3employees.*
Fromemployees
Orderbyemployees.salarydesc
在所有填写了出生日期的数据中,找出年龄最大的三位员工的信息。
语句如下:
Selecttop3employees.*
Fromemployees
Wherebirthdateisnotnull
Orderbybirthdate
找出所有工资介于2000到3000之间的职工,语句如下:
SELECT*FROMemployeesWHEREsalary>=2000ANDsalary<=3000
找出所有在memo字段中含有“经理”两字的员工。
语句如下:
SELECT*FROMemployeesWHEREmemoLIKE'%经理%'
找出所有员工中工资最高的、最低的及平均工资,语句如下:
SELECTMAX(salary)AS'最高工资',MIX(salary)AS'最低工资',AVG(salary)AS'平均工资'
FROMemployees
统计employees表中各部门的人数。
语句如下:
SELECTdepartments.depnameAS'部门',count(*)as'人数'
fromdepartmentsinnerjoin
employeesondepartments.depid=employees.department
Groupbydepartments.depname
查询工资高于3000的员工有多少人。
语句如下:
SELECTcount(*)as'人数'
FROMemployees
WHEREsalary>3000
查询各部门的平均工资,并将结果排序。
语句如下:
SELECTdepartments.depname,avg(salary)
FROMemployees
INNERJOINdepartmentsONemployees.department=departments.depid
GROUPBYdepartments.depnameORDERBYavg(salary)
向employees表中添加一条数据。
语句如下:
INSERTINTOemployees
VALUES('0010','赵月',4,NULL,'1980-12-10',NULL)
将所有“工资”一项为空的职员,工资定为600。
语句如下:
UPDATEemployees
SETsalary=600
WHEREsalaryISNULL
将所有工资低于1500的职员涨200。
语句如下:
UPDATEemployees
SETsalary=salary+200
WHEREsalary<1500
将所有开发部的职员工资上调10%。
语句如下:
UPDATEemployeesSETsalary=salary*1.1
FROMemployeesINNERJOINdepartments
ONemployees.department=departments.depid
WHEREdepartments.depname='开发部'
删除所有年龄大于50的员工的数据。
语句如下:
DELETEFROMemployees
WHEREyear(getdate())-year(birthdate)>50
删除属于销售部的所有员工。
语句如下:
DELETEemployees
FROMemployeesINNERJOINdepartments
ONemployees.department=departments.depid
WHEREdepartments.depname='销售部'
删除所有员工的数据。
语句如下:
TRUNCATETABLEemployees
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 视图与数据查询 SQL基本语句范文 视图 数据 查询 SQL 基本 语句 范文