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

第第4章章 数据库的查询和视图数据库的查询和视图01 选择、投影和连接选择、投影和连接02 数据库的查询数据库的查询03 数据库视图数据库视图1 选择、投影和连接选择、投影和连接主要内容 选择选择 投影投影 连接连接010203选择学号姓名性别平均成绩104215王敏男74104211李晓林女82 104210胡小平男88选择性别为女且平均成绩在80分以上的行学号姓名性别平均成绩104211李晓林女82 学生表选择后的结果查询满足条件的行投影学号姓名性别平均成绩104215王敏男74104211李晓林女82104210胡小平男88对“学号”和“平均成绩”投影学号平均成绩104215741042118210421088学生表选择后的结果查询满足条件的列连接T1T21A6F2BT1T2T3T4T51A13M2B20NA表T3T4T513M20NB表T1=T3等值连接连接后的表等值连接T1T2T310A1B15A1C220D2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D1A表T1T4T5T61100A1D11002B2C1200A2D1510A2C2B表自然连接连接后的表自然连接2 数据库的查询数据库的查询主要内容 选择列选择列 选择行选择行 连接连接 统计统计 排序排序0102030405为列指定列指定别名名不使用列不使用列别名名使用列使用列别名名看下面这两种查询结果，哪个看着比较顺眼呢？看下面看下面这个例子个例子检索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 job、ename、empno from emp;分析分析用SELECT语句来查询job、ename、empno，只要在SELECT后面写job、ename、empno就可以啦伪列列一种数据类型，唯一标识一条记录，物理位置的一个id，基于64位编码的18个字符。定定义 它并不是真实的存在于数据表中的列，所以被称为伪列。伪列可以从表中查询，但是不能插入、更新或删除。伪列的用途1.能以最快的方式访问表中的一行2.能显示表的行是如何存储的3.作为表中唯一标识 常用的伪列rowid和rownum数据库中的每一行都有一个行地址，rowid伪列返回该行地址。可以使用rowid值来定位表中的一行，通常情况下，rowid值可以唯一地标识数据库的一行。rowid对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。返回第一行的rownum值为1，第二行的rownum值为2，以此类推。通过使用rownum伪列，用户可以限制查询返回的行数rownum1.查询emp表的rowid列select rowid from emp;2.从emp表中查询前5条数据select*from emp where rownum 6;选择行1表达式比较比较运算符的格式为：expression =|=|!=expression查询emp表中工资（sal）大于1500的数据记录SQL select empno,ename,sal from emp where sal 1500;选择行2模式匹配LIKE谓词表达式的格式为：string_expression NOT LIKE string_expressionESCAPE escape_characterLIKE谓词 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_listFROM table_nameWHERE conditional_expressionGROUP BY columns_listORDER BYORDER BY order_by_expression ASC|DESC ,.n 3 数据库视图数据库视图主要内容 视图的概念视图的概念 创建视图创建视图 查询视图查询视图 更新视图更新视图 修改视图的定义修改视图的定义0102030405 删除视图删除视图06视图的概念 视图是一个虚拟表，它由存储的查询构成，可以将它的输出看作是一个表。视图同真的表一样，也可以包含一系列带有名称的列和行数据。但是，视图并不在数据库中存储数据值，其数据值来自定义视图的查询语句所引用的表，数据库只在数据字典中存储视图的定义信息。视图建立在关系表上，也可以在其它视图上，或者同时建立在两者之上。视图看上去非常像数据库中的表，甚至可以在视图中进行INSERT、UPDATE和DELETE操作。通过视图修改数据时，实际上就是在修改基本表中的数据。与之相对应，改变基本表中的数据也会反映到由该表组成的视图中。创建视图使用SQL Developer中创建视图创建视图使用CREATE VIEW语句创建视图create or replace view alias,alias)as with check option constraint constraint_namewith read only语法法查询视图 用户可以通过SELECTSELECT语句语句像查询普通的数据表一样查询视图的信息。SQL select*from emp_view;在SCOTT模式下，通过select语句查询视图emp_view。更新视图可更新视图满足以下条件：没有没有使用连接函数、聚合函数和组函数；创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、START WITH子句及DISTINCT关键字；创建视图的SELECT语句中不包括从基表列通过计算所得的列；创建视图没有包含只读属性。更新视图使用UPDATEUPDATE语句语句可以通过视图修改基本表的数据。将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 VIEWDROP VIEW语句语句删除视图。用户可以直接删除其自身模式中的视图，但如果要删除其它用户模式中的视图，要求该用户必须具有DROP ANY VIEW DROP ANY VIEW 系统权限系统权限。删除视图emp_view试一试试一试试一试试一试SQL drop view emp_view;小结小结 本章首先介本章首先介绍绍了三种关系运算：了三种关系运算：选择选择、投影和、投影和连连接接；然后重点；然后重点讲讲解了数据解了数据库库中的中的查询查询；最后；最后讲讲解了解了视图视图的概念的概念以及以及视图视图的的创创建与使用方法建与使用方法。本章重点。本章重点讨论讨论了了数据数据库库的的查询查询，学，学习习本章内容本章内容时时，应该应该重点掌如何使用重点掌如何使用SELECTSELECT语语句句对对数据数据库进库进行行各种各种查询查询，和，和管理管理视图视图的操作。的操作。上机指导使用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;