oracle¼òµ¥Óï·¨±Ê¼Ç
¡¡
¡¡
DML Data manipulation language
SELECT
SELECT [DISTINCT] *|ÁÐxx [AS] "±ðÃûxx"[,ÁÐxx "±ðÃûxx"...]
×Ö·û´®Á¬½Ó·û ||, ×Ö·û»òÈÕÆÚÀàÐ͵Ä×Ö·û´®Óõ¥ÒýºÅ’’, ÁбðÃûÓÃË«ÒýºÅ“”¡£Èç¹û±ðÃûÖÐÓпոñ¡¢ÌØÊâ×Ö·û»òÕßÒªÇóÇø·Ö´óСд£¬±ØÐëÓÃË«ÒýºÅ¡£Ä¬ÈÏÇé¿öÏÂÁбêÌâΪ´óд£¬×Ö·ûºÍÈÕÆÚ±êÌâ¿¿×ó£¬Êý×Ö¿¿ÓÒ
ÁбðÃû¿ÉÒÔÓÃÔÚorder byÖУ¬µ«²»ÄÜÓÃÔÚwhereºÍgroup byÀï
SQL²»Çø·Ö´óСд£¬¹Ø¼ü×Ö²»Äܼòд»ò·ÖÐÐд£¬Óï¾ä½áÊøÓÃ;
ROWID,ROWNUMÐбêºÅ£¬ÐéÄâÁÐ
DISTINCTÏû³ýÖØ¸´ÁÐ
¡¡
from ±íxx ±í±ðÃûxx[£¬±íxx ±í±ðÃûxx...]
¡¡
¡¡
WHERE Ìõ¼þ
is NOT Null£¬is Null,NULLÊDz»È·¶¨µÄδ֪µÄÖµ
+ - * /
> >= = <= < <> !=
and, or, not
[not] between .. and
In, not In
Like, not like (%ÈÎÒⳤ¶È£¬_Ò»¸ö³¤¶È)
Like 'a/%' Escape '/' = like 'a%', escape ¶¨ÒåתÒå×Ö·û/£¬Ò²¿ÉÒÔÓÃÆäËû×Ö·û
ÓÅÏȹæÔò£ºËãÊõ¡·Á¬×Ö¡·±È½Ï¡·[not] null,like,in¡·[not] between¡·not¡·and¡·or
¡¡
GROUP BY ·Ö×éÁÐ HAVING ·Ö×éÌõ¼þ
·Ö×麯ÊýÖ»ÔÚ·Ö×éÖÐʹÓÃ(select,having)£¬²»ÔÊÐíÔÚwhereÖУ¬ÒªÖ¸¶¨Ìõ¼þÓÃhaving¡£
Select¹Ø¼ü×ÖÖ®ºóÁгöµÄÿ¸öͳ¼Æº¯ÊýÖ®ÍâµÄ×ֶΣ¬¶¼Òª³öÏÖÔÚgroup byÖ®ºó
ÓÃWhereÌõ¼þ½øÐзÖ×éǰµÄ¹ýÂË£¬having½øÐзÖ×éºóµÄ¹ýÂË£¬¼´Ö¸¶¨ÄÄЩ·Ö×é½á¹û±»ÏÔʾ
¡¡
ORDER BY ÅÅÐò×Ö¶Î ASC|DESC
order byºó¿ÉÒÔÊÇ×ֶλòÕß×Ö¶ÎÔËËãºóµÄ±í´ïʽ£¬Ä¬ÈÏAsc
order by¿ÉÒÔÓÃÁбðÃû£¬selectÖеÄÁÐÐòºÅ£¬Ò²¿ÉÒÔÊDz»ÔÚselectÖеÄÁÐ
¶à±í²éѯSQL:1999
select * from emp CROSS JOIN dept
½»²æÁ¬½Ó£º»á²úÉúµÑ¿¨¶û»ý
¡¡
select * from emp Netural JOIN dept
×ÔÈ»Á¬½Ó£º×Ô¶¯¹ØÁªÁ½¸ö±íÖÐÓÐÏàͬÃû×ÖºÍÊý¾ÝÀàÐ͵ÄÁÐ
¡¡
select * from emp JOIN dept USING(deptno)
Using×Ӿ䣺ֱ½Ó¹ØÁªÁÐ
¡¡
select * from emp e JOIN dept d ON(e.deptno=d.deptno)
On×Ӿ䣺Óû§×Ô¼º±àд¹ØÁªÌõ¼þ£¬¿ÉÒÔÓöà¸öjoin..onÁ¬½Ó¶à¸ö±í
¡¡
select * from emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno)
ÓÒÍâÁ¬½Ó£ºÏ൱ÓÚwhere e.deptno(+)=d.deptno
(+)·ÅÔÚÁ¬½ÓµÄȱÉÙÐÅÏ¢µÄÒ»²àµÄÁÐÃûºóÃæ£¬ÈçÏÔʾûÓÐÔ±¹¤µÄ²¿ÃÅ£¬½«+·ÅÔÚÔ±¹¤±íºóÃæ£»ÏÔʾûÓв¿ÃŵÄÔ±¹¤£¬½«+·ÅÔÚ²¿ÃűíºóÃæ
¡¡
select * from emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno)
×óÍâÁ¬½Ó£ºÏ൱ÓÚ where e.deptno=d.dept
Ïà¹ØÎĵµ£º
ÀûÓÃosÉóºË
µÇ¼oracleʱÔÚWinÖÐʵÏÖ¶ÔosµÄÉóºËÓÐÈçϼ¸²½£º
1¡¢ create os user id
2¡¢ create os group ora_dba(Õâ¸ö×éÖÐÓû§¾ßÓйÜÀíËùÓÐoracle database µÄȨÏÞ),
ora_sid_dba£¨Ö»ÄܶÔÓ¦µ½ÏàÓ¦sidµ ......
Ò»¡¢³£ÓÃÓï·¨ --1. ɾ³ý±íʱ¼¶ÁªÉ¾³ýÔ¼Êø
drop table ±íÃû cascade constraint
--2. µ±¸¸±íÖеÄÄÚÈݱ»É¾³ýºó£¬×Ó±íÖеÄÄÚÈÝÒ²±»É¾³ý
on delete casecade
--3. ÏÔʾ±íµÄ½á¹¹
desc ±íÃû
--4. ´´½¨ÐµÄÓû§
create user [username] identified by [password]
--5. ¸øÓû§·ÖÅäȨÏÞ
grant ȨÏÞ1¡¢È¨ÏÞ2...to Óû§ ......
1.»ù´¡ÈÏʶ
DBMS_RANDOM°üµÄÏêϸÐÅÏ¢£º
select text from all_source
where name = 'DBMS_RANDOM'
......