视图与数据查询 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
升级会员 