oracleµÄһЩ֪ʶС½á
SqlÖÐÁ½¸ö“-”±íʾעÊ͵ĿªÊ¼¡£
Æ´½ÓÔËËã·û£º”||”,×¢Ò⣺ֻÓÐÔÚËùÓеÄÔËËã·ûΪnullʱ£¬Æ´½ÓµÄ½á¹ûÊÇnull¡£
±È½ÏÔËËã·ûÓÃÓڱȽÏÁ½¸öÖµ»ò±í´ïʽ£¬¸ø³öÒ»¸ö²¼¶ûÐ͵Ľá¹û true,false,null.
±È½ÏÔËËã·û£º
=
!= <> ^=
[not]in °üº¬
Any some ½«Ò»¸öÖµÓëÁбíÖеÄÿ¸öÖµ»òÕßÓë²éѯ½á¹ûÖеÄÿһ¸öÖµ½øÐбȽϣ¬¼ÙÈç²éѯûÓзµ»Ø½á¹û£¬Ôò½á¹ûΪfalse¡£Ç°Ãæ±ØÐëÓÐ=,!=,>=,<µÈ·ûºÅ¡£
Select last_name from emp where salary<=any(select salary from emp where deptno=10);
All ½«Ò»¸öÖµÓëÁбíÖеÄÿ¸öÖµ»òÕßÓë²éѯ½á¹ûÖеÄÿ¸öÖµ½øÐбȽϣ¬¼ÙÈç²éѯûÓзµ»Ø½á¹û£¬Ôò½á¹ûΪtrue¡£Ç°Ãæ±ØÐëÓÐ=,!=,<,>µÈ·ûºÅ¡£
Select last_name from emp where salary<=all(500,1000.9000);
[not] between a and b
Select last_name from emp where salary between 4000 and 10000;
[not] exists ¼ÙÈçÒ»¸ö×Ó²éѯÖÁÉÙ·µ»ØÒ»ÐÐʱ£¬½á¹ûΪtrue
Select last_name from emp a where not exists(select ‘x’ dummu from emp b where b.deptno=10 and a.last_name=b.last_name);
A [not] like b [escape ‘char’] ÓÃÓÚģʽƥÅ䣻¼ÙÈçģʽaÓëģʽbÆ¥Å䣬Ôò½á¹ûΪtrue¡£Ä£Ê½Æ¥Åä·û%ÓÃÓÚÆ¥Åä0¸ö»òÈÎÒâ¶à¸ö×Ö·û¡£_ÓÃÓÚÆ¥ÅäÒ»¸ö×Ö·û¡£¹Ø¼ü×ÖescapeÓÃÓÚÌáʾoracle½âÊÍ%ºÍ_×Ö·û£¬²»Òª½«ËûÃÇ×÷ÎªÌØÊâ×Ö·û´®´¦Àí¡£
Select last_name from emp where last_name not like ‘Th%’;
Select last_name from emp where last_name like ‘_tt%’;
Select synonym_name from dba_synonums wnere synonym_name like ‘all\_u%’escape’\’;
Is [not] null Ö»ÓÐÒ»¸öÔËËã·û£¬ÓÃÀ´²âÊÔÆäÖµÊÇ·ñΪ¿Õ¡£
Select last_name from emp where salary is not null;
Âß¼ÔËËã·û£º
Not ½«½á¹ûÈ¡·´¡£¼ÙÈçÔËËãÊýÊÇnull£¬Ôò·µ»Ønull£¬µ±ÔËËãÊýÊÇfalseʱ£¬½á¹û·µ»Øtrue£¬·ñÔò·µ»Øfalse¡£
Select * from emp where not (salary<100)
And ¼ÙÈçËùÓеÄÔËËãÊýΪtrue£¬Ôò½á¹ûΪtrue£»Èç¹ûÆäÖÐÒ»¸öΪfalse£¬Ôò½á¹ûΪfalse¡£·ñÔò½á¹ûÊÇnull¡£
Or¼ÙÈçËùÓеÄÔËËãÊýΪfalse£¬Ôò½á¹ûΪfalse£»Èç¹ûÆäÖÐÒ»¸öΪtrue£¬Ôò½á¹ûΪtrue¡£·ñÔò½á¹ûÊÇnull¡£
¼¯ºÏÔËËã·û£º
Union ·µ»ØËùÓвéѯ½á¹ûÖеÄÐУ¬²»°üÀ¨Öظ´µÄÖµ
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
---------Êýѧº¯Êý
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:select cast ......
ÈçºÎÕýÈ·ÀûÓÃRownumÀ´ÏÞÖÆ²éѯËù·µ»ØµÄÐÐÊý£¿
º¬Òå½âÊÍ£º
1¡¢rownumÊÇoracleϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬
¡¡ ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖÆ²éѯ·µ»ØµÄ×ÜÐÐÊý¡£
2¡¢rownum²»ÄÜÒÔÈκλù±íµÄÃû³Æ×÷Ϊǰ׺¡£
ʹÓ÷½·¨£º
Ï ......
OracleάÈÕ³£»¤µãµÎ
ËäÈ»Oracleά»¤²»ÊÇÎÒµÄÖ°Ô𣬵«Æ½Ê±»¹ÊÇÄÑÃâÒª¸úËü´ò½»µÀ£¬Òò´Ë¶ÔÓÚOracleµÄÈÕ³£Î¬»¤ÂÔÖªÒ»¶þ»¹ÊǺÜÓкô¦µÄ¡£
1. µÇ¼£º
£¨1£©²ÉÓÃϵͳ¹ÜÀíÔ±Óû§µÇ¼£º
#su - oracle
$ sqlplus / as sysdba
£¨2£©²ÉÓÃÒ»°ãÓû§µÇ¼£¬¼ÙÉèÓû§ÃûΪoracle£¬ÃÜÂëΪpasswor ......
update t_tmprpt_firstreplycosttime t
set (t.firstreplytime,
t.dealstaff,
t.firstreplyfailcontent)
= (select a.suggesttime,
a.suggester,
substr(a.remark,instr(a.remark,'¡¿',1)+2)
from t_wf_suggesthis a
......