Oracel 简单查询2

--======================

--author:yeeXun

--date: 2010-11-26　10:35:00

--address:17-304

--======================

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 

Connected as scott

 

SQL> select deptno,avg(sal) from emp group by deptno;

 

DEPTNO   AVG(SAL)

------ ----------

    30 1566.66666

    20       2175

    10 2916.66666

 

SQL> select * from emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

 7839 KING       PRESIDENT       1981-11-17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20

 7900 JAMES      CLERK      7698 1981-12-3      950.00               30

 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20

 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

 

14 rows selected

 

SQL> select a.ename,a.sal,a.deptno,b.avgsal from a,(select deptno,avg(sal) avgsal from emp group by deptno)b where a.deptno=b.deptno and a.sal>b.avgsal;

 

select a.ename,a.sal,a.deptno,b.avgsal from a,(select deptno,avg(sal) avgsal from emp group by deptno)b where a.deptno=b.deptno and a.sal>b.avgsal

 

ORA-00942: 表或视图不存在

 --表视图：只存在于查询期间，指定别名不能用“as”关键字，如果指定列的别名可以使用关键字“as”

SQL> select a.ename,a.sal,a.deptno,b.avgsal from emp a,(select deptno,avg(sal) avgsal from emp group by deptno)b where a.deptno=b.deptno and a.sal>b.avgsal;

 

ENAME            SAL DEPTNO     AVGSAL

---------- --------- ------ ----------

ALLEN        1600.00     30 1566.66666

JONES        2975.00     20       2175

BLAKE        2850.00     30 1566.66666

SCOTT        3000.00     20       2175

KING         5000.00     10 2916.66666

FORD         3000.00     20       2175

 

6 rows selected

 

SQL> select * from emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

 7839 KING       PRESIDENT       1981-11-17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20

 7900 JAMES      CLERK      7698 1981-12-3      950.00               30

 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20

 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

 

14 rows selected

 

--分页，

 

SQL> select a.*,rownum ru from (select * from emp)a;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO         RU

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20          1

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30          2

 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30          3

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20          4

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30          5

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30          6

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10          7

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20          8

 7839 KING       PRESIDENT       1981-11-17    5000.00               10          9

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30         10

 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20         11

 7900 JAMES      CLERK      7698 1981-12-3      950.00               30         12

 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20         13

 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10         14

 

14 rows selected

 

SQL> select a.*,rownum ru from (select * from emp)a where ru<=10;

 

select a.*,rownum ru from (select * from emp)a where ru<=10

 

ORA-00904: "RU": 标识符无效

--先找出指定上限范围内的数据

SQL> select a.*,rownum ru from (select * from emp)a where rownum<=10;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO         RU

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20          1

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30          2

 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30          3

 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20          4

 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30          5

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30          6

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10          7

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20          8

 7839 KING       PRESIDENT       1981-11-17    5000.00               10          9

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30         10

 

10 rows selected

 --再指定下限范围内的数据

SQL> select * from (select a.*,rownum ru from (select * from emp)a where rownum<=10) where ru>=6;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO         RU

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30          6

 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10          7

 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20          8

 7839 KING       PRESIDENT       1981-11-17    5000.00               10          9

 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30         10

 --修改显示数据，只需要修改最里层的查询即可

SQL> select * from (select a.*,rownum ru from (select ename,sal from emp)a where rownum<=10) where ru>=6;

 

ENAME            SAL         RU

---------- --------- ----------

BLAKE        2850.00          6

CLARK        2450.00          7

SCOTT        3000.00          8

KING         5000.00          9

TURNER       1500.00         10

 

SQL> select * from (select a.*,rownum ru from (select ename,sal from emp order by sal)a where rownum<=10) where ru>=6;

 

ENAME            SAL         RU

---------- --------- ----------

MILLER       1300.00          6

TURNER       1500.00          7

ALLEN        1600.00          8

CLARK        2450.00          9

BLAKE        2850.00         10

 

SQL> select count(*) from emp;

 

  COUNT(*)

----------

        14

 

SQL> create table myemp(id ,ename,sal) as select empno,ename,sal from e,mp;

 

create table myemp(id ,ename,sal) as select empno,ename,sal from e,mp

 

ORA-00942: 表或视图不存在

 

--数据复制法

SQL> create table myemp(id ,ename,sal) as select empno,ename,sal from emp;

 

Table created

 

SQL> desc myemp;

Name  Type         Nullable Default Comments 

----- ------------ -------- ------- -------- 

ID    NUMBER(4)    Y                         

ENAME VARCHAR2(10) Y                         

SAL   NUMBER(7,2)  Y                         

 

SQL> select * from myemp;

 

   ID ENAME            SAL

----- ---------- ---------

 7369 SMITH         800.00

 7499 ALLEN        1600.00

 7521 WARD         1250.00

 7566 JONES        2975.00

 7654 MARTIN       1250.00

 7698 BLAKE        2850.00

 7782 CLARK        2450.00

 7788 SCOTT        3000.00

 7839 KING         5000.00

 7844 TURNER       1500.00

 7876 ADAMS        1100.00

 7900 JAMES         950.00

 7902 FORD         3000.00

 7934 MILLER       1300.00

 

14 rows selected

 

SQL> select ename,sal,job from emp where sal>2500;

 

ENAME            SAL JOB

---------- --------- ---------

JONES        2975.00 MANAGER

BLAKE        2850.00 MANAGER

SCOTT        3000.00 ANALYST

KING         5000.00 PRESIDENT

FORD         3000.00 ANALYST

 

SQL> select ename,sal,job from emp where sal>2500 union 

  2  select ename,sal,job from emp where jon='manager';

 

select ename,sal,job from emp where sal>2500 union

select ename,sal,job from emp where jon='manager'

 

ORA-00904: "JON": 标识符无效

 

SQL> 

SQL> select ename,sal,job from emp where sal>2500 union

  2  select ename,sal,job from emp where jon='manager'

SQL> 

SQL> select ename,sal,job from emp where sal>2500 union

  2  select ename,sal,job from emp where job='manager';

 

ENAME            SAL JOB

---------- --------- ---------

BLAKE        2850.00 MANAGER

FORD         3000.00 ANALYST

JONES        2975.00 MANAGER

KING         5000.00 PRESIDENT

SCOTT        3000.00 ANALYST

 

SQL> select ename,sal,job from emp where sal>2500 union

  2  select ename,sal,job from emp where job='MANAGER';

 

ENAME            SAL JOB

---------- --------- ---------

BLAKE        2850.00 MANAGER

CLARK        2450.00 MANAGER

FORD         3000.00 ANALYST

JONES        2975.00 MANAGER

KING         5000.00 PRESIDENT

SCOTT        3000.00 ANALYST

 

6 rows selected

 

SQL> select ename,sal,job from emp union all

  2  select ename,sal,job from emp where job='MANAGER';

 

ENAME            SAL JOB

---------- --------- ---------

SMITH         800.00 CLERK

ALLEN        1600.00 SALESMAN

WARD         1250.00 SALESMAN

JONES        2975.00 MANAGER

MARTIN       1250.00 SALESMAN

BLAKE        2850.00 MANAGER

CLARK        2450.00 MANAGER

SCOTT        3000.00 ANALYST

KING         5000.00 PRESIDENT

TURNER       1500.00 SALESMAN

ADAMS        1100.00 CLERK

JAMES         950.00 CLERK

FORD         3000.00 ANALYST

MILLER       1300.00 CLERK

JONES        2975.00 MANAGER

BLAKE        2850.00 MANAGER

CLARK        2450.00 MANAGER

 

17 rows selected

 

SQL> select distinct ename,sal,job from emp;

 

ENAME            SAL JOB

---------- --------- ---------

WARD         1250.00 SALESMAN

SMITH         800.00 CLERK

KING         5000.00 PRESIDENT

TURNER       1500.00 SALESMAN

JAMES         950.00 CLERK

FORD         3000.00 ANALYST

CLARK        2450.00 MANAGER

BLAKE        2850.00 MANAGER

ADAMS        1100.00 CLERK

SCOTT        3000.00 ANALYST

MILLER       1300.00 CLERK

ALLEN        1600.00 SALESMAN

MARTIN       1250.00 SALESMAN

JONES        2975.00 MANAGER

 

14 rows selected

 

SQL> exits;

 

-the end-