SQL中的單記錄函數(shù)
??2 1 . ASCII
??3 返回與指定的字符對應(yīng)的十進(jìn)制數(shù);
??4 SQL > ? select ? ascii ( ' A ' )?A, ascii ( ' a ' )?a, ascii ( ' 0 ' )?zero, ascii ( ' ? ' )? space ? from ?dual;
??5
??6 ????????A?????????A??????ZERO????? SPACE
??7 -- -------?---------?---------?---------
??8 ??????? 65 ???????? 97 ???????? 48 ???????? 32
??9
?10
?11 2 .CHR
?12 給出整數(shù),返回對應(yīng)的字符;
?13 SQL > ? select ?chr( 54740 )?zhao,chr( 65 )?chr65? from ?dual;
?14
?15 ZH?C
?16 -- ?-
?17 趙?A
?18
?19 3 .CONCAT
?20 連接兩個(gè)字符串;
?21 SQL > ? select ?concat( ' 010- ' , ' 88888888 ' ) || ' 轉(zhuǎn)23 ' ??高乾競電話? from ?dual;
?22
?23 高乾競電話
?24 -- --------------
?25 010 - 88888888轉(zhuǎn)23
?26
?27 4 .INITCAP
?28 返回字符串并將字符串的第一個(gè)字母變?yōu)榇髮?
?29 SQL > ? select ?initcap( ' smith ' )?upp? from ?dual;
?30
?31 UPP
?32 -- ---
?33 Smith
?34
?35
?36 5 .INSTR(C1,C2,I,J)
?37 在一個(gè)字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置;
?38 C1????被搜索的字符串
?39 C2????希望搜索的字符串
?40 I?????搜索的開始位置,默認(rèn)為1
?41 J?????出現(xiàn)的位置,默認(rèn)為1
?42 SQL > ? select ?instr( ' oracle?traning ' , ' ra ' , 1 , 2 )?instring? from ?dual;
?43
?44 ?INSTRING
?45 -- -------
?46 ???????? 9
?47
?48
?49 6 .LENGTH
?50 返回字符串的長度;
?51 SQL > ? select ?name,length(name),addr,length(addr),sal,length(to_char(sal))? from ?gao.nchar_tst;
?52
?53 NAME???LENGTH(NAME)?ADDR?????????????LENGTH(ADDR)???????SAL?LENGTH(TO_CHAR(SAL))
?54 -- ----?------------?----------------?------------?---------?--------------------
?55 高乾競???????????? 3 ?北京市海錠區(qū)???????????????? 6 ??? 9999.99 ???????????????????? 7
?56
?57 ?
?58
?59 7 . LOWER
?60 返回字符串,并將所有的字符小寫
?61 SQL > ? select ? lower ( ' AaBbCcDd ' )AaBbCcDd? from ?dual;
?62
?63 AABBCCDD
?64 -- ------
?65 aabbccdd
?66
?67
?68 8 . UPPER
?69 返回字符串,并將所有的字符大寫
?70 SQL > ? select ? upper ( ' AaBbCcDd ' )? upper ? from ?dual;
?71
?72 UPPER
?73 -- ------
?74 AABBCCDD
?75
?76 ?
?77
?78 9 .RPAD和LPAD(粘貼字符)
?79 RPAD??在列的右邊粘貼字符
?80 LPAD??在列的左邊粘貼字符
?81 SQL > ? select ?lpad(rpad( ' gao ' , 10 , ' * ' ), 17 , ' * ' ) from ?dual;
?82
?83 LPAD(RPAD( ' GAO ' , 1
?84 -- ---------------
?85 ******* gao *******
?86 不夠字符則用 * 來填滿
?87
?88
?89 10 .LTRIM和RTRIM
?90 LTRIM ??刪除左邊出現(xiàn)的字符串
?91 RTRIM ??刪除右邊出現(xiàn)的字符串
?92 SQL > ? select ? ltrim ( rtrim ( ' ???gao?qian?jing??? ' , ' ? ' ), ' ? ' )? from ?dual;
?93
?94 LTRIM ( RTRIM ( '
?95 -------------
?96 gao?qian?jing
?97
?98
?99 11.SUBSTR(string,start,count)
100 取子字符串,從start開始,取count個(gè)
101 SQL>?select?substr( ' 13088888888 ' ,3,8)?from?dual;
102
103 SUBSTR( '
104 -- ------
105 08888888
106
107
108 12 . REPLACE ( ' string ' , ' s1 ' , ' s2 ' )
109 string???希望被替換的字符或變量?
110 s1???????被替換的字符串
111 s2???????要替換的字符串
112 SQL > ? select ? replace ( ' he?love?you ' , ' he ' , ' i ' )? from ?dual;
113
114 REPLACE ( ' H
115 ----------
116 i?love?you
117
118
119 13.SOUNDEX
120 返回一個(gè)與給定的字符串讀音相同的字符串
121 SQL>?create?table?table1(xm?varchar(8));
122 SQL>?insert?into?table1?values( ' weather ' );
123 SQL>?insert?into?table1?values( ' wether ' );
124 SQL>?insert?into?table1?values( ' gao ' );
125
126 SQL>?select?xm?from?table1?where?soundex(xm)=soundex( ' weather ' );
127
128 XM
129 --------
130 weather
131 wether
132
133
134 14.TRIM( ' s ' ?from? ' string ' )
135 LEADING???剪掉前面的字符
136 TRAILING??剪掉后面的字符
137 如果不指定,默認(rèn)為空格符?
138
139 15.ABS
140 返回指定值的絕對值
141 SQL>?select?abs(100),abs(-100)?from?dual;
142
143 ?ABS(100)?ABS(-100)
144 ---------?---------
145 ??????100???????100
146
147
148 16.ACOS
149 給出反余弦的值
150 SQL>?select?acos(-1)?from?dual;
151
152 ?ACOS(-1)
153 ---------
154 3.1415927
155
156
157 17.ASIN
158 給出反正弦的值
159 SQL>?select?asin(0.5)?from?dual;
160
161 ASIN(0.5)
162 ---------
163 .52359878
164
165
166 18.ATAN
167 返回一個(gè)數(shù)字的反正切值
168 SQL>?select?atan(1)?from?dual;
169
170 ??ATAN(1)
171 ---------
172 .78539816
173
174
175 19.CEIL
176 返回大于或等于給出數(shù)字的最小整數(shù)
177 SQL>?select?ceil(3.1415927)?from?dual;
178
179 CEIL(3.1415927)
180 ---------------
181 ??????????????4
182
183
184 20.COS
185 返回一個(gè)給定數(shù)字的余弦
186 SQL>?select?cos(-3.1415927)?from?dual;
187
188 COS(-3.1415927)
189 ---------------
190 ?????????????-1
191
192
193 21.COSH
194 返回一個(gè)數(shù)字反余弦值
195 SQL>?select?cosh(20)?from?dual;
196
197 ?COSH(20)
198 ---------
199 242582598
200
201
202 22.EXP
203 返回一個(gè)數(shù)字e的n次方根
204 SQL>?select?exp(2),exp(1)?from?dual;
205
206 ???EXP(2)????EXP(1)
207 ---------?---------
208 7.3890561?2.7182818
209
210
211 23.FLOOR
212 對給定的數(shù)字取整數(shù)
213 SQL>?select?floor(2345.67)?from?dual;
214
215 FLOOR(2345.67)
216 --------------
217 ??????????2345
218
219
220 24.LN
221 返回一個(gè)數(shù)字的對數(shù)值
222 SQL>?select?ln(1),ln(2),ln(2.7182818)?from?dual;
223
224 ????LN(1)?????LN(2)?LN(2.7182818)
225 ---------?---------?-------------
226 ????????0?.69314718?????.99999999
227
228
229 25.LOG(n1,n2)
230 返回一個(gè)以n1為底n2的對數(shù)?
231 SQL>?select?log(2,1),log(2,4)?from?dual;
232
233 ?LOG(2,1)??LOG(2,4)
234 ---------?---------
235 ????????0?????????2
236
237
238 26.MOD(n1,n2)
239 返回一個(gè)n1除以n2的余數(shù)
240 SQL>?select?mod(10,3),mod(3,3),mod(2,3)?from?dual;
241
242 MOD(10,3)??MOD(3,3)??MOD(2,3)
243 ---------?---------?---------
244 ????????1?????????0?????????2
245
246
247 27.POWER
248 返回n1的n2次方根
249 SQL>?select?power(2,10),power(3,3)?from?dual;
250
251 POWER(2,10)?POWER(3,3)
252 -----------?----------
253 ???????1024?????????27
254
255
256 28.ROUND和TRUNC
257 按照指定的精度進(jìn)行舍入
258 SQL>?select?round(55.5),round(-55.4),trunc(55.5),trunc(-55.5)?from?dual;
259
260 ROUND(55.5)?ROUND(-55.4)?TRUNC(55.5)?TRUNC(-55.5)
261 -----------?------------?-----------?------------
262 ?????????56??????????-55??????????55??????????-55
263
264
265 29.SIGN
266 取數(shù)字n的符號,大于0返回1,小于0返回-1,等于0返回0
267 SQL>?select?sign(123),sign(-100),sign(0)?from?dual;
268
269 SIGN(123)?SIGN(-100)???SIGN(0)
270 ---------?----------?---------
271 ????????1?????????-1?????????0
272
273
274 30.SIN
275 返回一個(gè)數(shù)字的正弦值
276 SQL>?select?sin(1.57079)?from?dual;
277
278 SIN(1.57079)
279 ------------
280 ???????????1
281
282
283 31.SIGH
284 返回雙曲正弦的值
285 SQL>?select?sin(20),sinh(20)?from?dual;
286
287 ??SIN(20)??SINH(20)
288 ---------?---------
289 .91294525?242582598
290
291
292 32.SQRT
293 返回?cái)?shù)字n的根
294 SQL>?select?sqrt(64),sqrt(10)?from?dual;
295
296 ?SQRT(64)??SQRT(10)
297 ---------?---------
298 ????????8?3.1622777
299
300
301 33.TAN
302 返回?cái)?shù)字的正切值
303 SQL>?select?tan(20),tan(10)?from?dual;
304
305 ??TAN(20)???TAN(10)
306 ---------?---------
307 2.2371609?.64836083
308
309
310 34.TANH
311 返回?cái)?shù)字n的雙曲正切值
312 SQL>?select?tanh(20),tan(20)?from?dual;
313
314 ?TANH(20)???TAN(20)
315 ---------?---------
316 ????????1?2.2371609
317
318 ?
319
320 35.TRUNC
321 按照指定的精度截取一個(gè)數(shù)
322 SQL>?select?trunc(124.1666,-2)?trunc1,trunc(124.16666,2)?from?dual;
323
324 ???TRUNC1?TRUNC(124.16666,2)
325 ---------?------------------
326 ??????100?????????????124.16
327
328 ?
329
330 36.ADD_MONTHS
331 增加或減去月份
332 SQL>?select?to_char(add_months(to_date( ' 199912 ' , ' yyyymm ' ),2), ' yyyymm ' )?from?dual;
333
334 TO_CHA
335 ------
336 200002
337 SQL>?select?to_char(add_months(to_date( ' 199912 ' , ' yyyymm ' ),-2), ' yyyymm ' )?from?dual;
338
339 TO_CHA
340 ------
341 199910
342
343
344 37.LAST_DAY
345 返回日期的最后一天
346 SQL>?select?to_char(sysdate, ' yyyy.mm.dd ' ),to_char((sysdate)+1, ' yyyy.mm.dd ' )?from?dual;
347
348 TO_CHAR(SY?TO_CHAR((S
349 ----------?----------
350 2004.05.09?2004.05.10
351 SQL>?select?last_day(sysdate)?from?dual;
352
353 LAST_DAY(S
354 ----------
355 31-5月?-04
356
357
358 38.MONTHS_BETWEEN(date2,date1)
359 給出date2-date1的月份
360 SQL>?select?months_between( ' 19 - 12月 - 1999 ' , ' 19 - 3月 - 1999 ' )?mon_between?from?dual;
361
362 MON_BETWEEN
363 -----------
364 ??????????9
365 SQL>selectmonths_between(to_date( ' 2000.05 . 20 ' , ' yyyy.mm.dd ' ),to_date( ' 2005.05 . 20 ' , ' yyyy.mm.dd ' ))?mon_betw?from?dual;
366
367 ?MON_BETW
368 ---------
369 ??????-60
370
371
372 39.NEW_TIME(date, ' this ' , ' that ' )
373 給出在this時(shí)區(qū)=other時(shí)區(qū)的日期和時(shí)間
374 SQL>?select?to_char(sysdate, ' yyyy.mm.dd?hh24:mi:ss ' )?bj_time,to_char(new_time
375 ??2??(sysdate, ' PDT ' , ' GMT ' ), ' yyyy.mm.dd?hh24:mi:ss ' )?los_angles?from?dual;
376
377 BJ_TIME?????????????LOS_ANGLES
378 -------------------?-------------------
379 2004.05.09?11:05:32?2004.05.09?18:05:32
380
381
382 40.NEXT_DAY(date, ' day ' )
383 給出日期date和星期x之后計(jì)算下一個(gè)星期的日期
384 SQL>?select?next_day( ' 18 - 5月 - 2001 ' , ' 星期五 ' )?next_day?from?dual;
385
386 NEXT_DAY
387 ----------
388 25-5月?-01
389
390 ?
391
392 41.SYSDATE
393 用來得到系統(tǒng)的當(dāng)前日期
394 SQL>?select?to_char(sysdate, ' dd - mm - yyyy? day ' )?from?dual;
395
396 TO_CHAR(SYSDATE, '
397 -- ---------------
398 09 - 05 - 2004 ?星期日
399 trunc(date,fmt)按照給出的要求將日期截?cái)?如果fmt = ' mi ' 表示保留分,截?cái)嗝?br /> 400 SQL > ? select ?to_char(trunc(sysdate, ' hh ' ), ' yyyy.mm.dd?hh24:mi:ss ' )?hh,
401 ?? 2 ??to_char(trunc(sysdate, ' mi ' ), ' yyyy.mm.dd?hh24:mi:ss ' )?hhmm? from ?dual;
402
403 HH??????????????????HHMM
404 -- -----------------?-------------------
405 2004.05 . 09 ? 11 : 00 : 00 ? 2004.05 . 09 ? 11 : 17 : 00
406
407 ?
408
409 42 .CHARTOROWID
410 將字符數(shù)據(jù)類型轉(zhuǎn)換為ROWID類型
411 SQL > ? select ?rowid,rowidtochar(rowid),ename? from ?scott.emp;
412
413 ROWID??????????????ROWIDTOCHAR(ROWID)?ENAME
414 -- ----------------?------------------?----------
415 AAAAfKAACAAAAEqAAA?AAAAfKAACAAAAEqAAA?SMITH
416 AAAAfKAACAAAAEqAAB?AAAAfKAACAAAAEqAAB?ALLEN
417 AAAAfKAACAAAAEqAAC?AAAAfKAACAAAAEqAAC?WARD
418 AAAAfKAACAAAAEqAAD?AAAAfKAACAAAAEqAAD?JONES
419
420
421 43 . CONVERT (c,dset,sset)
422 將源字符串?sset從一個(gè)語言字符集轉(zhuǎn)換到另一個(gè)目的dset字符集
423 SQL > ? select ? convert ( ' strutz ' , ' we8hp ' , ' f7dec ' )?"conversion"? from ?dual;
424
425 conver
426 -- ----
427 strutz
428
429
430 44 .HEXTORAW
431 將一個(gè)十六進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為二進(jìn)制
432
433
434 45 .RAWTOHEXT
435 將一個(gè)二進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為十六進(jìn)制
436
437 ?
438
439 46 .ROWIDTOCHAR
440 將ROWID數(shù)據(jù)類型轉(zhuǎn)換為字符類型
441
442 ?
443
444 47 .TO_CHAR(date, ' format ' )
445 SQL > ? select ?to_char(sysdate, ' yyyy/mm/dd?hh24:mi:ss ' )? from ?dual;
446
447 TO_CHAR(SYSDATE, ' YY
448 -------------------
449 2004/05/09?21:14:41
450
451 ?
452
453 48.TO_DATE(string, ' format ' )
454 將字符串轉(zhuǎn)化為ORACLE中的一個(gè)日期
455
456
457 49.TO_MULTI_BYTE
458 將字符串中的單字節(jié)字符轉(zhuǎn)化為多字節(jié)字符
459 SQL>??select?to_multi_byte( ' 高 ' )?from?dual;
460
461 TO
462 --
463 高
464
465
466 50.TO_NUMBER
467 將給出的字符轉(zhuǎn)換為數(shù)字
468 SQL>?select?to_number( ' 1999 ' )?year?from?dual;
469
470 ?????YEAR
471 ---------
472 ?????1999
473
474
475 51.BFILENAME(dir,file)
476 指定一個(gè)外部二進(jìn)制文件
477 SQL>insert?into?file_tb1?values(bfilename( ' lob_dir1 ' , ' image1.gif ' ));
478
479
480 52.CONVERT( ' x ' , ' desc ' , ' source ' )
481 將x字段或變量的源source轉(zhuǎn)換為desc
482 SQL>?select?sid,serial#,username,decode(command,
483 ??2??0, ' none ' ,
484 ??3??2, ' insert ' ,
485 ??4??3,
486 ??5?? ' select ' ,
487 ??6??6, ' update ' ,
488 ??7??7, ' delete ' ,
489 ??8??8, ' drop ' ,
490 ??9?? ' other ' )?cmd??from?v$session?where?type!= ' background ' ;
491
492 ??????SID???SERIAL#?USERNAME???????????????????????CMD
493 ---------?---------?------------------------------?------
494 ????????1?????????1????????????????????????????????none
495 ????????2?????????1????????????????????????????????none
496 ????????3?????????1????????????????????????????????none
497 ????????4?????????1????????????????????????????????none
498 ????????5?????????1????????????????????????????????none
499 ????????6?????????1????????????????????????????????none
500 ????????7??????1275????????????????????????????????none
501 ????????8??????1275????????????????????????????????none
502 ????????9????????20?GAO????????????????????????????select
503 ???????10????????40?GAO????????????????????????????none
504
505
506 53.DUMP(s,fmt,start,length)
507 DUMP函數(shù)以fmt指定的內(nèi)部數(shù)字格式返回一個(gè)VARCHAR2類型的值
508 SQL>?col?global_name?for?a30
509 SQL>?col?dump_string?for?a50
510 SQL>?set?lin?200
511 SQL>?select?global_name,dump(global_name,1017,8,5)?dump_string?from?global_name;
512
513 GLOBAL_NAME????????????????????DUMP_STRING
514 ------------------------------?--------------------------------------------------
515 ORACLE.WORLD???????????????????Typ=1?Len=12?CharacterSet=ZHS16GBK:?W,O,R,L,D
516
517
518 54.EMPTY_BLOB()和EMPTY_CLOB()
519 這兩個(gè)函數(shù)都是用來對大數(shù)據(jù)類型字段進(jìn)行初始化操作的函數(shù)
520
521
522 55.GREATEST
523 返回一組表達(dá)式中的最大值,即比較字符的編碼大小.
524 SQL>?select?greatest( ' AA ' , ' AB ' , ' AC ' )?from?dual;
525
526 GR
527 --
528 AC
529 SQL>?select?greatest( ' 啊 ' , ' 安 ' , ' 天 ' )?from?dual;
530
531 GR
532 --
533 天
534
535
536 56.LEAST
537 返回一組表達(dá)式中的最小值?
538 SQL>?select?least( ' 啊 ' , ' 安 ' , ' 天 ' )?from?dual;
539
540 LE
541 --
542 啊
543
544
545 57.UID
546 返回標(biāo)識當(dāng)前用戶的唯一整數(shù)
547 SQL>?show?user
548 USER?為"GAO"
549 SQL>?select?username,user_id?from?dba_users?where?user_id=uid;
550
551 USERNAME?????????????????????????USER_ID
552 ------------------------------?---------
553 GAO???????????????????????????????????25
554
555 ?
556
557 58.USER
558 返回當(dāng)前用戶的名字
559 SQL>?select?user?from??dual;
560
561 USER
562 ------------------------------
563 GAO
564
565
566 59.USEREVN
567 返回當(dāng)前用戶環(huán)境的信息,opt可以是:
568 ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
569 ISDBA??查看當(dāng)前用戶是否是DBA如果是則返回true
570 SQL>?select?userenv( ' isdba ' )?from?dual;
571
572 USEREN
573 ------
574 FALSE
575 SQL>?select?userenv( ' isdba ' )?from?dual;
576
577 USEREN
578 ------
579 TRUE
580 SESSION
581 返回會(huì)話標(biāo)志
582 SQL>?select?userenv( ' sessionid ' )?from?dual;
583
584 USERENV( ' SESSIONID ' )
585 --------------------
586 ?????????????????152
587 ENTRYID
588 返回會(huì)話人口標(biāo)志
589 SQL>?select?userenv( ' entryid ' )?from?dual;
590
591 USERENV( ' ENTRYID ' )
592 ------------------
593 ?????????????????0
594 INSTANCE
595 返回當(dāng)前INSTANCE的標(biāo)志
596 SQL>?select?userenv( ' instance ' )?from?dual;
597
598 USERENV( ' INSTANCE ' )
599 -------------------
600 ??????????????????1
601 LANGUAGE
602 返回當(dāng)前環(huán)境變量
603 SQL>?select?userenv( ' language ' )?from?dual;
604
605 USERENV( ' LANGUAGE ' )
606 ----------------------------------------------------
607 SIMPLIFIED?CHINESE_CHINA.ZHS16GBK
608 LANG
609 返回當(dāng)前環(huán)境的語言的縮寫
610 SQL>?select?userenv( ' lang ' )?from?dual;
611
612 USERENV( ' LANG ' )
613 ----------------------------------------------------
614 ZHS
615 TERMINAL
616 返回用戶的終端或機(jī)器的標(biāo)志
617 SQL>?select?userenv( ' terminal ' )?from?dual;
618
619 USERENV( ' TERMINA
620 -- --------------
621 GAO
622 VSIZE(X)
623 返回X的大小(字節(jié))數(shù)
624 SQL > ? select ?vsize( user ), user ? from ?dual;
625
626 VSIZE( USER )? USER
627 -- ---------?------------------------------
628 ?????????? 6 ?SYSTEM
629
630 ?
631
632 60 . AVG ( DISTINCT |ALL )
633 all表示對所有的值求平均值,distinct只對不同的值求平均值
634 SQLWKS > ? create ? table ?table3(xm? varchar ( 8 ),sal? number ( 7 , 2 ));
635 語句已處理。
636 SQLWKS > ?? insert ? into ?table3? values ( ' gao ' , 1111.11 );
637 SQLWKS > ?? insert ? into ?table3? values ( ' gao ' , 1111.11 );
638 SQLWKS > ?? insert ? into ?table3? values ( ' zhu ' , 5555.55 );
639 SQLWKS > ? commit ;
640
641 SQL > ? select ? avg ( distinct ?sal)? from ?gao.table3;
642
643 AVG (DISTINCTSAL)
644 -- --------------
645 ????????? 3333.33
646
647 SQL > ? select ? avg ( all ?sal)? from ?gao.table3;
648
649 AVG (ALLSAL)
650 -- ---------
651 ???? 2592.59
652
653
654 61 . MAX ( DISTINCT |ALL )
655 求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次
656 SQL > ? select ? max ( distinct ?sal)? from ?scott.emp;
657
658 MAX (DISTINCTSAL)
659 -- --------------
660 ???????????? 5000
661
662
663 62 . MIN ( DISTINCT |ALL )
664 求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次
665 SQL > ? select ? min ( all ?sal)? from ?gao.table3;
666
667 MIN (ALLSAL)
668 -- ---------
669 ???? 1111.11
670
671
672 63 .STDDEV( distinct |all )
673 求標(biāo)準(zhǔn)差,ALL表示對所有的值求標(biāo)準(zhǔn)差,DISTINCT表示只對不同的值求標(biāo)準(zhǔn)差
674 SQL > ? select ?stddev(sal)? from ?scott.emp;
675
676 STDDEV(SAL)
677 -- ---------
678 ?? 1182.5032
679
680 SQL > ? select ?stddev( distinct ?sal)? from ?scott.emp;
681
682 STDDEV(DISTINCTSAL)
683 -- -----------------
684 ??????????? 1229.951
685
686 ?
687
688 64 .VARIANCE( DISTINCT |ALL )
689 求協(xié)方差?
690
691 SQL > ? select ?variance(sal)? from ?scott.emp;
692
693 VARIANCE(SAL)
694 -- -----------
695 ???? 1398313.9
696
697
698 65 . GROUP ? BY
699 主要用來對一組數(shù)進(jìn)行統(tǒng)計(jì)
700 SQL > ? select ?deptno, count ( * ), sum (sal)? from ?scott.emp? group ? by ?deptno;
701
702 ???DEPTNO?? COUNT ( * )?? SUM (SAL)
703 -- -------?---------?---------
704 ??????? 10 ????????? 3 ?????? 8750
705 ??????? 20 ????????? 5 ????? 10875
706 ??????? 30 ????????? 6 ?????? 9400
707
708 ?
709
710 66 . HAVING
711 對分組統(tǒng)計(jì)再加限制條件
712 SQL > ? select ?deptno, count ( * ), sum (sal)? from ?scott.emp? group ? by ?deptno? having ? count ( * ) >= 5 ;
713
714 ???DEPTNO?? COUNT ( * )?? SUM (SAL)
715 -- -------?---------?---------
716 ??????? 20 ????????? 5 ????? 10875
717 ??????? 30 ????????? 6 ?????? 9400
718 SQL > ? select ?deptno, count ( * ), sum (sal)? from ?scott.emp? having ? count ( * ) >= 5 ? group ? by ?deptno?;
719
720 ???DEPTNO?? COUNT ( * )?? SUM (SAL)
721 -- -------?---------?---------
722 ??????? 20 ????????? 5 ????? 10875
723 ??????? 30 ????????? 6 ?????? 9400
724
725
726 67 . ORDER ? BY
727 用于對查詢到的結(jié)果進(jìn)行排序輸出
728 SQL > ? select ?deptno,ename,sal? from ?scott.emp? order ? by ?deptno,sal? desc ;
729
730 ???DEPTNO?ENAME????????????SAL
731 -- -------?----------?---------
732 ??????? 10 ?KING???????????? 5000
733 ??????? 10 ?CLARK??????????? 2450
734 ??????? 10 ?MILLER?????????? 1300
735 ??????? 20 ?SCOTT??????????? 3000
736 ??????? 20 ?FORD???????????? 3000
737 ??????? 20 ?JONES??????????? 2975
738 ??????? 20 ?ADAMS??????????? 1100
739 ??????? 20 ?SMITH???????????? 800
740 ??????? 30 ?BLAKE??????????? 2850
741 ??????? 30 ?ALLEN??????????? 1600
742 ??????? 30 ?TURNER?????????? 1500
743 ??????? 30 ?WARD???????????? 1250
744 ??????? 30 ?MARTIN?????????? 1250
745 ??????? 30 ?JAMES???????????? 950