Oracle总结

1.
这里是以scott数据库做实验
set timing on打开显示时间 可以看到查询的效率 select * from emp;与显示select ENAME,JOB from emp;效率更低
（本人有个疑问就是我在插入65536数据时）
create table users(
id int,
name varchar(20),
age int
)
insert into user(id,name,age) select * from user;不断的复制的插入
insert into users(id,name,age) values(1,'fushaogao',12);
insert into users(id,name,age)select[flash=200,200][/flash] * from users
查询select * from users;用了00:01:09.36    00:04:01.46                
    select id,name,age from users;用了00:02:11.75  00:03:52.48       
    select name from users; 用了00:02:46.56怎么回事00:03:18.34

2.desc 表名;
select * from emp; 00:00:00.07
select deptno from emp;  0:00:00.07
empno,ename,job,mgr,hiredate,sal,comm,

3.取消重复行select distinct deptno,job from emp; 将重复的过滤掉

4 select * from emp where ename='aa' ;
  select * from emp where ename='AA' ;是不同的

5.使用算术表达式
查处emp表中的员工年工资(comm是奖金)(nvl(comm,o)时处理null值，当为null时就变成0) Oracle本事当有一个为null时就会全部为null
select sal*12+nvl(comm,o)*12 "年工资",ename from emp; 


6.使用where语句
查出emp表中入职时间在1982,1,1日后入职的员工
select ename,hiredate from emp where hiredate>'1-1月-1982'

7.使用like操作符
%：表示任意0到多个字符
_表示单个字符
比如显示第三个字符为大写O的所有员工的名字和薪资
select ename,sal from emp where ename like '__O%'(注意这里是两个下划线)

8. 在where中使用in
select * from emp where empno in (123,234,456);这种时批量查询的，速度极快

9. is null 的用法
select * from emp where mgr is null(没有上级的员工查出来) 不要想当然的认为是
select * from emp where mgr=null；
select * from emp where mgr= ''都是查不到的


10.使用逻辑操作符(查找工资高于500或职位为经理)同时姓名第一个一定要以J开头
select * from emp where (sal>500 or job='manage') and ename like 'J%';

11.order by 字段 按照某个字段排序 默认是升序，要想降序就加上一个desc
如按照部门号升序，而雇员的工资降序排列
select * from emp order by deptno (asc),sal desc;
使用列的别名排序 按员工的年薪排名
select ename,sal*12+nvl(comm,0)*12  "年薪" from emp order by "年薪";

二：Oracle复杂查询
1.数据分组max，min，avg,1.分组函数一定是放在选择列中，或having group by,order by字句中
                        2.having group by order by 的顺序一定是group by,having order by
                        3.在选择列中如果有列，表达式，分组函数，那些列和表达式必须有一个在group by 字句中否则会出错如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
select max(sal) "最高薪资",min(sal) "最低薪资" from emp;


oracle 多表查询基于两个或两个以上的表或视图的查询，查询单张表不能满足要求
笛卡尔集多表查询的条件至少不能少于表的个数-1
例子：显示雇员名称，雇员工资，部门名称
select  a.dname,a2.sal,a2.ename from dept a1,emp a2 where a1.deptno=a2.deptno
    显示10号部门的雇员名称，雇员工资，部门名称
select  a1.ename,a2.dname,a1.sal from emp a1, dept a2 where a1.deptno=a2.deptno and a1.deptno=10;
显示各个员工的名称，工资及工资的级别
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
显示雇员名，雇员工资及所在部门的名字，并按部门排序
select a1.ename,a1.sal,a2.dname  from emp a1,dept a2 where a1.deptno=a2.deptno and group by a1.deptno;


select avg(a1.sal) from emp a1,dept a2 where a1.deptno=a2.deptno group by a1.sal ;
select count(a1.sal) from emp a1,dept a2 where a1.deptno=a2.deptno group by a1.sal ;
第一个能出来而第二个不能出来，因为count(a1.sal)得到的不能匹配这么多的行
单行子查询：查询的结果只返回一条子查询语句
多行子查询：查询的结果返回多条子查询in all any some
如：显示工资比部门30的所有员工的工资高的员工姓名，工资和部门号
select ename,sal,dept from emp where sal>all(select sal from emp where deptno=30);



%%%%%%%%%%%%%%%%%%%%%%%如何显示高于自己部门平均工资的员工信息%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
首先查询各个部门的平均工资和部门号 select deptno ,avg(sal) mysal from emp group by deptno
把上面的查询作为一张子表 给他取个名字为a2
select a2.ename,a2.sal,a2.deptno a1.sal from emp a1,(select deptno ,avg(sal) mysal from emp group by deptno)a2 where a1.deptno=a2.deptno and a1.sal>al.mysal;(子查询当做一个表查询就是a2表就是内嵌视图)
(总结在from字句中使用子查询时该子查询会被作为一个视图来对待，因此叫着内嵌视图，当from字句中使用子查询必须为它指定别名，但是不能用as，它只能用在列上)

oracle的三部分页方式
1。
首先select * from emp
再次select a1.*,rownum rn from (select * from emp)a1;oracle为每一行指定的行号
再后select a1.*,rownum  rn from (select * from emp)a1 where rowum<=10;(select a1.*,rownum rn from (select * from emp)a1 where rowum<=10 and rowum>6不行，它采用了二分制)
最后select  * from (select a1.*,rownum rn from (select * from emp)a1 where rownum<=10) a2 where rn>6

orcle用查询的结果创建新表
这个命令是一种快捷建表方式create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
希望员工scott的岗位，工资，补助与Smith一样
update emp set(job,sal.comm)=(select job,sal.comm from emp where sname='smith') where ename='scott';
oracle合并查询，为了合并多个select语句的结果，可以使用集合操作符号union,union all，intersect,minus
(1)union 该操作符表示的是取两者的并集，当有重复的语句只会取一次，
(2)union 该操作符表示的是取两者的并集，当有重复的话有多少重复的就取多少条
(3)intersect该操作符石取两者的交集
(4)minus是取差集，用大的集合减去小的集合或小的集合减去大的集合
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='manager';
这种速度快

Oracle与java连接
一.jdbc-odbc桥连接
1.加载驱动Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
2.得到连接Connect ct=DriverManager.getConnection("jdbc:odbc:你配置的数据源",用户名,密码);
配置方法控制面板-管理工具-数据源-添加-Oracle in OracleDb10g_home1 为它取个名字放在上面的数据源
3.Statement sm=ct.createStatement();
4.ResultSet rs=sm.executeQuery("select * from emp") ;
5.while(rs.next()){
system.out.println("用户名"+rs.getString("2"));默认是从一开始
}

二jdbc连接Oracle
1.加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");
2.得到连接Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:oracle实例orcl","scott","tiger");

Oracle使用to_date()就可以插入你想插入的任何格式to_date('1988-12-12','YYYY-mm-dd');



三ORALCE的事务：
1.事务是为了保持数据的一致性，它是由一组相关的dml语句(增删改语句)要么一起成功，要么一起失败(如网上转账)工行减10元，农行加10元
具有原子性，不可分割性，一致性，要么一起成功，要么一起失败
2.事务和锁，当多个用户访问同一个程序时，就必须加锁，当执行事务操作时dml语句Oracle会被作用的表上加锁，防止其他用户改表的结构，
当程序运行时进入表中，它就会寻找锁，并拿到它，除非有别的程序拿到了锁，只有等到锁被释放，当然锁也有级别，当级别高的话就可以先拿到锁
3.提交事务
4.回滚事务rollback to a1   a1为设置的保存点 Savapoint a2; 取消部分事务rollback to a2; 取消全部事务rollback

Java程序中如何使用事务使用try catch

四：1.只读事务：只允许查询的操作，而不允许 执行任何其他dml操作事务，使用只读事务可以保证用户取得某时间点的数据，假设机票代售点每天18点开始统计今天的销售情况，这是可以使用只读事务，尽管其他事物会提交新的事务，但是只读事务将不会取得新数据的变化，从而保证取得特定时间点的信息
set transaction read only


五：Oracle的sql函数的使用
一.字符函数
1.lower(char)
2.upper(char)
3.length(char);
4.substr(char m,n);取字符串的字串
5.replace(char1,search_String,replace_String)
6.instr取字串在字符串中的位置
二：数学函数cos cosh,exp,In,log,sin,sinh,sqrt,tan tanh,acos,asin,attan,round，
1.round(n,[m])（m表示保留的小数位）用于执行四舍五入select round(sal,1) from emp where ename='aa';如果sal=34.34的话得到的是34.3
2.trunc 直接截取掉  select trunc(sal,1) from emp where ename='aa'如果为34.34的话就变成34.3
                    select trunc(sal,-1) from emp where ename='aa'如果为34.34的话就变成30
                    select trunc(sal) from emp where ename='aa'如果为34.34的话就变成34
3.mod(m,n)取模select mod(10,2) from dual; 返回的为0 dual为虚拟的表，用来测试的
4.floor(n)返回小于或等于n的最大整数
5.ceil(n)返回大于或等于n的最小整数
6.abs(n)返回数字n的绝对值select abs(-13) from dual;
7.acos(n)返回数字的反余弦值
8.asin(n)返回数字的反正弦值
9.atan(n)返回数字的反正切值
10.cos(n)
11.exp(n)返回e的n次幂
12.log(m,n)返回对数值
13.power(m,n)返回m的n次幂

三：日期函数
1.sysdate:该函数返回系统日期默认是dd-mon-yy即12-7月-78
2.add_month(d,m)返回的是d+m日期
3.last_day(d)返回指定日期的月份的最后一天
查找员工加入公司的天数select trunc(sysdate-hiredate) "入职天数" from emp(天数啊，大哥)
找出各个月倒数第三天受雇佣的员工select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

四：转化函数将数据类型从一种到另一种，在某种情况下Oracle server允许值的数据类型和实际类型的不一样，这是Oracle会隐含的转化为数据类型
如creat table t1(id int);
insert into t1 values('10');这样Oracle会自动的将'10'转化为10前提是可以转化不能为abc
to_char