Oracle知识点总结

                            oracle知识点总结
数据库查询
入门语句
入门语句
SQL简介
查询基础
基本关键字
算术表达式+-*/
连接运算符||
字段别名as
空值 is null
去除重复行 distinct
查询结果排序 order by asc(desc)
比较运算符 > < (!=  or  <>) between and
in 操作  not in
模糊查询 like
逻辑运算符   or and not
练习题
—
查询部门30中所有员工的信息
找出奖金高于工资的员工
找出每个员工工资和奖金的总和
找出部门10中的经理(manager)和部门20中的普通员工(clerk)
找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工
二
找出有奖金的员工的不同工作
找出没有奖金或者奖金低于500的员工
显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
单行函数
字符函数
字符函数
Trim
Rpad
Lpad
Instr
Replace
Length
Substr
Lower
Upper
Initcap
Concat
数值函数
Round
Mod
Trunc
日期函数
Months_between
Add_months
Next_day
Last_day
转换函数
to_char
to_date
to_number
通用函数
NVL
NULLIF
NVL2
COALESCE
CASE
DECODE
练习题
—
找出每个月倒数第三天受雇的员工
找出25年前雇的员工
所有员工名字前加上Dear,并且名字首字母大写
找出姓名为5个字母的员工
找出姓名中不带R这个字母的员工
二
显示所有员工的姓名的第一个字
显示所有员工，按名字降序排列，若相同，则按工资升序排列
假设一个月有30天，找出所有员工的日薪，不计小数
找到2月份受雇的员工
列出员工加入公司的天数(四舍五入)
三
用case函数列出员工所在的部门,部门号是多少则显示'部门多少',否则显示'其他部门'
用decode函数列出员工所在的部门,部门号是多少则显示'部门多少',否则显示'其他部门'
分组函数
count
共有多少条记录,如果没有数据，则返回0
avg
平均值函数
max
最大值函数
min
最小值函数
sum
总和函数,求的是一列的和,多列直接用+
分组函数与空值
分组函数省略列中的空值,可使用nvl()函数先处理空值
Group by 子句
1.出现在select、order by列表中的字段,有分组函数，那么未在分组函数中的字段需要在group by 中出现
2.不允许在where子句中使用分组函数，建议用having
Having子句
代替where 后面接分组函数
查询1981-05-01后入职,平均薪水大于1200的部门、岗位的平均薪水
练习题
一
分组统计各部门下工资>500的员工的平均工资
统计各部门下平均工资大于500的部门
算出部门30中得到最多奖金的员工的奖金
算出每个职位的员工数和最低工资
二
列出员工表中每个部门的员工数，和部门编号
得到工资大于自己部门的平均工资的员工信息
分组统计每个部门下，每种职位的平均奖金，也要算没奖金的人，和总工资(包括奖金)
算出每个部门，每个职位的平均工资和平均奖金(平均值包括没有奖金),如果平均奖金大于300,显示"奖金不错",如果平均奖金100到300,显示'奖金一般',如果平均奖金小于100,显示基本没有奖金，按部门编号降序，平均工资降序排列
多表查询
笛卡尔集
不加入关联条件的查询,结果是多张表的记录条数的积
等值连接
用关联条件把两张表关联起来
非等值连接
自连接
发生在一张表内，一张主做主表，别一张同样的表作虚表为从表
内连接
等同于等值连接
左外连接
在内连接的基础上会多查询出主表中未关联上的数据
右外连接
在内连接的基础上会多查询出从表中未关联上的数据
全外连接
在内连接的基础上会多查询出主从表中都未关联 的数据
集合操作
Union 并集
所有的内容都查询，重复的显示一次,即会去除重复的记录
Union All 并集
所有的内容都显示，包括重复的
Intersect  交集
只显示重复的
Minus 差集
只显示对方没有的
子查询
单列单行
子查询未返回任何结果，则主查询也不会返回任何结果

子查询返回的是一个具体的值
查询比员工7566工资高的员工信息
查询和smith的工作相同的员工信息
单列多行

子查询返回的是一列数据的多个值
查询薪水高于每个部门的平均薪水的员工 
查询薪水高于任意部门的平均薪水的员工
查询 和 MARTIN 或者 SMITH 岗位相同的员工
查询和部门10工作岗位相同的雇员信息
查询工资比部门30的所有员工的工资高的员工信息
查询工资比部门30的任意一个员工的工资高的员工信息
单行多列

返回的是一行，多列的数据
查询和smith的部门和岗位完全相同的所有雇员
多行多列

返回的多行，多列的数据
查询高于自己部门平均工资的员工信息
分页查询 ROWNUM函数
rownum 函数直接使用直接查询1开始的数据，数据中必须包括从1开始的数据 如下:
select * from emp where rownum=1 or rownum=2; --非法
select * from (select * from emp order by sal ) where rownum<=5;--正常
select * from emp where rownum<=5;--正常
常见的分页sql
先查询出需要的数据进行排序,再进行rownum 发号，取需要的记录段
exists
练习题
一
列出员工表中每个部门的员工数和部门号
列出员工表中每个部门的员工数(员工数必须大于3) ,和部门名称
找出工资比jones多的员工
列出所有员工的姓名和其上级的姓名
以职位分组，找出平均工资最高的两种职位
二
查出不在部门20,且比部门20中任何一个人工资都高的员工姓名、部门名称
得到平均工资大于2000的工作职种
分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500
得到每个月工资总数最小的那个部门的部门编号，部门名称，部门位置
分部门得到平均工资等级为2级(等级表) 的部门编号
三
查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字,部门名字，部门位置
查找出收入(工资奖金) 下级比自己上级还高的员工的编号，员工名字，员工收入
查找出工资等级不为4级的员工的员工名字，部门名字，部门位置
查找出职位和 'MARTIN' 或者 'SMITH' 一样的员工的平均工资
查找出不属于任何部门的员工
四
按部门统计员工数，查处员工数最多的部门的第二名到第五名(列出部门名字，部门位置)
查询出king所在部门的部门号\部门名称\部门人数
查询出king所在部门的工作年限最大的员工名字
查询出工资成本最高的部门的部门号和部门名称
高级查询
随机返回emp表中5条记录dbms_random.value()
处理空值排序
Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
查询跳过表中的偶数行
语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
语法:ROW_NUMBER() OVER(ORDER BY COLUMN)
row_number() 从1开始，为每条分组记录返回一个数字，order by column 表示按哪一列进行排序,partition by column 表示按哪一列进行分组, 意思是 按分组、排序后，再给每条分组记录返回一个数字，从1开始  就得到了后图中的数据

数据源:

预期结果

SQL脚本
查询每个部门的员工工资及其所在部门的最高工资，最低工资，平均薪资
连接求和
sum(sal) over(order by ename) 指的是连续求和，是以ename来排序的.若有多个这样的窗口函数，以后面的排序为主

SQL脚本
分部门连续求和
sum(sal) over(partition by deptno order by ename)

SQL脚本
得到当前行上一行或者下一行的数据
lead(sal) over(order by sal) 上一行
lag(sal) over(order by sal)  下一行



select ename,sal,lead(sal) over(order by sal) aaa,lag(sal) over(order by sal) bbb from emp;
确定一年内的天数
trunc函数
日期 trunc(date[,fmt])
trunc(sysdate,'yyyy') --返回当年第一天
trunc(sysdate,'mm') --返回当月第一天
trunc(sysdate,'d') --返回当前星期的第一天
数字 trunc(number[,decimals])
保留指定位置的小数，对后面的部分直接舍去
select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual;
查询EMP员工表下每个部门工资前二名的员工信息

SQL脚本:
找出选了所有课程的学生信息

SQL脚本:
面试题
面试题表信息











删除表中重复记录








rq                 shengfu





面试题查询信息
用一条sql 语句算出商品A、B 目前还剩多少 
1.找出忘记填写性别的员工
查询统计出各年龄中：表中文化程度四个类别各有多少人,占总人口百分比
1.查找陈红老师教的学生有哪些
2.找学生小明所有的文科老师
3.找出没有选修陈红老师的学生
4.教的学生最少的老师
--非常经典的面试题 id是主键唯一，重复记录其他都一样
查询出每门课都大于80分的学生姓名
查出该表中一个FID对应多个不同的Fno的记录
假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人： fsalary>9999 and fage > 35           fsalary>9999 and fage < 35 fsalary<9999 and fage > 35                fsalary<9999 and fage < 35                     每种员工的数量；
如果要生成下列结果, 该如何写sql语句?
  时间           胜   负
2005-05-09  2     2
2005-05-10  1     2


求table3图示的数据,即求各个月份的业绩
扩展：求各个季度的业绩
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。

数据字典
9.1 查询某用户下所有表
9.2 查询EMP表中所有字段
9.3 列出表的索引列
9.4 列出表中约束
9.5 在oracle中描述数据字典视图
数据类型
CHAR
varchar
varchar2
nvarchar2
date
blob(binary 二进制流的大对象)
clob(文件大对象)
1.char是固定长度的，速度比varcar2快得多，但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉
2.varchar2 一般适用于英文和数字，Nvarchar2适用中文和其他字符,N表示unicode常量，可解决多语言字符集间的转换问题
3.Number默认38位
体系结构 DBA
数据库 Database
物理存储结构
Desc v$logfile;  日志文件
Select member from v$logfile;
V$controlfile; 控制文件
V$datafile;  数据文件
逻辑存储结构
表空间
段
区
块
数据库实例 Database Instance
oracle内存结构和后台进程被称为数据库的实例
oracle实时应用集群
数据库服务名
网络服务名
监听器
改变表结构 DDL
12.1创建表
—
12.2使用子查询创建表又叫复制表
—
12.3字段的操作
alter table 表名   add/drop/modify 字段
添加字段
修改字段
删除字段
12.6 清空表中数据
12.7 删除表
—
12.8重命名表
练习题:
子主题 1
scott下面创建一个表  emp1    empno      number(10) ename   varchar2(50)
添加一个字段
sal    number(10,2)
修改字段
删除字段
把表emp1改名为emp2
删除表 emp2
创建一个和emp结构一样的表emp3，并同时插入工资大于1000的数据
清空emp3表的数据(用truncate)
ename varchar(100)
sal
改变数据结构 DML
insert
表间数据拷贝，即复制某表的部分数据到另一张表
update
将编号为7779用户的工作和所属上级换成编号为7566的雇员的工作和所属上级
delete
—
merge
MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。
练习题:
子主题 1
往emp表中插入empno,ename,sal 数据 (111,'1',1000)    (222,'2'.2000)
把empno=111的员工comm改成100
往dept表中再插入dept表中deptno=100的数据
删除empno=111的数据
为所有人长工资，标准是 :         
10号部门涨10% 
20号部门涨15%   
30号部门涨20%
其他部门长18%
根据工作年限长工资，标准是，为公司工作了几个月就涨几个百分点
约束/视图/索引/序列/同义词
约束
约束就是指对插入数据的各种限制，可以对数据库中的数据进行保护，可以在建表的时候直接申明，也可以为已建好的表添加约束
非空约束力 NOT NULL   
不能为空 name VARCHAR(30) NOT NULL
主键约束 PRIMARY KEY
不能重复，不能为空  pid NUMBER PRIMARY KEY
ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid);
唯一约束  UNIQUE
值不能重复,空值除外   tel VARCHAR(50) UNIQUE
ALTER TABLE person ADD CONSTRAINT person_tel_uk UNIQUE(tel);
条件约束 CHECK
插入的数据必须满足某些条件  age NUMBER CHECK(age BETWEEN 0 AND 150)
ALTER TABLE person ADD CONSTRAINT person_age_ck  CHECK(age BETWEEN 0 AND 150)
外键约束  FOREIGN KEY
某一列指向另一张表的主键
本表与另一张表是多对一或一对一的关系
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid)  REFERENCES person(pid) ON DELETE CASCADE;
级联删除
加入  ON DELETE CASCADE 
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
删除约束
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;
启用约束
ALTER TABLE book enable CONSTRAINT person_book_pid_fk;
禁用约束
ALTER TABLE book disable CONSTRAINT person_book_pid_fk;
练习题 : 
子主题 1
创建一张表 student
id        number     name  varchar2(10)   age       number         tel        varchar2(10) 
创建一张学员兴趣爱好表 hobby
id number(10)
hobby_name varchar2(10)
sid  number --学生的id
子主题 2
创建表并
给id 字段添加主键约束
给name添加非空约束
给age字段添加check约束(age必须大于18岁)
给tel添加唯一非空约束
答案
创建表并
给sid字段添加外键约束,指向student表的id,并且要带级联删除
删除掉student 表中tel的唯一约束(先写出查看该表约束的sql)
答案
查约束
删除约束
手动添加student表中tel字段的唯一约束
约束名为:my_constraint_1
答案
禁用约束  MY_CONSTRAINT_1
答案
启用约束MY_CONSTRAINT_1
答案
视图
一个封装了各种复杂查询的语句
每次查询视图，都会执行一次sql查询语句，而不是直接获得数据
默认情况下，创建的视图，如果基表数据更新了，则会更新视图中的数据
创建视图
create view view_名字 (字段)  AS 子查询
建立一个只包含20部门雇员信息的视图(雇员编号、姓名、工资)
将一个较复杂的语句包装成视图
显示部门内最低工资比20部门最低工资还要高的部门的编号及部门内最低工资,并包装成视图
高级视图
删除视图
创建并替换
视图中的数据最好不要更新
保护视图的创建规则
WITH CHECK OPTION
只读，不可修改
with read only 
查看视图
select TEXT from 视图名
索引
什么是索引
1.一种用于提升查询效率的数据库对象
2.通过快速定位数据的方法，减少硬盘I/O操作
3.索引信息与表独立存放
4.Oracle数据库自动使用和维护索引
创建索引的目的
1.维护被索引列的唯一性
2.提供快速访问表中数据的策略
索引查询
查询现有的索引
查询索引建立在哪些字段上
索引分类
唯一性索引
非唯一性索引
创建索引
自动创建
在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引
手动创建
用户可以在其它列上创建非唯一的索引，以加速查询
建立索引的时候，字段的组合顺序是非常重要的。一般情况一，需要经常访问的字段放在组合字段的前面
create index abc on student (sid,sname);
索引abc对select * from student where sid=1 ; 这样的查询语句更有效
create index abc1 on student (sname,sid);
索引abc1对select * from student where sname='louis';  这样的查询语句更有效
删除索引
drop Index pk_dept1;
索引的存储
索引和表都是独立存在的
在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生IO冲突
使用Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好 的提高查询速度 
索引类型
B 树索引  B-Tree Index
创建索引的默认类型，结构是一颗树，采用的是平衡 B 树算法 
右子树节点的键值大于等于父节点的键值
左子树节点的键值小于等于父节点的键值

位图索引 BitMap Index
如果表中的某些字段取值范围比较小，如职员性别、分数列ABC级,这样的字段建立B树索引没有意义，不能提高检索速度
Create BitMap Index student on(sex);
索引优缺点
优点
1.大大加快数据的检索速度
2.创建唯一性索引，保证数据库中每一行数据的唯一性
3.加速表和表之间的连接
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
缺点
1.索引需要物理空间
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据维护速度
创建索引的原则
1.在select操作占大部分的表上创建索引
2.在where中出现最频繁的列上创建索引
3.在选择性高的列上创建索引
4.复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列
5.小于5M的表，最好不要使用索引来查询，表越不，越适合用全表扫描
使用索引的原则
1.查询结果是所有数据行的5%以下时，使用index查询效果最好
2.where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引
3.索引利于select,但对经常insert,delete尤其update的表，会降低效率
emp表的deptno列上建有unique index
A:select dname,deptno from dept where deptno not in (select deptno from emp);
B:select dname,deptno from dept where not exists (select deptno from emp where dept.deptno=emp.deptno);
B效率要比A效率高，A会对整个emp表进行扫描，B会在子查询中使用联合查询，只是对emp表进行部分数据扫描，并会用deptno列的索引 
4.where 子句中的这个字段，必须是复合索引的第一个字段
一个索引是按f1 f2 f3 的次序建立的，若  where f2=:var2,则无法使用索引
where 子句中的这个字段，不应该参与任何形式的计算，任何对列的操作都将导致表扫描,包括数据库函数、计算表达式等
查询时要尽可能将操作移到等号右边
应尽量熟悉各种操作符对于Oracle是否使用索引的影响，以下操作会显式阻止Oracle使用索引
is null        is not null
not in    !=       like
to_char    to_date    to_number
select * from emp where to_date(hiredate)>=to_date('2001-7-18','yyyy-mm-dd');
列的索引也不会生效
管理索引
1.先插入数据后创建索引
2.设置合理的索引列顺序
3.限制每个表索引的数量
4.删除不必要的索引
5.为每个索引指定表空间
6.经常做insert,delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片
要停应用，以保持数据一致性，不实用
7.有索引的最好定期rebuild索引，rebuild期间只允许表的select操作，可以数据库较空闲时间提交，以降低索引碎片，提高效率
索引题目
问
针对一个表的查询语句能否会用到两个索引?
如果能用到，那么其实现原理是怎样的？
效率如何，其代价如何，比如额外开销等
答
可以同时用到两个索引
select * from t where x=1 and y=2;
索引是以独立于表存在的一种数据库对象，它是对基表的一种排序（默认B树索引就是二叉树的排序方式）
如在表t 上(x,y,z)分别建立了索引(index1,index2,index3),那么在查询select * from t where x=1 and y=2 时,会分别用到index1,index2
原理是先到index1索引表中查到符合x=1条件的记录，然后到index2索引表中查到y=2条件的记录
查询效率肯定是大于没有索引情况的全表扫描,但有两个问题
问题一：建立索引将占用额外的数据库空间，更重要的是增删改操作的时候，索引的排序也必须改变，加大的维护的成本
问题二：建立经常查询x=?和y=?,那推荐组合是index(x,y),这种情况下组合索引的效率是远高于两个单独的索引的
那在查询条件中出现x,xy,xyz,yzx都是可以用到该组合索引的，但y,yz,z是不能用到该索引的，即必须包含组合索引第一列
序列
序列的使用
创建序列
Create SEQUENCE 序列名    START WITH  1   INCREMENT BY  1 ORDER  CACHE  20  NOCYCLE; 
序列使用
select 序列名.nextval from dual;
下一个值,序列真正的使用
select 序列名.currval from dual;
查看当前值,必须先有nextval,才能currval
Cycle  轮回
create sequence XX start with 1 increment by 1 cycle maxvalue 3 nocache
到了3又会从1开始
Cache 缓存
如果指定cache值 ,oracle就可以预先在内存里面放置一些sequence,这样存取的快些,cache里面的取完后，oracle自动再取一组到cache.
使用cache或者会跳号，比如数据库不正常down掉，cache中的sequence就会丢失
不能改变当前值，但是可以改变增量
alter sequence 序列名 increment by 3;
同义词
同义词的体现：
任何一个用户下，都可以直接访问dual,而不需要加上前缀的用户名
dual其实是sys用户下的一张表
同义词的作用：
很方便的操作不同用户下的对象
能使两个应用程序使用不同的名字指向同一张表
使用不同的用户指向同一张表
创建同义词
create SYNONYM dept for soctt.dept ;  
这样创建的的同义词才是私有的，只有创建者才能用
drop synonym dept;
Create public synonym dept for soctt.dept;
这样创建的同义词才是公有的
drop public synonym dept;
练习题 : 
视图题
创建一个包含1982年3月31日以后入职的所有雇员的视图
创建一个包含佣金高于其薪金的雇员视图
创建一个包含所有雇员的雇员编号、雇员名称、部门名称和薪金的视图
创建一个有手下的雇员的视图，包括雇员编号，雇员名称
创建一个包含各种工作的薪金总和的视图
序列题
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
同义词题
为emp表创建一个同义词emp1
在上面创建的同义词中插入值，并观察对基表的影响
索引题
在emp表的empno字段上创建一个索引，并检查是否可以创建
在emp表的sal上创建一个索引
综合题
列出您所创建的全部视图、同义词、序列和索引
删除你所创建的任何视图的基表，然后尝试查询视图，并观察查询的输出情况
删除您创建的所有视图、同义词、序列和索引
SQL优化
例：删除一张表的重复记录(ID是自增唯一主键，重复记录，其他字段都一样),数据量很大，性能要求很高

做法一
delete from t where id not in (selecte min(id) from t group by name,age)
做法二
delete from t where id in(selete a.id from t a1,t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age)
做法三
delete from t a1 where not exists (selete * from t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age)
数据量>100,0000 以上三种做法，均可，第三种性能最佳. 二快于一
SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充分利用索引,执行过程中访问尽量少的数据块，减少表扫描的I/O次数，尽量避免全表扫描和其他额外开销
oracle数据库常用的两种优化器
RBO  rule-based-optimizer
在RBO方式下，Oracle会根据自己内部设置的一些规则来决定选择计划，例如:Oracle会根据以下优先级来选择执行计划（越靠前，rank越氏，越快）
CBO  cost-based-optimizer
目前更多的采用，基于此种基于开销的优化器
在CBO方式下，Oracle会根据表及索引的状态信息来选择计划；
优化1：尽量少用IN操作符
基本上所有的IN操作符都可以用EXISTS代替，在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑
优化2：尽量用NOT EXISTS或者外连接替代NOT IN操作符
因为NOT IN 不能应用表的索引
优化3 : 尽量不用"<>"或者"!="操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描
优化4 : 在设计表时，把索引列设置为NOT NULL
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的
优化5 : 尽量不用通配符 "%" 或者 "_" 作为查询字符串的第一个字符
当用这两个通配符时，索引不会被使用,产生全表扫描
优化6 : where 子句中避免在索引列上使用计算 
如果索引不是基于函数，那么当在Where子句中对索引列使用函数时，索引不再起作用
如:substr(no,1,4)='5400'  优化处理: no like '5400%'
如:trunc(hiredate)=trunc(sysdate) , 优化处理： hiredate>=trunc(sysdate) and hiredate <trunc(sysdate+1)
优化7 : 用">=" 替代 ">
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，
而如果数据量很大的时候，如>2和 >=3差别就很大了
优化8 : 利用SGA 共享池,避开parse阶段
ORACLE 共享内存SGA的原理，可以得出ORACLE对每个SQL都会对其进行一次分析，并且占用共享内存,如果将SQL的字符串及格式写得完全相同则oracle只会分析一次,共享内存也只会留下一次的分析结果,  可以减少分析sql的时间，而且可以减少共享内在重复的信息，oracle也可以准确统计sql的执行频率
不同区域出现的相同的Sql语句要保证查询字符完全相同，建议经常使用变量来代替常量，以尽量使用重复sql代码，以利用SGA共享池，避开parse阶段，防止相同的Sql语句被多次分析，提高执行速度。
因此使用存储过程，是一种很有效的提高share pool共享率，跳过parse阶段，提高效率的办法。
优化9： where后面的条件顺序要求,where后面的条件，表连接语句写在最前，可以过滤掉最大数量记录的条件居后
优化10 : 使用表的别名，并将之作为每列的前缀
当在sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀，这样可以减少解析时间
优化11 : 进行了显式或隐式的运算的字段不能进行索引
如 : ss_df+20>50 优化处理：ss_df>30
如 : 'X'||hbs_bh>'X5400021452'  优化处理：hbs_bh>'5400021452'
如 : sk_rq+5=sysdate,优化处理: sk_rq=sysdate-5
hbs_bh=5401002554 优化处理：hbs_bh='5401002554' 注，hbs_bh是字符型，直接给数字会进行to_number转换
优化12:用Union all 代替 Union
unoin 会去掉结果集中的重复记录，所以oracle会进行sort unique操作(与使用distinct时操作类似)，如果结果集较大，则操作会比较慢.如果数据本身重复行较少，则用unoin all会比用union效率高很多 
其他优化：
1.尽量使用packages
packages 在第一次调用时能将整个包load进内存，对提高性能有帮助
2.尽量使用cached sequences 来生成primary key
提高主键生成速度和使用性能
3.很好的利用空间
如用varchar2数据类型代替char
4.使用Sql优化工具
sqlexpert;toad;explain-table;pl/sql;oem
SGA 数据库的系统全局区
共享池
共享SQL区
专门存放用户SQL命令，oracle使用最近最少使用等优先级算法来更新覆盖
数据字典缓冲区
存放数据库库运行的动态信息
数据缓冲区
存放sql运行结果抓取到的data block
日志缓冲区
存放数据库运行生成的日志
PL/SQL
pl/sql块
PL/SQL块
—
IF
—
LOOP
—
WHILE
练习题 : 
子主题 1
输入一个雇员的编号
如果其工资高于3500，则显示高工资
工资大于2000，则显示中等工资
输入一个雇员编号，根据它所在的部门涨工资，规则：


• 10部门上涨10%
• 20部门上涨20%
• 30部门上涨30%
所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。
一答
二答

接收部门编号，显示部门名和地理位置

答


接收雇员号，显示 该雇员的所有信息，没有提成的用0替代。（用%rowtype实现）
答

接收雇员号，显示 该雇员的工资和提成，没有提成的用0替代。（用%type实现）
答

子主题 2

接收一个雇员名，判断他的job，根据job不同，为他增加相应的sal（用if-elsif实现）
答


用loop循环结构，为dept表增加50-90这些部门
答

接收一个雇员名，显示该雇员的所有内容，（用%rowtype实现），当没有这个雇员时（no_data_found），用异常来显示错误提示
答

编写一个PL/SQL程序块以计算某个雇员的年度薪水总额
答

编写一个PL/SQL程序块以向emp表添加10新雇员编号（7901-7910）
答
子主题 3

接受2个数相除，并显示结果，如果除数为0，则显示错误提示;
答

游标
出生日期
—
电话
—
电子邮箱
—
地址
—
函数
出生日期
—
电话
—
电子邮箱
—
地址
—
存储过程
触发器
练习题
子主题 1
子主题 1
子主题 2
子主题 3
子主题 4
子主题 5
子主题 6
事务/管理/备份恢复
事务
出生日期
—
电话
—
电子邮箱
—
地址
—
用户管理
出生日期
—
电话
—
电子邮箱
—
地址
—
备份恢复
出生日期
—
电话
—
电子邮箱
—
地址
—
设计范式/设计工具
设计范式
第一范式
字段要设计的不可再分
第二范式
两个表的关系，在第三张关系表中体现
多对多的关系表，这种关系需要在第三张表中体现
第三范式
多张表中，只存关系，不存具体信息
如果一对多用第三张关系表来表示，则会出现问题
设计工具
对象关系数据库系统
oracle安装与卸载
安装注意事项
卸载步骤

表信息




工资等级表

@byHY_xiaoxiao 2015/10/01 

emp限制
ename不为空
hiredate默认当前系统时间
数据库查询
入门语句
入门语句
SQL简介
查询基础
基本关键字
算术表达式+-*/
连接运算符||
字段别名as
空值 is null
去除重复行 distinct
查询结果排序 order by asc(desc)
比较运算符 > < (!=  or  <>) between and
in 操作  not in
模糊查询 like
逻辑运算符   or and not
练习题
—
查询部门30中所有员工的信息
找出奖金高于工资的员工
找出每个员工工资和奖金的总和
找出部门10中的经理(manager)和部门20中的普通员工(clerk)
找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工
二
找出有奖金的员工的不同工作
找出没有奖金或者奖金低于500的员工
显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
单行函数
字符函数
字符函数
Trim
Rpad
Lpad
Instr
Replace
Length
Substr
Lower
Upper
Initcap
Concat
数值函数
Round
Mod
Trunc
日期函数
Months_between
Add_months
Next_day
Last_day
转换函数
to_char
to_date
to_number
通用函数
NVL
NULLIF
NVL2
COALESCE
CASE
DECODE
练习题
—
找出每个月倒数第三天受雇的员工
找出25年前雇的员工
所有员工名字前加上Dear,并且名字首字母大写
找出姓名为5个字母的员工
找出姓名中不带R这个字母的员工
二
显示所有员工的姓名的第一个字
显示所有员工，按名字降序排列，若相同，则按工资升序排列
假设一个月有30天，找出所有员工的日薪，不计小数
找到2月份受雇的员工
列出员工加入公司的天数(四舍五入)
三
用case函数列出员工所在的部门,部门号是多少则显示'部门多少',否则显示'其他部门'
用decode函数列出员工所在的部门,部门号是多少则显示'部门多少',否则显示'其他部门'
分组函数
count
共有多少条记录,如果没有数据，则返回0
avg
平均值函数
max
最大值函数
min
最小值函数
sum
总和函数,求的是一列的和,多列直接用+
分组函数与空值
分组函数省略列中的空值,可使用nvl()函数先处理空值
Group by 子句
1.出现在select、order by列表中的字段,有分组函数，那么未在分组函数中的字段需要在group by 中出现
2.不允许在where子句中使用分组函数，建议用having
Having子句
代替where 后面接分组函数
查询1981-05-01后入职,平均薪水大于1200的部门、岗位的平均薪水
练习题
一
分组统计各部门下工资>500的员工的平均工资
统计各部门下平均工资大于500的部门
算出部门30中得到最多奖金的员工的奖金
算出每个职位的员工数和最低工资
二
列出员工表中每个部门的员工数，和部门编号
得到工资大于自己部门的平均工资的员工信息
分组统计每个部门下，每种职位的平均奖金，也要算没奖金的人，和总工资(包括奖金)
算出每个部门，每个职位的平均工资和平均奖金(平均值包括没有奖金),如果平均奖金大于300,显示"奖金不错",如果平均奖金100到300,显示'奖金一般',如果平均奖金小于100,显示基本没有奖金，按部门编号降序，平均工资降序排列
多表查询
笛卡尔集
不加入关联条件的查询,结果是多张表的记录条数的积
等值连接
用关联条件把两张表关联起来
非等值连接
自连接
发生在一张表内，一张主做主表，别一张同样的表作虚表为从表
内连接
等同于等值连接
左外连接
在内连接的基础上会多查询出主表中未关联上的数据
右外连接
在内连接的基础上会多查询出从表中未关联上的数据
全外连接
在内连接的基础上会多查询出主从表中都未关联 的数据
集合操作
Union 并集
所有的内容都查询，重复的显示一次,即会去除重复的记录
Union All 并集
所有的内容都显示，包括重复的
Intersect  交集
只显示重复的
Minus 差集
只显示对方没有的
子查询
单列单行
子查询未返回任何结果，则主查询也不会返回任何结果

子查询返回的是一个具体的值
查询比员工7566工资高的员工信息
查询和smith的工作相同的员工信息
单列多行

子查询返回的是一列数据的多个值
查询薪水高于每个部门的平均薪水的员工 
查询薪水高于任意部门的平均薪水的员工
查询 和 MARTIN 或者 SMITH 岗位相同的员工
查询和部门10工作岗位相同的雇员信息
查询工资比部门30的所有员工的工资高的员工信息
查询工资比部门30的任意一个员工的工资高的员工信息
单行多列

返回的是一行，多列的数据
查询和smith的部门和岗位完全相同的所有雇员
多行多列

返回的多行，多列的数据
查询高于自己部门平均工资的员工信息
分页查询 ROWNUM函数
rownum 函数直接使用直接查询1开始的数据，数据中必须包括从1开始的数据 如下:
select * from emp where rownum=1 or rownum=2; --非法
select * from (select * from emp order by sal ) where rownum<=5;--正常
select * from emp where rownum<=5;--正常
常见的分页sql
先查询出需要的数据进行排序,再进行rownum 发号，取需要的记录段
exists
练习题
一
列出员工表中每个部门的员工数和部门号
列出员工表中每个部门的员工数(员工数必须大于3) ,和部门名称
找出工资比jones多的员工
列出所有员工的姓名和其上级的姓名
以职位分组，找出平均工资最高的两种职位
二
查出不在部门20,且比部门20中任何一个人工资都高的员工姓名、部门名称
得到平均工资大于2000的工作职种
分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500
得到每个月工资总数最小的那个部门的部门编号，部门名称，部门位置
分部门得到平均工资等级为2级(等级表) 的部门编号
三
查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字,部门名字，部门位置
查找出收入(工资奖金) 下级比自己上级还高的员工的编号，员工名字，员工收入
查找出工资等级不为4级的员工的员工名字，部门名字，部门位置
查找出职位和 'MARTIN' 或者 'SMITH' 一样的员工的平均工资
查找出不属于任何部门的员工
四
按部门统计员工数，查处员工数最多的部门的第二名到第五名(列出部门名字，部门位置)
查询出king所在部门的部门号\部门名称\部门人数
查询出king所在部门的工作年限最大的员工名字
查询出工资成本最高的部门的部门号和部门名称
高级查询
随机返回emp表中5条记录dbms_random.value()
处理空值排序
Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
查询跳过表中的偶数行
语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
语法:ROW_NUMBER() OVER(ORDER BY COLUMN)
row_number() 从1开始，为每条分组记录返回一个数字，order by column 表示按哪一列进行排序,partition by column 表示按哪一列进行分组, 意思是 按分组、排序后，再给每条分组记录返回一个数字，从1开始  就得到了后图中的数据

数据源:

预期结果

SQL脚本
查询每个部门的员工工资及其所在部门的最高工资，最低工资，平均薪资
连接求和
sum(sal) over(order by ename) 指的是连续求和，是以ename来排序的.若有多个这样的窗口函数，以后面的排序为主

SQL脚本
分部门连续求和
sum(sal) over(partition by deptno order by ename)

SQL脚本
得到当前行上一行或者下一行的数据
lead(sal) over(order by sal) 上一行
lag(sal) over(order by sal)  下一行



select ename,sal,lead(sal) over(order by sal) aaa,lag(sal) over(order by sal) bbb from emp;
确定一年内的天数
trunc函数
日期 trunc(date[,fmt])
trunc(sysdate,'yyyy') --返回当年第一天
trunc(sysdate,'mm') --返回当月第一天
trunc(sysdate,'d') --返回当前星期的第一天
数字 trunc(number[,decimals])
保留指定位置的小数，对后面的部分直接舍去
select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual;
查询EMP员工表下每个部门工资前二名的员工信息

SQL脚本:
找出选了所有课程的学生信息

SQL脚本:
面试题
面试题表信息











删除表中重复记录








rq                 shengfu





面试题查询信息
用一条sql 语句算出商品A、B 目前还剩多少 
1.找出忘记填写性别的员工
查询统计出各年龄中：表中文化程度四个类别各有多少人,占总人口百分比
1.查找陈红老师教的学生有哪些
2.找学生小明所有的文科老师
3.找出没有选修陈红老师的学生
4.教的学生最少的老师
--非常经典的面试题 id是主键唯一，重复记录其他都一样
查询出每门课都大于80分的学生姓名
查出该表中一个FID对应多个不同的Fno的记录
假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人： fsalary>9999 and fage > 35           fsalary>9999 and fage < 35 fsalary<9999 and fage > 35                fsalary<9999 and fage < 35                     每种员工的数量；
如果要生成下列结果, 该如何写sql语句?
  时间           胜   负
2005-05-09  2     2
2005-05-10  1     2


求table3图示的数据,即求各个月份的业绩
扩展：求各个季度的业绩
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。

数据字典
9.1 查询某用户下所有表
9.2 查询EMP表中所有字段
9.3 列出表的索引列
9.4 列出表中约束
9.5 在oracle中描述数据字典视图
数据类型
CHAR
varchar
varchar2
nvarchar2
date
blob(binary 二进制流的大对象)
clob(文件大对象)
1.char是固定长度的，速度比varcar2快得多，但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉
2.varchar2 一般适用于英文和数字，Nvarchar2适用中文和其他字符,N表示unicode常量，可解决多语言字符集间的转换问题
3.Number默认38位
体系结构 DBA
数据库 Database
物理存储结构
Desc v$logfile;  日志文件
Select member from v$logfile;
V$controlfile; 控制文件
V$datafile;  数据文件
逻辑存储结构
表空间
段
区
块
数据库实例 Database Instance
oracle内存结构和后台进程被称为数据库的实例
oracle实时应用集群
数据库服务名
网络服务名
监听器
改变表结构 DDL
12.1创建表
—
12.2使用子查询创建表又叫复制表
—
12.3字段的操作
alter table 表名   add/drop/modify 字段
添加字段
修改字段
删除字段
12.6 清空表中数据
12.7 删除表
—
12.8重命名表
练习题:
子主题 1
scott下面创建一个表  emp1    empno      number(10) ename   varchar2(50)
添加一个字段
sal    number(10,2)
修改字段
删除字段
把表emp1改名为emp2
删除表 emp2
创建一个和emp结构一样的表emp3，并同时插入工资大于1000的数据
清空emp3表的数据(用truncate)
ename varchar(100)
sal
改变数据结构 DML
insert
表间数据拷贝，即复制某表的部分数据到另一张表
update
将编号为7779用户的工作和所属上级换成编号为7566的雇员的工作和所属上级
delete
—
merge
MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。
练习题:
子主题 1
往emp表中插入empno,ename,sal 数据 (111,'1',1000)    (222,'2'.2000)
把empno=111的员工comm改成100
往dept表中再插入dept表中deptno=100的数据
删除empno=111的数据
为所有人长工资，标准是 :         
10号部门涨10% 
20号部门涨15%   
30号部门涨20%
其他部门长18%
根据工作年限长工资，标准是，为公司工作了几个月就涨几个百分点
约束/视图/索引/序列/同义词
约束
约束就是指对插入数据的各种限制，可以对数据库中的数据进行保护，可以在建表的时候直接申明，也可以为已建好的表添加约束
非空约束力 NOT NULL   
不能为空 name VARCHAR(30) NOT NULL
主键约束 PRIMARY KEY
不能重复，不能为空  pid NUMBER PRIMARY KEY
ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid);
唯一约束  UNIQUE
值不能重复,空值除外   tel VARCHAR(50) UNIQUE
ALTER TABLE person ADD CONSTRAINT person_tel_uk UNIQUE(tel);
条件约束 CHECK
插入的数据必须满足某些条件  age NUMBER CHECK(age BETWEEN 0 AND 150)
ALTER TABLE person ADD CONSTRAINT person_age_ck  CHECK(age BETWEEN 0 AND 150)
外键约束  FOREIGN KEY
某一列指向另一张表的主键
本表与另一张表是多对一或一对一的关系
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid)  REFERENCES person(pid) ON DELETE CASCADE;
级联删除
加入  ON DELETE CASCADE 
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
删除约束
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;
启用约束
ALTER TABLE book enable CONSTRAINT person_book_pid_fk;
禁用约束
ALTER TABLE book disable CONSTRAINT person_book_pid_fk;
练习题 : 
子主题 1
创建一张表 student
id        number     name  varchar2(10)   age       number         tel        varchar2(10) 
创建一张学员兴趣爱好表 hobby
id number(10)
hobby_name varchar2(10)
sid  number --学生的id
子主题 2
创建表并
给id 字段添加主键约束
给name添加非空约束
给age字段添加check约束(age必须大于18岁)
给tel添加唯一非空约束
答案
创建表并
给sid字段添加外键约束,指向student表的id,并且要带级联删除
删除掉student 表中tel的唯一约束(先写出查看该表约束的sql)
答案
查约束
删除约束
手动添加student表中tel字段的唯一约束
约束名为:my_constraint_1
答案
禁用约束  MY_CONSTRAINT_1
答案
启用约束MY_CONSTRAINT_1
答案
视图
一个封装了各种复杂查询的语句
每次查询视图，都会执行一次sql查询语句，而不是直接获得数据
默认情况下，创建的视图，如果基表数据更新了，则会更新视图中的数据
创建视图
create view view_名字 (字段)  AS 子查询
建立一个只包含20部门雇员信息的视图(雇员编号、姓名、工资)
将一个较复杂的语句包装成视图
显示部门内最低工资比20部门最低工资还要高的部门的编号及部门内最低工资,并包装成视图
高级视图
删除视图
创建并替换
视图中的数据最好不要更新
保护视图的创建规则
WITH CHECK OPTION
只读，不可修改
with read only 
查看视图
select TEXT from 视图名
索引
什么是索引
1.一种用于提升查询效率的数据库对象
2.通过快速定位数据的方法，减少硬盘I/O操作
3.索引信息与表独立存放
4.Oracle数据库自动使用和维护索引
创建索引的目的
1.维护被索引列的唯一性
2.提供快速访问表中数据的策略
索引查询
查询现有的索引
查询索引建立在哪些字段上
索引分类
唯一性索引
非唯一性索引
创建索引
自动创建
在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引
手动创建
用户可以在其它列上创建非唯一的索引，以加速查询
建立索引的时候，字段的组合顺序是非常重要的。一般情况一，需要经常访问的字段放在组合字段的前面
create index abc on student (sid,sname);
索引abc对select * from student where sid=1 ; 这样的查询语句更有效
create index abc1 on student (sname,sid);
索引abc1对select * from student where sname='louis';  这样的查询语句更有效
删除索引
drop Index pk_dept1;
索引的存储
索引和表都是独立存在的
在为索引指定表空间的时候，不要将被索引的表和索引指向同一个表空间，这样可以避免产生IO冲突
使用Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好 的提高查询速度 
索引类型
B 树索引  B-Tree Index
创建索引的默认类型，结构是一颗树，采用的是平衡 B 树算法 
右子树节点的键值大于等于父节点的键值
左子树节点的键值小于等于父节点的键值

位图索引 BitMap Index
如果表中的某些字段取值范围比较小，如职员性别、分数列ABC级,这样的字段建立B树索引没有意义，不能提高检索速度
Create BitMap Index student on(sex);
索引优缺点
优点
1.大大加快数据的检索速度
2.创建唯一性索引，保证数据库中每一行数据的唯一性
3.加速表和表之间的连接
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
缺点
1.索引需要物理空间
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据维护速度
创建索引的原则
1.在select操作占大部分的表上创建索引
2.在where中出现最频繁的列上创建索引
3.在选择性高的列上创建索引
4.复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列
5.小于5M的表，最好不要使用索引来查询，表越不，越适合用全表扫描
使用索引的原则
1.查询结果是所有数据行的5%以下时，使用index查询效果最好
2.where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引
3.索引利于select,但对经常insert,delete尤其update的表，会降低效率
emp表的deptno列上建有unique index
A:select dname,deptno from dept where deptno not in (select deptno from emp);
B:select dname,deptno from dept where not exists (select deptno from emp where dept.deptno=emp.deptno);
B效率要比A效率高，A会对整个emp表进行扫描，B会在子查询中使用联合查询，只是对emp表进行部分数据扫描，并会用deptno列的索引 
4.where 子句中的这个字段，必须是复合索引的第一个字段
一个索引是按f1 f2 f3 的次序建立的，若  where f2=:var2,则无法使用索引
where 子句中的这个字段，不应该参与任何形式的计算，任何对列的操作都将导致表扫描,包括数据库函数、计算表达式等
查询时要尽可能将操作移到等号右边
应尽量熟悉各种操作符对于Oracle是否使用索引的影响，以下操作会显式阻止Oracle使用索引
is null        is not null
not in    !=       like
to_char    to_date    to_number
select * from emp where to_date(hiredate)>=to_date('2001-7-18','yyyy-mm-dd');
列的索引也不会生效
管理索引
1.先插入数据后创建索引
2.设置合理的索引列顺序
3.限制每个表索引的数量
4.删除不必要的索引
5.为每个索引指定表空间
6.经常做insert,delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片
要停应用，以保持数据一致性，不实用
7.有索引的最好定期rebuild索引，rebuild期间只允许表的select操作，可以数据库较空闲时间提交，以降低索引碎片，提高效率
索引题目
问
针对一个表的查询语句能否会用到两个索引?
如果能用到，那么其实现原理是怎样的？
效率如何，其代价如何，比如额外开销等
答
可以同时用到两个索引
select * from t where x=1 and y=2;
索引是以独立于表存在的一种数据库对象，它是对基表的一种排序（默认B树索引就是二叉树的排序方式）
如在表t 上(x,y,z)分别建立了索引(index1,index2,index3),那么在查询select * from t where x=1 and y=2 时,会分别用到index1,index2
原理是先到index1索引表中查到符合x=1条件的记录，然后到index2索引表中查到y=2条件的记录
查询效率肯定是大于没有索引情况的全表扫描,但有两个问题
问题一：建立索引将占用额外的数据库空间，更重要的是增删改操作的时候，索引的排序也必须改变，加大的维护的成本
问题二：建立经常查询x=?和y=?,那推荐组合是index(x,y),这种情况下组合索引的效率是远高于两个单独的索引的
那在查询条件中出现x,xy,xyz,yzx都是可以用到该组合索引的，但y,yz,z是不能用到该索引的，即必须包含组合索引第一列
序列
序列的使用
创建序列
Create SEQUENCE 序列名    START WITH  1   INCREMENT BY  1 ORDER  CACHE  20  NOCYCLE; 
序列使用
select 序列名.nextval from dual;
下一个值,序列真正的使用
select 序列名.currval from dual;
查看当前值,必须先有nextval,才能currval
Cycle  轮回
create sequence XX start with 1 increment by 1 cycle maxvalue 3 nocache
到了3又会从1开始
Cache 缓存
如果指定cache值 ,oracle就可以预先在内存里面放置一些sequence,这样存取的快些,cache里面的取完后，oracle自动再取一组到cache.
使用cache或者会跳号，比如数据库不正常down掉，cache中的sequence就会丢失
不能改变当前值，但是可以改变增量
alter sequence 序列名 increment by 3;
同义词
同义词的体现：
任何一个用户下，都可以直接访问dual,而不需要加上前缀的用户名
dual其实是sys用户下的一张表
同义词的作用：
很方便的操作不同用户下的对象
能使两个应用程序使用不同的名字指向同一张表
使用不同的用户指向同一张表
创建同义词
create SYNONYM dept for soctt.dept ;  
这样创建的的同义词才是私有的，只有创建者才能用
drop synonym dept;
Create public synonym dept for soctt.dept;
这样创建的同义词才是公有的
drop public synonym dept;
练习题 : 
视图题
创建一个包含1982年3月31日以后入职的所有雇员的视图
创建一个包含佣金高于其薪金的雇员视图
创建一个包含所有雇员的雇员编号、雇员名称、部门名称和薪金的视图
创建一个有手下的雇员的视图，包括雇员编号，雇员名称
创建一个包含各种工作的薪金总和的视图
序列题
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
创建一个序列，从50开始，每次增加10
同义词题
为emp表创建一个同义词emp1
在上面创建的同义词中插入值，并观察对基表的影响
索引题
在emp表的empno字段上创建一个索引，并检查是否可以创建
在emp表的sal上创建一个索引
综合题
列出您所创建的全部视图、同义词、序列和索引
删除你所创建的任何视图的基表，然后尝试查询视图，并观察查询的输出情况
删除您创建的所有视图、同义词、序列和索引
SQL优化
例：删除一张表的重复记录(ID是自增唯一主键，重复记录，其他字段都一样),数据量很大，性能要求很高

做法一
delete from t where id not in (selecte min(id) from t group by name,age)
做法二
delete from t where id in(selete a.id from t a1,t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age)
做法三
delete from t a1 where not exists (selete * from t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age)
数据量>100,0000 以上三种做法，均可，第三种性能最佳. 二快于一
SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充分利用索引,执行过程中访问尽量少的数据块，减少表扫描的I/O次数，尽量避免全表扫描和其他额外开销
oracle数据库常用的两种优化器
RBO  rule-based-optimizer
在RBO方式下，Oracle会根据自己内部设置的一些规则来决定选择计划，例如:Oracle会根据以下优先级来选择执行计划（越靠前，rank越氏，越快）
CBO  cost-based-optimizer
目前更多的采用，基于此种基于开销的优化器
在CBO方式下，Oracle会根据表及索引的状态信息来选择计划；
优化1：尽量少用IN操作符
基本上所有的IN操作符都可以用EXISTS代替，在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑
优化2：尽量用NOT EXISTS或者外连接替代NOT IN操作符
因为NOT IN 不能应用表的索引
优化3 : 尽量不用"<>"或者"!="操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描
优化4 : 在设计表时，把索引列设置为NOT NULL
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的
优化5 : 尽量不用通配符 "%" 或者 "_" 作为查询字符串的第一个字符
当用这两个通配符时，索引不会被使用,产生全表扫描
优化6 : where 子句中避免在索引列上使用计算 
如果索引不是基于函数，那么当在Where子句中对索引列使用函数时，索引不再起作用
如:substr(no,1,4)='5400'  优化处理: no like '5400%'
如:trunc(hiredate)=trunc(sysdate) , 优化处理： hiredate>=trunc(sysdate) and hiredate <trunc(sysdate+1)
优化7 : 用">=" 替代 ">
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，
而如果数据量很大的时候，如>2和 >=3差别就很大了
优化8 : 利用SGA 共享池,避开parse阶段
ORACLE 共享内存SGA的原理，可以得出ORACLE对每个SQL都会对其进行一次分析，并且占用共享内存,如果将SQL的字符串及格式写得完全相同则oracle只会分析一次,共享内存也只会留下一次的分析结果,  可以减少分析sql的时间，而且可以减少共享内在重复的信息，oracle也可以准确统计sql的执行频率
不同区域出现的相同的Sql语句要保证查询字符完全相同，建议经常使用变量来代替常量，以尽量使用重复sql代码，以利用SGA共享池，避开parse阶段，防止相同的Sql语句被多次分析，提高执行速度。
因此使用存储过程，是一种很有效的提高share pool共享率，跳过parse阶段，提高效率的办法。
优化9： where后面的条件顺序要求,where后面的条件，表连接语句写在最前，可以过滤掉最大数量记录的条件居后
优化10 : 使用表的别名，并将之作为每列的前缀
当在sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀，这样可以减少解析时间
优化11 : 进行了显式或隐式的运算的字段不能进行索引
如 : ss_df+20>50 优化处理：ss_df>30
如 : 'X'||hbs_bh>'X5400021452'  优化处理：hbs_bh>'5400021452'
如 : sk_rq+5=sysdate,优化处理: sk_rq=sysdate-5
hbs_bh=5401002554 优化处理：hbs_bh='5401002554' 注，hbs_bh是字符型，直接给数字会进行to_number转换
优化12:用Union all 代替 Union
unoin 会去掉结果集中的重复记录，所以oracle会进行sort unique操作(与使用distinct时操作类似)，如果结果集较大，则操作会比较慢.如果数据本身重复行较少，则用unoin all会比用union效率高很多 
其他优化：
1.尽量使用packages
packages 在第一次调用时能将整个包load进内存，对提高性能有帮助
2.尽量使用cached sequences 来生成primary key
提高主键生成速度和使用性能
3.很好的利用空间
如用varchar2数据类型代替char
4.使用Sql优化工具
sqlexpert;toad;explain-table;pl/sql;oem
SGA 数据库的系统全局区
共享池
共享SQL区
专门存放用户SQL命令，oracle使用最近最少使用等优先级算法来更新覆盖
数据字典缓冲区
存放数据库库运行的动态信息
数据缓冲区
存放sql运行结果抓取到的data block
日志缓冲区
存放数据库运行生成的日志
PL/SQL
pl/sql块
PL/SQL块
—
IF
—
LOOP
—
WHILE
练习题 : 
子主题 1
输入一个雇员的编号
如果其工资高于3500，则显示高工资
工资大于2000，则显示中等工资
输入一个雇员编号，根据它所在的部门涨工资，规则：


• 10部门上涨10%
• 20部门上涨20%
• 30部门上涨30%
所有部门的上涨工资，最不能超过5000，如果超过5000，则工资就为5000。
一答
二答

接收部门编号，显示部门名和地理位置

答


接收雇员号，显示 该雇员的所有信息，没有提成的用0替代。（用%rowtype实现）
答

接收雇员号，显示 该雇员的工资和提成，没有提成的用0替代。（用%type实现）
答

子主题 2

接收一个雇员名，判断他的job，根据job不同，为他增加相应的sal（用if-elsif实现）
答


用loop循环结构，为dept表增加50-90这些部门
答

接收一个雇员名，显示该雇员的所有内容，（用%rowtype实现），当没有这个雇员时（no_data_found），用异常来显示错误提示
答

编写一个PL/SQL程序块以计算某个雇员的年度薪水总额
答

编写一个PL/SQL程序块以向emp表添加10新雇员编号（7901-7910）
答
子主题 3

接受2个数相除，并显示结果，如果除数为0，则显示错误提示;
答

游标
出生日期
—
电话
—
电子邮箱
—
地址
—
函数
出生日期
—
电话
—
电子邮箱
—
地址
—
存储过程
触发器
练习题
子主题 1
子主题 1
子主题 2
子主题 3
子主题 4
子主题 5
子主题 6
事务/管理/备份恢复
事务
出生日期
—
电话
—
电子邮箱
—
地址
—
用户管理
出生日期
—
电话
—
电子邮箱
—
地址
—
备份恢复
出生日期
—
电话
—
电子邮箱
—
地址
—
设计范式/设计工具
设计范式
第一范式
字段要设计的不可再分
第二范式
两个表的关系，在第三张关系表中体现
多对多的关系表，这种关系需要在第三张表中体现
第三范式
多张表中，只存关系，不存具体信息
如果一对多用第三张关系表来表示，则会出现问题
设计工具
对象关系数据库系统
oracle安装与卸载
安装注意事项
卸载步骤

表信息




工资等级表

@byHY_xiaoxiao 2015/10/01 


emp限制
ename不为空
hiredate默认当前系统时间