Oracle Day3 多行函数、多表查询

时间：2022-03-14 18:41

1.多行函数

Sum avg max min count
组函数具有滤空的作用(添加nvl屏蔽该功能)
分组groupby
多行分组
分组过滤
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 70 71 SQL> select sum(sal)/count(sal)) from emp; 72 select sum(sal)/count(sal)) from emp 73 * 74 第 1 行出现错误: 75 ORA-00923: 未找到要求的 FROM 关键字 76 77 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 373 374 SQL> select deptno,sum(sal),avg(sal) 375 2 from emp 376 3 group by deptno;d 377 4 ; 378 group by deptno;d 379 * 380 第 3 行出现错误: 381 ORA-00911: 无效字符 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 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