Oracle数据库总结

sqlplus scott/dmd@192.168.56.101:1521/orcl
1:查询表select * from 表;
2:设置表单的长度set linesize 变量;
3:设置sal那一列的数值宽度为4位数col sal for 9999;
4:通过列名查询select 列名1,列名2,列名3,...,from 表名;
5:sql优化,尽量用列名来代替*号
6:查询员工的信息:员工号,姓名,月薪,年薪.select 员工号,姓名,月薪,月薪*12 from 表名;
7:查询员工信息：员工号,姓名,月薪,年薪,奖金,年收入 select 员工号,姓名,月薪,月薪*12,奖金,月薪*12+奖金 from 表名
8:如何让一个null变成一个0的数字用nvl  select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp
9:Orcale唯一区别大小写的地方是就是"ADSAH"字符串中的内容.
10:distinct去掉重复记录 select distinct 列名 from 表名; 
11:连接符||和concat 函数  相当于(+) 
12:查询员工信息: ***salary is*** select 列名 || 'salary is' say from emp; 
13:查询日期格式:select * from v$nls_parameters;
14:永久修改日期格式:alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
15:查询薪水位于1000~2000的员工 用between and实现   select * from emp where 列名 between 1000 and 2000; 或者  select * from 表名 where 列名>1000 and 列名<2000;
16:查询部门号在10和20之间的员工 select * from 表名 where 列名 in(10,20);
17:模糊查询查询以%/_% 查询带下划线的数据 select * from 表名 where 列名 like '%\_%' escape '\';  escape表示转译字符串
18:Oracle中 where 解析顺序 右-->左
19:查询员工信息,按照月薪排序  order by ;  select * from 表名 order by sal;(升序)   在后面加desc就是降序;
20:多个列进行排序:select * from 表名 order by 列名,列名,列名; 在后面加上 desc 就是降序  但是desc 只作用于离他最近的一个列名
21: 如果有些值为空的话可以这样:select * from 表名 order by ename,sal ,comm desc nulls last; 表示把空值放到最后面去.






字符函数


22:select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写;
23:从"Hello World" 中的第三位还是截取(包含第三位) 取select substr('Hello World',3) from dual;
24:获取字符数和字节数,select length('Hello World') 字符,lengthb('Hello Wrold') 字节 from dual;
25:lpad 左填充 rpad 右填充 select lpad ('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;
26:去掉前后指定字符,trim select trim('H' from 'Hello WorldH') from dual;获得的值是elloworld
27:替换 replace ;select replace ('Hello World','l','*') from dual;用*去代替l;
28:四舍五入 select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2) from dual; 第一个是45.93,第二个是45.9,第三个是46,第四个是50,第五个是0;
29:获取当前时间:select sysdate from dual;
30:改时间格式,select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') from dual;(2014-04-07 15:37:15:586000);
31:显示--昨天 今天  明天  select (sysdate-1) 昨天,sysdate 今天 ,(sysdate+1) 明天 from dual;
32:用一个有误差的方法计算员工的工龄:天  星期 月  年 select 员工,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365年 from emp;
33:精确 select 列名,hiredate,months_between(sysdate,hiredate) from emp;
34:这个月的最后一天select last_day(sysdate) from dual;
35:获得下一个星期一select next_day(sysdate,'星期一') from dual;
36:对日期进行四舍五入,select round(sysdate,'month'),round(sysdate,'year') from dual;
37:字符串转换成日期,select to_char(sysdate.'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
38:获得两位小数点,千位符,本地货币代码  select to_char(sal,'L9',999,99) from emp;
39:通用函数如果comm=null的时候,返回0,否则返回第二个comm select sal*12+nvl12(comm,comm,0) from emp;
40:当abc=b时候,返回null,否则就返回abc   是select nullif('abc',b) from dual
41:从左到右 找到第一个不为null的值  select 列名1,列名2, coalesec(列名1,列名2) from emp;


条件表达式: ---涨工资,总裁1000  经理800 其他400
42:--条件表达式:两种 select 列名,列名,列名 涨前,case 列名 when '列名(总裁)' then 列名+1000
 when '列名(经理)'  then sal+800
 else 列名+400 end 涨后 from emp;
43:第二种: select 列名,列名,列名 涨前, decode(列名,'列名(总裁)',列名+1000
  '列名(经理)'列名+800
列名+400) 涨后 from emp;\






//多行函数


44:工资总额 select sum(列名) from emp;
45:所有人数 select count(*) from emp;
46:平均工资 select sum(列名)/count(*) 第一种,avg(sal) 第二种 from emp;
47:平均奖金 select sun(comm)/count(*) 第一种,sum(comm)/count(comm) 第二种,avg(comm) 第三者;一种是获取所有员工的平均奖金.后面是获得只有奖金的平均值
48:自动将奖金为空的设置成0值,  select count(*),count(nvl(comm,0)) from emp;






//组函数============分组 group by
49:获得每一个部门的平均工资  select 列名 部门号,avg(列名) 平均工资 from emp group by 列名;  group by 后面的数据是avg里面没包含的都要写上
50:根据第一个列名获取平均工资并进行一个排序   select 列名,列名,avg(列名) from emp group by 列名,列名 order by 1;




//过滤器分组
51:获得每个部门的平均工资  select 列名 部门号,avg(sal) 平均工资 from emp group by 列名;
52:获取每个部门的平均工资大于2000的 select 列名 部门号,avg(列名) 平均工资 from emp group by 列名 having avg(列名)>2000;
53:获取每个部门的平均工资大于2000的 select 列名 部门号,avg(列名) 平均工资 from emp  where avg(列名)>2000 group by 列名;
54:select 列名,列名,sum(列名) from emp group by rollup(列名,列名);
   select deptno,job,sum(sal) from emp group by rollup(deptno,job); 意思就是获得deptno 和job部门的值 并获得他们的每一个部门的总值 rollup的意思是把里面的参数最后汇总






//多表查询=================★★★★★★


多表查询分,




55: 查询这两种表中当deptno相等时候的所有数据select e.empno,e.ename,e.sal,d.dname from 表名 e,表名 d where e.deptno=d.deptno;
56: 查询这两张表中工资等级在losal和hisal之间的所有数据select e.empno,e.ename,e.sal,s.grade from 表名 e, 表名 s where e.sal between s.losal and s,hisal;








57:
//外连接


查询部门号和部门名称中的所有元素  select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from 表名 e,表名 d where e.deptno=d.group by d.deptno,d.dname;




上面的不能满足需求 必须要使用到左外连接  和右外连接
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from 表名 e,表名 d where e.deptno(+)=d.deptno group by d.deptno,d.dname;






//自连接
通过表的别名，将同一张表视为多张表select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno;










自连接:不适合操作大表
层次查询:


select level,empno,ename,mgr from emp connect by prior empno=mgr start with mgr is null order by 1;




子查询