标语表之间的数据合并--union,
1多个数据的合并union:
create view vw_sal3 as
select * from (select * frm vw_sal union select * from vw_sal2);
2多条件查询 ()
select ename,job,deptno,sal from emp where (deptno,sal ) in (select * from vw_sal3)
3 oracle系统自带的数据库dual
查询时间:
select sysdate from dual;
4 查询orcle内所有已建的表
select * from cat;
5 rownum 只能和<搭配使用,不能和>搭配使用,如果想利用<>一起使用 ,那么则利用rownum的别名,第三个变量
select * from (select rownum id,emp.* from emp) where id>9 and id <21;
6查看当前用户
show user
show username from user_users;
7查看表格的结构
desc dept;
8 group by 最好是两个变量之间的分组,多变量,分组无法依据
select deptno ,avg(sal) from emp where deptno <> 20 group by deptno order by deptno;
<>==不包含
9关于分组,按什么分组取决于之前select 的为哪一位,没有查询出结果来,也许就是反应出你分组的还不够细
找出各部门中job 为clerk的的最高工资与最低工资
select * from (select max(sal) ,deptno from emp where job='clerk' group by deptno union select min(sal) ,deptn from emp where job='clerk' group by deptno);
注:group by 放在最后,where之后
等同于 select max(sal) as 最高工资 ,min(sal) as 最低工资 ,deptno 部门编号 from emp where job='CLERK' GROUP BY DEPTNO ;
AS只是max(),min(),deptno 的"别名"
10 数据尽量些详细,这样更容易出结果
select max(sal),min(sal),deptno from emp where job='clerk' and1000>(select min(sal) from emp ) group by deptno;
易错:
select max(sal),min(sal),deptno from emp where job='clerk' and1000> min(sal) group by deptno;(数据描述不够细)
11 涉及到两个方面的排序
select ename,sal,deptno from emp order by deptno asc,sal desc;
12 创建表与更新数据
create table t(id number(22) ,phone number(11),name varchar(22));
update t set name=‘zhangsan’ where name=’lisi‘;
13 oracle语句查询,一般方法是分步来看,一步步来