Oracle Day3 多行函数、多表查询
1.多行函数
- Sum avg max min count
- 组函数具有滤空的作用(添加nvl屏蔽该功能)
- 分组group by
- 多行分组
- 分组过滤
- where 和分组过滤的区别（having)
- 分组的增强(rollup)
break on deptno skip 2;
break on null;
1 SQL> -- 计算一下每一个月要发多少工资,不含奖金 2 SQL> select sum(sal) from emp; 3 4 SUM(SAL) 5 ---------- 6 29025 7 8 SQL> select sal from emp; 9 10 SAL 11 ---------- 12 800 13 1600 14 1250 15 2975 16 1250 17 2850 18 2450 19 3000 20 5000 21 1500 22 1100 23 24 SAL 25 ---------- 26 950 27 3000 28 1300 29 30 已选择14行。 31 32 SQL> -- 计算一下每年发的奖金的和 33 SQL> select sum(comm) from emp; 34 35 SUM(COMM) 36 ---------- 37 2200 38 39 SQL> select comm from emp; 40 41 COMM 42 ---------- 43 44 300 45 500 46 47 1400 48 49 50 51 52 0 53 54 55 COMM 56 ---------- 57 58 59 60 61 已选择14行。 62 63 SQL> -- 多行函数自动虑空 64 SQL> -- 计算一下公司的平均工资 65 SQL> select sum(sal)/count(*) from emp; 66 67 SUM(SAL)/COUNT(*) 68 ----------------- 69 2073.21429 76 78 SQL> select sum(sal)/count(sal) from emp; 79 80 SUM(SAL)/COUNT(SAL) 81 ------------------- 82 2073.21429 83 84 SQL> select avg(sal) from emp; 85 86 AVG(SAL) 87 ---------- 88 2073.21429 89 90 SQL> -- 计算一下平均奖金 91 SQL> select sum(comm)/count(*) from emp; 92 93 SUM(COMM)/COUNT(*) 94 ------------------ 95 157.142857 96 97 SQL> select sum(comm)/count(comm) from emp; 98 99 SUM(COMM)/COUNT(COMM) 100 --------------------- 101 550 错误方法 102 103 SQL> select avg(comm) from emp; 104 105 AVG(COMM) 106 ---------- 107 550 错误方法 108 109 SQL> -- 多行函数的虑空并不是在所有的场合都适用,如果你不希望他的虑空起作用你可以采用函数的嵌套来屏蔽该功能 110 SQL> select sum(comm)/count(*),avg(nvl(comm,0)) from emp; 111 112 SUM(COMM)/COUNT(*) AVG(NVL(COMM,0)) 113 ------------------ ---------------- 114 157.142857 157.142857 115 116 SQL> -- 查询工资最高和最低的员工信息 117 SQL> select max(sal),min(sal) from emp; 118 119 MAX(SAL) MIN(SAL) 120 ---------- ---------- 121 5000 800 122 123 SQL> select max(comm),min(comm) from emp; 124 125 MAX(COMM) MIN(COMM) 126 ---------- ---------- 127 1400 0 128 129 SQL> -- 分组 130 SQL> -- 求每一个部门的工资总和 和平均工资 131 SQL> select deptno,sum(sal),avg(sal) 132 2 from emp 133 3 group by deptno; 134 135 DEPTNO SUM(SAL) AVG(SAL) 136 ---------- ---------- ---------- 137 30 9400 1566.66667 138 20 10875 2175 139 10 8750 2916.66667 140 141 SQL> -- 统计部门的平均工资，部门号，岗位 142 SQL> select deptno,avg(sal),job 143 2 from emp 144 3 group by deptno,job; 145 146 DEPTNO AVG(SAL) JOB 147 ---------- ---------- ------------------ 148 20 950 CLERK 149 30 1400 SALESMAN 150 20 2975 MANAGER 151 30 950 CLERK 152 10 5000 PRESIDENT 153 30 2850 MANAGER 154 10 1300 CLERK 155 10 2450 MANAGER 156 20 3000 ANALYST 157 158 已选择9行。 159 160 SQL> -- group by 后面必须要跟select后面没有在多行函数里面的字段 161 SQL> -- 分组函数的过滤 162 SQL> -- 统计部门号为20的部门下的所有职位的平均工资 163 SQL> select deptno,avg(sal),job 164 2 from emp 165 3 where deptno=20 166 4 group by deptno,job; 167 168 DEPTNO AVG(SAL) JOB 169 ---------- ---------- ------------------ 170 20 950 CLERK 171 20 2975 MANAGER 172 20 3000 ANALYST 173 174 SQL> select deptno,avga(sal),job 175 2 from emp 176 3 group by deptno,job 177 4 having deptno=20; 178 select deptno,avga(sal),job 179 * 180 第 1 行出现错误: 181 ORA-00904: "AVGA": 标识符无效 182 183 184 SQL> c /avga(sal)/avg(sal); 185 1* select deptno,avg(sal),job 186 SQL> / 187 188 DEPTNO AVG(SAL) JOB 189 ---------- ---------- ------------------ 190 20 950 CLERK 191 20 2975 MANAGER 192 20 3000 ANALYST 193 194 SQL> -- 统计平均工资大于2000的部门 195 SQL> select deptno,avg(sal) 196 2 from emp 197 3 where avg(sal) >2000 198 4 group by deptno,job; 199 where avg(sal) >2000 200 * 201 第 3 行出现错误: 202 ORA-00934: 此处不允许使用分组函数 203 204 205 SQL> select deptno,avg(sal) 206 2 from emp 207 3 group by deptno 208 4 having avg(sal)>2000; 209 210 DEPTNO AVG(SAL) 211 ---------- ---------- 212 20 2175 213 10 2916.66667 214 215 SQL> --1. where和having都可以用来做条件的过滤操作,但是where后面不能跟分组函数,having后面可以跟分组函数 216 SQL> -- 2 尽量使用where 因为他的效率更高 217 SQL> select deptno,job,sum(sal) 218 2 from emp 219 3 group by rollup(deptno,job); 220 221 DEPTNO JOB SUM(SAL) 222 ---------- ------------------ ---------- 223 10 CLERK 1300 224 10 MANAGER 2450 225 10 PRESIDENT 5000 226 10 8750 227 20 CLERK 1900 228 20 ANALYST 6000 229 20 MANAGER 2975 230 20 10875 231 30 CLERK 950 232 30 MANAGER 2850 233 30 SALESMAN 5600 234 235 DEPTNO JOB SUM(SAL) 236 ---------- ------------------ ---------- 237 30 9400 238 29025 239 240 已选择13行。 241 242 SQL> break on deptno skip 2; 243 SQL> / 244 245 DEPTNO JOB SUM(SAL) 246 ---------- ------------------ ---------- 247 10 CLERK 1300 248 MANAGER 2450 249 PRESIDENT 5000 250 8750 251 252 253 20 CLERK 1900 254 ANALYST 6000 255 MANAGER 2975 256 10875 257 258 259 DEPTNO JOB SUM(SAL) 260 ---------- ------------------ ---------- 261 262 30 CLERK 950 263 MANAGER 2850 264 SALESMAN 5600 265 9400 266 267 268 29025 269 270 271 272 已选择13行。 273 274 SQL> --break on deptno skip 2;去除deptno后相同的no,并空两行； 275 SQL> break on null; 276 SQL> / 277 278 DEPTNO JOB SUM(SAL) 279 ---------- ------------------ ---------- 280 10 CLERK 1300 281 10 MANAGER 2450 282 10 PRESIDENT 5000 283 10 8750 284 20 CLERK 1900 285 20 ANALYST 6000 286 20 MANAGER 2975 287 20 10875 288 30 CLERK 950 289 30 MANAGER 2850 290 30 SALESMAN 5600 291 292 DEPTNO JOB SUM(SAL) 293 ---------- ------------------ ---------- 294 30 9400 295 29025 296 297 已选择13行。 298 299 SQL> break on deptno skip 3; 300 SQL> / 301 302 DEPTNO JOB SUM(SAL) 303 ---------- ------------------ ---------- 304 10 CLERK 1300 305 MANAGER 2450 306 PRESIDENT 5000 307 8750 308 309 310 311 20 CLERK 1900 312 ANALYST 6000 313 MANAGER 2975 314 10875 315 316 DEPTNO JOB SUM(SAL) 317 ---------- ------------------ ---------- 318 319 320 321 30 CLERK 950 322 MANAGER 2850 323 SALESMAN 5600 324 9400 325 326 327 328 29025 329 330 已选择13行。 331 332 SQL> select sum(sal) from emp; 333 334 SUM(SAL) 335 ---------- 336 29025 337 338 SQL> select sum(comm) from emp; 339 340 SUM(COMM) 341 ---------- 342 2200 343 344 SQL> select sum(sal)/count(*) from emp; 345 346 SUM(SAL)/COUNT(*) 347 ----------------- 348 2073.21429 349 350 SQL> select sum(sal)/count(sal) from emp; 351 352 SUM(SAL)/COUNT(SAL) 353 ------------------- 354 2073.21429 355 356 SQL> select avg(sal) from emp; 357 358 AVG(SAL) 359 ---------- 360 2073.21429 361 362 SQL> select max(sal),min(sal) from emp; 363 364 MAX(SAL) MIN(SAL) 365 ---------- ---------- 366 5000 800 367 368 SQL> select max(comm),min(comm) from emp; 369 370 MAX(COMM) MIN(COMM) 371 ---------- ---------- 372 1400 0 382 383 384 SQL> select deptno,sum(sal),avg(sal) 385 2 from emp 386 3 group by deptno; 387 388 DEPTNO SUM(SAL) AVG(SAL) 389 ---------- ---------- ---------- 390 30 9400 1566.66667 391 392 393 394 20 10875 2175 395 396 397 398 10 8750 2916.66667 399 400 401 402 403 SQL> select deptno,avg(sal),job 404 2 from emp 405 3 group by deptno,job; 406 407 DEPTNO AVG(SAL) JOB 408 ---------- ---------- ------------------ 409 20 950 CLERK 410 411 412 413 30 1400 SALESMAN 414 415 416 417 20 2975 MANAGER 418 419 420 421 DEPTNO AVG(SAL) JOB 422 ---------- ---------- ------------------ 423 424 30 950 CLERK 425 426 427 428 10 5000 PRESIDENT 429 430 431 432 30 2850 MANAGER 433 434 435 DEPTNO AVG(SAL) JOB 436 ---------- ---------- ------------------ 437 438 439 10 1300 CLERK 440 2450 MANAGER 441 442 443 444 20 3000 ANALYST 445 446 447 448 449 已选择9行。 450 451 SQL> select deptno,avg(sal),job 452 2 from emp 453 3 where deptno=20 454 4 group by deptno,job; 455 456 DEPTNO AVG(SAL) JOB 457 ---------- ---------- ------------------ 458 20 950 CLERK 459 2975 MANAGER 460 3000 ANALYST 461 462 463 464 465 SQL> select deptno,avg(sal),job 466 2 from emp 467 3 group by deotno,job 468 4 having deptno=20; 469 group by deotno,job 470 * 471 第 3 行出现错误: 472 ORA-00904: "DEOTNO": 标识符无效 473 474 /*
c:用来改错（只能改一个单词）
*/ 475 SQL> c /deotno/deptno; 476 3* group by deptno,job 477 SQL> / 478 479 DEPTNO AVG(SAL) JOB 480 ---------- ---------- ------------------ 481 20 950 CLERK 482 2975 MANAGER 483 3000 ANALYST 484 485 486 487 488 SQL> select deptno,avg(sal),job 489 2 from emp 490 3 group by deptno,job 491 4 having avg(sal)>2000; 492 493 DEPTNO AVG(SAL) JOB 494 ---------- ---------- ------------------ 495 20 2975 MANAGER 496 497 498 499 10 5000 PRESIDENT 500 501 502 503 30 2850 MANAGER 504 505 506 507 DEPTNO AVG(SAL) JOB 508 ---------- ---------- ------------------ 509 510 10 2450 MANAGER 511 512 513 514 20 3000 ANALYST 515 516 517 518 519 SQL> select deptno,job,sum(sal) 520 2 from emp 521 3 group by rollup(deptno,job); 522 523 DEPTNO JOB SUM(SAL) 524 ---------- ------------------ ---------- 525 10 CLERK 1300 526 MANAGER 2450 527 PRESIDENT 5000 528 8750 529 530 531 532 20 CLERK 1900 533 ANALYST 6000 534 MANAGER 2975 535 10875 536 537 DEPTNO JOB SUM(SAL) 538 ---------- ------------------ ---------- 539 540 541 542 30 CLERK 950 543 MANAGER 2850 544 SALESMAN 5600 545 9400 546 547 548 549 29025 550 551 已选择13行。 552 553 SQL> break on null; 554 SQL> / 555 556 DEPTNO JOB SUM(SAL) 557 ---------- ------------------ ---------- 558 10 CLERK 1300 559 10 MANAGER 2450 560 10 PRESIDENT 5000 561 10 8750 562 20 CLERK 1900 563 20 ANALYST 6000 564 20 MANAGER 2975 565 20 10875 566 30 CLERK 950 567 30 MANAGER 2850 568 30 SALESMAN 5600 569 570 DEPTNO JOB SUM(SAL) 571 ---------- ------------------ ---------- 572 30 9400 573 29025 574 575 已选择13行。 576 577 SQL> break on deptno skip 2; 578 SQL> / 579 580 DEPTNO JOB SUM(SAL) 581 ---------- ------------------ ---------- 582 10 CLERK 1300 583 MANAGER 2450 584 PRESIDENT 5000 585 8750 586 587 588 20 CLERK 1900 589 ANALYST 6000 590 MANAGER 2975 591 10875 592 593 594 DEPTNO JOB SUM(SAL) 595 ---------- ------------------ ---------- 596 597 30 CLERK 950 598 MANAGER 2850 599 SALESMAN 5600 600 9400 601 602 603 29025 604 605 606 607 已选择13行。 608 609 SQL> spool off;
2.多表查询
1）笛卡尔集
2）等值连接
3）非等值连接
4）外连接
5）内连接
6）层次查询(只能有一张表 connect by prior empno = mgr start with empno = 7839)
1 SQL> -- 查询所有部门的信息和部门下的所有员工信息 2 SQL> select e.empno,e.ename,job,d.deptno,d.dname 3 2 from emp e,dept d 4 3 where e.deptno=d.deptno; 5 6 EMPNO ENAME JOB DEPTNO DNAME 7 ---------- ------------------------------ ------------------ ---------- ---------------------------- 8 7369 SMITH CLERK 20 RESEARCH 9 10 11 7499 ALLEN SALESMAN 30 SALES 12 7521 WARD SALESMAN SALES 13 14 15 7566 JONES MANAGER 20 RESEARCH 16 17 18 7654 MARTIN SALESMAN 30 SALES 19 20 EMPNO ENAME JOB DEPTNO DNAME 21 ---------- ------------------------------ ------------------ ---------- ---------------------------- 22 7698 BLAKE MANAGER 30 SALES 23 24 25 7782 CLARK MANAGER 10 ACCOUNTING 26 27 28 7788 SCOTT ANALYST 20 RESEARCH 29 30 31 7839 KING PRESIDENT 10 ACCOUNTING 32 33 34 EMPNO ENAME JOB DEPTNO DNAME 35 ---------- ------------------------------ ------------------ ---------- ---------------------------- 36 37 7844 TURNER SALESMAN 30 SALES 38 39 40 7876 ADAMS CLERK 20 RESEARCH 41 42 43 7900 JAMES CLERK 30 SALES 44 45 46 7902 FORD ANALYST 20 RESEARCH 47 48 EMPNO ENAME JOB DEPTNO DNAME 49 ---------- ------------------------------ ------------------ ---------- ---------------------------- 50 51 52 7934 MILLER CLERK 10 ACCOUNTING 53 54 55 56 已选择14行。 57 58 SQL> select * from tab; 59 60 TNAME TABTYPE CLUSTERID 61 ------------------------------------------------------------ -------------- ---------- 62 DEPT TABLE 63 EMP TABLE 64 BONUS TABLE 65 SALGRADE TABLE 66 67 SQL> select *from salgrade; 68 69 GRADE LOSAL HISAL 70 ---------- ---------- ---------- 71 1 700 1200 72 2 1201 1400 73 3 1401 2000 74 4 2001 3000 75 5 3001 9999 76 77 SQL> -- 查询员工信息和员工的工资级别 78 SQL> select e.empno,e.sal,s.grade,s.losal,s.hisal 79 2 from emp e,salgrade s 80 3 where e.sal between s.losal and s.hisal; 81 82 EMPNO SAL GRADE LOSAL HISAL 83 ---------- ---------- ---------- ---------- ---------- 84 7369 800 1 700 1200 85 7900 950 1 700 1200 86 7876 1100 1 700 1200 87 7521 1250 2 1201 1400 88 7654 1250 2 1201 1400 89 7934 1300 2 1201 1400 90 7844 1500 3 1401 2000 91 7499 1600 3 1401 2000 92 7782 2450 4 2001 3000 93 7698 2850 4 2001 3000 94 7566 2975 4 2001 3000 95 96 EMPNO SAL GRADE LOSAL HISAL 97 ---------- ---------- ---------- ---------- ---------- 98 7788 3000 4 2001 3000 99 7902 3000 4 2001 3000 100 7839 5000 5 3001 9999 101 102 已选择14行。 103 104 SQL> -- 不等值连接 105 SQL> -- 外连接 107 SQL> -- 统计每一个部门的人数 108 SQL> -- 显示部门号 部门名称 部门人数 109 SQL> select e.deptno,d.dname,count(*) 110 2 from emp e,dept d 111 3 where e.deptno=d.deptno 112 4 group by e.deptno,d.dname; 113 114 DEPTNO DNAME COUNT(*) 115 ---------- ---------------------------- ---------- 116 10 ACCOUNTING 3 117 118 119 20 RESEARCH 5 120 121 122 30 SALES 6 123 124 125 126 SQL> select * from dept; 127 128 DEPTNO DNAME LOC 129 ---------- ---------------------------- -------------------------- 130 10 ACCOUNTING NEW YORK 131 132 133 20 RESEARCH DALLAS 134 135 136 30 SALES CHICAGO 137 138 139 40 OPERATIONS BOSTON 140 141 142 143 SQL> /* 144 SQL> 145 SQL> 外连接:分为左外连接和右外连接 146 SQL> 左外连接: where e.deptno = d.deptno(+) 不成立的时候,无论右边是否有数据与之对应,左边的都要显示 147 SQL> where e.deptno = d.depton(+) 148 SQL> 右外连接: where e.deptno(+) = d.deptno 不成立的时候,无论左边表是否有数据与之对应,右边的都会显示 149 SQL> where e.deptno(+) = d.deptno; 150 SQL> */ 151 SQL> select d.deptno,d.dname,count(e.empno) 152 2 from emp e,dept d 153 3 where e.deptno(+)=d.deptno 154 4 group by d.deptno,d.deptname; 155 group by d.deptno,d.deptname 156 * 157 第 4 行出现错误: 158 ORA-00904: "D"."DEPTNAME": 标识符无效 159 160 161 SQL> c /d.deptname/d.dname; 162 4* group by d.deptno,d.dname 163 SQL> / 164 165 DEPTNO DNAME COUNT(E.EMPNO) 166 ---------- ---------------------------- -------------- 167 10 ACCOUNTING 3 168 169 170 40 OPERATIONS 0 171 172 173 20 RESEARCH 5 174 175 176 30 SALES 6 177 178 179 180 SQL> select d.deptno,d.dname,count(e.empno) 181 2 from emp e,dept d 182 3 where e.deptno=d.deptno(+) 183 4 group by d.deptno,d.dname; 184 185 DEPTNO DNAME COUNT(E.EMPNO) 186 ---------- ---------------------------- -------------- 187 10 ACCOUNTING 3 188 189 190 20 RESEARCH 5 191 192 193 30 SALES 6 194 195 196 197 SQL> select e.ename 领导名字,e.empno 领导id,p.ename 员工名字,p.empno 员工id 198 2 from emp e,emp p 199 3 where e.ename=p.mgr; 200 where e.ename=p.mgr 201 * 202 第 3 行出现错误: 203 ORA-01722: 无效数字 204 205 206 SQL> c /e.ename/e.empno; 207 3* where e.empno=p.mgr 208 SQL> / 209 210 领导名字 领导ID 员工名字 员工ID 211 -------------------- ---------- -------------------- ---------- 212 FORD 7902 SMITH 7369 213 BLAKE 7698 ALLEN 7499 214 BLAKE 7698 WARD 7521 215 KING 7839 JONES 7566 216 BLAKE 7698 MARTIN 7654 217 KING 7839 BLAKE 7698 218 KING 7839 CLARK 7782 219 JONES 7566 SCOTT 7788 220 BLAKE 7698 TURNER 7844 221 SCOTT 7788 ADAMS 7876 222 BLAKE 7698 JAMES 7900 223 224 领导名字 领导ID 员工名字 员工ID 225 -------------------- ---------- -------------------- ---------- 226 JONES 7566 FORD 7902 227 CLARK 7782 MILLER 7934 228 229 已选择13行。 230 231 SQL> select count(*) from emp e,emp p 232 2 where e.empno=p.mgr; 233 234 COUNT(*) 235 ---------- 236 13 237 238 SQL> select count(*) from emp e,emp p; 239 240 COUNT(*) 241 ---------- 242 196 243 244 SQL> -- 内连接只能做数据量小的表,对于大表用层次查询 245 SQL> -- 层次查询 246 SQL> select ename 领导名字,empno 领导id 247 2 from emp 248 3 connect by prior empno=mgr 249 4 start with mgr is null; 250 251 领导名字 领导ID 252 -------------------- ---------- 253 KING 7839 254 JONES 7566 255 SCOTT 7788 256 ADAMS 7876 257 FORD 7902 258 SMITH 7369 259 BLAKE 7698 260 ALLEN 7499 261 WARD 7521 262 MARTIN 7654 263 TURNER 7844 264 265 领导名字 领导ID 266 -------------------- ---------- 267 JAMES 7900 268 CLARK 7782 269 MILLER 7934 270 271 已选择14行。
本文出自wentaokyle，转载请标明出处！谢谢！
