《sql查询与视图》由会员分享，可在线阅读，更多相关《sql查询与视图（33页珍藏版）》请在金锄头文库上搜索。
1、回顾qT-SQL语言基础q 1.数据类型和常量q 2.局部变量和全局变量q 3.运算和表达式q 4.流程控制语句q 5.系统函数第十三讲查询和视图1 T-SQL高级查询 2 视图 3 小结数据查询与视图1 T-SQL高级查询1.1 连接查询1.2 使用分组1.3 合并结果集1.4 汇总计算1.5 子查询连接类型：内连接、外连接、自连接、交叉连接1.1 连接查询 1内连接(自然连接)FROM 表1 inner JOIN 表2 ON 条件表达式1从学生信息表和教学成绩表中查询学生的学号、姓名、课程 编号和分数use 教学成绩管理数据库select 教学成绩表.学号,姓名,课程编号,分数from 教
2、学成绩表,学生信息表where 教学成绩表.学号 = 学生信息表.学号为数据表指定别名从学生信息表、教学成绩表和课程信息表中查 询学生的学号、姓名、课程名称和分数use 教学成绩管理数据库select cj.学号,姓名,kc.名称,分数from 教学成绩表 as cj inner join 学生信息表 as xs on cj.学号 = xs.学号 inner join 课程信息表 as kc on cj.课程编号 = kc.编号1.1 连接查询外连接 (1)左连接：FROM 表1 LEFT OUTER JOIN 表2 ON 条件表达式 从学生信息表、教学成绩表和课程信息表中查询 查询学生的学号
3、、姓名、课程名称和分数use 教学成绩管理数据库select xs.学号,姓名,kc.名称,分数from 学生信息表 as xs left join 教学成绩表 as cj on cj.学号 = xs.学号 left join 课程信息表 as kc on cj.课程编号 = kc.编号(2)右外连接： FROM 表1 RIGHT OUTER JOIN 表2 ON 条件表达式从学生信息表、教学成绩表和课程信息表中查询查 询学生的学号、姓名、课程名称和分数use 教学成绩管理数据库select xs.学号, 姓名, kc.名称, 分数from 教学成绩表 as cj join 学生信息表 as
4、xs on cj.学号 = xs.学号 right join 课程信息表 as kc on cj.课程编号 = kc.编号(3)全外连接：FROM 表1 FULL OUTER JOIN 表2 ON 条件表达式 从学生信息表和教师信息表中查询同姓的教师和学生 的姓名 use 教学成绩管理数据库select left(xs.姓名,1) as 姓氏,xs.姓名 as 学生姓名 ,js.姓名 as 教师姓名from 教师信息表 as js full join 学生信息表 as xs on left(js.姓名,1) = left(xs.姓名,1)1.1 连接查询 3自连接在同一张表内进行自身连接 (必
5、须为表指定两个别名 ) 从学生信息表中查询同名学生的姓名和学号 use 教学成绩管理数据库select xs1.姓名,xs1.学号 ,xs2.学号from 学生信息表 as xs1 join 学生信息表 as xs2 on xs1.姓名 = xs2.姓名where xs1.学号70 and 班级编号=200301 order by avg(分数) desc 查询语句 UNION ALL 查询语句1.3 合并结果集 注意：(1)联合查询是将两个表(结果集)顺序连接。(2)UNION中的每一个查询所涉及的列必须具有相同的列数、相同位置 的列数据类型要相同。若长度不同，以最长的字段作为输出字段 的长
6、度。(3)结果集中的列名来自第一个SELECT语句。(4)最后一个SELECT查询可以带ORDER BY子句,对整个UNION 操作结果 集起作用。且只能用第一个SELECT查询中的字段作排序列。(5)系统自动删除结果集中重复的记录，除非使用ALL关键字。查询所有学生和教师的姓名、性别use 教学成绩管理数据库select 姓名,性别from 学生信息表unionselect 姓名,性别from 教师信息表COMPUTE 行聚合函数名(统计表达式) ,.nBY 分类表达式 ,.n 1.4 汇总计算函数描述 AVG平均值 COUNT行数MAX最高值 MIN最低值 SUM和 STDEV统计标准偏差
7、 STDEVP填充统计标准偏差 VAR统计方差 VARP填充统计方差use 教学成绩管理数据库select 学生信息表.学号,姓名,名称,分数from 学生信息表,教学成绩表,课程信息表where 学生信息表.学号 = 教学成绩表.学号and 课程信息表.编号 = 教学成绩表.课程编号 and 教学成绩表. 课程编号 = 900013 compute max(分数), min(分数), avg(分数)在教学成绩管理数据库中查询课程编号为900013的 学生学号、姓名、课程名称和分数，并计算出最高分 、最低分和均分子查询：在SELECT语句的WHERE子句中嵌套另一条SELECT语句 。1.5
8、子查询嵌套子查询的执行不依赖于外部嵌套。嵌套子查询的执 行过程为：首先执行子查询，子查询得到的结果集不 被显示出来，而是传给外部查询，作为外部查询的条 件使用，然后执行外部查询，并显示查询结果。子查 询可以多层嵌套。相关子查询是指在子查询中, 子查询的查询条件中引用了 外层查询表中的属性值。相关子查询中，首先选取外 层查询表中的第一行记录，内层的子查询则利用此行 中相关的属性值进行查询，然后外层查询根据子查询 返回的结果判断此行是否满足查询条件。如果满足条 件，则把该行放入外层查询结果集合中。重复这一过 程的执行，直到处理完外层查询表中的每一行数据。 3.5 子查询(1)返回单个值查询学习杜老
9、师所授课程的学生的学号、姓名、该课程 名称及分数1嵌套子查询use 教学成绩管理数据库select 学生信息表.学号,姓名,名称,分数from 学生信息表,教学成绩表,课程信息表where 学生信息表.学号 = 教学成绩表.学号and 课程信息表.编号 = 教学成绩表.课程编号 and 教师编号 = (select 编号 from 教师信息表 where 姓名=杜老师)(2)返回一个值列表查询200303班没有学习900011课程的学生的学号、姓名2相关子查询查询没有学习任何课程的学生的学号、姓名use 教学成绩管理数据库select 学号,姓名from 学生信息表where 班级编号=200
10、303 and学号 not in (select 学号 from 教学成绩表 where 课程编号=900011)use 教学成绩管理数据库select 学号,姓名from 学生信息表where not exists (select * from 教学成绩表 where 教学成绩表.学号=学生信息表.学号)2 视图2.1 视图的概念2.2 创建视图2.3 修改视图2.4 查询视图2.5 通过视图更新数据2.6 删除视图2.7 使用视图的优点及注意事项2.1 视图的概念 视图是由一个或多个数据表(基本表)或视图导出的 虚拟表或查询表，是关系数据库系统提供给用 户以多种角度观察数据库中数据的重要机
11、制。 视图是虚表。所谓的虚表，视图不是表。视图只储 存了它的定义(select语句)，而没有储存视图 对应的数据，这些数据仍存放在原来的数据表( 基表)，数据与基表中数据同步，即对视图的数 据进行操作时，系统根据视图的定义去操作与 视图相关联的基本表。所谓的虚表，视图又像 表,可以像基本表一样进行数据操作：查询、修 改、删除和更新数据。2.2 创建视图 1用企业管理器创建视图(1)打开企业管理器，展开数据库，右键单击教学成绩管 理数据库，在弹出的快捷菜单上选择【新建】、【视图 】。2)在出现的窗口的第一个窗格中单击鼠标右键，在弹出的 快捷菜单中选【添加表】。(3)在添加表对话框中，选择与视图相
12、关联的表、视图或 函数，单击【添加】按钮。 (4)在第二个窗格中选择创建视图所需的字段。 (5)单击【保存】按钮，输入视图名，单击【确定】按钮 。4.2 创建视图 2用T-SQL创建视图CREATE VIEW 视图名(列名1 ,n)AS查询语句注意：(1) 定义视图的用户必须对所参照的表或视图有查询权限 ，即可执行SELECT语句。 (2) 不能使用COMPUTE或COMPUTE BY子句。 (3) 不能使用ORDER BY子句。 (4) 不能使用INTO子句。 (5) 不能在临时表或表变量上创建视图。创建电子商务班学生 信息表视图use 教学成绩管理数据库gocreate view 电子商务
13、班学生信息表 asSELECT 学号, 姓名, 性别, 班级编号FROM 学生信息表where 班级编号=2003012.3 修改视图 使用ALTER VIEW命令ALTER VIEW 视图名(列名1 ,n)AS 查询语句 修改电子商务班学生信息表视图，增加联系电话use 教学成绩管理数据库goalter view 电子商务班学生信息表 asSELECT 学号, 姓名, 性别, 联系电话, 班级编号FROM 学生信息表where 班级编号=2003012.4 查询视图 创建视图后，就可以像查询基本表那样对视图进行查询 。查询电子商务班学生信息表女生的学号, 姓名, 性别, 联系电话 use 教
14、学成绩管理数据库SELECT 学号, 姓名, 性别, 联系电话FROM 电子商务班学生信息表where 性别=女2.5 通过视图更新数据 通过视图更新数据包括插入、修改和删除数据1插入数据 使用INSERT命令通过视图电子商务班学生信息表插入学号为110099、 姓名为花和尚、联系电话为0351-6339999的男生use 教学成绩管理数据库select * from 电子商务班学生信息表insert 电子商务班学生信息表(学号, 姓名, 性别, 联系电话, 班级编号) values(110099,花和尚,男,0351-6339999,200301) select * from 电子商务班学生
15、信息表2修改数据 使 用UPDATE命令通过视图电子商 务班学生信息 表修改学号为 110099的学生 的姓名、性别3删除数据 使用 DELETE命令通过视图电子商务 班学生信息表删 除学号为110099的 学生use 教学成绩管理数据库select * from 电子商务班学生信息表update 电子商务班学生信息表 set 姓名=扈三娘, 性别=女where 学号=110099select * from 电子商务班学生信息表use 教学成绩管理数据库select * from 电子商务班学生信息表delete 电子商务班学生信息表 where 学号=110099select * from
16、电子商务班学生信息表2.6 删除视图 使用DROP VIEW命令DROP VIEW 视图名,n删除视图电子商务班学生信息表 use 教学成绩管理数据库drop view 电子商务班学生信息表 2.7使用视图的优点及注意事项 优点：(1)为用户集中数据，简化用户的数据查询和处理。(2)简化用户权限的管理，也增加了安全性。(3)屏蔽数据库的复杂性。(4)便于数据共享。(5)可以重新组织数据以便输出到其他应用程序中。4.7 使用视图的优点及注意事项 注意事项：(1)只有在当前数据库中才能创建视图。(2)视图的命名必须遵循标识符命名规则，不能与表同名 ，且对每个用户视图名必须是惟一的，即对不同用户， 即使是定义相同的视图，也必须使用不同的名字。(3)不能把规则、默认值或触发器与视图相关联。(4)不能在
