该文是本人在学习oracle视频时整理的,MLDN李兴华老师的视频。tangyj

oracle 基本语法总结
1. oracle的总体内容
oracle的安装及配置
sql语法:1 DML 2 DDL 3 DCL
DML:简单查询 ,限制查询,分组查询,多表查询,子查询,更新
DDL :创建管理数据库
DCL:授权及回收
oracle主要用户
sys/change_on_install:超级管理员
system/manager:普通管理员
scott/tiger:普通用户
sqlplusw命令
set linesize 90
set pagesize 80
连接:conn scott/tiger as sysdba
一 基本语法
1 distinct 消除重复
select distinct empno from emp;
2 || 连接字符串
select '编号是:'||empno||'雇员是:'||empname from emp;
3 not null 非空查询
select * form emp where com is not null;
4 not 整体取反
基本工资不大于1500,同时comm为空
select * from emp where not(sal>1500 or comm is not null)
5 between and
日期之间的人员,加‘
select * from emp where hiredate between '1-1月 -81' and '31-12月 -81'
6 like 模糊查询
% 匹配任意长度
—匹配一个长度
select * from emp where ename like '_m%'
7 不等号:<>,|=
条件>,>=,=,<,<=
二 单行函数
1 单行函数分类
字符函数:接收字符输入并且返回字符或数值
数值函数:接收数值输入并返回数值
日期函数:对日期类型进行操作
转换函数:从一种数据类型转换为另一种数据类型
通用函数:NVL函数,DECODE 函数
2 UPPER()
将小写字符转为大写字符
select upper('smith') from dual;
3 LOWER()
select lower('SMITH') from dual;
4 INITCAP()
将单词的第一个字符大写
select initcap('sEEdd DDed') from dual;
5 concat()字符串连接
select concat('hello','word') from dual;
6 substr()
字符串截取
select substr('hello',1,3)
从第1位置开始截取,到第3位置结束
从0和1是一个意思
显示雇员姓名的后3个字符
select substr(ename,length(ename)-2) from emp;
如果参数一个数字,表示从该位置截取到最后。
7 replace('hello','1','x')将hello中的l换为x
length()取长度
8 数值操作
round():四舍五入
TRUNC():截取小数位
MOD():取余
select round(89.222)from dual;
得到89整数
select round(789.549,2) from dual;
保留2位小数四舍五入,789.55
select round(789.534,-2) from dual;
第二个参数是负数,表示对整数四舍五 入,800
select trunc(789.532) from dual;
表示直接截取小数,不会四舍五入。789
select trunc(789.537,2) from dual;
截取小数的2位后,789.53
select trunc(789.532,-2) from dual;
截取整数,700
select mod(10,3) from dual;
去结果进行取余,1
9 日期函数
日期-数字=日期
日期+数字=日期
日期-日期=数字
sysdate表示系统时间
select sysdate from dual;
求出10部门雇员进入公司的星期数
select ename,round((sysdate-hiredate)/7) from emp;
10 oracle中的日期支持函数
MONTHS_BETWEEN():求出给定日期之间的月数
ADD_MONTHS():在指定的日期上加上指定的月数,求出之后的日期
NEXT_DAY():下一个星期几是哪一个日期
LAST_DAY():求出给定日期的最后一天日期
select ename,MONTH_BETWEEN(SYSDATE,HIREDATE)from emp;
结果中会包含小数点,需要用round去掉
select add_months(sysdate,4)from dual;
select next_day(sysdate,'星期一') from dual;
select last_day(sysdate)from dual;
求出当前日期中月的最后一天
11 转换函数
TO_CHAR():转换为字符串
TO_NUMBER():转换为数字
TO_DATE():转换为日期
12 select ename,TO_CHAR(hiredate,'yyyy') year,TO_CHAR(hiredate,'mm') months,TO_CHAR(hiredate,'dd') day
from emp;
单独拿出日期中的年月日显示
select TO_CHAR(hiredate,'yyyy-mm-dd')from emp;
fm的用法
12 select ename,TO_CHAR(SAL,'99,9999')from emp;
按照指定的格式显示sal
select ename,TO_CHAR(sal,'$99,9999');
13 select to_number('22')+to_number('2') from dual;
结果是24
14 select TO_DATE('2009-02-16','YYYY-MM-DD') FROM DUAL;
将字符串按照日期格式转换,但后面的不是转换后的格式,而是字符串的格式,转换后为系统自己的日期格式。
15 NVL ()将null的值转为指定类型
select ename,NVL(COMM,O) FROM EMP;
将comm字段为空的转为0
16 DECODE ()类似于 if elseif else
select ename,DECODE(job,'clerk','1','slaemane','2','mangetr','3') from emp;
函数的意思是查询job字段,如果为clerk显示1,为mangert显示3
17 sign()函数根据表达式的值是0、正数还是负数,分别返回0、1、-1;

select decode(sign(to_number(to_char(sysdate,'MM'))-6),1,'下半年',-1,'上半年',0,'六月') as MM from dual;

18 decode 和case when 比较
SELECT DECODE(OWNER, 'SYSTEM', 'SYSTEM', 'SYS', 'SYSTEM', 'USER') from emp;

6075760 rows selected.

Elapsed: 00:00:07.24

SELECT CASE OWNER WHEN 'SYSTEM' THEN 'SYSTEM'
WHEN 'SYS' THEN 'SYSTEM'
ELSE 'USER' END
FROM emp;

6075760 rows selected.

Elapsed: 00:00:07.22

19 select distinc job from emp where com is not null;
(select * from emp where comm is null or comm<500)
(select * from emp where hidaerate =last_day(hiredate)-2)
早于12年前受雇
select * from emp where ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)>=12
首字母大写
select initcap(ename) from emp;
5个字符员工
select * from emp where length(ename)=5;
显示不带R的员工姓名
select * from emp where ename not like '%R%;
所有员工姓名的前三个字符
select substr(enam,1,3) from emp;
显示员工姓名,用A 代替a
select replace(ename,'a','A') FROM EMP;
显示服务满10年服务员的姓名和雇佣日期
select ename,hiredate from emp where months_between(sysdate,hiredate)>10
显示员工的信息,按姓名排序
select * from emp order by ename;
显示员工姓名和受雇日期,老员工排在前面
select ename,hiredate from emp order by hiredate ;
显示员工姓名工作薪金,按工作降序排序,若工作相同,按薪金排序
select ename,job,sales from emp order by job desc,sales;
显示所有员工的姓名,加入公司的年份和月份,按受雇日期所在月排序,月份相同则最早年份在上面
select ename,to_char(hiredate,'yyyy') y,to_char(hiredate,'mm') m from emp order by m,y;
显示员工的日薪资,忽略余数
select enamel,round(sal/30) from emp;
找出2月受聘的所有员工
select * from emp
where to_char(hiredate,'mm')='2';
显示员工加入公司的天数
select ename,sysdate-hiredate from emp;
以年月日的方式显示所有员工的服务年限
select ename ,trunc(months_between(sysdate,hiredate)/12)year,mod(months_between(sysdate,hiredate),12) month,mod(sysdate-hiredate,30)dd from emp;
多表查询
查询出雇员的姓名,工资,部门名称,工资在公司的等级
select e.ename,e.sal,d,dname,s.gradefrom emp e,dept d,salgrade s

where e.deptno=d.deptno and e.sal between s.local and s.high;




oracle中join和union 的语法总结
1.join
inner join cross join left join right join
其中left join表示 +在等号右边,右边强制有空,right join 表示+在等号左边,左边强制为空。
spacer.gif
spacer.gif
spacer.gif
spacer.gif
spacer.gif
oracle union union all intersect minus

1.union :得到两个结果的并集,并且自动去掉重复行。不会排序
2.union all:得到两个查询结果的并集,不会去掉重复行。不排序
3.intersect:得到两个查询结果的交集,并且按照结果集的第一个列进行排序。
4.ninus:得到两个查询结果的减集,以第一列进行排序

spacer.gif


函数和存储过程的区别
create or replace procedure add_emailinfo(namee email_info.fullname%type ,address email_info.email_address%type )

is

begin

insert into email_info(fullname,email_address) values (namee,address);

end;

//调用过程

call add_emailinfo('cherry','asdfsdf');

/////////////////////////////////////////////////////////////////////////////////////

//创建函数

create or replace function sel_emailinfo(namee email_info.fullname%type )

return varchar2 is

address varchar2(30);

begin

select email_address into address from email_info

where trim(fullname)=trim(namee);

return address;

end ;

//调用函数

select sel_emailinfo('bbb') from dual;

spacer.gif

本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接:http://www.linuxidc.com/Linux/2012-09/70652.htm



1.sql语句处理的四个阶段
open-parse-bind-execute-fetch-close
sql tunning的重点
SQL:insert update delete select
关注select
关注:如何用最小的硬件资源消耗、最少的响应时间定位数据位置
减少服务器资源消耗(磁盘IO)
查询尽量用确定的列名,少用*号。select count(key)from tab where key>0 性能优于select count(*)from tab;
尽量少嵌套子查询,这种查询会消耗大量的cpu资源。对于有比较多的or运算的查询,建议分成多个查询,用union all联结起来。将记录少的放在右面
带有distinct union minus intersect order by的SQL语句会启动sql引擎执行耗费资源的排序功能。
这些功能都可以用别的方式代替
用exists替换distinct
select distinct dept_no,dept_name from dept d,emp e where d.dept_no=e.dept_no;
select dept_no,dept_name from dept d
where exists(select 'x' from emp e where e.dept_no=d.dept_no);
可以的话用union all 替换union

oracle中sql语句的优化(转帖)


一、执行顺序及优化细则

1.表名顺序优化
(1) 基础表放下面,当两表进行关联时数据量少的表的表名放右边
表或视图:
Student_info (30000条数据)
Description_info (30条数据)
select *
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'

select *
from student_info si--学生信息表
,description_info di
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
以student_info作为基础表,你会发现运行的速度会有很大的差距。


(2) 当出现多个表时,关联表被称之为交叉表,交叉表作为基础表
select *
from description_info di
,description_info di2
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and si.school_id = di.lookup_code(+)
and di.lookup_type(+) = 'SCHOOL_ID'

select *
from student_info si--学生信息表
,description_info di
,description_info di2
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and si.school_id = di.lookup_code(+)
and di.lookup_type(+) = 'SCHOOL_ID'
以student_info作为基础表,你会发现运行的速度会有很大的差距,
当基础表放在后面,这样的执行速度会明显快很多。

2.where执行顺序
where执行会从至下往上执行
select *
from student_info si --学生信息表
where si.school_id=10 --学院ID
and si.system_id=100--系ID
摆放where子句时,把能过滤大量数据的条件放在最下边

3. is null 和is not null
当要过滤列为空数据或不为空的数据时使用
select *
from student_info si --学生信息表
where si.school_id is null(当前列中的null为少数时用is not null,否则is null)

4.使用表别名
当查询时出现多个表时,查询时加上别名,
避免出现减少解析的时间字段歧义引起的语法错误。

5. where执行速度比having快
尽可能的使用where代替having
select from student_info si
group by si.student_id
having si.system_id!=100
and si.school_id!=10
(select from student_info si
wehre si.system_id!=100
and si.school_id!=10
group by si.student_id)

6. * 号引起的执行效率
尽量减少使用select * 来进行查询,当你查询使用*,
数据库会进行解析并将*转换为全部列。


二、替代优化
1、用>=替代>
select ui.user_name
from user_info ui--员工信息表
where ui.student_id>=10

select ui.user_name
from user_info ui--员工信息表
where ui.student_id>9
执行时>=会比>执行得要快

2、用UNION替换OR (适用于索引列)
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=10
union
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=2
上面语句可有效避免全表查询
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=10
or ui.student_id=2
如果坚持要用OR, 可以把返回记录最少的索引列写在最前面

3、用in 代替or
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=10
or ui.student_id=20
or ui.student_id=30
改成
select ui.user_name
from user_info ui--员工信息表
where ui.student_id in (10,20,30)
执行会更有效率

4、 Union All 与Union
Union All重复输出两个结果集合中相同记录
如果两个并集中数据都不一样.那么使用Union All 与Union是没有区别的,
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=10
union All
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=2

select ui.user_name
from user_info ui--员工信息表
where ui.student_id=10
union
select ui.user_name
from user_info ui--员工信息表
where ui.student_id=2
但Union All会比Union要执行得快

5、分离表和索引
总是将你的表和索引建立在另外的表空间内
决不要将这些对象存放到SYSTEM表空间里

三、一些优化技巧


1、计算表的记录数时

select count(si.student_id)
from Student_info si(student_id为索引)

select count(*) from Student_info si
执行时.上面的语句明显会比下面没有用索引统计的语句要快

2.使用函数提高SQL执行速度

当出现复杂的查询sql语名,可以考虑使用函数来提高速度
查询学生信息并查询学生(李明)个人信息与的数学成绩排名

select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.description = '李明'

而且我们将上面order_num排名写成一个fuction时
create or replace package body order_num_pkg is
function order_num(p_student_id number) return_number is
v_return_number number;
begin
select res.order_num --排名
into v_return_number
from result res
where res.student_id = di.student_id
order by result_math;
return v_return_number;
exception
when others then
null;
return null;
end;
end order_num_pkg;
执行
select di.description student_name
,order_num_pkg.order_num(di.student_id) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.description = '李明'
执行查询时的速度也会有所提高

3.减少访问数据库的次数

执行次数的减少(当要查询出student_id=100的学生和student_id=20的学生信息时)
select address_id
from student_info si --学生信息表
where si.student_id=100

select address_id
from student_info si --学生信息表
where si.student_id=20
都进行查询.这样的效率是很低的
而进行
(
select si.address_id,si2.address_id
from student_info si --学生信息表
,student_info si2
where si.student_id=100
and si2.student_id=20

select decode(si.student_id,100,address_id)
,decode(si.student_id,20,address_id)
from student_info si
)
执行速度是提高了,但可读性反而差了..
所以这种写法个人并不太推荐

4、用Exists(Not Exists)代替In(Not In)

在执行当中使用Exists或者Not Exists可以高效的进行查询

5、Exists取代Distinct取唯一值的

取出关联表部门对员工时,这时取出员工部门时,出现多条..
select distinct di.dept_name
from departments_info di --部门表
,user_info ui --员工信息表
where ui.dept_no = di.dept_no
可以修改成
select di.dept_name
from departments_info di --部门表
where exists (select 'X'
from user_info ui --员工信息表
where di.dept_no = ui.dept_no)
6、用表连接代替Exists
通过表的关联来代替exists会使执行更有效率
select ui.user_name
from user_info ui--员工信息表
where exists (select 'x '
from departments_info di--部门表
where di.dept_no = ui.dept_no
and ui.dept_cat = 'IT');
执行是比较快,但还可以使用表的连接取得更快的查询效率
select ui.user_name
from departments_info di
,user_info ui --员工信息表
where ui.dept_no = di.dept_no
and ui.department_type_code = 'IT'

代码是经测试并进行优化所写,
以上只例子,具体使用还是要针对各个不同的具体的业务使用用Exists(Not Exists)代替In(Not In)

四、索引篇

1、运算导致的索引失效

select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and si.student_id+0=100/*student_id索引将失效*/

2、类型转换导致的索引失效

select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.student_id='100'

student_id为number类型的索引,当执行下列语句,
oracle会自动转换成
select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.student_id=to_number('100')
所幸,只是解析并转换类型,并没有导到失效,
但要是写成下面,将会使用其失效
select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and to_char(di.student_id)='100'

3、在索引列上进行计算引起的问题

select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.student_id-2=10
在索引列中进行运算,将会不使用索引而使用全表扫描
而将
select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.student_id=10+2
将会得到高效的运行速度

4、 Is not null引起的问题(student_id为索引)

不要把存在空值的列做为索引,否则无法使用索引
select ui.user_name
from user_info ui--员工信息表
where ui.student_id is not null--索引失效

select ui.user_name
from user_info ui--员工信息表
where ui.student_id>=-1--索引有效

5、Order by导致索引失效(student_id为索引)

select ui.user_name
from user_info ui--员工信息表
group by ui.student_id
而使用
select ui.user_name
from user_info ui--员工信息表
where ui.student_id>=-1
将使其有效,
在order by中只存在两种条件下可以使用索引
(ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
ORDER BY中所有的列必须定义为非空. )

6、自动选择索引

如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性.
在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯一性索引.

7、 !=导致索引失效

select ui.user_name
from user_info ui--员工信息表
where ui.student_id!=0
在Where中使用!=将会把索引失效

8、%导致的索引失效

select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.look_code Like '%12'/*look_code为索引,索引将失效*/

select di.description student_name
,(select res.order_num--排名
from result res
where res.student_id = di.student_id
order by result_math) order_num
from description_info di
,student_info si --学生信息表
where si.student_id = di.lookup_code(+)
and di.lookup_type(+) = 'STUDENT_ID'
and di.look_code Like '12%'/*索引有效*/
以上只例子,具体还是要针对各个不同的具体的业务使用

五、oracle 中的not Exists与Not in的性能巨大差异

Not Exists与Not in的作用同样是排除数据,在oracle 中使用not in并不象mysql中的执行那么快,如(
select jt1.doc_num --单据号码
,oalc.description school_name --学校名称
,oalc2.description system_name --系名称
,oalc.description class_name --班级名称
from java_table1 jt1
,java_table_description oalc
,java_table_description oalc2
,java_table_description oalc3
where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
and jt1.school_id = oalc.lookup_code(+)
and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
and jt1.system_id = oalc2.lookup_code(+)
and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
and jt1.class_id = oalc3.lookup_code(+)
and not exists
(select jt2.header_id
from java_table2 jt2 jt1.header_id = jt2.header_id))

select jt1.doc_num --单据号码
,oalc.description school_name --学校名称
,oalc2.description system_name --系名称
,oalc.description class_name --班级名称
from java_table1 jt1
,java_table_description oalc
,java_table_description oalc2
,java_table_description oalc3
where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
and jt1.school_id = oalc.lookup_code(+)
and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
and jt1.system_id = oalc2.lookup_code(+)
and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
and jt1.class_id = oalc3.lookup_code(+)
and jt1.header_id not in (select jt2.header_id from java_table2 jt2)

当jt2表中的数据比较大时,就会出现巨大的差异,以上只能是我的个人理解与测试结果(java_table1 视图测试

数据量为36749,java_table2 为300条),如有其它可相互讨论