ä¸ãå
³ç³»è¿ç®ï¼1. çå¼æ¯è¾: =
è¯æ³ï¼A=B
æä½ç±»åï¼ææåºæ¬ç±»å
æè¿°: å¦æ表达å¼Aä¸è¡¨è¾¾å¼Bç¸çï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive>select 1 from lxw_dual where 1=1;
1
2. ä¸çå¼æ¯è¾: <>
è¯æ³: A <> B
æä½ç±»å: ææåºæ¬ç±»å
æè¿°: å¦æ表达å¼A为NULLï¼æè
表达å¼B为NULLï¼è¿åNULLï¼å¦æ表达å¼Aä¸è¡¨è¾¾å¼Bä¸ç¸çï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select1 from lxw_dual where 1 <> 2;
1
3. å°äºæ¯è¾: <
è¯æ³: A < B
æä½ç±»å: ææåºæ¬ç±»å
æè¿°: å¦æ表达å¼A为NULLï¼æè
表达å¼B为NULLï¼è¿åNULLï¼å¦æ表达å¼Aå°äºè¡¨è¾¾å¼Bï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select1 from lxw_dual where 1 < 2;
1
4. å°äºçäºæ¯è¾: <=
è¯æ³: A <= B
æä½ç±»å: ææåºæ¬ç±»å
æè¿°: å¦æ表达å¼A为NULLï¼æè
表达å¼B为NULLï¼è¿åNULLï¼å¦æ表达å¼Aå°äºæè
çäºè¡¨è¾¾å¼Bï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select1 from lxw_dual where 1 <= 1;
1
5. 大äºæ¯è¾: >
è¯æ³: A > B
æä½ç±»å: ææåºæ¬ç±»å
æè¿°: å¦æ表达å¼A为NULLï¼æè
表达å¼B为NULLï¼è¿åNULLï¼å¦æ表达å¼A大äºè¡¨è¾¾å¼Bï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select1 from lxw_dual where 2 > 1;
1
6. 大äºçäºæ¯è¾: >=
è¯æ³: A >= B
æä½ç±»å: ææåºæ¬ç±»å
æè¿°: å¦æ表达å¼A为NULLï¼æè
表达å¼B为NULLï¼è¿åNULLï¼å¦æ表达å¼A大äºæè
çäºè¡¨è¾¾å¼Bï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select1 from lxw_dual where 1 >= 1;
1
注æï¼Stringçæ¯è¾è¦æ³¨æ(常ç¨çæ¶é´æ¯è¾å¯ä»¥å
to_dateä¹ååæ¯è¾)
hive> select* from lxw_dual;
OK
201111120900:00:00 2011111209
hive> selecta,b,a<b,a>b,a=b from lxw_dual;
201111120900:00:00 2011111209 false true false
7. 空å¼å¤æ: IS NULL
è¯æ³: A IS NULL
æä½ç±»å: ææç±»å
æè¿°: å¦æ表达å¼Açå¼ä¸ºNULLï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select1 from lxw_dual where null is null;
1
8. é空å¤æ: IS NOTNULL
è¯æ³: A IS NOT NULL
æä½ç±»å: ææç±»å
æè¿°: å¦æ表达å¼Açå¼ä¸ºNULLï¼å为FALSEï¼å¦å为TRUE
举ä¾ï¼
hive> select1 from lxw_dual where 1 is not null;
1
9. LIKEæ¯è¾: LIKE
è¯æ³: A LIKE B
æä½ç±»å: strings
æè¿°: å¦æå符串Aæè
å符串B为NULLï¼åè¿åNULLï¼å¦æå符串A符å表达å¼B çæ£åè¯æ³ï¼å为TRUEï¼å¦å为FALSEãBä¸å符â_â表示任æå个å符ï¼èå符â%â表示任ææ°éçå符ã
举ä¾ï¼
hive> select1 from lxw_dual where 'football' like 'foot%';
1
hive> select1 from lxw_dual where 'football' like 'foot____';
1
注æï¼å¦å®æ¯è¾æ¶åç¨NOT ALIKE B
hive> select1 from lxw_dual where NOT 'football' like 'fff%';
1
10. JAVAçLIKEæä½: RLIKE
è¯æ³: A RLIKE B
æä½ç±»å: strings
æè¿°: å¦æå符串Aæè
å符串B为NULLï¼åè¿åNULLï¼å¦æå符串A符åJAVAæ£å表达å¼Bçæ£åè¯æ³ï¼å为TRUEï¼å¦å为FALSEã
举ä¾ï¼
hive> select1 from lxw_dual where 'footbarâ rlike '^f.*r$â;
1
注æï¼å¤æä¸ä¸ªå符串æ¯å¦å
¨ä¸ºæ°åï¼
hive>select 1from lxw_dual where '123456' rlike '^\\d+$';
1
hive> select1 from lxw_dual where '123456aa' rlike '^\\d+$';
11. REGEXPæä½: REGEXP
è¯æ³: A REGEXP B
æä½ç±»å: strings
æè¿°: åè½ä¸RLIKEç¸å
举ä¾ï¼
hive> select1 from lxw_dual where 'footbar' REGEXP '^f.*r$';
1
äºãæ°å¦è¿ç®ï¼1. å æ³æä½: +
è¯æ³: A + B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAä¸Bç¸å çç»æãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ãæ¯å¦ï¼int + int ä¸è¬ç»æ为intç±»åï¼èint + double ä¸è¬ç»æ为doubleç±»å
举ä¾ï¼
hive> select1 + 9 from lxw_dual;
10
hive> createtable lxw_dual as select 1 + 1.2 from lxw_dual;
hive>describe lxw_dual;
_c0 double
2. åæ³æä½: -
è¯æ³: A â B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAä¸Bç¸åçç»æãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ãæ¯å¦ï¼int â int ä¸è¬ç»æ为intç±»åï¼èint â double ä¸è¬ç»æ为doubleç±»å
举ä¾ï¼
hive> select10 â 5 from lxw_dual;
5
hive> createtable lxw_dual as select 5.6 â 4 from lxw_dual;
hive>describe lxw_dual;
_c0 double
3. ä¹æ³æä½: *
è¯æ³: A * B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAä¸Bç¸ä¹çç»æãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ã注æï¼å¦æAä¹ä»¥Bçç»æè¶
è¿é»è®¤ç»æç±»åçæ°å¼èå´ï¼åéè¦éè¿castå°ç»æ转æ¢æèå´æ´å¤§çæ°å¼ç±»å
举ä¾ï¼
hive> select40 * 5 from lxw_dual;
200
4. é¤æ³æä½: /
è¯æ³: A / B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAé¤ä»¥Bçç»æãç»æçæ°å¼ç±»å为double
举ä¾ï¼
hive> select40 / 5 from lxw_dual;
8.0
注æï¼hiveä¸æé«ç²¾åº¦çæ°æ®ç±»åæ¯double,åªç²¾ç¡®å°å°æ°ç¹å16ä½ï¼å¨åé¤æ³è¿ç®çæ¶åè¦ç¹å«æ³¨æ
hive>select ceil(28.0/6.999999999999999999999) from lxw_duallimit 1;
ç»æ为4
hive>select ceil(28.0/6.99999999999999) from lxw_dual limit1;
ç»æ为5
5. åä½æä½: %
è¯æ³: A % B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAé¤ä»¥Bçä½æ°ãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ã
举ä¾ï¼
hive> select 41 % 5 from lxw_dual;
1
hive> select 8.4 % 4 from lxw_dual;
0.40000000000000036
注æï¼ç²¾åº¦å¨hiveä¸æ¯ä¸ªå¾å¤§çé®é¢ï¼ç±»ä¼¼è¿æ ·çæä½æ好éè¿roundæå®ç²¾åº¦
hive> select round(8.4 % 4 , 2) from lxw_dual;
0.4
6. ä½ä¸æä½: &
è¯æ³: A & B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAåBæä½è¿è¡ä¸æä½çç»æãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ã
举ä¾ï¼
hive> select 4 & 8 from lxw_dual;
0
hive> select 6 & 4 from lxw_dual;
4
7. ä½ææä½: |
è¯æ³: A | B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAåBæä½è¿è¡ææä½çç»æãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ã
举ä¾ï¼
hive> select 4 | 8 from lxw_dual;
12
hive> select 6 | 8 from lxw_dual;
14
8. ä½å¼ææä½: ^
è¯æ³: A ^ B
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAåBæä½è¿è¡å¼ææä½çç»æãç»æçæ°å¼ç±»åçäºAçç±»ååBçç±»åçæå°ç¶ç±»åï¼è¯¦è§æ°æ®ç±»åç继æ¿å
³ç³»ï¼ã
举ä¾ï¼
hive> select 4 ^ 8 from lxw_dual;
12
hive> select 6 ^ 4 from lxw_dual;
2
9ï¼ä½ååæä½: ~
è¯æ³: ~A
æä½ç±»åï¼æææ°å¼ç±»å
说æï¼è¿åAæä½ååæä½çç»æãç»æçæ°å¼ç±»åçäºAçç±»åã
举ä¾ï¼
hive> select ~6 from lxw_dual;
-7
hive> select ~4 from lxw_dual;
-5
ä¸ãé»è¾è¿ç®ï¼1. é»è¾ä¸æä½: AND
è¯æ³: A AND B
æä½ç±»åï¼boolean
说æï¼å¦æAåBå为TRUEï¼å为TRUEï¼å¦å为FALSEãå¦æA为NULLæB为NULLï¼å为NULL
举ä¾ï¼
hive> select 1 from lxw_dual where 1=1 and 2=2;
1
2. é»è¾ææä½: OR
è¯æ³: A OR B
æä½ç±»åï¼boolean
说æï¼å¦æA为TRUEï¼æè
B为TRUEï¼æè
AåBå为TRUEï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select 1 from lxw_dual where 1=2 or 2=2;
1
3. é»è¾éæä½: NOT
è¯æ³: NOT A
æä½ç±»åï¼boolean
说æï¼å¦æA为FALSEï¼æè
A为NULLï¼å为TRUEï¼å¦å为FALSE
举ä¾ï¼
hive> select 1 from lxw_dual where not 1=2;
1
åãæ°å¼è®¡ç®1. åæ´å½æ°: round
è¯æ³: round(double a)
è¿åå¼: BIGINT
说æ: è¿ådoubleç±»åçæ´æ°å¼é¨åï¼éµå¾ªåèäºå
¥ï¼
举ä¾ï¼
hive> select round(3.1415926) from lxw_dual;
3
hive> select round(3.5) from lxw_dual;
4
hive> create table lxw_dual as select round(9542.158) fromlxw_dual;
hive> describe lxw_dual;
_c0 bigint
2. æå®ç²¾åº¦åæ´å½æ°: round
è¯æ³: round(double a, int d)
è¿åå¼: DOUBLE
说æ: è¿åæå®ç²¾åº¦dçdoubleç±»å
举ä¾ï¼
hive> select round(3.1415926,4) from lxw_dual;
3.1416
3. åä¸åæ´å½æ°: floor
è¯æ³: floor(double a)
è¿åå¼: BIGINT
说æ: è¿åçäºæè
å°äºè¯¥doubleåéçæ大çæ´æ°
举ä¾ï¼
hive> select floor(3.1415926) from lxw_dual;
3
hive> select floor(25) from lxw_dual;
25
4. åä¸åæ´å½æ°: ceil
è¯æ³: ceil(double a)
è¿åå¼: BIGINT
说æ: è¿åçäºæè
大äºè¯¥doubleåéçæå°çæ´æ°
举ä¾ï¼
hive> select ceil(3.1415926) from lxw_dual;
4
hive> select ceil(46) from lxw_dual;
46
5. åä¸åæ´å½æ°: ceiling
è¯æ³: ceiling(double a)
è¿åå¼: BIGINT
说æ: ä¸ceilåè½ç¸å
举ä¾ï¼
hive> select ceiling(3.1415926) from lxw_dual;
4
hive> select ceiling(46) from lxw_dual;
46
6. åéæºæ°å½æ°: rand
è¯æ³: rand(),rand(int seed)
è¿åå¼: double
说æ: è¿åä¸ä¸ª0å°1èå´å
çéæºæ°ãå¦ææå®ç§åseedï¼åä¼çå°ä¸ä¸ªç¨³å®çéæºæ°åºå
举ä¾ï¼
hive> select rand() from lxw_dual;
0.5577432776034763
hive> select rand() from lxw_dual;
0.6638336467363424
hive> select rand(100) from lxw_dual;
0.7220096548596434
hive> select rand(100) from lxw_dual;
0.7220096548596434
7. èªç¶ææ°å½æ°: exp
è¯æ³: exp(double a)
è¿åå¼: double
说æ: è¿åèªç¶å¯¹æ°eça次æ¹
举ä¾ï¼
hive> select exp(2) from lxw_dual;
7.38905609893065
èªç¶å¯¹æ°å½æ°: ln
è¯æ³: ln(double a)
è¿åå¼: double
说æ: è¿åaçèªç¶å¯¹æ°
举ä¾ï¼
hive> select ln(7.38905609893065) from lxw_dual;
2.0
8. 以10为åºå¯¹æ°å½æ°: log10
è¯æ³: log10(double a)
è¿åå¼: double
说æ: è¿å以10为åºçaç对æ°
举ä¾ï¼
hive> select log10(100) from lxw_dual;
2.0
9. 以2为åºå¯¹æ°å½æ°: log2
è¯æ³: log2(double a)
è¿åå¼: double
说æ: è¿å以2为åºçaç对æ°
举ä¾ï¼
hive> select log2(8) from lxw_dual;
3.0
10. 对æ°å½æ°: log
è¯æ³: log(double base, double a)
è¿åå¼: double
说æ: è¿å以base为åºçaç对æ°
举ä¾ï¼
hive> select log(4,256) from lxw_dual;
4.0
11. å¹è¿ç®å½æ°: pow
è¯æ³: pow(double a, double p)
è¿åå¼: double
说æ: è¿åaçp次å¹
举ä¾ï¼
hive> select pow(2,4) from lxw_dual;
16.0
12. å¹è¿ç®å½æ°: power
è¯æ³: power(double a, double p)
è¿åå¼: double
说æ: è¿åaçp次å¹,ä¸powåè½ç¸å
举ä¾ï¼
hive> select power(2,4) from lxw_dual;
16.0
13. å¼å¹³æ¹å½æ°: sqrt
è¯æ³: sqrt(double a)
è¿åå¼: double
说æ: è¿åaçå¹³æ¹æ ¹
举ä¾ï¼
hive> select sqrt(16) from lxw_dual;
4.0
14. äºè¿å¶å½æ°: bin
è¯æ³: bin(BIGINT a)
è¿åå¼: string
说æ: è¿åaçäºè¿å¶ä»£ç 表示
举ä¾ï¼
hive> select bin(7) from lxw_dual;
111
15. åå
è¿å¶å½æ°: hex
è¯æ³: hex(BIGINT a)
è¿åå¼: string
说æ: å¦æåéæ¯intç±»åï¼é£ä¹è¿åaçåå
è¿å¶è¡¨ç¤ºï¼å¦æåéæ¯stringç±»åï¼åè¿å该å符串çåå
è¿å¶è¡¨ç¤º
举ä¾ï¼
hive> select hex(17) from lxw_dual;
11
hive> select hex(âabcâ) from lxw_dual;
616263
16. å转åå
è¿å¶å½æ°: unhex
è¯æ³: unhex(string a)
è¿åå¼: string
说æ: è¿å该åå
è¿å¶å符串æ代ç çå符串
举ä¾ï¼
hive> select unhex(â616263â)from lxw_dual;
abc
hive> select unhex(â11â)from lxw_dual;
-
hive> select unhex(616263) from lxw_dual;
abc
17. è¿å¶è½¬æ¢å½æ°: conv
è¯æ³: conv(BIGINT num, int from_base, int to_base)
è¿åå¼: string
说æ: å°æ°å¼numä»from_baseè¿å¶è½¬åå°to_baseè¿å¶
举ä¾ï¼
hive> select conv(17,10,16) from lxw_dual;
11
hive> select conv(17,10,2) from lxw_dual;
10001
18. ç»å¯¹å¼å½æ°: abs
è¯æ³: abs(double a) abs(int a)
è¿åå¼: double int
说æ: è¿åæ°å¼açç»å¯¹å¼
举ä¾ï¼
hive> select abs(-3.9) from lxw_dual;
3.9
hive> select abs(10.9) from lxw_dual;
10.9
19. æ£åä½å½æ°: pmod
è¯æ³: pmod(int a, int b),pmod(double a, double b)
è¿åå¼: int double
说æ: è¿åæ£çaé¤ä»¥bçä½æ°
举ä¾ï¼
hive> select pmod(9,4) from lxw_dual;
1
hive> select pmod(-9,4) from lxw_dual;
3
20. æ£å¼¦å½æ°: sin
è¯æ³: sin(double a)
è¿åå¼: double
说æ: è¿åaçæ£å¼¦å¼
举ä¾ï¼
hive> select sin(0.8) from lxw_dual;
0.7173560908995228
21. åæ£å¼¦å½æ°: asin
è¯æ³: asin(double a)
è¿åå¼: double
说æ: è¿åaçåæ£å¼¦å¼
举ä¾ï¼
hive> select asin(0.7173560908995228) from lxw_dual;
0.8
22. ä½å¼¦å½æ°: cos
è¯æ³: cos(double a)
è¿åå¼: double
说æ: è¿åaçä½å¼¦å¼
举ä¾ï¼
hive> select cos(0.9) from lxw_dual;
0.6216099682706644
23. åä½å¼¦å½æ°: acos
è¯æ³: acos(double a)
è¿åå¼: double
说æ: è¿åaçåä½å¼¦å¼
举ä¾ï¼
hive> select acos(0.6216099682706644) from lxw_dual;
0.9
24. positiveå½æ°: positive
è¯æ³: positive(int a), positive(double a)
è¿åå¼: int double
说æ: è¿åa
举ä¾ï¼
hive> select positive(-10) from lxw_dual;
-10
hive> select positive(12) from lxw_dual;
12
25. negativeå½æ°: negative
è¯æ³: negative(int a), negative(double a)
è¿åå¼: int double
说æ: è¿å-a
举ä¾ï¼
hive> select negative(-5) from lxw_dual;
5
hive> select negative(8) from lxw_dual;
-8
温馨提示:答案为网友推荐,仅供参考