Oracle
单行语句查询
LIKE 语句
Select ename from emp where ename like ‘%ALL%’;所有含有ALL的ename;
Select ename from emp where ename like
‘_A%’;第二个字母是A;
Select ename from emp where ename like
‘%\%%’; \为默认的转义字符,选出含有%的ename;
Select ename from emp where ename like
‘%&%%’ escape ‘&’; 将&设置为转义字符;
Distinct 唯一的,不可重复的
Select distinct deptno from emp;
Select distinct deptno , job from emp;
Deptno , job 组合之间重复的值去掉;
Order by 语句
Select empno from emp order by empno asc; 升序排列empno;
Select empno , ename from emp order by empno desc; 降序排列empno;
Select empno , ename from emp where empno < > 10 order by empno desc;
Select empno ,ename from emp order by empno asc , ename desc; 先按empno升序处理,在empno相同的情况下,再降序排列ename;
To_date()函数,特定的时间格式
Select ename , hiredate from emp where hiredate > to_date(‘1980-2-1 12:34:56’ , ‘yyyy-mm-dd hh24:mi:ss’);
To_number()函数,将字符中的数字识别
Select sal from emp where sal > to_ number(‘$1,230.000’ , ‘$9,999.999’);
Nvl(comm,0)函数
Select ename , sal*12 +nvl(comm,0) from emp;
MAX函数
Select max(sal) from emp;
MIN 函数
Select min(sal) from emp;
AVG函数
Select avg(sal) from emp;
To_char 函数
Select to_char(avg(sal) , ‘9999.999’) from emp;
Round 函数
Select round(avg(sal) , X) from emp;
SUM函数
Select sum(sal) from emp;
COUNT函数
Select count(*) from emp;
Select count(*) from emp where deptno = 10;
查询当deptno=10的时候这个部门有多少人;
Select count(ename) from emp;
Select count(ename) from emp where deptno = 10; 当deptno=10的时候有几个ename;
Group by 分组函数
Select deptno , avg(sal) from emp group by deptno;
Select deptno,job,avg(sal) from emp group by deptno , job;
规则:出现在select 列表里面的字段如果没有出现在组函数里,则必须出现在group by 函数里。
Having
Select deptno , avg(sal) from emp group by deptno having avg(sal) > 2000;
查询各部门的平均薪水中大于2000的。Where 不能支持多组查询。
单行查询语句总结:
{ Select deptno ,round( avg(sal) , 2)
From emp
Where sal > 1500
Group by deptno
Having avg(sal) > 2000
Order by deptno desc; }
Oracle 子查询
Select ename , sal from emp where sal > ( select avg(sal ) from emp);
{ Select ename ,sal from emp
Join (select max(sal) max_sal , deptno from emp group by deptno) t
On(emp.sal=t.max_sal and emp.deptno = t.deptno); }
查询emp中每个deptno组里与对应最大sal值相等的ename和sal;
理解子查询的关键,就是把它当成一张表。
自连接
Select e1.ename , e2.ename from emp e1 , emp e2 where e1.mgr = e2.empno;
查询emp表中ename对应的mgr的名字;
交叉连接
Select ename , dname from emp cross join dept;
左外连接,右外连接,全连接--full;
Select ename ,dname , grade from emp e
Join dept d on(e.deptno = d.deptno )
Join salgrade s on(e.sal between s.losal and s.hisal);