oracle复杂查询2

1、NoImage2013年9月Oracle复杂查询叶顺华叶顺华1Oracle查询查询0. 0. 数据库的一些基本概念数据库的一些基本概念 1. 1. 表的管理表的管理 2. 2. 基本查询基本查询3. 3. 复杂查询复杂查询4. 4. 数据库索引数据库索引5. SQL5. SQL语句性能优化语句性能优化2基本概念基本概念-数据库服务器、数据库和表的关系数据库服务器、数据库和表的关系oracleDBDB表表Clientl所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。l为保存应用中实体的数据，一般会在数据库创建多

2、个表，以保存程序中实体的数据。l数据库服务器、数据库和表的关系如图所示：表3基本概念-数据在数据库中的存储方式id=1name=“lisi”age=23User对象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)l表的一行称之为一条记录l表中一条记录对应一个java对象的数据User对象4表的管理-创建表(基本语句)CREATE TABLE table_name(field1 datatype,field2 datatype,field3 datatype,)field：指定列名datatype：指定列类型注意：创

3、建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象 id int name string password string birthday dateIdName Passwordbirthday5表的管理表的管理oracle常用数据类型常用数据类型分类分类数据类型数据类型说明说明文本、二进制类文本、二进制类型型CHAR(size) char(20)VARCHAR(size) varchar(20)nchar(n) nvarchar2(n)clob(character large object)blob(binary large object) 定长定长

4、最大最大2000字符字符变长变长 最大最大4000字符字符Unicode数据类型数据类型 ,定长定长 最大最大2000字符字符Unicode数据类型数据类型 ,变长变长 最大最大4000字符字符字符型大对象字符型大对象 ,最大最大8tb二进制数据二进制数据 可以存放图片可以存放图片/声音声音 8tb数值类型数值类型number(p,s)p为整数位为整数位,s为小数位为小数位.范围：范围： 1 = p =38, -84 = s = 127保存数据范围：保存数据范围：-1.0e-130 = number value 1.0e+126 保存在机器内部的范围：保存在机器内部的范围： 1 22 byte

5、s时间日期时间日期dateTIMESTAMP(n)包含年月日包含年月日,时分秒。默认格式：时分秒。默认格式：DD-MON-YYYY。从公元前从公元前4712年年1月月1日到公元日到公元4712年年12月月31日的所日的所有合法日期有合法日期n的取值为的取值为09.表示指定表示指定TIMESTAMP中秒的小数位中秒的小数位数。数。N为可选。如果为可选。如果n为为0，timestamp与与date等价等价不不推荐推荐 number可以理解成是一个可变的数值类型,比如 number(12) ,你放一个小整数，它占用的字节数就少,你放一个大整数，它占用的字节数就多，很好!6oracle表的管理表的管理

6、-oracle支持的数据类型支持的数据类型 数值型数值型 NUMBER(precision , scale) NUMBER(p,s)范围： 1 = p =38, -84 = s = 127保存数据范围：-1.0e-130 = number value 0精确到小数点右边s位，并四舍五入。然后检验有效位是否 = p。s 0 精确到小数点左边s位，并四舍五入。然后检验有效位是否 = p + |s|。s = 0 等价于NUMBER(p)此时NUMBER表示整数。 7oracle表的管理表的管理-oracle支持的数据类型支持的数据类型 数值型数值型 看几个案例看几个案例:number(5,2)表示一

7、个小数有5位有效数，2位小数 。范围 -999.99999.99如果数值超出了位数限制就会被截取多余的位数。但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。number(5) number(5,0)表示一个五位整数，范围 -9999999999。输入57523.316，真正保存的数据是575238oracle表的管理表的管理-oracle支持的数据类型支持的数据类型 数值型数值型 (小练习小练习)Actual Data Specified As Stored As-123.89 NUMBER 123.89123.89 NUMBER(3) 124123.89

8、NUMBER(6,2) 123.89123.89 NUMBER(6,1) 123.9123.89 NUMBER(4,2) exceeds precision (有效位为5, 5 4)123.89 NUMBER(6,-2) 100.01234 NUMBER(4,5) .01234 (有效位为4).00012 NUMBER(4,5) .00012.000127 NUMBER(4,5) .00013.0000012 NUMBER(2,7) .0000012.00000123 NUMBER(2,7) .00000121.2e-4 NUMBER(2,5) 0.000121.2e-5 NUMBER(2,5

9、) 0.00001Actual Data Specified As Stored As-123.2564 NUMBER 123.25641234.9876 NUMBER(6,2) 1234.9912345.12345 NUMBER(6,2) Error (有效位为5+2 6)1234.9876 NUMBER(6) 1235 (s没有表示s=0)12345.345 NUMBER(5,-2) 123001234567 NUMBER(5,-2) 123460012345678 NUMBER(5,-2) Error (有效位为8 7)123456789 NUMBER(5,-4) 12346000012

10、34567890 NUMBER(5,-4) Error (有效位为10 9)12345.58 NUMBER(*, 1) 12345.60.1 NUMBER(4,5) Error (0.10000, 有效位为5 4)0.01234567 NUMBER(4,5) 0.012350.09999 NUMBER(4,5) 0.099999oracle表的管理表的管理创建表创建表 建表建表 -学生表-班级表包含: 班级编号，班级名称字段字段类型Id整形name字符型sex字符型brithday日期型fellowship小数型resume大文本型10oracle表的管理表的管理修改表修改表使用使用 ALTE

11、R TABLE 语句添加语句添加, 修改修改, 或删除列的语法或删除列的语法.ALTER TABLE tablenameADD (columnname datatype);ALTER TABLE tableMODIFY (columnname datatype);ALTER TABLE tableDROP column (column);修改表的名称：rename 表名 to 新表名11oracle表的管理表的管理修改表修改表练习练习练习练习1.给学生表添加班级编号 2.学生姓名 变成 varchar2(30) 3 学生姓名 变成 char(30) 4.删除学生表的 fellowship 字段

12、 5.把学生表名 student修改成 stu 6.删除学生表12oracle表的管理表的管理crudInsert语句 (增加数据)Update语句 (更新数据)Delete语句 (删除数据)Select语句 (查找数据)13oracle表的管理表的管理添加数据添加数据INSERT INTOtable (column , column.)VALUES(value , value.);使用 INSERT 语句向表中插入数据。l插入的数据应与字段的数据类型相同。l数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。l在values中列出的数据位置必须与被加入的列

13、的排列位置相对应。l字符和日期型数据应包含在单引号中。字符和日期型数据应包含在单引号中。l插入空值，不指定或insert into table value(null)14oracle表的管理表的管理添加数据添加数据 练习：使用insert语句向表中插入三个学生的信息。l注意：字符和日期要包含在单引号中。字段字段类型Id整形name字符型sex字符型brithday日期型fellowship小数型resume大文本型15oracle表的管理表的管理添加数据添加数据 插入部分字段插入部分字段 插入空值插入空值INSERT INTOtable (column , column.)VALUES(val

14、ue , value.);16oracle表的管理表的管理修改数据修改数据UPDATE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition 使用 update语句修改表中数据。lUPDATEUPDATE语法可以用新值更新原有表行中的各列。语法可以用新值更新原有表行中的各列。lSETSET子句指示要修改哪些列和要给予哪些值。子句指示要修改哪些列和要给予哪些值。lWHEREWHERE子句指定应更新哪些行。如没有子句指定应更新哪些行。如没有WHEREWHERE子句，则更新所有的行。子句，则更新所有的行。17o

15、racle表的管理修改数据练习 改一个字段改一个字段根据学号修改性别. 修改多个字段修改多个字段根据学号修改性别和生日 修改含有修改含有null值的数据值的数据l要求l将所有学生薪水修改为5000元。l将姓名为zs的学生薪水修改为3000元。l将lisi的薪水在原有基础上增加1000元。l将没有奖学金同学的奖学金改成10元18oracle表的管理表的管理删除数据删除数据delete from tbl_name WHERE where_definition 使用 delete语句删除表中数据。l如果不使用where子句，将删除表中所有数据。lDelete语句不能删除某一列的值（可使用update

16、）l使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。l同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。l删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。19oracle表的管理表的管理删除数据删除数据 删除数据删除数据 delete from 表名表名; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢 drop table 表名; 删除表的结构和数据 delete from student where x

17、h=A001; 删除一条记录 truncate table 表名; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快20oracle表基本查询表基本查询介绍介绍 介绍介绍在我们讲解的过程中我们利用scott用户存在的几张表(emp,dept)为大家演示如何使用select语句，select语句在软件编程中非常的有用，希望大家好好的掌握21oracle表基本查询表基本查询介绍介绍SELECT DISTINCT *|column1, column2. column3.FROMtable;lSelect 指定查询哪些列的数据。lcolumn指定列名。l*号代表查询所有列。lFro

18、m指定查询哪张表。lDISTINCT可选，指显示结果时，是否剔除重复数据l基本基本selectselect语句语句22oracle表基本查询表基本查询简单的查询语句简单的查询语句 查看表结构查看表结构sqldesc 表名; 查询所有列查询所有列select * from 表名; 查询指定列查询指定列select 列1，列2 from 表名; 如何取消重复行如何取消重复行select distinct deptno ,job from emp;?查询SMITH 的薪水,工作,所在部门23oracle表基本查询表基本查询简单的查询语句简单的查询语句 使用算数表达式使用算数表达式?显示每个雇员的年工

19、资 使用列的别名使用列的别名select ename 姓名,sal*12 as 年收入 from emp; 如何处理如何处理null值值使用nvl函数来处理 如何连接字符串如何连接字符串(|)24oracle表基本查询表基本查询简单的查询语句简单的查询语句 使用使用where子句子句?如何显示工资高于3000的员工?如何查找1982.1.1后入职的员工?如何显示工资在2000到2500的员工情况 如何使用如何使用like操作符操作符%: 表示任意 0到多个字符 _: 表示任意单个字符?如何显示首字符为S的员工姓名和工资?如何显示第三个字符为大写O的所有员工的姓名和工资 在在where条件中使用

20、条件中使用in ?如何显示empno为 123,345,800.的雇员情况 使用使用is null的操作符的操作符 ?如何显示没有上级的雇员的情况25oracle表基本查询表基本查询简单的查询语句简单的查询语句 使用逻辑操作符号使用逻辑操作符号?查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J 使用使用order by子句子句?如何按照工资的从低到高的顺序显示雇员的信息?按照部门号升序而雇员的入职时间降序排列 使用列的别名排序使用列的别名排序select ename,sal*12 年薪 from emp order by 年薪 asc;别名需要使用“

21、号圈中26oracle表基本查询表基本查询简单的查询语句简单的查询语句 分页查询分页查询按雇员的id号升序取出。27oracle表复杂查询表复杂查询 说明说明在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句 数据分组数据分组-max,min,avg,sum,count?如何显示所有员工中最高工资和最低工资?显示所有员工的平均工资和工资总和?计算共有多少员工扩展要求：?请显示工资最高的员工的名字，工作岗位?请显示工资高于平均工资的员工信息28oracle表复杂查询表复杂查询 group by 和和 having子句子句group by

22、用于对查询的结果分组统计,having子句用于限制分组显示结果.?如何显示每个部门的平均工资和最高工资?显示每个部门的每种岗位的平均工资和最低工资?显示平均工资低于2000的部门号和它的平均工资扩展要求：29oracle表复杂查询表复杂查询 对数据分组的总结对数据分组的总结1分组函数只能出现在选择列表、having、order by子句种2 如果在select 语句种同时包含有group by ,having ,order by 那么他们的顺序是group by , having , order by3 在选择列种如果有列、表达式、和分组函数,那么这些列和表达式必须有一个出现在group by

23、 子句中，否则就会出错如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)all (select sal from emp where deptno=30);扩展要求：大家想想还有没有别的查询方法.Select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno=30);34oracle表复杂查询表复杂查询子查询子查询 在多在多行子查询中使用在多在多行子查询中使用any操作符操作符请思考:如何显示工资比部门

24、30的任意一个员工的工资高的员工的姓名、工资和部门号扩展要求：大家想想还有没有别的查询方法.35oracle表复杂查询表复杂查询子查询子查询 多列子查询多列子查询单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的，而多列子查序则是指查询返回多个列数据的子查询语句请思考如何查询与smith的部门和岗位完全相同的所有雇员36oracle表复杂查询表复杂查询子查询子查询 在在from子句中使用子查询子句中使用子查询请思考：如何显示高于自己部门平均工资的员工的信息这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用解法select e1.*,e2.mya

25、vg from emp e1,(select avg(sal) myavg,deptno from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sale2.myavg解法select e1.* from emp e1 where e1.sal(select avg(sal) from emp where deptno=e1.deptno)37oracle表复杂查询表复杂查询子查询子查询 在在fromfrom子句中使用子查询子句中使用子查询请思考：查找每个部门工资最高的人的详细资料解法解法思路:得到所有的员工，进行筛选，每拿到

26、一个员工，判断该员工的工资是否是他们部门的最高工资。select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);38oracle表复杂查询表复杂查询子查询子查询 在在fromfrom子句中使用子查询子句中使用子查询请思考：显示每个部门的信息和人员数量解法自己完成解法查询获得所有的部门信息，每获得一个部门，我们就查询该部门的人数，保存为一个新的列。select d.*,(select count(*) from emp where deptno=d.deptno) allnum from dept

27、d; 39oracle表复杂查询表复杂查询子查询子查询 在from子句中使用子查询这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个临时表来对待,当在from子句中使用子查询时，必须给子查询指定别名.40oracle表复杂查询表复杂查询子查询子查询 分页查询分页查询按雇员的id号升序取出。41oracle表复杂查询表复杂查询子查询子查询 用查询结果创建新表用查询结果创建新表这个命令是一种快捷的建表方法.create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from e

28、mp; 自我复制数据自我复制数据(蠕虫复制蠕虫复制)有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。insert into mytable (id,name,sal,job,deptno) select empno,ename,sal,job,deptno from emp;42oracle表复杂查询表复杂查询合并查询合并查询 合并查询合并查询有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union , union all,intersect , minus1)union 1)union 该操作符用于取得两个结果集的并集。

29、当使用该操作符时，会自动去掉结果集中重复行。select ename,sal,job from emp where sal2500 union select ename,sal,job from emp where job=MANAGER;43oracle表复杂查询表复杂查询合并查询合并查询2)union all2)union all该操作赋与union相似，但是它不会取消重复行,而且不会排序。select ename,sal,job from emp where sal2500 union all select ename,sal,job from emp where job=manager

30、; 3)intersect3)intersect使用该操作符用于取得两个结果集的交集。select ename,sal,job from emp where sal2500 intersect select ename,sal,job from emp where job=manager;44oracle表复杂查询表复杂查询合并查询合并查询4) minus4) minus使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。select ename,sal,job from emp where sal2500 minus select ename,sal,job from emp where job=manager;45oracle表内连接和外连接表内连接和外连接 概述概述表连接分为内连接和外连接