oracle----sql----查询---为例---计算

--ddl dml dcl dql
select *from emp e;--*代表所有列
select empno,ename from emp;--,分格例
select empno as id,ename as name from emp;
select empno "编号",ename"姓名"from emp;--特殊字符
select distinct deptno from emp;--去重复
---------*    ,  as  distinct
--固定值
select empno,sal,comm,8888888"红包"from emp;
--计算列
select empno,enma,sal||'美元'as money from emp;
--null运算(遇空为空)
select empno,ename,sal,comm,sal+comm+1 as money from emp;
--null运算(遇空为值)
select empno,ename,sal,comm,sal+nvl(comm,0) as money from emp;
--加减乘除运算
select ename,sal,(mod(sal+2-2)*2/2 ,100) as money from emp;
--升序
select empno,ename,sal,com from emp order by sal asc;
--降序
select empno,ename,sal,com from emp order by sal desc;
--根据放在desc前的列降序
select empno,ename,sal,com from emp order by sal,comm desc;
--根据运算结果降序
select empno,ename,sal,comm from emp order by sal+nvl(comm,0) desc;

---------------------------------------------------
--where     查询
--where快速寻找= > < >= <=    <>不等
select*from emp where empno=7900;
--两者之间
select*from emp where sal>=1600 and sal<=3000;
select*from emp where sal between 1600 and 3000;
--或者
select*from emp where ename='JAMES' or ename='ADAMS' or ename='SMITH';
select*from emp where ename in('JAMES','ADAMS','SMITH');
select*from emp where substr(ename,1,1)='S';
--%任意一个(第一个S)
select*from emp where ename like 'S%'
--取不到它第一个s(少用)
select*from emp where ename  not like 'S%'
select*from emp where not(ename   like 'S%');
--第二个是s;
select*from emp where ename like '_S%';
--有个s就行
select*from emp where ename like '%S%';
update emp set ename='sm%mm_tom' where empno=7369;
--escape转移符号,找有%的
select*from emp where ename like '%\%%'escape'\';
--找有_的
select*from emp where ename like '%\_%' escape'\';
--寻找为空的
select*from emp where nvl(comm,0)=0;
select*from emp where comm is null;



-------------------------------------------------
--伪列(rowid)
select rowid,e.*from emp e;

select rownum,e.*from emp e;
select rownum,e.*from emp e where rownum<=10;
--不能直接用between.
select*from(select rownum num,e.*from emp e where rownum<=10) a where a.num>5;
--分组
select*from emp;
select distinct job from emp;
select job from emp group by job;
--统计函数
select
job,
count (empno) 人数 ,
sum(sal)工资,
avg(sal)平均,
max(sal)最大,
min(sal)最小
from emp
group by job;

--统计函数不统计null值
select
job,
count (empno) 人数 ,
sum(nvl(comm,0))工资,
avg(nvl(comm,0))平均,
max(nvl(comm,0))最大,
min(nvl(comm,0))最小
from emp
group by job;

--部门
select
deptno,
job,
count (empno) 人数 ,
sum(sal)工资,
avg(sal)平均,
max(sal)最大,
min(sal)最小
from emp
group by deptno,job,
--格式
order by deptno,job;

select
deptno,
count (empno) 人数 ,
sum(sal)工资,
avg(sal)平均,
max(sal)最大,
min(sal)最小
from emp
group by deptno
having sum(sal)>9000;

select
count (empno) 人数 ,
sum(sal)工资,
avg(sal)平均,
max(sal)最大,
min(sal)最小
from emp

-----------------------------------------
create or view v$student1(id,name,age)
--union all把表加起来
select 1,'tom01',19 from dual
union all
select 2,'tom02',20 from dual
union all
select 3,'tom03',30 from dual
select*from v$student1;
----------------------------------------
create or view v$student2(id,name,age)
--union all把表加起来(所有数据库通用)
--union删掉重复记录(顺序,遇到它以前重复都去重复)
select 1,'tom01',19 from dual
union all
select 2,'tom02',20 from dual
union
select 2,'tom02',20 from dual
union all
select 3,'tom03',30 from dual

select*from v$student2;
----------------------------------------
--交集--oracle   sql专用
select*from v$student1
intersect
select*from v$student2;
------------------------------
--差集--oracle  sql专用
select*from v$student1
minus
select*from v$student2;