亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

SQL> select e.empno,e.ename,e.sal,d.dname

? 2? from emp e,dept d

? 3? where e.deptno=d.deptno;

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL DNAME

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

? ? ? 7782 CLARK? ? ? ? ? ? 2450 ACCOUNTING

? ? ? 7839 KING? ? ? ? ? ? ?5000 ACCOUNTING

? ? ? 7934 MILLER? ? ? ? ? ?1300 ACCOUNTING

? ? ? 7566 JONES? ? ? ? ? ? 2975 RESEARCH

? ? ? 7902 FORD? ? ? ? ? ? ?3000 RESEARCH

? ? ? 7876 ADAMS? ? ? ? ? ? 1100 RESEARCH

? ? ? 7369 SMITH? ? ? ? ? ? ?800 RESEARCH

? ? ? 7788 SCOTT? ? ? ? ? ? 3000 RESEARCH

? ? ? 7521 WARD? ? ? ? ? ? ?1250 SALES

? ? ? 7844 TURNER? ? ? ? ? ?1500 SALES

? ? ? 7499 ALLEN? ? ? ? ? ? 1600 SALES

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL DNAME

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

? ? ? 7900 JAMES? ? ? ? ? ? ?950 SALES

? ? ? 7698 BLAKE? ? ? ? ? ? 2850 SALES

? ? ? 7654 MARTIN? ? ? ? ? ?1250 SALES

連接條件可以避免使用笛卡爾乘積的全集

多表查詢

1、什么是多表查詢？

2、笛卡爾集

3、等值連接

4、不等值連接

5、外連接

6、自連接

7、層次查詢

ttitle col 15 '我的報表' col 35 sql.pno

col deptno heading 部門號

col job heading 職位

col sum(sal) heading 工資總額

break on deptno skip 1

ttitle col 15 '我的報表' col 35 sql.pno

表示標題前空15列，后空35列

sql.pno 表示報表的第一頁，第二頁

break on deptno skip 1

get sql文件，讀取sql文件

@sql文件，執行sql文件

*******************************************************

SQL> get e:\temp\report.sql

? 1? ttitle col 15 '我的報表' col 35 sql.pno

? 2? col deptno heading 部門號

? 3? col job heading 職位

? 4? col sum(sal) heading 工資總額

? 5* break on deptno skip 1

SQL> @e:\temp\report.sql

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

? ? ? ? ? ? ? 我的報表? ? ? ? ? ? ? ? ? ? ?1

? ? 部門號 職位? ? ? ? 工資總額

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

? ? ? ? 10 CLERK? ? ? ? ? ?1300

? ? ? ? ? ?MANAGER? ? ? ? ?2450

? ? ? ? ? ?PRESIDENT? ? ? ?5000

? ? ? ? ? ? ? ? ? ? ? ? ? ?8750

? ? ? ? 20 CLERK? ? ? ? ? ?1900

? ? ? ? ? ?ANALYST? ? ? ? ?6000

? ? ? ? ? ?MANAGER? ? ? ? ?2975

? ? ? ? ? ? ? ? ? ? ? ? ? 10875

? ? ? ? 30 CLERK? ? ? ? ? ? 950

? ? ? ? ? ?MANAGER? ? ? ? ?2850

? ? ? ? ? ?SALESMAN? ? ? ? 5600

? ? ? ? ? ? ? ? ? ? ? ? ? ?9400

? ? ? ? ? ? ? ? ? ? ? ? ? 29025

已選擇13行。

SQL> set pagesize 10

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

? ? ? ? ? ? ? 我的報表? ? ? ? ? ? ? ? ? ? ?1

? ? 部門號 職位? ? ? ? 工資總額

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

? ? ? ? 10 CLERK? ? ? ? ? ?1300

? ? ? ? ? ?MANAGER? ? ? ? ?2450

? ? ? ? ? ?PRESIDENT? ? ? ?5000

? ? ? ? ? ? ? ? ? ? ? ? ? ?8750

? ? ? ? 20 CLERK? ? ? ? ? ?1900

? ? ? ? ? ? ? 我的報表? ? ? ? ? ? ? ? ? ? ?2

? ? 部門號 職位? ? ? ? 工資總額

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

? ? ? ? 20 ANALYST? ? ? ? ?6000

? ? ? ? ? ?MANAGER? ? ? ? ?2975

? ? ? ? ? ? ? ? ? ? ? ? ? 10875

? ? ? ? 30 CLERK? ? ? ? ? ? 950

? ? ? ? ? ?MANAGER? ? ? ? ?2850

? ? ? ? ? ? ? 我的報表? ? ? ? ? ? ? ? ? ? ?3

? ? 部門號 職位? ? ? ? 工資總額

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

? ? ? ? 30 SALESMAN? ? ? ? 5600

? ? ? ? ? ? ? ? ? ? ? ? ? ?9400

? ? ? ? ? ? ? ? ? ? ? ? ? 29025

已選擇13行。

select deptno,job,sum(salary) from users group by deptno,job;

+

select deptno,sum(sal) from emp group by deptno

+

select sum(sal) from emp

=

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

group by rollup---叫group by語句的增強------在報表中用的比較多

group by rollup(a,b)

等價于

group by a,b

+

group by a

+

group by null

break on deptno skip 2

相同部門號僅僅顯示一次，

不同的部門號之間跳過2行

set pagesize 30 ---讓每列顯示30條數據

*******************************************************

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

? ? DEPTNO JOB? ? ? ? ?SUM(SAL)

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

? ? ? ? 10 CLERK? ? ? ? ? ?1300

? ? ? ? 10 MANAGER? ? ? ? ?2450

? ? ? ? 10 PRESIDENT? ? ? ?5000

? ? ? ? 10? ? ? ? ? ? ? ? ?8750

? ? ? ? 20 CLERK? ? ? ? ? ?1900

? ? ? ? 20 ANALYST? ? ? ? ?6000

? ? ? ? 20 MANAGER? ? ? ? ?2975

? ? ? ? 20? ? ? ? ? ? ? ? 10875

? ? ? ? 30 CLERK? ? ? ? ? ? 950

? ? ? ? 30 MANAGER? ? ? ? ?2850

? ? ? ? 30 SALESMAN? ? ? ? 5600

? ? DEPTNO JOB? ? ? ? ?SUM(SAL)

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

? ? ? ? 30? ? ? ? ? ? ? ? ?9400

? ? ? ? ? ? ? ? ? ? ? ? ? 29025

已選擇13行。

SQL> break on deptno skip 2

SQL> set pagesize 30

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

? ? DEPTNO JOB? ? ? ? ?SUM(SAL)

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

? ? ? ? 10 CLERK? ? ? ? ? ?1300

? ? ? ? ? ?MANAGER? ? ? ? ?2450

? ? ? ? ? ?PRESIDENT? ? ? ?5000

? ? ? ? ? ? ? ? ? ? ? ? ? ?8750

? ? ? ? 20 CLERK? ? ? ? ? ?1900

? ? ? ? ? ?ANALYST? ? ? ? ?6000

? ? ? ? ? ?MANAGER? ? ? ? ?2975

? ? ? ? ? ? ? ? ? ? ? ? ? 10875

? ? ? ? 30 CLERK? ? ? ? ? ? 950

? ? ? ? ? ?MANAGER? ? ? ? ?2850

? ? ? ? ? ?SALESMAN? ? ? ? 5600

? ? ? ? ? ? ? ? ? ? ? ? ? ?9400

? ? ? ? ? ? ? ? ? ? ? ? ? 29025

已選擇13行。

分組函數的嵌套

示例：求部門平均工資的最大值

*******************************************************

SQL> select max(avg(sal))

? 2? from emp

? 3? group by deptno;

MAX(AVG(SAL))

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

? ?2916.66667

select deptno,avg(sal)

from emp

group by deptno

order by avg(sal);

select deptno,avg(sal) 平均工資

from emp

group by deptno

order by 平均工資;

select deptno,avg(sal) 平均工資

from emp

group by deptno

order by 2;

2指得是第二列;

默認是升序的，降序加上desc

-- a命令 append，指在上行命令下追加

tips:a命令后面的空格一定是2個或者2個以上

"/"---表示執行這條sql語句

******************************************************************************

SQL> select deptno,avg(sal)

? 2? from emp

? 3? group by deptno

? 4? order by avg(sal);

? ? DEPTNO? ?AVG(SAL)

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

? ? ? ? 30 1566.66667

? ? ? ? 20? ? ? ?2175

? ? ? ? 10 2916.66667

SQL> select deptno,avg(sal) 平均工資

? 2? from emp

? 3? group by deptno

? 4? order by 平均工資;

? ? DEPTNO? ?平均工資

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

? ? ? ? 30 1566.66667

? ? ? ? 20? ? ? ?2175

? ? ? ? 10 2916.66667

SQL> select deptno,avg(sal) 平均工資

? 2? from emp

? 3? group by deptno

? 4? order by 2;

? ? DEPTNO? ?平均工資

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

? ? ? ? 30 1566.66667

? ? ? ? 20? ? ? ?2175

? ? ? ? 10 2916.66667

SQL> a? desc

? 4* order by 2 desc

SQL> /

? ? DEPTNO? ?平均工資

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

? ? ? ? 10 2916.66667

? ? ? ? 20? ? ? ?2175

? ? ? ? 30 1566.66667

having子句的語法

group by之后再加上having來過濾數據

where和having的區別

1.不能在where子句中使用組函數

2.可以在having子句中使用組函數

3.having 先分組再過濾；where先過濾再分組

----where使得分組記錄數據大大降低，從而提高了sql的效率

查詢10號部門的平均工資

select deptno,avg(sal)

from emp

group by deptno

having deptno=10;

select deptno,avg(sal)

from emp

where deptno=10

group by deptno;

*******************************************************

SQL> select deptno,avg(sal)

? 2? from emp

? 3? group by deptno

? 4? having avg(sal) > 2000;

? ? DEPTNO? ?AVG(SAL)

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

? ? ? ? 20? ? ? ?2175

? ? ? ? 10 2916.66667

SQL> select deptno,avg(sal)

? 2? from emp

? 3? group by deptno

? 4? having deptno=10;

? ? DEPTNO? ?AVG(SAL)

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

? ? ? ? 10 2916.66667

SQL>? ? select deptno,avg(sal)

? 2? from emp

? 3? where deptno=10

? 4? group by deptno;

? ? DEPTNO? ?AVG(SAL)

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

? ? ? ? 10 2916.66667

在select列表中所有未包含在組函數中的列都應包含在group by子句中

換句話說:包含在group by子句中的列不必包含在select列表中

select a,b,c,組函數(x)

from table

group by a,b,c;

上面select后面a，b，c一個都不能少；

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

select avg(sal) from emp group by deptno;

按照部門號，不同的職位對員工工資進行分組

select deptno,job,sum(sal)

from emp

group by deptno,job;

加上排序

select deptno,job,sum(sal)

from emp

group by deptno,job

order by deptno;

*******************************************************

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

? ? DEPTNO? ?AVG(SAL)

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

? ? ? ? 30 1566.66667

? ? ? ? 20? ? ? ?2175

? ? ? ? 10 2916.66667

SQL>

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

? AVG(SAL)

----------

1566.66667

? ? ? 2175

2916.66667

SQL> select deptno,job,sum(sal)

? 2? from emp

? 3? group by deptno,job;

? ? DEPTNO JOB? ? ? ? ?SUM(SAL)

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

? ? ? ? 20 CLERK? ? ? ? ? ?1900

? ? ? ? 30 SALESMAN? ? ? ? 5600

? ? ? ? 20 MANAGER? ? ? ? ?2975

? ? ? ? 30 CLERK? ? ? ? ? ? 950

? ? ? ? 10 PRESIDENT? ? ? ?5000

? ? ? ? 30 MANAGER? ? ? ? ?2850

? ? ? ? 10 CLERK? ? ? ? ? ?1300

? ? ? ? 10 MANAGER? ? ? ? ?2450

? ? ? ? 20 ANALYST? ? ? ? ?6000

已選擇9行。

SQL> select deptno,job,sum(sal)

? 2? from emp

? 3? group by deptno,job

? 4? order by deptno;

? ? DEPTNO JOB? ? ? ? ?SUM(SAL)

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

? ? ? ? 10 CLERK? ? ? ? ? ?1300

? ? ? ? 10 MANAGER? ? ? ? ?2450

? ? ? ? 10 PRESIDENT? ? ? ?5000

? ? ? ? 20 ANALYST? ? ? ? ?6000

? ? ? ? 20 CLERK? ? ? ? ? ?1900

? ? ? ? 20 MANAGER? ? ? ? ?2975

? ? ? ? 30 CLERK? ? ? ? ? ? 950

? ? ? ? 30 MANAGER? ? ? ? ?2850

? ? ? ? 30 SALESMAN? ? ? ? 5600

已選擇9行。

分組函數會自動忽略空值

NVL函數使分組函數無法忽略空值

?select count(*),count(nvl(comm,0)) from emp;

nvl(comm,0)?

如果comm不為空，返回comm

如果是空，返回0，就不會返回空

*******************************************************

SQL> select sum(sal)/count(*) 一,sum(sal)/count(sal) 二,avg(sal) 三

? 2? from emp;

? ? ? ? 一? ? ? ? ?二? ? ? ? ?三

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

2073.21429 2073.21429 2073.21429

SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三

? 2? from emp;

? ? ? ? 一? ? ? ? ?二? ? ? ? ?三

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

157.142857? ? ? ? 550? ? ? ? 550

SQL> select count(*),count(comm) from emp;

? COUNT(*) COUNT(COMM)

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

? ? ? ? 14? ? ? ? ? ?4

SQL> select * from emp;

? ? ?EMPNO ENAME? ? ? JOB? ? ? ? ? ? ? MGR HIREDATE? ? ? ? ? ? ? SAL? ? ? ?COMM? ? ?DEPTNO

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

? ? ? 7369 SMITH? ? ? CLERK? ? ? ? ? ?7902 17-12月-80? ? ? ? ? ? 800? ? ? ? ? ? ? ? ? ? 20

? ? ? 7499 ALLEN? ? ? SALESMAN? ? ? ? 7698 20-2月 -81? ? ? ? ? ?1600? ? ? ? 300? ? ? ? ?30

? ? ? 7521 WARD? ? ? ?SALESMAN? ? ? ? 7698 22-2月 -81? ? ? ? ? ?1250? ? ? ? 500? ? ? ? ?30

? ? ? 7566 JONES? ? ? MANAGER? ? ? ? ?7839 02-4月 -81? ? ? ? ? ?2975? ? ? ? ? ? ? ? ? ? 20

? ? ? 7654 MARTIN? ? ?SALESMAN? ? ? ? 7698 28-9月 -81? ? ? ? ? ?1250? ? ? ?1400? ? ? ? ?30

? ? ? 7698 BLAKE? ? ? MANAGER? ? ? ? ?7839 01-5月 -81? ? ? ? ? ?2850? ? ? ? ? ? ? ? ? ? 30

? ? ? 7782 CLARK? ? ? MANAGER? ? ? ? ?7839 09-6月 -81? ? ? ? ? ?2450? ? ? ? ? ? ? ? ? ? 10

? ? ? 7788 SCOTT? ? ? ANALYST? ? ? ? ?7566 19-4月 -87? ? ? ? ? ?3000? ? ? ? ? ? ? ? ? ? 20

? ? ? 7839 KING? ? ? ?PRESIDENT? ? ? ? ? ? 17-11月-81? ? ? ? ? ?5000? ? ? ? ? ? ? ? ? ? 10

? ? ? 7844 TURNER? ? ?SALESMAN? ? ? ? 7698 08-9月 -81? ? ? ? ? ?1500? ? ? ? ? 0? ? ? ? ?30

? ? ? 7876 ADAMS? ? ? CLERK? ? ? ? ? ?7788 23-5月 -87? ? ? ? ? ?1100? ? ? ? ? ? ? ? ? ? 20

? ? ?EMPNO ENAME? ? ? JOB? ? ? ? ? ? ? MGR HIREDATE? ? ? ? ? ? ? SAL? ? ? ?COMM? ? ?DEPTNO

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

? ? ? 7900 JAMES? ? ? CLERK? ? ? ? ? ?7698 03-12月-81? ? ? ? ? ? 950? ? ? ? ? ? ? ? ? ? 30

? ? ? 7902 FORD? ? ? ?ANALYST? ? ? ? ?7566 03-12月-81? ? ? ? ? ?3000? ? ? ? ? ? ? ? ? ? 20

? ? ? 7934 MILLER? ? ?CLERK? ? ? ? ? ?7782 23-1月 -82? ? ? ? ? ?1300? ? ? ? ? ? ? ? ? ? 10

已選擇14行。

SQL> select count(*),count(nvl(comm,0)) from emp;

? COUNT(*) COUNT(NVL(COMM,0))

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

? ? ? ? 14? ? ? ? ? ? ? ? ?14

wm_concat 行轉列

select deptno 部門號,wm_concat(ename) 部門中員工的姓名 from emp group by deptno;

wm_concat(ename) 將部門中員工的姓名用，拼接

col 部門中員工的姓名 for a60

把列寬設置為60的寬度

************************************************************

SQL> select deptno,wm_concat(ename) from emp group by deptno;

? ? DEPTNO

----------

WM_CONCAT(ENAME)

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

? ? ? ? 10

CLARK,MILLER,KING

? ? ? ? 20

SMITH,FORD,ADAMS,SCOTT,JONES

? ? ? ? 30

ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL> set linesize 200;

SQL> col 部門中員工的姓名 for a60

SQL> select deptno 部門號,wm_concat(ename) 部門中員工姓名 from emp group by deptno;

? ? 部門號

----------

部門中員工姓名

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

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

? ? ? ? 10

CLARK,MILLER,KING

? ? ? ? 20

SMITH,FORD,ADAMS,SCOTT,JONES

? ? ? ? 30

ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL> select deptno 部門號,wm_concat(ename) 部門中員工的姓名 from emp group by deptno;

? ? 部門號 部門中員工的姓名

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

? ? ? ? 10 CLARK,MILLER,KING

? ? ? ? 20 SMITH,FORD,ADAMS,SCOTT,JONES

? ? ? ? 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL> select * from salgrade;

? ? ?GRADE? ? ? LOSAL? ? ? HISAL

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

? ? ? ? ?1? ? ? ? 700? ? ? ?1200

? ? ? ? ?2? ? ? ?1201? ? ? ?1400

? ? ? ? ?3? ? ? ?1401? ? ? ?2000

? ? ? ? ?4? ? ? ?2001? ? ? ?3000

? ? ? ? ?5? ? ? ?3001? ? ? ?9999

SQL> select avg(sal),sum(sal) from emp;

? AVG(SAL)? ?SUM(SAL)

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

2073.21429? ? ? 29025

SQL> select min(sal),max(sal) from emp;

? MIN(SAL)? ?MAX(SAL)

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

? ? ? ?800? ? ? ?5000

SQL> select count(*) from emp;

? COUNT(*)

----------

? ? ? ? 14

SQL> select count(empno) from emp;

COUNT(EMPNO)

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

? ? ? ? ? 14

SQL> select count(deptno) from emp;

COUNT(DEPTNO)

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

? ? ? ? ? ?14

SQL> select depno from emp;

select depno from emp

? ? ? ?*

第 1 行出現錯誤:

ORA-00904: "DEPNO": 標識符無效

SQL> select deptno from emp;

? ? DEPTNO

----------

? ? ? ? 20

? ? ? ? 30

? ? ? ? 30

? ? ? ? 20

? ? ? ? 30

? ? ? ? 30

? ? ? ? 10

? ? ? ? 20

? ? ? ? 10

? ? ? ? 30

? ? ? ? 20

? ? DEPTNO

----------

? ? ? ? 30

? ? ? ? 20

? ? ? ? 10

已選擇14行。

SQL> select count(distinct deptno) from emp;

COUNT(DISTINCTDEPTNO)

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

? ? ? ? ? ? ? ? ? ? 3

scott?

下有個員工表emp

host cls 清屏

實現oracle的分頁查詢一定需要嵌套子查詢來實現

和mysql 的 limit方式不同---

select rownum,r,empno,ename,sal

from (select rownum r,empno,ename,sal

from (select rownum,empno,ename,sal from emp order by sal desc) e1

where rownum<=8) e2

where r>=5;

子查詢注意的問題