oracle语句案例

select * from emp;

select ename,comm,nvl(comm,100) from emp;

select ename,comm,nvl2(comm,comm+100,100) from emp;

select distinct deptno,sal from emp;

select ename name from emp where ename like ‘S%’
order by ename;

/查询 emp 中 姓名中带有 M 的雇员信息/
select * from emp where ename like ‘%M%’ ;

/查询 emp 中 姓名首字母是 M 的雇员信息/
select * from emp where ename like ‘M%’ ;
/查询 emp 中 姓名第二个 字母是 M 的雇员信息/
select * from emp where ename like ‘_M%’ ;

/查询 emp 中 姓名第含有 _ 的雇员信息/
select t.*,rowId from emp t ;
select * from emp where ename like ‘%_%’
escape ‘\’ ;

select * from emp where
nvl(comm,0)=0

/* 查询 10 或 20 部门 薪资高于 2000的雇员信息*/
select * from emp
where sal>2000
and (
deptno=10 or deptno=20)

/* 查询 20部门 薪资高于 2000 或 10部门的雇员信息*/
select * from emp
where sal>2000
and deptno=20
or deptno=10
order by deptno

select * from emp order by deptno asc,sal desc;

select ename,
lower(ename) ,
upper(lower(ename)),
initcap(ename)
from emp;

select ename,length(ename) from emp;
select * from emp where length(ename)>=6;

select ename,
substr(ename,1,1),
replace(ename,substr(ename,1,1),’*’)
from emp;

select ename,
‘*’||substr(ename,2)
from emp;

select ename,
concat(‘*’,substr(ename,2))
from emp;

select ename,rpad(ename,10,’*’) from emp;

select sal,round(sal) from emp;

select sal,trunc(sal) from emp;
select sal,trunc(sal,1) from emp;
select sal,trunc(sal,-2) from emp;

select sysdate from dual;

select * from emp
where trunc((months_between(sysdate,hireDate))/12)>35;
– 1星期天 2 星期一 。。。。
– 下一个星期几 是哪天
select next_day(sysdate,3) from dual;

select last_day(sysdate) from dual;

select hireDate,last_day(hireDate) from emp;
/雇员是在入职月份倒数第三天入职的雇员/
select * from emp
where to_char(hireDate,’yyyy-MM-dd’)=
to_char(last_day(hireDate)-2,’yyyy-MM-dd’);

select add_months(sysdate,1)
from dual;

select sysdate+1/24 from dual;

select to_number(‘200’)+100 from dual;

select substr(to_char(100),1,1) from dual;

select to_char(sysdate,’yyyy-MM-dd HH24:mi:ss’)
from dual;
/部门10 sal 1.2
20 sal * 1.5
30 sal * 2
*/
select deptno,
sal ,
decode(deptno,10,sal*1.2,
20,sal*1.5,
30,sal*2,
sal
)
from emp;

/行转列/
create table t_score(
id number,
sname varchar(20),
cname varchar(20),
score number
)
select * from t_score
select sname,
sum(decode(cname,’数学’, score)) 数学,
sum(decode(cname,’语文’, score)) 语文,
sum(decode(cname,’计算机’, score)) 计算机
from
t_score
group by sname;

select * from dept;
select * from emp ;
update emp set deptno=null where empno=7782;

select * from emp e, dept d
where d.deptno=e.deptno

 select * from emp e, dept d
              where d.deptno(+)=e.deptno;

 select * from emp e full outer join dept d
               on d.deptno=e.deptno;

/查询出部门人数多于 3人的部门 信息/
select * from dept where deptno in
(
select deptno from emp
group by deptno
having count(1)>3
)

select max(ename)  from emp;   

select count(comm) from emp;

select * from emp;

select avg(comm) from emp;

/*谁的工资是最低*/
  select * from emp where sal=
      (select min(sal) from emp);

/哪些员工的工资等于本部门的最高工资/
select e.*,t.maxsal from emp e,
( select deptno,max(sal) maxsal from emp
group by deptno) t
where e.deptno=t.deptno
and e.sal=t.maxsal
/哪个部门的平均工资比20部门的平均工资高/
select deptno ,avg(sal) from emp
group by deptno
having avg(sal) >
(select avg(sal) from emp where deptno=20)

/哪些员工的工资比本部门的平均工资高？/
select e.* from emp e,
(select deptno,avg(sal) avgsal
from emp
group by deptno) t
where e.deptno=t.deptno
and e.sal>t.avgsal

/* exists in */
/查询存在雇员的部门信息/
select * from dept where deptno in
(select deptno from emp)

 select * from dept d where exists(
        select  1 from emp e where d.deptno=e.deptno
    )