第4章-数据库的查询和视图.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,第4章 数据库的查询和视图,01,选择、投影和连接,02,数据库的查询,03,数据库视图,1,选择、投影和连接,主要内容,选择,投影,连接,01,0,2,0,3,选择,学号,姓名,性别,平均成绩,104215,王敏,男,74,104211,李晓林,女,82,104210,胡小平,男,88,选择性别为女且平均成绩在,80,分以上的行,学号,姓名,性别,平均成绩,104211,李晓林,女,82,学生表,选择后的结果,查询满足条件的,行,投影,学号,姓名,性别,平均成绩,104215,王敏,男,74,104211,李晓林,女,82,104210,胡小平,男,88,对“学号”和“平均成绩”投影,学号,平均成绩,104215,74,104211,82,104210,88,学生表,选择后的结果,查询满足条件的,列,连接,T1,T2,1,A,6,F,2,B,T1,T2,T3,T4,T5,1,A,1,3,M,2,B,2,0,N,A,表,T3,T4,T5,1,3,M,2,0,N,B,表,T1=T3,等值连接,连接后的表,等值连接,T1,T2,T3,10,A1,B1,5,A1,C2,20,D2,C2,T1,T2,T3,T4,T5,T6,5,A1,C2,10,A2,C2,20,D2,C2,0,A2,D1,A,表,T1,T4,T5,T6,1,100,A1,D1,100,2,B2,C1,20,0,A2,D1,5,10,A2,C2,B,表,自然连接,连接后的表,自然连接,2,数据库的查询,主要内容,选择列,选择行,连接,统计,排序,01,02,03,04,05,为列指定别名,不使用列别名,使用列别名,看下面这两种查询结果，哪个看着比较顺眼呢？,看下面这个例子,检索,emp,表员工的编号、姓名和年基本工资、日基本工资信息。,分析：,年基本工资,查询语句：,select empno,ename,sal*12,sal/30 from emp;,sal*12,日基本工资信息,sal/30,使用列别名,列名,AS,列别名,使用列别名,列名,AS,列别名,列名,列别名,ename as,员工姓名,empno,员工编号,1.,检索,emp,表员工的编号、姓名和年基本工资、日基本工资信息，并为查询结果设置列别名,select empno as,员工编号,ename as,员工名称,sal*12 as,年基本工资,sal/30 as,日基本工资,from emp;,2.,检索,emp,表的指定列（,empno,、,ename,、,job,），不使用任何关键字而直接为这些列指定中文的别名,select empno,员工编号,ename,员工名称,job,职务,from emp;,如果别名中包含了特殊字符，或想让别名原样显示，就要使用,双引号,把别名括起来。,“试一试”中的第一个查询，如果这样写：,select empno as,员工编号,ename as,员工名称,sal*12 as,年,-,基本工资,sal/30 as,日基本工资,from emp;,分析,列别名：年,-,基本工资，包含特殊符号“,-,”，而且别名没有用双引号括起来,注意使用双引号,排除重复行,查询结果中包含重复行,查询结果中排除重复行,查询结果中包含重复行,默认情况下，查询结果会包含所有满足条件的结果，其中可能会包含重复行。,在,SCOTT,模式下，显示,emp,表中的,job,（职务）列,查询结果中排除重复行,为了排除重复行，需要使用,DISTINCT,关键字,在,SCOTT,模式下，显示,emp,表中的,job,（职务）列，要求显示的“职务”记录不重复,select distinct job from emp;,distinct,的作用是消除重复内容，但是所谓的消除重复的内容，是指一条完整的数据全部是重复的，如果多行记录只有一列重复而其他列不重复，那么也是无法消除的。,查询,emp,表的员工编号和职位,当查询比较大的表时应尽可能地避免使用,DISTINCT,关键字,带有表达式的,SELECT,子句,在使用,SELECT,语句时，对于数字数据和日期数据都可以使用算术表达式。在,SELECT,语句中可以使用算术运算符，包括（,+,）、减（,-,）、乘（*）、除（,/,）和括号。,检索,emp,表的,sal,列，把其值调整为原来的,1.1,倍,select ename,sal,sal*(1+0.1)from emp;,算术运算符是有,优先级,先乘除后加减。,在表达式中同一优先级的运算符计算次序是从左到右。,如果使用了括号，括号中的运算优先。,如果有多重括号嵌套，内存括号中的运算优先。,查询所有列,查询单个表中所有列,查询多个表中所有列,select distinct|*|,列名称,AS,列别名,，列名称,AS,列别名,from,表名称,表别名,;,查询单个表中所有列,要查询表中所有列,在,SELECT,子句后面使用星号（*）来实现,在,SCOTT,模式下，在,SELECT,语句中使用星号（*）来检索,dept,表中所有的数据,第一步：,连接,SCOTT,模式,connect scott/tiger,第二步：,查询,dept,表中所有的数据,select*from dept;,;,如果在,SYSTEM,模式中，查询,emp,表，要如何进行查询呢？,想一想,如果这样做：,select*from emp;,应该这样做：,select*from soctt.emp;,查询多个表中所有列,要查询多个表中所有列,在,FROM,子句后面指定多个数据表，用逗号隔开,在,SCOTT,模式下，同时查询,dept,和,salgrade,表中的所有数据,分析：,要想“同时查询,dept,和,salgrade,表中的所有数据”,就在,from,子句中指定两个数据表,dept,和,salgrade,查询语句：,select*from dept,salgrade,;,查询特定列,查询特定列,伪列,如果想查找单个数据怎么办？,买 雪地鞋，手套,从 百货大楼,查询特定列,SELECT column_name1,column_name2,column_name3,column_name,FROM,表名称,;,SELECT,子句后面可以加想要查询的列名，用“，”隔开,语法,在,SCOTT,模式下，检索,emp,表中指定的列（,job,、,ename,、,empno,）,查询语句,select,j,ob、ename、empno from,emp,;,分析,用,SELECT,语句来查询,job、ename、empno,，只要在,SELECT,后面写,j,ob、ename、empno,就可以啦,伪列,一种数据类型，唯一标识一条记录，物理位置的一个,id,，基于,64,位编码的,18,个字符。,定义,它并不是真实的存在于数据表中的列，所以被称为,伪列,。,伪列可以从表中查询，但是不能插入、更新或删除。,伪列的用途,1.,能以最快的方式访问表中的一行,2.,能显示表的行是如何存储的,3.,作为表中唯一标识,常用的伪列,rowid,和,rownum,数据库中的每一行都有一个行地址，,rowid,伪列返回该行地址。可以使用,rowid,值来定位表中的一行，通常情况下，,rowid,值可以唯一地标识数据库的一行。,rowid,对于一个查询返回的每一行，,rownum,伪列返回一个数值代表的次序。返回第一行的,rownum,值为,1,，第二行的,rownum,值为,2,，以此类推。通过使用,rownum,伪列，用户可以限制查询返回的行数,rownum,1.,查询,emp,表的,rowid,列,select,rowid,from,emp,;,2.,从,emp,表中查询前,5,条数据,select,*,from,emp where rownum=|!=,expression,查询emp表中工资（sal）大于1500的数据记录,SQL select empno,ename,sal from emp where sal 1500;,选择行,2模式匹配,LIKE谓词表达式的格式为：,string_expression NOT LIKE string_expressionESCAPE,escape_character,LIKE,谓词,LIKE运算符可以使用以下两个通配符“%”和“_”。其中：,“%”：代表0个或多个字符。,“_”：代表一个且只能是一个字符。,选择行,3范围比较,用于范围比较的关键字,BETWEEN关键字,IN关键字,选择行,4空值比较,空值（NULL）从技术上来说就是,未知的、不确定的,值，但空值与空字符串不同，因为,空值是不存在的值，而空字符串是长度为0的字符串,。,SQL select empno,ename,sal,comm from emp where comm is null;,查询emp表中没有奖金的员工信息,选择行,5子查询,（1）单行子查询,单行子查询是指返回一行数据的子查询语句。当在WHERE子句中引用单行子查询时，可以使用单行比较运算符（=、=、）。,SQL select empno,ename,sal from emp,where sal (select min(sal)from emp),and sal select empno,ename,job,from emp where deptno in,(select deptno from dept where dnameSALES);,在emp表中，查询不是销售部门（SALES）的员工信息,选择行,（3）关联子查询,在一些特殊需求的子查询中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。,SQL select empno,ename,sal,from emp f,where sal (select avg(sal)from emp where job=f.job),order by job;,在emp表中，使用“关联子查询”检索工资大于同职位的平均工资的员工信息,连接,使用简短的,表别名,就可以,替代,原有,较长的表名称,，这样就可以大大缩减语句的长度。,SQL select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门,from emp e,dept d,where e.deptno=d.deptno,and e.job=MANAGER;,1表别名,通过DEPTNO（部门号）列来关联emp表和dept表，并检索这两个表中相关字段的信息,。,连接,内连接,是一种常用的,多表关联查询,方式，一般使用关键字,INNER JOIN,来实现。其中，INNER关键字可以省略，当只使用JOIN关键字时，语句只表示内连接操作。,SQL select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门,from emp e inner join dept d,on e.deptno=d.deptno;,2内连接,通过deptno字段来内连接emp表和dept表，并检索这两个表中相关字段的信息,。,连接,3外连接,外连接通常有以下三种：,左外连接,：关键字为LEFT OUTER JOIN或LEFT JOIN。,右外连接,：关键字为RIGHT OUTER JOIN 或RIGHT JOIN。,完全外连接,：关键字为FULL OUTER JOIN或FULL JOIN。,连接,左外连接的查询结果中不仅包含了,满足连接条件的数据行,，而且还包含,左表中不满足连接条件的数据行,。,SQL insert into emp(empno,ename,job)values(9527,EAST,SALESMAN);,SQL select e.empno,e.ename,e.job,d.deptno,d.dname,from emp e left join dept d,on e.deptno=d.deptno;,（1）左外连接,首先使用insert语句在emp表中插入新记录（注意没有为deptno和dname列插入值，即它们的值为null），然后实现emp表和dept表之间通过deptno列进行左外连接,。,连接,同样道理，右外连接的查询结果中不仅包含了,满足连接条件的数据行,，而且还包含,右表中不满足连接条件的数据行,。,SQL select e.empno,e.ename,e.job,d.deptno,d.dname,from emp e right join dept d,on e.deptno=d.deptno;,（2）右外连接,实现emp表和dept表之间通过deptno列进行右外连接,。,连接,在执行完全外连接时，Oracle会执行一个,完整的左外连接和右外连接查询,，然后将,查询结果合并,，并,消除重复的记录行,。,SQL select e.empno,e.ename,e.job,d.deptno,d.dname,from emp e full join dept d,on e.deptno=d.deptno;,（3）完全外连接,实现emp表和dept表之间通过deptno列进行完全外连接,。,连接,自然连接和内连接的功能相似，自然连接是指在检索多个表时，Oracle会将,第一个表中的列与第二个表中具有相同名称的列,进行自动连接。在自然连接中，用户不需要明确指定进行连接的列，这个任务由Oracle系统自动完成，自然连接使用,“,NATURAL JOIN,”,关键字。,SQL select empno,ename,job,dname,from emp natural join dept,where sal 2000;,4自然连接,在emp表中检索工资（sal字段）大于2000的记录，并实现emp表与dept表的自然连接,。,连接,自连接主要用在,自参照表,上显示,上下级关系,或者,层次关系,。自参照表是指在同一张表的不同列之间具有参照关系或主从关系的表。例如，emp表包含empno（雇员号）和mgr（管理员号）列，两者之间就具有参照关系。这样用户就可以通过mgr列与empno列的关系，实现查询某个管理者所管理的下属员工信息。,5自连接,连接,SQL select em2.ename 上层管理者,em1.ename as 下属员工,from emp em1 left join emp em2,on em1.mgr=em2.empno,order by em1.mgr;,查询所有管理者所管理的下属员工信息,。,连接,交叉连接实际上就是,不需要任何连接条件,的连接，它使用,cross join,关键字来实现,。,6交叉连接,SQL select count(*),from dept cross join emp;,通过交叉连接dept表和emp表，计算出查询结果的行数,。,统计,1聚合函数,函 数,说 明,AVG,(xDISTINCT|ALL),计算选择列表项的平均值，列表项目可以是一个列或多个列的表达式,COUNT,(xDISTINCT|ALL),返回查询结果中的记录数,MAX,(xDISTINCT|ALL),返回选择列表项目中的最大数，列表项目可以是一个列或多个列的表达式,MIN,(xDISTINCT|ALL),返回选择列表项目中的最小数，列表项目可以是一个列或多个列的表达式,SUM,(xDISTINCT|ALL),返回选择列表项目的数值总和，列表项目可以是一个列或多个列的表达式,VARIANCE,(xDISTINCT|ALL),返回选择列表项目的统计方差，列表项目可以是一个列或多个列的表达式,STDDEV,(xDISTINCT|ALL),返回选择列表项目的标准偏差，列表项目可以是一个列或多个列的表达式,统计,2GROUP BY函数,GROUP BY子句,经常与聚集函数一起使用,。使用GROUP BY子句和聚集函数，可以实现对查询结果中每一组数据进行,分类,统计。所以，在结果中,每个,数据都有一个与之对应的统计值。,函 数,说 明,AVG,返回一个数字列或是计算列的平均值,COUNT,返回查询结果中的记录数,MAX,返回一个数字列或是计算列的最大值,MIN,返回一个数字列或是计算列的最小值,SUM,返回一个数字列或是计算列的总和,统计,3HAVING子句,HAVING子句通常,与GROUP BY子句一起使用,，在完成对分组结果统计后，可以使用HAVING子句对分组的结果做,进一步的筛选,。,SQL select deptno as 部门编号,avg(sal)as 平均工资,from emp,group by deptno,having avg(sal)2000;,在emp表中，首先通过分组的方式计算出每个部门的平均工资，然后再通过having子句过滤出平均工资大于2000的记录信息,。,排序,在SELECT语句中，可以使用ORDER BY子句对检索的结果集进行排序，该子句位于FROM子句之后，其语法格式如下：,SELECT columns_list,FROM table_name,WHERE conditional_expression,GROUP BY columns_list,ORDER BY,order_by_expression ASC|DESC ,.n,3,数据库视图,主要内容,视图的概念,创建视图,查询视图,更新视图,修改视图的定义,01,02,03,04,05,删除视图,06,视图的概念,视图是一个,虚拟表,，它由存储的查询构成，可以将它的输出看作是一个表。视图同真,的,表一样，也可以包含一系列带有名称的列和行数据。但是，,视图并不在数据库中存储数据值,，其数据值来自定义视图的查询语句所引用的表，数据库只在数据字典中存储视图的定义信息。,视图建立在关系表上，也可以在其它视图上，或者同时建立在两者之上。视图看上去非常像数据库中的表，甚至可以在视图中进行,INSERT、UPDATE和DELETE操作,。通过视图修改数据时，实际上就是在修改基本表中的数据。与之相对应，改变基本表中的数据也会反映到由该表组成的视图中。,创建视图,使用SQL Developer中创建视图,创建视图,使用CREATE VIEW语句创建视图,create or replace view alias,alias),as,with check option constraint constraint_name,with read only,语法,查询视图,用户可以通过,SELECT,语句,像查询普通的数据表一样查询视,图的信息,。,SQL select*from emp_view;,在SCOTT模式下，通过select语句查询视图emp_view,。,更新视图,可更新视图满足以下条件,：,没有,使用,连接函数,、,聚合函数,和,组函数,；,创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、,START WITH子句及DISTINCT关键字；,创建视图的SELECT语句中不包括从基表列通过计算所得的列；,创建视图没有包含只读属性。,更新视图,使用,UPDATE语句,可以通过视图修改基本表的数据。,将emp_view_complex视图中员工编号是7566的员工的工资改为3000,试一试,修改视图的定义,使用SQL Developer语句修改视图,修改视图的定义,使用SQL命令修改视图,修改视图emp_view_union，使该视图实现查询部门编号为30的功能（原查询信息是部门编号为20的记录）,试一试,SQL create or replace view emp_view_union as,select d.dname,d.loc,e.empno,e.ename,from emp e,dept d,where e.deptno=d.deptno and d.deptno=30;,删除视图,当视图不再需要时，用户可以执行,DROP VIEW语句,删除视图。用户可,以直接删除其自身模式中的视图，但如果要删除其它用户模式中的视图，,要求该用户必须具有,DROP ANY VIEW 系统权限,。,删除视图emp_view,试一试,SQL drop view emp_view;,小结,本章首先介绍了三种关系运算：,选择、投影和连接,；然后重点讲解了数据库中的,查询,；最后讲解了,视图的概念,以及,视图的创建与使用方法,。本章重点讨论了数据库的查询，学习本章内容时，应该重点掌如何使用,SELECT语句,对数据库进行各种查询，和,管理视图,的操作。,上机指导,使用LIKE关键字，但是要查询的字符串中含有,“,%,”,或,“,_,”,，要如何操作呢？,要查询的字符串中含有,“,%,”,或,“,_,”,时，可以使用转义（escape）关键字实现查询。,（1）创建一个和dept表相同结构和数据的表dept_temp，代码如下。,SQL create table dept_temp,as,select*from dept;,（2）插入一条记录，代码如下。,SQL insert into dept_temp,values(60,IT_RESEARCH,BEIJING);,（3）显示临时表dept_temp中部门名称以IT_开头的所有数据行，代码如下。,SQL select*,from dept_temp,where dname like IT_%escape;,