Oracle Day2 过滤、排序、单行函数
1.过滤和排序
1 SQL> --查询10号部门的所有员工信息 2 SQL> select * 3 2 from emp 4 3 where empno = 10; 5 6 未选定行 7 8 SQL> ed 9 SP2-0110: 无法创建保存文件 "afiedt.buf" 10 SQL> 3 11 3* where empno = 10 12 SQL> c /empno/deptno; 13 3* where deptno = 10 14 SQL> / 15 16 EMPNO ENAME JOB MGR HIREDATE 17 ---------- -------------------- ------------------ ---------- -------------- 18 SAL COMM DEPTNO 19 ---------- ---------- ---------- 20 7782 CLARK MANAGER 7839 09-6月 -81 21 2450 10 22 23 7839 KING PRESIDENT 17-11月-81 24 5000 10 25 26 7934 MILLER CLERK 7782 23-1月 -82 27 1300 10 28 29 30 SQL> set linesize 120l 31 SP2-0268: linesize 选项的编号无效 32 SQL> set linesize 120; 33 SQL> / 34 35 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 36 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 37 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 38 7839 KING PRESIDENT 17-11月-81 5000 10 39 7934 MILLER CLERK 7782 23-1月 -82 1300 10 40 41 SQL> -- 查询名字为KING的员工信息 42 SQL> select * 43 2 from emp 44 3 where ename = 'KING'; 45 46 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 47 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 48 7839 KING PRESIDENT 17-11月-81 5000 10 49 50 SQL> 3 51 3* where ename = 'KING' 52 SQL> c /KING/King; 53 3* where ename = 'King' 54 SQL> / 55 56 未选定行 57 58 SQL> --在oracle中字段的名称是不区分大小写的，但是字段里面的值是区分大小的 59 SQL> --查询入职日期是81-11-17号的员工信息 60 SQL> select * 61 2 from emp 62 3 where hiredate = '17-11月-81'; 63 64 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 65 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 66 7839 KING PRESIDENT 17-11月-81 5000 10 67 68 SQL> -- 在oracle中时间的表示格式是 DD-MON-RR 69 SQL> select * from 70 2 emp 71 3 where hiredate = '1981-11-17'; 72 where hiredate = '1981-11-17' 73 * 74 第 3 行出现错误: 75 ORA-01861: 文字与格式字符串不匹配 76 77 78 SQL> --查询oracle中的一般常量设置 79 SQL> select * from v$nls_paramters; 80 select * from v$nls_paramters 81 * 82 第 1 行出现错误: 83 ORA-00942: 表或视图不存在 84 85 86 SQL> select * from v$nls_parameters; 87 88 PARAMETER 89 ------------------------------------------------------------------------------------------------------------------------ 90 VALUE 91 ------------------------------------------------------------------------------------------------------------------------ 92 NLS_LANGUAGE 93 SIMPLIFIED CHINESE 94 95 NLS_TERRITORY 96 CHINA 97 98 NLS_CURRENCY 99 ￥ 100 101 102 PARAMETER 103 ------------------------------------------------------------------------------------------------------------------------ 104 VALUE 105 ------------------------------------------------------------------------------------------------------------------------ 106 NLS_ISO_CURRENCY 107 CHINA 108 109 NLS_NUMERIC_CHARACTERS 110 ., 111 112 NLS_CALENDAR 113 GREGORIAN 114 115 116 PARAMETER 117 ------------------------------------------------------------------------------------------------------------------------ 118 VALUE 119 ------------------------------------------------------------------------------------------------------------------------ 120 NLS_DATE_FORMAT 121 DD-MON-RR 122 123 NLS_DATE_LANGUAGE 124 SIMPLIFIED CHINESE 125 126 NLS_CHARACTERSET 127 AL32UTF8 128 129 130 PARAMETER 131 ------------------------------------------------------------------------------------------------------------------------ 132 VALUE 133 ------------------------------------------------------------------------------------------------------------------------ 134 NLS_SORT 135 BINARY 136 137 NLS_TIME_FORMAT 138 HH.MI.SSXFF AM 139 140 NLS_TIMESTAMP_FORMAT 141 DD-MON-RR HH.MI.SSXFF AM 142 143 144 PARAMETER 145 ------------------------------------------------------------------------------------------------------------------------ 146 VALUE 147 ------------------------------------------------------------------------------------------------------------------------ 148 NLS_TIME_TZ_FORMAT 149 HH.MI.SSXFF AM TZR 150 151 NLS_TIMESTAMP_TZ_FORMAT 152 DD-MON-RR HH.MI.SSXFF AM TZR 153 154 NLS_DUAL_CURRENCY 155 ￥ 156 157 158 PARAMETER 159 ------------------------------------------------------------------------------------------------------------------------ 160 VALUE 161 ------------------------------------------------------------------------------------------------------------------------ 162 NLS_NCHAR_CHARACTERSET 163 AL16UTF16 164 165 NLS_COMP 166 BINARY 167 168 NLS_LENGTH_SEMANTICS 169 BYTE 170 171 172 PARAMETER 173 ------------------------------------------------------------------------------------------------------------------------ 174 VALUE 175 ------------------------------------------------------------------------------------------------------------------------ 176 NLS_NCHAR_CONV_EXCP 177 FALSE 178 179 180 已选择19行。 181 182 SQL> set linesize 200; 183 SQL> / 184 185 PARAMETER 186 -------------------------------------------------------------------------------------------------------------------------------- 187 VALUE 188 -------------------------------------------------------------------------------------------------------------------------------- 189 NLS_LANGUAGE 190 SIMPLIFIED CHINESE 191 192 NLS_TERRITORY 193 CHINA 194 195 NLS_CURRENCY 196 ￥ 197 198 199 PARAMETER 200 -------------------------------------------------------------------------------------------------------------------------------- 201 VALUE 202 -------------------------------------------------------------------------------------------------------------------------------- 203 NLS_ISO_CURRENCY 204 CHINA 205 206 NLS_NUMERIC_CHARACTERS 207 ., 208 209 NLS_CALENDAR 210 GREGORIAN 211 212 213 PARAMETER 214 -------------------------------------------------------------------------------------------------------------------------------- 215 VALUE 216 -------------------------------------------------------------------------------------------------------------------------------- 217 NLS_DATE_FORMAT 218 DD-MON-RR 219 220 NLS_DATE_LANGUAGE 221 SIMPLIFIED CHINESE 222 223 NLS_CHARACTERSET 224 AL32UTF8 225 226 227 PARAMETER 228 -------------------------------------------------------------------------------------------------------------------------------- 229 VALUE 230 -------------------------------------------------------------------------------------------------------------------------------- 231 NLS_SORT 232 BINARY 233 234 NLS_TIME_FORMAT 235 HH.MI.SSXFF AM 236 237 NLS_TIMESTAMP_FORMAT 238 DD-MON-RR HH.MI.SSXFF AM 239 240 241 PARAMETER 242 -------------------------------------------------------------------------------------------------------------------------------- 243 VALUE 244 -------------------------------------------------------------------------------------------------------------------------------- 245 NLS_TIME_TZ_FORMAT 246 HH.MI.SSXFF AM TZR 247 248 NLS_TIMESTAMP_TZ_FORMAT 249 DD-MON-RR HH.MI.SSXFF AM TZR 250 251 NLS_DUAL_CURRENCY 252 ￥ 253 254 255 PARAMETER 256 -------------------------------------------------------------------------------------------------------------------------------- 257 VALUE 258 -------------------------------------------------------------------------------------------------------------------------------- 259 NLS_NCHAR_CHARACTERSET 260 AL16UTF16 261 262 NLS_COMP 263 BINARY 264 265 NLS_LENGTH_SEMANTICS 266 BYTE 267 268 269 PARAMETER 270 -------------------------------------------------------------------------------------------------------------------------------- 271 VALUE 272 -------------------------------------------------------------------------------------------------------------------------------- 273 NLS_NCHAR_CONV_EXCP 274 FALSE 275 276 277 已选择19行。 278 279 SQL> col parameter for a50; 280 SQL> / 281 282 PARAMETER VALUE 283 -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 284 NLS_LANGUAGE SIMPLIFIED CHINESE 285 NLS_TERRITORY CHINA 286 NLS_CURRENCY ￥ 287 NLS_ISO_CURRENCY CHINA 288 NLS_NUMERIC_CHARACTERS ., 289 NLS_CALENDAR GREGORIAN 290 NLS_DATE_FORMAT DD-MON-RR 291 NLS_DATE_LANGUAGE SIMPLIFIED CHINESE 292 NLS_CHARACTERSET AL32UTF8 293 NLS_SORT BINARY 294 NLS_TIME_FORMAT HH.MI.SSXFF AM 295 296 PARAMETER VALUE 297 -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 298 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 299 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 300 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 301 NLS_DUAL_CURRENCY ￥ 302 NLS_NCHAR_CHARACTERSET AL16UTF16 303 NLS_COMP BINARY 304 NLS_LENGTH_SEMANTICS BYTE 305 NLS_NCHAR_CONV_EXCP FALSE 306 307 已选择19行。 308 309 SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd'; 310 311 会话已更改。 312 313 SQL> select * 314 2 from emp 315 3 where hiredate='1981-11-17'; 316 317 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 318 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 319 7839 KING PRESIDENT 1981-11-17 5000 10 320 321 SQL> select * 322 2 from emp 323 3 where hiredate='17-11月-81'; 324 where hiredate='17-11月-81' 325 * 326 第 3 行出现错误: 327 ORA-01861: 文字与格式字符串不匹配 328 329 330 SQL> select * from v$nls_parameters; 331 332 PARAMETER VALUE 333 -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 334 NLS_LANGUAGE SIMPLIFIED CHINESE 335 NLS_TERRITORY CHINA 336 NLS_CURRENCY ￥ 337 NLS_ISO_CURRENCY CHINA 338 NLS_NUMERIC_CHARACTERS ., 339 NLS_CALENDAR GREGORIAN 340 NLS_DATE_FORMAT yyyy-mm-dd 341 NLS_DATE_LANGUAGE SIMPLIFIED CHINESE 342 NLS_CHARACTERSET AL32UTF8 343 NLS_SORT BINARY 344 NLS_TIME_FORMAT HH.MI.SSXFF AM 345 346 PARAMETER VALUE 347 -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 348 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 349 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 350 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 351 NLS_DUAL_CURRENCY ￥ 352 NLS_NCHAR_CHARACTERSET AL16UTF16 353 NLS_COMP BINARY 354 NLS_LENGTH_SEMANTICS BYTE 355 NLS_NCHAR_CONV_EXCP FALSE 356 357 已选择19行。 358 359 SQL> select * 360 2 from emp 361 3 where ename='KING' and job='PRESIDENT'; 362 363 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 364 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 365 7839 KING PRESIDENT 1981-11-17 5000 10 366 367 SQL> --查询工资在2000-5000之间的所有员工 368 SQL> select * 369 2 from emp 370 3 where sal>2000 and sal < 5000; 371 372 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 373 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 374 7566 JONES MANAGER 7839 1981-04-02 2975 20 375 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 376 7782 CLARK MANAGER 7839 1981-06-09 2450 10 377 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 378 7902 FORD ANALYST 7566 1981-12-03 3000 20 379 380 SQL> select * 381 2 from emp 382 3 where sal between 2000 and 5000; 383 384 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 385 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 386 7566 JONES MANAGER 7839 1981-04-02 2975 20 387 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 388 7782 CLARK MANAGER 7839 1981-06-09 2450 10 389 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 390 7839 KING PRESIDENT 1981-11-17 5000 10 391 7902 FORD ANALYST 7566 1981-12-03 3000 20 392 393 已选择6行。 394 395 SQL> select * 396 2 from emp 397 3 where sal between 5000 and 2000; 398 399 未选定行 400 401 SQL> --between ...and 只能将小的写在前面大的写在后面 402 SQL> -- in/not in 403 SQL> --查询20?30号部门的所有员工 404 SQL> select * 405 2 from emp 406 3 where deptno = 20 or deptno = 30; 407 408 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 409 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 410 7369 SMITH CLERK 7902 1980-12-17 800 20 411 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 412 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 413 7566 JONES MANAGER 7839 1981-04-02 2975 20 414 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 415 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 416 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 417 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 418 7876 ADAMS CLERK 7788 1987-05-23 1100 20 419 7900 JAMES CLERK 7698 1981-12-03 950 30 420 7902 FORD ANALYST 7566 1981-12-03 3000 20 421 422 已选择11行。 423 424 SQL> select * 425 2 from emp 426 3 where deptno in(20,30); 427 428 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 429 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 430 7369 SMITH CLERK 7902 1980-12-17 800 20 431 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 432 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 433 7566 JONES MANAGER 7839 1981-04-02 2975 20 434 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 435 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 436 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 437 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 438 7876 ADAMS CLERK 7788 1987-05-23 1100 20 439 7900 JAMES CLERK 7698 1981-12-03 950 30 440 7902 FORD ANALYST 7566 1981-12-03 3000 20 441 442 已选择11行。 443 444 SQL> -- 查询不在20和30号部门里面的所有员工 445 SQL> select * 446 2 from emp 447 3 where deptno != 20 and deptno != 30; 448 449 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 450 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 451 7782 CLARK MANAGER 7839 1981-06-09 2450 10 452 7839 KING PRESIDENT 1981-11-17 5000 10 453 7934 MILLER CLERK 7782 1982-01-23 1300 10 454 455 SQL> select * 456 2 from emp 457 3 where deptno not in(20,30); 458 459 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 460 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 461 7782 CLARK MANAGER 7839 1981-06-09 2450 10 462 7839 KING PRESIDENT 1981-11-17 5000 10 463 7934 MILLER CLERK 7782 1982-01-23 1300 10 464 465 SQL> -- 查询奖金为300和为空的所有员工信息 466 SQL> select * 467 2 from emp 468 3 where comm in(300,null); 469 470 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 471 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 472 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 473 474 SQL> select * 475 2 from emp 476 3 where comm not in (300,null); 477 478 未选定行 479 480 SQL> -- in里面可以跟null，而not in 里面不能跟null 481 SQL> -- 查询comm为空的所有员工信息 482 SQL> select * 483 2 from emp 484 3 where comm is null; 485 486 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 487 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 488 7369 SMITH CLERK 7902 1980-12-17 800 20 489 7566 JONES MANAGER 7839 1981-04-02 2975 20 490 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 491 7782 CLARK MANAGER 7839 1981-06-09 2450 10 492 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 493 7839 KING PRESIDENT 1981-11-17 5000 10 494 7876 ADAMS CLERK 7788 1987-05-23 1100 20 495 7900 JAMES CLERK 7698 1981-12-03 950 30 496 7902 FORD ANALYST 7566 1981-12-03 3000 20 497 7934 MILLER CLERK 7782 1982-01-23 1300 10 498 499 已选择10行。 500 501 SQL> -- 模糊查询 502 SQL> -- 查询员工名字里面包含了O的所有员工信息 503 SQL> select * 504 2 from emp 505 3 where ename like '%O%'; 506 507 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 508 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 509 7566 JONES MANAGER 7839 1981-04-02 2975 20 510 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 511 7902 FORD ANALYST 7566 1981-12-03 3000 20 512 513 SQL> -- like 是一个关键字 表示模糊查询，其中的% 代表通配 514 SQL> -- 查询员工名字里面以K打头的员工信息 515 SQL> select * 516 2 from emp 517 3 where ename like 'K%'; 518 519 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 520 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 521 7839 KING PRESIDENT 1981-11-17 5000 10 522 523 SQL> -- 查询ename的长度为4的所有员工信息 524 SQL> select * 525 2 from emp 526 3 where ename like '____'; 527 528 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 529 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 530 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 531 7839 KING PRESIDENT 1981-11-17 5000 10 532 7902 FORD ANALYST 7566 1981-12-03 3000 20 533 534 SQL> -- 在oracle中_ 代表一个字符 535 SQL> insert into emp(empno,ename,sal,deptno) values(1001,'TOM_ab',50000,20); 536 537 已创建 1 行。 538 539 SQL> select * from emp; 540 541 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 542 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 543 7369 SMITH CLERK 7902 1980-12-17 800 20 544 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 545 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 546 7566 JONES MANAGER 7839 1981-04-02 2975 20 547 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 548 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 549 7782 CLARK MANAGER 7839 1981-06-09 2450 10 550 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 551 7839 KING PRESIDENT 1981-11-17 5000 10 552 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 553 7876 ADAMS CLERK 7788 1987-05-23 1100 20 554 555 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 556 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 557 7900 JAMES CLERK 7698 1981-12-03 950 30 558 7902 FORD ANALYST 7566 1981-12-03 3000 20 559 7934 MILLER CLERK 7782 1982-01-23 1300 10 560 1001 TOM_ab 50000 20 561 562 已选择15行。 563 564 SQL> -- 查询ename字段里面包含了_的员工信息 565 SQL> select * 566 2 from emp 567 3 where ename like '%_%'; 568 569 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 570 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 571 7369 SMITH CLERK 7902 1980-12-17 800 20 572 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 573 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 574 7566 JONES MANAGER 7839 1981-04-02 2975 20 575 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 576 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 577 7782 CLARK MANAGER 7839 1981-06-09 2450 10 578 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 579 7839 KING PRESIDENT 1981-11-17 5000 10 580 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 581 7876 ADAMS CLERK 7788 1987-05-23 1100 20 582 583 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 584 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 585 7900 JAMES CLERK 7698 1981-12-03 950 30 586 7902 FORD ANALYST 7566 1981-12-03 3000 20 587 7934 MILLER CLERK 7782 1982-01-23 1300 10 588 1001 TOM_ab 50000 20 589 590 已选择15行。 591 592 SQL> -- oracle中的转义 593 SQL> select * 594 2 from emp 595 3 where ename like '%\_%' escape '\'; 596 597 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 598 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 599 1001 TOM_ab 50000 20 600 601 SQL> select * from emp; 602 603 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 604 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 605 7369 SMITH CLERK 7902 1980-12-17 800 20 606 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 607 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 608 7566 JONES MANAGER 7839 1981-04-02 2975 20 609 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 610 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 611 7782 CLARK MANAGER 7839 1981-06-09 2450 10 612 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 613 7839 KING PRESIDENT 1981-11-17 5000 10 614 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 615 7876 ADAMS CLERK 7788 1987-05-23 1100 20 616 617 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 618 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 619 7900 JAMES CLERK 7698 1981-12-03 950 30 620 7902 FORD ANALYST 7566 1981-12-03 3000 20 621 7934 MILLER CLERK 7782 1982-01-23 1300 10 622 1001 TOM_ab 50000 20 623 624 已选择15行。 625 626 SQL> rollback; 627 628 回退已完成。 629 630 SQL> / 631 632 回退已完成。 633 634 SQL> select * from emp; 635 636 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 637 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 638 7369 SMITH CLERK 7902 1980-12-17 800 20 639 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 640 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 641 7566 JONES MANAGER 7839 1981-04-02 2975 20 642 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 643 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 644 7782 CLARK MANAGER 7839 1981-06-09 2450 10 645 7788 SCOTT ANALYST 7566 1987-04-19 3000 20 646 7839 KING PRESIDENT 1981-11-17 5000 10 647 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 648 7876 ADAMS CLERK 7788 1987-05-23 1100 20 649 650 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 651 ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 652 7900 JAMES CLERK 7698 1981-12-03 950 30 653 7902 FORD ANALYST 7566 1981-12-03 3000 20 654 7934 MILLER CLERK 7782 1982-01-23 1300 10 655 656 已选择14行。 657 658 SQL> spool off;
1 SQL> /* 2 SQL> sql 优化2： 3 SQL> where条件优化： 4 SQL> 执行的顺序是从右往左去执行 where deptno = 10 and ename like '%a%'; or 5 SQL> */ 6 SQL> -- 排序 order by 7 SQL> -- 根据员工的sal 排序 8 SQL> select * 9 2 from emp 10 3 order by sal; 11 12 EMPNO ENAME JOB MGR HIREDATE 13 ---------- -------------------- ------------------ ---------- -------------- 14 SAL COMM DEPTNO 15 ---------- ---------- ---------- 16 7369 SMITH CLERK 7902 17-12月-80 17 800 20 18 19 7900 JAMES CLERK 7698 03-12月-81 20 950 30 21 22 7876 ADAMS CLERK 7788 23-5月 -87 23 1100 20 24 25 26 EMPNO ENAME JOB MGR HIREDATE 27 ---------- -------------------- ------------------ ---------- -------------- 28 SAL COMM DEPTNO 29 ---------- ---------- ---------- 30 7521 WARD SALESMAN 7698 22-2月 -81 31 1250 500 30 32 33 7654 MARTIN SALESMAN 7698 28-9月 -81 34 1250 1400 30 35 36 7934 MILLER CLERK 7782 23-1月 -82 37 1300 10 38 39 40 EMPNO ENAME JOB MGR HIREDATE 41 ---------- -------------------- ------------------ ---------- -------------- 42 SAL COMM DEPTNO 43 ---------- ---------- ---------- 44 7844 TURNER SALESMAN 7698 08-9月 -81 45 1500 0 30 46 47 7499 ALLEN SALESMAN 7698 20-2月 -81 48 1600 300 30 49 50 7782 CLARK MANAGER 7839 09-6月 -81 51 2450 10 52 53 54 EMPNO ENAME JOB MGR HIREDATE 55 ---------- -------------------- ------------------ ---------- -------------- 56 SAL COMM DEPTNO 57 ---------- ---------- ---------- 58 7698 BLAKE MANAGER 7839 01-5月 -81 59 2850 30 60 61 7566 JONES MANAGER 7839 02-4月 -81 62 2975 20 63 64 7788 SCOTT ANALYST 7566 19-4月 -87 65 3000 20 66 67 68 EMPNO ENAME JOB MGR HIREDATE 69 ---------- -------------------- ------------------ ---------- -------------- 70 SAL COMM DEPTNO 71 ---------- ---------- ---------- 72 7902 FORD ANALYST 7566 03-12月-81 73 3000 20 74 75 7839 KING PRESIDENT 17-11月-81 76 5000 10 77 78 79 已选择14行。 80 81 SQL> set linesize 200; 82 SQL> / 83 84 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 85 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 86 7369 SMITH CLERK 7902 17-12月-80 800 20 87 7900 JAMES CLERK 7698 03-12月-81 950 30 88 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 89 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 90 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 91 7934 MILLER CLERK 7782 23-1月 -82 1300 10 92 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 93 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 94 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 95 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 96 7566 JONES MANAGER 7839 02-4月 -81 2975 20 97 98 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 99 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 100 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 101 7902 FORD ANALYST 7566 03-12月-81 3000 20 102 7839 KING PRESIDENT 17-11月-81 5000 10 103 104 已选择14行。 105 106 SQL> --默认情况下oarcle是按照升序排列 107 SQL> select * 108 2 from emp 109 3 order by sal desc; 110 111 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 112 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 113 7839 KING PRESIDENT 17-11月-81 5000 10 114 7902 FORD ANALYST 7566 03-12月-81 3000 20 115 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 116 7566 JONES MANAGER 7839 02-4月 -81 2975 20 117 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 118 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 119 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 120 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 121 7934 MILLER CLERK 7782 23-1月 -82 1300 10 122 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 123 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 124 125 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 126 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 127 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 128 7900 JAMES CLERK 7698 03-12月-81 950 30 129 7369 SMITH CLERK 7902 17-12月-80 800 20 130 131 已选择14行。 132 133 SQL> select * 134 2 from emp 135 3 order by sal asc; 136 137 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 138 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 139 7369 SMITH CLERK 7902 17-12月-80 800 20 140 7900 JAMES CLERK 7698 03-12月-81 950 30 141 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 142 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 143 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 144 7934 MILLER CLERK 7782 23-1月 -82 1300 10 145 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 146 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 147 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 148 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 149 7566 JONES MANAGER 7839 02-4月 -81 2975 20 150 151 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 152 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 153 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 154 7902 FORD ANALYST 7566 03-12月-81 3000 20 155 7839 KING PRESIDENT 17-11月-81 5000 10 156 157 已选择14行。 158 159 SQL> -- desc 降序 asc升序 160 SQL> -- 按照sal和部门号排序 161 SQL> select * 162 2 from emp 163 3 order by sal,deptno; 164 165 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 166 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 167 7369 SMITH CLERK 7902 17-12月-80 800 20 168 7900 JAMES CLERK 7698 03-12月-81 950 30 169 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 170 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 171 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 172 7934 MILLER CLERK 7782 23-1月 -82 1300 10 173 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 174 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 175 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 176 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 177 7566 JONES MANAGER 7839 02-4月 -81 2975 20 178 179 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 180 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 181 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 182 7902 FORD ANALYST 7566 03-12月-81 3000 20 183 7839 KING PRESIDENT 17-11月-81 5000 10 184 185 已选择14行。 186 187 SQL> select * 188 2 from emp 189 3 order by job,deptno; 190 191 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 192 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 193 7902 FORD ANALYST 7566 03-12月-81 3000 20 194 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 195 7934 MILLER CLERK 7782 23-1月 -82 1300 10 196 7369 SMITH CLERK 7902 17-12月-80 800 20 197 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 198 7900 JAMES CLERK 7698 03-12月-81 950 30 199 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 200 7566 JONES MANAGER 7839 02-4月 -81 2975 20 201 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 202 7839 KING PRESIDENT 17-11月-81 5000 10 203 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 204 205 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 206 ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 207 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 208 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 209 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 210 211 已选择14行。 212 213 SQL> -- 如果排序后面跟了多个列，那么会首先使用第一列来排，第一列拍出来的，使用第二列来拍，以此类推。 214 SQL> -- order by 后面能跟什么东西？ 215 SQL> -- order by 列明，别名，表达式，列所在的数字顺序 216 SQL> select deptno,empno 员工号 217 2 from emp 218 3 order by 员工号; 219 220 DEPTNO 员工号 221 ---------- ---------- 222 20 7369 223 30 7499 224 30 7521 225 20 7566 226 30 7654 227 30 7698 228 10 7782 229 20 7788 230 10 7839 231 30 7844 232 20 7876 233 234 DEPTNO 员工号 235 ---------- ---------- 236 30 7900 237 20 7902 238 10 7934 239 240 已选择14行。 241 242 SQL> select sal*12 基本工资 243 2 from emp 244 3 order by sal*12; 245 246 基本工资 247 ---------- 248 9600 249 11400 250 13200 251 15000 252 15000 253 15600 254 18000 255 19200 256 29400 257 34200 258 35700 259 260 基本工资 261 ---------- 262 36000 263 36000 264 60000 265 266 已选择14行。 267 268 SQL> select empno,deptno,sal*12 + nvl(comm,0) 269 2 from emp 270 3 order by 3; 271 272 EMPNO DEPTNO SAL*12+NVL(COMM,0) 273 ---------- ---------- ------------------ 274 7369 20 9600 275 7900 30 11400 276 7876 20 13200 277 7521 30 15500 278 7934 10 15600 279 7654 30 16400 280 7844 30 18000 281 7499 30 19500 282 7782 10 29400 283 7698 30 34200 284 7566 20 35700 285 286 EMPNO DEPTNO SAL*12+NVL(COMM,0) 287 ---------- ---------- ------------------ 288 7788 20 36000 289 7902 20 36000 290 7839 10 60000 291 292 已选择14行。 293 294 SQL> select empno,deptno 295 2 from emp 296 3 order by empno,depton desc; 297 order by empno,depton desc 298 * 299 第 3 行出现错误: 300 ORA-00904: "DEPTON": 标识符无效 301 302 303 SQL> 3 304 3* order by empno,depton desc 305 SQL> c /depton/deptno; 306 3* order by empno,deptno desc 307 SQL> / 308 309 EMPNO DEPTNO 310 ---------- ---------- 311 7369 20 312 7499 30 313 7521 30 314 7566 20 315 7654 30 316 7698 30 317 7782 10 318 7788 20 319 7839 10 320 7844 30 321 7876 20 322 323 EMPNO DEPTNO 324 ---------- ---------- 325 7900 30 326 7902 20 327 7934 10 328 329 已选择14行。 330 331 SQL> select job,deptno 332 2 from emp 333 3 order by job,deptno desc; 334 335 JOB DEPTNO 336 ------------------ ---------- 337 ANALYST 20 338 ANALYST 20 339 CLERK 30 340 CLERK 20 341 CLERK 20 342 CLERK 10 343 MANAGER 30 344 MANAGER 20 345 MANAGER 10 346 PRESIDENT 10 347 SALESMAN 30 348 349 JOB DEPTNO 350 ------------------ ---------- 351 SALESMAN 30 352 SALESMAN 30 353 SALESMAN 30 354 355 已选择14行。 356 357 SQL> -- desc只用用于理他最近的那一列 358 SQL> select empno,comm 359 2 from emp 360 3 order by comm desc; 361 362 EMPNO COMM 363 ---------- ---------- 364 7369 365 7782 366 7902 367 7900 368 7876 369 7566 370 7698 371 7934 372 7788 373 7839 374 7654 1400 375 376 EMPNO COMM 377 ---------- ---------- 378 7521 500 379 7499 300 380 7844 0 381 382 已选择14行。 383 384 SQL> -- 在oracle中null最大 385 SQL> -- 让null在最后显示 386 SQL> select empno,comm 387 2 from emp 388 3 order by comm desc 389 4 nulls last; 390 391 EMPNO COMM 392 ---------- ---------- 393 7654 1400 394 7521 500 395 7499 300 396 7844 0 397 7788 398 7839 399 7876 400 7900 401 7902 402 7934 403 7698 404 405 EMPNO COMM 406 ---------- ---------- 407 7566 408 7369 409 7782 410 411 已选择14行。 412 413 SQL> spool off;
2.单行函数
1 SQL> -- 大写 小写 首字母大写 2 SQL> select upper('hello world') 大写, lower('Hello World') 小写,initcap('hello world') 首字母大写 from dual; 3 4 大写 小写 首字母大写 5 ---------------------- ---------------------- ---------------------- 6 HELLO WORLD hello world Hello World 7 8 SQL> -- 字节和字符的统计 9 SQL> select length('中国') 字符,lengthb('中国') 字节 from dual; 10 11 字符 字节 12 ---------- ---------- 13 2 6 14 15 SQL> -- 替换字符串里面的o 16 SQL> select replace('hello','o','a') from dual; 17 18 REPLACE('H 19 ---------- 20 hella 21 22 SQL> select replace('hello','o') from dual; 23 24 REPLACE( 25 -------- 26 hell 27 28 SQL> select lengthb(replace('hello','o')) from dual; 29 30 LENGTHB(REPLACE('HELLO','O')) 31 ----------------------------- 32 4 33 34 SQL> -- 截取字符串的子串 35 SQL> select substr('hello world',3) from dual; 36 37 SUBSTR('HELLOWORLD 38 ------------------ 39 llo world 40 41 SQL> select substr('hello world',3,4) from dual; 42 43 SUBSTR(' 44 -------- 45 llo 46 47 SQL> /* 48 SQL> substr('',a) 从第a为开始截取到最后 49 SQL> substr('',a,b) 从第a位开始截取截取b个长度 50 SQL> */ 51 SQL> -- 在一个字符串中查找某一个字符或者字符串 52 SQL> select instr('hello world','o') from dual; 53 54 INSTR('HELLOWORLD','O') 55 ----------------------- 56 5 57 58 SQL> select instr('hello world','hello') from dual; 59 60 INSTR('HELLOWORLD','HELLO') 61 --------------------------- 62 1 63 64 SQL> -- instr 查询第一次出现某一个字符或者字符串的位置 65 SQL> -- 左补齐和右补齐 66 SQL> select lpad('hello world',15,'*') 左补齐,rpad('hello world',15,'@') 右补齐 from dual; 67 68 左补齐 右补齐 69 ------------------------------ ------------------------------ 70 ****hello world hello world@@@@ 71 72 SQL> -- 去掉字符串里面的某一个字符或者子串 73 SQL> select trim('hello world','hel') from dual; 74 select trim('hello world','hel') from dual 75 * 76 第 1 行出现错误: 77 ORA-00907: 缺失右括号 78 79 80 SQL> select trim('hel' from 'hello world') from dual; 81 select trim('hel' from 'hello world') from dual 82 * 83 第 1 行出现错误: 84 ORA-30001: 截取集仅能有一个字符 85 86 87 SQL> select trim('h' from 'hello world') from dual; 88 89 TRIM('H'FROM'HELLOWO 90 -------------------- 91 ello world 92 93 SQL> -- trim只能去除一个字符，不能去除字符串 94 SQL> -- 数值函数 95 SQL> -- round 四舍五入 96 SQL> select round(42.965,2) 一,round(42.965,1) 二,round(42.965,0) 三, 97 2 round(42.965,-1) 四,round(42.965,-2) 五 from dual; 98 99 一 二 三 四 五 100 ---------- ---------- ---------- ---------- ---------- 101 42.97 43 43 40 0 102 103 SQL> select trunc(42.965,2) 一,trunc(42.965,1) 二,trunc(42.965,0) 三, 104 2 trunc(42.965,-1) 四,trunc(42.965,-2) 五 from dual; 105 106 一 二 三 四 五 107 ---------- ---------- ---------- ---------- ---------- 108 42.96 42.9 42 40 0 109 110 SQL> select mod(10,3) from dual; 111 112 MOD(10,3) 113 ---------- 114 1 115 116 SQL> select mod(3,10) from dual; 117 118 MOD(3,10) 119 ---------- 120 3 121 122 SQL> spool off
1 SQL> -- 查询系统当前时间 2 SQL> select sysdate from dual; 3 4 SYSDATE 5 -------------- 6 15-3月 -16 7 8 SQL> alter session set NLS_DATE_PARAMETER='yyyy-mm-dd'; 9 alter session set NLS_DATE_PARAMETER='yyyy-mm-dd' 10 * 11 第 1 行出现错误: 12 ORA-00922: 选项缺失或无效 13 14 15 SQL> select * from v$nls_parameters; 16 17 PARAMETER 18 -------------------------------------------------------------------------------------------------------------------------------- 19 VALUE 20 -------------------------------------------------------------------------------------------------------------------------------- 21 NLS_LANGUAGE 22 SIMPLIFIED CHINESE 23 24 NLS_TERRITORY 25 CHINA 26 27 NLS_CURRENCY 28 ￥ 29 30 31 PARAMETER 32 -------------------------------------------------------------------------------------------------------------------------------- 33 VALUE 34 -------------------------------------------------------------------------------------------------------------------------------- 35 NLS_ISO_CURRENCY 36 CHINA 37 38 NLS_NUMERIC_CHARACTERS 39 ., 40 41 NLS_CALENDAR 42 GREGORIAN 43 44 45 PARAMETER 46 -------------------------------------------------------------------------------------------------------------------------------- 47 VALUE 48 -------------------------------------------------------------------------------------------------------------------------------- 49 NLS_DATE_FORMAT 50 DD-MON-RR 51 52 NLS_DATE_LANGUAGE 53 SIMPLIFIED CHINESE 54 55 NLS_CHARACTERSET 56 AL32UTF8 57 58 59 PARAMETER 60 -------------------------------------------------------------------------------------------------------------------------------- 61 VALUE 62 -------------------------------------------------------------------------------------------------------------------------------- 63 NLS_SORT 64 BINARY 65 66 NLS_TIME_FORMAT 67 HH.MI.SSXFF AM 68 69 NLS_TIMESTAMP_FORMAT 70 DD-MON-RR HH.MI.SSXFF AM 71 72 73 PARAMETER 74 -------------------------------------------------------------------------------------------------------------------------------- 75 VALUE 76 -------------------------------------------------------------------------------------------------------------------------------- 77 NLS_TIME_TZ_FORMAT 78 HH.MI.SSXFF AM TZR 79 80 NLS_TIMESTAMP_TZ_FORMAT 81 DD-MON-RR HH.MI.SSXFF AM TZR 82 83 NLS_DUAL_CURRENCY 84 ￥ 85 86 87 PARAMETER 88 -------------------------------------------------------------------------------------------------------------------------------- 89 VALUE 90 -------------------------------------------------------------------------------------------------------------------------------- 91 NLS_NCHAR_CHARACTERSET 92 AL16UTF16 93 94 NLS_COMP 95 BINARY 96 97 NLS_LENGTH_SEMANTICS 98 BYTE 99 100 101 PARAMETER 102 -------------------------------------------------------------------------------------------------------------------------------- 103 VALUE 104 -------------------------------------------------------------------------------------------------------------------------------- 105 NLS_NCHAR_CONV_EXCP 106 FALSE 107 108 109 已选择19行。 110 111 SQL> set linesize 200; 112 SQL> / 113 114 PARAMETER 115 -------------------------------------------------------------------------------------------------------------------------------- 116 VALUE 117 -------------------------------------------------------------------------------------------------------------------------------- 118 NLS_LANGUAGE 119 SIMPLIFIED CHINESE 120 121 NLS_TERRITORY 122 CHINA 123 124 NLS_CURRENCY 125 ￥ 126 127 128 PARAMETER 129 -------------------------------------------------------------------------------------------------------------------------------- 130 VALUE 131 -------------------------------------------------------------------------------------------------------------------------------- 132 NLS_ISO_CURRENCY 133 CHINA 134 135 NLS_NUMERIC_CHARACTERS 136 ., 137 138 NLS_CALENDAR 139 GREGORIAN 140 141 142 PARAMETER 143 -------------------------------------------------------------------------------------------------------------------------------- 144 VALUE 145 -------------------------------------------------------------------------------------------------------------------------------- 146 NLS_DATE_FORMAT 147 DD-MON-RR 148 149 NLS_DATE_LANGUAGE 150 SIMPLIFIED CHINESE 151 152 NLS_CHARACTERSET 153 AL32UTF8 154 155 156 PARAMETER 157 -------------------------------------------------------------------------------------------------------------------------------- 158 VALUE 159 -------------------------------------------------------------------------------------------------------------------------------- 160 NLS_SORT 161 BINARY 162 163 NLS_TIME_FORMAT 164 HH.MI.SSXFF AM 165 166 NLS_TIMESTAMP_FORMAT 167 DD-MON-RR HH.MI.SSXFF AM 168 169 170 PARAMETER 171 -------------------------------------------------------------------------------------------------------------------------------- 172 VALUE 173 -------------------------------------------------------------------------------------------------------------------------------- 174 NLS_TIME_TZ_FORMAT 175 HH.MI.SSXFF AM TZR 176 177 NLS_TIMESTAMP_TZ_FORMAT 178 DD-MON-RR HH.MI.SSXFF AM TZR 179 180 NLS_DUAL_CURRENCY 181 ￥ 182 183 184 PARAMETER 185 -------------------------------------------------------------------------------------------------------------------------------- 186 VALUE 187 -------------------------------------------------------------------------------------------------------------------------------- 188 NLS_NCHAR_CHARACTERSET 189 AL16UTF16 190 191 NLS_COMP 192 BINARY 193 194 NLS_LENGTH_SEMANTICS 195 BYTE 196 197 198 PARAMETER 199 -------------------------------------------------------------------------------------------------------------------------------- 200 VALUE 201 -------------------------------------------------------------------------------------------------------------------------------- 202 NLS_NCHAR_CONV_EXCP 203 FALSE 204 205 206 已选择19行。 207 208 SQL> col parameter for a50; 209 SQL> / 210 211 PARAMETER VALUE 212 -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 213 NLS_LANGUAGE SIMPLIFIED CHINESE 214 NLS_TERRITORY CHINA 215 NLS_CURRENCY ￥ 216 NLS_ISO_CURRENCY CHINA 217 NLS_NUMERIC_CHARACTERS ., 218 NLS_CALENDAR GREGORIAN 219 NLS_DATE_FORMAT DD-MON-RR 220 NLS_DATE_LANGUAGE SIMPLIFIED CHINESE 221 NLS_CHARACTERSET AL32UTF8 222 NLS_SORT BINARY 223 NLS_TIME_FORMAT HH.MI.SSXFF AM 224 225 PARAMETER VALUE 226 -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 227 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 228 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 229 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 230 NLS_DUAL_CURRENCY ￥ 231 NLS_NCHAR_CHARACTERSET AL16UTF16 232 NLS_COMP BINARY 233 NLS_LENGTH_SEMANTICS BYTE 234 NLS_NCHAR_CONV_EXCP FALSE 235 236 已选择19行。 237 238 SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd'; 239 240 会话已更改。 241 242 SQL> select sysdate from dual; 243 244 SYSDATE 245 ---------- 246 2016-03-15 247 248 SQL> -- 改变时间的显示的另一种方式 249 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 250 251 TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS' 252 -------------------------------------- 253 2016-03-15 16:39:38 254 255 SQL> desc emp; 256 名称 是否为空? 类型 257 ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- 258 EMPNO NOT NULL NUMBER(4) 259 ENAME VARCHAR2(10) 260 JOB VARCHAR2(9) 261 MGR NUMBER(4) 262 HIREDATE DATE 263 SAL NUMBER(7,2) 264 COMM NUMBER(7,2) 265 DEPTNO NUMBER(2) 266 267 SQL> -- oracle中如果你的字符串的格式满足日期的格式系统会根据你的需求帮你转换 268 SQL> -- 输出昨天 ，今天 和明天 269 SQL> select sysdate + 1 明天,sysdate 今天, sysdate - 1 昨天 from dual; 270 271 明天 今天 昨天 272 ---------- ---------- ---------- 273 2016-03-16 2016-03-15 2016-03-14 274 275 SQL> -- 明天减去今天等于多少 276 SQL> select (sysdate + 1) - (sysdate) from dual; 277 278 (SYSDATE+1)-(SYSDATE) 279 --------------------- 280 1 281 282 SQL> -- 明天加今天等于多少 283 SQL> select (sydate + 1) + sysdate from dual; 284 select (sydate + 1) + sysdate from dual 285 * 286 第 1 行出现错误: 287 ORA-00904: "SYDATE": 标识符无效 288 289 290 SQL> -- 日期不能去做加法，只能加几天，可以做减法，减去一个日期或者几天 291 SQL> -- 根据入司时间计算一下emp表里面的所有员工的工龄 292 SQL> select (sysdate - hiredate) 天,(sysdate -hiredate)/7 周, (sysdate - hiredate)/30 月 from emp; 293 294 天 周 月 295 ---------- ---------- ---------- 296 12872.7023 1838.95747 429.090077 297 12807.7023 1829.67176 426.92341 298 12805.7023 1829.38604 426.856743 299 12766.7023 1823.81461 425.556743 300 12587.7023 1798.24319 419.590077 301 12737.7023 1819.67176 424.590077 302 12698.7023 1814.10033 423.290077 303 10558.7023 1508.38604 351.956743 304 12537.7023 1791.10033 417.92341 305 12607.7023 1801.10033 420.256743 306 10524.7023 1503.5289 350.82341 307 308 天 周 月 309 ---------- ---------- ---------- 310 12521.7023 1788.81461 417.390077 311 12521.7023 1788.81461 417.390077 312 12470.7023 1781.5289 415.690077 313 314 已选择14行。 315 316 SQL> select (sysdate - hiredate)/30 粗略, months_between(sysdate,hiredate) 精确 from emp; 317 318 粗略 精确 319 ---------- ---------- 320 429.090142 422.958202 321 426.923475 420.861427 322 426.856808 420.796911 323 425.556808 419.442073 324 419.590142 413.603363 325 424.590142 418.474331 326 423.290142 417.216266 327 351.956808 346.893685 328 417.923475 411.958202 329 420.256808 414.248524 330 350.823475 345.764653 331 332 粗略 精确 333 ---------- ---------- 334 417.390142 411.409814 335 417.390142 411.409814 336 415.690142 409.764653 337 338 已选择14行。 339 340 SQL> -- 在一个日期上加上几个月 341 SQL> select add_months(sysdate,5) from dual; 342 343 ADD_MONTHS 344 ---------- 345 2016-08-15 346 347 SQL> -- 查看当前月的最后一天 348 SQL> select last_day(sysdate) from dual; 349 350 LAST_DAY(S 351 ---------- 352 2016-03-31 353 354 SQL> -- 下一个日期 比如下一个星期六 355 SQL> select next_day(sysdate,'星期二') from dual; 356 357 NEXT_DAY(S 358 ---------- 359 2016-03-22 360 361 SQL> -- 日期的四舍五入 362 SQL> select round(sysdate,'year') from dual; 363 364 ROUND(SYSD 365 ---------- 366 2016-01-01 367 368 SQL> select round(sysdate,'month') from dual; 369 370 ROUND(SYSD 371 ---------- 372 2016-03-01 373 374 SQL> select round(sysdate,'day') from dual; 375 376 ROUND(SYSD 377 ---------- 378 2016-03-13 379 380 SQL> select sysdate from dual; 381 382 SYSDATE 383 ---------- 384 2016-03-15 385 386 SQL> select trunc(sysdate,'day') from dual; 387 388 TRUNC(SYSD 389 ---------- 390 2016-03-13 391 392 SQL> -- 今天是星期二 天气真的很好 393 SQL> select to_char(sysdate,'"今天是"yyyy-mm-dd day "天气真的很好") from dual; 394 ERROR: 395 ORA-01756: 引号内的字符串没有正确结束 396 397 398 SQL> select to_char(sysdate,'"今天是"yyyy-mm-dd day "天气真的很好"') from dual; 399 400 TO_CHAR(SYSDATE,'"今天是"YYYY-MM-DDDAY"天气真的很好"') 401 ------------------------------------------------------------------------------------------------------ 402 今天是2016-03-15 星期二 天气真的很好 403 404 SQL> -- 双引号的作用除了作为别名以外还可以作为日期格式里面的常亮 405 SQL> --常量 406 SQL> -- 将整数转换为一个字符串 407 SQL> select to_char(1524251,L999,999) from dual; 408 select to_char(1524251,L999,999) from dual 409 * 410 第 1 行出现错误: 411 ORA-00904: "L999": 标识符无效 412 413 414 SQL> select to_char(1524251,'L999,999') from dual; 415 416 TO_CHAR(1524251,'L999,999') 417 ------------------------------------ 418 ################## 419 420 SQL> select to_char(1524251,'L9,999,999') from dual; 421 422 TO_CHAR(1524251,'L9,999,999') 423 ---------------------------------------- 424 ￥1,524,251 425 426 SQL> -- L 表示本地货比 427 SQL> select to_number('12345','99,99') from dual; 428 select to_number('12345','99,99') from dual 429 * 430 第 1 行出现错误: 431 ORA-01722: 无效数字 432 433 434 SQL> select to_number('12345','99,999') from dual 435 2 ; 436 select to_number('12345','99,999') from dual 437 * 438 第 1 行出现错误: 439 ORA-01722: 无效数字 440 441 442 SQL> select to_number('123') from dual; 443 444 TO_NUMBER('123') 445 ---------------- 446 123 447 448 SQL> -- 通用函数 449 SQL> -- nvl2 是nvl的一个加强版 450 SQL> -- nvl2(a,b,c) 如果a==null ,返回c否则返回b 451 SQL> select sal,nvl2(comm,comm,sal) from emp; 452 453 SAL NVL2(COMM,COMM,SAL) 454 ---------- ------------------- 455 800 800 456 1600 300 457 1250 500 458 2975 2975 459 1250 1400 460 2850 2850 461 2450 2450 462 3000 3000 463 5000 5000 464 1500 0 465 1100 1100 466 467 SAL NVL2(COMM,COMM,SAL) 468 ---------- ------------------- 469 950 950 470 3000 3000 471 1300 1300 472 473 已选择14行。 474 475 SQL> select sal,nvl2(comm,comm,sal), comm from emp; 476 477 SAL NVL2(COMM,COMM,SAL) COMM 478 ---------- ------------------- ---------- 479 800 800 480 1600 300 300 481 1250 500 500 482 2975 2975 483 1250 1400 1400 484 2850 2850 485 2450 2450 486 3000 3000 487 5000 5000 488 1500 0 0 489 1100 1100 490 491 SAL NVL2(COMM,COMM,SAL) COMM 492 ---------- ------------------- ---------- 493 950 950 494 3000 3000 495 1300 1300 496 497 已选择14行。 498 499 SQL> -- nullif(a,b) 如果a == b 返回空,如果不等于返回a 500 SQL> select nullif('abc','abc') from dual; 501 502 NULLIF 503 ------ 504 505 506 SQL> select nullif('abc','abcc') from dual; 507 508 NULLIF 509 ------ 510 abc 511 512 SQL> -- coalesce(a,b,c....) 从最左边开始查找，查找到第一个不为空的返回 513 SQL> select sal, comm, coalesce(sal,comm,empno) from emp; 514 515 SAL COMM COALESCE(SAL,COMM,EMPNO) 516 ---------- ---------- ------------------------ 517 800 800 518 1600 300 1600 519 1250 500 1250 520 2975 2975 521 1250 1400 1250 522 2850 2850 523 2450 2450 524 3000 3000 525 5000 5000 526 1500 0 1500 527 1100 1100 528 529 SAL COMM COALESCE(SAL,COMM,EMPNO) 530 ---------- ---------- ------------------------ 531 950 950 532 3000 3000 533 1300 1300 534 535 已选择14行。 536 537 SQL> select sal, comm, coalesce(comm,sal,empno) from emp; 538 539 SAL COMM COALESCE(COMM,SAL,EMPNO) 540 ---------- ---------- ------------------------ 541 800 800 542 1600 300 300 543 1250 500 500 544 2975 2975 545 1250 1400 1400 546 2850 2850 547 2450 2450 548 3000 3000 549 5000 5000 550 1500 0 0 551 1100 1100 552 553 SAL COMM COALESCE(COMM,SAL,EMPNO) 554 ---------- ---------- ------------------------ 555 950 950 556 3000 3000 557 1300 1300 558 559 已选择14行。 560 561 SQL> -- case 和decode ，case是sql99标准里面的内容,decode只有在oracle中支持 562 SQL> select sal 调前, 563 2 case job 564 3 when 'PRESIDENT' then sal + 1000; 565 when 'PRESIDENT' then sal + 1000 566 * 567 第 3 行出现错误: 568 ORA-00905: 缺失关键字 569 570 571 SQL> when 'MANAGER' then sal + 800; 572 SP2-0734: 未知的命令开头 "when 'MANA..." - 忽略了剩余的行。 573 SQL> sal + 400; 574 SP2-0042: 未知命令 "sal + 400" - 其余行忽略。 575 SQL> end 涨后 from emp; 576 SP2-0734: 未知的命令开头 "end 涨后 f..." - 忽略了剩余的行。 577 SQL> 578 SQL> 579 SQL> select sal 调前,case job when 'PRESIDENT' then sal + 1000;when 'MANAGER' then sal + 800;sal + 400;end 涨后 from emp; 580 select sal 调前,case job when 'PRESIDENT' then sal + 1000;when 'MANAGER' then sal + 800;sal + 400;end 涨后 from emp 581 * 582 第 1 行出现错误: 583 ORA-00911: 无效字符 584 585 586 SQL> select sal, case 587 2 when job = 'PRESIDENT' then sal + 1000; 588 when job = 'PRESIDENT' then sal + 1000 589 * 590 第 2 行出现错误: 591 ORA-00905: 缺失关键字 592 593 594 SQL> select sal 调前,case job when 'PRESIDENT' then sal + 1000 when 'MANAGER' then sal + 800 sal + 400 end 涨后 from emp; 595 select sal 调前,case job when 'PRESIDENT' then sal + 1000 when 'MANAGER' then sal + 800 sal + 400 end 涨后 from emp 596 * 597 第 1 行出现错误: 598 ORA-00905: 缺失关键字 599 600 601 SQL> 602 SQL> select sal 调前,case when job = 'PRESIDENT' then sal + 1000 when job = 'MANAGER' then sal + 800 sal + 400 end 涨后 from emp; 603 select sal 调前,case when job = 'PRESIDENT' then sal + 1000 when job = 'MANAGER' then sal + 800 sal + 400 end 涨后 from emp 604 * 605 第 1 行出现错误: 606 ORA-00905: 缺失关键字 607 608 609 SQL> select sal, case 610 2 when job = 'MANAGER' then sal + 800 611 3 end from emp; 612 613 SAL CASEWHENJOB='MANAGER'THENSAL+800END 614 ---------- ----------------------------------- 615 800 616 1600 617 1250 618 2975 3775 619 1250 620 2850 3650 621 2450 3250 622 3000 623 5000 624 1500 625 1100 626 627 SAL CASEWHENJOB='MANAGER'THENSAL+800END 628 ---------- ----------------------------------- 629 950 630 3000 631 1300 632 633 已选择14行。 634 635 SQL> spool off;
本文出自wentaokyle，转载请标明出处！谢谢！
