浏览 7212 次
锁定老帖子 主题:oracle查询语句学习笔记以及语句
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2011-11-10
最后修改:2011-11-29
一、Oracle学习查询语句的笔记 在Oracle 中是对大小写 敏感的 也就是区分大小写 || 连接or 或者 and 和、 并且 where 条件 between ..最小值 and 最大值(这个关键字包含等于) in 字段 in(值1,值2,值3…值n) or 关键字 例子—-select * from emp where (deptno=10 and job =’MANAGER’) or (deptno=20 and job=’CLERK’); distinct去掉重复的 like |模糊查询(注意使用通配符的问题 ) 可以在任何地方应用 |主要的通配符有 |···|“%”:可以匹配任意长度的内容 |···|“_”:可以匹配一个长度的内同 例如 select * from emp where ename like ‘%M%’; <> 不等号 #######基本查询语句结束#######接下来是排序####### Ordre by 排序:asc升序(默认的) desc:降序 *放在sql语句的最后 #######排序结束#######接下来是函数####### 一、字符函数 upper() 小写字母变大写 ·select upper(‘smith’) from dual; 必须 加上from ·select * from emp where ename =upper(‘smith’); lower() 大写字母变小写 ·select lower(‘HELLO WORLD’) from dual; initcap() 开头字母大写 ·select initcap(‘HELLO WORLD’) from dual; ·select initcap(ename) from emp; *字符串除了可以使用||来连接。 还可以使用concat();函数来进行连接 ·select concat(‘hellow’,'world’) from dual; 可以进行字符串截取,求字符串长度。进行指定内容替换 ·字符串截取:substr(); substr 的截取点是从0或者是1效果都是一样的(Oracle) Oracle 中 可以输入负值 来倒着截取。 ·select ename ,substr(ename,-3,3) from emp; ·字符串长度:length(); ·内容替换: replace(); _________________范例_______________ select substr(‘hello’,1,3) 截取字符串, length(‘hello’) 字符串长度, replace(‘hello’,'l’,'x’) 字符串替换 from dual; ________________________________________ 二、数值函数 ·四舍五入 :round(); 可以指定四舍五入位数select round(789.546,-2) from dual; 负值对整数进行操作。 正值是小数 ·截断小数位 :trunc(); ·取余(取模):mod 三、日期函数 ·日期-数字=日期 ·日期+数字=日期 ·日期-日期=数字(天数) ·months_between();求出指定日期范围的月数 ·add_months();在制定日期加上制定的月数,求出之后的日期 ·next_day();下一个的今天是哪一个日期 ·last_day();求出给定日期的月最后一天的日期 当前日期 sysdate关键字 范例: select sysdate from dual; 四、转换函数 ·to_char(): 转换成字符串 ·通配符:·年:yyyy ·月:mm ·日:dd ·to_number(): 转换成数字 ·to_date(): 转换成日期 五、通用函数 ·nvl(字段,0) 如果字段里面的值是空 就按照0显示 __________________________范例__________________________ select empno,ename,(nvl(sal,0)+nvl(comm,0))*12 from emp; ________________________________________________________ ·decode 类似if(){}else{} __________________________范例_________________________________ 1·select decode(1,1,’内容是1′,2,’内容是2′,3,’内容是3′) from dual; 2·select empno 编号, ename 姓名 , HIREDATE 日期,decode ( job,’CLERK’,'业务员’,'SALESMAN’,'销售经理’, ‘MANAGER’,'经理’,'ANALYST’,'分析员’, ‘PRESIDENT’,'总裁’ ) 职业 from emp; _______________________________________________________________ #######函数结束#######接下来是多表查询####### 1·基础语句 *在使用多表查询的时候会出现笛卡尔积,如果表的数据越多,那么笛卡尔积也就会越大。 比如一张表有1W条记录,就有1W的五次方条记录。(出现的原因是有关联字段) *在多表查询的时候加入where语句,就能消除笛卡尔积。 一般会为表取别名 别名范例: select * from emp e ,dept d where e.deptno=d.deptno; ***范例(难题)** 问题:求出雇员姓名,工资,部门名称,工资等级,上级领导名字,领导的工资,领导的工资等级。 __________________________________________________________________________________________ select e.ename 雇员姓名,e.sal 工资,d.dname 部门名称, decode(s.grade,’1′,’第五等工资’,’2′,’第四等工资’,’3′,’第三等工资’,’4′,’第二等工资’ ,’5′,’第五等工资’) 工资等级, m.ename 上级领导名字,m.sal 领导的工资, decode(ms.grade,’1′,’第五等工资’,’2′,’第四等工资’,’3′,’第三等工资’,’4′,’第二等工资’ ,’5′,’第五等工资’)领导的工资等级 from emp e, dept d ,salgrade s ,emp m,salgrade ms where e.deptno =d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno and m.sal between ms.losal and ms.hisal; __________________________________________________________________________________________ *注意* 有一个人没有上级领导,查不出,因为什么呢?因为所有人的最上层x领导是他! 2·左右连接 去除笛卡尔积的时候会让某些字段不被查出。需要使用左右连接((+)用这个符号) 例如select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno; 表示右连接,以右边的表为准,证明:(+)在左边表示右连接,反之左连接。默认左连接 3·sql1999语法支持(了解即可) ·cross join : 交叉连接==>产生笛卡尔积 ·natural join: 自然连接==>消除笛卡尔积 ·using 字句: 直接关联操作列 _________________________范例_________________________________ ·select * from emp e join dept d using(deptno) where deptno=30; on是自己定义条件·select * from emp e join dept d on(e.deptno=d.deptno) where e.deptno=30; __________________其结果都是一样的____________________________ ·左右连接(左右外连接) ·left join ·right join #######多表查询结束#######接下来是组函数及分组统计####### *!重点!* 1·组函数 ·count(): 记录数 ·max(): 最大值\ >不能用在字符串 ·min(): 最小值/ ·avg(): 平均值 ·sum(): 总和 2·分组统计 group by 分组条件 __________________________范例_________________________ ·select deptno,count(empno) from emp group by deptno; ·select deptno,avg(sal) from emp group by deptno; _______________________________________________________ 错误:_______________________________________________________ 1、使用了分组函数,有两种情况 ·使用了group by 并指定了分组条件 会将分组条件一起查询出来 ·没使用分组,就只能单独使用分组函数。 2、在使用分组函数的时候,不能出现分组函数和分组条之外的字段 select d.dname ,count(e.empno) from dept d ,emp e where d.deptno=e.deptno group by d.dname; _______________________________________________________ select max(avg(sal)) from emp group by deptno; _______________________________________________________ *!注意!*:分组函数值能在分组中使用不能在where中使用,要使用 having 关键字 _____________________范例______________________________ select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; _______________________________________________________ #######接下来是多表查询######接下来是子查询######## 子查询是指,在以个查询的内部还包括另外一个查询。 *!注意!*:所有子查询都要在()中编写 子查询分为以下三类: ·单列子查询:返回结果是一个列的一个内容 ·单行子查询,返回多个列,有可能是一条完整的记录 ·多行子查询,返回多条记录 ________________________范例__________________________________________ –select * from emp where sal>(select sal from emp where empno=7654) and job=(select job fromemp where empno=7788); —————————————————- select d.dname,ed.c,ed.a,e.ename from dept d , (select deptno,count(empno) c, avg(sal)a,min(sal)min from emp group by deptno) ed ,emp e where d.deptno=ed.deptno and e.sal =ed.min; ______________________________________________________________________ 子查询三种符号: ·in :指定一个查询范围 ·any :有三种情况 ,大于小于等于 ·=any相当于in ·>any比里面最小的值要大。 ·all比最大的值要大。 ·1500;-工资大于1500 – 能领取奖金的 –select * from emp where COMM is not null; –能领取奖金的工资大于1500的人 –select * from emp where COMM is not null and sal>1500; –能领取奖金或者工资大于1500的人 –select * from emp where COMM is not null or sal>1500; –括号能表示一组条件 –select * from emp where not (sal >1500 and COMM is not null); –select * from emp where sal >=1500 and sal <=3000; –select * from emp where sal between 1500 and 3000; –1981年1月1日到1981年12月31日的员工 –select * from emp where HIREDATE between ’01-1月 -81′ and ’31-12月 -81′; –select * from emp where ename= ‘SMITH’; –select * from emp where empno=7369 or empno=7499 or empno =7521;se –select * from emp where empno in(7369,7499,7521); –select * from emp where empno not in(7369,7499,7521); –select * from emp where ename in(‘SMITH’,'ALLEN’,'KING’) and COMM is not null; –select * from emp where ename like ‘%M%’ ; –select * from emp where HIREDATE like ‘%81′; –<>不等号 查询编号不是7369的雇员 –selec t * from emp where empno<>7369; select * from emp where empno!=7369; –工资由低到高 –select * from emp order by SAL ; –降序 –select * from emp order by SAL desc ; –select * from emp where DEPTNO=10 order by SAL desc ,HIREDATE asc; ######################查询及排序语句结束########################### ##########函数开始########### –select upper(‘smith’) from dual; –select * from emp where ename =upper(‘smith’); –select lower(‘HELLO WORLD’) from dual; –select initcap(‘HELLO WORLD’) from dual; –select initcap(ename) from emp; –select concat(‘hellow’,'world’) from dual; /*select substr(‘hello’,0,3) 截取字符串, length(‘hello’) 字符串长度, replace(‘hello’,'l’,'x’) 字符串替换 from dual; */ –显示雇员姓名及姓名后三个字符 –select ename ,substr(ename,length(ename)-2) from emp; –select ename ,substr(ename,-3,3) from emp; –select round(789.546,-2) from dual; –select trunc(789.536,2) from dual; –select mod(10,3) 十除三的模 from dual; –求当前日期 使用sysdate –select sysdate from dual; –select empno,ename,round((sysdate-HIREDATE)/7) 星期数 from emp; –select empno,ename,round(months_between(sysdate,HIREDATE)) from emp; –select add_months(sysdate ,4) from emp; –select next_day(sysdate,’星期二’) from dual; –select last_day(sysdate) from dual; /* select empno,ename,to_char(HIREDATE,’yyyy’) year , to_char(HIREDATE,’mm’) month, to_char(HIREDATE,’dd’) day from emp; */ –select empno, ename,to_char(HIREDATE,’fmyyyy-mm-dd’)日期 from emp; –select empno ,ename,to_char(sal,’l99,999′) from emp; –select to_number(’123′)+to_number(’123′) from dual ; –select to_char(to_date(’2010-3-21′,’yyyy-mm-dd’),’fmyyyy-mm-dd’)from dual; –select empno,ename,(nvl(sal,0)+nvl(comm,0))*12 from emp; –select decode(1,1,’内容是1′,2,’内容是2′,3,’内容是3′) from dual; /* select empno 编号, ename 姓名 , HIREDATE 日期,decode (job,’CLERK’,'业务员’,'SALESMAN’,'销售经理’,'MANAGER’,'经理’,'ANALYST’,'分析员’,'PRESIDENT’,'总裁’) 职业 from emp; ##########函数结束########### ##########多表查询开始########### */ –select * from emp e ,dept d where e.deptno=d.deptno; /* select e.ename 雇员姓名,e.sal 工资,d.dname 部门名称, decode(s.grade,’1′,’第五等工资’,’2′,’第四等工资’,’3′,’第三等工资’,’4′,’第二等工资’ ,’5′,’第五等工资’) 工资等级, m.ename 上级领导名字,m.sal 领导的工资, decode(ms.grade,’1′,’第五等工资’,’2′,’第四等工资’,’3′,’第三等工资’,’4′,’第二等工资’ ,’5′,’第五等工资’)领导的工资等级 from emp e, dept d ,salgrade s ,emp m,salgrade ms where e.deptno =d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno and m.sal between ms.losal and ms.hisal; */ –select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno; –select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno(+); –select * from emp natural join dept; –select * from emp e join dept d using(deptno) ; –select * from emp e join dept d on(e.deptno=d.deptno) where e.deptno=30; –select e.ename,d.deptno,d.loc from emp e right outer join dept d on (e.deptno=d.deptno); ##########组函数和分组开始########### –select deptno,count(empno) from emp group by deptno; –select deptno,avg(sal) from emp group by deptno; –select d.dname ,count(e.empno) from dept d ,emp e where d.deptno=e.deptno group by d.dname; –select ename, deptno,avg(sal) from emp group by deptno; /* select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; */ /* select job,sum(sal) su from emp where job<>‘SALESMAN’ group by job having sum(sal)>5000 order by su; */ select max(avg(sal)) from emp group by deptno; –select * from emp where sal>(select sal s from emp where empno=7654); –select * from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788); –select ename,job,sal from emp where sal = (select min(sal) from emp); /* select d.dname,ed.c,ed.a,e.ename from dept d , (select deptno,count(empno) c, avg(sal)a,min(sal)min from emp group by deptno) ed ,emp e where d.deptno=ed.deptno and e.sal =ed.min; */ –select * from emp where sal in (select min(sal) from emp group by deptno); –select * from emp where sal >any(select min(sal) from emp group by deptno); 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2011-11-25
整理得蛮详细的。。
|
|
返回顶楼 | |
发表时间:2011-11-29
楼主辛苦了。很好
|
|
返回顶楼 | |
发表时间:2011-11-29
lizhensan 写道 楼主辛苦了。很好
不辛苦,第一次得到这样的肯定,谢谢大家了。我会努力的,努力学习,工作,赡养父母········· |
|
返回顶楼 | |
发表时间:2011-11-29
楼主辛苦了,把能直接运行的代码放到代码框里就更好了 --搜出所有表 select * from tab; |
|
返回顶楼 | |
发表时间:2011-11-29
javamingming 写道
楼主辛苦了,把能直接运行的代码放到代码框里就更好了 --搜出所有表 select * from tab; 对不起啊,因为是前几年写的,当时只是用Txt给自己做个参照物,所以没有注意这些,有时间我一定补全。。 |
|
返回顶楼 | |