oracle教学全套课件

1、中博中博oracle培训培训目录目录0. Oracle数据库安装数据库安装1. 开发成功的Oracle应用2. Oracle 体系结构3. 锁、并发、事务4. 表、索引、分区5. 数据类型、函数6. SQL、视图7. PL/SQL8. 存储过程、函数、触发器9. 备份恢复10.ERWIN11.linux基础、shellOracle数据库安装数据库安装1. 1. 选择高级安装选择高级安装2. 2. 单击下一步单击下一步Oracle数据库安装数据库安装Oracle数据库安装数据库安装1. 1. 指定主目录的名称指定主目录的名称( (用于区别安装的多个用于区别安装的多个oracle)oracle)2

2、. 2. 指定指定oracleoracle的安装路径的安装路径Oracle数据库安装数据库安装这一项未执行，不用管它这一项未执行，不用管它检查结果为通过检查结果为通过Oracle数据库安装数据库安装选择选择 是是 Oracle数据库安装数据库安装Oracle数据库安装数据库安装Oracle数据库安装数据库安装数据库名和数据库名和SID,SID,连接数据库时会用到连接数据库时会用到Oracle数据库安装数据库安装Oracle数据库安装数据库安装Oracle数据库安装数据库安装Oracle数据库安装数据库安装设置几个系统用户的密码设置几个系统用户的密码Oracle数据库安装数据库安装Oracle数

3、据库安装数据库安装Oracle数据库安装数据库安装Oracle客户端配置客户端配置如果安装了服务器端，则不需要再安装客户端要访问远程服务器上的oracle，需要配置网络服务名用记事本打开oracle安装目录下的product10.2.0db_2networkADMINtnsnames.ora文件，加入远程数据库信息 other_db= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 远程服务器IP或主机名)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 全局

4、数据库名) ) )注意:oracle默认端口为1521,根据需要修改SQL*PLUS 登录这里用scott用户登录数据库，密码oracle在命令行输入: sqlplus 用名名/密码网络服务名(网络服务名的配置参考上页)SQL*PLUS 登录数据库后，就可以执行oracle命令，以及SQL和PL/SQL语句了使用使用PL/SQL Developer客户端配置好网络服务名后，会在这个下拉框显示出来客户端配置好网络服务名后，会在这个下拉框显示出来使用使用PL/SQL Developer这个窗口列出了所有的数据库对象，这个窗口列出了所有的数据库对象，可以可视化的创建和管理可以可视化的创建和管理使用使

5、用PL/SQL Developer执行执行目录目录0. Oracle数据库安装1. 开发成功的开发成功的Oracle应用应用2. Oracle 体系结构3. 锁、并发、事务4. 表、索引、分区5. 数据类型、函数6. SQL、视图7. PL/SQL8. 存储过程、函数、触发器9. 备份恢复10.ERWIN11.linux基础、shell1.1 数据库设计原则与技巧数据库设计原则与技巧目录目录0. Oracle数据库安装1. 开发成功的Oracle应用2. Oracle 体系结构体系结构3. 锁、并发、事务4. 表、索引、分区5. 数据类型、函数6. SQL、视图7. PL/SQL8. 存储过程

6、、函数、触发器9. 备份恢复10.ERWIN11.linux基础、shell2.Oracle体系结构体系结构 2.1 数据库和实例后台进程后台进程后台进程后台进程后台进程后台进程后台进程后台进程后台进程后台进程SGA文件文件文件文件文件文件实实 例例数据库数据库实例装载和打开数据库实例装载和打开数据库2.1 数据库和实例数据库和实例 数据库Database:物理操作系统文件或磁盘(disk) 的集合 实例instance:一组后台进程/线程和一个共享内存区实例只能装载并打开一个数据库一个数据库可以由一个或多个实例(使用RAC)装载并打开2.1 数据库和实例数据库和实例 连接到Oracle1.

7、专用服务器为连接创建一个新的进程，客户端于这个专用服务器直接通信，并由这个专用服务器接收和执行SQL2. 共享服务器使用“共享进程”池为大量用户服务，实际上就是一种连接池机制。客户端不能直接与共享服务器通信，调度器将客户请求放入请求队列，由共享服务器处理后，把响应放入原调度器的响应队列中，最好调度器把响应传回给客户2.1 数据库和实例数据库和实例后台进程后台进程后台进程后台进程后台进程后台进程后台进程后台进程后台进程后台进程SGA文件文件文件文件文件文件实实 例例数据库数据库专用服务器磁盘I/O内存访问客户连接专用服务器连接专用服务器连接2.1 数据库和实例数据库和实例数据库数据库请求队列响应

8、队列共享服务器调度器客户连接1234共享服务器连接共享服务器连接2.2 文件系统机制文件系统机制操作系统(OS)文件系统: 文件存放在文 件系统中，操作系统中可以查看到这些文件原始分区(raw partitions,也称裸分区): 不是文件，而是原始磁盘，没有文件系统自动存储管理(Atuomatic Storage Management,ASM): Oracle 10g的新特性，ASM是专为数据库设计的文件系统集群文件系统: 专用于RAC(集群)环境，集群中的多个节点共享的cooked文件系统数据库可能包含上述所有文件系统中的文件，不必只选择一个2.3 逻辑存储结构逻辑存储结构Oracle存储

9、层次体系1.数据库由一个或多个表空间组成2.表空间是一个逻辑存储容器，由一个或多个数据文件组成，表空间包含段3.段就是占用存储空间的数据库对象，由一个或多个区组成，段在表空间中，但是可以包含这个表空间中多个数据文件中的数据4.区是文件中一组逻辑连续的块，区段只在一个表空间中，而且总是在该表空间内的一个文件中5.块是数据库中最小的分配单位，也是数据库使用的最小I/O单位2.3 逻辑存储结构逻辑存储结构 段(segment):段就是占用存储空间的数据库对象，如表、索引、回滚段等。创建表时，会创建一个表段。创建分区表时，每个分区会创建一个段。创建索引时，会创建一个索引段。依次类推，占用存储空间的没一

10、个对象都会存储在一个段中，此外，还有回滚段(rollback segment)、临时段(temporary segment)、聚族段(cluster segment)、索引段(index segment)等。2.3 逻辑存储结构逻辑存储结构 例子：create table T(id int primary key, content clob)这里创建了四个段a) 1个Table T的段b) 1个索引段(主键会自动添加唯一索引)c) 2个clob段(一个clob段是lob索引,一个段是lob数据本身)2.3 逻辑存储结构逻辑存储结构 区(extent)区是文件中一个逻辑上连续分配的空间，即由一些

11、连续分布的块组成，最大可以达到2GB 块(block)块是Oracle中最小的空间分配单位。数据行、索引条目或临时排序结果就存储在块中，通常Oracle从磁盘读写的就是块。块常见4种大小:2KB,4KB,8KB,16KB(有些情况下32KB也可以，受操作系统限制)2.3 逻辑存储结构逻辑存储结构 字典管理的表空间由字典表管理区(extent)的分配,需要串行的查询、更改字典表，来获得空间 本地管理的表空间使用每个数据文件中存储的一个位图来管理区，需要得到一个区，系统只需在位图中将某一位设置为1，释放空间，再设置为02.3 逻辑存储结构逻辑存储结构默认表空间数据库默认有SYSTEM、SYSAUX

12、、TEMP三个表空间创建表空间create tablespace tbs_dat datafile c:oradatatbs_dat.dbf size 2000M;注意要用拥有create tablespace权限的用户，比如sys2.3 逻辑存储结构逻辑存储结构 扩充表空间大小1.添加数据文件添加数据文件alter tablespace tbs_dat add datafile c:oradatatbs_dat2.dbf size 100M;2.改变数据文件大小改变数据文件大小alter database datafile c:oradatatbs_dat.dbf resize 2500M;

13、3.数据文件自动扩展数据文件自动扩展大小大小alter database datafile c:oradatatbs_dat.dbf autoextend on next 1m maxsize 20m;2.3 逻辑存储结构逻辑存储结构 修改表空间名称alter tablespace tbs_dat rename to tbs_dat1; 删除表空间drop tablespace tbs_dat including contents and datafiles;注意注意:and datafiles，表示同时也删除物理文，表示同时也删除物理文件件2.4 物理结构物理结构物理存储结构主要是指在操作系

14、统中，Oracle数据的存储数据的存储和管理方式和管理方式。它的组成包括：数据文件(data file)存储表、索引等实际数据的文件.一个表空间，可以有多个数据文件，一个数据文件，只能属于一个表空间控制文件(control file)存储数据库的物理结构等信息的文件。重做日志文件(redo file)记录数据库的修改操作和事务操作的文件其他文件2.5 内存结构内存结构 系统全局区(Sytem Global Area),每个实例都只有一个SGA区。当多个用户连接到同一实例时，这些用户进程、服务进程共享SGA区。包括:a)数据高速缓存区b)字典缓存区c)重做日志缓存区d)SQL共享池程序全局区PG

15、A（PROCESS GLOBAL AREA）是一个内存区，包含单个进程的数据和控制信息，所以又称为进程全局区。 目录目录0. Oracle数据库安装1. 开发成功的Oracle应用2. Oracle 体系结构3. 锁、并发、事务锁、并发、事务4. 表、索引、分区5. 数据类型、函数6. SQL、视图7. PL/SQL8. 存储过程、函数、触发器9. 备份恢复10.ERWIN11.linux基础、shell3.1 锁锁 什么是锁锁(lock)机制用于管理对共享资源的并发访问. 锁定数据行select * from emp where emp.id=1for update nowait这样就锁定了

16、emp表中id=1的那行数据注意：通过for update锁定后，这些行不能修改了，但是还可以查询3.1 锁锁 for update和for update nowait使用for update锁定行，对这行执行update,delete,select . for update语句都会阻塞，即等待锁的释放后继续执行使用for update nowait锁定行，对这行执行update,delete,select . for update语句，会马上返回一个“ORA-00054:resource busy”错误，不用一直等待锁的释放后继续执行.3.1 锁锁 锁类型1.DML锁(DML lock):用

17、于确保一次只能修改某一行，而且别人不能删除你正在处理的表, DML锁包括a)TX锁(事务锁),事务发起第一个修改时会得到TX锁，而且会一直持有这个锁，直至事务commit或者rollbackb)TM锁,用于确保在修改表的数据容时，表的结构不会改变,当更新了一个表的数据时，你就会得到这张表的一个TM锁3.2 锁锁2. DDL锁,DDL操作过程中会自动为对象加DDL锁,保护这些对象不会被其它会话修改3. latch,这是Oracle的内部锁，用于协调对其共享数据结构的访问3.2 丢失更新问题丢失更新问题时间线idvalue1100用户A查询用户B查询用户A保存用户B保存T1T2T3T4用户用户A的

18、修改丢失了的修改丢失了idvalue1100idvalue1500将value修改为500idvalue1900将value修改为9003.2 丢失更新问题丢失更新问题 悲观锁1.用户A通过 select * from emp where id=1 for update nowait查询并锁定id=1这条记录2.用户B也通过select * from emp where id=1 for udpate nowait来查询id=1这条记录，这时候，oracle会报一个资源忙的错误.因此，B必须等到A完成以后，才能查询到数据，不会再出现用户A丢失更新的问题了3.2 丢失更新问题丢失更新问题 悲观锁

19、的问题1. 悲观锁性能差,不能并发操作，只能排队等待处理。实际上，查询的操作完全可以并发处理的。2.可移植性差,依赖于特定数据库，而且并不是所有数据库提供悲观锁。 悲观锁的优点数据库级别的解决办法，从而有效的保证的数据的正确性;通过各种途径操作数据库(java项目，pl/sql developer)，都会得到很好的保护。3.2 丢失更新问题丢失更新问题时间线idvalueversion1100100用户A查询用户B查询用户A保存用户B保存T1T2T3T4idvalueversion1100100idvalueversion15001001.查询时得到的version=1002.数据库实际的ve

20、rsion=1003.这两个version相等,允许保存4.将version加11.查询时得到的version=1002.数据库实际的version=1013.这两个这两个version不相等不相等,不能保存不能保存idvalueversion1900100乐观锁乐观锁3.2 丢失更新问题丢失更新问题 乐观锁1.给表加一个version字段,保存数据行的版本2.查询时，得到version的值，假设为1003.通过类型下面语句保存 update emp set value=500 where id=1 and version=100如果更新条数等于1，说明保存成功!如果更新条数等于0，说明保存失

21、败，说明有其它用户修改了这条记录4.如果保存成功，更新version值加13.3 事务事务事务是包含一系列步骤的完整操作。一个事务包含一个或者多个步骤，这些步骤要么全部成功，要么全部失败，是一个整体。例如：从A银行转帐到B银行,包括2个步骤，从A银行转出和B银行转入，这2个步骤是一个完整的操作，转帐过程就是一个事务。事务的4个特点1.原子性(atomicity):事务的所有步骤，要么都成功，要么都失败2.一致性(consistency):事务将数据从一种一致状态转变为下一种一致状态3.隔离性(isolation):是个事务的影响，在该事务提交之前对其它事务都不可见4.持久性（durabilit

22、y):事务一旦提交，其结果就是永久性的3.3 事务事务 事务控制语句隐含地，事务在修改数据的第1条SQL语句处自动启动需要显式使用commit和rollback来终止事务，注意：rollback to savepoint不会结束事务commit:提交事务，将事务期间所做修改保存rollback:回滚事务，撤销事务期间所做的修改savepoint:在事务中创建“标记点”，可以回滚到这些标记点rollback to :回滚到标记点,而不回滚标记点之前的修改set transaction:设置事务属性,如事务的隔离级别以及事务是只读还是可读写的。3.3 事务事务 分布式事务oracle能透明的处理分

23、布式事务这里假设有A,B两个oracle数据库1.创建数据库连接(database link)(后面第6章有数据库连接的介绍)2.这样执行一个分布式事务与执行本地事务没什么两样了update local_table set x=5;update remote_tabledb_link set y=10;commit;目录目录0. Oracle数据库安装1. 开发成功的Oracle应用2. Oracle 体系结构3. 锁、并发、事务4. 表、索引、分区表、索引、分区5. 数据类型、函数6. SQL、视图7. PL/SQL8. 存储过程、函数、触发器9. 备份恢复10.ERWIN11.linux基

24、础、shell4.1 表表表由行和列组成，也称为二维表例：员工信息表记录：表中一行，称为一条记录字段：构成记录的各数据项，比如姓名、性别用户编号用户编号姓名姓名性别性别生日生日部门部门001张三男2000-01-01IT002李四男2000-01-01IT003王五男2000-01-01IT4.1 表表 创建表create table EMP(EMP_ID number(24) not null, EMP_CODE varchar2(10) not null, EMP_NAME varchar2(20) not null, E_MAIL varchar2(100), DEPT_ID numbe

25、r(24) not null) tablespace UM_DAT;4.2 约束约束主键约束-添加主键alter table EMP add constraint pk_emp_id primary key (EMP_ID);唯一约束alter table EMP add constraint uq_emp_code unique (EMP_CODE);外键约束 alter table EMP add constraint fk_dept_id foreign key (DEPT_ID) references dept (DEPT_ID);oracle自动为主键和唯一约束创建索引。4.3 索引

26、索引索引作用类似书的目录，用于快速查找数据索引还可用户数据完整性限制，比如唯一索引，可以保证字段值的唯一性包含以下的类型： 标准索引(B*树)数据量非常大的情况下，查找依然很快 惟一索引(Unique Index)比如员工编号,唯一索引查找最快 位图索引(Bitmap)适合基数小的字段，比如性别,节约空间 基于函数的索引(FBI)4.3 索引索引 创建标准索引create index IDX_DEPT_NAME on DEPT (dept_name); 创建唯一索引create unique index IDX_DEPT_CODE on DEPT (dept_code); 创建位图索引crea

27、te bitmap index IDX_EMP_SEX on EMP (sex); 创建函数索引create index IDX_EMP_BDATE on EMP (TO_CHAR(B_DATE,YYYY-MM-DD);4.3 索引索引 哪些字段建议建立索引呢？select emp.e_mail,count(*) ct from empjoin dept on emp.dept_id=dept.dept_idwhere dept.dept_name = ITgroup by emp.e_mailorder by emp.e_mail1.1.表间关联字段表间关联字段( (外键外键) )2.2.查

28、询的字段查询的字段3.group by3.group by的字段的字段4.order by4.order by的字段的字段4.3 索引索引身份证这类唯一属性，应建唯一索引 性别,只有男、女、未定等少数几种状态值，应创建位图索引，位图索引更节约空间对字段使用函数，会停用索引，可创建函数索引4.3 索引索引 索引的优缺点 优点:某些情况下，数据查找快 缺点:a)在通过索引查找，返回结果比较多的情况下，由于需要占用非常多的磁盘I/O，这时全表扫描比索引查找更快b)索引占用空间惊人，甚至超过表数据所占空间,不利于管理。c)创建索引后，会降低插入，修改，删除等操作的效率。4.4 分区分区分区就是把表和索

29、引分成几大块，每一块存放到一个表空间上，性能调优的重要手段。有三种分区方式 1.散列分区 均匀分布数据，i/o设备负担均衡。 2.范围分区按数据值的范围进行分区，比如将员工信息表，按入职时间分区，06年一个区，07年一个区，08年一个区,现在我要找一个06年入职的员工，只需要扫描06年那个分区，时间会快很多，磁盘i/o也会减少4.4 分区分区3.复合分区 范围分区和散列分区结合起来使用，先把数据按范围分区，然后在每个分区内再使用散列分区，把数据均匀分布注意：索引，分区这些调优技术，虽然在数据查询上效率得到了提高，但是，数据在插入，修改操作会更慢，因为在插入的时候，还需要做索引数据，分区数据的额

30、外工作。数据库调优，需要考虑一个度的问题4.4 分区分区范围分区例子create table EMPS( SALARY NUMBER(24,4) not null, EMP_ID NUMBER(24) not null)partition by range (SALARY)( partition P_SALARY_2000 values less than (2000), partition P_SALARY_3000 values less than (3000);目录目录0. Oracle数据库安装1. 开发成功的Oracle应用2. Oracle 体系结构3. 锁、并发、事务4. 表、索

31、引、分区5. 数据类型、函数数据类型、函数6. SQL、视图7. PL/SQL8. 存储过程、函数、触发器9. 备份恢复10.ERWIN11.linux基础、shell5.1 数据类型数据类型1. char:定长字符串，长度不足的，会以空格填充达到最大长度,例如char(10),总是包含10个字节，不足的以空格填充。char最大长度为2000字节2. nchar:包含unicode编码数据的定长字符串串,nchar(10)，总是包含10个字符,最大长度为2000个字节3. varchar2: 变长字符串,于char不同，不会用空格填充到最大长度，目前于varchar类型完全相同，最大长度400

32、04. nvarchar2:包含unicode编码数据的变长字符串,nvarchar2(10),包含010个字符的信息，最大长度4000字节5.1 数据类型数据类型5.raw:变长二进制数据类型，最多存储2000个字节6.number:最多达38位数字，number(24,4),表示最多24位数字，其中小数部分最多4位，整数部分最多20位7.binary_float:32单精度浮点数，oracle 10g开始提供8.binary_double:64位双精度浮点数,oracle 10g开始提供9.long:最多存储2GB的字符数据,遗留类型，推荐使用clob类型代替10.long raw:最多2

33、GB的二进制信息,推荐使用blob每张表只能有一个long或long raw列5.1 数据类型数据类型11.date:日期类型，精确到秒12.timestamp:最多精确到小数点后9位的秒,如timestamp(6),精确到微秒13.blob:在oracle 9i及以前，存储最多4GB的二进制数据,10g开始，存储最多4GB*数据块大小字节的数据14.clob:oracle 9i及以前，存储最多4GB的字符数据，10g开始，存储最多4GB*数据块大小字节的字符数据，适合存储大文本15.nclob:包含unicode编码数据5.2 oracle常用函数常用函数字符函数 upper(str) ,转

34、为大写 lower(str),转为小写 substr(str,n,m) ,从n位开始，截取m个字符 substr(str,n),从n位开始，截取后面字符 length(str),得到字符串的长度 ltrim(str),去掉左边空格 rtrim(str),去掉右边空格 instr(str,c),得到字符c在str的位置 lpad(str,n,c),将str补足为n位长度,不足左边用字符c代替 rpad(str,n,c),将str补足为n位长度,不足右边以字符c代替5.2 oracle常用函数常用函数 字符函数例子-不区分大小写查询select emp_code,emp_namefrom empw

35、here upper(emp_name) = upper(Tom)-去掉空格select rtrim(ltrim(emp_code)from emp5.2 oracle常用函数常用函数 数值函数 round(col,n) 四舍五入round(457.628,2),小数点后2位四舍五入 结果 457.63round(457.628,-1),小数点前1位四舍五入 结果460trunc(col,n) 截断数值trunc(457.628,2) 结果457.62trunc(457.628,-1) 结果450 5.2 oracle常用函数常用函数 日期函数 months_between(date1,dat

36、e2),两个日期间的月数，结果为实数 add_months(date,m),增加m个月，m可以为负数，结果为减少m个月round,日期四舍五入trunc,截断日期last_day ,当月最后一天5.2 oracle常用函数常用函数 日期函数例子当前日期增加1个月select add_months(sysdate,1) from dual;去年同月select add_months(sysdate,-12) from dual;得到年初select trunc(sysdate,YYYY) from dual;得到月初select trunc(sysdate,MM) from dual;精确到天，

37、截断小时分秒select trunc(sysdate) from dual;当月最后一天select last_day(sysdate) from dual;5.2 oracle常用函数常用函数 转换函数日期转为字符:to_char(date1,format_model)format_model:转换后的显示格式YYYY 年,MM 月,DD 日,HH24 小时,MI 分,SS 秒例子:select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) rqfrom dual;5.2 oracle常用函数常用函数转换函数字符转为日期to_date(2007-11-11,Y

38、YYY-MM-DD)数值转为字符select to_char(55676,fm99,999.00) from dualfm表示去掉前面的空格和0结果: 55,676.00目录目录0. Oracle数据库安装1. 开发成功的Oracle应用2. Oracle 体系结构3. 锁、并发、事务4. 表、索引、分区5. 数据类型、函数6. SQL、视图、视图7. PL/SQL8. 存储过程、函数、触发器9. 备份恢复10.ERWIN11.linux基础、shellnull ,表示不确定，包含null值的算术运算，结果都为null 字符连接用 |别名，以空格 或者as连接，如果别名包含空格或者区分大小写，

39、需要用双引号判断null值，is null 和 is not nulllike 通配符，%代表零或多个字符,下划线_代表单个字符日期类型加整数，表示加几天,两个日期类型相减，结果为天数sysdate为当前日期时间6.1 基本查询基本查询 order by 排序 select swjg_dm dm,swjg_mc from dm_swjg order by swjg_dm 也可以使用别名 order by dm 还可以使用列的位置 order by 1 排序的时候，null值最大6.1 基本查询基本查询 聚合函数 avg,sum,max,min,count 除了count(*)之外，其它的不统计

40、null值 count(*) ,所有行数量 count(swjg_mc),swjg_mc非null值的记录的数量 count(distinct swjg_dm),去掉重复的记录 count(1),第一列非null值的记录的数量 聚合函数，不能出现在where子句中， 比如where avg(salary) 40006.1 基本查询基本查询 聚合函数 例子: select count(*) c1, count(1) c2, count(swjg_dm) c3, count(distinct swjg_dm) c4 from dm_swjg6.1 基本查询基本查询所有的行数第1列非null值行数s

41、wjg_dm字段非null值行数swjg_dm字段，非null，去掉重复的行数 group by select 列表 中的非聚合函数列，都必须出现在group by子句中 但是，group by子句中的列，不一定要出现在select列表中 group by 可以使用表达式,但不可以使用别名6.1 基本查询基本查询 group by 例子统计税务机关每月的入库 select swjg_dm, to_char(rkrq_jz,YYYY-MM) yf, sum(se) se from sb_zsxx group by swjg_dm, to_char(rkrq_jz,YYYY-MM)可以group

42、by 表达式，不能使用别名yf6.1 基本查询基本查询 having having,用于过滤分组 可以使用聚合函数 例:找出入库税额大于500000的税务机关 select swjg_dm , sum(se) se from sb_zsxx group by swjg_dm having sum(se)5000006.1 基本查询基本查询 rollup rollup和group by一起使用 用来产生各分组的小计以及最后的合计 例：统计税务机关的入库数，并添加合计 select swjg_dm,sum(se) sefrom sb_zsxxgroup by rollup(swjg_dm)6.1

43、基本查询基本查询6.1 基本查询基本查询 rollup 给最后一行加上”合计”二字 select nvl(swjg_dm,合计) swjg_dm, sum(se) se from sb_zsxx group by rollup(swjg_dm)6.1 基本查询基本查询6.1 基本查询基本查询grouping函数 用于判断是否由rollup产生的 group(swjg_dm)=1,表示由rollup产生 select case when grouping(swjg_dm) = 1 then 合计 else swjg_dm end swjg_dm, sum(se) se from sb_zsxx

44、group by rollup(swjg_dm)6.1 基本查询基本查询 Case 表达式 语法: case 表达式 when 值1 then 结果1 when 值2 then 结果2 else 默认结果 end6.2 条件表达式条件表达式 第二种方式 case when 条件1 then 结果1 when 条件2 then 结果2 else 默认结果 end6.2 条件表达式条件表达式 例子，交叉报表idnamekechen fengshu 1张三数学562张三语文673张三化学874李四语文245王五化学54name数学数学 语文语文化学化学张三566787李四24王五546.2 条件表达

45、式条件表达式 例子，交叉报表select name, sum(case kechen when 语文 then fengshu end) yuwen, sum(case kechen when 数学 then fengshu end) shuxue, sum(case kechen when 化学 then fengshu end) huaxuefrom tablegroup by name6.2 条件表达式条件表达式例子,本期，上期，去年同期select zsxm_dm,to_char(rkrq_jz,YYYY-MM) yf, sum(case when rkrq_jz = to_date(

46、2006-09-01, YYYY-MM-DD) then se end) bq, -本期 sum(case when rkrq_jz = to_date(2007-08-01, YYYY-MM-DD) then se end) sq, -上期 sum(case when rkrq_jz = to_date(2006-09-01, YYYY-MM-DD) then se end) qntq -去年同期 from sb_zsxx group by zsxm_dm,to_char(rkrq_jz,YYYY-MM)6.2 条件表达式条件表达式6.2 条件表达式条件表达式 Decode函数 Decode

47、(表达式, 条件1,结果1, 条件2,结果2, ,默认结果)6.2 条件表达式条件表达式 Decode例子select swjg_dm, decode(swjg_bz, B, 税务部门, J, 税务机关) from dm_swjg6.2 条件表达式条件表达式 用于解决一般SQL很难完成的问题函数(参数) Over (partition by col_list order by col_list)例：统计各税种占总收入的比重select zsxm_dm, sum(se) se, -收入 sum(sum(se) over() zse, -总收入 sum(se) * 100 / sum(sum(se

48、) over(), 2) -比重 from sb_zsxx group by zsxm_dm6.3 分析函数分析函数6.3 分析函数分析函数例子，统计税务机关各月份收入及累计收入select swjg_dm, to_char(rkrq_jz, YYYY-MM) yf, -月份 sum(se) se, -当月收入 sum(sum(se) over(order by to_char(rkrq_jz, YYYY-MM) rows unbounded preceding) ljse -累计收入 from sb_zsxx where swjg_dm = 22103020000 group by swjg

49、_dm, to_char(rkrq_jz, YYYY-MM)6.3 分析函数分析函数Emp_idEmp_nameDept_idE01罗代均D01E02罗曾英D02E03老焦D03E04老肖D05Dept_idDept_nameD01资讯课D02生产三课D03生管课D04采购课Emp_idEmp_nameDept_idDept_nameE01罗代均D01资讯课E02罗曾英D02生产三课E03老焦D03生管课内连接(结果为两表都包含的dept_id的行)6.4 多表关联查询多表关联查询 内连接 ISO标准:(oracle 9i开始支持ISO标准写法)select e.emp_id,e.emp_na

50、me,d.dept_namefrom emp einner join dept d on e.dept_id=d.dept_id Oracle : select e.emp_id,e.emp_name,d.dept_namefrom emp e,dept dwhere e.dept_id=d.dept_id6.4 多表关联查询多表关联查询Emp_idEmp_nameDept_idE01罗代均D01E02罗曾英D02E03老焦D03E04老肖D05Dept_idDept_nameD01资讯课D02生产三课D03生管课D04采购课Emp_idEmp_nameDept_idDept_nameE01罗

51、代均D01资讯课E02罗曾英D02生产三课E03老焦D03生管课E04老肖D05左连接(以左表(emp)为准，右表没有的，为空值null)D05,左表有，右表无6.4 多表关联查询多表关联查询 左连接ISO标准:select e.emp_id,e.emp_name,d.dept_namefrom emp eleft join dept d on e.dept_id=d.dept_idOracle :select e.emp_id,e.emp_name,d.dept_namefrom emp e,dept dwhere e.dept_id =d.dept_id(+)6.4 多表关联查询多表关联查

52、询 右连接跟左连接相反，以右表为准ISO :select e.emp_id,e.emp_name,d.dept_namefrom emp eright join dept d on e.dept_id=e.dept_idOracle:select e.emp_id,e.emp_name,d.dept_namefrom emp e,dept dwhere e.dept_id(+)=d.dept_id6.4 多表关联查询多表关联查询Emp_idEmp_nameDept_idE01罗代均D01E02罗曾英D02E03老焦D03E04老肖D05Dept_idDept_nameD01资讯课D02生产三课

53、D03生管课D04采购课Emp_idEmp_nameDept_idDept_nameE01罗代均D01资讯课E02罗曾英D02生产三课E03老焦D03生管课E04老肖D05D04采购课全外连接(包含两表的数据)D05,左表有，右表无D04,右表有，左表无6.4 多表关联查询多表关联查询 全外连接Oracle 9i以上版本支持select e.emp_id,e.emp_name,d.dept_namefrom emp efull outer join dept d on e.dept_id=d.dept_id6.4 多表关联查询多表关联查询 where 子句中使用子查询select swjg_d

54、m, sum(se) se from sb_zsxx where swjg_dm = (select swjg_dm from dm_swjg where swjg_mc = 鞍山市地方税务局) group by swjg_dm 成对比较where (col1,col2) = (select col1,col2 from.)6.5 子查询子查询 子查询返回多行的情况swjg_dm In (子查询) ,swjg_dm在子查询结果中salary any (子查询) 小于子查询其中一个值salary all(子查询)小于子查询中的所有值 null对in和not in的影响 使用in和not in的时

55、候，子查询不能返回null的行,否则查询不到数据6.5 子查询子查询 select子句中使用子查询例子：查询鞍山市地税局下级税务机关的入库数select swjg_dm, (select sum(se) from sb_zsxx zs, dm_swjg dm where zs.swjg_dm = dm.swjg_dm and dm.jbdm like swjg.jbdm | %) se from dm_swjg swjg where sj_swjg_dm = 221030000006.5 子查询子查询 inline view可以用这种方式，取代临时表,子查询，相当于一个视图select bq.

56、se,lj.sefrom (select swjg_dm,sum(se) se from ) bq,-本期(select swjg_dm,sum(se) se from .)lj-累计where bq.swjg_dm=lj.swjg_dm6.5 子查询子查询 给返回结果加上序号rownum伪列，可以给返回结果加上序号但是，序号是order by之前分配的，所以如果有order by,最后的序号是乱的!加一个子查询可以解决这个问题select t.*, rownum from (select swjg_dm, swjg_mc from dm_swjg order by swjg_dm ) t6.

57、5 子查询子查询 TOP-N问题返回前N条数据由于rownum是在order by之前分配的，所以需要有子查询的办法，返回TOP-N 条数据例子：返回前100位纳税大户select t.* from (select nsrdzdah from sb_zsxx order by se desc) t where rownum 1006.5 子查询子查询 exists/not existsexists,测试子查询的结果集的存在性例子：SB_ZSXX中，过滤掉在DM_SWJG中不存在的SWJG_DMselect swjg_dm, se from sb_zsxx zs where exists(sel

58、ect null from dm_swjg swjg where swjg.swjg_dm = zs.swjg_dm)首先执行外查询，查询sb_zsxx，得到一个swjg_dm,然后用这个swjg_dm查询子查询，exists判断子查询是否返回结果子查询select 返回什么结果不重要，只需要知道是否返回结果，所以这里我用了select null,可以随便写6.5 子查询子查询 WITH子句With子句，使用多个子查询，每个子查询的结果放入用户临时表中，方便使用及性能的提高with bq as (select swjg_dm,se ),-本期入库 qs as (select swjg-dm,s

59、e )-欠税 select bq.swjg_dm,bq.se,qs.se from bq,qs where bq.swjg_dm=qs.swjg_dm6.5 子查询子查询 Update/Delete语句中使用子查询Update table set col=(sub_query)Where col = (sub_query)Delete from tableWhere col=(sub_query)6.5 子查询子查询 遍历树形数据select level,col1,col2.from tablestart with 条件connect by prior 条件level ,伪列，每条记录的层级6

60、.6 层次查询层次查询 例子递归查询鞍山市局的所有下级税务机构select level, swjg_dm, swjg_mc from dm_swjg start with swjg_dm = 22103000000-鞍山市局，往下找connect by prior swjg_dm = sj_swjg_dm6.6 层次查询层次查询还可以加where过滤一些节点返回辽宁省局下面所有县级税务机构select swjg_dm, swjg_mc from dm_swjg where yxws = 7 -县级 Start with swjg_dm = 22100000000 -省局开始connect by prior s