Oracle学习查询语句的笔记

一、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);